"""Random-sample query for the showcase.

Uses the tsm_system_rows TABLESAMPLE method (migration 0004) instead of
ORDER BY random(): sampling cost scales with the sample size, not the table,
so it stays fast as the collection grows. SYSTEM_ROWS(n) returns up to n
rows; an empty table yields none.
"""

from sqlalchemy import select, text
from sqlalchemy.ext.asyncio import AsyncSession

from ..models import ImageRecord
from .gallery_service import thumbnail_url


class ShowcaseService:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def random_sample(self, limit: int = 60) -> list[dict]:
        if limit < 1 or limit > 200:
            raise ValueError("limit must be between 1 and 200")
        # Over-sample then random-order (#699): SYSTEM_ROWS reads CONTIGUOUS rows
        # from each sampled page, so sequentially-imported near-duplicates
        # (multi-image posts, variant sets) come back adjacent and cluster in the
        # showcase ("three near-identical in a row"). Sampling a multiple of
        # `limit` spans more pages, and ORDER BY random() before taking `limit`
        # breaks the physical adjacency — far better spread, still cheap
        # (random() over a few hundred rows, not the whole table).
        oversample = min(limit * 5, 1000)
        stmt = select(ImageRecord).from_statement(
            text(
                "SELECT * FROM ("
                "  SELECT * FROM image_record TABLESAMPLE SYSTEM_ROWS(:o)"
                ") sub ORDER BY random() LIMIT :n"
            ).bindparams(o=oversample, n=limit)
        )
        rows = (await self.session.execute(stmt)).scalars().all()
        return [
            {
                "id": r.id,
                "sha256": r.sha256,
                "mime": r.mime,
                "width": r.width,
                "height": r.height,
                "thumbnail_url": thumbnail_url(r.thumbnail_path, r.sha256, r.mime),
            }
            for r in rows
        ]