<?php
/**
 * Dana Pensiun Data Checker & Helper
 * Diagnostics and data insertion utility
 */

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

$action = $_GET['action'] ?? '';

// Get data status
$totalDanaPensiun = $db->query("SELECT COUNT(*) as c FROM simpanan WHERE jenis='dana_pensiun'")->fetch()['c'];
$totalAnggota = $db->query("SELECT COUNT(*) as c FROM anggota WHERE status='aktif'")->fetch()['c'];
$allJenis = $db->query("SELECT DISTINCT jenis FROM simpanan")->fetchAll(PDO::FETCH_COLUMN);

?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Dana Pensiun Checker</title>
    <style>
        body { font-family: Arial; margin: 20px; background: #f5f5f5; }
        .card { background: white; padding: 20px; margin: 10px 0; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .stat { font-size: 24px; font-weight: bold; color: #2563eb; }
        .warn { color: #dc2626; }
        .ok { color: #16a34a; }
        button { background: #2563eb; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; margin: 5px; }
        button:hover { background: #1d4ed8; }
        pre { background: #f0f0f0; padding: 10px; border-radius: 4px; overflow-x: auto; font-size: 12px; }
    </style>
</head>
<body>

<h1>📊 Dana Pensiun - Status Checker</h1>

<div class="card">
    <h2>📈 Data Status</h2>
    <p>Total Dana Pensiun Records: <span class="stat <?=$totalDanaPensiun > 0 ? 'ok' : 'warn'?>"><?=$totalDanaPensiun?></span></p>
    <p>Total Active Members: <span class="stat ok"><?=$totalAnggota?></span></p>
    <p>Available Jenis Types: <span class="stat ok"><?=implode(', ', $allJenis)?></span></p>
</div>

<div class="card">
    <h2>🔧 Diagnostic Options</h2>
    <p><button onclick="location='?action=view_data'">📋 View Raw Data</button></p>
    <p><button onclick="location='?action=insert_sample'">➕ Insert Sample Data</button></p>
    <p><button onclick="location='?action=query_debug'">🐛 Debug Query</button></p>
</div>

<?php
if ($action === 'view_data') {
    echo '<div class="card"><h2>💾 Raw Dana Pensiun Data</h2>';
    $data = $db->query("SELECT s.id, s.anggota_id, a.nama, s.tipe, s.jumlah, s.tanggal FROM simpanan s LEFT JOIN anggota a ON s.anggota_id=a.id WHERE s.jenis='dana_pensiun' ORDER BY s.tanggal DESC LIMIT 20")->fetchAll();
    echo '<pre>' . json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . '</pre>';
    echo '</div>';
}

if ($action === 'query_debug') {
    echo '<div class="card"><h2>🐛 Query Debug</h2><pre>';
    $query = "
    SELECT s.*, a.nama, a.no_anggota
    FROM simpanan s JOIN anggota a ON s.anggota_id=a.id
    WHERE s.jenis = 'dana_pensiun'
    ORDER BY s.tanggal DESC, s.created_at DESC
    LIMIT 15 OFFSET 0
    ";
    echo "SQL Query:\n$query\n\n";
    
    $result = $db->prepare($query)->execute();
    $stmt = $db->prepare($query);
    $stmt->execute();
    $rows = $stmt->fetchAll();
    echo "Result Count: " . count($rows) . "\n";
    echo "Results:\n" . json_encode($rows, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
    echo '</pre></div>';
}

if ($action === 'insert_sample') {
    // Get first 2 active members
    $members = $db->query("SELECT id FROM anggota WHERE status='aktif' LIMIT 3")->fetchAll(PDO::FETCH_COLUMN);
    
    if (count($members) >= 2) {
        try {
            $db->beginTransaction();
            
            $stmt = $db->prepare("INSERT INTO simpanan (anggota_id, jenis, tipe, jumlah, tanggal, keterangan, created_by) VALUES (?, ?, ?, ?, ?, ?, 1)");
            
            $stmt->execute([$members[0], 'dana_pensiun', 'setoran', 500000, date('Y-m-01'), 'Simpanan dana pensiun bulanan']);
            $stmt->execute([$members[0], 'dana_pensiun', 'setoran', 500000, date('Y-m-d'),  'Simpanan dana pensiun harian']);
            $stmt->execute([$members[1], 'dana_pensiun', 'setoran', 300000, date('Y-m-15'), 'Simpanan dana pensiun']);
            
            $db->commit();
            echo '<div class="card"><h2 class="ok">✅ 3 Sample Records Inserted!</h2>
            <p>Inserted for members: ' . implode(', ', array_slice($members, 0, 2)) . '</p>
            <p><button onclick="location=\'simpanan_dana_pensiun.php\'">👉 Go to Dana Pensiun Page</button></p>
            </div>';
        } catch (Exception $e) {
            echo '<div class="card"><h2 class="warn">❌ Error: ' . htmlspecialchars($e->getMessage()) . '</h2></div>';
        }
    } else {
        echo '<div class="card"><h2 class="warn">❌ Not enough members (need at least 2)</h2></div>';
    }
}
?>

<div class="card">
    <h2>ℹ️ Info</h2>
    <p>If Dana Pensiun page shows empty but count > 0 here, there might be a JOIN issue or display error.</p>
    <p><a href="simpanan_dana_pensiun.php">← Back to Dana Pensiun</a></p>
</div>

</body>
</html>
