import logging
from contextlib import closing

from app.lvar.database import get_connection

logger = logging.getLogger("lvar.storage.mkt_security")

TABLE_NAME = "mktSecurityInfo"


def _ensure_security_code_unique(conn):
    """Allow ON CONFLICT upserts on existing DBs created without UNIQUE(security_code)."""
    conn.execute(
        f"CREATE UNIQUE INDEX IF NOT EXISTS idx_{TABLE_NAME}_security_code "
        f"ON {TABLE_NAME}(security_code)"
    )


def store_mkt_security_to_sqlite(data):
    # Single-pass upsert — much shorter write lock than SELECT + INSERT/UPDATE per row
    sql = f"""
        INSERT INTO {TABLE_NAME} (
            is_active, security_code, company_name, sector, marginable,
            un_close, ad_close, volitility, betas, outstanding_shares_mn,
            floating_percent, floating_shares_mn, create_at, update_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(security_code) DO UPDATE SET
            is_active = excluded.is_active,
            company_name = excluded.company_name,
            sector = excluded.sector,
            marginable = excluded.marginable,
            un_close = excluded.un_close,
            ad_close = excluded.ad_close,
            volitility = excluded.volitility,
            betas = excluded.betas,
            outstanding_shares_mn = excluded.outstanding_shares_mn,
            floating_percent = excluded.floating_percent,
            floating_shares_mn = excluded.floating_shares_mn,
            create_at = excluded.create_at,
            update_at = excluded.update_at
    """
    with closing(get_connection()) as conn:
        _ensure_security_code_unique(conn)
        conn.executemany(sql, data)
        conn.commit()

    return {
        "status": 200,
        "message": "Security data stored/updated successfully",
    }


def _num(value, default=0.0):
    """Coerce None / missing to default so NOT NULL float columns never reject rows."""
    if value is None or value == "":
        return default
    return value


def post_mkt_security_to_sqlite(securityData: list):
    try:
        batch_data_list = []
        for item in securityData:
            # Source may send nulls or the corrected spelling "volatility"
            volatility = item.get("volitility", item.get("volatility"))
            batch_data_list.append(
                (
                    item.get("is_active") or "Y",
                    item["security_code"],
                    item.get("company_name") or "",
                    item.get("sector") or "",
                    item.get("marginable") or "N",
                    _num(item.get("un_close")),
                    _num(item.get("ad_close")),
                    _num(volatility),
                    _num(item.get("betas")),
                    item.get("outstanding_shares_mn"),  # Can be NULL
                    item.get("floating_percent"),  # Can be NULL
                    item.get("floating_shares_mn"),  # Can be NULL
                    item.get("create_at"),
                    item.get("update_at"),
                )
            )
        result = store_mkt_security_to_sqlite(batch_data_list)
        if result["status"] == 200:
            reply_data = {
                "status": 200,
                "message": "Security data stored successfully",
                "data_count": len(batch_data_list),
            }
        else:
            reply_data = {
                "status": 500,
                "message": "Security data stored failed",
                "data_count": len(batch_data_list),
            }
        return reply_data
    except Exception:
        logger.exception("Error processing market security data")
        return {"status": 500, "message": "Error processing security data"}


def get_mkt_security_from_sqlite():
    sql = f"SELECT * FROM {TABLE_NAME}"
    with closing(get_connection()) as conn:
        return conn.execute(sql).fetchall()


def get_outstanding_shares_security_codes():
    sql = f"SELECT security_code, outstanding_shares_mn, floating_shares_mn, floating_percent FROM {TABLE_NAME}"
    with closing(get_connection()) as conn:
        return conn.execute(sql).fetchall()
