"""SQLite acceptance fixture for tenant-scoped active names and restore conflicts."""
import json
from pathlib import Path
import sqlite3


def database():
    db = sqlite3.connect(":memory:")
    db.execute("PRAGMA foreign_keys = ON")
    db.executescript("""
        CREATE TABLE projects (
            id TEXT PRIMARY KEY NOT NULL,
            tenant_id TEXT NOT NULL,
            name_key TEXT NOT NULL,
            deleted_at TEXT
        );
        CREATE UNIQUE INDEX active_project_name
            ON projects(tenant_id, name_key) WHERE deleted_at IS NULL;
        CREATE TABLE tasks (
            id TEXT PRIMARY KEY NOT NULL,
            project_id TEXT NOT NULL REFERENCES projects(id)
        );
    """)
    return db


def create(db, project_id, tenant, name):
    try:
        with db:
            db.execute("INSERT INTO projects VALUES (?, ?, ?, NULL)", (project_id, tenant, name))
        return "created"
    except sqlite3.IntegrityError:
        return "conflict"


def trash(db, project_id):
    with db:
        db.execute("UPDATE projects SET deleted_at = 'fixture-trash' WHERE id = ?", (project_id,))


def restore(db, project_id, tenant, name=None):
    # tenant is assumed to come from a trusted request context; authorization
    # itself is outside the fixture. A mismatched tenant must not mutate a row.
    try:
        with db:
            if name is None:
                cursor = db.execute(
                    "UPDATE projects SET deleted_at = NULL WHERE id = ? AND tenant_id = ? AND deleted_at IS NOT NULL",
                    (project_id, tenant),
                )
            else:
                cursor = db.execute(
                    "UPDATE projects SET name_key = ?, deleted_at = NULL WHERE id = ? AND tenant_id = ? AND deleted_at IS NOT NULL",
                    (name, project_id, tenant),
                )
            return "restored" if cursor.rowcount else "not-found"
    except sqlite3.IntegrityError:
        return "conflict"


def row(db, project_id):
    result = db.execute("SELECT id, tenant_id, name_key, deleted_at FROM projects WHERE id = ?", (project_id,)).fetchone()
    return dict(zip(["id", "tenant_id", "name_key", "deleted_at"], result))


def race(first):
    db = database()
    assert create(db, "old", "alpha", "reports") == "created"
    trash(db, "old")
    if first == "restore":
        restored = restore(db, "old", "alpha")
        created = create(db, "new", "alpha", "reports")
    else:
        created = create(db, "new", "alpha", "reports")
        restored = restore(db, "old", "alpha")
    active = db.execute("SELECT id FROM projects WHERE tenant_id = 'alpha' AND name_key = 'reports' AND deleted_at IS NULL").fetchall()
    assert len(active) == 1
    assert (restored, created) == (("restored", "conflict") if first == "restore" else ("conflict", "created"))
    result = {"case": f"race-{first}-first", "restore": restored, "create": created,
              "active_owner": active[0][0], "active_owner_count": len(active)}
    db.close()
    return result


def main():
    db = database()
    assert create(db, "original", "alpha", "reports") == "created"
    db.execute("INSERT INTO tasks VALUES ('task-1', 'original')")
    db.commit()
    trash(db, "original")
    cases = []
    assert create(db, "replacement", "alpha", "reports") == "created"
    cases.append({"case": "reuse-deleted-name", "outcome": "created", "new_id": "replacement"})
    before = row(db, "replacement")
    assert restore(db, "original", "alpha") == "conflict"
    assert row(db, "replacement") == before and row(db, "original")["deleted_at"] is not None
    cases.append({"case": "restore-occupied-name", "outcome": "conflict", "replacement_unchanged": True})
    assert restore(db, "original", "alpha", "reports-old") == "restored"
    assert db.execute("SELECT project_id FROM tasks WHERE id='task-1'").fetchone()[0] == "original"
    cases.append({"case": "restore-new-name", "outcome": "restored", "id": "original", "name": "reports-old", "task_reference_preserved": True})
    assert create(db, "other-tenant", "beta", "reports") == "created"
    cases.append({"case": "same-name-other-tenant", "outcome": "created"})
    assert create(db, "duplicate", "alpha", "reports") == "conflict"
    cases.append({"case": "duplicate-active-name", "outcome": "conflict"})
    trash(db, "replacement")
    assert create(db, "third", "alpha", "reports") == "created"
    trash(db, "third")
    count = db.execute("SELECT COUNT(*) FROM projects WHERE tenant_id='alpha' AND name_key='reports' AND deleted_at IS NOT NULL").fetchone()[0]
    assert count == 2
    cases.append({"case": "multiple-deleted-same-name", "outcome": "retained", "deleted_rows": count})
    before = row(db, "replacement")
    assert restore(db, "replacement", "beta") == "not-found"
    assert row(db, "replacement") == before
    cases.append({"case": "mismatched-tenant-restore", "outcome": "not-found", "row_unchanged": True})
    cases += [race("restore"), race("create")]
    assert len(cases) == 9
    result = {"engine": "Python standard-library sqlite3", "sqlite_version": sqlite3.sqlite_version,
              "policy": "Active name unique within tenant; restore never overwrites another row",
              "case_count": len(cases), "race_orders": 2, "cases": cases,
              "limits": ["SQLite execution, not a PostgreSQL test", "Race orders are serialized schedules, not overlapping transactions",
                         "Exact lowercase name keys supplied; Unicode normalization not tested",
                         "No authorization service, external resources, purge or retention workflow"]}
    Path(__file__).with_name("results.json").write_text(json.dumps(result, indent=2) + "\n")
    db.close()
    print("PASS: nine cases; both race orders have one active owner; ID references survive renamed restore.")


if __name__ == "__main__":
    main()