import pymysql
import openpyxl
from openpyxl.styles import PatternFill, Font

wb_path = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\documentacion raul asencio\Productos_Etiquetas_Nutricional_Alergenos.xlsx'
wb = openpyxl.load_workbook(wb_path)
ws = wb.active

conn = pymysql.connect(
    host='85.215.144.168',
    port=3306,
    user='obradores',
    password='74bjGPSkdD!)9_7K',
    database='pastelerias',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# 1. Map articles by EAN
articulos_by_ean = {}
with conn.cursor() as cur:
    cur.execute('SELECT id, ean, descripcion, ingredientes FROM ge_articulos')
    for row in cur.fetchall():
        if row['ean'] and str(row['ean']).strip():
            ean_clean = str(row['ean']).strip()
            articulos_by_ean[ean_clean] = row
            articulos_by_ean[ean_clean.lstrip('0')] = row

    cur.execute('SELECT articulo_id, ean FROM ge_articulos_ean')
    for row in cur.fetchall():
        if row['ean'] and str(row['ean']).strip():
            ean_clean = str(row['ean']).strip()
            if ean_clean not in articulos_by_ean:
                cur.execute('SELECT id, ean, descripcion, ingredientes FROM ge_articulos WHERE id = %s', (row['articulo_id'],))
                art = cur.fetchone()
                if art:
                    articulos_by_ean[ean_clean] = art
                    articulos_by_ean[ean_clean.lstrip('0')] = art

    # Check which articles have ficha tecnica
    cur.execute('SELECT id_articulo FROM ge_articulos_fichas_tecnicas')
    fichas_exist = set(r['id_articulo'] for r in cur.fetchall())

matches = []
unmatched = []

# Style for unmatched rows (soft orange/yellow highlight)
unmatched_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')
matched_fill = PatternFill(start_color='E2EFDA', end_color='E2EFDA', fill_type='solid') # soft green

sql_updates_articulos = []
sql_updates_fichas = []
sql_inserts_fichas = []

for r in range(2, ws.max_row + 1):
    num = ws.cell(row=r, column=1).value
    nombre = ws.cell(row=r, column=2).value
    ean_val = str(ws.cell(row=r, column=3).value or '').strip()
    ingredientes = ws.cell(row=r, column=4).value or ''
    alergenos = ws.cell(row=r, column=5).value or ''
    kj = ws.cell(row=r, column=6).value
    kcal = ws.cell(row=r, column=7).value
    grasas = ws.cell(row=r, column=8).value
    grasas_sat = ws.cell(row=r, column=9).value
    hidratos = ws.cell(row=r, column=10).value
    azucares = ws.cell(row=r, column=11).value
    fibra = ws.cell(row=r, column=12).value
    proteinas = ws.cell(row=r, column=13).value
    sal = ws.cell(row=r, column=14).value

    # Parse numeric
    def to_num(val):
        if val is None or str(val).strip() in ('', 'N/D', 'None', '-'):
            return 0.00
        try:
            return float(str(val).replace(',', '.'))
        except:
            return 0.00

    n_kj = to_num(kj)
    n_kcal = to_num(kcal)
    n_grasas = to_num(grasas)
    n_grasas_sat = to_num(grasas_sat)
    n_hidratos = to_num(hidratos)
    n_azucares = to_num(azucares)
    n_fibra = to_num(fibra)
    n_proteinas = to_num(proteinas)
    n_sal = to_num(sal)

    art = None
    if ean_val in articulos_by_ean:
        art = articulos_by_ean[ean_val]
    elif ean_val.lstrip('0') in articulos_by_ean:
        art = articulos_by_ean[ean_val.lstrip('0')]

    if art:
        art_id = art['id']
        matches.append({
            'row': r,
            'num': num,
            'nombre_excel': nombre,
            'ean': ean_val,
            'art_id': art_id,
            'art_desc': art['descripcion'],
            'ingredientes': ingredientes,
            'kj': n_kj, 'kcal': n_kcal, 'grasas': n_grasas, 'grasas_sat': n_grasas_sat,
            'hidratos': n_hidratos, 'azucares': n_azucares, 'fibra': n_fibra,
            'proteinas': n_proteinas, 'sal': n_sal
        })
        # Apply matched fill (soft green)
        for col in range(1, ws.max_column + 1):
            ws.cell(row=r, column=col).fill = matched_fill
            
        # SQL for ge_articulos
        # Escape string
        ing_escaped = ingredientes.replace("'", "''")
        sql_updates_articulos.append(
            f"UPDATE ge_articulos SET ingredientes = '{ing_escaped}' WHERE id = {art_id}; -- {art['descripcion']} (EAN {ean_val})"
        )
        
        # SQL for ge_articulos_fichas_tecnicas
        if art_id in fichas_exist:
            sql_updates_fichas.append(
                f"UPDATE ge_articulos_fichas_tecnicas SET "
                f"ingredientes = '{ing_escaped}', "
                f"nutricional_energia_kj = {n_kj:.2f}, "
                f"nutricional_energia_kcal = {n_kcal:.2f}, "
                f"nutricional_grasas = {n_grasas:.2f}, "
                f"nutricional_grasas_saturadas = {n_grasas_sat:.2f}, "
                f"nutricional_hidratos = {n_hidratos:.2f}, "
                f"nutricional_azucares = {n_azucares:.2f}, "
                f"nutricional_fibra = {n_fibra:.2f}, "
                f"nutricional_proteinas = {n_proteinas:.2f}, "
                f"nutricional_sal = {n_sal:.2f} "
                f"WHERE id_articulo = {art_id}; -- {art['descripcion']}"
            )
        else:
            sql_inserts_fichas.append(
                f"INSERT INTO ge_articulos_fichas_tecnicas ("
                f"id_articulo, ingredientes, "
                f"nutricional_energia_kj, nutricional_energia_kcal, "
                f"nutricional_grasas, nutricional_grasas_saturadas, "
                f"nutricional_hidratos, nutricional_azucares, "
                f"nutricional_fibra, nutricional_proteinas, nutricional_sal"
                f") VALUES ("
                f"{art_id}, '{ing_escaped}', "
                f"{n_kj:.2f}, {n_kcal:.2f}, "
                f"{n_grasas:.2f}, {n_grasas_sat:.2f}, "
                f"{n_hidratos:.2f}, {n_azucares:.2f}, "
                f"{n_fibra:.2f}, {n_proteinas:.2f}, {n_sal:.2f}"
                f"); -- {art['descripcion']}"
            )
    else:
        unmatched.append({
            'row': r,
            'num': num,
            'nombre_excel': nombre,
            'ean': ean_val
        })
        # Apply unmatched fill (soft orange/yellow)
        for col in range(1, ws.max_column + 1):
            ws.cell(row=r, column=col).fill = unmatched_fill

# Save updated Excel
wb.save(wb_path)
print(f"Excel guardado con estilos en: {wb_path}")
print(f"Total coincidencias: {len(matches)}")
print(f"Total no encontrados: {len(unmatched)}")
print(f"Updates ge_articulos: {len(sql_updates_articulos)}")
print(f"Updates fichas: {len(sql_updates_fichas)}")
print(f"Inserts fichas: {len(sql_inserts_fichas)}")

# Write SQL script to scratch for presentation/review
with open(r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\scratch\propuesta_actualizacion_articulos.sql', 'w', encoding='utf-8') as f:
    f.write("-- =============================================================================\n")
    f.write("-- PROPUESTA DE ACTUALIZACIÓN DE ARTÍCULOS Y FICHAS TÉCNICAS\n")
    f.write(f"-- Coincidencias encontradas por EAN: {len(matches)}\n")
    f.write(f"-- Artículos no encontrados por EAN: {len(unmatched)}\n")
    f.write("-- =============================================================================\n\n")
    
    f.write("-- 1. ACTUALIZACIÓN EN ge_articulos (Campo ingredientes)\n")
    f.write("-- -----------------------------------------------------------------------------\n")
    for s in sql_updates_articulos:
        f.write(s + "\n")
    f.write("\n")
    
    if sql_updates_fichas:
        f.write("-- 2. ACTUALIZACIÓN EN ge_articulos_fichas_tecnicas (Fichas ya existentes)\n")
        f.write("-- -----------------------------------------------------------------------------\n")
        for s in sql_updates_fichas:
            f.write(s + "\n")
        f.write("\n")
        
    if sql_inserts_fichas:
        f.write("-- 3. INSERCIÓN EN ge_articulos_fichas_tecnicas (Artículos sin ficha previa)\n")
        f.write("-- -----------------------------------------------------------------------------\n")
        for s in sql_inserts_fichas:
            f.write(s + "\n")
        f.write("\n")

conn.close()