#!/usr/bin/env python3
"""
Test pipeline ด้วย data ที่ filter outliers แล้ว
"""

import sys
import os

# Set UTF-8 encoding
if sys.platform == 'win32':
    os.environ['PYTHONIOENCODING'] = 'utf-8'
    sys.stdout.reconfigure(encoding='utf-8')

import pandas as pd
from openpyxl import load_workbook
from utils.preprocessing import preprocess_load_data, preprocess_temperature_data, preprocess_measurement_data
from utils.merge_data import aggregate_daily_max, merge_load_temp, merge_with_measurements
from utils.excel_loader import load_submarine_forecast_data

try:
    print("=" * 60)
    print("🔧 Loading submarine_forecast.xlsx...")
    print("=" * 60)
    
    # Load Excel
    xls_file = './dataset/submarine_forecast.xlsx'
    data = load_submarine_forecast_data(xls_file)
    
    print(f"✅ Loaded 4 sheets: {list(data.keys())}")
    print()
    
    # ========== PREPROCESS ==========
    print("=" * 60)
    print("🔄 PREPROCESSING...")
    print("=" * 60)
    
    # Load
    print("\n📊 Load data (samui_load):")
    df_load = preprocess_load_data(data['load'])
    print(f"  Shape: {df_load.shape}")
    print(f"  Date range: {df_load['datetime'].min()} to {df_load['datetime'].max()}")
    print(f"  MW range: {df_load['mw'].min():.2f} - {df_load['mw'].max():.2f} MW")
    print(f"  Sample:\n{df_load.head(3)}")
    
    # Temperature
    print("\n🌡️  Temperature data (MAX_TEMP_BY_DAY):")
    df_temp = preprocess_temperature_data(data['temp'])
    print(f"  Shape: {df_temp.shape}")
    print(f"  Date range: {df_temp['date'].min()} to {df_temp['date'].max()}")
    if 'max_temp' in df_temp.columns:
        print(f"  Max_Temp range: {df_temp['max_temp'].min():.2f} - {df_temp['max_temp'].max():.2f}°C")
    if 'fiber_temp' in df_temp.columns:
        print(f"  Fiber_Temp range: {df_temp['fiber_temp'].min():.2f} - {df_temp['fiber_temp'].max():.2f}°C")
    print(f"  Sample:\n{df_temp.head(3)}")
    
    # Measurements
    print("\n📏 TEPR data (MEASUREMENT_TEPR):")
    df_tepr = preprocess_measurement_data(data['tepr'], param_type='TEPR')
    print(f"  Shape: {df_tepr.shape}")
    if len(df_tepr) > 0:
        print(f"  Distances: {len(df_tepr['distance'].unique())} unique")
        print(f"  VALUE stats - Mean: {df_tepr['TEPR_mean'].mean():.2f}, Std: {df_tepr['TEPR_std'].mean():.2f}")
    print(f"  Sample:\n{df_tepr.head(3)}")
    
    print("\n📏 STRR data (MEASUREMENT_STRR):")
    df_strr = preprocess_measurement_data(data['strr'], param_type='STRR')
    print(f"  Shape: {df_strr.shape}")
    if len(df_strr) > 0:
        print(f"  Distances: {len(df_strr['distance'].unique())} unique")
        print(f"  VALUE stats - Mean: {df_strr['STRR_mean'].mean():.2f}, Std: {df_strr['STRR_std'].mean():.2f}")
    print(f"  Sample:\n{df_strr.head(3)}")
    
    # ========== AGGREGATE & MERGE ==========
    print("\n" + "=" * 60)
    print("🔗 AGGREGATION & MERGING...")
    print("=" * 60)
    
    # Daily aggregate
    print("\n📈 Aggregating load to daily max...")
    df_daily = aggregate_daily_max(df_load)
    print(f"  Shape: {df_daily.shape}")
    print(f"  Date range: {df_daily['date'].min()} to {df_daily['date'].max()}")
    print(f"  mw_max range: {df_daily['mw_max'].min():.2f} - {df_daily['mw_max'].max():.2f} MW")
    print(f"  mw_theoretical_80pct range: {df_daily['mw_theoretical_80pct'].min():.2f} - {df_daily['mw_theoretical_80pct'].max():.2f} MW")
    print(f"  Sample:\n{df_daily.head(3)}")
    
    # Merge load + temp
    print("\n🔗 Merging load + temperature...")
    df_merged = merge_load_temp(df_daily, df_temp)
    print(f"  Shape: {df_merged.shape}")
    print(f"  Columns: {list(df_merged.columns)}")
    print(f"  Sample:\n{df_merged.head(3)}")
    
    # Merge with measurements
    print("\n🔗 Adding measurements (TEPR/STRR statistics)...")
    df_final = merge_with_measurements(df_merged, df_tepr, df_strr)
    print(f"  Shape: {df_final.shape}")
    print(f"  Columns: {list(df_final.columns)}")
    print(f"  mw_max range: {df_final['mw_max'].min():.2f} - {df_final['mw_max'].max():.2f} MW ✅")
    print(f"  mw_theoretical_80pct range: {df_final['mw_theoretical_80pct'].min():.2f} - {df_final['mw_theoretical_80pct'].max():.2f} MW ✅")
    print(f"  Sample:\n{df_final.head(3)}")
    
    print("\n" + "=" * 60)
    print("✅ PIPELINE SUCCESS!")
    print("=" * 60)
    print(f"Final data shape: {df_final.shape}")
    print(f"Final mw_max reasonable? {df_final['mw_max'].max() < 100}")
    
except Exception as e:
    print(f"\n❌ ERROR: {e}")
    import traceback
    traceback.print_exc()
    sys.exit(1)