<?php
/**
 * Fix SQLite Tables - Pastikan semua tabel dibuat dengan benar
 */

echo "<h3>Fix SQLite Database Tables</h3>";

$sqlite_path = 'data/sipp_sync.sqlite';

try {
    // Connect to SQLite
    $sqlite_pdo = new PDO("sqlite:$sqlite_path");
    $sqlite_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<div style='color: green;'>✅ Connected to SQLite: $sqlite_path</div>";
    
    // Create arsip table
    $sql_arsip = "CREATE TABLE IF NOT EXISTS arsip (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nomor_perkara TEXT NOT NULL,
        nomor_arsip TEXT,
        no_ruang TEXT,
        no_lemari TEXT,
        no_rak TEXT,
        no_berkas TEXT,
        tanggal_sync DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(nomor_perkara, nomor_arsip)
    )";
    $sqlite_pdo->exec($sql_arsip);
    echo "<div style='color: green;'>✅ Table 'arsip' created/verified</div>";
    
    // Create sync_log table
    $sql_sync_log = "CREATE TABLE IF NOT EXISTS sync_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        sync_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        total_records INTEGER,
        success_records INTEGER,
        error_records INTEGER,
        sync_duration REAL,
        status TEXT,
        error_message TEXT
    )";
    $sqlite_pdo->exec($sql_sync_log);
    echo "<div style='color: green;'>✅ Table 'sync_log' created/verified</div>";
    
    // Create indexes
    $sqlite_pdo->exec("CREATE INDEX IF NOT EXISTS idx_nomor_perkara ON arsip(nomor_perkara)");
    $sqlite_pdo->exec("CREATE INDEX IF NOT EXISTS idx_nomor_arsip ON arsip(nomor_arsip)");
    echo "<div style='color: green;'>✅ Indexes created/verified</div>";
    
    // Check tables
    echo "<h4>Database Tables:</h4>";
    $stmt = $sqlite_pdo->query("SELECT name FROM sqlite_master WHERE type='table'");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    foreach ($tables as $table) {
        echo "<div style='color: blue;'>📋 Table: $table</div>";
        
        // Count records
        $count_stmt = $sqlite_pdo->query("SELECT COUNT(*) FROM $table");
        $count = $count_stmt->fetchColumn();
        echo "<div>   Records: " . number_format($count) . "</div>";
    }
    
    // File info
    echo "<h4>File Information:</h4>";
    if (file_exists($sqlite_path)) {
        $file_size = filesize($sqlite_path);
        echo "<div><strong>File Size:</strong> " . number_format($file_size / 1024, 2) . " KB</div>";
        echo "<div><strong>Full Path:</strong> " . realpath($sqlite_path) . "</div>";
    }
    
    echo "<div style='color: green; font-weight: bold; margin-top: 20px;'>🎯 ALL TABLES FIXED!</div>";
    echo "<div>Sekarang coba akses test SQLite lagi.</div>";
    
} catch (Exception $e) {
    echo "<div style='color: red;'>❌ Error: " . $e->getMessage() . "</div>";
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Fix SQLite Tables</title>
</head>
<body>
    <hr>
    <p>
        <a href="test-sqlite-arsip.php">📊 Test Data SQLite</a> | 
        <a href="sync-sipp-to-sqlite.php">🔄 Sync SIPP</a> |
        <a href="arsip-sqlite.php">👁️ Lihat Arsip SQLite</a>
    </p>
</body>
</html>
