import json
import logging
import queue
import threading
import time
from contextlib import contextmanager
from typing import Any, Dict, List, Optional
import pymysql
import pymysql.cursors

from config import (
    DB_HOST,
    DB_NAME,
    DB_PASSWORD,
    DB_POOL_SIZE,
    DB_PORT,
    DB_USER,
)
from crypto import decrypt_value, encrypt_value, is_encrypted

logger = logging.getLogger("Database")

# Sensitive setting keys that must always be encrypted in app_settings table
SENSITIVE_SETTINGS = {"mozisarok_token", "jwt_token", "api_token", "secret_key"}


class MySQLConnectionPool:
    """Thread-safe connection pool with a fixed number of connections (default: 4)."""

    def __init__(
        self,
        host: str = DB_HOST,
        port: int = DB_PORT,
        user: str = DB_USER,
        password: str = DB_PASSWORD,
        database: str = DB_NAME,
        max_connections: int = DB_POOL_SIZE,
    ):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.max_connections = max_connections
        self._pool: queue.Queue = queue.Queue(maxsize=max_connections)
        self._lock = threading.Lock()
        self._created_connections = 0

        # Pre-populate pool
        for _ in range(max_connections):
            conn = self._create_new_connection()
            if conn:
                self._pool.put(conn)

    def _create_new_connection(self) -> Optional[pymysql.Connection]:
        try:
            conn = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset="utf8mb4",
                cursorclass=pymysql.cursors.DictCursor,
                autocommit=True,
                connect_timeout=15,
                read_timeout=30,
                write_timeout=30,
            )
            with self._lock:
                self._created_connections += 1
            return conn
        except Exception as e:
            logger.error(f"Failed to create MySQL connection: {e}")
            return None

    def get_connection(self, timeout: float = 20.0) -> pymysql.Connection:
        try:
            conn = self._pool.get(block=True, timeout=timeout)
            try:
                conn.ping()
            except Exception:
                logger.warning("Connection ping failed, recreating connection.")
                conn = self._create_new_connection()
                if not conn:
                    raise RuntimeError("Failed to reconnect to MySQL database.")
            return conn
        except queue.Empty:
            # Fallback if pool is exhausted: try to create a transient connection
            conn = self._create_new_connection()
            if conn:
                return conn
            raise TimeoutError("Connection pool exhausted and cannot allocate connection.")

    def release_connection(self, conn: pymysql.Connection):
        if conn and conn.open:
            try:
                self._pool.put_nowait(conn)
            except queue.Full:
                try:
                    conn.close()
                except Exception:
                    pass
        else:
            # Replace dead connection with a fresh one
            new_conn = self._create_new_connection()
            if new_conn:
                try:
                    self._pool.put_nowait(new_conn)
                except queue.Full:
                    new_conn.close()

    @contextmanager
    def cursor(self):
        conn = self.get_connection()
        try:
            with conn.cursor() as cur:
                yield cur
        finally:
            self.release_connection(conn)


# Global Pool instance
db_pool = MySQLConnectionPool()


def init_db():
    """Initializes tables in the MySQL database if not already present."""
    schema = """
    CREATE TABLE IF NOT EXISTS accounts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        provider VARCHAR(50) NOT NULL,
        name VARCHAR(100) NOT NULL,
        username VARCHAR(100),
        password VARCHAR(255),
        token TEXT,
        cookies TEXT,
        extra_data JSON,
        is_active TINYINT DEFAULT 1,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_provider_name (provider, name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE IF NOT EXISTS media_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tmdb_id INT NOT NULL,
        media_type ENUM('movie', 'tv') NOT NULL,
        title VARCHAR(255) NOT NULL,
        original_title VARCHAR(255),
        overview TEXT,
        release_date VARCHAR(50),
        poster_path VARCHAR(255),
        backdrop_path VARCHAR(255),
        vote_average FLOAT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_tmdb_type (tmdb_id, media_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE IF NOT EXISTS videos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        media_id INT NOT NULL,
        account_id INT NULL,
        file_path VARCHAR(500) NOT NULL,
        file_name VARCHAR(255) NOT NULL,
        file_size BIGINT DEFAULT 0,
        provider VARCHAR(50) NOT NULL,
        provider_video_id VARCHAR(100),
        direct_url TEXT,
        embed_url TEXT,
        season INT DEFAULT NULL,
        episode INT DEFAULT NULL,
        language VARCHAR(50) DEFAULT 'Magyar',
        subtitle VARCHAR(50) DEFAULT NULL,
        quality VARCHAR(50) DEFAULT '1080p',
        mozisarok_submitted TINYINT DEFAULT 0,
        mozisarok_verified TINYINT DEFAULT 0,
        status VARCHAR(100) DEFAULT 'várakozik',
        error_message TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        completed_at DATETIME NULL,
        FOREIGN KEY (media_id) REFERENCES media_items(id) ON DELETE CASCADE,
        FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE IF NOT EXISTS app_settings (
        `key` VARCHAR(100) PRIMARY KEY,
        `value` TEXT,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    CREATE TABLE IF NOT EXISTS admin_users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        salt VARCHAR(64) NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """
    with db_pool.cursor() as cur:
        for statement in schema.split(";"):
            statement = statement.strip()
            if statement:
                cur.execute(statement)

        # Migration: ensure status column is VARCHAR(100) supporting Hungarian progress descriptions
        try:
            cur.execute("ALTER TABLE videos MODIFY COLUMN status VARCHAR(100) DEFAULT 'várakozik'")
        except Exception:
            pass

        # Migration: ensure accounts.password is TEXT to support long encrypted ciphertext
        try:
            cur.execute("ALTER TABLE accounts MODIFY COLUMN password TEXT")
        except Exception:
            pass

    # Migrate existing unencrypted passwords, tokens, and settings
    migrate_encrypt_existing_credentials()


def migrate_encrypt_existing_credentials() -> Dict[str, int]:
    """
    Scans the database for unencrypted passwords, tokens, cookies, and sensitive settings,
    and encrypts them in place using the unlock key from config.
    Safe and idempotent (does not double-encrypt).
    """
    accounts_encrypted = 0
    settings_encrypted = 0

    try:
        with db_pool.cursor() as cur:
            # 1. Accounts: check password, token, cookies
            cur.execute("SELECT id, password, token, cookies FROM accounts")
            accounts = cur.fetchall()
            for acc in accounts:
                acc_id = acc["id"]
                pwd = acc.get("password")
                tok = acc.get("token")
                ck = acc.get("cookies")

                updates = []
                params = []

                if pwd and not is_encrypted(pwd):
                    updates.append("password = %s")
                    params.append(encrypt_value(pwd))

                if tok and not is_encrypted(tok):
                    updates.append("token = %s")
                    params.append(encrypt_value(tok))

                if ck and not is_encrypted(ck):
                    updates.append("cookies = %s")
                    params.append(encrypt_value(ck))

                if updates:
                    params.append(acc_id)
                    cur.execute(
                        f"UPDATE accounts SET {', '.join(updates)} WHERE id = %s",
                        tuple(params),
                    )
                    accounts_encrypted += 1

            # 2. App Settings: check sensitive keys
            cur.execute("SELECT `key`, `value` FROM app_settings")
            settings = cur.fetchall()
            for s in settings:
                k = s["key"]
                v = s.get("value")
                if not v:
                    continue
                is_sensitive = (
                    k in SENSITIVE_SETTINGS
                    or k.endswith("_token")
                    or k.endswith("_password")
                    or k.endswith("_secret")
                )
                if is_sensitive and not is_encrypted(v):
                    cur.execute(
                        "UPDATE app_settings SET `value` = %s WHERE `key` = %s",
                        (encrypt_value(v), k),
                    )
                    settings_encrypted += 1

        if accounts_encrypted > 0 or settings_encrypted > 0:
            logger.info(
                f"✅ Adatbázis titkosítási migráció sikeres: {accounts_encrypted} fiók és {settings_encrypted} beállítás letitkosítva."
            )
    except Exception as e:
        logger.error(f"Hiba az adatbázis titkosítási migrációja során: {e}")

    return {"accounts_encrypted": accounts_encrypted, "settings_encrypted": settings_encrypted}


# =====================================================================
# Database Schematic Verification & Diagnostic Module
# =====================================================================

EXPECTED_SCHEMA: Dict[str, Dict[str, Any]] = {
    "accounts": {
        "description": "Feltöltő fiókok (Videa / VK felhasználók, jelszavak és tokenek)",
        "required_columns": [
            "id", "provider", "name", "username", "password", 
            "token", "cookies", "extra_data", "is_active", "created_at", "updated_at"
        ],
        "primary_key": "id",
    },
    "media_items": {
        "description": "TMDb film és sorozat metaadatok és borítóképek gyorsítótára",
        "required_columns": [
            "id", "tmdb_id", "media_type", "title", "original_title",
            "overview", "release_date", "poster_path", "backdrop_path",
            "vote_average", "created_at", "updated_at"
        ],
        "primary_key": "id",
    },
    "videos": {
        "description": "Feltöltött és sorban lévő videók, embed linkek, állapotok",
        "required_columns": [
            "id", "media_id", "account_id", "file_path", "file_name", "file_size",
            "provider", "provider_video_id", "direct_url", "embed_url",
            "season", "episode", "language", "subtitle", "quality",
            "mozisarok_submitted", "mozisarok_verified", "status",
            "error_message", "created_at", "completed_at"
        ],
        "primary_key": "id",
    },
    "app_settings": {
        "description": "Központi alkalmazás beállítások kulcs-érték tára",
        "required_columns": ["key", "value", "updated_at"],
        "primary_key": "key",
    },
    "admin_users": {
        "description": "Adminisztrátori fiók, PBKDF2-HMAC-SHA256 hashek és sók",
        "required_columns": ["id", "username", "password_hash", "salt", "created_at", "updated_at"],
        "primary_key": "id",
    },
}


def check_database_schema() -> Dict[str, Any]:
    """
    Részletes adatbázis séma (schematic) ellenőrzést hajt végre a MySQL adatbázison:
    - Ellenőrzi a kapcsolatot és méri a ping válaszidőt (latencia ms)
    - Lekéri a szerver verziót, karakterkészletet és illesztést (charset/collation)
    - Megvizsgálja az összes elvárt tábla létezését
    - Ellenőrzi az összes kötelező oszlopot és típust táblánként
    - Pontos sorszámot és méretet számol táblánként
    - Részletes állapotjelentést készít táblánként és globálisan.
    """
    t0 = time.time()
    try:
        with db_pool.cursor() as cur:
            cur.execute("SELECT 1")
        latency_ms = round((time.time() - t0) * 1000, 1)
    except Exception as e:
        logger.error(f"Séma ellenőrzési hiba: nem sikerült elérni az adatbázist: {e}")
        return {
            "healthy": False,
            "status_text": f"Nem sikerült csatlakozni az adatbázishoz: {e}",
            "latency_ms": None,
            "checked_at": time.strftime("%Y-%m-%d %H:%M:%S"),
            "server_info": {"host": DB_HOST, "port": DB_PORT, "database": DB_NAME},
            "summary": {
                "total_expected_tables": len(EXPECTED_SCHEMA),
                "total_found_tables": 0,
                "missing_tables": list(EXPECTED_SCHEMA.keys()),
                "total_rows": 0,
                "total_size_kb": 0.0,
            },
            "tables": [],
            "issues": [f"Adatbázis csatlakozási hiba: {e}"],
        }

    issues: List[str] = []
    tables_report: List[Dict[str, Any]] = []

    try:
        with db_pool.cursor() as cur:
            # 1. Server info
            cur.execute("SELECT VERSION() AS ver")
            v_row = cur.fetchone() or {}
            mysql_ver = v_row.get("ver") or v_row.get("VERSION()") or "Ismeretlen"

            cur.execute("SELECT @@character_set_database AS cs, @@collation_database AS col")
            c_row = cur.fetchone() or {}
            charset = c_row.get("cs") or c_row.get("@@character_set_database") or "utf8mb4"
            collation = c_row.get("col") or c_row.get("@@collation_database") or "utf8mb4_unicode_ci"

            # 2. Information schema tables
            cur.execute("""
                SELECT TABLE_NAME, ENGINE, DATA_LENGTH, INDEX_LENGTH 
                FROM information_schema.tables 
                WHERE table_schema = DATABASE()
            """)
            tables_meta = {
                (r.get("TABLE_NAME") or r.get("table_name") or "").lower(): r 
                for r in cur.fetchall()
            }

            # 3. Information schema columns
            cur.execute("""
                SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY 
                FROM information_schema.columns 
                WHERE table_schema = DATABASE()
                ORDER BY TABLE_NAME, ORDINAL_POSITION
            """)
            all_cols = cur.fetchall()

        table_cols_map: Dict[str, List[Dict[str, Any]]] = {}
        for c in all_cols:
            tname = (c.get("TABLE_NAME") or c.get("table_name") or "").lower()
            table_cols_map.setdefault(tname, []).append(c)

        total_rows = 0
        total_size_kb = 0.0
        missing_tables = []

        # 4. Process each expected table
        for t_name, spec in EXPECTED_SCHEMA.items():
            meta = tables_meta.get(t_name)
            cols = table_cols_map.get(t_name, [])

            if not meta:
                missing_tables.append(t_name)
                issues.append(f"Hiányzó tábla az adatbázisban: '{t_name}'!")
                tables_report.append({
                    "table_name": t_name,
                    "description": spec["description"],
                    "exists": False,
                    "status": "missing",
                    "status_label": "Hiányzik",
                    "row_count": 0,
                    "columns_count": 0,
                    "columns_expected": len(spec["required_columns"]),
                    "missing_columns": spec["required_columns"],
                    "present_columns": [],
                    "size_kb": 0.0,
                    "engine": None,
                    "issues": [f"A(z) '{t_name}' tábla még nincs létrehozva az adatbázisban."],
                })
                continue

            # Exact row count
            row_count = 0
            try:
                with db_pool.cursor() as cur:
                    cur.execute(f"SELECT COUNT(*) AS cnt FROM `{t_name}`")
                    rc_row = cur.fetchone()
                    row_count = int(rc_row.get("cnt") or rc_row.get("COUNT(*)") or 0)
            except Exception as e:
                issues.append(f"Hiba a(z) '{t_name}' sorok lekérdezésekor: {e}")

            data_len = int(meta.get("DATA_LENGTH") or meta.get("data_length") or 0)
            idx_len = int(meta.get("INDEX_LENGTH") or meta.get("index_length") or 0)
            size_kb = round((data_len + idx_len) / 1024, 1)

            total_rows += row_count
            total_size_kb += size_kb

            present_col_names = {
                (c.get("COLUMN_NAME") or c.get("column_name") or "").lower(): c 
                for c in cols
            }
            missing_cols = [
                req_col for req_col in spec["required_columns"]
                if req_col.lower() not in present_col_names
            ]

            table_issues: List[str] = []
            if missing_cols:
                table_issues.append(f"Hiányzó oszlopok: {', '.join(missing_cols)}")
                issues.append(f"A(z) '{t_name}' táblából hiányoznak oszlopok: {', '.join(missing_cols)}")

            # Diagnostic checks
            if t_name == "videos":
                status_col = present_col_names.get("status")
                if status_col:
                    ctype = (status_col.get("COLUMN_TYPE") or "").lower()
                    if "varchar" in ctype and "varchar(100)" not in ctype and "text" not in ctype:
                        try:
                            clen = int(ctype.split("(")[1].split(")")[0])
                            if clen < 100:
                                table_issues.append(f"A 'status' oszlop mérete ({ctype}) túl rövid a magyar állapotokhoz (legalább VARCHAR(100) szükséges).")
                        except Exception:
                            pass

            if t_name == "accounts":
                pwd_col = present_col_names.get("password")
                if pwd_col:
                    ctype = (pwd_col.get("COLUMN_TYPE") or "").lower()
                    if "varchar" in ctype and "text" not in ctype:
                        table_issues.append("Az 'accounts.password' TEXT típusúra ajánlott a hosszú titkosított kulcsok miatt.")

            col_list = []
            for c in cols:
                cname = c.get("COLUMN_NAME") or c.get("column_name")
                ctype = c.get("COLUMN_TYPE") or c.get("column_type") or c.get("DATA_TYPE")
                cnull = (c.get("IS_NULLABLE") or "").upper() == "YES"
                ckey = c.get("COLUMN_KEY") or ""
                col_list.append({
                    "name": cname,
                    "type": ctype,
                    "nullable": cnull,
                    "key": ckey,
                    "is_primary": "PRI" in ckey,
                })

            t_status = "ok"
            t_label = "Ép / Rendben"
            if missing_cols:
                t_status = "error"
                t_label = "Hiányos"
            elif table_issues:
                t_status = "warning"
                t_label = "Figyelmeztetés"

            tables_report.append({
                "table_name": t_name,
                "description": spec["description"],
                "exists": True,
                "status": t_status,
                "status_label": t_label,
                "row_count": row_count,
                "columns_count": len(cols),
                "columns_expected": len(spec["required_columns"]),
                "missing_columns": missing_cols,
                "present_columns": col_list,
                "size_kb": size_kb,
                "engine": meta.get("ENGINE") or meta.get("engine") or "InnoDB",
                "issues": table_issues,
            })

        is_healthy = (len(missing_tables) == 0 and all(t["status"] in ("ok", "warning") for t in tables_report))

        if is_healthy:
            status_text = f"Minden séma és táblastruktúra ép és naprakész ({len(tables_report)}/{len(EXPECTED_SCHEMA)} tábla ellenőrizve)."
        else:
            status_text = f"Séma eltérés észlelve! ({len(issues)} probléma található)."

        return {
            "healthy": is_healthy,
            "status_text": status_text,
            "latency_ms": latency_ms,
            "checked_at": time.strftime("%Y-%m-%d %H:%M:%S"),
            "server_info": {
                "version": mysql_ver,
                "host": DB_HOST,
                "port": DB_PORT,
                "database": DB_NAME,
                "user": DB_USER,
                "pool_size": DB_POOL_SIZE,
                "charset": charset,
                "collation": collation,
            },
            "summary": {
                "total_expected_tables": len(EXPECTED_SCHEMA),
                "total_found_tables": len(EXPECTED_SCHEMA) - len(missing_tables),
                "missing_tables": missing_tables,
                "total_rows": total_rows,
                "total_size_kb": round(total_size_kb, 1),
            },
            "tables": tables_report,
            "issues": issues,
        }
    except Exception as e:
        logger.exception(f"Hiba a séma ellenőrzésekor: {e}")
        return {
            "healthy": False,
            "status_text": f"Kivétel a séma vizsgálata közben: {e}",
            "latency_ms": latency_ms,
            "checked_at": time.strftime("%Y-%m-%d %H:%M:%S"),
            "server_info": {"host": DB_HOST, "port": DB_PORT, "database": DB_NAME},
            "summary": {"total_expected_tables": len(EXPECTED_SCHEMA), "total_found_tables": 0},
            "tables": [],
            "issues": [str(e)],
        }


def repair_database_schema() -> Dict[str, Any]:
    """
    Kijavítja az esetleges séma eltéréseket vagy hiányzó táblákat és oszlopokat:
    1. Lefuttatja az init_db()-t (táblák létrehozása, ha hiányoznak)
    2. Ellenőrzi az összes elvárt oszlop meglétét, és a hiányzókat hozzáadja (ALTER TABLE ADD COLUMN)
    3. Lefuttatja az oszlop méret/típus migrációkat (status VARCHAR(100), password TEXT)
    4. Lefuttatja a hitelesítőadat titkosítási migrációt
    5. Visszaadja a friss check_database_schema() állapotot.
    """
    logger.info("Adatbázis séma ellenőrzése és javítása megkezdve...")
    # 1. Init DB tables
    init_db()

    # 2. Check and add any missing columns for existing tables
    try:
        with db_pool.cursor() as cur:
            cur.execute("""
                SELECT TABLE_NAME, COLUMN_NAME 
                FROM information_schema.columns 
                WHERE table_schema = DATABASE()
            """)
            existing_cols = {
                ((r.get("TABLE_NAME") or r.get("table_name") or "").lower(),
                 (r.get("COLUMN_NAME") or r.get("column_name") or "").lower())
                for r in cur.fetchall()
            }

            column_defs = {
                ("accounts", "extra_data"): "ADD COLUMN extra_data JSON NULL",
                ("accounts", "is_active"): "ADD COLUMN is_active TINYINT DEFAULT 1",
                ("media_items", "backdrop_path"): "ADD COLUMN backdrop_path VARCHAR(255) NULL",
                ("media_items", "vote_average"): "ADD COLUMN vote_average FLOAT NULL",
                ("videos", "mozisarok_submitted"): "ADD COLUMN mozisarok_submitted TINYINT DEFAULT 0",
                ("videos", "mozisarok_verified"): "ADD COLUMN mozisarok_verified TINYINT DEFAULT 0",
                ("videos", "status"): "ADD COLUMN status VARCHAR(100) DEFAULT 'várakozik'",
                ("videos", "error_message"): "ADD COLUMN error_message TEXT NULL",
                ("videos", "completed_at"): "ADD COLUMN completed_at DATETIME NULL",
                ("admin_users", "salt"): "ADD COLUMN salt VARCHAR(64) NOT NULL DEFAULT ''",
            }

            for (t_name, col_name), add_clause in column_defs.items():
                if (t_name, col_name) not in existing_cols:
                    try:
                        cur.execute(f"ALTER TABLE `{t_name}` {add_clause}")
                        logger.info(f"Hiányzó oszlop létrehozva: {t_name}.{col_name}")
                    except Exception as e:
                        logger.warning(f"Nem sikerült hozzáadni a(z) {t_name}.{col_name} oszlopot: {e}")

            # 3. Specific migrations
            try:
                cur.execute("ALTER TABLE videos MODIFY COLUMN status VARCHAR(100) DEFAULT 'várakozik'")
            except Exception:
                pass

            try:
                cur.execute("ALTER TABLE accounts MODIFY COLUMN password TEXT")
            except Exception:
                pass

    except Exception as e:
        logger.error(f"Hiba a séma javításakor: {e}")

    # 4. Encrypt credentials if needed
    try:
        migrate_encrypt_existing_credentials()
    except Exception:
        pass

    logger.info("Adatbázis séma javítás befejeződött.")
    return check_database_schema()


# Status mappings (Hungarian text saved to MySQL as requested: "feltöltve", "várakozik", etc.)
STATUS_TO_HUNGARIAN: Dict[str, str] = {
    "queued": "várakozik",
    "waiting": "várakozik",
    "uploading": "feltöltés alatt",
    "uploaded": "feltöltve",
    "converting": "konvertálás alatt",
    "submitting": "beküldés alatt",
    "verifying": "ellenőrzés alatt",
    "completed": "kész",
    "failed": "hiba",
    "cancelled": "megszakítva",
}

HUNGARIAN_TO_STATUS: Dict[str, str] = {
    "várakozik": "queued",
    "feltöltés alatt": "uploading",
    "feltöltve": "uploaded",
    "konvertálás alatt": "converting",
    "beküldés alatt": "submitting",
    "ellenőrzés alatt": "verifying",
    "kész": "completed",
    "hiba": "failed",
    "megszakítva": "cancelled",
}


def to_hungarian_status(status: Optional[str]) -> str:
    if not status:
        return "várakozik"
    s = status.strip()
    return STATUS_TO_HUNGARIAN.get(s.lower(), s)


# --- Account Operations ---
def _decrypt_account_record(acc: Optional[Dict[str, Any]]) -> Optional[Dict[str, Any]]:
    """Helper to decrypt sensitive credentials in an account dictionary."""
    if not acc:
        return acc
    if acc.get("password"):
        acc["password"] = decrypt_value(acc["password"])
    if acc.get("token"):
        acc["token"] = decrypt_value(acc["token"])
    if acc.get("cookies"):
        acc["cookies"] = decrypt_value(acc["cookies"])
    if isinstance(acc.get("extra_data"), str):
        try:
            acc["extra_data"] = json.loads(acc["extra_data"])
        except Exception:
            pass
    return acc


def save_account(
    provider: str,
    name: str,
    username: Optional[str] = None,
    password: Optional[str] = None,
    token: Optional[str] = None,
    cookies: Optional[str] = None,
    extra_data: Optional[Dict[str, Any]] = None,
    is_active: int = 1,
) -> int:
    with db_pool.cursor() as cur:
        extra_json = json.dumps(extra_data) if extra_data is not None else None

        # Encrypt sensitive credentials before saving to MySQL
        enc_password = encrypt_value(password) if password else None
        enc_token = encrypt_value(token) if token else None
        enc_cookies = encrypt_value(cookies) if cookies else None

        sql = """
        INSERT INTO accounts (provider, name, username, password, token, cookies, extra_data, is_active)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            username = VALUES(username),
            password = VALUES(password),
            token = VALUES(token),
            cookies = VALUES(cookies),
            extra_data = VALUES(extra_data),
            is_active = VALUES(is_active)
        """
        cur.execute(
            sql,
            (provider, name, username, enc_password, enc_token, enc_cookies, extra_json, is_active),
        )
        return cur.lastrowid or 0


def get_accounts(provider: Optional[str] = None, active_only: bool = False) -> List[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        conditions = []
        params = []
        if provider:
            conditions.append("provider = %s")
            params.append(provider)
        if active_only:
            conditions.append("is_active = 1")

        where_clause = f"WHERE {' AND '.join(conditions)}" if conditions else ""
        cur.execute(f"SELECT * FROM accounts {where_clause} ORDER BY id ASC", tuple(params))
        accounts = cur.fetchall()
        for acc in accounts:
            _decrypt_account_record(acc)
        return accounts


def get_account_by_id(account_id: int) -> Optional[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        cur.execute("SELECT * FROM accounts WHERE id = %s", (account_id,))
        acc = cur.fetchone()
        if acc:
            _decrypt_account_record(acc)
        return acc


def update_account_cookies(account_id: int, cookies: str):
    enc_cookies = encrypt_value(cookies) if cookies else None
    with db_pool.cursor() as cur:
        cur.execute("UPDATE accounts SET cookies = %s WHERE id = %s", (enc_cookies, account_id))


def update_account_tokens(account_id: int, token: Optional[str] = None, cookies: Optional[str] = None):
    updates = []
    params = []
    if token is not None:
        updates.append("token = %s")
        params.append(encrypt_value(token) if token else None)
    if cookies is not None:
        updates.append("cookies = %s")
        params.append(encrypt_value(cookies) if cookies else None)
    if not updates:
        return
    params.append(account_id)
    with db_pool.cursor() as cur:
        cur.execute(f"UPDATE accounts SET {', '.join(updates)} WHERE id = %s", tuple(params))


def update_account_status(account_id: int, is_active: int):
    with db_pool.cursor() as cur:
        cur.execute("UPDATE accounts SET is_active = %s WHERE id = %s", (is_active, account_id))


def update_account_details(
    account_id: int,
    name: Optional[str] = None,
    username: Optional[str] = None,
    password: Optional[str] = None,
    token: Optional[str] = None,
    extra_data: Optional[Dict[str, Any]] = None,
    is_active: Optional[int] = None,
) -> bool:
    """
    Frissíti a megadott fiók adatait (megnevezés, felhasználónév, jelszó, token, extra_data).
    Ha a jelszó mező üres vagy None, a meglévő jelszó nem változik.
    """
    updates = []
    params = []
    if name is not None and name.strip():
        updates.append("name = %s")
        params.append(name.strip())
    if username is not None:
        updates.append("username = %s")
        params.append(username.strip())
    if password is not None and password.strip():
        updates.append("password = %s")
        params.append(encrypt_value(password.strip()))
    if token is not None:
        updates.append("token = %s")
        params.append(encrypt_value(token.strip()) if token.strip() else None)
    if extra_data is not None:
        updates.append("extra_data = %s")
        params.append(json.dumps(extra_data))
    if is_active is not None:
        updates.append("is_active = %s")
        params.append(int(is_active))

    if not updates:
        return False

    params.append(account_id)
    with db_pool.cursor() as cur:
        sql = f"UPDATE accounts SET {', '.join(updates)} WHERE id = %s"
        cur.execute(sql, tuple(params))
        return True


def delete_account(account_id: int):
    with db_pool.cursor() as cur:
        cur.execute("DELETE FROM accounts WHERE id = %s", (account_id,))


# --- Media Items (Adatlapok) Operations ---
def save_media_item(
    tmdb_id: int,
    media_type: str,
    title: str,
    original_title: Optional[str] = None,
    overview: Optional[str] = None,
    release_date: Optional[str] = None,
    poster_path: Optional[str] = None,
    backdrop_path: Optional[str] = None,
    vote_average: Optional[float] = None,
) -> int:
    with db_pool.cursor() as cur:
        sql = """
        INSERT INTO media_items (tmdb_id, media_type, title, original_title, overview, release_date, poster_path, backdrop_path, vote_average)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            title = VALUES(title),
            original_title = VALUES(original_title),
            overview = VALUES(overview),
            release_date = VALUES(release_date),
            poster_path = VALUES(poster_path),
            backdrop_path = VALUES(backdrop_path),
            vote_average = VALUES(vote_average)
        """
        cur.execute(
            sql,
            (
                tmdb_id,
                media_type,
                title,
                original_title,
                overview,
                release_date,
                poster_path,
                backdrop_path,
                vote_average,
            ),
        )
        cur.execute(
            "SELECT id FROM media_items WHERE tmdb_id = %s AND media_type = %s",
            (tmdb_id, media_type),
        )
        row = cur.fetchone()
        return row["id"] if row else 0


def get_media_item(tmdb_id: int, media_type: str) -> Optional[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        cur.execute(
            "SELECT * FROM media_items WHERE tmdb_id = %s AND media_type = %s",
            (tmdb_id, media_type),
        )
        return cur.fetchone()


def get_media_item_by_id(media_id: int) -> Optional[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        cur.execute("SELECT * FROM media_items WHERE id = %s", (media_id,))
        return cur.fetchone()


def get_all_media_items() -> List[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        cur.execute("SELECT * FROM media_items ORDER BY updated_at DESC")
        return cur.fetchall()


# --- Videos Operations ---
def save_video(
    media_id: int,
    account_id: Optional[int],
    file_path: str,
    file_name: str,
    file_size: int,
    provider: str,
    season: Optional[int] = None,
    episode: Optional[int] = None,
    language: str = "Magyar",
    subtitle: Optional[str] = None,
    quality: str = "1080p",
    status: str = "queued",
) -> int:
    db_status = to_hungarian_status(status)
    with db_pool.cursor() as cur:
        sql = """
        INSERT INTO videos (
            media_id, account_id, file_path, file_name, file_size,
            provider, season, episode, language, subtitle, quality, status
        )
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cur.execute(
            sql,
            (
                media_id,
                account_id,
                file_path,
                file_name,
                file_size,
                provider,
                season,
                episode,
                language,
                subtitle,
                quality,
                db_status,
            ),
        )
        return cur.lastrowid


def update_video_progress(
    video_id: int,
    status: Optional[str] = None,
    provider_video_id: Optional[str] = None,
    direct_url: Optional[str] = None,
    embed_url: Optional[str] = None,
    error_message: Optional[str] = None,
    mozisarok_submitted: Optional[int] = None,
    mozisarok_verified: Optional[int] = None,
    completed: bool = False,
):
    updates = []
    params = []
    if status is not None:
        db_status = to_hungarian_status(status)
        updates.append("status = %s")
        params.append(db_status)
    if provider_video_id is not None:
        updates.append("provider_video_id = %s")
        params.append(provider_video_id)
    if direct_url is not None:
        updates.append("direct_url = %s")
        params.append(direct_url)
    if embed_url is not None:
        updates.append("embed_url = %s")
        params.append(embed_url)
    if error_message is not None:
        updates.append("error_message = %s")
        params.append(error_message)
    if mozisarok_submitted is not None:
        updates.append("mozisarok_submitted = %s")
        params.append(mozisarok_submitted)
    if mozisarok_verified is not None:
        updates.append("mozisarok_verified = %s")
        params.append(mozisarok_verified)
    if completed:
        updates.append("completed_at = CURRENT_TIMESTAMP")

    if not updates:
        return

    params.append(video_id)
    with db_pool.cursor() as cur:
        sql = f"UPDATE videos SET {', '.join(updates)} WHERE id = %s"
        cur.execute(sql, tuple(params))


def get_video_by_id(video_id: int) -> Optional[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        cur.execute("SELECT * FROM videos WHERE id = %s", (video_id,))
        row = cur.fetchone()
        if row and row.get("status"):
            row["status_code"] = HUNGARIAN_TO_STATUS.get(row["status"].lower(), row["status"])
        return row


def get_video_with_details(video_id: int) -> Optional[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        sql = """
        SELECT
            v.*,
            m.title as media_title,
            m.media_type,
            m.tmdb_id,
            m.poster_path,
            a.name as account_name
        FROM videos v
        JOIN media_items m ON v.media_id = m.id
        LEFT JOIN accounts a ON v.account_id = a.id
        WHERE v.id = %s
        """
        cur.execute(sql, (video_id,))
        row = cur.fetchone()
        if row and row.get("status"):
            row["status_code"] = HUNGARIAN_TO_STATUS.get(row["status"].lower(), row["status"])
        return row


def get_all_videos_with_details() -> List[Dict[str, Any]]:
    with db_pool.cursor() as cur:
        sql = """
        SELECT
            v.*,
            m.title as media_title,
            m.media_type,
            m.tmdb_id,
            m.poster_path,
            a.name as account_name
        FROM videos v
        JOIN media_items m ON v.media_id = m.id
        LEFT JOIN accounts a ON v.account_id = a.id
        ORDER BY v.id DESC
        """
        cur.execute(sql)
        rows = cur.fetchall()
        for r in rows:
            if r.get("status"):
                r["status_code"] = HUNGARIAN_TO_STATUS.get(r["status"].lower(), r["status"])
        return rows


def get_saved_series_list() -> List[Dict[str, Any]]:
    """
    Returns list of all TV series present in media_items that have episodes in videos table,
    including episode counts and distinct seasons.
    """
    with db_pool.cursor() as cur:
        sql = """
        SELECT
            m.id as media_id,
            m.tmdb_id,
            m.media_type,
            m.title,
            m.original_title,
            m.poster_path,
            m.release_date,
            COUNT(v.id) as total_episodes,
            COUNT(CASE WHEN v.mozisarok_submitted = 1 THEN 1 END) as submitted_episodes,
            COUNT(CASE WHEN v.mozisarok_verified = 1 THEN 1 END) as verified_episodes,
            GROUP_CONCAT(DISTINCT v.season ORDER BY v.season ASC SEPARATOR ', ') as seasons_list
        FROM media_items m
        JOIN videos v ON v.media_id = m.id
        WHERE m.media_type = 'tv'
        GROUP BY m.id, m.tmdb_id, m.media_type, m.title, m.original_title, m.poster_path, m.release_date
        ORDER BY m.title ASC
        """
        cur.execute(sql)
        rows = cur.fetchall()
        for r in rows:
            seasons_str = r.get("seasons_list") or ""
            seasons_arr = []
            for s in seasons_str.split(","):
                s_strip = s.strip()
                if s_strip.isdigit():
                    seasons_arr.append(int(s_strip))
            r["seasons"] = seasons_arr
        return rows


def get_series_episodes_from_db(tmdb_id: int, season: Optional[int] = None) -> List[Dict[str, Any]]:
    """
    Returns all video records for a specific TV series by tmdb_id (and optional season),
    ordered by season ASC, episode ASC, id DESC.
    """
    with db_pool.cursor() as cur:
        conditions = ["m.tmdb_id = %s", "m.media_type = 'tv'"]
        params = [tmdb_id]
        if season is not None:
            conditions.append("v.season = %s")
            params.append(season)

        where_clause = " AND ".join(conditions)
        sql = f"""
        SELECT
            v.*,
            m.title as media_title,
            m.media_type,
            m.tmdb_id,
            m.poster_path,
            a.name as account_name
        FROM videos v
        JOIN media_items m ON v.media_id = m.id
        LEFT JOIN accounts a ON v.account_id = a.id
        WHERE {where_clause}
        ORDER BY v.season ASC, v.episode ASC, v.id DESC
        """
        cur.execute(sql, tuple(params))
        rows = cur.fetchall()
        for r in rows:
            if r.get("status"):
                r["status_code"] = HUNGARIAN_TO_STATUS.get(r["status"].lower(), r["status"])
        return rows


def delete_video(video_id: int):
    with db_pool.cursor() as cur:
        cur.execute("DELETE FROM videos WHERE id = %s", (video_id,))


def count_videa_converting_videos() -> int:
    """
    Returns the count of Videa videos currently converting / processing in the MySQL database.
    Statuses considered: 'konvertálás alatt', 'feltöltve'.
    """
    try:
        with db_pool.cursor() as cur:
            cur.execute(
                """
                SELECT COUNT(*) AS cnt 
                FROM videos 
                WHERE provider = 'videa' 
                  AND status IN ('konvertálás alatt', 'feltöltve')
                """
            )
            row = cur.fetchone()
            return row["cnt"] if row else 0
    except Exception as e:
        logger.error(f"Error counting converting Videa videos in DB: {e}")
        return 0


def count_videa_uploading_videos() -> int:
    """
    Returns the count of Videa videos currently uploading in the MySQL database.
    Status considered: 'feltöltés alatt'.
    """
    try:
        with db_pool.cursor() as cur:
            cur.execute(
                """
                SELECT COUNT(*) AS cnt 
                FROM videos 
                WHERE provider = 'videa' 
                  AND status = 'feltöltés alatt'
                """
            )
            row = cur.fetchone()
            return row["cnt"] if row else 0
    except Exception as e:
        logger.error(f"Error counting uploading Videa videos in DB: {e}")
        return 0


# --- App Settings Operations ---
def get_setting(key: str, default: Optional[str] = None) -> Optional[str]:
    with db_pool.cursor() as cur:
        cur.execute("SELECT `value` FROM app_settings WHERE `key` = %s", (key,))
        row = cur.fetchone()
        if not row:
            return default
        val = row["value"]
        is_sensitive = (
            key in SENSITIVE_SETTINGS
            or key.endswith("_token")
            or key.endswith("_password")
            or key.endswith("_secret")
        )
        if is_sensitive and val:
            return decrypt_value(val)
        return val


def set_setting(key: str, value: str):
    val_to_save = value
    is_sensitive = (
        key in SENSITIVE_SETTINGS
        or key.endswith("_token")
        or key.endswith("_password")
        or key.endswith("_secret")
    )
    if is_sensitive and value:
        val_to_save = encrypt_value(value)

    with db_pool.cursor() as cur:
        cur.execute(
            "INSERT INTO app_settings (`key`, `value`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)",
            (key, val_to_save),
        )


def get_all_settings() -> Dict[str, str]:
    """Lekéri az összes app_settings beállítást dekódolva."""
    with db_pool.cursor() as cur:
        cur.execute("SELECT `key`, `value` FROM app_settings")
        rows = cur.fetchall()
        res = {}
        for r in rows:
            k = r["key"]
            val = r["value"]
            is_sensitive = (
                k in SENSITIVE_SETTINGS
                or k.endswith("_token")
                or k.endswith("_password")
                or k.endswith("_secret")
            )
            if is_sensitive and val:
                res[k] = decrypt_value(val)
            else:
                res[k] = val or ""
        return res


# --- Admin User Authentication Operations ---
def get_admin_user() -> Optional[Dict[str, Any]]:
    """Lekéri a konfigurált admin fiók rekordját."""
    with db_pool.cursor() as cur:
        cur.execute("SELECT id, username, password_hash, salt, created_at, updated_at FROM admin_users LIMIT 1")
        return cur.fetchone()


def has_admin_user() -> bool:
    """Megvizsgálja, hogy létezik-e már mentett admin fiók."""
    with db_pool.cursor() as cur:
        cur.execute("SELECT COUNT(*) AS cnt FROM admin_users")
        row = cur.fetchone()
        return bool(row and row.get("cnt", 0) > 0)


def set_admin_user(username: str, password_hash: str, salt: str):
    """Ment vagy frissít egy adminisztrátori fiókot."""
    with db_pool.cursor() as cur:
        cur.execute("SELECT id FROM admin_users LIMIT 1")
        existing = cur.fetchone()
        if existing:
            cur.execute(
                "UPDATE admin_users SET username = %s, password_hash = %s, salt = %s WHERE id = %s",
                (username, password_hash, salt, existing["id"]),
            )
        else:
            cur.execute(
                "INSERT INTO admin_users (username, password_hash, salt) VALUES (%s, %s, %s)",
                (username, password_hash, salt),
            )