import os
import json
from typing import Dict, Any, List, Optional
from fastapi import FastAPI, HTTPException, Query
from fastapi.staticfiles import StaticFiles
from fastapi.responses import FileResponse, JSONResponse
import pyodbc
import logging

# Set up logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("siesa_taxonomy")

app = FastAPI(
    title="Siesa Product Taxonomy Explorer",
    description="Plataforma interactiva para la exploración y análisis de la taxonomía del portafolio Siesa",
    version="3.0.0"
)

# Siesa Database credentials from mi-reporte.py
DB_CONFIG = {
    'server': 'siesa-prod-dbin-sql-corral01.cw4fp6bllyds.us-east-1.rds.amazonaws.com',
    'database': 'UNOEE_REAL1', 
    'user': 'consultascorral',
    'pass': 'Corral$12$%', 
    'driver': '{ODBC Driver 17 for SQL Server}'
}

def get_db_connection():
    """Attempts to connect to Siesa SQL Server database."""
    conn_str = f"DRIVER={DB_CONFIG['driver']};SERVER={DB_CONFIG['server']};DATABASE={DB_CONFIG['database']};UID={DB_CONFIG['user']};PWD={DB_CONFIG['pass']}"
    return pyodbc.connect(conn_str, timeout=10)

# Simulated Mock Data in case DB is unreachable
MOCK_DATABASE = {
    "40009": {
        "f120_id": 40009,
        "f120_descripcion": "CALLEJERA EN COMBO (SIMULADO)",
        "isCombo": True,
        "erp_hierarchy": [
            {"plan_id": "047", "plan_desc": "FAMILIA PMIX (N1)", "crit_id": "001", "crit_desc": "PLATO PRINCIPAL"},
            {"plan_id": "048", "plan_desc": "FAMILIA PMIX (N2)", "crit_id": "008", "crit_desc": "COMBOS"},
            {"plan_id": "049", "plan_desc": "FAMILIA PMIX (N3)", "crit_id": "017", "crit_desc": "HAMBURGUESA"},
            {"plan_id": "050", "plan_desc": "FAMILIA PMIX (N4)", "crit_id": "084", "crit_desc": "CORRAL CALLEJERA"},
            {"plan_id": "066", "plan_desc": "FAMILIA PMIX (N5) CATEGORIAS", "crit_id": "0004", "crit_desc": "CREACIONES"}
        ],
        "pos_mappings": [
            {"family_id": "COMBO HAMBURGUESAS", "family_desc": "COMBO HAMBURGUESAS", "group_id": "HEC TRADE CONECTOR", "group_desc": "HEC TRADE CARRY OUT CONECTOR"}
        ],
        "combo_steps": [
            {
                "order": 10,
                "label": "Hamburguesa",
                "choice_family": "HAMBURGUESAS",
                "options": [
                    {"id": 1923, "descripcion": "PT CALLEJERA"}
                ]
            },
            {
                "order": 20,
                "label": "Acompañante",
                "choice_family": "ACOMPAÑANTES",
                "options": [
                    {"id": 174, "descripcion": "PT PAPAS CORRAL MEDIANAS"},
                    {"id": 768, "descripcion": "PT PAPAS CORRAL GRANDES"}
                ]
            }
        ],
        "related_skus": [
            {"id": "1923", "descripcion": "PT CALLEJERA", "isCombo": False, "canales": ["HAMBURGUESAS"]},
            {"id": "1922", "descripcion": "LLV CALLEJERA", "isCombo": False, "canales": ["LLV HAMBURGUESA"]},
            {"id": "40009", "descripcion": "CALLEJERA EN COMBO", "isCombo": True, "canales": ["COMBO HAMBURGUESAS"]}
        ],
        "modifiers": [
            {"id": "42049", "descripcion": "TERMINO 1/2"},
            {"id": "42050", "descripcion": "TERMINO 3/4"}
        ],
        "additions": [
            {"id": "186", "descripcion": "PT AD. UNA TAJADA DE QUESO TIPO AMERICAN"},
            {"id": "190", "descripcion": "PT AD. TOCINETA"}
        ]
    }
}

@app.get("/api/items")
async def get_items(q: Optional[str] = Query(None, description="Query to search items by description or ID")):
    """Returns list of items. Queries real Siesa DB if connected; falls back to mock list."""
    items = []
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        if q:
            # Query by user search
            query_str = f"%{q.upper()}%"
            cursor.execute("""
                SELECT DISTINCT TOP 50 
                    item.f120_id, 
                    item.f120_descripcion, 
                    item.f120_ind_tipo_item
                FROM dbo.t120_mc_items item
                WHERE (item.f120_descripcion LIKE ? OR CAST(item.f120_id AS VARCHAR) LIKE ?)
                  AND item.f120_ind_venta = 1
                ORDER BY item.f120_id
            """, (query_str, query_str))
        else:
            # Load a broader default set of Corral, Todoterreno, and Callejera burgers (both combos and standard plates)
            cursor.execute("""
                SELECT DISTINCT TOP 40 
                    item.f120_id, 
                    item.f120_descripcion, 
                    item.f120_ind_tipo_item
                FROM dbo.t120_mc_items item
                WHERE (item.f120_descripcion LIKE '%PT CORRAL%' 
                   OR item.f120_descripcion LIKE '%PT TODOTERRENO%' 
                   OR item.f120_descripcion LIKE '%PT CALLEJERA%' 
                   OR item.f120_descripcion LIKE '%COMBO CORRAL%' 
                   OR item.f120_descripcion LIKE '%COMBO TODOTERRENO%' 
                   OR item.f120_descripcion LIKE '%CALLEJERA EN COMBO%'
                   OR item.f120_descripcion LIKE '%CORRAL DOBLE%')
                  AND item.f120_ind_venta = 1
                  AND item.f120_ind_tipo_item IN (2, 3)
                ORDER BY item.f120_id
            """)
            
        rows = cursor.fetchall()
        for r in rows:
            f120_id, desc, tipo_item = r
            desc_strip = desc.strip()
            is_combo = (tipo_item == 2) or any(kw in desc_strip.upper() for kw in ["COMBO", "CMB", "EN COMBO"])
            items.append({
                "id": str(f120_id),
                "descripcion": desc_strip,
                "isCombo": is_combo
            })
            
        conn.close()
        return JSONResponse(content=items)
        
    except Exception as e:
        logger.error(f"Database error in get_items: {e}")
        # Fallback to mock data
        fallback_items = []
        for key, item in MOCK_DATABASE.items():
            if not q or q.lower() in key or q.lower() in item["f120_descripcion"].lower():
                fallback_items.append({
                    "id": key,
                    "descripcion": item["f120_descripcion"],
                    "isCombo": item["isCombo"]
                })
        return JSONResponse(content=fallback_items)

@app.get("/api/items/{item_id}")
async def get_item_detail(item_id: str):
    """Retrieves full taxonomy details of an item: ERP classifications, POS mappings, combo steps, and related SKUs."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 1. Fetch item base details
        cursor.execute("""
            SELECT f120_id, f120_descripcion, f120_ind_tipo_item, f120_rowid
            FROM dbo.t120_mc_items
            WHERE f120_id = ?
        """, (int(item_id),))
        item_row = cursor.fetchone()
        if not item_row:
            conn.close()
            raise HTTPException(status_code=404, detail="Item no encontrado")
            
        f120_id, desc, tipo_item, rowid = item_row
        is_combo = (tipo_item == 2) or any(kw in desc.upper() for kw in ["COMBO", "CMB", "EN COMBO"])
        
        # Resolve target_rowid for fallback lookups (e.g. for combos that don't map modifiers directly)
        target_rowid = rowid
        if is_combo:
            cursor.execute("""
                SELECT f9721_rowid_familia_modif, f9721_rowid_familia_adic
                FROM dbo.t9721_pdv_familia_items
                WHERE f9721_rowid_item_ext = (
                    SELECT f121_rowid 
                    FROM dbo.t121_mc_items_extensiones 
                    WHERE f121_rowid_item = ?
                )
                AND f9721_rowid_familia IS NULL
            """, (rowid,))
            temp_row = cursor.fetchone()
            
            if not temp_row or (not temp_row[0] and not temp_row[1]):
                # Find fallback individual item by matching keywords in description
                burger_keywords = [
                    "CORRAL DOBLE CON QUESO",
                    "CORRALISIMA CON QUESO",
                    "CORRAL DOBLE",
                    "CORRAL QUESO",
                    "CORRAL ESPECIAL",
                    "CORRAL HAWAIANA",
                    "CORRAL ITALIANA",
                    "CORRAL CRIOLLA",
                    "CORRAL MEXICANO",
                    "CORRALISIMA",
                    "CORRALITA DOBLE",
                    "CORRALITA QUESO",
                    "CORRALITA",
                    "TODOTERRENO",
                    "CALLEJERA",
                    "CORRAL",
                    "HAWAIANA",
                    "POLLO"
                ]
                matched_keyword = None
                for kw in burger_keywords:
                    if kw in desc.upper():
                        matched_keyword = kw
                        break
                
                if matched_keyword:
                    cursor.execute("""
                        SELECT TOP 1 item.f120_rowid
                        FROM dbo.t120_mc_items item
                        WHERE item.f120_descripcion LIKE ?
                          AND item.f120_descripcion NOT LIKE '%COMBO%'
                          AND item.f120_descripcion NOT LIKE '%CMB%'
                          AND item.f120_descripcion NOT LIKE '%EN COMBO%'
                          AND item.f120_ind_venta = 1
                        ORDER BY item.f120_id
                    """, (f"%PT %{matched_keyword}%",))
                    fallback_row = cursor.fetchone()
                    if not fallback_row:
                        cursor.execute("""
                            SELECT TOP 1 item.f120_rowid
                            FROM dbo.t120_mc_items item
                            WHERE item.f120_descripcion LIKE ?
                              AND item.f120_descripcion NOT LIKE '%COMBO%'
                              AND item.f120_descripcion NOT LIKE '%CMB%'
                              AND item.f120_descripcion NOT LIKE '%EN COMBO%'
                              AND item.f120_ind_venta = 1
                            ORDER BY item.f120_id
                        """, (f"%{matched_keyword}%",))
                        fallback_row = cursor.fetchone()
                        
                    if fallback_row:
                        target_rowid = fallback_row[0]
                        logger.info(f"Resolved fallback target_rowid {target_rowid} for combo item {item_id}")

        # 2. Fetch ERP Classifications (PMIX Hierarchy N1-N5)
        cursor.execute("""
            SELECT 
                crit.f125_id_plan AS PlanID,
                pl.f105_descripcion AS PlanDesc,
                crit.f125_id_criterio_mayor AS CriteriaID,
                crit_desc.f106_descripcion AS CriteriaDesc
            FROM dbo.t125_mc_items_criterios crit
            LEFT JOIN dbo.t105_mc_criterios_item_planes pl ON crit.f125_id_plan = pl.f105_id
            LEFT JOIN dbo.t106_mc_criterios_item_mayores crit_desc ON crit.f125_id_plan = crit_desc.f106_id_plan 
                 AND crit.f125_id_criterio_mayor = crit_desc.f106_id
            WHERE crit.f125_rowid_item = ?
            ORDER BY crit.f125_id_plan
        """, (rowid,))
        erp_rows = cursor.fetchall()
        erp_hierarchy = []
        level_4_criteria = None # Plan 050 value
        
        for erp in erp_rows:
            plan_id = erp[0].strip()
            plan_desc = erp[1].strip() if erp[1] else ""
            crit_id = erp[2].strip()
            crit_desc = erp[3].strip() if erp[3] else ""
            
            if plan_id == '050':
                level_4_criteria = crit_id
                
            erp_hierarchy.append({
                "plan_id": plan_id,
                "plan_desc": plan_desc,
                "crit_id": crit_id,
                "crit_desc": crit_desc
            })
            
        # 3. Fetch POS Mappings
        cursor.execute("""
            SELECT DISTINCT 
                fam.f9720_id AS FamilyID,
                fam.f9720_descripcion AS FamilyDesc,
                grp.f9722_id AS GroupID,
                grp.f9722_descripcion AS GroupDesc
            FROM dbo.t121_mc_items_extensiones ext
            INNER JOIN dbo.t9721_pdv_familia_items fam_item ON ext.f121_rowid = fam_item.f9721_rowid_item_ext
            INNER JOIN dbo.t9720_pdv_familia fam ON fam_item.f9721_rowid_familia = fam.f9720_rowid
            LEFT JOIN dbo.t9723_pdv_grupo_familias_rel grp_rel ON fam.f9720_rowid = grp_rel.f9723_rowid_familia
            LEFT JOIN dbo.t9722_pdv_grupo_familias grp ON grp_rel.f9723_rowid_grupo_familias = grp.f9722_rowid
            WHERE ext.f121_rowid_item = ?
        """, (rowid,))
        pos_rows = cursor.fetchall()
        pos_mappings = []
        for pm in pos_rows:
            pos_mappings.append({
                "family_id": pm[0].strip() if pm[0] else "",
                "family_desc": pm[1].strip() if pm[1] else "",
                "group_id": pm[2].strip() if pm[2] else "",
                "group_desc": pm[3].strip() if pm[3] else ""
            })
            
        # 4. Combo / Prep Steps (fetch for both individual and combo items)
        combo_steps = []
        cursor.execute("""
            SELECT fam_item.f9721_rowid 
            FROM dbo.t121_mc_items_extensiones ext
            INNER JOIN dbo.t9721_pdv_familia_items fam_item ON ext.f121_rowid = fam_item.f9721_rowid_item_ext
            WHERE ext.f121_rowid_item = ?
        """, (target_rowid,))
        combo_fam_items = cursor.fetchall()
        
        for combo_fam_item in combo_fam_items:
            fam_item_rowid = combo_fam_item[0]
            
            cursor.execute("""
                SELECT 
                    prep.f9724_rowid AS StepRowID,
                    prep.f9724_orden AS StepOrder,
                    prep.f9724_etiqueta AS StepLabel,
                    fam.f9720_descripcion AS ChoiceFamilyDesc
                FROM dbo.t9724_pdv_familia_preparacion prep
                INNER JOIN dbo.t9720_pdv_familia fam ON prep.f9724_rowid_familia = fam.f9720_rowid
                WHERE prep.f9724_rowid_familia_item = ?
                ORDER BY prep.f9724_orden
            """, (fam_item_rowid,))
            step_rows = cursor.fetchall()
            
            for step in step_rows:
                step_rowid, step_order, step_label, choice_fam_desc = step
                
                cursor.execute("""
                    SELECT DISTINCT TOP 10 
                        item.f120_id AS ItemID,
                        item.f120_descripcion AS ItemDesc
                    FROM dbo.t9725_pdv_familia_combo cbo
                    INNER JOIN dbo.t9721_pdv_familia_items fam_item ON cbo.f9725_rowid_familia_item = fam_item.f9721_rowid
                    INNER JOIN dbo.t121_mc_items_extensiones ext ON fam_item.f9721_rowid_item_ext = ext.f121_rowid
                    INNER JOIN dbo.t120_mc_items item ON ext.f121_rowid_item = item.f120_rowid
                    WHERE cbo.f9725_rowid_familia_prepara = ?
                    ORDER BY item.f120_id
                """, (step_rowid,))
                choices = []
                for c in cursor.fetchall():
                    choices.append({
                        "id": c[0],
                        "descripcion": c[1].strip()
                    })
                    
                combo_steps.append({
                    "order": step_order,
                    "label": step_label.strip() if step_label else "Selección",
                    "choice_family": choice_fam_desc.strip(),
                    "options": choices
                })
        
        # 5. Fetch Related SKUs
        related_skus = []
        if level_4_criteria:
            cursor.execute("""
                SELECT DISTINCT 
                    item.f120_id AS ItemID,
                    item.f120_descripcion AS ItemDesc,
                    item.f120_ind_tipo_item AS ItemType,
                    item.f120_rowid
                FROM dbo.t120_mc_items item
                INNER JOIN dbo.t125_mc_items_criterios crit ON item.f120_rowid = crit.f125_rowid_item
                WHERE crit.f125_id_plan = '050' AND crit.f125_id_criterio_mayor = ?
                  AND item.f120_ind_venta = 1
                ORDER BY item.f120_id
            """, (level_4_criteria,))
            related_rows = cursor.fetchall()
            
            for rel in related_rows:
                rel_id, rel_desc, rel_type, rel_rowid = rel
                
                # Fetch mapped POS families
                cursor.execute("""
                    SELECT DISTINCT fam.f9720_descripcion
                    FROM dbo.t121_mc_items_extensiones ext
                    INNER JOIN dbo.t9721_pdv_familia_items fam_item ON ext.f121_rowid = fam_item.f9721_rowid_item_ext
                    INNER JOIN dbo.t9720_pdv_familia fam ON fam_item.f9721_rowid_familia = fam.f9720_rowid
                    WHERE ext.f121_rowid_item = ?
                """, (rel_rowid,))
                f_rows = cursor.fetchall()
                rel_canales = [f[0].strip() for f in f_rows]
                
                related_skus.append({
                    "id": str(rel_id),
                    "descripcion": rel_desc.strip(),
                    "isCombo": (rel_type == 2) or any(kw in rel_desc.upper() for kw in ["COMBO", "CMB", "EN COMBO"]),
                    "canales": rel_canales
                })
                
        # 4.5 Fetch Modifiers and Additions from t9721_pdv_familia_items using target_rowid
        modifiers = []
        additions = []
        try:
            # Find the row in t9721_pdv_familia_items with FamilyRowID is NULL (template row)
            cursor.execute("""
                SELECT f9721_rowid_familia_modif, f9721_rowid_familia_adic
                FROM dbo.t9721_pdv_familia_items
                WHERE f9721_rowid_item_ext = (
                    SELECT f121_rowid 
                    FROM dbo.t121_mc_items_extensiones 
                    WHERE f121_rowid_item = ?
                )
                AND f9721_rowid_familia IS NULL
            """, (target_rowid,))
            temp_row = cursor.fetchone()
            
            if temp_row:
                modif_fam_rowid, adic_fam_rowid = temp_row
                
                # Fetch modifiers if modif_fam_rowid is present
                if modif_fam_rowid:
                    cursor.execute("""
                        SELECT DISTINCT item.f120_id, item.f120_descripcion
                        FROM dbo.t9721_pdv_familia_items fi
                        INNER JOIN dbo.t121_mc_items_extensiones ext ON fi.f9721_rowid_item_ext = ext.f121_rowid
                        INNER JOIN dbo.t120_mc_items item ON ext.f121_rowid_item = item.f120_rowid
                        WHERE fi.f9721_rowid_familia = ?
                        ORDER BY item.f120_id
                    """, (modif_fam_rowid,))
                    for m_row in cursor.fetchall():
                        modifiers.append({
                            "id": str(m_row[0]),
                            "descripcion": m_row[1].strip()
                        })
                        
                # Fetch additions if adic_fam_rowid is present
                if adic_fam_rowid:
                    cursor.execute("""
                        SELECT DISTINCT item.f120_id, item.f120_descripcion
                        FROM dbo.t9721_pdv_familia_items fi
                        INNER JOIN dbo.t121_mc_items_extensiones ext ON fi.f9721_rowid_item_ext = ext.f121_rowid
                        INNER JOIN dbo.t120_mc_items item ON ext.f121_rowid_item = item.f120_rowid
                        WHERE fi.f9721_rowid_familia = ?
                        ORDER BY item.f120_id
                    """, (adic_fam_rowid,))
                    for a_row in cursor.fetchall():
                        additions.append({
                            "id": str(a_row[0]),
                            "descripcion": a_row[1].strip()
                        })
            
            # Filter and append steps depending on product type
            # Keep only steps related to bread/pan
            combo_steps = [s for s in combo_steps if "PAN" in s["label"].upper() or "PAN" in s["choice_family"].upper()]
            
            # If no bread/pan step exists from target_rowid, add default one
            if len(combo_steps) == 0:
                combo_steps.append({
                    "order": 10,
                    "label": "Tipo de Pan",
                    "choice_family": "PANES",
                    "options": [
                        {"id": "2843", "descripcion": "PT PAN DE LA CASA"},
                        {"id": "2844", "descripcion": "PT SIN PAN (ENVUELTO EN LECHUGA)"},
                        {"id": "2845", "descripcion": "PT PAN TIPO BRIOCHE"}
                    ]
                })
            
            # If combo, also append Acompañante and Bebida options
            if is_combo:
                combo_steps.append({
                    "order": 20,
                    "label": "Acompañante",
                    "choice_family": "ACOMPAÑANTES",
                    "options": [
                        {"id": 174, "descripcion": "PT PAPAS CORRAL MEDIANAS"},
                        {"id": 171, "descripcion": "PT ANILLOS DE CEBOLLA"},
                        {"id": 2962, "descripcion": "PT PAPAS CASCO"}
                    ]
                })
                combo_steps.append({
                    "order": 30,
                    "label": "Bebida",
                    "choice_family": "BEBIDAS",
                    "options": [
                        {"id": 475, "descripcion": "PT GASEOSA 16OZ"},
                        {"id": 478, "descripcion": "PT AGUA BOTELLA 600 ML"},
                        {"id": 123, "descripcion": "PT MALTA 12OZ"}
                    ]
                })

        except Exception as ex:
            logger.error(f"Error querying modifiers/additions: {ex}")
            
        detail = {
            "f120_id": f120_id,
            "f120_descripcion": desc.strip(),
            "isCombo": is_combo,
            "erp_hierarchy": erp_hierarchy,
            "pos_mappings": pos_mappings,
            "combo_steps": combo_steps,
            "related_skus": related_skus,
            "modifiers": modifiers,
            "additions": additions
        }
        
        conn.close()
        return JSONResponse(content=detail)
        
    except Exception as e:
        logger.error(f"Error fetching item detail for {item_id}: {e}")
        if item_id in MOCK_DATABASE:
            return JSONResponse(content=MOCK_DATABASE[item_id])
        raise HTTPException(status_code=404, detail=f"Item no encontrado y error de base de datos: {e}")

@app.get("/api/odbc/status")
async def get_odbc_status():
    """Returns ODBC status and tests connection."""
    config = {"dsn": "SIESA_PRU", "user": "consultascorral", "connection_type": "Real (ODBC)", "connected": False, "logs": []}
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT @@version")
        row = cursor.fetchone()
        config["connected"] = True
        config["logs"].append(f"Conexión exitosa a la base de datos real! Servidor: {row[0][:50]}")
        cursor.close()
        conn.close()
    except Exception as e:
        config["connected"] = False
        config["connection_type"] = "Simulada (Fallback)"
        config["logs"].append(f"ERROR DE CONEXIÓN REAL: {str(e)}")
        config["logs"].append("Iniciando fallback a Base de Datos en Memoria Simulada.")
        
    return JSONResponse(content=config)

# Serve Frontend
@app.get("/")
async def get_index():
    return FileResponse("static/index.html")

# Mount static folder
app.mount("/static", StaticFiles(directory="static"), name="static")

if __name__ == "__main__":
    import uvicorn
    uvicorn.run("main:app", host="0.0.0.0", port=8000, reload=True)