"""Dashboard service — aggregated summary data across modules."""

from __future__ import annotations

from datetime import date
from decimal import Decimal

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

from app.models.expenses import Expense
from app.models.operational import (
    LaporanShift,
    PenjualanNozzle,
    StatusLaporan,
    StockAdjustment,
    StockAdjustmentItem,
)
from app.models.penebusan import Penebusan, PenebusanItem, StatusPenebusan
from app.models.penerimaan import Penerimaan, PenerimaanItem
from app.models.penyetoran import Penyetoran, StatusPenyetoran
from app.models.product import Produk
from app.models.spbu import Nozzle, Shift, Tangki
from app.repositories import anomali_repository
from app.schemas.dashboard import (
    DashboardPenebusanPending,
    DashboardProdukSales,
    DashboardShiftStatus,
    DashboardSummary,
    DashboardTangkiStatus,
)


async def get_summary(
    db: AsyncSession,
    spbu_id: int,
    tanggal_mulai: date,
    tanggal_akhir: date,
) -> DashboardSummary:
    """Build aggregated dashboard summary for a date range."""

    # ── KPI: Total penjualan ─────────────────────────────────────────────────
    sales_q = (
        select(
            func.coalesce(func.sum(PenjualanNozzle.volume), Decimal("0")),
            func.coalesce(func.sum(PenjualanNozzle.nilai), Decimal("0")),
        )
        .join(LaporanShift, PenjualanNozzle.laporan_shift_id == LaporanShift.id)
        .where(
            LaporanShift.spbu_id == spbu_id,
            LaporanShift.tanggal >= tanggal_mulai,
            LaporanShift.tanggal <= tanggal_akhir,
        )
    )
    sales_result = await db.execute(sales_q)
    total_volume, total_nilai = sales_result.one()

    # ── KPI: Total expenses ──────────────────────────────────────────────────
    exp_q = (
        select(func.coalesce(func.sum(Expense.jumlah), Decimal("0")))
        .where(
            Expense.spbu_id == spbu_id,
            Expense.tanggal >= tanggal_mulai,
            Expense.tanggal <= tanggal_akhir,
        )
    )
    total_expenses = (await db.execute(exp_q)).scalar_one() or Decimal("0")

    # ── KPI: Total penyetoran ────────────────────────────────────────────────
    setor_q = (
        select(func.coalesce(func.sum(Penyetoran.jumlah_setor), Decimal("0")))
        .where(
            Penyetoran.spbu_id == spbu_id,
            Penyetoran.tanggal >= tanggal_mulai,
            Penyetoran.tanggal <= tanggal_akhir,
        )
    )
    total_penyetoran = (await db.execute(setor_q)).scalar_one() or Decimal("0")

    # ── KPI: Total penerimaan volume ─────────────────────────────────────────
    pen_q = (
        select(func.coalesce(func.sum(PenerimaanItem.volume_diterima), Decimal("0")))
        .join(Penerimaan, PenerimaanItem.penerimaan_id == Penerimaan.id)
        .where(
            Penerimaan.spbu_id == spbu_id,
            Penerimaan.tanggal >= tanggal_mulai,
            Penerimaan.tanggal <= tanggal_akhir,
        )
    )
    total_penerimaan = (await db.execute(pen_q)).scalar_one() or Decimal("0")

    # ── Per-product sales breakdown ──────────────────────────────────────────
    produk_q = (
        select(
            Produk.id,
            Produk.nama,
            Produk.kode,
            func.coalesce(func.sum(PenjualanNozzle.volume), Decimal("0")),
            func.coalesce(func.sum(PenjualanNozzle.nilai), Decimal("0")),
        )
        .join(Nozzle, PenjualanNozzle.nozzle_id == Nozzle.id)
        .join(Produk, Nozzle.produk_id == Produk.id)
        .join(LaporanShift, PenjualanNozzle.laporan_shift_id == LaporanShift.id)
        .where(
            LaporanShift.spbu_id == spbu_id,
            LaporanShift.tanggal >= tanggal_mulai,
            LaporanShift.tanggal <= tanggal_akhir,
        )
        .group_by(Produk.id, Produk.nama, Produk.kode)
        .order_by(func.sum(PenjualanNozzle.nilai).desc())
    )
    produk_rows = (await db.execute(produk_q)).all()
    produk_sales = [
        DashboardProdukSales(
            produk_id=r[0], produk_nama=r[1], produk_kode=r[2],
            total_volume=r[3], total_nilai=r[4],
        )
        for r in produk_rows
    ]

    # ── Tank status (latest sounding) ────────────────────────────────────────
    tangki_list_q = (
        select(Tangki)
        .options(selectinload(Tangki.produk))
        .where(
            Tangki.spbu_id == spbu_id,
            Tangki.is_active.is_(True),
            Tangki.deleted_at.is_(None),
        )
    )
    tangki_list = (await db.execute(tangki_list_q)).scalars().all()

    tangki_status = []
    for t in tangki_list:
        # Get latest sounding for this tank
        latest_q = (
            select(StockAdjustmentItem.volume_final_liter)
            .join(StockAdjustment, StockAdjustmentItem.stock_adjustment_id == StockAdjustment.id)
            .where(
                StockAdjustment.spbu_id == spbu_id,
                StockAdjustmentItem.tangki_id == t.id,
            )
            .order_by(StockAdjustment.tanggal.desc(), StockAdjustment.id.desc())
            .limit(1)
        )
        vol = (await db.execute(latest_q)).scalar_one_or_none()
        persen = None
        if vol is not None and t.kapasitas_liter > 0:
            persen = (vol / t.kapasitas_liter * 100).quantize(Decimal("0.1"))

        tangki_status.append(DashboardTangkiStatus(
            tangki_id=t.id,
            tangki_nama=t.nama,
            produk_nama=t.produk.nama if t.produk else None,
            kapasitas_liter=t.kapasitas_liter,
            volume_terakhir=vol,
            persen_isi=persen,
        ))

    # ── Shift status for today ───────────────────────────────────────────────
    shifts_q = (
        select(Shift)
        .where(
            Shift.spbu_id == spbu_id,
            Shift.is_active.is_(True),
            Shift.deleted_at.is_(None),
        )
        .order_by(Shift.jam_mulai)
    )
    shifts = (await db.execute(shifts_q)).scalars().all()

    shift_status = []
    for s in shifts:
        lap_q = select(LaporanShift.status).where(
            LaporanShift.spbu_id == spbu_id,
            LaporanShift.shift_id == s.id,
            LaporanShift.tanggal == tanggal_akhir,
        )
        lap_st = (await db.execute(lap_q)).scalar_one_or_none()

        sa_q = select(StockAdjustment.status).where(
            StockAdjustment.spbu_id == spbu_id,
            StockAdjustment.shift_id == s.id,
            StockAdjustment.tanggal == tanggal_akhir,
        )
        sa_st = (await db.execute(sa_q)).scalar_one_or_none()

        shift_status.append(DashboardShiftStatus(
            shift_id=s.id,
            shift_nama=s.nama,
            laporan_status=lap_st,
            stock_status=sa_st,
        ))

    # ── Pending penebusan ────────────────────────────────────────────────────
    penebusan_q = (
        select(Penebusan)
        .options(selectinload(Penebusan.items))
        .where(
            Penebusan.spbu_id == spbu_id,
            Penebusan.status.in_([
                StatusPenebusan.DRAFT,
                StatusPenebusan.WAITING_SO,
                StatusPenebusan.SUBMITTED,
                StatusPenebusan.PARTIALLY_RECEIVED,
            ]),
        )
        .order_by(Penebusan.tanggal.desc())
        .limit(10)
    )
    penebusan_rows = (await db.execute(penebusan_q)).scalars().all()
    penebusan_pending = [
        DashboardPenebusanPending(
            id=p.id,
            no_do=p.no_do,
            status=p.status,
            tanggal=p.tanggal,
            total_volume=sum((i.volume_pesan for i in p.items), Decimal("0")),
        )
        for p in penebusan_rows
    ]

    # ── Laporan pending count ────────────────────────────────────────────────
    pending_q = (
        select(func.count())
        .select_from(LaporanShift)
        .where(
            LaporanShift.spbu_id == spbu_id,
            LaporanShift.status == StatusLaporan.SUBMITTED,
        )
    )
    laporan_pending_count = (await db.execute(pending_q)).scalar_one()

    # ── Anomali count ────────────────────────────────────────────────────────
    anomali_count = await anomali_repository.count_active(db, spbu_id)

    return DashboardSummary(
        tanggal_mulai=tanggal_mulai,
        tanggal_akhir=tanggal_akhir,
        total_penjualan_volume=total_volume or Decimal("0"),
        total_penjualan_nilai=total_nilai or Decimal("0"),
        total_expenses=total_expenses,
        total_penyetoran=total_penyetoran,
        total_penerimaan_volume=total_penerimaan,
        produk_sales=produk_sales,
        tangki_status=tangki_status,
        shift_status=shift_status,
        penebusan_pending=penebusan_pending,
        anomali_count=anomali_count,
        laporan_pending_count=laporan_pending_count,
    )