<?php
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>
    <?php include "include/header.php"; ?>

    <?php if (isset($_GET["user"])): ?>
    <?php
        $associate_id = mysqli_real_escape_string($con, $_GET["user"]);
        $associate_sql = mysqli_query($con, "SELECT * FROM users WHERE utype = 'Associate' AND uid = '$associate_id'");
        if ($associate_row = mysqli_fetch_assoc($associate_sql)):
            $associate_name = $associate_row["uname"];

            // Filters
            $limit = 10;
            $page = isset($_GET['page']) ? intval($_GET['page']) : 1;
            $offset = ($page - 1) * $limit;

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

            $where = "";
            $filterStudent = '';
            $student_id = '';
            $courseWhere = "WHERE 1";

            // Associate filter
            if (!empty($asso_search)) {
                $asso_search_safe = mysqli_real_escape_string($con, $asso_search);
                $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']);
                    $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'])) . "'";
                    }
                    $ref_codes = array_merge(["'$associate_uref'"], $sub_assoc_codes);
                    $ref_in = implode(",", $ref_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'] . "'";
                    }
                    if (!empty($uid_list)) {
                        $where .= " AND sb_for IN (" . implode(",", $uid_list) . ")";
                        $filterStudent = " AND uid IN (" . implode(",", $uid_list) . ")";
                    } else {
                        $where .= " AND 1=0";
                    }
                } else {
                    $where .= " AND 1=0";
                }
            }

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

            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_safe'");
                if ($uid_row = mysqli_fetch_assoc($uid_q)) {
                    $student_id = $uid_row['uid'];
                    $where .= " AND sb_for = '$student_id'";
                    $courseWhere = "WHERE p.pClient = '$student_id'";
                } else {
                    $where .= " AND 1=0";
                }
            }

            if (!empty($start_date) && !empty($end_date)) {
                $start_formatted = date("M j, Y", strtotime($start_date));
                $end_formatted = date("M j, Y", strtotime($end_date));
                $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')";
            } elseif (!empty($start_date)) {
                $start_formatted = date("M j, Y", strtotime($start_date));
                $where .= " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) = STR_TO_DATE('$start_formatted', '%b %e, %Y')";
            } elseif (!empty($end_date)) {
                $end_formatted = date("M j, Y", strtotime($end_date));
                $where .= " AND DATE(STR_TO_DATE(sb_date, '%b %e, %Y %l:%i %p')) <= STR_TO_DATE('$end_formatted', '%b %e, %Y')";
            }

            if (!empty($selected_pid)) {
                // Clean and prepare the values for SQL IN clause
                $pid_array = array_filter(array_map('trim', explode(',', $selected_pid)));

                // Escape each PID
                $escaped_pids = array_map(function($pid) use ($con) {
                    return "'" . mysqli_real_escape_string($con, $pid) . "'";
                }, $pid_array);

                // Join for SQL
                $pid_list = implode(',', $escaped_pids);

                // Use IN clause
                if (!empty($pid_list)) {
                    $where .= " AND sb_course IN ($pid_list)";
                }
            }

            // Fetch data
            $result = mysqli_query($con, "SELECT * FROM student_bill WHERE sb_associate = '$associate_id' AND sb_type = 'Pay Out' $where ORDER BY sb_id DESC LIMIT $offset, $limit");
            $total_result = mysqli_query($con, "SELECT COUNT(*) FROM student_bill WHERE sb_associate = '$associate_id' AND sb_type = 'Pay Out' $where");
            $total_row = mysqli_fetch_row($total_result);
            $total_records = $total_row[0];
            $total_pages = ceil($total_records / $limit);

            $total_paid = 0;
            $total_payable = 0;
            $unique_pairs = [];

            mysqli_data_seek($result, 0);
            while ($row = mysqli_fetch_assoc($result)) {
                $total_paid += floatval($row['sb_amount']);
                $pair_key = $row['sb_for'] . '_' . $row['sb_course'];
                if (!isset($unique_pairs[$pair_key])) {
                    $unique_pairs[$pair_key] = true;
                    $p_query = mysqli_query($con, "SELECT pAmount FROM product WHERE pid = '" . mysqli_real_escape_string($con, $row['sb_course']) . "'");
                    if ($p_row = mysqli_fetch_assoc($p_query)) {
                        $total_payable += floatval($p_row['pAmount']);
                    }
                }
            }
            mysqli_data_seek($result, 0);
    ?>
    <section id="inner-body">
        <div class="body-content">
            <div class="container-fluid">
                <div class="card mt-3 mb-3">
                    <div class="card-header d-flex justify-content-between align-items-center">
                        <h4 class="text-gray-800">Pay Out For <span style="color:orange"><?= $associate_name ?></span>
                        </h4>
                        <a href="view-franchiee.php#franchisee<?= $associate_id ?>"
                            class="btn btn-warning text-white btn-sm">
                            <i class="fa fa-arrow-left"></i> Back
                        </a>
                    </div>
                    <div class="card-body">
                        <div class="row">
                            <div class="col-md-12 mb-3">
                                <form method="get" action="asso-payout.php">
                                    <input type="hidden" name="user" value="<?= $associate_id;?>">
                                    <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 sb_associate = '$associate_id' AND sb_recycle = '1' $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' $filterStudent
                                                    ORDER BY u.uid ASC
                                                ");
                                                while ($row = mysqli_fetch_assoc($res)) {
                                                    echo '<option value="' . $row['uname'] . '">' . $row['uname'] . '</option>';
                                                }
                                                ?>
                                            </datalist>
                                        </div>

                                        <?php
                                        $courseWhere = "WHERE p.pFranchiee = '" . mysqli_real_escape_string($con, $_GET["user"]) . "'";
                                        if (!empty($student_id)) {
                                            $client_id = mysqli_real_escape_string($con, $student_id);
                                            $courseWhere .= " AND 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(s) -->
                                            <input type="hidden" name="selected_pid" id="selected_pid">

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

                                                // Mapping: course_name => [pid1, pid2, ...]
                                                $coursePidMap = [];
                                                while ($row = mysqli_fetch_assoc($res)) {
                                                    $course_name = htmlspecialchars($row['course_name']);
                                                    $pid = htmlspecialchars($row['pid']);
                                                    if (!isset($coursePidMap[$course_name])) {
                                                        $coursePidMap[$course_name] = [];
                                                    }
                                                    $coursePidMap[$course_name][] = $pid;
                                                }

                                                // Output unique course names in datalist
                                                foreach (array_keys($coursePidMap) as $courseName) {
                                                    echo '<option value="' . $courseName . '"></option>';
                                                }
                                                ?>
                                            </datalist>

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

                                            document.getElementById('course_search').addEventListener('input',
                                                function() {
                                                    const selectedCourse = this.value;
                                                    const pids = coursePidMap[selectedCourse] || [];
                                                    // Join multiple pids by comma if there are more than one
                                                    document.getElementById('selected_pid').value = pids.join(',');
                                                });
                                            </script>
                                        </div>


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

                                        <!-- Date To -->
                                        <div class="col-md-2">
                                            <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-2 d-flex gap-1">
                                            <button type="submit" class="btn btn-primary btn-sm w-100">Search</button>
                                            <a href="asso-payout.php?user=<?= $associate_id;?>"
                                                class="btn btn-secondary btn-sm w-100">Reset</a>
                                        </div>
                                    </div>
                                </form>
                            </div>
                            <?php if(mysqli_num_rows($result)>0){?>
                            <div class="col-md-12">
                                <div class="alert alert-info">
                                    <strong>Total Paid:</strong> ₹<?= number_format($total_paid, 2) ?>
                                    <!--|
                                    <strong>Total Payable:</strong> ₹<?= number_format($total_payable, 2) ?>-->
                                </div>
                            </div>
                            <div class="col-md-12 table-responsive">
                                <table class="table table-bordered">
                                    <thead>
                                        <tr>
                                            <th>#</th>
                                            <th>Invoice</th>
                                            <th>Course</th>
                                            <th>Student Name</th>
                                            <th>College</th>
                                            <th>Amount</th>
                                            <th>Date</th>
                                            <th>Actions</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                        $i = $offset + 1;
                                        while ($row = mysqli_fetch_assoc($result)) {
                                            // Get course name
                                            $course = "";
                                            $product_sql = mysqli_query($con, "SELECT * FROM product WHERE pid = '{$row['sb_course']}'");

                                            if(mysqli_num_rows($product_sql) > 0){
                                                $product_row = mysqli_fetch_assoc($product_sql); // ✅ fetch the row
                                                $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 student name
                                            $client_sql = mysqli_query($con, "SELECT uname FROM users WHERE utype = 'Student' AND uid = '{$row['sb_for']}'");
                                            if ($client_row = mysqli_fetch_assoc($client_sql)) {
                                                $student_name = $client_row["uname"];
                                            }

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

                                            if (mysqli_num_rows($college_sql) > 0) {
                                                $college_row = mysqli_fetch_assoc($college_sql);
                                                $college = $college_row['uname'];
                                            }

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

                                        ?>
                                        <tr>
                                            <td><?= $i++ ?></td>
                                            <td><?= $row['sb_invoice'] ?></td>
                                            <td><?= $course . '( ' . $department . ' )' ?></td>
                                            <td><?= $student_name ?></td>
                                            <td><?= $college ?></td>
                                            <td>₹<?= number_format($row['sb_amount'], 2) ?></td>
                                            <td><?= $row['sb_date'] ?></td>
                                            <td>
                                                <?php if(isset($_GET["Kallol"])){?>
                                                <a href="student-bill.php?client_id=<?= $client_id?>&edit_id=<?= $row['sb_id'] ?>"
                                                    class="btn btn-sm btn-warning text-white"><i
                                                        class="fa fa-pencil"></i> Edit</a>
                                                <a href="student-bill.php?client_id=<?= $client_id?>&del_id=<?= $row['sb_id'] ?>"
                                                    class="btn btn-sm btn-danger"
                                                    onclick="return confirm('Delete this bill?')"><i
                                                        class="fa fa-trash"></i> Delete</a>
                                                <?php }?>
                                                <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>
                                            </td>
                                        </tr>
                                        <?php } ?>
                                    </tbody>
                                </table>
                                <!-- Paginate -->
                                <?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 ?>&user=<?= $associate_id ?>&invoice_search=<?= urlencode($invoice_search) ?>&student_search=<?= urlencode($student_search) ?>&selected_pid=<?= urlencode($selected_pid) ?>&start_date=<?= $start_date ?>&end_date=<?= $end_date ?>">
                                                <?= $p ?>
                                            </a>
                                        </li>
                                        <?php endfor; ?>
                                    </ul>
                                </nav>
                                <?php endif; ?>
                            </div>
                            <?php } else{?>
                            <div class="text-center">No Data Found.</div>
                            <?php } ?>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <?php include "include/footer.php"; ?>
    </section>
    <?php endif; ?>
    <?php endif; ?>

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

</html>