"""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,
    )
    for model in (MusehubIssue, MusehubIssueComment):
        cols = {c.name for c in model.__table__.columns}
        assert "updated_at" in cols, f"{model.__name__} is missing updated_at"


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"


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

async def test_orphan_scan_returns_empty_when_no_orphans(
    db_session: AsyncSession,
) -> None:
    """scan_orphan_objects must return empty when all objects have at least one ref."""
    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")

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

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


async def test_orphan_scan_detects_objects_with_no_refs(
    db_session: AsyncSession,
) -> None:
    """scan_orphan_objects must find objects that have no row in musehub_object_refs."""
    from musehub.maintenance.orphan_scan import scan_orphan_objects
    from musehub.db import musehub_models as db_models

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

    # Insert an object with no corresponding ref row.
    obj = db_models.MusehubObject(
        object_id=orphan_obj_id,
        path="orphan.bin",
        size_bytes=4,
        disk_path="orphan.bin",
        storage_uri="local://orphan.bin",
    )
    db_session.add(obj)
    await db_session.commit()

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