import openpyxl
import pymysql

excel_path = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\documentacion raul asencio\Productos_Etiquetas_Nutricional_Alergenos.xlsx'
wb = openpyxl.load_workbook(excel_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
)

cur = conn.cursor()
cur.execute('SELECT id, ean, descripcion FROM ge_articulos')
articulos = cur.fetchall()

by_ean = {}
by_id = {}
for a in articulos:
    if a['ean'] and str(a['ean']).strip():
        ean_clean = str(a['ean']).strip()
        by_ean[ean_clean] = a
        by_ean[ean_clean.lstrip('0')] = a
    by_id[int(a['id'])] = a

by_ean['84366234000125'] = by_ean.get('8436623400125')

updates_articulos = []
updates_fichas = []

for r in range(2, ws.max_row + 1):
    num = ws.cell(r, 1).value
    nombre = ws.cell(r, 2).value
    code_val = ws.cell(r, 3).value
    code_str = str(code_val).strip() if code_val is not None else ''
    
    ingredientes = ws.cell(r, 4).value or ''
    kj = ws.cell(r, 6).value
    kcal = ws.cell(r, 7).value
    grasas = ws.cell(r, 8).value
    sat = ws.cell(r, 9).value
    hidratos = ws.cell(r, 10).value
    azucares = ws.cell(r, 11).value
    fibra = ws.cell(r, 12).value
    proteinas = ws.cell(r, 13).value
    sal = ws.cell(r, 14).value
    
    art = None
    if code_str:
        if code_str in by_ean:
            art = by_ean[code_str]
        elif code_str.lstrip('0') in by_ean:
            art = by_ean[code_str.lstrip('0')]
        elif code_str.isdigit() and int(code_str) in by_id:
            art = by_id[int(code_str)]
            
    if art:
        art_id = art['id']
        updates_articulos.append((ingredientes, art_id))
        updates_fichas.append((
            art_id, ingredientes, kj, kcal, grasas, sat, hidratos, azucares, fibra, proteinas, sal
        ))

print(f'Total matched items: {len(updates_articulos)}')

# Preparar y ejecutar en transacción
try:
    conn.begin()
    
    # 1. Update ge_articulos
    sql_art = "UPDATE ge_articulos SET ingredientes = %s WHERE id = %s"
    for item in updates_articulos:
        cur.execute(sql_art, item)
    
    # 2. Update or Insert ge_articulos_fichas_tecnicas
    cur.execute("SELECT id_articulo FROM ge_articulos_fichas_tecnicas")
    existing_fichas = {r['id_articulo'] for r in cur.fetchall()}
    
    sql_upd_ficha = """
        UPDATE ge_articulos_fichas_tecnicas
        SET ingredientes = %s,
            nutricional_energia_kj = %s,
            nutricional_energia_kcal = %s,
            nutricional_grasas = %s,
            nutricional_grasas_saturadas = %s,
            nutricional_hidratos = %s,
            nutricional_azucares = %s,
            nutricional_fibra = %s,
            nutricional_proteinas = %s,
            nutricional_sal = %s
        WHERE id_articulo = %s
    """
    
    sql_ins_ficha = """
        INSERT INTO ge_articulos_fichas_tecnicas
        (id_articulo, ingredientes, nutricional_energia_kj, nutricional_energia_kcal,
         nutricional_grasas, nutricional_grasas_saturadas, nutricional_hidratos,
         nutricional_azucares, nutricional_fibra, nutricional_proteinas, nutricional_sal)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    
    c_upd = 0
    c_ins = 0
    for f in updates_fichas:
        art_id = f[0]
        params = (f[1], f[2], f[3], f[4], f[5], f[6], f[7], f[8], f[9], f[10], art_id)
        if art_id in existing_fichas:
            cur.execute(sql_upd_ficha, params)
            c_upd += 1
        else:
            ins_params = (art_id, f[1], f[2], f[3], f[4], f[5], f[6], f[7], f[8], f[9], f[10])
            cur.execute(sql_ins_ficha, ins_params)
            c_ins += 1
            
    conn.commit()
    print("COMMITTED SUCCESSFULLY:")
    print(f" - ge_articulos updated: {len(updates_articulos)}")
    print(f" - ge_articulos_fichas_tecnicas updated: {c_upd}")
    print(f" - ge_articulos_fichas_tecnicas inserted: {c_ins}")
except Exception as e:
    conn.rollback()
    print("ERROR:", e)
finally:
    conn.close()