import logging
import sqlite3
from contextlib import closing

from app.lvar.database import get_connection

logger = logging.getLogger("lvar.storage.mkt_day_end")

TABLE_NAME = "mktDayEndData"
DEFAULT_LIMIT = 300
MAX_LIMIT = 5000
# Stay well below SQLite's bound-variable limit (999 on older builds).
IN_CHUNK_SIZE = 500


def _chunks(items, size=IN_CHUNK_SIZE):
    for i in range(0, len(items), size):
        yield items[i:i + size]


def _parse_limit(value) -> int:
    """Parse the `limit` filter safely: clamp to 1..MAX_LIMIT, bad value -> default."""
    try:
        n = int(str(value).strip())
    except (TypeError, ValueError):
        return DEFAULT_LIMIT
    if n <= 0:
        return DEFAULT_LIMIT
    return min(n, MAX_LIMIT)


def store_mkt_day_end_data_to_sqlite(data):
    # Insert data, update if duplicate based on date and security_code
    if not data:
        return {
            "status": 200,
            "message": "Data stored/updated successfully",
            "data_count": 0,
        }

    # Using INSERT OR REPLACE for upsert functionality
    sql = f"""
        INSERT INTO {TABLE_NAME} (
            date, security_code, un_open, un_high, un_low, un_close, un_volume, total_value,
            ad_open, ad_high, ad_low, ad_close, ad_volume, adst_ratio_value,
            pct_change, pct_change_with_dsex, create_at, update_at
        ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(date, security_code) DO UPDATE SET
            un_open = excluded.un_open,
            un_high = excluded.un_high,
            un_low = excluded.un_low,
            un_close = excluded.un_close,
            un_volume = excluded.un_volume,
            total_value = excluded.total_value,
            ad_open = excluded.ad_open,
            ad_high = excluded.ad_high,
            ad_low = excluded.ad_low,
            ad_close = excluded.ad_close,
            ad_volume = excluded.ad_volume,
            adst_ratio_value = excluded.adst_ratio_value,
            pct_change = excluded.pct_change,
            pct_change_with_dsex = excluded.pct_change_with_dsex,
            create_at = excluded.create_at,
            update_at = excluded.update_at
    """

    with closing(get_connection()) as conn:
        conn.executemany(sql, data)
        conn.commit()

    return {
        "status": 200,
        "message": "Data stored/updated successfully",
        "data_count": len(data),
    }


def post_mkt_day_end_data_to_sqlite(marketDayEndData: list):
    try:
        batch_data_list = []
        for item in marketDayEndData:
            if (
                item["un_close"] == 0
                or item["un_volume"] == 0
                or item["ad_close"] == 0
                or item["ad_volume"] == 0
            ):
                continue
            batch_data_list.append(
                (
                    item["date"],
                    item["security_code"],
                    item["un_open"],
                    item["un_high"],
                    item["un_low"],
                    item["un_close"],
                    item["un_volume"],
                    item["total_value"],
                    item["ad_open"],
                    item["ad_high"],
                    item["ad_low"],
                    item["ad_close"],
                    item["ad_volume"],
                    item["adst_ratio_value"],
                    item["pct_change"],
                    item["pct_change_with_dsex"],
                    item["create_at"],
                    item["update_at"],
                )
            )
        result = store_mkt_day_end_data_to_sqlite(batch_data_list)
        if result["status"] == 200:
            reply_data = {
                "status": 200,
                "message": "Data stored successfully",
                "data_count": len(batch_data_list),
            }
        else:
            reply_data = {
                "status": 500,
                "message": "Data stored failed",
                "data_count": len(batch_data_list),
            }
        return reply_data
    except Exception:
        logger.exception("Error processing market day-end data")
        return {"status": 500, "message": "Error processing data"}


def get_mkt_day_end_data_from_sqlite(filter_type: str | None = None, filter_value: str | None = None):
    sql = f"SELECT * FROM {TABLE_NAME} ORDER BY date DESC LIMIT {DEFAULT_LIMIT}"
    params = ()

    if filter_type and filter_value:
        if filter_type == "date":
            sql = f"SELECT * FROM {TABLE_NAME} WHERE date = ? ORDER BY date DESC"
            params = (filter_value,)
        elif filter_type == "security_code":
            sql = (
                f"SELECT * FROM {TABLE_NAME} WHERE security_code = ? ORDER BY date DESC"
            )
            params = (filter_value,)
        elif filter_type == "limit":
            sql = f"SELECT * FROM {TABLE_NAME} ORDER BY date DESC LIMIT ?"
            params = (_parse_limit(filter_value),)

    with closing(get_connection()) as conn:
        return conn.execute(sql, params).fetchall()


def get_today_mkt_day_end_data_from_sqlite():
    sql = f"SELECT security_code, ad_close as market_price FROM {TABLE_NAME} WHERE date = ? ORDER BY date ASC"
    today_date = "2025-11-02"
    with closing(get_connection(row_factory=sqlite3.Row)) as conn:
        rows = conn.execute(sql, (today_date,)).fetchall()

    return [dict(row) for row in rows]


def get_historical_market_price(security_codes):
    if hasattr(security_codes, "tolist"):
        security_codes = security_codes.tolist()
        security_codes.append("00DSEX")
    security_codes = list(security_codes)

    result = []
    with closing(get_connection(row_factory=sqlite3.Row)) as conn:
        for chunk in _chunks(security_codes):
            placeholders = ",".join(["?" for _ in chunk])
            sql = f"SELECT security_code, date, ad_close as market_price, ad_open, ad_high, ad_low, ad_volume, ad_close FROM {TABLE_NAME} WHERE security_code IN ({placeholders}) ORDER BY date ASC"
            result.extend(dict(row) for row in conn.execute(sql, chunk).fetchall())

    if len(security_codes) > IN_CHUNK_SIZE:
        result.sort(key=lambda r: r["date"])  # stable: restore global date order
    return result


def get_all_security_codes_last_valid_market_price():
    sql = f"SELECT security_code, ad_close AS market_price, date FROM ( SELECT security_code, ad_close, date, ROW_NUMBER() OVER ( PARTITION BY security_code ORDER BY date DESC ) AS rn FROM {TABLE_NAME} WHERE ad_close IS NOT NULL AND ad_close != 0 ) AS x WHERE rn = 1;"
    with closing(get_connection(row_factory=sqlite3.Row)) as conn:
        rows = conn.execute(sql).fetchall()
    return [dict(row) for row in rows]


def get_last_30_days_market_value_avg(security_codes: list):
    remove_00DSEX = [code for code in security_codes if code != "00DSEX"]

    adtv = []
    with closing(get_connection(row_factory=sqlite3.Row)) as conn:
        for chunk in _chunks(remove_00DSEX):
            placeholders = ",".join(["?" for _ in chunk])
            # Partitioned by security_code, so chunks are independent.
            sql = f"SELECT security_code, AVG(total_value) AS avg_total_value FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY security_code ORDER BY date DESC) AS rn FROM {TABLE_NAME} WHERE security_code IN ({placeholders}) ) t WHERE rn <= 30 GROUP BY security_code;"
            adtv.extend(conn.execute(sql, chunk).fetchall())
    return adtv
