<?php
/**
 * SIMPLE DATABASE CONNECTION TEST
 * Upload file ini ke folder yang sama dengan api.php
 * Akses via: http://localhost/path/to/test_db.php
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);
?>
<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <title>Test Database Connection</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f5f5f5;
        }
        .box {
            background: white;
            padding: 20px;
            margin: 20px 0;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        .success { color: #22c55e; font-weight: bold; }
        .error { color: #ef4444; font-weight: bold; }
        h2 { color: #333; border-bottom: 2px solid #667eea; padding-bottom: 10px; }
        code { 
            background: #f1f5f9;
            padding: 2px 6px;
            border-radius: 3px;
            font-family: monospace;
        }
        ul { line-height: 1.8; }
    </style>
</head>
<body>

<div class="box">
    <h2>🔍 Test Koneksi Database</h2>
    
    <?php
    // Konfigurasi - SESUAIKAN DENGAN api.php ANDA
    $host = "localhost";
    $user = "root";
    $pass = "";
    $db   = "inventory_acf";

    echo "<p><strong>Konfigurasi:</strong></p>";
    echo "<ul>";
    echo "<li>Host: <code>$host</code></li>";
    echo "<li>User: <code>$user</code></li>";
    echo "<li>Database: <code>$db</code></li>";
    echo "</ul>";

    // Test 1: Koneksi ke MySQL
    echo "<h3>1. Test Koneksi MySQL</h3>";
    $conn = @new mysqli($host, $user, $pass);
    
    if ($conn->connect_error) {
        echo "<p class='error'>❌ GAGAL: " . $conn->connect_error . "</p>";
        echo "<p><strong>Solusi:</strong></p>";
        echo "<ul>";
        echo "<li>Pastikan MySQL berjalan: <code>sudo systemctl status mysql</code></li>";
        echo "<li>Cek username dan password</li>";
        echo "<li>Restart MySQL: <code>sudo systemctl restart mysql</code></li>";
        echo "</ul>";
        exit;
    } else {
        echo "<p class='success'>✅ Koneksi ke MySQL berhasil!</p>";
    }

    // Test 2: Cek Database
    echo "<h3>2. Test Database '$db'</h3>";
    $result = $conn->query("SHOW DATABASES LIKE '$db'");
    
    if ($result->num_rows === 0) {
        echo "<p class='error'>❌ Database '$db' TIDAK DITEMUKAN!</p>";
        echo "<p><strong>Database yang tersedia:</strong></p><ul>";
        $result = $conn->query("SHOW DATABASES");
        while ($row = $result->fetch_array()) {
            echo "<li><code>" . $row[0] . "</code></li>";
        }
        echo "</ul>";
        echo "<p><strong>Solusi:</strong></p>";
        echo "<p>Buat database atau import SQL:</p>";
        echo "<code>CREATE DATABASE $db;</code><br>";
        echo "<code>mysql -u root -p $db < setup_database.sql</code>";
        $conn->close();
        exit;
    } else {
        echo "<p class='success'>✅ Database '$db' ditemukan!</p>";
    }

    // Pilih database
    if (!$conn->select_db($db)) {
        echo "<p class='error'>❌ Tidak bisa memilih database: " . $conn->error . "</p>";
        exit;
    }

    // Test 3: Cek Tabel
    echo "<h3>3. Test Tabel 'kepatuhan_pembayaran'</h3>";
    $result = $conn->query("SHOW TABLES LIKE 'kepatuhan_pembayaran'");
    
    if ($result->num_rows === 0) {
        echo "<p class='error'>❌ Tabel 'kepatuhan_pembayaran' TIDAK DITEMUKAN!</p>";
        
        // Tampilkan tabel yang ada
        $result = $conn->query("SHOW TABLES");
        if ($result->num_rows > 0) {
            echo "<p><strong>Tabel yang tersedia:</strong></p><ul>";
            while ($row = $result->fetch_array()) {
                echo "<li><code>" . $row[0] . "</code></li>";
            }
            echo "</ul>";
        } else {
            echo "<p class='error'>Database kosong, tidak ada tabel!</p>";
        }
        
        echo "<p><strong>Solusi:</strong></p>";
        echo "<p>Import SQL schema:</p>";
        echo "<code>mysql -u root -p $db < setup_database.sql</code>";
        $conn->close();
        exit;
    } else {
        echo "<p class='success'>✅ Tabel 'kepatuhan_pembayaran' ditemukan!</p>";
    }

    // Test 4: Cek Struktur Tabel
    echo "<h3>4. Struktur Tabel</h3>";
    $result = $conn->query("DESCRIBE kepatuhan_pembayaran");
    
    if ($result) {
        echo "<p class='success'>✅ Struktur tabel:</p>";
        echo "<table border='1' cellpadding='5' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr style='background: #f1f5f9;'><th>Field</th><th>Type</th><th>Null</th></tr>";
        while ($row = $result->fetch_assoc()) {
            echo "<tr>";
            echo "<td><code>" . $row['Field'] . "</code></td>";
            echo "<td>" . $row['Type'] . "</td>";
            echo "<td>" . $row['Null'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        // Cek kolom yang diperlukan
        $requiredFields = ['period', 'ta', 'month', 'target', 'capaian', 'piutang', 'persen', 'kategori'];
        $existingFields = [];
        
        $result = $conn->query("DESCRIBE kepatuhan_pembayaran");
        while ($row = $result->fetch_assoc()) {
            $existingFields[] = $row['Field'];
        }
        
        $missingFields = array_diff($requiredFields, $existingFields);
        
        if (empty($missingFields)) {
            echo "<p class='success'>✅ Semua kolom yang diperlukan ada!</p>";
        } else {
            echo "<p class='error'>❌ Kolom yang hilang: " . implode(', ', $missingFields) . "</p>";
        }
        
    } else {
        echo "<p class='error'>❌ Gagal mengambil struktur: " . $conn->error . "</p>";
    }

    // Test 5: Cek Data
    echo "<h3>5. Test Data</h3>";
    $result = $conn->query("SELECT COUNT(*) as cnt FROM kepatuhan_pembayaran");
    
    if ($result) {
        $row = $result->fetch_assoc();
        $count = $row['cnt'];
        
        if ($count > 0) {
            echo "<p class='success'>✅ Ditemukan $count baris data</p>";
            
            // Test query periode
            $result = $conn->query("SELECT DISTINCT period FROM kepatuhan_pembayaran ORDER BY period DESC");
            
            if ($result->num_rows > 0) {
                echo "<p><strong>Periode yang tersedia:</strong></p><ul>";
                while ($row = $result->fetch_assoc()) {
                    echo "<li><code>" . $row['period'] . "</code></li>";
                }
                echo "</ul>";
            } else {
                echo "<p class='error'>❌ Tidak ada periode ditemukan!</p>";
            }
            
        } else {
            echo "<p class='error'>❌ Tabel kosong (0 baris)</p>";
            echo "<p><strong>Solusi:</strong> Import data dengan SQL file</p>";
        }
    } else {
        echo "<p class='error'>❌ Gagal query data: " . $conn->error . "</p>";
    }

    // Test 6: Simulasi Query API
    echo "<h3>6. Simulasi Query API</h3>";
    
    $testPeriod = null;
    $result = $conn->query("SELECT DISTINCT period FROM kepatuhan_pembayaran ORDER BY period DESC LIMIT 1");
    if ($result && $result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $testPeriod = $row['period'];
        
        echo "<p>Testing dengan periode: <code>$testPeriod</code></p>";
        
        // Query seperti di API
        $sql = "SELECT ta, target, capaian, piutang, persen, kategori 
                FROM kepatuhan_pembayaran 
                WHERE period = ? AND month = 'TOTAL' 
                ORDER BY ta";
        
        $stmt = $conn->prepare($sql);
        if ($stmt) {
            $stmt->bind_param("s", $testPeriod);
            if ($stmt->execute()) {
                $result = $stmt->get_result();
                $rowCount = $result->num_rows;
                
                echo "<p class='success'>✅ Query berhasil! Ditemukan $rowCount baris</p>";
                
                if ($rowCount > 0) {
                    echo "<p><strong>Sample data (3 baris pertama):</strong></p>";
                    echo "<table border='1' cellpadding='5' style='border-collapse: collapse; width: 100%;'>";
                    echo "<tr style='background: #f1f5f9;'><th>TA</th><th>Target</th><th>Capaian</th><th>Kategori</th></tr>";
                    
                    $i = 0;
                    while ($row = $result->fetch_assoc() && $i < 3) {
                        echo "<tr>";
                        echo "<td>" . $row['ta'] . "</td>";
                        echo "<td>Rp " . number_format($row['target'], 0, ',', '.') . "</td>";
                        echo "<td>Rp " . number_format($row['capaian'], 0, ',', '.') . "</td>";
                        echo "<td>" . $row['kategori'] . "</td>";
                        echo "</tr>";
                        $i++;
                    }
                    echo "</table>";
                }
                
            } else {
                echo "<p class='error'>❌ Gagal execute: " . $stmt->error . "</p>";
            }
            $stmt->close();
        } else {
            echo "<p class='error'>❌ Gagal prepare statement: " . $conn->error . "</p>";
        }
    } else {
        echo "<p class='error'>❌ Tidak ada data periode untuk test</p>";
    }

    $conn->close();
    ?>

</div>

<div class="box">
    <h2>🎯 Kesimpulan</h2>
    <p>Jika semua test di atas <span class="success">✅ BERHASIL</span>, maka:</p>
    <ul>
        <li>Database connection OK</li>
        <li>Tabel struktur OK</li>
        <li>Data tersedia</li>
        <li>Query API seharusnya berfungsi</li>
    </ul>
    
    <p><strong>Langkah selanjutnya:</strong></p>
    <ol>
        <li>Pastikan file <code>api.php</code> menggunakan konfigurasi database yang sama</li>
        <li>Aktifkan error display di <code>api.php</code>: <code>ini_set('display_errors', 1);</code></li>
        <li>Test API: <code>api.php?action=get_periods</code></li>
        <li>Cek error log jika masih error 500</li>
    </ol>
</div>

</body>
</html>