"""Payments and billing endpoints."""

import base64
import csv
import io
from datetime import date, datetime, time, timezone
from typing import List, Optional

from fastapi import APIRouter, Depends, HTTPException, Query, status
from openpyxl import Workbook
from sqlalchemy import func

from ..database import get_session
from ..models import License
from ..security import require_roles
from ..utils import log_action

router = APIRouter()


@router.get("/subscriptions")
def list_subscriptions(session=Depends(get_session), _admin=Depends(require_roles("super-admin", "analyst"))):
    """
    List subscriptions - adapted to use tier-based logic since users table
    doesn't have stripe_subscription_id column yet.
    """
    rows = (
        session.query(License.email, License.tier, License.is_active)
        .filter(License.tier.in_(['pro', 'entreprise', 'enterprise']))
        .all()
    )
    return [
        {
            "subscription_id": f"tier-{row.tier}-{row.email}",  # Mock subscription ID
            "email": row.email,
            "status": "active" if row.is_active else "cancelled",
            "tier": row.tier,
        }
        for row in rows
    ]


@router.get("/summary")
def payment_summary(session=Depends(get_session), _admin=Depends(require_roles("super-admin", "analyst"))):
    """
    Payment summary - adapted to use tier-based logic since users table
    doesn't have stripe_subscription_id column yet.
    """
    # Count paid tiers (pro, entreprise, enterprise) as active subscriptions
    active = (
        session.query(func.count(License.id))
        .filter(
            License.tier.in_(['pro', 'entreprise', 'enterprise']),
            License.is_active == True
        )
        .scalar() or 0
    )
    # Count inactive paid accounts as cancelled
    cancelled = (
        session.query(func.count(License.id))
        .filter(
            License.tier.in_(['pro', 'entreprise', 'enterprise']),
            License.is_active == False
        )
        .scalar() or 0
    )
    return {"active_subscriptions": active, "cancelled": cancelled}


@router.post("/refunds/{subscription_id}")
def refund(
    subscription_id: str,
    reason: str | None = None,
    session=Depends(get_session),
    _admin=Depends(require_roles("super-admin")),
):
    exists = (
        session.query(License)
        .filter(License.stripe_subscription_id == subscription_id)
        .first()
    )
    if not exists:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Abonnement introuvable")
    log_action(session, _admin.email, "payment_refund", target=subscription_id, metadata={"reason": reason})
    session.commit()
    return {"status": "accepted", "subscription_id": subscription_id}


def _filter_subscriptions(query, start_date: Optional[date], end_date: Optional[date], statuses: Optional[List[str]]):
    if start_date:
        start_dt = datetime.combine(start_date, time.min, tzinfo=timezone.utc)
        query = query.filter(License.created_at >= start_dt)
    if end_date:
        end_dt = datetime.combine(end_date, time.max, tzinfo=timezone.utc)
        query = query.filter(License.created_at <= end_dt)
    if statuses:
        lowered = [item.lower() for item in statuses if item]
        if lowered:
            query = query.filter(License.status.in_(lowered))
    return query


def _build_dataset(rows) -> List[List[str]]:
    dataset = [["Email", "Subscription ID", "Status", "Tier", "Created At", "Updated At"]]
    for row in rows:
        dataset.append(
            [
                row.email,
                row.stripe_subscription_id,
                row.status,
                row.tier,
                row.created_at.isoformat() if row.created_at else "",
                row.updated_at.isoformat() if row.updated_at else "",
            ]
        )
    return dataset


@router.get("/export")
def export_transactions(
    format: str = Query("csv", pattern="^(csv|excel|json)$"),
    start_date: Optional[date] = Query(None, description="Filtre date de début"),
    end_date: Optional[date] = Query(None, description="Filtre date de fin"),
    status: Optional[str] = Query(None, description="Liste de statuts séparés par des virgules"),
    session=Depends(get_session),
    _admin=Depends(require_roles("super-admin")),
):
    statuses = [item.strip() for item in status.split(",")] if status else None
    query = session.query(License).filter(License.stripe_subscription_id.isnot(None))
    query = _filter_subscriptions(query, start_date, end_date, statuses)
    rows = query.order_by(License.created_at.desc()).all()
    dataset = _build_dataset(rows)

    log_action(
        session,
        _admin.email,
        "payments_export",
        metadata={
            "format": format,
            "count": len(rows),
            "start_date": start_date.isoformat() if start_date else None,
            "end_date": end_date.isoformat() if end_date else None,
            "statuses": statuses,
        },
    )
    session.commit()

    if format == "json":
        return {
            "format": "json",
            "generated_at": datetime.now(timezone.utc).isoformat(),
            "count": len(rows),
            "items": [
                {
                    "email": row.email,
                    "subscription_id": row.stripe_subscription_id,
                    "status": row.status,
                    "tier": row.tier,
                    "created_at": row.created_at.isoformat() if row.created_at else None,
                    "updated_at": row.updated_at.isoformat() if row.updated_at else None,
                }
                for row in rows
            ],
        }

    if format == "csv":
        buffer = io.StringIO()
        writer = csv.writer(buffer)
        writer.writerows(dataset)
        return {
            "format": "csv",
            "generated_at": datetime.now(timezone.utc).isoformat(),
            "content": buffer.getvalue(),
        }

    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "Subscriptions"
    for row in dataset:
        sheet.append(row)

    output = io.BytesIO()
    workbook.save(output)
    encoded = base64.b64encode(output.getvalue()).decode("ascii")
    filename = f"payments_export_{datetime.now(timezone.utc).date()}.xlsx"
    return {
        "format": "excel",
        "generated_at": datetime.now(timezone.utc).isoformat(),
        "filename": filename,
        "content_base64": encoded,
    }