<?php
/**
 * Migration: Create Konsumsi Monthly & Per-Member Limits Tables
 */

require_once __DIR__ . '/../config/database.php';
$db = getDB();

try {
    // Create monthly consumption limits table
    $db->exec("
        CREATE TABLE IF NOT EXISTS konsumsi_batas_bulanan (
            id INT AUTO_INCREMENT PRIMARY KEY,
            bulan INT NOT NULL COMMENT '1-12',
            batas DECIMAL(12,2) NOT NULL DEFAULT 50000,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_bulan (bulan)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        COMMENT='Monthly consumption spending limits (same for all members in a month)';
    ");
    
    // Create per-member consumption limits table
    $db->exec("
        CREATE TABLE IF NOT EXISTS konsumsi_batas_anggota (
            id INT AUTO_INCREMENT PRIMARY KEY,
            anggota_id INT NOT NULL,
            bulan INT NOT NULL COMMENT '1-12, NULL for annual override',
            batas DECIMAL(12,2) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_anggota_bulan (anggota_id, bulan),
            FOREIGN KEY (anggota_id) REFERENCES anggota(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        COMMENT='Per-member monthly consumption spending limits (optional override)';
    ");
    
    // Check if monthly limits already have data, if not populate with default 50000
    $checkStmt = $db->query("SELECT COUNT(*) as cnt FROM konsumsi_batas_bulanan");
    $result = $checkStmt->fetch();
    
    if ($result['cnt'] == 0) {
        // Populate all 12 months with default limit
        $stmt = $db->prepare("INSERT INTO konsumsi_batas_bulanan (bulan, batas) VALUES (?, ?)");
        for ($i = 1; $i <= 12; $i++) {
            $stmt->execute([$i, 50000]);
        }
        echo "✅ Monthly limits initialized (all months: Rp 50,000)<br>";
    } else {
        echo "✅ Monthly limits table already populated<br>";
    }
    
    echo "✅ Migration completed successfully!";
    
} catch (Exception $e) {
    die("❌ Migration failed: " . $e->getMessage());
}
?>
