"""Penerimaan repository — DB queries for BBM receipt."""

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.penerimaan import Penerimaan, PenerimaanFoto, PenerimaanItem
from app.models.spbu import Tangki
from app.models.product import Produk


def _eager_options():
    """Standard selectinload chain for full Penerimaan graph."""
    return [
        selectinload(Penerimaan.penebusan),
        selectinload(Penerimaan.created_by),
        selectinload(Penerimaan.submitted_by),
        selectinload(Penerimaan.reviewed_by),
        selectinload(Penerimaan.unlocked_by),
        selectinload(Penerimaan.fotos),
        selectinload(Penerimaan.items).options(
            selectinload(PenerimaanItem.tangki).selectinload(Tangki.kalibrasi),
            selectinload(PenerimaanItem.tangki).selectinload(Tangki.produk),
            selectinload(PenerimaanItem.produk),
            selectinload(PenerimaanItem.penebusan_item),
            selectinload(PenerimaanItem.fotos),
        ),
    ]


async def get_all_penerimaan(
    db: AsyncSession,
    spbu_id: int,
    tanggal=None,
    penebusan_id: int | None = None,
    skip: int = 0,
    limit: int = 20,
) -> tuple[list[Penerimaan], int]:
    query = (
        select(Penerimaan)
        .where(Penerimaan.spbu_id == spbu_id)
        .options(*_eager_options())
        .order_by(Penerimaan.tanggal.desc(), Penerimaan.id.desc())
    )
    if tanggal is not None:
        query = query.where(Penerimaan.tanggal == tanggal)
    if penebusan_id is not None:
        query = query.where(Penerimaan.penebusan_id == penebusan_id)

    count_q = await db.execute(select(func.count()).select_from(query.subquery()))
    total = count_q.scalar_one()

    result = await db.execute(query.offset(skip).limit(limit))
    return list(result.scalars().unique().all()), total


async def get_penerimaan_by_id(
    db: AsyncSession, penerimaan_id: int, spbu_id: int
) -> Penerimaan | None:
    result = await db.execute(
        select(Penerimaan)
        .where(Penerimaan.id == penerimaan_id, Penerimaan.spbu_id == spbu_id)
        .options(*_eager_options())
    )
    return result.scalar_one_or_none()


async def create_penerimaan(
    db: AsyncSession,
    header_dict: dict,
    items_dicts: list[dict],
) -> Penerimaan:
    """Create Penerimaan header + all PenerimaanItem rows atomically."""
    penerimaan = Penerimaan(**header_dict)
    db.add(penerimaan)
    await db.flush()  # obtain penerimaan.id
    for item_dict in items_dicts:
        db.add(PenerimaanItem(penerimaan_id=penerimaan.id, **item_dict))
    await db.flush()
    return await get_penerimaan_by_id(db, penerimaan.id, header_dict["spbu_id"])


async def delete_penerimaan(db: AsyncSession, penerimaan: Penerimaan) -> None:
    await db.delete(penerimaan)
    await db.flush()


async def add_foto(
    db: AsyncSession,
    penerimaan_id: int,
    penerimaan_item_id: int | None,
    tipe: str,
    url: str,
) -> PenerimaanFoto:
    foto = PenerimaanFoto(
        penerimaan_id=penerimaan_id,
        penerimaan_item_id=penerimaan_item_id,
        tipe=tipe,
        url=url,
    )
    db.add(foto)
    await db.flush()
    await db.refresh(foto)
    return foto


async def delete_foto(
    db: AsyncSession, foto_id: int, penerimaan_id: int
) -> str:
    """Delete a foto record. Returns the stored file URL so the caller can clean up storage."""
    result = await db.execute(
        select(PenerimaanFoto).where(
            PenerimaanFoto.id == foto_id,
            PenerimaanFoto.penerimaan_id == penerimaan_id,
        )
    )
    foto = result.scalar_one_or_none()
    if foto is None:
        raise ValueError(f"Foto id={foto_id} tidak ditemukan")
    url = foto.url
    await db.delete(foto)
    await db.flush()
    return url