"""Penebusan repository — DB queries for BBM procurement."""

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

from app.models.penebusan import Penebusan, PenebusanItem, StatusPenebusan


async def get_all_penebusan(
    db: AsyncSession,
    spbu_id: int,
    tanggal=None,
    status: StatusPenebusan | None = None,
    booking_code: str | None = None,
    skip: int = 0,
    limit: int = 20,
) -> tuple[list[Penebusan], int]:
    """Fetch paginated penebusan rows with items and relationships."""
    query = (
        select(Penebusan)
        .where(Penebusan.spbu_id == spbu_id)
        .options(
            selectinload(Penebusan.created_by),
            selectinload(Penebusan.items).selectinload(PenebusanItem.produk),
        )
        .order_by(Penebusan.tanggal.desc(), Penebusan.id.desc())
    )
    if tanggal is not None:
        query = query.where(Penebusan.tanggal == tanggal)
    if status is not None:
        query = query.where(Penebusan.status == status)
    if booking_code is not None:
        query = query.where(Penebusan.booking_code.ilike(f"%{booking_code}%"))

    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_penebusan_by_id(
    db: AsyncSession, penebusan_id: int, spbu_id: int
) -> Penebusan | None:
    """Fetch a single Penebusan by PK and SPBU, eagerly loading items with produk."""
    result = await db.execute(
        select(Penebusan)
        .where(Penebusan.id == penebusan_id, Penebusan.spbu_id == spbu_id)
        .options(
            selectinload(Penebusan.created_by),
            selectinload(Penebusan.spbu),
            selectinload(Penebusan.items).selectinload(PenebusanItem.produk),
        )
    )
    return result.scalar_one_or_none()


async def get_penebusan_by_booking_code(
    db: AsyncSession, spbu_id: int, booking_code: str
) -> Penebusan | None:
    """Check if a penebusan with this booking_code already exists for the SPBU."""
    result = await db.execute(
        select(Penebusan).where(
            Penebusan.spbu_id == spbu_id,
            Penebusan.booking_code == booking_code,
        )
    )
    return result.scalar_one_or_none()


async def create_penebusan(
    db: AsyncSession,
    data_dict: dict,
    item_dicts: list[dict],
) -> Penebusan:
    """Insert a new Penebusan together with its PenebusanItem rows atomically."""
    penebusan = Penebusan(**data_dict)
    db.add(penebusan)
    await db.flush()
    for item in item_dicts:
        db.add(PenebusanItem(penebusan_id=penebusan.id, **item))
    await db.flush()
    return await get_penebusan_by_id(db, penebusan.id, data_dict["spbu_id"])


async def update_penebusan(
    db: AsyncSession,
    penebusan: Penebusan,
    data_dict: dict,
    item_dicts: list[dict] | None = None,
) -> Penebusan:
    """Update header fields and optionally replace all items."""
    for key, value in data_dict.items():
        setattr(penebusan, key, value)

    if item_dicts is not None:
        await db.execute(
            delete(PenebusanItem).where(PenebusanItem.penebusan_id == penebusan.id)
        )
        for item in item_dicts:
            db.add(PenebusanItem(penebusan_id=penebusan.id, **item))

    await db.flush()
    return await get_penebusan_by_id(db, penebusan.id, penebusan.spbu_id)


async def update_penebusan_status(
    db: AsyncSession,
    penebusan: Penebusan,
    status: StatusPenebusan,
    no_so: str | None = None,
) -> Penebusan:
    """Transition status, optionally setting the SO number."""
    penebusan.status = status
    if no_so is not None:
        penebusan.no_so = no_so
    await db.flush()
    await db.refresh(penebusan)
    return await get_penebusan_by_id(db, penebusan.id, penebusan.spbu_id)


async def update_file_url(
    db: AsyncSession,
    penebusan: Penebusan,
    field_name: str,
    url: str,
) -> Penebusan:
    """Update pdf_do_url or pdf_bukti_bayar_url."""
    setattr(penebusan, field_name, url)
    await db.flush()
    await db.refresh(penebusan)
    return await get_penebusan_by_id(db, penebusan.id, penebusan.spbu_id)