"""Admin payment reporting service (Increment 2 — read-only observability).

Everything the admin Payments dashboard needs — KPI cards, a filterable/searchable
table, per-payment drawer data, a derived lifecycle timeline, revenue analytics
and CSV export — built in a fixed, small number of grouped aggregation queries
(never row-by-row, never N+1). The Increment 1 payment *processing* (orders,
verification, webhooks, activation) is untouched; this module only reads.

The lifecycle timeline is **derived** from the payment's own fields (status +
timestamps) — no duplicate per-event storage — reusing the same approach as the
advertisement drawer's lifecycle.
"""

from __future__ import annotations

from datetime import datetime, time, timedelta
from decimal import Decimal

from sqlalchemy import func, or_
from sqlalchemy.orm import selectinload

from app.extensions import db
from app.models import (
    PaymentStatus,
    PremiumRequest,
    PremiumRequestStatus,
    Subscription,
    SubscriptionStatus,
    User,
    UserProfile,
)
from app.models.base import utcnow
from app.payments.models import Payment

# Status groupings used across KPIs, filters and charts (single source of truth).
_SUCCESS = PaymentStatus.CAPTURED
_PENDING = (PaymentStatus.CREATED, PaymentStatus.AUTHORIZED)

# Refunds are performed externally (Razorpay Dashboard) and only synced in via
# webhook — they are not an in-app filter/metric, so REFUNDED is omitted from the
# admin filter options. Refunded payments still appear in the table as history.
STATUS_OPTIONS = [(s.value, s.value.title()) for s in PaymentStatus if s is not PaymentStatus.REFUNDED]
PROVIDER_LABELS = {"razorpay": "Razorpay", "offline": "Offline"}
SORT_OPTIONS = [
    ("recent", "Newest first"),
    ("oldest", "Oldest first"),
    ("amount_high", "Amount (high → low)"),
    ("amount_low", "Amount (low → high)"),
]
PAGE_SIZE = 20
TREND_WINDOW_DAYS = 30


# --- KPI cards -------------------------------------------------------------

def payment_kpis() -> dict:
    """KPI figures for the dashboard cards — three aggregate queries total."""
    # One grouped pass: per-status count + summed amount.
    rows = db.session.execute(
        db.select(Payment.status, func.count(), func.coalesce(func.sum(Payment.amount), 0))
        .group_by(Payment.status)
    ).all()
    by_status = {s: {"count": int(c), "amount": Decimal(str(a or 0))} for s, c, a in rows}

    def count(*statuses):
        return sum(by_status.get(s, {}).get("count", 0) for s in statuses)

    captured = by_status.get(_SUCCESS, {"count": 0, "amount": Decimal("0")})
    total_revenue = captured["amount"]
    successful = captured["count"]
    failed = count(PaymentStatus.FAILED)
    pending = count(*_PENDING)
    refunded_row = by_status.get(PaymentStatus.REFUNDED, {"count": 0, "amount": Decimal("0")})

    month_start = datetime.combine(utcnow().date().replace(day=1), time.min)
    revenue_month = db.session.scalar(
        db.select(func.coalesce(func.sum(Payment.amount), 0)).where(
            Payment.status == _SUCCESS, Payment.paid_at >= month_start
        )
    ) or 0

    active_subscribers = db.session.scalar(
        db.select(func.count(func.distinct(Subscription.user_id))).where(
            Subscription.status == SubscriptionStatus.ACTIVE, Subscription.end_date > utcnow()
        )
    ) or 0

    completed = successful + failed
    success_rate = round(successful / completed * 100, 1) if completed else 0.0

    return {
        "total_revenue": total_revenue,
        "revenue_month": Decimal(str(revenue_month)),
        "successful": successful,
        "failed": failed,
        "pending": pending,
        "refunded": refunded_row["count"],
        "refunded_amount": refunded_row["amount"],
        "active_subscribers": int(active_subscribers),
        "success_rate": success_rate,
        "total_payments": sum(v["count"] for v in by_status.values()),
    }


# --- Filterable / searchable table -----------------------------------------

def _apply_filters(query, *, status, provider, plan, q, amount_min, amount_max, date_from, date_to):
    if status in {s.value for s in PaymentStatus}:
        query = query.where(Payment.status == PaymentStatus(status))
    if provider:
        query = query.where(Payment.provider == provider)
    if plan:
        query = query.where(Payment.plan_name == plan)
    if amount_min is not None:
        query = query.where(Payment.amount >= amount_min)
    if amount_max is not None:
        query = query.where(Payment.amount <= amount_max)
    if date_from:
        query = query.where(Payment.created_at >= datetime.combine(date_from, time.min))
    if date_to:
        query = query.where(Payment.created_at <= datetime.combine(date_to, time.max))
    if q:
        like = f"%{q.strip()}%"
        # Search user name/email + gateway ids + public id (Phase 9).
        query = query.where(
            or_(
                UserProfile.full_name.ilike(like),
                User.email.ilike(like),
                Payment.provider_order_id.ilike(like),
                Payment.provider_payment_id.ilike(like),
                Payment.public_id.ilike(like),
            )
        )
    return query


def _sorted(query, sort):
    if sort == "oldest":
        return query.order_by(Payment.created_at.asc())
    if sort == "amount_high":
        return query.order_by(Payment.amount.desc(), Payment.created_at.desc())
    if sort == "amount_low":
        return query.order_by(Payment.amount.asc(), Payment.created_at.desc())
    return query.order_by(Payment.created_at.desc())


def _base_query(filters):
    # Join once for search/eager-load; selectinload avoids the per-row profile N+1.
    return _apply_filters(
        db.select(Payment)
        .join(User, Payment.user_id == User.id)
        .outerjoin(UserProfile, UserProfile.user_id == User.id)
        .options(selectinload(Payment.user).selectinload(User.profile)),
        **filters,
    )


def list_payments(filters: dict, sort: str = "recent", page: int = 1):
    """Paginated payments matching ``filters`` (newest first by default)."""
    query = _sorted(_base_query(filters), sort)
    return db.paginate(query, page=page, per_page=PAGE_SIZE, error_out=False)


def plan_options() -> list[str]:
    """Distinct plan names seen in payments (for the plan filter)."""
    return [p for (p,) in db.session.execute(
        db.select(Payment.plan_name).distinct().order_by(Payment.plan_name)
    ).all()]


def provider_options() -> list[tuple[str, str]]:
    """Distinct providers seen in payments, labelled (for the provider filter)."""
    rows = [p for (p,) in db.session.execute(
        db.select(Payment.provider).distinct().order_by(Payment.provider)
    ).all()]
    return [(p, PROVIDER_LABELS.get(p, p.title())) for p in rows]


# --- Per-payment detail + derived timeline ---------------------------------

def get_payment_or_404(public_id: str) -> Payment:
    return db.first_or_404(
        db.select(Payment)
        .options(selectinload(Payment.user).selectinload(User.profile))
        .where(Payment.public_id == public_id)
    )


def payment_timeline(payment: Payment) -> list[dict]:
    """A chronological lifecycle derived from the payment's own state.

    No separate event table — the steps are inferred from status + timestamps
    (mirrors the advertisement drawer's derived lifecycle). Each step is
    ``{label, at, done}``; ``at`` may be None when the exact time isn't stored.
    """
    created = payment.created_at
    paid = payment.paid_at
    steps: list[dict] = [{"label": "Order created", "at": created, "done": True}]

    if payment.status == PaymentStatus.FAILED:
        steps.append({"label": "Payment failed", "at": payment.updated_at, "done": True})
        return steps

    captured = payment.status in (PaymentStatus.CAPTURED, PaymentStatus.REFUNDED)
    steps.append({"label": "Payment captured", "at": paid, "done": captured})
    steps.append({"label": "Premium activated", "at": paid,
                  "done": captured and payment.subscription_id is not None})
    steps.append({"label": "Notification sent", "at": paid, "done": captured})

    if payment.status == PaymentStatus.REFUNDED:
        steps.append({"label": "Refund processed", "at": payment.updated_at, "done": True})
        steps.append({"label": "Premium revoked", "at": payment.updated_at, "done": True})
    return steps


def payment_meta(payment: Payment) -> dict:
    """Non-sensitive detail for the drawer. Never exposes secrets — only whether a
    signature was verified, not its value."""
    return {
        "webhook_status": "Captured via gateway" if payment.paid_at else "Awaiting capture",
        "signature_verified": bool(payment.provider_signature) or payment.status in (
            PaymentStatus.CAPTURED, PaymentStatus.REFUNDED),
    }


# --- Revenue analytics (Phase 8) -------------------------------------------

def _daily_revenue(start_date, days: int = TREND_WINDOW_DAYS) -> list[dict]:
    start_dt = datetime.combine(start_date, time.min)
    rows = db.session.execute(
        db.select(func.date(Payment.paid_at), func.coalesce(func.sum(Payment.amount), 0))
        .where(Payment.status == _SUCCESS, Payment.paid_at >= start_dt)
        .group_by(func.date(Payment.paid_at))
    ).all()
    totals = {str(d)[:10]: float(a or 0) for d, a in rows}
    return [
        {"date": (start_date + timedelta(days=i)).isoformat(),
         "amount": totals.get((start_date + timedelta(days=i)).isoformat(), 0.0)}
        for i in range(days)
    ]


def revenue_analytics() -> dict:
    """Chart-ready series for the dashboard (all grouped queries)."""
    today = utcnow().date()
    start_date = today - timedelta(days=TREND_WINDOW_DAYS - 1)

    daily = _daily_revenue(start_date)

    status_rows = dict(db.session.execute(
        db.select(Payment.status, func.count()).group_by(Payment.status)
    ).all())
    # Refunds are external + synced-only, so they are not shown as a status metric.
    status_series = {
        "labels": ["Successful", "Failed", "Pending"],
        "values": [
            int(status_rows.get(_SUCCESS, 0)),
            int(status_rows.get(PaymentStatus.FAILED, 0)),
            int(status_rows.get(PaymentStatus.CREATED, 0)) + int(status_rows.get(PaymentStatus.AUTHORIZED, 0)),
        ],
    }

    plan_rows = db.session.execute(
        db.select(Payment.plan_name, func.coalesce(func.sum(Payment.amount), 0))
        .where(Payment.status == _SUCCESS)
        .group_by(Payment.plan_name)
        .order_by(func.sum(Payment.amount).desc())
    ).all()

    # Provider breakdown — provider-agnostic. Online providers come from the
    # payments table; "Offline" reuses the manual approval count (no Payment row).
    provider_rows = dict(db.session.execute(
        db.select(Payment.provider, func.count())
        .where(Payment.status == _SUCCESS).group_by(Payment.provider)
    ).all())
    offline = db.session.scalar(
        db.select(func.count(PremiumRequest.id)).where(
            PremiumRequest.status == PremiumRequestStatus.APPROVED)
    ) or 0
    provider_labels = [PROVIDER_LABELS.get(p, p.title()) for p in provider_rows] + ["Offline"]
    provider_values = [int(c) for c in provider_rows.values()] + [int(offline)]

    # Premium conversion trend — new subscriptions per day.
    conv_start = datetime.combine(start_date, time.min)
    conv_rows = dict(db.session.execute(
        db.select(func.date(Subscription.start_date), func.count())
        .where(Subscription.start_date >= conv_start)
        .group_by(func.date(Subscription.start_date))
    ).all())
    conversion = {
        "labels": [(start_date + timedelta(days=i)).isoformat()[5:] for i in range(TREND_WINDOW_DAYS)],
        "values": [int(conv_rows.get(str((start_date + timedelta(days=i)).isoformat()), 0))
                   for i in range(TREND_WINDOW_DAYS)],
    }

    return {
        "revenue_over_time": {"labels": [d["date"][5:] for d in daily],
                              "values": [d["amount"] for d in daily]},
        "status_distribution": status_series,
        "revenue_by_plan": {"labels": [p for p, _ in plan_rows],
                            "values": [float(a or 0) for _, a in plan_rows]},
        "provider_breakdown": {"labels": provider_labels, "values": provider_values},
        "conversion_trend": conversion,
    }


# --- CSV export (Phase 10) -------------------------------------------------

CSV_HEADER = ["User", "Email", "Plan", "Amount", "Currency", "Status",
              "Provider", "Order ID", "Payment ID", "Created", "Paid"]


def payments_csv_rows(filters: dict, sort: str = "recent") -> list[list[str]]:
    """All matching payments as CSV rows (respects filters — not just one page)."""
    payments = db.session.scalars(_sorted(_base_query(filters), sort)).all()
    rows = [CSV_HEADER]
    for p in payments:
        profile = p.user.profile if p.user else None
        rows.append([
            (profile.full_name if profile else (p.user.email if p.user else "—")),
            (p.user.email if p.user else "—"),
            p.plan_name,
            f"{p.amount:.2f}",
            p.currency,
            p.status.value,
            p.provider,
            p.provider_order_id or "",
            p.provider_payment_id or "",
            p.created_at.strftime("%Y-%m-%d %H:%M") if p.created_at else "",
            p.paid_at.strftime("%Y-%m-%d %H:%M") if p.paid_at else "",
        ])
    return rows