"""Penebusan service — business logic for BBM procurement module."""

from decimal import Decimal

from sqlalchemy import delete, func, select
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.penebusan import Penebusan, PenebusanItem, StatusPenebusan
from app.models.penerimaan import Penerimaan
from app.models.product import Produk
from app.repositories import penebusan_repository
from app.schemas.penebusan import (
    ParsedPDFItem,
    ParsedPDFResult,
    PenebusanCreate,
    PenebusanDetailResponse,
    PenebusanItemInput,
    PenebusanItemResponse,
    PenebusanResponse,
    PenebusanUpdate,
)
from app.utils.pdf_parser import parse_pertamina_pdf


# ---------------------------------------------------------------------------
# Internal helpers
# ---------------------------------------------------------------------------

def _build_item_response(item) -> PenebusanItemResponse:
    """Build enriched item response from ORM object."""
    produk = item.produk
    return PenebusanItemResponse(
        id=item.id,
        produk_id=item.produk_id,
        produk_nama=produk.nama if produk else "",
        produk_kode=produk.kode if produk else "",
        kode_item_pertamina=item.kode_item_pertamina,
        volume_pesan=item.volume_pesan,
        volume_diterima=item.volume_diterima,
        tanggal_kirim=item.tanggal_kirim,
        sent_to_text=item.sent_to_text,
    )


def _build_penebusan_response(p: Penebusan) -> PenebusanResponse:
    """Build a summary response from an ORM object."""
    items = p.items or []
    total_vol_pesan = sum((i.volume_pesan for i in items), Decimal("0"))
    total_vol_diterima = sum((i.volume_diterima for i in items), Decimal("0"))

    # Build products summary: "Pertamax (8000L), Pertalite (16000L)"
    prod_summary_parts = []
    for item in items:
        nama = item.produk.nama if item.produk else "?"
        prod_summary_parts.append(f"{nama} ({item.volume_pesan:,.0f}L)")
    products_summary = ", ".join(prod_summary_parts)

    return PenebusanResponse(
        id=p.id,
        spbu_id=p.spbu_id,
        tanggal=p.tanggal,
        booking_code=p.booking_code,
        no_so=p.no_so,
        status=p.status,
        total=p.total,
        item_count=len(items),
        total_volume_pesan=total_vol_pesan,
        total_volume_diterima=total_vol_diterima,
        products_summary=products_summary,
        pdf_do_url=p.pdf_do_url,
        pdf_bukti_bayar_url=p.pdf_bukti_bayar_url,
        created_by_name=p.created_by.name if p.created_by else None,
        created_at=p.created_at,
    )


def _build_detail_response(p: Penebusan, has_penerimaan: bool = False) -> PenebusanDetailResponse:
    """Build a full detail response including items and cost breakdown."""
    summary = _build_penebusan_response(p)
    item_responses = [_build_item_response(item) for item in (p.items or [])]
    return PenebusanDetailResponse(
        **summary.model_dump(),
        items=item_responses,
        subtotal=p.subtotal,
        discount=p.discount,
        ppn=p.ppn,
        pph22=p.pph22,
        pbbkb=p.pbbkb,
        rounding=p.rounding,
        debit_credit=p.debit_credit,
        has_penerimaan=has_penerimaan,
    )


def _item_input_to_dict(item: PenebusanItemInput) -> dict:
    """Convert a PenebusanItemInput to a dict for DB insertion."""
    return {
        "produk_id": item.produk_id,
        "kode_item_pertamina": item.kode_item_pertamina,
        "volume_pesan": item.volume_pesan,
        "tanggal_kirim": item.tanggal_kirim,
        "sent_to_text": item.sent_to_text,
    }


# ---------------------------------------------------------------------------
# Public service functions
# ---------------------------------------------------------------------------

async def list_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[PenebusanResponse], int]:
    """Return a paginated list of PenebusanResponse for an SPBU."""
    rows, total = await penebusan_repository.get_all_penebusan(
        db, spbu_id, tanggal, status, booking_code, skip, limit
    )
    return [_build_penebusan_response(r) for r in rows], total


async def _count_penerimaan(db: AsyncSession, penebusan_id: int) -> int:
    result = await db.execute(
        select(func.count()).select_from(Penerimaan).where(Penerimaan.penebusan_id == penebusan_id)
    )
    return result.scalar() or 0


async def get_penebusan_detail(
    db: AsyncSession, spbu_id: int, penebusan_id: int
) -> PenebusanDetailResponse:
    """Return a fully enriched PenebusanDetailResponse."""
    p = await penebusan_repository.get_penebusan_by_id(db, penebusan_id, spbu_id)
    if p is None:
        raise ValueError("Penebusan tidak ditemukan")
    penerimaan_count = await _count_penerimaan(db, penebusan_id)
    return _build_detail_response(p, has_penerimaan=penerimaan_count > 0)


async def delete_penebusan(
    db: AsyncSession,
    spbu_id: int,
    penebusan_id: int,
) -> None:
    """Delete a penebusan. Blocked if any penerimaan records are linked."""
    p = await penebusan_repository.get_penebusan_by_id(db, penebusan_id, spbu_id)
    if p is None:
        raise ValueError("Penebusan tidak ditemukan")
    penerimaan_count = await _count_penerimaan(db, penebusan_id)
    if penerimaan_count > 0:
        raise ValueError(
            "Penebusan tidak dapat dihapus karena sudah ada penerimaan yang terkait"
        )
    try:
        await db.delete(p)
        await db.commit()
    except SQLAlchemyError:
        await db.rollback()
        raise


async def create_penebusan(
    db: AsyncSession,
    spbu_id: int,
    data: PenebusanCreate,
    user_id: int,
) -> PenebusanDetailResponse:
    """Create a new penebusan record."""
    # Check booking_code uniqueness
    existing = await penebusan_repository.get_penebusan_by_booking_code(
        db, spbu_id, data.booking_code
    )
    if existing is not None:
        raise ValueError(f"Penebusan dengan booking code '{data.booking_code}' sudah ada")

    # Validate all produk_ids exist
    for item in data.items:
        result = await db.execute(
            select(Produk).where(Produk.id == item.produk_id, Produk.deleted_at.is_(None))
        )
        if result.scalar_one_or_none() is None:
            raise ValueError(f"Produk id={item.produk_id} tidak ditemukan")

    # Determine initial status
    initial_status = StatusPenebusan.WAITING_SO if data.no_so is None else StatusPenebusan.SUBMITTED

    data_dict = {
        "spbu_id": spbu_id,
        "tanggal": data.tanggal,
        "booking_code": data.booking_code,
        "no_so": data.no_so,
        "status": initial_status,
        "subtotal": data.subtotal,
        "discount": data.discount,
        "ppn": data.ppn,
        "pph22": data.pph22,
        "pbbkb": data.pbbkb,
        "rounding": data.rounding,
        "debit_credit": data.debit_credit,
        "total": data.total,
        "created_by_id": user_id,
    }
    item_dicts = [_item_input_to_dict(item) for item in data.items]

    try:
        p = await penebusan_repository.create_penebusan(db, data_dict, item_dicts)
        await db.commit()
        p = await penebusan_repository.get_penebusan_by_id(db, p.id, spbu_id)
        return _build_detail_response(p)
    except IntegrityError:
        await db.rollback()
        raise ValueError("Data penebusan duplikat atau melanggar constraint database")
    except SQLAlchemyError:
        await db.rollback()
        raise


async def update_penebusan(
    db: AsyncSession,
    spbu_id: int,
    penebusan_id: int,
    data: PenebusanUpdate,
) -> PenebusanDetailResponse:
    """Update an existing penebusan. Line items locked if penerimaan exists."""
    p = await penebusan_repository.get_penebusan_by_id(db, penebusan_id, spbu_id)
    if p is None:
        raise ValueError("Penebusan tidak ditemukan")
    penerimaan_count = await _count_penerimaan(db, penebusan_id)
    if penerimaan_count > 0 and data.items is not None:
        raise ValueError("Line items tidak dapat diubah karena sudah ada penerimaan yang terkait")

    # Build update dict from non-None fields (exclude items)
    update_fields = {}
    for field_name in [
        "tanggal", "booking_code", "no_so",
        "subtotal", "discount", "ppn", "pph22", "pbbkb", "rounding", "debit_credit", "total",
    ]:
        value = getattr(data, field_name)
        if value is not None:
            update_fields[field_name] = value

    # Check booking_code uniqueness if changed
    if "booking_code" in update_fields and update_fields["booking_code"] != p.booking_code:
        existing = await penebusan_repository.get_penebusan_by_booking_code(
            db, spbu_id, update_fields["booking_code"]
        )
        if existing is not None:
            raise ValueError(f"Penebusan dengan booking code '{update_fields['booking_code']}' sudah ada")

    # If SO is being set and status is WAITING_SO, transition to SUBMITTED
    if data.no_so is not None and p.status == StatusPenebusan.WAITING_SO:
        update_fields["status"] = StatusPenebusan.SUBMITTED

    # Prepare items if provided
    item_dicts = None
    if data.items is not None:
        for item in data.items:
            result = await db.execute(
                select(Produk).where(Produk.id == item.produk_id, Produk.deleted_at.is_(None))
            )
            if result.scalar_one_or_none() is None:
                raise ValueError(f"Produk id={item.produk_id} tidak ditemukan")
        item_dicts = [_item_input_to_dict(item) for item in data.items]

    try:
        p = await penebusan_repository.update_penebusan(db, p, update_fields, item_dicts)
        await db.commit()
        p = await penebusan_repository.get_penebusan_by_id(db, p.id, spbu_id)
        return _build_detail_response(p)
    except IntegrityError:
        await db.rollback()
        raise ValueError("Data penebusan duplikat atau melanggar constraint database")
    except SQLAlchemyError:
        await db.rollback()
        raise


async def set_so(
    db: AsyncSession,
    spbu_id: int,
    penebusan_id: int,
    no_so: str,
) -> PenebusanDetailResponse:
    """Set SO number on a WAITING_SO penebusan, transitioning to SUBMITTED."""
    p = await penebusan_repository.get_penebusan_by_id(db, penebusan_id, spbu_id)
    if p is None:
        raise ValueError("Penebusan tidak ditemukan")
    if p.status != StatusPenebusan.WAITING_SO:
        raise ValueError("Set SO hanya bisa dilakukan pada penebusan berstatus WAITING_SO")

    try:
        p = await penebusan_repository.update_penebusan_status(
            db, p, StatusPenebusan.SUBMITTED, no_so=no_so
        )
        await db.commit()
        p = await penebusan_repository.get_penebusan_by_id(db, p.id, spbu_id)
        return _build_detail_response(p)
    except IntegrityError:
        await db.rollback()
        raise ValueError("Data penebusan duplikat atau melanggar constraint database")
    except SQLAlchemyError:
        await db.rollback()
        raise


async def parse_pdf(
    db: AsyncSession,
    pdf_bytes: bytes,
) -> ParsedPDFResult:
    """Parse a Pertamina PDF and match products to DB records."""
    parsed = parse_pertamina_pdf(pdf_bytes)

    # Load all active products for matching
    result = await db.execute(
        select(Produk).where(Produk.deleted_at.is_(None), Produk.is_active.is_(True))
    )
    all_products = list(result.scalars().all())

    # Build alias map for fuzzy matching
    alias_map = {
        "pertamax turbo": "pertamax turbo",
        "pertamax,bulk": "pertamax",
        "pertamax": "pertamax",
        "pertalite": "pertalite",
        "biosolar b40": "biosolar",
        "biosolar": "biosolar",
        "bio solar": "biosolar",
        "dexlite": "dexlite",
        "pertadex": "pertadex",
    }

    warnings = list(parsed.warnings)
    pdf_items: list[ParsedPDFItem] = []

    for item in parsed.items:
        matched_produk = None
        name_lower = item.product_name.lower().strip()

        # Try exact match first
        for p in all_products:
            if p.nama.lower() == name_lower:
                matched_produk = p
                break

        # Try alias map
        if matched_produk is None:
            resolved_name = alias_map.get(name_lower)
            if resolved_name:
                for p in all_products:
                    if p.nama.lower() == resolved_name:
                        matched_produk = p
                        break

        # Try substring match
        if matched_produk is None:
            for p in all_products:
                if p.nama.lower() in name_lower or name_lower in p.nama.lower():
                    matched_produk = p
                    break

        if matched_produk is None:
            warnings.append(f"Produk '{item.product_name}' tidak ditemukan di database")

        pdf_items.append(ParsedPDFItem(
            kode_item_pertamina=item.kode_item_pertamina,
            product_name=item.product_name,
            produk_id=matched_produk.id if matched_produk else None,
            produk_nama=matched_produk.nama if matched_produk else None,
            volume_liter=item.volume_liter,
            no_so=item.no_so,
            tanggal_kirim=item.tanggal_kirim,
            sent_to_text=item.sent_to_text,
        ))

    return ParsedPDFResult(
        booking_code=parsed.booking_code,
        tanggal=parsed.tanggal,
        items=pdf_items,
        subtotal=parsed.subtotal,
        discount=parsed.discount,
        ppn=parsed.ppn,
        pph22=parsed.pph22,
        pbbkb=parsed.pbbkb,
        rounding=parsed.rounding,
        debit_credit=parsed.debit_credit,
        total=parsed.total,
        warnings=warnings,
    )


async def upload_file(
    db: AsyncSession,
    spbu_id: int,
    penebusan_id: int,
    file_bytes: bytes,
    filename: str,
    file_type: str,
) -> PenebusanDetailResponse:
    """Upload a DO PDF or bukti bayar file."""
    from app.utils.file_upload import save_upload, UploadContext

    p = await penebusan_repository.get_penebusan_by_id(db, penebusan_id, spbu_id)
    if p is None:
        raise ValueError("Penebusan tidak ditemukan")

    from app.utils.file_upload import delete_file
    if file_type == "do":
        field_name = "pdf_do_url"
    elif file_type == "bukti":
        field_name = "pdf_bukti_bayar_url"
    else:
        raise ValueError("file_type harus 'do' atau 'bukti'")

    old_url = getattr(p, field_name, None)
    from app.utils.file_upload import get_spbu_code
    spbu_code = await get_spbu_code(db, p.spbu_id)
    ctx = UploadContext(spbu_code, "penebusan", p.tanggal)
    url = await save_upload(file_bytes, filename, ctx)

    try:
        p = await penebusan_repository.update_file_url(db, p, field_name, url)
        await db.commit()
        p = await penebusan_repository.get_penebusan_by_id(db, p.id, spbu_id)
    except IntegrityError:
        await db.rollback()
        raise ValueError("Gagal menyimpan URL file ke database")
    except SQLAlchemyError:
        await db.rollback()
        raise
    await delete_file(old_url)
    return _build_detail_response(p)