#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pandas as pd
import mysql.connector
import os
import unicodedata

# ========== CONFIGURAÇÃO ==========
EXCEL_PATH = r"C:\Produtividade_66e5001e-6d43-4ff1-bcb8-e32d1c37828d.xlsx"

DB_CONFIG = {
    "user": "root",
    "password": "",
    "host": "localhost",
    "database": "bihits",
}

START_DATE = "2025-04-01"
END_DATE   = "2025-04-17 23:59:59"

# ========== 1) LEITURA E LIMPEZA DO EXCEL ==========

df = pd.read_excel(EXCEL_PATH, dtype=str)

# Função que "slugifica" os nomes de coluna: tira acentos, pontuação e espaços
def slug(col):
    nf = unicodedata.normalize("NFKD", col)
    no_acc = "".join(c for c in nf if not unicodedata.combining(c))
    return (
        no_acc.strip()
        .lower()
        .replace(" ", "_")
        .replace("-", "_")
        .replace("/", "_")
        .replace("ç", "c")
    )

df.columns = [slug(c) for c in df.columns]

# Renomeia somente as métricas de interesse para nomes consistentes
df = df.rename(columns={
    "rn":            "RN_csv",
    "diarias":       "Diarias_csv",
    "aeb":           "AeB_csv",
    "diaria_pensao": "DiariaPensao_csv",
})

# Métricas que vamos comparar
metrics = ["RN_csv", "Diarias_csv", "AeB_csv", "DiariaPensao_csv"]

# Converte métricas principais para float
for m in metrics:
    if m not in df.columns:
        raise KeyError(f"Coluna esperada não encontrada no Excel: '{m}'")
    df[m] = (
        df[m]
        .str.replace(r"\.", "", regex=True)
        .str.replace(",", ".", regex=False)
        .astype(float)
    )

# Converte RN total da planilha
if "roomingnightcount" not in df.columns:
    raise KeyError("Coluna 'roomingnightcount' não encontrada.")
df["roomingnightcount"] = df["roomingnightcount"].astype(float)

# Converte campos de controle
segmentos_invalidos = [
    'Cortesia - agente de viagens', 'Cortesia - outros',
    'Fracionado - distrato', 'Fracionado - semana',
    'Uso da casa - funcionário', 'Uso da casa - proprietário',
    'Reserva Teste', 'Back to home - semana (fracionado)',
    'Cortesia - influencer', 'Fracionado - voucher cortesia',
    'Vazias', 'Viagens de inspeção - cortesia'
]

# Garante colunas necessárias
for col in ['dailytype', 'marketsegmentname']:
    if col not in df.columns:
        raise KeyError(f"Coluna esperada não encontrada: {col}")

# Calcula RN válidas na planilha
df["RN_csv_valida"] = df.apply(
    lambda row: row["roomingnightcount"]
    if row["dailytype"] == "Regular" and row["marketsegmentname"] not in segmentos_invalidos
    else 0,
    axis=1
)

# Agrega por hotel+empresa
df_pivot = (
    df
    .groupby(["hotel","empresa"], as_index=False)
    .agg({
        "roomingnightcount": "sum",
        "RN_csv_valida": "sum",
        "RN_csv": "sum",
        "Diarias_csv": "sum",
        "AeB_csv": "sum",
        "DiariaPensao_csv": "sum",
    })
    .rename(columns={"roomingnightcount": "RN_csv_total"})
)

# ========== 2) LEITURA DO MYSQL ==========

conn = mysql.connector.connect(**DB_CONFIG)

sql = f"""
SELECT
    propertyName AS hotel,
    companyName  AS empresa,

    SUM(roomingNightCount) AS RN_csv_total,
    SUM(
      CASE
        WHEN dailyType = 'Regular'
         AND marketSegmentName NOT IN (
           'Cortesia - agente de viagens','Cortesia - outros',
           'Fracionado - distrato','Fracionado - semana',
           'Uso da casa - funcionário','Uso da casa - proprietário',
           'Reserva Teste','Back to home - semana (fracionado)',
           'Cortesia - influencer','Fracionado - voucher cortesia',
           'Vazias','Viagens de inspeção - cortesia'
         )
        THEN roomingNightCount
        ELSE 0
      END
    ) AS RN_sql_valida,
    SUM(
      CASE
        WHEN dailyType = 'Regular'
         AND marketSegmentName NOT IN (
           'Cortesia - agente de viagens','Cortesia - outros',
           'Fracionado - distrato','Fracionado - semana',
           'Uso da casa - funcionário','Uso da casa - proprietário',
           'Reserva Teste','Back to home - semana (fracionado)',
           'Cortesia - influencer','Fracionado - voucher cortesia',
           'Vazias','Viagens de inspeção - cortesia'
         )
        THEN amount
        ELSE 0
      END
    ) AS Diarias_sql,
    SUM(
      CASE
        WHEN dailyType = 'Regular'
         AND marketSegmentName NOT IN (
           'Cortesia - agente de viagens','Cortesia - outros',
           'Fracionado - distrato','Fracionado - semana',
           'Uso da casa - funcionário','Uso da casa - proprietário',
           'Reserva Teste','Back to home - semana (fracionado)',
           'Cortesia - influencer','Fracionado - voucher cortesia',
           'Vazias','Viagens de inspeção - cortesia'
         )
        THEN mealPlanAmountPaxChd
        ELSE 0
      END
    ) AS AeB_sql,
    SUM(
      CASE
        WHEN dailyType = 'Regular'
         AND marketSegmentName NOT IN (
           'Cortesia - agente de viagens','Cortesia - outros',
           'Fracionado - distrato','Fracionado - semana',
           'Uso da casa - funcionário','Uso da casa - proprietário',
           'Reserva Teste','Back to home - semana (fracionado)',
           'Cortesia - influencer','Fracionado - voucher cortesia',
           'Vazias','Viagens de inspeção - cortesia'
         )
        THEN amount + mealPlanAmountPaxChd
        ELSE 0
      END
    ) AS DiariaPensao_sql
FROM RoomingNights
WHERE endOfDayDate BETWEEN '{START_DATE}' AND '{END_DATE}'
  AND reservationStatus <> 2
GROUP BY propertyName, companyName
ORDER BY propertyName, companyName
"""

df_sql = pd.read_sql(sql, conn)
conn.close()

# ========== 3) JUNÇÃO E CÁLCULO DAS DIFERENÇAS ==========

df_merge = pd.merge(
    df_pivot,
    df_sql,
    on=["hotel", "empresa"],
    how="outer"
).fillna(0)

# Calcula diferenças
df_merge["Diff_RN_valida"] = df_merge["RN_csv_valida"] - df_merge["RN_sql_valida"]
df_merge["Diff_Diarias"] = df_merge["Diarias_csv"] - df_merge["Diarias_sql"]
df_merge["Diff_AeB"] = df_merge["AeB_csv"] - df_merge["AeB_sql"]
df_merge["Diff_DiariaPensao"] = df_merge["DiariaPensao_csv"] - df_merge["DiariaPensao_sql"]

# Filtro de discrepâncias
diff_cols = ["Diff_RN_valida", "Diff_Diarias", "Diff_AeB", "Diff_DiariaPensao"]
df_diff = df_merge.loc[
    (df_merge[diff_cols] != 0).any(axis=1),
    ["hotel", "empresa"] + diff_cols
]

# ========== 4) SAÍDA FINAL ==========

print("\nDiscrepâncias encontradas:")
print(df_diff.to_string(index=False, float_format="%.2f"))

OUT = "discrepancias_com_rn_valida.csv"
df_diff.to_csv(OUT, sep=";", index=False, encoding="utf-8-sig")
print(f"\nArquivo gerado: {os.path.abspath(OUT)}")