"""PB (Permintaan Barang) repository."""

from datetime import datetime, timezone

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

from app.models.pb import PB, PBItem, Project, StatusPB, StatusPBItem


def _pb_eager():
    return [
        selectinload(PB.project),
        selectinload(PB.items),
        selectinload(PB.created_by),
        selectinload(PB.forwarded_by),
        selectinload(PB.recalled_by),
        selectinload(PB.reviewed_by),
    ]


# ── Project ───────────────────────────────────────────────────────────────────

async def get_projects(db: AsyncSession, spbu_id: int) -> list[Project]:
    result = await db.execute(
        select(Project)
        .where(Project.spbu_id == spbu_id)
        .order_by(Project.nama)
    )
    return list(result.scalars().all())


async def get_project_by_id(db: AsyncSession, project_id: int, spbu_id: int) -> Project | None:
    result = await db.execute(
        select(Project).where(Project.id == project_id, Project.spbu_id == spbu_id)
    )
    return result.scalar_one_or_none()


async def create_project(db: AsyncSession, spbu_id: int, nama: str, deskripsi: str | None) -> Project:
    proj = Project(spbu_id=spbu_id, nama=nama, deskripsi=deskripsi)
    db.add(proj)
    await db.flush()
    await db.refresh(proj)
    return proj


async def update_project(db: AsyncSession, proj: Project, data: dict) -> Project:
    for k, v in data.items():
        setattr(proj, k, v)
    await db.flush()
    await db.refresh(proj)
    return proj


# ── PB numbering ──────────────────────────────────────────────────────────────

async def next_pb_nomor(db: AsyncSession, spbu_code: str, yyyymm: str) -> str:
    prefix = f"PB-{spbu_code}-{yyyymm}-"
    result = await db.execute(
        select(func.count()).where(PB.nomor.like(f"{prefix}%"))
    )
    seq = (result.scalar() or 0) + 1
    return f"{prefix}{seq:03d}"


# ── PB CRUD ───────────────────────────────────────────────────────────────────

async def get_pb_list(
    db: AsyncSession,
    spbu_id: int,
    status: str | None = None,
    project_id: int | None = None,
    skip: int = 0,
    limit: int = 50,
) -> tuple[list[PB], int]:
    q = select(PB).where(PB.spbu_id == spbu_id)
    if status:
        q = q.where(PB.status == status)
    if project_id:
        q = q.where(PB.project_id == project_id)

    total_result = await db.execute(select(func.count()).select_from(q.subquery()))
    total = total_result.scalar() or 0

    q = q.options(*_pb_eager()).order_by(PB.created_at.desc()).offset(skip).limit(limit)
    result = await db.execute(q)
    return list(result.scalars().all()), total


async def get_pb_by_id(db: AsyncSession, pb_id: int, spbu_id: int) -> PB | None:
    result = await db.execute(
        select(PB)
        .options(*_pb_eager())
        .where(PB.id == pb_id, PB.spbu_id == spbu_id)
    )
    return result.scalar_one_or_none()


async def create_pb(db: AsyncSession, spbu_id: int, user_id: int, nomor: str, data: dict) -> PB:
    items_data = data.pop("items", [])
    pb = PB(spbu_id=spbu_id, nomor=nomor, created_by_id=user_id, **data)
    db.add(pb)
    await db.flush()
    for i, item in enumerate(items_data):
        pb_item = PBItem(pb_id=pb.id, urutan=item.get("urutan", i), **{k: v for k, v in item.items() if k != "urutan"})
        db.add(pb_item)
    await db.flush()
    result = await db.execute(select(PB).options(*_pb_eager()).where(PB.id == pb.id))
    return result.scalar_one()


async def update_pb(db: AsyncSession, pb: PB, data: dict) -> PB:
    items_data = data.pop("items", None)
    for k, v in data.items():
        setattr(pb, k, v)
    if items_data is not None:
        # Full replace: delete existing, re-insert
        for item in list(pb.items):
            await db.delete(item)
        await db.flush()
        for i, item in enumerate(items_data):
            pb_item = PBItem(pb_id=pb.id, urutan=item.get("urutan", i), **{k: v for k, v in item.items() if k != "urutan"})
            db.add(pb_item)
    await db.flush()
    result = await db.execute(select(PB).options(*_pb_eager()).where(PB.id == pb.id))
    return result.scalar_one()


async def delete_pb(db: AsyncSession, pb: PB) -> None:
    await db.delete(pb)
    await db.flush()


async def reload_pb(db: AsyncSession, pb_id: int) -> PB:
    result = await db.execute(select(PB).options(*_pb_eager()).where(PB.id == pb_id))
    return result.scalar_one()


# ── PB Item ───────────────────────────────────────────────────────────────────

async def get_pb_item(db: AsyncSession, item_id: int, pb_id: int) -> PBItem | None:
    result = await db.execute(
        select(PBItem).where(PBItem.id == item_id, PBItem.pb_id == pb_id)
    )
    return result.scalar_one_or_none()


async def update_pb_item(db: AsyncSession, item: PBItem, data: dict) -> PBItem:
    for k, v in data.items():
        setattr(item, k, v)
    await db.flush()
    await db.refresh(item)
    return item


# ── Expense realisasi (for ledger) ────────────────────────────────────────────

async def get_expenses_for_pb(db: AsyncSession, pb_id: int):
    from app.models.expenses import Expense, StatusExpense
    from sqlalchemy.orm import selectinload as si
    result = await db.execute(
        select(Expense)
        .options(si(Expense.created_by))
        .where(Expense.pb_id == pb_id, Expense.status == StatusExpense.APPROVED)
        .order_by(Expense.tanggal)
    )
    return list(result.scalars().all())