backend/database.py

# database.py — Koneksi MySQL, baca & tulis semua tabel
import os
import pandas as pd
import sqlalchemy
from sqlalchemy import text
from dotenv import load_dotenv

load_dotenv()

def get_engine():
    url = (
        f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}"
        f"@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT', 3306)}"
        f"/{os.getenv('DB_NAME')}"
    )
    return sqlalchemy.create_engine(url, pool_pre_ping=True, pool_recycle=3600)

# ── READ ──────────────────────────────────────────────────────────────────────

def get_all_komoditas():
    """Ambil semua komoditas aktif."""
    engine = get_engine()
    return pd.read_sql(
        "SELECT id, nama, slug, unit, volatile, volatilitas_skor FROM komoditas WHERE is_active=1",
        engine
    )

def get_harga_harian(komoditas_id: int, days: int = 730):
    """Ambil data harga harian untuk satu komoditas (default 2 tahun terakhir)."""
    engine = get_engine()
    query = f"""
        SELECT hh.tanggal AS date, p.nama AS pasar_nama, hh.harga
        FROM harga_harian hh
        JOIN pasar p ON p.id = hh.pasar_id
        WHERE hh.komoditas_id = {komoditas_id}
          AND hh.tanggal >= DATE_SUB(CURDATE(), INTERVAL {days} DAY)
        ORDER BY hh.tanggal ASC
    """
    df_raw = pd.read_sql(query, engine)
    if df_raw.empty:
        return pd.DataFrame()
    # Pivot → wide format
    df = df_raw.pivot_table(
        index='date', columns='pasar_nama', values='harga'
    ).reset_index()
    df.columns.name = None
    df['date'] = pd.to_datetime(df['date'])
    df = df.sort_values('date').reset_index(drop=True)
    return df

def get_pasar_list(komoditas_id: int):
    """Ambil daftar pasar yang punya data untuk komoditas ini."""
    engine = get_engine()
    return pd.read_sql(f"""
        SELECT DISTINCT p.id, p.nama
        FROM harga_harian hh
        JOIN pasar p ON p.id = hh.pasar_id
        WHERE hh.komoditas_id = {komoditas_id}
    """, engine)

# ── WRITE ─────────────────────────────────────────────────────────────────────

def save_model_ml(data: dict):
    """Upsert ke tabel model_ml."""
    engine = get_engine()
    with engine.begin() as conn:
        conn.execute(text("""
            INSERT INTO model_ml (
                komoditas_id, nama_model, versi, deskripsi, file_path,
                mape, rmse, mae, r2_score, confidence_level,
                stabilitas, status_validasi, catatan_validasi,
                tanggal_training, tanggal_evaluasi, is_active,
                created_at, updated_at
            ) VALUES (
                :komoditas_id, :nama_model, :versi, :deskripsi, :file_path,
                :mape, :rmse, :mae, :r2_score, :confidence_level,
                :stabilitas, :status_validasi, :catatan_validasi,
                :tanggal_training, :tanggal_evaluasi, 1, NOW(), NOW()
            )
            ON DUPLICATE KEY UPDATE
                nama_model       = VALUES(nama_model),
                versi            = VALUES(versi),
                deskripsi        = VALUES(deskripsi),
                file_path        = VALUES(file_path),
                mape             = VALUES(mape),
                rmse             = VALUES(rmse),
                mae              = VALUES(mae),
                r2_score         = VALUES(r2_score),
                confidence_level = VALUES(confidence_level),
                stabilitas       = VALUES(stabilitas),
                status_validasi  = VALUES(status_validasi),
                catatan_validasi = VALUES(catatan_validasi),
                tanggal_evaluasi = VALUES(tanggal_evaluasi),
                updated_at       = NOW()
        """), data)

    with engine.connect() as conn:
        return conn.execute(
            text("SELECT id FROM model_ml WHERE komoditas_id=:kid ORDER BY updated_at DESC LIMIT 1"),
            {'kid': data['komoditas_id']}
        ).scalar()

def save_hasil_prediksi(predictions: list, komoditas_id: int, pasar_id, model_id: int, meta: dict):
    """Hapus prediksi lama & insert 7 baris baru."""
    engine = get_engine()
    with engine.begin() as conn:
        conn.execute(text("""
            DELETE FROM hasil_prediksi
            WHERE komoditas_id = :kid AND pasar_id = :pid
              AND tanggal_target >= CURDATE()
        """), {'kid': komoditas_id, 'pid': pasar_id})

        for p in predictions:
            conn.execute(text("""
                INSERT INTO hasil_prediksi (
                    komoditas_id, pasar_id, model_id,
                    tanggal_prediksi, tanggal_target,
                    harga_prediksi, confidence_level,
                    model_name, mape, rmse, created_at
                ) VALUES (
                    :kid, :pid, :model_id,
                    CURDATE(), :tanggal_target,
                    :harga_prediksi, :confidence_level,
                    :model_name, :mape, :rmse, NOW()
                )
            """), {
                'kid'             : komoditas_id,
                'pid'             : pasar_id,
                'model_id'        : model_id,
                'tanggal_target'  : p['tanggal'],
                'harga_prediksi'  : p['harga_prediksi'],
                'confidence_level': meta['confidence_level'],
                'model_name'      : meta['nama_model'],
                'mape'            : meta['mape'],
                'rmse'            : meta['rmse'],
            })

def save_ringkasan_prediksi(data: dict, komoditas_id: int, model_id: int):
    """Upsert ringkasan prediksi."""
    engine = get_engine()
    with engine.begin() as conn:
        conn.execute(text("""
            INSERT INTO ringkasan_prediksi (
                komoditas_id, model_id,
                harga_min, harga_max, tren, confidence_level,
                status_analisis, deskripsi_status,
                tanggal_mulai, tanggal_akhir, created_at
            ) VALUES (
                :komoditas_id, :model_id,
                :harga_min, :harga_max, :tren, :confidence_level,
                :status_analisis, :deskripsi_status,
                :tanggal_mulai, :tanggal_akhir, NOW()
            )
            ON DUPLICATE KEY UPDATE
                harga_min        = VALUES(harga_min),
                harga_max        = VALUES(harga_max),
                tren             = VALUES(tren),
                confidence_level = VALUES(confidence_level),
                status_analisis  = VALUES(status_analisis),
                deskripsi_status = VALUES(deskripsi_status),
                tanggal_mulai    = VALUES(tanggal_mulai),
                tanggal_akhir    = VALUES(tanggal_akhir)
        """), {**data, 'komoditas_id': komoditas_id, 'model_id': model_id})

def save_insight_prediksi(insights: list, komoditas_id: int, model_id: int):
    """Hapus insight lama & insert baru."""
    engine = get_engine()
    with engine.begin() as conn:
        conn.execute(text(
            "DELETE FROM insight_prediksi WHERE komoditas_id = :kid"
        ), {'kid': komoditas_id})
        for ins in insights:
            conn.execute(text("""
                INSERT INTO insight_prediksi (
                    komoditas_id, model_id, konten, tipe, ikon,
                    urutan, is_active, created_at, updated_at
                ) VALUES (
                    :kid, :model_id, :konten, :tipe, :ikon,
                    :urutan, 1, NOW(), NOW()
                )
            """), {
                'kid'     : komoditas_id,
                'model_id': model_id,
                'konten'  : ins['konten'],
                'tipe'    : ins['tipe'],
                'ikon'    : ins['ikon'],
                'urutan'  : ins['urutan'],
            })

def update_komoditas_volatilitas(komoditas_id: int, volatile: int, skor: float):
    engine = get_engine()
    with engine.begin() as conn:
        conn.execute(text("""
            UPDATE komoditas SET volatile=:v, volatilitas_skor=:s, updated_at=NOW()
            WHERE id=:kid
        """), {'v': volatile, 's': skor, 'kid': komoditas_id})


def save_all_model_results(all_results: dict, best_meta: dict, komoditas_id: int, pasar_id: int):
    """Save ALL model results to model_ml (not just best). Best gets is_active=1."""
    engine = get_engine()
    best_name = best_meta.get('nama_model', '')
    with engine.begin() as conn:
        # Deactivate old models for this komoditas
        conn.execute(text(
            "UPDATE model_ml SET is_active=0 WHERE komoditas_id=:kid"
        ), {'kid': komoditas_id})
        # Insert each model result
        for model_name, metrics in all_results.items():
            is_best = 1 if model_name == best_name or f"{model_name}_Tuned" == best_name else 0
            
            # Hitung stabilitas sederhana untuk kolom ENUM agar tidak error
            mape_val = metrics.get('mape', 0)
            stabilitas_label = (
                'optimal' if mape_val < 2 else 
                'baik' if mape_val < 5 else 
                'cukup' if mape_val < 10 else 
                'perlu_retrain'
            )

            conn.execute(text("""
                INSERT INTO model_ml (
                    komoditas_id, nama_model, versi, deskripsi, file_path,
                    mape, rmse, mae, r2_score, confidence_level,
                    stabilitas, status_validasi, catatan_validasi,
                    tanggal_training, tanggal_evaluasi, is_active,
                    created_at, updated_at
                ) VALUES (
                    :komoditas_id, :nama_model, '1.0', :deskripsi, '',
                    :mape, :rmse, :mae, :r2_score, 0,
                    :stabilitas, 'terverifikasi', '',
                    CURDATE(), CURDATE(), :is_active, NOW(), NOW()
                )
            """), {
                'komoditas_id': komoditas_id,
                'nama_model': model_name,
                'deskripsi': f"Model {model_name} untuk komoditas ID {komoditas_id}",
                'mape': round(mape_val, 4),
                'rmse': round(metrics.get('rmse', 0), 4),
                'mae': round(metrics.get('mae', 0), 4),
                'r2_score': round(metrics.get('r2', 0), 4),
                'stabilitas': stabilitas_label,
                'is_active': is_best,
            })


# ── READ untuk API response ───────────────────────────────────────────────────

def get_prediksi_data(komoditas_id: int):
    """Ambil semua data yang dibutuhkan frontend untuk satu komoditas."""
    engine = get_engine()

    komoditas = pd.read_sql(f"""
        SELECT k.*, kk.nama as kategori_nama
        FROM komoditas k
        JOIN kategori_komoditas kk ON kk.id = k.kategori_id
        WHERE k.id = {komoditas_id}
    """, engine).to_dict('records')
    komoditas = komoditas[0] if komoditas else None

    harga_terkini = pd.read_sql(f"""
        SELECT hh.harga, hh.tanggal, p.nama as pasar_nama, p.id as pasar_id
        FROM harga_harian hh
        JOIN pasar p ON p.id = hh.pasar_id
        WHERE hh.komoditas_id = {komoditas_id}
        ORDER BY hh.tanggal DESC LIMIT 1
    """, engine).to_dict('records')
    harga_terkini = harga_terkini[0] if harga_terkini else None

    # Best (active) model
    model = pd.read_sql(f"""
        SELECT * FROM model_ml WHERE komoditas_id={komoditas_id} AND is_active=1
        ORDER BY updated_at DESC LIMIT 1
    """, engine).to_dict('records')
    model = model[0] if model else None

    # ALL models for comparison (latest run)
    all_models = pd.read_sql(f"""
        SELECT nama_model, mape, rmse, mae, r2_score, is_active,
               tanggal_training, created_at
        FROM model_ml WHERE komoditas_id={komoditas_id}
          AND DATE(created_at) = (
            SELECT DATE(MAX(created_at)) FROM model_ml WHERE komoditas_id={komoditas_id}
          )
        ORDER BY rmse ASC
    """, engine).to_dict('records')

    ringkasan = pd.read_sql(f"""
        SELECT * FROM ringkasan_prediksi WHERE komoditas_id={komoditas_id}
        ORDER BY created_at DESC LIMIT 1
    """, engine).to_dict('records')
    ringkasan = ringkasan[0] if ringkasan else None

    hasil = pd.read_sql(f"""
        SELECT tanggal_target, harga_prediksi, confidence_level
        FROM hasil_prediksi
        WHERE komoditas_id={komoditas_id} AND tanggal_target >= CURDATE()
        ORDER BY tanggal_target ASC
    """, engine).to_dict('records')

    insights = pd.read_sql(f"""
        SELECT * FROM insight_prediksi
        WHERE komoditas_id={komoditas_id} AND is_active=1
        ORDER BY urutan ASC
    """, engine).to_dict('records')

    return {
        'komoditas'     : komoditas,
        'harga_terkini' : harga_terkini,
        'model'         : model,
        'all_models'    : all_models,
        'ringkasan'     : ringkasan,
        'prediksi_7hari': hasil,
        'insights'      : insights,
    }