"""Rekonsiliasi Harian service — daily sounding-based reconciliation logic.

Formula per tangki:
  Stok Awal       = volume_final dari sounding shift pertama hari ini
  + Penerimaan    = BBM diterima hari itu untuk tangki ini
  + Pemindahan In
  - Penjualan     = Volume dari nozzle bersumber tangki ini
  - Pemindahan Out
  = Stok Teoritis

  Stok Aktual     = volume_final dari sounding shift terakhir hari ini
  Losses / Gain   = Stok Aktual - Stok Teoritis

Prerequisite: all shifts for the day must be at least SUBMITTED.
"""

from __future__ import annotations

from datetime import date
from decimal import Decimal

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

from app.models.operational import (
    LaporanShift,
    PenjualanNozzle,
    StatusLaporan,
    StockAdjustment,
    StockAdjustmentItem,
)
from app.models.penerimaan import Penerimaan, PenerimaanItem
from app.models.rekonsiliasi import RekonsiliasiHarian, StatusRekonsiliasi
from app.models.spbu import Nozzle, Shift, Tangki
from app.repositories import rekonsiliasi_repository
from app.schemas.rekonsiliasi import (
    RekonsiliasiHarianResponse,
    RekonsiliasiListResponse,
    RekonsiliasiTangkiResponse,
)


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


def _build_detail_response(r: RekonsiliasiHarian) -> RekonsiliasiHarianResponse:
    items = []
    for item in r.items:
        items.append(RekonsiliasiTangkiResponse(
            id=item.id,
            tangki_id=item.tangki_id,
            tangki_nama=item.tangki.nama if item.tangki else None,
            produk_nama=(
                item.tangki.produk.nama
                if item.tangki and hasattr(item.tangki, "produk") and item.tangki.produk
                else None
            ),
            stok_awal=item.stok_awal,
            penerimaan=item.penerimaan,
            pemindahan_in=item.pemindahan_in,
            pemindahan_out=item.pemindahan_out,
            penjualan=item.penjualan,
            stok_teoritis=item.stok_teoritis,
            stok_aktual=item.stok_aktual,
            losses=item.losses,
        ))
    return RekonsiliasiHarianResponse(
        id=r.id,
        spbu_id=r.spbu_id,
        tanggal=r.tanggal,
        status=r.status,
        run_by_name=r.run_by.name if r.run_by else None,
        approved_by_name=r.approved_by.name if r.approved_by else None,
        items=items,
        created_at=r.created_at,
        updated_at=r.updated_at,
    )


def _build_list_response(r: RekonsiliasiHarian) -> RekonsiliasiListResponse:
    total_losses = sum((item.losses for item in r.items), Decimal("0"))
    return RekonsiliasiListResponse(
        id=r.id,
        spbu_id=r.spbu_id,
        tanggal=r.tanggal,
        status=r.status,
        jumlah_tangki=len(r.items),
        total_losses=total_losses,
        run_by_name=r.run_by.name if r.run_by else None,
        approved_by_name=r.approved_by.name if r.approved_by else None,
        created_at=r.created_at,
        updated_at=r.updated_at,
    )


async def _get_active_shifts(db: AsyncSession, spbu_id: int) -> list[Shift]:
    """Get active shifts for an SPBU, ordered by jam_mulai."""
    result = await db.execute(
        select(Shift)
        .where(Shift.spbu_id == spbu_id, Shift.is_active.is_(True), Shift.deleted_at.is_(None))
        .order_by(Shift.jam_mulai)
    )
    return list(result.scalars().all())


async def _get_active_tangki(db: AsyncSession, spbu_id: int) -> list[Tangki]:
    """Get active tanks for an SPBU."""
    result = await db.execute(
        select(Tangki)
        .where(Tangki.spbu_id == spbu_id, Tangki.is_active.is_(True), Tangki.deleted_at.is_(None))
    )
    return list(result.scalars().all())


async def _check_all_shifts_submitted(
    db: AsyncSession, spbu_id: int, tanggal: date, shifts: list[Shift]
) -> list[str]:
    """Return list of shift names that are NOT yet submitted (or higher)."""
    not_submitted = []
    accepted = {StatusLaporan.SUBMITTED, StatusLaporan.APPROVED, StatusLaporan.LOCKED}

    for shift in shifts:
        # Check stock_adjustment
        sa_result = await db.execute(
            select(StockAdjustment.status).where(
                StockAdjustment.spbu_id == spbu_id,
                StockAdjustment.shift_id == shift.id,
                StockAdjustment.tanggal == tanggal,
            )
        )
        sa_status = sa_result.scalar_one_or_none()

        # Check laporan_shift
        ls_result = await db.execute(
            select(LaporanShift.status).where(
                LaporanShift.spbu_id == spbu_id,
                LaporanShift.shift_id == shift.id,
                LaporanShift.tanggal == tanggal,
            )
        )
        ls_status = ls_result.scalar_one_or_none()

        if sa_status not in accepted or ls_status not in accepted:
            not_submitted.append(shift.nama)

    return not_submitted


async def _get_sounding_volume(
    db: AsyncSession, spbu_id: int, shift_id: int, tanggal: date, tangki_id: int
) -> Decimal | None:
    """Get volume_final_liter for a specific tank from a stock adjustment."""
    result = await db.execute(
        select(StockAdjustmentItem.volume_final_liter)
        .join(StockAdjustment, StockAdjustmentItem.stock_adjustment_id == StockAdjustment.id)
        .where(
            StockAdjustment.spbu_id == spbu_id,
            StockAdjustment.shift_id == shift_id,
            StockAdjustment.tanggal == tanggal,
            StockAdjustmentItem.tangki_id == tangki_id,
        )
    )
    return result.scalar_one_or_none()


async def _get_penerimaan_volume(
    db: AsyncSession, spbu_id: int, tanggal: date, tangki_id: int
) -> Decimal:
    """Sum of BBM received for a tank on a given day."""
    result = await db.execute(
        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,
            PenerimaanItem.tangki_id == tangki_id,
        )
    )
    return result.scalar_one() or Decimal("0")


async def _get_penjualan_volume(
    db: AsyncSession, spbu_id: int, tanggal: date, tangki_id: int
) -> Decimal:
    """Sum of sales volume from nozzles sourced from a tank on a given day."""
    result = await db.execute(
        select(func.coalesce(func.sum(PenjualanNozzle.volume), Decimal("0")))
        .join(LaporanShift, PenjualanNozzle.laporan_shift_id == LaporanShift.id)
        .join(Nozzle, PenjualanNozzle.nozzle_id == Nozzle.id)
        .where(
            LaporanShift.spbu_id == spbu_id,
            LaporanShift.tanggal == tanggal,
            Nozzle.tangki_id == tangki_id,
        )
    )
    return result.scalar_one() or Decimal("0")


# ---------------------------------------------------------------------------
# Public API
# ---------------------------------------------------------------------------


async def list_rekonsiliasi(
    db: AsyncSession,
    spbu_id: int,
    tanggal_mulai: date | None = None,
    tanggal_akhir: date | None = None,
    status: str | None = None,
    skip: int = 0,
    limit: int = 50,
) -> tuple[list[RekonsiliasiListResponse], int]:
    rows, total = await rekonsiliasi_repository.get_all(
        db, spbu_id,
        tanggal_mulai=tanggal_mulai,
        tanggal_akhir=tanggal_akhir,
        status=status,
        skip=skip,
        limit=limit,
    )
    return [_build_list_response(r) for r in rows], total


async def get_rekonsiliasi(
    db: AsyncSession, spbu_id: int, tanggal: date
) -> RekonsiliasiHarianResponse | None:
    """Get reconciliation for a specific date, or None if not yet run."""
    rekon = await rekonsiliasi_repository.get_by_tanggal(db, spbu_id, tanggal)
    if rekon is None:
        return None
    return _build_detail_response(rekon)


async def run_rekonsiliasi(
    db: AsyncSession, spbu_id: int, tanggal: date, user_id: int
) -> RekonsiliasiHarianResponse:
    """Run (or re-run) daily reconciliation for a given date.

    Steps:
    1. Validate all shifts have submitted data
    2. For each active tank, calculate:
       stok_awal, penerimaan, penjualan, stok_teoritis, stok_aktual, losses
    3. Create or update RekonsiliasiHarian + items
    """
    # 1. Get shifts and validate
    shifts = await _get_active_shifts(db, spbu_id)
    if not shifts:
        raise ValueError("SPBU ini tidak memiliki shift aktif")

    not_submitted = await _check_all_shifts_submitted(db, spbu_id, tanggal, shifts)
    if not_submitted:
        raise ValueError(
            f"Shift berikut belum submit laporan: {', '.join(not_submitted)}. "
            "Semua shift harus sudah disubmit sebelum bisa menjalankan rekonsiliasi."
        )

    # 2. Get tanks
    tangki_list = await _get_active_tangki(db, spbu_id)
    if not tangki_list:
        raise ValueError("SPBU ini tidak memiliki tangki aktif")

    first_shift = shifts[0]
    last_shift = shifts[-1]

    # 3. Calculate per tank
    items_data = []
    for tangki in tangki_list:
        # Stok awal = sounding shift pertama
        stok_awal = await _get_sounding_volume(
            db, spbu_id, first_shift.id, tanggal, tangki.id
        )
        if stok_awal is None:
            stok_awal = Decimal("0")

        # Stok aktual = sounding shift terakhir
        # Per CLAUDE.md: stok akhir shift N = stok awal shift N+1
        # For daily recon, stok aktual = sounding of the LAST shift of the day
        stok_aktual = await _get_sounding_volume(
            db, spbu_id, last_shift.id, tanggal, tangki.id
        )
        if stok_aktual is None:
            stok_aktual = Decimal("0")

        penerimaan = await _get_penerimaan_volume(db, spbu_id, tanggal, tangki.id)
        penjualan = await _get_penjualan_volume(db, spbu_id, tanggal, tangki.id)

        # Pemindahan: not yet implemented
        pemindahan_in = Decimal("0")
        pemindahan_out = Decimal("0")

        stok_teoritis = stok_awal + penerimaan + pemindahan_in - penjualan - pemindahan_out
        losses = stok_aktual - stok_teoritis

        items_data.append({
            "tangki_id": tangki.id,
            "stok_awal": stok_awal,
            "penerimaan": penerimaan,
            "pemindahan_in": pemindahan_in,
            "pemindahan_out": pemindahan_out,
            "penjualan": penjualan,
            "stok_teoritis": stok_teoritis,
            "stok_aktual": stok_aktual,
            "losses": losses,
        })

        # Run anomali detection for this tank
        from app.utils.anomali import run_rekonsiliasi_checks
        await run_rekonsiliasi_checks(
            db, spbu_id, tangki.id, stok_teoritis, stok_aktual,
            penjualan, penerimaan, str(tanggal),
        )

    # 4. Create or update
    try:
        existing = await rekonsiliasi_repository.get_by_tanggal(db, spbu_id, tanggal)
        if existing is not None:
            # Re-run: update status and replace items
            await rekonsiliasi_repository.update_status(
                db, existing, {"status": StatusRekonsiliasi.PENDING, "run_by_id": user_id}
            )
            rekon = await rekonsiliasi_repository.upsert(db, existing, items_data)
        else:
            rekon = await rekonsiliasi_repository.create(db, {
                "spbu_id": spbu_id,
                "tanggal": tanggal,
                "status": StatusRekonsiliasi.PENDING,
                "run_by_id": user_id,
            })
            rekon = await rekonsiliasi_repository.upsert(db, rekon, items_data)
        await db.commit()
        rekon = await rekonsiliasi_repository.get_by_tanggal(db, spbu_id, tanggal)
    except IntegrityError:
        await db.rollback()
        raise ValueError("Rekonsiliasi sudah ada atau constraint error")
    except SQLAlchemyError:
        await db.rollback()
        raise

    return _build_detail_response(rekon)  # type: ignore[arg-type]


async def approve_rekonsiliasi(
    db: AsyncSession, spbu_id: int, tanggal: date, user_id: int
) -> RekonsiliasiHarianResponse:
    """Approve a pending reconciliation."""
    rekon = await rekonsiliasi_repository.get_by_tanggal(db, spbu_id, tanggal)
    if rekon is None:
        raise ValueError("Rekonsiliasi belum dijalankan untuk tanggal ini")
    if rekon.status != StatusRekonsiliasi.PENDING:
        raise ValueError(
            f"Rekonsiliasi status '{rekon.status}', hanya bisa approve dari status 'pending'"
        )

    try:
        updated = await rekonsiliasi_repository.update_status(
            db, rekon, {"status": StatusRekonsiliasi.APPROVED, "approved_by_id": user_id}
        )
        await db.commit()
        updated = await rekonsiliasi_repository.get_by_tanggal(db, spbu_id, tanggal)
    except SQLAlchemyError:
        await db.rollback()
        raise

    return _build_detail_response(updated)  # type: ignore[arg-type]