"""Billing-panel customers: POP-grouped read-only mirror of the maXim panels.

Import via `backend/scripts/import_billing_customers.py` (upsert on
provider + external_id). No write endpoints — the panels own this data.
"""
from fastapi import APIRouter, Depends, Query
from sqlalchemy import func, or_, select
from sqlalchemy.orm import Session

from app.api.deps import get_current_user
from app.db.session import get_db
from app.models.billing import PROVIDER_LABELS, BillingCustomer
from app.models.user import User
from app.schemas.billing import BillingCustomerOut, PopSummary, ProviderSummary
from app.schemas.common import Page

router = APIRouter(prefix="/billing-customers", tags=["billing-customers"])


@router.get("/providers", response_model=list[ProviderSummary])
def list_providers(
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
) -> list[ProviderSummary]:
    rows = db.execute(
        select(BillingCustomer.provider, BillingCustomer.pop, func.count())
        .group_by(BillingCustomer.provider, BillingCustomer.pop)
        .order_by(BillingCustomer.provider, BillingCustomer.pop)
    ).all()
    by_provider: dict[str, list[PopSummary]] = {}
    for provider, pop, count in rows:
        by_provider.setdefault(provider, []).append(PopSummary(pop=pop, count=count))
    return [
        ProviderSummary(
            provider=provider,
            label=PROVIDER_LABELS.get(provider, provider),
            total=sum(p.count for p in pops),
            pops=pops,
        )
        for provider, pops in sorted(by_provider.items())
    ]


@router.get("", response_model=Page[BillingCustomerOut])
def list_billing_customers(
    db: Session = Depends(get_db),
    _: User = Depends(get_current_user),
    provider: str | None = Query(None, description="'tajri' or 'vision'"),
    pop: str | None = Query(None, description="POP name, e.g. Bonogram-POP"),
    search: str | None = Query(None, description="Name, username, customer ID, or mobile"),
    status: str | None = Query(None, description="Billing status, e.g. Active"),
    page: int = Query(1, ge=1),
    page_size: int = Query(25, ge=1, le=200),
) -> Page[BillingCustomerOut]:
    stmt = select(BillingCustomer)
    if provider:
        stmt = stmt.where(BillingCustomer.provider == provider.strip().lower())
    if pop:
        stmt = stmt.where(BillingCustomer.pop == pop.strip())
    if status:
        stmt = stmt.where(BillingCustomer.status == status.strip())
    if search:
        pattern = f"%{search.strip()}%"
        stmt = stmt.where(
            or_(
                BillingCustomer.name.ilike(pattern),
                BillingCustomer.username.ilike(pattern),
                BillingCustomer.cust_id.ilike(pattern),
                BillingCustomer.mobile.ilike(pattern),
            )
        )

    total = db.scalar(select(func.count()).select_from(stmt.subquery())) or 0
    rows = db.scalars(
        stmt.order_by(BillingCustomer.pop, BillingCustomer.name, BillingCustomer.id)
        .offset((page - 1) * page_size)
        .limit(page_size)
    ).all()
    return Page.build([BillingCustomerOut.model_validate(r) for r in rows], total, page, page_size)