import pymysql
import openpyxl
from openpyxl.styles import PatternFill

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

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

matched_fill = PatternFill(start_color='E2EFDA', end_color='E2EFDA', fill_type='solid') # verde
unmatched_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid') # amarillo

# Custom mapping for rows based on user input and specific EAN/ID resolution
row_art_map = {}

with conn.cursor() as cur:
    # 1. Fetch all ge_articulos
    cur.execute('SELECT id, ean, descripcion, ingredientes FROM ge_articulos')
    art_by_id = {}
    art_by_ean = {}
    for r in cur.fetchall():
        art_by_id[r['id']] = r
        if r['ean']:
            ean_str = str(r['ean']).strip()
            art_by_ean[ean_str] = r
            art_by_ean[ean_str.lstrip('0')] = r

    # 2. Fetch ge_articulos_ean
    cur.execute('SELECT articulo_id, ean FROM ge_articulos_ean')
    for r in cur.fetchall():
        if r['ean']:
            ean_str = str(r['ean']).strip()
            if ean_str not in art_by_ean and r['articulo_id'] in art_by_id:
                art_by_ean[ean_str] = art_by_id[r['articulo_id']]
                art_by_ean[ean_str.lstrip('0')] = art_by_id[r['articulo_id']]

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

matches = []
unmatched = []

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
    col3_val = ws.cell(row=r, column=3).value
    ingredientes = ws.cell(row=r, column=4).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

    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)

    val_str = str(col3_val).strip() if col3_val is not None else ''

    art = None
    # A) ID direct match (e.g. 191, 1440, 192, 1441, 3080)
    if (isinstance(col3_val, int) and col3_val < 100000) or (val_str.isdigit() and len(val_str) <= 5):
        art_id_target = int(val_str)
        if art_id_target in art_by_id:
            art = art_by_id[art_id_target]

    # B) Special disambiguation for Row 18 vs 27:
    if not art and r == 18:
        # HELADO DE CREMA DE MANTECADO -> ID 1309 (MANTECADO HELADO DE CREMA)
        art = art_by_id.get(1309)
    elif not art and r == 27:
        # HELADO DE CREMA DE GOFRE -> ID 658 (HELADO DE CREMA DE GOFRE 2.5L)
        art = art_by_id.get(658)
    elif not art and r == 31:
        # HELADO DE CREMA DE PAN ACEITE Y CHOCOLATE -> ID 1315
        art = art_by_id.get(1315)
    elif not art and r == 32:
        # HELADO DE FRUTOS DEL BOSQUE -> ID 1084 (EAN 8436623400125)
        art = art_by_id.get(1084)

    # C) EAN match
    if not art and val_str:
        clean = val_str.lstrip('0')
        if val_str in art_by_ean:
            art = art_by_ean[val_str]
        elif clean in art_by_ean:
            art = art_by_ean[clean]

    if art:
        art_id = art['id']
        matches.append({
            'row': r, 'num': num, 'nombre_excel': nombre,
            'col3': val_str, 'art_id': art_id, 'art_desc': art['descripcion']
        })
        # Color row green
        for col in range(1, ws.max_column + 1):
            ws.cell(row=r, column=col).fill = matched_fill

        ing_escaped = ingredientes.replace("'", "''")
        sql_updates_articulos.append(
            f"UPDATE ge_articulos SET ingredientes = '{ing_escaped}' WHERE id = {art_id}; -- {art['descripcion']} (Fila {r}, ID {art_id})"
        )

        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, 'col3': val_str
        })
        for col in range(1, ws.max_column + 1):
            ws.cell(row=r, column=col).fill = unmatched_fill

wb.save(wb_path)

print(f"Total filas procesadas: {ws.max_row - 1}")
print(f"Total coincidencias resueltas: {len(matches)}")
print(f"Total sin coincidencia: {len(unmatched)}")
for u in unmatched:
    print(f"   -> Sin resolver: Fila {u['row']} [#{u['num']}]: {u['nombre_excel']} (Col3={u['col3']})")

# Write proposal SQL
with open(r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\scratch\propuesta_actualizacion_articulos_v2.sql', 'w', encoding='utf-8') as f:
    f.write("-- =============================================================================\n")
    f.write("-- PROPUESTA DE ACTUALIZACIÓN DE ARTÍCULOS Y FICHAS TÉCNICAS (ACTUALIZACIÓN V2)\n")
    f.write(f"-- Coincidencias resueltas (EAN + ID manual): {len(matches)}\n")
    f.write(f"-- No resueltos: {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()