"""Tests for the workshop-overlay schema additions: typed Overlay, partial
unique indexes, WorkshopItem registry, and overlay_workshop_items association.
"""
import pytest
from sqlalchemy.exc import IntegrityError

from l4d2web.db import init_db, session_scope
from l4d2web.models import (
    Job,
    Overlay,
    OverlayWorkshopItem,
    User,
    WorkshopItem,
)


@pytest.fixture
def db(tmp_path, monkeypatch):
    monkeypatch.setenv("DATABASE_URL", f"sqlite:///{tmp_path/'workshop.db'}")
    init_db()
    yield


def _make_user(username: str) -> int:
    with session_scope() as s:
        user = User(username=username, password_digest="x")
        s.add(user)
        s.flush()
        return user.id


def test_overlay_has_type_and_user_id(db) -> None:
    with session_scope() as s:
        s.add(Overlay(name="standard", path="standard"))
        s.flush()
        row = s.query(Overlay).filter_by(name="standard").one()
        assert row.type == "workshop"
        assert row.user_id is None


def test_overlay_has_script_columns(db) -> None:
    with session_scope() as s:
        s.add(Overlay(name="defaulted", path="1"))
        s.flush()
        row = s.query(Overlay).filter_by(name="defaulted").one()
        assert row.script == ""
        assert row.last_build_status == ""


def test_two_system_overlays_with_same_name_are_rejected(db) -> None:
    with session_scope() as s:
        s.add(Overlay(name="shared", path="shared", type="l4d2center_maps", user_id=None))
        s.flush()

    with pytest.raises(IntegrityError):
        with session_scope() as s:
            s.add(Overlay(name="shared", path="other", type="cedapug_maps", user_id=None))
            s.flush()


def test_two_users_can_share_workshop_overlay_name(db) -> None:
    alice_id = _make_user("alice")
    bob_id = _make_user("bob")

    with session_scope() as s:
        s.add(Overlay(name="my-maps", path="1", type="workshop", user_id=alice_id))
        s.add(Overlay(name="my-maps", path="2", type="workshop", user_id=bob_id))
        s.flush()

    with session_scope() as s:
        rows = s.query(Overlay).filter_by(name="my-maps").all()
        assert {r.user_id for r in rows} == {alice_id, bob_id}


def test_same_user_cannot_have_duplicate_workshop_name(db) -> None:
    user_id = _make_user("alice")

    with session_scope() as s:
        s.add(Overlay(name="dupe", path="1", type="workshop", user_id=user_id))
        s.flush()

    with pytest.raises(IntegrityError):
        with session_scope() as s:
            s.add(Overlay(name="dupe", path="2", type="workshop", user_id=user_id))
            s.flush()


def test_workshop_item_steam_id_is_unique(db) -> None:
    with session_scope() as s:
        s.add(WorkshopItem(steam_id="123", title="Map A"))
        s.flush()

    with pytest.raises(IntegrityError):
        with session_scope() as s:
            s.add(WorkshopItem(steam_id="123", title="Map A duplicate"))
            s.flush()


def test_overlay_workshop_item_unique_per_overlay(db) -> None:
    user_id = _make_user("alice")

    with session_scope() as s:
        ov = Overlay(name="my-maps", path="1", type="workshop", user_id=user_id)
        wi = WorkshopItem(steam_id="555", title="A")
        s.add_all([ov, wi])
        s.flush()
        s.add(OverlayWorkshopItem(overlay_id=ov.id, workshop_item_id=wi.id))
        s.flush()
        overlay_id = ov.id
        workshop_item_id = wi.id

    with pytest.raises(IntegrityError):
        with session_scope() as s:
            s.add(
                OverlayWorkshopItem(
                    overlay_id=overlay_id, workshop_item_id=workshop_item_id
                )
            )
            s.flush()


def test_deleting_overlay_cascades_associations_but_not_workshop_items(db) -> None:
    user_id = _make_user("alice")

    with session_scope() as s:
        ov = Overlay(name="my-maps", path="1", type="workshop", user_id=user_id)
        wi = WorkshopItem(steam_id="777", title="A")
        s.add_all([ov, wi])
        s.flush()
        s.add(OverlayWorkshopItem(overlay_id=ov.id, workshop_item_id=wi.id))
        s.flush()
        overlay_id = ov.id

    # Delete via raw connection to actually exercise ON DELETE CASCADE / RESTRICT.
    from l4d2web.db import get_engine

    engine = get_engine()
    with engine.begin() as conn:
        conn.exec_driver_sql("PRAGMA foreign_keys=ON")
        conn.exec_driver_sql(f"DELETE FROM overlays WHERE id = {overlay_id}")

    with session_scope() as s:
        assert s.query(OverlayWorkshopItem).count() == 0
        assert s.query(WorkshopItem).filter_by(steam_id="777").count() == 1


def test_job_has_overlay_id_column(db) -> None:
    user_id = _make_user("alice")

    with session_scope() as s:
        ov = Overlay(name="my-maps", path="1", type="workshop", user_id=user_id)
        s.add(ov)
        s.flush()
        s.add(
            Job(
                user_id=user_id,
                server_id=None,
                overlay_id=ov.id,
                operation="build_overlay",
                state="queued",
            )
        )
        s.flush()

    with session_scope() as s:
        job = s.query(Job).filter_by(operation="build_overlay").one()
        assert job.overlay_id is not None
        assert job.server_id is None


def test_overlay_id_does_not_reuse_after_delete(db) -> None:
    """SQLite AUTOINCREMENT must guarantee deleted IDs are never reused."""
    user_id = _make_user("alice")
    with session_scope() as s:
        s.add(Overlay(name="first", path="1", type="workshop", user_id=user_id))
        s.add(Overlay(name="second", path="2", type="workshop", user_id=user_id))
        s.flush()
        ids_before = sorted(o.id for o in s.query(Overlay).all())
        last_id = ids_before[-1]

    with session_scope() as s:
        last = s.query(Overlay).filter_by(id=last_id).one()
        s.delete(last)
        s.flush()

    with session_scope() as s:
        s.add(Overlay(name="third", path="3", type="workshop", user_id=user_id))
        s.flush()
        new_id = s.query(Overlay).filter_by(name="third").one().id

    assert new_id > last_id, (
        f"AUTOINCREMENT should never reuse IDs, but got {new_id} after deleting {last_id}"
    )
