# -*- coding: utf-8 -*-
# resources/lib/database/metadata_db.py
# Centrální SQLite DB pro plugin.video.mmirousek_v3
# Autor: mmirousek + Grok 2025

import os
import sqlite3
import json
import time
from datetime import datetime, timedelta
from typing import List, Optional, Dict, Any

try:
    import xbmc
    import xbmcvfs
    import xbmcaddon
    import xbmcgui
    ADDON = xbmcaddon.Addon('plugin.video.mmirousek_v3')
    PROFILE = xbmcvfs.translatePath(ADDON.getAddonInfo('profile'))
    if not xbmcvfs.exists(PROFILE):
        xbmcvfs.mkdirs(PROFILE)
    DB_PATH = os.path.join(PROFILE, 'metadata.db')
except ImportError:
    # Fallback pro běh mimo Kodi (CLI, testy)
    ADDON = None
    PROFILE = os.path.join(os.path.expanduser('~'), '.kodi_mmirousek_v3')
    os.makedirs(PROFILE, exist_ok=True)
    DB_PATH = os.path.join(PROFILE, 'metadata.db')

from resources.lib.utils.log_utils import get_logger

log = get_logger()

class MetadataDB:
    def __init__(self):
        self.conn = None
        self._connect()
        self._create_tables()
        self._create_indexes()

    def _connect(self):
        try:
            if self.conn:
                try:
                    self.conn.close()
                except Exception:
                    pass
            self.conn = sqlite3.connect(DB_PATH, timeout=30.0, check_same_thread=False)
            if self.conn:
                self.conn.execute("PRAGMA foreign_keys = ON")
                self.conn.execute("PRAGMA journal_mode = WAL")
                self.conn.execute("PRAGMA synchronous = NORMAL")
        except Exception as e:
            log.error(f"[MetadataDB] Connect error: {e}")

    def reopen(self):
        """Znovu otevře spojení s databází (např. po CloudSync)"""
        log.info("[MetadataDB] Reopening database connection")
        self._connect()

    def _create_tables(self):
        try:
            if not self.conn:
                return

            # Krok 1: Hlavní tabulka 'media' (Sjednocená V3 struktura)
            sql = """
            CREATE TABLE IF NOT EXISTS media (
                tmdb_id INTEGER PRIMARY KEY,
                title_cs TEXT,
                title_en TEXT,
                title_original TEXT,
                year INTEGER,
                overview_cs TEXT,
                overview_en TEXT,
                poster_path TEXT,
                backdrop_path TEXT,
                vote_average REAL,
                release_date TEXT,
                runtime INTEGER,
                genres TEXT,
                is_series BOOLEAN DEFAULT 0,
                season_count INTEGER DEFAULT 0,
                last_updated TEXT DEFAULT CURRENT_TIMESTAMP,
                last_accessed TEXT DEFAULT CURRENT_TIMESTAMP,
                trakt_last_watched_at TEXT,
                trakt_full_data TEXT,
                trakt_is_fully_watched INTEGER DEFAULT 0,
                trakt_next_episode_id TEXT DEFAULT NULL,
                trakt_episodes_aired INTEGER DEFAULT 0,
                trakt_episodes_completed INTEGER DEFAULT 0,
                trakt_next_episode_title TEXT DEFAULT NULL,
                trakt_total_episodes INTEGER DEFAULT 0,
                ws_watchlist_ts REAL DEFAULT NULL,
                media_type TEXT,
                imdb_id TEXT,
                imdb_rating REAL DEFAULT 0.0,
                imdb_votes INTEGER DEFAULT 0,
                metascore TEXT,
                rt_rating TEXT,
                box_office TEXT,
                source_flags INTEGER DEFAULT 0,
                cz_unavailable INTEGER DEFAULT 0,
                tmdb_audit_skip INTEGER DEFAULT 0
            )
            """
            self.conn.execute(sql)

            sql_ep = """
            CREATE TABLE IF NOT EXISTS episodes (
                tv_id INTEGER,
                season_number INTEGER,
                episode_number INTEGER,
                name TEXT,
                air_date TEXT,
                PRIMARY KEY (tv_id, season_number, episode_number),
                FOREIGN KEY(tv_id) REFERENCES media(tmdb_id) ON DELETE CASCADE
            )
            """
            self.conn.execute(sql_ep)

            # --- Tabulky CloudSync ---
            sql_sync_base = """
            CREATE TABLE IF NOT EXISTS cloud_sync_status (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                device_id TEXT,
                last_local_update INTEGER,
                last_remote_update INTEGER,
                last_sync_time INTEGER
            )
            """
            self.conn.execute(sql_sync_base)
            self.conn.execute("INSERT OR IGNORE INTO cloud_sync_status (id, last_local_update, last_remote_update, last_sync_time) VALUES (1, 0, 0, 0)")

            sql_logs = """
            CREATE TABLE IF NOT EXISTS sync_operation_log (
                timestamp INTEGER PRIMARY KEY,
                action TEXT,
                table_name TEXT,
                item_keys TEXT,
                payload TEXT,
                sync_status INTEGER DEFAULT 0
            )
            """
            self.conn.execute(sql_logs)
            
            # --- Fáze 3: Tabulka pro lokální seriály jako JSON BLOB
            sql_series_data = """
            CREATE TABLE IF NOT EXISTS series_data (
                series_name TEXT PRIMARY KEY,
                json_data TEXT,
                last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """
            self.conn.execute(sql_series_data)

            # --- Fáze 5: Offline Cache pro rychlé UI ---
            sql_offline_cache = """
            CREATE TABLE IF NOT EXISTS offline_cache (
                series_name TEXT,
                season_num INTEGER,
                episode_num INTEGER,
                file_path TEXT,
                file_size INTEGER,
                last_checked TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (series_name, season_num, episode_num)
            )
            """
            self.conn.execute(sql_offline_cache)

            # --- Fáze 2: Tabulky pro Playlist Index ---
            sql_playlist_meta = """
            CREATE TABLE IF NOT EXISTS playlist_meta (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                source_path TEXT,
                source_mtime INTEGER,
                build_time TEXT,
                count INTEGER
            )
            """
            self.conn.execute(sql_playlist_meta)

            sql_playlist_index = """
            CREATE TABLE IF NOT EXISTS playlist_index (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title_raw TEXT,
                title_norm TEXT,
                year INTEGER,
                tmdb_id INTEGER,
                media_type TEXT,
                m3u_url TEXT,
                logo TEXT,
                group_title TEXT,
                kodiprop_json TEXT,
                vlcopt_json TEXT,
                poster_path TEXT,
                plot TEXT,
                display_title TEXT,
                confidence REAL
            )
            """
            self.conn.execute(sql_playlist_index)
            
            self.conn.commit()

        except Exception as e:
            log.error(f"[MetadataDB] Create tables error: {e}")
                # Pokud se to nepodaří, nemá smysl pokračovat
            
        # ----------------------------------------------------------------------
        # Krok 2: Inicializace a migrace
        # TOTO JE KLÍČOVÁ SEKCE PRO SNADNÉ PŘIDÁVÁNÍ SLOUPCŮ BEZ ZTRÁTY DAT
        # ----------------------------------------------------------------------
        try:
            if not self.conn: return
            # Vytvoříme pomocnou tabulku pro sledování verze databáze
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS db_version (
                    id INTEGER PRIMARY KEY,
                    version INTEGER NOT NULL
                )
            """)
            
            # Zjistíme aktuální verzi (výchozí verze je 0 pro databáze, kde db_version ještě neexistuje)
            db_version = 0
            try:
                cursor = self.conn.execute("SELECT version FROM db_version WHERE id = 1")
                res = cursor.fetchone()
                if res:
                    db_version = res[0]
            except Exception:
                pass

            # Všechny staré tabulky `media` do verze 8 byly sloučeny do prvního _create_tables. 
            # Je tu však IF blok verze 8, který doplňuje zpětně i nějaké fallbacky (sloupce navíc), takže jím
            # může nově vytvořená databáze (s verzí 0) normálně bezpečně projít - `if "xyz" not in existing_cols` ji ochrání.
            
            # --- Migrace Verze 8 (last_updated, last_accessed, source_flags) ---
            if db_version < 9:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 9: Nové tabulky pro historii a hledání.")
                try:
                    self.conn.execute("""
                    CREATE TABLE IF NOT EXISTS local_history (
                        id                 INTEGER PRIMARY KEY AUTOINCREMENT,
                        tmdb_id            INTEGER,
                        media_type         TEXT NOT NULL,
                        title              TEXT,
                        progress           REAL DEFAULT 0,
                        watched_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        stream_url         TEXT,
                        season_num         INTEGER,
                        episode_num        INTEGER,
                        trakt_payload      TEXT
                    )
                    """)
                    
                    self.conn.execute("""
                    CREATE TABLE IF NOT EXISTS search_history (
                        query              TEXT PRIMARY KEY,
                        timestamp          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                    """)
                    log.info("[MetadataDB Migrace] Tabulky local_history a search_history vytvořeny.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error creating history tables during migration: {e}")
                db_version = 9
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 9)")
                self.conn.commit()

            # --- Migrace Verze 10 (Fáze 3: Tabulka pro data seriálů) ---
            if db_version < 10:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 10: Tabulka series_data pro Fázi 3.")
                try:
                    self.conn.execute("""
                    CREATE TABLE IF NOT EXISTS series_data (
                        series_name TEXT PRIMARY KEY,
                        json_data TEXT,
                        last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                    """)
                    log.info("[MetadataDB Migrace] Tabulka series_data vytvořena.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error creating series_data table: {e}")
                
                db_version = 10
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 10)")
                self.conn.commit()

            # --- Migrace Verze 11 (Chybějící doplňkové sloupce v tabulce media) ---
            if db_version < 11:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 11: Přidání chybějících sloupců pro OMDB.")
                try:
                    cur = self.conn.execute("PRAGMA table_info(media)")
                    columns = [row[1] for row in cur.fetchall()]
                    
                    if 'title_original' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN title_original TEXT")
                    if 'imdb_votes' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN imdb_votes INTEGER DEFAULT 0")
                    if 'metascore' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN metascore TEXT")
                    if 'rt_rating' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN rt_rating TEXT")
                    if 'box_office' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN box_office TEXT")
                        
                    log.info("[MetadataDB Migrace] Sloupce přidány do tabulky media.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error altering media table: {e}")
                
                db_version = 11
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 11)")
                self.conn.commit()

            # --- Migrace Verze 12 (Offline Cache pro rychlé UI) ---
            if db_version < 12:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 12: Tabulka offline_cache.")
                try:
                    self.conn.execute("""
                    CREATE TABLE IF NOT EXISTS offline_cache (
                        series_name TEXT,
                        season_num INTEGER,
                        episode_num INTEGER,
                        file_path TEXT,
                        file_size INTEGER,
                        last_checked TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        PRIMARY KEY (series_name, season_num, episode_num)
                    )
                    """)
                    log.info("[MetadataDB Migrace] Tabulka offline_cache vytvořena.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error creating offline_cache table: {e}")
                
                db_version = 12
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 12)")
                self.conn.commit()

            # --- Migrace Verze 13 (VOD providers cache v tabulce media) ---
            if db_version < 13:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 13: VOD providers cache sloupce.")
                try:
                    cur = self.conn.execute("PRAGMA table_info(media)")
                    columns = [row[1] for row in cur.fetchall()]
                    if 'vod_providers_cz' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN vod_providers_cz TEXT DEFAULT NULL")
                        log.info("[MetadataDB Migrace] Sloupec vod_providers_cz přidán.")
                    if 'vod_providers_updated_at' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN vod_providers_updated_at TEXT DEFAULT NULL")
                        log.info("[MetadataDB Migrace] Sloupec vod_providers_updated_at přidán.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error altering media table (v13): {e}")

                db_version = 13
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 13)")
                self.conn.commit()

            # --- Migrace Verze 14 (Tombstones pro smazané seriály) ---
            if db_version < 14:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 14: Tabulka deleted_series (tombstones).")
                try:
                    self.conn.execute("""
                    CREATE TABLE IF NOT EXISTS deleted_series (
                        series_name TEXT PRIMARY KEY,
                        deleted_at  TEXT DEFAULT CURRENT_TIMESTAMP
                    )
                    """)
                    log.info("[MetadataDB Migrace] Tabulka deleted_series vytvořena.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error creating deleted_series table: {e}")

                db_version = 14
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 14)")
                self.conn.commit()

            # --- Migrace Verze 15 (cz_unavailable flag pro záznamy bez CZ dat na TMDb) ---
            if db_version < 15:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 15: cz_unavailable flag.")
                try:
                    cur = self.conn.execute("PRAGMA table_info(media)")
                    columns = [row[1] for row in cur.fetchall()]
                    if 'cz_unavailable' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN cz_unavailable INTEGER DEFAULT 0")
                        log.info("[MetadataDB Migrace] Sloupec cz_unavailable přidán do media.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error altering media table (v15): {e}")

                db_version = 15
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 15)")
                self.conn.commit()

            # --- Migrace Verze 16 (tmdb_audit_skip flag pro auto audit skip) ---
            if db_version < 16:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 16: tmdb_audit_skip flag.")
                try:
                    cur = self.conn.execute("PRAGMA table_info(media)")
                    columns = [row[1] for row in cur.fetchall()]
                    if 'tmdb_audit_skip' not in columns:
                        self.conn.execute("ALTER TABLE media ADD COLUMN tmdb_audit_skip INTEGER DEFAULT 0")
                        log.info("[MetadataDB Migrace] Sloupec tmdb_audit_skip přidán do media.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error altering media table (v16): {e}")

                db_version = 16
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 16)")
                self.conn.commit()

            # --- Migrace Verze 17 (trakt_ok flag pro lokální historii) ---
            if db_version < 17:
                log.info("[MetadataDB Migrace] Spouštím migraci na verzi 17: trakt_ok flag v local_history.")
                try:
                    cur = self.conn.execute("PRAGMA table_info(local_history)")
                    columns = [row[1] for row in cur.fetchall()]
                    if 'trakt_ok' not in columns:
                        self.conn.execute("ALTER TABLE local_history ADD COLUMN trakt_ok INTEGER")
                        log.info("[MetadataDB Migrace] Sloupec trakt_ok přidán do local_history.")
                except sqlite3.OperationalError as e:
                    log.error(f"[MetadataDB] Error altering local_history table (v17): {e}")

                db_version = 17
                self.conn.execute("INSERT OR REPLACE INTO db_version (id, version) VALUES (1, 17)")
                self.conn.commit()

            # General schema checks and updates (not tied to db_version migration)
            if not self.conn: return
            try:
                # Kontrola a přidání search_history (if it wasn't created by migration)
                cur = self.conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='search_history'")
                if not cur.fetchone():
                    self.conn.execute('''
                        CREATE TABLE search_history (
                            query TEXT PRIMARY KEY,
                            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                        )
                    ''')
                    self.conn.commit()
                    log.info("[MetadataDB] Tabulka search_history přidána")
                    
                # Kontrola a přidání sloupce 'title' do local_history
                cur = self.conn.execute("PRAGMA table_info(local_history)")
                columns = [row[1] for row in cur.fetchall()]
                if 'title' not in columns:
                    self.conn.execute("ALTER TABLE local_history ADD COLUMN title TEXT")
                    self.conn.commit()
                    log.info("[MetadataDB] Sloupec 'title' přidán do local_history")
                
                # Kontrola fulltext indexů (FTS) ... (placeholder for future FTS checks)
                # cur = self.conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='media_fts'")
                # if not cur.fetchone():
                #     # Add FTS table creation logic here if needed
                #     pass

            except Exception as e:
                log.error(f"[MetadataDB] Schema check/update error: {e}")

        except Exception as e:
            log.error(f"[MetadataDB] Migration error: {e}")

        # ----------------------------------------------------------------------
        # Krok 3: Indexy (volání tvé původní metody)
        # ----------------------------------------------------------------------
        self._create_indexes()

    def _create_indexes(self):
        try:
            if not self.conn: return
            self.conn.execute("CREATE INDEX IF NOT EXISTS idx_media_is_series ON media(is_series)")
            self.conn.execute("CREATE INDEX IF NOT EXISTS idx_episodes_tvid ON episodes(tv_id)")
            self.conn.execute("CREATE INDEX IF NOT EXISTS idx_local_history_time ON local_history(watched_at DESC)")
            self.conn.execute("CREATE INDEX IF NOT EXISTS idx_search_history_time ON search_history(timestamp DESC)")
            self.conn.commit()
        except Exception:
            pass
        indexes = [
            "CREATE INDEX IF NOT EXISTS idx_imdb ON media(imdb_id)",
            "CREATE INDEX IF NOT EXISTS idx_trakt ON media(trakt_id)",
            "CREATE INDEX IF NOT EXISTS idx_title ON media(title_cs, title_en)",
            "CREATE INDEX IF NOT EXISTS idx_accessed ON media(last_accessed)",
            "CREATE INDEX IF NOT EXISTS idx_type_year ON media(media_type, year)"
        ]
        for idx in indexes:
            try:
                if self.conn:
                    self.conn.execute(idx)
            except:
                pass
        if self.conn:
            self.conn.commit()

    def get(self, tmdb_id):
        """Vrátí záznam nebo None (Memory Cachováno přes Window property)"""
        try:
            if not self.conn: return None
            
            # --- 1) Pokus o Memory Cache ---
            if 'xbmcgui' in globals():
                try:
                    win = xbmcgui.Window(10000)
                    cached_str = win.getProperty(f"meta_{tmdb_id}")
                    if cached_str:
                        cached_data = json.loads(cached_str)
                        return cached_data
                except Exception as e:
                    log.debug(f"[MetadataDB] memory-cache miss for {tmdb_id}: {e}")
            
            # --- 2) Fallback na SQLite ---
            cur = self.conn.cursor()
            cur.execute("SELECT * FROM media WHERE tmdb_id = ?", (tmdb_id,))
            row = cur.fetchone()
            if row:
                cols = [col[0] for col in cur.description]
                data = dict(zip(cols, row))
                
                # --- 3) Uložení zpět do Memory Cache ---
                if 'xbmcgui' in globals():
                    try:
                        xbmcgui.Window(10000).setProperty(f"meta_{tmdb_id}", json.dumps(data))
                    except Exception:
                        pass
                
                return data
        except Exception as e:
            log.error(f"[MetadataDB] get error: {e}")
        return None

    def get_or_create(self, tmdb_id: int, media_type: str, metadata: Dict[str, Any]) -> Optional[Dict[str, Any]]:
        """
        Pokusí se získat záznam podle tmdb_id. Pokud neexistuje, vytvoří jej s minimálními metadaty.
        Vrací existující nebo nově vytvořený záznam.
        """
        if not self.conn: return None # Added check
        if not tmdb_id:
            return None

        # 1. Zkusíme načíst existující
        existing = self.get_by_tmdb_id(tmdb_id, columns=['tmdb_id'])
        
        if existing:
            return existing
        
        # 2. Neexistuje - vytvoříme nový záznam s minimem dat
        try:
            # Non-latin guard: do title sloupců smí jen latinka (jinak NULL → doplní audit).
            # Lazy import: ui_utils importuje metadata_db na module-level, obráceně to nejde.
            cz_title = metadata.get('cz_title')
            eng_title = metadata.get('eng_title')
            try:
                from resources.lib.utils.ui_utils import has_nonlatin_text
                if has_nonlatin_text(cz_title):
                    cz_title = None
                if has_nonlatin_text(eng_title):
                    eng_title = None
            except Exception:
                pass

            # Připravíme data pro nový záznam
            new_data = {
                'tmdb_id': int(tmdb_id),
                'media_type': media_type,
                'title_cs': cz_title or eng_title,
                'title_en': eng_title,
                'title_original': eng_title, # Pro jednoduchost
                'year': metadata.get('year'),
                # Všechny ostatní sloupce (plot, poster, ts) budou NULL nebo defaultní (což je správně)
            }
            
            # Vložení nového záznamu (použijeme metodu update pro insert s tmdb_id)
            # Metoda update je již robustní a volá insert/commit.
            self.update(tmdb_id, new_data, source='trakt_lazy_load')

            # Vracíme záznam s tmdb_id, který stačí pro tvorbu kontextového menu
            return {'tmdb_id': tmdb_id} 

        except Exception as e:
            log.error(f"[MetadataDB] CHYBA při lazy-load filmu {tmdb_id}: {e}")
            return None

    def get_by_tmdb_id(self, tmdb_id: int, columns: List[str] = None) -> Optional[dict]:
        """
        Vyhledá a vrátí záznam pro film nebo seriál na základě tmdb_id.
        Volitelně omezuje vrácené sloupce (ignoruje Window cache pro specifičtější DB calls).
        """
        if not tmdb_id:
            return None

        col_str = ', '.join(columns) if columns else '*'
        row = None
        
        try:
            if not self.conn: return None
            
            # Pro kompletní request se pokus o cachování jako u get()
            if not columns and 'xbmcgui' in globals():
                try:
                    win = xbmcgui.Window(10000)
                    cached_str = win.getProperty(f"meta_{tmdb_id}")
                    if cached_str:
                        return json.loads(cached_str)
                except Exception: pass
            
            with self.conn: # 🚨 KRITICKÁ ÚPRAVA C
                cur = self.conn.cursor()
                cur.execute(f"SELECT {col_str} FROM media WHERE tmdb_id = ?", (int(tmdb_id),))
                row = cur.fetchone()
            
            if row:
                cols = [col[0] for col in cur.description]
                data = dict(zip(cols, row))
                
                # Cache save only if grabbing full row matching memory-cache behavior
                if not columns and 'xbmcgui' in globals():
                    try:
                        xbmcgui.Window(10000).setProperty(f"meta_{tmdb_id}", json.dumps(data))
                    except Exception: pass
                    
                return data
        except Exception as e:
            log.error(f"[MetadataDB] get_by_tmdb_id error pro {tmdb_id}: {e}")
            
        return None
        
    def update(self, tmdb_id, data, source='tmdb'):
        """Aktualizuje nebo vloží záznam a synchronizuje Memory Cache"""
        try:
            if not self.conn: return
            existing = self.get(tmdb_id)
            if existing:
                sql = "UPDATE media SET "
                params = []
                for k, v in data.items():
                    if k in ('last_updated', 'last_accessed', 'source_flags'):
                        continue
                    sql += f"{k} = ?, "
                    params.append(v)
                sql += "last_accessed = CURRENT_TIMESTAMP, "
                sql += "last_updated = CURRENT_TIMESTAMP WHERE tmdb_id = ?"
                params.append(tmdb_id)
                self.conn.execute(sql, params)
                
                # Aktualizace existujici pameti
                if 'xbmcgui' in globals():
                    updated_data = existing.copy()
                    updated_data.update(data)
                    try:
                        xbmcgui.Window(10000).setProperty(f"meta_{tmdb_id}", json.dumps(updated_data))
                    except Exception: pass
            else:
                keys = ['tmdb_id'] + list(data.keys())
                placeholders = ','.join(['?'] * len(keys))
                sql = f"INSERT INTO media ({', '.join(keys)}) VALUES ({placeholders})"
                values = [tmdb_id] + list(data.values())
                self.conn.execute(sql, values)
                
                # Zapis nove struktury do pameti
                if 'xbmcgui' in globals():
                    new_data = data.copy()
                    new_data['tmdb_id'] = tmdb_id
                    try:
                        xbmcgui.Window(10000).setProperty(f"meta_{tmdb_id}", json.dumps(new_data))
                    except Exception: pass

            self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] update error: {e}")

    def save_search_query(self, query: str, max_items: int = 50):
        try:
            if not self.conn: return
            ts = int(time.time())
            self.conn.execute("""
                INSERT OR IGNORE INTO search_history (query, timestamp)
                VALUES (?, ?)
            """, (query, ts))
            self.conn.execute("""
                UPDATE search_history SET timestamp = ? WHERE query = ?
            """, (ts, query))
            self.conn.commit()

            # Trim history
            if max_items > 0:
                cur = self.conn.execute(
                    "SELECT query FROM search_history ORDER BY timestamp DESC LIMIT -1 OFFSET ?",
                    (max_items,)
                )
                to_delete = [row[0] for row in cur.fetchall()]
                for q in to_delete:
                    self.conn.execute("DELETE FROM search_history WHERE query = ?", (q,))
                if to_delete:
                    self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] save_search_query error: {e}")

    def load_search_queries(self, limit: int = 50) -> List[str]:
        """Vrátí seznam stringů uložných v historii vyhledávání, seřazený sestupně podle času."""
        try:
            if not self.conn: return []
            cur = self.conn.execute("SELECT query FROM search_history ORDER BY timestamp DESC LIMIT ?", (limit,))
            return [row[0] for row in cur.fetchall()]
        except Exception as e:
            log.error(f"[MetadataDB] load_search_queries error: {e}")
            return []

    # NOTE: Historicky zde byla duplicitní (a buggy) definice `get_local_history`
    # čtoucí neexistující sloupec `name`. Živá implementace je níže v souboru
    # (vrací `title`, `progress`, `trakt_ok`, …). Duplicitu jsme smazali, aby
    # nedocházelo k záměně při čtení kódu.

    def remove_search_query(self, query: str):
        try:
            if not self.conn: return
            self.conn.execute("DELETE FROM search_history WHERE query = ?", (query,))
            self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] remove_search_query error: {e}")

    def cleanup(self, days=30):
        """Smaže staré záznamy"""
        try:
            if not self.conn: return
            cutoff = (datetime.now() - timedelta(days=days)).strftime('%Y-%m-%d %H:%M:%S')
            self.conn.execute("DELETE FROM media WHERE last_accessed < ?", (cutoff,))
            self.conn.commit()
            log.info(f"[MetadataDB] Cleanup: {self.conn.total_changes} records removed")
        except Exception as e:
            log.error(f"[MetadataDB] cleanup error: {e}")

    def close(self):
        if self.conn:
            self.conn.close()


    def save_local_history(self, tmdb_id: int, media_type: str, title: str, progress: float = 0, stream_url: str = None, season_num: int = None, episode_num: int = None, trakt_payload: dict = None, trakt_ok: Optional[int] = None):
        """Uloží nebo zaktualizuje záznam do lokální historie.

        trakt_ok:
            None = neznámo (např. úvodní zápis při startu přehrávání),
            1    = Trakt STOP odeslán úspěšně (HTTP 201/409),
            0    = neodesláno / selhalo.
        """
        if not self.conn: return
        try:
            payload_str = json.dumps(trakt_payload, ensure_ascii=False) if trakt_payload else None

            # Zkusíme smazat existující záznam a vložit jej na konec (jako nejnovější)
            self.conn.execute("DELETE FROM local_history WHERE tmdb_id=? AND media_type=?", (tmdb_id, media_type))

            sql = """
            INSERT INTO local_history (tmdb_id, media_type, title, progress, stream_url, season_num, episode_num, trakt_payload, trakt_ok)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """
            self.conn.execute(sql, (tmdb_id, media_type, title, progress, stream_url, season_num, episode_num, payload_str, trakt_ok))
            
            # Autoclean (necháme např. jen top 200 pro daný typ)
            cur = self.conn.execute("SELECT id FROM local_history WHERE media_type=? ORDER BY watched_at DESC LIMIT -1 OFFSET 200", (media_type,))
            olds = [row[0] for row in cur.fetchall()]
            for old_id in olds:
                self.conn.execute("DELETE FROM local_history WHERE id=?", (old_id,))
                
            self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] Error v save_local_history: {e}")

    def remove_local_history(self, tmdb_id: int, media_type: str):
        """Odstraní záznam z lokální historie."""
        if not self.conn: return
        try:
            self.conn.execute("DELETE FROM local_history WHERE tmdb_id=? AND media_type=?", (tmdb_id, media_type))
            self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] Error v remove_local_history: {e}")

    def add_local_history(self, db_dict: dict):
        """Pomocná metoda pro vložení celého záznamu historie."""
        self.save_local_history(
            tmdb_id=db_dict.get('tmdb_id'),
            media_type=db_dict.get('media_type', 'movie'),
            title=(db_dict.get('title') or db_dict.get('name') or ''),
            progress=db_dict.get('progress', 0),
            stream_url=db_dict.get('stream_url'),
            season_num=db_dict.get('season_num'),
            episode_num=db_dict.get('episode_num'),
            trakt_payload=db_dict.get('trakt_payload'),
            trakt_ok=db_dict.get('trakt_ok'),
        )

    def get_local_history(self, media_type: str = None, limit: int = 50) -> List[dict]:
        """Vrátí historii seřazenou podle watched_at (od nejnovějších)."""
        if not self.conn: return []
        try:
            sql = "SELECT tmdb_id, media_type, title, progress, stream_url, season_num, episode_num, trakt_payload, watched_at, trakt_ok FROM local_history"
            params = []
            if media_type:
                sql += " WHERE media_type=?"
                params.append(media_type)
            sql += " ORDER BY watched_at DESC"
            if limit:
                sql += " LIMIT ?"
                params.append(limit)

            cur = self.conn.execute(sql, params)
            res = []
            for row in cur.fetchall():
                res.append({
                    'tmdb_id': row[0],
                    'media_type': row[1],
                    'title': row[2],
                    'progress': row[3],
                    'stream_url': row[4],
                    'season_num': row[5],
                    'episode_num': row[6],
                    'trakt_payload': json.loads(row[7]) if row[7] else {},
                    'watched_at': row[8],
                    'trakt_ok': row[9],
                })
            return res
        except Exception as e:
            log.error(f"[MetadataDB] Error v get_local_history: {e}")
            return []

    def delete_local_history(self, tmdb_id: int, media_type: str = None):
        """Vymaže konkrétní záznam z historie."""
        if self.conn:
            try:
                if media_type:
                    self.conn.execute("DELETE FROM local_history WHERE tmdb_id=? AND media_type=?", (tmdb_id, media_type))
                else:
                    self.conn.execute("DELETE FROM local_history WHERE tmdb_id=?", (tmdb_id,))
                self.conn.commit()
            except Exception as e:
                log.error(f"[MetadataDB] delete_local_history error: {e}")

    # =========================================================================
    # FÁZE 3: SERIÁLOVÁ DATA (BLOB / JSON)
    # =========================================================================

    def get_series_data(self, series_name: str) -> Optional[Dict[str, Any]]:
        """
        Fáze 3: Načte kompletní JSON data jednoho seriálu přímo ze SQLite.
        """
        if self.conn and series_name:
            try:
                cursor = self.conn.cursor()
                cursor.execute("SELECT json_data FROM series_data WHERE series_name = ?", (series_name,))
                row = cursor.fetchone()
                if row and row[0]:
                    return json.loads(row[0])
            except Exception as e:
                log.error(f"[MetadataDB] Chyba pri get_series_data '{series_name}': {e}")
        return None

    def save_series_data(self, series_name: str, json_data_dict: Dict[str, Any]):
        """
        Fáze 3: Uloží (Insert or Replace) python dict jako JSON string do tabulky series_data.
        Při uložení (včetně re-addu) maže matching tombstones v deleted_series,
        aby CloudSync merge seriál znovu nefiltroval.
        """
        if self.conn and series_name and json_data_dict:
            try:
                json_str = json.dumps(json_data_dict, ensure_ascii=False)
                self.conn.execute("""
                    INSERT OR REPLACE INTO series_data (series_name, json_data, last_modified) 
                    VALUES (?, ?, CURRENT_TIMESTAMP)
                """, (series_name, json_str))

                # Resurrect: smazat tombstony pro tento seriál a varianty klíče
                try:
                    from resources.lib.utils.ui_utils import clean_filename
                    keys = {
                        series_name,
                        clean_filename(series_name),
                        series_name.replace(':', '_').replace('?', '_'),
                    }
                    tid = (json_data_dict.get('tmdb') or {}).get('id')
                    if tid:
                        keys.add(f"tmdb_{tid}")
                    placeholders = ','.join('?' * len(keys))
                    self.conn.execute(
                        f"DELETE FROM deleted_series WHERE series_name IN ({placeholders})",
                        tuple(keys),
                    )
                except Exception as te:
                    log.debug(f"[MetadataDB] clear tombstones on save '{series_name}': {te}")

                self.conn.commit()
            except Exception as e:
                log.error(f"[MetadataDB] Chyba pri save_series_data '{series_name}': {e}")

    def delete_series_data(self, series_name: str, tmdb_id=None) -> bool:
        """
        Fáze 3: Smaže seriál z lokální SQLite tabulky series_data.
        Zároveň zapíše tombstone do deleted_series pro správnou CloudSync merge.
        """
        if not self.conn or not series_name:
            return False
        try:
            from resources.lib.utils.ui_utils import clean_filename
            clean_name = clean_filename(series_name)
            alt_name = series_name.replace(':', '_').replace('?', '_')

            if tmdb_id:
                tmdb_key = f"tmdb_{tmdb_id}"
                cur = self.conn.execute(
                    "DELETE FROM series_data WHERE series_name IN (?, ?, ?, ?)",
                    (series_name, clean_name, alt_name, tmdb_key)
                )
                # Tombstones pro všechny varianty klíče
                for key in (series_name, clean_name, alt_name, tmdb_key):
                    self.conn.execute(
                        "INSERT OR REPLACE INTO deleted_series (series_name, deleted_at) VALUES (?, CURRENT_TIMESTAMP)",
                        (key,)
                    )
            else:
                cur = self.conn.execute(
                    "DELETE FROM series_data WHERE series_name IN (?, ?, ?)",
                    (series_name, clean_name, alt_name)
                )
                for key in (series_name, clean_name, alt_name):
                    self.conn.execute(
                        "INSERT OR REPLACE INTO deleted_series (series_name, deleted_at) VALUES (?, CURRENT_TIMESTAMP)",
                        (key,)
                    )

            self.conn.commit()
            deleted = cur.rowcount > 0
            if deleted:
                log.info(f"[MetadataDB] Tombstone zapsán pro: {series_name}")
            return deleted
        except Exception as e:
            log.error(f"[MetadataDB] Chyba pri delete_series_data '{series_name}': {e}")
            return False

    # === Fáze 2: PLAYLIST INDEX ===============================================
    def save_playlist_meta(self, meta: dict):
        if not self.conn: return
        try:
            self.conn.execute("""
                INSERT OR REPLACE INTO playlist_meta (id, source_path, source_mtime, build_time, count)
                VALUES (1, ?, ?, ?, ?)
            """, (
                meta.get('source_path', ''),
                meta.get('source_mtime', 0),
                meta.get('build_time', ''),
                meta.get('count', 0)
            ))
            self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] Chyba při save_playlist_meta: {e}")

    def get_playlist_meta(self) -> dict:
        if not self.conn: return {}
        try:
            cur = self.conn.execute("SELECT source_path, source_mtime, build_time, count FROM playlist_meta WHERE id=1")
            row = cur.fetchone()
            if row:
                return {
                    'source_path': row[0],
                    'source_mtime': row[1],
                    'build_time': row[2],
                    'count': row[3]
                }
        except Exception as e:
            log.error(f"[MetadataDB] Chyba při get_playlist_meta: {e}")
        return {}

    def save_playlist_items(self, items: list):
        if not self.conn: return
        try:
            self.conn.execute("BEGIN TRANSACTION;")
            self.conn.execute("DELETE FROM playlist_index;")
            
            sql = """
            INSERT INTO playlist_index (
                title_raw, title_norm, year, tmdb_id, media_type, m3u_url, logo, group_title, 
                kodiprop_json, vlcopt_json, poster_path, plot, display_title, confidence
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """
            
            data = []
            for it in items:
                data.append((
                    it.get('title_raw', ''),
                    it.get('title_norm', ''),
                    it.get('year'),
                    int(it.get('tmdb_id') or 0),
                    it.get('media_type', ''),
                    it.get('m3u_url', ''),
                    it.get('logo', ''),
                    it.get('group', ''),
                    json.dumps(it.get('kodiprop', {})),
                    json.dumps(it.get('vlcopt', {})),
                    it.get('poster_path', ''),
                    it.get('plot', ''),
                    it.get('display_title', ''),
                    float(it.get('confidence', 0.0))
                ))
            
            self.conn.executemany(sql, data)
            self.conn.commit()
        except Exception as e:
            self.conn.execute("ROLLBACK;")
            log.error(f"[MetadataDB] Chyba při save_playlist_items: {e}")

    def search_playlist_index(self, tmdb_id: int = 0, title_norm: str = '', year: int = None) -> dict:
        """Hledá nejlepší shodu pro M3U lookup z API / GUI"""
        if not self.conn: return None
        try:
            def _map_row(r):
                return {
                    'title_raw': r[1], 'title_norm': r[2], 'year': r[3], 'tmdb_id': r[4],
                    'media_type': r[5], 'm3u_url': r[6], 'logo': r[7], 'group': r[8],
                    'kodiprop': json.loads(r[9] or '{}'), 'vlcopt': json.loads(r[10] or '{}'),
                    'poster_path': r[11], 'plot': r[12], 'display_title': r[13], 'confidence': r[14]
                }
                
            if tmdb_id and tmdb_id > 0:
                cur = self.conn.execute("SELECT * FROM playlist_index WHERE tmdb_id = ? LIMIT 1", (tmdb_id,))
                row = cur.fetchone()
                if row: return _map_row(row)
                
            if title_norm:
                if year:
                    cur = self.conn.execute("SELECT * FROM playlist_index WHERE title_norm = ? AND year = ?", (title_norm, year))
                    rows = cur.fetchall()
                    if rows:
                        for row in rows:
                            if (row[8] or '').casefold() == 'filmy': return _map_row(row)
                        return _map_row(rows[0])
                        
                cur = self.conn.execute("SELECT * FROM playlist_index WHERE title_norm = ?", (title_norm,))
                rows = cur.fetchall()
                if rows:
                    for row in rows:
                        if (row[8] or '').casefold() == 'filmy': return _map_row(row)
                    return _map_row(rows[0])
                    
            return None
        except Exception as e:
            log.error(f"[MetadataDB] Chyba při search_playlist_index: {e}")
            return None

    def get_all_playlist_items(self) -> list:
        """Vrátí všechny položky v indexu pro in-memory filtering (např. v Toplistech)"""
        if not self.conn: return []
        try:
            cur = self.conn.execute("SELECT * FROM playlist_index")
            out = []
            for r in cur.fetchall():
                out.append({
                    'title_raw': r[1], 'title_norm': r[2], 'year': r[3], 'tmdb_id': r[4],
                    'media_type': r[5], 'm3u_url': r[6], 'logo': r[7], 'group': r[8],
                    'kodiprop': json.loads(r[9] or '{}'), 'vlcopt': json.loads(r[10] or '{}'),
                    'poster_path': r[11], 'plot': r[12], 'display_title': r[13], 'confidence': r[14]
                })
            return out
        except Exception as e:
            log.error(f"[MetadataDB] Chyba při get_all_playlist_items: {e}")
            return []

    def export_user_data(self) -> dict:
        """
        Exportuje z lokální databáze všechna klíčová uživatelská data.
        Tzn. local_history, search_history a nově i series_data (lokální progres).
        """
        data = {
            "local_history": [],
            "search_history": [],
            "series_data": [],
            "deleted_series": []
        }
        if not self.conn:
            return data
            
        try:
            # Export local history
            cur = self.conn.execute("SELECT tmdb_id, media_type, title, progress, stream_url, season_num, episode_num, trakt_payload, watched_at FROM local_history")
            for row in cur.fetchall():
                data["local_history"].append({
                    "tmdb_id": row[0],
                    "media_type": row[1],
                    "title": row[2],
                    "progress": row[3],
                    "stream_url": row[4],
                    "season_num": row[5],
                    "episode_num": row[6],
                    "trakt_payload": json.loads(row[7]) if row[7] else {},
                    "watched_at": row[8]
                })
                
            # Export search history
            cur2 = self.conn.execute("SELECT query, timestamp FROM search_history")
            for row in cur2.fetchall():
                data["search_history"].append({
                    "query": row[0],
                    "timestamp": row[1]
                })

            # Export series_data (fáze 3 JSON BLOBy)
            cur3 = self.conn.execute("SELECT series_name, json_data, last_modified FROM series_data")
            for row in cur3.fetchall():
                data["series_data"].append({
                    "series_name": row[0],
                    "json_data": row[1],
                    "last_modified": row[2]
                })

            # Export deleted_series (tombstones pro CloudSync merge)
            try:
                cur4 = self.conn.execute("SELECT series_name, deleted_at FROM deleted_series")
                for row in cur4.fetchall():
                    data["deleted_series"].append({
                        "series_name": row[0],
                        "deleted_at": row[1]
                    })
            except Exception:
                pass  # tabulka nemusí existovat na starých DB
                
        except Exception as e:
            log.error(f"[MetadataDB] export_user_data error: {e}")
            
        return data

    def import_merged_user_data(self, merged_data: dict):
        """
        Zapíše finálně smíchaná data z cloudu zpět do SQLite databáze.
        Očekává dict strukturu vygenerovanou z export_user_data.
        """
        if not self.conn or not merged_data:
            return
            
        try:
            self.conn.execute("BEGIN TRANSACTION;")
            
            # 1. Obnova search_history (kompletní přepis jelikož data z cloudu už mají merge)
            self.conn.execute("DELETE FROM search_history;")
            for search_item in merged_data.get("search_history", []):
                self.conn.execute(
                    "INSERT INTO search_history (query, timestamp) VALUES (?, ?)",
                    (search_item["query"], search_item["timestamp"])
                )
                
            # 2. Obnova local_history
            self.conn.execute("DELETE FROM local_history;")
            for hist_item in merged_data.get("local_history", []):
                payload_str = json.dumps(hist_item.get("trakt_payload", {}), ensure_ascii=False)
                self.conn.execute("""
                    INSERT INTO local_history (tmdb_id, media_type, title, progress, stream_url, season_num, episode_num, trakt_payload, watched_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    hist_item.get("tmdb_id"),
                    hist_item.get("media_type"),
                    hist_item.get("title"),
                    hist_item.get("progress", 0),
                    hist_item.get("stream_url"),
                    hist_item.get("season_num"),
                    hist_item.get("episode_num"),
                    payload_str,
                    hist_item.get("watched_at")
                ))
            # 3. Obnova series_data (lokální progrese seriálů)
            self.conn.execute("DELETE FROM series_data;")
            for series_item in merged_data.get("series_data", []):

                self.conn.execute("""
                    INSERT OR REPLACE INTO series_data (series_name, json_data, last_modified) 
                    VALUES (?, ?, ?)
                """, (
                    series_item["series_name"],
                    series_item["json_data"],
                    series_item["last_modified"]
                ))

            # 4. Obnova tombstonů (deleted_series) — plný přepis jako series_data
            try:
                self.conn.execute("DELETE FROM deleted_series;")
                for tomb in merged_data.get("deleted_series", []):
                    self.conn.execute(
                        "INSERT OR REPLACE INTO deleted_series (series_name, deleted_at) VALUES (?, ?)",
                        (tomb["series_name"], tomb["deleted_at"])
                    )
            except Exception as e:
                log.warning(f"[MetadataDB] import tombstones chyba (může chybět tabulka na starém DB): {e}")
            
            self.conn.commit()
            log.info("[MetadataDB] import_merged_user_data úspěšně dokončen.")
        except Exception as e:
            self.conn.execute("ROLLBACK;")
            log.error(f"[MetadataDB] import_merged_user_data error: {e}")

    def export_media_data(self) -> list:
        """
        Exportuje tabulku 'media' pro CloudSync.
        Vynechává trakt_full_data (velký blob) a source_flags.
        Vrací list dictů, prázdné (NULL) hodnoty jsou vynechány pro kompaktnost.
        """
        result = []
        if not self.conn:
            return result
        try:
            # Vybereme všechny sloupce kromě trakt_full_data a source_flags
            excluded = {'trakt_full_data', 'source_flags'}
            cur = self.conn.execute("PRAGMA table_info(media)")
            all_cols = [row[1] for row in cur.fetchall() if row[1] not in excluded]
            col_str = ', '.join(all_cols)
            cur = self.conn.execute(f"SELECT {col_str} FROM media")
            for row in cur.fetchall():
                d = {}
                for col, val in zip(all_cols, row):
                    if val is not None and val != '':
                        d[col] = val
                if d:
                    result.append(d)
        except Exception as e:
            log.error(f"[MetadataDB] export_media_data error: {e}")
        return result

    def import_merged_media_data(self, media_list: list):
        """
        Importuje metadata filmů/seriálů z CloudSync.
        Pro každý záznam: pokud lokální je novější (last_updated), ponechá lokální.
        Pokud vzdálený je novější nebo lokální neexistuje, přepíše/vloží.
        """
        if not self.conn or not media_list:
            return
        imported = 0
        skipped = 0
        try:
            for item in media_list:
                tmdb_id = item.get('tmdb_id')
                if not tmdb_id:
                    continue
                remote_ts = item.get('last_updated', '1970-01-01 00:00:00') or '1970-01-01 00:00:00'
                # Zkontroluj lokální timestamp
                try:
                    cur = self.conn.execute(
                        "SELECT last_updated FROM media WHERE tmdb_id = ?", (int(tmdb_id),)
                    )
                    row = cur.fetchone()
                    if row:
                        local_ts = row[0] or '1970-01-01 00:00:00'
                        if local_ts >= remote_ts:
                            skipped += 1
                            continue  # lokální je stejně starý nebo novější → přeskočit
                except Exception:
                    pass
                # Vložit nebo přepsat vzdáleným záznamem
                try:
                    keys = list(item.keys())
                    placeholders = ','.join(['?'] * len(keys))
                    col_str = ', '.join(keys)
                    values = [item[k] for k in keys]
                    self.conn.execute(
                        f"INSERT OR REPLACE INTO media ({col_str}) VALUES ({placeholders})",
                        values
                    )
                    imported += 1
                except Exception as e:
                    log.warning(f"[MetadataDB] import_merged_media_data skip tmdb_id={tmdb_id}: {e}")
            self.conn.commit()
            log.info(f"[MetadataDB] import_merged_media_data: {imported} importováno, {skipped} přeskočeno (lokální novější).")
        except Exception as e:
            log.error(f"[MetadataDB] import_merged_media_data error: {e}")



    # =========================================================================
    # FÁZE 5: OFFLINE CACHE (Rychlé UI)
    # =========================================================================

    def clear_offline_cache_for_series(self, series_name: str):
        if not self.conn: return
        try:
            self.conn.execute("DELETE FROM offline_cache WHERE series_name = ?", (series_name,))
            self.conn.commit()
        except Exception as e:
            log.error(f"[MetadataDB] clear_offline_cache_for_series error: {e}")

    def save_offline_cache_items(self, series_name: str, items: list):
        """
        items: list of dicts: {'season': int, 'episode': int, 'path': str, 'size': int}
        """
        if not self.conn: return
        try:
            self.conn.execute("BEGIN TRANSACTION;")
            self.conn.execute("DELETE FROM offline_cache WHERE series_name = ?", (series_name,))
            
            sql = """
            INSERT INTO offline_cache (series_name, season_num, episode_num, file_path, file_size)
            VALUES (?, ?, ?, ?, ?)
            """
            data = []
            for it in items:
                data.append((
                    series_name,
                    int(it.get('season', 0)),
                    int(it.get('episode', 0)),
                    str(it.get('path', '')),
                    int(it.get('size', 0))
                ))
            
            self.conn.executemany(sql, data)
            self.conn.commit()
        except Exception as e:
            self.conn.execute("ROLLBACK;")
            log.error(f"[MetadataDB] save_offline_cache_items error: {e}")

    def get_offline_cache_for_series(self, series_name: str) -> list:
        if not self.conn: return []
        try:
            cur = self.conn.execute("SELECT season_num, episode_num, file_path, file_size FROM offline_cache WHERE series_name = ?", (series_name,))
            out = []
            for r in cur.fetchall():
                out.append({
                    'season': r[0],
                    'episode': r[1],
                    'path': r[2],
                    'size': r[3]
                })
            return out
        except Exception as e:
            log.error(f"[MetadataDB] get_offline_cache_for_series error: {e}")
            return []

    def get_offline_cache_all(self) -> dict:
        """Vrátí dict strukturu celého cache pro rychlé načtení u seznamu seriálů"""
        if not self.conn: return {}
        try:
            cur = self.conn.execute("SELECT series_name, season_num, episode_num, file_path, file_size FROM offline_cache")
            out = {}
            for r in cur.fetchall():
                s_name = r[0]
                if s_name not in out:
                    out[s_name] = []
                out[s_name].append({
                    'season': r[1],
                    'episode': r[2],
                    'path': r[3],
                    'size': r[4]
                })
            return out
        except Exception as e:
            log.error(f"[MetadataDB] get_offline_cache_all error: {e}")
            return {}

# Globální instance
db = MetadataDB()

