data_handler.py

import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from datetime import datetime
import prediction_engine as engine
import re
import os

# ==============================================================================
# 1. SMART COLUMN MAPPING (Kamus Sinonim)
# ==============================================================================
# Dictionary ini digunakan untuk mencocokkan nama kolom dari file pengguna
# dengan nama kolom standar yang dibutuhkan sistem.
COLUMN_SYNONYMS = {
    'Town': ['town', 'city', 'location', 'kota', 'lokasi', 'area', 'wilayah', 'daerah'],
    'Property_Residential': ['property_residential', 'property', 'type', 'tipe', 'jenis', 'kategori', 'category', 'jenis properti'],
    'List Year': ['list year', 'year', 'tahun', 'thn', 'tahun daftar', 'listing year'],
    'Assessed Value': ['assessed value', 'value', 'price', 'assessed', 'nilai', 'harga', 'taksiran', 'harga taksiran', 'amount', 'rp', 'idr', 'usd']
}

def find_header_row(df_raw):
    """
    Mencari baris mana yang mengandung Header secara otomatis. 
    Berguna jika header Excel ada di baris ke-2 atau ke-3 (bukan baris pertama).
    """
    # Cek 10 baris pertama
    for i in range(min(10, len(df_raw))):
        row_values = df_raw.iloc[i].astype(str).str.lower().tolist()
        # Hitung berapa banyak kata kunci 'wajib' yang muncul di baris ini
        matches = 0
        for key, synonyms in COLUMN_SYNONYMS.items():
            if any(syn in " ".join(row_values) for syn in synonyms):
                matches += 1
        
        # Jika minimal 3 kolom wajib ditemukan di baris ini, ini adalah Header!
        if matches >= 3:
            print(f"✅ Header ditemukan di baris ke-{i}")
            # Set baris ini sebagai header
            df_new = df_raw.iloc[i+1:].copy()
            df_new.columns = df_raw.iloc[i]
            return df_new
            
    # Jika tidak ketemu, kembalikan apa adanya (asumsi baris 0 adalah header)
    return df_raw

def normalize_columns(df):
    """
    Mengubah nama kolom dari file user menjadi standar sistem.
    Contoh: 'Kota' -> 'Town', 'Harga Taksiran' -> 'Assessed Value'
    """
    df.columns = df.columns.astype(str).str.strip()
    rename_map = {}
    
    # Loop setiap kolom di Dataframe user
    for col in df.columns:
        col_lower = col.lower().replace('_', ' ').replace('.', ' ')
        
        # Cari kecocokan di kamus sinonim
        for standard_col, synonyms in COLUMN_SYNONYMS.items():
            # Cek Exact Match atau Partial Match
            if any(syn == col_lower or syn in col_lower.split() for syn in synonyms):
                if standard_col not in rename_map.values():
                    rename_map[col] = standard_col
    
    if rename_map:
        print(f"🔄 Rename Kolom: {rename_map}")
        df = df.rename(columns=rename_map)
        
    return df

def clean_currency_aggressive(x):
    """
    Fungsi pembersih angka yang kuat untuk menangani format mata uang.
    Bisa membaca: 'Rp 1.500.000', '$ 150,000.00', '150.000', '150,000'
    """
    if pd.isna(x) or x == "":
        return 0.0
    
    s = str(x).strip()
    
    try:
        # Jika formatnya sudah float/int murni, langsung return
        if isinstance(x, (int, float)):
            return float(x)

        # 1. Buang Simbol Mata Uang & Huruf (Rp, $, USD, dll)
        # Hanya sisakan angka, titik, koma, dan minus
        s = re.sub(r'[^\d.,-]', '', s)
        
        if not s: return 0.0

        # 2. Deteksi Format Indonesia (Titik sebagai ribuan) vs US (Koma sebagai ribuan)
        # Jika ada titik DAN koma (misal: 150.000,00 atau 150,000.00)
        if '.' in s and ',' in s:
            if s.rfind('.') < s.rfind(','): 
                # Format Indo: 150.000,00 -> Titik dihapus, Koma jadi Titik
                s = s.replace('.', '').replace(',', '.')
            else:
                # Format US: 150,000.00 -> Koma dihapus
                s = s.replace(',', '')
        
        # Jika hanya ada Titik (misal: 150.000 atau 150.55)
        elif '.' in s:
            # Jika titik muncul lebih dari sekali (1.000.000), itu pasti ribuan -> Hapus
            if s.count('.') > 1:
                s = s.replace('.', '')
            # Jika titik cuma satu tapi di akhir (100. -> 100)
            elif s.endswith('.'):
                s = s.replace('.', '')
            # Asumsi input properti angka bulat besar: Hapus titik jika terlihat seperti ribuan
            elif len(s.split('.')[-1]) == 3: 
                s = s.replace('.', '')
                
        # Jika hanya ada Koma (misal: 150,000) -> Hapus koma
        elif ',' in s:
            s = s.replace(',', '')

        return float(s)
    except:
        return 0.0

# ==============================================================================
# 2. ROBUST PLOTTING (Visualisasi Data)
# ==============================================================================
def safe_generate_plots(df):
    """
    Membuat 9 grafik visualisasi menggunakan Plotly.
    Fungsi ini aman (safe), artinya jika data kosong, akan mengembalikan grafik kosong, bukan error.
    """
    if df is None or df.empty:
        empty = go.Figure().update_layout(
            title="Data Kosong / Gagal Membaca Angka", 
            xaxis={"visible":False}, yaxis={"visible":False},
            annotations=[{"text": "Cek Format Angka Excel Anda", "showarrow":False, "font":{"size":20}}]
        )
        return [empty] * 9

    # 1. Scatter: Prediksi vs Nilai Taksiran
    try:
        fig1 = px.scatter(
            df, x="Assessed Value", y="Predicted_Sale_Amount", color="Property_Residential",
            hover_data=['Town', 'List Year'], title="📈 Prediksi vs Nilai Taksiran",
            template="plotly_white", opacity=0.8
        )
        fig1.update_traces(marker=dict(size=12)) # Memperbesar ukuran titik
    except: fig1 = go.Figure()

    # 2. Box Plot: Sebaran Harga (Restored)
    try:
        top_towns = df['Town'].value_counts().nlargest(10).index
        df_top = df[df['Town'].isin(top_towns)]
        fig2 = px.box(
            df_top, x="Town", y="Predicted_Sale_Amount", color="Town",
            title="🏙️ Sebaran Harga (Price Distribution per Town)", 
            template="plotly_white", points="all"
        )
        fig2.update_layout(showlegend=False)
    except: fig2 = go.Figure()

    # 3. Line Chart: Trend (Dual Axis)
    # Menampilkan Assessed Value dan Predicted Price dalam satu grafik dengan dua skala sumbu Y.
    try:
        df_line = df.sort_values(by="Assessed Value").reset_index(drop=True)
        fig3 = make_subplots(specs=[[{"secondary_y": True}]])
        fig3.add_trace(go.Scatter(x=df_line.index, y=df_line['Assessed Value'], mode='lines', name='Assessed Value', line=dict(color='orange')), secondary_y=False)
        fig3.add_trace(go.Scatter(x=df_line.index, y=df_line['Predicted_Sale_Amount'], mode='lines', name='Predicted Price', line=dict(color='blue')), secondary_y=True)
        fig3.update_layout(title="📈 Predicted vs Assessed Value Trend (Dual Scale)", xaxis_title="Property Index (Sorted by Value)", template="plotly_white")
        fig3.update_yaxes(title_text="Assessed Value ($)", secondary_y=False)
        fig3.update_yaxes(title_text="Predicted Price ($)", secondary_y=True)
    except: fig3 = go.Figure()

    # 4. Violin Plot: Price Distribution Top 5 (Restored)
    try:
        top_5_towns = df['Town'].value_counts().nlargest(5).index
        df_dist = df[df['Town'].isin(top_5_towns)]
        fig4 = px.violin(
            df_dist, x="Predicted_Sale_Amount", y="Town", orientation='h', 
            box=True, points="all", color="Town",
            title="🎻 Price Distribution by Town (Top 5)",
            template="plotly_white"
        )
        fig4.update_layout(showlegend=False)
    except: fig4 = go.Figure()

    # 5. Heatmap: Town vs Type (Restored)
    try:
        heatmap_data = df.groupby(['Town', 'Property_Residential'])['Predicted_Sale_Amount'].mean().reset_index()
        top_towns = df['Town'].value_counts().nlargest(20).index
        heatmap_data = heatmap_data[heatmap_data['Town'].isin(top_towns)]
        fig5 = px.density_heatmap(
            heatmap_data, x="Town", y="Property_Residential", z="Predicted_Sale_Amount",
            histfunc="avg", title="🔥 Avg Price Heatmap (Town vs Type)",
            color_continuous_scale="Viridis", template="plotly_white"
        )
    except: fig5 = go.Figure()

    # 6. Bar Chart: Top Growth Towns
    try:
        df['Ratio'] = df['Predicted_Sale_Amount'] / df['Assessed Value']
        growth = df.groupby("Town")['Ratio'].mean().reset_index()
        growth = growth.sort_values(by="Ratio", ascending=False).head(10)
        fig6 = px.bar(
            growth, x="Ratio", y="Town", orientation='h', 
            title="🚀 Top 10 High Growth Towns (Avg Ratio)",
            color="Ratio", color_continuous_scale="RdBu", template="plotly_white"
        )
        fig6.add_vline(x=1.0, line_dash="dash", line_color="black")
    except: fig6 = go.Figure()

    # 7. Pie Chart: Price Segmentation (New)
    try:
        mean_val = df['Predicted_Sale_Amount'].mean()
        def segment(x):
            if x < mean_val * 0.8: return 'Budget'
            elif x > mean_val * 1.5: return 'Luxury'
            else: return 'Mid-Range'
        df['Segment'] = df['Predicted_Sale_Amount'].apply(segment)
        fig7 = px.pie(
            df, names='Segment', values='Predicted_Sale_Amount', 
            title="💰 Price Segmentation (Value Share)",
            template="plotly_white", hole=0.4
        )
    except: fig7 = go.Figure()

    # 8. Scatter: Outlier Detection (>100% threshold)
    try:
        df['Diff_Pct'] = ((df['Predicted_Sale_Amount'] - df['Assessed Value']) / df['Assessed Value']) * 100
        # Threshold: > 100% perbedaan
        df['Is_Outlier'] = df['Diff_Pct'].abs() > 100
        
        fig8 = px.scatter(
            df, x="Assessed Value", y="Diff_Pct", color="Is_Outlier",
            hover_data=['Town', 'Property_Residential'],
            title="⚠️ Outlier Detection (> 100% Difference)",
            labels={"Diff_Pct": "Difference (%)", "Is_Outlier": "Is Outlier?"},
            template="plotly_white", color_discrete_map={True: 'red', False: 'gray'}
        )
        fig8.add_hline(y=100, line_dash="dash", line_color="red")
        fig8.add_hline(y=-100, line_dash="dash", line_color="red")
    except: fig8 = go.Figure()

    # 9. Bar Chart: Investment Potential (New)
    try:
        df['Potential_Upside'] = df['Predicted_Sale_Amount'] - df['Assessed Value']
        top_invest = df.nlargest(10, 'Potential_Upside')
        fig9 = px.bar(
            top_invest, x="Town", y="Potential_Upside", color="Property_Residential",
            hover_data=['Predicted_Sale_Amount'],
            title="💎 Investment Potential (Top Undervalued)",
            labels={"Potential_Upside": "Potential Gain ($)"},
            template="plotly_white"
        )
    except: fig9 = go.Figure()

    return fig1, fig2, fig3, fig4, fig5, fig6, fig7, fig8, fig9

# ==============================================================================
# 3. PIPELINE UTAMA (Proses Data)
# ==============================================================================
def load_preview(file_obj):
    """
    Memuat file (Excel/CSV) dan mengembalikan dataframe untuk preview.
    """
    if file_obj is None: 
        return None, "No file uploaded."
    
    try:
        filename = file_obj.name.lower()
        if filename.endswith('.csv'):
            df = pd.read_csv(file_obj.name, header=None)
        elif filename.endswith(('.xlsx', '.xls')):
            df = pd.read_excel(file_obj.name, header=None)
        else:
            return None, "Format file tidak didukung. Gunakan .xlsx atau .csv"
        
        df = find_header_row(df)
        df = normalize_columns(df)
        
        # Proses awal minimal untuk preview
        if 'Assessed Value' in df.columns:
            # Bersihkan mata uang tapi biarkan string/float untuk sementara
            df['Assessed Value'] = df['Assessed Value'].apply(clean_currency_aggressive)
        
        return df, None
    except Exception as e:
        return None, f"Error reading file: {str(e)}"

def process_dataframe(df):
    """
    Memproses dataframe utama: Cleaning, Prediksi, dan Visualisasi.
    """
    if df is None or df.empty:
        # Kembalikan None untuk 9 figure
        return "ERROR_GENERIC", "Dataframe kosong.", None, None, None, None, None, None, None, None, None

    try:
        # Cek kelengkapan kolom
        req_cols = ['Town', 'Property_Residential', 'List Year', 'Assessed Value']
        missing = [c for c in req_cols if c not in df.columns]
        if missing:
             return "ERROR_COLS", f"Kolom hilang: {missing}", None, None, None, None, None, None, None, None, None
             
        # Konversi Tahun
        df['List Year'] = pd.to_numeric(df['List Year'], errors='coerce').fillna(2021).astype(int).astype(str)
        
        # Pastikan Assessed Value numerik
        df['Assessed Value'] = pd.to_numeric(df['Assessed Value'], errors='coerce').fillna(0.0)

        # Filter baris yang valid (Value > 0)
        df_valid = df[df['Assessed Value'] > 0].copy()
        
        if df_valid.empty:
             return "ERROR_COLS", "Tidak ada data dengan Assessed Value > 0.", None, None, None, None, None, None, None, None, None

        if 'Prediction Date' not in df_valid.columns:
            df_valid['Prediction Date'] = datetime.now().strftime('%Y-%m-%d')

        preds = []
        
        # Loop prediksi per baris
        for _, row in df_valid.iterrows():
            val = engine.predict_single(
                row['Assessed Value'], 
                row['Town'], 
                row['Property_Residential'], 
                row['List Year'], 
                str(row['Prediction Date']),
                return_debug=False 
            )
            preds.append(round(val, 3)) 
        
        df_valid['Predicted_Sale_Amount'] = preds
        
        # Hitung Metrics & Diff
        metrics = engine.calculate_key_metrics(df_valid)
        
        if 'Diff' in df_valid.columns:
            df_valid['Diff'] = df_valid['Diff'].round(3)
            
        # Generate 9 Plots
        fig1, fig2, fig3, fig4, fig5, fig6, fig7, fig8, fig9 = safe_generate_plots(df_valid)
        
        # Simpan hasil ke Excel
        out_file = "analysis_result.xlsx"
        df_valid.to_excel(out_file, index=False) 
        
        return df_valid, metrics, fig1, fig2, fig3, fig4, fig5, fig6, fig7, fig8, fig9, out_file

    except Exception as e:
        import traceback
        traceback.print_exc()
        return "ERROR_GENERIC", str(e), None, None, None, None, None, None, None, None, None