import logging
import os
import sqlite3
from contextlib import closing
from pathlib import Path

logger = logging.getLogger("lvar.database")

# Default lives next to the storage modules; override with DATABASE_PATH (tests, mounted volume).
_DEFAULT_DATABASE_PATH = Path(__file__).resolve().parent / "storage" / "finStock.db"
DATABASE_PATH = os.getenv("DATABASE_PATH") or str(_DEFAULT_DATABASE_PATH)
MKT_DAY_END_DATA_TABLE_NAME = "mktDayEndData"
MKT_SECURITY_INFO_TABLE_NAME = "mktSecurityInfo"
# Only these identifiers may ever be interpolated into SQL text.
ALLOWED_TABLES = frozenset(
    {MKT_DAY_END_DATA_TABLE_NAME, MKT_SECURITY_INFO_TABLE_NAME}
)
# 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 _validate_table(table_name):
    """Whitelist table names before they are used in f-string SQL."""
    if table_name not in ALLOWED_TABLES:
        raise ValueError(f"Unknown table name: {table_name!r}")
    return table_name


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)
    try:
        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)}")
    except Exception:
        conn.close()
        raise
    return conn


def create_database(database_path=DATABASE_PATH):
    with closing(get_connection(database_path)):
        pass
    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):
    _validate_table(table_name)
    with closing(get_connection(database_path)) as conn:
        conn.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})")
        conn.commit()
    return True


def check_table_exists(table_name, database_path=DATABASE_PATH):
    with closing(get_connection(database_path)) as conn:
        result = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
            (table_name,),
        ).fetchone()
    return result is not None


def delete_table(table_name, database_path=DATABASE_PATH):
    _validate_table(table_name)
    with closing(get_connection(database_path)) as conn:
        conn.execute(f"DROP TABLE IF EXISTS {table_name}")
        conn.commit()
    return True


def get_table_column_count(table_name, database_path=DATABASE_PATH):
    _validate_table(table_name)
    if not check_table_exists(table_name, database_path):
        return {
            "status": 404,
            "message": "Table not found",
            "count": 0
        }
    with closing(get_connection(database_path)) as conn:
        result = conn.execute(f"SELECT COUNT(*) FROM {table_name}").fetchone()
    state = result[0] > 0

    if state:
        status = 200
        dataCount = result[0]
    else:
        status = 203
        dataCount = 0

    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:
            logger.info("Table deleted successfully")
            return True
        else:
            logger.info("Table deletion failed")
            return False
    else:
        logger.info("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:
            logger.info("Table deleted successfully")
            return True
        else:
            logger.info("Table deletion failed")
            return False
    else:
        logger.info("Table not found")
        return True


def create_MktDayEndData_table_if_not_exists():
    if check_table_exists(MKT_DAY_END_DATA_TABLE_NAME):
        logger.info("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:
            logger.info("Table created successfully")
            return True
        else:
            logger.info("Table creation failed")
            return False


def create_MktSecurityInfo_table_if_not_exists():
    if check_table_exists(MKT_SECURITY_INFO_TABLE_NAME):
        logger.info("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:
            logger.info("Table created successfully")
            return True
        else:
            logger.info("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 / 409 / 500) and per-table affected row counts.
    """
    msi = MKT_SECURITY_INFO_TABLE_NAME
    mde = MKT_DAY_END_DATA_TABLE_NAME
    try:
        with closing(get_connection()) as conn:
            try:
                cur = conn.cursor()
                # Check the code actually exists in at least one table
                cur.execute(
                    f"SELECT COUNT(*) FROM {msi} WHERE security_code = ?",
                    (old_code,),
                )
                msi_count = cur.fetchone()[0]

                cur.execute(
                    f"SELECT COUNT(*) FROM {mde} WHERE security_code = ?",
                    (old_code,),
                )
                mde_count = cur.fetchone()[0]

                if msi_count == 0 and mde_count == 0:
                    return {
                        "status": 404,
                        "message": f"Security code '{old_code}' not found in any table",
                    }

                # Collision: new_code already used where it would violate a UNIQUE key
                cur.execute(
                    f"SELECT COUNT(*) FROM {msi} WHERE security_code = ?",
                    (new_code,),
                )
                msi_conflict = cur.fetchone()[0] > 0 and msi_count > 0
                cur.execute(
                    f"SELECT COUNT(*) FROM {mde} n JOIN {mde} o ON n.date = o.date "
                    f"WHERE n.security_code = ? AND o.security_code = ?",
                    (new_code, old_code),
                )
                mde_conflict = cur.fetchone()[0] > 0
                if msi_conflict or mde_conflict:
                    return {
                        "status": 409,
                        "message": f"Security code '{new_code}' already exists; rename would cause a conflict",
                    }

                # Perform both updates inside one transaction
                cur.execute(
                    f"UPDATE {msi} SET security_code = ? WHERE security_code = ?",
                    (new_code, old_code),
                )
                msi_updated = cur.rowcount

                cur.execute(
                    f"UPDATE {mde} SET security_code = ? WHERE security_code = ?",
                    (new_code, old_code),
                )
                mde_updated = cur.rowcount

                conn.commit()
            except sqlite3.IntegrityError:
                conn.rollback()
                return {
                    "status": 409,
                    "message": f"Security code '{new_code}' already exists; rename would cause a conflict",
                }
            except Exception:
                conn.rollback()
                raise

        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:
        logger.exception("Rename of security code failed")
        return {
            "status": 500,
            "message": "Rename failed",
        }
