"""
Cargador de datos CSV → MySQL para el sistema de Procurement Analytics.
Carga el reporte de compras directamente a la tabla staging_compras.

Requisitos: pip install mysql-connector-python
"""
import csv
import sys
import os

try:
    import mysql.connector
except ImportError:
    print("Instalando mysql-connector-python...")
    os.system(f"{sys.executable} -m pip install mysql-connector-python")
    import mysql.connector

# ============================================================
# CONFIGURACIÓN - Ajustar según tu XAMPP
# ============================================================
DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '',  # XAMPP default: sin contraseña
    'database': 'procurement_analytics',
    'charset': 'utf8mb4',
    'allow_local_infile': True
}

CSV_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)),
                        '..', 'reporte_comprador_autorizador_2026_05_01_al_2026_06_09.csv')
CSV_ENCODING = 'latin-1'

# Mapeo de columnas CSV → columnas de la tabla
COLUMN_MAP = [
    'id_interno_linea',
    'subsidiaria',
    'folio_requisicion',
    'cotizacion',
    'folio_orden_compra',
    'solicitante',
    'autoriza_req',
    'comprador',
    'autorizador_oc',
    'comentarios',
    'area_consumo',
    'ubicacion_consumo',
    'ubicacion_almacen',
    'ubicacion_recepcion',
    'fecha_creacion',
    'fecha_envio_autorizacion',
    'fecha_autorizacion',
    'fecha_invitacion_proveedor',
    'fecha_finalizacion_cotizacion',
    'fecha_envio_autorizacion_oc',
    'fecha_autorizacion_oc',
    'fecha_recepcion',
    'estatus_requisicion',
    'estatus_almacen',
    'estatus_cotizacion',
    'estatus_orden_compra',
    'articulo',
    'unidad',
    'cantidad_oc',
    'cantidad_solicitada',
    'precio_ultima_compra',
    'familia',
    'proveedor',
    'precio_final_mxn',
    'moneda',
    'precio_final_proveedor',
    'monto_total_colocado',
    'por_var_ultimo_precio',
    'por_var_precio_max',
    'por_var_precio_min',
    'proveedor_max',
    'precio_final_max',
    'proveedor_min',
    'precio_final_min',
]

# Columnas de fecha (necesitan parseo especial)
DATE_COLUMNS = {
    'fecha_creacion', 'fecha_envio_autorizacion', 'fecha_autorizacion',
    'fecha_invitacion_proveedor', 'fecha_finalizacion_cotizacion',
    'fecha_envio_autorizacion_oc', 'fecha_autorizacion_oc', 'fecha_recepcion'
}

# Columnas numéricas
DECIMAL_COLUMNS = {
    'cantidad_oc', 'cantidad_solicitada', 'precio_ultima_compra',
    'precio_final_mxn', 'precio_final_proveedor', 'monto_total_colocado',
    'por_var_ultimo_precio', 'por_var_precio_max', 'por_var_precio_min',
    'precio_final_max', 'precio_final_min'
}

INT_COLUMNS = {'id_interno_linea'}


def clean_value(col_name, raw_value):
    """Limpia y convierte un valor según su tipo de columna."""
    val = raw_value.strip() if raw_value else ''
    
    if val == '':
        return None
    
    if col_name in DATE_COLUMNS:
        # Formato: "2026-05-01 11:15:57.0" → "2026-05-01 11:15:57"
        val = val.replace('.0', '').strip()
        if val == '':
            return None
        return val
    
    if col_name in INT_COLUMNS:
        try:
            return int(float(val))
        except (ValueError, TypeError):
            return None
    
    if col_name in DECIMAL_COLUMNS:
        try:
            return float(val)
        except (ValueError, TypeError):
            return None
    
    # Campos de texto: limpiar espacios extra
    return ' '.join(val.split())


def main():
    print("=" * 60)
    print("  CARGADOR DE DATOS - PROCUREMENT ANALYTICS")
    print("=" * 60)
    
    # 1. Verificar archivo CSV
    csv_path = os.path.normpath(CSV_FILE)
    if not os.path.exists(csv_path):
        print(f"\n❌ ERROR: No se encontró el archivo CSV en:\n   {csv_path}")
        sys.exit(1)
    print(f"\n✅ Archivo CSV encontrado: {csv_path}")
    
    # 2. Conectar a MySQL
    print(f"\n📡 Conectando a MySQL ({DB_CONFIG['host']}:{DB_CONFIG['port']})...")
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        print("✅ Conexión exitosa")
    except mysql.connector.Error as e:
        print(f"\n❌ ERROR de conexión: {e}")
        print("\n💡 Verifica que:")
        print("   1. XAMPP MySQL está ejecutándose")
        print("   2. La base de datos 'procurement_analytics' existe")
        print("   3. Ejecuta primero: sql/001_create_database.sql")
        sys.exit(1)
    
    # 3. Limpiar tabla staging
    print("\n🗑️  Limpiando tabla staging_compras...")
    cursor.execute("DELETE FROM staging_compras")
    cursor.execute("ALTER TABLE staging_compras AUTO_INCREMENT = 1")
    conn.commit()
    
    # 4. Leer CSV y cargar datos
    print(f"\n📂 Leyendo CSV (encoding: {CSV_ENCODING})...")
    
    placeholders = ', '.join(['%s'] * len(COLUMN_MAP))
    columns = ', '.join(COLUMN_MAP)
    insert_sql = f"INSERT INTO staging_compras ({columns}) VALUES ({placeholders})"
    
    rows_loaded = 0
    rows_error = 0
    batch = []
    batch_size = 100
    
    with open(csv_path, 'r', encoding=CSV_ENCODING) as f:
        reader = csv.DictReader(f)
        csv_headers = reader.fieldnames
        
        print(f"   Columnas detectadas: {len(csv_headers)}")
        
        for row_num, row in enumerate(reader, start=2):
            try:
                values = []
                for i, col_name in enumerate(COLUMN_MAP):
                    csv_col = csv_headers[i]
                    raw = row.get(csv_col, '')
                    values.append(clean_value(col_name, raw))
                
                batch.append(tuple(values))
                
                if len(batch) >= batch_size:
                    cursor.executemany(insert_sql, batch)
                    conn.commit()
                    rows_loaded += len(batch)
                    batch = []
                    print(f"\r   Cargadas: {rows_loaded} filas...", end='', flush=True)
                    
            except Exception as e:
                rows_error += 1
                if rows_error <= 5:
                    print(f"\n   ⚠️  Error fila {row_num}: {e}")
    
    # Cargar último batch
    if batch:
        cursor.executemany(insert_sql, batch)
        conn.commit()
        rows_loaded += len(batch)
    
    print(f"\r   Cargadas: {rows_loaded} filas.          ")
    
    # 5. Validación
    print("\n" + "=" * 60)
    print("  VALIDACIÓN DE CARGA")
    print("=" * 60)
    
    cursor.execute("SELECT COUNT(*) FROM staging_compras")
    total = cursor.fetchone()[0]
    print(f"\n   Total filas en tabla: {total}")
    print(f"   Filas con error:     {rows_error}")
    
    # Validaciones rápidas
    validations = [
        ("Filas con fecha_creacion", "SELECT COUNT(*) FROM staging_compras WHERE fecha_creacion IS NOT NULL"),
        ("Filas con fecha_recepcion", "SELECT COUNT(*) FROM staging_compras WHERE fecha_recepcion IS NOT NULL"),
        ("Subsidiarias distintas", "SELECT COUNT(DISTINCT subsidiaria) FROM staging_compras"),
        ("Compradores distintos", "SELECT COUNT(DISTINCT TRIM(comprador)) FROM staging_compras WHERE TRIM(comprador) != ''"),
        ("Proveedores distintos", "SELECT COUNT(DISTINCT TRIM(proveedor)) FROM staging_compras WHERE TRIM(proveedor) != ''"),
        ("Familias distintas", "SELECT COUNT(DISTINCT familia) FROM staging_compras WHERE familia IS NOT NULL AND TRIM(familia) != ''"),
        ("OC con folio", "SELECT COUNT(DISTINCT folio_orden_compra) FROM staging_compras WHERE folio_orden_compra IS NOT NULL AND TRIM(folio_orden_compra) != ''"),
        ("Monto total MXN", "SELECT FORMAT(SUM(precio_final_mxn), 0) FROM staging_compras WHERE precio_final_mxn > 0"),
    ]
    
    for label, query in validations:
        cursor.execute(query)
        result = cursor.fetchone()[0]
        print(f"   {label}: {result}")
    
    # 6. Primer KPI rápido
    print("\n" + "=" * 60)
    print("  PRIMER DIAGNÓSTICO RÁPIDO (KPI-T6)")
    print("=" * 60)
    
    kpi_query = """
    SELECT 'E1: Creación → Envío Auth REQ' AS etapa,
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_creacion, fecha_envio_autorizacion)), 1) AS promedio_horas,
           COUNT(*) AS registros
    FROM staging_compras WHERE fecha_creacion IS NOT NULL AND fecha_envio_autorizacion IS NOT NULL
    UNION ALL
    SELECT 'E2: Envío Auth → Autorización REQ',
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_envio_autorizacion, fecha_autorizacion)), 1), COUNT(*)
    FROM staging_compras WHERE fecha_envio_autorizacion IS NOT NULL AND fecha_autorizacion IS NOT NULL
    UNION ALL
    SELECT 'E3: Auth REQ → Invitación Prov',
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_autorizacion, fecha_invitacion_proveedor)), 1), COUNT(*)
    FROM staging_compras WHERE fecha_autorizacion IS NOT NULL AND fecha_invitacion_proveedor IS NOT NULL
    UNION ALL
    SELECT 'E4: Invitación → Fin Cotización',
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_invitacion_proveedor, fecha_finalizacion_cotizacion)), 1), COUNT(*)
    FROM staging_compras WHERE fecha_invitacion_proveedor IS NOT NULL AND fecha_finalizacion_cotizacion IS NOT NULL
    UNION ALL
    SELECT 'E5: Fin Cot → Envío Auth OC',
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_finalizacion_cotizacion, fecha_envio_autorizacion_oc)), 1), COUNT(*)
    FROM staging_compras WHERE fecha_finalizacion_cotizacion IS NOT NULL AND fecha_envio_autorizacion_oc IS NOT NULL
    UNION ALL
    SELECT 'E6: Envío Auth → Auth OC',
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_envio_autorizacion_oc, fecha_autorizacion_oc)), 1), COUNT(*)
    FROM staging_compras WHERE fecha_envio_autorizacion_oc IS NOT NULL AND fecha_autorizacion_oc IS NOT NULL
    UNION ALL
    SELECT 'E7: Auth OC → Recepción',
           ROUND(AVG(TIMESTAMPDIFF(HOUR, fecha_autorizacion_oc, fecha_recepcion)), 1), COUNT(*)
    FROM staging_compras WHERE fecha_autorizacion_oc IS NOT NULL AND fecha_recepcion IS NOT NULL
    ORDER BY promedio_horas DESC
    """
    
    cursor.execute(kpi_query)
    results = cursor.fetchall()
    
    print(f"\n   {'Etapa':<40} {'Prom. Horas':>12} {'Registros':>10}")
    print("   " + "-" * 62)
    for etapa, horas, registros in results:
        bar = "█" * min(int((horas or 0) / 10), 30)
        print(f"   {etapa:<40} {horas or 0:>12.1f} {registros:>10}  {bar}")
    
    print("\n✅ ¡Carga completada exitosamente!")
    print(f"   Dashboard disponible en: http://localhost/Procurement/dashboard/")
    
    cursor.close()
    conn.close()


if __name__ == '__main__':
    main()