import os, sys, glob, json
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# Load parsed recipes
with open('parsed_recipes.json', encoding='utf-8') as f:
    recipes = json.load(f)

print(f"Generating consolidated Excel for {len(recipes)} recipes...")

# Keywords to determine if a recipe is "PREELABORADO" (bases, cremas, rellenos, pralinés, masas madre, almíbares, trufas, coberturas...)
PREELABORADO_KEYWORDS = [
    'crema', 'yema', 'trufa', 'nata', 'gelatina', 'almíbar', 'almibar', 'glasa', 'glaseado',
    'praliné', 'praline', 'variegato', 'cobertura', 'masa madre', 'fondant', 'caramelo',
    'pasta de almendra', 'pasta de avellana', 'gianduja', 'preparado'
]

def is_preelaborado(recipe):
    name = recipe['name'].lower()
    fam = recipe['family'].lower()
    if 'cremas' in fam or 'nata' in fam or 'trufas' in fam or 'rellenos' in fam:
        return True
    for kw in PREELABORADO_KEYWORDS:
        if kw in name:
            return True
    return False

wb = openpyxl.Workbook()
# Remove default sheet later or use it as Index
ws_index = wb.active
ws_index.title = "ÍNDICE DE RECETAS"

# Styles
FONT_TITLE = Font(name="Segoe UI", size=14, bold=True, color="FFFFFF")
FONT_SUBTITLE = Font(name="Segoe UI", size=9, italic=True, color="ECEFF1")
FONT_HEADER = Font(name="Segoe UI", size=10, bold=True, color="FFFFFF")
FONT_BOLD = Font(name="Segoe UI", size=10, bold=True, color="1E293B")
FONT_REGULAR = Font(name="Segoe UI", size=10, color="1E293B")
FONT_SMALL = Font(name="Segoe UI", size=8, italic=True, color="64748B")

FILL_NAVY = PatternFill(start_color="1A365D", end_color="1A365D", fill_type="solid")
FILL_SLATE = PatternFill(start_color="2D3748", end_color="2D3748", fill_type="solid")
FILL_BLUE_HEADER = PatternFill(start_color="2B6CB0", end_color="2B6CB0", fill_type="solid")
FILL_ACCENT = PatternFill(start_color="EBF8FF", end_color="EBF8FF", fill_type="solid")
FILL_ZEBRA = PatternFill(start_color="F8FAFC", end_color="F8FAFC", fill_type="solid")
FILL_WHITE = PatternFill(start_color="FFFFFF", end_color="FFFFFF", fill_type="solid")
FILL_PREELAB = PatternFill(start_color="FEFCBF", end_color="FEFCBF", fill_type="solid") # light gold/yellow

BORDER_THIN = Border(
    left=Side(style='thin', color="CBD5E1"),
    right=Side(style='thin', color="CBD5E1"),
    top=Side(style='thin', color="CBD5E1"),
    bottom=Side(style='thin', color="CBD5E1")
)

ALIGN_LEFT = Alignment(horizontal="left", vertical="center")
ALIGN_CENTER = Alignment(horizontal="center", vertical="center")
ALIGN_RIGHT = Alignment(horizontal="right", vertical="center")

# Format index sheet
ws_index.views.sheetView[0].showGridLines = True
ws_index.merge_cells("A1:F2")
c = ws_index["A1"]
c.value = "RECETARIO GENERAL · RAÚL ASENCIO PASTELERÍAS"
c.font = Font(name="Segoe UI", size=16, bold=True, color="FFFFFF")
c.fill = FILL_NAVY
c.alignment = ALIGN_CENTER

headers_index = ["Nº", "NOMBRE DE LA RECETA", "¿PREELABORADO?", "FAMILIA / CATEGORÍA", "TOTAL INGR.", "HOJA"]
for col_idx, h in enumerate(headers_index, 1):
    cell = ws_index.cell(row=4, column=col_idx, value=h)
    cell.font = FONT_HEADER
    cell.fill = FILL_SLATE
    cell.alignment = ALIGN_CENTER
    cell.border = BORDER_THIN

ws_index.column_dimensions["A"].width = 8
ws_index.column_dimensions["B"].width = 38
ws_index.column_dimensions["C"].width = 18
ws_index.column_dimensions["D"].width = 28
ws_index.column_dimensions["E"].width = 14
ws_index.column_dimensions["F"].width = 20

sheet_names_created = set()

for idx, r in enumerate(recipes, 1):
    preelab = is_preelaborado(r)
    r['es_preelaborado'] = preelab
    
    # Safe sheet name (max 31 chars, no special chars)
    safe_name = f"{r['num']}_{r['name']}"
    for ch in [':', '\\', '/', '?', '*', '[', ']']:
        safe_name = safe_name.replace(ch, '_')
    safe_name = safe_name[:30].strip()
    if safe_name in sheet_names_created:
        safe_name = f"{safe_name[:26]}_{idx}"
    sheet_names_created.add(safe_name)
    
    # Add to index
    row_idx = 4 + idx
    fill_row = FILL_ZEBRA if idx % 2 == 0 else FILL_WHITE
    
    c_num = ws_index.cell(row=row_idx, column=1, value=int(r['num']) if r['num'].isdigit() else r['num'])
    c_num.font = FONT_BOLD
    c_num.alignment = ALIGN_CENTER
    c_num.border = BORDER_THIN
    c_num.fill = fill_row
    
    c_name = ws_index.cell(row=row_idx, column=2, value=r['name'])
    c_name.font = FONT_REGULAR
    c_name.alignment = ALIGN_LEFT
    c_name.border = BORDER_THIN
    c_name.fill = fill_row
    
    c_pre = ws_index.cell(row=row_idx, column=3, value="SÍ" if preelab else "NO")
    c_pre.font = FONT_BOLD if preelab else FONT_REGULAR
    c_pre.alignment = ALIGN_CENTER
    c_pre.border = BORDER_THIN
    c_pre.fill = FILL_PREELAB if preelab else fill_row
    
    c_fam = ws_index.cell(row=row_idx, column=4, value=r['family'])
    c_fam.font = FONT_REGULAR
    c_fam.alignment = ALIGN_LEFT
    c_fam.border = BORDER_THIN
    c_fam.fill = fill_row
    
    c_tot = ws_index.cell(row=row_idx, column=5, value=len(r['ingredients']))
    c_tot.font = FONT_REGULAR
    c_tot.alignment = ALIGN_CENTER
    c_tot.border = BORDER_THIN
    c_tot.fill = fill_row
    
    c_link = ws_index.cell(row=row_idx, column=6, value=f"Ir a Ficha")
    c_link.hyperlink = f"#'{safe_name}'!A1"
    c_link.font = Font(name="Segoe UI", size=10, color="2563EB", underline="single")
    c_link.alignment = ALIGN_CENTER
    c_link.border = BORDER_THIN
    c_link.fill = fill_row
    
    # ---------------- Create Recipe Sheet ----------------
    ws = wb.create_sheet(title=safe_name)
    ws.views.sheetView[0].showGridLines = True
    
    # Set column widths
    ws.column_dimensions["A"].width = 8
    ws.column_dimensions["B"].width = 38
    ws.column_dimensions["C"].width = 20
    ws.column_dimensions["D"].width = 16
    ws.column_dimensions["E"].width = 30
    
    # Title Block
    ws.merge_cells("A1:E1")
    t1 = ws["A1"]
    t1.value = "RAÚL ASENCIO PASTELERÍAS · FICHA TÉCNICA DE RECETA / FORMULACIÓN"
    t1.font = FONT_TITLE
    t1.fill = FILL_NAVY
    t1.alignment = ALIGN_CENTER
    ws.row_dimensions[1].height = 28
    
    # Back to index link
    ws.merge_cells("A2:E2")
    t2 = ws["A2"]
    t2.value = f"Categoría: {r['family']}  |  Código: REC-{r['num'].zfill(3)}  |  Fuente manuscrito: {r['source']}"
    t2.font = FONT_SUBTITLE
    t2.fill = FILL_SLATE
    t2.alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[2].height = 18
    
    # Row 4: RECETA | ¿ES PREELABORADO?
    ws.merge_cells("A4:C4")
    c_rec = ws["A4"]
    c_rec.value = f"RECETA:  {r['name'].upper()}"
    c_rec.font = Font(name="Segoe UI", size=12, bold=True, color="0F172A")
    c_rec.fill = FILL_ACCENT
    c_rec.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    
    ws.merge_cells("D4:E4")
    c_chk = ws["D4"]
    si_mark = "[ X ]" if preelab else "[   ]"
    no_mark = "[ X ]" if not preelab else "[   ]"
    c_chk.value = f"¿ES PREELABORADO?   {si_mark} SÍ     {no_mark} NO"
    c_chk.font = Font(name="Segoe UI", size=11, bold=True, color="0F172A")
    c_chk.fill = FILL_PREELAB if preelab else FILL_ACCENT
    c_chk.alignment = ALIGN_CENTER
    
    for row in range(4, 5):
        for col in range(1, 6):
            ws.cell(row=row, column=col).border = BORDER_THIN
    ws.row_dimensions[4].height = 26
    
    # Row 5: Secondary metadata
    ws.merge_cells("A5:C5")
    ws["A5"].value = f"FECHA REVISIÓN: 05/11/2026       RESPONSABLE: Obrador Raúl Asencio"
    ws["A5"].font = FONT_REGULAR
    ws["A5"].alignment = Alignment(horizontal="left", vertical="center", indent=1)
    
    ws.merge_cells("D5:E5")
    ws["D5"].value = f"VERSIÓN: 1.0       REFERENCIA IMAGEN: {r['source']}"
    ws["D5"].font = FONT_REGULAR
    ws["D5"].alignment = ALIGN_CENTER
    
    for row in range(5, 6):
        for col in range(1, 6):
            ws.cell(row=row, column=col).border = BORDER_THIN
    ws.row_dimensions[5].height = 20
    
    # Table Header Row 7
    ws.row_dimensions[7].height = 22
    headers = ["Nº", "INGREDIENTE / MATERIA PRIMA", "CANTIDAD (g o ml)", "UNIDAD", "OBSERVACIONES / DETALLES"]
    for col_idx, h in enumerate(headers, 1):
        cell = ws.cell(row=7, column=col_idx, value=h)
        cell.font = FONT_HEADER
        cell.fill = FILL_BLUE_HEADER
        cell.alignment = ALIGN_CENTER
        cell.border = BORDER_THIN
        
    # Table Ingredients Rows
    curr_row = 8
    for i, ing in enumerate(r['ingredients'], 1):
        ws.row_dimensions[curr_row].height = 20
        fill_curr = FILL_ZEBRA if i % 2 == 0 else FILL_WHITE
        
        # Nº
        c_i = ws.cell(row=curr_row, column=1, value=i)
        c_i.font = FONT_BOLD
        c_i.alignment = ALIGN_CENTER
        c_i.fill = fill_curr
        c_i.border = BORDER_THIN
        
        # Ingrediente
        c_ing = ws.cell(row=curr_row, column=2, value=ing['ingrediente'])
        c_ing.font = FONT_BOLD
        c_ing.alignment = Alignment(horizontal="left", vertical="center", indent=1)
        c_ing.fill = fill_curr
        c_ing.border = BORDER_THIN
        
        # Cantidad en gramos o ml
        # Normalize: if unit is kg or L, can show converted or keep original clearly
        cant_val = ing['cantidad']
        unid_val = ing['unidad']
        
        c_cant = ws.cell(row=curr_row, column=3, value=cant_val)
        c_cant.font = FONT_BOLD
        c_cant.alignment = ALIGN_CENTER
        c_cant.fill = fill_curr
        c_cant.border = BORDER_THIN
        
        c_unid = ws.cell(row=curr_row, column=4, value=unid_val if unid_val else "g / ml")
        c_unid.font = FONT_REGULAR
        c_unid.alignment = ALIGN_CENTER
        c_unid.fill = fill_curr
        c_unid.border = BORDER_THIN
        
        c_obs = ws.cell(row=curr_row, column=5, value=ing['obs'])
        c_obs.font = FONT_SMALL
        c_obs.alignment = ALIGN_LEFT
        c_obs.fill = fill_curr
        c_obs.border = BORDER_THIN
        
        curr_row += 1
        
    # Ensure minimum empty rows up to 15 if table is short
    while curr_row < 22:
        ws.row_dimensions[curr_row].height = 18
        fill_curr = FILL_ZEBRA if (curr_row % 2 == 0) else FILL_WHITE
        for col in range(1, 6):
            cell = ws.cell(row=curr_row, column=col)
            cell.border = BORDER_THIN
            cell.fill = fill_curr
            if col == 1:
                cell.value = curr_row - 7
                cell.alignment = ALIGN_CENTER
                cell.font = FONT_SMALL
        curr_row += 1
        
    # Process / Notes Section
    curr_row += 1
    ws.merge_cells(start_row=curr_row, start_column=1, end_row=curr_row, end_column=5)
    sec_hdr = ws.cell(row=curr_row, column=1, value="MÉTODO DE ELABORACIÓN / PROCESO Y PARÁMETROS:")
    sec_hdr.font = FONT_HEADER
    sec_hdr.fill = FILL_SLATE
    sec_hdr.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[curr_row].height = 20
    curr_row += 1
    
    # Process text
    process_lines = []
    if r['proceso']:
        process_lines.append(r['proceso'].strip())
    if r['notas']:
        process_lines.extend(r['notas'])
    if not process_lines:
        process_lines.append("No se detallan instrucciones específicas en el manuscrito original.")
        
    full_process_txt = "\n".join(process_lines)
    
    ws.merge_cells(start_row=curr_row, start_column=1, end_row=curr_row+4, end_column=5)
    proc_cell = ws.cell(row=curr_row, column=1, value=full_process_txt)
    proc_cell.font = FONT_REGULAR
    proc_cell.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True, indent=1)
    for r_idx in range(curr_row, curr_row+5):
        for c_idx in range(1, 6):
            ws.cell(row=r_idx, column=c_idx).border = BORDER_THIN
            ws.cell(row=r_idx, column=c_idx).fill = FILL_WHITE
            
    # Bottom Link back to Index
    curr_row += 6
    b_link = ws.cell(row=curr_row, column=1, value="<< Volver al Índice General")
    b_link.hyperlink = "#'ÍNDICE DE RECETAS'!A1"
    b_link.font = Font(name="Segoe UI", size=10, bold=True, color="2563EB", underline="single")

excel_path = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\documentacion raul asencio\recetas\Recetario_Completo_Rellenado_Raul_Asencio.xlsx'
wb.save(excel_path)
print(f"Consolidated Excel saved successfully at: {excel_path}")

