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

output_dir = r"c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\documentacion raul asencio\recetas"
os.makedirs(output_dir, exist_ok=True)
xlsx_path = os.path.join(output_dir, "Plantilla_Ficha_Receta_Rellenar.xlsx")

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Ficha Receta"

# Configurar vista de página A4 vertical
ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 1
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.sheet_view.showGridLines = True

# Paleta de colores corporativos
COLOR_PRIMARY_DARK = "3E2723"    # Marrón chocolate oscuro obrador
COLOR_ACCENT = "8D6E63"          # Café suave
COLOR_HEADER_BG = "4E342E"       # Encabezado tabla
COLOR_ZEBRA = "F7F5F3"           # Fondo alterno filas
COLOR_LIGHT_BG = "EFEBE9"        # Fondo suave datos generales
COLOR_BORDER = "CCCCCC"
COLOR_DARK_BORDER = "5D4037"

font_title = Font(name="Calibri", size=18, bold=True, color="FFFFFF")
font_subtitle = Font(name="Calibri", size=10, italic=True, color="D7CCC8")
font_label = Font(name="Calibri", size=11, bold=True, color="3E2723")
font_value = Font(name="Calibri", size=12, bold=False, color="000000")
font_check = Font(name="Calibri", size=12, bold=True, color="3E2723")
font_th = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
font_td = Font(name="Calibri", size=11, color="000000")
font_td_center = Font(name="Calibri", size=11, color="000000")
font_notes = Font(name="Calibri", size=9, italic=True, color="757575")

fill_header_banner = PatternFill(start_color=COLOR_PRIMARY_DARK, end_color=COLOR_PRIMARY_DARK, fill_type="solid")
fill_meta = PatternFill(start_color=COLOR_LIGHT_BG, end_color=COLOR_LIGHT_BG, fill_type="solid")
fill_table_head = PatternFill(start_color=COLOR_HEADER_BG, end_color=COLOR_HEADER_BG, fill_type="solid")
fill_zebra = PatternFill(start_color=COLOR_ZEBRA, end_color=COLOR_ZEBRA, fill_type="solid")

thin_side = Side(border_style="thin", color=COLOR_BORDER)
thick_bottom = Side(border_style="medium", color=COLOR_DARK_BORDER)
border_cell = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
border_meta = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)

# Anchos de columna
ws.column_dimensions['A'].width = 6    # Nº
ws.column_dimensions['B'].width = 38   # Ingrediente / Materia Prima
ws.column_dimensions['C'].width = 24   # Cantidad (g / ml)
ws.column_dimensions['D'].width = 18   # Unidad (g / ml)
ws.column_dimensions['E'].width = 26   # Observaciones / Lote

# Fila 1: Título superior
ws.row_dimensions[1].height = 40
ws.merge_cells("A1:E1")
cell_title = ws["A1"]
cell_title.value = "RAÚL ASENCIO PASTELERÍAS — FICHA DE RECETA"
cell_title.font = font_title
cell_title.alignment = Alignment(horizontal="center", vertical="center")
cell_title.fill = fill_header_banner

# Fila 2: Subtítulo
ws.row_dimensions[2].height = 20
ws.merge_cells("A2:E2")
cell_sub = ws["A2"]
cell_sub.value = "Documento de Control y Formulación de Obrador"
cell_sub.font = font_subtitle
cell_sub.alignment = Alignment(horizontal="center", vertical="center")
cell_sub.fill = fill_header_banner

# Fila 3: Espacio
ws.row_dimensions[3].height = 8

# Fila 4: Nombre de la Receta y Cuadro Preelaborado
ws.row_dimensions[4].height = 30
ws["A4"].value = "RECETA:"
ws["A4"].font = font_label
ws["A4"].alignment = Alignment(horizontal="left", vertical="center")

ws.merge_cells("B4:C4")
cell_receta = ws["B4"]
cell_receta.value = ""  # Espacio para rellenar a mano o escribir
cell_receta.font = Font(name="Calibri", size=12, bold=True)
cell_receta.alignment = Alignment(horizontal="left", vertical="center")
cell_receta.border = Border(bottom=Side(border_style="medium", color=COLOR_DARK_BORDER))

ws.merge_cells("D4:E4")
cell_preelab = ws["D4"]
cell_preelab.value = "¿ES PREELABORADO?     [   ] SÍ      [   ] NO"
cell_preelab.font = font_check
cell_preelab.alignment = Alignment(horizontal="center", vertical="center")
cell_preelab.fill = fill_meta
for col in ["D", "E"]:
    ws[f"{col}4"].border = border_meta

# Fila 5: Datos complementarios de cabecera (Fecha, Responsable, Peso total teórico)
ws.row_dimensions[5].height = 26
ws["A5"].value = "FECHA:"
ws["A5"].font = Font(name="Calibri", size=10, bold=True, color="5D4037")
ws["A5"].alignment = Alignment(horizontal="left", vertical="center")

ws["B5"].value = "____ / ____ / 202___"
ws["B5"].font = font_value
ws["B5"].alignment = Alignment(horizontal="left", vertical="center")

ws["C5"].value = "RESPONSABLE:"
ws["C5"].font = Font(name="Calibri", size=10, bold=True, color="5D4037")
ws["C5"].alignment = Alignment(horizontal="right", vertical="center")

ws.merge_cells("D5:E5")
cell_resp = ws["D5"]
cell_resp.value = ""
cell_resp.border = Border(bottom=Side(border_style="thin", color=COLOR_DARK_BORDER))

# Fila 6: Espacio antes de tabla
ws.row_dimensions[6].height = 10

# Fila 7: Cabecera de la tabla
ws.row_dimensions[7].height = 28
headers = [
    ("A7", "Nº", Alignment(horizontal="center", vertical="center")),
    ("B7", "INGREDIENTE / MATERIA PRIMA", Alignment(horizontal="left", vertical="center")),
    ("C7", "CANTIDAD", Alignment(horizontal="center", vertical="center")),
    ("D7", "UNIDAD (g / ml)", Alignment(horizontal="center", vertical="center")),
    ("E7", "OBSERVACIONES / NOTAS", Alignment(horizontal="left", vertical="center"))
]

for pos, text, align in headers:
    c = ws[pos]
    c.value = text
    c.font = font_th
    c.fill = fill_table_head
    c.alignment = align
    c.border = border_cell

# Filas 8 a 32: 25 líneas para rellenar ingredientes
start_row = 8
num_rows = 24
for i in range(num_rows):
    r = start_row + i
    ws.row_dimensions[r].height = 24
    is_zebra = (i % 2 == 1)
    row_fill = fill_zebra if is_zebra else None
    
    # Nº
    c_num = ws.cell(row=r, column=1, value=i + 1)
    c_num.font = font_td_center
    c_num.alignment = Alignment(horizontal="center", vertical="center")
    c_num.border = border_cell
    if row_fill: c_num.fill = row_fill
    
    # Ingrediente
    c_ing = ws.cell(row=r, column=2, value="")
    c_ing.font = font_td
    c_ing.alignment = Alignment(horizontal="left", vertical="center")
    c_ing.border = border_cell
    if row_fill: c_ing.fill = row_fill
    
    # Cantidad
    c_cant = ws.cell(row=r, column=3, value="")
    c_cant.font = font_td
    c_cant.alignment = Alignment(horizontal="right", vertical="center")
    c_cant.border = border_cell
    if row_fill: c_cant.fill = row_fill
    
    # Unidad
    c_uni = ws.cell(row=r, column=4, value="")
    c_uni.font = font_td_center
    c_uni.alignment = Alignment(horizontal="center", vertical="center")
    c_uni.border = border_cell
    if row_fill: c_uni.fill = row_fill
    
    # Observaciones
    c_obs = ws.cell(row=r, column=5, value="")
    c_obs.font = font_td
    c_obs.alignment = Alignment(horizontal="left", vertical="center")
    c_obs.border = border_cell
    if row_fill: c_obs.fill = row_fill

# Fila total
total_row = start_row + num_rows
ws.row_dimensions[total_row].height = 26
ws.merge_cells(f"A{total_row}:B{total_row}")
cell_tot_lbl = ws[f"A{total_row}"]
cell_tot_lbl.value = "PESO TOTAL FORMULACIÓN (g / ml):"
cell_tot_lbl.font = Font(name="Calibri", size=11, bold=True, color="3E2723")
cell_tot_lbl.alignment = Alignment(horizontal="right", vertical="center")
cell_tot_lbl.fill = fill_meta
ws[f"B{total_row}"].border = border_cell
ws[f"A{total_row}"].border = border_cell

cell_tot_val = ws[f"C{total_row}"]
cell_tot_val.value = f"=SUM(C{start_row}:C{total_row-1})"
cell_tot_val.font = Font(name="Calibri", size=12, bold=True, color="000000")
cell_tot_val.alignment = Alignment(horizontal="right", vertical="center")
cell_tot_val.border = border_cell
cell_tot_val.fill = fill_meta

ws.merge_cells(f"D{total_row}:E{total_row}")
ws[f"D{total_row}"].border = border_cell
ws[f"E{total_row}"].border = border_cell
ws[f"D{total_row}"].fill = fill_meta

# Pie de página / notas
note_row = total_row + 2
ws.merge_cells(f"A{note_row}:E{note_row}")
cell_note = ws[f"A{note_row}"]
cell_note.value = "Notas / Instrucciones de elaboración o alérgenos relevantes:"
cell_note.font = Font(name="Calibri", size=10, bold=True, color="5D4037")

note_box = note_row + 1
ws.row_dimensions[note_box].height = 45
ws.merge_cells(f"A{note_box}:E{note_box}")
cell_box = ws[f"A{note_box}"]
cell_box.border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)

wb.save(xlsx_path)
print(f"Excel guardado exitosamente en: {xlsx_path}")

