import sqlite3
import os

DATABASE_PATH = os.getenv("DATABASE_PATH", "finStock.db")
MKT_DAY_END_DATA_TABLE_NAME = "mktDayEndData"
MKT_SECURITY_INFO_TABLE_NAME = "mktSecurityInfo"
# Wait up to this many seconds when another writer holds the DB (Cloud Run concurrent requests).
SQLITE_TIMEOUT_SEC = float(os.getenv("SQLITE_TIMEOUT_SEC", "60"))


def get_connection(database_path=DATABASE_PATH, row_factory=None):
    """Open SQLite with WAL + busy timeout to reduce 'database is locked' under concurrent writes."""
    conn = sqlite3.connect(database_path, timeout=SQLITE_TIMEOUT_SEC)
    if row_factory is not None:
        conn.row_factory = row_factory
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute(f"PRAGMA busy_timeout={int(SQLITE_TIMEOUT_SEC * 1000)}")
    return conn


def create_database(database_path=DATABASE_PATH):
    conn = get_connection(database_path)
    conn.close()
    return True


def delete_database(database_path=DATABASE_PATH):
    if os.path.exists(database_path):
        os.remove(database_path)
        return True
    return False


def create_table(table_name, columns, database_path=DATABASE_PATH):
    conn = get_connection(database_path)
    cursor = conn.cursor()
    cursor.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})")
    conn.commit()
    conn.close()
    return True


def check_table_exists(table_name, database_path=DATABASE_PATH):
    conn = get_connection(database_path)
    cursor = conn.cursor()
    cursor.execute(
        f"SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}'"
    )
    result = cursor.fetchone()
    conn.close()
    return result is not None


def delete_table(table_name, database_path=DATABASE_PATH):
    conn = get_connection(database_path)
    cursor = conn.cursor()
    cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
    conn.commit()
    conn.close()
    return True


def get_table_column_count(table_name, database_path=DATABASE_PATH):
    if not check_table_exists(table_name, database_path):
        return {
            "status": 404,
            "message": "Table not found",
            "count": 0
        }
    conn = get_connection(database_path)
    cursor = conn.cursor()
    cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
    result = cursor.fetchone()
    state = True if result[0] > 0 else False

    if state:
        status = 200
        dataCount = result[0]
    else:
        status = 203
        dataCount = 0
    conn.close()

    return {
        "status": status,
        "message": "Table column count",
        "count": dataCount
    }


# --- Project State Management Functions ---

def projectState_init():
    res_db = create_database()
    res_mde = create_MktDayEndData_table_if_not_exists()
    res_msi = create_MktSecurityInfo_table_if_not_exists()
    return res_db and res_mde and res_msi


def projectState_delete():
    res_mde = delete_MktDayEndData_table_if_not_exists()
    res_msi = delete_MktSecurityInfo_table_if_not_exists()
    res_db = delete_database()
    return res_mde and res_msi and res_db


def delete_MktDayEndData_table_if_not_exists():
    if check_table_exists(MKT_DAY_END_DATA_TABLE_NAME):
        res = delete_table(MKT_DAY_END_DATA_TABLE_NAME)
        if res:
            print("Table deleted successfully")
            return True
        else:
            print("Table deletion failed")
            return False
    else:
        print("Table not found")
        return True


def delete_MktSecurityInfo_table_if_not_exists():
    if check_table_exists(MKT_SECURITY_INFO_TABLE_NAME):
        res = delete_table(MKT_SECURITY_INFO_TABLE_NAME)
        if res:
            print("Table deleted successfully")
            return True
        else:
            print("Table deletion failed")
            return False
    else:
        print("Table not found")
        return True


def create_MktDayEndData_table_if_not_exists():
    if check_table_exists(MKT_DAY_END_DATA_TABLE_NAME):
        print("Table already exists")
        return True
    else:
        columns = (
            "id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "date DATE NOT NULL, "
            "security_code VARCHAR(32) NOT NULL, "
            "un_open FLOAT, "
            "un_high FLOAT, "
            "un_low FLOAT, "
            "un_close FLOAT, "
            "un_volume INTEGER, "
            "total_value FLOAT, "
            "ad_open FLOAT, "
            "ad_high FLOAT, "
            "ad_low FLOAT, "
            "ad_close FLOAT, "
            "ad_volume INTEGER, "
            "adst_ratio_value FLOAT, "
            "pct_change FLOAT, "
            "pct_change_with_dsex FLOAT, "
            "create_at DATETIME, "
            "update_at DATETIME, "
            "UNIQUE(date, security_code)"
        )
        res = create_table(MKT_DAY_END_DATA_TABLE_NAME, columns)
        if res:
            print("Table created successfully")
            return True
        else:
            print("Table creation failed")
            return False


def create_MktSecurityInfo_table_if_not_exists():
    if check_table_exists(MKT_SECURITY_INFO_TABLE_NAME):
        print("Table already exists")
        return True
    else:
        columns = (
            "id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "is_active CHAR(1) NOT NULL, "
            "security_code VARCHAR(64) NOT NULL UNIQUE, "
            "company_name VARCHAR(128) NOT NULL, "
            "sector VARCHAR(64) NOT NULL, "
            "marginable CHAR(1) NOT NULL, "
            "un_close FLOAT NOT NULL, "
            "ad_close FLOAT NOT NULL, "
            "volitility FLOAT NOT NULL, "
            "betas FLOAT NOT NULL, "
            "outstanding_shares_mn FLOAT, "
            "floating_percent FLOAT, "
            "floating_shares_mn FLOAT, "
            "create_at DATETIME NOT NULL, "
            "update_at DATETIME NOT NULL"
        )
        res = create_table(MKT_SECURITY_INFO_TABLE_NAME, columns)
        if res:
            print("Table created successfully")
            return True
        else:
            print("Table creation failed")
            return False


def rename_security_code_in_all_tables(old_code: str, new_code: str) -> dict:
    """
    Rename a security_code across every table that holds it
    (mktDayEndData and mktSecurityInfo) in a single atomic transaction.

    Returns a dict with status (200 / 404 / 500) and per-table affected row counts.
    """
    conn = get_connection()
    try:
        # Check the code actually exists in at least one table
        cur = conn.cursor()
        cur.execute(
            f"SELECT COUNT(*) FROM {MKT_SECURITY_INFO_TABLE_NAME} WHERE security_code = ?",
            (old_code,),
        )
        msi_count = cur.fetchone()[0]

        cur.execute(
            f"SELECT COUNT(*) FROM {MKT_DAY_END_DATA_TABLE_NAME} WHERE security_code = ?",
            (old_code,),
        )
        mde_count = cur.fetchone()[0]

        if msi_count == 0 and mde_count == 0:
            conn.close()
            return {
                "status": 404,
                "message": f"Security code '{old_code}' not found in any table",
            }

        # Perform both updates inside one transaction
        cur.execute(
            f"UPDATE {MKT_SECURITY_INFO_TABLE_NAME} SET security_code = ? WHERE security_code = ?",
            (new_code, old_code),
        )
        msi_updated = cur.rowcount

        cur.execute(
            f"UPDATE {MKT_DAY_END_DATA_TABLE_NAME} SET security_code = ? WHERE security_code = ?",
            (new_code, old_code),
        )
        mde_updated = cur.rowcount

        conn.commit()
        conn.close()

        return {
            "status": 200,
            "message": f"Security code renamed from '{old_code}' to '{new_code}' successfully",
            "mktSecurityInfo_rows_updated": msi_updated,
            "mktDayEndData_rows_updated": mde_updated,
        }

    except Exception as e:
        conn.rollback()
        conn.close()
        return {
            "status": 500,
            "message": f"Rename failed: {str(e)}",
        }
