backend/app/settings_store.py

# settings_store.py — SQLite Local Storage for Persistent Configs, Templates, & Schedules
import os
import sqlite3
import json

DB_DIR = os.path.join(os.path.dirname(os.path.dirname(__file__)), "data")
os.makedirs(DB_DIR, exist_ok=True)
DB_PATH = os.path.join(DB_DIR, "portal_config.db")

DEFAULT_HYPERPARAMS = {
    "lightgbm": {
        "num_leaves": 31,
        "max_depth": 7,
        "learning_rate": 0.05,
        "n_estimators": 300,
        "min_child_samples": 20,
        "subsample": 0.8,
        "colsample_bytree": 0.8,
        "reg_alpha": 0.1,
        "reg_lambda": 0.1
    },
    "xgboost": {
        "n_estimators": 300,
        "max_depth": 6,
        "learning_rate": 0.05,
        "subsample": 0.8,
        "colsample_bytree": 0.8,
        "reg_alpha": 0.1,
        "reg_lambda": 0.1
    },
    "arima": {
        "order": [5, 1, 0]
    },
    "sarima": {
        "order": [1, 1, 1],
        "seasonal_order": [1, 1, 1, 7]
    },
    "prophet": {
        "yearly_seasonality": True,
        "weekly_seasonality": True,
        "daily_seasonality": False,
        "changepoint_prior_scale": 0.05
    }
}

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_settings_db():
    """Create tables if not exists and seed default templates."""
    with get_connection() as conn:
        cursor = conn.cursor()
        
        # 1. Schedules table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS portal_schedules (
                id TEXT PRIMARY KEY,
                type TEXT NOT NULL,
                cron_expression TEXT NOT NULL,
                label TEXT,
                is_active INTEGER DEFAULT 1
            )
        """)
        
        # 2. Templates table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS ml_templates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                is_default INTEGER DEFAULT 0,
                training_days INTEGER DEFAULT 730,
                test_split_ratio REAL DEFAULT 0.3,
                forecast_days INTEGER DEFAULT 7,
                enable_optuna_tuning INTEGER DEFAULT 1,
                optuna_trials INTEGER DEFAULT 30,
                enabled_models TEXT NOT NULL,
                hyperparams TEXT NOT NULL
            )
        """)
        
        # 3. Commodity ML config table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS commodity_configs (
                komoditas_id INTEGER PRIMARY KEY,
                use_template INTEGER DEFAULT 1,
                training_days INTEGER DEFAULT 730,
                test_split_ratio REAL DEFAULT 0.3,
                forecast_days INTEGER DEFAULT 7,
                enable_optuna_tuning INTEGER DEFAULT 1,
                optuna_trials INTEGER DEFAULT 30,
                enabled_models TEXT,
                hyperparams TEXT
            )
        """)
        conn.commit()

        # Seed Default Template if empty
        cursor.execute("SELECT COUNT(*) FROM ml_templates WHERE is_default = 1")
        if cursor.fetchone()[0] == 0:
            cursor.execute("""
                INSERT INTO ml_templates (
                    name, is_default, training_days, test_split_ratio, forecast_days,
                    enable_optuna_tuning, optuna_trials, enabled_models, hyperparams
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                "Template Default",
                1,
                730,
                0.3,
                7,
                1,
                30,
                json.dumps(["lightgbm", "xgboost", "prophet", "sarima", "arima"]),
                json.dumps(DEFAULT_HYPERPARAMS)
            ))
            conn.commit()

# --- SCHEDULES CRUD ---
def get_schedules(sched_type=None):
    with get_connection() as conn:
        cursor = conn.cursor()
        if sched_type:
            cursor.execute("SELECT * FROM portal_schedules WHERE type = ?", (sched_type,))
        else:
            cursor.execute("SELECT * FROM portal_schedules")
        return [dict(row) for row in cursor.fetchall()]

def add_schedule(id_val, type_val, cron, label, is_active=1):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT OR REPLACE INTO portal_schedules (id, type, cron_expression, label, is_active)
            VALUES (?, ?, ?, ?, ?)
        """, (id_val, type_val, cron, label, is_active))
        conn.commit()

def remove_schedule(id_val):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM portal_schedules WHERE id = ?", (id_val,))
        conn.commit()

# --- TEMPLATES CRUD ---
def get_templates():
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM ml_templates ORDER BY is_default DESC, name ASC")
        rows = cursor.fetchall()
        result = []
        for r in rows:
            d = dict(r)
            d["enabled_models"] = json.loads(d["enabled_models"])
            d["hyperparams"] = json.loads(d["hyperparams"])
            result.append(d)
        return result

def get_default_template():
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM ml_templates WHERE is_default = 1 LIMIT 1")
        row = cursor.fetchone()
        if row:
            d = dict(row)
            d["enabled_models"] = json.loads(d["enabled_models"])
            d["hyperparams"] = json.loads(d["hyperparams"])
            return d
    return None

def add_template(name, training_days, test_split_ratio, forecast_days, enable_tuning, optuna_trials, enabled_models, hyperparams, is_default=0):
    with get_connection() as conn:
        cursor = conn.cursor()
        if is_default == 1:
            # Unset other default templates
            cursor.execute("UPDATE ml_templates SET is_default = 0")
        
        cursor.execute("""
            INSERT INTO ml_templates (
                name, is_default, training_days, test_split_ratio, forecast_days,
                enable_optuna_tuning, optuna_trials, enabled_models, hyperparams
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            name, is_default, training_days, test_split_ratio, forecast_days,
            enable_tuning, optuna_trials, json.dumps(enabled_models), json.dumps(hyperparams)
        ))
        conn.commit()
        return cursor.lastrowid

def update_template(id_val, name, training_days, test_split_ratio, forecast_days, enable_tuning, optuna_trials, enabled_models, hyperparams, is_default=0):
    with get_connection() as conn:
        cursor = conn.cursor()
        if is_default == 1:
            cursor.execute("UPDATE ml_templates SET is_default = 0 WHERE id != ?", (id_val,))
        
        cursor.execute("""
            UPDATE ml_templates
            SET name = ?, is_default = ?, training_days = ?, test_split_ratio = ?, forecast_days = ?,
                enable_optuna_tuning = ?, optuna_trials = ?, enabled_models = ?, hyperparams = ?
            WHERE id = ?
        """, (
            name, is_default, training_days, test_split_ratio, forecast_days,
            enable_tuning, optuna_trials, json.dumps(enabled_models), json.dumps(hyperparams), id_val
        ))
        conn.commit()

def delete_template(id_val):
    with get_connection() as conn:
        cursor = conn.cursor()
        # Check if it was default
        cursor.execute("SELECT is_default FROM ml_templates WHERE id = ?", (id_val,))
        row = cursor.fetchone()
        if row and row["is_default"] == 1:
            # Cannot delete default unless another exists. We raise exception
            cursor.execute("SELECT COUNT(*) FROM ml_templates")
            if cursor.fetchone()[0] <= 1:
                raise ValueError("Tidak dapat menghapus satu-satunya template.")
            # Set another template as default
            cursor.execute("UPDATE ml_templates SET is_default = 1 WHERE id != ? LIMIT 1", (id_val,))
            
        cursor.execute("DELETE FROM ml_templates WHERE id = ?", (id_val,))
        conn.commit()

def set_default_template(id_val):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE ml_templates SET is_default = 0")
        cursor.execute("UPDATE ml_templates SET is_default = 1 WHERE id = ?", (id_val,))
        conn.commit()

# --- COMMODITY CONFIGS CRUD ---
def get_commodity_config(komoditas_id):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM commodity_configs WHERE komoditas_id = ?", (komoditas_id,))
        row = cursor.fetchone()
        if row:
            d = dict(row)
            if d["enabled_models"]:
                d["enabled_models"] = json.loads(d["enabled_models"])
            if d["hyperparams"]:
                d["hyperparams"] = json.loads(d["hyperparams"])
            return d
        
        # If not exists, return a dummy pointing to default template
        return {
            "komoditas_id": komoditas_id,
            "use_template": 1,
            "training_days": 730,
            "test_split_ratio": 0.3,
            "forecast_days": 7,
            "enable_optuna_tuning": 1,
            "optuna_trials": 30,
            "enabled_models": ["lightgbm", "xgboost", "prophet", "sarima", "arima"],
            "hyperparams": DEFAULT_HYPERPARAMS
        }

def save_commodity_config(komoditas_id, use_template, training_days, test_split_ratio, forecast_days, enable_tuning, optuna_trials, enabled_models, hyperparams):
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT OR REPLACE INTO commodity_configs (
                komoditas_id, use_template, training_days, test_split_ratio, forecast_days,
                enable_optuna_tuning, optuna_trials, enabled_models, hyperparams
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            komoditas_id, use_template, training_days, test_split_ratio, forecast_days,
            enable_tuning, optuna_trials, json.dumps(enabled_models), json.dumps(hyperparams)
        ))
        conn.commit()

def get_resolved_config(komoditas_id):
    """
    Get resolved ML configuration for a commodity.
    If use_template is 1 or no custom config exists, resolves to default template.
    """
    config = get_commodity_config(komoditas_id)
    if config.get("use_template") == 1:
        default_tmpl = get_default_template()
        if default_tmpl:
            # Override komoditas_id and use_template
            default_tmpl["komoditas_id"] = komoditas_id
            default_tmpl["use_template"] = 1
            return default_tmpl
    return config

# Run initialization on import
init_settings_db()