<?php
session_start();

require("../includes/db_connect.php");

if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    if ((!empty($_POST['page'])) && (!empty($_POST['search_id'])) && (!empty($_POST['dateFrom'])) && (!empty($_POST['dateTo']))) {

        if ($conn) {
            try {

                $page = $_POST['page'];
                $search_id = $_POST['search_id'];
                $dateFrom = convertDate($_POST['dateFrom']);
                $dateTo = convertDate($_POST['dateTo']);

                $no_of_records_per_page = 50;
                $offset = ($page - 1) * $no_of_records_per_page;

                // Total users count
                $allcount_query = "SELECT * FROM users WHERE (DATE(date) BETWEEN ? AND ?) ORDER BY date DESC";
                $allcount_fetch = $conn->prepare($allcount_query, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
                $allcount_fetch->execute([$dateFrom, $dateTo]);

                // Calculate total pages
                $allcount = $allcount_fetch->rowCount();
                $totalPages = ceil($allcount / $no_of_records_per_page);

                // Fetch users based on search parameters
                if ($search_id != "default") {
                    // Case when `search_id` is provided
                    $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
                        WHERE :searchId IN (u.accountId, u.email, u.fullname)  -- Search by accountId, email, or fullname
                        GROUP BY u.accountId
                        ORDER BY u.date DESC
                        LIMIT :offset, :rowperpage";
                
                    $sth = $conn->prepare($sql);
                    $sth->bindParam(':searchId', $search_id, PDO::PARAM_STR);
                    $sth->bindParam(':offset', $offset, PDO::PARAM_INT);
                    $sth->bindParam(':rowperpage', $no_of_records_per_page, PDO::PARAM_INT);
                    $sth->execute();
                } else {
                    // Case when `search_id` is not provided, search by date range
                    $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
                        WHERE (DATE(u.date) BETWEEN :dateFrom AND :dateTo)
                        GROUP BY u.accountId
                        ORDER BY u.date DESC
                        LIMIT :offset, :rowperpage";
                
                    $sth = $conn->prepare($sql);
                    $sth->bindParam(':dateFrom', $dateFrom, PDO::PARAM_STR);
                    $sth->bindParam(':dateTo', $dateTo, PDO::PARAM_STR);
                    $sth->bindParam(':offset', $offset, PDO::PARAM_INT);
                    $sth->bindParam(':rowperpage', $no_of_records_per_page, 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 (count($history) > 0) {
                    foreach ($history as $key => $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>";
                }

                    $response = array(
                        'history' => $users_history,
                        'total_pages' => $totalPages,
                    );

                    echo json_encode($response);
                } else {
                    echo "empty";
                }
            } catch (PDOException $e) {
                die('An unexpected error occurred. Connection failed');
            }
        } else {
            die('An unexpected error occurred. Connection failed.');
        }
    } else {
        die('Invalid user information.');
    }
} else {
    die('Invalid request.');
}


function convertDate($date)
{
    $dt = str_replace('/', '-', $date);

    return date('Y-m-d', strtotime($dt));
}
