<?php
/**
 * Export Student Savings to Excel
 * Koperasi Management System
 */

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

$type = $_GET['type'] ?? 'harian';
$date = $_GET['date'] ?? date('Y-m-d');
$month = $_GET['month'] ?? date('n');
$year = $_GET['year'] ?? date('Y');

// Filename
if ($type === 'harian') {
    $filename = "Laporan_Harian_Tabungan_Siswa_" . $date . ".xls";
} elseif ($type === 'tahunan') {
    $filename = "Rekap_Tahunan_Tabungan_Siswa_" . $year . ".xls";
} else {
    $filename = "Laporan_Bulanan_Tabungan_Siswa_" . $month . "_" . $year . ".xls";
}

// Headers for Excel download
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Pragma: no-cache");
header("Expires: 0");

// Helper to format month name
if (!function_exists('formatBulan')) {
    function formatBulan($m) {
        $bulan = [
            1 => 'Januari', 2 => 'Februari', 3 => 'Maret', 4 => 'April',
            5 => 'Mei', 6 => 'Juni', 7 => 'Juli', 8 => 'Agustus',
            9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'Desember'
        ];
        return $bulan[(int)$m] ?? '';
    }
}

// Fetch Data
if ($type === 'harian') {
    $stmt = $db->prepare("
        SELECT ts.*, s.nama as nama_siswa, s.kelas 
        FROM tabungan_siswa ts 
        JOIN siswa s ON ts.siswa_id = s.id 
        WHERE ts.tanggal = ? 
        ORDER BY ts.created_at ASC
    ");
    $stmt->execute([$date]);
    $title = "LAPORAN HARIAN TABUNGAN SISWA - " . date('d/m/Y', strtotime($date));
} elseif ($type === 'tahunan') {
    $stmt = $db->prepare("
        SELECT ts.*, s.nama as nama_siswa, s.kelas 
        FROM tabungan_siswa ts 
        JOIN siswa s ON ts.siswa_id = s.id 
        WHERE YEAR(ts.tanggal) = ? 
        ORDER BY ts.tanggal ASC, ts.created_at ASC
    ");
    $stmt->execute([$year]);
    $title = "REKAP TAHUNAN TABUNGAN SISWA - " . $year;
} else {
    $stmt = $db->prepare("
        SELECT ts.*, s.nama as nama_siswa, s.kelas 
        FROM tabungan_siswa ts 
        JOIN siswa s ON ts.siswa_id = s.id 
        WHERE MONTH(ts.tanggal) = ? AND YEAR(ts.tanggal) = ? 
        ORDER BY ts.tanggal ASC, ts.created_at ASC
    ");
    $stmt->execute([$month, $year]);
    $title = "REKAP BULANAN TABUNGAN SISWA - " . strtoupper(formatBulan($month)) . " " . $year;
}

$data = $stmt->fetchAll();

// Calculate Starting Balance (Saldo Awal) before this period
if ($type === 'harian') {
    $stmtSaldo = $db->prepare("SELECT SUM(CASE WHEN tipe='setoran' THEN jumlah ELSE -jumlah END) FROM tabungan_siswa WHERE tanggal < ?");
    $stmtSaldo->execute([$date]);
} elseif ($type === 'tahunan') {
    $stmtSaldo = $db->prepare("SELECT SUM(CASE WHEN tipe='setoran' THEN jumlah ELSE -jumlah END) FROM tabungan_siswa WHERE YEAR(tanggal) < ?");
    $stmtSaldo->execute([$year]);
} else {
    $stmtSaldo = $db->prepare("SELECT SUM(CASE WHEN tipe='setoran' THEN jumlah ELSE -jumlah END) FROM tabungan_siswa WHERE tanggal < ?");
    $stmtSaldo->execute(["$year-$month-01"]);
}
$saldoAwal = (float)($stmtSaldo->fetchColumn() ?: 0);

// Calculate Totals and Running Balance
$totalSetoran = 0;
$totalPenarikan = 0;
$runningBalance = $saldoAwal;
foreach ($data as &$row) {
    if ($row['tipe'] === 'setoran') {
        $totalSetoran += $row['jumlah'];
        $runningBalance += $row['jumlah'];
    } else {
        $totalPenarikan += $row['jumlah'];
        $runningBalance -= $row['jumlah'];
    }
    $row['saldo_akhir'] = $runningBalance;
}
unset($row);
?>

<table border="1">
    <tr>
        <th colspan="7" style="font-size: 1.4rem; font-weight: bold; border: none;">KOPERASI TSUROYYA</th>
    </tr>
    <tr>
        <th colspan="7" style="font-size: 1.2rem; font-weight: bold; border: none;">MA Darul Mutaqien</th>
    </tr>
    <tr>
        <th colspan="7" style="font-size: 1rem; border: none;">Bolong, Ngaditirto, Selpampang, Temanggung</th>
    </tr>
    <tr><th colspan="7" style="border: none;"></th></tr>
    <tr>
        <th colspan="7" style="font-size: 1.1rem; font-weight: bold; border: none; text-decoration: underline;"><?= $title ?></th>
    </tr>
    <tr><th colspan="7" style="border: none;"></th></tr>
    <thead>
        <tr style="background-color: #f2f2f2; font-weight: bold;">
            <th>NO</th>
            <th>TANGGAL</th>
            <th>NAMA SISWA</th>
            <th>KELAS</th>
            <th>TIPE</th>
            <th>JUMLAH (Rp)</th>
            <th>SALDO AKHIR (Rp)</th>
            <th>KETERANGAN</th>
        </tr>
    </thead>
    <tbody>
        <?php 
        $no = 1;
        foreach ($data as $row): ?>
        <tr>
            <td align="center"><?= $no++ ?></td>
            <td><?= date('d/m/Y', strtotime($row['tanggal'])) ?></td>
            <td><?= htmlspecialchars($row['nama_siswa']) ?></td>
            <td><?= htmlspecialchars($row['kelas']) ?></td>
            <td><?= strtoupper($row['tipe']) ?></td>
            <td align="right"><?= number_format($row['jumlah'], 0, ',', '.') ?></td>
            <td align="right"><?= number_format($row['saldo_akhir'], 0, ',', '.') ?></td>
            <td><?= htmlspecialchars($row['keterangan'] ?: '-') ?></td>
        </tr>
        <?php endforeach; ?>
    </tbody>
    <tfoot>
        <tr style="font-weight: bold; background-color: #f9f9f9;">
            <td colspan="5" align="right">TOTAL SETORAN :</td>
            <td align="right"><?= number_format($totalSetoran, 0, ',', '.') ?></td>
            <td></td>
            <td></td>
        </tr>
        <tr style="font-weight: bold; background-color: #f9f9f9;">
            <td colspan="5" align="right">TOTAL PENARIKAN :</td>
            <td align="right"><?= number_format($totalPenarikan, 0, ',', '.') ?></td>
            <td></td>
            <td></td>
        </tr>
        <tr style="font-weight: bold; background-color: #eee;">
            <td colspan="5" align="right">SALDO BERSIH :</td>
            <td align="right"><?= number_format($totalSetoran - $totalPenarikan, 0, ',', '.') ?></td>
            <td></td>
            <td></td>
        </tr>
    </tfoot>
</table>
