"""Endpoint clienti e dashboard avanzata."""
from datetime import datetime, timedelta
from zoneinfo import ZoneInfo
from fastapi import APIRouter, Depends, HTTPException, Header
from sqlalchemy.orm import Session
from sqlalchemy import text, func
from app.database import get_db
from app.models import Booking, Service, Provider
from app.routers.admin import verify_api_key

router = APIRouter(prefix="/api/admin", tags=["customers"])
TZ = ZoneInfo("Europe/Rome")


# === Customers ===

@router.get("/customers", dependencies=[Depends(verify_api_key)])
def list_customers(q: str = "", page: int = 1, per_page: int = 20, db: Session = Depends(get_db)):
    """Lista clienti con ricerca e paginazione."""
    where = ""
    params = {}
    if q:
        where = " WHERE name ILIKE :q OR phone ILIKE :q OR email ILIKE :q"
        params["q"] = f"%{q}%"
    total = db.execute(text("SELECT COUNT(*) FROM customers" + where), params).scalar()
    sql = "SELECT id, name, phone, email, notes, total_visits, last_visit_at, first_visit_at, created_at FROM customers" + where
    sql += " ORDER BY total_visits DESC, last_visit_at DESC NULLS LAST"
    sql += f" LIMIT {per_page} OFFSET {(page-1)*per_page}"
    rows = db.execute(text(sql), params).fetchall()
    return {"items": [dict(r._mapping) for r in rows], "total": total, "page": page, "per_page": per_page, "pages": max(1,(total + per_page - 1) // per_page)}


@router.get("/customers/{customer_id}", dependencies=[Depends(verify_api_key)])
def customer_detail(customer_id: int, db: Session = Depends(get_db)):
    """Dettaglio cliente con storico prenotazioni."""
    c = db.execute(text("SELECT * FROM customers WHERE id = :id"), {"id": customer_id}).fetchone()
    if not c:
        raise HTTPException(404)
    customer = dict(c._mapping)

    # Storico prenotazioni
    bookings = db.execute(text("""
        SELECT b.id, b.start_at, b.end_at, b.status, b.notes,
               s.name as service, p.name as provider
        FROM bookings b
        LEFT JOIN services s ON s.id = b.service_id
        LEFT JOIN providers p ON p.id = b.provider_id
        WHERE b.customer_id = :cid
        ORDER BY b.start_at DESC
    """), {"cid": customer_id}).fetchall()
    customer["bookings"] = [dict(r._mapping) for r in bookings]

    # Servizi più usati
    top_svcs = db.execute(text("""
        SELECT s.name, COUNT(*) as cnt
        FROM bookings b JOIN services s ON s.id = b.service_id
        WHERE b.customer_id = :cid
        GROUP BY s.name ORDER BY cnt DESC LIMIT 5
    """), {"cid": customer_id}).fetchall()
    customer["top_services"] = [{"name": r[0], "count": r[1]} for r in top_svcs]

    return customer


@router.put("/customers/{customer_id}", dependencies=[Depends(verify_api_key)])
def update_customer(customer_id: int, data: dict, db: Session = Depends(get_db)):
    """Aggiorna note cliente."""
    db.execute(text("UPDATE customers SET notes = :notes WHERE id = :id"),
               {"notes": data.get("notes", ""), "id": customer_id})
    db.commit()
    return {"ok": True}


# === Dashboard avanzata ===

@router.get("/dashboard", dependencies=[Depends(verify_api_key)])
def dashboard_data(db: Session = Depends(get_db)):
    """Dati completi per la dashboard."""
    now = datetime.now(TZ)
    today = now.date()
    today_start = datetime.combine(today, datetime.min.time(), tzinfo=TZ)
    next7_end = today_start + timedelta(days=7)

    # KPI base
    today_count = db.execute(text(
        "SELECT COUNT(*) FROM bookings WHERE start_at >= :s AND start_at < :e AND status = 'confirmed'"
    ), {"s": today_start, "e": today_start + timedelta(days=1)}).scalar()

    week_count = db.execute(text(
        "SELECT COUNT(*) FROM bookings WHERE start_at >= :s AND start_at < :e AND status IN ('confirmed','completed')"
    ), {"s": today_start, "e": next7_end}).scalar()

    # Domani
    tomorrow_start = today_start + timedelta(days=1)
    tomorrow_count = db.execute(text(
        "SELECT COUNT(*) FROM bookings WHERE start_at >= :s AND start_at < :e AND status = 'confirmed'"
    ), {"s": tomorrow_start, "e": tomorrow_start + timedelta(days=1)}).scalar()

    total = db.execute(text("SELECT COUNT(*) FROM bookings WHERE status IN ('confirmed','completed')")).scalar()
    no_shows = db.execute(text("SELECT COUNT(*) FROM bookings WHERE status = 'no_show'")).scalar()
    total_customers = db.execute(text("SELECT COUNT(*) FROM customers")).scalar()

    # Prenotazioni per giorno questa settimana (per chart)
    week_daily = []
    for d in range(7):
        day = today_start + timedelta(days=d)
        day_end = day + timedelta(days=1)
        cnt = db.execute(text(
            "SELECT COUNT(*) FROM bookings WHERE start_at >= :s AND start_at < :e AND status IN ('confirmed','completed')"
        ), {"s": day, "e": day_end}).scalar()
        weekday_idx = (today.weekday() + d) % 7
        week_daily.append({"day": ['Lun','Mar','Mer','Gio','Ven','Sab','Dom'][weekday_idx], "date": day.strftime("%Y-%m-%d"), "count": cnt, "is_today": d == 0})

    # Top servizi (ultimi 30gg)
    top_services = db.execute(text("""
        SELECT s.name, COUNT(*) as cnt FROM bookings b
        JOIN services s ON s.id = b.service_id
        WHERE b.start_at >= :s AND b.status IN ('confirmed','completed')
        GROUP BY s.name ORDER BY cnt DESC LIMIT 5
    """), {"s": today_start - timedelta(days=30)}).fetchall()

    # Top operatori (ultimi 30gg)
    top_providers = db.execute(text("""
        SELECT p.name, COUNT(*) as cnt FROM bookings b
        JOIN providers p ON p.id = b.provider_id
        WHERE b.start_at >= :s AND b.status IN ('confirmed','completed')
        GROUP BY p.name ORDER BY cnt DESC LIMIT 5
    """), {"s": today_start - timedelta(days=30)}).fetchall()

    # Prossime prenotazioni (prossimi 7 giorni)
    upcoming = db.execute(text("""
        SELECT b.id, b.start_at, b.customer_name, s.name as service, p.name as provider, b.status
        FROM bookings b
        LEFT JOIN services s ON s.id = b.service_id
        LEFT JOIN providers p ON p.id = b.provider_id
        WHERE b.start_at >= :now AND b.start_at < :week_end AND b.status = 'confirmed'
        ORDER BY b.start_at LIMIT 8
    """), {"now": now, "week_end": today_start + timedelta(days=7)}).fetchall()

    # Attività recenti (ultime 10 prenotazioni create)
    recent = db.execute(text("""
        SELECT b.id, b.created_at, b.customer_name, s.name as service, b.status
        FROM bookings b LEFT JOIN services s ON s.id = b.service_id
        ORDER BY b.created_at DESC LIMIT 8
    """)).fetchall()

    return {
        "kpi": {
            "today": today_count,
            "week": week_count,
            "tomorrow": tomorrow_count,
            "total": total,
            "no_shows": no_shows,
            "customers": total_customers,
        },
        "week_chart": week_daily,
        "top_services": [{"name": r[0], "count": r[1]} for r in top_services],
        "top_providers": [{"name": r[0], "count": r[1]} for r in top_providers],
        "upcoming": [dict(r._mapping) for r in upcoming],
        "recent": [dict(r._mapping) for r in recent],
    }