"""
routes/sales.py
Dedicated modular APIRouter serving commercial sales records and catalog sales rankings.
Completely decoupled from the live 500-cohort telemetry endpoint (/artists).
"""

import sqlite3
from typing import Any, Dict, List, Optional
from fastapi import APIRouter, Depends, HTTPException, Query, Header, status

DB_PATH = "data/hiffi_catalog.db"
AUTH_SECRET = "hiffi_live_secret_key_prod_2026"

sales_router = APIRouter(prefix="/sales", tags=["Commercial Sales"])


def verify_sales_auth(x_auth_secret: Optional[str] = Header(None)) -> str:
    """Verifies incoming X-Auth-Secret header for protected sales endpoints."""
    if x_auth_secret != AUTH_SECRET:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid or missing X-Auth-Secret header"
        )
    return x_auth_secret


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
    finally:
        conn.close()


@sales_router.get("/summary", dependencies=[Depends(verify_sales_auth)])
def get_sales_summary(db: sqlite3.Connection = Depends(get_db)) -> Dict[str, Any]:
    """Returns aggregate commercial sales totals across all ingested catalog records."""
    cur = db.cursor()
    summary_sql = """
        SELECT 
            COUNT(*) as total_catalog_artists,
            SUM(CASE WHEN audited_sales_units > 0 THEN 1 ELSE 0 END) as commercial_active_artists,
            COALESCE(SUM(audited_sales_units), 0) as total_audited_units,
            COALESCE(SUM(pure_album_sales), 0) as total_pure_albums,
            COALESCE(SUM(digital_singles_sales), 0) as total_digital_singles,
            COALESCE(SUM(physical_singles_sales), 0) as total_physical_singles,
            COALESCE(SUM(streaming_eas), 0) as total_streaming_eas,
            COALESCE(SUM(awards_count), 0) as total_certifications
        FROM commercial_sales
    """
    row = cur.execute(summary_sql).fetchone()
    return dict(row)


@sales_router.get("/artists", dependencies=[Depends(verify_sales_auth)])
def list_sales_artists(
    limit: int = Query(50, ge=1, le=500, description="Records per page"),
    offset: int = Query(0, ge=0, description="Offset for pagination"),
    search: Optional[str] = Query(None, description="Search artist by name"),
    provenance: Optional[str] = Query(None, description="Filter by provenance flag"),
    min_sales: Optional[float] = Query(None, description="Minimum audited sales units"),
    db: sqlite3.Connection = Depends(get_db)
) -> List[Dict[str, Any]]:
    """Returns paginated catalog commercial sales rankings ordered strictly by sales_rank."""
    cur = db.cursor()
    base_sql = """
        SELECT 
            cs.sales_rank,
            cs.artist_id,
            a.artist_name,
            a.country_origin,
            a.image_url,
            cs.audited_sales_units,
            cs.pure_album_sales,
            cs.digital_singles_sales,
            cs.physical_singles_sales,
            cs.streaming_eas,
            cs.awards_count,
            cs.provenance_flag
        FROM commercial_sales cs
        JOIN artists a ON cs.artist_id = a.artist_id
        WHERE 1=1
    """
    params = []
    if search:
        base_sql += " AND a.artist_name LIKE ?"
        params.append(f"%{search}%")
    if provenance:
        base_sql += " AND cs.provenance_flag = ?"
        params.append(provenance)
    if min_sales is not None:
        base_sql += " AND cs.audited_sales_units >= ?"
        params.append(min_sales)

    base_sql += " ORDER BY cs.sales_rank ASC LIMIT ? OFFSET ?"
    params.extend([limit, offset])

    rows = cur.execute(base_sql, params).fetchall()
    return [dict(r) for r in rows]


@sales_router.get("/artists/{artist_id}", dependencies=[Depends(verify_sales_auth)])
def get_artist_sales_dossier(
    artist_id: str,
    db: sqlite3.Connection = Depends(get_db)
) -> Dict[str, Any]:
    """Returns commercial sales dossier for a single catalog artist."""
    cur = db.cursor()
    sql = """
        SELECT 
            cs.sales_rank,
            cs.artist_id,
            a.artist_name,
            a.country_origin,
            a.image_url,
            a.youtube_url,
            a.spotify_url,
            a.instagram_url,
            cs.audited_sales_units,
            cs.pure_album_sales,
            cs.digital_singles_sales,
            cs.physical_singles_sales,
            cs.streaming_eas,
            cs.awards_count,
            cs.provenance_flag
        FROM commercial_sales cs
        JOIN artists a ON cs.artist_id = a.artist_id
        WHERE cs.artist_id = ?
    """
    row = cur.execute(sql, (artist_id,)).fetchone()
    if not row:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Commercial sales record for artist ID '{artist_id}' not found."
        )
    return dict(row)