"""
Job deduplication store — prevents the same job appearing twice across runs.

Uses SQLite so history persists across sessions.

Dedup strategy (two layers):
  1. URL-primary: exact URL match within last N days (original behavior, unchanged)
  2. Content fingerprint: SHA-256 of normalized (title+company) for cross-platform dedup.
     Catches cases where the same job is posted on LinkedIn AND Greenhouse (different URLs,
     same title+company). Added for ever-jobs integration (R2: dedup at 160+ platform scale).
"""

import sqlite3
import hashlib
from pathlib import Path
from datetime import datetime, timedelta

DB_PATH = Path("data/job_history.db")


def _conn() -> sqlite3.Connection:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(DB_PATH))
    conn.execute("""
        CREATE TABLE IF NOT EXISTS seen_jobs (
            url         TEXT PRIMARY KEY,
            title       TEXT,
            company     TEXT,
            platform    TEXT,
            first_seen  TEXT,
            last_seen   TEXT,
            run_count   INTEGER DEFAULT 1,
            content_fp  TEXT
        )
    """)
    conn.commit()
    try:
        conn.execute("ALTER TABLE seen_jobs ADD COLUMN content_fp TEXT")
        conn.commit()
    except Exception:
        pass
    return conn


def _canonical_fingerprint(title: str, company: str) -> str:
    key = f"{title.lower().strip()}|{company.lower().strip()}"
    return hashlib.sha256(key.encode("utf-8")).hexdigest()[:16]


def is_duplicate(url: str, days: int = 30) -> bool:
    if not url:
        return False
    cutoff = (datetime.now() - timedelta(days=days)).isoformat()
    with _conn() as conn:
        row = conn.execute(
            "SELECT last_seen FROM seen_jobs WHERE url = ? AND last_seen > ?",
            (url, cutoff)
        ).fetchone()
    return row is not None


def is_duplicate_by_content(title: str, company: str, days: int = 30) -> bool:
    if not title or not company:
        return False
    fp = _canonical_fingerprint(title, company)
    cutoff = (datetime.now() - timedelta(days=days)).isoformat()
    with _conn() as conn:
        row = conn.execute(
            "SELECT last_seen FROM seen_jobs WHERE content_fp = ? AND last_seen > ?",
            (fp, cutoff)
        ).fetchone()
    return row is not None


def mark_seen(url: str, title: str = "", company: str = "", platform: str = ""):
    if not url:
        return
    now = datetime.now().isoformat()
    fp  = _canonical_fingerprint(title, company) if (title and company) else None
    with _conn() as conn:
        existing = conn.execute(
            "SELECT run_count FROM seen_jobs WHERE url = ?", (url,)
        ).fetchone()
        if existing:
            conn.execute(
                "UPDATE seen_jobs SET last_seen=?, run_count=run_count+1, content_fp=? WHERE url=?",
                (now, fp, url)
            )
        else:
            conn.execute(
                "INSERT INTO seen_jobs (url, title, company, platform, first_seen, last_seen, content_fp) "
                "VALUES (?, ?, ?, ?, ?, ?, ?)",
                (url, title, company, platform, now, now, fp)
            )
        conn.commit()


def bulk_mark_seen(jobs: list):
    if not jobs:
        return
    now = datetime.now().isoformat()
    with _conn() as conn:
        for job in jobs:
            if isinstance(job, dict):
                url      = job.get("url", "")
                title    = job.get("title", "")
                company  = job.get("company", "")
                platform = job.get("platform", "")
            else:
                url      = getattr(job, "url", "")
                title    = getattr(job, "title", "")
                company  = getattr(job, "company", "")
                platform = getattr(job, "platform", "")
            if not url:
                continue
            fp = _canonical_fingerprint(title, company) if (title and company) else None
            existing = conn.execute(
                "SELECT run_count FROM seen_jobs WHERE url = ?", (url,)
            ).fetchone()
            if existing:
                conn.execute(
                    "UPDATE seen_jobs SET last_seen=?, run_count=run_count+1, content_fp=? WHERE url=?",
                    (now, fp, url)
                )
            else:
                conn.execute(
                    "INSERT INTO seen_jobs (url, title, company, platform, first_seen, last_seen, content_fp) "
                    "VALUES (?, ?, ?, ?, ?, ?, ?)",
                    (url, title, company, platform, now, now, fp)
                )
        conn.commit()


def get_stats() -> dict:
    with _conn() as conn:
        total = conn.execute("SELECT COUNT(*) FROM seen_jobs").fetchone()[0]
        recent = conn.execute(
            "SELECT COUNT(*) FROM seen_jobs WHERE last_seen > ?",
            ((datetime.now() - timedelta(days=7)).isoformat(),)
        ).fetchone()[0]
    return {"total_seen": total, "seen_last_7_days": recent, "db_path": str(DB_PATH)}


def clear_old_entries(days: int = 60):
    cutoff = (datetime.now() - timedelta(days=days)).isoformat()
    with _conn() as conn:
        deleted = conn.execute(
            "DELETE FROM seen_jobs WHERE last_seen < ?", (cutoff,)
        ).fetchone()
        conn.commit()