"""SQLite bilan ishlash: ulanish, sxema/migratsiya, zaxira nusxa.

- Har bir ulanish ishlatilgandan keyin YOPILADI (avval faqat commit qilinardi).
- WAL rejimi: bot va Web Panel (ikki alohida jarayon) bir vaqtda yozganda
  "database is locked" xatosi chiqish ehtimolini keskin kamaytiradi.
- Zaxira nusxa sqlite'ning o'z backup API'si bilan olinadi - fayl yozilayotgan
  paytda ham buzilmagan nusxa chiqadi.
"""
import glob
import logging
import os
import secrets
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timedelta

import config

logger = logging.getLogger("attendance_bot.db")


@contextmanager
def get_db():
    conn = sqlite3.connect(config.DB_PATH, timeout=15)
    try:
        conn.execute("PRAGMA foreign_keys = ON")
        conn.execute("PRAGMA busy_timeout = 15000")
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def _add_column(cursor, table: str, coldef: str):
    try:
        cursor.execute(f"ALTER TABLE {table} ADD COLUMN {coldef}")
    except sqlite3.OperationalError:
        pass  # ustun allaqachon mavjud


def init_db(admin_ids=None):
    admin_ids = admin_ids if admin_ids is not None else config.ADMIN_IDS
    os.makedirs(os.path.dirname(config.DB_PATH) or ".", exist_ok=True)
    with get_db() as conn:
        c = conn.cursor()
        try:
            c.execute("PRAGMA journal_mode = WAL")
        except sqlite3.OperationalError as e:
            logger.warning(f"WAL rejimini yoqib bo'lmadi: {e}")

        c.execute('''
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY,
                full_name TEXT,
                is_approved INTEGER DEFAULT 0,
                monthly_salary REAL DEFAULT 0.0,
                norm_days INTEGER DEFAULT 26,
                work_start_time TEXT DEFAULT NULL,
                work_end_time TEXT DEFAULT NULL,
                auto_checkout_enabled INTEGER DEFAULT 0,
                overtime_eligible INTEGER DEFAULT 0
            )
        ''')
        c.execute('''
            CREATE TABLE IF NOT EXISTS attendance (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                date TEXT,
                check_in_time TEXT,
                check_out_time TEXT,
                lateness_minutes INTEGER DEFAULT 0,
                lateness_reason TEXT DEFAULT '',
                break_start TEXT DEFAULT NULL,
                break_end TEXT DEFAULT NULL,
                break_minutes INTEGER DEFAULT 0,
                work_hours REAL DEFAULT 0,
                paid_hours REAL DEFAULT 0,
                checkin_source TEXT DEFAULT 'self',
                checkout_source TEXT DEFAULT 'self',
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            )
        ''')
        c.execute('''
            CREATE TABLE IF NOT EXISTS advances (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                amount REAL DEFAULT 0.0,
                date TEXT,
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            )
        ''')
        c.execute('''
            CREATE TABLE IF NOT EXISTS work_schedule (
                user_id INTEGER,
                date TEXT,
                PRIMARY KEY (user_id, date),
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            )
        ''')
        # --- Yangi jadvallar ---
        c.execute('''
            CREATE TABLE IF NOT EXISTS absences (
                user_id INTEGER,
                date TEXT,
                type TEXT NOT NULL,
                note TEXT DEFAULT '',
                created_by INTEGER,
                created_at TEXT,
                PRIMARY KEY (user_id, date),
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            )
        ''')
        c.execute('''
            CREATE TABLE IF NOT EXISTS adjustments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                date TEXT,
                amount REAL NOT NULL,
                note TEXT DEFAULT '',
                created_by INTEGER,
                created_at TEXT,
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            )
        ''')
        c.execute('''
            CREATE TABLE IF NOT EXISTS audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                ts TEXT,
                actor_id INTEGER,
                action TEXT,
                target_user_id INTEGER,
                details TEXT
            )
        ''')
        c.execute("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)")
        # Ishxona kalendari: is_off=1 - dam olish/bayram (qizil), is_off=0 - ko'chirilgan ish kuni
        c.execute("CREATE TABLE IF NOT EXISTS holidays (date TEXT PRIMARY KEY, name TEXT DEFAULT '', "
                  "is_off INTEGER DEFAULT 1)")
        # Web sayt: bir martalik kirish havolalari va brauzer sessiyalari (faqat xeshlari saqlanadi)
        c.execute("CREATE TABLE IF NOT EXISTS login_tokens (token_hash TEXT PRIMARY KEY, admin_id INTEGER, "
                  "expires_at INTEGER, used INTEGER DEFAULT 0)")
        c.execute("CREATE TABLE IF NOT EXISTS sessions (sid_hash TEXT PRIMARY KEY, admin_id INTEGER, csrf TEXT, "
                  "created_at INTEGER, expires_at INTEGER, last_seen INTEGER, user_agent TEXT)")

        for table, col in [
            ("users", "is_approved INTEGER DEFAULT 0"),
            ("users", "monthly_salary REAL DEFAULT 0.0"),
            ("users", "norm_days INTEGER DEFAULT 26"),
            ("users", "work_start_time TEXT DEFAULT NULL"),
            ("users", "work_end_time TEXT DEFAULT NULL"),
            ("users", "auto_checkout_enabled INTEGER DEFAULT 0"),
            ("users", "overtime_eligible INTEGER DEFAULT 0"),
            ("users", "is_active INTEGER DEFAULT 1"),
            ("users", "daily_hours REAL DEFAULT NULL"),
            # Ish rejimi: Du..Ya uchun 7 belgi (1 - ish kuni). Standart 6 kunlik (yakshanba dam).
            ("users", "work_weekdays TEXT DEFAULT '1111110'"),
            ("attendance", "lateness_reason TEXT DEFAULT ''"),
            ("attendance", "break_start TEXT DEFAULT NULL"),
            ("attendance", "break_end TEXT DEFAULT NULL"),
            ("attendance", "break_minutes INTEGER DEFAULT 0"),
            ("attendance", "paid_hours REAL DEFAULT 0"),
            ("attendance", "checkin_source TEXT DEFAULT 'self'"),
            ("attendance", "checkout_source TEXT DEFAULT 'self'"),
            ("attendance", "break_count INTEGER DEFAULT 0"),
            ("attendance", "checkin_lat REAL DEFAULT NULL"),
            ("attendance", "checkin_lon REAL DEFAULT NULL"),
            ("attendance", "checkin_accuracy REAL DEFAULT NULL"),
            ("attendance", "location_flag TEXT DEFAULT NULL"),
            ("advances", "note TEXT DEFAULT ''"),
            ("advances", "created_by INTEGER DEFAULT NULL"),
        ]:
            _add_column(c, table, col)

        c.execute(
            "UPDATE attendance SET paid_hours = work_hours "
            "WHERE (paid_hours IS NULL OR paid_hours = 0) AND work_hours > 0"
        )
        c.execute("UPDATE users SET is_active = 1 WHERE is_active IS NULL")
        c.execute("UPDATE users SET work_weekdays = '1111110' WHERE work_weekdays IS NULL OR work_weekdays = ''")
        # Avtomatik norma shu oydan boshlab qo'llanadi - o'tgan (to'langan) oylar hisobi o'zgarmasin
        c.execute("INSERT OR IGNORE INTO settings (key, value) VALUES ('auto_norm_since', ?)",
                  (config.get_now().strftime("%Y-%m"),))
        try:
            c.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_attendance_user_date ON attendance(user_id, date)")
        except sqlite3.IntegrityError:
            logger.warning("attendance jadvalida dublikat (user_id, date) yozuvlar bor - UNIQUE indeks yaratilmadi.")
        c.execute("CREATE INDEX IF NOT EXISTS idx_advances_user_date ON advances(user_id, date)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_adjust_user_date ON adjustments(user_id, date)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_audit_target ON audit_log(target_user_id, ts)")

        for admin_id in admin_ids:
            c.execute(
                "INSERT OR IGNORE INTO users (user_id, full_name, is_approved, monthly_salary, norm_days) "
                "VALUES (?, 'Admin', 1, 0.0, 26)", (admin_id,)
            )
            c.execute("UPDATE users SET is_approved = 1, is_active = 1 WHERE user_id = ?", (admin_id,))


# ================= SOZLAMALAR (settings) =================
def get_setting(key: str, default=None):
    with get_db() as conn:
        row = conn.execute("SELECT value FROM settings WHERE key = ?", (key,)).fetchone()
    return row[0] if row else default


def set_setting(key: str, value: str):
    with get_db() as conn:
        conn.execute(
            "INSERT INTO settings (key, value) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET value = excluded.value", (key, value)
        )


def get_or_create_secret(key: str = "webapp_secret") -> str:
    val = get_setting(key)
    if not val:
        val = secrets.token_hex(32)
        set_setting(key, val)
    return val


# ================= ZAXIRA NUSXA =================
def backup_db(dest_path: str | None = None) -> str:
    """Bazaning izchil (buzilmagan) nusxasini oladi va fayl yo'lini qaytaradi."""
    os.makedirs(config.BACKUP_DIR, exist_ok=True)
    if not dest_path:
        stamp = config.get_now().strftime("%Y-%m-%d_%H%M")
        dest_path = os.path.join(config.BACKUP_DIR, f"attendance_{stamp}.db")
    src = sqlite3.connect(config.DB_PATH, timeout=30)
    dst = sqlite3.connect(dest_path)
    try:
        src.backup(dst)
    finally:
        dst.close()
        src.close()
    return dest_path


def prune_backups(keep_days: int | None = None) -> int:
    """keep_days kundan eski zaxira fayllarini o'chiradi. O'chirilganlar sonini qaytaradi."""
    keep_days = keep_days if keep_days is not None else config.BACKUP_KEEP_DAYS
    cutoff = datetime.now() - timedelta(days=keep_days)
    removed = 0
    for path in glob.glob(os.path.join(config.BACKUP_DIR, "attendance_*.db")):
        try:
            if datetime.fromtimestamp(os.path.getmtime(path)) < cutoff:
                os.remove(path)
                removed += 1
        except OSError:
            pass
    return removed
