import pytest
from sqlalchemy import func, select
from sqlalchemy.exc import IntegrityError

from backend.app.models import Artist, PatreonSeenMedia, Source

pytestmark = pytest.mark.integration


async def _source(db):
    artist = Artist(name="Seen Ledger Artist", slug="seen-ledger-artist")
    db.add(artist)
    await db.flush()
    source = Source(
        artist_id=artist.id,
        platform="patreon",
        url="https://www.patreon.com/seenledger",
    )
    db.add(source)
    await db.flush()
    return source.id


@pytest.mark.asyncio
async def test_patreon_seen_media_dedup(db):
    source_id = await _source(db)
    db.add(
        PatreonSeenMedia(
            source_id=source_id,
            filehash="0123456789abcdef0123456789abcdef",
            post_id="123456",
        )
    )
    await db.flush()

    seen_at = await db.scalar(
        select(PatreonSeenMedia.seen_at).where(
            PatreonSeenMedia.source_id == source_id
        )
    )
    assert seen_at is not None

    # A second sighting of the same (source_id, filehash) is rejected by the
    # unique constraint. Wrap in a savepoint so the IntegrityError doesn't
    # poison the outer transaction.
    with pytest.raises(IntegrityError):
        async with db.begin_nested():
            db.add(
                PatreonSeenMedia(
                    source_id=source_id,
                    filehash="0123456789abcdef0123456789abcdef",
                    post_id="654321",
                )
            )
            await db.flush()

    # Same filehash but a different post_id sentinel still inserts: the
    # dedup axis is (source_id, filehash), not post_id.
    db.add(
        PatreonSeenMedia(
            source_id=source_id,
            filehash="video:123456:7890",
            post_id="123456",
        )
    )
    await db.flush()

    count = await db.scalar(
        select(func.count(PatreonSeenMedia.id)).where(
            PatreonSeenMedia.source_id == source_id
        )
    )
    assert count == 2