<?php
/**
 * Export Rekap Umum Simpanan Sukarela - Preview & Download
 * Shows all members' sukarela savings summary
 */

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

$db = getDB();

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

// Get all active members with their sukarela savings summary
$stmt = $db->prepare("
    SELECT 
        a.id,
        a.no_anggota,
        a.nama,
        COALESCE(SUM(CASE WHEN s.tipe='setoran' AND YEAR(s.tanggal)=? THEN s.jumlah ELSE 0 END), 0) as total_setoran,
        COALESCE(SUM(CASE WHEN s.tipe='penarikan' AND YEAR(s.tanggal)=? THEN s.jumlah ELSE 0 END), 0) as total_penarikan,
        COALESCE(SUM(CASE WHEN s.tipe='setoran' AND YEAR(s.tanggal)=? THEN s.jumlah ELSE -s.jumlah END), 0) as saldo
    FROM anggota a
    LEFT JOIN simpanan s ON s.anggota_id = a.id AND s.jenis = 'sukarela'
    WHERE a.status = 'aktif'
    GROUP BY a.id, a.no_anggota, a.nama
    HAVING total_setoran > 0 OR total_penarikan > 0
    ORDER BY a.nama
");
$stmt->execute([$tahun, $tahun, $tahun]);
$members = $stmt->fetchAll();

// Grand totals
$grandSetoran = 0;
$grandPenarikan = 0;
$grandSaldo = 0;
foreach ($members as $m) {
    $grandSetoran += $m['total_setoran'];
    $grandPenarikan += $m['total_penarikan'];
    $grandSaldo += $m['saldo'];
}

// 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 $members, $grandSetoran, $grandPenarikan, $grandSaldo, $tahun;
    
    header('Content-Type: text/html; charset=utf-8');
    ?>
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>Rekap Umum Simpanan Sukarela <?= $tahun ?></title>
        <style>
            @page { size: A4 landscape; margin: 15mm; }
            body { font-family: Arial, sans-serif; margin: 0; padding: 15px; color: #333; font-size: 10px; }
            .header { text-align: center; margin-bottom: 20px; border-bottom: 3px solid #10b981; padding-bottom: 15px; }
            .header h1 { margin: 0; color: #059669; font-size: 20px; }
            .header h2 { margin: 8px 0 5px; color: #374151; font-size: 16px; }
            table.data-table { width: 100%; border-collapse: collapse; margin-top: 15px; }
            table.data-table th { background: #10b981; color: white; padding: 8px; text-align: left; font-size: 10px; }
            table.data-table td { padding: 6px 8px; border-bottom: 1px solid #e5e7eb; font-size: 10px; }
            table.data-table tr:nth-child(even) { background: #f0fdf4; }
            .summary { margin-top: 20px; background: #f0fdf4; padding: 15px; border-radius: 8px; }
            .summary table { width: 100%; }
            .summary td { padding: 8px; font-size: 11px; }
            .summary td:first-child { font-weight: 600; }
            .summary .total { font-size: 14px; font-weight: bold; color: #059669; }
            .signature { margin-top: 40px; display: flex; justify-content: space-between; }
            .signature-box { text-align: center; width: 200px; }
            .signature-box .line { margin-top: 70px; 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: #f0fdf4; padding: 12px; text-align: center; margin-bottom: 15px; border-radius: 8px;">
            <button onclick="window.print()" style="padding: 8px 16px; background: #10b981; color: white; border: none; border-radius: 5px; cursor: pointer; margin: 0 5px;">🖨️ Print</button>
            <a href="?action=download&format=pdf" style="padding: 8px 16px; background: #059669; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">💾 PDF</a>
            <a href="?format=word" style="padding: 8px 16px; background: #2563eb; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">📝 Word</a>
            <a href="?format=excel" style="padding: 8px 16px; background: #10b981; color: white; text-decoration: none; border-radius: 5px; margin: 0 5px;">📊 Excel</a>
            <a href="simpanan_sukarela_rekap_umum.php" style="padding: 8px 16px; 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 UMUM SIMPANAN SUKARELA</h2>
            <p>Tahun <?= $tahun ?></p>
        </div>
        
        <table class="data-table">
            <thead>
                <tr>
                    <th style="width: 40px;">No</th>
                    <th style="width: 100px;">No. Anggota</th>
                    <th>Nama Anggota</th>
                    <th style="text-align: right; width: 120px;">Total Setoran</th>
                    <th style="text-align: right; width: 120px;">Total Penarikan</th>
                    <th style="text-align: right; width: 120px;">Saldo</th>
                </tr>
            </thead>
            <tbody>
                <?php $no = 1; foreach ($members as $m): ?>
                <tr>
                    <td style="text-align: center;"><?= $no++ ?></td>
                    <td><?= htmlspecialchars($m['no_anggota']) ?></td>
                    <td><?= htmlspecialchars($m['nama']) ?></td>
                    <td style="text-align: right; color: #059669; font-weight: 600;">Rp <?= number_format($m['total_setoran'], 0, ',', '.') ?></td>
                    <td style="text-align: right; color: #dc2626; font-weight: 600;">Rp <?= number_format($m['total_penarikan'], 0, ',', '.') ?></td>
                    <td style="text-align: right; font-weight: 700;">Rp <?= number_format($m['saldo'], 0, ',', '.') ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        
        <div class="summary">
            <table>
                <tr>
                    <td class="total">Grand Total Setoran</td>
                    <td class="total">: Rp <?= number_format($grandSetoran, 0, ',', '.') ?></td>
                    <td class="total">Grand Total Penarikan</td>
                    <td class="total">: Rp <?= number_format($grandPenarikan, 0, ',', '.') ?></td>
                    <td class="total">Grand Total Saldo</td>
                    <td class="total">: Rp <?= number_format($grandSaldo, 0, ',', '.') ?></td>
                </tr>
            </table>
        </div>
        
        <div class="signature">
            <div class="signature-box">
                <p>Mengetahui,</p>
                <p>Ketua Koperasi</p>
                <div class="line">(_________________)</div>
            </div>
            <div class="signature-box">
                <p>Bendahara</p>
                <div class="line">(_________________)</div>
            </div>
        </div>
    </body>
    </html>
    <?php
    exit;
}

function exportWord() {
    global $members, $grandSetoran, $grandPenarikan, $grandSaldo, $tahun;
    
    header('Content-Type: application/msword');
    header('Content-Disposition: attachment; filename="Rekap_Umum_Sukarela_' . $tahun . '.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 Umum Simpanan Sukarela</title>
        <style>
            body { font-family: Arial, sans-serif; font-size: 10pt; }
            .header { text-align: center; margin-bottom: 15px; }
            .header h1 { margin: 0; font-size: 16pt; }
            .header h2 { margin: 8px 0 5px; font-size: 14pt; }
            table { width: 100%; border-collapse: collapse; }
            th { background-color: #10b981; color: white; padding: 6px; text-align: left; font-size: 10pt; }
            td { padding: 6px; border: 1px solid #ddd; font-size: 10pt; }
        </style>
    </head>
    <body>
        <div class="header">
            <h1>KOPERASI TSUROYA</h1>
            <h2>REKAP UMUM SIMPANAN SUKARELA - Tahun <?= $tahun ?></h2>
        </div>
        
        <table>
            <tr>
                <th>No</th>
                <th>No. Anggota</th>
                <th>Nama Anggota</th>
                <th style="text-align: right;">Total Setoran</th>
                <th style="text-align: right;">Total Penarikan</th>
                <th style="text-align: right;">Saldo</th>
            </tr>
            <?php $no = 1; foreach ($members as $m): ?>
            <tr>
                <td style="text-align: center;"><?= $no++ ?></td>
                <td><?= htmlspecialchars($m['no_anggota']) ?></td>
                <td><?= htmlspecialchars($m['nama']) ?></td>
                <td style="text-align: right;"><?= number_format($m['total_setoran'], 0, ',', '.') ?></td>
                <td style="text-align: right;"><?= number_format($m['total_penarikan'], 0, ',', '.') ?></td>
                <td style="text-align: right; font-weight: bold;"><?= number_format($m['saldo'], 0, ',', '.') ?></td>
            </tr>
            <?php endforeach; ?>
            <tr style="font-weight: bold; background-color: #f0fdf4;">
                <td colspan="3" style="text-align: right;">GRAND TOTAL:</td>
                <td style="text-align: right;"><?= number_format($grandSetoran, 0, ',', '.') ?></td>
                <td style="text-align: right;"><?= number_format($grandPenarikan, 0, ',', '.') ?></td>
                <td style="text-align: right;"><?= number_format($grandSaldo, 0, ',', '.') ?></td>
            </tr>
        </table>
    </body>
    </html>
    <?php
    exit;
}

function exportExcel() {
    global $members, $grandSetoran, $grandPenarikan, $grandSaldo, $tahun;
    
    $filename = "Rekap_Umum_Sukarela_" . $tahun;
    $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="25" customWidth="1"/>
<col min="4" max="4" width="20" customWidth="1"/>
<col min="5" max="5" width="20" 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 UMUM SIMPANAN SUKARELA - Tahun ' . $tahun . '</v></c></row>';
    $sheetData .= '<row r="3"/>';
    
    $headers = ['No', 'No. Anggota', 'Nama Anggota', 'Total Setoran', 'Total Penarikan', 'Saldo'];
    $sheetData .= '<row r="4">';
    foreach ($headers as $i => $header) {
        $col = chr(65 + $i);
        $sheetData .= '<c r="' . $col . '4" t="str"><v>' . $header . '</v></c>';
    }
    $sheetData .= '</row>';
    
    $rowNum = 5;
    $no = 1;
    foreach ($members as $m) {
        $sheetData .= '<row r="' . $rowNum . '">';
        $sheetData .= '<c r="A' . $rowNum . '"><v>' . $no . '</v></c>';
        $sheetData .= '<c r="B' . $rowNum . '" t="str"><v>' . htmlspecialchars($m['no_anggota']) . '</v></c>';
        $sheetData .= '<c r="C' . $rowNum . '" t="str"><v>' . htmlspecialchars($m['nama']) . '</v></c>';
        $sheetData .= '<c r="D' . $rowNum . '"><v>' . $m['total_setoran'] . '</v></c>';
        $sheetData .= '<c r="E' . $rowNum . '"><v>' . $m['total_penarikan'] . '</v></c>';
        $sheetData .= '<c r="F' . $rowNum . '"><v>' . $m['saldo'] . '</v></c>';
        $sheetData .= '</row>';
        $rowNum++;
        $no++;
    }
    
    $sheetData .= '<row r="' . $rowNum . '"><c r="C' . $rowNum . '" t="str"><v>GRAND TOTAL</v></c><c r="D' . $rowNum . '"><v>' . $grandSetoran . '</v></c><c r="E' . $rowNum . '"><v>' . $grandPenarikan . '</v></c><c r="F' . $rowNum . '"><v>' . $grandSaldo . '</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';
?>

<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 - Rekap Umum Simpanan Sukarela</h2>
    <p><strong>Tahun:</strong> <?= $tahun ?></p>
    <p><strong>Jumlah Anggota:</strong> <?= count($members) ?></p>
</div>

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

<?php
if ($format === 'pdf' && $action === 'preview'):
?>
    <iframe src="?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="?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="?format=excel&action=download" class="export-btn btn-excel" target="_blank">📊 Open / Download Excel</a>
    </div>
<?php endif; ?>
