import time

from sqlalchemy import text

from app import db as appdb


def test_init_db_sets_version_and_is_idempotent():
    with appdb.engine.begin() as conn:
        version = conn.execute(text("SELECT version FROM schema_version")).fetchone()[0]
    assert version >= 3  # all shipped migrations applied
    # Re-running must not raise (e.g. duplicate-column on a re-applied ALTER).
    appdb.init_db()
    appdb.init_db()


def test_prune_old_job_runs_fk_safe():
    now = time.time()
    old = now - 100 * 86400
    with appdb.engine.begin() as conn:
        conn.execute(
            text(
                "INSERT INTO job_runs (id, job_key, started_at, status, triggered_by) "
                "VALUES (1,'old',:o,'success','schedule'),(2,'new',:n,'success','schedule')"
            ),
            {"o": old, "n": now},
        )
        conn.execute(
            text(
                "INSERT INTO manual_imports (id, source_path, imported_by, imported_at, status, job_run_id) "
                "VALUES (1,'x','me',:o,'done',1)"
            ),
            {"o": old},
        )

    deleted = appdb.prune_old_job_runs(max_age_days=90)
    assert deleted == 1
    with appdb.engine.begin() as conn:
        remaining = [r[0] for r in conn.execute(text("SELECT job_key FROM job_runs"))]
        mi_ref = conn.execute(text("SELECT job_run_id FROM manual_imports WHERE id=1")).fetchone()[0]
        mi_count = conn.execute(text("SELECT COUNT(*) FROM manual_imports")).fetchone()[0]
    assert remaining == ["new"]
    assert mi_ref is None  # reference nulled, not cascade-deleted
    assert mi_count == 1  # the import record itself is kept


def test_mark_interrupted_runs():
    now = time.time()
    with appdb.engine.begin() as conn:
        conn.execute(
            text(
                "INSERT INTO job_runs (id, job_key, started_at, status, triggered_by) "
                "VALUES (1,'stuck',:n,'running','schedule')"
            ),
            {"n": now},
        )
    appdb.mark_interrupted_runs()
    with appdb.engine.begin() as conn:
        row = conn.execute(text("SELECT status, finished_at FROM job_runs WHERE id=1")).fetchone()
    assert row[0] == "interrupted"
    assert row[1] is not None