import csv
from decimal import Decimal
from html import escape
from io import StringIO

import nepali_datetime
from django.db import models
from django.db.models import Q, Sum
from django.db.models.functions import Coalesce
from django.http import HttpResponse
from drf_spectacular.utils import extend_schema, OpenApiResponse, inline_serializer, OpenApiParameter
from rest_framework import serializers as drf_serializers
from rest_framework.exceptions import ValidationError
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from rest_framework.views import APIView
from users.permissions import is_admin_user

from .models import (
    ImportSubmission,
    ExportSubmission,
    RevenueFineSubmission,
    HealthInspectionSubmission,
    DestructionSubmission,
)
from .unit_conversion import UnitConversionNormalizer

NEPALI_MONTHS = {
    1: {"name": "Baishakh", "short": "BAI"},
    2: {"name": "Jestha", "short": "JES"},
    3: {"name": "Ashadh", "short": "ASR"},
    4: {"name": "Shrawan", "short": "SHR"},
    5: {"name": "Bhadra", "short": "BHA"},
    6: {"name": "Ashwin", "short": "ASW"},
    7: {"name": "Kartik", "short": "KAR"},
    8: {"name": "Mangsir", "short": "MAN"},
    9: {"name": "Poush", "short": "POU"},
    10: {"name": "Magh", "short": "MAG"},
    11: {"name": "Falgun", "short": "FAL"},
    12: {"name": "Chaitra", "short": "CHA"},
}

FISCAL_MONTH_ORDER = list(range(4, 13)) + list(range(1, 4))


def _coalesce_zero(field):
    return Coalesce(Sum(field), Decimal("0.0"), output_field=models.DecimalField())


def _parse_fiscal_year_start(raw_value):
    if raw_value is None:
        return None

    value = str(raw_value).strip()
    if not value:
        return None

    normalized = value.replace(" ", "")
    for sep in ("/", "-"):
        if sep not in normalized:
            continue

        parts = normalized.split(sep)
        if len(parts) != 2:
            break

        start_raw, end_raw = parts
        if not start_raw.isdigit() or not end_raw.isdigit():
            break

        start_year = int(start_raw)
        end_year = int(end_raw)

        if len(end_raw) == 2:
            if end_year != (start_year + 1) % 100:
                break
        elif len(end_raw) == 4:
            if end_year != start_year + 1:
                break
        else:
            break

        return start_year

    if normalized.isdigit():
        return int(normalized)

    raise ValidationError(
        {
            "fiscal_year": (
                "Invalid fiscal year format. Use one of: 2082/83, 2082-83, "
                "2082/2083, or 2082."
            )
        }
    )


def _get_nepali_year(request):
    """Parse fiscal year start from query params, defaulting to current Nepali year."""
    current_year = nepali_datetime.date.today().year

    fiscal_year_raw = request.query_params.get("fiscal_year")
    if fiscal_year_raw is not None and str(fiscal_year_raw).strip():
        return _parse_fiscal_year_start(fiscal_year_raw)

    year_raw = request.query_params.get("year")
    if year_raw is None or not str(year_raw).strip():
        return current_year

    try:
        return int(str(year_raw).strip())
    except (ValueError, TypeError):
        # Backward-compatible: also accept fiscal year labels passed accidentally in ?year=
        try:
            return _parse_fiscal_year_start(year_raw)
        except ValidationError:
            return current_year


def _fiscal_year_qs(qs, fiscal_year, prefix="nepali"):
    """Filter a queryset to the Nepali fiscal year.
    Nepal FY starts Shrawan (month 4) and ends Ashad (month 3) of the next year.
    e.g. FY 2081 = Shrawan 2081 (m4) → Ashad 2082 (m3)
    """
    first_half = Q(**{f"{prefix}_year": fiscal_year, f"{prefix}_month__gte": 4})   # Shrawan-Chaitra
    second_half = Q(**{f"{prefix}_year": fiscal_year + 1, f"{prefix}_month__lte": 3})  # Baishakh-Ashad
    return qs.filter(first_half | second_half)


def _scope_queryset_for_user(queryset, user):
    if is_admin_user(user):
        return queryset

    user_checkpoint = getattr(user, "checkpoint", None)
    if not user_checkpoint:
        return queryset.none()

    return queryset.filter(checkpoint=user_checkpoint)


def _parse_bs_date(raw_date):
    if not raw_date:
        return None

    value = str(raw_date).strip()
    for sep in ("-", "/"):
        parts = value.split(sep)
        if len(parts) != 3 or not all(part.isdigit() for part in parts):
            continue

        if len(parts[0]) == 4:
            year, month, day = map(int, parts)
        else:
            day, month, year = map(int, parts)

        try:
            return nepali_datetime.date(year, month, day)
        except Exception:
            continue

    raise ValidationError(
        {
            "date_range": (
                "Invalid B.S. date format. Use YYYY-MM-DD, YYYY/MM/DD, "
                "DD-MM-YYYY, or DD/MM/YYYY."
            )
        }
    )


def _get_bs_date_range(request):
    from_raw = request.query_params.get("from_bs_date") or request.query_params.get("from_bs")
    to_raw = request.query_params.get("to_bs_date") or request.query_params.get("to_bs")

    from_bs_date = _parse_bs_date(from_raw) if from_raw else None
    to_bs_date = _parse_bs_date(to_raw) if to_raw else None

    if from_bs_date and to_bs_date and from_bs_date > to_bs_date:
        raise ValidationError({"date_range": "'from_bs_date' cannot be after 'to_bs_date'."})

    return from_bs_date, to_bs_date


def _apply_bs_date_range_filter(qs, from_bs_date=None, to_bs_date=None, prefix="nepali"):
    yf, mf, df = f"{prefix}_year", f"{prefix}_month", f"{prefix}_day"
    if from_bs_date:
        qs = qs.filter(
            Q(**{f"{yf}__gt": from_bs_date.year})
            | Q(**{yf: from_bs_date.year, f"{mf}__gt": from_bs_date.month})
            | Q(**{yf: from_bs_date.year, mf: from_bs_date.month, f"{df}__gte": from_bs_date.day})
        )

    if to_bs_date:
        qs = qs.filter(
            Q(**{f"{yf}__lt": to_bs_date.year})
            | Q(**{yf: to_bs_date.year, f"{mf}__lt": to_bs_date.month})
            | Q(**{yf: to_bs_date.year, mf: to_bs_date.month, f"{df}__lte": to_bs_date.day})
        )

    return qs


def _apply_commodity_filters(qs, commodity_category_id=None, commodity_type_id=None):
    if commodity_category_id:
        qs = qs.filter(commodity_type__category_id=commodity_category_id)
    if commodity_type_id:
        qs = qs.filter(commodity_type_id=commodity_type_id)
    return qs


def _parse_checkpoint_filters(request):
    checkpoint_values = []
    for param_name in ("checkpoint", "checkpoints"):
        for raw_value in request.query_params.getlist(param_name):
            checkpoint_values.extend(
                value.strip()
                for value in str(raw_value).split(",")
                if value and value.strip()
            )

    # Preserve order while removing duplicates.
    return list(dict.fromkeys(checkpoint_values))


def _validate_checkpoint_filter_access(user, checkpoint_ids):
    if not checkpoint_ids or is_admin_user(user):
        return

    user_checkpoint = getattr(user, "checkpoint", None)
    if not user_checkpoint:
        raise ValidationError(
            {
                "checkpoint": (
                    "No checkpoint is assigned to this user. Please contact an administrator."
                )
            }
        )

    if any(str(user_checkpoint.id) != str(checkpoint_id) for checkpoint_id in checkpoint_ids):
        raise ValidationError(
            {
                "checkpoint": (
                    "You can only filter by your assigned checkpoint."
                )
            }
        )


def _fiscal_year_label(year):
    return f"{year}/{(year + 1) % 100:02d}"


def _decimal_text(value):
    number = Decimal(str(value))
    text = format(number, "f")
    if "." in text:
        text = text.rstrip("0").rstrip(".")
    return text or "0"


def _monthly_pivot_data(monthly_data):
    month_headers = [entry["month_name"] for entry in monthly_data]
    month_lookup = {entry["month_name"]: entry["nepali_month"] for entry in monthly_data}
    rows = {}
    month_totals = {month_name: Decimal("0.0") for month_name in month_headers}

    for entry in monthly_data:
        month_name = entry["month_name"]
        for item in entry["items"]:
            key = (item["commodity"], item["unit"])
            if key not in rows:
                rows[key] = {
                    "item": item["commodity"],
                    "unit": item["unit"],
                    "months": {name: Decimal("0.0") for name in month_headers},
                    "total": Decimal("0.0"),
                }

            qty = Decimal(str(item["total_quantity"]))
            rows[key]["months"][month_name] += qty
            rows[key]["total"] += qty
            month_totals[month_name] += qty

    ordered_rows = sorted(rows.values(), key=lambda row: row["total"], reverse=True)
    grand_total = sum(month_totals.values(), Decimal("0.0"))

    return {
        "month_headers": month_headers,
        "month_lookup": month_lookup,
        "rows": ordered_rows,
        "month_totals": month_totals,
        "grand_total": grand_total,
    }


def _monthly_data_to_csv(title, fiscal_year, monthly_data):
    pivot = _monthly_pivot_data(monthly_data)
    output = StringIO()
    writer = csv.writer(output)

    writer.writerow([title])
    writer.writerow([f"Fiscal Year: {fiscal_year}"])
    writer.writerow([])

    writer.writerow(["Item / Description", "Unit", *pivot["month_headers"], "Total"])
    for row in pivot["rows"]:
        writer.writerow(
            [
                row["item"],
                row["unit"],
                *[_decimal_text(row["months"][month]) for month in pivot["month_headers"]],
                _decimal_text(row["total"]),
            ]
        )

    writer.writerow(
        [
            "Grand Total (Mixed Units)",
            "",
            *[_decimal_text(pivot["month_totals"][month]) for month in pivot["month_headers"]],
            _decimal_text(pivot["grand_total"]),
        ]
    )
    return output.getvalue()


def _monthly_data_to_print_html(title, fiscal_year, monthly_data):
    pivot = _monthly_pivot_data(monthly_data)

    headers_html = "".join(f"<th>{escape(month)}</th>" for month in pivot["month_headers"])
    rows_html = []
    for row in pivot["rows"]:
        month_cells = "".join(
            f"<td>{escape(_decimal_text(row['months'][month]))}</td>"
            for month in pivot["month_headers"]
        )
        rows_html.append(
            "<tr>"
            f"<td>{escape(row['item'])}</td>"
            f"<td>{escape(row['unit'])}</td>"
            f"{month_cells}"
            f"<td><strong>{escape(_decimal_text(row['total']))}</strong></td>"
            "</tr>"
        )

    totals_cells = "".join(
        f"<td><strong>{escape(_decimal_text(pivot['month_totals'][month]))}</strong></td>"
        for month in pivot["month_headers"]
    )

    return (
        "<!doctype html>"
        "<html><head><meta charset='utf-8'>"
        f"<title>{escape(title)}</title>"
        "<style>"
        "body{font-family:Arial,sans-serif;margin:24px;color:#111;}"
        "table{border-collapse:collapse;width:100%;}"
        "th,td{border:1px solid #ddd;padding:8px;text-align:left;font-size:13px;}"
        "th{background:#f5f7fb;}"
        "</style>"
        "</head><body>"
        f"<h2>{escape(title)}</h2>"
        f"<p><strong>Fiscal Year:</strong> {escape(fiscal_year)}</p>"
        "<table>"
        "<thead><tr><th>Item / Description</th><th>Unit</th>"
        f"{headers_html}<th>Total</th></tr></thead>"
        "<tbody>"
        f"{''.join(rows_html)}"
        "<tr>"
        "<td><strong>Grand Total (Mixed Units)</strong></td><td></td>"
        f"{totals_cells}"
        f"<td><strong>{escape(_decimal_text(pivot['grand_total']))}</strong></td>"
        "</tr>"
        "</tbody></table></body></html>"
    )


def _render_simple_pdf(lines):
    safe_lines = list(lines)
    max_lines = 55
    if len(safe_lines) > max_lines:
        safe_lines = safe_lines[: max_lines - 1] + ["... (truncated)"]

    def _esc(text):
        return text.replace("\\", "\\\\").replace("(", "\\(").replace(")", "\\)")

    stream_lines = ["BT", "/F1 9 Tf", "12 TL", "36 792 Td"]
    for index, line in enumerate(safe_lines):
        if index > 0:
            stream_lines.append("T*")
        stream_lines.append(f"({_esc(line)}) Tj")
    stream_lines.append("ET")
    content = "\n".join(stream_lines).encode("latin-1", errors="replace")

    objects = [
        b"<< /Type /Catalog /Pages 2 0 R >>",
        b"<< /Type /Pages /Kids [3 0 R] /Count 1 >>",
        b"<< /Type /Page /Parent 2 0 R /MediaBox [0 0 612 842] /Contents 4 0 R /Resources << /Font << /F1 5 0 R >> >> >>",
        b"<< /Length %d >>\nstream\n%b\nendstream" % (len(content), content),
        b"<< /Type /Font /Subtype /Type1 /BaseFont /Courier >>",
    ]

    pdf = bytearray(b"%PDF-1.4\n%\xe2\xe3\xcf\xd3\n")
    offsets = [0]

    for index, obj in enumerate(objects, start=1):
        offsets.append(len(pdf))
        pdf.extend(f"{index} 0 obj\n".encode("ascii"))
        pdf.extend(obj)
        pdf.extend(b"\nendobj\n")

    xref_offset = len(pdf)
    pdf.extend(f"xref\n0 {len(offsets)}\n".encode("ascii"))
    pdf.extend(b"0000000000 65535 f \n")
    for offset in offsets[1:]:
        pdf.extend(f"{offset:010d} 00000 n \n".encode("ascii"))

    pdf.extend(
        (
            f"trailer\n<< /Size {len(offsets)} /Root 1 0 R >>\n"
            f"startxref\n{xref_offset}\n%%EOF"
        ).encode("ascii")
    )
    return bytes(pdf)


def _monthly_data_to_pdf_bytes(title, fiscal_year, monthly_data):
    pivot = _monthly_pivot_data(monthly_data)
    month_headers = [
        NEPALI_MONTHS[pivot["month_lookup"][month]]["short"]
        for month in pivot["month_headers"]
    ]

    lines = [
        title,
        f"Fiscal Year: {fiscal_year}",
        "",
        "Item/Description | Unit | " + " | ".join(month_headers) + " | Total",
    ]

    for row in pivot["rows"]:
        month_values = " | ".join(
            _decimal_text(row["months"][month]) for month in pivot["month_headers"]
        )
        lines.append(
            f"{row['item']} | {row['unit']} | {month_values} | {_decimal_text(row['total'])}"
        )

    lines.append("")
    totals = " | ".join(
        _decimal_text(pivot["month_totals"][month]) for month in pivot["month_headers"]
    )
    lines.append(f"Grand Total (Mixed Units) |  | {totals} | {_decimal_text(pivot['grand_total'])}")

    return _render_simple_pdf(lines)


# ─── Inline OpenAPI schema helpers ───────────────────────────────────────────

_year_param = OpenApiParameter(
    name="year",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description=(
        "Nepali fiscal year start year (B.S.), e.g. 2082 means FY 2082/83 "
        "(Shrawan 2082 - Ashad 2083). "
        "Defaults to the current fiscal year."
    ),
)

_fiscal_year_param = OpenApiParameter(
    name="fiscal_year",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description=(
        "Nepali fiscal year label. Supported formats: 2082/83, 2082-83, 2082/2083, 2082. "
        "When provided, this takes precedence over `year`."
    ),
)

_from_bs_date_param = OpenApiParameter(
    name="from_bs_date",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description=(
        "Start B.S. date filter (inclusive). Supported formats: "
        "YYYY-MM-DD, YYYY/MM/DD, DD-MM-YYYY, DD/MM/YYYY."
    ),
)

_to_bs_date_param = OpenApiParameter(
    name="to_bs_date",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description=(
        "End B.S. date filter (inclusive). Supported formats: "
        "YYYY-MM-DD, YYYY/MM/DD, DD-MM-YYYY, DD/MM/YYYY."
    ),
)

_commodity_category_param = OpenApiParameter(
    name="commodity_category",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description="Commodity category UUID filter.",
)

_commodity_type_param = OpenApiParameter(
    name="commodity_type",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description="Commodity type UUID filter.",
)

_checkpoint_param = OpenApiParameter(
    name="checkpoint",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description=(
        "Checkpoint UUID filter. Accepts a single UUID or comma-separated UUIDs. "
        "Admin can filter by any checkpoint; staff can only use their assigned checkpoint."
    ),
)

_checkpoints_param = OpenApiParameter(
    name="checkpoints",
    type=str,
    location=OpenApiParameter.QUERY,
    required=False,
    description=(
        "Alternative multi-checkpoint filter. Accepts comma-separated UUIDs "
        "or repeated query params."
    ),
)



_report_category_param = OpenApiParameter(
    name="report_category",
    type=str,
    location=OpenApiParameter.QUERY,
    required=True,
    description="Report category: import, export, disposal, health.",
)

_export_format_param = OpenApiParameter(
    name="export_format",
    type=str,
    location=OpenApiParameter.QUERY,
    required=True,
    description="Export format: print, excel, pdf.",
)

_date_range_params = [_from_bs_date_param, _to_bs_date_param]
_commodity_filter_params = [_commodity_category_param, _commodity_type_param]
_fiscal_year_params = [_year_param, _fiscal_year_param]

class CheckpointEntrySchemaSerializer(drf_serializers.Serializer):
    checkpoint__name = drf_serializers.CharField()
    total_value = drf_serializers.DecimalField(max_digits=15, decimal_places=2)


class CheckpointReportSchemaSerializer(drf_serializers.Serializer):
    imports = CheckpointEntrySchemaSerializer(many=True)
    exports = CheckpointEntrySchemaSerializer(many=True)
    revenue = CheckpointEntrySchemaSerializer(many=True)


class CheckpointTotalsSchemaSerializer(drf_serializers.Serializer):
    checkpoint_id = drf_serializers.UUIDField()
    checkpoint_name = drf_serializers.CharField()
    import_value = drf_serializers.DecimalField(max_digits=15, decimal_places=2)
    export_value = drf_serializers.DecimalField(max_digits=15, decimal_places=2)
    revenue_value = drf_serializers.DecimalField(max_digits=15, decimal_places=2)


class DashboardSummaryResponseSchemaSerializer(drf_serializers.Serializer):
    fiscal_year = drf_serializers.CharField()
    total_import_value = drf_serializers.DecimalField(max_digits=15, decimal_places=2)
    total_export_value = drf_serializers.DecimalField(max_digits=15, decimal_places=2)
    total_revenue = drf_serializers.DecimalField(max_digits=15, decimal_places=2)
    reports_by_checkpoint = CheckpointReportSchemaSerializer()
    checkpoint_totals = CheckpointTotalsSchemaSerializer(many=True)


_summary_response = DashboardSummaryResponseSchemaSerializer

_monthly_response = inline_serializer(
    name="MonthlyTrendsResponse",
    fields={
        "nepali_year": drf_serializers.IntegerField(),
        "monthly_trends": inline_serializer(
            name="MonthlyEntry",
            fields={
                "nepali_month": drf_serializers.IntegerField(),
                "month_name": drf_serializers.CharField(),
                "total_import": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
                "total_export": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
                "total_revenue": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
            }
        ),
    }
)

_quarterly_response = inline_serializer(
    name="QuarterlyTrendsResponse",
    fields={
        "nepali_year": drf_serializers.IntegerField(),
        "quarterly_trends": inline_serializer(
            name="QuarterlyEntry",
            fields={
                "nepali_quarter": drf_serializers.IntegerField(),
                "total_import": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
                "total_export": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
                "total_revenue": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
            }
        ),
    }
)

_commodity_detail = inline_serializer(
    name="CommodityDetail",
    fields={
        "commodity": drf_serializers.CharField(),
        "unit": drf_serializers.CharField(),
        "total_quantity": drf_serializers.DecimalField(max_digits=15, decimal_places=2),
    }
)

_monthly_commodity_response = inline_serializer(
    name="MonthlyCommodityResponse",
    fields={
        "fiscal_year": drf_serializers.CharField(),
        "monthly_data": inline_serializer(
            name="MonthlyCommodityEntry",
            fields={
                "nepali_month": drf_serializers.IntegerField(),
                "month_name": drf_serializers.CharField(),
                "items": drf_serializers.ListField(child=_commodity_detail),
            }
        ),
    }
)


# ─── Endpoint 1: Overall Summary + Checkpoint Breakdown ──────────────────────

@extend_schema(
    tags=["Dashboard"],
    summary="Overall totals and checkpoint breakdown (fiscal year)",
    description=(
        "Returns total import, export, and revenue values for the specified Nepali fiscal year, "
        "plus a per-checkpoint breakdown. FY 2081 = Shrawan 2081 to Ashad 2082. "
        "Date filters apply to import_date, export_date, and revenue_date."
    ),
    parameters=[*_fiscal_year_params, *_date_range_params, _checkpoint_param, _checkpoints_param],
    responses={200: OpenApiResponse(response=_summary_response, description="Overall summary data")},
)
class DashboardSummaryAPIView(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request, *args, **kwargs):
        year = _get_nepali_year(request)
        from_bs_date, to_bs_date = _get_bs_date_range(request)
        checkpoint_ids = _parse_checkpoint_filters(request)

        _validate_checkpoint_filter_access(request.user, checkpoint_ids)

        import_qs = _scope_queryset_for_user(
            _fiscal_year_qs(ImportSubmission.objects.all(), year, prefix="import_nepali"),
            request.user,
        )
        export_qs = _scope_queryset_for_user(
            _fiscal_year_qs(ExportSubmission.objects.all(), year, prefix="export_nepali"),
            request.user,
        )
        revenue_qs = _scope_queryset_for_user(
            _fiscal_year_qs(
                RevenueFineSubmission.objects.all(),
                year,
                prefix="revenue_nepali",
            ),
            request.user,
        )

        import_qs = _apply_bs_date_range_filter(
            import_qs, from_bs_date, to_bs_date, prefix="import_nepali"
        )
        export_qs = _apply_bs_date_range_filter(
            export_qs, from_bs_date, to_bs_date, prefix="export_nepali"
        )
        revenue_qs = _apply_bs_date_range_filter(
            revenue_qs, from_bs_date, to_bs_date, prefix="revenue_nepali"
        )

        if checkpoint_ids:
            import_qs = import_qs.filter(checkpoint_id__in=checkpoint_ids)
            export_qs = export_qs.filter(checkpoint_id__in=checkpoint_ids)
            revenue_qs = revenue_qs.filter(checkpoint_id__in=checkpoint_ids)

        total_import = import_qs.aggregate(total=_coalesce_zero('amount_in_npr'))['total']
        total_export = export_qs.aggregate(total=_coalesce_zero('amount_in_npr'))['total']
        total_revenue = revenue_qs.aggregate(total=_coalesce_zero('amount_in_npr'))['total']

        imports_by_checkpoint_rows = list(
            import_qs.values('checkpoint_id', 'checkpoint__name')
            .annotate(total_value=_coalesce_zero('amount_in_npr'))
            .order_by('-total_value')
        )
        exports_by_checkpoint_rows = list(
            export_qs.values('checkpoint_id', 'checkpoint__name')
            .annotate(total_value=_coalesce_zero('amount_in_npr'))
            .order_by('-total_value')
        )
        revenue_by_checkpoint_rows = list(
            revenue_qs.values('checkpoint_id', 'checkpoint__name')
            .annotate(total_value=_coalesce_zero('amount_in_npr'))
            .order_by('-total_value')
        )

        imports_by_checkpoint = [
            {
                "checkpoint__name": row["checkpoint__name"],
                "total_value": row["total_value"],
            }
            for row in imports_by_checkpoint_rows
        ]
        exports_by_checkpoint = [
            {
                "checkpoint__name": row["checkpoint__name"],
                "total_value": row["total_value"],
            }
            for row in exports_by_checkpoint_rows
        ]
        revenue_by_checkpoint = [
            {
                "checkpoint__name": row["checkpoint__name"],
                "total_value": row["total_value"],
            }
            for row in revenue_by_checkpoint_rows
        ]

        checkpoint_totals_map = {}

        def _ensure_checkpoint_total_row(checkpoint_id, checkpoint_name):
            key = str(checkpoint_id)
            if key not in checkpoint_totals_map:
                checkpoint_totals_map[key] = {
                    "checkpoint_id": checkpoint_id,
                    "checkpoint_name": checkpoint_name,
                    "import_value": Decimal("0.0"),
                    "export_value": Decimal("0.0"),
                    "revenue_value": Decimal("0.0"),
                }
            return checkpoint_totals_map[key]

        for row in imports_by_checkpoint_rows:
            checkpoint_row = _ensure_checkpoint_total_row(
                row["checkpoint_id"], row["checkpoint__name"]
            )
            checkpoint_row["import_value"] = row["total_value"]

        for row in exports_by_checkpoint_rows:
            checkpoint_row = _ensure_checkpoint_total_row(
                row["checkpoint_id"], row["checkpoint__name"]
            )
            checkpoint_row["export_value"] = row["total_value"]

        for row in revenue_by_checkpoint_rows:
            checkpoint_row = _ensure_checkpoint_total_row(
                row["checkpoint_id"], row["checkpoint__name"]
            )
            checkpoint_row["revenue_value"] = row["total_value"]

        checkpoint_totals = sorted(
            checkpoint_totals_map.values(),
            key=lambda row: row["checkpoint_name"],
        )

        return Response({
            "fiscal_year": _fiscal_year_label(year),
            "total_import_value": total_import,
            "total_export_value": total_export,
            "total_revenue": total_revenue,
            "reports_by_checkpoint": {
                "imports": imports_by_checkpoint,
                "exports": exports_by_checkpoint,
                "revenue": revenue_by_checkpoint,
            },
            "checkpoint_totals": checkpoint_totals,
        })


# ─── Endpoint 2: Monthly Trends ──────────────────────────────────────────────

@extend_schema(
    tags=["Dashboard"],
    summary="Monthly import/export/revenue trends (B.S. calendar)",
    description=(
        "Returns a 12-month breakdown of import, export, and revenue totals "
        "for the specified Nepali fiscal year. Date filters apply to import_date, "
        "export_date, and revenue_date."
    ),
    parameters=[*_fiscal_year_params, *_date_range_params, *_commodity_filter_params, _checkpoint_param, _checkpoints_param],
    responses={200: OpenApiResponse(response=_monthly_response, description="Monthly trend data")},
)
class DashboardMonthlyAPIView(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request, *args, **kwargs):
        year = _get_nepali_year(request)
        from_bs_date, to_bs_date = _get_bs_date_range(request)
        commodity_category_id = request.query_params.get("commodity_category")
        commodity_type_id = request.query_params.get("commodity_type")
        checkpoint_ids = _parse_checkpoint_filters(request)

        _validate_checkpoint_filter_access(request.user, checkpoint_ids)

        # Monthly breakdown across full fiscal year (Shrawan Y → Ashad Y+1)
        # We aggregate all 12 months: months 4-12 from 'year', months 1-3 from 'year+1'
        def _month_totals(Model, prefix, apply_commodity=False):
            base_qs = _scope_queryset_for_user(Model.objects.all(), request.user)
            base_qs = _fiscal_year_qs(base_qs, year, prefix=prefix)
            base_qs = _apply_bs_date_range_filter(
                base_qs, from_bs_date, to_bs_date, prefix=prefix
            )
            if checkpoint_ids:
                base_qs = base_qs.filter(checkpoint_id__in=checkpoint_ids)
            if apply_commodity:
                base_qs = _apply_commodity_filters(
                    base_qs,
                    commodity_category_id=commodity_category_id,
                    commodity_type_id=commodity_type_id,
                )
            return {
                row[f"{prefix}_month"]: row["total"]
                for row in base_qs.values(f"{prefix}_month").annotate(
                    total=_coalesce_zero("amount_in_npr")
                )
            }

        monthly_imports = _month_totals(
            ImportSubmission, prefix="import_nepali", apply_commodity=True
        )
        monthly_exports = _month_totals(
            ExportSubmission, prefix="export_nepali", apply_commodity=True
        )
        monthly_revenue = _month_totals(
            RevenueFineSubmission, prefix="revenue_nepali"
        )

        # Fiscal year order: Shrawan(4) → Chaitra(12), then Baishakh(1) → Ashad(3)
        fiscal_month_order = FISCAL_MONTH_ORDER
        monthly_trends = [
            {
                "nepali_month": month,
                "month_name": NEPALI_MONTHS[month]["name"],
                "total_import": monthly_imports.get(month, Decimal("0.0")),
                "total_export": monthly_exports.get(month, Decimal("0.0")),
                "total_revenue": monthly_revenue.get(month, Decimal("0.0")),
            }
            for month in fiscal_month_order
        ]

        return Response({
            "fiscal_year": _fiscal_year_label(year),
            "monthly_trends": monthly_trends,
        })


# ─── Endpoint 3: Quarterly Trends ────────────────────────────────────────────

@extend_schema(
    tags=["Dashboard"],
    summary="Quarterly import/export/revenue trends (B.S. calendar)",
    description=(
        "Returns a 4-quarter breakdown of import, export, and revenue totals "
        "for the specified Nepali fiscal year. "
        "Q1=Shrawan-Ashwin, Q2=Kartik-Poush, Q3=Magh-Chaitra, Q4=Baishakh-Ashad. "
        "Date filters apply to import_date, export_date, and revenue_date."
    ),
    parameters=[*_fiscal_year_params, *_date_range_params, *_commodity_filter_params, _checkpoint_param, _checkpoints_param],
    responses={200: OpenApiResponse(response=_quarterly_response, description="Quarterly trend data")},
)
class DashboardQuarterlyAPIView(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request, *args, **kwargs):
        year = _get_nepali_year(request)
        from_bs_date, to_bs_date = _get_bs_date_range(request)
        commodity_category_id = request.query_params.get("commodity_category")
        commodity_type_id = request.query_params.get("commodity_type")
        checkpoint_ids = _parse_checkpoint_filters(request)

        _validate_checkpoint_filter_access(request.user, checkpoint_ids)

        import_qs = _scope_queryset_for_user(
            _fiscal_year_qs(ImportSubmission.objects.all(), year, prefix="import_nepali"),
            request.user,
        )
        export_qs = _scope_queryset_for_user(
            _fiscal_year_qs(ExportSubmission.objects.all(), year, prefix="export_nepali"),
            request.user,
        )
        revenue_qs = _scope_queryset_for_user(
            _fiscal_year_qs(
                RevenueFineSubmission.objects.all(),
                year,
                prefix="revenue_nepali",
            ),
            request.user,
        )

        import_qs = _apply_bs_date_range_filter(
            import_qs, from_bs_date, to_bs_date, prefix="import_nepali"
        )
        export_qs = _apply_bs_date_range_filter(
            export_qs, from_bs_date, to_bs_date, prefix="export_nepali"
        )
        revenue_qs = _apply_bs_date_range_filter(
            revenue_qs, from_bs_date, to_bs_date, prefix="revenue_nepali"
        )
        if checkpoint_ids:
            import_qs = import_qs.filter(checkpoint_id__in=checkpoint_ids)
            export_qs = export_qs.filter(checkpoint_id__in=checkpoint_ids)
            revenue_qs = revenue_qs.filter(checkpoint_id__in=checkpoint_ids)
        import_qs = _apply_commodity_filters(
            import_qs,
            commodity_category_id=commodity_category_id,
            commodity_type_id=commodity_type_id,
        )
        export_qs = _apply_commodity_filters(
            export_qs,
            commodity_category_id=commodity_category_id,
            commodity_type_id=commodity_type_id,
        )

        quarterly_imports = {
            r["import_nepali_quarter"]: r["total"]
            for r in import_qs.values("import_nepali_quarter").annotate(
                total=_coalesce_zero("amount_in_npr")
            )
        }
        quarterly_exports = {
            r["export_nepali_quarter"]: r["total"]
            for r in export_qs.values("export_nepali_quarter").annotate(
                total=_coalesce_zero("amount_in_npr")
            )
        }
        quarterly_revenue = {
            r["revenue_nepali_quarter"]: r["total"]
            for r in revenue_qs.values("revenue_nepali_quarter").annotate(
                total=_coalesce_zero("amount_in_npr")
            )
        }

        quarterly_trends = [
            {
                "nepali_quarter": q,
                "quarter_label": ["Q1 (Shrawan-Ashwin)", "Q2 (Kartik-Poush)", "Q3 (Magh-Chaitra)", "Q4 (Baishakh-Ashad)"][q - 1],
                "total_import": quarterly_imports.get(q, Decimal("0.0")),
                "total_export": quarterly_exports.get(q, Decimal("0.0")),
                "total_revenue": quarterly_revenue.get(q, Decimal("0.0")),
            }
            for q in range(1, 5)
        ]

        return Response({
            "fiscal_year": _fiscal_year_label(year),
            "quarterly_trends": quarterly_trends,
        })


# ─── Detailed Commodity Analytics ─────────────────────────────────────────────

def _get_monthly_commodity_breakdown(
    Model,
    year,
    user,
    quantity_field="quantity",
    from_bs_date=None,
    to_bs_date=None,
    commodity_category_id=None,
    commodity_type_id=None,
    extra_filters=None,
    prefix="nepali",
    unit_normalizer=None,
):
    """
    Helper to aggregate commodity quantities by month across a fiscal year.
    Returns a list of 12 months with nested commodity totals.
    """
    # 1. Fetch filtered and aggregated data
    base_qs = _scope_queryset_for_user(Model.objects.all(), user)
    qs = _fiscal_year_qs(base_qs, year, prefix=prefix)
    qs = _apply_bs_date_range_filter(qs, from_bs_date, to_bs_date, prefix=prefix)
    qs = _apply_commodity_filters(
        qs,
        commodity_category_id=commodity_category_id,
        commodity_type_id=commodity_type_id,
    )
    if extra_filters:
        qs = qs.filter(**extra_filters)
    
    if unit_normalizer is None:
        unit_normalizer = UnitConversionNormalizer()

    # We group by month, commodity name, and unit id/name.
    raw_data = (
        qs.values(
            f"{prefix}_month",
            "commodity_type__name",
            "unit_of_measurement_id",
            "unit_of_measurement__name",
        )
        .annotate(total_quantity=Coalesce(Sum(quantity_field), Decimal("0.0"), output_field=models.DecimalField()))
        .order_by(f"{prefix}_month", "commodity_type__name")
    )

    # 2. Organize by month for easy lookup
    data_by_month = {}
    for entry in raw_data:
        m = entry[f"{prefix}_month"]
        if m not in data_by_month:
            data_by_month[m] = {}

        normalized_quantity, normalized_unit_name = unit_normalizer.normalize_quantity(
            entry["total_quantity"],
            entry["unit_of_measurement_id"],
            fallback_unit_name=entry["unit_of_measurement__name"] or "",
        )

        commodity_name = entry["commodity_type__name"]
        row_key = (commodity_name, normalized_unit_name)
        data_by_month[m][row_key] = data_by_month[m].get(row_key, Decimal("0.0")) + normalized_quantity

    # 3. Format into the standard 12-month fiscal order
    monthly_data = [
        {
            "nepali_month": month,
            "month_name": NEPALI_MONTHS[month]["name"],
            "items": [
                {
                    "commodity": commodity_name,
                    "unit": unit_name,
                    "total_quantity": quantity,
                }
                for (commodity_name, unit_name), quantity in sorted(
                    data_by_month.get(month, {}).items(),
                    key=lambda item: (item[0][0] or "", item[0][1] or ""),
                )
            ],
        }
        for month in FISCAL_MONTH_ORDER
    ]
    return monthly_data


class BaseMonthlyCommodityAPIView(APIView):
    permission_classes = [IsAuthenticated]
    model = None
    quantity_field = "quantity"
    date_prefix = "nepali"
    summary_text = ""

    def get(self, request, *args, **kwargs):
        year = _get_nepali_year(request)
        from_bs_date, to_bs_date = _get_bs_date_range(request)
        commodity_category_id = request.query_params.get("commodity_category")
        commodity_type_id = request.query_params.get("commodity_type")
        monthly_data = _get_monthly_commodity_breakdown(
            self.model,
            year,
            request.user,
            self.quantity_field,
            from_bs_date=from_bs_date,
            to_bs_date=to_bs_date,
            commodity_category_id=commodity_category_id,
            commodity_type_id=commodity_type_id,
            prefix=self.date_prefix,
        )
        return Response({
            "fiscal_year": _fiscal_year_label(year),
            "monthly_data": monthly_data,
        })


@extend_schema(
    tags=["Dashboard"],
    summary="Monthly Import Commodities Breakdown",
    description=(
        "Detailed monthly breakdown of imported commodities, units, and quantities. "
        "Date filters apply directly to import_date."
    ),
    parameters=[*_fiscal_year_params, *_date_range_params, *_commodity_filter_params],
    responses={200: OpenApiResponse(response=_monthly_commodity_response)},
)
class DashboardImportMonthlyAPIView(BaseMonthlyCommodityAPIView):
    model = ImportSubmission
    quantity_field = "total_quantity"
    date_prefix = "import_nepali"


@extend_schema(
    tags=["Dashboard"],
    summary="Monthly Export Commodities Breakdown",
    description=(
        "Detailed monthly breakdown of exported commodities, units, and quantities. "
        "Date filters apply directly to export_date."
    ),
    parameters=[*_fiscal_year_params, *_date_range_params, *_commodity_filter_params],
    responses={200: OpenApiResponse(response=_monthly_commodity_response)},
)
class DashboardExportMonthlyAPIView(BaseMonthlyCommodityAPIView):
    model = ExportSubmission
    date_prefix = "export_nepali"


@extend_schema(
    tags=["Dashboard"],
    summary="Monthly Disposal/Destruction Breakdown",
    description="Detailed monthly breakdown of destroyed commodities (disposals), units, and quantities. Note: The `year`, `from_bs_date`, and `to_bs_date` filters apply directly to the destruction dates, not the submission dates.",
    parameters=[*_fiscal_year_params, *_date_range_params, *_commodity_filter_params],
    responses={200: OpenApiResponse(response=_monthly_commodity_response)},
)
class DashboardDisposalMonthlyAPIView(BaseMonthlyCommodityAPIView):
    model = DestructionSubmission

    def get(self, request, *args, **kwargs):
        year = _get_nepali_year(request)
        from_bs_date, to_bs_date = _get_bs_date_range(request)
        commodity_category_id = request.query_params.get("commodity_category")
        commodity_type_id = request.query_params.get("commodity_type")
        
        monthly_data = _get_monthly_commodity_breakdown(
            self.model,
            year,
            request.user,
            self.quantity_field,
            from_bs_date=from_bs_date,
            to_bs_date=to_bs_date,
            commodity_category_id=commodity_category_id,
            commodity_type_id=commodity_type_id,
            prefix="destruction_nepali",
        )
        return Response({
            "fiscal_year": _fiscal_year_label(year),
            "monthly_data": monthly_data,
        })


@extend_schema(
    tags=["Dashboard"],
    summary="Monthly Health Inspection Breakdown",
    description=(
        "Detailed monthly breakdown of commodities inspected for health compliance. "
        "Date filters apply directly to health_date."
    ),
    parameters=[*_fiscal_year_params, *_date_range_params, *_commodity_filter_params],
    responses={200: OpenApiResponse(response=_monthly_commodity_response)},
)
class DashboardHealthMonthlyAPIView(BaseMonthlyCommodityAPIView):
    model = HealthInspectionSubmission
    date_prefix = "health_nepali"


_REPORT_CATEGORY_CONFIG = {
    "import": {
        "model": ImportSubmission,
        "quantity_field": "total_quantity",
        "title": "Monthly Import Breakdown",
        "prefix": "import_nepali",
        "date_field": "import_date",
    },
    "export": {
        "model": ExportSubmission,
        "quantity_field": "quantity",
        "title": "Monthly Export Breakdown",
        "prefix": "export_nepali",
        "date_field": "export_date",
    },
    "disposal": {
        "model": DestructionSubmission,
        "quantity_field": "quantity",
        "title": "Monthly Disposal Breakdown",
        "prefix": "destruction_nepali",
        "date_field": "destruction_date",
    },
    "health": {
        "model": HealthInspectionSubmission,
        "quantity_field": "quantity",
        "title": "Monthly Health Inspection Breakdown",
        "prefix": "health_nepali",
        "date_field": "health_date",
    },
}


@extend_schema(
    tags=["Dashboard"],
    summary="Export Monthly Commodity Reports",
    description=(
        "Exports monthly commodity report data in print-ready HTML, Excel-friendly CSV, "
        "or PDF format using the same fiscal year/date/category/type filters. "
        "Date filters apply to import_date, export_date, destruction_date, or health_date "
        "depending on report_category."
    ),
    parameters=[
        _report_category_param,
        _export_format_param,
        *_fiscal_year_params,
        *_date_range_params,
        *_commodity_filter_params,
    ],
    responses={
        200: OpenApiResponse(description="Export file response."),
    },
)
class DashboardMonthlyExportAPIView(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request, *args, **kwargs):
        report_category = (
            request.query_params.get("report_category", "").strip().lower()
        )
        export_format = request.query_params.get("export_format", "").strip().lower()

        if report_category not in _REPORT_CATEGORY_CONFIG:
            raise ValidationError(
                {
                    "report_category": (
                        "Invalid report category. Use one of: import, export, disposal, health."
                    )
                }
            )
        if export_format not in {"print", "excel", "pdf"}:
            raise ValidationError(
                {"export_format": "Invalid format. Use one of: print, excel, pdf."}
            )

        year = _get_nepali_year(request)
        from_bs_date, to_bs_date = _get_bs_date_range(request)
        commodity_category_id = request.query_params.get("commodity_category")
        commodity_type_id = request.query_params.get("commodity_type")

        config = _REPORT_CATEGORY_CONFIG[report_category]

        monthly_data = _get_monthly_commodity_breakdown(
            config["model"],
            year,
            request.user,
            config["quantity_field"],
            from_bs_date=from_bs_date,
            to_bs_date=to_bs_date,
            commodity_category_id=commodity_category_id,
            commodity_type_id=commodity_type_id,
            prefix=config["prefix"],
        )
        fiscal_year = _fiscal_year_label(year)
        report_title = config["title"]
        filename_base = f"{report_category}_monthly_report_{year}"

        if export_format == "excel":
            csv_content = _monthly_data_to_csv(report_title, fiscal_year, monthly_data)
            response = HttpResponse(csv_content, content_type="text/csv; charset=utf-8")
            response["Content-Disposition"] = (
                f'attachment; filename="{filename_base}.csv"'
            )
            return response

        if export_format == "pdf":
            pdf_content = _monthly_data_to_pdf_bytes(
                report_title, fiscal_year, monthly_data
            )
            response = HttpResponse(pdf_content, content_type="application/pdf")
            response["Content-Disposition"] = (
                f'attachment; filename="{filename_base}.pdf"'
            )
            return response

        html_content = _monthly_data_to_print_html(
            report_title, fiscal_year, monthly_data
        )
        response = HttpResponse(html_content, content_type="text/html; charset=utf-8")
        response["Content-Disposition"] = f'inline; filename="{filename_base}.html"'
        return response