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
from app.core.database import get_db
from app.core.security import require_roles
from app.models.models import Customer, Invoice, Payment, Voucher
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"))

@router.get("/reports", response_class=HTMLResponse)
async def reports_page(request: Request, db: AsyncSession = Depends(get_db)):
    data = {
        "customers": await db.scalar(select(func.count(Customer.id)).where(Customer.is_archived == False)),
        "active_customers": await db.scalar(select(func.count(Customer.id)).where(Customer.status == "active", Customer.is_archived == False)),
        "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")),
        "payments_total": await db.scalar(select(func.coalesce(func.sum(Payment.amount), 0))),
        "voucher_available": await db.scalar(select(func.count(Voucher.id)).where(Voucher.status == "available")),
        "voucher_sold": await db.scalar(select(func.count(Voucher.id)).where(Voucher.status == "sold")),
        "voucher_used": await db.scalar(select(func.count(Voucher.id)).where(Voucher.status == "used")),
    }
    return templates.TemplateResponse(request=request, name="reports.html", context={"data": data})