# database.py — SQLite layer for TDX BLACK HOST
import sqlite3
import threading
import logging
from datetime import datetime
from typing import Optional, List, Dict, Any
from config import DATABASE_PATH

logger = logging.getLogger('TDX.DB')
_local = threading.local()


def get_conn() -> sqlite3.Connection:
    if not hasattr(_local, 'conn') or _local.conn is None:
        _local.conn = sqlite3.connect(DATABASE_PATH, check_same_thread=False)
        _local.conn.row_factory = sqlite3.Row
        _local.conn.execute("PRAGMA journal_mode=WAL")
        _local.conn.execute("PRAGMA foreign_keys=ON")
    return _local.conn


def init_db() -> None:
    conn = get_conn()
    cur = conn.cursor()

    cur.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        user_id     INTEGER PRIMARY KEY,
        username    TEXT,
        first_name  TEXT,
        last_name   TEXT,
        created_at  TEXT DEFAULT (datetime('now')),
        last_seen   TEXT DEFAULT (datetime('now')),
        is_banned   INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS admins (
        user_id     INTEGER PRIMARY KEY,
        added_by    INTEGER,
        added_at    TEXT DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS subscriptions (
        user_id     INTEGER PRIMARY KEY,
        plan        TEXT DEFAULT 'FREE',
        expires_at  TEXT,
        updated_at  TEXT DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS projects (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_id        INTEGER NOT NULL,
        name            TEXT NOT NULL,
        file_type       TEXT NOT NULL,
        file_path       TEXT NOT NULL,
        file_size       INTEGER DEFAULT 0,
        status          TEXT DEFAULT 'pending',
        approval_status TEXT DEFAULT 'pending',
        created_at      TEXT DEFAULT (datetime('now')),
        scan_result     TEXT DEFAULT 'UNKNOWN',
        scan_details    TEXT DEFAULT ''
    );

    CREATE TABLE IF NOT EXISTS processes (
        project_id  INTEGER PRIMARY KEY,
        pid         INTEGER,
        started_at  TEXT,
        log_file    TEXT,
        FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS settings (
        key     TEXT PRIMARY KEY,
        value   TEXT
    );

    CREATE TABLE IF NOT EXISTS banned_users (
        user_id     INTEGER PRIMARY KEY,
        banned_by   INTEGER,
        reason      TEXT,
        banned_at   TEXT DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_id);
    CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
    """)

    conn.commit()

    # default settings
    defaults = [
        ('bot_locked', '0'),
        ('maintenance_msg', 'System under maintenance. Back shortly.'),
    ]
    for key, val in defaults:
        cur.execute("INSERT OR IGNORE INTO settings(key,value) VALUES(?,?)", (key, val))
    conn.commit()
    logger.info("Database initialized.")


# ── users ──────────────────────────────────────────────────────────────────

def upsert_user(user_id: int, username: str, first_name: str, last_name: str) -> None:
    conn = get_conn()
    conn.execute("""
        INSERT INTO users(user_id,username,first_name,last_name,last_seen)
        VALUES(?,?,?,?,datetime('now'))
        ON CONFLICT(user_id) DO UPDATE SET
            username=excluded.username,
            first_name=excluded.first_name,
            last_name=excluded.last_name,
            last_seen=datetime('now')
    """, (user_id, username or '', first_name or '', last_name or ''))

    conn.execute("""
        INSERT OR IGNORE INTO subscriptions(user_id) VALUES(?)
    """, (user_id,))
    conn.commit()


def get_user(user_id: int) -> Optional[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM users WHERE user_id=?", (user_id,)
    ).fetchone()


def get_all_users() -> List[sqlite3.Row]:
    return get_conn().execute("SELECT * FROM users ORDER BY created_at DESC").fetchall()


def get_user_count() -> int:
    return get_conn().execute("SELECT COUNT(*) FROM users").fetchone()[0]


def is_banned(user_id: int) -> bool:
    row = get_conn().execute(
        "SELECT 1 FROM banned_users WHERE user_id=?", (user_id,)
    ).fetchone()
    return row is not None


def ban_user(user_id: int, banned_by: int, reason: str = '') -> None:
    conn = get_conn()
    conn.execute("""
        INSERT OR REPLACE INTO banned_users(user_id,banned_by,reason)
        VALUES(?,?,?)
    """, (user_id, banned_by, reason))
    conn.execute("UPDATE users SET is_banned=1 WHERE user_id=?", (user_id,))
    conn.commit()


def unban_user(user_id: int) -> None:
    conn = get_conn()
    conn.execute("DELETE FROM banned_users WHERE user_id=?", (user_id,))
    conn.execute("UPDATE users SET is_banned=0 WHERE user_id=?", (user_id,))
    conn.commit()


# ── admins ─────────────────────────────────────────────────────────────────

def is_admin(user_id: int, owner_id: int) -> bool:
    if user_id == owner_id:
        return True
    row = get_conn().execute(
        "SELECT 1 FROM admins WHERE user_id=?", (user_id,)
    ).fetchone()
    return row is not None


def add_admin(user_id: int, added_by: int) -> None:
    get_conn().execute(
        "INSERT OR IGNORE INTO admins(user_id,added_by) VALUES(?,?)",
        (user_id, added_by)
    )
    get_conn().commit()
    upsert_subscription(user_id, 'ADMIN')


def remove_admin(user_id: int) -> None:
    get_conn().execute("DELETE FROM admins WHERE user_id=?", (user_id,))
    get_conn().commit()
    upsert_subscription(user_id, 'FREE')


def get_all_admins() -> List[sqlite3.Row]:
    return get_conn().execute("SELECT * FROM admins").fetchall()


# ── subscriptions ──────────────────────────────────────────────────────────

def get_subscription(user_id: int) -> Optional[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM subscriptions WHERE user_id=?", (user_id,)
    ).fetchone()


def upsert_subscription(user_id: int, plan: str, expires_at: str = None) -> None:
    conn = get_conn()
    conn.execute("""
        INSERT INTO subscriptions(user_id,plan,expires_at,updated_at)
        VALUES(?,?,?,datetime('now'))
        ON CONFLICT(user_id) DO UPDATE SET
            plan=excluded.plan,
            expires_at=excluded.expires_at,
            updated_at=datetime('now')
    """, (user_id, plan, expires_at))
    conn.commit()


def get_plan_limit(user_id: int, owner_id: int) -> int:
    from config import SUBSCRIPTION_LIMITS
    if user_id == owner_id:
        return -1
    if is_admin(user_id, owner_id):
        return -1
    sub = get_subscription(user_id)
    plan = sub['plan'] if sub else 'FREE'
    return SUBSCRIPTION_LIMITS.get(plan, SUBSCRIPTION_LIMITS['FREE'])['projects']


# ── projects ───────────────────────────────────────────────────────────────

def create_project(owner_id: int, name: str, file_type: str,
                   file_path: str, file_size: int) -> int:
    conn = get_conn()
    cur = conn.execute("""
        INSERT INTO projects(owner_id,name,file_type,file_path,file_size,status,approval_status)
        VALUES(?,?,?,?,?,'pending','pending')
    """, (owner_id, name, file_type, file_path, file_size))
    conn.commit()
    return cur.lastrowid


def get_project(project_id: int) -> Optional[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM projects WHERE id=?", (project_id,)
    ).fetchone()


def get_user_projects(owner_id: int) -> List[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM projects WHERE owner_id=? ORDER BY created_at DESC",
        (owner_id,)
    ).fetchall()


def get_all_projects() -> List[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM projects ORDER BY created_at DESC"
    ).fetchall()


def get_pending_projects() -> List[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM projects WHERE approval_status='pending' ORDER BY created_at DESC"
    ).fetchall()


def update_project_status(project_id: int, status: str) -> None:
    get_conn().execute(
        "UPDATE projects SET status=? WHERE id=?", (status, project_id)
    )
    get_conn().commit()


def update_project_approval(project_id: int, approval: str) -> None:
    get_conn().execute(
        "UPDATE projects SET approval_status=? WHERE id=?", (approval, project_id)
    )
    get_conn().commit()


def update_project_scan(project_id: int, result: str, details: str) -> None:
    get_conn().execute(
        "UPDATE projects SET scan_result=?,scan_details=? WHERE id=?",
        (result, details, project_id)
    )
    get_conn().commit()


def delete_project(project_id: int) -> None:
    get_conn().execute("DELETE FROM projects WHERE id=?", (project_id,))
    get_conn().commit()


def count_user_projects(owner_id: int) -> int:
    return get_conn().execute(
        "SELECT COUNT(*) FROM projects WHERE owner_id=? AND approval_status != 'rejected'",
        (owner_id,)
    ).fetchone()[0]


def get_running_count() -> int:
    return get_conn().execute(
        "SELECT COUNT(*) FROM projects WHERE status='running'"
    ).fetchone()[0]


# ── processes ──────────────────────────────────────────────────────────────

def save_process(project_id: int, pid: int, log_file: str) -> None:
    conn = get_conn()
    conn.execute("""
        INSERT OR REPLACE INTO processes(project_id,pid,started_at,log_file)
        VALUES(?,?,datetime('now'),?)
    """, (project_id, pid, log_file))
    conn.commit()


def get_process_row(project_id: int) -> Optional[sqlite3.Row]:
    return get_conn().execute(
        "SELECT * FROM processes WHERE project_id=?", (project_id,)
    ).fetchone()


def delete_process(project_id: int) -> None:
    get_conn().execute("DELETE FROM processes WHERE project_id=?", (project_id,))
    get_conn().commit()


# ── settings ───────────────────────────────────────────────────────────────

def get_setting(key: str) -> Optional[str]:
    row = get_conn().execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
    return row['value'] if row else None


def set_setting(key: str, value: str) -> None:
    conn = get_conn()
    conn.execute(
        "INSERT OR REPLACE INTO settings(key,value) VALUES(?,?)", (key, value)
    )
    conn.commit()