"""
SQL aggregation repository for computing laboratory operational and financial metrics.
"""

from datetime import date, datetime, timedelta
from decimal import Decimal
from uuid import UUID
from zoneinfo import ZoneInfo

from sqlalchemy import false, func, literal_column, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.appointment import Appointment, AppointmentSlot
from app.models.billing import Bill, BillStatus, Payment
from app.models.order import (
    OrderPackage,
    OrderStatus,
    OrderTest,
    OrderTestStatus,
    Sample,
    SampleStatus,
    VisitOrder,
)
from app.models.package import TestPackage
from app.models.patient import Patient
from app.models.referral import CommissionEntry, RadiologistCommission, Referral
from app.models.report import Report, ReportStatus
from app.models.test import Test as LabTest
from app.schemas.dashboard import (
    DailyTimeSeriesPoint,
    FinancialStats,
    OperationalStats,
    TopItemMetric,
    TopReferralMetric,
)


class DashboardRepository:
    """Executes performant SQL aggregate queries for dashboard metrics."""

    @staticmethod
    def _day_start(day: date) -> datetime:
        # Compare indexed timestamps directly; keep timezone casts only in GROUP BY.
        return datetime(day.year, day.month, day.day, tzinfo=ZoneInfo("Asia/Kathmandu"))

    async def get_operational_stats(
        self,
        db: AsyncSession,
        lab_id: UUID | None,
        start_date: date,
        end_date: date,
    ) -> OperationalStats:
        """Compute operational volumes and status distribution."""
        # 1. Total patients
        pat_q = select(func.count(Patient.id)).where(Patient.deleted_at.is_(None))
        if lab_id:
            pat_q = pat_q.where(Patient.lab_id == lab_id)
        tot_pat = (await db.execute(pat_q)).scalar() or 0

        # 2. New patients in period
        new_pat_q = select(func.count(Patient.id)).where(
            Patient.deleted_at.is_(None),
            Patient.created_at >= self._day_start(start_date),
            Patient.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            new_pat_q = new_pat_q.where(Patient.lab_id == lab_id)
        new_pat = (await db.execute(new_pat_q)).scalar() or 0

        # 3. Total orders & orders by status in period
        order_status_q = select(
            VisitOrder.status,
            func.count(VisitOrder.id),
        ).where(
            VisitOrder.deleted_at.is_(None),
            VisitOrder.created_at >= self._day_start(start_date),
            VisitOrder.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            order_status_q = order_status_q.where(VisitOrder.lab_id == lab_id)
        order_status_q = order_status_q.group_by(VisitOrder.status)

        status_rows = (await db.execute(order_status_q)).all()
        orders_by_status: dict[str, int] = {}
        total_orders = 0
        for status_val, count in status_rows:
            key = status_val.value if hasattr(status_val, "value") else str(status_val)
            orders_by_status[key] = count
            total_orders += count

        # 4. Pending samples
        sample_q = select(func.count(Sample.id)).where(
            Sample.status == SampleStatus.PENDING,
        )
        if lab_id:
            sample_q = sample_q.where(Sample.lab_id == lab_id)
        pending_samples = (await db.execute(sample_q)).scalar() or 0

        # 5. Pending verifications (reports in draft or pending_verification)
        report_q = (
            select(func.count(Report.id))
            .join(VisitOrder, Report.order_id == VisitOrder.id)
            .where(
                Report.deleted_at.is_(None),
                VisitOrder.deleted_at.is_(None),
                Report.status.in_([ReportStatus.DRAFT, ReportStatus.PENDING_VERIFICATION]),
            )
        )
        if lab_id:
            report_q = report_q.where(VisitOrder.lab_id == lab_id)
        pending_verifications = (await db.execute(report_q)).scalar() or 0

        # 6. Today's appointments and appointments by status
        today = datetime.now(ZoneInfo("Asia/Kathmandu")).date()
        appointment_day = func.coalesce(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), AppointmentSlot.start_at)),
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Appointment.created_at)),
        )
        today_apt_q = (
            select(func.count(Appointment.id))
            .outerjoin(AppointmentSlot, Appointment.slot_id == AppointmentSlot.id)
            .where(
                Appointment.deleted_at.is_(None),
                appointment_day == today,
            )
        )
        if lab_id:
            today_apt_q = today_apt_q.where(Appointment.lab_id == lab_id)
        today_appointments = (await db.execute(today_apt_q)).scalar() or 0

        apt_status_q = select(
            Appointment.status,
            func.count(Appointment.id),
        ).where(
            Appointment.deleted_at.is_(None),
            Appointment.created_at >= self._day_start(start_date),
            Appointment.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            apt_status_q = apt_status_q.where(Appointment.lab_id == lab_id)
        apt_status_q = apt_status_q.group_by(Appointment.status)

        apt_rows = (await db.execute(apt_status_q)).all()
        appointments_by_status: dict[str, int] = {}
        for apt_val, count in apt_rows:
            key = apt_val.value if hasattr(apt_val, "value") else str(apt_val)
            appointments_by_status[key] = count

        return OperationalStats(
            total_patients=tot_pat,
            new_patients_period=new_pat,
            total_orders=total_orders,
            orders_by_status=orders_by_status,
            pending_samples=pending_samples,
            pending_verifications=pending_verifications,
            today_appointments=today_appointments,
            appointments_by_status=appointments_by_status,
        )

    async def get_financial_stats(
        self,
        db: AsyncSession,
        lab_id: UUID | None,
        start_date: date,
        end_date: date,
    ) -> FinancialStats:
        """Compute billed revenue, collection rate, outstanding dues, and commissions."""
        # 1. Bills aggregates (strictly excluding VOID bills)
        bill_q = select(
            func.coalesce(func.sum(Bill.total_amount), 0),
            func.coalesce(func.sum(Bill.paid_amount), 0),
            func.coalesce(func.sum(Bill.due_amount), 0),
            func.coalesce(func.sum(Bill.discount_amount), 0),
        ).where(
            Bill.status != BillStatus.VOID,
            Bill.deleted_at.is_(None),
            Bill.created_at >= self._day_start(start_date),
            Bill.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            bill_q = bill_q.where(Bill.lab_id == lab_id)

        bill_res = (await db.execute(bill_q)).one()
        invoiced = Decimal(str(bill_res[0]))
        collected = Decimal(str(bill_res[1]))
        outstanding = Decimal(str(bill_res[2]))
        discounts = Decimal(str(bill_res[3]))

        # 2. Revenue by tender mode
        tender_q = (
            select(
                Payment.payment_mode,
                func.coalesce(func.sum(Payment.amount), 0),
            )
            .join(Bill, Payment.bill_id == Bill.id)
            .where(
                Bill.status != BillStatus.VOID,
                Bill.deleted_at.is_(None),
                Payment.paid_at >= self._day_start(start_date),
                Payment.paid_at < self._day_start(end_date + timedelta(days=1)),
            )
        )
        if lab_id:
            tender_q = tender_q.where(Payment.lab_id == lab_id)
        tender_q = tender_q.group_by(Payment.payment_mode)

        tender_rows = (await db.execute(tender_q)).all()
        revenue_by_tender: dict[str, Decimal] = {}
        for mode_val, amt in tender_rows:
            key = mode_val.value if hasattr(mode_val, "value") else str(mode_val)
            revenue_by_tender[key] = Decimal(str(amt))

        # 3. Referral commissions (accrued vs unsettled)
        comm_q = select(
            func.coalesce(func.sum(CommissionEntry.commission_amount), 0),
        ).where(
            CommissionEntry.created_at >= self._day_start(start_date),
            CommissionEntry.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            comm_q = comm_q.where(CommissionEntry.lab_id == lab_id)
        accrued = Decimal(str((await db.execute(comm_q)).scalar() or "0.00"))

        unsettled_q = select(
            func.coalesce(func.sum(CommissionEntry.commission_amount), 0),
        ).where(
            CommissionEntry.is_settled == false(),
            CommissionEntry.created_at >= self._day_start(start_date),
            CommissionEntry.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            unsettled_q = unsettled_q.where(CommissionEntry.lab_id == lab_id)
        unsettled = Decimal(str((await db.execute(unsettled_q)).scalar() or "0.00"))

        # 4. Radiologist per-report commissions (accrued vs unsettled)
        rad_q = select(
            func.coalesce(func.sum(RadiologistCommission.commission_amount), 0),
        ).where(
            RadiologistCommission.created_at >= self._day_start(start_date),
            RadiologistCommission.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            rad_q = rad_q.where(RadiologistCommission.lab_id == lab_id)
        rad_accrued = Decimal(str((await db.execute(rad_q)).scalar() or "0.00"))

        rad_unsettled_q = select(
            func.coalesce(func.sum(RadiologistCommission.commission_amount), 0),
        ).where(
            RadiologistCommission.is_settled == false(),
            RadiologistCommission.created_at >= self._day_start(start_date),
            RadiologistCommission.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            rad_unsettled_q = rad_unsettled_q.where(RadiologistCommission.lab_id == lab_id)
        rad_unsettled = Decimal(str((await db.execute(rad_unsettled_q)).scalar() or "0.00"))

        return FinancialStats(
            total_invoiced=invoiced,
            total_collected=collected,
            total_outstanding=outstanding,
            total_discounts=discounts,
            revenue_by_tender=revenue_by_tender,
            accrued_commissions=accrued,
            unsettled_commissions=unsettled,
            radiologist_accrued_commissions=rad_accrued,
            radiologist_unsettled_commissions=rad_unsettled,
        )

    async def get_daily_trends(
        self,
        db: AsyncSession,
        lab_id: UUID | None,
        start_date: date,
        end_date: date,
    ) -> list[DailyTimeSeriesPoint]:
        """Generate contiguous daily time-series points across the date window."""
        # 1. Orders per day
        order_trend_q = select(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), VisitOrder.created_at)).label("d"),
            func.count(VisitOrder.id),
        ).where(
            VisitOrder.deleted_at.is_(None),
            VisitOrder.created_at >= self._day_start(start_date),
            VisitOrder.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            order_trend_q = order_trend_q.where(VisitOrder.lab_id == lab_id)
        order_trend_q = order_trend_q.group_by(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), VisitOrder.created_at))
        )
        order_map = {str(d): c for d, c in (await db.execute(order_trend_q)).all()}

        # 2. Revenue per day (from payments)
        rev_trend_q = (
            select(
                func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Payment.paid_at)).label("d"),
                func.coalesce(func.sum(Payment.amount), 0),
            )
            .join(Bill, Payment.bill_id == Bill.id)
            .where(
                Bill.status != BillStatus.VOID,
                Bill.deleted_at.is_(None),
                Payment.paid_at >= self._day_start(start_date),
                Payment.paid_at < self._day_start(end_date + timedelta(days=1)),
            )
        )
        if lab_id:
            rev_trend_q = rev_trend_q.where(Payment.lab_id == lab_id)
        rev_trend_q = rev_trend_q.group_by(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Payment.paid_at))
        )
        rev_map = {str(d): Decimal(str(amt)) for d, amt in (await db.execute(rev_trend_q)).all()}

        # 2b. Invoice totals and invoice count by issue date.
        invoice_trend_q = select(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Bill.created_at)).label("d"),
            func.coalesce(func.sum(Bill.total_amount), 0),
            func.count(Bill.id),
        ).where(
            Bill.deleted_at.is_(None),
            Bill.status != BillStatus.VOID,
            Bill.created_at >= self._day_start(start_date),
            Bill.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            invoice_trend_q = invoice_trend_q.where(Bill.lab_id == lab_id)
        invoice_trend_q = invoice_trend_q.group_by(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Bill.created_at))
        )
        invoice_map = {
            str(d): (Decimal(str(amount)), count)
            for d, amount, count in (await db.execute(invoice_trend_q)).all()
        }

        # 3. Patients per day
        pat_trend_q = select(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Patient.created_at)).label("d"),
            func.count(Patient.id),
        ).where(
            Patient.deleted_at.is_(None),
            Patient.created_at >= self._day_start(start_date),
            Patient.created_at < self._day_start(end_date + timedelta(days=1)),
        )
        if lab_id:
            pat_trend_q = pat_trend_q.where(Patient.lab_id == lab_id)
        pat_trend_q = pat_trend_q.group_by(
            func.date(func.timezone(literal_column("'Asia/Kathmandu'"), Patient.created_at))
        )
        pat_map = {str(d): c for d, c in (await db.execute(pat_trend_q)).all()}

        # Build contiguous daily points
        results: list[DailyTimeSeriesPoint] = []
        curr = start_date
        while curr <= end_date:
            d_str = curr.strftime("%Y-%m-%d")
            results.append(
                DailyTimeSeriesPoint(
                    date=d_str,
                    order_count=order_map.get(d_str, 0),
                    revenue=rev_map.get(d_str, Decimal("0.00")),
                    invoiced_amount=invoice_map.get(d_str, (Decimal("0.00"), 0))[0],
                    invoice_count=invoice_map.get(d_str, (Decimal("0.00"), 0))[1],
                    patient_count=pat_map.get(d_str, 0),
                )
            )
            curr += timedelta(days=1)

        return results

    async def get_top_tests(
        self,
        db: AsyncSession,
        lab_id: UUID | None,
        start_date: date,
        end_date: date,
        limit: int | None = 5,
    ) -> list[TopItemMetric]:
        """Rank diagnostic tests by order frequency and monetary value."""
        q = (
            select(
                LabTest.id,
                LabTest.name,
                LabTest.code,
                func.count(OrderTest.id).label("cnt"),
                func.coalesce(func.sum(OrderTest.price_at_order), 0).label("val"),
            )
            .join(VisitOrder, OrderTest.order_id == VisitOrder.id)
            .join(LabTest, OrderTest.test_id == LabTest.id)
            .where(
                VisitOrder.deleted_at.is_(None),
                VisitOrder.status != OrderStatus.CANCELLED,
                OrderTest.status != OrderTestStatus.CANCELLED,
                VisitOrder.created_at >= self._day_start(start_date),
                VisitOrder.created_at < self._day_start(end_date + timedelta(days=1)),
            )
        )
        if lab_id:
            q = q.where(VisitOrder.lab_id == lab_id)
        q = (
            q.group_by(LabTest.id, LabTest.name, LabTest.code)
            .order_by(func.count(OrderTest.id).desc())
            .limit(limit)
        )

        rows = (await db.execute(q)).all()
        return [
            TopItemMetric(
                id=r[0],
                name=r[1],
                code=r[2],
                count=r[3],
                total_value=Decimal(str(r[4])),
            )
            for r in rows
        ]

    async def get_top_packages(
        self,
        db: AsyncSession,
        lab_id: UUID | None,
        start_date: date,
        end_date: date,
        limit: int | None = 5,
    ) -> list[TopItemMetric]:
        """Rank wellness packages by order frequency and monetary value."""
        q = (
            select(
                TestPackage.id,
                TestPackage.name,
                TestPackage.code,
                func.count(OrderPackage.id).label("cnt"),
                func.coalesce(func.sum(OrderPackage.price_at_order), 0).label("val"),
            )
            .join(VisitOrder, OrderPackage.order_id == VisitOrder.id)
            .join(TestPackage, OrderPackage.package_id == TestPackage.id)
            .where(
                VisitOrder.deleted_at.is_(None),
                VisitOrder.status != OrderStatus.CANCELLED,
                VisitOrder.created_at >= self._day_start(start_date),
                VisitOrder.created_at < self._day_start(end_date + timedelta(days=1)),
            )
        )
        if lab_id:
            q = q.where(VisitOrder.lab_id == lab_id)
        q = (
            q.group_by(TestPackage.id, TestPackage.name, TestPackage.code)
            .order_by(func.count(OrderPackage.id).desc())
            .limit(limit)
        )

        rows = (await db.execute(q)).all()
        return [
            TopItemMetric(
                id=r[0],
                name=r[1],
                code=r[2],
                count=r[3],
                total_value=Decimal(str(r[4])),
            )
            for r in rows
        ]

    async def get_top_referrers(
        self,
        db: AsyncSession,
        lab_id: UUID | None,
        start_date: date,
        end_date: date,
        limit: int | None = 5,
    ) -> list[TopReferralMetric]:
        """Rank referral partners by referral activity and earned commissions."""
        q = (
            select(
                Referral.id,
                Referral.name,
                Referral.referral_type,
                func.count(CommissionEntry.id).label("cnt"),
                func.coalesce(func.sum(CommissionEntry.commission_amount), 0).label("earned"),
            )
            .join(Referral, CommissionEntry.referral_id == Referral.id)
            .where(
                Referral.deleted_at.is_(None),
                CommissionEntry.created_at >= self._day_start(start_date),
                CommissionEntry.created_at < self._day_start(end_date + timedelta(days=1)),
            )
        )
        if lab_id:
            q = q.where(CommissionEntry.lab_id == lab_id)
        q = (
            q.group_by(Referral.id, Referral.name, Referral.referral_type)
            .order_by(func.coalesce(func.sum(CommissionEntry.commission_amount), 0).desc())
            .limit(limit)
        )

        rows = (await db.execute(q)).all()
        return [
            TopReferralMetric(
                id=r[0],
                name=r[1],
                type=r[2].value if hasattr(r[2], "value") else str(r[2]),
                referral_count=r[3],
                commission_earned=Decimal(str(r[4])),
            )
            for r in rows
        ]