<?php
require_once __DIR__ . '/config/app.php';
require_once __DIR__ . '/config/database.php';

requireLogin();

$db = getDB();

$filter = (string) ($_GET['filter'] ?? 'semua');
$sort = (string) ($_GET['sort'] ?? 'input');
$export = (string) ($_GET['export'] ?? '');
$limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 10;
$validLimits = [10, 20, 50, 100, 300, 500];
if (!in_array($limit, $validLimits)) {
    $limit = 10;
}
$page = isset($_GET['p']) ? (int)$_GET['p'] : 1;
$offset = ($page - 1) * $limit;

$validFilter = ['semua', 'layak', 'tidak_layak', 'belum'];
$validSort = ['input', 'nama_asc', 'nama_desc'];
if (!in_array($filter, $validFilter, true)) {
    $filter = 'semua';
}
if (!in_array($sort, $validSort, true)) {
    $sort = 'input';
}

$sql = 'SELECT id, nama, pekerjaan_ortu, penghasilan_ortu, jumlah_saudara, kepemilikan_kps, kepemilikan_kip, prob_layak, status_kelayakan
        FROM siswa';
$where = [];
$params = [];

if ($filter === 'layak') {
    $where[] = 'status_kelayakan = ?';
    $params[] = 'Layak';
} elseif ($filter === 'tidak_layak') {
    $where[] = 'status_kelayakan = ?';
    $params[] = 'Tidak Layak';
} elseif ($filter === 'belum') {
    $where[] = 'status_kelayakan IS NULL';
}

if (!empty($where)) {
    $sql .= ' WHERE ' . implode(' AND ', $where);
}

if ($sort === 'nama_asc') {
    $sql .= ' ORDER BY nama ASC, id ASC';
} elseif ($sort === 'nama_desc') {
    $sql .= ' ORDER BY nama DESC, id DESC';
} else {
    $sql .= ' ORDER BY id ASC';
}

// Get total count for pagination
$countSql = str_replace('SELECT id, nama, pekerjaan_ortu, penghasilan_ortu, jumlah_saudara, kepemilikan_kps, kepemilikan_kip, prob_layak, status_kelayakan', 'SELECT COUNT(*)', $sql);
$countStmt = $db->prepare($countSql);
$countStmt->execute($params);
$totalRows = $countStmt->fetchColumn();
$totalPages = ceil($totalRows / $limit);

// Add pagination to main query
$sql .= " LIMIT $limit OFFSET $offset";

$stmt = $db->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();

$filterLabelMap = [
    'semua' => 'Semua Hasil',
    'layak' => 'Layak',
    'tidak_layak' => 'Tidak Layak',
    'belum' => 'Belum Dihitung',
];
$sortLabelMap = [
    'input' => 'Urutan Input',
    'nama_asc' => 'Nama A-Z',
    'nama_desc' => 'Nama Z-A',
];

$filterLabel = $filterLabelMap[$filter];
$sortLabel = $sortLabelMap[$sort];

if ($export === 'excel') {
    // Get all data without pagination for export
    $exportSql = 'SELECT id, nama, pekerjaan_ortu, penghasilan_ortu, jumlah_saudara, kepemilikan_kps, kepemilikan_kip, prob_layak, status_kelayakan
        FROM siswa';
    $exportWhere = [];
    $exportParams = [];
    
    if ($filter === 'layak') {
        $exportWhere[] = 'status_kelayakan = ?';
        $exportParams[] = 'Layak';
    } elseif ($filter === 'tidak_layak') {
        $exportWhere[] = 'status_kelayakan = ?';
        $exportParams[] = 'Tidak Layak';
    } elseif ($filter === 'belum') {
        $exportWhere[] = 'status_kelayakan IS NULL';
    }
    
    if (!empty($exportWhere)) {
        $exportSql .= ' WHERE ' . implode(' AND ', $exportWhere);
    }
    
    if ($sort === 'nama_asc') {
        $exportSql .= ' ORDER BY nama ASC, id ASC';
    } elseif ($sort === 'nama_desc') {
        $exportSql .= ' ORDER BY nama DESC, id DESC';
    } else {
        $exportSql .= ' ORDER BY id ASC';
    }
    
    $exportStmt = $db->prepare($exportSql);
    $exportStmt->execute($exportParams);
    $exportRows = $exportStmt->fetchAll();
    
    require_once __DIR__ . '/vendor/autoload.php';
    
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    // Set headers
    $headers = ['No', 'Nama', 'Pekerjaan Orang Tua', 'Penghasilan Orang Tua', 'Jumlah Saudara Kandung', 'Penerima KPS', 'Penerima KIP', 'Hasil'];
    $sheet->fromArray($headers, null, 'A1');
    
    // Style header row
    $headerStyle = [
        'font' => ['bold' => true],
        'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => ['rgb' => 'F1F5F9']],
        'borders' => [
            'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb' => 'CCCCCC']]
        ],
        'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
    ];
    $sheet->getStyle('A1:H1')->applyFromArray($headerStyle);
    
    // Add data
    $row = 2;
    foreach ($exportRows as $i => $r) {
        $hasil = $r['status_kelayakan'] ?: 'Belum Dihitung';
        
        $sheet->setCellValue('A' . $row, $i + 1);
        $sheet->setCellValue('B' . $row, $r['nama']);
        $sheet->setCellValue('C' . $row, $r['pekerjaan_ortu']);
        $sheet->setCellValue('D' . $row, $r['penghasilan_ortu']);
        $sheet->setCellValue('E' . $row, (int) $r['jumlah_saudara']);
        $sheet->setCellValue('F' . $row, $r['kepemilikan_kps']);
        $sheet->setCellValue('G' . $row, $r['kepemilikan_kip']);
        $sheet->setCellValue('H' . $row, $hasil);
        
        // Style data row
        $dataStyle = [
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb' => 'CCCCCC']]
            ]
        ];
        $sheet->getStyle('A' . $row . ':H' . $row)->applyFromArray($dataStyle);
        
        $row++;
    }
    
    // Auto-size columns
    foreach (range('A', 'H') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    // Set title
    $sheet->setCellValue('A1', 'Laporan Data Siswa');
    $sheet->mergeCells('A1:H1');
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    
    // Set filter info
    $sheet->setCellValue('A2', "Filter: $filterLabel | Sort: $sortLabel");
    $sheet->mergeCells('A2:H2');
    
    // Move headers to row 3
    $sheet->fromArray($headers, null, 'A3');
    $sheet->getStyle('A3:H3')->applyFromArray($headerStyle);
    
    // Move data to start from row 4
    $row = 4;
    foreach ($exportRows as $i => $r) {
        $hasil = $r['status_kelayakan'] ?: 'Belum Dihitung';
        
        $sheet->setCellValue('A' . $row, $i + 1);
        $sheet->setCellValue('B' . $row, $r['nama']);
        $sheet->setCellValue('C' . $row, $r['pekerjaan_ortu']);
        $sheet->setCellValue('D' . $row, $r['penghasilan_ortu']);
        $sheet->setCellValue('E' . $row, (int) $r['jumlah_saudara']);
        $sheet->setCellValue('F' . $row, $r['kepemilikan_kps']);
        $sheet->setCellValue('G' . $row, $r['kepemilikan_kip']);
        $sheet->setCellValue('H' . $row, $hasil);
        
        $dataStyle = [
            'borders' => [
                'allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['rgb' => 'CCCCCC']]
            ]
        ];
        $sheet->getStyle('A' . $row . ':H' . $row)->applyFromArray($dataStyle);
        
        $row++;
    }
    
    // Auto-size columns again
    foreach (range('A', 'H') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    // Output
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="laporan-siswa.xlsx"');
    header('Cache-Control: max-age=0');
    
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

if ($export === 'pdf') {
    header('Content-Type: text/html; charset=UTF-8');
    ?>
    <!DOCTYPE html>
    <html lang="id">
    <head>
      <meta charset="UTF-8">
      <title>Laporan Siswa</title>
      <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        h1 { margin: 0 0 8px 0; }
        p { margin: 0 0 12px 0; color: #555; }
        table { width: 100%; border-collapse: collapse; font-size: 12px; }
        th, td { border: 1px solid #ccc; padding: 6px; text-align: left; }
        th { background: #f1f5f9; }
      </style>
    </head>
    <body>
      <h1>Laporan Data Siswa</h1>
      <p>Filter: <?= e($filterLabel) ?> | Sort: <?= e($sortLabel) ?></p>
      <table>
        <thead>
          <tr>
            <th>No</th>
            <th>Nama</th>
            <th>Pekerjaan Orang Tua</th>
            <th>Penghasilan Orang Tua</th>
            <th>Jumlah Saudara Kandung</th>
            <th>Penerima KPS</th>
            <th>Penerima KIP</th>
            <th>Hasil</th>
          </tr>
        </thead>
        <tbody>
          <?php if (empty($rows)): ?>
          <tr><td colspan="8">Tidak ada data.</td></tr>
          <?php endif; ?>
          <?php foreach ($rows as $i => $r): ?>
          <tr>
            <td><?= $i + 1 ?></td>
            <td><?= e($r['nama']) ?></td>
            <td><?= e($r['pekerjaan_ortu']) ?></td>
            <td><?= e($r['penghasilan_ortu']) ?></td>
            <td><?= (int) $r['jumlah_saudara'] ?></td>
            <td><?= e($r['kepemilikan_kps']) ?></td>
            <td><?= e($r['kepemilikan_kip']) ?></td>
            <td><?= e($r['status_kelayakan'] ?: 'Belum Dihitung') ?></td>
          </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
      <script>window.print();</script>
    </body>
    </html>
    <?php
    exit;
}

$pageTitle = 'Laporan';
require_once __DIR__ . '/includes/header.php';
?>

<div class="page-header">
  <h1>Laporan</h1>
</div>
<div class="page-body">
  <form method="get" class="toolbar laporan-filters">
    <div class="form-group" style="min-width:220px">
      <label>Filter Hasil</label>
      <select class="form-control" name="filter" onchange="this.form.submit()">
        <option value="semua" <?= $filter === 'semua' ? 'selected' : '' ?>>Semua Hasil</option>
        <option value="layak" <?= $filter === 'layak' ? 'selected' : '' ?>>Layak</option>
        <option value="tidak_layak" <?= $filter === 'tidak_layak' ? 'selected' : '' ?>>Tidak Layak</option>
        <option value="belum" <?= $filter === 'belum' ? 'selected' : '' ?>>Belum Dihitung</option>
      </select>
    </div>
    <div class="form-group" style="min-width:220px">
      <label>Sort Nama</label>
      <select class="form-control" name="sort" onchange="this.form.submit()">
        <option value="input" <?= $sort === 'input' ? 'selected' : '' ?>>Urutan Input</option>
        <option value="nama_asc" <?= $sort === 'nama_asc' ? 'selected' : '' ?>>A-Z</option>
        <option value="nama_desc" <?= $sort === 'nama_desc' ? 'selected' : '' ?>>Z-A</option>
      </select>
    </div>
  </form>

  <div class="laporan-meta-row">
    <p class="subtitle laporan-meta">Menampilkan: <?= e($filterLabel) ?> · <?= e($sortLabel) ?></p>
    <div style="display:flex;align-items:center;gap:0.5rem">
      <select class="form-control" style="width:auto;padding:0.5rem" onchange="location.href='laporan.php?filter=<?= e($filter) ?>&sort=<?= e($sort) ?>&limit='+this.value+'&p=1'">
        <option value="10" <?= $limit === 10 ? 'selected' : '' ?>>10</option>
        <option value="20" <?= $limit === 20 ? 'selected' : '' ?>>20</option>
        <option value="50" <?= $limit === 50 ? 'selected' : '' ?>>50</option>
        <option value="100" <?= $limit === 100 ? 'selected' : '' ?>>100</option>
        <option value="300" <?= $limit === 300 ? 'selected' : '' ?>>300</option>
        <option value="500" <?= $limit === 500 ? 'selected' : '' ?>>500</option>
      </select>
      <div class="laporan-export-btns">
        <a class="btn btn-outline" href="laporan.php?filter=<?= e($filter) ?>&sort=<?= e($sort) ?>&limit=<?= $limit ?>&export=pdf">
          <i class="fas fa-file-pdf"></i> Export PDF
        </a>
        <a class="btn btn-outline" href="laporan.php?filter=<?= e($filter) ?>&sort=<?= e($sort) ?>&limit=<?= $limit ?>&export=excel">
          <i class="fas fa-file-excel"></i> Export Excel
        </a>
      </div>
    </div>
  </div>

  <div class="card laporan-card">
    <div class="card-header laporan-card-header">
      <h2>Daftar Siswa</h2>
      <span class="subtitle laporan-count"><?= count($rows) ?> siswa</span>
    </div>
    <div class="card-body" style="padding-top:0">
      <div class="table-responsive">
        <table class="data-table" id="tableLaporan">
          <thead>
            <tr>
              <th>No</th>
              <th>Nama</th>
              <th>Pekerjaan Orang Tua</th>
              <th>Penghasilan Orang Tua</th>
              <th>Jumlah Saudara Kandung</th>
              <th>Penerima KPS</th>
              <th>Penerima KIP</th>
              <th>Hasil</th>
            </tr>
          </thead>
          <tbody>
            <?php if (empty($rows)): ?>
            <tr><td colspan="8" style="text-align:center;color:var(--text-muted)">Tidak ada data untuk filter ini.</td></tr>
            <?php endif; ?>
            <?php foreach ($rows as $i => $r): ?>
            <tr>
              <td><?= $offset + $i + 1 ?></td>
              <td><?= e($r['nama']) ?></td>
              <td><?= e($r['pekerjaan_ortu']) ?></td>
              <td><?= e($r['penghasilan_ortu']) ?></td>
              <td><?= (int) $r['jumlah_saudara'] ?></td>
              <td><?= e($r['kepemilikan_kps']) ?></td>
              <td><?= e($r['kepemilikan_kip']) ?></td>
              <td>
                <?php if (!empty($r['status_kelayakan'])): ?>
                <span class="badge <?= $r['status_kelayakan'] === 'Layak' ? 'badge-layak' : 'badge-tidak' ?>">
                  <?= e($r['status_kelayakan']) ?>
                </span>
                <?php else: ?>
                <span class="badge badge-belum">Belum Dihitung</span>
                <?php endif; ?>
              </td>
            </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>

      <?php if ($totalPages > 1): ?>
      <div class="pagination">
      <?php if ($page > 1): ?>
      <a href="laporan.php?filter=<?= e($filter) ?>&sort=<?= e($sort) ?>&limit=<?= $limit ?>&p=<?= $page - 1 ?>" class="btn btn-outline">&laquo; Prev</a>
      <?php endif; ?>
      
      <?php for ($i = 1; $i <= $totalPages; $i++): ?>
        <?php if ($i === $page): ?>
        <span class="btn btn-primary" style="pointer-events:none"><?= $i ?></span>
        <?php else: ?>
        <a href="laporan.php?filter=<?= e($filter) ?>&sort=<?= e($sort) ?>&limit=<?= $limit ?>&p=<?= $i ?>" class="btn btn-outline"><?= $i ?></a>
        <?php endif; ?>
      <?php endfor; ?>
      
      <?php if ($page < $totalPages): ?>
      <a href="laporan.php?filter=<?= e($filter) ?>&sort=<?= e($sort) ?>&limit=<?= $limit ?>&p=<?= $page + 1 ?>" class="btn btn-outline">Next &raquo;</a>
      <?php endif; ?>
      </div>
      <?php endif; ?>
    </div>
  </div>
</div>

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