<?php
    // session_start();
    include "../db.php";
    require '../auth.php';
    restrictAccess(['master_admin']);
    date_default_timezone_set('Asia/Kolkata');
    $currentTime = date('M j, Y g:i a');

    if ( !isset($_SESSION['uid']) && !isset($_SESSION['uref']) && !isset($_SESSION['uref_code']) && !isset($_SESSION['uname']) && !isset($_SESSION['utype']) ) {
        header("Location: logout.php");
        exit();
    }
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <?php include "include/meta.php"; ?>
    <?php include "include/css.php"; ?>
    <?php include "include/php-functions.php"; ?>
    <script src="https://cdn.ckeditor.com/4.6.2/standard-all/ckeditor.js"></script>
</head>

<body>
    <!--  Header -->
    <?php include "include/header.php"; ?>
    <!-- #header -->

    <section id="inner-body">
        <div class="body-content">
            <div class="container-fluid">
                <div class="card mt-3 mb-3">
                    <div class="card-header">
                        <div class="row align-items-center">
                            <div class="col-md-12 col-12 mt-2 mb-2">
                                <div class="row justify-content-between align-items-center">
                                    <div class="col-md-8 col-12 mb-2">
                                        <h4 class="text-gray-800 m-0">All Billing History</h4>
                                    </div>
                                    <div class="col-md-4 col-12 text-end">
                                        <a href="index.php" class="btn btn-warning text-white btn-sm"><i
                                                class="fa fa-arrow-left"></i> Back</a>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="card-body">
                        <?php
                            $limit = 10;
                            $page = isset($_GET['page']) ? intval($_GET['page']) : 1;
                            $offset = ($page - 1) * $limit;

                            $invoice_search = isset($_GET['invoice_search']) ? trim($_GET['invoice_search']) : '';
                            $student_search = isset($_GET['student_search']) ? trim($_GET['student_search']) : '';
                            $asso_search = isset($_GET['asso_search']) ? trim($_GET['asso_search']) : '';
                            $start_date = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
                            $end_date = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
                            $show_pending = isset($_GET['show_pending']) && $_GET['show_pending'] == '1';
                            $course_search = isset($_GET['course_search']) ? $_GET['course_search'] : '';
                            $selected_pid = isset($_GET['selected_pid']) ? $_GET['selected_pid'] : '';
                            $batch = isset($_GET['batch']) ? $_GET['batch'] : '';

                            $where = "WHERE (sb_type = 'Bill' OR sb_type = '')";

                            if ($show_pending) {
                                $where .= " AND sb_recycle = '0'";
                            } else {
                                $where .= " AND sb_recycle = '1'";
                            }

                            // Invoice filter
                            if (!empty($invoice_search)) {
                                $invoice_search_safe = mysqli_real_escape_string($con, $invoice_search);
                                $where .= " AND sb_invoice LIKE '%$invoice_search_safe%'";
                            }

                            // Associate filter
                            if (!empty($asso_search)) {
                                $asso_search_safe = mysqli_real_escape_string($con, $asso_search);

                                // 1. Get the Associate's uref_code
                                $fran_sql = mysqli_query($con, "SELECT uref_code FROM users WHERE utype = 'Associate' AND uname = '$asso_search_safe'");
    
                                if ($fran_row = mysqli_fetch_assoc($fran_sql)) {
                                    $associate_uref = trim($fran_row['uref_code']);

                                    // 2. Get all Sub-Associate uref_codes under this Associate
                                    $sub_assoc_codes = [];
                                    $sub_q = mysqli_query($con, "SELECT uref_code FROM users WHERE utype = 'Sub Associate' AND admin_ref = '$associate_uref'");
                                    while ($sub_row = mysqli_fetch_assoc($sub_q)) {
                                        $sub_assoc_codes[] = "'" . mysqli_real_escape_string($con, trim($sub_row['uref_code'])) . "'";
                                    }

                                    // 3. Combine Associate + Sub-Associate uref_codes
                                    $ref_codes = array_merge(["'$associate_uref'"], $sub_assoc_codes);
                                    $ref_in = implode(",", $ref_codes);

                                    // 4. Get all student UIDs whose admin_ref matches ANY of those codes
                                    $uid_q = mysqli_query($con, "SELECT uid FROM users WHERE admin_ref IN ($ref_in)");
                                    $uid_list = [];
                                    while ($uid_row = mysqli_fetch_assoc($uid_q)) {
                                        $uid_list[] = "'" . $uid_row['uid'] . "'";
                                    }

                                    // 5. Apply filter
                                    if (!empty($uid_list)) {
                                        $where .= " AND sb_for IN (" . implode(",", $uid_list) . ")";
                                    } else {
                                        $where .= " AND 1=0"; // No matching students
                                    }

                                } else {
                                    $where .= " AND 1=0"; // Associate not found
                                }
                            }

                            // Student filter
                            if (!empty($student_search)) {
                                $student_search_safe = mysqli_real_escape_string($con, $student_search);
                                $uid_q = mysqli_query($con, "SELECT uid FROM users WHERE utype = 'Student' AND uname = '$student_search'");
                                if ($uid_row = mysqli_fetch_assoc($uid_q)) {
                                    $student_uid = $uid_row['uid'];
                                    $where .= " AND sb_for = '$student_uid'";
                                } else {
                                    $where .= " AND 1=0"; // No match
                                }
                            }

                            // Date filter
                            if (!empty($start_date) && !empty($end_date)) {
                                $start_ts = strtotime($start_date);
                                $end_ts = strtotime($end_date);

                                $start_formatted = date("M j, Y", $start_ts);
                                $end_formatted = date("M j, Y", $end_ts);

                                $where .= " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) BETWEEN STR_TO_DATE('$start_formatted', '%b %e, %Y') AND STR_TO_DATE('$end_formatted', '%b %e, %Y')";
                            }

                            if (!empty($start_date) && empty($end_date)) {
                                $start_ts = strtotime($start_date);
                                $start_formatted = date("M j, Y", $start_ts);
                                $where .= " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) = STR_TO_DATE('$start_formatted', '%b %e, %Y')";
                            }

                            if (empty($start_date) && !empty($end_date)) {
                                $end_ts = strtotime($end_date);
                                $end_formatted = date("M j, Y", $end_ts);
                                $where .= " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) <= STR_TO_DATE('$end_formatted', '%b %e, %Y')";
                            }
                            
                            //Search By Course
                            if (!empty($selected_pid)) {
                                $selected_pid = mysqli_real_escape_string($con, $selected_pid);
                                $where .= " AND sb_course = '$selected_pid'";
                            }
                            
                            //Search By Batch
                            if (!empty($batch)) {
                                $batch_sql = mysqli_query($con, "SELECT * FROM branch WHERE b_id = '$batch'");
                                if($batch_row = mysqli_fetch_assoc($batch_sql)){
                                    $batch_name = $batch_row["batch_title"];
                                }
                                $batch = mysqli_real_escape_string($con, $batch);
                                $where .= " AND sb_batch = '$batch'";
                            }

                            // Query
                            $sql = "SELECT * FROM student_bill $where ORDER BY sb_id DESC LIMIT $offset, $limit";
                            // echo $sql; // for query debuging
                            $result = mysqli_query($con, $sql);

                            // Pagination count
                            $total_sql = "SELECT COUNT(*) FROM student_bill $where";
                            $total_result = mysqli_query($con, $total_sql);
                            $total_row = mysqli_fetch_row($total_result);
                            $total_records = $total_row[0];
                            $total_pages = ceil($total_records / $limit);

                        ?>
                        <?php 
                            $total_paid = 0;
                            $total_payable = 0;

                            // To track unique (student, course) combinations
                            $unique_pairs = [];

                            if (mysqli_num_rows($result) > 0) {
                                // Reset pointer to start
                                mysqli_data_seek($result, 0);

                                while ($row = mysqli_fetch_assoc($result)) {
                                    $total_paid += floatval($row['sb_amount']); // Always add paid

                                    $student_id = $row['sb_for'];
                                    $course_id = $row['sb_course'];

                                    // Create a unique key for student-course pair
                                    $pair_key = $student_id . '_' . $course_id;

                                    // Only add pAmount if this pair hasn't been counted
                                    if (!isset($unique_pairs[$pair_key])) {
                                        $unique_pairs[$pair_key] = true;

                                        if (!empty($course_id)) {
                                            $p_query = mysqli_query($con, "SELECT pAmount FROM product WHERE pid = '" . mysqli_real_escape_string($con, $course_id) . "'");
                                            if ($p_row = mysqli_fetch_assoc($p_query)) {
                                                $total_payable += floatval($p_row['pAmount']);
                                            }
                                        }
                                    }
                                }

                                // Reset again for table display
                                mysqli_data_seek($result, 0);
                            }
                        ?>
                        <div class="row justify-content-end">
                            <!-- Search Form -->
                            <div class="col-md-12 mb-3">
                                <form method="get" action="all-bill.php">
                                    <div class="row g-2">
                                        <!-- Invoice Search -->
                                        <div class="col-md-2">
                                            <input type="text" list="FirstNo" name="invoice_search"
                                                class="form-control form-control-sm"
                                                placeholder="Search by Invoice Number"
                                                value="<?= htmlspecialchars($invoice_search) ?>">
                                            <datalist id="FirstNo">
                                                <?php
                                                $res = mysqli_query($con, "SELECT DISTINCT sb_invoice FROM student_bill $where ORDER BY sb_id ASC");
                                                while ($row = mysqli_fetch_assoc($res)) {
                                                    echo '<option value="' . $row['sb_invoice'] . '">';
                                                }
                                                ?>
                                            </datalist>
                                        </div>

                                        <!-- Student Name Search -->
                                        <div class="col-md-2">
                                            <input type="text" list="First" name="student_search"
                                                class="form-control form-control-sm"
                                                placeholder="Search by Student Name"
                                                value="<?= htmlspecialchars($student_search) ?>">
                                            <datalist id="First">
                                                <?php
                                                $res = mysqli_query($con, "
                                                    SELECT DISTINCT u.uname 
                                                    FROM users u 
                                                    JOIN student_bill sb ON sb.sb_for = u.uid 
                                                    WHERE u.utype = 'Student' AND u.udisplay != 'disable'
                                                    ORDER BY u.uid ASC
                                                ");
                                                while ($row = mysqli_fetch_assoc($res)) {
                                                    echo '<option value="' . $row['uname'] . '">' . $row['uname'] . '</option>';
                                                }
                                                ?>
                                            </datalist>
                                        </div>

                                        <!-- Associate Name Search -->
                                        <div class="col-md-2">
                                            <input type="text" list="second" class="form-control form-control-sm"
                                                name="asso_search" value="<?= htmlspecialchars($asso_search) ?>"
                                                placeholder="Search By Associate Name">
                                            <datalist id="second">
                                                <?php 
                                                    $sql = "SELECT DISTINCT uname FROM users WHERE utype = 'Associate' AND udisplay != 'disable' ORDER BY uid ASC";
                                                    $res = mysqli_query($con,$sql);
                                                    while ($row = mysqli_fetch_assoc($res)) {
                                                ?>
                                                <option value="<?php echo $row['uname']; ?>">
                                                    <?php echo $row['uname']; ?></option>
                                                <?php } ?>
                                            </datalist>
                                        </div>

                                        <!-- Course Name Search -->
                                        <?php
                                        if(!empty($student_id)){
                                            $client_id = $student_id;
                                            $courseWhere = "WHERE p.pClient = '$client_id'";
                                        }
                                        ?>
                                        <div class="col-md-2">
                                            <!-- Course Name Search -->
                                            <input type="text" list="third" id="course_search" name="course_search"
                                                class="form-control form-control-sm" placeholder="Search by Course Name"
                                                value="<?= htmlspecialchars($course_search) ?>">

                                            <!-- Hidden field to hold selected pid -->
                                            <input type="hidden" name="selected_pid" id="selected_pid">

                                            <datalist id="third">
                                                <?php
                                                $res = mysqli_query($con, "
                                                    SELECT DISTINCT p.pid, cc.course_name 
                                                    FROM product p 
                                                    JOIN clg_course cc ON cc.sl_no = p.product 
                                                    $courseWhere
                                                    ORDER BY cc.course_name ASC
                                                ");

                                                $coursePidMap = [];
                                                while ($row = mysqli_fetch_assoc($res)) {
                                                    $course_name = htmlspecialchars($row['course_name']);
                                                    $pid = htmlspecialchars($row['pid']);
                                                    $coursePidMap[$course_name] = $pid;
                                                    echo '<option value="' . $course_name . '">' . $course_name . '</option>';
                                                }
                                                ?>
                                            </datalist>
                                        </div>

                                        <!-- Batch Namw Search -->
                                        <div class="col-md-2 mb-2">
                                            <select name="batch" id="batch" class="form-control form-control-sm">
                                                <?php if(!empty($batch_name)){?>
                                                <option value="<?= $batch?>"><?= $batch_name;?></option>
                                                <?php } else{?>
                                                <option value="">Select Course First</option>
                                                <?php }?>
                                            </select>
                                        </div>

                                        <!-- Date From -->
                                        <div class="col-md-1">
                                            <input type="date" name="start_date" class="form-control form-control-sm"
                                                value="<?= htmlspecialchars($_GET['start_date'] ?? '') ?>">
                                        </div>

                                        <!-- Date To -->
                                        <div class="col-md-1">
                                            <input type="date" name="end_date" class="form-control form-control-sm"
                                                value="<?= htmlspecialchars($_GET['end_date'] ?? '') ?>">
                                        </div>
                                        <?php if ($show_pending): ?>
                                        <input type="hidden" name="show_pending" value="1">
                                        <?php endif; ?>

                                        <!-- Buttons -->
                                        <div class="col-md-4 text-end">
                                            <div class="d-flex gap-1 justify-content-start">
                                                <button type="submit" class="btn btn-primary btn-sm ">Search</button>
                                                <a href="all-bill.php" class="btn btn-secondary btn-sm ">Reset</a>

                                                <?php
                                                    $check_pending = mysqli_query($con, "SELECT 1 FROM student_bill WHERE sb_recycle = '0' LIMIT 1");
                                                    if (mysqli_num_rows($check_pending) > 0) {
                                                        echo '<a href="all-bill.php?show_pending=1" class="btn btn-warning btn-sm  text-white">Verify</a>';
                                                    }
                                                    // Conditionally show the PDF button if any filters are applied
                                                    if (!empty($_GET['invoice_search']) || !empty($_GET['student_search']) || !empty($_GET['asso_search']) || !empty($_GET['course_search']) || !empty($_GET['batch']) || !empty($_GET['start_date']) || !empty($_GET['end_date'])) {
                                                        // Preserve filters in the query string
                                                        $query_string = http_build_query($_GET);
                                                        echo '<a href="download-bill-pdf.php?' . $query_string . '" class="btn btn-success btn-sm" target="_blank"><i class="fa fa-download"></i> Download PDF</a>';
                                                    }
                                                ?>
                                            </div>
                                        </div>
                                    </div>
                                </form>
                            </div>

                            <?php if ($total_paid > 0 || $total_payable > 0): ?>
                            <div class="alert alert-info d-flex justify-content-between align-items-center">
                                <div><strong>Total Payable:</strong> ₹<?= number_format($total_payable, 2) ?></div>
                                <div><strong>Total Paid:</strong> ₹<?= number_format($total_paid, 2) ?></div>
                                <div><strong>Pending:</strong> ₹<?= number_format($total_payable - $total_paid, 2) ?>
                                </div>
                            </div>
                            <?php endif; ?>


                            <?php if(mysqli_num_rows($result)>0){ ?>
                            <!-- Table -->
                            <div class="col-md-12 table-responsive">
                                <table class="table table-bordered">
                                    <thead>
                                        <tr>
                                            <th>#</th>
                                            <th>Invoice</th>
                                            <th>Student Name</th>
                                            <th>Course</th>
                                            <!--<th>College</th>-->
                                            <th>Amount</th>
                                            <th>Date</th>
                                            <th>Bill By</th>
                                            <th>Actions</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                            $i = $offset + 1;
                                            while ($row = mysqli_fetch_assoc($result)) {
                                                $total_paid = 0;
                                                $course_ids = [];

                                                $client_id = $row['sb_for'];
                                                $student_name = '';
                                                $course = '';
                                                $college = '';
                                                $department = '';

                                                // Get student name
                                                $client_sql = mysqli_query($con, "SELECT uname FROM users WHERE uid = '$client_id'");
                                                if ($client_row = mysqli_fetch_assoc($client_sql)) {
                                                    $student_name = $client_row["uname"];
                                                }

                                                // Get course
                                                $product_sql = mysqli_query($con, "SELECT * FROM product WHERE pid = '{$row['sb_course']}'");
                                                if ($product_row = mysqli_fetch_assoc($product_sql)) {
                                                    $course_q = mysqli_query($con, "SELECT course_name FROM clg_course WHERE sl_no = '{$product_row["product"]}'");
                                                    if ($cr = mysqli_fetch_assoc($course_q)) {
                                                        $course = $cr['course_name'];
                                                    }
                                                }

                                                // Get college
                                                $college_sql = mysqli_query($con, "SELECT uname FROM users WHERE uid = '{$row['sb_college']}'");
                                                if ($college_row = mysqli_fetch_assoc($college_sql)) {
                                                    $college = $college_row['uname'];
                                                }

                                                // Get department
                                                $dept_sql = mysqli_query($con, "SELECT cat_name FROM clg_course_cat WHERE sl_no = '{$row['sb_dept']}'");
                                                if ($dept_row = mysqli_fetch_assoc($dept_sql)) {
                                                    $department = $dept_row['cat_name'];
                                                }

                                                // Get Bill By
                                                $sb_by = $row['sb_by'];
                                                if ($sb_by == 1) {
                                                    $bill_by = "Master Admin";
                                                } else {
                                                    $by_sql = mysqli_query($con, "SELECT uname, utype FROM users WHERE uid = '$sb_by'");
                                                    if ($by_row = mysqli_fetch_assoc($by_sql)) {
                                                        $bill_by = $by_row['uname'] . " (" . $by_row['utype'] . ")";
                                                    } else {
                                                        $bill_by = "Unknown";
                                                    }
                                                }

                                                // Get batch name
                                                $batch = "";
                                                $batch_sql = mysqli_query($con, "SELECT batch_title FROM branch WHERE b_id = '{$row['sb_batch']}'");
                                                if (mysqli_num_rows($batch_sql) > 0) {
                                                    $batch_row = mysqli_fetch_assoc($batch_sql);
                                                    $batch = $batch_row['batch_title'];
                                                }
                                            ?>
                                        <tr>
                                            <td><?= $i++ ?></td>
                                            <td><?= $row['sb_invoice'] ?></td>
                                            <td><?= $student_name ?></td>
                                            <td><?= $course . ' ( ' . $department . ' ) <br/> [ Batch: ' . $batch . ']' ?>
                                            </td>
                                            <!--<td><?= $college ?></td>-->
                                            <td>₹<?= number_format($row['sb_amount'], 2) ?></td>
                                            <td><?= $row['sb_date'] ?></td>
                                            <td><?= $bill_by ?></td>
                                            <td>
                                                <?php if ($row['sb_recycle'] == '1') { ?>
                                                <!-- ✅ already accepted → show Print -->
                                                <a href="print-bill.php?id=<?= $row['sb_id'] ?>"
                                                    class="btn btn-sm btn-success" target="_blank">
                                                    <i class="fa fa-print"></i> Print
                                                </a>
                                                <?php } else { ?>
                                                <!-- 🔄 pending verification → show View -->
                                                <button type="button"
                                                    class="btn btn-sm btn-warning view-bill text-white"
                                                    data-id="<?= $row['sb_id'] ?>" data-bs-toggle="modal"
                                                    data-bs-target="#billModal">
                                                    <i class="fa fa-eye"></i> View
                                                </button>
                                                <?php } ?>
                                            </td>

                                        </tr>
                                        <?php } ?>
                                    </tbody>
                                </table>

                                <!-- Pagination -->
                                <?php if ($total_pages > 1): ?>
                                <nav>
                                    <ul class="pagination">
                                        <?php for ($p = 1; $p <= $total_pages; $p++): ?>
                                        <li class="page-item <?= ($p == $page) ? 'active' : '' ?>">
                                            <a class="page-link"
                                                href="?page=<?= $p ?>&invoice_search=<?= urlencode($invoice_search) ?>&student_search=<?= urlencode($student_search) ?>&start_date=<?= urlencode($start_date) ?>&end_date=<?= urlencode($end_date) ?><?= $show_pending ? '&show_pending=1' : '' ?>">
                                                <?= $p ?>
                                            </a>
                                        </li>
                                        <?php endfor; ?>
                                    </ul>
                                </nav>
                                <?php endif; ?>
                            </div>
                            <?php } else{ echo "<div class='text-center'>No Data Found.</div>";}?>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <!-- Bill‑details Modal (one modal for all rows) -->
        <div class="modal fade" id="billModal" tabindex="-1" aria-hidden="true">
            <div class="modal-dialog modal-lg modal-dialog-scrollable">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title">Bill Details</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
                    </div>

                    <div class="modal-body" id="billModalBody">
                        <div class="text-center py-5">
                            <div class="spinner-border"></div>
                        </div>
                    </div>

                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                        <button type="button" id="acceptBill" class="btn btn-primary d-none">Accept</button>
                    </div>
                </div>
            </div>
        </div>

        <?php include "include/footer.php"; ?>
    </section>

    <!-- JavaScript Libraries -->

    <?php include "include/js.php"; ?>

    <script>
    const coursePidMap = <?= json_encode($coursePidMap) ?>;

    document.getElementById('course_search').addEventListener('input', function() {
        const selectedCourse = this.value;
        const pid = coursePidMap[selectedCourse] || '';
        document.getElementById('selected_pid').value = pid;

        if (pid) {
            fetchBatches(pid);
        } else {
            document.getElementById('batch').innerHTML = '<option value="">Select Course First</option>';
        }
    });

    function fetchBatches(course_id) {
        $.ajax({
            url: 'fetch_batch.php', // your PHP script
            type: 'POST',
            data: {
                course_id: course_id
            },
            success: function(response) {
                document.getElementById('batch').innerHTML = response;
            },
            error: function() {
                document.getElementById('batch').innerHTML =
                    '<option value="">Failed to load batches</option>';
            }
        });
    }
    </script>


    <script>
    $(function() {
        let currentID = null;

        // When the modal is triggered by a .view-bill button
        $('#billModal').on('show.bs.modal', function(e) {
            const btn = $(e.relatedTarget);
            currentID = btn.data('id');

            // Reset modal
            $('#billModalBody').html(
                '<div class="text-center py-5"><div class="spinner-border"></div></div>');
            $('#acceptBill').addClass('d-none'); // hide Accept until content loads

            // Fetch bill details
            $.get('fetch-bill.php', {
                id: currentID
            }, function(html) {
                $('#billModalBody').html(html);
                $('#acceptBill').removeClass('d-none');
            });
        });

        // Accept button ⇒ flip sb_recycle to 1
        $('#acceptBill').on('click', function() {
            if (!currentID) return;

            $.post('accept-bill.php', {
                id: currentID
            }, function(resp) {
                if (resp.trim() === 'ok') {
                    // Replace View btn with Print link in the table row
                    const row = $('button.view-bill[data-id="' + currentID + '"]').closest(
                        'td');
                    row.html(
                        '<a href="print-bill.php?id=' + currentID + '" ' +
                        'class="btn btn-sm btn-success" target="_blank">' +
                        '<i class="fa fa-print"></i> Print</a>'
                    );
                    $('#billModal').modal('hide');
                } else {
                    alert('Failed to accept. Try again.');
                }
            });
        });
    });
    </script>


</body>

</html>