import openpyxl
import os
from collections import Counter
from datetime import datetime

wb_path = r"c:\PROJETOS\integra-viman\database\te0070_100926_125400.xlsx"
wb = openpyxl.load_workbook(wb_path, data_only=True)
ws = wb.active

headers = [ws.cell(1, c).value for c in range(1, ws.max_column + 1)]
print("Headers:", headers)

col_doc = headers.index('Documento') + 1
col_tipo = headers.index('Tipo') + 1
col_emissao = headers.index('Emissao') + 1
col_vr_doc = headers.index('Vr.documento') + 1
col_vr_desc = headers.index('Vr.desconto') + 1
col_vr_liq = headers.index('Vr.liquido') + 1

total_doc = 0.0
total_desc = 0.0
total_liq = 0.0
dates = []
tipos = Counter()
docs = set()
nfs = set()

for r in range(2, ws.max_row + 1):
    doc_val = str(ws.cell(r, col_doc).value or '')
    tipo_val = ws.cell(r, col_tipo).value
    emissao_val = ws.cell(r, col_emissao).value
    vr_doc = float(ws.cell(r, col_vr_doc).value or 0.0)
    vr_desc = float(ws.cell(r, col_vr_desc).value or 0.0)
    vr_liq = float(ws.cell(r, col_vr_liq).value or 0.0)

    total_doc += vr_doc
    total_desc += vr_desc
    total_liq += vr_liq
    tipos[tipo_val] += 1
    docs.add(doc_val)

    # Extrai o numero da nota (antes da barra / ou formatado)
    clean_nf = doc_val.replace("'", "").split('/')[0].replace(".", "").strip()
    nfs.add(clean_nf)

    if emissao_val:
        dates.append(emissao_val)

print(f"Total Rows: {ws.max_row - 1}")
print(f"Unique Docs (parcelas): {len(docs)}")
print(f"Unique NFs: {len(nfs)}")
print(f"Tipos: {tipos}")
if dates:
    min_date = min(dates)
    max_date = max(dates)
    print(f"Period: {min_date} to {max_date}")

print(f"Total Vr.documento: R$ {total_doc:,.2f}")
print(f"Total Vr.desconto:  R$ {total_desc:,.2f}")
print(f"Total Vr.liquido:   R$ {total_liq:,.2f}")