import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.styles import (Font, PatternFill, Alignment, Border, Side,
                              numbers as xlnumbers)
from openpyxl.utils import get_column_letter
import warnings
warnings.filterwarnings('ignore')

# ── Cores ────────────────────────────────────────────────────────────────────
C_AZUL_ESC  = "1F3864"   # cabeçalho principal
C_AZUL_MED  = "2F5496"   # grupo
C_AZUL_CLR  = "D6E4F7"   # linha de subtotal receita
C_VERDE_ESC = "1E5631"   # resultado positivo header
C_VERDE_CLR = "D9EAD3"   # resultado positivo
C_VERM_CLR  = "FCE4D6"   # resultado negativo
C_CINZA     = "D9D9D9"   # linha neutra
C_AMARELO   = "FFF2CC"   # destaque
C_BRANCO    = "FFFFFF"
C_PRETO     = "000000"

def fill(hex_color):
    return PatternFill("solid", fgColor=hex_color)

def font(bold=False, color=C_PRETO, size=10, italic=False):
    return Font(bold=bold, color=color, size=size, italic=italic,
                name="Calibri")

def border_thin():
    s = Side(style='thin', color="BFBFBF")
    return Border(left=s, right=s, top=s, bottom=s)

def border_medium_bottom():
    thin = Side(style='thin', color="BFBFBF")
    med  = Side(style='medium', color="000000")
    return Border(left=thin, right=thin, top=thin, bottom=med)

def align(h='left', v='center', wrap=False):
    return Alignment(horizontal=h, vertical=v, wrap_text=wrap)

FMT_BRL   = '#,##0.00'
FMT_PCT   = '0.0%'
FMT_BRL_0 = '#,##0'

# ── Carregar dados ────────────────────────────────────────────────────────────
df_raw = pd.read_excel(r'C:\Users\Claudio\AppData\Local\Temp\dre_copia.xlsx',
                       sheet_name='Planilha1')

# Colunas de data válidas (2021-2025)
date_cols = []
for c in df_raw.columns:
    if hasattr(c, 'year') and 2021 <= c.year <= 2025:
        date_cols.append(c)

anos = [2021, 2022, 2023, 2024, 2025]

# ── Funções auxiliares de extração ───────────────────────────────────────────

def soma_conta(conta_nome, subconta_nome=None):
    """Retorna série mensal (apenas datas 2021-2025) de uma conta/subconta."""
    mask = df_raw['Conta'].astype(str).str.strip().str.upper() == conta_nome.strip().upper()
    if subconta_nome:
        mask &= (df_raw['SubConta'].astype(str).str.strip().str.upper()
                 == subconta_nome.strip().upper())
    sub = df_raw.loc[mask, date_cols]
    if sub.empty:
        return pd.Series(0.0, index=date_cols)
    return sub.apply(pd.to_numeric, errors='coerce').fillna(0).sum()

def soma_grupo(conta_nome):
    """Soma todas as subcontas de um grupo (Conta)."""
    mask = df_raw['Conta'].astype(str).str.strip().str.upper() == conta_nome.strip().upper()
    sub = df_raw.loc[mask, date_cols]
    if sub.empty:
        return pd.Series(0.0, index=date_cols)
    return sub.apply(pd.to_numeric, errors='coerce').fillna(0).sum()

def soma_linha(idx):
    """Retorna série de uma linha específica pelo índice."""
    row = df_raw.iloc[idx]
    return row[date_cols].apply(pd.to_numeric, errors='coerce').fillna(0)

def anual(serie):
    """Agrega série mensal em dicionário ano -> total."""
    result = {}
    for a in anos:
        cols_ano = [c for c in date_cols if c.year == a]
        result[a] = float(serie[cols_ano].sum())
    return result

def anual_mes(serie, ano):
    """Retorna lista com 12 valores mensais de um ano."""
    cols_ano = [c for c in date_cols if c.year == ano]
    return [float(serie.get(c, 0)) for c in cols_ano]

# ── Calcular blocos ───────────────────────────────────────────────────────────

# 1. FATURAMENTO BRUTO (linha 87 – valor real de receita)
receita = soma_linha(87)

# 2. CUSTOS DE MERCADORIAS (linha 91 – custo de mercadorias vendidas)
cmv = soma_linha(91)

# Subcategorias CMV
cmv_subs = {
    'Areia':            soma_conta('CUSTOS MERCADORIAS', 'AREIA'),
    'Cal Hidratado':    soma_conta('CUSTOS MERCADORIAS', 'CAL HIDRATADO'),
    'Cimento':          soma_conta('CUSTOS MERCADORIAS', 'CIMENTO'),
    'Cimento Cola':     soma_conta('CUSTOS MERCADORIAS', 'CIMENTO COLA'),
    'Ferro':            soma_conta('CUSTOS MERCADORIAS', 'FERRO'),
    'Fios':             soma_conta('CUSTOS MERCADORIAS', 'FIOS'),
    'Madeiras':         soma_conta('CUSTOS MERCADORIAS', 'MADEIRAS'),
    'Material Atacado': soma_conta('CUSTOS MERCADORIAS', 'MATERIAL ATACADO'),
    'Pedra':            soma_conta('CUSTOS MERCADORIAS', 'PEDRA'),
    'Pintura':          soma_conta('CUSTOS MERCADORIAS', 'PINTURA'),
    'Pisos':            soma_conta('CUSTOS MERCADORIAS', 'PISOS'),
    'Portas e Janelas': soma_conta('CUSTOS MERCADORIAS', 'PORTAS E JANELAS'),
    'Telhas':           soma_conta('CUSTOS MERCADORIAS', 'TELHAS'),
    'Tijolos e Blocos': soma_conta('CUSTOS MERCADORIAS', 'TIJOLOS E BLOCOS'),
}

# 3. DESPESAS FIXAS
desp_fixas = soma_grupo('DESPESAS FIXA - F')
desp_fixas_subs = {
    'ACASAP':               soma_conta('DESPESAS FIXA - F', 'ACASAP'),
    'Água e Esgoto':        soma_conta('DESPESAS FIXA - F', 'AGUA E ESGOTO'),
    'Aluguel':              soma_conta('DESPESAS FIXA - F', 'ALUGUEL'),
    'Energia Elétrica':     soma_conta('DESPESAS FIXA - F', 'ENERGIA ELETRICA'),
    'Internet':             soma_conta('DESPESAS FIXA - F', 'INTERNET'),
    'Plano de Saúde':       soma_conta('DESPESAS FIXA - F', 'PLANO DE SAUDE'),
    'Rede Casa Vale Mais':  soma_conta('DESPESAS FIXA - F', 'REDE CASA VALE MAIS - MENSALID'),
    'Treinamento Rede':     soma_conta('DESPESAS FIXA - F', 'REDE CASA VALE MAIS - TREINAME'),
    'Securit Master':       soma_conta('DESPESAS FIXA - F', 'SECURIT MASTER'),
    'Seguro':               soma_conta('DESPESAS FIXA - F', 'SEGURO'),
    'Sistema':              soma_conta('DESPESAS FIXA - F', 'SISTEMA'),
    'Telefone':             soma_conta('DESPESAS FIXA - F', 'TELEFONE'),
}

# 4. DESPESAS TRIBUTÁRIAS
desp_trib = soma_grupo('DESPESAS FIXAS/TRIBUTARIAS ')
desp_trib_subs = {
    'DAS':                  soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'DAS'),
    'ICMS':                 soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'ICMS'),
    'Impostos / DARF':      soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'IMPOSTOS / DARF'),
    'IPVA':                 soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'IPVA'),
    'Licenciamento':        soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'LICENCIAMENTO'),
    'Outros Impostos':      soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'OUTROS IMPOSTOS'),
    'Seguro (Tributário)':  soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'SEGURO'),
    'Sindicato Patronal':   soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'SINDICATO PATRONAL'),
    'Taxas Municipais':     soma_conta('DESPESAS FIXAS/TRIBUTARIAS ', 'TAXAS MUNICIPAIS'),
}

# 5. DESPESAS VARIÁVEIS
desp_var = soma_grupo('DESPESAS VARIAVEIS - V')
desp_var_subs = {
    'Aquisição':            soma_conta('DESPESAS VARIAVEIS - V', 'AQUISICAO'),
    'Café / Lanches':       soma_conta('DESPESAS VARIAVEIS - V', 'CAFE / LANCHES'),
    'Combustível':          soma_conta('DESPESAS VARIAVEIS - V', 'COMBUSTIVEL'),
    'Consultas Cheques':    soma_conta('DESPESAS VARIAVEIS - V', 'CONSULTAS CHEQUES'),
    'Investimento':         soma_conta('DESPESAS VARIAVEIS - V', 'INVESTIMENTO'),
    'Manutenção':           soma_conta('DESPESAS VARIAVEIS - V', 'MANUTENCAO'),
    'Material de Escritório': soma_conta('DESPESAS VARIAVEIS - V', 'MATERIAL DE ESCRITORIO'),
    'Material de Limpeza':  soma_conta('DESPESAS VARIAVEIS - V', 'MATERIAL DE LIMPEZA'),
    'Patrocínios':          soma_conta('DESPESAS VARIAVEIS - V', 'PATROCINIOS'),
    'Publicidade':          soma_conta('DESPESAS VARIAVEIS - V', 'PUBLICIDADE'),
    'Serviços Mecânicos':   soma_conta('DESPESAS VARIAVEIS - V', 'SERVICOS MECANICOS'),
    'Vale Transporte':      soma_conta('DESPESAS VARIAVEIS - V', 'VALE TRANSPORTE'),
}

# 6. FOLHA DE PAGAMENTO (inclui Pró-Labore da conta CAIXA)
folha = soma_grupo('VARIAVEIS / FOLHA DE PAGA') + soma_conta('CAIXA', 'PRO LABORE')
folha_subs = {
    '13º Salário':          soma_conta('VARIAVEIS / FOLHA DE PAGA', '13º SALARIO'),
    'Comissão':             soma_conta('VARIAVEIS / FOLHA DE PAGA', 'COMISSAO'),
    'Férias':               soma_conta('VARIAVEIS / FOLHA DE PAGA', 'FERIAS'),
    'FGTS':                 soma_conta('VARIAVEIS / FOLHA DE PAGA', 'FGTS'),
    'GPS (INSS)':           soma_conta('VARIAVEIS / FOLHA DE PAGA', 'GPS'),
    'Honorário Contador':   soma_conta('VARIAVEIS / FOLHA DE PAGA', 'HONORARIO CONTADOR'),
    'Metas Vendas':         soma_conta('VARIAVEIS / FOLHA DE PAGA', 'METAS VENDAS'),
    'Pró-Labore':           soma_conta('CAIXA ', 'PRO LABORE'),
    'Retirada Pró-Labore':  soma_conta('VARIAVEIS / FOLHA DE PAGA', 'RETIRADA - DINHEIRO PRO LA'),
    'Salários':             soma_conta('0', 'SALARIOS'),
    'Seguro Vida':          soma_conta('VARIAVEIS / FOLHA DE PAGA', 'SEGURO VIDA'),
    'Taxa Sindicato':       soma_conta('VARIAVEIS / FOLHA DE PAGA', 'TAXA SINDICATO'),
    'Temporários':          soma_conta('VARIAVEIS / FOLHA DE PAGA', 'TEMPORARIOS'),
    'Vale':                 soma_conta('VARIAVEIS / FOLHA DE PAGA', 'VALE'),
}

# 7. CONTA CORRENTE / ENCARGOS FINANCEIROS
cc_fin = soma_grupo('CONTA CORRENTE - F')
cc_subs = {
    'Cheque Devolvido':     soma_conta('CONTA CORRENTE - F', 'CHEQUE DEVOLVIDO'),
    'Depósito':             soma_conta('CONTA CORRENTE - F', 'DEPOSITO'),
    'Diferença de Caixa':   soma_conta('CONTA CORRENTE - F', 'DIFERENCA DE CAIXA'),
    'Entrada Empréstimos':  soma_conta('CONTA CORRENTE - F', 'ENTRADA DE EMPRESTIMOS'),
    'Juros / IOF':          soma_conta('CONTA CORRENTE - F', 'JUROS / IOF'),
    'Pgto Empréstimos':     soma_conta('CONTA CORRENTE - F', 'PAGAMENTO DE EMPRESTIMOS'),
    'Tarifa Desc. Cartões': soma_conta('Cc / DESPESAS VARIAVEIS ', 'TARIFA DESCONTO DE CARTOES'),
    'Taxas e Encargos':     soma_conta('CONTA CORRENTE - F', 'TAXAS E ENCARGOS'),
    'Troco em Cheque':      soma_conta('CONTA CORRENTE - F', 'TROCO EM CHEQUE'),
    'Troco em Dinheiro':    soma_conta('CONTA CORRENTE - F', 'TROCO EM DINHEIRO'),
}

# ── Cálculos anuais ───────────────────────────────────────────────────────────
R = anual(receita)
CMV_a = anual(cmv)
LB = {a: R[a] - CMV_a[a] for a in anos}   # Lucro Bruto
DF_a = anual(desp_fixas)
DT_a = anual(desp_trib)
DV_a = anual(desp_var)
FP_a = anual(folha)
CC_a = anual(cc_fin)
TOT_DESP = {a: DF_a[a] + DT_a[a] + DV_a[a] + FP_a[a] + CC_a[a] for a in anos}
EBITDA = {a: LB[a] - TOT_DESP[a] for a in anos}
MARGEM_LB = {a: (LB[a] / R[a] * 100) if R[a] else 0 for a in anos}
MARGEM_LQ = {a: (EBITDA[a] / R[a] * 100) if R[a] else 0 for a in anos}

# ── Criar workbook ────────────────────────────────────────────────────────────
wb = Workbook()
wb.remove(wb.active)

MESES_NOME = ['Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun',
               'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez']

# ══════════════════════════════════════════════════════════════════════════════
# ABA 1 – RESUMO CONSOLIDADO (2021-2025)
# ══════════════════════════════════════════════════════════════════════════════
ws = wb.create_sheet("DFE - Consolidado")
ws.sheet_view.showGridLines = False
ws.freeze_panes = "B4"

# Larguras
ws.column_dimensions['A'].width = 38
for i, a in enumerate(anos, start=2):
    ws.column_dimensions[get_column_letter(i)].width = 16
ws.column_dimensions[get_column_letter(len(anos)+2)].width = 16  # var %

# ── Título ────────────────────────────────────────────────────────────────────
ws.merge_cells("A1:G1")
c = ws["A1"]
c.value = "DEMONSTRATIVO DO EXERCÍCIO FINANCEIRO  –  2021 a 2025"
c.font = Font(name="Calibri", bold=True, size=14, color=C_BRANCO)
c.fill = fill(C_AZUL_ESC)
c.alignment = align('center')
ws.row_dimensions[1].height = 28

ws.merge_cells("A2:G2")
c = ws["A2"]
c.value = "Valores em R$ | Elaborado em: " + pd.Timestamp.now().strftime("%d/%m/%Y")
c.font = Font(name="Calibri", size=9, italic=True, color="595959")
c.fill = fill("EBF0F7")
c.alignment = align('center')
ws.row_dimensions[2].height = 16

# ── Cabeçalho anos ────────────────────────────────────────────────────────────
row_header = 3
for col, ano in enumerate(anos, start=2):
    c = ws.cell(row=row_header, column=col, value=str(ano))
    c.font = Font(name="Calibri", bold=True, size=11, color=C_BRANCO)
    c.fill = fill(C_AZUL_ESC)
    c.alignment = align('center')
    c.border = border_thin()

ws.cell(row=row_header, column=len(anos)+2, value="Var% 24→25")
ws.cell(row=row_header, column=len(anos)+2).font = Font(name="Calibri", bold=True, size=11, color=C_BRANCO)
ws.cell(row=row_header, column=len(anos)+2).fill = fill(C_AZUL_ESC)
ws.cell(row=row_header, column=len(anos)+2).alignment = align('center')
ws.cell(row=row_header, column=len(anos)+2).border = border_thin()
ws.cell(row=row_header, column=1).fill = fill(C_AZUL_ESC)
ws.row_dimensions[row_header].height = 20

row = 4

def write_group_header(ws, row, label):
    ws.merge_cells(f"A{row}:G{row}")
    c = ws.cell(row=row, column=1, value=f"  {label}")
    c.font = Font(name="Calibri", bold=True, size=10, color=C_BRANCO)
    c.fill = fill(C_AZUL_MED)
    c.alignment = align('left')
    c.border = border_thin()
    ws.row_dimensions[row].height = 18
    return row + 1

def write_detail(ws, row, label, valores_dict, bg=C_BRANCO, indent=True):
    label_cell = "    " + label if indent else label
    c = ws.cell(row=row, column=1, value=label_cell)
    c.font = Font(name="Calibri", size=10, color="363636")
    c.fill = fill(bg)
    c.alignment = align('left')
    c.border = border_thin()
    for i, a in enumerate(anos, start=2):
        v = valores_dict.get(a, 0)
        cell = ws.cell(row=row, column=i, value=v if v != 0 else None)
        cell.number_format = FMT_BRL
        cell.font = Font(name="Calibri", size=10, color="363636")
        cell.fill = fill(bg)
        cell.alignment = align('right')
        cell.border = border_thin()
    # Var%
    v24 = valores_dict.get(2024, 0)
    v25 = valores_dict.get(2025, 0)
    var = ((v25 - v24) / abs(v24)) if v24 else None
    vc = ws.cell(row=row, column=len(anos)+2, value=var)
    vc.number_format = FMT_PCT
    vc.font = Font(name="Calibri", size=10,
                   color="1E5631" if (var or 0) >= 0 else "C00000")
    vc.fill = fill(bg)
    vc.alignment = align('center')
    vc.border = border_thin()
    return row + 1

def write_subtotal(ws, row, label, valores_dict, bg=C_AZUL_CLR):
    c = ws.cell(row=row, column=1, value=f"  {label}")
    c.font = Font(name="Calibri", bold=True, size=10, color=C_AZUL_ESC)
    c.fill = fill(bg)
    c.alignment = align('left')
    c.border = border_medium_bottom()
    for i, a in enumerate(anos, start=2):
        v = valores_dict.get(a, 0)
        cell = ws.cell(row=row, column=i, value=v)
        cell.number_format = FMT_BRL
        cell.font = Font(name="Calibri", bold=True, size=10, color=C_AZUL_ESC)
        cell.fill = fill(bg)
        cell.alignment = align('right')
        cell.border = border_medium_bottom()
    v24 = valores_dict.get(2024, 0)
    v25 = valores_dict.get(2025, 0)
    var = ((v25 - v24) / abs(v24)) if v24 else None
    vc = ws.cell(row=row, column=len(anos)+2, value=var)
    vc.number_format = FMT_PCT
    vc.font = Font(name="Calibri", bold=True, size=10,
                   color="1E5631" if (var or 0) >= 0 else "C00000")
    vc.fill = fill(bg)
    vc.alignment = align('center')
    vc.border = border_medium_bottom()
    ws.row_dimensions[row].height = 18
    return row + 1

def write_resultado(ws, row, label, valores_dict, positivo_bom=True):
    c = ws.cell(row=row, column=1, value=f"  {label}")
    c.font = Font(name="Calibri", bold=True, size=11, color=C_BRANCO)
    c.border = border_medium_bottom()
    c.alignment = align('left')
    for i, a in enumerate(anos, start=2):
        v = valores_dict.get(a, 0)
        bg = C_VERDE_CLR if (v >= 0) == positivo_bom else C_VERM_CLR
        cell = ws.cell(row=row, column=i, value=v)
        cell.number_format = FMT_BRL
        cell.font = Font(name="Calibri", bold=True, size=11,
                         color="1E5631" if (v >= 0) == positivo_bom else "C00000")
        cell.fill = fill(bg)
        cell.alignment = align('right')
        cell.border = border_medium_bottom()
    # preenche label bg
    bg_lbl = C_VERDE_CLR if valores_dict.get(2025, 0) >= 0 else C_VERM_CLR
    c.fill = fill(bg_lbl)
    c.font = Font(name="Calibri", bold=True, size=11,
                  color="1E5631" if valores_dict.get(2025, 0) >= 0 else "C00000")
    v24 = valores_dict.get(2024, 0)
    v25 = valores_dict.get(2025, 0)
    var = ((v25 - v24) / abs(v24)) if v24 else None
    vc = ws.cell(row=row, column=len(anos)+2, value=var)
    vc.number_format = FMT_PCT
    vc.font = Font(name="Calibri", bold=True, size=11,
                   color="1E5631" if (var or 0) >= 0 else "C00000")
    vc.fill = fill(bg_lbl)
    vc.alignment = align('center')
    vc.border = border_medium_bottom()
    ws.row_dimensions[row].height = 20
    return row + 1

def write_pct_row(ws, row, label, pct_dict):
    c = ws.cell(row=row, column=1, value=f"    {label}")
    c.font = Font(name="Calibri", size=9, italic=True, color="595959")
    c.fill = fill("F2F7FD")
    c.alignment = align('left')
    c.border = border_thin()
    for i, a in enumerate(anos, start=2):
        v = pct_dict.get(a, 0)
        cell = ws.cell(row=row, column=i, value=v / 100)
        cell.number_format = FMT_PCT
        cell.font = Font(name="Calibri", size=9, italic=True, color="595959")
        cell.fill = fill("F2F7FD")
        cell.alignment = align('right')
        cell.border = border_thin()
    vc = ws.cell(row=row, column=len(anos)+2, value=None)
    vc.fill = fill("F2F7FD")
    vc.border = border_thin()
    return row + 1

def spacer(ws, row):
    for col in range(1, len(anos)+3):
        c = ws.cell(row=row, column=col, value=None)
        c.fill = fill("EBF0F7")
        c.border = border_thin()
    ws.row_dimensions[row].height = 6
    return row + 1

# ── FATURAMENTO BRUTO ─────────────────────────────────────────────────────────
row = write_group_header(ws, row, "1. RECEITA BRUTA")
row = write_subtotal(ws, row, "Faturamento Bruto", R, bg=C_AMARELO)
row = spacer(ws, row)

# ── CMV ───────────────────────────────────────────────────────────────────────
row = write_group_header(ws, row, "2. CUSTO DAS MERCADORIAS VENDIDAS (CMV)")
for nome, serie in cmv_subs.items():
    row = write_detail(ws, row, nome, anual(serie))
row = write_subtotal(ws, row, "(-) Total CMV", CMV_a, bg=C_VERM_CLR)
row = spacer(ws, row)

# ── LUCRO BRUTO ───────────────────────────────────────────────────────────────
row = write_resultado(ws, row, "= LUCRO BRUTO", LB)
row = write_pct_row(ws, row, "Margem Bruta %", MARGEM_LB)
row = spacer(ws, row)

# ── DESPESAS FIXAS ────────────────────────────────────────────────────────────
row = write_group_header(ws, row, "3. DESPESAS FIXAS")
for nome, serie in desp_fixas_subs.items():
    row = write_detail(ws, row, nome, anual(serie))
row = write_subtotal(ws, row, "(-) Total Despesas Fixas", DF_a, bg=C_VERM_CLR)
row = spacer(ws, row)

# ── DESPESAS TRIBUTÁRIAS ──────────────────────────────────────────────────────
row = write_group_header(ws, row, "4. DESPESAS TRIBUTÁRIAS")
for nome, serie in desp_trib_subs.items():
    row = write_detail(ws, row, nome, anual(serie))
row = write_subtotal(ws, row, "(-) Total Tributário", DT_a, bg=C_VERM_CLR)
row = spacer(ws, row)

# ── DESPESAS VARIÁVEIS ────────────────────────────────────────────────────────
row = write_group_header(ws, row, "5. DESPESAS VARIÁVEIS")
for nome, serie in desp_var_subs.items():
    row = write_detail(ws, row, nome, anual(serie))
row = write_subtotal(ws, row, "(-) Total Despesas Variáveis", DV_a, bg=C_VERM_CLR)
row = spacer(ws, row)

# ── FOLHA DE PAGAMENTO ────────────────────────────────────────────────────────
row = write_group_header(ws, row, "6. FOLHA DE PAGAMENTO / PESSOAL")
for nome, serie in folha_subs.items():
    row = write_detail(ws, row, nome, anual(serie))
row = write_subtotal(ws, row, "(-) Total Folha de Pagamento", FP_a, bg=C_VERM_CLR)
row = spacer(ws, row)

# ── ENCARGOS FINANCEIROS ──────────────────────────────────────────────────────
row = write_group_header(ws, row, "7. ENCARGOS FINANCEIROS / CONTA CORRENTE")
for nome, serie in cc_subs.items():
    row = write_detail(ws, row, nome, anual(serie))
row = write_subtotal(ws, row, "(-) Total Financeiro", CC_a, bg=C_VERM_CLR)
row = spacer(ws, row)

# ── TOTAL DESPESAS ────────────────────────────────────────────────────────────
row = write_subtotal(ws, row, "(-) TOTAL GERAL DE DESPESAS", TOT_DESP, bg=C_CINZA)
row = spacer(ws, row)

# ── RESULTADO FINAL ───────────────────────────────────────────────────────────
row = write_resultado(ws, row, "= RESULTADO DO EXERCÍCIO (EBITDA aprox.)", EBITDA)
row = write_pct_row(ws, row, "Margem Líquida %", MARGEM_LQ)
row = spacer(ws, row)

# ══════════════════════════════════════════════════════════════════════════════
# ABAS ANUAIS
# ══════════════════════════════════════════════════════════════════════════════

def criar_aba_ano(wb, ano):
    ws = wb.create_sheet(f"DFE {ano}")
    ws.sheet_view.showGridLines = False
    ws.freeze_panes = "B5"

    # Larguras
    ws.column_dimensions['A'].width = 36
    ws.column_dimensions['N'].width = 16   # Total
    ws.column_dimensions['O'].width = 11   # %receita
    for m in range(1, 13):
        ws.column_dimensions[get_column_letter(m+1)].width = 12

    # Título
    ws.merge_cells("A1:O1")
    c = ws["A1"]
    c.value = f"DEMONSTRATIVO DO EXERCÍCIO FINANCEIRO  –  {ano}"
    c.font = Font(name="Calibri", bold=True, size=13, color=C_BRANCO)
    c.fill = fill(C_AZUL_ESC)
    c.alignment = align('center')
    ws.row_dimensions[1].height = 26

    ws.merge_cells("A2:O2")
    c = ws["A2"]
    c.value = "Valores em R$"
    c.font = Font(name="Calibri", size=9, italic=True, color="595959")
    c.fill = fill("EBF0F7")
    c.alignment = align('center')
    ws.row_dimensions[2].height = 14

    # Cabeçalho meses
    ws.cell(row=4, column=1, value="Conta / Descrição")
    ws.cell(row=4, column=1).font = Font(name="Calibri", bold=True, size=10, color=C_BRANCO)
    ws.cell(row=4, column=1).fill = fill(C_AZUL_ESC)
    ws.cell(row=4, column=1).alignment = align('center')
    ws.cell(row=4, column=1).border = border_thin()

    for m, nome in enumerate(MESES_NOME, start=2):
        c = ws.cell(row=4, column=m, value=nome)
        c.font = Font(name="Calibri", bold=True, size=10, color=C_BRANCO)
        c.fill = fill(C_AZUL_ESC)
        c.alignment = align('center')
        c.border = border_thin()

    ws.cell(row=4, column=14, value="TOTAL ANUAL")
    ws.cell(row=4, column=14).font = Font(name="Calibri", bold=True, size=10, color=C_BRANCO)
    ws.cell(row=4, column=14).fill = fill(C_VERDE_ESC)
    ws.cell(row=4, column=14).alignment = align('center')
    ws.cell(row=4, column=14).border = border_thin()

    ws.cell(row=4, column=15, value="% Receita")
    ws.cell(row=4, column=15).font = Font(name="Calibri", bold=True, size=10, color=C_BRANCO)
    ws.cell(row=4, column=15).fill = fill(C_VERDE_ESC)
    ws.cell(row=4, column=15).alignment = align('center')
    ws.cell(row=4, column=15).border = border_thin()
    ws.row_dimensions[4].height = 20

    row_receita_total = None  # para % receita
    row = 5

    def wgh(label):
        nonlocal row
        ws.merge_cells(f"A{row}:O{row}")
        c = ws.cell(row=row, column=1, value=f"  {label}")
        c.font = Font(name="Calibri", bold=True, size=10, color=C_BRANCO)
        c.fill = fill(C_AZUL_MED)
        c.alignment = align('left')
        c.border = border_thin()
        ws.row_dimensions[row].height = 16
        row += 1

    def wd(label, serie, bg=C_BRANCO):
        nonlocal row
        vals = anual_mes(serie, ano)
        total = sum(vals)
        c = ws.cell(row=row, column=1, value=f"    {label}")
        c.font = Font(name="Calibri", size=10)
        c.fill = fill(bg)
        c.alignment = align('left')
        c.border = border_thin()
        for m, v in enumerate(vals, start=2):
            cell = ws.cell(row=row, column=m, value=v if v != 0 else None)
            cell.number_format = FMT_BRL
            cell.fill = fill(bg)
            cell.alignment = align('right')
            cell.border = border_thin()
            cell.font = Font(name="Calibri", size=10)
        tc = ws.cell(row=row, column=14, value=total if total != 0 else None)
        tc.number_format = FMT_BRL
        tc.fill = fill(bg)
        tc.alignment = align('right')
        tc.border = border_thin()
        tc.font = Font(name="Calibri", size=10, bold=True)
        ws.cell(row=row, column=15).fill = fill(bg)
        ws.cell(row=row, column=15).border = border_thin()
        row += 1
        return total

    def wst(label, total_val, bg=C_AZUL_CLR, serie=None):
        nonlocal row
        if serie is not None:
            vals = anual_mes(serie, ano)
        else:
            vals = [None]*12
        c = ws.cell(row=row, column=1, value=f"  {label}")
        c.font = Font(name="Calibri", bold=True, size=10, color=C_AZUL_ESC)
        c.fill = fill(bg)
        c.alignment = align('left')
        c.border = border_medium_bottom()
        for m, v in enumerate(vals, start=2):
            cell = ws.cell(row=row, column=m, value=v if v else None)
            cell.number_format = FMT_BRL
            cell.font = Font(name="Calibri", bold=True, size=10, color=C_AZUL_ESC)
            cell.fill = fill(bg)
            cell.alignment = align('right')
            cell.border = border_medium_bottom()
        tc = ws.cell(row=row, column=14, value=total_val)
        tc.number_format = FMT_BRL
        tc.font = Font(name="Calibri", bold=True, size=10, color=C_AZUL_ESC)
        tc.fill = fill(bg)
        tc.alignment = align('right')
        tc.border = border_medium_bottom()
        ws.cell(row=row, column=15).fill = fill(bg)
        ws.cell(row=row, column=15).border = border_medium_bottom()
        ws.row_dimensions[row].height = 18
        row += 1

    def wres(label, serie_vals, ref_total):
        nonlocal row
        vals = anual_mes(serie_vals, ano) if hasattr(serie_vals, 'values') else None
        total_v = sum(anual_mes(serie_vals, ano)) if hasattr(serie_vals, 'values') else serie_vals
        bg = C_VERDE_CLR if total_v >= 0 else C_VERM_CLR
        txt_color = "1E5631" if total_v >= 0 else "C00000"
        c = ws.cell(row=row, column=1, value=f"  {label}")
        c.font = Font(name="Calibri", bold=True, size=11, color=txt_color)
        c.fill = fill(bg)
        c.alignment = align('left')
        c.border = border_medium_bottom()
        if vals:
            for m, v in enumerate(vals, start=2):
                r_bg = C_VERDE_CLR if v >= 0 else C_VERM_CLR
                r_color = "1E5631" if v >= 0 else "C00000"
                cell = ws.cell(row=row, column=m, value=v)
                cell.number_format = FMT_BRL
                cell.font = Font(name="Calibri", bold=True, size=10, color=r_color)
                cell.fill = fill(r_bg)
                cell.alignment = align('right')
                cell.border = border_medium_bottom()
        else:
            for m in range(2, 14):
                cell = ws.cell(row=row, column=m)
                cell.fill = fill(bg)
                cell.border = border_medium_bottom()

        tc = ws.cell(row=row, column=14, value=total_v)
        tc.number_format = FMT_BRL
        tc.font = Font(name="Calibri", bold=True, size=11, color=txt_color)
        tc.fill = fill(bg)
        tc.alignment = align('right')
        tc.border = border_medium_bottom()

        pct = (total_v / ref_total * 100) if ref_total else 0
        pc = ws.cell(row=row, column=15, value=pct/100)
        pc.number_format = FMT_PCT
        pc.font = Font(name="Calibri", bold=True, size=10, color=txt_color)
        pc.fill = fill(bg)
        pc.alignment = align('center')
        pc.border = border_medium_bottom()
        ws.row_dimensions[row].height = 20
        row += 1

    def sp():
        nonlocal row
        for col in range(1, 16):
            c = ws.cell(row=row, column=col)
            c.fill = fill("EBF0F7")
            c.border = border_thin()
        ws.row_dimensions[row].height = 5
        row += 1

    r_ano = R[ano]

    # Receita
    wgh("1. RECEITA BRUTA")
    wst("Faturamento Bruto", r_ano, bg=C_AMARELO, serie=receita)
    sp()

    # CMV
    wgh("2. CUSTO DAS MERCADORIAS VENDIDAS (CMV)")
    for nome, serie in cmv_subs.items():
        wd(nome, serie)
    wst("(-) Total CMV", CMV_a[ano], bg=C_VERM_CLR, serie=cmv)
    sp()

    # Lucro Bruto
    lb_serie_vals = receita - cmv
    wres("= LUCRO BRUTO", lb_serie_vals, r_ano)
    sp()

    # Despesas Fixas
    wgh("3. DESPESAS FIXAS")
    for nome, serie in desp_fixas_subs.items():
        wd(nome, serie)
    wst("(-) Total Despesas Fixas", DF_a[ano], bg=C_VERM_CLR, serie=desp_fixas)
    sp()

    # Tributárias
    wgh("4. DESPESAS TRIBUTÁRIAS")
    for nome, serie in desp_trib_subs.items():
        wd(nome, serie)
    wst("(-) Total Tributário", DT_a[ano], bg=C_VERM_CLR, serie=desp_trib)
    sp()

    # Variáveis
    wgh("5. DESPESAS VARIÁVEIS")
    for nome, serie in desp_var_subs.items():
        wd(nome, serie)
    wst("(-) Total Despesas Variáveis", DV_a[ano], bg=C_VERM_CLR, serie=desp_var)
    sp()

    # Folha
    wgh("6. FOLHA DE PAGAMENTO / PESSOAL")
    for nome, serie in folha_subs.items():
        wd(nome, serie)
    wst("(-) Total Folha de Pagamento", FP_a[ano], bg=C_VERM_CLR, serie=folha)
    sp()

    # Financeiro
    wgh("7. ENCARGOS FINANCEIROS / CONTA CORRENTE")
    for nome, serie in cc_subs.items():
        wd(nome, serie)
    wst("(-) Total Financeiro", CC_a[ano], bg=C_VERM_CLR, serie=cc_fin)
    sp()

    # Total Despesas
    wst("(-) TOTAL GERAL DE DESPESAS", TOT_DESP[ano], bg=C_CINZA)
    sp()

    # Resultado Final
    ebitda_serie = receita - cmv - desp_fixas - desp_trib - desp_var - folha - cc_fin
    wres("= RESULTADO DO EXERCÍCIO (EBITDA aprox.)", ebitda_serie, r_ano)
    sp()

for ano in anos:
    criar_aba_ano(wb, ano)

# ══════════════════════════════════════════════════════════════════════════════
# ABA GRÁFICOS – dados para gráfico externo (summary table)
# ══════════════════════════════════════════════════════════════════════════════
ws_g = wb.create_sheet("Resumo Indicadores")
ws_g.sheet_view.showGridLines = False
ws_g.column_dimensions['A'].width = 30
for i in range(2, 7):
    ws_g.column_dimensions[get_column_letter(i)].width = 16

headers = ["Indicador"] + [str(a) for a in anos]
for ci, h in enumerate(headers, start=1):
    c = ws_g.cell(row=1, column=ci, value=h)
    c.font = Font(name="Calibri", bold=True, size=11, color=C_BRANCO)
    c.fill = fill(C_AZUL_ESC)
    c.alignment = align('center')
    c.border = border_thin()

indicadores = [
    ("Faturamento Bruto", R, FMT_BRL),
    ("CMV", CMV_a, FMT_BRL),
    ("Lucro Bruto", LB, FMT_BRL),
    ("Despesas Fixas", DF_a, FMT_BRL),
    ("Despesas Tributárias", DT_a, FMT_BRL),
    ("Despesas Variáveis", DV_a, FMT_BRL),
    ("Folha de Pagamento", FP_a, FMT_BRL),
    ("Encargos Financeiros", CC_a, FMT_BRL),
    ("Total Despesas", TOT_DESP, FMT_BRL),
    ("Resultado do Exercício", EBITDA, FMT_BRL),
    ("Margem Bruta %", {a: MARGEM_LB[a]/100 for a in anos}, FMT_PCT),
    ("Margem Líquida %", {a: MARGEM_LQ[a]/100 for a in anos}, FMT_PCT),
]

for ri, (nome, vals, fmt) in enumerate(indicadores, start=2):
    bg = C_VERDE_CLR if nome.startswith("Result") else (C_VERM_CLR if "Total" in nome and nome != "Faturamento" else C_BRANCO)
    c = ws_g.cell(row=ri, column=1, value=nome)
    c.font = Font(name="Calibri", bold=True, size=10)
    c.fill = fill(bg if bg != C_BRANCO else "F5F5F5")
    c.alignment = align('left')
    c.border = border_thin()
    for ai, a in enumerate(anos, start=2):
        v = vals.get(a, 0)
        cell = ws_g.cell(row=ri, column=ai, value=v)
        cell.number_format = fmt
        cell.font = Font(name="Calibri", size=10)
        cell.fill = fill(bg if bg != C_BRANCO else "F5F5F5")
        cell.alignment = align('right')
        cell.border = border_thin()

# ── Salvar ─────────────────────────────────────────────────────────────────────
output_path = r'D:\Evolução categorias\DFE_2021_2025.xlsx'
wb.save(output_path)
print(f"DFE gerado com sucesso: {output_path}")

# ── Imprimir resumo ────────────────────────────────────────────────────────────
print()
print("=" * 70)
print(f"{'DEMONSTRATIVO DO EXERCÍCIO FINANCEIRO':^70}")
print("=" * 70)
print(f"{'Indicador':<35}" + "".join(f"{a:>14}" for a in anos))
print("-" * 70)
for nome, vals, _ in indicadores[:10]:
    linha = f"{nome:<35}"
    for a in anos:
        v = vals.get(a, 0)
        linha += f"{v:>14,.0f}".replace(",", "X").replace(".", ",").replace("X", ".")
    print(linha)
print("=" * 70)