import json
import random
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timezone
from pathlib import Path

from app.config import DEDUP_DB_PATH
from app.dedup.normalize import find_near_duplicate, normalize, text_hash

_SCHEMA_PATH = Path(__file__).resolve().parent / "schema.sql"


def connect() -> sqlite3.Connection:
    DEDUP_DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DEDUP_DB_PATH)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.executescript(_SCHEMA_PATH.read_text(encoding="utf-8"))
    return conn


@contextmanager
def get_connection():
    conn = connect()
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


def get_recent_facts(conn: sqlite3.Connection, category: str, recent_limit: int = 40, random_limit: int = 20) -> list[str]:
    rows = conn.execute(
        "SELECT fact_text FROM facts WHERE category = ? ORDER BY id DESC LIMIT ?",
        (category, recent_limit),
    ).fetchall()
    recent = [r[0] for r in rows]

    older_rows = conn.execute(
        "SELECT fact_text FROM facts WHERE category = ? ORDER BY id ASC LIMIT MAX(0, (SELECT COUNT(*) FROM facts WHERE category = ?) - ?)",
        (category, category, recent_limit),
    ).fetchall()
    older = [r[0] for r in older_rows]
    sample_size = min(random_limit, len(older))
    random_sample = random.sample(older, sample_size) if sample_size else []

    return recent + random_sample


def get_all_normalized(conn: sqlite3.Connection, category: str) -> list[str]:
    rows = conn.execute(
        "SELECT normalized_text FROM facts WHERE category = ?",
        (category,),
    ).fetchall()
    return [r[0] for r in rows]


def find_duplicate(conn: sqlite3.Connection, category: str, fact_text: str) -> str | None:
    normalized = normalize(fact_text)
    existing = get_all_normalized(conn, category)
    if normalized in existing:
        return normalized
    return find_near_duplicate(normalized, existing)


def insert_fact(
    conn: sqlite3.Connection,
    category: str,
    fact_text: str,
    title: str,
    description: str,
    hashtags: list[str],
) -> int:
    normalized = normalize(fact_text)
    cursor = conn.execute(
        """
        INSERT INTO facts (category, fact_text, normalized_text, text_hash, title, description, hashtags, created_at, status)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'generated')
        """,
        (
            category,
            fact_text,
            normalized,
            text_hash(normalized),
            title,
            description,
            json.dumps(hashtags, ensure_ascii=False),
            datetime.now(timezone.utc).isoformat(),
        ),
    )
    return cursor.lastrowid


def update_status(
    conn: sqlite3.Connection,
    fact_id: int,
    status: str,
    published_at: str | None = None,
    youtube_video_id: str | None = None,
) -> None:
    conn.execute(
        "UPDATE facts SET status = ?, published_at = COALESCE(?, published_at), youtube_video_id = COALESCE(?, youtube_video_id) WHERE id = ?",
        (status, published_at, youtube_video_id, fact_id),
    )