"""
Authorizations CRUD — database operations for the authorizations report module.

All queries are merchant-scoped and filter deleted_at IS NULL.
No business logic lives here — that belongs in services.py.
"""
from datetime import datetime, timezone
from typing import Dict, List, Optional, Tuple

from sqlalchemy import asc, desc, func, or_, select
from sqlalchemy.orm import Session, joinedload

from src.apps.customers.models.customer import Customer
from src.apps.payment_requests.models.payment_request import PaymentRequest
from src.apps.payment_requests.models.payment_request_authorizations import (
    PaymentRequestAuthorizations,
)


def get_authorization_list(
    db: Session,
    merchant_id: int,
    status: Optional[str] = None,
    authorization_type: Optional[str] = None,
    date_from: Optional[datetime] = None,
    date_to: Optional[datetime] = None,
    search: Optional[str] = None,
    amount_min: Optional[float] = None,
    amount_max: Optional[float] = None,
    sort_by: str = "created_at",
    sort_desc: bool = True,
    page: int = 1,
    per_page: int = 20,
) -> Tuple[List[PaymentRequestAuthorizations], int]:
    """
    Return a paginated list of authorizations for a merchant with optional filters.

    Eagerly loads: payment_request, customer, payer (CustomerContact).
    """
    stmt = (
        select(PaymentRequestAuthorizations)
        .join(
            PaymentRequest,
            PaymentRequestAuthorizations.payment_request_id == PaymentRequest.id,
        )
        .join(Customer, PaymentRequestAuthorizations.customer_id == Customer.id)
        .options(
            joinedload(PaymentRequestAuthorizations.payment_request),
            joinedload(PaymentRequestAuthorizations.customer),
            joinedload(PaymentRequestAuthorizations.payer),
        )
        .where(
            PaymentRequestAuthorizations.merchant_id == merchant_id,
            PaymentRequestAuthorizations.deleted_at.is_(None),
        )
    )

    if status:
        stmt = stmt.where(PaymentRequestAuthorizations.status == status)
    if authorization_type:
        stmt = stmt.where(
            PaymentRequestAuthorizations.authorization_type == authorization_type
        )
    if date_from:
        stmt = stmt.where(PaymentRequestAuthorizations.created_at >= date_from)
    if date_to:
        stmt = stmt.where(PaymentRequestAuthorizations.created_at <= date_to)
    if search:
        search_term = f"%{search}%"
        stmt = stmt.where(
            or_(
                PaymentRequestAuthorizations.authorization_id.ilike(search_term),
                Customer.first_name.ilike(search_term),
                Customer.last_name.ilike(search_term),
                Customer.email.ilike(search_term),
            )
        )
    if amount_min is not None:
        stmt = stmt.where(PaymentRequest.amount >= amount_min)
    if amount_max is not None:
        stmt = stmt.where(PaymentRequest.amount <= amount_max)

    # Count total matching rows (before pagination)
    count_stmt = select(func.count()).select_from(stmt.subquery())
    total = db.execute(count_stmt).scalar_one()

    # Sorting
    _sort_map = {
        "created_at": PaymentRequestAuthorizations.created_at,
        "authorization_date": PaymentRequestAuthorizations.authorization_date,
        "status": PaymentRequestAuthorizations.status,
        "authorization_type": PaymentRequestAuthorizations.authorization_type,
    }
    sort_col = _sort_map.get(sort_by, PaymentRequestAuthorizations.created_at)
    stmt = stmt.order_by(desc(sort_col) if sort_desc else asc(sort_col))

    # Pagination
    offset = (page - 1) * per_page
    stmt = stmt.offset(offset).limit(per_page)

    items = list(db.execute(stmt).scalars().unique().all())
    return items, total


def get_authorization_by_authorization_id(
    db: Session,
    authorization_id: str,
    merchant_id: int,
) -> Optional[PaymentRequestAuthorizations]:
    """
    Fetch a single authorization by its string authorization_id OR authorization_literal
    OR integer pk, scoped to merchant.

    Lookup priority:
      1. authorization_id field (UUID-style, e.g. "auth_XXX")
      2. authorization_literal field (formatted, e.g. "AUTH-XXXXXXXX")
      3. integer primary key (fallback for old records without auth_id/literal)

    Eagerly loads all relationships needed for the detail view.
    """
    _options = [
        joinedload(PaymentRequestAuthorizations.payment_request),
        joinedload(PaymentRequestAuthorizations.customer),
        joinedload(PaymentRequestAuthorizations.payer),
        joinedload(PaymentRequestAuthorizations.signature_file),
        joinedload(PaymentRequestAuthorizations.hpp_session),
        joinedload(PaymentRequestAuthorizations.merchant_signer),
    ]
    _scope = [
        PaymentRequestAuthorizations.merchant_id == merchant_id,
        PaymentRequestAuthorizations.deleted_at.is_(None),
    ]

    # 1. Try by authorization_id
    stmt = (
        select(PaymentRequestAuthorizations)
        .options(*_options)
        .where(
            PaymentRequestAuthorizations.authorization_id == authorization_id,
            *_scope,
        )
    )
    result = db.execute(stmt).unique().scalar_one_or_none()
    if result:
        return result

    # 2. Try by authorization_literal
    stmt = (
        select(PaymentRequestAuthorizations)
        .options(*_options)
        .where(
            PaymentRequestAuthorizations.authorization_literal == authorization_id,
            *_scope,
        )
    )
    result = db.execute(stmt).unique().scalar_one_or_none()
    if result:
        return result

    # 3. Try by integer pk (for records without authorization_id/literal)
    try:
        pk = int(authorization_id)
        stmt = (
            select(PaymentRequestAuthorizations)
            .options(*_options)
            .where(
                PaymentRequestAuthorizations.id == pk,
                *_scope,
            )
        )
        return db.execute(stmt).unique().scalar_one_or_none()
    except (ValueError, TypeError):
        return None


def expire_authorization(
    db: Session,
    authorization: PaymentRequestAuthorizations,
) -> PaymentRequestAuthorizations:
    """Stamp status = EXPIRED and set expired_at to now(UTC). Does NOT commit."""
    authorization.status = "EXPIRED"
    authorization.expired_at = datetime.now(timezone.utc)
    db.flush()
    return authorization


def get_authorization_list_for_export(
    db: Session,
    merchant_id: int,
    status: Optional[str] = None,
    authorization_type: Optional[str] = None,
    date_from: Optional[datetime] = None,
    date_to: Optional[datetime] = None,
    search: Optional[str] = None,
    amount_min: Optional[float] = None,
    amount_max: Optional[float] = None,
) -> List[PaymentRequestAuthorizations]:
    """
    Return all matching authorizations for CSV export (no pagination limit).
    Uses the same filter logic as get_authorization_list.
    """
    stmt = (
        select(PaymentRequestAuthorizations)
        .join(
            PaymentRequest,
            PaymentRequestAuthorizations.payment_request_id == PaymentRequest.id,
        )
        .join(Customer, PaymentRequestAuthorizations.customer_id == Customer.id)
        .options(
            joinedload(PaymentRequestAuthorizations.payment_request),
            joinedload(PaymentRequestAuthorizations.customer),
            joinedload(PaymentRequestAuthorizations.payer),
            joinedload(PaymentRequestAuthorizations.hpp_session),
        )
        .where(
            PaymentRequestAuthorizations.merchant_id == merchant_id,
            PaymentRequestAuthorizations.deleted_at.is_(None),
        )
    )

    if status:
        stmt = stmt.where(PaymentRequestAuthorizations.status == status)
    if authorization_type:
        stmt = stmt.where(
            PaymentRequestAuthorizations.authorization_type == authorization_type
        )
    if date_from:
        stmt = stmt.where(PaymentRequestAuthorizations.created_at >= date_from)
    if date_to:
        stmt = stmt.where(PaymentRequestAuthorizations.created_at <= date_to)
    if search:
        search_term = f"%{search}%"
        stmt = stmt.where(
            or_(
                PaymentRequestAuthorizations.authorization_id.ilike(search_term),
                Customer.first_name.ilike(search_term),
                Customer.last_name.ilike(search_term),
                Customer.email.ilike(search_term),
            )
        )
    if amount_min is not None:
        stmt = stmt.where(PaymentRequest.amount >= amount_min)
    if amount_max is not None:
        stmt = stmt.where(PaymentRequest.amount <= amount_max)

    stmt = stmt.order_by(desc(PaymentRequestAuthorizations.created_at))
    return list(db.execute(stmt).scalars().unique().all())


def get_authorization_summary(
    db: Session,
    merchant_id: int,
    limit: int = 3,
) -> Dict:
    """Return top-N customer summaries by amount and frequency."""

    _base_stmt = (
        select(
            Customer.id.label("customer_id"),
            func.concat(Customer.first_name, " ", Customer.last_name).label(
                "account_name"
            ),
            func.sum(PaymentRequest.amount).label("total_amount"),
            func.count(PaymentRequestAuthorizations.id).label("authorization_count"),
        )
        .join(Customer, PaymentRequestAuthorizations.customer_id == Customer.id)
        .join(
            PaymentRequest,
            PaymentRequestAuthorizations.payment_request_id == PaymentRequest.id,
        )
        .where(
            PaymentRequestAuthorizations.merchant_id == merchant_id,
            PaymentRequestAuthorizations.deleted_at.is_(None),
        )
        .group_by(Customer.id, Customer.first_name, Customer.last_name)
    )

    highest = db.execute(
        _base_stmt.order_by(desc("total_amount")).limit(limit)
    ).all()

    lowest = db.execute(
        _base_stmt.order_by(asc("total_amount")).limit(limit)
    ).all()

    frequent = db.execute(
        _base_stmt.order_by(desc("authorization_count")).limit(limit)
    ).all()

    def _row_to_dict(row) -> Dict:
        return {
            "customer_id": row.customer_id,
            "account_name": row.account_name,
            "total_amount": float(row.total_amount) if row.total_amount is not None else None,
            "authorization_count": row.authorization_count,
        }

    return {
        "highest_paying": [_row_to_dict(r) for r in highest],
        "lowest_paying": [_row_to_dict(r) for r in lowest],
        "most_frequent": [_row_to_dict(r) for r in frequent],
    }