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

$pageTitle = 'Laporan Transaksi';
$pageSubtitle = 'Rincian seluruh transaksi kas masuk dan keluar';

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

$namaBulan = [1=>'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];

// Ensure laba_rugi table exists
$db->exec("
    CREATE TABLE IF NOT EXISTS laba_rugi (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tanggal DATE NOT NULL,
        jenis ENUM('pendapatan', 'pengeluaran') NOT NULL,
        kategori VARCHAR(100) NOT NULL,
        jumlah DECIMAL(15,2) NOT NULL,
        keterangan VARCHAR(255) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB
");

// Filter dates
$bulan = (isset($_GET['bulan']) && is_numeric($_GET['bulan'])) ? (int)$_GET['bulan'] : (int)date('m');
$tahun = (isset($_GET['tahun']) && is_numeric($_GET['tahun'])) ? (int)$_GET['tahun'] : (int)date('Y');

$dari = $tahun . '-' . str_pad($bulan, 2, '0', STR_PAD_LEFT) . '-01';
$sampai = date('Y-m-t', strtotime($dari));
$labelPeriode = $namaBulan[$bulan] . ' ' . $tahun;

// ── Calculate Saldo Awal (Total before $dari) ───────────────────────────────
$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 = [
    // Simpanan - group by type (jenis)
    "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 by member name as requested
    "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 ', ?) as kategori, 
        'setoran' as tipe, jumlah_bayar as jumlah, tanggal_bayar as tanggal 
        FROM angsuran ag WHERE ag.tanggal_bayar BETWEEN ? AND ?",
    
    // Laba/Rugi - group by category
    "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 ?"
];

// Execute unions manually
$allTrans = [];

// 1. Simpanan
$stmt = $db->prepare($unions[0]);
$stmt->execute([$dari, $sampai]);
$allTrans = array_merge($allTrans, $stmt->fetchAll());

// 2. Pinjaman (Disbursements) - RINCI
$stmt = $db->prepare($unions[1]);
$stmt->execute([$dari, $sampai]);
$allTrans = array_merge($allTrans, $stmt->fetchAll());

// 3. Angsuran (Repayments) - GLOBAL
$stmt = $db->prepare($unions[2]);
$stmt->execute(['Bulan ' . $labelPeriode, $dari, $sampai]);
$allTrans = array_merge($allTrans, $stmt->fetchAll());

// 4. Laba/Rugi
$stmt = $db->prepare($unions[3]);
$stmt->execute([$dari, $sampai]);
$allTrans = array_merge($allTrans, $stmt->fetchAll());

// ── Group and Sum ────────────────────────────────────────────────────────────
$displayGroups = [];
foreach ($allTrans as $t) {
    $kategori = $t['kategori'];
    
    // Merge manual "Jasa" entries into "Angsuran" group
    if ($t['entitas'] === 'Laba/Rugi' && (stripos($kategori, 'jasa') !== false)) {
        $kategori = 'Angsuran Bulan ' . $labelPeriode;
    }

    // Force unique key for Pinjaman to keep it "rinci"
    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,
            'tipe' => $t['tipe']
        ];
    }
    if ($t['tipe'] === 'setoran') $displayGroups[$groupKey]['masuk'] += $t['jumlah'];
    else $displayGroups[$groupKey]['keluar'] += $t['jumlah'];
}

// ── Prepare Final Rows ───────────────────────────────────────────────────────
$masukRows = []; $keluarRows = [];
foreach ($displayGroups as $g) {
    if ($g['masuk'] > 0) $masukRows[] = $g;
    else $keluarRows[] = $g;
}

// Custom sort: Angsuran always first in Masuk
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'));
$saldoAkhir = $saldoAwal + $totalMasuk - $totalKeluar;

include __DIR__ . '/../includes/header.php';
?>

<style>
    .ledger-container {
        padding: 40px;
        background: #fff;
        box-shadow: 0 0 20px rgba(0,0,0,0.05);
        border-radius: 8px;
        overflow-x: auto;
    }
    .ledger-table {
        width: 100%;
        border-collapse: collapse;
        background: #fff;
        margin-top: 10px;
    }
    .ledger-table th, .ledger-table td {
        border: 1px solid #000;
        padding: 8px 12px;
        font-size: 0.85rem;
        color: #000;
    }
    .ledger-table th {
        background: #fff;
        text-align: center;
        font-weight: 700;
        color: #000;
    }
    .text-right { text-align: right; }
    .text-center { text-align: center; }
    .ledger-header {
        text-align: center;
        margin-bottom: 20px;
    }
    .ledger-header h1 {
        margin: 2px 0;
        font-size: 1.15rem;
        text-transform: uppercase;
        font-weight: 700;
        text-decoration: underline;
    }
    .ledger-header h2 {
        margin: 2px 0;
        font-size: 1.1rem;
        text-transform: uppercase;
        font-weight: 700;
    }
    
    .btn-print-view {
        display: inline-flex;
        align-items: center;
        gap: 8px;
        padding: 8px 16px;
        background: #fff;
        border: 1px solid #ddd;
        border-radius: 4px;
        cursor: pointer;
        font-weight: 600;
        color: #333;
        transition: all 0.2s;
    }
    .btn-print-view:hover { background: #f5f5f5; }
    
    @media print {
        @page { size: portrait; margin: 1cm; }
        .app-layout .sidebar, .app-layout .topbar, .card:first-child, .btn-print-view, .breadcrumb, .page-title, .header-time {
            display: none !important;
        }
        .main-content { margin: 0; padding: 0; }
        .content { padding: 0; margin: 0; }
        .ledger-container { box-shadow: none; padding: 0; }
        .ledger-table { width: 100% !important; }
    }
</style>

<!-- Filter Bar -->
<div class="card mb-3">
    <div class="card-body">
        <form method="GET" class="toolbar" style="align-items: flex-end;">
            <div class="form-group" style="margin-bottom: 0;">
                <label style="font-size: 0.78rem; margin-bottom: 4px;">Bulan</label>
                <select name="bulan" class="form-control" id="filter_bulan">
                    <?php for ($i=1; $i<=12; $i++): ?>
                        <option value="<?= $i ?>" <?= $bulan == $i ? 'selected' : '' ?>><?= $namaBulan[$i] ?></option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="form-group" style="margin-bottom: 0;">
                <label style="font-size: 0.78rem; margin-bottom: 4px;">Tahun</label>
                <input type="number" name="tahun" class="form-control" value="<?= $tahun ?>" id="filter_tahun">
            </div>
            <button type="submit" class="btn btn-primary">
                <i class="fas fa-filter"></i> Filter
            </button>
            <a href="../exports/laporan_export.php?dari=<?= $dari ?>&sampai=<?= $sampai ?>&format=excel" class="btn btn-success" target="_blank">
                <i class="fas fa-file-excel"></i> Export Excel
            </a>
            <button type="button" class="btn-print-view" onclick="window.print()">
                <i class="fas fa-print"></i> Cetak Laporan
            </button>
        </form>
    </div>
</div>

<div class="ledger-container">
    <div class="ledger-header">
        <h1>LAPORAN KEUANGAN BULANAN</h1>
        <h2>KOPERASI TSUROYA</h2>
        <h2>MADRASAH ALIYAH DARUL MUTTAQIEN</h2>
        <h2>TAHUN <?= $tahun ?></h2>
    </div>

    <div style="display: flex; justify-content: flex-end; margin-bottom: 5px; font-weight: 700; font-size: 0.9rem;">
        Bulan <?= $namaBulan[$bulan] ?> <?= $tahun ?>
    </div>

    <table class="ledger-table">
        <thead>
            <tr>
                <th width="35">No</th>
                <th width="120">Tanggal</th>
                <th>Keterangan/ Transaksi</th>
                <th width="160" colspan="2">Masuk</th>
                <th width="160" colspan="2">Keluar</th>
                <th width="160" colspan="2">Saldo</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            $currentSaldo = $saldoAwal;
            $rowNo = 1;
            ?>
            <tr>
                <td class="text-center"><?= $rowNo++ ?></td>
                <td class="text-center"><?= date('d', strtotime($dari)) ?> <?= $namaBulan[$bulan] ?> <?= $tahun ?></td>
                <td>Saldo Bulan <?= $namaBulan[$bulan] ?> <?= $tahun ?></td>
                <td width="30" style="border-right:none">Rp</td>
                <td class="text-right" style="border-left:none"></td>
                <td width="30" style="border-right:none">Rp</td>
                <td class="text-right" style="border-left:none"></td>
                <td width="30" style="border-right:none">Rp</td>
                <td class="text-right" style="border-left:none"><?= number_format($currentSaldo, 0, ',', '.') ?></td>
            </tr>

            <?php foreach ($finalRows as $row): 
                $currentSaldo += $row['masuk'];
                $currentSaldo -= $row['keluar'];
            ?>
                <tr>
                    <td class="text-center"></td>
                    <td class="text-center"></td>
                    <td><?= htmlspecialchars($row['kategori']) ?></td>
                    <td width="30" style="border-right:none">Rp</td>
                    <td class="text-right" style="border-left:none"><?= $row['masuk'] > 0 ? number_format($row['masuk'], 0, ',', '.') : '' ?></td>
                    <td width="30" style="border-right:none">Rp</td>
                    <td class="text-right" style="border-left:none"><?= $row['keluar'] > 0 ? number_format($row['keluar'], 0, ',', '.') : '' ?></td>
                    <td width="30" style="border-right:none">Rp</td>
                    <td class="text-right" style="border-left:none"><?= number_format($currentSaldo, 0, ',', '.') ?></td>
                </tr>
            <?php endforeach; ?>

            <tr style="font-weight: 700;">
                <td colspan="2"></td>
                <td class="text-center">Saldo Bulan <?= $namaBulan[$bulan] ?> <?= $tahun ?></td>
                <td width="30" style="border-right:none">Rp</td>
                <td class="text-right" style="border-left:none"><?= number_format($totalMasuk, 0, ',', '.') ?></td>
                <td width="30" style="border-right:none">Rp</td>
                <td class="text-right" style="border-left:none"><?= number_format($totalKeluar, 0, ',', '.') ?></td>
                <td width="30" style="border-right:none">Rp</td>
                <td class="text-right" style="border-left:none"><?= number_format($currentSaldo, 0, ',', '.') ?></td>
            </tr>
        </tbody>
    </table>
</div>

<?php include __DIR__ . '/../includes/footer.php'; ?>
