"""
gerar_acesso.py
Extrai lista de vendedores + dados de vendas do banco Firebird
e gera 'vendedores_data.json' para uso pelo acesso_vendedor.html
"""
import os, json, unicodedata

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"))

print("Conectando ao banco de dados...")
con = fdb.connect(
    database=r"D:\Bd_dados\DEVSYS_DATABASE.gdb",
    sql_dialect=3, user="SYSDBA", password="masterkey"
)
cur = con.cursor()

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

# Subquery que garante UM único setor por produto (MIN evita dupla contagem)
# e LEFT JOIN captura produtos sem setor (COALESCE → 'SEM SETOR')
PRD_SETOR = """
    (SELECT ep.REFERENCIAL, COALESCE(MIN(es.NOME), 'SEM SETOR') as SETOR_NOME
     FROM EST_PRODUTO ep
     LEFT JOIN EST_SETOR es ON es.REFERENCIAL = ep.REF_SETOR
     GROUP BY ep.REFERENCIAL)
"""

def fix_nome(n):
    if not n:
        return ""
    n = n.strip()
    try:
        n = n.encode("latin-1").decode("utf-8")
    except Exception:
        pass
    return unicodedata.normalize("NFC", n)

def fix_setor(s):
    if not s:
        return "SEM SETOR"
    s2 = s.strip().rstrip("*").strip()
    try:
        s2 = s2.encode("latin-1").decode("utf-8")
    except Exception:
        pass
    s2 = unicodedata.normalize("NFC", s2)
    replacements = [
        ("MATERIAIS B",  "MATERIAIS BASICOS"),
        ("HIDR",         "HIDRAULICAS"),
        ("LOU",          "LOUCAS"),
        ("ILUMINA",      "ILUMINACAO"),
        ("ULTILIDADES",  "UTILIDADES DOMESTICAS"),
        ("UTILIDADES",   "UTILIDADES DOMESTICAS"),
    ]
    for prefix, replacement in replacements:
        if s2.upper().startswith(prefix):
            return replacement
    return s2

# ── 1. Lista de vendedores ─────────────────────────────────────────────────
print("Buscando lista de vendedores...")
cur.execute(f"""
    SELECT DISTINCT f.NOME
    FROM VEN_SAIDA s
    JOIN FIN_FUNCIONARIOS f ON f.REFERENCIAL = s.REF_FUN
    WHERE {FILTER} AND {YEARS}
    ORDER BY f.NOME
""")
vendedores = [fix_nome(r[0]) for r in cur.fetchall() if r[0]]

# ── 2. Totais mensais por vendedor ─────────────────────────────────────────
print("Buscando vendas por vendedor/mês...")
cur.execute(f"""
    SELECT f.NOME,
           EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA),
           SUM(sp.VALOR), COUNT(DISTINCT s.REFERENCIAL)
    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 f.NOME, EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA)
    ORDER BY EXTRACT(YEAR FROM s.DATA), EXTRACT(MONTH FROM s.DATA),
             SUM(sp.VALOR) DESC
""")
seller_month = [
    {"vendedor": fix_nome(r[0]), "mes": int(r[1]), "ano": int(r[2]),
     "total": round(float(r[3] or 0), 2), "qtd": int(r[4] or 0)}
    for r in cur.fetchall()
]

# ── 3. Totais mensais gerais (fonte de verdade para master) ────────────────
print("Buscando totais mensais gerais...")
cur.execute(f"""
    SELECT EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA),
           SUM(sp.VALOR), COUNT(DISTINCT s.REFERENCIAL)
    FROM VEN_SAIDA_PRODUTOS sp
    JOIN VEN_SAIDA s ON s.REFERENCIAL = sp.REF_SAIDA
    WHERE {FILTER} AND {YEARS}
    GROUP BY EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA)
    ORDER BY EXTRACT(YEAR FROM s.DATA), EXTRACT(MONTH FROM s.DATA)
""")
monthly = [
    {"mes": int(r[0]), "ano": int(r[1]),
     "total": round(float(r[2] or 0), 2), "qtd": int(r[3] or 0)}
    for r in cur.fetchall()
]

# ── 4. Vendas por vendedor / setor / mês  (query corrigida) ───────────────
print("Buscando vendas por setor/vendedor...")
cur.execute(f"""
    SELECT f.NOME, prd.SETOR_NOME,
           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
    LEFT JOIN {PRD_SETOR} prd ON prd.REFERENCIAL = sp.REF_PRODUTO
    WHERE {FILTER} AND {YEARS}
    GROUP BY f.NOME, prd.SETOR_NOME,
             EXTRACT(MONTH FROM s.DATA), EXTRACT(YEAR FROM s.DATA)
    ORDER BY EXTRACT(YEAR FROM s.DATA), EXTRACT(MONTH FROM s.DATA)
""")
seller_sector_month = [
    {"vendedor": fix_nome(r[0]), "setor": fix_setor(r[1]),
     "mes": int(r[2]), "ano": int(r[3]),
     "total": round(float(r[4] or 0), 2)}
    for r in cur.fetchall()
]

# ── 5. Setor acumulado por vendedor (query corrigida) ──────────────────────
cur.execute(f"""
    SELECT f.NOME, prd.SETOR_NOME, 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
    LEFT JOIN {PRD_SETOR} prd ON prd.REFERENCIAL = sp.REF_PRODUTO
    WHERE {FILTER} AND {YEARS}
    GROUP BY f.NOME, prd.SETOR_NOME
    ORDER BY SUM(sp.VALOR) DESC
""")
seller_sector = [
    {"vendedor": fix_nome(r[0]), "setor": fix_setor(r[1]),
     "total": round(float(r[2] or 0), 2)}
    for r in cur.fetchall()
]

con.close()
print("Banco de dados fechado.")

# ── Salva JSON ─────────────────────────────────────────────────────────────
out = {
    "vendedores":          vendedores,
    "seller_month":        seller_month,
    "monthly":             monthly,
    "seller_sector_month": seller_sector_month,
    "seller_sector":       seller_sector,
}

out_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "vendedores_data.json")
with open(out_path, "w", encoding="utf-8") as f:
    json.dump(out, f, ensure_ascii=False, separators=(",", ":"))

print(f"Dados salvos em: {out_path}")
print(f"  Vendedores : {len(vendedores)}")
print(f"  Registros  : {len(seller_month)}")

# ── Validação automática ───────────────────────────────────────────────────
print("\nValidando totais por mês...")
monthly_idx = {(r["mes"], r["ano"]): r["total"] for r in monthly}
ssm_idx = {}
for r in seller_sector_month:
    k = (r["mes"], r["ano"])
    ssm_idx[k] = ssm_idx.get(k, 0) + r["total"]

ok = True
for k in sorted(monthly_idx):
    diff = round(monthly_idx[k] - ssm_idx.get(k, 0), 2)
    if abs(diff) > 0.10:
        print(f"  AVISO {k[0]:02d}/{k[1]}: diferença R$ {diff:,.2f}")
        ok = False
if ok:
    print("  OK — totais conferem!")