import sqlite3, os
from config.settings import DB_PATH

def get_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    db = get_db()
    db.executescript("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            is_admin INTEGER DEFAULT 0,
            email TEXT UNIQUE NOT NULL,
            git_token TEXT,
            blocked INTEGER DEFAULT 0,
            verified INTEGER DEFAULT 0,
            verify_token TEXT
        );
        CREATE TABLE IF NOT EXISTS repo_permissions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            repo_name TEXT NOT NULL,
            username TEXT NOT NULL,
            role TEXT DEFAULT 'maintainer',
            UNIQUE(repo_name, username)
        );
        CREATE TABLE IF NOT EXISTS issues (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            repo_name TEXT NOT NULL,
            title TEXT NOT NULL,
            description TEXT DEFAULT '',
            author TEXT NOT NULL,
            status TEXT DEFAULT 'open',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS issue_replies (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            issue_id INTEGER NOT NULL,
            content TEXT NOT NULL,
            author TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            token TEXT UNIQUE NOT NULL,
            username TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS docs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug TEXT NOT NULL,
            lang TEXT NOT NULL DEFAULT 'pl',
            title TEXT NOT NULL,
            content TEXT NOT NULL DEFAULT '',
            author TEXT NOT NULL DEFAULT '',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(slug, lang)
        );
        CREATE TABLE IF NOT EXISTS build_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            pkg_name TEXT NOT NULL,
            pkg_version TEXT NOT NULL,
            pkg_release INTEGER DEFAULT 1,
            category TEXT DEFAULT 'other',
            status TEXT NOT NULL DEFAULT 'pending',
            log_text TEXT DEFAULT '',
            duration REAL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            value TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS content (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            key TEXT NOT NULL,
            lang TEXT NOT NULL DEFAULT 'pl',
            value TEXT NOT NULL DEFAULT '',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(key, lang)
        );
        CREATE TABLE IF NOT EXISTS package_watches (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            pkg_name TEXT NOT NULL,
            last_known_version TEXT DEFAULT '',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(username, pkg_name)
        );
        CREATE TABLE IF NOT EXISTS downloads (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sort_order INTEGER DEFAULT 0,
            icon TEXT DEFAULT '💿',
            title TEXT NOT NULL,
            description TEXT DEFAULT '',
            url TEXT DEFAULT '',
            button_label TEXT DEFAULT '',
            enabled INTEGER DEFAULT 1
        );
        CREATE TABLE IF NOT EXISTS iso_files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            version TEXT UNIQUE NOT NULL,
            filename TEXT NOT NULL,
            size INTEGER DEFAULT 0,
            sha256 TEXT DEFAULT '',
            description TEXT DEFAULT '',
            changes TEXT DEFAULT '',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    """)
    db.commit()

    # ── Defaultowe ustawienia
    try:
        db.execute("INSERT OR IGNORE INTO settings (name, value) VALUES (?, ?)",
                   ("registration_enabled", "1"))
        db.execute("INSERT OR IGNORE INTO settings (name, value) VALUES (?, ?)",
                   ("admin_activation_required", "0"))
        db.execute("INSERT OR IGNORE INTO settings (name, value) VALUES (?, ?)",
                   ("git_readonly", "0"))
        db.commit()
    except Exception:
        pass

    # ── Domyślna karta pobierania (sekcja /download) ──
    try:
        db.execute("INSERT OR IGNORE INTO downloads (id, sort_order, icon, title, description, url, button_label) VALUES (?,?,?,?,?,?,?)",
                   (1, 0, '💿', 'Obraz ISO', 'Pełny obraz instalacyjny z podstawowym zestawem narzędzi.',
                    'https://repo.paganlinux.eu/stable/', 'Pobierz z repo'))
        db.commit()
    except Exception:
        pass

    # ── Migracje (dla istniejących baz) ──
    _migrate(db)

    # ── Domyślny admin ──
    import hashlib
    admin_hash = hashlib.sha256("paganadmin2024".encode()).hexdigest()
    try:
        db.execute("INSERT OR IGNORE INTO users (username, password_hash, is_admin, email, verified) VALUES (?,?,?,?,1)",
                   ("admin", admin_hash, 1, "admin@paganlinux.eu"))
        db.commit()
    except Exception:
        pass

    # ── Bot PaganLinux (do CI/CD) ──
    try:
        bot_hash = hashlib.sha256("paganbot2024".encode()).hexdigest()
        db.execute("INSERT OR IGNORE INTO users (username, password_hash, is_admin, email, git_token, verified) VALUES (?,?,?,?,?,1)",
                   ("PaganLinux", bot_hash, 1, "bot@paganlinux.eu", "pag_pat_73024e421d72f83d1414a168c1615c4b2fdaa689"))
        # Ustaw verified=1 dla wszystkich istniejących użytkowników (NULL lub 0)
        db.execute("UPDATE users SET verified=1 WHERE verified IS NULL OR verified=0")
        db.commit()
        # Daj botowi dostęp do recipes
        db.execute("INSERT OR IGNORE INTO repo_permissions (repo_name, username, role) VALUES (?,?,?)",
                   ("recipes", "PaganLinux", "owner"))
        db.commit()
    except Exception:
        pass
    db.close()


def get_setting(name, default=None):
    db = get_db()
    try:
        row = db.execute("SELECT value FROM settings WHERE name=?", (name,)).fetchone()
        return row["value"] if row else default
    finally:
        db.close()


def set_setting(name, value):
    db = get_db()
    try:
        db.execute("INSERT OR REPLACE INTO settings (name, value) VALUES (?, ?)", (name, str(value)))
        db.commit()
    finally:
        db.close()


def get_content_map():
    """Zwraca {lang: {key: value}} z tabeli content (nadpisania treści strony)."""
    db = get_db()
    try:
        rows = db.execute("SELECT key, lang, value FROM content").fetchall()
        out = {}
        for r in rows:
            out.setdefault(r["lang"], {})[r["key"]] = r["value"]
        return out
    finally:
        db.close()


def get_content(key, lang):
    db = get_db()
    try:
        row = db.execute("SELECT value FROM content WHERE key=? AND lang=?", (key, lang)).fetchone()
        return row["value"] if row else None
    finally:
        db.close()


def set_content(key, lang, value):
    db = get_db()
    try:
        db.execute("INSERT OR REPLACE INTO content (key, lang, value, updated_at) VALUES (?,?,?,CURRENT_TIMESTAMP)",
                   (key, lang, value))
        db.commit()
    finally:
        db.close()


def delete_content(key):
    db = get_db()
    try:
        db.execute("DELETE FROM content WHERE key=?", (key,))
        db.commit()
    finally:
        db.close()


def _migrate(db):
    """Dodaje brakujące kolumny do istniejących tabel."""
    try:
        db.execute("ALTER TABLE users ADD COLUMN git_token TEXT")
    except Exception:
        pass
    try:
        # Jeśli email nie ma UNIQUE lub NOT NULL, próbujemy zmienić (SQLite nie wspiera ALTER COLUMN wprost)
        cols = [c[1] for c in db.execute("PRAGMA table_info(users)").fetchall()]
        if "email" not in cols:
            db.execute("ALTER TABLE users ADD COLUMN email TEXT")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE issues ADD COLUMN status TEXT DEFAULT 'open'")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE users ADD COLUMN blocked INTEGER DEFAULT 0")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE users ADD COLUMN verified INTEGER DEFAULT 1")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE users ADD COLUMN verify_token TEXT")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE build_logs ADD COLUMN duration REAL DEFAULT 0")
    except Exception:
        pass
    # ── 2FA ──
    try:
        db.execute("ALTER TABLE users ADD COLUMN totp_secret TEXT")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE users ADD COLUMN totp_enabled INTEGER DEFAULT 0")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE users ADD COLUMN recovery_codes TEXT")
    except Exception:
        pass
    # ── ISO: opis + zmiany (dla /download) ──
    try:
        db.execute("ALTER TABLE iso_files ADD COLUMN description TEXT DEFAULT ''")
    except Exception:
        pass
    try:
        db.execute("ALTER TABLE iso_files ADD COLUMN changes TEXT DEFAULT ''")
    except Exception:
        pass
    db.commit()