"""Reports router — occupancy, revenue, and audit reports."""
from typing import Optional

from fastapi import APIRouter, Depends, Query
from sqlalchemy import select, func, and_
from sqlalchemy.ext.asyncio import AsyncSession

from src.core.dependencies import require_min_role, require_tenant
from src.core.constants import UserRole
from src.core.schemas.response import success
from src.database.session import get_db
from src.apps.auth.models.user import User
from src.apps.plots.models.plot import Plot
from src.apps.billing.models.invoice import Invoice
from src.apps.records.models.record import Record

router = APIRouter(prefix="/reports", tags=["reports"])


@router.get("/occupancy")
async def occupancy_report(
    section_id: Optional[str] = Query(None),
    current_user: User = Depends(require_min_role(UserRole.STAFF)),
    tenant_id: str = Depends(require_tenant),
    db: AsyncSession = Depends(get_db),
):
    """Plot occupancy breakdown by status."""
    filters = [Plot.tenant_id == tenant_id]
    if section_id:
        filters.append(Plot.section_id == section_id)

    result = await db.execute(
        select(Plot.status, func.count(Plot.id).label("count"))
        .where(*filters)
        .group_by(Plot.status)
    )
    rows = result.all()
    total = sum(r.count for r in rows)
    breakdown = {r.status: r.count for r in rows}

    occupied = breakdown.get("occupied", 0)
    vacant = breakdown.get("vacant", 0)
    reserved = breakdown.get("reserved", 0)

    return success({
        "total_plots": total,
        "breakdown": breakdown,
        "occupancy_rate": round(occupied / total * 100, 2) if total else 0,
        "available_plots": vacant,
        "reserved_plots": reserved,
    })


@router.get("/revenue")
async def revenue_report(
    year: Optional[int] = Query(None),
    month: Optional[int] = Query(None),
    current_user: User = Depends(require_min_role(UserRole.MANAGER)),
    tenant_id: str = Depends(require_tenant),
    db: AsyncSession = Depends(get_db),
):
    """Revenue summary from invoices."""
    filters = [Invoice.tenant_id == tenant_id]

    if year:
        filters.append(func.extract("year", Invoice.created_at) == year)
    if month:
        filters.append(func.extract("month", Invoice.created_at) == month)

    result = await db.execute(
        select(
            Invoice.status,
            func.count(Invoice.id).label("count"),
            func.coalesce(func.sum(Invoice.total), 0).label("total"),
            func.coalesce(func.sum(Invoice.amount_paid), 0).label("paid"),
            func.coalesce(func.sum(Invoice.balance), 0).label("outstanding"),
        )
        .where(*filters)
        .group_by(Invoice.status)
    )
    rows = result.all()
    breakdown = [
        {
            "status": r.status,
            "count": r.count,
            "total": float(r.total),
            "paid": float(r.paid),
            "outstanding": float(r.outstanding),
        }
        for r in rows
    ]
    grand_total = sum(r["total"] for r in breakdown)
    grand_paid = sum(r["paid"] for r in breakdown)
    grand_outstanding = sum(r["outstanding"] for r in breakdown)

    return success({
        "breakdown": breakdown,
        "summary": {
            "total_invoiced": grand_total,
            "total_collected": grand_paid,
            "total_outstanding": grand_outstanding,
            "collection_rate": round(grand_paid / grand_total * 100, 2) if grand_total else 0,
        },
    })


@router.get("/records-summary")
async def records_summary(
    current_user: User = Depends(require_min_role(UserRole.STAFF)),
    tenant_id: str = Depends(require_tenant),
    db: AsyncSession = Depends(get_db),
):
    """Summary counts for records."""
    total = (
        await db.execute(
            select(func.count(Record.id)).where(
                Record.tenant_id == tenant_id,
                Record.deleted_at.is_(None),
            )
        )
    ).scalar_one()

    veterans = (
        await db.execute(
            select(func.count(Record.id)).where(
                Record.tenant_id == tenant_id,
                Record.is_veteran.is_(True),
                Record.deleted_at.is_(None),
            )
        )
    ).scalar_one()

    return success({
        "total_records": total,
        "veteran_records": veterans,
        "non_veteran_records": total - veterans,
    })