"""Lưu trữ SQLite: thiết bị, backup, job, nhật ký, người dùng, cài đặt."""
from __future__ import annotations

import json
import sqlite3
import threading
from datetime import datetime
from pathlib import Path
from typing import Any, Iterable, Optional

SCHEMA = """
CREATE TABLE IF NOT EXISTS settings(key TEXT PRIMARY KEY, value TEXT);
CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    role TEXT NOT NULL,
    salt BLOB NOT NULL,
    wrapped_key BLOB NOT NULL,
    created TEXT);
CREATE TABLE IF NOT EXISTS devices(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    ip TEXT NOT NULL,
    port INTEGER DEFAULT 22,
    protocol TEXT DEFAULT 'ssh',
    vendor TEXT NOT NULL,
    username TEXT DEFAULT '',
    password_enc TEXT DEFAULT '',
    secret_enc TEXT DEFAULT '',
    site TEXT DEFAULT '',
    tags TEXT DEFAULT '',
    model TEXT DEFAULT '',
    os_version TEXT DEFAULT '',
    status TEXT DEFAULT 'unknown',
    last_seen TEXT DEFAULT '',
    notes TEXT DEFAULT '',
    simulated INTEGER DEFAULT 0);
CREATE TABLE IF NOT EXISTS backups(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id INTEGER NOT NULL,
    created TEXT NOT NULL,
    config TEXT NOT NULL,
    hash TEXT NOT NULL,
    note TEXT DEFAULT '',
    user TEXT DEFAULT '',
    path TEXT DEFAULT '');
CREATE INDEX IF NOT EXISTS idx_backups_dev ON backups(device_id, id);
CREATE TABLE IF NOT EXISTS jobs(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TEXT NOT NULL,
    label TEXT,
    user TEXT,
    dry_run INTEGER DEFAULT 0,
    total INTEGER DEFAULT 0,
    ok INTEGER DEFAULT 0,
    failed INTEGER DEFAULT 0,
    status TEXT DEFAULT 'running',
    finished TEXT DEFAULT '');
CREATE TABLE IF NOT EXISTS job_results(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    job_id INTEGER NOT NULL,
    device_id INTEGER,
    device_name TEXT,
    ip TEXT,
    status TEXT,
    message TEXT,
    commands TEXT,
    output TEXT,
    diff TEXT,
    started TEXT,
    finished TEXT);
CREATE TABLE IF NOT EXISTS scheduled_jobs(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TEXT NOT NULL,
    run_at TEXT NOT NULL,
    label TEXT,
    user TEXT,
    devices INTEGER DEFAULT 0,
    payload_enc TEXT,
    status TEXT DEFAULT 'scheduled',
    result TEXT DEFAULT '');
CREATE TABLE IF NOT EXISTS audit(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts TEXT NOT NULL,
    user TEXT,
    action TEXT,
    detail TEXT);
"""

DEVICE_FIELDS = ("name", "ip", "port", "protocol", "vendor", "username", "password_enc",
                 "secret_enc", "site", "tags", "model", "os_version", "notes", "simulated")


def now() -> str:
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")


class Database:
    def __init__(self, path: Path | str):
        self.path = str(path)
        self._lock = threading.RLock()
        self._conn = sqlite3.connect(self.path, check_same_thread=False)
        self._conn.row_factory = sqlite3.Row
        with self._lock:
            self._conn.executescript(SCHEMA)
            self._migrate()
            self._conn.commit()

    def _migrate(self) -> None:
        """Nâng cấp CSDL từ phiên bản cũ."""
        cols = {r["name"] for r in self._conn.execute("PRAGMA table_info(devices)").fetchall()}
        if "simulated" not in cols:
            self._conn.execute("ALTER TABLE devices ADD COLUMN simulated INTEGER DEFAULT 0")
        # bản cũ có hãng 'mock' (thiết bị giả lập kiểu Cisco IOS) -> Cisco IOS + cờ giả lập
        self._conn.execute("UPDATE devices SET vendor='cisco_ios', simulated=1 WHERE vendor='mock'")
        # SRX trước đây nằm chung với Junos switch -> tách thành dòng Juniper SRX riêng
        self._conn.execute("UPDATE devices SET vendor='juniper_srx' WHERE vendor='juniper_junos' AND lower(model) LIKE 'srx%'")

    # ---- helpers -------------------------------------------------------
    def _exec(self, sql: str, params: Iterable[Any] = ()) -> int:
        with self._lock:
            cur = self._conn.execute(sql, tuple(params))
            self._conn.commit()
            return cur.lastrowid or 0

    def _query(self, sql: str, params: Iterable[Any] = ()) -> list[dict]:
        with self._lock:
            cur = self._conn.execute(sql, tuple(params))
            return [dict(r) for r in cur.fetchall()]

    def _one(self, sql: str, params: Iterable[Any] = ()) -> Optional[dict]:
        rows = self._query(sql, params)
        return rows[0] if rows else None

    def close(self) -> None:
        with self._lock:
            self._conn.close()

    # ---- settings ------------------------------------------------------
    def get_setting(self, key: str, default: Any = None) -> Any:
        row = self._one("SELECT value FROM settings WHERE key=?", (key,))
        if row is None:
            return default
        try:
            return json.loads(row["value"])
        except (TypeError, ValueError):
            return row["value"]

    def set_setting(self, key: str, value: Any) -> None:
        self._exec("INSERT INTO settings(key,value) VALUES(?,?) "
                   "ON CONFLICT(key) DO UPDATE SET value=excluded.value", (key, json.dumps(value)))

    # ---- users ---------------------------------------------------------
    def add_user(self, username: str, role: str, salt: bytes, wrapped_key: bytes) -> int:
        return self._exec("INSERT INTO users(username,role,salt,wrapped_key,created) VALUES(?,?,?,?,?)",
                          (username, role, salt, wrapped_key, now()))

    def get_user(self, username: str) -> Optional[dict]:
        return self._one("SELECT * FROM users WHERE username=?", (username,))

    def list_users(self) -> list[dict]:
        return self._query("SELECT id,username,role,created FROM users ORDER BY username")

    def update_user_key(self, username: str, salt: bytes, wrapped_key: bytes) -> None:
        self._exec("UPDATE users SET salt=?, wrapped_key=? WHERE username=?", (salt, wrapped_key, username))

    def update_user_role(self, username: str, role: str) -> None:
        self._exec("UPDATE users SET role=? WHERE username=?", (role, username))

    def delete_user(self, username: str) -> None:
        self._exec("DELETE FROM users WHERE username=?", (username,))

    def count_users(self) -> int:
        return self._one("SELECT COUNT(*) AS n FROM users")["n"]

    # ---- devices -------------------------------------------------------
    def add_device(self, **kw: Any) -> int:
        cols = [c for c in DEVICE_FIELDS if c in kw]
        sql = f"INSERT INTO devices({','.join(cols)}) VALUES({','.join('?' * len(cols))})"
        return self._exec(sql, [kw[c] for c in cols])

    def update_device(self, dev_id: int, **kw: Any) -> None:
        allowed = set(DEVICE_FIELDS) | {"status", "last_seen"}
        cols = [c for c in kw if c in allowed]
        if not cols:
            return
        sql = f"UPDATE devices SET {','.join(c + '=?' for c in cols)} WHERE id=?"
        self._exec(sql, [kw[c] for c in cols] + [dev_id])

    def delete_device(self, dev_id: int) -> None:
        self._exec("DELETE FROM backups WHERE device_id=?", (dev_id,))
        self._exec("DELETE FROM devices WHERE id=?", (dev_id,))

    def get_device(self, dev_id: int) -> Optional[dict]:
        return self._one("SELECT * FROM devices WHERE id=?", (dev_id,))

    def list_devices(self, search: str = "", site: str = "", vendor: str = "") -> list[dict]:
        sql, params = "SELECT * FROM devices WHERE 1=1", []
        if search:
            like = f"%{search.lower()}%"
            sql += " AND (lower(name) LIKE ? OR lower(ip) LIKE ? OR lower(tags) LIKE ? OR lower(model) LIKE ?)"
            params += [like] * 4
        if site:
            sql += " AND site=?"
            params.append(site)
        if vendor:
            sql += " AND vendor=?"
            params.append(vendor)
        return self._query(sql + " ORDER BY site, name", params)

    def list_sites(self) -> list[str]:
        return [r["site"] for r in self._query(
            "SELECT DISTINCT site FROM devices WHERE site<>'' ORDER BY site")]

    def find_device_by_ip(self, ip: str) -> Optional[dict]:
        return self._one("SELECT * FROM devices WHERE ip=?", (ip,))

    # ---- backups -------------------------------------------------------
    def add_backup(self, device_id: int, config: str, hash_: str, note: str, user: str, path: str) -> int:
        return self._exec(
            "INSERT INTO backups(device_id,created,config,hash,note,user,path) VALUES(?,?,?,?,?,?,?)",
            (device_id, now(), config, hash_, note, user, path))

    def last_backup(self, device_id: int) -> Optional[dict]:
        return self._one("SELECT * FROM backups WHERE device_id=? ORDER BY id DESC LIMIT 1", (device_id,))

    def list_backups(self, device_id: int) -> list[dict]:
        return self._query(
            "SELECT id,device_id,created,hash,note,user,path,length(config) AS size "
            "FROM backups WHERE device_id=? ORDER BY id DESC", (device_id,))

    def get_backup(self, backup_id: int) -> Optional[dict]:
        return self._one("SELECT * FROM backups WHERE id=?", (backup_id,))

    def count_backups(self) -> int:
        return self._one("SELECT COUNT(*) AS n FROM backups")["n"]

    # ---- jobs ----------------------------------------------------------
    def add_job(self, label: str, user: str, dry_run: bool, total: int) -> int:
        return self._exec("INSERT INTO jobs(created,label,user,dry_run,total) VALUES(?,?,?,?,?)",
                          (now(), label, user, int(dry_run), total))

    def finish_job(self, job_id: int, ok: int, failed: int, status: str) -> None:
        self._exec("UPDATE jobs SET ok=?, failed=?, status=?, finished=? WHERE id=?",
                   (ok, failed, status, now(), job_id))

    def add_job_result(self, job_id: int, r: dict) -> int:
        return self._exec(
            "INSERT INTO job_results(job_id,device_id,device_name,ip,status,message,commands,output,diff,"
            "started,finished) VALUES(?,?,?,?,?,?,?,?,?,?,?)",
            (job_id, r.get("device_id"), r.get("name"), r.get("ip"), r.get("status"), r.get("message", ""),
             "\n".join(r.get("commands", [])), r.get("output", ""), r.get("diff", ""),
             r.get("started", ""), r.get("finished", "")))

    def list_jobs(self, limit: int = 100) -> list[dict]:
        return self._query("SELECT * FROM jobs ORDER BY id DESC LIMIT ?", (limit,))

    def job_results(self, job_id: int) -> list[dict]:
        return self._query("SELECT * FROM job_results WHERE job_id=? ORDER BY id", (job_id,))

    def get_job(self, job_id: int) -> Optional[dict]:
        return self._one("SELECT * FROM jobs WHERE id=?", (job_id,))

    # ---- lịch hẹn ------------------------------------------------------
    def add_sched(self, run_at: str, label: str, user: str, payload_enc: str, devices: int) -> int:
        return self._exec("INSERT INTO scheduled_jobs(created,run_at,label,user,devices,payload_enc) VALUES(?,?,?,?,?,?)",
                          (now(), run_at, label, user, devices, payload_enc))

    def list_sched(self, limit: int = 200) -> list[dict]:
        return self._query("SELECT id,created,run_at,label,user,devices,status,result FROM scheduled_jobs ORDER BY run_at DESC LIMIT ?", (limit,))

    def get_sched(self, sid: int) -> Optional[dict]:
        return self._one("SELECT * FROM scheduled_jobs WHERE id=?", (sid,))

    def due_sched(self, now_str: str) -> list[dict]:
        return self._query("SELECT * FROM scheduled_jobs WHERE status='scheduled' AND run_at<=? ORDER BY run_at", (now_str,))

    def update_sched(self, sid: int, status: str, result: str) -> None:
        self._exec("UPDATE scheduled_jobs SET status=?, result=? WHERE id=?", (status, result, sid))

    # ---- dọn dẹp backup ------------------------------------------------
    def prune_backups(self, device_id: int, keep: int = 50, days: int = 0) -> list[str]:
        """Xóa bản sao lưu cũ: giữ tối đa `keep` bản mới nhất; nếu days>0, xóa thêm các bản cũ hơn N ngày (luôn giữ bản mới nhất)."""
        rows = self._query("SELECT id,created,path FROM backups WHERE device_id=? ORDER BY id DESC", (device_id,))
        drop = list(rows[max(1, keep):])
        if days > 0:
            from datetime import datetime as _dt, timedelta as _td
            cutoff = (_dt.now() - _td(days=days)).strftime("%Y-%m-%d %H:%M:%S")
            drop += [r for r in rows[1:max(1, keep)] if r["created"] < cutoff]
        ids = list({r["id"]: r for r in drop})
        for i in ids:
            self._exec("DELETE FROM backups WHERE id=?", (i,))
        return [r["path"] for r in drop if r["path"]]

    # ---- audit ---------------------------------------------------------
    def audit(self, user: str, action: str, detail: str = "") -> None:
        self._exec("INSERT INTO audit(ts,user,action,detail) VALUES(?,?,?,?)", (now(), user, action, detail))

    def list_audit(self, limit: int = 300) -> list[dict]:
        return self._query("SELECT * FROM audit ORDER BY id DESC LIMIT ?", (limit,))
