from fastapi import APIRouter, Request, Depends
from fastapi.responses import HTMLResponse
from fastapi.templating import Jinja2Templates
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, case
from app.core.database import get_db
from app.core.security import require_roles
from app.models.models import Customer, Invoice, Payment, Voucher, VoucherArchive, VoucherAgent, Site
import os

router = APIRouter(dependencies=[Depends(require_roles("super_admin", "admin_cs"))])
BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
templates = Jinja2Templates(directory=os.path.join(BASE_DIR, "app/templates"))


async def _collect_reports(db: AsyncSession, site_id: int | None = None) -> dict:
    """Build every figure the reports page needs in one pass, with optional site filter."""
    # ---- Ringkasan pelanggan ----
    cust_query = select(func.count(Customer.id)).where(Customer.is_archived == False)
    active_cust_query = select(func.count(Customer.id)).where(Customer.status == "active", Customer.is_archived == False)

    customers = await db.scalar(cust_query)
    active_customers = await db.scalar(active_cust_query)

    # ---- Invoice ----
    unpaid_invoices = await db.scalar(
        select(func.count(Invoice.id)).where(Invoice.status == "unpaid")
    )
    invoice_total = await db.scalar(
        select(func.coalesce(func.sum(Invoice.amount), 0)).where(Invoice.status == "unpaid")
    )

    # ---- Pembayaran ----
    pay_stmt = (
        select(
            Customer.name,
            Customer.service_mode,
            Payment.amount,
            Payment.period,
            Payment.method,
            Payment.status,
            Payment.paid_at,
        )
        .select_from(Payment)
        .outerjoin(Customer, Customer.id == Payment.customer_id)
    )
    if site_id:
        pay_stmt = pay_stmt.where(Payment.site_id == site_id)
    pay_stmt = pay_stmt.order_by(Payment.paid_at.desc())

    payment_rows = (await db.execute(pay_stmt)).all()

    payments_total = sum(r.amount or 0 for r in payment_rows)

    # Rekap per pelanggan
    per_customer: dict[str, dict] = {}
    for name, mode, amount, period, method, status, paid_at in payment_rows:
        key = name or "(Tanpa Pelanggan)"
        row = per_customer.setdefault(key, {"name": key, "mode": mode, "total": 0, "count": 0})
        if row["mode"] is None:
            row["mode"] = mode
        row["total"] += amount or 0
        row["count"] += 1

    per_period: dict[str, dict] = {}
    for _n, _m, amount, period, _me, _s, _p in payment_rows:
        row = per_period.setdefault(period or "-", {"period": period or "-", "total": 0, "count": 0})
        row["total"] += amount or 0
        row["count"] += 1

    per_method: dict[str, dict] = {}
    for _n, _m, amount, _pe, method, _s, _p in payment_rows:
        key = (method or "-").upper()
        row = per_method.setdefault(key, {"method": key, "total": 0, "count": 0})
        row["total"] += amount or 0
        row["count"] += 1

    orphan_payments = sum(1 for r in payment_rows if not r[0])

    # ---- Voucher aktif ----
    v_avail_stmt = select(func.count(Voucher.id)).where(Voucher.status == "available")
    v_sold_stmt = select(func.count(Voucher.id)).where(Voucher.status == "sold")
    v_used_stmt = select(func.count(Voucher.id)).where(Voucher.status == "used")
    if site_id:
        v_avail_stmt = v_avail_stmt.where(Voucher.site_id == site_id)
        v_sold_stmt = v_sold_stmt.where(Voucher.site_id == site_id)
        v_used_stmt = v_used_stmt.where(Voucher.site_id == site_id)

    voucher_available = await db.scalar(v_avail_stmt)
    voucher_sold = await db.scalar(v_sold_stmt)
    voucher_used = await db.scalar(v_used_stmt)

    # ---- Laporan keuangan voucher (dari arsip) ----
    archive_stmt = (
        select(
            VoucherArchive.code,
            VoucherArchive.profile,
            VoucherArchive.price,
            VoucherArchive.status,
            VoucherArchive.reason,
            VoucherArchive.used_seconds,
            VoucherArchive.used_at,
            VoucherArchive.deleted_at,
            VoucherArchive.agent_id,
            VoucherAgent.name,
            VoucherAgent.area,
            VoucherAgent.commission_rate,
        )
        .select_from(VoucherArchive)
        .outerjoin(VoucherAgent, VoucherAgent.id == VoucherArchive.agent_id)
    )
    if site_id:
        archive_stmt = archive_stmt.join(Voucher, Voucher.id == VoucherArchive.voucher_id).where(Voucher.site_id == site_id)
    archive_stmt = archive_stmt.order_by(VoucherArchive.deleted_at.desc())

    archive_rows = (await db.execute(archive_stmt)).all()

    voucher_revenue = sum(r[2] or 0 for r in archive_rows)
    voucher_revenue_agent = sum(r[2] or 0 for r in archive_rows if r[8] is not None)
    voucher_revenue_direct = sum(r[2] or 0 for r in archive_rows if r[8] is None)

    per_agent: dict[str, dict] = {}
    for _c, _p, price, _s, reason, _us, _ua, _da, _aid, agent_name, area, rate in archive_rows:
        key = agent_name or "Direct / Tanpa Agen"
        row = per_agent.setdefault(
            key,
            {
                "name": key,
                "area": area or "-",
                "rate": rate or 0,
                "qty": 0,
                "gross": 0,
                "commission": 0,
            },
        )
        if row["area"] == "-" and area:
            row["area"] = area
        row["qty"] += 1
        row["gross"] += price or 0
        row["commission"] += int((price or 0) * (rate or 0) / 100)
    for row in per_agent.values():
        row["net"] = row["gross"] - row["commission"]

    per_profile: dict[str, dict] = {}
    for _c, profile, price, _s, _r, _us, _ua, _da, *_rest in archive_rows:
        key = profile or "-"
        row = per_profile.setdefault(key, {"profile": key, "qty": 0, "gross": 0})
        row["qty"] += 1
        row["gross"] += price or 0

    reason_labels = {
        "expired": "Masa Berlaku Habis",
        "duration_exhausted": "Jatah Waktu Habis",
        "gone_from_device": "Lepas dari Perangkat",
    }
    per_reason: dict[str, dict] = {}
    for _c, _p, price, _s, reason, *_rest in archive_rows:
        key = reason or "-"
        row = per_reason.setdefault(
            key, {"reason": key, "label": reason_labels.get(key, key), "qty": 0, "gross": 0}
        )
        row["qty"] += 1
        row["gross"] += price or 0

    total_commission = sum(r["commission"] for r in per_agent.values())

    return {
        "customers": customers,
        "active_customers": active_customers,
        "unpaid_invoices": unpaid_invoices,
        "voucher_available": voucher_available,
        "voucher_sold": voucher_sold,
        "voucher_used": voucher_used,
        "invoice_total": invoice_total,
        "payments_total": payments_total,
        "payment_rows": payment_rows,
        "per_customer": sorted(per_customer.values(), key=lambda r: -r["total"]),
        "per_period": sorted(per_period.values(), key=lambda r: str(r["period"]), reverse=True),
        "per_method": sorted(per_method.values(), key=lambda r: -r["total"]),
        "orphan_payments": orphan_payments,
        "voucher_revenue": voucher_revenue,
        "voucher_revenue_agent": voucher_revenue_agent,
        "voucher_revenue_direct": voucher_revenue_direct,
        "voucher_archive_count": len(archive_rows),
        "per_agent": sorted(per_agent.values(), key=lambda r: -r["gross"]),
        "per_profile": sorted(per_profile.values(), key=lambda r: -r["gross"]),
        "per_reason": sorted(per_reason.values(), key=lambda r: -r["gross"]),
        "total_commission": total_commission,
        "voucher_net": voucher_revenue - total_commission,
        "grand_total": payments_total + voucher_revenue,
        "archive_rows": archive_rows,
    }


@router.get("/reports", response_class=HTMLResponse)
async def reports_page(request: Request, db: AsyncSession = Depends(get_db)):
    site_id_str = request.query_params.get("site_id")
    parsed_site_id = int(site_id_str) if site_id_str and str(site_id_str).isdigit() else None
    sites = (await db.execute(select(Site).where(Site.is_active == True))).scalars().all()
    data = await _collect_reports(db, site_id=parsed_site_id)
    return templates.TemplateResponse(
        request=request,
        name="reports.html",
        context={"data": data, "sites": sites, "selected_site_id": parsed_site_id}
    )
