"""
═══════════════════════════════════════════════════════════════
  CARGADOR DE DATOS - PROCUREMENT ANALYTICS
  Soporta CSV y XLSX. Detecta formato automáticamente.
═══════════════════════════════════════════════════════════════

USO:
  python actualizar_datos.py                          → Carga el archivo más reciente de data/
  python actualizar_datos.py mi_reporte.csv           → Carga un archivo específico
  python actualizar_datos.py mi_reporte.xlsx          → Carga un Excel específico

FORMATOS SOPORTADOS:
  - CSV  (recomendado, más rápido, no requiere dependencias extra)
  - XLSX (requiere openpyxl: pip install openpyxl)

DÓNDE PONER LOS ARCHIVOS:
  Coloca tus reportes descargados en la carpeta:
    C:/xampp/htdocs/Procurement/data/
  
  El script tomará automáticamente el archivo más reciente.
"""
import csv
import sys
import os
import glob
from datetime import datetime

# ============================================================
# CONFIGURACIÓN
# ============================================================
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.path.join(SCRIPT_DIR, 'data')
DB_CONFIG = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '',
    'database': 'procurement_analytics',
    'charset': 'utf8mb4',
    'allow_local_infile': True
}

# Mapeo de columnas (orden del CSV/XLSX)
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',
]

DATE_COLUMNS = {
    'fecha_creacion', 'fecha_envio_autorizacion', 'fecha_autorizacion',
    'fecha_invitacion_proveedor', 'fecha_finalizacion_cotizacion',
    'fecha_envio_autorizacion_oc', 'fecha_autorizacion_oc', 'fecha_recepcion'
}
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 segun su tipo de columna."""
    if raw_value is None:
        return None
    val = str(raw_value).strip()
    if val == '' or val.lower() == 'none' or val.lower() == 'nan':
        return None

    if col_name in DATE_COLUMNS:
        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

    return ' '.join(val.split())


def read_csv_file(filepath):
    """Lee un archivo CSV y devuelve filas como listas de valores."""
    # Intentar diferentes encodings
    for encoding in ['utf-8-sig', 'utf-8', 'latin-1', 'cp1252']:
        try:
            with open(filepath, 'r', encoding=encoding) as f:
                reader = csv.DictReader(f)
                headers = reader.fieldnames
                rows = list(reader)
            print(f"   Encoding detectado: {encoding}")
            print(f"   Columnas: {len(headers)}")
            print(f"   Filas: {len(rows)}")
            return headers, rows
        except (UnicodeDecodeError, UnicodeError):
            continue
    raise ValueError("No se pudo leer el CSV con ningun encoding conocido")


def read_xlsx_file(filepath):
    """Lee un archivo XLSX y devuelve filas como listas de diccionarios."""
    try:
        import openpyxl
    except ImportError:
        print("\n   Instalando openpyxl para leer archivos Excel...")
        os.system(f"{sys.executable} -m pip install openpyxl")
        import openpyxl

    wb = openpyxl.load_workbook(filepath, read_only=True, data_only=True)
    ws = wb.active
    print(f"   Hoja activa: {ws.title}")

    all_rows = list(ws.iter_rows(values_only=True))
    if not all_rows:
        raise ValueError("El archivo Excel esta vacio")

    headers = [str(h).strip() if h else f'col_{i}' for i, h in enumerate(all_rows[0])]
    print(f"   Columnas: {len(headers)}")

    rows = []
    for row_data in all_rows[1:]:
        row_dict = {}
        for i, val in enumerate(row_data):
            if i < len(headers):
                row_dict[headers[i]] = val
        rows.append(row_dict)

    print(f"   Filas: {len(rows)}")
    wb.close()
    return headers, rows


def find_latest_file():
    """Busca el archivo mas reciente en la carpeta data/."""
    if not os.path.exists(DATA_DIR):
        os.makedirs(DATA_DIR)
        print(f"\n   Se creo la carpeta: {DATA_DIR}")
        print(f"   Coloca aqui tus reportes descargados.")
        return None

    patterns = ['*.csv', '*.xlsx']
    files = []
    for pattern in patterns:
        files.extend(glob.glob(os.path.join(DATA_DIR, pattern)))

    # Tambien buscar en la raiz del proyecto
    root_dir = SCRIPT_DIR
    for pattern in patterns:
        files.extend(glob.glob(os.path.join(root_dir, pattern)))

    if not files:
        return None

    # Ordenar por fecha de modificacion (mas reciente primero)
    files.sort(key=os.path.getmtime, reverse=True)
    return files[0]


def load_to_mysql(rows_data, headers):
    """Carga las filas procesadas a MySQL."""
    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

    print(f"\n   Conectando a MySQL ({DB_CONFIG['host']}:{DB_CONFIG['port']})...")
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()
    print("   Conexion exitosa")

    # Preguntar si reemplazar o agregar
    cursor.execute("SELECT COUNT(*) FROM staging_compras")
    existing = cursor.fetchone()[0]

    if existing > 0:
        print(f"\n   La tabla ya tiene {existing} registros.")
        print("   REEMPLAZANDO datos existentes con los nuevos...")
        cursor.execute("DELETE FROM staging_compras")
        cursor.execute("ALTER TABLE staging_compras AUTO_INCREMENT = 1")
        conn.commit()

    # Preparar INSERT
    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

    for row_num, row in enumerate(rows_data, start=2):
        try:
            values = []
            for i, col_name in enumerate(COLUMN_MAP):
                if i < len(headers):
                    csv_col = headers[i]
                    raw = row.get(csv_col, '')
                else:
                    raw = ''
                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}")

    if batch:
        cursor.executemany(insert_sql, batch)
        conn.commit()
        rows_loaded += len(batch)

    print(f"\r   Cargadas: {rows_loaded} filas.          ")

    # Validacion
    print("\n   --- VALIDACION ---")
    validations = [
        ("Total en tabla", "SELECT COUNT(*) FROM staging_compras"),
        ("Con fecha recepcion", "SELECT COUNT(*) FROM staging_compras WHERE fecha_recepcion IS NOT NULL"),
        ("Sin recepcion (backlog)", "SELECT COUNT(*) FROM staging_compras WHERE fecha_recepcion IS NULL"),
        ("Subsidiarias", "SELECT COUNT(DISTINCT subsidiaria) FROM staging_compras"),
        ("Proveedores", "SELECT COUNT(DISTINCT TRIM(proveedor)) FROM staging_compras WHERE TRIM(proveedor) != ''"),
        ("Gasto 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}")

    print(f"\n   Filas con error: {rows_error}")

    cursor.close()
    conn.close()
    return rows_loaded, rows_error


def main():
    print("=" * 60)
    print("  ACTUALIZACION DE DATOS - PROCUREMENT ANALYTICS")
    print("=" * 60)
    print(f"  Fecha: {datetime.now().strftime('%d/%m/%Y %H:%M')}")

    # Determinar archivo a cargar
    if len(sys.argv) > 1:
        filepath = sys.argv[1]
        if not os.path.isabs(filepath):
            filepath = os.path.join(os.getcwd(), filepath)
    else:
        print(f"\n   Buscando archivos en:")
        print(f"   1. {DATA_DIR}")
        print(f"   2. {SCRIPT_DIR}")
        filepath = find_latest_file()

    if not filepath or not os.path.exists(filepath):
        print(f"\n   ERROR: No se encontro ningun archivo CSV o XLSX.")
        print(f"\n   COMO ACTUALIZAR DATOS:")
        print(f"   1. Descarga tu reporte del sistema en formato CSV")
        print(f"   2. Coloca el archivo en: {DATA_DIR}")
        print(f"   3. Ejecuta: python actualizar_datos.py")
        print(f"\n   O especifica el archivo directamente:")
        print(f"   python actualizar_datos.py ruta/al/archivo.csv")
        sys.exit(1)

    ext = os.path.splitext(filepath)[1].lower()
    print(f"\n   Archivo: {os.path.basename(filepath)}")
    print(f"   Ruta: {filepath}")
    print(f"   Formato: {ext.upper()}")
    print(f"   Tamano: {os.path.getsize(filepath) / 1024:.1f} KB")

    # Leer archivo
    print(f"\n   Leyendo archivo...")
    if ext == '.csv':
        headers, rows = read_csv_file(filepath)
    elif ext in ('.xlsx', '.xls'):
        headers, rows = read_xlsx_file(filepath)
    else:
        print(f"\n   ERROR: Formato '{ext}' no soportado. Usa CSV o XLSX.")
        sys.exit(1)

    if not rows:
        print("   ERROR: El archivo no contiene datos.")
        sys.exit(1)

    # Cargar a MySQL
    loaded, errors = load_to_mysql(rows, headers)
    
    # Run normalization
    print("\n   Ejecutando normalizacion de datos (Fase 2)...")
    normalize_script = os.path.join(SCRIPT_DIR, 'sql', 'normalize_data.py')
    os.system(f"{sys.executable} \"{normalize_script}\"")

    # Resultado final
    print("\n" + "=" * 60)
    
    # Mover archivo a la carpeta 'procesados'
    import shutil
    processed_dir = os.path.join(DATA_DIR, 'procesados')
    os.makedirs(processed_dir, exist_ok=True)
    
    filename = os.path.basename(filepath)
    name, ext_file = os.path.splitext(filename)
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    new_filename = f"{name}_{timestamp}{ext_file}"
    dest_path = os.path.join(processed_dir, new_filename)
    
    try:
        shutil.move(filepath, dest_path)
        print(f"  ARCHIVO ARCHIVADO EN: data/procesados/{new_filename}")
    except Exception as e:
        print(f"  ADVERTENCIA: No se pudo mover el archivo: {e}")
    if errors == 0:
        print(f"  EXITO: {loaded} registros cargados sin errores")
    else:
        print(f"  COMPLETADO: {loaded} cargados, {errors} con error")
    print(f"\n  Dashboard actualizado en:")
    print(f"  http://localhost/Procurement/dashboard/")
    print("=" * 60)


if __name__ == '__main__':
    main()