import os
embed_path = r"D:\fb_work\fb64"
os.environ["PATH"] = embed_path + ";" + os.environ.get("PATH", "")
import fdb
fdb.load_api(os.path.join(embed_path, "fbembed.dll"))
con = fdb.connect(database=r"D:\Bd_dados\DEVSYS_DATABASE.gdb",
                  sql_dialect=3, user="SYSDBA", password="masterkey")
cur = con.cursor()

FILTER = "s.FINALIZADO = 'Sim' AND (s.ORCAMENTO IS NULL OR s.ORCAMENTO <> 'Sim')"
YEARS  = "EXTRACT(YEAR FROM s.DATA) BETWEEN 2024 AND 2026"

# Total por seller_month (sem setor)
cur.execute(f"""
    SELECT EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA), SUM(sp.VALOR)
    FROM VEN_SAIDA_PRODUTOS sp
    JOIN VEN_SAIDA s ON s.REFERENCIAL = sp.REF_SAIDA
    JOIN FIN_FUNCIONARIOS f ON f.REFERENCIAL = s.REF_FUN
    WHERE {FILTER} AND {YEARS}
    GROUP BY EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA)
    ORDER BY 2, 1
""")
sem_setor = {(int(r[0]),int(r[1])): float(r[2] or 0) for r in cur.fetchall()}

# Total por seller_sector_month (com setor — JOIN em EST_SETOR)
cur.execute(f"""
    SELECT EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA), SUM(sp.VALOR)
    FROM VEN_SAIDA_PRODUTOS sp
    JOIN VEN_SAIDA s ON s.REFERENCIAL = sp.REF_SAIDA
    JOIN FIN_FUNCIONARIOS f ON f.REFERENCIAL = s.REF_FUN
    JOIN EST_PRODUTO ep ON ep.REFERENCIAL = sp.REF_PRODUTO
    JOIN EST_SETOR es ON es.REFERENCIAL = ep.REF_SETOR
    WHERE {FILTER} AND {YEARS}
    GROUP BY EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA)
    ORDER BY 2, 1
""")
com_setor = {(int(r[0]),int(r[1])): float(r[2] or 0) for r in cur.fetchall()}

print("Mês/Ano  | seller_month      | seller_sector     | Diferença (perdida)")
print("-"*72)
for k in sorted(sem_setor):
    a = sem_setor[k]
    b = com_setor.get(k, 0)
    diff = a - b
    flag = " <<<" if abs(diff) > 0.01 else ""
    print(f"{k[0]:02d}/{k[1]} | R${a:>14,.2f}  | R${b:>14,.2f}  | R${diff:>10,.2f}{flag}")

# Produtos sem setor
cur.execute(f"""
    SELECT COUNT(*), SUM(sp.VALOR)
    FROM VEN_SAIDA_PRODUTOS sp
    JOIN VEN_SAIDA s ON s.REFERENCIAL = sp.REF_SAIDA
    JOIN EST_PRODUTO ep ON ep.REFERENCIAL = sp.REF_PRODUTO
    WHERE {FILTER} AND {YEARS}
    AND (ep.REF_SETOR IS NULL OR
         ep.REF_SETOR NOT IN (SELECT REFERENCIAL FROM EST_SETOR))
""")
r = cur.fetchone()
print(f"\nProdutos sem setor: {r[0]} itens | R$ {float(r[1] or 0):,.2f}")

con.close()