"""Tests for checklist section 5.1 — Database integrity.

Covers:
  - updated_at present on critical tables
  - FK constraints enforced (PostgreSQL)
  - Orphan object scan (scan and delete)
"""
from __future__ import annotations

import pytest
from sqlalchemy.ext.asyncio import AsyncSession

from tests.factories import create_repo


# ── updated_at on critical tables ─────────────────────────────────────────────

def test_musehub_repo_has_updated_at() -> None:
    from musehub.db.musehub_models import MusehubRepo
    cols = {c.name for c in MusehubRepo.__table__.columns}
    assert "updated_at" in cols, "MusehubRepo is missing updated_at column"


def test_musehub_proposal_has_updated_at() -> None:
    from musehub.db.musehub_models import MusehubProposal
    cols = {c.name for c in MusehubProposal.__table__.columns}
    assert "updated_at" in cols, "MusehubProposal is missing updated_at column"


def test_musehub_webhook_has_updated_at() -> None:
    from musehub.db.musehub_models import MusehubWebhook
    cols = {c.name for c in MusehubWebhook.__table__.columns}
    assert "updated_at" in cols, "MusehubWebhook is missing updated_at column"


def test_musehub_release_has_updated_at() -> None:
    from musehub.db.musehub_models import MusehubRelease
    cols = {c.name for c in MusehubRelease.__table__.columns}
    assert "updated_at" in cols, "MusehubRelease is missing updated_at column"


def test_existing_critical_tables_have_updated_at() -> None:
    """Spot-check that pre-existing updated_at columns are still present."""
    from musehub.db.musehub_models import (
        MusehubIssue,
        MusehubIssueComment,
        MusehubMilestone,
    )
    for model in (MusehubIssue, MusehubIssueComment, MusehubMilestone):
        cols = {c.name for c in model.__table__.columns}
        assert "updated_at" in cols, f"{model.__name__} is missing updated_at"


@pytest.mark.anyio
async def test_repo_updated_at_is_populated_on_create(
    db_session: AsyncSession,
) -> None:
    """A freshly created repo must have a non-null updated_at."""
    repo = await create_repo(db_session, slug="updated-at-test", owner="testuser")
    assert repo.updated_at is not None, "updated_at must be set on repo creation"


# ── Foreign key constraints ────────────────────────────────────────────────────


def test_fk_constraints_defined_on_object_table() -> None:
    """MusehubObject must declare a FK on repo_id pointing to musehub_repos."""
    from musehub.db.musehub_models import MusehubObject
    fk_targets = {
        fk.column.table.name
        for col in MusehubObject.__table__.columns
        for fk in col.foreign_keys
    }
    assert "musehub_repos" in fk_targets, (
        "MusehubObject.repo_id must have a FK to musehub_repos"
    )


def test_fk_ondelete_cascade_on_object_table() -> None:
    """MusehubObject FK on repo_id must use ondelete=CASCADE."""
    from musehub.db.musehub_models import MusehubObject
    repo_col = MusehubObject.__table__.c["repo_id"]
    for fk in repo_col.foreign_keys:
        assert fk.ondelete == "CASCADE", (
            f"MusehubObject.repo_id FK must have ondelete=CASCADE, got {fk.ondelete!r}"
        )


# ── Orphan object scan ─────────────────────────────────────────────────────────

@pytest.mark.anyio
async def test_orphan_scan_returns_empty_when_no_orphans(
    db_session: AsyncSession,
) -> None:
    """scan_orphan_objects must return an empty result when all objects have valid repos."""
    from musehub.maintenance.orphan_scan import scan_orphan_objects
    from musehub.db import musehub_models as db_models

    repo = await create_repo(db_session, slug="orphan-scan-clean", owner="testuser")

    obj = db_models.MusehubObject(
        object_id="sha256:" + "a" * 64,
        repo_id=repo.repo_id,
        path="test.bin",
        size_bytes=4,
        disk_path="test.bin",
        storage_uri="local://test.bin",
    )
    db_session.add(obj)
    await db_session.commit()

    result = await scan_orphan_objects(db_session)
    assert result.ok
    assert result.count == 0


@pytest.mark.anyio
async def test_orphan_scan_detects_objects_with_deleted_repo(
    db_session: AsyncSession,
) -> None:
    """scan_orphan_objects must find objects whose repo_id no longer exists.

    We disable FK enforcement temporarily so we can insert a row with a
    dangling repo_id — simulating what a direct DB edit or failed migration
    could leave behind.
    """
    from musehub.maintenance.orphan_scan import scan_orphan_objects
    from musehub.db import musehub_models as db_models
    from sqlalchemy import text

    orphan_obj_id = "sha256:" + "c" * 64

    # Temporarily disable FK triggers to insert the orphan row (PostgreSQL equivalent).
    await db_session.execute(text("SET session_replication_role = 'replica'"))
    await db_session.execute(
        db_models.MusehubObject.__table__.insert().values(
            object_id=orphan_obj_id,
            repo_id="nonexistent-repo-id-orphan-test",
            path="orphan.bin",
            size_bytes=4,
            disk_path="orphan.bin",
            storage_uri="local://orphan.bin",
        )
    )
    await db_session.commit()
    await db_session.execute(text("SET session_replication_role = DEFAULT"))

    result = await scan_orphan_objects(db_session)
    assert not result.ok, "Orphan scan should detect the dangling row"
    assert orphan_obj_id in result.orphaned_object_ids