<?php
/**
 * Debug & Fix Dana Pensiun - Check and fix imported data dates
 */

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

header('Content-Type: text/html; charset=utf-8');

// Handle fix action
$fixed = false;
if (isset($_GET['action']) && $_GET['action'] === 'fix_dates') {
    try {
        $db->beginTransaction();
        
        // Get all dana_pensiun records with wrong dates
        $records = $db->query("
            SELECT id, tanggal, created_at
            FROM simpanan
            WHERE jenis = 'dana_pensiun'
            AND (tanggal IS NULL OR tanggal < '2020-01-01' OR tanggal > '2030-12-31')
        ")->fetchAll();
        
        $fixedCount = 0;
        foreach ($records as $r) {
            // If date is invalid or looks like Excel serial number
            $newDate = date('Y-m-d'); // Default to today
            
            $stmt = $db->prepare("UPDATE simpanan SET tanggal = ? WHERE id = ?");
            $stmt->execute([$newDate, $r['id']]);
            $fixedCount++;
        }
        
        $db->commit();
        $fixed = true;
        $fixedMessage = "Berhasil memperbaiki $fixedCount record dengan tanggal yang salah.";
    } catch (Exception $e) {
        $db->rollBack();
        $fixed = false;
        $fixedMessage = "Error: " . $e->getMessage();
    }
}

// Get all dana_pensiun records with their dates
$allRecords = $db->query("
    SELECT s.id, s.anggota_id, a.nama, a.no_anggota, s.tipe, s.jumlah, s.tanggal, s.keterangan, s.created_at,
           DATE_FORMAT(s.tanggal, '%Y-%m') as bulan_tanggal
    FROM simpanan s
    LEFT JOIN anggota a ON s.anggota_id = a.id
    WHERE s.jenis = 'dana_pensiun'
    ORDER BY s.created_at DESC
")->fetchAll();

$groupedByBulan = [];
foreach ($allRecords as $r) {
    $bulan = $r['bulan_tanggal'] ?? 'Tidak Ada';
    if (!isset($groupedByBulan[$bulan])) {
        $groupedByBulan[$bulan] = ['count' => 0, 'total' => 0];
    }
    $groupedByBulan[$bulan]['count']++;
    $groupedByBulan[$bulan]['total'] += $r['jumlah'];
}

// Summary for current month vs all time
$summaryAll = $db->query("
    SELECT 
        COUNT(*) as total_records,
        SUM(CASE WHEN tipe='setoran' THEN jumlah ELSE 0 END) as total_setoran,
        SUM(CASE WHEN tipe='penarikan' THEN jumlah ELSE 0 END) as total_penarikan,
        MIN(tanggal) as first_date,
        MAX(tanggal) as last_date
    FROM simpanan
    WHERE jenis = 'dana_pensiun'
")->fetch();

$summaryApril = $db->query("
    SELECT 
        COUNT(*) as total_records,
        SUM(CASE WHEN tipe='setoran' THEN jumlah ELSE 0 END) as total_setoran
    FROM simpanan
    WHERE jenis = 'dana_pensiun'
    AND DATE_FORMAT(tanggal, '%Y-%m') = '2026-04'
")->fetch();

?>
<!DOCTYPE html>
<html>
<head>
    <title>Debug & Fix Dana Pensiun</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        .container { max-width: 1200px; margin: 0 auto; }
        .card { background: white; padding: 20px; margin: 20px 0; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        h1 { color: #2c3e50; }
        h2 { color: #34495e; border-bottom: 2px solid #3498db; padding-bottom: 10px; }
        table { border-collapse: collapse; width: 100%; margin: 20px 0; }
        th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
        th { background-color: #3498db; color: white; }
        tr:nth-child(even) { background-color: #f8f9fa; }
        .success { color: #27ae60; font-weight: bold; }
        .error { color: #e74c3c; font-weight: bold; }
        .warning { color: #f39c12; font-weight: bold; }
        .info { background: #e3f2fd; padding: 15px; border-left: 4px solid #2196f3; margin: 20px 0; }
        .btn { display: inline-block; padding: 10px 20px; background: #3498db; color: white; text-decoration: none; border-radius: 5px; margin: 5px; }
        .btn:hover { background: #2980b9; }
        .btn-danger { background: #e74c3c; }
        .btn-danger:hover { background: #c0392b; }
        .btn-success { background: #27ae60; }
        .btn-success:hover { background: #229954; }
        .stat { display: inline-block; margin: 10px 20px 10px 0; padding: 15px; background: #f8f9fa; border-radius: 5px; }
        .stat-value { font-size: 24px; font-weight: bold; color: #2c3e50; }
        .stat-label { font-size: 12px; color: #7f8c8d; }
    </style>
</head>
<body>
    <div class="container">
        <h1>🔍 Debug & Fix Dana Pensiun</h1>
        
        <?php if ($fixed): ?>
            <div class="info">
                <strong>✅ Berhasil!</strong> <?= $fixedMessage ?>
            </div>
        <?php endif; ?>
        
        <!-- Summary Statistics -->
        <div class="card">
            <h2>📊 Summary Statistics</h2>
            
            <div class="stat">
                <div class="stat-value"><?= number_format($summaryAll['total_records']) ?></div>
                <div class="stat-label">Total Records (Semua Waktu)</div>
            </div>
            
            <div class="stat">
                <div class="stat-value"><?= number_format($summaryAll['total_setoran']) ?></div>
                <div class="stat-label">Total Setoran (Semua Waktu)</div>
            </div>
            
            <div class="stat">
                <div class="stat-value"><?= number_format($summaryApril['total_records']) ?></div>
                <div class="stat-label">Records April 2026</div>
            </div>
            
            <div class="stat">
                <div class="stat-value"><?= number_format($summaryApril['total_setoran']) ?></div>
                <div class="stat-label">Setoran April 2026</div>
            </div>
            
            <?php if ($summaryAll['first_date']): ?>
            <div class="stat">
                <div class="stat-value"><?= $summaryAll['first_date'] ?></div>
                <div class="stat-label">Tanggal Pertama</div>
            </div>
            
            <div class="stat">
                <div class="stat-value"><?= $summaryAll['last_date'] ?></div>
                <div class="stat-label">Tanggal Terakhir</div>
            </div>
            <?php endif; ?>
        </div>
        
        <!-- Records Grouped by Month -->
        <div class="card">
            <h2>📅 Data Dana Pensiun Grouped by Month</h2>
            
            <?php if (empty($groupedByBulan)): ?>
                <p class="error">Tidak ada data dana_pensiun di database!</p>
            <?php else: ?>
                <table>
                    <tr>
                        <th>Bulan</th>
                        <th>Jumlah Records</th>
                        <th>Total Nominal</th>
                        <th>Action</th>
                    </tr>
                    <?php foreach ($groupedByBulan as $bulan => $data): ?>
                        <tr>
                            <td><strong><?= $bulan ?></strong></td>
                            <td><?= number_format($data['count']) ?></td>
                            <td>Rp <?= number_format($data['total'], 0, ',', '.') ?></td>
                            <td>
                                <?php if ($bulan === '2026-04'): ?>
                                    <span class="success">✓ Sudah di April 2026</span>
                                <?php elseif ($bulan === 'Tidak Ada' || $bulan < '2020-01'): ?>
                                    <span class="error">⚠ Tanggal tidak valid!</span>
                                    <a href="?action=fix_dates" class="btn btn-success" onclick="return confirm('Perbaiki semua tanggal ke hari ini?')">Fix Dates</a>
                                <?php else: ?>
                                    <span class="warning">⚠ Bukan April 2026</span>
                                    <a href="simpanan/simpanan_dana_pensiun.php?bulan=<?= $bulan ?>" class="btn">Lihat</a>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </table>
            <?php endif; ?>
        </div>
        
        <!-- All Records Detail -->
        <div class="card">
            <h2>📋 Detail Semua Records Dana Pensiun</h2>
            
            <?php if (empty($allRecords)): ?>
                <p class="error">Tidak ada data!</p>
            <?php else: ?>
                <table>
                    <tr>
                        <th>ID</th>
                        <th>Anggota</th>
                        <th>No Anggota</th>
                        <th>Tipe</th>
                        <th>Jumlah</th>
                        <th>Tanggal</th>
                        <th>Keterangan</th>
                        <th>Created At</th>
                    </tr>
                    <?php foreach ($allRecords as $r): ?>
                        <tr style="<?= (!$r['tanggal'] || $r['tanggal'] < '2020-01-01') ? 'background: #ffe6e6;' : '' ?>">
                            <td><?= $r['id'] ?></td>
                            <td><?= htmlspecialchars($r['nama']) ?></td>
                            <td><?= htmlspecialchars($r['no_anggota']) ?></td>
                            <td><?= $r['tipe'] ?></td>
                            <td>Rp <?= number_format($r['jumlah'], 0, ',', '.') ?></td>
                            <td>
                                <?php if (!$r['tanggal']): ?>
                                    <span class="error">NULL</span>
                                <?php elseif ($r['tanggal'] < '2020-01-01' || $r['tanggal'] > '2030-12-31'): ?>
                                    <span class="error"><?= $r['tanggal'] ?> (INVALID!)</span>
                                <?php else: ?>
                                    <?= $r['tanggal'] ?>
                                <?php endif; ?>
                            </td>
                            <td><?= htmlspecialchars($r['keterangan'] ?? '-') ?></td>
                            <td><?= $r['created_at'] ?></td>
                        </tr>
                    <?php endforeach; ?>
                </table>
            <?php endif; ?>
        </div>
        
        <!-- Action Buttons -->
        <div class="card">
            <h2>🔧 Actions</h2>
            <a href="?action=fix_dates" class="btn btn-success" onclick="return confirm('Perbaiki semua tanggal yang tidak valid ke hari ini (<?= date('Y-m-d') ?>)?')">
                🔧 Fix Invalid Dates to Today
            </a>
            <a href="simpanan/simpanan_dana_pensiun.php" class="btn">
                ← Back to Dana Pensiun
            </a>
            <a href="simpanan/simpanan_dana_pensiun.php?bulan=" class="btn">
                📅 Show All Months (No Filter)
            </a>
        </div>
        
        <div class="info">
            <strong>💡 Tips:</strong>
            <ul>
                <li>Jika data tidak muncul di tabel, kemungkinan filter bulan sedang aktif (April 2026) tapi data memiliki tanggal berbeda</li>
                <li>Klik "Show All Months" untuk melihat semua data tanpa filter bulan</li>
                <li>Klik "Fix Invalid Dates" jika tanggal data tidak valid (NULL atau tahun < 2020)</li>
                <li>Saat import, pastikan kolom Tanggal diisi dengan format yang benar (YYYY-MM-DD atau format tanggal Excel)</li>
            </ul>
        </div>
    </div>
</body>
</html>
