[Cibersegurança] Validação de usuários

import pandas as pd
from pathlib import Path

# ================== PARÂMETROS ==================
BASE_DIR = Path(r"C:/automação python")       #caminho da pasta
ARQ1 = BASE_DIR / "arq1.xlsx"         #arquivo1
ARQ2 = BASE_DIR / "arq2.xlsx"         #arquivo2
SHEET1 = "Planilha1"
SHEET2 = "Planilha1"

# Nomes dos cabeçalhos da coluna H em cada arquivo:
COL_CPF_1 = "CPF"  # arq1.xlsx (coluna H)
COL_CPF_2 = "CPF"  # arq2.xlsx (coluna H)

ARQ_SAIDA = BASE_DIR / "comparacao_cpf.xlsx"

# ============== FUNÇÕES AUXILIARES ==============
def as_text(x):
    """Força leitura como texto, preservando NA."""
    if pd.isna(x):
        return pd.NA
    return str(x)

def ler_planilha_coluna_h_como_texto(caminho, sheet, nome_esperado_h):
    """
    Lê a planilha garantindo que a coluna H seja tratada como TEXTO.
    1) Tenta o cabeçalho (ex.: 'cpf' ou 'CPF');
    2) Se não achar, cai para a coluna H (8ª).
    """
    df = pd.read_excel(caminho, sheet_name=sheet, engine="openpyxl")

    # localizar coluna pelo cabeçalho (strip)
    cols_map = {c: str(c).strip() for c in df.columns}
    col_real = None
    for c, ct in cols_map.items():
        if ct == nome_esperado_h:
            col_real = c
            break

    if col_real is None:
        # fallback: coluna H
        if df.shape[1] >= 8:
            col_real = df.columns[7]  # H
            print(f"Aviso: no arquivo '{caminho.name}' não achei a coluna '{nome_esperado_h}'. "
                  f"Usando a coluna H: '{col_real}'.")
        else:
            raise ValueError(f"No arquivo '{caminho}', não encontrei a coluna '{nome_esperado_h}' "
                             f"e não há coluna H. Colunas: {list(df.columns)}")

    # Releitura forçando a coluna identificada como TEXTO
    df2 = pd.read_excel(
        caminho,
        sheet_name=sheet,
        engine="openpyxl",
        converters={col_real: as_text}
    )
    return df2, col_real

def compactar_colunas(df: pd.DataFrame) -> pd.DataFrame:
    """
    Remove:
      - colunas totalmente vazias (NaN ou apenas espaços),
      - colunas cujo cabeçalho começa com 'Unnamed' (típico de colunas vazias no Excel).
    """
    out = df.copy()

    # normaliza nomes (strip) e remove 'Unnamed'
    out.columns = [str(c).strip() if c is not None else "" for c in out.columns]
    cols_validas = [c for c in out.columns if not str(c).startswith("Unnamed")]
    out = out[cols_validas]

    # trata strings em branco como NA e remove colunas totalmente vazias
    out = out.replace(r"^\s*$", pd.NA, regex=True).dropna(axis=1, how="all")
    return out

def garantir_limite_excel(df: pd.DataFrame, nome: str, chave: str = "CPF_chave") -> pd.DataFrame:
    """
    Garante que o número de colunas não passe de 16.384 (limite do Excel).
    Se exceder, mantém a chave e trunca o restante.
    """
    MAX_COLS = 16384
    if df.shape[1] > MAX_COLS:
        outras = [c for c in df.columns if c != chave]
        keep = [chave] + outras[:MAX_COLS - 1]
        print(f"Aviso: '{nome}' tem {df.shape[1]} colunas; truncando para {MAX_COLS}.")
        return df[keep]
    return df

# ==================== LEITURA ====================
t1, col1 = ler_planilha_coluna_h_como_texto(ARQ1, SHEET1, COL_CPF_1)
t2, col2 = ler_planilha_coluna_h_como_texto(ARQ2, SHEET2, COL_CPF_2)

# Compactar colunas (remove vazias e 'Unnamed')
t1 = compactar_colunas(t1)
t2 = compactar_colunas(t2)

# Cria a chave BRUTA (sem normalização)
t1 = t1.copy()
t2 = t2.copy()
t1["CPF_chave"] = t1[col1]
t2["CPF_chave"] = t2[col2]

# Remove linhas sem chave
t1 = t1[~t1["CPF_chave"].isna()].reset_index(drop=True)
t2 = t2[~t2["CPF_chave"].isna()].reset_index(drop=True)

# DEDUP estritamente pela chave
t1_dedup = t1.drop_duplicates(subset=["CPF_chave"]).reset_index(drop=True)
t2_dedup = t2.drop_duplicates(subset=["CPF_chave"]).reset_index(drop=True)

# ================= COMPARAÇÃO ====================
cpfs_t1 = set(t1_dedup["CPF_chave"])
cpfs_t2 = set(t2_dedup["CPF_chave"])

cpfs_ambas = cpfs_t1 & cpfs_t2
cpfs_so_t1 = cpfs_t1 - cpfs_t2
cpfs_so_t2 = cpfs_t2 - cpfs_t1

# ========== MONTAR DATAFRAMES DE SAÍDA ==========
# Em ambas: TODAS as colunas de cada lado (sufixos para nomes iguais)
ambas = (
    pd.merge(
        t1_dedup, t2_dedup,
        on="CPF_chave",
        how="inner",
        suffixes=("_T1", "_T2")
    )
    .sort_values("CPF_chave")
    .reset_index(drop=True)
)
so_t1 = (
    t1_dedup[t1_dedup["CPF_chave"].isin(cpfs_so_t1)]
    .sort_values("CPF_chave")
    .reset_index(drop=True)
)
so_t2 = (
    t2_dedup[t2_dedup["CPF_chave"].isin(cpfs_so_t2)]
    .sort_values("CPF_chave")
    .reset_index(drop=True)
)

# Garante limite do Excel (após a limpeza, normalmente já fica OK)
ambas = garantir_limite_excel(ambas, "Em_ambas", "CPF_chave")
so_t1 = garantir_limite_excel(so_t1, "So_Tabela1", "CPF_chave")
so_t2 = garantir_limite_excel(so_t2, "So_Tabela_2", "CPF_chave")

# Resumo
resumo = pd.DataFrame({
    "Categoria": ["Em ambas", "Só na Tabela 1", "Só na Tabela 2", "Total distintos"],
    "Quantidade": [len(cpfs_ambas), len(cpfs_so_t1), len(cpfs_so_t2), len(cpfs_t1 | cpfs_t2)]
})

# =================== EXPORTAR ===================
with pd.ExcelWriter(ARQ_SAIDA, engine="openpyxl") as writer:
    resumo.to_excel(writer, sheet_name="Resumo", index=False)
    ambas.to_excel(writer, sheet_name="Em_ambas", index=False)
    so_t1.to_excel(writer, sheet_name="So_Tabela1", index=False)
    so_t2.to_excel(writer, sheet_name="So_Tabela_2", index=False)

    # Formatar CPF_chave como TEXTO em todas as abas de dados
    wb = writer.book

    def formatar_texto(sheet_name, col_name="CPF_chave"):
        ws = wb[sheet_name]
        headers = [cell.value for cell in ws[1]]
        if col_name in headers:
            col_idx = headers.index(col_name) + 1  # 1-based
            for row in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx):
                for cell in row:
                    cell.number_format = "@"

    for aba in ["Em_ambas", "So_Tabela1", "So_Tabela_2"]:
        formatar_texto(aba, "CPF_chave")

print(f"Concluído! Resultado salvo em: {ARQ_SAIDA}")