<?php 
ob_start();
include "../../db.php";
date_default_timezone_set('Asia/Kolkata');
session_start();

$currentCoreDate = date("Y-m-d");

if (isset($_POST['start'])) {
    $start = (int) $_POST['start']; // Ensure integer for security

    // Get duplicate `st_ph` values
    $dup_sql = "SELECT st_ph FROM student_details WHERE flag_status = 'A' GROUP BY st_ph HAVING COUNT(*) > 1 ORDER BY st_no ASC LIMIT $start, 100";
    $dup_res = mysqli_query($con, $dup_sql);
    
    $phone_numbers = [];
    while ($row = mysqli_fetch_assoc($dup_res)) {
        $phone_numbers[] = "'" . mysqli_real_escape_string($con, $row['st_ph']) . "'";
    }

    if (!empty($phone_numbers)) {
        $phone_list = implode(",", $phone_numbers);

        // Fetch all students with duplicate `st_ph`
        $sql = "SELECT * FROM student_details WHERE st_ph IN ($phone_list) AND flag_status = 'A' ORDER BY st_ph, st_no DESC";
        $result = mysqli_query($con, $sql);

        $students = [];
        $student_ids = [];
        $user_ids = [];

        while ($rows = mysqli_fetch_assoc($result)) {
            $students[$rows['st_ph']][] = $rows; // Group by `st_ph`
            $student_ids[] = "'" . $rows['st_no'] . "'";
            if (!empty($rows['st_by'])) {
                $user_ids[] = "'" . $rows['st_by'] . "'";
            }
        }

        // Fetch admin details
        $admin_details = [];
        if (!empty($user_ids)) {
            $user_list = implode(",", array_unique($user_ids));
            $adm_sql = "SELECT uid, uname FROM users WHERE uid IN ($user_list)";
            $adm_run = mysqli_query($con, $adm_sql);
            while ($adm_rows = mysqli_fetch_assoc($adm_run)) {
                $admin_details[$adm_rows['uid']] = "<b style='color:#3333ff;'><i class='fa fa-user'></i> {$adm_rows['uname']}</b>";
            }
        }

        // Fetch status names
        $status_names = [];
        if (!empty($_SESSION['uref'])) {
            $st_show_sql = "SELECT s_id, s_name FROM status WHERE s_by = '{$_SESSION['uref']}'";
            $st_show_run = mysqli_query($con, $st_show_sql);
            while ($st_show_row = mysqli_fetch_assoc($st_show_run)) {
                $status_names[$st_show_row['s_id']] = $st_show_row['s_name'];
            }
        }

        // Fetch remarks
        $remarks = [];
        $admin_remarks = [];
        if (!empty($student_ids)) {
            $student_list = implode(",", array_unique($student_ids));

            // User remarks
            $r_sql = "SELECT r_sid, remark, uploadDate FROM remark WHERE r_sid IN ($student_list) ORDER BY r_id DESC";
            $r_run = mysqli_query($con, $r_sql);
            while ($r_row = mysqli_fetch_assoc($r_run)) {
                if (!isset($remarks[$r_row['r_sid']])) {
                    $remarks[$r_row['r_sid']] = "<b style='color:#3333ff;'><i class='fas fa-comments'></i> {$r_row['remark']} </b> ({$r_row['uploadDate']})";
                }
            }

            // Admin remarks
            $ar_sql = "SELECT r_sid, remark, uploadDate FROM admin_remark WHERE r_sid IN ($student_list) ORDER BY r_id DESC";
            $ar_run = mysqli_query($con, $ar_sql);
            while ($ar_row = mysqli_fetch_assoc($ar_run)) {
                if (!isset($admin_remarks[$ar_row['r_sid']])) {
                    $admin_remarks[$ar_row['r_sid']] = "A<i class='fas fa-comments'></i> {$ar_row['remark']} ({$ar_row['uploadDate']})";
                }
            }
        }

        // Status dropdown
        $st_loop = "<option selected disabled>select</option>";
        foreach ($status_names as $s_id => $s_name) {
            $st_loop .= "<option value='{$s_id}'>{$s_name}</option>";
        }

        // Output results
        foreach ($students as $st_ph => $student_group) {
            echo "<li class='clearfix'>
                    <div class='single-dis-admin mb-2' style='border:1px solid #ccc;padding:10px;border-radius:5px;background:#f9f9f9;'>
                        <b style='color:blue;'><i class='fa fa-phone fa-rotate-90'></i> {$st_ph} (Duplicate Entries)</b>
                        <hr>";

            foreach ($student_group as $rows) {
                $admin = $admin_details[$rows['st_by']] ?? "<b style='color:red;'><i class='fa fa-user'></i> wait for an agent</b>";
                $st_show = $status_names[$rows['st_status']] ?? "Global Data";
                $latest_remark = $remarks[$rows['st_no']] ?? "";
                $last_admin_remark = $admin_remarks[$rows['st_no']] ?? "";

                echo "<div class='row'>
                        <div class='col-md-9 col-xs-7 second-item'>
                            <i class='fa fa-user-circle' style='color:orange;font-size:18px;'></i>
                            <b style='color:black;font-size:17px;'>{$rows['st_name']}</b> (<b>{$st_show}</b>)
                            <br> {$admin}
                            <br>
                            <i class='fas fa-map-marker-alt'></i> {$rows['st_address']}
                            <br>
                            <b>S<i class='far fa-clock'></i>: </b> {$rows['uploadDT']}
                            <br> {$latest_remark} <br> {$last_admin_remark}
                        </div>
                        <div class='col-md-3 col-xs-5 second-item'>
                            <button class='m-2 btn btn-success btn-sm MoveToRecycleBinBtn' id='{$rows['st_no']}'
                                title='Move to RecycleBin' style='width:50px;'>
                                <i class='fa fa-trash' aria-hidden='true' style='font-size:15px;'></i>
                            </button>
                        </div>
                        <div class='col-md-12 second-item'>
                            <div class='about-display mb-2'>
                                <label for=''>About :</label> 
                                " . implode(', ', array_filter([
                                    $rows['st_about'], $rows['st_about2'], $rows['st_about3'], 
                                    $rows['st_about4'], $rows['st_about5'], $rows['st_about6'], 
                                    $rows['st_about7'], $rows['st_about8']
                                ])) . "
                            </div>
                        </div>
                    </div>
                    <hr>";

}

echo "</div>
</li>";
}
}
}
?>



<script>
$(document).ready(function() {

    $('.MoveToRecycleBinBtn').on('click', function() {

        var sid = $(this).attr('id');

        $.ajax({
            url: "action-Ajax.php",
            method: "POST",
            data: {
                action: 'MoveToRecycleBin',
                sid: sid
            },
            cache: false,
            success: function(result) {
                $("#status-msg-" + sid).html(result);
            }
        });


    });
});
</script>