from app.database import get_connection

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):
    conn = get_connection()
    cursor = conn.cursor()
    _ensure_security_code_unique(conn)

    # 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
    """
    cursor.executemany(sql, data)
    conn.commit()
    conn.close()

    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 as e:
        return {"status": 500, "message": f"Error processing security data: {str(e)}"}


def get_mkt_security_from_sqlite():
    conn = get_connection()
    cursor = conn.cursor()
    sql = f"SELECT * FROM {TABLE_NAME}"
    cursor.execute(sql)
    result = cursor.fetchall()
    conn.close()
    return result


def get_outstanding_shares_security_codes():
    conn = get_connection()
    cursor = conn.cursor()
    sql = f"SELECT security_code, outstanding_shares_mn, floating_shares_mn, floating_percent FROM {TABLE_NAME}"
    cursor.execute(sql)
    result = cursor.fetchall()
    conn.close()
    return result
