# normalize_inventory.py
# Requisitos: pandas, numpy, openpyxl (para leer .xlsx)
# pip install pandas numpy openpyxl

import re
import pandas as pd
import numpy as np
from pathlib import Path

# === Config ===
INPUT_PATH = Path("C:/Users/julio/OneDrive/Documentos/Trabajo/Ideas Frescas/Proyectos/Veredas/Datos/Base de datos proyectos competencia limpio.xlsx")
OUTPUT_DIR = Path("C:/Users/julio/OneDrive/Documentos/Trabajo/Ideas Frescas/Proyectos/Veredas/Datos")

# ---------- Helpers ----------

MONTH_MAP = {
    "ene": 1, "feb": 2, "mar": 3, "abr": 4, "may": 5, "jun": 6,
    "jul": 7, "ago": 8, "sep": 9, "oct": 10, "nov": 11, "dic": 12
}

def normalize_col(c):
    c = str(c).strip().lower()
    c = re.sub(r"\s+", "_", c)
    c = c.replace("__","_")
    accents = str.maketrans("áéíóúñ", "aeioun")
    c = c.translate(accents)
    return c

def parse_date_from_suffix(col):
    """
    De una columna 'ventas_abr_2024' regresa Timestamp('2024-04-01')
    """
    parts = col.split("_")
    if len(parts) >= 2:
        mon = parts[-2].strip().lower()
        yr = re.sub(r"\D", "", parts[-1])
        if mon in MONTH_MAP and yr.isdigit():
            return pd.Timestamp(int(yr), MONTH_MAP[mon], 1)
    return None

def coerce_numeric(x):
    if pd.isna(x):
        return np.nan
    if isinstance(x, (int, float, np.integer, np.floating)):
        return float(x)
    x = str(x).strip()
    if x in {"-", ""}:
        return np.nan
    x = x.replace("$", "").replace(",", "")
    try:
        return float(x)
    except:
        return np.nan

def melt_family(df, id_cols, value_cols, value_name, extra_process_date=True):
    """Unpivot genérico para familias como ventas_*, inventario_* ..."""
    if not value_cols:
        return pd.DataFrame(columns=id_cols + (["as_of_date"] if extra_process_date else []) + [value_name])
    sub = df[id_cols + value_cols].copy()
    m = sub.melt(id_vars=id_cols, value_vars=value_cols, var_name="column", value_name=value_name)
    if extra_process_date:
        m["as_of_date"] = m["column"].apply(parse_date_from_suffix)
    m[value_name] = m[value_name].apply(coerce_numeric)
    m = m.drop(columns=["column"])
    # Filtra filas totalmente vacías
    if "as_of_date" in m.columns:
        m = m[~(m[value_name].isna() & m["as_of_date"].isna())]
    else:
        m = m[~m[value_name].isna()]
    return m

# ---------- Read ----------

if not INPUT_PATH.exists():
    raise FileNotFoundError(f"No se encontró el archivo: {INPUT_PATH}. "
                            f"Colócalo junto a este script como 'inventario_limpio.xlsx'.")

raw = pd.read_excel(INPUT_PATH)
raw.columns = [normalize_col(c) for c in raw.columns]
df = raw.copy()

# ---------- Columnas mínimas esperadas ----------
mandatory = [
    "nombre_desarrollo","etapa","nombre_prototipo","numero_viviendas_planeadas",
    "medida_terreno_m2","fecha_inicio_preventa_ultima_etapa",
    "inicio_entrega_unidades","fecha_entrega_desarrollo_completo","meses_en_venta"
]
for col in mandatory:
    if col not in df.columns:
        df[col] = np.nan

        
# --- Limpiezas previas ---
# Limpia el nombre del desarrollo para que agrupe bien
df["nombre_desarrollo"] = df["nombre_desarrollo"].astype(str).str.strip()

# Convierte campos numéricos a float (maneja "-", "$", comas, etc.)
def to_num(s):
    return (pd.to_numeric(
        s.astype(str)
         .str.replace("$","", regex=False)
         .str.replace(",","", regex=False)
         .str.strip()
         .replace({"-": None, "": None}),
        errors="coerce"
    ))

df["numero_viviendas_planeadas"] = to_num(df.get("numero_viviendas_planeadas"))
df["meses_en_venta"] = to_num(df.get("meses_en_venta"))

# Convierte fechas a datetime (si hay texto mixto no truena)
for c in ["fecha_inicio_preventa_ultima_etapa","inicio_entrega_unidades","fecha_entrega_desarrollo_completo"]:
    if c in df.columns:
        df[c] = pd.to_datetime(df[c], errors="coerce", dayfirst=True)

# ---------- developments ----------
dev_fixed_cols = [
    "nombre_desarrollo","numero_viviendas_planeadas",
    "fecha_inicio_preventa_ultima_etapa","inicio_entrega_unidades",
    "fecha_entrega_desarrollo_completo","meses_en_venta"
]

# Agregamos con funciones seguras para tipos:
developments = (df[dev_fixed_cols]
                .groupby("nombre_desarrollo", as_index=False)
                .agg({
                    # toma el máximo numérico de las planeadas (útil si hay varias filas)
                    "numero_viviendas_planeadas": "max",
                    # toma la primera fecha válida que aparezca (puedes cambiar por 'min' si prefieres)
                    "fecha_inicio_preventa_ultima_etapa": "first",
                    "inicio_entrega_unidades": "first",
                    "fecha_entrega_desarrollo_completo": "first",
                    # meses en venta: máximo visto
                    "meses_en_venta": "max"
                }))

developments.insert(0, "development_id", range(1, len(developments)+1))


# ---------- stages ----------
stages = (df[["nombre_desarrollo","etapa"]]
          .drop_duplicates()
          .dropna(subset=["etapa"]))
stages = stages[stages["etapa"].astype(str).str.strip()!=""]

stages = stages.merge(developments[["development_id","nombre_desarrollo"]], on="nombre_desarrollo", how="left")
stages = stages.rename(columns={"etapa":"stage_name"})
stages["stage_units_planned"] = np.nan
stages.insert(0, "stage_id", range(1, len(stages)+1))

# ---------- prototypes ----------
# Conecta development_id
df = df.merge(developments[["development_id","nombre_desarrollo"]], on="nombre_desarrollo", how="left")
# Conecta stage_id si hay etapa
if "stage_name" not in df.columns:
    # renombra temporalmente para hacer merge
    df = df.rename(columns={"etapa":"stage_name"})
else:
    # si ya existe stage_name por alguna razón, asegúrate de no duplicar
    pass

df = df.merge(stages[["stage_id","development_id","stage_name"]],
              on=["development_id","stage_name"], how="left")

# Heurística is_lot (opcional)
df["is_lot_heur"] = df["nombre_prototipo"].astype(str).str.contains(r"\blote\b|\barea\b", flags=re.I, regex=True)

prototypes_cols = ["development_id","stage_id","nombre_prototipo","medida_terreno_m2","is_lot_heur"]
prototypes = df[prototypes_cols].copy()
prototypes = prototypes.rename(columns={
    "nombre_prototipo": "prototype_name",
    "is_lot_heur": "is_lot"
})
prototypes["home_model_code"] = np.nan
prototypes["spec_notes"] = np.nan

# Asegura unicidad de prototipos por fila original
prototypes.insert(0, "prototype_id", range(1, len(prototypes)+1))

# Map back prototype_id
df = df.join(prototypes["prototype_id"])

# ---------- Detecta familias ----------
ventas_cols = [c for c in df.columns if re.fullmatch(r"ventas_(ene|feb|mar|abr|may|jun|jul|ago|sep|oct|nov|dic)_[0-9]{4}", c)]
inventario_cols = [c for c in df.columns if re.fullmatch(r"inventario_(ene|feb|mar|abr|may|jun|jul|ago|sep|oct|nov|dic)_[0-9]{4}", c)]
prom_cols = [c for c in df.columns if re.fullmatch(r"prom_ventas_mensual_(ene|feb|mar|abr|may|jun|jul|ago|sep|oct|nov|dic)_[0-9]{4}", c)]
precio_terreno_cols = [c for c in df.columns if re.fullmatch(r"precio_terreno_m2_(ene|feb|mar|abr|may|jun|jul|ago|sep|oct|nov|dic)_[0-9]{4}", c)]
precio_vivienda_cols = [c for c in df.columns if re.fullmatch(r"precio_vivienda_(ene|feb|mar|abr|may|jun|jul|ago|sep|oct|nov|dic)_[0-9]{4}", c)]

id_cols = ["prototype_id"]

ventas_long = melt_family(df, id_cols, ventas_cols, "units_sold_cum")
inventario_long = melt_family(df, id_cols, inventario_cols, "units_available")
prom_long = melt_family(df, id_cols, prom_cols, "avg_monthly_sales")

def melt_prices(df, cols, price_type):
    m = melt_family(df, id_cols, cols, "price_amount")
    if len(m)==0:
        return m
    m["price_type"] = price_type
    m["currency"] = "MXN"  # confirmado por el usuario
    return m

price_terreno_long = melt_prices(df, precio_terreno_cols, "lot_m2")
price_vivienda_long = melt_prices(df, precio_vivienda_cols, "home_total")
prices = pd.concat([price_terreno_long, price_vivienda_long], ignore_index=True)

# ---------- Absorción trimestral ----------
absor_cols = [c for c in df.columns if c in {"absorcion_trim_abr","absorcion_trim_ene"}]
absorption = pd.DataFrame(columns=["prototype_id","as_of_date","absorption_last_quarter"])

if absor_cols:
    # Año ancla = máximo año visto en las familias con fecha
    date_candidates = []
    for fam in [ventas_cols, inventario_cols, prom_cols, precio_terreno_cols, precio_vivienda_cols]:
        for c in fam:
            d = parse_date_from_suffix(c)
            if d is not None:
                date_candidates.append(d.year)
    anchor_year = max(date_candidates) if date_candidates else pd.Timestamp.today().year

    tmp = df[["prototype_id"] + absor_cols].copy()
    for col in absor_cols:
        m = tmp[["prototype_id", col]].copy()
        m["absorption_last_quarter"] = m[col].apply(coerce_numeric)
        if col.endswith("_abr"):
            as_of = pd.Timestamp(anchor_year, 4, 1)
        elif col.endswith("_ene"):
            as_of = pd.Timestamp(anchor_year, 1, 1)
        else:
            as_of = None
        m["as_of_date"] = as_of
        m = m.drop(columns=[col])
        absorption = pd.concat([absorption, m], ignore_index=True)

# ---------- monthly_status ----------
monthly_status = None
for part in [ventas_long, inventario_long, prom_long, absorption]:
    if part is None or len(part)==0:
        continue
    monthly_status = part if monthly_status is None else monthly_status.merge(part, on=["prototype_id","as_of_date"], how="outer")

if monthly_status is None:
    monthly_status = pd.DataFrame(columns=["prototype_id","as_of_date","units_sold_cum","units_available","avg_monthly_sales","absorption_last_quarter"])

monthly_status = (monthly_status
                  .sort_values(["prototype_id","as_of_date"])
                  .drop_duplicates(subset=["prototype_id","as_of_date"], keep="last"))

# ---------- Housekeeping ----------

# Dates en developments
for c in ["fecha_inicio_preventa_ultima_etapa","inicio_entrega_unidades","fecha_entrega_desarrollo_completo"]:
    if c in developments.columns:
        developments[c] = pd.to_datetime(developments[c], errors="coerce")

# Numéricos
developments["numero_viviendas_planeadas"] = pd.to_numeric(developments["numero_viviendas_planeadas"], errors="coerce")
developments["meses_en_venta"] = pd.to_numeric(developments["meses_en_venta"], errors="coerce")

prototypes["lot_area_m2"] = pd.to_numeric(prototypes["medida_terreno_m2"], errors="coerce")
prototypes = prototypes.drop(columns=["medida_terreno_m2"])

prices["price_amount"] = pd.to_numeric(prices["price_amount"], errors="coerce")

# ---------- Export ----------
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)

developments.to_csv(OUTPUT_DIR / "developments.csv", index=False, encoding="utf-8")
stages.to_csv(OUTPUT_DIR / "stages.csv", index=False, encoding="utf-8")
prototypes.to_csv(OUTPUT_DIR / "prototypes.csv", index=False, encoding="utf-8")
monthly_status.to_csv(OUTPUT_DIR / "monthly_status.csv", index=False, encoding="utf-8")
prices.to_csv(OUTPUT_DIR / "prices.csv", index=False, encoding="utf-8")

print("Export listo en:", OUTPUT_DIR.resolve())
print({
    "developments_rows": len(developments),
    "stages_rows": len(stages),
    "prototypes_rows": len(prototypes),
    "monthly_status_rows": len(monthly_status),
    "prices_rows": len(prices),
})