import os
import logging
import sqlite3
import time

from flask import g
from web.config import Config

logger = logging.getLogger(__name__)


def _database_path(db_name):
    paths = {
        'l2': Config.DB_L2_PATH,
        'l3': Config.DB_L3_PATH,
        'web': Config.DB_WEB_PATH,
    }
    try:
        return paths[db_name]
    except KeyError as exc:
        raise ValueError(f"Unknown database: {db_name}") from exc


def initialize_web_db():
    """Create and migrate the small application-owned database."""
    os.makedirs(os.path.dirname(Config.DB_WEB_PATH), exist_ok=True)
    db = sqlite3.connect(
        Config.DB_WEB_PATH,
        timeout=Config.SQLITE_TIMEOUT_SECONDS,
    )
    try:
        table_exists = db.execute(
            "SELECT 1 FROM sqlite_master WHERE type='table' AND name='team_lineups'"
        ).fetchone()
        if table_exists:
            columns = {
                row[1] for row in db.execute("PRAGMA table_info(team_lineups)")
            }
            if 'is_active' not in columns:
                db.execute(
                    "ALTER TABLE team_lineups "
                    "ADD COLUMN is_active INTEGER NOT NULL DEFAULT 0"
                )
                first_id = db.execute(
                    "SELECT id FROM team_lineups "
                    "ORDER BY created_at DESC, id DESC LIMIT 1"
                ).fetchone()
                if first_id:
                    db.execute(
                        "UPDATE team_lineups SET is_active = 1 WHERE id = ?",
                        first_id,
                    )
            else:
                active_ids = [
                    row[0] for row in db.execute(
                        "SELECT id FROM team_lineups WHERE is_active = 1 "
                        "ORDER BY created_at DESC, id DESC"
                    )
                ]
                if not active_ids:
                    latest_id = db.execute(
                        "SELECT id FROM team_lineups "
                        "ORDER BY created_at DESC, id DESC LIMIT 1"
                    ).fetchone()
                    if latest_id:
                        db.execute(
                            "UPDATE team_lineups SET is_active = 1 WHERE id = ?",
                            latest_id,
                        )
                elif len(active_ids) > 1:
                    db.execute("UPDATE team_lineups SET is_active = 0")
                    db.execute(
                        "UPDATE team_lineups SET is_active = 1 WHERE id = ?",
                        [active_ids[0]],
                    )

        with open(Config.DB_WEB_SCHEMA_PATH, 'r', encoding='utf-8') as schema_file:
            db.executescript(schema_file.read())
        db.execute(
            """
            INSERT OR IGNORE INTO schema_migrations (version, description)
            VALUES (?, ?)
            """,
            [
                Config.WEB_SCHEMA_VERSION,
                'Roster versions, member roles and team performance governance',
            ],
        )
        db.commit()
    finally:
        db.close()


def get_db(db_name):
    """
    db_name: 'l2', 'l3', or 'web'
    """
    db_attr = f'db_{db_name}'
    db = getattr(g, db_attr, None)
    
    if db is None:
        path = _database_path(db_name)
        if db_name != 'web' and not os.path.exists(path):
            raise RuntimeError(
                f"{db_name.upper()} database does not exist: {path}. "
                "Run the corresponding data builder first."
            )
        db = sqlite3.connect(
            path,
            timeout=Config.SQLITE_TIMEOUT_SECONDS,
        )
        db.row_factory = sqlite3.Row
        db.execute("PRAGMA busy_timeout = 15000")
        if db_name != 'l3':
            db.execute("PRAGMA foreign_keys = ON")
        setattr(g, db_attr, db)
        
    return db

def close_dbs(e=None):
    for db_name in ['l2', 'l3', 'web']:
        db_attr = f'db_{db_name}'
        db = getattr(g, db_attr, None)
        if db is not None:
            db.close()

def query_db(db_name, query, args=(), one=False):
    started = time.perf_counter()
    cur = get_db(db_name).execute(query, args)
    try:
        rv = cur.fetchall()
    finally:
        cur.close()
    duration = time.perf_counter() - started
    if duration >= Config.SLOW_QUERY_THRESHOLD_SECONDS:
        logger.warning(
            "Slow query db=%s duration=%.3fs sql=%s",
            db_name,
            duration,
            " ".join(query.split())[:500],
        )
    return (rv[0] if rv else None) if one else rv

def execute_db(db_name, query, args=()):
    db = get_db(db_name)
    started = time.perf_counter()
    cur = db.execute(query, args)
    db.commit()
    duration = time.perf_counter() - started
    if duration >= Config.SLOW_QUERY_THRESHOLD_SECONDS:
        logger.warning(
            "Slow write db=%s duration=%.3fs sql=%s",
            db_name,
            duration,
            " ".join(query.split())[:500],
        )
    cur.close()
    return cur.lastrowid