<?php
/**
 * Laporan Transaksi Export Excel
 * Koperasi Management System
 */

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

$dari = $_GET['dari'] ?? date('Y-m-01');
$sampai = $_GET['sampai'] ?? date('Y-m-t');

$bulan = (int)date('m', strtotime($dari));
$tahun = (int)date('Y', strtotime($dari));
$namaBulan = [1=>'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
$labelPeriode = $namaBulan[$bulan] . ' ' . $tahun;

// ── Calculate Saldo Awal ────────────────────────────────────────────────────
$stmtOpen = $db->prepare("
    SELECT SUM(val) FROM (
        SELECT (CASE WHEN tipe='setoran' THEN jumlah ELSE -jumlah END) as val FROM simpanan WHERE tanggal < ?
        UNION ALL
        SELECT -jumlah_pinjaman as val FROM pinjaman WHERE status IN ('disetujui', 'lunas') AND tanggal_pinjaman < ?
        UNION ALL
        SELECT jumlah_bayar as val FROM angsuran WHERE tanggal_bayar < ?
        UNION ALL
        SELECT (CASE WHEN jenis='pendapatan' THEN jumlah ELSE -jumlah END) as val FROM laba_rugi WHERE tanggal < ?
    ) as history
");
$stmtOpen->execute([$dari, $dari, $dari, $dari]);
$saldoAwal = (float)$stmtOpen->fetchColumn();

// ── Get Transactions (Unified) ──────────────────────────────────────────────
$unions = [
    "SELECT 'Simpanan' as entitas, jenis as orig_kategori, 
        (CASE 
            WHEN jenis='wajib' THEN 'Tabungan wajib' 
            WHEN jenis='dana_pensiun' THEN 'Dana Pensiun'
            WHEN jenis='pokok' THEN 'Tabungan pokok'
            WHEN jenis='sukarela' THEN 'Tabungan sukarela'
            ELSE CONCAT('Simpanan ', UPPER(SUBSTRING(jenis,1,1)), LOWER(SUBSTRING(jenis,2)))
        END) as kategori, 
        tipe, jumlah, tanggal FROM simpanan s WHERE s.tanggal BETWEEN ? AND ?",
    
    // Pinjaman - Detailed
    "SELECT 'Pinjaman' as entitas, 'pinjaman' as orig_kategori, CONCAT('Dipinjam ', a.nama) as kategori, 
        'penarikan' as tipe, jumlah_pinjaman as jumlah, tanggal_pinjaman as tanggal 
        FROM pinjaman p JOIN anggota a ON p.anggota_id = a.id 
        WHERE p.status IN ('disetujui', 'lunas') AND p.tanggal_pinjaman BETWEEN ? AND ?",
    
    // Angsuran - grouped globally
    "SELECT 'Angsuran' as entitas, 'angsuran' as orig_kategori, 
        CONCAT('Angsuran Bulan ', ?) as kategori, 
        'setoran' as tipe, jumlah_bayar as jumlah, tanggal_bayar as tanggal 
        FROM angsuran ag WHERE ag.tanggal_bayar BETWEEN ? AND ?",
    
    // Laba/Rugi
    "SELECT 'Laba/Rugi' as entitas, 'laba_rugi' as orig_kategori, kategori, 
        CASE WHEN jenis='pendapatan' THEN 'setoran' ELSE 'penarikan' END as tipe, 
        jumlah, tanggal FROM laba_rugi WHERE tanggal BETWEEN ? AND ?"
];

$allTrans = [];
$stmt = $db->prepare($unions[0]); $stmt->execute([$dari, $sampai]); $allTrans = array_merge($allTrans, $stmt->fetchAll());
$stmt = $db->prepare($unions[1]); $stmt->execute([$dari, $sampai]); $allTrans = array_merge($allTrans, $stmt->fetchAll());
$stmt = $db->prepare($unions[2]); $stmt->execute([$labelPeriode, $dari, $sampai]); $allTrans = array_merge($allTrans, $stmt->fetchAll());
$stmt = $db->prepare($unions[3]); $stmt->execute([$dari, $sampai]); $allTrans = array_merge($allTrans, $stmt->fetchAll());

// Grouping
$displayGroups = [];
foreach ($allTrans as $t) {
    $kategori = $t['kategori'];
    if ($t['entitas'] === 'Laba/Rugi' && (stripos($kategori, 'jasa') !== false)) {
        $kategori = 'Angsuran Bulan ' . $labelPeriode;
    }

    if ($t['entitas'] === 'Pinjaman') {
        $groupKey = 'Pinjaman|' . $kategori . '|' . $t['tanggal'] . '|' . rand(1000,9999);
    } else {
        $groupKey = $t['entitas'] . '|' . $kategori;
    }

    if (!isset($displayGroups[$groupKey])) {
        $displayGroups[$groupKey] = [
            'kategori' => $kategori,
            'tanggal' => $t['tanggal'],
            'masuk' => 0,
            'keluar' => 0
        ];
    }
    if ($t['tipe'] === 'setoran') $displayGroups[$groupKey]['masuk'] += $t['jumlah'];
    else $displayGroups[$groupKey]['keluar'] += $t['jumlah'];
}

$masukRows = []; $keluarRows = [];
foreach ($displayGroups as $g) {
    if ($g['masuk'] > 0) $masukRows[] = $g;
    else $keluarRows[] = $g;
}
usort($masukRows, function($a, $b) use ($labelPeriode) {
    $target = 'Angsuran Bulan ' . $labelPeriode;
    if ($a['kategori'] === $target) return -1;
    if ($b['kategori'] === $target) return 1;
    return strcmp($a['kategori'], $b['kategori']);
});
$finalRows = array_merge($masukRows, $keluarRows);

$totalMasuk = array_sum(array_column($finalRows, 'masuk'));
$totalKeluar = array_sum(array_column($finalRows, 'keluar'));

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Laporan_Keuangan_{$namaBulan[$bulan]}_{$tahun}.xls");
?>
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body>
    <div style="text-align: center;">
        <h3>LAPORAN KEUANGAN BULANAN</h3>
        <h3>KOPERASI TSUROYA</h3>
        <h3>MADRASAH ALIYAH DARUL MUTTAQIEN</h3>
        <h3>TAHUN <?= $tahun ?></h3>
    </div>

    <table border="1" cellpadding="5" cellspacing="0">
        <thead>
            <tr>
                <th>No</th>
                <th>Tanggal</th>
                <th>Keterangan/ Transaksi</th>
                <th>Masuk</th>
                <th>Keluar</th>
                <th>Saldo</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            $currentSaldo = $saldoAwal;
            $no = 1;
            ?>
            <tr>
                <td align="center"><?= $no++ ?></td>
                <td><?= date('d F Y', strtotime($dari)) ?></td>
                <td>Saldo Bulan <?= $namaBulan[$bulan] ?> <?= $tahun ?></td>
                <td align="right"></td>
                <td align="right"></td>
                <td align="right"><?= $currentSaldo ?></td>
            </tr>
            <?php foreach ($finalRows as $row): 
                $currentSaldo += $row['masuk'];
                $currentSaldo -= $row['keluar'];
            ?>
            <tr>
                <td></td>
                <td></td>
                <td><?= htmlspecialchars($row['kategori']) ?></td>
                <td align="right"><?= $row['masuk'] > 0 ? $row['masuk'] : '' ?></td>
                <td align="right"><?= $row['keluar'] > 0 ? $row['keluar'] : '' ?></td>
                <td align="right"><?= $currentSaldo ?></td>
            </tr>
            <?php endforeach; ?>
            <tr style="font-weight: bold; background-color: #f2f2f2;">
                <td colspan="3" align="center">Saldo Bulan <?= $namaBulan[$bulan] ?> <?= $tahun ?></td>
                <td align="right"><?= $totalMasuk ?></td>
                <td align="right"><?= $totalKeluar ?></td>
                <td align="right"><?= $currentSaldo ?></td>
            </tr>
        </tbody>
    </table>
</body>
</html>
