import pandas as pd
import numpy as np
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
from matplotlib.gridspec import GridSpec
from matplotlib.ticker import FuncFormatter
import warnings
warnings.filterwarnings('ignore')

# ── Dados ─────────────────────────────────────────────────────────────────────
df_raw = pd.read_excel(r'C:\Users\Claudio\AppData\Local\Temp\dre_copia.xlsx', sheet_name='Planilha1')
date_cols = [c for c in df_raw.columns if hasattr(c, 'year') and 2021 <= c.year <= 2025]
anos = [2021, 2022, 2023, 2024, 2025]

def soma_linha(idx):
    row = df_raw.iloc[idx]
    return row[date_cols].apply(pd.to_numeric, errors='coerce').fillna(0)

def soma_grupo(conta):
    mask = df_raw['Conta'].astype(str).str.strip().str.upper() == conta.strip().upper()
    return df_raw.loc[mask, date_cols].apply(pd.to_numeric, errors='coerce').fillna(0).sum()

def soma_conta(conta, sub):
    mask = (df_raw['Conta'].astype(str).str.strip().str.upper() == conta.strip().upper()) & \
           (df_raw['SubConta'].astype(str).str.strip().str.upper() == sub.strip().upper())
    return df_raw.loc[mask, date_cols].apply(pd.to_numeric, errors='coerce').fillna(0).sum()

def anual(s):
    return [float(s[[c for c in date_cols if c.year == a]].sum()) for a in anos]

receita   = soma_linha(87)
cmv       = soma_linha(91)
lb        = receita - cmv
df_fix    = soma_grupo('DESPESAS FIXA - F')
df_trib   = soma_grupo('DESPESAS FIXAS/TRIBUTARIAS')
df_var    = soma_grupo('DESPESAS VARIAVEIS - V')
folha     = soma_grupo('VARIAVEIS / FOLHA DE PAGA') + soma_conta('CAIXA', 'PRO LABORE')
financ    = soma_grupo('CONTA CORRENTE - F') + soma_grupo('Cc / DESPESAS VARIAVEIS')
tot_desp  = df_fix + df_trib + df_var + folha + financ
resultado = lb - tot_desp

R  = anual(receita)
C  = anual(cmv)
LB = anual(lb)
DF = anual(df_fix)
DT = anual(df_trib)
DV = anual(df_var)
FP = anual(folha)
FN = anual(financ)
TD = anual(tot_desp)
RS = anual(resultado)
MG_BR = [lb/r*100 if r else 0 for lb, r in zip(LB, R)]
MG_LQ = [rs/r*100 if r else 0 for rs, r in zip(RS, R)]
CMV_PCT = [c/r*100 if r else 0 for c, r in zip(C, R)]

# Despesas Fixas subcontas
fix_subs = {
    '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'),
    'Energia Elétrica':     soma_conta('DESPESAS FIXA - F', 'ENERGIA ELETRICA'),
    'Aluguel':              soma_conta('DESPESAS FIXA - F', 'ALUGUEL'),
    'Securit Master':       soma_conta('DESPESAS FIXA - F', 'SECURIT MASTER'),
    'Telefone':             soma_conta('DESPESAS FIXA - F', 'TELEFONE'),
    'Água e Esgoto':        soma_conta('DESPESAS FIXA - F', 'AGUA E ESGOTO'),
    'Sistema':              soma_conta('DESPESAS FIXA - F', 'SISTEMA'),
    'Outros':               soma_conta('DESPESAS FIXA - F', 'INTERNET') +
                            soma_conta('DESPESAS FIXA - F', 'ACASAP') +
                            soma_conta('DESPESAS FIXA - F', 'REDE CASA VALE MAIS - TREINAME'),
}

# ── Paleta ────────────────────────────────────────────────────────────────────
COR_AZUL    = '#1F3864'
COR_AZUL2   = '#2F5496'
COR_AZUL3   = '#4472C4'
COR_VERDE   = '#1E5631'
COR_VERDE2  = '#70AD47'
COR_VERM    = '#C00000'
COR_VERM2   = '#FF6B6B'
COR_ALAR    = '#ED7D31'
COR_AMAR    = '#FFC000'
COR_CINZA   = '#D9D9D9'
COR_BG      = '#F5F7FA'
COR_PANEL   = '#FFFFFF'
ANOS_STR    = [str(a) for a in anos]

def fmt_milhao(x, pos):
    if abs(x) >= 1e6:   return f'R${x/1e6:.1f}M'
    if abs(x) >= 1e3:   return f'R${x/1e3:.0f}K'
    return f'R${x:.0f}'

def fmt_mil(x, pos):
    if abs(x) >= 1e3:   return f'R${x/1e3:.0f}K'
    return f'R${x:.0f}'

def fmt_pct(x, pos):
    return f'{x:.1f}%'

# ── Figura principal ──────────────────────────────────────────────────────────
fig = plt.figure(figsize=(22, 28), facecolor=COR_BG)
fig.subplots_adjust(left=0.05, right=0.97, top=0.94, bottom=0.03, hspace=0.52, wspace=0.35)

# Título principal
fig.text(0.5, 0.965, 'DASHBOARD FINANCEIRO  –  DEMONSTRATIVO DO EXERCÍCIO  2021–2025',
         ha='center', va='center', fontsize=18, fontweight='bold',
         color=COR_AZUL, fontfamily='DejaVu Sans')
fig.text(0.5, 0.955, 'Valores em R$  |  Elaborado em: ' + pd.Timestamp.now().strftime('%d/%m/%Y'),
         ha='center', va='center', fontsize=10, color='#595959')

gs = GridSpec(5, 4, figure=fig,
              left=0.05, right=0.97, top=0.945, bottom=0.03,
              hspace=0.55, wspace=0.38)

# ══════════════════════════════════════════════════════════════════════════════
# LINHA 0 – KPI CARDS (5 indicadores do último ano 2025)
# ══════════════════════════════════════════════════════════════════════════════
kpis = [
    ('Faturamento\nBruto 2025',  f"R$ {R[4]/1e6:.2f}M",   f"{(R[4]-R[3])/R[3]*100:+.1f}% vs 2024", COR_AZUL),
    ('Lucro\nBruto 2025',        f"R$ {LB[4]/1e6:.2f}M",  f"Margem {MG_BR[4]:.1f}%",               COR_AZUL2),
    ('Total\nDespesas 2025',     f"R$ {TD[4]/1e6:.2f}M",  f"{(TD[4]-TD[3])/TD[3]*100:+.1f}% vs 2024", COR_VERM),
    ('Resultado\n2025',          f"R$ {RS[4]/1e3:.0f}K",  f"Margem {MG_LQ[4]:.1f}%",               COR_VERDE),
    ('Margem Líq.\nMédia 5 anos',f"{sum(MG_LQ)/5:.1f}%",  f"Pico {max(MG_LQ):.1f}% em 2021",       COR_ALAR),
]

for i, (titulo, valor, sub, cor) in enumerate(kpis):
    ax = fig.add_subplot(gs[0, i if i < 4 else 3])
    ax.set_facecolor(cor)
    ax.set_xlim(0, 1); ax.set_ylim(0, 1)
    ax.axis('off')
    # Card
    fancy = mpatches.FancyBboxPatch((0.02, 0.05), 0.96, 0.90,
                                     boxstyle="round,pad=0.05",
                                     linewidth=0, facecolor=cor, zorder=1)
    ax.add_patch(fancy)
    ax.text(0.5, 0.72, titulo, ha='center', va='center', fontsize=9.5,
            color='white', fontweight='bold', wrap=True, zorder=2)
    ax.text(0.5, 0.42, valor, ha='center', va='center', fontsize=14,
            color='white', fontweight='bold', zorder=2)
    ax.text(0.5, 0.15, sub, ha='center', va='center', fontsize=8.5,
            color='#DDDDDD', zorder=2)

# ══════════════════════════════════════════════════════════════════════════════
# LINHA 1 – Faturamento vs CMV + Lucro Bruto (esquerda, largo)
#           Composição das Despesas por Ano (direita)
# ══════════════════════════════════════════════════════════════════════════════

# Gráfico 1 – Faturamento, CMV, Lucro Bruto
ax1 = fig.add_subplot(gs[1, :2])
ax1.set_facecolor(COR_PANEL)
x = np.arange(5)
w = 0.28
b1 = ax1.bar(x - w, R,  w, label='Faturamento Bruto', color=COR_AZUL,  zorder=3)
b2 = ax1.bar(x,     C,  w, label='CMV',               color=COR_VERM2, zorder=3)
b3 = ax1.bar(x + w, LB, w, label='Lucro Bruto',       color=COR_VERDE2,zorder=3)
ax1.set_xticks(x); ax1.set_xticklabels(ANOS_STR, fontsize=10)
ax1.yaxis.set_major_formatter(FuncFormatter(fmt_milhao))
ax1.set_title('Faturamento Bruto  ×  CMV  ×  Lucro Bruto', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)
ax1.legend(fontsize=8, framealpha=0.7, loc='upper right')
ax1.grid(axis='y', linestyle='--', alpha=0.4, zorder=0)
ax1.set_axisbelow(True)
ax1.tick_params(labelsize=9)
for bar in b3:
    h = bar.get_height()
    ax1.text(bar.get_x()+bar.get_width()/2, h+30000,
             f'R${h/1e6:.2f}M', ha='center', va='bottom', fontsize=7.5, color=COR_VERDE)
for spine in ax1.spines.values(): spine.set_visible(False)

# Gráfico 2 – Composição Despesas Stacked Bar
ax2 = fig.add_subplot(gs[1, 2:])
ax2.set_facecolor(COR_PANEL)
labels_desp = ['Fixas', 'Tributárias', 'Variáveis', 'Folha/Pessoal', 'Financeiro']
cores_desp  = [COR_AZUL, COR_ALAR, COR_AMAR, COR_VERM, COR_AZUL2]
dados_desp  = [DF, DT, DV, FP, FN]
bottom = np.zeros(5)
for label, cor, dados in zip(labels_desp, cores_desp, dados_desp):
    vals = np.array(dados)
    bars = ax2.bar(ANOS_STR, vals, bottom=bottom, label=label, color=cor, zorder=3)
    bottom += vals
ax2.yaxis.set_major_formatter(FuncFormatter(fmt_milhao))
ax2.set_title('Composição das Despesas Totais por Ano', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)
ax2.legend(fontsize=8, framealpha=0.7, loc='upper left')
ax2.grid(axis='y', linestyle='--', alpha=0.4, zorder=0)
ax2.set_axisbelow(True)
ax2.tick_params(labelsize=9)
for spine in ax2.spines.values(): spine.set_visible(False)

# ══════════════════════════════════════════════════════════════════════════════
# LINHA 2 – Margens (linha dupla) + Resultado do Exercício
# ══════════════════════════════════════════════════════════════════════════════

# Gráfico 3 – Margem Bruta e Líquida
ax3 = fig.add_subplot(gs[2, :2])
ax3.set_facecolor(COR_PANEL)
ax3.plot(ANOS_STR, MG_BR, 'o-', color=COR_AZUL3, lw=2.5, ms=7,
         label='Margem Bruta %', zorder=3)
ax3.plot(ANOS_STR, MG_LQ, 's--', color=COR_VERDE, lw=2.5, ms=7,
         label='Margem Líquida %', zorder=3)
ax3.fill_between(ANOS_STR, MG_LQ, alpha=0.12, color=COR_VERDE)
for i, (mb, ml) in enumerate(zip(MG_BR, MG_LQ)):
    ax3.text(i, mb+0.4, f'{mb:.1f}%', ha='center', va='bottom', fontsize=8.5,
             color=COR_AZUL3, fontweight='bold')
    ax3.text(i, ml-0.7, f'{ml:.1f}%', ha='center', va='top', fontsize=8.5,
             color=COR_VERDE, fontweight='bold')
ax3.yaxis.set_major_formatter(FuncFormatter(fmt_pct))
ax3.set_title('Evolução das Margens (%)', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)
ax3.legend(fontsize=9, framealpha=0.7)
ax3.grid(linestyle='--', alpha=0.4, zorder=0)
ax3.set_axisbelow(True)
ax3.tick_params(labelsize=9)
for spine in ax3.spines.values(): spine.set_visible(False)

# Gráfico 4 – Resultado do Exercício
ax4 = fig.add_subplot(gs[2, 2:])
ax4.set_facecolor(COR_PANEL)
cores_res = [COR_VERDE2 if v >= 0 else COR_VERM for v in RS]
bars = ax4.bar(ANOS_STR, RS, color=cores_res, zorder=3, width=0.5)
ax4.plot(ANOS_STR, RS, 'D-', color=COR_VERDE, lw=2, ms=6, zorder=4)
for bar, v in zip(bars, RS):
    ax4.text(bar.get_x()+bar.get_width()/2, v + 15000,
             f'R${v/1e3:.0f}K', ha='center', va='bottom', fontsize=8.5,
             color=COR_VERDE if v >= 0 else COR_VERM, fontweight='bold')
ax4.yaxis.set_major_formatter(FuncFormatter(fmt_milhao))
ax4.set_title('Resultado do Exercício (EBITDA aprox.)', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)
ax4.grid(axis='y', linestyle='--', alpha=0.4, zorder=0)
ax4.set_axisbelow(True)
ax4.tick_params(labelsize=9)
for spine in ax4.spines.values(): spine.set_visible(False)

# ══════════════════════════════════════════════════════════════════════════════
# LINHA 3 – Despesas Fixas: pizza composição + evolução subcontas
# ══════════════════════════════════════════════════════════════════════════════

# Gráfico 5 – Pizza despesas fixas (total 2021-2025)
ax5 = fig.add_subplot(gs[3, :2])
ax5.set_facecolor(COR_PANEL)
totais_fix = {k: float(anual(v).__class__(anual(v)) and sum(anual(v))) for k, v in fix_subs.items()}
# simplify
totais_fix = {k: sum(anual(v)) for k, v in fix_subs.items()}
labels_pie = list(totais_fix.keys())
sizes_pie  = list(totais_fix.values())
cores_pie  = [COR_AZUL, COR_AZUL2, COR_AZUL3, COR_ALAR, COR_AMAR,
              COR_VERM2, '#9DC3E6', '#A9D18E', COR_CINZA]
explode = [0.05 if v == max(sizes_pie) else 0 for v in sizes_pie]
wedges, texts, autotexts = ax5.pie(
    sizes_pie, labels=None, autopct='%1.1f%%',
    colors=cores_pie[:len(sizes_pie)], explode=explode,
    startangle=140, pctdistance=0.75,
    textprops={'fontsize': 8})
for at in autotexts: at.set_color('white'); at.set_fontweight('bold')
ax5.legend(wedges, labels_pie, loc='lower center', bbox_to_anchor=(0.5, -0.22),
           ncol=3, fontsize=8, framealpha=0.7)
ax5.set_title('Composição Despesas Fixas\n(Total 2021–2025)', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)

# Gráfico 6 – Evolução das 4 maiores despesas fixas
ax6 = fig.add_subplot(gs[3, 2:])
ax6.set_facecolor(COR_PANEL)
top4 = sorted(fix_subs.items(), key=lambda x: sum(anual(x[1])), reverse=True)[:4]
cores_top4 = [COR_AZUL, COR_ALAR, COR_VERDE2, COR_VERM2]
for (nome, serie), cor in zip(top4, cores_top4):
    vals = anual(serie)
    ax6.plot(ANOS_STR, vals, 'o-', color=cor, lw=2.2, ms=6, label=nome, zorder=3)
    ax6.text(len(ANOS_STR)-1, vals[-1], f' R${vals[-1]/1e3:.0f}K',
             va='center', fontsize=8, color=cor, fontweight='bold')
ax6.yaxis.set_major_formatter(FuncFormatter(fmt_mil))
ax6.set_title('Top 4 Despesas Fixas – Evolução Anual', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)
ax6.legend(fontsize=8.5, framealpha=0.7, loc='upper left')
ax6.grid(linestyle='--', alpha=0.4, zorder=0)
ax6.set_axisbelow(True)
ax6.tick_params(labelsize=9)
for spine in ax6.spines.values(): spine.set_visible(False)

# ══════════════════════════════════════════════════════════════════════════════
# LINHA 4 – CMV % do Faturamento + Indicadores-chave tabela
# ══════════════════════════════════════════════════════════════════════════════

# Gráfico 7 – CMV % receita + Despesas % receita
ax7 = fig.add_subplot(gs[4, :2])
ax7.set_facecolor(COR_PANEL)
desp_pct = [td/r*100 for td, r in zip(TD, R)]
cmv_pct  = CMV_PCT
ax7.fill_between(ANOS_STR, cmv_pct, alpha=0.25, color=COR_VERM2, label='_nolegend_')
ax7.fill_between(ANOS_STR, desp_pct, alpha=0.18, color=COR_AZUL2, label='_nolegend_')
ax7.plot(ANOS_STR, cmv_pct,  'o-', color=COR_VERM,  lw=2.5, ms=7, label='CMV % Receita', zorder=3)
ax7.plot(ANOS_STR, desp_pct, 's--', color=COR_AZUL2, lw=2.5, ms=7, label='Desp. Totais % Receita', zorder=3)
for i, (c, d) in enumerate(zip(cmv_pct, desp_pct)):
    ax7.text(i, c+0.3, f'{c:.1f}%', ha='center', fontsize=8, color=COR_VERM, fontweight='bold')
    ax7.text(i, d+0.3, f'{d:.1f}%', ha='center', fontsize=8, color=COR_AZUL2, fontweight='bold')
ax7.yaxis.set_major_formatter(FuncFormatter(fmt_pct))
ax7.set_title('CMV e Despesas Totais como % do Faturamento', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)
ax7.legend(fontsize=9, framealpha=0.7)
ax7.grid(linestyle='--', alpha=0.4, zorder=0)
ax7.set_axisbelow(True)
ax7.tick_params(labelsize=9)
for spine in ax7.spines.values(): spine.set_visible(False)

# Gráfico 8 – Tabela resumo de indicadores
ax8 = fig.add_subplot(gs[4, 2:])
ax8.set_facecolor(COR_PANEL)
ax8.axis('off')
col_labels = ['Indicador'] + ANOS_STR
row_data = [
    ['Faturamento'] + [f'R${v/1e6:.2f}M' for v in R],
    ['CMV']         + [f'R${v/1e6:.2f}M' for v in C],
    ['Lucro Bruto'] + [f'R${v/1e6:.2f}M' for v in LB],
    ['Tot.Despesas'] + [f'R${v/1e6:.2f}M' for v in TD],
    ['Resultado']   + [f'R${v/1e3:.0f}K' for v in RS],
    ['Mg. Bruta']   + [f'{v:.1f}%' for v in MG_BR],
    ['Mg. Líquida'] + [f'{v:.1f}%' for v in MG_LQ],
]
tbl = ax8.table(cellText=row_data, colLabels=col_labels,
                loc='center', cellLoc='center')
tbl.auto_set_font_size(False)
tbl.set_fontsize(9)
tbl.scale(1, 1.55)
# Estilo cabeçalho
for j in range(len(col_labels)):
    tbl[0, j].set_facecolor(COR_AZUL)
    tbl[0, j].set_text_props(color='white', fontweight='bold')
# Estilo linhas alternadas
cores_row = [COR_PANEL, '#EBF0F7']
for i in range(1, len(row_data)+1):
    for j in range(len(col_labels)):
        tbl[i, j].set_facecolor(cores_row[i % 2])
        if j == 0:
            tbl[i, j].set_text_props(fontweight='bold', color=COR_AZUL)
        # Resultado linha verde
        if row_data[i-1][0] == 'Resultado':
            tbl[i, j].set_facecolor('#D9EAD3')
            tbl[i, j].set_text_props(color=COR_VERDE, fontweight='bold')
        if row_data[i-1][0] in ('Mg. Bruta', 'Mg. Líquida'):
            tbl[i, j].set_facecolor('#FFF2CC')
ax8.set_title('Resumo de Indicadores 2021–2025', fontsize=11,
              fontweight='bold', color=COR_AZUL, pad=8)

# ── Salvar ────────────────────────────────────────────────────────────────────
output = r'D:\Evolução categorias\Dashboard_DFE_2021_2025.png'
plt.savefig(output, dpi=160, bbox_inches='tight', facecolor=COR_BG)
plt.close()
print(f"Dashboard salvo: {output}")