from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func, case
from typing import Optional
from datetime import date
from app.database import get_db
from app.models.assignment import Assignment
from app.models.mass_schedule import MassSchedule
from app.models.asim import Asim

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


@router.get("/assignments")
def report_assignments(
    date_from: Optional[date] = Query(None),
    date_to: Optional[date] = Query(None),
    position_type_code: Optional[str] = Query(None),
    include_inactive: bool = Query(False),
    db: Session = Depends(get_db),
):
    """
    Laporan assignment per ASIM dengan fairness metrics.
    Filter: date range, position type, active/inactive.
    """

    # Base query: join Assignment → MassSchedule → Asim
    q = (
        db.query(
            Asim.id.label("asim_id"),
            Asim.no_asim,
            Asim.full_name,
            Asim.is_active,
            func.count(Assignment.id).label("total"),
        )
        .join(Assignment, Assignment.asim_id == Asim.id, isouter=True)
        .join(MassSchedule, Assignment.schedule_id == MassSchedule.id, isouter=True)
    )

    # Filter is_active
    if not include_inactive:
        q = q.filter(Asim.is_active == True)

    # Filter date range
    if date_from:
        q = q.filter(
            (MassSchedule.date >= date_from) | (Assignment.id == None)
        )
    if date_to:
        q = q.filter(
            (MassSchedule.date <= date_to) | (Assignment.id == None)
        )

    # Filter date range ketat (hanya count assignment dalam range)
    # Pendekatan: subquery per ASIM
    # Rebuild dengan subquery yang lebih presisi
    assign_q = (
        db.query(
            Assignment.asim_id,
            func.count(Assignment.id).label("cnt"),
        )
        .join(MassSchedule, Assignment.schedule_id == MassSchedule.id)
        .filter(Assignment.asim_id != None)
    )

    if date_from:
        assign_q = assign_q.filter(MassSchedule.date >= date_from)
    if date_to:
        assign_q = assign_q.filter(MassSchedule.date <= date_to)
    if position_type_code:
        assign_q = assign_q.filter(Assignment.position_type_code == position_type_code)

    assign_q = assign_q.group_by(Assignment.asim_id)
    assign_counts = {row.asim_id: row.cnt for row in assign_q.all()}

    # Query semua ASIM (filter active)
    asim_q = db.query(Asim)
    if not include_inactive:
        asim_q = asim_q.filter(Asim.is_active == True)
    asim_list = asim_q.order_by(Asim.no_asim).all()

    # Build data
    data = []
    for asim in asim_list:
        total = assign_counts.get(asim.id, 0)
        data.append({
            "asim_id": asim.id,
            "no_asim": asim.no_asim,
            "full_name": asim.full_name,
            "is_active": asim.is_active,
            "total": total,
        })

    # Sort by total desc
    data.sort(key=lambda x: (-x["total"], x["no_asim"]))

    # Fairness metrics
    totals = [d["total"] for d in data]
    avg = sum(totals) / len(totals) if totals else 0
    max_val = max(totals) if totals else 0
    min_val = min(totals) if totals else 0

    # Add deviation + fairness label per ASIM
    threshold = max(1, avg * 0.25)  # ±25% dari rata2
    for d in data:
        d["deviation"] = round(d["total"] - avg, 1)
        if d["total"] > avg + threshold:
            d["fairness"] = "above"
        elif d["total"] < avg - threshold and avg > 0:
            d["fairness"] = "below"
        else:
            d["fairness"] = "normal"

    summary = {
        "total_asim": len(data),
        "total_assignments": sum(totals),
        "avg_per_asim": round(avg, 1),
        "max_assignments": max_val,
        "min_assignments": min_val,
    }

    return {
        "summary": summary,
        "data": data,
    }