import pandas as pd
import numpy as np
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment, Border, Side,
                              numbers as xl_num)
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference, PieChart
from openpyxl.chart.series import DataPoint
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import io, os

# ---- Carregar dados ----
df = pd.read_pickle("D:/Evolução categorias/.worktrees/comissao/analise_produtos.pkl")

# ---- Paleta de cores ----
COR_TITULO   = "1F3864"   # azul escuro
COR_HEADER   = "2E75B6"   # azul médio
COR_HEADER2  = "2F5496"
COR_VERDE    = "70AD47"
COR_AMARELO  = "FFD966"
COR_LARANJA  = "F4B942"
COR_VERMELHO = "C00000"
COR_CINZA    = "D6DCE4"
COR_BRANCO   = "FFFFFF"
COR_FUNDO    = "EEF3FB"

def hdr(ws, row, col, valor, bold=True, bg=COR_HEADER, fg=COR_BRANCO, size=10, wrap=False, align='center'):
    c = ws.cell(row=row, column=col, value=valor)
    c.font = Font(bold=bold, color=fg, size=size, name='Calibri')
    c.fill = PatternFill("solid", fgColor=bg)
    c.alignment = Alignment(horizontal=align, vertical='center', wrap_text=wrap)
    c.border = Border(
        left=Side(style='thin', color='AAAAAA'),
        right=Side(style='thin', color='AAAAAA'),
        top=Side(style='thin', color='AAAAAA'),
        bottom=Side(style='thin', color='AAAAAA'))
    return c

def cel(ws, row, col, valor, fmt=None, bold=False, bg=None, fg='000000', align='center'):
    c = ws.cell(row=row, column=col, value=valor)
    c.font = Font(bold=bold, color=fg, size=10, name='Calibri')
    if bg:
        c.fill = PatternFill("solid", fgColor=bg)
    c.alignment = Alignment(horizontal=align, vertical='center')
    c.border = Border(
        left=Side(style='thin', color='DDDDDD'),
        right=Side(style='thin', color='DDDDDD'),
        top=Side(style='thin', color='EEEEEE'),
        bottom=Side(style='thin', color='EEEEEE'))
    if fmt:
        c.number_format = fmt
    return c

# ============================================================
wb = openpyxl.Workbook()

# ===========================================================
# ABA 1 – DASHBOARD RESUMO
# ===========================================================
ws1 = wb.active
ws1.title = "Dashboard"
ws1.sheet_view.showGridLines = False
ws1.column_dimensions['A'].width = 3

# Título
ws1.merge_cells('B1:P1')
c = ws1['B1']
c.value = "ANÁLISE DE FORNECEDORES E MATERIAIS – RENTABILIDADE E GIRO DE ESTOQUE"
c.font = Font(bold=True, size=16, color=COR_BRANCO, name='Calibri')
c.fill = PatternFill("solid", fgColor=COR_TITULO)
c.alignment = Alignment(horizontal='center', vertical='center')
ws1.row_dimensions[1].height = 36

ws1.merge_cells('B2:P2')
ws1['B2'].value = f"Gerado em: {pd.Timestamp.today().strftime('%d/%m/%Y %H:%M')}   |   Base: DEVSYS_DATABASE.GDB"
ws1['B2'].font = Font(size=9, color='666666', name='Calibri')
ws1['B2'].alignment = Alignment(horizontal='center', vertical='center')
ws1.row_dimensions[2].height = 20

ws1.row_dimensions[3].height = 10

# ---- KPIs ----
kpis = [
    ("Produtos Ativos",    f"{len(df):,}",                      COR_HEADER),
    ("Valor Total Estoque",f"R$ {df['VL_ESTOQUE'].sum():,.0f}", "1F5C8B"),
    ("Estoque Parado",     f"R$ {df[df['QTD_VENDIDA']==0]['VL_ESTOQUE'].sum():,.0f}", COR_VERMELHO),
    ("Receita Total",      f"R$ {df['VL_VENDIDO'].sum():,.0f}", COR_VERDE),
    ("Lucro Bruto",        f"R$ {df['LUCRO_BRUTO'].sum():,.0f}","188038"),
    ("Margem Média",       f"{(df[df['VL_VENDIDO']>0]['MARGEM_REAL'].mean()):.1f}%",  COR_LARANJA),
]
col_start = 2
for i, (label, valor, cor) in enumerate(kpis):
    cs = col_start + i * 2
    ce = cs + 1
    ws1.merge_cells(start_row=4, start_column=cs, end_row=4, end_column=ce)
    ws1.merge_cells(start_row=5, start_column=cs, end_row=5, end_column=ce)
    ws1.merge_cells(start_row=6, start_column=cs, end_row=6, end_column=ce)
    c_lab = ws1.cell(row=4, column=cs, value=label)
    c_lab.font = Font(size=9, color=COR_BRANCO, bold=True, name='Calibri')
    c_lab.fill = PatternFill("solid", fgColor=cor)
    c_lab.alignment = Alignment(horizontal='center', vertical='center')
    ws1.row_dimensions[4].height = 22
    c_val = ws1.cell(row=5, column=cs, value=valor)
    c_val.font = Font(size=14, color=cor, bold=True, name='Calibri')
    c_val.alignment = Alignment(horizontal='center', vertical='center')
    ws1.row_dimensions[5].height = 30
    ws1.row_dimensions[6].height = 6

ws1.row_dimensions[7].height = 12

# ---- Tabela por classificação ----
classif = df.groupby('CLASSIFICACAO').agg(
    QTD=('REF_PROD','count'),
    VL_EST=('VL_ESTOQUE','sum'),
    RECEITA=('VL_VENDIDO','sum'),
    LUCRO=('LUCRO_BRUTO','sum'),
    SCORE=('SCORE_PROBLEMA','mean')
).reset_index().sort_values('VL_EST', ascending=False)

cores_class = {
    'SEM VENDA / ESTOQUE PARADO':       COR_VERMELHO,
    'BAIXO GIRO (>180d sem venda)':     'C55A11',
    'MARGEM CRÍTICA (<5%)':             'C00000',
    'EXCESSO DE ESTOQUE (>12 meses)':   'F4B942',
    'ESTOQUE ALTO (>6 meses)':          COR_AMARELO,
    'BAIXA MARGEM (<15%)':              'ED7D31',
    'SEM VENDA / SEM ESTOQUE':          '7F7F7F',
    'NORMAL':                           COR_VERDE,
}

row = 8
hdr(ws1, row, 2, "Classificação", bg=COR_HEADER2, size=9)
hdr(ws1, row, 3, "Produtos", bg=COR_HEADER2, size=9)
hdr(ws1, row, 4, "Vlr Estoque", bg=COR_HEADER2, size=9)
hdr(ws1, row, 5, "Receita", bg=COR_HEADER2, size=9)
hdr(ws1, row, 6, "Lucro Bruto", bg=COR_HEADER2, size=9)
hdr(ws1, row, 7, "% do Estoque Total", bg=COR_HEADER2, size=9)
ws1.row_dimensions[row].height = 22
ws1.column_dimensions['B'].width = 32
ws1.column_dimensions['C'].width = 10
ws1.column_dimensions['D'].width = 14
ws1.column_dimensions['E'].width = 14
ws1.column_dimensions['F'].width = 14
ws1.column_dimensions['G'].width = 18

total_est = df['VL_ESTOQUE'].sum()
for _, r in classif.iterrows():
    row += 1
    cor = cores_class.get(r['CLASSIFICACAO'], COR_CINZA)
    ws1.row_dimensions[row].height = 18
    cel(ws1, row, 2, r['CLASSIFICACAO'], bold=True, fg=COR_BRANCO, bg=cor, align='left')
    cel(ws1, row, 3, int(r['QTD']), bg='F8F8F8')
    cel(ws1, row, 4, r['VL_EST'], fmt='R$ #,##0.00', bg='F8F8F8')
    cel(ws1, row, 5, r['RECEITA'], fmt='R$ #,##0.00', bg='F8F8F8')
    cel(ws1, row, 6, r['LUCRO'], fmt='R$ #,##0.00', bg='F8F8F8')
    pct = r['VL_EST'] / total_est if total_est else 0
    cel(ws1, row, 7, pct, fmt='0.0%', bg='F8F8F8')

# ============================================================
# ABA 2 – RANKING FORNECEDORES PROBLEMÁTICOS
# ============================================================
ws2 = wb.create_sheet("Ranking Fornecedores")
ws2.sheet_view.showGridLines = False
ws2.column_dimensions['A'].width = 3

ws2.merge_cells('B1:M1')
ws2['B1'].value = "RANKING DE FORNECEDORES – MAIOR IMPACTO NEGATIVO"
ws2['B1'].font = Font(bold=True, size=14, color=COR_BRANCO, name='Calibri')
ws2['B1'].fill = PatternFill("solid", fgColor=COR_TITULO)
ws2['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws2.row_dimensions[1].height = 32

df_forn = df.copy()
df_forn['FORNECEDOR'] = df_forn['FORNECEDOR'].fillna('(SEM FORNECEDOR)')

forn_agg = df_forn.groupby('FORNECEDOR').agg(
    TOT_PROD=('REF_PROD','count'),
    PROD_PARADO=('REF_PROD', lambda x: ((df_forn.loc[x.index,'QTD_VENDIDA']==0) & (df_forn.loc[x.index,'ESTOQUE_ATUAL']>0)).sum()),
    PROD_BAIXO_GIRO=('DIAS_SEM_VENDA', lambda x: (x > 180).sum()),
    VL_EST_PARADO=('VL_ESTOQUE', lambda x: x[df_forn.loc[x.index,'QTD_VENDIDA']==0].sum()),
    VL_EST_TOTAL=('VL_ESTOQUE','sum'),
    RECEITA=('VL_VENDIDO','sum'),
    LUCRO=('LUCRO_BRUTO','sum'),
    SCORE_MED=('SCORE_PROBLEMA','mean'),
).reset_index()

forn_agg['MARGEM_FORN'] = np.where(forn_agg['RECEITA']>0,
    forn_agg['LUCRO']/forn_agg['RECEITA']*100, 0)
forn_agg['PCT_PARADO'] = np.where(forn_agg['VL_EST_TOTAL']>0,
    forn_agg['VL_EST_PARADO']/forn_agg['VL_EST_TOTAL']*100, 0)

# Score composto para ranking
forn_agg['SCORE_FORN'] = (
    forn_agg['VL_EST_PARADO'] * 0.4 +
    forn_agg['PROD_PARADO'] * 50 +
    forn_agg['SCORE_MED'] * 100
)

top_forn = forn_agg[forn_agg['VL_EST_TOTAL'] > 0].sort_values('SCORE_FORN', ascending=False).head(50)

colunas_forn = [
    ('B', 30, 'FORNECEDOR', 'Fornecedor', None, 'left'),
    ('C', 11, 'TOT_PROD', 'Total\nProdutos', None, 'center'),
    ('D', 13, 'PROD_PARADO', 'Produtos\nParados', None, 'center'),
    ('E', 13, 'PROD_BAIXO_GIRO', 'Baixo\nGiro', None, 'center'),
    ('F', 16, 'VL_EST_PARADO', 'Valor Est.\nParado (R$)', 'R$ #,##0.00', 'right'),
    ('G', 16, 'VL_EST_TOTAL', 'Valor Est.\nTotal (R$)', 'R$ #,##0.00', 'right'),
    ('H', 12, 'PCT_PARADO', '% Est.\nParado', '0.0%', 'center'),
    ('I', 16, 'RECEITA', 'Receita (R$)', 'R$ #,##0.00', 'right'),
    ('J', 16, 'LUCRO', 'Lucro Bruto (R$)', 'R$ #,##0.00', 'right'),
    ('K', 11, 'MARGEM_FORN', 'Margem\n(%)', '0.0%', 'center'),
    ('L', 13, 'SCORE_MED', 'Score\nMédio', '0.0', 'center'),
]

row = 2
ws2.row_dimensions[row].height = 8

row = 3
ws2.row_dimensions[row].height = 30
for i, (col_l, width, field, label, fmt, align) in enumerate(colunas_forn):
    col_idx = ord(col_l) - ord('A') + 1
    ws2.column_dimensions[col_l].width = width
    hdr(ws2, row, col_idx, label, bg=COR_HEADER2, size=9, wrap=True)

for rank_i, (_, r) in enumerate(top_forn.iterrows()):
    row += 1
    ws2.row_dimensions[row].height = 18
    bg = 'F2F2F2' if rank_i % 2 == 0 else COR_BRANCO
    for i, (col_l, width, field, label, fmt, align) in enumerate(colunas_forn):
        col_idx = ord(col_l) - ord('A') + 1
        val = r[field]
        if fmt == '0.0%':
            val = val / 100 if val > 1 else val
        c = cel(ws2, row, col_idx, val, fmt=fmt, bg=bg, align=align)
        # Colorir % estoque parado
        if field == 'PCT_PARADO' and r['PCT_PARADO'] > 50:
            c.font = Font(bold=True, color=COR_VERMELHO, size=10, name='Calibri')
        elif field == 'MARGEM_FORN' and r['MARGEM_FORN'] < 15:
            c.font = Font(bold=True, color=COR_VERMELHO, size=10, name='Calibri')

# Formatação condicional
from openpyxl.formatting.rule import ColorScaleRule
ws2.conditional_formatting.add(
    f"F4:F{row}",
    ColorScaleRule(start_type='min', start_color='FFFFFF',
                   end_type='max', end_color='C00000'))

# ============================================================
# ABA 3 – PRODUTOS SEM VENDA / ESTOQUE PARADO
# ============================================================
ws3 = wb.create_sheet("Estoque Parado")
ws3.sheet_view.showGridLines = False
ws3.column_dimensions['A'].width = 3

ws3.merge_cells('B1:L1')
ws3['B1'].value = "PRODUTOS SEM VENDA COM ESTOQUE PARADO"
ws3['B1'].font = Font(bold=True, size=14, color=COR_BRANCO, name='Calibri')
ws3['B1'].fill = PatternFill("solid", fgColor=COR_VERMELHO)
ws3['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws3.row_dimensions[1].height = 32

df_parado = df[(df['QTD_VENDIDA'] == 0) & (df['ESTOQUE_ATUAL'] > 0)].copy()
df_parado = df_parado.sort_values('VL_ESTOQUE', ascending=False)
df_parado['FORNECEDOR'] = df_parado['FORNECEDOR'].fillna('(SEM FORNECEDOR)')

colunas_par = [
    ('B', 14, 'CODIGO', 'Código', None, 'center'),
    ('C', 38, 'NOME', 'Produto', None, 'left'),
    ('D', 25, 'FORNECEDOR', 'Fornecedor', None, 'left'),
    ('E', 18, 'GRUPO', 'Grupo', None, 'left'),
    ('F', 13, 'ESTOQUE_ATUAL', 'Estoque\nAtual', '0.00', 'center'),
    ('G', 14, 'PRECO_CUSTO', 'Preço\nCusto (R$)', 'R$ #,##0.00', 'right'),
    ('H', 16, 'VL_ESTOQUE', 'Valor\nEstoque (R$)', 'R$ #,##0.00', 'right'),
    ('I', 14, 'MARGEM', 'Margem\nCadastro (%)', '0.0', 'center'),
    ('J', 16, 'DIAS_SEM_ENTRADA', 'Dias Sem\nEntrada', '0', 'center'),
    ('K', 16, 'DIAS_SEM_VENDA', 'Dias Sem\nVenda', '0', 'center'),
]

row = 3
ws3.row_dimensions[row].height = 30
for col_l, width, field, label, fmt, align in colunas_par:
    col_idx = ord(col_l) - ord('A') + 1
    ws3.column_dimensions[col_l].width = width
    hdr(ws3, row, col_idx, label, bg=COR_VERMELHO, size=9, wrap=True)

for rank_i, (_, r) in enumerate(df_parado.iterrows()):
    row += 1
    ws3.row_dimensions[row].height = 16
    bg = 'FFF2F2' if rank_i % 2 == 0 else COR_BRANCO
    for col_l, width, field, label, fmt, align in colunas_par:
        col_idx = ord(col_l) - ord('A') + 1
        val = r[field]
        if field == 'DIAS_SEM_VENDA' and val == 9999: val = 'Nunca'
        if field == 'DIAS_SEM_ENTRADA' and val == 9999: val = 'Nunca'
        cel(ws3, row, col_idx, val, fmt=fmt, bg=bg, align=align)

# Total
row += 1
ws3.row_dimensions[row].height = 20
hdr(ws3, row, 2, f"TOTAL: {len(df_parado):,} produtos", bg=COR_HEADER2, size=9, align='left')
cel(ws3, row, 6, df_parado['ESTOQUE_ATUAL'].sum(), fmt='0.00', bg=COR_CINZA, bold=True)
cel(ws3, row, 8, df_parado['VL_ESTOQUE'].sum(), fmt='R$ #,##0.00', bg=COR_CINZA, bold=True)

# ============================================================
# ABA 4 – BAIXO GIRO
# ============================================================
ws4 = wb.create_sheet("Baixo Giro")
ws4.sheet_view.showGridLines = False
ws4.column_dimensions['A'].width = 3

ws4.merge_cells('B1:N1')
ws4['B1'].value = "PRODUTOS COM BAIXO GIRO – MAIS DE 180 DIAS SEM VENDA"
ws4['B1'].font = Font(bold=True, size=14, color=COR_BRANCO, name='Calibri')
ws4['B1'].fill = PatternFill("solid", fgColor='C55A11')
ws4['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws4.row_dimensions[1].height = 32

df_giro = df[(df['DIAS_SEM_VENDA'] > 180) & (df['QTD_VENDIDA'] > 0)].copy()
df_giro['FORNECEDOR'] = df_giro['FORNECEDOR'].fillna('(SEM FORNECEDOR)')
df_giro = df_giro.sort_values(['VL_ESTOQUE'], ascending=False)

colunas_giro = [
    ('B', 14, 'CODIGO', 'Código', None, 'center'),
    ('C', 38, 'NOME', 'Produto', None, 'left'),
    ('D', 25, 'FORNECEDOR', 'Fornecedor', None, 'left'),
    ('E', 13, 'ESTOQUE_ATUAL', 'Estoque\nAtual', '0.00', 'center'),
    ('F', 14, 'PRECO_CUSTO', 'Custo (R$)', 'R$ #,##0.00', 'right'),
    ('G', 14, 'VL_ESTOQUE', 'Vlr Est (R$)', 'R$ #,##0.00', 'right'),
    ('H', 13, 'QTD_VENDIDA', 'Qtd\nVendida', '0.0', 'center'),
    ('I', 14, 'VL_VENDIDO', 'Receita (R$)', 'R$ #,##0.00', 'right'),
    ('J', 12, 'MARGEM_REAL', 'Margem\nReal (%)', '0.0', 'center'),
    ('K', 16, 'COBERTURA_MESES', 'Cobertura\n(meses)', '0.0', 'center'),
    ('L', 16, 'DIAS_SEM_VENDA', 'Dias Sem\nVenda', '0', 'center'),
    ('M', 12, 'GIRO_ESTOQUE', 'Giro\nEstoque', '0.00', 'center'),
]

row = 3
ws4.row_dimensions[row].height = 30
for col_l, width, field, label, fmt, align in colunas_giro:
    col_idx = ord(col_l) - ord('A') + 1
    ws4.column_dimensions[col_l].width = width
    hdr(ws4, row, col_idx, label, bg='C55A11', size=9, wrap=True)

for rank_i, (_, r) in enumerate(df_giro.iterrows()):
    row += 1
    ws4.row_dimensions[row].height = 16
    bg = 'FFF5EE' if rank_i % 2 == 0 else COR_BRANCO
    for col_l, width, field, label, fmt, align in colunas_giro:
        col_idx = ord(col_l) - ord('A') + 1
        val = r[field]
        c = cel(ws4, row, col_idx, val, fmt=fmt, bg=bg, align=align)
        if field == 'MARGEM_REAL' and val < 10:
            c.font = Font(bold=True, color=COR_VERMELHO, size=10, name='Calibri')
        if field == 'COBERTURA_MESES' and val > 12:
            c.font = Font(bold=True, color=COR_VERMELHO, size=10, name='Calibri')

# ============================================================
# ABA 5 – EXCESSO DE ESTOQUE
# ============================================================
ws5 = wb.create_sheet("Excesso Estoque")
ws5.sheet_view.showGridLines = False
ws5.column_dimensions['A'].width = 3

ws5.merge_cells('B1:M1')
ws5['B1'].value = "PRODUTOS COM EXCESSO DE ESTOQUE (COBERTURA > 6 MESES)"
ws5['B1'].font = Font(bold=True, size=14, color=COR_BRANCO, name='Calibri')
ws5['B1'].fill = PatternFill("solid", fgColor=COR_LARANJA)
ws5['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws5.row_dimensions[1].height = 32

df_exc = df[(df['COBERTURA_MESES'] > 6) & (df['QTD_VENDIDA'] > 0) & (df['ESTOQUE_ATUAL'] > 0)].copy()
df_exc['FORNECEDOR'] = df_exc['FORNECEDOR'].fillna('(SEM FORNECEDOR)')
df_exc = df_exc.sort_values('VL_ESTOQUE', ascending=False)

colunas_exc = [
    ('B', 14, 'CODIGO', 'Código', None, 'center'),
    ('C', 38, 'NOME', 'Produto', None, 'left'),
    ('D', 25, 'FORNECEDOR', 'Fornecedor', None, 'left'),
    ('E', 13, 'ESTOQUE_ATUAL', 'Estoque\nAtual', '0.00', 'center'),
    ('F', 14, 'VL_ESTOQUE', 'Vlr Est (R$)', 'R$ #,##0.00', 'right'),
    ('G', 13, 'QTD_VENDIDA', 'Qtd\nVendida', '0.0', 'center'),
    ('H', 14, 'VL_VENDIDO', 'Receita (R$)', 'R$ #,##0.00', 'right'),
    ('I', 13, 'MARGEM_REAL', 'Margem\nReal (%)', '0.0', 'center'),
    ('J', 16, 'COBERTURA_MESES', 'Cobertura\n(meses)', '0.0', 'center'),
    ('K', 13, 'GIRO_ESTOQUE', 'Giro\nEstoque', '0.00', 'center'),
]

row = 3
ws5.row_dimensions[row].height = 30
for col_l, width, field, label, fmt, align in colunas_exc:
    col_idx = ord(col_l) - ord('A') + 1
    ws5.column_dimensions[col_l].width = width
    hdr(ws5, row, col_idx, label, bg=COR_LARANJA, size=9, wrap=True)

for rank_i, (_, r) in enumerate(df_exc.iterrows()):
    row += 1
    ws5.row_dimensions[row].height = 16
    bg = 'FFFBF0' if rank_i % 2 == 0 else COR_BRANCO
    cob = r['COBERTURA_MESES']
    for col_l, width, field, label, fmt, align in colunas_exc:
        col_idx = ord(col_l) - ord('A') + 1
        val = r[field]
        c = cel(ws5, row, col_idx, val, fmt=fmt, bg=bg, align=align)
        if field == 'COBERTURA_MESES':
            if cob > 24: c.font = Font(bold=True, color=COR_VERMELHO, size=10, name='Calibri')
            elif cob > 12: c.font = Font(bold=True, color='C55A11', size=10, name='Calibri')

# ============================================================
# ABA 6 – ANÁLISE POR GRUPO
# ============================================================
ws6 = wb.create_sheet("Por Grupo")
ws6.sheet_view.showGridLines = False
ws6.column_dimensions['A'].width = 3

ws6.merge_cells('B1:K1')
ws6['B1'].value = "ANÁLISE DE RENTABILIDADE E GIRO POR GRUPO DE PRODUTO"
ws6['B1'].font = Font(bold=True, size=14, color=COR_BRANCO, name='Calibri')
ws6['B1'].fill = PatternFill("solid", fgColor=COR_TITULO)
ws6['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws6.row_dimensions[1].height = 32

df_g = df.copy()
df_g['GRUPO'] = df_g['GRUPO'].fillna('(SEM GRUPO)')
grupo_agg = df_g.groupby('GRUPO').agg(
    PROD=('REF_PROD','count'),
    PARADO=('REF_PROD', lambda x: ((df_g.loc[x.index,'QTD_VENDIDA']==0) & (df_g.loc[x.index,'ESTOQUE_ATUAL']>0)).sum()),
    VL_EST=('VL_ESTOQUE','sum'),
    RECEITA=('VL_VENDIDO','sum'),
    LUCRO=('LUCRO_BRUTO','sum'),
    GIRO_MED=('GIRO_ESTOQUE','mean'),
).reset_index()
grupo_agg['MARGEM'] = np.where(grupo_agg['RECEITA']>0,
    grupo_agg['LUCRO']/grupo_agg['RECEITA']*100, 0)
grupo_agg = grupo_agg.sort_values('VL_EST', ascending=False)

colunas_grupo = [
    ('B', 30, 'GRUPO', 'Grupo', None, 'left'),
    ('C', 11, 'PROD', 'Produtos', None, 'center'),
    ('D', 11, 'PARADO', 'Parados', None, 'center'),
    ('E', 16, 'VL_EST', 'Vlr Estoque (R$)', 'R$ #,##0.00', 'right'),
    ('F', 16, 'RECEITA', 'Receita (R$)', 'R$ #,##0.00', 'right'),
    ('G', 16, 'LUCRO', 'Lucro Bruto (R$)', 'R$ #,##0.00', 'right'),
    ('H', 12, 'MARGEM', 'Margem (%)', '0.0', 'center'),
    ('I', 12, 'GIRO_MED', 'Giro Médio', '0.00', 'center'),
]

row = 3
ws6.row_dimensions[row].height = 22
for col_l, width, field, label, fmt, align in colunas_grupo:
    col_idx = ord(col_l) - ord('A') + 1
    ws6.column_dimensions[col_l].width = width
    hdr(ws6, row, col_idx, label, bg=COR_HEADER2, size=9)

for rank_i, (_, r) in enumerate(grupo_agg.iterrows()):
    row += 1
    ws6.row_dimensions[row].height = 16
    bg = 'F2F6FB' if rank_i % 2 == 0 else COR_BRANCO
    for col_l, width, field, label, fmt, align in colunas_grupo:
        col_idx = ord(col_l) - ord('A') + 1
        val = r[field]
        c = cel(ws6, row, col_idx, val, fmt=fmt, bg=bg, align=align)
        if field == 'MARGEM' and val < 15:
            c.font = Font(bold=True, color=COR_VERMELHO, size=10, name='Calibri')

# ============================================================
# ABA 7 – GRÁFICOS (usando matplotlib)
# ============================================================
ws_graf = wb.create_sheet("Gráficos")
ws_graf.sheet_view.showGridLines = False
ws_graf.merge_cells('A1:Z1')
ws_graf['A1'].value = "PAINÉIS GRÁFICOS – ANÁLISE DE ESTOQUE E RENTABILIDADE"
ws_graf['A1'].font = Font(bold=True, size=14, color=COR_BRANCO, name='Calibri')
ws_graf['A1'].fill = PatternFill("solid", fgColor=COR_TITULO)
ws_graf['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws_graf.row_dimensions[1].height = 32

def img_to_openpyxl(fig):
    buf = io.BytesIO()
    fig.savefig(buf, format='png', dpi=150, bbox_inches='tight')
    buf.seek(0)
    return openpyxl.drawing.image.Image(buf)

# Gráfico 1: Distribuição por classificação
fig1, ax1 = plt.subplots(figsize=(9, 5))
classif_sort = df['CLASSIFICACAO'].value_counts()
colors_map = {
    'SEM VENDA / ESTOQUE PARADO':       '#C00000',
    'BAIXO GIRO (>180d sem venda)':     '#C55A11',
    'MARGEM CRÍTICA (<5%)':             '#FF0000',
    'EXCESSO DE ESTOQUE (>12 meses)':   '#ED7D31',
    'ESTOQUE ALTO (>6 meses)':          '#FFD966',
    'BAIXA MARGEM (<15%)':              '#F4B942',
    'SEM VENDA / SEM ESTOQUE':          '#7F7F7F',
    'NORMAL':                           '#70AD47',
}
colors = [colors_map.get(c, '#AAAAAA') for c in classif_sort.index]
bars = ax1.barh(classif_sort.index, classif_sort.values, color=colors, height=0.6)
ax1.set_xlabel('Quantidade de Produtos', fontsize=11)
ax1.set_title('Distribuição por Classificação de Risco', fontsize=13, fontweight='bold')
for bar, val in zip(bars, classif_sort.values):
    ax1.text(bar.get_width() + 10, bar.get_y() + bar.get_height()/2, f'{val:,}',
             va='center', fontsize=9)
ax1.invert_yaxis()
ax1.set_facecolor('#F8F8FF')
fig1.patch.set_facecolor('#FFFFFF')
fig1.tight_layout()
img1 = img_to_openpyxl(fig1)
img1.anchor = 'B3'
img1.width = 680
img1.height = 370
ws_graf.add_image(img1)
plt.close(fig1)

# Gráfico 2: Top 15 fornecedores por valor estoque parado
top15_forn = forn_agg[forn_agg['VL_EST_PARADO']>0].nlargest(15, 'VL_EST_PARADO')
fig2, ax2 = plt.subplots(figsize=(9, 5))
bars2 = ax2.barh(top15_forn['FORNECEDOR'].str[:35], top15_forn['VL_EST_PARADO'],
                  color='#C00000', height=0.6, alpha=0.85)
ax2.set_xlabel('Valor em Estoque Parado (R$)', fontsize=11)
ax2.set_title('Top 15 Fornecedores – Maior Valor de Estoque Parado', fontsize=13, fontweight='bold')
for bar, val in zip(bars2, top15_forn['VL_EST_PARADO']):
    ax2.text(bar.get_width() + 100, bar.get_y() + bar.get_height()/2,
             f'R$ {val:,.0f}', va='center', fontsize=8)
ax2.invert_yaxis()
ax2.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'R$ {x:,.0f}'))
ax2.set_facecolor('#F8F8FF')
fig2.patch.set_facecolor('#FFFFFF')
fig2.tight_layout()
img2 = img_to_openpyxl(fig2)
img2.anchor = 'B27'
img2.width = 680
img2.height = 370
ws_graf.add_image(img2)
plt.close(fig2)

# Gráfico 3: Valor de estoque por classificação (pizza)
fig3, ax3 = plt.subplots(figsize=(7, 5))
est_class = df.groupby('CLASSIFICACAO')['VL_ESTOQUE'].sum()
est_class = est_class[est_class > 0].sort_values(ascending=False)
colors3 = [colors_map.get(c, '#AAAAAA') for c in est_class.index]
wedges, texts, autotexts = ax3.pie(
    est_class.values, labels=None,
    autopct='%1.1f%%', colors=colors3, startangle=90,
    wedgeprops=dict(edgecolor='white', linewidth=1.5))
ax3.legend(wedges, [f"{c[:30]} (R${v:,.0f})" for c, v in zip(est_class.index, est_class.values)],
           loc='lower center', bbox_to_anchor=(0.5, -0.25), fontsize=7, ncol=1)
ax3.set_title('Composição do Estoque por Classificação (R$)', fontsize=12, fontweight='bold')
fig3.patch.set_facecolor('#FFFFFF')
fig3.tight_layout()
img3 = img_to_openpyxl(fig3)
img3.anchor = 'N3'
img3.width = 520
img3.height = 420
ws_graf.add_image(img3)
plt.close(fig3)

# Gráfico 4: Top grupos por estoque parado
top_grupo = df[df['QTD_VENDIDA']==0].groupby('GRUPO')['VL_ESTOQUE'].sum().nlargest(12).fillna(0)
fig4, ax4 = plt.subplots(figsize=(8, 4))
top_grupo.plot.bar(ax=ax4, color='#ED7D31', alpha=0.85, edgecolor='white')
ax4.set_title('Top 12 Grupos – Valor de Estoque sem Venda', fontsize=12, fontweight='bold')
ax4.set_ylabel('Valor (R$)', fontsize=10)
ax4.set_xlabel('')
ax4.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: f'R$ {x:,.0f}'))
ax4.set_facecolor('#F8F8FF')
plt.xticks(rotation=35, ha='right', fontsize=8)
fig4.patch.set_facecolor('#FFFFFF')
fig4.tight_layout()
img4 = img_to_openpyxl(fig4)
img4.anchor = 'N27'
img4.width = 600
img4.height = 340
ws_graf.add_image(img4)
plt.close(fig4)

# ============================================================
# Salvar
# ============================================================
output_path = "D:/Evolução categorias/.worktrees/comissao/Analise_Fornecedores_Materiais.xlsx"
wb.save(output_path)
print(f"\nRelatório salvo em:\n{output_path}")
print("\n=== RESUMO FINAL ===")
print(f"Produtos ativos:           {len(df):,}")
print(f"Estoque parado (sem venda):{len(df_parado):,} produtos | R$ {df_parado['VL_ESTOQUE'].sum():,.2f}")
print(f"Baixo giro (>180d):        {len(df_giro):,} produtos")
print(f"Excesso estoque (>6m):     {len(df_exc):,} produtos")
print(f"Total valor em estoque:    R$ {df['VL_ESTOQUE'].sum():,.2f}")
print(f"Receita total:             R$ {df['VL_VENDIDO'].sum():,.2f}")
print(f"Lucro bruto:               R$ {df['LUCRO_BRUTO'].sum():,.2f}")
print(f"Margem média geral:        {(df[df['VL_VENDIDO']>0]['MARGEM_REAL'].mean()):.1f}%")