"""
SQLite Database Engine for VideoDetection.
Lưu trữ toàn diện thông tin video, vector đặc trưng V-JEPA 2 / NeuralFP và lịch sử đối soát
vào 1 file database duy nhất: Data/videodetection.db
Đảm bảo 100% Portable (sử dụng thư viện sqlite3 chuẩn có sẵn trong Python, Zero-Install).
"""
from __future__ import annotations

import datetime
import hashlib
import io
import logging
import sqlite3
from pathlib import Path
from typing import Any, Dict, List, Optional, Tuple

import numpy as np

from ..core.paths import DATA_DIR
from .feature_store import CachedVideoFeatures

logger = logging.getLogger(__name__)

DEFAULT_DB_PATH = DATA_DIR / "videodetection.db"


def compute_file_hash(file_path: Path, max_bytes: int = 2 * 1024 * 1024) -> str:
    """Tính hash MD5 nhanh bằng cách đọc 2MB đầu + đuôi file (tránh đọc cả file video hàng GB)."""
    hasher = hashlib.md5()
    try:
        size = file_path.stat().st_size
        with open(file_path, "rb") as f:
            if size <= max_bytes * 2:
                hasher.update(f.read())
            else:
                hasher.update(f.read(max_bytes))
                f.seek(max(0, size - max_bytes))
                hasher.update(f.read(max_bytes))
        return hasher.hexdigest()
    except Exception:
        return ""


def array_to_blob(arr: np.ndarray) -> bytes:
    """Nén mảng numpy thành bytes lưu vào SQLite BLOB."""
    if arr is None or arr.size == 0:
        return b""
    buffer = io.BytesIO()
    np.save(buffer, arr, allow_pickle=False)
    return buffer.getvalue()


def blob_to_array(blob: bytes) -> np.ndarray:
    """Giải nén bytes từ SQLite BLOB về lại numpy array."""
    if not blob:
        return np.empty(0, dtype=np.float32)
    buffer = io.BytesIO(blob)
    return np.load(buffer, allow_pickle=False)


class VideoDatabase:
    """
    Quản lý cơ sở dữ liệu SQLite cục bộ tại Data/videodetection.db.
    """

    def __init__(self, db_path: Path = DEFAULT_DB_PATH) -> None:
        self.db_path = db_path
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._init_schema()

    def _get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(str(self.db_path), timeout=15.0)
        conn.row_factory = sqlite3.Row
        # Kích hoạt WAL mode để đọc ghi đồng thời siêu nhanh
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        return conn

    def _init_schema(self) -> None:
        """Khởi tạo cấu trúc bảng nếu chưa tồn tại."""
        with self._get_connection() as conn:
            conn.executescript("""
            -- Bảng quản lý danh mục video theo tên file
            CREATE TABLE IF NOT EXISTS videos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                video_name TEXT UNIQUE NOT NULL,      -- Tên file video (vd: goc_01.mp4)
                video_id TEXT UNIQUE NOT NULL,        -- Stem không kèm đuôi file
                file_path TEXT NOT NULL,
                category TEXT NOT NULL,               -- 'reference' (kho Data/) hoặc 'target'
                duration_sec REAL NOT NULL,
                file_size INTEGER NOT NULL,
                file_mtime REAL NOT NULL,
                file_hash TEXT,
                visual_clips_count INTEGER DEFAULT 0,
                audio_chunks_count INTEGER DEFAULT 0,
                status TEXT DEFAULT 'indexed',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            -- Bảng lưu trữ vector đặc trưng dạng BLOB nhị phân nén PQ64 & Hamming
            CREATE TABLE IF NOT EXISTS video_features (
                video_id TEXT PRIMARY KEY,
                visual_times BLOB,
                visual_embs BLOB,                     -- Matrix [N_vis, 1024]
                audio_times BLOB,
                audio_fps BLOB,                       -- Matrix [N_aud, 128]
                visual_codes BLOB,                    -- Matrix PQ64 [N_vis, 64] uint8
                audio_bits BLOB,                      -- Matrix Hamming [N_aud, 16] uint8
                visual_dim INTEGER DEFAULT 1024,
                audio_dim INTEGER DEFAULT 128,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (video_id) REFERENCES videos(video_id) ON DELETE CASCADE
            );

            -- Bảng lưu lịch sử các lần đối soát trùng lặp
            CREATE TABLE IF NOT EXISTS comparison_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                target_video_name TEXT NOT NULL,
                reference_video_name TEXT NOT NULL,
                target_start_sec REAL NOT NULL,
                target_end_sec REAL NOT NULL,
                ref_start_sec REAL NOT NULL,
                ref_end_sec REAL NOT NULL,
                duration_sec REAL NOT NULL,
                confidence_score REAL NOT NULL,
                match_type TEXT NOT NULL,
                scanned_at TEXT NOT NULL
            );

            CREATE INDEX IF NOT EXISTS idx_videos_category ON videos(category);
            CREATE INDEX IF NOT EXISTS idx_videos_name ON videos(video_name);
            CREATE INDEX IF NOT EXISTS idx_history_target ON comparison_history(target_video_name);
            """)

    def is_video_up_to_date(self, video_path: Path) -> bool:
        """Kiểm tra video trong database đã được index và có cùng mtime / size không."""
        video_name = video_path.name
        try:
            stat = video_path.stat()
            with self._get_connection() as conn:
                row = conn.execute(
                    "SELECT file_size, file_mtime FROM videos WHERE video_name = ?",
                    (video_name,),
                ).fetchone()

                if row is None:
                    return False

                return (
                    row["file_size"] == stat.st_size
                    and abs(row["file_mtime"] - stat.st_mtime) < 1.0
                )
        except Exception:
            return False

    def upsert_video(
        self,
        video_path: Path,
        category: str,
        features: CachedVideoFeatures,
    ) -> None:
        """
        Lưu hoặc cập nhật video cùng các vector đặc trưng vào database theo tên video.
        """
        now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        stat = video_path.stat()
        file_hash = compute_file_hash(video_path)

        vis_times_blob = array_to_blob(features.visual_times)
        vis_embs_blob = array_to_blob(features.visual_embs)
        aud_times_blob = array_to_blob(features.audio_times)
        aud_fps_blob = array_to_blob(features.audio_fps)
        vis_codes_blob = array_to_blob(features.visual_codes) if features.visual_codes is not None else b""
        aud_bits_blob = array_to_blob(features.audio_bits) if features.audio_bits is not None else b""

        with self._get_connection() as conn:
            # 1. Ghi vào bảng videos
            conn.execute(
                """
                INSERT INTO videos (
                    video_name, video_id, file_path, category, duration_sec,
                    file_size, file_mtime, file_hash, visual_clips_count,
                    audio_chunks_count, status, created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'indexed', ?, ?)
                ON CONFLICT(video_name) DO UPDATE SET
                    file_path = excluded.file_path,
                    category = excluded.category,
                    duration_sec = excluded.duration_sec,
                    file_size = excluded.file_size,
                    file_mtime = excluded.file_mtime,
                    file_hash = excluded.file_hash,
                    visual_clips_count = excluded.visual_clips_count,
                    audio_chunks_count = excluded.audio_chunks_count,
                    status = 'indexed',
                    updated_at = excluded.updated_at
                """,
                (
                    video_path.name,
                    features.video_id,
                    str(video_path),
                    category,
                    features.duration_sec,
                    stat.st_size,
                    stat.st_mtime,
                    file_hash,
                    features.visual_count,
                    features.audio_count,
                    now_str,
                    now_str,
                ),
            )

            # 2. Ghi vào bảng video_features
            conn.execute(
                """
                INSERT INTO video_features (
                    video_id, visual_times, visual_embs, audio_times, audio_fps,
                    visual_codes, audio_bits, visual_dim, audio_dim, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(video_id) DO UPDATE SET
                    visual_times = excluded.visual_times,
                    visual_embs = excluded.visual_embs,
                    audio_times = excluded.audio_times,
                    audio_fps = excluded.audio_fps,
                    visual_codes = excluded.visual_codes,
                    audio_bits = excluded.audio_bits,
                    updated_at = excluded.updated_at
                """,
                (
                    features.video_id,
                    vis_times_blob,
                    vis_embs_blob,
                    aud_times_blob,
                    aud_fps_blob,
                    vis_codes_blob,
                    aud_bits_blob,
                    1024,
                    128,
                    now_str,
                ),
            )

    def get_features_by_id(self, video_id: str) -> Optional[CachedVideoFeatures]:
        """Đọc vector đặc trưng trực tiếp từ Database SQLite."""
        with self._get_connection() as conn:
            row_meta = conn.execute(
                "SELECT * FROM videos WHERE video_id = ?", (video_id,)
            ).fetchone()
            if not row_meta:
                return None

            row_feat = conn.execute(
                "SELECT * FROM video_features WHERE video_id = ?", (video_id,)
            ).fetchone()
            if not row_feat:
                return None

            vis_codes = blob_to_array(row_feat["visual_codes"]) if "visual_codes" in row_feat.keys() and row_feat["visual_codes"] else None
            aud_bits = blob_to_array(row_feat["audio_bits"]) if "audio_bits" in row_feat.keys() and row_feat["audio_bits"] else None

            return CachedVideoFeatures(
                video_id=row_meta["video_id"],
                filename=row_meta["video_name"],
                video_path=row_meta["file_path"],
                duration_sec=row_meta["duration_sec"],
                file_size_bytes=row_meta["file_size"],
                file_mtime=row_meta["file_mtime"],
                visual_times=blob_to_array(row_feat["visual_times"]),
                visual_embs=blob_to_array(row_feat["visual_embs"]),
                audio_times=blob_to_array(row_feat["audio_times"]),
                audio_fps=blob_to_array(row_feat["audio_fps"]),
                visual_codes=vis_codes,
                audio_bits=aud_bits,
            )

    def get_features_by_name(self, video_name: str) -> Optional[CachedVideoFeatures]:
        """Truy vấn đặc trưng theo tên file video."""
        with self._get_connection() as conn:
            row = conn.execute(
                "SELECT video_id FROM videos WHERE video_name = ?", (video_name,)
            ).fetchone()
            if not row:
                return None
            return self.get_features_by_id(row["video_id"])

    def get_all_videos(self, category: Optional[str] = None) -> List[Dict[str, Any]]:
        """Lấy danh sách thông tin tất cả video trong database."""
        query = "SELECT * FROM videos"
        params: Tuple[Any, ...] = ()
        if category:
            query += " WHERE category = ?"
            params = (category,)
        query += " ORDER BY updated_at DESC"

        with self._get_connection() as conn:
            rows = conn.execute(query, params).fetchall()
            return [dict(r) for r in rows]

    def log_match_history(self, target_name: str, matches: List[Any]) -> None:
        """Ghi nhận lịch sử các đoạn trùng lặp vào bảng comparison_history."""
        if not matches:
            return
        now_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        with self._get_connection() as conn:
            records = [
                (
                    target_name,
                    m.reference_video_id,
                    float(m.target_start_sec),
                    float(m.target_end_sec),
                    float(m.ref_start_sec),
                    float(m.ref_end_sec),
                    float(m.duration_sec),
                    float(m.confidence_score),
                    m.match_type,
                    now_str,
                )
                for m in matches
            ]
            conn.executemany(
                """
                INSERT INTO comparison_history (
                    target_video_name, reference_video_name, target_start_sec,
                    target_end_sec, ref_start_sec, ref_end_sec, duration_sec,
                    confidence_score, match_type, scanned_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                records,
            )

    def get_comparison_history(self, limit: int = 50) -> List[Dict[str, Any]]:
        """Lấy lịch sử đối soát gần nhất."""
        with self._get_connection() as conn:
            rows = conn.execute(
                "SELECT * FROM comparison_history ORDER BY id DESC LIMIT ?",
                (limit,),
            ).fetchall()
            return [dict(r) for r in rows]

    def get_database_stats(self) -> Dict[str, Any]:
        """Thống kê tổng quan cơ sở dữ liệu Data/videodetection.db."""
        with self._get_connection() as conn:
            ref_count = conn.execute("SELECT COUNT(*) FROM videos WHERE category = 'reference'").fetchone()[0]
            target_count = conn.execute("SELECT COUNT(*) FROM videos WHERE category = 'target'").fetchone()[0]
            history_count = conn.execute("SELECT COUNT(*) FROM comparison_history").fetchone()[0]
            db_size_bytes = self.db_path.stat().st_size if self.db_path.exists() else 0

            return {
                "db_file": str(self.db_path),
                "db_size_mb": round(db_size_bytes / (1024 * 1024), 2),
                "total_reference_videos": ref_count,
                "total_target_videos": target_count,
                "total_match_history": history_count,
            }