<?php
/**
 * =========================================================================
 * migrate_api_keys.php — MIGRASI STATIS → DINAMIS
 * =========================================================================
 * Jalankan SEKALI untuk memindahkan API key dari kolom settings
 * ke tabel baru `api_keys`. Aman dijalankan berkali-kali (idempotent).
 *
 * Cara pakai: buka di browser → http://localhost/xieqa/llm/api/migrate_api_keys.php
 * =========================================================================
 */

session_start();
include '../../config/database.php';

header('Content-Type: text/html; charset=utf-8');
echo "<h2>🔄 Migrasi API Keys: Statis → Dinamis</h2><hr>";

// =========================================================================
// STEP 1: Buat tabel api_keys jika belum ada
// =========================================================================
$create_table = "CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(20) NOT NULL DEFAULT 'gemini',
    api_key VARCHAR(255) NOT NULL,
    label VARCHAR(50) DEFAULT NULL,
    status ENUM('ready','cooldown','suspended') DEFAULT 'ready',
    fail_count INT DEFAULT 0,
    cooldown_until DATETIME DEFAULT NULL,
    last_used DATETIME DEFAULT '2000-01-01 00:00:00',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_key (api_key(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

if (mysqli_query($conn, $create_table)) {
    echo "<p>✅ Tabel <code>api_keys</code> siap.</p>";
} else {
    echo "<p>❌ Gagal buat tabel: " . mysqli_error($conn) . "</p>";
    exit;
}

// =========================================================================
// STEP 2: Baca API keys dari settings (kolom statis lama)
// =========================================================================
$q_cfg = mysqli_query($conn, "SELECT * FROM settings WHERE id = 1");
$cfg = mysqli_fetch_assoc($q_cfg);

if (!$cfg) {
    echo "<p>❌ Tidak bisa baca tabel settings.</p>";
    exit;
}

$migrated = 0;
$skipped = 0;

// Mapping kolom settings → provider
$slot_map = [];
for ($i = 1; $i <= 8; $i++) {
    $slot_map[$i == 1 ? 'gemini_api_key' : "gemini_api_key_$i"] = ['provider' => 'gemini', 'label' => "Gemini Slot $i"];
    $slot_map[$i == 1 ? 'groq_api_key' : "groq_api_key_$i"] = ['provider' => 'groq', 'label' => "Groq Slot $i"];
}

foreach ($slot_map as $col_name => $meta) {
    $key_value = trim($cfg[$col_name] ?? '');
    if (empty($key_value)) continue; // Skip slot kosong
    
    // Cek apakah key sudah ada di tabel baru (hindari duplikat)
    $escaped_key = mysqli_real_escape_string($conn, $key_value);
    $check = mysqli_query($conn, "SELECT id FROM api_keys WHERE api_key = '$escaped_key'");
    
    if ($check && mysqli_num_rows($check) > 0) {
        $skipped++;
        echo "<p>⏭️ <code>$col_name</code> → sudah ada di tabel, skip.</p>";
        continue;
    }
    
    // Ambil status health dari api_health_monitor (jika ada)
    $status = 'ready';
    $fail_count = 0;
    $cooldown_until = 'NULL';
    $last_used = "'2000-01-01 00:00:00'";
    
    $q_health = mysqli_query($conn, "SELECT * FROM api_health_monitor WHERE slot_id = '$col_name'");
    if ($q_health && $health = mysqli_fetch_assoc($q_health)) {
        $status = $health['status'] ?? 'ready';
        $fail_count = (int)($health['fail_count'] ?? 0);
        if (!empty($health['cooldown_until'])) {
            $cooldown_until = "'" . mysqli_real_escape_string($conn, $health['cooldown_until']) . "'";
        }
        if (!empty($health['last_used'])) {
            $last_used = "'" . mysqli_real_escape_string($conn, $health['last_used']) . "'";
        }
        // Reset suspended keys to ready pada migrasi
        if ($status === 'suspended') $status = 'ready';
    }
    
    $provider = $meta['provider'];
    $label = mysqli_real_escape_string($conn, $meta['label']);
    
    $insert = "INSERT INTO api_keys (provider, api_key, label, status, fail_count, cooldown_until, last_used)
               VALUES ('$provider', '$escaped_key', '$label', '$status', $fail_count, $cooldown_until, $last_used)";
    
    if (mysqli_query($conn, $insert)) {
        $migrated++;
        echo "<p>✅ <code>$col_name</code> → berhasil dipindahkan sebagai <strong>$provider</strong> key.</p>";
    } else {
        echo "<p>❌ Gagal pindahkan <code>$col_name</code>: " . mysqli_error($conn) . "</p>";
    }
}

// =========================================================================
// STEP 3: Ringkasan
// =========================================================================
echo "<hr>";
echo "<h3>📊 Ringkasan Migrasi</h3>";
echo "<p><strong>Dipindahkan:</strong> $migrated key</p>";
echo "<p><strong>Di-skip (duplikat):</strong> $skipped key</p>";

$total = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as total FROM api_keys"));
echo "<p><strong>Total key di tabel baru:</strong> " . $total['total'] . "</p>";

// Breakdown per provider
$q_gemini = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as jml FROM api_keys WHERE provider='gemini'"));
$q_groq = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(*) as jml FROM api_keys WHERE provider='groq'"));
echo "<p>🔵 Gemini: <strong>" . $q_gemini['jml'] . "</strong> key</p>";
echo "<p>🟠 Groq: <strong>" . $q_groq['jml'] . "</strong> key</p>";

// =========================================================================
// STEP 4: Auto-Reset semua status ke READY (bersihkan status lama)
// =========================================================================
mysqli_query($conn, "UPDATE api_keys SET status = 'ready', fail_count = 0, cooldown_until = NULL");
echo "<hr><p>🔄 <strong>Auto-Reset:</strong> Semua key direset ke status <span style='color:green;font-weight:bold;'>READY / PRIMA</span>.</p>";

echo "<hr><p style='color: green; font-weight: bold;'>✅ Migrasi selesai! Sekarang API key dikelola dari tabel <code>api_keys</code>.</p>";
echo "<p><a href='../admin/konfigurasi.php'>→ Buka halaman Konfigurasi</a></p>";
?>
