import spacy as scy
from collections import Counter
from wordcloud import WordCloud
import matplotlib.pyplot as plt
from sklearn.tree import DecisionTreeClassifier, plot_tree
from sklearn.model_selection import train_test_split, cross_val_score
from sklearn.metrics import confusion_matrix, accuracy_score
import numpy as np
import pandas as pd
import seaborn as sns
from sqlalchemy import create_engine
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, confusion_matrix
from sklearn.preprocessing import StandardScaler, LabelEncoder
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score
import xgboost as xgb
from sklearn.cluster import KMeans
from statsmodels.tsa.arima.model import ARIMA
from scipy import stats

engine = create_engine(
    "postgresql://postgres:3061@localhost:5432/proyecto_portafolio"
)

# pd.read_sql maneja el text() automáticamente
df_stores = pd.read_sql("SELECT * FROM stores", engine)
print(f"Tabla cargada: {df_stores.shape[0]} filas, {df_stores.shape[1]} columnas")
print(df_stores.head())

df_stocks = pd.read_sql("SELECT * FROM stocks", engine)
print(f"Tabla cargada: {df_stocks.shape[0]} filas, {df_stocks.shape[1]} columnas")
print(df_stocks.head())

df_order_items = pd.read_sql("SELECT * FROM order_items", engine)
print(f"Tabla cargada: {df_order_items.shape[0]} filas, {df_order_items.shape[1]} columnas")
print(df_order_items.head())

df_products = pd.read_sql("SELECT * FROM products", engine)
print(f"Tabla cargada: {df_products.shape[0]} filas, {df_products.shape[1]} columnas")
print(df_products.head())

df_orders = pd.read_sql("SELECT * FROM orders", engine)
print(f"Tabla cargada: {df_orders.shape[0]} filas, {df_orders.shape[1]} columnas")
print(df_orders.head())

df_brands = pd.read_sql("SELECT * FROM brands", engine)
print(f"Tabla cargada: {df_brands.shape[0]} filas, {df_brands.shape[1]} columnas")
print(df_brands.head())

df_categories = pd.read_sql("SELECT * FROM categories", engine)
print(f"Tabla cargada: {df_categories.shape[0]} filas, {df_categories.shape[1]} columnas")
print(df_categories.head())

df_categories = pd.read_sql("SELECT * FROM categories", engine)
print(f"Tabla cargada: {df_categories.shape[0]} filas, {df_categories.shape[1]} columnas")
print(df_categories.head())

df_customers = pd.read_sql("SELECT * FROM customers", engine)
print(f"Tabla cargada: {df_customers.shape[0]} filas, {df_customers.shape[1]} columnas")
print(df_customers.head())

df_staffs = pd.read_sql("SELECT * FROM staffs", engine)
print(f"Tabla cargada: {df_staffs.shape[0]} filas, {df_staffs.shape[1]} columnas")
print(df_staffs.head())

# 1. CARGA DE DATOS Y FEATURE ENGINEERING
# Crear un dataset completo uniendo las tablas relevantes
query = """
SELECT 
    oi.order_id,
    oi.item_id,
    oi.quantity,
    oi.list_price,
    oi.discount,
    o.order_date,
    o.required_date,
    o.shipped_date,
    o.order_status,
    p.product_name,
    p.model_year,
    p.list_price as product_list_price,
    b.brand_name,
    c.category_name,
    cust.city as customer_city,
    cust.state as customer_state,
    s.store_name,
    st.first_name as staff_first_name,
    st.last_name as staff_last_name
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN brands b ON p.brand_id = b.brand_id
JOIN categories c ON p.category_id = c.category_id
JOIN customers cust ON o.customer_id = cust.customer_id
JOIN stores s ON o.store_id = s.store_id
JOIN staffs st ON o.staff_id = st.staff_id
"""

df_bd_completa = pd.read_sql(query, engine)

print(f"Dataset cargado: {df_bd_completa.shape}")
print(df_bd_completa.head())
print(f"\nColumnas: {df_bd_completa.columns.tolist()}")
print(f"\nValores nulos:\n{df_bd_completa.isnull().sum()}")

# ==========================================
# 2. FEATURE ENGINEERING
# ==========================================
print("\n[2/6] Realizando Feature Engineering...")

df_bd_completa['total_sale'] = df_bd_completa['quantity'] * df_bd_completa['list_price'] * (1 - df_bd_completa['discount'])
df_bd_completa['order_date'] = pd.to_datetime(df_bd_completa['order_date'])
df_bd_completa['month'] = df_bd_completa['order_date'].dt.month
df_bd_completa['day_of_week'] = df_bd_completa['order_date'].dt.dayofweek
df_bd_completa['quarter'] = df_bd_completa['order_date'].dt.quarter

print(f"Variables temporales creadas")
print(f"  - total_sale: ${df_bd_completa['total_sale'].min():.2f} - ${df_bd_completa['total_sale'].max():.2f}")
print(f"  - Promedio de venta: ${df_bd_completa['total_sale'].mean():.2f}")
print(f"  - Mediana de venta: ${df_bd_completa['total_sale'].median():.2f}")

# Encoding
le_brand = LabelEncoder()
le_category = LabelEncoder()
le_state = LabelEncoder()
le_status = LabelEncoder()

df_bd_completa['brand_encoded'] = le_brand.fit_transform(df_bd_completa['brand_name'])
df_bd_completa['category_encoded'] = le_category.fit_transform(df_bd_completa['category_name'])
df_bd_completa['state_encoded'] = le_state.fit_transform(df_bd_completa['customer_state'])
df_bd_completa['status_encoded'] = le_status.fit_transform(df_bd_completa['order_status'])

print(f"Variables categóricas codificadas")
print(f"  - Brands únicos: {df_bd_completa['brand_name'].nunique()}")
print(f"  - Categorías únicas: {df_bd_completa['category_name'].nunique()}")
print(f"  - Estados únicos: {df_bd_completa['customer_state'].nunique()}")

# ==========================================
# 3. PREPARACIÓN DE DATOS
# ==========================================
print("\n[3/6] Preparando datos para entrenamiento...")

features = ['quantity', 'list_price', 'discount', 'model_year', 
            'month', 'day_of_week', 'quarter',
            'brand_encoded', 'category_encoded', 'state_encoded', 'status_encoded']

X = df_bd_completa[features]
y = df_bd_completa['total_sale']

# Verificar valores nulos
null_counts = X.isnull().sum()
if null_counts.sum() > 0:
    print(f"Advertencia: Valores nulos encontrados:")
    print(null_counts[null_counts > 0])
else:
    print(f"No hay valores nulos en las features")

# Split
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

print(f"Datos divididos:")
print(f"  - Entrenamiento: {X_train.shape[0]:,} registros ({(X_train.shape[0]/len(X))*100:.1f}%)")
print(f"  - Prueba: {X_test.shape[0]:,} registros ({(X_test.shape[0]/len(X))*100:.1f}%)")

# Escalado
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)

print(f"Features escaladas con StandardScaler")

# ==========================================
# 4. ENTRENAMIENTO - RANDOM FOREST
# ==========================================
print("\n[4/6] Entrenando modelo Random Forest...")
print("-" * 80)
rf_model = RandomForestRegressor(
    n_estimators=100, 
    random_state=42, 
    n_jobs=-1,
    verbose=0
)
rf_model.fit(X_train_scaled, y_train)

print(f"Random Forest entrenado")
print(f"  - Número de árboles: {rf_model.n_estimators}")
print(f"  - Profundidad máxima: {rf_model.max_depth}")

# Predicciones
y_pred_rf = rf_model.predict(X_test_scaled)

# Métricas
mae_rf = mean_absolute_error(y_test, y_pred_rf)
rmse_rf = np.sqrt(mean_squared_error(y_test, y_pred_rf))
r2_rf = r2_score(y_test, y_pred_rf)
mape_rf = np.mean(np.abs((y_test - y_pred_rf) / y_test)) * 100

print("\nRESULTADOS - RANDOM FOREST")
print("=" * 80)
print(f"MAE (Error Absoluto Medio):        ${mae_rf:>10.2f}")
print(f"RMSE (Raíz Error Cuadrático):      ${rmse_rf:>10.2f}")
print(f"R² (Coeficiente Determinación):     {r2_rf:>10.4f}  ({r2_rf*100:.2f}%)")
print(f"MAPE (Error Porcentual Absoluto):   {mape_rf:>10.2f}%")
print("=" * 80)

# Análisis de errores
errors_rf = np.abs(y_test - y_pred_rf)
print(f"\nAnálisis de Errores:")
print(f"  - Error mínimo:  ${errors_rf.min():.2f}")
print(f"  - Error máximo:  ${errors_rf.max():.2f}")
print(f"  - Error mediano: ${errors_rf.median():.2f}")
print(f"  - Desv. estándar: ${errors_rf.std():.2f}")

# ==========================================
# 5. ENTRENAMIENTO - XGBOOST
# ==========================================
print("\n[5/6] Entrenando modelo XGBoost...")
print("-" * 80)

xgb_model = xgb.XGBRegressor(
    n_estimators=100, 
    learning_rate=0.1, 
    random_state=42,
    verbosity=0
)
xgb_model.fit(X_train_scaled, y_train)

print(f"XGBoost entrenado")
print(f"  - Número de estimadores: {xgb_model.n_estimators}")
print(f"  - Tasa de aprendizaje: {xgb_model.learning_rate}")

# Predicciones
y_pred_xgb = xgb_model.predict(X_test_scaled)

# Métricas
mae_xgb = mean_absolute_error(y_test, y_pred_xgb)
rmse_xgb = np.sqrt(mean_squared_error(y_test, y_pred_xgb))
r2_xgb = r2_score(y_test, y_pred_xgb)
mape_xgb = np.mean(np.abs((y_test - y_pred_xgb) / y_test)) * 100

print("\nRESULTADOS - XGBOOST")
print("=" * 80)
print(f"MAE (Error Absoluto Medio):        ${mae_xgb:>10.2f}")
print(f"RMSE (Raíz Error Cuadrático):      ${rmse_xgb:>10.2f}")
print(f"R² (Coeficiente Determinación):     {r2_xgb:>10.4f}  ({r2_xgb*100:.2f}%)")
print(f"MAPE (Error Porcentual Absoluto):   {mape_xgb:>10.2f}%")
print("=" * 80)

# Análisis de errores
errors_xgb = np.abs(y_test - y_pred_xgb)
print(f"\nAnálisis de Errores:")
print(f"  - Error mínimo:  ${errors_xgb.min():.2f}")
print(f"  - Error máximo:  ${errors_xgb.max():.2f}")
print(f"  - Error mediano: ${errors_xgb.median():.2f}")
print(f"  - Desv. estándar: ${errors_xgb.std():.2f}")

# ==========================================
# 6. COMPARACIÓN DE MODELOS
# ==========================================
print("\n[6/6] Comparación de Modelos")
print("=" * 80)

comparison = pd.DataFrame({
    'Métrica': ['MAE ($)', 'RMSE ($)', 'R²', 'MAPE (%)'],
    'Random Forest': [mae_rf, rmse_rf, r2_rf, mape_rf],
    'XGBoost': [mae_xgb, rmse_xgb, r2_xgb, mape_xgb],
    'Diferencia': [
        mae_rf - mae_xgb,
        rmse_rf - rmse_xgb,
        r2_rf - r2_xgb,
        mape_rf - mape_xgb
    ]
})

print(comparison.to_string(index=False))
print("=" * 80)

# Mejora porcentual
mejora_mae = ((mae_rf - mae_xgb) / mae_rf) * 100
mejora_rmse = ((rmse_rf - rmse_xgb) / rmse_rf) * 100

print(f"\nMODELO GANADOR: XGBoost")
print(f"  - MAE mejorado en:  {mejora_mae:.2f}%")
print(f"  - RMSE mejorado en: {mejora_rmse:.2f}%")

# ==========================================
# 7. FEATURE IMPORTANCE
# ==========================================
print("\nIMPORTANCIA DE FEATURES - Random Forest")
print("-" * 80)

feature_importance = pd.DataFrame({
    'Feature': features,
    'Importancia': rf_model.feature_importances_
}).sort_values('Importancia', ascending=False)

for idx, row in feature_importance.iterrows():
    bar = '█' * int(row['Importancia'] * 100)
    print(f"{row['Feature']:20s} | {bar} {row['Importancia']:.4f}")

print("\nIMPORTANCIA DE FEATURES - XGBoost")
print("-" * 80)

feature_importance_xgb = pd.DataFrame({
    'Feature': features,
    'Importancia': xgb_model.feature_importances_
}).sort_values('Importancia', ascending=False)

for idx, row in feature_importance_xgb.iterrows():
    bar = '█' * int(row['Importancia'] * 100)
    print(f"{row['Feature']:20s} | {bar} {row['Importancia']:.4f}")

# ==========================================
# 8. EJEMPLOS DE PREDICCIONES
# ==========================================
print("\nEJEMPLOS DE PREDICCIONES (5 casos aleatorios)")
print("=" * 80)

sample_indices = np.random.choice(len(y_test), 5, replace=False)
samples = pd.DataFrame({
    'Real': y_test.iloc[sample_indices].values,
    'Pred_RF': y_pred_rf[sample_indices],
    'Pred_XGB': y_pred_xgb[sample_indices],
    'Error_RF': np.abs(y_test.iloc[sample_indices].values - y_pred_rf[sample_indices]),
    'Error_XGB': np.abs(y_test.iloc[sample_indices].values - y_pred_xgb[sample_indices])
})

for i, (idx, row) in enumerate(samples.iterrows(), 1):
    print(f"\nCaso {i}:")
    print(f"  Valor Real:        ${row['Real']:>8.2f}")
    print(f"  Predicción RF:     ${row['Pred_RF']:>8.2f}  (error: ${row['Error_RF']:>6.2f})")
    print(f"  Predicción XGB:    ${row['Pred_XGB']:>8.2f}  (error: ${row['Error_XGB']:>6.2f})")

print("\n" + "=" * 80)
print("PROCESO COMPLETADO EXITOSAMENTE")
print("=" * 80)

# Agregación temporal
monthly_sales = df_bd_completa.groupby([
    pd.Grouper(key='order_date', freq='M'),
    'category_name',
    'brand_name'
]).agg({
    'quantity': 'sum',
    'total_sale': 'sum'
}).reset_index()

print(f"Nuevo dataset: {monthly_sales.shape}")
print(monthly_sales.head())

# Ahora predice total_sale o quantity agregado
# Esto tiene más señal porque reduces el ruido

# Agregación temporal
monthly_sales = df_bd_completa.groupby([
    pd.Grouper(key='order_date', freq='M'),
    'category_name',
    'brand_name'
]).agg({
    'quantity': 'sum',
    'total_sale': 'sum'
}).reset_index()

print(f"Nuevo dataset: {monthly_sales.shape}")
print(monthly_sales.head())

# Ahora predice total_sale o quantity agregado
# Esto tiene más señal porque reduces el ruido

print("=" * 80)
print("DIAGNÓSTICO COMPLETO DEL DATASET")
print("=" * 80)

# 1. Distribución de quantity
print("\n1) DISTRIBUCIÓN DE QUANTITY")
print(df_bd_completa['quantity'].describe())
print("\nConteo de valores:")
print(df_bd_completa['quantity'].value_counts().sort_index().head(10))

# 2. Correlaciones
print("\n2) CORRELACIONES CON QUANTITY")
numeric_cols = ['quantity', 'model_year', 'month', 'day_of_week', 'quarter']
correlations = df_bd_completa[numeric_cols].corr()['quantity'].sort_values(ascending=False)
print(correlations)

# 3. Ventas por categoría
print("\n3) QUANTITY PROMEDIO POR CATEGORÍA")
category_stats = df_bd_completa.groupby('category_name')['quantity'].agg(['mean', 'std', 'count'])
print(category_stats.sort_values('mean', ascending=False))

# 4. Análisis temporal
print("\n4) TENDENCIA TEMPORAL")
monthly = df_bd_completa.groupby(df_bd_completa['order_date'].dt.to_period('M'))['quantity'].agg(['mean', 'sum'])
print(monthly)

# 1. FILTRADO POR ESTADO DE PEDIDO
# Basado en tu esquema, asumimos que '4' es 'Completed'. 
# Ajusta el número según la lógica de tu negocio.
df_filtrado = df_bd_completa[df_bd_completa['order_status'] == 4].copy()

# 2. AGREGACIÓN MENSUAL
monthly_sales = df_filtrado.groupby(df_filtrado['order_date'].dt.to_period('M')).agg({
    'total_sale': 'sum'
}).reset_index()
monthly_sales['order_date'] = monthly_sales['order_date'].dt.to_timestamp()

# 3. DETECCIÓN Y CORRECCIÓN DEL PICO (OUTLIERS)
# Usamos el Z-score para identificar si ese valor de 800k es estadísticamente extremo
z_scores = stats.zscore(monthly_sales['total_sale'])
abs_z_scores = np.abs(z_scores)
# Si el valor es mayor a 3 desviaciones estándar, lo reemplazamos por la mediana
outliers = (abs_z_scores > 3)

if outliers.any():
    print(f"Advertencia: Se detectaron {sum(outliers)} meses con ventas atípicas. Ajustando a la mediana...")
    median_val = monthly_sales['total_sale'].median()
    monthly_sales.loc[outliers, 'total_sale'] = median_val

# --- EL RESTO DEL MODELO ---

print("=" * 80)
print("PREDICCIÓN DE VENTAS MENSUALES (DATOS LIMPIOS)")
print("=" * 80)

# Split temporal
train_size = int(len(monthly_sales) * 0.8)
train = monthly_sales['total_sale'][:train_size]
test = monthly_sales['total_sale'][train_size:]

# Modelo ARIMA (Ajustado a 1,1,1 o puedes probar 1,0,1 si los datos son más estables)
model = ARIMA(train, order=(1,1,1))
fitted_model = model.fit()

# Predicciones
predictions = fitted_model.forecast(steps=len(test))

# Métricas
mae = mean_absolute_error(test, predictions)
rmse = np.sqrt(mean_squared_error(test, predictions))
r2 = r2_score(test, predictions)

print(f"MAE:  ${mae:,.2f}")
print(f"R²:   {r2:.4f}")

# Visualización mejorada
plt.figure(figsize=(12, 6))
plt.plot(monthly_sales['order_date'][:train_size], train, label='Entrenamiento (Limpio)', marker='o')
plt.plot(monthly_sales['order_date'][train_size:], test, label='Real (Test)', marker='o')
plt.plot(monthly_sales['order_date'][train_size:], predictions, label='Predicción', color='red', linestyle='--')
plt.title('Predicción de Ventas Mensuales después de Limpieza')
plt.legend()
plt.show()

import statsmodels.api as sm

# 1. PREPARACIÓN DE DATOS (Usando el DataFrame limpio anterior)
# Aseguramos que el índice sea temporal para que el modelo entienda la frecuencia
monthly_sales = monthly_sales.set_index('order_date')
monthly_sales.index.freq = 'MS'  # MS = Month Start

# 2. DEFINICIÓN DEL MODELO SARIMA
# order: (p, d, q) -> Componente no estacional
# seasonal_order: (P, D, Q, s) -> Componente estacional (s=12 para meses del año)
sarima_model = sm.tsa.statespace.SARIMAX(
    train, 
    order=(1, 1, 1), 
    seasonal_order=(1, 1, 1, 12),
    enforce_stationarity=False,
    enforce_invertibility=False
)

sarima_results = sarima_model.fit(disp=False)

# 3. PREDICCIONES
# Predecimos sobre el rango del set de test
pred_sarima = sarima_results.get_forecast(steps=len(test))
pred_values = pred_sarima.predicted_mean
conf_int = pred_sarima.conf_int() # Intervalos de confianza

# 4. MÉTRICAS
mae_s = mean_absolute_error(test, pred_values)
r2_s = r2_score(test, pred_values)

print(f"\nRESULTADOS - SARIMA")
print("=" * 80)
print(f"MAE:  ${mae_s:,.2f}")
print(f"R²:   {r2_s:.4f}")
print("=" * 80)

# 5. VISUALIZACIÓN
plt.figure(figsize=(12, 6))
plt.plot(train.index, train, label='Entrenamiento', marker='o')
plt.plot(test.index, test, label='Real (Test)', marker='o')
plt.plot(test.index, pred_values, label='Predicción SARIMA', color='red', linestyle='--', marker='x')

# Opcional: Mostrar sombreado de incertidumbre
plt.fill_between(test.index, conf_int.iloc[:, 0], conf_int.iloc[:, 1], color='pink', alpha=0.3)

plt.title('Predicción de Ventas Mensuales con Modelo SARIMA')
plt.legend()
plt.grid(True, alpha=0.3)
plt.show()

# Agregación mensual
monthly_sales = df_bd_completa.groupby(df_bd_completa['order_date'].dt.to_period('M')).agg({
    'total_sale': 'sum',
    'quantity': 'sum',
    'order_id': 'nunique'
}).reset_index()

monthly_sales['order_date'] = monthly_sales['order_date'].dt.to_timestamp()

print("=" * 80)
print("PREDICCIÓN DE VENTAS MENSUALES - SERIES TEMPORALES")
print("=" * 80)

# Visualizar tendencia
plt.figure(figsize=(12, 6))
plt.plot(monthly_sales['order_date'], monthly_sales['total_sale'], marker='o')
plt.title('Ventas Totales Mensuales')
plt.xlabel('Fecha')
plt.ylabel('Ventas ($)')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

# Split temporal (último 20% para test)
train_size = int(len(monthly_sales) * 0.8)
train = monthly_sales['total_sale'][:train_size]
test = monthly_sales['total_sale'][train_size:]

# Modelo ARIMA
model = ARIMA(train, order=(1,1,1))
fitted_model = model.fit()

# Predicciones
predictions = fitted_model.forecast(steps=len(test))

# Métricas
from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score
import numpy as np

mae = mean_absolute_error(test, predictions)
rmse = np.sqrt(mean_squared_error(test, predictions))
r2 = r2_score(test, predictions)

print(f"\nRESULTADOS - ARIMA")
print("=" * 80)
print(f"MAE:  ${mae:,.2f}")
print(f"RMSE: ${rmse:,.2f}")
print(f"R²:   {r2:.4f}")
print("=" * 80)

# Visualizar predicciones
plt.figure(figsize=(12, 6))
plt.plot(monthly_sales['order_date'][:train_size], train, label='Entrenamiento', marker='o')
plt.plot(monthly_sales['order_date'][train_size:], test, label='Real', marker='o')
plt.plot(monthly_sales['order_date'][train_size:], predictions, label='Predicción', marker='x', linestyle='--')
plt.title('Predicción de Ventas Mensuales')
plt.xlabel('Fecha')
plt.ylabel('Ventas ($)')
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

print("\nINSIGHTS:")
print(f"- Venta mensual promedio: ${monthly_sales['total_sale'].mean():,.2f}")
print(f"- Tendencia: {'Creciente' if monthly_sales['total_sale'].iloc[-6:].mean() > monthly_sales['total_sale'].iloc[:6].mean() else 'Estable'}")

# Cargar solo la tabla orders con customer_id
query_orders = """
SELECT 
    order_id,
    customer_id,
    order_date,
    order_status
FROM orders
"""

orders_df = pd.read_sql(query_orders, engine)

print(f"Orders cargados: {orders_df.shape}")
print(orders_df.head())

# Hacer merge con tu df principal
# Asumiendo que df ya tiene 'order_id'
df_with_customer = df_bd_completa.merge(
    orders_df[['order_id', 'customer_id']], 
    on='order_id', 
    how='left'
)
print(f"\nMerge completado: {df_with_customer.shape}")
print(f"customer_id agregado: {'customer_id' in df_with_customer.columns}")
print(f"Clientes únicos: {df_with_customer['customer_id'].nunique()}")
# Verificar que no haya nulos
print(f"\nAdvertencia: Valores nulos en customer_id: {df_with_customer['customer_id'].isnull().sum()}")
# Actualizar df
df_bd_completa = df_with_customer

# Crear perfil de clientes
customer_profile = df_bd_completa.groupby('customer_id').agg({
    'total_sale': ['sum', 'mean', 'count'],
    'quantity': 'sum',
    'discount': 'mean',
    'order_date': lambda x: (x.max() - x.min()).days  # Días entre primera y última compra
}).reset_index()

customer_profile.columns = ['customer_id', 'total_spent', 'avg_order', 
                            'num_orders', 'total_items', 'avg_discount', 'customer_lifetime_days']

print("=" * 80)
print("SEGMENTACIÓN DE CLIENTES - K-MEANS CLUSTERING")
print("=" * 80)

# Preparar features
features = ['total_spent', 'num_orders', 'avg_order', 'avg_discount']
X = customer_profile[features]

# Escalar
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

# K-Means
kmeans = KMeans(n_clusters=4, random_state=42, n_init=10)
customer_profile['segment'] = kmeans.fit_predict(X_scaled)

# Analizar segmentos
print("\nCARACTERÍSTICAS DE LOS SEGMENTOS:")
print("=" * 80)

segment_analysis = customer_profile.groupby('segment').agg({
    'customer_id': 'count',
    'total_spent': 'mean',
    'num_orders': 'mean',
    'avg_order': 'mean',
    'avg_discount': 'mean'
}).round(2)

segment_analysis.columns = ['Cantidad_Clientes', 'Gasto_Promedio', 
                            'Num_Ordenes_Prom', 'Ticket_Promedio', 'Descuento_Prom']

print(segment_analysis)

# Nombrar segmentos
segment_names = {
    segment_analysis['Gasto_Promedio'].idxmax(): 'VIP (Alto Valor)',
    segment_analysis['Num_Ordenes_Prom'].idxmax(): 'Frecuentes',
    segment_analysis['Gasto_Promedio'].idxmin(): 'Ocasionales',
}

print("\nINTERPRETACIÓN DE SEGMENTOS:")
for seg, name in segment_names.items():
    count = segment_analysis.loc[seg, 'Cantidad_Clientes']
    value = segment_analysis.loc[seg, 'Gasto_Promedio']
    print(f"  Segmento {seg} - {name}: {count} clientes, ${value:,.2f} promedio")

# Visualización
plt.figure(figsize=(12, 6))
plt.subplot(1, 2, 1)
plt.scatter(customer_profile['total_spent'], customer_profile['num_orders'], 
            c=customer_profile['segment'], cmap='viridis', alpha=0.6)
plt.xlabel('Gasto Total ($)')
plt.ylabel('Número de Órdenes')
plt.title('Segmentación de Clientes')
plt.colorbar(label='Segmento')

plt.subplot(1, 2, 2)
segment_analysis['Cantidad_Clientes'].plot(kind='bar')
plt.xlabel('Segmento')
plt.ylabel('Cantidad de Clientes')
plt.title('Distribución de Segmentos')
plt.tight_layout()
plt.show()

# RFM Analysis (Recency, Frequency, Monetary)
import datetime

reference_date = df_bd_completa['order_date'].max() + pd.Timedelta(days=1)

# Force 5 stable bins using ranking
rfm['R_score'] = pd.qcut(
    rfm['recency'].rank(method='first'),
    5,
    labels=[5,4,3,2,1]
).astype(int)

rfm['F_score'] = pd.qcut(
    rfm['frequency'].rank(method='first'),
    5,
    labels=[1,2,3,4,5]
).astype(int)

rfm['M_score'] = pd.qcut(
    rfm['monetary'].rank(method='first'),
    5,
    labels=[1,2,3,4,5]
).astype(int)

rfm['RFM_score'] = (
    rfm['R_score'].astype(str) +
    rfm['F_score'].astype(str) +
    rfm['M_score'].astype(str)
)

print("=" * 80)
print("ANÁLISIS RFM - IDENTIFICACIÓN DE CLIENTES EN RIESGO")
print("=" * 80)

# Clientes en riesgo (alta recencia, baja frecuencia)
at_risk = rfm[(rfm['recency'] > rfm['recency'].median()) & 
              (rfm['frequency'] < rfm['frequency'].median())]

print(f"\nAdvertencia: Clientes en riesgo de churn: {len(at_risk)} ({len(at_risk)/len(rfm)*100:.1f}%)")
print(f"Valor en riesgo: ${at_risk['monetary'].sum():,.2f}")

# Top clientes
champions = rfm[(rfm['R_score'].astype(int) >= 4) & 
                (rfm['F_score'].astype(int) >= 4) & 
                (rfm['M_score'].astype(int) >= 4)]

print(f"\nClientes Champions: {len(champions)} ({len(champions)/len(rfm)*100:.1f}%)")
print(f"Valor generado: ${champions['monetary'].sum():,.2f}")

def rfm_segment(row):
    if row['R_score'] >= 4 and row['F_score'] >= 4 and row['M_score'] >= 4:
        return 'Champions'
    elif row['R_score'] >= 3 and row['F_score'] >= 4:
        return 'Loyal Customers'
    elif row['R_score'] >= 4 and row['F_score'] <= 2:
        return 'Potential Loyalists'
    elif row['R_score'] >= 3 and row['F_score'] <= 2:
        return 'Recent Customers'
    elif row['R_score'] <= 2 and row['F_score'] >= 3:
        return 'At Risk'
    elif row['R_score'] <= 2 and row['F_score'] <= 2 and row['M_score'] >= 3:
        return 'Hibernating'
    elif row['R_score'] == 1 and row['F_score'] == 1:
        return 'Lost'
    else:
        return 'Others'
rfm['Segment'] = rfm.apply(rfm_segment, axis=1)

segment_summary = (
    rfm.groupby('Segment')
       .agg(
           customers=('customer_id', 'count'),
           avg_recency=('recency', 'mean'),
           avg_frequency=('frequency', 'mean'),
           avg_monetary=('monetary', 'mean'),
           total_revenue=('monetary', 'sum')
       )
       .sort_values('customers', ascending=False)
)

segment_summary

rfm[['R_score','F_score','M_score']].hist(bins=5, figsize=(10,4))
plt.suptitle('Distribution of RFM Scores')
plt.show()

rfm['Segment'].value_counts().plot(kind='bar', figsize=(10,4))
plt.title('Customer Distribution by RFM Segment')
plt.ylabel('Number of Customers')
plt.show()

(
    rfm.groupby('Segment')['monetary']
       .sum()
       .sort_values(ascending=False)
       .plot(kind='bar', figsize=(10,4))
)

plt.title('Revenue Contribution by RFM Segment')
plt.ylabel('Total Revenue')
plt.show()

plt.figure(figsize=(8,5))
plt.scatter(rfm['R_score'], rfm['F_score'], alpha=0.4)
plt.xlabel('Recency Score')
plt.ylabel('Frequency Score')
plt.title('Customer Distribution: Recency vs Frequency')
plt.show()

segment_summary = segment_summary.reset_index()

segment_summary['model_version'] = 'rfm_kmeans_v1'

segment_summary.head()

engine = create_engine(
    "postgresql://postgres:3061@localhost:5432/proyecto_portafolio"
)

segment_summary.to_sql(
    "customer_rfm_segments",
    engine,
    if_exists="append",   # permite múltiples ejecuciones
    index=False
)
customer_profile[['customer_id', 'segment']].to_sql(
    "customer_segments_detail",
    engine,
    if_exists="replace",
    index=False
)