<?php
require("../includes/db_connect.php");
require_once("includes/admin_session_token.php");

$pageno = 1;
$accountId = $_SESSION['adm_accountId'];

// Get the first registered user date
$sql = "SELECT date FROM users ORDER BY date ASC LIMIT 1";
$sth = $conn->prepare($sql);
$sth->execute();
$users = $sth->fetch();

$dateFrom = $users ? (new DateTime($users['date']))->format('d/m/Y') : date("d/m/Y");
$dateTo = date("d/m/Y");

$rowperpage = 50;

// Get total user count
$allcount_query = "SELECT COUNT(*) FROM users";
$allcount_fetch = $conn->prepare($allcount_query);
$allcount_fetch->execute();
$allcount = $allcount_fetch->fetchColumn();

$totalPages = ceil($allcount / $rowperpage);

// Fetch user history with bot information and referrer details
$offset = 0;

$sql = "SELECT u.*, 
            COALESCE(GROUP_CONCAT(pb.type SEPARATOR ', '), 'User has no active bots.') AS user_bots,
            r.accountId AS referrer_id, 
            r.fullname AS referrer_name
        FROM users u
        LEFT JOIN purchased_bots pb ON u.accountId = pb.accountId
        LEFT JOIN users r ON u.referrer = r.accountId  -- Self-join to get referrer info
        GROUP BY u.accountId
        ORDER BY u.date DESC
        LIMIT :offset, :rowperpage";

$sth = $conn->prepare($sql);
$sth->bindParam(':offset', $offset, PDO::PARAM_INT);
$sth->bindParam(':rowperpage', $rowperpage, PDO::PARAM_INT);
$sth->execute();
$history = $sth->fetchAll();

// Debugging: Check returned data
/*if (!$history) {
    var_dump($sth->errorInfo());  // Print SQL error if any
}
*/

$users_history = "";

if (!empty($history)) {
    foreach ($history as $value) {
        $usr_accountId = htmlspecialchars($value['accountId']);
        $fullName = explode(" ", $value['fullname'], 2);
        $first_name = htmlspecialchars($fullName[0]);
        $last_name = isset($fullName[1]) ? htmlspecialchars($fullName[1]) : "";
        $email = htmlspecialchars($value['email']);
        $phone_number = htmlspecialchars($value['phone_number']);
        $country = htmlspecialchars($value['country']);
        $status = htmlspecialchars($value['status']);
        $date = $value['date'];
        $user_bots = htmlspecialchars($value['user_bots']);

        // Referrer details
        $referrer_id = htmlspecialchars($value['referrer_id'] ?? "No referrer");
        $referrer_name = htmlspecialchars($value['referrer_name'] ?? "No referrer");

        // Fetch users referred by this user
        $referrals_sql = "SELECT accountId, fullname FROM users WHERE referrer = :accountId";
        $referrals_stmt = $conn->prepare($referrals_sql);
        $referrals_stmt->bindParam(':accountId', $usr_accountId, PDO::PARAM_STR);
        $referrals_stmt->execute();
        $referred_users = $referrals_stmt->fetchAll(PDO::FETCH_ASSOC);

        $referred_list = "";
        if (!empty($referred_users)) {
            foreach ($referred_users as $referred) {
                $referred_list .= htmlspecialchars($referred['fullname']) . " (ID: " . htmlspecialchars($referred['accountId']) . "), ";
            }
            $referred_list = rtrim($referred_list, ", "); // Remove trailing comma
        } else {
            $referred_list = "No referred users";
        }

        $dis_date = (new DateTime($date))->format('d/m/Y');

        $users_history .= "<tr>
            <td>$usr_accountId</td>
            <td>$first_name</td>
            <td>$last_name</td>
            <td>$email</td>
            <td>$phone_number</td>
            <td>$country</td>
            <td>$referrer_name (ID: $referrer_id)</td>
            <td>$user_bots</td>
            <td>$referred_list</td>
            <td>$dis_date</td>
        </tr>";
    }
} else {
    $users_history = "<tr><td colspan='10' class='tx-color-03 tx-normal text-center'>No data available</td></tr>";
}
?>

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

<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <title>Users Referrals</title>
    <?php require_once("includes/header.php"); ?>

    <div class="content ht-100v pd-0">
        <div class="content-header">
        </div><!-- content-header -->

        <div class="content-body">
            <div class="container pd-x-0">
                <div class="d-sm-flex align-items-center justify-content-between mg-b-20 mg-lg-b-25 mg-xl-b-30">
                    <div>
                        <nav aria-label="breadcrumb">
                            <ol class="breadcrumb breadcrumb-style1 mg-b-10">
                                <li class="breadcrumb-item"><a href="#">Users Referrals</a></li>
                            </ol>
                        </nav>
                        <h4 class="mg-b-0 tx-spacing--1">Total Users (<?php echo ($allcount); ?>)</h4>
                    </div>

                </div>

                <div class="row row-xs">
                    <div class="col-sm-12">
                        <div class="card">
                            <div class="card-body">
                                <div class="row col-xl-12">
                                    <div class="col-md-6 row">
                                        <div class="col-md-6">
                                            <label>From</label>

                                            <input type="text" id="dateFrom" class="form-control"
                                                placeholder="DD/MM/YYYY" value="<?php echo ($dateFrom) ?>">
                                        </div><!-- col -->
                                        <div class="col-md-6">
                                            <label>To</label>

                                            <input type="text" id="dateTo" class="form-control" placeholder="DD/MM/YYYY"
                                                value="<?php echo ($dateTo) ?>">
                                        </div><!-- col -->
                                    </div>
                                    <div class="col-md-4 search-field" style="padding-top: 28px; margin-bottom: 10px">

                                        <input type="text" id="search_field" class="form-control"
                                            placeholder="Search account ID, email..." name="search">
                                        <button id="search_button" class="btn" type="button">
                                            <i class="search-icon" data-feather="search"></i></i>
                                        </button>
                                    </div>
                                </div>

                                <div class="table-responsive user-listing" style="margin-top: 30px">
                                    <table class="table table-centered table-nowrap mb-0">
                                        <thead class="thead-light">
                                            <tr>
                                                <th>Account ID</th>
                                                <th>First Name</th>
                                                <th>Last Name</th>
                                                <th>Email</th>
                                                <th>Mobile</th>
                                                <th>Country</th>
                                                <th>Referred By</th>
                                                <th>User Bots</th>
                                                <th>Referred Users</th>
                                                <th>Joined</th>
                                            </tr>
                                        </thead>
                                        <tbody id="users_history">
                                            <?php echo ($users_history); ?>
                                        </tbody>
                                    </table>
                                    <input type="hidden" id="history_page" value="1">
                                    <input type="hidden" id="total_pages" value="<?php echo ($totalPages); ?>">
                                    <input type="hidden" id="all_history" value="<?php echo ($allcount); ?>">
                                    <input type="hidden" id="loading_status" value="loaded">

                                    <input type="hidden" id="delete_account_id" value="loaded">
                                </div>
                                <div class="col-md-10" style="margin-left: auto; margin-right: auto; margin-top: 20px">
                                    <div class="" style="float: right;">
                                        <nav aria-label="Page navigation example">
                                            <ul id="pagination" class="pagination pagination-circle mg-b-0" <?php if ($totalPages < 2) {
                                                                                                                echo "style='display: none'";
                                                                                                            } ?>>
                                                <li id="prev_history" class="page-item <?php if ($pageno <= 1) {
                                                                                            echo 'disabled';
                                                                                        } ?>">
                                                    <a class="page-link page-link-icon"
                                                        href="javascript:function previous() { return false; }"><i
                                                            data-feather="chevron-left"></i></a>
                                                </li>
                                                <li id="next_history" class="page-item <?php if ($pageno >= $totalPages) {
                                                                                            echo 'disabled';
                                                                                        } ?>">
                                                    <a class="page-link page-link-icon"
                                                        href="javascript:function next() { return false; }"><i
                                                            data-feather="chevron-right"></i></a>
                                                </li>
                                            </ul>
                                        </nav>
                                    </div><!-- df-example -->
                                </div>
                            </div>
                        </div>
                    </div>
                </div><!-- row -->
            </div><!-- container -->
        </div>
    </div>

    <?php require_once("includes/footer.php"); ?>
    <script type="text/javascript">
    $(document).ready(function() {
        var classes = $("#mn-users-referrals").prop('class');
        $("#mn-users-referrals").prop('class', classes + ' active pcoded-trigger');
    });
    </script>
    <script src="../js/admin/users-referrals.js"></script>

    </body>

</html>