import pymysql
import openpyxl

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

with conn.cursor() as cur:
    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
        val = ws.cell(row=r, column=3).value
        fill = ws.cell(row=r, column=1).fill
        color = fill.start_color.rgb if fill and fill.start_color else ''
        
        art = None
        val_str = str(val).strip() if val is not None else ''
        
        # Check if numeric ID (<= 5 digits)
        if (isinstance(val, int) and val < 100000) or (val_str.isdigit() and len(val_str) <= 5):
            cur.execute('SELECT id, ean, descripcion FROM ge_articulos WHERE id = %s', (int(val_str),))
            art = cur.fetchone()
        
        # If not found or is EAN
        if not art and val_str:
            cur.execute('SELECT id, ean, descripcion FROM ge_articulos WHERE ean = %s OR ean = %s', (val_str, val_str.lstrip('0')))
            art = cur.fetchone()
            if not art:
                cur.execute('SELECT articulo_id FROM ge_articulos_ean WHERE ean = %s OR ean = %s', (val_str, val_str.lstrip('0')))
                ean_row = cur.fetchone()
                if ean_row:
                    cur.execute('SELECT id, ean, descripcion FROM ge_articulos WHERE id = %s', (ean_row['articulo_id'],))
                    art = cur.fetchone()
        
        if art:
            print(f"Row {r:2d} [#{num}] (Color={color}): Val={val_str} -> FOUND ArtID={art['id']} ({art['descripcion']})")
        else:
            print(f"Row {r:2d} [#{num}] (Color={color}): Val={val_str} | Nombre={nombre} -> NOT FOUND")

conn.close()