<?php
// admin/siswa.php
include_once 'init.php';

$page_title = 'Data Nasabah';
$page_icon = 'bi-people';

$action = $_POST['action'] ?? $_GET['action'] ?? '';

/**
 * FUNGSI REKALKULASI SALDO DINAMIS
 * Menghitung ulang saldo berdasarkan mutasi transaksi yang valid
 */


// === LOGIKA PROSES ===
try {
    if ($action === 'tambah_siswa') {
        $pdo->beginTransaction();
        
        // Cek apakah NIS sudah ada
        $cek = $pdo->prepare("SELECT COUNT(*) FROM siswa WHERE nis = ?");
        $cek->execute([$_POST['nis']]);
        if ($cek->fetchColumn() > 0) { 
            throw new Exception("NIS sudah terdaftar!"); 
        }

        $pass_hash = password_hash('123456', PASSWORD_DEFAULT);
        
        // UPDATE: Hanya insert ke tabel siswa (Putus hubungan dengan tabel users)
        $stmt = $pdo->prepare("INSERT INTO siswa (nis, nama_siswa, id_kelas, username, password, pin) VALUES (?, ?, ?, ?, ?, ?)");
        $stmt->execute([$_POST['nis'], $_POST['nama_siswa'], $_POST['id_kelas'], $_POST['nis'], $pass_hash, '112233']);

        $pdo->commit();
        $_SESSION['message'] = "Nasabah berhasil ditambahkan dengan password default 123456.";
        header("Location: siswa.php"); exit();
    }

    if ($action === 'update_siswa') {
        $id_siswa = $_POST['id_siswa'];
        $nis = $_POST['nis'];
        $nama = $_POST['nama_siswa'];
        $id_kelas = $_POST['id_kelas'];
        $username = $_POST['username'];

        // UPDATE: Logika pembaruan NIS, Nama, Kelas, Username, dan Password (jika diisi)
        if (!empty($_POST['password'])) {
            $pass_hash = password_hash($_POST['password'], PASSWORD_DEFAULT);
            $stmt = $pdo->prepare("UPDATE siswa SET nis = ?, nama_siswa = ?, id_kelas = ?, username = ?, password = ? WHERE id_siswa = ?");
            $stmt->execute([$nis, $nama, $id_kelas, $username, $pass_hash, $id_siswa]);
        } else {
            $stmt = $pdo->prepare("UPDATE siswa SET nis = ?, nama_siswa = ?, id_kelas = ?, username = ? WHERE id_siswa = ?");
            $stmt->execute([$nis, $nama, $id_kelas, $username, $id_siswa]);
        }
        
        // Jalankan rekalkulasi saldo agar tetap akurat
        sinkronkanSaldoSiswa($pdo, $id_siswa);
        
        $_SESSION['message'] = "Data nasabah berhasil diperbarui.";
        header("Location: siswa.php"); exit();
    }

    if ($action === 'hapus_siswa' && isset($_POST['id_hapus'])) {
        $pdo->beginTransaction();
        
        $stmt_get = $pdo->prepare("SELECT s.*, k.nama_kelas FROM siswa s JOIN kelas k ON s.id_kelas = k.id_kelas WHERE s.id_siswa = ?");
        $stmt_get->execute([$_POST['id_hapus']]);
        $nasabah = $stmt_get->fetch();

        if ($nasabah) {
            // Backup transaksi ke tabel bulk
            $stmt_trx = $pdo->prepare("SELECT * FROM transaksi WHERE id_siswa = ?");
            $stmt_trx->execute([$_POST['id_hapus']]);
            $all_trx = $stmt_trx->fetchAll();

            if (count($all_trx) > 0) {
                $stmt_bulk = $pdo->prepare("INSERT INTO bulk 
                    (nis, nama_siswa, nama_kelas, id_transaksi_lama, jenis_transaksi, jumlah, keterangan, tanggal_transaksi, tanggal_verifikasi, status_akhir, diinput_oleh) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                foreach ($all_trx as $t) {
                    $stmt_bulk->execute([
                        $nasabah['nis'], $nasabah['nama_siswa'], $nasabah['nama_kelas'],
                        $t['id_transaksi'], $t['jenis_transaksi'], $t['jumlah'],
                        $t['keterangan'], $t['tanggal_transaksi'], $t['tanggal_verifikasi'],
                        $t['status'], $t['input_by']
                    ]);
                }
            }

            // UPDATE: Hanya hapus di tabel siswa (Sesuai instruksi putus hubungan dengan tabel users)
            $pdo->prepare("DELETE FROM siswa WHERE id_siswa = ?")->execute([$_POST['id_hapus']]);

            $pdo->commit();
            $_SESSION['message'] = "Nasabah dihapus. Riwayat dipindahkan ke Cadangan (Bulk).";
        }
        header("Location: siswa.php"); exit();
    }
} catch (Exception $e) {
    if ($pdo->inTransaction()) $pdo->rollBack();
    $_SESSION['error'] = "Gagal: " . $e->getMessage();
    header("Location: siswa.php"); exit();
}

// === LOGIKA AJAX RIWAYAT BUKU BANK ===
if (isset($_GET['get_riwayat'])) {
    $id_siswa = $_GET['id_siswa'];
    
    // UNION antara transaksi umum dan transaksi_payment (dengan JOIN ke data_pembayaran)
    $sql = "SELECT tanggal_transaksi as tanggal, jenis_transaksi, jumlah, keterangan, status 
            FROM transaksi 
            WHERE id_siswa = ? AND status = 'valid'
            
            UNION ALL
            
            SELECT tp.tanggal_bayar as tanggal, 'pembayaran' as jenis_transaksi, tp.jumlah, 
                   CONCAT('Pembayaran ', dp.nama_pembayaran, ' ', COALESCE(tp.keterangan, '')) as keterangan, tp.status 
            FROM transaksi_payment tp
            JOIN data_pembayaran dp ON tp.id_pembayaran = dp.id_pembayaran
            WHERE tp.id_siswa = ? AND tp.status = 'valid'
            
            ORDER BY tanggal ASC";
            
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id_siswa, $id_siswa]);
    $transaksi = $stmt->fetchAll();
    
    $html = '';
    $saldo_berjalan = 0;
    foreach ($transaksi as $t) {
        $debet = ($t['jenis_transaksi'] == 'setoran' || $t['jenis_transaksi'] == 'transfer_masuk') ? $t['jumlah'] : 0;
        $kredit = ($t['jenis_transaksi'] == 'penarikan' || $t['jenis_transaksi'] == 'transfer_keluar' || $t['jenis_transaksi'] == 'pembayaran') ? $t['jumlah'] : 0;
        $saldo_berjalan += ($debet - $kredit);
        
        $html .= "<tr>
            <td>".date('d/m/y H:i', strtotime($t['tanggal']))."</td>
            <td>".htmlspecialchars($t['keterangan'] ?: ucfirst($t['jenis_transaksi']))."</td>
            <td class='text-success text-end'>".($debet > 0 ? number_format($debet, 0, ',', '.') : '-')."</td>
            <td class='text-danger text-end'>".($kredit > 0 ? number_format($kredit, 0, ',', '.') : '-')."</td>
            <td class='fw-bold text-end'>".number_format($saldo_berjalan, 0, ',', '.')."</td>
        </tr>";
    }
    echo $html ?: '<tr><td colspan="5" class="text-center">Belum ada mutasi valid.</td></tr>';
    exit;
}
// === LOGIKA SEARCH, SORT, & PAGINASI ===
$search = $_GET['search'] ?? '';
$sort = $_GET['sort'] ?? 'nama_asc';
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 10;
$offset = ($page - 1) * $limit;

switch ($sort) {
    case 'saldo_desc': $orderby = "s.saldo DESC"; break;
    case 'saldo_asc':  $orderby = "s.saldo ASC"; break;
    case 'nama_desc':  $orderby = "s.nama_siswa DESC"; break;
    default:           $orderby = "s.nama_siswa ASC"; break;
}

$where = "";
$params = [];
if ($search) {
    $where = " WHERE s.nama_siswa LIKE ? OR s.nis LIKE ?";
    $params = ["%$search%", "%$search%"];
}

$total_stmt = $pdo->prepare("SELECT COUNT(*) FROM siswa s $where");
$total_stmt->execute($params);
$total_rows = $total_stmt->fetchColumn();
$total_pages = ceil($total_rows / $limit);

$sql = "SELECT s.*, k.nama_kelas FROM siswa s JOIN kelas k ON s.id_kelas = k.id_kelas $where ORDER BY $orderby LIMIT $limit OFFSET $offset";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$siswa_list = $stmt->fetchAll();

$kelasList = $pdo->query("SELECT * FROM kelas ORDER BY nama_kelas ASC")->fetchAll();

include 'header.php';
?>

<style>
    @media print {
        body * { visibility: hidden; }
        #modalRiwayat .modal-content, #modalRiwayat .modal-content * { visibility: visible; }
        #modalRiwayat .modal-content { position: absolute; left: 0; top: 0; width: 100%; border: none; }
        .btn-close, .modal-footer, .btn-printer-none { display: none !important; }
        .table-responsive { overflow: visible !important; }
    }
</style>

<div class="content-card shadow-sm p-4 bg-white rounded">
    <div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-3 mb-4">
        <div>
            <h5 class="mb-0 fw-bold text-primary">Daftar Nasabah Tabungan</h5>
            <small class="text-muted">Total: <?= $total_rows ?> Nasabah</small>
        </div>
        
        <div class="d-flex flex-wrap gap-2">
            <form action="" method="GET" class="d-flex gap-1">
                <select name="sort" class="form-select form-select-sm" onchange="this.form.submit()">
                    <option value="nama_asc" <?= $sort == 'nama_asc' ? 'selected' : '' ?>>Nama (A-Z)</option>
                    <option value="nama_desc" <?= $sort == 'nama_desc' ? 'selected' : '' ?>>Nama (Z-A)</option>
                    <option value="saldo_desc" <?= $sort == 'saldo_desc' ? 'selected' : '' ?>>Saldo Terbesar</option>
                    <option value="saldo_asc" <?= $sort == 'saldo_asc' ? 'selected' : '' ?>>Saldo Terkecil</option>
                </select>
                <input type="text" name="search" class="form-control form-control-sm" placeholder="Cari..." value="<?= htmlspecialchars($search) ?>">
                <button class="btn btn-sm btn-outline-primary"><i class="bi bi-search"></i></button>
            </form>
            
            <button class="btn btn-sm btn-outline-success" type="button" data-bs-toggle="collapse" data-bs-target="#collapseUpload">
                <i class="bi bi-file-earmark-excel"></i> Impor
            </button>
            
            <button class="btn btn-primary btn-sm" data-bs-toggle="modal" data-bs-target="#modalSiswa" onclick="resetForm()">
                <i class="bi bi-plus-lg me-1"></i> Tambah
            </button>
        </div>
    </div>

    <div class="collapse mb-4" id="collapseUpload">
        <div class="card card-body bg-light border-success p-3">
            <form action="upload_template.php" method="POST" enctype="multipart/form-data" class="d-flex align-items-center gap-2">
                <input type="hidden" name="type" value="siswa">
                <div class="flex-grow-1">
                    <input type="file" class="form-control form-control-sm" name="excelFile" accept=".xlsx, .xls" required>
                </div>
                <button type="submit" class="btn btn-success btn-sm">Unggah</button>
                <a href="../template/template_siswa.xlsx" class="btn btn-outline-secondary btn-sm" download>Template</a>
            </form>
        </div>
    </div>

    <?php if (isset($_SESSION['message'])): ?>
        <div class="alert alert-success alert-dismissible fade show"><?= $_SESSION['message']; unset($_SESSION['message']); ?><button type="button" class="btn-close" data-bs-dismiss="alert"></button></div>
    <?php endif; ?>
    <?php if (isset($_SESSION['error'])): ?>
        <div class="alert alert-danger alert-dismissible fade show"><?= $_SESSION['error']; unset($_SESSION['error']); ?><button type="button" class="btn-close" data-bs-dismiss="alert"></button></div>
    <?php endif; ?>

    <div class="table-responsive">
        <table class="table table-hover align-middle border">
            <thead class="table-light small">
                <tr>
                    <th width="15%">No. Rek (NIS)</th>
                    <th>Nama Nasabah</th>
                    <th>Kelas</th>
                    <th class="text-end">Saldo</th>
                    <th class="text-center">Aksi</th>
                </tr>
            </thead>
            <tbody>
                <?php if (empty($siswa_list)): ?>
                    <tr><td colspan="5" class="text-center py-4">Data tidak ditemukan.</td></tr>
                <?php endif; ?>
                <?php foreach ($siswa_list as $s): ?>
                <tr>
                    <td><span class="badge bg-secondary font-monospace"><?= $s['nis'] ?></span></td>
                    <td><strong><?= htmlspecialchars($s['nama_siswa']) ?></strong></td>
                    <td><?= htmlspecialchars($s['nama_kelas']) ?></td>
                    <td class="text-end text-success fw-bold">Rp <?= number_format($s['saldo'], 0, ',', '.') ?></td>
                    <td class="text-center">
                        <div class="btn-group btn-group-sm">
                            <button class="btn btn-outline-primary btn-riwayat" 
                                    data-id="<?= $s['id_siswa'] ?>" 
                                    data-nama="<?= htmlspecialchars($s['nama_siswa']) ?>" 
                                    data-nis="<?= $s['nis'] ?>" 
                                    data-kelas="<?= htmlspecialchars($s['nama_kelas']) ?>" 
                                    title="Buku Bank"><i class="bi bi-eye-fill"></i></button>
                            
                            <button class="btn btn-outline-warning btn-edit" 
                                    data-id="<?= $s['id_siswa'] ?>" 
                                    data-nis="<?= $s['nis'] ?>" 
                                    data-nama="<?= $s['nama_siswa'] ?>" 
                                    data-kelas="<?= $s['id_kelas'] ?>" 
                                    data-username="<?= $s['username'] ?>"
                                    title="Edit"><i class="bi bi-pencil"></i></button>
                            
                            <button class="btn btn-outline-danger btn-delete" 
                                    data-id="<?= $s['id_siswa'] ?>" 
                                    data-nama="<?= $s['nama_siswa'] ?>" 
                                    title="Hapus"><i class="bi bi-trash"></i></button>
                        </div>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <?php if($total_pages > 1): ?>
    <nav class="mt-4">
        <ul class="pagination pagination-sm justify-content-center">
            <li class="page-item <?= ($page <= 1) ? 'disabled' : '' ?>">
                <a class="page-link" href="?page=<?= $page-1 ?>&sort=<?= $sort ?>&search=<?= $search ?>">Prev</a>
            </li>
            <?php for($i=1; $i<=$total_pages; $i++): ?>
                <li class="page-item <?= ($page == $i) ? 'active' : '' ?>">
                    <a class="page-link" href="?page=<?= $i ?>&sort=<?= $sort ?>&search=<?= $search ?>"><?= $i ?></a>
                </li>
            <?php endfor; ?>
            <li class="page-item <?= ($page >= $total_pages) ? 'disabled' : '' ?>">
                <a class="page-link" href="?page=<?= $page+1 ?>&sort=<?= $sort ?>&search=<?= $search ?>">Next</a>
            </li>
        </ul>
    </nav>
    <?php endif; ?>
</div>

<div class="modal fade" id="modalRiwayat" tabindex="-1">
    <div class="modal-dialog modal-lg">
        <div class="modal-content">
            <div class="modal-header bg-dark text-white">
                <h5 class="modal-title"><i class="bi bi-journal-text me-2"></i>Buku Tabungan</h5>
                <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body p-0">
                <div class="p-3 bg-light border-bottom text-center">
                    <h5 class="mb-0 fw-bold" id="view_identitas"></h5>
                </div>
                <div class="table-responsive">
                    <table class="table table-sm table-striped mb-0">
                        <thead class="table-secondary small text-uppercase">
                            <tr>
                                <th class="ps-3">Tanggal</th>
                                <th>Ket</th>
                                <th class="text-end">Debet (+)</th>
                                <th class="text-end">Kredit (-)</th>
                                <th class="text-end pe-3">Saldo</th>
                            </tr>
                        </thead>
                        <tbody id="riwayat_body" class="small"></tbody>
                    </table>
                </div>
            </div>
            <div class="modal-footer btn-printer-none">
                <button class="btn btn-secondary btn-sm" data-bs-dismiss="modal">Tutup</button>
                <button class="btn btn-primary btn-sm" onclick="window.print()"><i class="bi bi-printer"></i> Cetak</button>
            </div>
        </div>
    </div>
</div>

<div class="modal fade" id="modalSiswa" tabindex="-1">
    <div class="modal-dialog">
        <form class="modal-content" method="POST" id="formSiswa">
            <div class="modal-header">
                <h5 id="modalLabel">Tambah Nasabah</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <div class="modal-body">
                <input type="hidden" name="id_siswa" id="id_siswa">
                <input type="hidden" name="action" id="action_siswa" value="tambah_siswa">
                <div class="mb-3">
                    <label class="form-label">NIS (Nomor Rekening)</label>
                    <input type="text" name="nis" id="nis" class="form-control" required>
                </div>
                <div class="mb-3">
                    <label class="form-label">Nama Lengkap</label>
                    <input type="text" name="nama_siswa" id="nama_siswa" class="form-control" required>
                </div>
                <div class="mb-3">
                    <label class="form-label">Kelas</label>
                    <select name="id_kelas" id="id_kelas" class="form-select">
                        <?php foreach($kelasList as $k): ?>
                            <option value="<?= $k['id_kelas'] ?>"><?= htmlspecialchars($k['nama_kelas']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div id="editFields" style="display:none;">
                    <hr>
                    <div class="mb-3">
                        <label class="form-label">Username</label>
                        <input type="text" name="username" id="username" class="form-control">
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Ganti Password</label>
                        <input type="password" name="password" class="form-control" placeholder="Kosongkan jika tidak diganti">
                        <small class="text-muted">Default awal adalah 123456</small>
                    </div>
                </div>
            </div>
            <div class="modal-footer">
                <button type="submit" class="btn btn-primary">Simpan</button>
            </div>
        </form>
    </div>
</div>

<div class="modal fade" id="deleteModalSiswa" tabindex="-1">
    <div class="modal-dialog modal-sm">
        <form class="modal-content" method="POST">
            <div class="modal-body text-center py-4">
                <input type="hidden" name="id_hapus" id="delete_id_siswa">
                <input type="hidden" name="action" value="hapus_siswa">
                <i class="bi bi-trash text-danger display-4"></i>
                <h5 class="mt-3">Hapus nasabah <br><b id="nama_del_siswa"></b>?</h5>
                <p class="small text-muted">Data akan dipindahkan ke folder Bulk (Cadangan).</p>
                <div class="d-flex justify-content-center gap-2 mt-4">
                    <button type="button" class="btn btn-light" data-bs-dismiss="modal">Batal</button>
                    <button type="submit" class="btn btn-danger">Hapus</button>
                </div>
            </div>
        </form>
    </div>
</div>

<script>
function resetForm() {
    document.getElementById('formSiswa').reset();
    document.getElementById('modalLabel').innerText = 'Tambah Nasabah';
    document.getElementById('action_siswa').value = 'tambah_siswa';
    document.getElementById('id_siswa').value = '';
    document.getElementById('editFields').style.display = 'none';
}

document.addEventListener('DOMContentLoaded', function() {
    // Tombol Edit
    const editBtns = document.querySelectorAll('.btn-edit');
    editBtns.forEach(btn => {
        btn.addEventListener('click', function() {
            document.getElementById('modalLabel').innerText = 'Edit Nasabah';
            document.getElementById('id_siswa').value = this.dataset.id;
            document.getElementById('nis').value = this.dataset.nis;
            document.getElementById('nama_siswa').value = this.dataset.nama;
            document.getElementById('id_kelas').value = this.dataset.kelas;
            document.getElementById('username').value = this.dataset.username;
            document.getElementById('action_siswa').value = 'update_siswa';
            
            document.getElementById('editFields').style.display = 'block';
            
            new bootstrap.Modal(document.getElementById('modalSiswa')).show();
        });
    });

    // Tombol Hapus
    const delBtns = document.querySelectorAll('.btn-delete');
    const delModal = new bootstrap.Modal(document.getElementById('deleteModalSiswa'));
    delBtns.forEach(btn => {
        btn.addEventListener('click', function() {
            document.getElementById('delete_id_siswa').value = this.dataset.id;
            document.getElementById('nama_del_siswa').textContent = this.dataset.nama;
            delModal.show();
        });
    });

    // Tombol Riwayat (AJAX)
    const riwayatModal = new bootstrap.Modal(document.getElementById('modalRiwayat'));
    const btnRiwayats = document.querySelectorAll('.btn-riwayat');
    btnRiwayats.forEach(btn => {
        btn.addEventListener('click', function() {
            document.getElementById('view_identitas').innerText = `${this.dataset.nama} - ${this.dataset.nis} - ${this.dataset.kelas}`;
            document.getElementById('riwayat_body').innerHTML = '<tr><td colspan="5" class="text-center">Memuat data...</td></tr>';
            riwayatModal.show();
            
            fetch(`siswa.php?get_riwayat=1&id_siswa=${this.dataset.id}`)
                .then(r => r.text())
                .then(data => { 
                    document.getElementById('riwayat_body').innerHTML = data; 
                });
        });
    });
});
</script>

<?php include 'footer.php'; ?>