# ==============================================================================
# Author: Simon-Pierre Boucher <contact@spboucher.ai>
# File:   tests/test_db.py
# Desc:   Tests de la persistance — upsert, détection de changements, délai de
#         grâce (2 syncs), dérive, clé de déduplication inter-sources.
# ==============================================================================
from __future__ import annotations

from sortika import db
from sortika.schema import Event


def _ev(eid="1", title="Fête au parc", price="Gratuit", source="sitq"):
    return Event(source=source, external_id=eid, url=f"https://x/{eid}",
                 title=title, city="Québec", tourist_region="Québec",
                 start_date="2027-01-15", price_label=price).finalize()


def test_sync_lifecycle(tmp_path):
    con = db.connect(tmp_path / "t.db")

    s = db.sync_source(con, "sitq", [_ev()])
    assert (s["added"], s["removed"]) == (1, 0)

    # inchangé
    s = db.sync_source(con, "sitq", [_ev()])
    assert (s["added"], s["updated"], s["unchanged"]) == (0, 0, 1)

    # changement de contenu détecté
    s = db.sync_source(con, "sitq", [_ev(price="Adulte 10 $")])
    assert s["updated"] == 1
    row = con.execute("SELECT is_free, price_min FROM events").fetchone()
    assert (row["is_free"], row["price_min"]) == (0, 10.0)

    # disparition : grâce au 1er raté, retrait au 2e
    s = db.sync_source(con, "sitq", [])
    assert s["removed"] == 0
    s = db.sync_source(con, "sitq", [])
    assert s["removed"] == 1
    assert con.execute("SELECT active FROM events").fetchone()[0] == 0


def test_drift_suspends_removals(tmp_path):
    con = db.connect(tmp_path / "t.db")
    batch = [_ev(eid=str(i), title=f"Événement {i}") for i in range(20)]
    for _ in range(3):
        db.sync_source(con, "sitq", batch)
    # chute brutale à 2 événements → alerte, aucun retrait
    s = db.sync_source(con, "sitq", batch[:2])
    assert "alert" in s and s["removed"] == 0
    n = con.execute("SELECT COUNT(*) FROM events WHERE active=1").fetchone()[0]
    assert n == 20


def test_start_time_and_artists_persisted(tmp_path):
    con = db.connect(tmp_path / "t.db")
    ev = Event(source="evenko", external_id="s1", url="https://x/s1",
               title="Deep Purple", city="Montréal",
               start_date="2027-01-15", start_time="19h30",
               artists=["Deep Purple", " deep purple "]).finalize()
    assert ev.start_time == "19:30"          # normalisée "HH:MM"
    assert ev.artists == ["Deep Purple"]     # dédupliqués
    db.sync_source(con, "evenko", [ev])
    row = con.execute("SELECT start_time, artists FROM events").fetchone()
    assert row["start_time"] == "19:30"
    assert row["artists"] == '["Deep Purple"]'


def test_migration_adds_columns(tmp_path):
    p = tmp_path / "old.db"
    con = db.connect(p)
    # simuler une base d'avant la vague 2 / Phase 2 (sans colonnes additives)
    for col in ("start_time", "artists", "end_time", "status", "quarantine"):
        con.execute(f"ALTER TABLE events DROP COLUMN {col}")
    con.commit()
    con.close()
    con = db.connect(p)        # la connexion migre (colonnes additives)
    cols = {r["name"] for r in con.execute("PRAGMA table_info(events)")}
    assert {"start_time", "artists", "end_time", "status", "quarantine"} <= cols


def test_end_time_and_status_persisted(tmp_path):
    con = db.connect(tmp_path / "t.db")
    ev = Event(source="eventbrite", external_id="e1", url="https://x/e1",
               title="Atelier", city="Montréal", start_date="2027-01-15",
               start_time="19:00", end_time="21h30",
               status="CANCELLED").finalize()
    assert ev.end_time == "21:30"            # normalisée "HH:MM"
    assert ev.status == "cancelled"          # taxonomie interne
    db.sync_source(con, "eventbrite", [ev])
    row = con.execute("SELECT end_time, status FROM events").fetchone()
    assert row["end_time"] == "21:30" and row["status"] == "cancelled"


def test_archive_past_events(tmp_path):
    """Règle Phase 2 : passé = archivé (active=0), jamais supprimé — et un
    passé archivé n'est pas réactivé par un sync « unchanged »."""
    con = db.connect(tmp_path / "t.db")
    past = Event(source="sitq", external_id="p1", url="https://x/p1",
                 title="Marché passé", city="Québec",
                 start_date="2020-05-01", end_date="2020-05-02").finalize()
    future = _ev(eid="f1", title="Événement futur")
    db.sync_source(con, "sitq", [past, future])
    n = db.archive_past_events(con)
    assert n == 1
    rows = {r["uid"]: r["active"] for r in
            con.execute("SELECT uid, active FROM events")}
    assert rows["sitq:p1"] == 0 and rows["sitq:f1"] == 1
    # la source publie toujours le passé (inchangé) → il RESTE archivé
    db.sync_source(con, "sitq", [past, future])
    assert con.execute("SELECT active FROM events WHERE uid='sitq:p1'"
                       ).fetchone()[0] == 0


def test_quarantine_rules(tmp_path):
    """Anti-aberrations : > 2 ans ou hors QC → quarantaine (réintégrable),
    comptée à part et absente des vues publiées."""
    con = db.connect(tmp_path / "t.db")
    far = Event(source="sitq", external_id="q1", url="https://x/q1",
                title="Trop loin", city="Québec",
                start_date="2031-01-01").finalize()
    hors = Event(source="bandsintown", external_id="q2", url="https://x/q2",
                 title="Show à Toronto", city="Toronto",
                 start_date="2026-10-01").finalize()
    ok = _ev(eid="q3", title="Événement sain")
    db.sync_source(con, "sitq", [far, ok])
    db.sync_source(con, "bandsintown", [hors])
    rows = {r["uid"]: r["quarantine"] for r in
            con.execute("SELECT uid, quarantine FROM events")}
    assert rows["sitq:q1"] and "2 ans" in rows["sitq:q1"]
    assert rows["bandsintown:q2"] and "hors Québec" in rows["bandsintown:q2"]
    assert rows["sitq:q3"] is None


def test_future_counts_and_alerts(tmp_path, monkeypatch):
    con = db.connect(tmp_path / "t.db")
    monkeypatch.setattr(db, "_FUTURE_STATE", tmp_path / "future_counts.json")
    past = Event(source="laval", external_id="m1", url="https://x/m1",
                 title="Activité terminée", city="Laval",
                 start_date="2025-12-09").finalize()
    db.sync_source(con, "laval", [past])
    db.sync_source(con, "sitq", [_ev(eid="ok")])
    counts = db.future_counts(con)
    assert counts.get("sitq") == 1 and "laval" not in counts
    alerts = db.check_future_health(con)
    assert any(a.startswith("laval") and "0 événement futur" in a
               for a in alerts)


def test_dedup_key_seances_distinctes():
    """Même titre/ville/jour mais heures différentes = séances distinctes
    (clés différentes) ; même heure = fusion inter-sources conservée."""
    a = Event(source="lepointdevente", external_id="1", url="https://x/1",
              title="Parcours découverte", city="Québec",
              start_date="2026-09-03", start_time="09:30").finalize()
    b = Event(source="lepointdevente", external_id="2", url="https://x/2",
              title="Parcours découverte", city="Québec",
              start_date="2026-09-03", start_time="14:30").finalize()
    c = Event(source="lavitrine", external_id="3", url="https://x/3",
              title="Parcours découverte", city="Québec",
              start_date="2026-09-03", start_time="09:30").finalize()
    assert a.dedup_key() != b.dedup_key()    # séances préservées
    assert a.dedup_key() == c.dedup_key()    # fusion inter-sources conservée


def test_dedup_key_cross_source():
    a = _ev(source="sitq", title="Grand marché de Noël")
    b = Event(source="montreal", external_id="99", url="https://y",
              title="  GRAND MARCHÉ de Noël ", city="Québec",
              start_date="2027-01-15").finalize()
    assert a.dedup_key() == b.dedup_key()
    assert a.dedup_key() != _ev(title="Autre événement").dedup_key()