"""FC-3k: cleanup_service unit tests.

Mutations go against real Postgres (db_sync fixture). File-system
side effects use tmp_path. Assertions on mutated rows use COLUMN
SELECTS per reference_async_coredml_test_assertions — never
re-read ORM attributes after a service mutates and re-fetches.
"""
from datetime import UTC, datetime

import pytest
from sqlalchemy import func, select

from backend.app.models import (
    Artist,
    ImageProvenance,
    ImageRecord,
    PatreonSeenMedia,
    Post,
    PostAttachment,
    Source,
    Tag,
    TagKind,
)
from backend.app.models.series_chapter import SeriesChapter
from backend.app.models.series_page import SeriesPage
from backend.app.models.tag import image_tag
from backend.app.services import cleanup_service

pytestmark = pytest.mark.integration


def _make_image(db_sync, *, artist, path, sha256, size=1000, thumb=None):
    img = ImageRecord(
        artist_id=artist.id,
        path=path,
        sha256=sha256,
        size_bytes=size,
        mime="image/jpeg",
        origin="imported_filesystem",
        thumbnail_path=thumb,
    )
    db_sync.add(img)
    db_sync.flush()
    return img


def _make_artist(db_sync, *, slug="aria", name="Aria"):
    a = Artist(name=name, slug=slug)
    db_sync.add(a)
    db_sync.flush()
    return a


def _make_tag(db_sync, *, name, kind=TagKind.general):
    t = Tag(name=name, kind=kind)
    db_sync.add(t)
    db_sync.flush()
    return t


# --- project_artist_cascade -----------------------------------------


def test_project_artist_cascade_returns_zeroes_for_empty_artist(db_sync):
    _make_artist(db_sync, slug="empty")
    db_sync.commit()
    result = cleanup_service.project_artist_cascade(db_sync, slug="empty")
    assert result["artist"]["slug"] == "empty"
    assert result["projected"] == {
        "images": 0,
        "sources": 0,
        "thumbs": 0,
        "import_tasks": 0,
        "bytes_on_disk": 0,
    }


def test_project_artist_cascade_counts_images_and_thumbs_and_bytes(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="counted")
    _make_image(
        db_sync, artist=a, path=str(tmp_path / "a.jpg"),
        sha256="a" * 64, size=1000, thumb=str(tmp_path / "a.thumb"),
    )
    _make_image(
        db_sync, artist=a, path=str(tmp_path / "b.jpg"),
        sha256="b" * 64, size=2500, thumb=None,
    )
    db_sync.commit()
    result = cleanup_service.project_artist_cascade(db_sync, slug="counted")
    assert result["projected"]["images"] == 2
    assert result["projected"]["thumbs"] == 1
    assert result["projected"]["bytes_on_disk"] == 3500


def test_project_artist_cascade_raises_on_unknown_slug(db_sync):
    with pytest.raises(LookupError):
        cleanup_service.project_artist_cascade(db_sync, slug="nope")


# --- project_bulk_image_delete --------------------------------------


def test_project_bulk_image_delete_separates_found_from_missing(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="bd")
    i1 = _make_image(
        db_sync, artist=a, path=str(tmp_path / "1.jpg"),
        sha256="1" * 64, size=10, thumb="t1",
    )
    i2 = _make_image(
        db_sync, artist=a, path=str(tmp_path / "2.jpg"),
        sha256="2" * 64, size=20, thumb=None,
    )
    db_sync.commit()
    result = cleanup_service.project_bulk_image_delete(
        db_sync, image_ids=[i1.id, i2.id, 9_999_999],
    )
    assert result["images_found"] == 2
    assert result["thumbs_to_unlink"] == 1
    assert result["bytes_on_disk"] == 30
    assert result["missing_ids"] == [9_999_999]


def test_project_bulk_image_delete_empty_input(db_sync):
    result = cleanup_service.project_bulk_image_delete(db_sync, image_ids=[])
    assert result == {
        "images_found": 0,
        "thumbs_to_unlink": 0,
        "bytes_on_disk": 0,
        "missing_ids": [],
    }


# --- count_tag_associations / find_unused_tags ----------------------


def test_count_tag_associations_counts_image_tag_rows(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="ta")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "x.jpg"), sha256="c" * 64,
    )
    tag = _make_tag(db_sync, name="cyberpunk")
    db_sync.execute(image_tag.insert().values(
        image_record_id=img.id, tag_id=tag.id,
    ))
    db_sync.commit()
    assert cleanup_service.count_tag_associations(db_sync, tag_id=tag.id) == 1


def test_count_tag_associations_fandom_includes_character_images(db_sync, tmp_path):
    # Deleting a fandom affects images carrying its characters too, so the
    # Tier-B blast-radius prompt must count them — not just direct image_tag
    # rows on the fandom (a fandom usually has zero of those). DISTINCT: an
    # image with both the fandom and its character counts once.
    a = _make_artist(db_sync, slug="fc")
    img0 = _make_image(db_sync, artist=a, path=str(tmp_path / "0.jpg"), sha256="e" * 64)
    img1 = _make_image(db_sync, artist=a, path=str(tmp_path / "1.jpg"), sha256="f" * 64)
    fandom = _make_tag(db_sync, name="Creux", kind=TagKind.fandom)
    char = _make_tag(db_sync, name="OcChar", kind=TagKind.character)
    char.fandom_id = fandom.id
    db_sync.flush()
    db_sync.execute(image_tag.insert().values([
        {"image_record_id": img0.id, "tag_id": char.id},
        {"image_record_id": img1.id, "tag_id": char.id},
        {"image_record_id": img1.id, "tag_id": fandom.id},
    ]))
    db_sync.commit()
    assert cleanup_service.count_tag_associations(db_sync, tag_id=fandom.id) == 2


def test_find_unused_tags_returns_only_unreferenced(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="fu")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "y.jpg"), sha256="d" * 64,
    )
    used = _make_tag(db_sync, name="used")
    _make_tag(db_sync, name="aaa-unused")
    _make_tag(db_sync, name="zzz-unused")
    db_sync.execute(image_tag.insert().values(
        image_record_id=img.id, tag_id=used.id,
    ))
    db_sync.commit()
    result = cleanup_service.find_unused_tags(db_sync)
    names = [t.name for t in result]
    assert "used" not in names
    assert "aaa-unused" in names
    assert "zzz-unused" in names
    # Sorted by name ascending.
    assert names.index("aaa-unused") < names.index("zzz-unused")


def test_find_unused_tags_excludes_fandom_referenced_by_character(db_sync):
    # A fandom is never on an image — a character carries it via fandom_id — so
    # an assigned fandom must NOT be flagged unused (deleting it SET-NULLs the
    # fandom off its characters). Operator-flagged 2026-06-08.
    fandom = _make_tag(db_sync, name="Creux", kind=TagKind.fandom)
    char = _make_tag(db_sync, name="OcChar", kind=TagKind.character)
    char.fandom_id = fandom.id
    _make_tag(db_sync, name="NobodysFandom", kind=TagKind.fandom)
    db_sync.commit()

    names = [t.name for t in cleanup_service.find_unused_tags(db_sync)]
    assert "Creux" not in names          # referenced by a character → kept
    assert "NobodysFandom" in names      # genuinely orphaned → still swept


def test_find_unused_tags_excludes_series_with_a_divider(db_sync):
    # A series with a chapter divider (which anchors to a page) is in use.
    a = _make_artist(db_sync)
    series = _make_tag(db_sync, name="DivSeries", kind=TagKind.series)
    img = _make_image(
        db_sync, artist=a, path="/tmp/fc_divser.jpg", sha256="e" * 64,
    )
    page = SeriesPage(series_tag_id=series.id, image_id=img.id, page_number=1)
    db_sync.add(page)
    db_sync.flush()
    db_sync.add(SeriesChapter(series_tag_id=series.id, anchor_page_id=page.id))
    db_sync.commit()

    names = [t.name for t in cleanup_service.find_unused_tags(db_sync)]
    assert "DivSeries" not in names


# --- unlink_image_files ---------------------------------------------


def test_unlink_image_files_removes_original_and_thumbnail(db_sync, tmp_path):
    original = tmp_path / "orig.jpg"
    original.write_bytes(b"x")
    custom_thumb = tmp_path / "custom.thumb"
    custom_thumb.write_bytes(b"x")
    conv_thumb = tmp_path / "thumbs" / "aaa" / ("a" * 64 + ".jpg")
    conv_thumb.parent.mkdir(parents=True)
    conv_thumb.write_bytes(b"x")

    img = ImageRecord(
        artist_id=None, path=str(original), sha256="a" * 64,
        size_bytes=1, thumbnail_path=str(custom_thumb),
    )
    result = cleanup_service.unlink_image_files(img, tmp_path)
    assert result == {"original": True, "thumbnail": True}
    assert not original.exists()
    assert not custom_thumb.exists()
    assert not conv_thumb.exists()


def test_unlink_image_files_missing_files_count_as_success(tmp_path):
    img = ImageRecord(
        artist_id=None, path=str(tmp_path / "nope.jpg"),
        sha256="b" * 64, size_bytes=1, thumbnail_path=None,
    )
    result = cleanup_service.unlink_image_files(img, tmp_path)
    assert result == {"original": True, "thumbnail": False}


# --- delete_artist_cascade ------------------------------------------


def test_delete_artist_cascade_removes_images_and_artist_row(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="cas")
    for i in range(3):
        f = tmp_path / f"img{i}.jpg"
        f.write_bytes(b"x")
        _make_image(
            db_sync, artist=a, path=str(f),
            sha256=f"{i:064x}", size=10,
        )
    db_sync.commit()
    artist_id = a.id

    result = cleanup_service.delete_artist_cascade(
        db_sync, artist_id=artist_id, images_root=tmp_path,
    )
    assert result["artist"]["slug"] == "cas"
    assert result["summary"]["images_deleted"] == 3
    assert result["summary"]["files_deleted"] == 3

    # Column-select assertions per reference_async_coredml_test_assertions.
    surviving_artist = db_sync.execute(
        select(func.count(Artist.id)).where(Artist.id == artist_id)
    ).scalar_one()
    surviving_images = db_sync.execute(
        select(func.count(ImageRecord.id))
        .where(ImageRecord.artist_id == artist_id)
    ).scalar_one()
    assert surviving_artist == 0
    assert surviving_images == 0


def test_delete_artist_cascade_idempotent_on_missing(db_sync, tmp_path):
    result = cleanup_service.delete_artist_cascade(
        db_sync, artist_id=9_999_999, images_root=tmp_path,
    )
    assert result["artist"] is None
    assert result["summary"]["images_deleted"] == 0


# --- delete_images --------------------------------------------------


def test_delete_images_removes_rows_and_files(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="di")
    f1 = tmp_path / "1.jpg"
    f1.write_bytes(b"x")
    f2 = tmp_path / "2.jpg"
    f2.write_bytes(b"x")
    i1 = _make_image(db_sync, artist=a, path=str(f1), sha256="1" * 64)
    i2 = _make_image(db_sync, artist=a, path=str(f2), sha256="2" * 64)
    db_sync.commit()

    result = cleanup_service.delete_images(
        db_sync, image_ids=[i1.id, i2.id, 9_999_999],
        images_root=tmp_path,
    )
    assert result["images_deleted"] == 2
    assert result["files_deleted"] == 2
    assert result["missing_ids"] == [9_999_999]

    surviving = db_sync.execute(
        select(func.count(ImageRecord.id))
        .where(ImageRecord.id.in_([i1.id, i2.id]))
    ).scalar_one()
    assert surviving == 0


# --- delete_tag -----------------------------------------------------


def test_delete_tag_cascades_associations(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="dt")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "x.jpg"), sha256="d" * 64,
    )
    tag = _make_tag(db_sync, name="doomed")
    db_sync.execute(image_tag.insert().values(
        image_record_id=img.id, tag_id=tag.id,
    ))
    db_sync.commit()
    tag_id = tag.id

    result = cleanup_service.delete_tag(db_sync, tag_id=tag_id)
    assert result["deleted"]["id"] == tag_id
    assert result["associations_removed"] == 1

    surviving_tag = db_sync.execute(
        select(func.count(Tag.id)).where(Tag.id == tag_id)
    ).scalar_one()
    surviving_assoc = db_sync.execute(
        select(func.count())
        .select_from(image_tag).where(image_tag.c.tag_id == tag_id)
    ).scalar_one()
    assert surviving_tag == 0
    assert surviving_assoc == 0


def test_delete_tag_raises_on_unknown_id(db_sync):
    with pytest.raises(LookupError):
        cleanup_service.delete_tag(db_sync, tag_id=9_999_999)


# --- prune_unused_tags ----------------------------------------------


def test_prune_unused_tags_dry_run_returns_count_and_names(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="pu")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "z.jpg"), sha256="e" * 64,
    )
    used = _make_tag(db_sync, name="kept")
    _make_tag(db_sync, name="prune-me-1")
    _make_tag(db_sync, name="prune-me-2")
    db_sync.execute(image_tag.insert().values(
        image_record_id=img.id, tag_id=used.id,
    ))
    db_sync.commit()

    result = cleanup_service.prune_unused_tags(db_sync, dry_run=True)
    assert result["count"] == 2
    assert "prune-me-1" in result["sample_names"]
    assert "prune-me-2" in result["sample_names"]
    # Nothing actually deleted. Non-system only — the seeded hygiene tags
    # (#128) are exempt from pruning and would inflate a whole-table count.
    surviving = db_sync.execute(
        select(func.count(Tag.id)).where(Tag.is_system.is_(False))
    ).scalar_one()
    assert surviving == 3


def test_prune_unused_tags_commit_deletes_them(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="pu2")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "k.jpg"), sha256="f" * 64,
    )
    used = _make_tag(db_sync, name="kept")
    _make_tag(db_sync, name="bye")
    db_sync.execute(image_tag.insert().values(
        image_record_id=img.id, tag_id=used.id,
    ))
    db_sync.commit()

    result = cleanup_service.prune_unused_tags(db_sync, dry_run=False)
    assert result["deleted"] == 1

    surviving_names = db_sync.execute(select(Tag.name)).scalars().all()
    assert "kept" in surviving_names
    assert "bye" not in surviving_names


def test_prune_unused_tags_commit_spares_fandom_and_chaptered_series(
    db_sync, tmp_path,
):
    # The LIVE delete (not just the preview) must use the same predicate — it
    # previously had only the image_tag/series_page checks and deleted every
    # fandom the preview correctly excluded (operator-flagged 2026-06-08, real
    # data loss). Both dry-run count and the commit must spare referenced tags.
    fandom = _make_tag(db_sync, name="Creux", kind=TagKind.fandom)
    char = _make_tag(db_sync, name="OcChar", kind=TagKind.character)
    char.fandom_id = fandom.id
    # The character itself is used (tagged on an image) — that is the real-world
    # shape: characters survive on their image_tag rows, and the fandom they
    # point at must survive with them.
    a = _make_artist(db_sync, slug="psf")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "c.jpg"), sha256="d" * 64,
    )
    db_sync.execute(image_tag.insert().values(
        image_record_id=img.id, tag_id=char.id,
    ))
    series = _make_tag(db_sync, name="EmptySeries", kind=TagKind.series)
    simg = _make_image(
        db_sync, artist=a, path=str(tmp_path / "s.jpg"), sha256="e" * 64,
    )
    db_sync.add(SeriesPage(
        series_tag_id=series.id, image_id=simg.id, page_number=1
    ))
    _make_tag(db_sync, name="GenuinelyUnused")
    db_sync.commit()

    # dry-run count agrees with the delete: only the 1 truly-unused tag.
    assert cleanup_service.prune_unused_tags(db_sync, dry_run=True)["count"] == 1
    result = cleanup_service.prune_unused_tags(db_sync, dry_run=False)
    assert result["deleted"] == 1

    surviving = db_sync.execute(select(Tag.name)).scalars().all()
    assert "OcChar" in surviving         # used character
    assert "Creux" in surviving          # fandom referenced by a character
    assert "EmptySeries" in surviving    # series referenced by a chapter
    assert "GenuinelyUnused" not in surviving


def test_prune_bare_posts_spares_linked_and_deletes_bare(db_sync, tmp_path):
    # The LIVE delete (not just the preview) shares _bare_post_conditions, so a
    # post is spared if it has ANY link — a primary image, a provenance (cross-
    # posted/duplicate) image, or an attachment — and only the truly-bare shell
    # is deleted (the empty-post flood, operator-flagged 2026-06-08).
    a = _make_artist(db_sync, slug="pb")
    img = _make_image(db_sync, artist=a, path=str(tmp_path / "x.jpg"), sha256="a" * 64)

    bare = Post(artist_id=a.id, external_post_id="bare1")
    has_primary = Post(artist_id=a.id, external_post_id="prim")
    has_prov = Post(artist_id=a.id, external_post_id="prov")
    has_att = Post(artist_id=a.id, external_post_id="att")
    db_sync.add_all([bare, has_primary, has_prov, has_att])
    db_sync.flush()

    img.primary_post_id = has_primary.id
    db_sync.add(ImageProvenance(image_record_id=img.id, post_id=has_prov.id))
    db_sync.add(PostAttachment(
        post_id=has_att.id, artist_id=a.id, sha256="b" * 64,
        path="/store/b", original_filename="f.pdf", ext=".pdf", size_bytes=1,
    ))
    db_sync.commit()

    # dry-run count agrees with the delete: only the 1 truly-bare post.
    assert cleanup_service.prune_bare_posts(db_sync, dry_run=True)["count"] == 1
    result = cleanup_service.prune_bare_posts(db_sync, dry_run=False)
    assert result["deleted"] == 1

    surviving = db_sync.execute(select(Post.external_post_id)).scalars().all()
    assert "bare1" not in surviving
    assert set(surviving) == {"prim", "prov", "att"}


# --- reset_content_tagging ------------------------------------------


def test_reset_content_tagging_dry_run_counts_without_deleting(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="rc")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "r.jpg"), sha256="1" * 64,
    )
    g = _make_tag(db_sync, name="solo", kind=TagKind.general)
    c = _make_tag(db_sync, name="naruto", kind=TagKind.character)
    _make_tag(db_sync, name="Naruto", kind=TagKind.fandom)
    _make_tag(db_sync, name="my-series", kind=TagKind.series)
    db_sync.execute(image_tag.insert().values([
        {"image_record_id": img.id, "tag_id": g.id},
        {"image_record_id": img.id, "tag_id": c.id},
    ]))
    db_sync.commit()

    result = cleanup_service.reset_content_tagging(db_sync, dry_run=True)
    assert result["count"] == 2
    assert result["by_kind"] == {"general": 1, "character": 1}
    assert result["applications"] == 2
    # Nothing deleted — all 4 fixture tags still present (non-system count;
    # the seeded hygiene tags #128 also survive, by design).
    assert db_sync.execute(
        select(func.count(Tag.id)).where(Tag.is_system.is_(False))
    ).scalar_one() == 4


def test_reset_content_tagging_deletes_content_keeps_fandom_series(db_sync, tmp_path):
    a = _make_artist(db_sync, slug="rc2")
    img = _make_image(
        db_sync, artist=a, path=str(tmp_path / "r2.jpg"), sha256="2" * 64,
    )
    g = _make_tag(db_sync, name="solo", kind=TagKind.general)
    c = _make_tag(db_sync, name="naruto", kind=TagKind.character)
    _make_tag(db_sync, name="Naruto", kind=TagKind.fandom)
    s = _make_tag(db_sync, name="my-series", kind=TagKind.series)
    db_sync.execute(image_tag.insert().values([
        {"image_record_id": img.id, "tag_id": g.id},
        {"image_record_id": img.id, "tag_id": c.id},
        {"image_record_id": img.id, "tag_id": s.id},  # series membership
    ]))
    db_sync.add(SeriesPage(
        series_tag_id=s.id, image_id=img.id, page_number=1
    ))
    db_sync.commit()

    result = cleanup_service.reset_content_tagging(db_sync, dry_run=False)
    assert result["deleted"] == 2

    # general + character gone; fandom + series kept. Non-system only — the
    # seeded hygiene tags (#128, kind=general) survive a reset by design.
    kinds = db_sync.execute(
        select(Tag.kind).where(Tag.is_system.is_(False))
    ).scalars().all()
    kind_vals = {k.value if hasattr(k, "value") else str(k) for k in kinds}
    assert kind_vals == {"fandom", "series"}
    # series_page ordering survived.
    assert db_sync.execute(
        select(func.count()).select_from(SeriesPage)
    ).scalar_one() == 1
    # Only the series image_tag association survived (content ones cascaded).
    remaining = db_sync.execute(select(image_tag.c.tag_id)).scalars().all()
    assert remaining == [s.id]


# ---- Tier-1 video dedup (#871) ------------------------------------------


def _make_video(db_sync, *, artist, path, sha256, duration, w, h, size=2000):
    img = ImageRecord(
        artist_id=artist.id, path=path, sha256=sha256, size_bytes=size,
        mime="video/mp4", origin="downloaded",
        width=w, height=h, duration_seconds=duration,
    )
    db_sync.add(img)
    db_sync.flush()
    return img


def test_dedup_videos_collapses_and_relinks(db_sync, tmp_path):
    """Apply: a same-artist, same-duration, same-aspect video pair collapses to the
    higher-res keeper, and the loser's post is re-pointed to the keeper first so it
    doesn't lose the video."""
    a = _make_artist(db_sync, slug="vd", name="VD")
    keeper = _make_video(
        db_sync, artist=a, path=str(tmp_path / "k.mp4"), sha256="k" * 64,
        duration=120.0, w=1920, h=1080,
    )
    loser = _make_video(
        db_sync, artist=a, path=str(tmp_path / "l.mp4"), sha256="l" * 64,
        duration=120.3, w=1280, h=720,  # within 1s, same 16:9, smaller
    )
    (tmp_path / "k.mp4").write_bytes(b"k")
    (tmp_path / "l.mp4").write_bytes(b"l")
    pa = Post(artist_id=a.id, external_post_id="pa")
    pb = Post(artist_id=a.id, external_post_id="pb")
    db_sync.add_all([pa, pb])
    db_sync.flush()
    db_sync.add(ImageProvenance(image_record_id=keeper.id, post_id=pa.id))
    db_sync.add(ImageProvenance(image_record_id=loser.id, post_id=pb.id))
    db_sync.commit()
    keeper_id, loser_id, pb_id = keeper.id, loser.id, pb.id

    out = cleanup_service.dedup_videos(db_sync, images_root=tmp_path, dry_run=False)
    assert out["groups"] == 1
    assert out["deleted"] == 1
    assert out["relinked_posts"] == 1

    db_sync.expire_all()
    assert db_sync.get(ImageRecord, loser_id) is None
    assert db_sync.get(ImageRecord, keeper_id) is not None
    linked = db_sync.execute(
        select(ImageProvenance.post_id)
        .where(ImageProvenance.image_record_id == keeper_id)
    ).scalars().all()
    assert pb_id in set(linked)  # keeper inherited the loser's post
    assert not (tmp_path / "l.mp4").exists()  # redundant file removed


def test_dedup_videos_dry_run_keeps_everything(db_sync, tmp_path):
    """Preview reports the group without deleting (rule 93 shared predicate)."""
    a = _make_artist(db_sync, slug="vd2", name="VD2")
    _make_video(
        db_sync, artist=a, path=str(tmp_path / "k.mp4"), sha256="a" * 64,
        duration=90.0, w=1920, h=1080,
    )
    _make_video(
        db_sync, artist=a, path=str(tmp_path / "l.mp4"), sha256="b" * 64,
        duration=90.2, w=1280, h=720,
    )
    db_sync.commit()

    out = cleanup_service.dedup_videos(db_sync, images_root=tmp_path, dry_run=True)
    assert out["groups"] == 1
    assert out["redundant"] == 1
    assert "deleted" not in out
    assert db_sync.execute(
        select(func.count(ImageRecord.id))
    ).scalar_one() == 2


def test_dedup_videos_distinct_durations_not_grouped(db_sync, tmp_path):
    """False-merge guard: clearly different durations stay separate."""
    a = _make_artist(db_sync, slug="vd3", name="VD3")
    _make_video(
        db_sync, artist=a, path=str(tmp_path / "x.mp4"), sha256="c" * 64,
        duration=60.0, w=1920, h=1080,
    )
    _make_video(
        db_sync, artist=a, path=str(tmp_path / "y.mp4"), sha256="d" * 64,
        duration=200.0, w=1920, h=1080,
    )
    db_sync.commit()

    out = cleanup_service.dedup_videos(db_sync, images_root=tmp_path, dry_run=True)
    assert out["groups"] == 0
    assert db_sync.execute(
        select(func.count(ImageRecord.id))
    ).scalar_one() == 2


# ---- Gated-post blurred-preview cleanup (#874 follow-up) ------------------


class _GatedFakeClient:
    """Stub PatreonClient for collect_gated_previews: `posts` is a list of
    (post_id, gated_bool, [filehashes])."""

    def __init__(self, posts):
        self._posts = posts

    @staticmethod
    def post_is_gated(post):
        return (post.get("attributes") or {}).get("current_user_can_view") is False

    def iter_posts(self, campaign_id, cursor=None):
        for pid, gated, hashes in self._posts:
            yield (
                {
                    "id": pid,
                    "attributes": {"current_user_can_view": not gated},
                    "_h": hashes,
                },
                {},
                None,
            )

    def extract_media(self, post, included):
        from backend.app.services.patreon_client import MediaItem
        return [
            MediaItem(
                url=f"https://cdn.patreon.com/{h}/x.jpg", filename="x.jpg",
                kind="images", filehash=h, post_id=str(post["id"]),
            )
            for h in post["_h"]
        ]


def test_collect_gated_previews_only_gated_posts():
    client = _GatedFakeClient([
        ("g1", True, ["aa", "bb"]),
        ("open1", False, ["cc"]),
        ("g2", True, []),  # gated but feed served no media
    ])
    out = cleanup_service.collect_gated_previews(client, "camp")
    assert out == {"g1": ["aa", "bb"], "g2": []}  # accessible post excluded


def _make_src(db_sync, artist):
    s = Source(
        artist_id=artist.id, platform="patreon",
        url="https://patreon.com/x", enabled=True, config_overrides={},
    )
    db_sync.add(s)
    db_sync.flush()
    return s


def _make_dl_image(db_sync, *, artist, path, sha256, fh, size=500):
    img = ImageRecord(
        artist_id=artist.id, path=path, sha256=sha256, size_bytes=size,
        mime="image/jpeg", origin="downloaded", source_filehash=fh,
    )
    db_sync.add(img)
    db_sync.flush()
    return img


def test_purge_gated_previews_deletes_only_hash_matched(db_sync, tmp_path):
    """#874: hash-matched blurred previews are deleted; a real file with a
    different source_filehash (downloaded when access existed) and a
    null-source_filehash image are both spared. A post left bare is removed; a post
    that still has real content is kept. Ledger rows for the blurred hash are
    cleared (so real media can re-ingest) while the real hash stays."""
    a = _make_artist(db_sync, slug="gp", name="GP")
    s = _make_src(db_sync, a)
    blurred = _make_dl_image(
        db_sync, artist=a, path=str(tmp_path / "blur.jpg"), sha256="1" * 64,
        fh="b" * 32, size=500,
    )
    real = _make_dl_image(
        db_sync, artist=a, path=str(tmp_path / "real.jpg"), sha256="2" * 64,
        fh="r" * 32, size=999,
    )
    nohash = _make_dl_image(
        db_sync, artist=a, path=str(tmp_path / "nh.jpg"), sha256="3" * 64,
        fh=None, size=100,
    )
    blurred2 = _make_dl_image(
        db_sync, artist=a, path=str(tmp_path / "blur2.jpg"), sha256="4" * 64,
        fh="c" * 32, size=700,
    )
    for name in ("blur.jpg", "real.jpg", "nh.jpg", "blur2.jpg"):
        (tmp_path / name).write_bytes(b"x")
    p1 = Post(artist_id=a.id, source_id=s.id, external_post_id="g1")
    p2 = Post(artist_id=a.id, source_id=s.id, external_post_id="g2")
    db_sync.add_all([p1, p2])
    db_sync.flush()
    # g1: blurred + real + nohash; g2: only blurred2.
    for img in (blurred, real, nohash):
        db_sync.add(ImageProvenance(image_record_id=img.id, post_id=p1.id, source_id=s.id))
    db_sync.add(ImageProvenance(image_record_id=blurred2.id, post_id=p2.id, source_id=s.id))
    db_sync.add_all([
        PatreonSeenMedia(source_id=s.id, filehash="b" * 32, post_id="g1"),
        PatreonSeenMedia(source_id=s.id, filehash="r" * 32, post_id="g1"),
        PatreonSeenMedia(source_id=s.id, filehash="c" * 32, post_id="g2"),
    ])
    db_sync.commit()
    ids = {
        "blurred": blurred.id, "real": real.id, "nohash": nohash.id,
        "blurred2": blurred2.id, "p1": p1.id, "p2": p2.id,
    }

    # The gated feed now serves the blurred hashes for g1 + g2.
    gated_map = {s.id: {"g1": ["b" * 32], "g2": ["c" * 32]}}

    # Preview (rule 93): reports matches without deleting.
    prev = cleanup_service.purge_gated_previews(
        db_sync, gated_map=gated_map, images_root=tmp_path, dry_run=True,
    )
    assert prev["matched"] == 2
    assert prev["gated_posts"] == 2
    assert prev["unverifiable"] == 1  # the null-source_filehash image on g1
    assert prev["reclaim_bytes"] == 500 + 700
    assert db_sync.get(ImageRecord, ids["blurred"]) is not None  # dry-run kept

    # Apply.
    out = cleanup_service.purge_gated_previews(
        db_sync, gated_map=gated_map, images_root=tmp_path, dry_run=False,
    )
    assert out["deleted"] == 2
    assert out["posts_deleted"] == 1   # g2 went bare; g1 still has real content
    assert out["ledger_cleared"] == 2  # b* and c* cleared; r* retained

    db_sync.expire_all()
    assert db_sync.get(ImageRecord, ids["blurred"]) is None    # blurred deleted
    assert db_sync.get(ImageRecord, ids["blurred2"]) is None
    assert db_sync.get(ImageRecord, ids["real"]) is not None   # real spared
    assert db_sync.get(ImageRecord, ids["nohash"]) is not None  # unverifiable kept
    assert not (tmp_path / "blur.jpg").exists()
    assert (tmp_path / "real.jpg").exists()
    # Ledger: blurred hashes cleared so the real media can re-ingest; real kept.
    remaining = db_sync.execute(
        select(PatreonSeenMedia.filehash).where(PatreonSeenMedia.source_id == s.id)
    ).scalars().all()
    assert set(remaining) == {"r" * 32}
    assert db_sync.get(Post, ids["p1"]) is not None  # still has real + nohash
    assert db_sync.get(Post, ids["p2"]) is None      # bare → removed


def test_purge_gated_previews_no_gated_posts_is_noop(db_sync, tmp_path):
    out = cleanup_service.purge_gated_previews(
        db_sync, gated_map={}, images_root=tmp_path, dry_run=False,
    )
    assert out["matched"] == 0
    assert out["deleted"] == 0
    assert out["gated_posts"] == 0


# ---- duplicate-post reconciliation (gallery-dl → native, milestone #73) ----


def test_reconcile_merges_attachmentid_and_postid_dupes(db_sync, tmp_path):
    # gallery-dl row (keyed by attachment id, holds the image + body/date) +
    # native row (keyed by the real post id, bare) → unified onto ONE post-id-
    # keyed keeper, image moved over, keeper backfilled from the legacy row.
    a = _make_artist(db_sync, slug="rc1")
    img = _make_image(db_sync, artist=a, path=str(tmp_path / "x.jpg"), sha256="c" * 64)
    legacy = Post(
        artist_id=a.id, external_post_id="711509",
        raw_metadata={"post_id": 1923726, "category": "subscribestar"},
        description="real body", post_title="T",
        post_date=datetime(2025, 6, 20, tzinfo=UTC),
    )
    native = Post(
        artist_id=a.id, external_post_id="1923726",
        raw_metadata={"post_id": 1923726, "category": "subscribestar"},
    )
    db_sync.add_all([legacy, native])
    db_sync.flush()
    img.primary_post_id = legacy.id
    img_id, native_id, legacy_id = img.id, native.id, legacy.id
    db_sync.commit()

    prev = cleanup_service.reconcile_duplicate_posts(db_sync, dry_run=True)
    assert prev["groups"] == 1
    assert prev["posts_to_merge"] == 1
    res = cleanup_service.reconcile_duplicate_posts(db_sync, dry_run=False)
    assert res["merged"] == 1

    rows = db_sync.execute(select(Post.id, Post.external_post_id)).all()
    assert len(rows) == 1
    keeper_id, keeper_epid = rows[0]
    assert keeper_id == native_id  # native (post-id-keyed) row survives
    assert keeper_epid == "1923726"
    # image moved to keeper; legacy row gone; keeper backfilled.
    assert db_sync.execute(
        select(ImageRecord.primary_post_id).where(ImageRecord.id == img_id)
    ).scalar_one() == native_id
    assert db_sync.execute(
        select(func.count()).select_from(Post).where(Post.id == legacy_id)
    ).scalar_one() == 0
    desc, pdate = db_sync.execute(
        select(Post.description, Post.post_date).where(Post.id == native_id)
    ).one()
    assert desc == "real body"
    assert pdate is not None


def test_reconcile_noop_when_posts_unique(db_sync):
    a = _make_artist(db_sync, slug="rc2")
    db_sync.add_all([
        Post(artist_id=a.id, external_post_id="100", raw_metadata={"post_id": 100}),
        Post(artist_id=a.id, external_post_id="200", raw_metadata={"post_id": 200}),
    ])
    db_sync.commit()
    assert cleanup_service.reconcile_duplicate_posts(db_sync, dry_run=True)["groups"] == 0
    res = cleanup_service.reconcile_duplicate_posts(db_sync, dry_run=False)
    assert res["merged"] == 0
    assert db_sync.execute(select(func.count()).select_from(Post)).scalar_one() == 2


def test_reconcile_dedups_provenance_collision(db_sync, tmp_path):
    # Both dup rows link the SAME image via provenance; merging must DROP the
    # colliding loser row (unique image_record_id+post_id), not raise.
    a = _make_artist(db_sync, slug="rc3")
    img = _make_image(db_sync, artist=a, path=str(tmp_path / "y.jpg"), sha256="d" * 64)
    legacy = Post(artist_id=a.id, external_post_id="9", raw_metadata={"post_id": 55})
    native = Post(artist_id=a.id, external_post_id="55", raw_metadata={"post_id": 55})
    db_sync.add_all([legacy, native])
    db_sync.flush()
    db_sync.add(ImageProvenance(image_record_id=img.id, post_id=legacy.id))
    db_sync.add(ImageProvenance(image_record_id=img.id, post_id=native.id))
    img_id, native_id = img.id, native.id
    db_sync.commit()

    cleanup_service.reconcile_duplicate_posts(db_sync, dry_run=False)
    prov = db_sync.execute(
        select(ImageProvenance.post_id).where(ImageProvenance.image_record_id == img_id)
    ).scalars().all()
    assert prov == [native_id]


def test_reconcile_preserves_from_attachment_on_provenance_collision(db_sync, tmp_path):
    # The gallery-dl loser row recorded which archive the image came out of
    # (#87); the native keeper row didn't. Merging drops the loser provenance row
    # on the (image, post) collision — but must first carry its from_attachment_id
    # onto the keeper so the containing-archive linkage survives.
    a = _make_artist(db_sync, slug="rc4")
    img = _make_image(db_sync, artist=a, path=str(tmp_path / "z.jpg"), sha256="e" * 64)
    legacy = Post(artist_id=a.id, external_post_id="9", raw_metadata={"post_id": 77})
    native = Post(artist_id=a.id, external_post_id="77", raw_metadata={"post_id": 77})
    db_sync.add_all([legacy, native])
    db_sync.flush()
    att = PostAttachment(
        post_id=legacy.id, artist_id=a.id, sha256="f" * 64,
        path=str(tmp_path / "bundle.cbz"), original_filename="bundle.cbz",
        ext=".cbz", size_bytes=9,
    )
    db_sync.add(att)
    db_sync.flush()
    db_sync.add(ImageProvenance(
        image_record_id=img.id, post_id=legacy.id, from_attachment_id=att.id,
    ))
    db_sync.add(ImageProvenance(image_record_id=img.id, post_id=native.id))
    img_id, native_id, att_id = img.id, native.id, att.id
    db_sync.commit()

    cleanup_service.reconcile_duplicate_posts(db_sync, dry_run=False)
    rows = db_sync.execute(
        select(ImageProvenance.post_id, ImageProvenance.from_attachment_id)
        .where(ImageProvenance.image_record_id == img_id)
    ).all()
    assert rows == [(native_id, att_id)]