"""
Site-admin router — restricted to users with role='site_admin'.
These endpoints are NOT tenant-scoped and operate across all accounts.
"""
from typing import List, Optional
from uuid import UUID

from fastapi import APIRouter, Depends, File, Query, Request, UploadFile
from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession

from src.apps.auth.schemas.requests import LoginRequest
from src.apps.auth.schemas.responses import UserResponse
from src.apps.auth.services.auth_service import AuthService
from src.apps.site_admin.models.lead import Lead
from src.apps.site_admin.models.cms_page import CmsPage
from src.apps.site_admin.models.blog_post import BlogPost
from src.apps.site_admin.models.faq import FaqItem, FaqSectionSettings
from src.apps.site_admin.models.plan import Plan
from src.apps.site_admin.models.platform_settings import PlatformSettings
from src.apps.site_admin.models.who_we_serve_category import WhoWeServeCategory
from src.apps.site_admin.schemas.faq import (
    FaqItemWriteRequest,
    ReorderFaqItemsRequest,
    UpdateFaqSettingsRequest,
)
from src.apps.site_admin.schemas.who_we_serve import UpdateWhoWeServeCategoryRequest
from src.apps.site_admin.schemas.plans import PlanResponse, UpdatePlanRequest, UpdateSettingsRequest
from src.apps.tenants.schemas.requests import (
    AdminCreateTenantRequest,
    SendCredentialsRequest,
    UpdateTenantRequest,
)
from src.apps.tenants.schemas.responses import (
    AccountDetailResponse,
    AccountResponse,
    SignupListItemResponse,
)
from src.apps.tenants.services.tenant_service import TenantService
from src.apps.site_admin.schemas.enquiry import (
    CreateEnquiryRequest,
    EnquiryDetailResponse,
    EnquiryListItemResponse,
    RecordSignupCompletedRequest,
    SendContactReplyRequest,
)
from src.apps.public.models.website_enquiry import WebsiteEnquiry
from src.apps.site_admin.schemas.payments import RecordPlatformPaymentRequest
from src.apps.site_admin.services.enquiry_service import EnquiryService
from src.apps.site_admin.services.payment_service import PaymentService
from src.core.config import settings
from src.core.dependencies import get_current_user
from src.core.exceptions import (
    AppException,
    ConflictError,
    ForbiddenError,
    NotFoundError,
    UnprocessableError,
    ValidationError,
)
from src.core.schemas.response import paginated, success
from src.database.session import get_db

router = APIRouter(prefix="/site-admin", tags=["Site Admin"])


# ---------------------------------------------------------------------------
# Serializers
# ---------------------------------------------------------------------------

def _serialize_page(p) -> dict:
    return {
        "id": str(p.id),
        "name": p.name,
        "path": p.path,
        "headline": p.headline,
        "subheading": p.subheading,
        "subhead": p.subhead,
        "content": p.content,
        "status": p.status,
        "updatedAt": p.updated_at.isoformat(),
    }


def _serialize_post(p) -> dict:
    return {
        "id": str(p.id),
        "title": p.title,
        "slug": p.slug,
        "category": p.category,
        "excerpt": p.excerpt,
        "body": p.body,
        "cover_image_url": p.cover_image_url,
        "author": p.author,
        "seo_title": p.seo_title,
        "seo_description": p.seo_description,
        "meta_keywords": p.meta_keywords,
        "tags": p.tags,
        "featured": p.featured,
        "status": p.status,
        "date": p.published_at.isoformat()[:10] if p.published_at else p.created_at.isoformat()[:10],
        "published_at": p.published_at.isoformat() if p.published_at else None,
        "created_at": p.created_at.isoformat(),
        "updated_at": p.updated_at.isoformat(),
    }


def _serialize_settings(s) -> dict:
    return {
        "email": s.email or "",
        "phone": s.phone or "",
        "office": s.office or "",
        "hours": s.hours or "",
        "linkedin": s.linkedin or "",
        "twitter": s.twitter or "",
    }


def _serialize_faq_settings(s) -> dict:
    return {
        "id": str(s.id),
        "heading": s.heading,
        "subheading": s.subheading,
        "intro": s.intro,
        "updatedAt": s.updated_at.isoformat(),
    }


def _serialize_faq_item(i) -> dict:
    return {
        "id": str(i.id),
        "question": i.question,
        "answer": i.answer,
        "sort_order": i.sort_order,
        "updatedAt": i.updated_at.isoformat(),
    }


def _serialize_ww_category(c) -> dict:
    return {
        "id": str(c.id),
        "category_key": c.category_key,
        "badge_label": c.badge_label,
        "heading": c.heading,
        "intro": c.intro,
        "points": c.points,
        "sort_order": c.sort_order,
        "updatedAt": c.updated_at.isoformat(),
    }


# ---------------------------------------------------------------------------
# Auth guard
# ---------------------------------------------------------------------------

async def require_site_admin(current_user=Depends(get_current_user)):
    """Dependency — allows only users whose role is 'site_admin'."""
    if current_user.role != "site_admin":
        raise ForbiddenError("Site admin access required")
    return current_user


# ---------------------------------------------------------------------------
# Auth
# ---------------------------------------------------------------------------

@router.post("/auth/login", response_model=dict)
async def site_admin_login(
    body: LoginRequest,
    request: Request,
    db: AsyncSession = Depends(get_db),
):
    """
    Login for site admin users.
    No tenant scoping — authenticates against the global user pool.
    """
    service = AuthService(db)
    # Pass tenant_id=None to skip tenant filtering
    access_token, refresh_token, user, _ = await service.login(body.email, body.password, tenant_id=None)

    if user.role != "site_admin":
        raise ForbiddenError("Site admin access required")

    return success(
        data={
            "access_token": access_token,
            "refresh_token": refresh_token,
            "token_type": "bearer",
            "user": UserResponse.model_validate(user).model_dump(),
        },
        message="Login successful",
    )


# ---------------------------------------------------------------------------
# Dashboard KPIs
# ---------------------------------------------------------------------------

@router.get("/dashboard/kpis", response_model=dict)
async def get_kpis(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """High-level KPI snapshot for the site admin dashboard."""
    from src.apps.tenants.models.account import Account
    from src.apps.auth.models.user import User
    from src.apps.records.models.record import Record

    total_tenants_result = await db.execute(select(func.count(Account.id)))
    total_tenants = total_tenants_result.scalar_one()

    active_tenants_result = await db.execute(
        select(func.count(Account.id)).where(Account.status == "active")
    )
    active_tenants = active_tenants_result.scalar_one()

    total_users_result = await db.execute(select(func.count(User.id)))
    total_users = total_users_result.scalar_one()

    total_records_result = await db.execute(
        select(func.count(Record.id)).where(Record.deleted_at.is_(None))
    )
    total_records = total_records_result.scalar_one()

    total_leads_result = await db.execute(select(func.count(Lead.id)))
    total_leads = total_leads_result.scalar_one()

    pages_result = await db.execute(
        select(func.count(CmsPage.id)).where(CmsPage.status == "Published")
    )
    published_pages = pages_result.scalar_one()

    posts_result = await db.execute(
        select(func.count(BlogPost.id)).where(BlogPost.deleted_at.is_(None))
    )
    blog_posts = posts_result.scalar_one()

    # Count accounts on a 14-day free trial plan (starter or professional)
    trial_stmt = (
        select(func.count(Account.id))
        .where(Account.plan.in_(["starter", "professional"]))
    )
    free_trial_signups = (await db.execute(trial_stmt)).scalar_one()

    total_enquiries_result = await db.execute(select(func.count(WebsiteEnquiry.id)))
    total_enquiries = total_enquiries_result.scalar_one()

    new_enquiries = await EnquiryService(db).count_new()

    return success(
        data={
            "total_tenants": total_tenants,
            "active_tenants": active_tenants,
            "total_users": total_users,
            "total_records": total_records,
            "total_leads": total_leads,
            "published_pages": published_pages,
            "blog_posts": blog_posts,
            "free_trial_signups": free_trial_signups,
            "total_enquiries": total_enquiries,
            "new_enquiries": new_enquiries,
        }
    )


# ---------------------------------------------------------------------------
# Tenant management
# ---------------------------------------------------------------------------

@router.get("/signups/kpis", response_model=dict)
async def get_signup_kpis(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """KPI counts by status for the sign-ups dashboard strip cards."""
    service = TenantService(db)
    kpis = await service.get_signup_kpis()
    return success(data=kpis)


@router.get("/signups", response_model=dict)
async def list_signups(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=500),
    status: str = Query(None),
    plan: str = Query(None),
    q: str = Query(None),
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Paginated list of all sign-up accounts."""
    service = TenantService(db)
    items, total = await service.list_signups(skip=skip, limit=limit, status=status, plan=plan, q=q)

    response_items = []
    for item in items:
        account = item["account"]
        response_items.append(
            SignupListItemResponse(
                id=account.id,
                subdomain=account.subdomain,
                name=account.organization_name,
                cemetery_type=account.cemetery_type,
                plan=account.plan,
                status=account.status,
                contact={
                    "name": item["contact_name"],
                    "email": item["contact_email"],
                    "phone": item["contact_phone"],
                    "title": "",
                },
                size=account.size,
                signup_source=account.signup_source,
                account_manager=account.account_manager,
                feature_flags=account.feature_flags,
                credentials_sent_at=account.credentials_sent_at,
                activated_at=account.activated_at,
                created_at=account.created_at,
            ).model_dump()
        )

    return paginated(
        items=response_items,
        total=total,
        page=(skip // limit) + 1,
        page_size=limit,
    )


@router.get("/tenants", response_model=dict)
async def list_tenants(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=100),
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    service = TenantService(db)
    accounts, total = await service.list_all(skip=skip, limit=limit)
    return paginated(
        items=[AccountResponse.model_validate(a).model_dump() for a in accounts],
        total=total,
        page=(skip // limit) + 1,
        page_size=limit,
    )


@router.post("/tenants", response_model=dict, status_code=201)
async def create_tenant(
    body: AdminCreateTenantRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Create a tenant account on behalf of a customer (admin wizard)."""
    service = TenantService(db)
    account, admin_user, temp_password = await service.admin_create(body.model_dump())
    return success(
        data={
            "account": AccountDetailResponse.model_validate(account).model_dump(),
            "admin_user": UserResponse.model_validate(admin_user).model_dump(),
            "temp_password": temp_password,
        },
        message="Tenant created",
    )


@router.get("/tenants/{tenant_id}", response_model=dict)
async def get_tenant(
    tenant_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    service = TenantService(db)
    account = await service.get_by_id(tenant_id)
    from src.core.exceptions import NotFoundError as _NotFoundError
    if not account:
        raise _NotFoundError("Tenant not found")
    return success(data=AccountDetailResponse.model_validate(account).model_dump())


@router.post("/tenants/{tenant_id}/send-credentials", response_model=dict)
async def send_credentials(
    tenant_id: UUID,
    body: SendCredentialsRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Record credential-send timestamp and dispatch login details email (server-composed)."""
    service = TenantService(db)
    account = await service.send_credentials(tenant_id, body.credentials_sent_at)

    from src.apps.auth.models.user import User
    from sqlalchemy import select as _select
    admin_result = await db.execute(
        _select(User).where(
            User.tenant_id == account.id,
            User.role == "administrator",
            User.deleted_at.is_(None),
        ).limit(1)
    )
    admin_user = admin_result.scalar_one_or_none()
    to_email = admin_user.email if admin_user else account.contact_email
    first_name = (
        (admin_user.first_name or "").strip()
        if admin_user and admin_user.first_name
        else account.organization_name
    )

    workspace_url = f"https://{account.subdomain}.{settings.APP_DOMAIN}"
    temp_password = account.temp_password or "(please contact INDELIS support)"

    from src.core.constants import EmailTriggerKey
    from src.core.email_dispatch import cemetery_context, email_dispatch_service
    await email_dispatch_service.send(
        db,
        trigger_key=EmailTriggerKey.NEW_USER_WELCOME,
        tenant_id=account.id,
        to=to_email,
        context={
            "user_name": first_name,
            "temp_password": temp_password,
            "login_url": f"{workspace_url}/login",
            **cemetery_context(account),
        },
    )

    return success(
        data={"credentials_sent_at": account.credentials_sent_at.isoformat() if account.credentials_sent_at else None},
        message="Login details sent",
    )


@router.get("/tenants/{tenant_id}/credentials-preview", response_model=dict)
async def get_credentials_preview(
    tenant_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Return admin email + temp_password for pre-filling the send-login email modal."""
    from src.apps.auth.models.user import User
    from sqlalchemy import select as _select
    from src.core.exceptions import NotFoundError as _NotFoundError

    service = TenantService(db)
    account = await service.get_by_id(tenant_id)
    if not account:
        raise _NotFoundError("Tenant not found")

    admin_result = await db.execute(
        _select(User).where(
            User.tenant_id == account.id,
            User.role == "administrator",
            User.deleted_at.is_(None),
        ).limit(1)
    )
    admin_user = admin_result.scalar_one_or_none()
    admin_email = admin_user.email if admin_user else account.contact_email

    return success(data={
        "admin_email": admin_email,
        "temp_password": account.temp_password,
    })


@router.patch("/tenants/{tenant_id}", response_model=dict)
async def update_tenant(
    tenant_id: UUID,
    body: UpdateTenantRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    service = TenantService(db)
    account = await service.update(tenant_id, body.model_dump(exclude_none=True))
    return success(
        data=AccountDetailResponse.model_validate(account).model_dump(),
        message="Tenant updated",
    )


# ---------------------------------------------------------------------------
# Leads
# ---------------------------------------------------------------------------

@router.get("/leads", response_model=dict)
async def list_leads(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=100),
    status: str = Query(None),
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from sqlalchemy import and_

    conditions = []
    if status:
        conditions.append(Lead.status == status)

    where_clause = and_(*conditions) if conditions else True

    count_result = await db.execute(
        select(func.count(Lead.id)).where(where_clause)
        if conditions
        else select(func.count(Lead.id))
    )
    total = count_result.scalar_one()

    query = select(Lead).order_by(Lead.created_at.desc()).offset(skip).limit(limit)
    if conditions:
        query = query.where(where_clause)

    result = await db.execute(query)
    leads = result.scalars().all()

    def _serialize_lead(lead: Lead) -> dict:
        return {
            "id": str(lead.id),
            "organization": lead.organization,
            "contact_name": lead.contact_name,
            "email": lead.email,
            "phone": lead.phone,
            "cemetery_type": lead.cemetery_type,
            "size_estimate": lead.size_estimate,
            "plan_interest": lead.plan_interest,
            "source": lead.source,
            "status": lead.status,
            "notes": lead.notes,
            "created_at": lead.created_at.isoformat(),
        }

    return paginated(
        items=[_serialize_lead(lead) for lead in leads],
        total=total,
        page=(skip // limit) + 1,
        page_size=limit,
    )


# ---------------------------------------------------------------------------
# Enquiries
# ---------------------------------------------------------------------------

@router.get("/enquiries", response_model=dict)
async def list_enquiries(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=500),
    type: str = Query(None),
    status: str = Query(None),
    q: str = Query(None),
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Paginated list of all website enquiries with optional type/status/search filters."""
    service = EnquiryService(db)
    items, total = await service.list(
        skip=skip,
        limit=limit,
        type_filter=type,
        status_filter=status,
        q=q,
    )
    return paginated(
        items=[EnquiryListItemResponse.model_validate(e).model_dump() for e in items],
        total=total,
        page=(skip // limit) + 1,
        page_size=limit,
    )


@router.get("/enquiries/new-count", response_model=dict)
async def get_enquiry_new_count(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Count of enquiries with status='new' — topbar bell badge."""
    service = EnquiryService(db)
    count = await service.count_new()
    return success(data={"count": count})


@router.post("/enquiries/record-signup-completed", response_model=dict)
async def record_signup_completed(
    body: RecordSignupCompletedRequest,
    db: AsyncSession = Depends(get_db),
):
    """Internal — called by signup flow after account creation succeeds.
    No auth required. Records signup_completed_at; status does NOT change."""
    service = EnquiryService(db)
    enquiry = await service.record_signup_completed(body.enquiry_id)
    if not enquiry:
        return success(data={}, message="No matching open enquiry found — no action taken")
    return success(
        data=EnquiryListItemResponse.model_validate(enquiry).model_dump(),
        message="Signup recorded — enquiry status unchanged, signup_completed_at set",
    )


@router.get("/enquiries/{enquiry_id}", response_model=dict)
async def get_enquiry_detail(
    enquiry_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Single enquiry with full audit log history."""
    from sqlalchemy.orm import selectinload
    from src.core.exceptions import NotFoundError as _NotFoundError
    result = await db.execute(
        select(WebsiteEnquiry)
        .options(selectinload(WebsiteEnquiry.audit_logs))
        .where(WebsiteEnquiry.id == enquiry_id)
    )
    enquiry = result.scalar_one_or_none()
    if not enquiry:
        raise _NotFoundError("Enquiry not found")
    return success(data=EnquiryDetailResponse.model_validate(enquiry).model_dump())


@router.post("/enquiries", response_model=dict, status_code=201)
async def create_enquiry(
    body: CreateEnquiryRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Manually create a new enquiry from the site-admin panel."""
    service = EnquiryService(db)
    enquiry = await service.create(body.model_dump())
    return success(
        data=EnquiryListItemResponse.model_validate(enquiry).model_dump(),
        message="Enquiry created",
    )


@router.post("/enquiries/{enquiry_id}/mark-contacted", response_model=dict)
async def mark_contacted_manual(
    enquiry_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Mark enquiry as contacted via manual action (no email sent)."""
    service = EnquiryService(db)
    enquiry = await service.mark_contacted_manual(
        enquiry_id, actor_id=current_user.id, actor_email=current_user.email
    )
    return success(
        data=EnquiryListItemResponse.model_validate(enquiry).model_dump(),
        message="Enquiry marked as contacted",
    )


@router.post("/enquiries/{enquiry_id}/close", response_model=dict)
async def close_enquiry_manual(
    enquiry_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Manually close an enquiry as not interested. Only allowed from new/contacted status."""
    service = EnquiryService(db)
    enquiry = await service.close_manual(
        enquiry_id, actor_id=current_user.id, actor_email=current_user.email
    )
    return success(
        data=EnquiryListItemResponse.model_validate(enquiry).model_dump(),
        message="Enquiry marked as not interested and closed",
    )


@router.post("/enquiries/{enquiry_id}/send-contact-reply", response_model=dict)
async def send_contact_reply(
    enquiry_id: UUID,
    body: SendContactReplyRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Send reply email then mark as contacted. Fails atomically if email send fails."""
    service = EnquiryService(db)
    enquiry = await service.get_by_id(enquiry_id)
    if not enquiry:
        from src.core.exceptions import NotFoundError as _NotFoundError
        raise _NotFoundError("Enquiry not found")

    service._assert_manual_transition(enquiry, "contacted")

    from src.core.constants import EmailTriggerKey
    from src.core.email_dispatch import email_dispatch_service
    admin_name = f"{getattr(current_user, 'first_name', '') or ''} {getattr(current_user, 'last_name', '') or ''}".strip() or current_user.email
    # Platform-level enquiry (no owning tenant) → dispatch with tenant_id=None,
    # which renders the hardcoded default template with INDELIS branding.
    await email_dispatch_service.send(
        db,
        trigger_key=EmailTriggerKey.CONTACT_ENQUIRY_REPLY,
        tenant_id=None,
        to=enquiry.email,
        context={
            "contact_name": enquiry.name or "",
            "reply_message": body.message,
            "admin_name": admin_name,
            "cemetery_name": "INDELIS",
            "cemetery_email": "support@indelis.com",
            "cemetery_phone": "1-800-INDELIS",
        },
    )

    enquiry = await service.mark_contacted_email_reply(
        enquiry_id, actor_id=current_user.id, actor_email=current_user.email
    )
    return success(
        data=EnquiryListItemResponse.model_validate(enquiry).model_dump(),
        message="Reply sent and enquiry marked as contacted",
    )


@router.post("/enquiries/{enquiry_id}/qualify", response_model=dict)
async def qualify_enquiry(
    enquiry_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Qualify an enquiry — sends qualification email with prefilled signup link."""
    import urllib.parse
    service = EnquiryService(db)
    enquiry = await service.get_by_id(enquiry_id)
    if not enquiry:
        from src.core.exceptions import NotFoundError as _NotFoundError
        raise _NotFoundError("Enquiry not found")

    service._assert_manual_transition(enquiry, "qualified")

    params = {
        "eq": str(enquiry.id),
        "name": enquiry.name or "",
        "email": enquiry.email or "",
        "org": enquiry.organization or "",
        "cemetery_type": enquiry.cemetery_type or "",
        "role": enquiry.role or "",
        "phone": enquiry.phone or "",
        "prefilled": "1",
    }
    signup_url = f"{settings.FRONTEND_URL}/signup?{urllib.parse.urlencode(params)}"

    from src.core.constants import EmailTriggerKey
    from src.core.email_dispatch import email_dispatch_service
    await email_dispatch_service.send(
        db,
        trigger_key=EmailTriggerKey.LEAD_QUALIFIED,
        tenant_id=None,
        to=enquiry.email,
        context={
            "contact_name": enquiry.name or "",
            "organization": enquiry.organization or "your cemetery",
            "signup_url": signup_url,
            "cemetery_name": "INDELIS",
            "cemetery_email": "support@indelis.com",
            "cemetery_phone": "1-800-INDELIS",
        },
    )

    enquiry = await service.qualify(
        enquiry_id, actor_id=current_user.id, actor_email=current_user.email
    )
    return success(
        data=EnquiryListItemResponse.model_validate(enquiry).model_dump(),
        message="Enquiry qualified and signup invitation sent",
    )


# ---------------------------------------------------------------------------
# CMS Pages
# ---------------------------------------------------------------------------

@router.post("/pages", response_model=dict, status_code=201)
async def create_page(
    body: dict,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from src.core.exceptions import ConflictError, ValidationError

    name = (body.get("name") or "").strip()
    path = (body.get("path") or "").strip().lower()
    if not name or not path:
        raise ValidationError("Name and path are required")

    existing = await db.execute(select(CmsPage).where(CmsPage.path == path))
    if existing.scalar_one_or_none():
        raise ConflictError(f"A page with path '{path}' already exists")

    page = CmsPage(
        name=name,
        path=path,
        headline=body.get("headline", ""),
        subheading=body.get("subheading", ""),
        subhead=body.get("subhead", ""),
        content=body.get("content"),
        status=body.get("status", "Draft"),
    )
    db.add(page)
    await db.flush()
    await db.refresh(page)
    return success(data=_serialize_page(page), message="Page created")


@router.get("/pages", response_model=dict)
async def list_pages(
    skip: int = Query(0, ge=0),
    limit: int = Query(50, ge=1, le=200),
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    count_result = await db.execute(select(func.count(CmsPage.id)))
    total = count_result.scalar_one()
    result = await db.execute(
        select(CmsPage).order_by(CmsPage.name.asc()).offset(skip).limit(limit)
    )
    items = result.scalars().all()
    return paginated(
        items=[_serialize_page(p) for p in items],
        total=total,
        page=(skip // limit) + 1 if limit else 1,
        page_size=limit,
    )


@router.get("/pages/{page_id}", response_model=dict)
async def get_page(
    page_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(select(CmsPage).where(CmsPage.id == page_id))
    page = result.scalar_one_or_none()
    if not page:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Page not found")
    return success(data=_serialize_page(page))


@router.patch("/pages/{page_id}", response_model=dict)
async def update_page(
    page_id: UUID,
    body: dict,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(select(CmsPage).where(CmsPage.id == page_id))
    page = result.scalar_one_or_none()
    if not page:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Page not found")
    mapping = {
        "headline": "headline",
        "subheading": "subheading",
        "subhead": "subhead",
        "content": "content",
        "status": "status",
    }
    for key, col in mapping.items():
        if key in body:
            setattr(page, col, body[key])
    await db.flush()
    await db.refresh(page)
    return success(data=_serialize_page(page), message="Page updated")


# ---------------------------------------------------------------------------
# Blog Posts
# ---------------------------------------------------------------------------

@router.get("/posts", response_model=dict)
async def list_posts(
    skip: int = Query(0, ge=0),
    limit: int = Query(20, ge=1, le=100),
    status: str = Query(None),
    q: str = Query(None),
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from sqlalchemy import and_, or_

    conditions = [BlogPost.deleted_at.is_(None)]
    if status:
        conditions.append(BlogPost.status == status)
    if q:
        term = f"%{q.lower()}%"
        conditions.append(or_(
            func.lower(BlogPost.title).like(term),
            func.lower(BlogPost.excerpt).like(term),
        ))

    where = and_(*conditions)
    count_result = await db.execute(select(func.count(BlogPost.id)).where(where))
    total = count_result.scalar_one()
    query = select(BlogPost).where(where).order_by(BlogPost.created_at.desc()).offset(skip).limit(limit)
    result = await db.execute(query)
    items = result.scalars().all()
    return paginated(
        items=[_serialize_post(p) for p in items],
        total=total,
        page=(skip // limit) + 1,
        page_size=limit,
    )


@router.get("/posts/{post_id}", response_model=dict)
async def get_post(
    post_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(BlogPost).where(BlogPost.id == post_id, BlogPost.deleted_at.is_(None))
    )
    post = result.scalar_one_or_none()
    if not post:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Post not found")
    return success(data=_serialize_post(post))


@router.post("/posts", response_model=dict, status_code=201)
async def create_post(
    body: dict,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from datetime import datetime, timezone
    import re
    from fastapi import HTTPException

    status = body.get("status", "Draft")
    published_at = None
    if status == "Published":
        published_at = datetime.now(timezone.utc)
    # Draft and Unpublished both have no published_at

    slug = body.get("slug", "")
    if not slug:
        title = body.get("title", "")
        slug = re.sub(r"[^a-z0-9]+", "-", title.lower()).strip("-")

    # Validate slug format
    if len(slug) < 2 or not re.match(r"^[a-z0-9][a-z0-9-]*[a-z0-9]$", slug):
        raise HTTPException(status_code=422, detail="Invalid slug format")

    # Check slug uniqueness
    existing = await db.execute(
        select(BlogPost).where(BlogPost.slug == slug, BlogPost.deleted_at.is_(None))
    )
    if existing.scalar_one_or_none():
        raise HTTPException(status_code=409, detail="This slug is already in use.")

    post = BlogPost(
        title=body.get("title", ""),
        slug=slug,
        category=body.get("category"),
        excerpt=body.get("excerpt"),
        body=body.get("body"),
        cover_image_url=body.get("cover_image_url"),
        author=body.get("author"),
        seo_title=body.get("seo_title"),
        seo_description=body.get("seo_description"),
        meta_keywords=body.get("meta_keywords"),
        tags=body.get("tags"),
        featured=body.get("featured", False),
        status=status,
        published_at=published_at,
    )
    db.add(post)
    await db.flush()
    await db.refresh(post)
    return success(data=_serialize_post(post))


@router.patch("/posts/{post_id}", response_model=dict)
async def update_post(
    post_id: UUID,
    body: dict,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from datetime import datetime, timezone
    from fastapi import HTTPException

    result = await db.execute(
        select(BlogPost).where(BlogPost.id == post_id, BlogPost.deleted_at.is_(None))
    )
    post = result.scalar_one_or_none()
    if not post:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Post not found")

    # Handle slug uniqueness (exclude self)
    if "slug" in body and body["slug"] != post.slug:
        slug = body["slug"]
        existing = await db.execute(
            select(BlogPost).where(
                BlogPost.slug == slug,
                BlogPost.id != post_id,
                BlogPost.deleted_at.is_(None),
            )
        )
        if existing.scalar_one_or_none():
            raise HTTPException(status_code=409, detail="This slug is already in use.")

    for field in ["title", "slug", "category", "excerpt", "body", "cover_image_url",
                  "author", "seo_title", "seo_description", "meta_keywords", "tags", "featured", "status"]:
        if field in body:
            setattr(post, field, body[field])

    if body.get("status") == "Published" and not post.published_at:
        post.published_at = datetime.now(timezone.utc)
    elif body.get("status") in ("Draft", "Unpublished"):
        post.published_at = None

    await db.flush()
    await db.refresh(post)
    return success(data=_serialize_post(post), message="Post updated")


@router.delete("/posts/{post_id}", status_code=204)
async def delete_post(
    post_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from datetime import datetime, timezone

    result = await db.execute(
        select(BlogPost).where(BlogPost.id == post_id, BlogPost.deleted_at.is_(None))
    )
    post = result.scalar_one_or_none()
    if not post:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Post not found")
    post.deleted_at = datetime.now(timezone.utc)
    await db.flush()


@router.patch("/posts/{post_id}/publish", response_model=dict)
async def toggle_publish(
    post_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from datetime import datetime, timezone

    result = await db.execute(
        select(BlogPost).where(BlogPost.id == post_id, BlogPost.deleted_at.is_(None))
    )
    post = result.scalar_one_or_none()
    if not post:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Post not found")

    if post.status == "Published":
        post.status = "Unpublished"
        post.published_at = None
    else:
        post.status = "Published"
        post.published_at = datetime.now(timezone.utc)

    await db.flush()
    await db.refresh(post)
    return success(data=_serialize_post(post), message="Publish status toggled")


@router.post("/posts/images", response_model=dict, status_code=201)
async def upload_post_image(
    request: Request,
    file: UploadFile = File(...),
    current_user=Depends(require_site_admin),
):
    import uuid as _uuid
    import boto3
    import botocore.exceptions
    from fastapi import HTTPException
    from src.core.config import settings as _settings

    ALLOWED_TYPES = {"image/jpeg", "image/png", "image/webp"}
    MAX_SIZE = 5 * 1024 * 1024  # 5 MB

    if file.content_type not in ALLOWED_TYPES:
        raise HTTPException(status_code=422, detail="Only JPG, PNG, and WebP are accepted.")

    content = await file.read()
    if len(content) > MAX_SIZE:
        raise HTTPException(status_code=422, detail="Image must be ≤ 5 MB.")

    if not _settings.AWS_ACCESS_KEY_ID or not _settings.S3_DOCUMENTS_BUCKET:
        raise HTTPException(status_code=501, detail="S3 storage is not configured.")

    ext = file.content_type.split("/")[-1].replace("jpeg", "jpg")
    key = f"blog_images/{_uuid.uuid4()}.{ext}"

    bucket = _settings.S3_DOCUMENTS_BUCKET
    region = _settings.S3_DOCUMENTS_REGION or "us-east-1"

    s3 = boto3.client(
        "s3",
        aws_access_key_id=_settings.AWS_ACCESS_KEY_ID,
        aws_secret_access_key=_settings.AWS_SECRET_ACCESS_KEY,
        region_name=region,
    )
    try:
        import asyncio as _asyncio
        await _asyncio.to_thread(
            s3.put_object,
            Bucket=bucket,
            Key=key,
            Body=content,
            ContentType=file.content_type,
        )
    except botocore.exceptions.ClientError as e:
        code = e.response.get("Error", {}).get("Code", "")
        if code == "NoSuchBucket":
            raise HTTPException(status_code=503, detail=f"S3 bucket '{bucket}' does not exist.")
        raise HTTPException(status_code=503, detail=f"S3 upload failed: {code}")

    proxy_url = str(request.base_url).rstrip("/") + f"/api/public/images/{key}"
    return success(data={"url": proxy_url})


# ---------------------------------------------------------------------------
# FAQ section CMS (INDL-52) — platform-level, marketing Home page
# ---------------------------------------------------------------------------

# API4 (Unrestricted Resource Consumption) guard — small, human-curated list;
# reject further creates once the active count already reached this cap.
_FAQ_ITEM_MAX_ACTIVE = 100

# Delete guard floor — must never drop below this many active items (A04 / AC-10).
_FAQ_ITEM_MIN_ACTIVE = 5


def _validate_faq_heading(value: Optional[str]) -> str:
    heading = (value or "").strip()
    if not heading:
        raise ValidationError("Heading is required")
    if len(heading) > 200:
        raise ValidationError("Max 200 characters")
    return heading


def _validate_faq_subheading(value: Optional[str]) -> Optional[str]:
    if value is None:
        return None
    subheading = value.strip()
    if not subheading:
        return None
    if len(subheading) > 300:
        raise ValidationError("Max 300 characters")
    return subheading


def _validate_faq_intro(value: Optional[str]) -> Optional[str]:
    if value is None:
        return None
    intro = value.strip()
    if not intro:
        return None
    if len(intro) > 1000:
        raise ValidationError("Max 1,000 characters")
    return intro


def _validate_faq_question(value: Optional[str]) -> str:
    question = (value or "").strip()
    if not question:
        raise ValidationError("Question is required")
    if len(question) < 5:
        raise ValidationError("Must be at least 5 characters")
    if len(question) > 300:
        raise ValidationError("Max 300 characters")
    return question


def _validate_faq_answer(value: Optional[str]) -> str:
    answer = (value or "").strip()
    if not answer:
        raise ValidationError("Answer is required")
    if len(answer) < 10:
        raise ValidationError("Must be at least 10 characters")
    if len(answer) > 2000:
        raise ValidationError("Max 2,000 characters")
    return answer


@router.get("/faq", response_model=dict)
async def get_faq_section(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    settings_result = await db.execute(
        select(FaqSectionSettings).order_by(FaqSectionSettings.created_at.asc()).limit(1)
    )
    settings_row = settings_result.scalar_one_or_none()

    items_result = await db.execute(
        select(FaqItem)
        .where(FaqItem.deleted_at.is_(None))
        .order_by(FaqItem.sort_order.asc())
    )
    items = items_result.scalars().all()

    return success(data={
        "settings": _serialize_faq_settings(settings_row) if settings_row else None,
        "items": [_serialize_faq_item(i) for i in items],
    })


@router.patch("/faq/settings", response_model=dict)
async def update_faq_settings(
    body: UpdateFaqSettingsRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    heading = _validate_faq_heading(body.heading)
    subheading = _validate_faq_subheading(body.subheading)
    intro = _validate_faq_intro(body.intro)

    result = await db.execute(
        select(FaqSectionSettings).order_by(FaqSectionSettings.created_at.asc()).limit(1)
    )
    settings_row = result.scalar_one_or_none()

    if settings_row is None:
        # Singleton missing (should not happen post-migration-0059 seed) — create it.
        settings_row = FaqSectionSettings(heading=heading, subheading=subheading, intro=intro)
        db.add(settings_row)
    else:
        settings_row.heading = heading
        settings_row.subheading = subheading
        settings_row.intro = intro

    await db.flush()
    await db.refresh(settings_row)
    return success(data=_serialize_faq_settings(settings_row), message="FAQ section updated.")


@router.post("/faq/items", response_model=dict, status_code=201)
async def create_faq_item(
    body: FaqItemWriteRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    question = _validate_faq_question(body.question)
    answer = _validate_faq_answer(body.answer)

    count_result = await db.execute(
        select(func.count(FaqItem.id)).where(FaqItem.deleted_at.is_(None))
    )
    active_count = count_result.scalar_one()
    if active_count >= _FAQ_ITEM_MAX_ACTIVE:
        raise UnprocessableError(
            f"FAQ item limit reached ({_FAQ_ITEM_MAX_ACTIVE} maximum active items)."
        )

    max_sort_result = await db.execute(
        select(func.max(FaqItem.sort_order)).where(FaqItem.deleted_at.is_(None))
    )
    max_sort = max_sort_result.scalar_one()
    next_sort = (max_sort + 1) if max_sort is not None else 0

    item = FaqItem(question=question, answer=answer, sort_order=next_sort)
    db.add(item)
    await db.flush()
    await db.refresh(item)
    return success(data=_serialize_faq_item(item), message="FAQ added.")


# NOTE: this static route MUST be registered before the "/faq/items/{item_id}"
# routes below — FastAPI/Starlette matches routes in registration order, and
# a dynamic "{item_id}: UUID" route registered first would swallow requests to
# the literal path "/faq/items/reorder" (and fail UUID parsing on "reorder").
@router.patch("/faq/items/reorder", response_model=dict)
async def reorder_faq_items(
    body: ReorderFaqItemsRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    order = body.order
    if len(set(order)) != len(order):
        raise ValidationError("Order list contains duplicate FAQ ids.")

    result = await db.execute(
        select(FaqItem).where(FaqItem.deleted_at.is_(None), FaqItem.id.in_(order))
    )
    items_by_id = {i.id: i for i in result.scalars().all()}

    missing = [str(item_id) for item_id in order if item_id not in items_by_id]
    if missing:
        raise NotFoundError(
            "One or more FAQ items were not found: " + ", ".join(missing)
        )

    for index, item_id in enumerate(order):
        items_by_id[item_id].sort_order = index

    await db.flush()

    items_result = await db.execute(
        select(FaqItem).where(FaqItem.deleted_at.is_(None)).order_by(FaqItem.sort_order.asc())
    )
    items = items_result.scalars().all()
    return success(
        data=[_serialize_faq_item(i) for i in items],
        message="FAQ order updated.",
    )


@router.patch("/faq/items/{item_id}", response_model=dict)
async def update_faq_item(
    item_id: UUID,
    body: FaqItemWriteRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(FaqItem).where(FaqItem.id == item_id, FaqItem.deleted_at.is_(None))
    )
    item = result.scalar_one_or_none()
    if not item:
        raise NotFoundError("FAQ item not found")

    fields_set = body.model_fields_set
    if "question" in fields_set:
        item.question = _validate_faq_question(body.question)
    if "answer" in fields_set:
        item.answer = _validate_faq_answer(body.answer)

    await db.flush()
    await db.refresh(item)
    return success(data=_serialize_faq_item(item), message="FAQ updated.")


@router.delete("/faq/items/{item_id}", status_code=204)
async def delete_faq_item(
    item_id: UUID,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    from datetime import datetime, timezone

    # Lock every active FAQ item row for the rest of this transaction so a
    # concurrent delete from a second admin tab cannot also read the
    # pre-delete count before either commits (PRD A04 — two tabs deleting at
    # exactly 6 active items must not both land at 4). Row count here is
    # always small (<= _FAQ_ITEM_MAX_ACTIVE), so locking the whole active set
    # is cheap and simpler than a separate advisory lock.
    locked = await db.execute(
        select(FaqItem).where(FaqItem.deleted_at.is_(None)).with_for_update()
    )
    active_items = locked.scalars().all()
    active_by_id = {i.id: i for i in active_items}

    target = active_by_id.get(item_id)
    if target is None:
        raise NotFoundError("FAQ item not found")

    if len(active_items) - 1 < _FAQ_ITEM_MIN_ACTIVE:
        raise ConflictError(f"At least {_FAQ_ITEM_MIN_ACTIVE} FAQs are required.")

    target.deleted_at = datetime.now(timezone.utc)
    await db.flush()


# ---------------------------------------------------------------------------
# Who We Serve category CMS (INDL-53) — platform-level, marketing page
# ---------------------------------------------------------------------------

# The 4 categories are structurally fixed -- never added/removed/reordered
# via the UI (see PRD Scope). Used as a defense-in-depth guard alongside the
# DB lookup by category_key itself (API1 -- don't rely purely on this
# in-memory set to reject unknown keys; a key not present in the DB must
# also 404).
_WHO_WE_SERVE_CATEGORY_KEYS = {"municipal", "private", "funeral_groups", "religious"}


def _validate_ww_heading(value: Optional[str]) -> str:
    heading = (value or "").strip()
    if not heading:
        raise ValidationError("Heading is required")
    if len(heading) > 200:
        raise ValidationError("Max 200 characters")
    return heading


def _validate_ww_intro(value: Optional[str]) -> str:
    intro = (value or "").strip()
    if not intro:
        raise ValidationError("Intro is required")
    if len(intro) < 10:
        raise ValidationError("Must be at least 10 characters")
    if len(intro) > 1000:
        raise ValidationError("Max 1,000 characters")
    return intro


def _validate_ww_points(value: Optional[List[str]]) -> List[str]:
    if value is None:
        raise ValidationError("At least 4 points are required")

    cleaned: List[str] = []
    for raw_point in value:
        point = (raw_point or "").strip()
        if not point:
            raise ValidationError("Point text is required")
        if len(point) > 200:
            raise ValidationError("Max 200 characters")
        cleaned.append(point)

    if len(cleaned) < 4:
        raise ValidationError("At least 4 points are required")
    if len(cleaned) > 8:
        raise ValidationError("Maximum 8 points")
    return cleaned


@router.get("/who-we-serve", response_model=dict)
async def get_who_we_serve_categories(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(WhoWeServeCategory).order_by(WhoWeServeCategory.sort_order.asc())
    )
    categories = result.scalars().all()
    return success(data=[_serialize_ww_category(c) for c in categories])


@router.patch("/who-we-serve/{category_key}", response_model=dict)
async def update_who_we_serve_category(
    category_key: str,
    body: UpdateWhoWeServeCategoryRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    # Lookup by category_key specifically (not a generic id) -- a key not
    # present in the DB 404s the same way an invalid/unknown key does, so we
    # never leak whether the key format itself is wrong vs a DB inconsistency
    # (API1).
    if category_key not in _WHO_WE_SERVE_CATEGORY_KEYS:
        raise NotFoundError("Who We Serve category not found")

    result = await db.execute(
        select(WhoWeServeCategory).where(WhoWeServeCategory.category_key == category_key)
    )
    category = result.scalar_one_or_none()
    if category is None:
        raise NotFoundError("Who We Serve category not found")

    # All three fields are required on every PATCH -- the admin UI always
    # submits heading+intro+points together as one "Save category" action
    # per card, so this is not a true partial-update endpoint (unlike FAQ's
    # item PATCH).
    category.heading = _validate_ww_heading(body.heading)
    category.intro = _validate_ww_intro(body.intro)
    category.points = _validate_ww_points(body.points)

    await db.flush()
    await db.refresh(category)

    message = f"{category.badge_label} category updated."
    return success(data=_serialize_ww_category(category), message=message)


# ---------------------------------------------------------------------------
# Platform Settings (single-row table)
# ---------------------------------------------------------------------------

DEFAULT_SETTINGS = {
    "email": "hello@indelis.com",
    "phone": "1-800-555-0100",
    "office": "Ottawa, ON, Canada",
    "hours": "Mon–Fri, 9 am – 5 pm ET",
    "linkedin": "https://linkedin.com/company/indelis",
    "twitter": "https://twitter.com/indelisapp",
}


@router.get("/settings", response_model=dict)
async def get_platform_settings(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(select(PlatformSettings).limit(1))
    settings = result.scalar_one_or_none()
    if not settings:
        return success(data=DEFAULT_SETTINGS)
    return success(data=_serialize_settings(settings))


@router.put("/settings", response_model=dict)
async def update_platform_settings(
    body: UpdateSettingsRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(select(PlatformSettings).limit(1))
    settings = result.scalar_one_or_none()
    if not settings:
        settings = PlatformSettings()
        db.add(settings)

    settings.email = body.email
    settings.phone = body.phone
    settings.office = body.office
    settings.hours = body.hours
    settings.linkedin = body.linkedin
    settings.twitter = body.twitter

    await db.flush()
    await db.refresh(settings)
    return success(data=_serialize_settings(settings), message="Settings saved")


# ---------------------------------------------------------------------------
# Plans (reference table — price and features are editable; slug/name are not)
# ---------------------------------------------------------------------------

@router.get("/plans", response_model=dict)
async def list_plans(
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Return all subscription plans ordered by sort_order."""
    result = await db.execute(select(Plan).order_by(Plan.sort_order.asc()))
    plans = result.scalars().all()
    return success(data=[PlanResponse.model_validate(p).model_dump() for p in plans])


@router.put("/plans/{plan_id}", response_model=dict)
async def update_plan(
    plan_id: UUID,
    body: UpdatePlanRequest,
    current_user=Depends(require_site_admin),
    db: AsyncSession = Depends(get_db),
):
    """Update price_cad and features for an existing plan."""
    result = await db.execute(select(Plan).where(Plan.id == plan_id))
    plan = result.scalar_one_or_none()
    if not plan:
        from src.core.exceptions import NotFoundError
        raise NotFoundError("Plan not found")

    plan.price_cad = body.price_cad
    plan.features = body.features

    await db.flush()
    await db.refresh(plan)
    return success(data=PlanResponse.model_validate(plan).model_dump(), message="Plan updated")


# ---------------------------------------------------------------------------
# Platform Payments
# Note: /payments/kpis and /payments/export-csv must be registered BEFORE
# any parameterised /payments/{id} route to avoid FastAPI routing conflicts.
# ---------------------------------------------------------------------------

def _get_payment_service(db: AsyncSession = Depends(get_db)) -> PaymentService:
    return PaymentService(db)


@router.get("/payments/kpis", response_model=dict)
async def get_payment_kpis(
    current_user=Depends(require_site_admin),
    service: PaymentService = Depends(_get_payment_service),
):
    """KPI totals (paid payments only): total_received, this_month, this_month_label,
    mrr, payment_count, stripe_verified. Reconciles stale "pending" trial payments
    against Stripe before computing totals."""
    kpis = await service.get_kpis()
    return success(data=kpis)


@router.get("/payments/export-csv")
async def export_payments_csv(
    plan: str = Query(None, pattern="^(starter|professional|enterprise)$"),
    source: str = Query(None, pattern="^(portal|admin)$"),
    q: str = Query(None),
    current_user=Depends(require_site_admin),
    service: PaymentService = Depends(_get_payment_service),
):
    """Streaming CSV export of filtered payments."""
    from fastapi.responses import StreamingResponse

    async def _gen():
        async for chunk in service.export_csv(plan=plan, source=source, q=q):
            yield chunk

    return StreamingResponse(
        _gen(),
        media_type="text/csv",
        headers={"Content-Disposition": 'attachment; filename="indelis-payments.csv"'},
    )


@router.get("/payments", response_model=dict)
async def list_payments(
    plan: str = Query(None, pattern="^(starter|professional|enterprise)$"),
    source: str = Query(None, pattern="^(portal|admin)$"),
    q: str = Query(None),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    current_user=Depends(require_site_admin),
    service: PaymentService = Depends(_get_payment_service),
):
    """Paginated + filtered list of platform payments, newest first."""
    items, total = await service.list_payments(
        plan=plan, source=source, q=q, page=page, page_size=page_size
    )
    pages = max(1, -(-total // page_size))
    return {
        "success": True,
        "data": items,
        "total": total,
        "page": page,
        "page_size": page_size,
        "pages": pages,
    }


@router.post("/payments", response_model=dict, status_code=201)
async def record_payment(
    body: RecordPlatformPaymentRequest,
    current_user=Depends(require_site_admin),
    service: PaymentService = Depends(_get_payment_service),
):
    """Manually record a platform payment (admin-created)."""
    payment = await service.record_payment(payload=body.model_dump())
    return success(data={"id": str(payment.id)}, message="Payment recorded")


@router.get("/payments/{payment_id}/invoice-pdf")
async def get_invoice_pdf(
    payment_id: UUID,
    current_user=Depends(require_site_admin),
    service: PaymentService = Depends(_get_payment_service),
):
    """Generate and stream an invoice PDF for a single payment."""
    from fastapi.responses import Response as FastAPIResponse

    payment, account = await service.get_payment_with_account(payment_id)

    # Fetch trial end date if the payment is in a trial (pending status)
    trial_ends_at = None
    if payment.status == "pending":
        from src.apps.tenants.models.subscription import Subscription
        sub_stmt = (
            select(Subscription)
            .where(
                Subscription.account_id == payment.account_id,
                Subscription.current_period_end.isnot(None),
            )
            .order_by(Subscription.created_at.desc())
            .limit(1)
        )
        sub = (await service.db.execute(sub_stmt)).scalar_one_or_none()
        if sub and sub.current_period_end:
            trial_ends_at = sub.current_period_end.date()

    try:
        pdf_bytes = service.generate_invoice_pdf(payment, account, trial_ends_at=trial_ends_at)
    except ImportError:
        raise AppException(
            message="PDF generation is not available — fpdf2 is not installed on this server.",
            status_code=503,
        )
    except Exception as exc:
        import logging
        logging.getLogger(__name__).error("PDF generation failed for payment %s: %s", payment_id, exc, exc_info=True)
        raise AppException(message="PDF generation failed. Please try again.", status_code=500)

    invoice_no = payment.invoice_no or str(payment.id)[:8].upper()
    filename = f"indelis-invoice-{invoice_no}.pdf"
    return FastAPIResponse(
        content=pdf_bytes,
        media_type="application/pdf",
        headers={"Content-Disposition": f'attachment; filename="{filename}"'},
    )