<?php
require_once(__DIR__ . "/../header.php");
require_once(__DIR__ . "/../../public/connection.php");

if (!isset($_SESSION)) session_start();

// Delete invoice
if(isset($_GET['delete_invoice'])){
    $del_id = $_GET['delete_invoice'];
    $stmt_del = $connection->prepare("DELETE FROM fee_payment WHERE id=?");
    $stmt_del->bind_param('i', $del_id);
    if($stmt_del->execute()){
        $_SESSION['msg'] = "Invoice deleted successfully!";
        $_SESSION['type'] = "success";
        header("Location: generate_fee_invoice");
        exit;
    }
}

// Bulk Delete Invoices
if (isset($_POST['bulk_delete']) && !empty($_POST['invoice_ids'])) {
    $ids = array_map('intval', $_POST['invoice_ids']);
    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $stmt = $connection->prepare("DELETE FROM fee_payment WHERE id IN ($placeholders)");
    $types = str_repeat('i', count($ids));
    $stmt->bind_param($types, ...$ids);
    if ($stmt->execute()) {
        $_SESSION['msg']  = count($ids) . " invoice(s) deleted successfully!";
        $_SESSION['type'] = "success";
    }
    header("Location: generate_fee_invoice");
    exit;
}

// Fetch classes and sessions
$classes = $connection->query("SELECT DISTINCT class FROM students ORDER BY class");
$sessions = $connection->query("SELECT DISTINCT session FROM fee_structure ORDER BY session");
$terms = ["First Term","Second Term","Third Term"];

$students = [];
$generated_invoices = [];

// Load students
if(isset($_POST['load_students'])){
    $class = $_POST['class'];
    $term = $_POST['term'];
    $session_sel = $_POST['session'];

    $stmt_students = $connection->prepare("
        SELECT s.reg_number, CONCAT(s.firstname,' ',s.othername,' ',s.lastname) AS student_name, 
               s.family_id, f.family_name, f.discount_percent
        FROM students s
        LEFT JOIN families f ON s.family_id=f.id
        WHERE s.class=? AND s.status='active'
        ORDER BY s.family_id ASC
    ");
    $stmt_students->bind_param('s',$class);
    $stmt_students->execute();
    $res_students = $stmt_students->get_result();
    while($r = $res_students->fetch_assoc()){
        // Check if an invoice already exists
        $stmt_check = $connection->prepare("SELECT student_type, extra_fee FROM fee_payment WHERE reg_number=? AND student_class=? AND fee_term=? AND fee_session=? LIMIT 1");
        $stmt_check->bind_param('ssss', $r['reg_number'], $class, $term, $session_sel);
        $stmt_check->execute();
        $res_check = $stmt_check->get_result();
        $existing = $res_check->fetch_assoc() ?? [];
        $stmt_check->close();

        $students[] = [
            'reg_number' => $r['reg_number'],
            'student_name' => $r['student_name'],
            'family_id' => $r['family_id'],
            'family_name' => $r['family_name'],
            'discount_percent' => $r['discount_percent'] ?? 0,
            'student_type' => $existing['student_type'] ?? 'returning',
            'extra_fee' => floatval($existing['extra_fee'] ?? 0)
        ];
    }
    $stmt_students->close();
}

// Generate invoices
if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['generate_invoices'])){
    $class = $_POST['class'];
    $term = $_POST['term'];
    $session_sel = $_POST['session'];

    $success_count = 0;
    $updated_count = 0;

    foreach($_POST['students'] as $reg_number => $data){
        $student_name = $data['student_name'];
        $family_id = !empty($data['family_id']) ? $data['family_id'] : NULL;
        $student_type = $data['student_type'] ?? 'returning';
        $extra_fee = floatval($data['extra_fee'] ?? 0);

        // Base fee
        $stmt_fee = $connection->prepare("SELECT amount FROM fee_structure WHERE class=? AND term=? AND session=?");
        $stmt_fee->bind_param('sss',$class,$term,$session_sel);
        $stmt_fee->execute();
        $fee_res = $stmt_fee->get_result();
        $base_fee = floatval($fee_res->fetch_assoc()['amount'] ?? 0);
        $stmt_fee->close();

        // Family discount
        $discount_percent = 0;
        if($family_id){
            $stmt_family = $connection->prepare("SELECT discount_percent FROM families WHERE id=? LIMIT 1");
            $stmt_family->bind_param('i',$family_id);
            $stmt_family->execute();
            $res_family = $stmt_family->get_result();
            if($res_family->num_rows>0){
                $discount_percent = floatval($res_family->fetch_assoc()['discount_percent']);
            }
            $stmt_family->close();
        }

        // Compute final fee
        $final_fee = $base_fee;
        if($student_type==='new') $final_fee += $extra_fee;
        $discount_amount = ($final_fee * $discount_percent)/100;
        $total_fee = $final_fee - $discount_amount;

        // Check if invoice exists
        $stmt_check = $connection->prepare("SELECT id, amount_paid, custom_ref FROM fee_payment WHERE reg_number=? AND student_class=? AND fee_term=? AND fee_session=? LIMIT 1");
        $stmt_check->bind_param('ssss',$reg_number,$class,$term,$session_sel);
        $stmt_check->execute();
        $res_check = $stmt_check->get_result();

        if($res_check->num_rows>0){
            // Update existing invoice
            $row = $res_check->fetch_assoc();
            $balance = $total_fee - $row['amount_paid'];
            $payment_status = ($balance <=0)?'Paid':'Pending';
            $custom_ref = $row['custom_ref']; // Keep existing ref

            $stmt_update = $connection->prepare("
                UPDATE fee_payment 
                SET total_fee=?, student_balance=?, payment_status=?, discount_percent=?, student_type=?, extra_fee=?, final_fee=?
                WHERE id=?
            ");
            $stmt_update->bind_param(
                'dssddddi',
                $total_fee,
                $balance,
                $payment_status,
                $discount_percent,
                $student_type,
                $extra_fee,
                $final_fee,
                $row['id']
            );
            if($stmt_update->execute()) $updated_count++;
            $stmt_update->close();
        } else {
            // Insert new invoice
            $custom_ref = strtoupper("STD_{$reg_number}_" . time() . rand(1000,9999));
            $stmt_insert = $connection->prepare("
                INSERT INTO fee_payment
                (reg_number, student_name, student_class, total_fee, fee_term, fee_session, amount_paid, student_balance, payment_status, custom_ref, family_id, discount_percent, student_type, extra_fee, final_fee)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ");
            $amount_paid = 0.00;
            $balance = $total_fee;
            $payment_status = 'Pending';
            $family_id_param = $family_id ?? NULL;

            $stmt_insert->bind_param(
                'sssdssdssdssddd',
                $reg_number,
                $student_name,
                $class,
                $total_fee,
                $term,
                $session_sel,
                $amount_paid,
                $balance,
                $payment_status,
                $custom_ref,
                $family_id_param,
                $discount_percent,
                $student_type,
                $extra_fee,
                $final_fee
            );
            if($stmt_insert->execute()) $success_count++;
            $stmt_insert->close();
        }

        $generated_invoices[] = [
            'reg_number'=>$reg_number,
            'student_name'=>$student_name,
            'class'=>$class,
            'term'=>$term,
            'session'=>$session_sel,
            'total_fee'=>$total_fee,
            'discount_percent'=>$discount_percent,
            'payment_status'=>$payment_status,
            'custom_ref'=>$custom_ref,
            'family_id'=>$family_id,
            'student_type'=>$student_type,
            'extra_fee'=>$extra_fee,
            'final_fee'=>$final_fee
        ];

        $stmt_check->close();
    }

    $_SESSION['msg'] = "✅ $success_count new invoices, $updated_count updated!";
    $_SESSION['type'] = "success";
    header("Location: generate_fee_invoice");
    exit;
}

// Fetch all invoices with pagination
$default_limit = 10;
$rows_options = [10,50,100,500];
$limit = isset($_GET['limit']) && in_array((int)$_GET['limit'],$rows_options) ? (int)$_GET['limit'] : $default_limit;
$page  = isset($_GET['page']) && is_numeric($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;

// Total invoices
$total_res = $connection->query("SELECT COUNT(*) AS total FROM fee_payment");
$total_row = $total_res->fetch_assoc();
$total_records = $total_row['total'];
$total_pages = ceil($total_records / $limit);

// Fetch invoices for page
$stmt_invoices = $connection->prepare("
    SELECT fp.*, f.family_name 
    FROM fee_payment fp
    LEFT JOIN families f ON fp.family_id = f.id
    ORDER BY fp.id DESC
    LIMIT ? OFFSET ?
");
$stmt_invoices->bind_param('ii', $limit, $offset);
$stmt_invoices->execute();
$all_invoices = $stmt_invoices->get_result();
$stmt_invoices->close();
?>

<!-- UI Starts -->
<div class="page-title mb-3">
    <h1><i class="fa fa-money"></i> Generate Fee Invoice</h1>
    <p>Load students, adjust types/extra fees, apply family discount, and view generated invoices below.</p>
</div>

<!-- Selection Form -->
<div class="card mb-4">
    <div class="card-header"><h3 class="card-title">Select Class / Term / Session</h3></div>
    <form method="POST">
        <div class="card-body row g-3">
            <div class="col-md-4">
                <label>Class</label>
                <select name="class" class="form-control" required>
                    <option value="">-- Select Class --</option>
                    <?php while($row=$classes->fetch_assoc()): ?>
                        <option value="<?= htmlspecialchars($row['class']); ?>" <?= (isset($class) && $class==$row['class'])?'selected':''; ?>><?= htmlspecialchars($row['class']); ?></option>
                    <?php endwhile; ?>
                </select>
            </div>
            <div class="col-md-4">
                <label>Term</label>
                <select name="term" class="form-control" required>
                    <option value="">-- Select Term --</option>
                    <?php foreach($terms as $t): ?>
                        <option value="<?= $t; ?>" <?= (isset($term) && $term==$t)?'selected':''; ?>><?= $t ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-4">
                <label>Session</label>
                <select name="session" class="form-control" required>
                    <option value="">-- Select Session --</option>
                    <?php while($row=$sessions->fetch_assoc()): ?>
                        <option value="<?= htmlspecialchars($row['session']); ?>" <?= (isset($session_sel) && $session_sel==$row['session'])?'selected':''; ?>><?= htmlspecialchars($row['session']); ?></option>
                    <?php endwhile; ?>
                </select>
            </div>
        </div>
        <div class="card-footer text-end">
            <button type="submit" name="load_students" class="btn btn-info">Load Students</button>
        </div>
    </form>
</div>

<!-- Students Table -->
<?php if(!empty($students)): ?>
<form method="POST">
<input type="hidden" name="class" value="<?= htmlspecialchars($class); ?>">
<input type="hidden" name="term" value="<?= htmlspecialchars($term); ?>">
<input type="hidden" name="session" value="<?= htmlspecialchars($session_sel); ?>">

<div class="card mb-4">
    <div class="card-header"><h3 class="card-title">Adjust Fees & Types</h3></div>
    <div class="card-body table-responsive">
        <table class="table table-bordered table-striped">
            <thead>
                <tr>
                    <th>Reg Number</th>
                    <th>Student Name</th>
                    <th>Family Name</th>
                    <th>Type</th>
                    <th>Extra Fee</th>
                    <th>Total Fee (Preview)</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($students as $s):
                    $base_fee_stmt = $connection->prepare("SELECT amount FROM fee_structure WHERE class=? AND term=? AND session=?");
                    $base_fee_stmt->bind_param('sss',$class,$term,$session_sel);
                    $base_fee_stmt->execute();
                    $fee_res = $base_fee_stmt->get_result();
                    $base_fee = floatval($fee_res->fetch_assoc()['amount'] ?? 0);
                    $base_fee_stmt->close();
                    $discount_percent = floatval($s['discount_percent'] ?? 0);
                    $final_fee = $base_fee + floatval($s['extra_fee'] ?? 0);
                ?>
                <tr>
                    <td><?= htmlspecialchars($s['reg_number']); ?></td>
                    <td><?= htmlspecialchars($s['student_name']); ?></td>
                    <td><?= htmlspecialchars($s['family_name'] ?? '-'); ?></td>
                    <td>
                        <select name="students[<?= $s['reg_number']; ?>][student_type]" class="form-control student_type" data-basefee="<?= $base_fee ?>" data-discount="<?= $discount_percent ?>">
                            <option value="returning" <?= ($s['student_type']=='returning')?'selected':''; ?>>Returning</option>
                            <option value="new" <?= ($s['student_type']=='new')?'selected':''; ?>>New</option>
                        </select>
                    </td>
                    <td>
                        <input type="number" name="students[<?= $s['reg_number']; ?>][extra_fee]" value="<?= number_format($s['extra_fee'],2) ?>" class="form-control extra_fee" step="0.01" <?= ($s['student_type']=='returning')?'disabled':''; ?>>
                    </td>
                    <td class="total_fee_preview"><?= number_format($final_fee*(1-$discount_percent/100),2); ?></td>
                    <input type="hidden" name="students[<?= $s['reg_number']; ?>][student_name]" value="<?= htmlspecialchars($s['student_name']); ?>">
                    <input type="hidden" name="students[<?= $s['reg_number']; ?>][family_id]" value="<?= htmlspecialchars($s['family_id'] ?? ''); ?>">
                    <td>
                        <a href="?delete_invoice=<?= $s['reg_number']; ?>" class="btn btn-sm btn-danger" onclick="return confirm('Are you sure?')">Delete</a>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <div class="card-footer text-end">
        <button type="submit" name="generate_invoices" class="btn btn-primary">Generate Invoices</button>
    </div>
</div>
</form>

<script>
document.querySelectorAll('.student_type').forEach(function(sel){
    const tr = sel.closest('tr');
    const extra_input = tr.querySelector('.extra_fee');
    const base_fee = parseFloat(sel.dataset.basefee);
    const discount = parseFloat(sel.dataset.discount);
    let total_cell = tr.querySelector('.total_fee_preview');

    function updateTotal(){
        let extra = parseFloat(extra_input.value) || 0;
        let total = (base_fee + extra)*(1 - discount/100);
        total_cell.textContent = total.toFixed(2);
    }

    sel.addEventListener('change', function(){
        if(sel.value==='returning'){
            extra_input.value = 0;
            extra_input.disabled = true;
        } else {
            extra_input.disabled = false;
        }
        updateTotal();
    });

    extra_input.addEventListener('input', updateTotal);
    sel.dispatchEvent(new Event('change'));
});
</script>
<?php endif; ?>

<!-- Invoices Table with Pagination -->
<div class="card">
    <form method="POST" id="bulkDeleteForm">
        <div class="card-header d-flex justify-content-between align-items-center">
            <h3 class="card-title mb-0">All Generated Invoices</h3>
            <button type="submit" name="bulk_delete" class="btn btn-danger btn-sm" onclick="return confirm('Delete selected invoices?')">
                Bulk Delete
            </button>
        </div>
        <div class="card-body table-responsive">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th><input type="checkbox" id="selectAll"></th>
                        <th>ID</th>
                        <th>Reg Number</th>
                        <th>Student Name</th>
                        <th>Class</th>
                        <th>Term</th>
                        <th>Session</th>
                        <th>Total Fee</th>
                        <th>Final Fee</th>
                        <th>Discount %</th>
                        <th>Extra Fee</th>
                        <th>Family Name</th>
                        <th>Status</th>
                        <th>Receipt</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php while($row = $all_invoices->fetch_assoc()): ?>
                    <tr>
                        <td><input type="checkbox" name="invoice_ids[]" value="<?= $row['id']; ?>" class="invoice-checkbox"></td>
                        <td><?= $row['id']; ?></td>
                        <td><?= htmlspecialchars($row['reg_number']); ?></td>
                        <td><?= htmlspecialchars($row['student_name']); ?></td>
                        <td><?= htmlspecialchars($row['student_class']); ?></td>
                        <td><?= htmlspecialchars($row['fee_term']); ?></td>
                        <td><?= htmlspecialchars($row['fee_session']); ?></td>
                        <td><?= number_format($row['total_fee'], 2); ?></td>
                        <td><?= number_format($row['final_fee'], 2); ?></td>
                        <td><?= $row['discount_percent']; ?>%</td>
                        <td><?= number_format($row['extra_fee'],2); ?></td>
                        <td><?= htmlspecialchars($row['family_name'] ?? '-'); ?></td>
                        <td><?= htmlspecialchars($row['payment_status']); ?></td>
                        <td><?= htmlspecialchars($row['custom_ref']); ?></td>
                        <td>
                            <a href="?delete_invoice=<?= $row['id']; ?>" class="btn btn-sm btn-danger" onclick="return confirm('Are you sure?')">Delete</a>
                        </td>
                    </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
        <div class="card-footer d-flex justify-content-between align-items-center">
            <div>
                <label>Rows:</label>
                <select onchange="window.location='?limit='+this.value" class="form-select form-select-sm d-inline-block w-auto">
                    <?php foreach($rows_options as $opt): ?>
                        <option value="<?= $opt; ?>" <?= ($opt==$limit)?'selected':''; ?>><?= $opt; ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div>
                <?php for($p=1;$p<=$total_pages;$p++): ?>
                    <a href="?page=<?= $p; ?>&limit=<?= $limit; ?>" class="btn btn-sm <?= ($p==$page)?'btn-primary':'btn-secondary'; ?>"><?= $p; ?></a>
                <?php endfor; ?>
            </div>
        </div>
    </form>
</div>

<script>
document.getElementById('selectAll').addEventListener('change', function(){
    document.querySelectorAll('.invoice-checkbox').forEach(cb => cb.checked=this.checked);
});
</script>