<?php
/**
 * Export Simpanan Dana Pensiun Rekap - Preview & Download
 * Supports: PDF, Word, Excel
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/auth.php';
requireAdmin();

$db = getDB();

// Get member ID
$anggotaId = isset($_GET['id']) ? intval($_GET['id']) : 0;
if (!$anggotaId) {
    die('Anggota tidak ditemukan');
}

// Get member info
$stmt = $db->prepare("SELECT * FROM anggota WHERE id = ?");
$stmt->execute([$anggotaId]);
$anggota = $stmt->fetch();

if (!$anggota) {
    die('Anggota tidak ditemukan');
}

// Get filters
$tahun = isset($_GET['tahun']) ? intval($_GET['tahun']) : date('Y');
$bulan = isset($_GET['bulan']) ? intval($_GET['bulan']) : 0;

// Build query
$where = "WHERE s.anggota_id = ? AND s.jenis = 'dana_pensiun'";
$params = [$anggotaId];

if ($bulan > 0) {
    $where .= " AND MONTH(s.tanggal) = ?";
    $params[] = $bulan;
}

$where .= " AND YEAR(s.tanggal) = ?";
$params[] = $tahun;

// Get transactions
$stmt = $db->prepare("
    SELECT s.*, u.nama_lengkap as petugas
    FROM simpanan s
    LEFT JOIN users u ON s.created_by = u.id
    $where
    ORDER BY s.tanggal DESC
");
$stmt->execute($params);
$transactions = $stmt->fetchAll();

// Calculate totals
$totalSetoran = 0;
$totalPenarikan = 0;
$saldo = 0;

foreach ($transactions as $t) {
    if ($t['tipe'] === 'setoran') {
        $totalSetoran += $t['jumlah'];
        $saldo += $t['jumlah'];
    } else {
        $totalPenarikan += $t['jumlah'];
        $saldo -= $t['jumlah'];
    }
}

// Handle export action
$action = isset($_GET['action']) ? $_GET['action'] : 'preview';
$format = isset($_GET['format']) ? $_GET['format'] : 'pdf';

if ($action === 'download') {
    if ($format === 'pdf') {
        exportPDF();
    } elseif ($format === 'word') {
        exportWord();
    } elseif ($format === 'excel') {
        exportExcel();
    }
    exit;
}

function exportPDF() {
    global $anggota, $transactions, $totalSetoran, $totalPenarikan, $saldo, $tahun, $bulan;
    
    $bulanNames = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 
                   'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
    $bulanText = $bulan > 0 ? $bulanNames[$bulan] . ' ' . $tahun : 'Tahun ' . $tahun;
    
    header('Content-Type: text/html; charset=utf-8');
    ?>
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>Rekap Dana Pensiun - <?= htmlspecialchars($anggota['nama']) ?></title>
        <style>
            @page { size: A4; margin: 20mm; }
            body { font-family: Arial, sans-serif; margin: 0; padding: 20px; color: #333; }
            .header { text-align: center; margin-bottom: 30px; border-bottom: 3px solid #f59e0b; padding-bottom: 20px; }
            .header h1 { margin: 0; color: #d97706; font-size: 24px; }
            .header h2 { margin: 10px 0 5px; color: #374151; font-size: 18px; }
            .header p { margin: 5px 0; color: #6b7280; }
            .info-box { background: #fef3c7; padding: 15px; border-radius: 8px; margin-bottom: 20px; }
            .info-box table { width: 100%; }
            .info-box td { padding: 5px 10px; }
            .info-box td:first-child { font-weight: bold; width: 150px; }
            table.data-table { width: 100%; border-collapse: collapse; margin-top: 20px; }
            table.data-table th { background: #f59e0b; color: white; padding: 12px; text-align: left; font-weight: 600; }
            table.data-table td { padding: 10px 12px; border-bottom: 1px solid #e5e7eb; }
            table.data-table tr:nth-child(even) { background: #fffbeb; }
            .setoran { color: #059669; font-weight: 600; }
            .penarikan { color: #dc2626; font-weight: 600; }
            .summary { margin-top: 30px; background: #fef3c7; padding: 20px; border-radius: 8px; }
            .summary table { width: 100%; }
            .summary td { padding: 10px; }
            .summary td:first-child { font-weight: 600; }
            .summary .total { font-size: 18px; font-weight: bold; color: #d97706; }
            .signature { margin-top: 50px; display: flex; justify-content: space-between; }
            .signature-box { text-align: center; width: 200px; }
            .signature-box .line { margin-top: 80px; border-top: 2px solid #333; padding-top: 5px; }
            @media print {
                body { padding: 0; }
                .no-print { display: none !important; }
            }
        </style>
    </head>
    <body>
        <div class="no-print" style="background: #fef3c7; padding: 15px; text-align: center; margin-bottom: 20px; border-radius: 8px;">
            <button onclick="window.print()" style="padding: 10px 20px; background: #f59e0b; color: white; border: none; border-radius: 5px; cursor: pointer; margin: 0 5px;">
                🖨️ Print / Save as PDF
            </button>
            <a href="?id=<?= $anggotaId ?>&action=download&format=pdf" style="padding: 10px 20px; background: #059669; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">
                💾 Download PDF
            </a>
            <a href="?id=<?= $anggotaId ?>&format=word" style="padding: 10px 20px; background: #2563eb; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">
                📝 Preview Word
            </a>
            <a href="?id=<?= $anggotaId ?>&format=excel" style="padding: 10px 20px; background: #10b981; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">
                📊 Preview Excel
            </a>
            <a href="simpanan_dana_pensiun_rekap.php?id=<?= $anggotaId ?>" style="padding: 10px 20px; background: #6b7280; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">
                ← Kembali
            </a>
        </div>
        
        <div class="header">
            <h1>KOPERASI TSUROYA</h1>
            <h2>REKAP DANA PENSIUN</h2>
            <p>Periode: <?= $bulanText ?></p>
        </div>
        
        <div class="info-box">
            <table>
                <tr>
                    <td>No. Anggota</td>
                    <td>: <?= htmlspecialchars($anggota['no_anggota']) ?></td>
                </tr>
                <tr>
                    <td>Nama Anggota</td>
                    <td>: <?= htmlspecialchars($anggota['nama']) ?></td>
                </tr>
            </table>
        </div>
        
        <table class="data-table">
            <thead>
                <tr>
                    <th style="width: 50px;">No</th>
                    <th style="width: 120px;">Tanggal</th>
                    <th>Tipe</th>
                    <th style="text-align: right;">Jumlah</th>
                    <th>Keterangan</th>
                    <th>Petugas</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $no = 1;
                foreach ($transactions as $t): 
                ?>
                <tr>
                    <td style="text-align: center;"><?= $no++ ?></td>
                    <td><?= date('d/m/Y', strtotime($t['tanggal'])) ?></td>
                    <td>
                        <span class="<?= $t['tipe'] ?>">
                            <?= $t['tipe'] === 'setoran' ? '↓ Setoran' : '↑ Penarikan' ?>
                        </span>
                    </td>
                    <td style="text-align: right;" class="<?= $t['tipe'] ?>">
                        <?= $t['tipe'] === 'setoran' ? '+' : '-' ?> Rp <?= number_format($t['jumlah'], 0, ',', '.') ?>
                    </td>
                    <td><?= htmlspecialchars($t['keterangan'] ?? '-') ?></td>
                    <td><?= htmlspecialchars($t['petugas'] ?? '-') ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        
        <div class="summary">
            <table>
                <tr>
                    <td>Total Setoran</td>
                    <td class="setoran">: Rp <?= number_format($totalSetoran, 0, ',', '.') ?></td>
                </tr>
                <tr>
                    <td>Total Penarikan</td>
                    <td class="penarikan">: Rp <?= number_format($totalPenarikan, 0, ',', '.') ?></td>
                </tr>
                <tr style="border-top: 2px solid #f59e0b;">
                    <td class="total">Saldo Dana Pensiun</td>
                    <td class="total">: Rp <?= number_format($saldo, 0, ',', '.') ?></td>
                </tr>
            </table>
        </div>
        
        <div class="signature">
            <div class="signature-box">
                <p>Penerima,</p>
                <div class="line">(_________________)</div>
            </div>
            <div class="signature-box">
                <p>Mengetahui,</p>
                <p>Bendahara</p>
                <div class="line">(_________________)</div>
            </div>
        </div>
    </body>
    </html>
    <?php
    exit;
}

function exportWord() {
    global $anggota, $transactions, $totalSetoran, $totalPenarikan, $saldo, $tahun, $bulan;
    
    $bulanNames = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 
                   'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
    $bulanText = $bulan > 0 ? $bulanNames[$bulan] . ' ' . $tahun : 'Tahun ' . $tahun;
    
    header('Content-Type: application/msword');
    header('Content-Disposition: attachment; filename="Rekap_DanaPensiun_' . $anggota['no_anggota'] . '_' . date('Ymd') . '.doc"');
    ?>
    <html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:w='urn:schemas-microsoft-com:office:word' xmlns='http://www.w3.org/TR/REC-html40'>
    <head>
        <meta charset="utf-8">
        <title>Rekap Dana Pensiun</title>
        <style>
            body { font-family: Arial, sans-serif; }
            .header { text-align: center; margin-bottom: 20px; }
            .header h1 { margin: 0; font-size: 18pt; }
            .header h2 { margin: 10px 0 5px; font-size: 14pt; }
            table { width: 100%; border-collapse: collapse; margin-top: 15px; }
            th { background-color: #f59e0b; color: white; padding: 8px; text-align: left; }
            td { padding: 8px; border: 1px solid #ddd; }
            .setoran { color: #059669; font-weight: bold; }
            .penarikan { color: #dc2626; font-weight: bold; }
        </style>
    </head>
    <body>
        <div class="header">
            <h1>KOPERASI TSUROYA</h1>
            <h2>REKAP DANA PENSIUN</h2>
            <p>Periode: <?= $bulanText ?></p>
        </div>
        
        <table style="width: 50%; margin-bottom: 20px;">
            <tr><td style="width: 120px;"><b>No. Anggota</b></td><td>: <?= htmlspecialchars($anggota['no_anggota']) ?></td></tr>
            <tr><td><b>Nama Anggota</b></td><td>: <?= htmlspecialchars($anggota['nama']) ?></td></tr>
        </table>
        
        <table>
            <tr>
                <th>No</th>
                <th>Tanggal</th>
                <th>Tipe</th>
                <th style="text-align: right;">Jumlah</th>
                <th>Keterangan</th>
                <th>Petugas</th>
            </tr>
            <?php 
            $no = 1;
            foreach ($transactions as $t): 
            ?>
            <tr>
                <td style="text-align: center;"><?= $no++ ?></td>
                <td><?= date('d/m/Y', strtotime($t['tanggal'])) ?></td>
                <td class="<?= $t['tipe'] ?>"><?= $t['tipe'] === 'setoran' ? 'Setoran' : 'Penarikan' ?></td>
                <td style="text-align: right;" class="<?= $t['tipe'] ?>"><?= number_format($t['jumlah'], 0, ',', '.') ?></td>
                <td><?= htmlspecialchars($t['keterangan'] ?? '-') ?></td>
                <td><?= htmlspecialchars($t['petugas'] ?? '-') ?></td>
            </tr>
            <?php endforeach; ?>
            <tr style="font-weight: bold; background-color: #fef3c7;">
                <td colspan="3" style="text-align: right;">Total Setoran:</td>
                <td style="text-align: right;"><?= number_format($totalSetoran, 0, ',', '.') ?></td>
                <td colspan="2"></td>
            </tr>
            <tr style="font-weight: bold; background-color: #fef3c7;">
                <td colspan="3" style="text-align: right;">Total Penarikan:</td>
                <td style="text-align: right;"><?= number_format($totalPenarikan, 0, ',', '.') ?></td>
                <td colspan="2"></td>
            </tr>
            <tr style="font-weight: bold; font-size: 14pt;">
                <td colspan="3" style="text-align: right;">Saldo Dana Pensiun:</td>
                <td style="text-align: right;"><?= number_format($saldo, 0, ',', '.') ?></td>
                <td colspan="2"></td>
            </tr>
        </table>
    </body>
    </html>
    <?php
    exit;
}

function exportExcel() {
    global $anggota, $transactions, $totalSetoran, $totalPenarikan, $saldo, $tahun, $bulan;
    
    $filename = "Rekap_DanaPensiun_" . $anggota['no_anggota'] . "_" . date('Ymd');
    
    $tmpfile = tempnam(sys_get_temp_dir(), 'xlsx_');
    
    $zip = new ZipArchive();
    if ($zip->open($tmpfile, ZipArchive::CREATE | ZipArchive::OVERWRITE) === false) {
        die('Gagal membuat file Excel');
    }
    
    $sheetData = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<cols>
<col min="1" max="1" width="5" customWidth="1"/>
<col min="2" max="2" width="15" customWidth="1"/>
<col min="3" max="3" width="15" customWidth="1"/>
<col min="4" max="4" width="20" customWidth="1"/>
<col min="5" max="5" width="30" customWidth="1"/>
<col min="6" max="6" width="20" customWidth="1"/>
</cols><sheetData>';

    $sheetData .= '<row r="1"><c r="A1" t="str"><v>KOPERASI TSUROYYA</v></c></row>';
    $sheetData .= '<row r="2"><c r="A2" t="str"><v>REKAP DANA PENSIUN</v></c></row>';
    
    $bulanNames = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 
                   'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
    $bulanText = $bulan > 0 ? $bulanNames[$bulan] . ' ' . $tahun : 'Tahun ' . $tahun;
    $sheetData .= '<row r="3"><c r="A3" t="str"><v>Periode: ' . $bulanText . '</v></c></row>';
    $sheetData .= '<row r="4"/>';
    
    $sheetData .= '<row r="5"><c r="A5" t="str"><v>No. Anggota</v></c><c r="B5" t="str"><v>' . htmlspecialchars($anggota['no_anggota']) . '</v></c></row>';
    $sheetData .= '<row r="6"><c r="A6" t="str"><v>Nama Anggota</v></c><c r="B6" t="str"><v>' . htmlspecialchars($anggota['nama']) . '</v></c></row>';
    $sheetData .= '<row r="7"/>';
    
    $headers = ['No', 'Tanggal', 'Tipe', 'Jumlah', 'Keterangan', 'Petugas'];
    $sheetData .= '<row r="8">';
    foreach ($headers as $i => $header) {
        $col = chr(65 + $i);
        $sheetData .= '<c r="' . $col . '8" t="str"><v>' . $header . '</v></c>';
    }
    $sheetData .= '</row>';
    
    $rowNum = 9;
    $no = 1;
    foreach ($transactions as $t) {
        $sheetData .= '<row r="' . $rowNum . '">';
        $sheetData .= '<c r="A' . $rowNum . '" t="str"><v>' . $no . '</v></c>';
        $sheetData .= '<c r="B' . $rowNum . '" t="str"><v>' . date('d/m/Y', strtotime($t['tanggal'])) . '</v></c>';
        $sheetData .= '<c r="C' . $rowNum . '" t="str"><v>' . ucfirst($t['tipe']) . '</v></c>';
        $sheetData .= '<c r="D' . $rowNum . '"><v>' . $t['jumlah'] . '</v></c>';
        $sheetData .= '<c r="E' . $rowNum . '" t="str"><v>' . htmlspecialchars($t['keterangan'] ?? '-') . '</v></c>';
        $sheetData .= '<c r="F' . $rowNum . '" t="str"><v>' . htmlspecialchars($t['petugas'] ?? '-') . '</v></c>';
        $sheetData .= '</row>';
        $rowNum++;
        $no++;
    }
    
    $rowNum += 2;
    $sheetData .= '<row r="' . $rowNum . '"><c r="C' . $rowNum . '" t="str"><v>Total Setoran</v></c><c r="D' . $rowNum . '"><v>' . $totalSetoran . '</v></c></row>';
    $rowNum++;
    $sheetData .= '<row r="' . $rowNum . '"><c r="C' . $rowNum . '" t="str"><v>Total Penarikan</v></c><c r="D' . $rowNum . '"><v>' . $totalPenarikan . '</v></c></row>';
    $rowNum++;
    $sheetData .= '<row r="' . $rowNum . '"><c r="C' . $rowNum . '" t="str"><v>Saldo Dana Pensiun</v></c><c r="D' . $rowNum . '"><v>' . $saldo . '</v></c></row>';
    
    $sheetData .= '</sheetData></worksheet>';
    $zip->addFromString('xl/worksheets/sheet1.xml', $sheetData);
    
    $contentTypes = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>';
    $zip->addFromString('[Content_Types].xml', $contentTypes);
    
    $rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>';
    $zip->addFromString('_rels/.rels', $rels);
    
    $workbookRels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
</Relationships>';
    $zip->addFromString('xl/_rels/workbook.xml.rels', $workbookRels);
    
    $workbook = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets><sheet name="Rekap" sheetId="1" r:id="rId1"/></sheets>
</workbook>';
    $zip->addFromString('xl/workbook.xml', $workbook);
    
    $styles = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="1"><font><sz val="11"/><name val="Calibri"/></font></fonts>
<fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>
<borders count="1"><border><left/><right/><top/><bottom/></border></borders>
<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
<cellXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/></cellXfs>
</styleSheet>';
    $zip->addFromString('xl/styles.xml', $styles);
    
    $zip->close();
    
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '.xlsx"');
    header('Content-Length: ' . filesize($tmpfile));
    header('Cache-Control: no-cache');
    
    readfile($tmpfile);
    unlink($tmpfile);
    exit;
}

include __DIR__ . '/../includes/header.php';
$bulanNames = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 
               'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
$bulanText = $bulan > 0 ? $bulanNames[$bulan] . ' ' . $tahun : 'Tahun ' . $tahun;
?>

<style>
    .export-actions { display: flex; gap: 10px; margin: 20px 0; flex-wrap: wrap; }
    .export-btn { padding: 12px 24px; border: none; border-radius: 8px; cursor: pointer; font-weight: 600; text-decoration: none; display: inline-flex; align-items: center; gap: 8px; transition: all 0.3s; }
    .export-btn:hover { transform: translateY(-2px); box-shadow: 0 4px 12px rgba(0,0,0,0.15); }
    .btn-pdf { background: #dc2626; color: white; }
    .btn-word { background: #2563eb; color: white; }
    .btn-excel { background: #10b981; color: white; }
    .btn-back { background: #6b7280; color: white; }
    .preview-frame { width: 100%; height: 600px; border: 2px solid #e5e7eb; border-radius: 8px; margin-top: 20px; }
    .info-card { background: white; padding: 20px; border-radius: 8px; margin-bottom: 20px; box-shadow: 0 2px 8px rgba(0,0,0,0.1); }
</style>

<div class="info-card">
    <h2 style="margin-top: 0;">📄 Export Preview - Dana Pensiun</h2>
    <p><strong>Anggota:</strong> <?= htmlspecialchars($anggota['nama']) ?> (<?= htmlspecialchars($anggota['no_anggota']) ?>)</p>
    <p><strong>Periode:</strong> <?= $bulanText ?></p>
    <p><strong>Jumlah Transaksi:</strong> <?= count($transactions) ?></p>
</div>

<div class="export-actions">
    <a href="?id=<?= $anggotaId ?>&format=pdf&action=preview" class="export-btn btn-pdf">
        📕 Preview PDF
    </a>
    <a href="?id=<?= $anggotaId ?>&format=word&action=preview" class="export-btn btn-word">
        📘 Preview Word
    </a>
    <a href="?id=<?= $anggotaId ?>&format=excel&action=preview" class="export-btn btn-excel">
        📗 Preview Excel
    </a>
    <a href="simpanan_dana_pensiun_rekap.php?id=<?= $anggotaId ?>" class="export-btn btn-back">
        ← Kembali
    </a>
</div>

<?php
if ($format === 'pdf' && $action === 'preview'):
?>
    <iframe src="?id=<?= $anggotaId ?>&format=pdf&action=download" class="preview-frame"></iframe>
<?php elseif ($format === 'word' && $action === 'preview'): ?>
    <div class="info-card">
        <h3>📘 Preview Word</h3>
        <p>Klik tombol di bawah untuk melihat atau download file Word:</p>
        <a href="?id=<?= $anggotaId ?>&format=word&action=download" class="export-btn btn-word" target="_blank">
            📝 Open / Download Word
        </a>
    </div>
<?php elseif ($format === 'excel' && $action === 'preview'): ?>
    <div class="info-card">
        <h3>📗 Preview Excel</h3>
        <p>Klik tombol di bawah untuk melihat atau download file Excel:</p>
        <a href="?id=<?= $anggotaId ?>&format=excel&action=download" class="export-btn btn-excel" target="_blank">
            📊 Open / Download Excel
        </a>
    </div>
<?php endif; ?>
