"""
Outfit Matches Viewer API
- GET /api/outfit-matches/products  → list all products with match count
- GET /api/outfit-matches/{code}    → get all matches for a product
- GET /api/outfit-matches/stats     → overall statistics
"""

import logging

from fastapi import APIRouter, Query

# StarRocks import deferred to runtime to avoid crash when USE_LOCAL_SQLITE=true

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/api/outfit-matches", tags=["Outfit Matches Viewer"])

PRODUCT_TABLE = "test_db.magento_product_dimension_with_text_embedding"
MATCH_TABLE = "dashboard_canifa.ai_outfit_product_matches"


def _use_sqlite():
    from config import USE_LOCAL_SQLITE
    return USE_LOCAL_SQLITE


def _get_sqlite():
    import sqlite3

    from common.constants import SQLITE_DB_PATH
    conn = sqlite3.connect(SQLITE_DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


@router.get("/stats")
async def outfit_stats():
    """Overall outfit matching stats."""
    try:
        if _use_sqlite():
            conn = _get_sqlite()
            c = conn.cursor()
            c.execute("SELECT COUNT(DISTINCT anchor_product_code) as anchors FROM pg__dashboard_canifa__ai_outfit_product_matches")
            anchors = c.fetchone()["anchors"]
            c.execute("SELECT COUNT(*) as total FROM pg__dashboard_canifa__ai_outfit_product_matches")
            total_matches = c.fetchone()["total"]
            c.execute("SELECT COUNT(DISTINCT magento_ref_code) as total FROM sr__test_db__magento_product_dimension_with_text_embedding")
            total_products = c.fetchone()["total"]
            c.execute("""
                SELECT match_role, COUNT(*) as cnt
                FROM pg__dashboard_canifa__ai_outfit_product_matches
                GROUP BY match_role ORDER BY cnt DESC
            """)
            by_role = [dict(r) for r in c.fetchall()]
            conn.close()
        else:
            db = get_starrocks_connection()
            anchors = db.execute_query(f"SELECT COUNT(DISTINCT anchor_product_code) as anchors FROM {MATCH_TABLE}")[0]["anchors"]
            total_matches = db.execute_query(f"SELECT COUNT(*) as total FROM {MATCH_TABLE}")[0]["total"]
            total_products = db.execute_query(f"SELECT COUNT(DISTINCT magento_ref_code) as total FROM {PRODUCT_TABLE}")[0]["total"]
            by_role = db.execute_query(f"SELECT match_role, COUNT(*) as cnt FROM {MATCH_TABLE} GROUP BY match_role ORDER BY cnt DESC")

        return {
            "ok": True,
            "stats": {
                "total_products": total_products,
                "matched_products": anchors,
                "unmatched_products": total_products - anchors,
                "coverage_pct": round(anchors / total_products * 100, 1) if total_products else 0,
                "total_match_pairs": total_matches,
                "by_role": by_role,
            }
        }
    except Exception as e:
        logger.error(f"[OutfitStats] error: {e}")
        return {"ok": False, "error": str(e)}


@router.get("/products")
async def list_products_with_matches(
    q: str = "",
    page: int = Query(1, ge=1),
    limit: int = Query(50, ge=10, le=200),
    filter: str = "",  # "matched" | "unmatched" | ""
):
    """List all products with their match count."""
    try:
        offset = (page - 1) * limit
        if _use_sqlite():
            conn = _get_sqlite()
            c = conn.cursor()

            where = "WHERE 1=1"
            params = []
            if q:
                where += " AND (p.magento_ref_code LIKE ? OR p.product_name LIKE ?)"
                params += [f"%{q}%", f"%{q}%"]

            if filter == "matched":
                where += " AND m.match_count > 0"
            elif filter == "unmatched":
                where += " AND (m.match_count IS NULL OR m.match_count = 0)"

            sql = f"""
                SELECT p.magento_ref_code as code, p.product_name as name,
                       p.product_line_vn as product_line, p.gender_by_product as gender,
                       p.age_by_product as age, p.sale_price,
                       COALESCE(m.match_count, 0) as match_count
                FROM sr__test_db__magento_product_dimension_with_text_embedding p
                LEFT JOIN (
                    SELECT anchor_product_code, COUNT(*) as match_count
                    FROM pg__dashboard_canifa__ai_outfit_product_matches
                    GROUP BY anchor_product_code
                ) m ON p.magento_ref_code = m.anchor_product_code
                {where}
                ORDER BY match_count DESC, p.product_name
                LIMIT ? OFFSET ?
            """
            params += [limit, offset]
            c.execute(sql, params)
            products = [dict(r) for r in c.fetchall()]

            # Count total
            count_sql = f"""
                SELECT COUNT(*) as cnt
                FROM sr__test_db__magento_product_dimension_with_text_embedding p
                LEFT JOIN (
                    SELECT anchor_product_code, COUNT(*) as match_count
                    FROM pg__dashboard_canifa__ai_outfit_product_matches
                    GROUP BY anchor_product_code
                ) m ON p.magento_ref_code = m.anchor_product_code
                {where}
            """
            c.execute(count_sql, params[:-2])  # without limit/offset
            total = c.fetchone()["cnt"]
            conn.close()
        else:
            return {"ok": False, "error": "StarRocks mode not implemented yet"}

        return {
            "ok": True,
            "products": products,
            "total": total,
            "page": page,
            "limit": limit,
            "pages": (total + limit - 1) // limit,
        }
    except Exception as e:
        logger.error(f"[OutfitProducts] error: {e}")
        return {"ok": False, "error": str(e)}


@router.get("/{code}")
async def get_product_matches(code: str):
    """Get all outfit matches for a specific product code."""
    try:
        if _use_sqlite():
            conn = _get_sqlite()
            c = conn.cursor()

            # Get anchor product info
            c.execute("""
                SELECT magento_ref_code as code, product_name as name,
                       product_line_vn as product_line, gender_by_product as gender,
                       age_by_product as age, sale_price, master_color as color
                FROM sr__test_db__magento_product_dimension_with_text_embedding
                WHERE magento_ref_code = ?
            """, (code,))
            product = c.fetchone()
            if not product:
                return {"ok": False, "error": f"Product {code} not found"}
            product = dict(product)

            # Get matches — deduplicate by match_product_code (keep highest score)
            c.execute("""
                SELECT m.match_product_code, m.match_product_name, m.match_role, 
                       MAX(m.score) as score, m.ai_reason, m.match_gender, m.match_age
                FROM pg__dashboard_canifa__ai_outfit_product_matches m
                WHERE m.anchor_product_code = ?
                GROUP BY m.match_product_code, m.match_role
                ORDER BY m.match_role, m.score DESC
            """, (code,))
            matches = [dict(r) for r in c.fetchall()]

            # Group by role for clean display
            by_role = {}
            for m in matches:
                role = m.get('match_role', 'other')
                if role not in by_role:
                    by_role[role] = []
                by_role[role].append(m)

            # Also check: where is this product used as a match target?
            c.execute("""
                SELECT anchor_product_code, match_role, MAX(score) as score, ai_reason
                FROM pg__dashboard_canifa__ai_outfit_product_matches
                WHERE match_product_code LIKE ?
                GROUP BY anchor_product_code, match_role
                ORDER BY score DESC
                LIMIT 20
            """, (f"{code}%",))
            used_as_match = [dict(r) for r in c.fetchall()]

            conn.close()
        else:
            return {"ok": False, "error": "StarRocks mode not implemented yet"}

        return {
            "ok": True,
            "product": product,
            "matches": matches,
            "by_role": by_role,
            "match_count": len(matches),
            "used_as_match_in": used_as_match,
        }
    except Exception as e:
        logger.error(f"[OutfitDetail] error: {e}")
        return {"ok": False, "error": str(e)}
