import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, LineChart, Reference

# 1. Crear el libro de trabajo
wb = openpyxl.Workbook()

# Definición de estilos formales (Saborízate)
font_title = Font(name="Calibri", size=14, bold=True, color="FFFFFF")
font_header = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
font_bold = Font(name="Calibri", size=11, bold=True)
font_regular = Font(name="Calibri", size=11)
font_alert = Font(name="Calibri", size=11, bold=True, color="9C0006")

fill_title = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid") # Azul Marino
fill_header = PatternFill(start_color="366092", end_color="366092", fill_type="solid") # Azul Secundario
fill_accent = PatternFill(start_color="DCE6F1", end_color="DCE6F1", fill_type="solid") # Azul Claro Totales
fill_alert = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") # Alerta Rojo

thin_border = Border(
    left=Side(style='thin', color='BFBFBF'),
    right=Side(style='thin', color='BFBFBF'),
    top=Side(style='thin', color='BFBFBF'),
    bottom=Side(style='thin', color='BFBFBF')
)

# ==========================================
# PESTAÑA 1: INVENTARIO
# ==========================================
ws1 = wb.active
ws1.title = "Inventario Materia Prima"
ws1.views.sheetView[0].showGridLines = True

ws1.merge_cells("A1:H1")
ws1["A1"] = "CONTROL DE INVENTARIO - SABORÍZATE"
ws1["A1"].font = font_title
ws1["A1"].fill = fill_title
ws1["A1"].alignment = Alignment(horizontal="center")

headers_inv = ["ID Insumo", "Descripción", "Unidad", "Stock Mínimo", "Stock Actual", "Costo Unitario ($)", "Valor Total ($)", "Estado"]
ws1.append([]) # Fila vacía
ws1.append(headers_inv)

for col_num, header in enumerate(headers_inv, 1):
    cell = ws1.cell(row=3, column=col_num)
    cell.font = font_header
    cell.fill = fill_header
    cell.alignment = Alignment(horizontal="center")

# Datos iniciales base del negocio
insumos_base = [
    ["INS-01", "Harina de Trigo Repostera Mocasa", "Saco 45kg", 2, 5, 32.00],
    ["INS-02", "Azúcar Refinada", "Kg", 10, 25, 1.20],
    ["INS-03", "Cacao en Polvo", "Kg", 5, 8, 6.50],
    ["INS-04", "Mantequilla / Materia Grasa", "Kg", 10, 15, 3.80],
    ["INS-05", "Huevos", "Cartón", 4, 10, 4.50],
    ["INS-06", "Envases de Plástico (Porción Individual)", "Unidades", 200, 500, 0.15],
    ["INS-07", "Polvo de Hornear", "Kg", 2, 1, 4.00]
]

for row_idx, data in enumerate(insumos_base, 4):
    ws1.append([data[0], data[1], data[2], data[3], data[4], data[5], f"=E{row_idx}*F{row_idx}", f'=SI(E{row_idx}<=D{row_idx};"REORDENAR";"OK")'])

# Formatear celdas de Inventario
for row in ws1.iter_rows(min_row=4, max_row=10, min_col=1, max_col=8):
    for cell in row:
        cell.border = thin_border
        if cell.column in [4, 5]:
            cell.alignment = Alignment(horizontal="right")
        elif cell.column == 6 or cell.column == 7:
            cell.number_format = '$#,##0.00'
        elif cell.column == 8:
            cell.alignment = Alignment(horizontal="center")

# ==========================================
# PESTAÑA 2: CONTABILIDAD DIARIA
# ==========================================
ws2 = wb.create_sheet(title="Contabilidad Diaria")
ws2.views.sheetView[0].showGridLines = True

ws2.merge_cells("A1:F1")
ws2["A1"] = "REGISTRO DIARIO DE INGRESOS Y EGRESOS"
ws2["A1"].font = font_title
ws2["A1"].fill = fill_title
ws2["A1"].alignment = Alignment(horizontal="center")

headers_cont = ["Fecha", "Categoría", "Tipo", "Descripción", "Método de Pago", "Monto ($)"]
ws2.append([])
ws2.append(headers_cont)

for col_num, header in enumerate(headers_cont, 1):
    cell = ws2.cell(row=3, column=col_num)
    cell.font = font_header
    cell.fill = fill_header
    cell.alignment = Alignment(horizontal="center")

# Datos demo de ejemplo para arrancar (Junio 2026)
historico_demo = [
    ["2026-06-01", "Venta Tortas", "Ingreso", "Venta de 55 porciones marmoleadas", "Pago Móvil", 220.00],
    ["2026-06-01", "Pago Personal", "Egreso", "Pago semanal ayudantes", "Divisas", 180.00],
    ["2026-06-02", "Venta Tortas", "Ingreso", "Venta de 60 porciones marmoleadas", "Efectivo", 240.00],
    ["2026-06-02", "Compra Materia Prima", "Egreso", "Compra Saco Harina Mocasa", "Pago Móvil", 32.00],
    ["2026-06-03", "Venta Tortas", "Ingreso", "Venta de 50 porciones Tres Leches", "Pago Móvil", 200.00],
]

for data in historico_demo:
    ws2.append(data)

for row in ws2.iter_rows(min_row=4, max_row=200, min_col=1, max_col=6):
    for cell in row:
        if cell.row <= 8:  # Solo el demo inicial lleva bordes al crear
            cell.border = thin_border
        if cell.column == 6:
            cell.number_format = '$#,##0.00'

# ==========================================
# PESTAÑA 3: RESUMEN FINANCIERO MENSUAL
# ==========================================
ws3 = wb.create_sheet(title="Resumen Mensual")
ws3.views.sheetView[0].showGridLines = True

ws3.merge_cells("A1:E1")
ws3["A1"] = "CONSOLIDADO FINANCIERO MENSUAL (2026)"
ws3["A1"].font = font_title
ws3["A1"].fill = fill_title
ws3["A1"].alignment = Alignment(horizontal="center")

headers_res = ["Mes", "Total Ingresos ($)", "Total Egresos ($)", "Ganancia Neta ($)", "Meta Alcanzada %"]
ws3.append([])
ws3.append(headers_res)

for col_num, header in enumerate(headers_res, 1):
    cell = ws3.cell(row=3, column=col_num)
    cell.font = font_header
    cell.fill = fill_header
    cell.alignment = Alignment(horizontal="center")

meses = ["Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre"]

# Valores demo fijos para los meses anteriores para que los gráficos tengan vida
ingresos_fijos = [4500, 4700, 4900, 5100, 5300]
egresos_fijos = [1200, 1300, 1150, 1400, 1350]

for i, mes in enumerate(meses, 4):
    if i-4 < len(ingresos_fijos):
        # Meses pasados (Enero - Mayo) con valores hardcoded para simulación de histórico
        ws3.append([mes, ingresos_fijos[i-4], egresos_fijos[i-4], f"=B{i}-C{i}", f"=D{i}/4800"])
    elif mes == "Junio":
        # JUNIO se calcula AUTOMÁTICAMENTE sumando dinámicamente de la pestaña Contabilidad Diaria
        ws3.append([
            mes, 
            '=SUMAR.SI.CONJUNTO(\'Contabilidad Diaria\'!F:F; \'Contabilidad Diaria\'!C:C; "Ingreso")', 
            '=SUMAR.SI.CONJUNTO(\'Contabilidad Diaria\'!F:F; \'Contabilidad Diaria\'!C:C; "Egreso")', 
            f"=B{i}-C{i}", 
            f"=D{i}/4800"
        ])
    else:
        # Meses futuros listos con fórmulas vacías esperando datos
        ws3.append([mes, 0, 0, f"=B{i}-C{i}", f"=D{i}/4800"])

# Fila de Totales Anuales
total_row = 16
ws3.cell(row=total_row, column=1, value="TOTAL ANUAL").font = font_bold
ws3.cell(row=total_row, column=1).fill = fill_accent
ws3.cell(row=total_row, column=1).border = thin_border

for col in range(2, 5):
    col_letter = get_column_letter(col)
    cell = ws3.cell(row=total_row, column=col, value=f"=SUMA({col_letter}4:{col_letter}15)")
    cell.font = font_bold
    cell.fill = fill_accent
    cell.border = thin_border
    cell.number_format = '$#,##0.00'

ws3.cell(row=total_row, column=5, value="=PROMEDIO(E4:E15)").font = font_bold
ws3.cell(row=total_row, column=5).fill = fill_accent
ws3.cell(row=total_row, column=5).border = thin_border
ws3.cell(row=total_row, column=5).number_format = '0.0%'

# Formatear tabla mensual
for row in ws3.iter_rows(min_row=4, max_row=15, min_col=1, max_col=5):
    for cell in row:
        cell.border = thin_border
        if cell.column in [2, 3, 4]:
            cell.number_format = '$#,##0.00'
        elif cell.column == 5:
            cell.number_format = '0.0%'

# ==========================================
# PESTAÑA 4: GRÁFICOS DE CONTROL
# ==========================================
ws4 = wb.create_sheet(title="Gráficos de Control")
ws4.views.sheetView[0].showGridLines = True

# Gráfico Mensual Histórico (Líneas)
chart_line = LineChart()
chart_line.title = "Evolución Financiera Mensual (Ingresos vs Egresos)"
chart_line.style = 13
chart_line.y_axis.title = "Monto ($)"
chart_line.x_axis.title = "Meses"
chart_line.width = 18
chart_line.height = 10

data_line = Reference(ws3, min_col=2, min_row=3, max_col=3, max_row=15)
cats_line = Reference(ws3, min_col=1, min_row=4, max_row=15)
chart_line.add_data(data_line, titles_from_data=True)
chart_line.set_categories(cats_line)
ws4.add_chart(chart_line, "B2")

# Gráfico de Cumplimiento de Metas (Barras)
chart_bar = BarChart()
chart_bar.type = "col"
chart_bar.style = 10
chart_bar.title = "Rentabilidad Neta por Mes vs Meta Base ($4,800)"
chart_bar.y_axis.title = "Ganancia Real ($)"
chart_bar.x_axis.title = "Meses"
chart_bar.width = 18
chart_bar.height = 10

data_bar = Reference(ws3, min_col=4, min_row=3, max_col=4, max_row=15)
chart_bar.add_data(data_bar, titles_from_data=True)
chart_bar.set_categories(cats_line)
chart_bar.legend = None # No hace falta leyenda al ser una sola barra
ws4.add_chart(chart_bar, "B22")


# Autoajustar anchos de columnas en todas las hojas para legibilidad limpia
for sheet in wb.worksheets:
    for col in sheet.columns:
        max_len = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            if cell.value:
                # Evitar que las fórmulas largas distorsionen el ancho
                val_str = str(cell.value)
                if not val_str.startswith("="):
                    max_len = max(max_len, len(val_str))
        sheet.column_dimensions[col_letter].width = max(max_len + 3, 12)

# Guardar el archivo final
wb.save("Control_General_Saborizate.xlsx")
print("¡Archivo 'Control_General_Saborizate.xlsx' creado exitosamente!")print('Hello world!')