import fdb
import pandas as pd
import numpy as np
from datetime import datetime

fdb.load_api("D:/fb_work/fb64/fbclient.dll")
con = fdb.connect(dsn='D:/Bd_dados/DEVSYS_DATABASE.GDB', user='SYSDBA', password='masterkey', charset='WIN1252')
cur = con.cursor()

print("Carregando produtos...")
cur.execute("""
    SELECT
        p.REFERENCIAL, p.CODIGO, p.NOME,
        p.PRECO_CUSTO, p.MARGEM, p.PRECO_VENDA,
        p.DT_ULTIMA_ENTRADA, p.DT_ULTIMA_SAIDA,
        p.REF_FORNECEDOR,
        f.NOME AS FORNECEDOR,
        e.ESTOQUE_ATUAL, e.ESTOQUE_MINIMO,
        g.NOME AS GRUPO
    FROM EST_PRODUTO p
    LEFT JOIN EST_FORNECEDOR f ON f.REFERENCIAL = p.REF_FORNECEDOR
    LEFT JOIN EST_ESTOQUE e ON e.REF_PRODUTO = p.REFERENCIAL
    LEFT JOIN EST_GRUPO g ON g.REFERENCIAL = p.REF_GRUPO
    WHERE p.STATUS = 1
""")
df_prod = pd.DataFrame(cur.fetchall(), columns=[
    'REF_PROD','CODIGO','NOME','PRECO_CUSTO','MARGEM','PRECO_VENDA',
    'DT_ULT_ENTRADA','DT_ULT_SAIDA','REF_FORN','FORNECEDOR',
    'ESTOQUE_ATUAL','ESTOQUE_MINIMO','GRUPO'])
print(f"  Produtos ativos: {len(df_prod)}")

print("Carregando vendas (FINALIZADO='Sim')...")
cur.execute("""
    SELECT sp.REF_PRODUTO,
           SUM(sp.QUANTIDADE)              AS QTD_VENDIDA,
           SUM(sp.VALOR)                   AS VL_VENDIDO,
           SUM(sp.QUANTIDADE*sp.PRECO_CUSTO) AS VL_CUSTO,
           COUNT(DISTINCT s.REFERENCIAL)   AS N_VENDAS
    FROM VEN_SAIDA_PRODUTOS sp
    JOIN VEN_SAIDA s ON s.REFERENCIAL = sp.REF_SAIDA
    WHERE s.FINALIZADO = 'Sim'
    GROUP BY sp.REF_PRODUTO
""")
df_vend = pd.DataFrame(cur.fetchall(), columns=[
    'REF_PROD','QTD_VENDIDA','VL_VENDIDO','VL_CUSTO','N_VENDAS'])
print(f"  Produtos c/ venda: {len(df_vend)}")

con.close()

# ---- MERGE ----
df = df_prod.merge(df_vend, on='REF_PROD', how='left')
df['QTD_VENDIDA'] = df['QTD_VENDIDA'].fillna(0)
df['VL_VENDIDO']  = df['VL_VENDIDO'].fillna(0)
df['VL_CUSTO']    = df['VL_CUSTO'].fillna(0)
df['N_VENDAS']    = df['N_VENDAS'].fillna(0)
for col in ['PRECO_CUSTO','MARGEM','PRECO_VENDA','ESTOQUE_ATUAL','ESTOQUE_MINIMO']:
    df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)

# ---- INDICADORES ----
today = pd.Timestamp.today()

df['DIAS_SEM_VENDA'] = df['DT_ULT_SAIDA'].apply(
    lambda d: (today - pd.Timestamp(d)).days if pd.notna(d) and d else 9999)
df['DIAS_SEM_ENTRADA'] = df['DT_ULT_ENTRADA'].apply(
    lambda d: (today - pd.Timestamp(d)).days if pd.notna(d) and d else 9999)

df['LUCRO_BRUTO'] = df['VL_VENDIDO'] - df['VL_CUSTO']
df['MARGEM_REAL'] = np.where(df['VL_VENDIDO'] > 0,
                             (df['LUCRO_BRUTO'] / df['VL_VENDIDO']) * 100, 0)
df['VL_ESTOQUE'] = df['ESTOQUE_ATUAL'] * df['PRECO_CUSTO']
df['GIRO_ESTOQUE'] = np.where(df['ESTOQUE_ATUAL'] > 0,
                               df['QTD_VENDIDA'] / df['ESTOQUE_ATUAL'], 0)
vendas_mes = df['QTD_VENDIDA'] / 16  # aprox meses de dados
df['COBERTURA_MESES'] = np.where(vendas_mes > 0,
                                  df['ESTOQUE_ATUAL'] / vendas_mes,
                                  np.where(df['ESTOQUE_ATUAL'] > 0, 9999, 0))

def score_problema(row):
    pts = 0
    if row['QTD_VENDIDA'] == 0:
        pts += 40
        if row['ESTOQUE_ATUAL'] > 0: pts += 20
    else:
        if row['DIAS_SEM_VENDA'] > 180: pts += 25
        elif row['DIAS_SEM_VENDA'] > 90: pts += 12
    if row['MARGEM_REAL'] < 5 and row['QTD_VENDIDA'] > 0: pts += 25
    elif row['MARGEM_REAL'] < 15 and row['QTD_VENDIDA'] > 0: pts += 12
    if row['COBERTURA_MESES'] > 12: pts += 20
    elif row['COBERTURA_MESES'] > 6: pts += 10
    if row['VL_ESTOQUE'] > 1000: pts += 10
    elif row['VL_ESTOQUE'] > 200: pts += 5
    return pts

df['SCORE_PROBLEMA'] = df.apply(score_problema, axis=1)

def classifica(row):
    if row['QTD_VENDIDA'] == 0 and row['ESTOQUE_ATUAL'] > 0:
        return 'SEM VENDA / ESTOQUE PARADO'
    elif row['QTD_VENDIDA'] == 0:
        return 'SEM VENDA / SEM ESTOQUE'
    elif row['DIAS_SEM_VENDA'] > 180:
        return 'BAIXO GIRO (>180d sem venda)'
    elif row['MARGEM_REAL'] < 5 and row['QTD_VENDIDA'] > 0:
        return 'MARGEM CRÍTICA (<5%)'
    elif row['MARGEM_REAL'] < 15 and row['QTD_VENDIDA'] > 0:
        return 'BAIXA MARGEM (<15%)'
    elif row['COBERTURA_MESES'] > 12:
        return 'EXCESSO DE ESTOQUE (>12 meses)'
    elif row['COBERTURA_MESES'] > 6:
        return 'ESTOQUE ALTO (>6 meses)'
    else:
        return 'NORMAL'

df['CLASSIFICACAO'] = df.apply(classifica, axis=1)

print("\n=== DISTRIBUIÇÃO POR CLASSIFICAÇÃO ===")
print(df['CLASSIFICACAO'].value_counts().to_string())

print(f"\n=== RESUMO FINANCEIRO ===")
print(f"Total produtos ativos:     {len(df):,}")
print(f"Total valor em estoque:    R$ {df['VL_ESTOQUE'].sum():,.2f}")
print(f"Estoque parado (sem venda):R$ {df[df['QTD_VENDIDA']==0]['VL_ESTOQUE'].sum():,.2f}")
print(f"Receita total:             R$ {df['VL_VENDIDO'].sum():,.2f}")
print(f"Lucro bruto:               R$ {df['LUCRO_BRUTO'].sum():,.2f}")

df.to_pickle("D:/Evolução categorias/.worktrees/comissao/analise_produtos.pkl")
print("\nDados salvos.")