"""Analytics service — daily trend data for charts."""

from __future__ import annotations

from datetime import date, timedelta
from decimal import Decimal

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

from app.models.expenses import Expense
from app.models.operational import LaporanShift, PenjualanNozzle
from app.models.penerimaan import Penerimaan, PenerimaanItem
from app.models.product import Produk
from app.models.spbu import Nozzle


async def get_daily_trend(
    db: AsyncSession,
    spbu_id: int,
    tanggal_mulai: date,
    tanggal_akhir: date,
) -> list[dict]:
    """Return per-day aggregation of sales & expenses for trend charts."""
    days = []
    current = tanggal_mulai
    while current <= tanggal_akhir:
        # Sales
        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 == current)
        )
        vol, val = (await db.execute(sales_q)).one()

        # Expenses
        exp_q = (
            select(func.coalesce(func.sum(Expense.jumlah), Decimal("0")))
            .where(Expense.spbu_id == spbu_id, Expense.tanggal == current)
        )
        expenses = (await db.execute(exp_q)).scalar_one() or Decimal("0")

        # Penerimaan
        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 == current)
        )
        penerimaan = (await db.execute(pen_q)).scalar_one() or Decimal("0")

        days.append({
            "tanggal": current.isoformat(),
            "penjualan_volume": str(vol or Decimal("0")),
            "penjualan_nilai": str(val or Decimal("0")),
            "expenses": str(expenses),
            "penerimaan_volume": str(penerimaan),
        })
        current += timedelta(days=1)

    return days


async def get_produk_trend(
    db: AsyncSession,
    spbu_id: int,
    tanggal_mulai: date,
    tanggal_akhir: date,
) -> list[dict]:
    """Return per-product aggregation for the period."""
    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())
    )
    rows = (await db.execute(q)).all()
    return [
        {
            "produk_id": r[0],
            "produk_nama": r[1],
            "produk_kode": r[2],
            "total_volume": str(r[3]),
            "total_nilai": str(r[4]),
        }
        for r in rows
    ]