"""SQLite dedup — table processed_domains pour éviter de rescraper les mêmes domaines."""

from __future__ import annotations

from datetime import datetime, timezone

import aiosqlite
import structlog

from src.config import DATA_DIR

log = structlog.get_logger()

DB_PATH = DATA_DIR / "leads.db"

CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS processed_domains (
    domain TEXT PRIMARY KEY,
    processed_at TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'synced',
    retry_count INTEGER NOT NULL DEFAULT 0
);
"""

CREATE_DAILY_STATS_SQL = """
CREATE TABLE IF NOT EXISTS daily_stats (
    date TEXT NOT NULL,
    niche TEXT NOT NULL,
    region TEXT NOT NULL,
    discovered INTEGER DEFAULT 0,
    qualified INTEGER DEFAULT 0,
    synced INTEGER DEFAULT 0,
    PRIMARY KEY (date, niche, region)
);
"""

CREATE_RESEND_CACHE_SQL = """
CREATE TABLE IF NOT EXISTS processed_resend_emails (
    resend_id TEXT PRIMARY KEY,
    email TEXT NOT NULL,
    action TEXT NOT NULL,
    processed_at TEXT NOT NULL
);
"""

CREATE_IMPORTED_FILES_SQL = """
CREATE TABLE IF NOT EXISTS imported_files (
    file_hash TEXT PRIMARY KEY,
    file_name TEXT NOT NULL,
    row_count INTEGER NOT NULL DEFAULT 0,
    imported_at TEXT NOT NULL,
    synced_count INTEGER NOT NULL DEFAULT 0,
    skipped_count INTEGER NOT NULL DEFAULT 0
);
"""

_MIGRATIONS = [
    "ALTER TABLE processed_domains ADD COLUMN status TEXT NOT NULL DEFAULT 'synced'",
    "ALTER TABLE processed_domains ADD COLUMN retry_count INTEGER NOT NULL DEFAULT 0",
]


async def _configure_pragmas(db: aiosqlite.Connection) -> None:
    """Configure les PRAGMAs SQLite pour la performance et la concurrence."""
    await db.execute("PRAGMA journal_mode=WAL")
    await db.execute("PRAGMA busy_timeout=5000")
    await db.execute("PRAGMA synchronous=NORMAL")


async def init_db() -> None:
    """Crée la base et la table si elles n'existent pas, puis applique les migrations."""
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    async with aiosqlite.connect(DB_PATH) as db:
        await _configure_pragmas(db)
        await db.execute(CREATE_TABLE_SQL)
        await db.execute(CREATE_DAILY_STATS_SQL)
        await db.execute(CREATE_RESEND_CACHE_SQL)
        await db.execute(CREATE_IMPORTED_FILES_SQL)
        # Migrations ALTER TABLE — ignorées si colonnes existent déjà
        for sql in _MIGRATIONS:
            try:
                await db.execute(sql)
            except Exception:  # noqa: BLE001 — duplicate column is expected
                pass
        await db.commit()


async def is_processed(domain: str) -> bool:
    """Vérifie si un domaine a déjà été traité."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT 1 FROM processed_domains WHERE domain = ?",
            (domain,),
        )
        row = await cursor.fetchone()
        return row is not None


async def get_processed_domains(domains: list[str]) -> set[str]:
    """Retourne l'ensemble des domaines déjà traités parmi la liste fournie."""
    if not domains:
        return set()

    async with aiosqlite.connect(DB_PATH) as db:
        # SQLite a une limite sur le nombre de variables (SQLITE_MAX_VARIABLE_NUMBER)
        # qui est généralement de 999 ou plus. Pour le pipeline (limit=100), on est bons.
        placeholders = ",".join("?" for _ in domains)
        cursor = await db.execute(
            f"SELECT domain FROM processed_domains WHERE domain IN ({placeholders})",
            domains,
        )
        rows = await cursor.fetchall()
        return {row[0] for row in rows}


async def get_processed_domains_with_status(
    domains: list[str],
) -> dict[str, tuple[str, int]]:
    """Retourne le status et retry_count des domaines déjà traités.

    Returns dict[domain, (status, retry_count)].
    """
    if not domains:
        return {}

    async with aiosqlite.connect(DB_PATH) as db:
        placeholders = ",".join("?" for _ in domains)
        cursor = await db.execute(
            f"SELECT domain, status, retry_count FROM processed_domains WHERE domain IN ({placeholders})",
            domains,
        )
        rows = await cursor.fetchall()
        return {row[0]: (row[1], row[2]) for row in rows}


async def mark_processed(domain: str, *, status: str = "synced") -> None:
    """Marque un domaine comme traité avec un status (synced, rejected, error)."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            """INSERT INTO processed_domains (domain, processed_at, status, retry_count)
               VALUES (?, ?, ?, 0)
               ON CONFLICT(domain) DO UPDATE SET
                   status = excluded.status,
                   processed_at = excluded.processed_at""",
            (domain, datetime.now(timezone.utc).isoformat(), status),
        )
        await db.commit()


async def increment_retry(domain: str) -> None:
    """Incrémente le retry_count d'un domaine en erreur avant re-traitement."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "UPDATE processed_domains SET retry_count = retry_count + 1 WHERE domain = ?",
            (domain,),
        )
        await db.commit()


async def get_processed_count() -> int:
    """Retourne le nombre de domaines traités."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute("SELECT COUNT(*) FROM processed_domains")
        row = await cursor.fetchone()
        return row[0] if row else 0


async def get_recent_domains(limit: int = 10) -> list[tuple[str, str]]:
    """Retourne les derniers domaines traités (domain, processed_at)."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT domain, processed_at FROM processed_domains ORDER BY processed_at DESC LIMIT ?",
            (limit,),
        )
        return await cursor.fetchall()


# --- Daily stats ---


async def record_daily_stats(
    date: str, niche: str, region: str, discovered: int, qualified: int, synced: int,
) -> None:
    """Enregistre les stats d'un run (INSERT OR REPLACE pour écraser si relancé)."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            """INSERT INTO daily_stats (date, niche, region, discovered, qualified, synced)
               VALUES (?, ?, ?, ?, ?, ?)
               ON CONFLICT(date, niche, region) DO UPDATE SET
                   discovered = discovered + excluded.discovered,
                   qualified = qualified + excluded.qualified,
                   synced = synced + excluded.synced""",
            (date, niche, region, discovered, qualified, synced),
        )
        await db.commit()


async def get_today_synced_count() -> int:
    """Retourne le nombre total de leads synced aujourd'hui."""
    today = datetime.now(timezone.utc).strftime("%Y-%m-%d")
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT COALESCE(SUM(synced), 0) FROM daily_stats WHERE date = ?",
            (today,),
        )
        row = await cursor.fetchone()
        return row[0] if row else 0


async def get_coverage_stats() -> dict[tuple[str, str], int]:
    """Retourne le total synced all-time par (niche, region)."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT niche, region, COALESCE(SUM(synced), 0) FROM daily_stats GROUP BY niche, region",
        )
        rows = await cursor.fetchall()
        return {(row[0], row[1]): row[2] for row in rows}


# --- Resend cache ---


async def get_processed_resend_ids() -> set[str]:
    """Retourne l'ensemble des resend_id déjà traités."""
    async with aiosqlite.connect(DB_PATH) as db:
        await _configure_pragmas(db)
        cursor = await db.execute("SELECT resend_id FROM processed_resend_emails")
        rows = await cursor.fetchall()
        return {row[0] for row in rows}


async def mark_resend_processed(resend_id: str, email: str, action: str) -> None:
    """Marque un email Resend comme traité (dead ou contacted)."""
    async with aiosqlite.connect(DB_PATH) as db:
        await _configure_pragmas(db)
        await db.execute(
            """INSERT INTO processed_resend_emails (resend_id, email, action, processed_at)
               VALUES (?, ?, ?, ?)
               ON CONFLICT(resend_id) DO NOTHING""",
            (resend_id, email, action, datetime.now(timezone.utc).isoformat()),
        )
        await db.commit()


# --- Imported files ---


async def is_file_imported(file_hash: str) -> bool:
    """Vérifie si un fichier CSV a déjà été importé (par hash SHA256)."""
    async with aiosqlite.connect(DB_PATH) as db:
        cursor = await db.execute(
            "SELECT 1 FROM imported_files WHERE file_hash = ?",
            (file_hash,),
        )
        return await cursor.fetchone() is not None


async def mark_file_imported(
    file_hash: str,
    file_name: str,
    row_count: int,
    synced_count: int,
    skipped_count: int,
) -> None:
    """Enregistre un fichier CSV comme importé."""
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            """INSERT INTO imported_files (file_hash, file_name, row_count, imported_at, synced_count, skipped_count)
               VALUES (?, ?, ?, ?, ?, ?)
               ON CONFLICT(file_hash) DO UPDATE SET
                   synced_count = excluded.synced_count,
                   skipped_count = excluded.skipped_count""",
            (file_hash, file_name, row_count, datetime.now(timezone.utc).isoformat(), synced_count, skipped_count),
        )
        await db.commit()