<?php
include('dbconnect.php');
include('function.php');

// Start session if not already started
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Get the selectedStatus from session if set
$selectedStatus = isset($_SESSION['selectedStatusnew']) ? $_SESSION['selectedStatusnew'] : '';

// Query to fetch data
$cond = "";
if($_SESSION['role'] == 'STANDARD'){
    $cond = "AND tbl_lead_user_assigned_user_id = " . $_SESSION['user_id'] . " ";
}

// Get project IDs from session if they exist
$projectFilter = "";
if (isset($_SESSION['selected_projects']) && !empty($_SESSION['selected_projects'])) {
    // Check if the selected_projects is a JSON string and decode it
    $projectIds = is_string($_SESSION['selected_projects']) 
        ? json_decode($_SESSION['selected_projects'], true) 
        : $_SESSION['selected_projects'];
    
    // Make sure we have an array
    if (is_array($projectIds) && !empty($projectIds)) {
        $projectIdsStr = implode(',', array_map('intval', $projectIds));
        $projectFilter = "AND tbl_lead_user_id IN (
            SELECT tbl_lead_user_id 
            FROM tbl_lead_user_projects 
            WHERE tbl_lead_user_prj_id IN ($projectIdsStr)
        )";
    }
}

// Get project IDs from session if they exist
$statusprojectFilter = "tbl_lead_user_status IN('New')";
if (isset($_SESSION['selected_status_projects']) && !empty($_SESSION['selected_status_projects'])) {
    // Check if the selected_projects is a JSON string and decode it
    $projectIds = is_string($_SESSION['selected_status_projects']) 
        ? json_decode($_SESSION['selected_status_projects'], true) 
        : $_SESSION['selected_status_projects'];
    
    // Make sure we have an array
    if (is_array($projectIds) && !empty($projectIds)) {
        $projectIdsStr = implode(',', array_map('intval', $projectIds));
        $statusprojectFilter = "tbl_lead_user_status IN (
            SELECT tbl_followup_status_name 
            FROM tbl_followup_status 
            WHERE tbl_followup_status_id IN ($projectIdsStr)
        )";
    }
}

// Get assigned user IDs from session if they exist
$userFilter = "";
if (isset($_SESSION['assigned_user']) && !empty($_SESSION['assigned_user'])) {
    // Check if the selected_projects is a JSON string and decode it
    $userIds = is_string($_SESSION['assigned_user']) 
        ? json_decode($_SESSION['assigned_user'], true) 
        : $_SESSION['assigned_user'];
    
    // Make sure we have an array
    if (is_array($userIds) && !empty($userIds)) {
        $userIdsStr = implode(',', array_map('intval', $userIds));
        $userFilter = "AND tbl_lead_user_assigned_user_id IN ($userIdsStr)";
    }
}

// Get Date from session if they exist
$dateFilter="";
if (
    (isset($_SESSION['date_option']) && !empty($_SESSION['date_option'])) || 
    (isset($_SESSION['date_start']) && !empty($_SESSION['date_start'])) || 
    (isset($_SESSION['date_end']) && !empty($_SESSION['date_end']))
) {
    // your code here
    if (!empty($_SESSION['date_option'])) {
        $today = date('Y-m-d');
        
        switch ($_SESSION['date_option']) {
            case 'today':
                $dateFilter = " AND DATE(tbl_lead_user_regdate) = '$today'";
                break;
    
            case 'yesterday':
                $yesterday = date('Y-m-d', strtotime('-1 day'));
                $dateFilter = " AND DATE(tbl_lead_user_regdate) = '$yesterday'";
                break;
    
            case 'this_week':
                $startOfWeek = date('Y-m-d', strtotime('monday this week'));
                $endOfWeek = date('Y-m-d', strtotime('sunday this week'));
                $dateFilter = " AND DATE(tbl_lead_user_regdate) BETWEEN '$startOfWeek' AND '$endOfWeek'";
                break;
    
            case 'this_month':
                $startOfMonth = date('Y-m-01');
                $endOfMonth = date('Y-m-t');
                $dateFilter = " AND DATE(tbl_lead_user_regdate) BETWEEN '$startOfMonth' AND '$endOfMonth'";
                break;
        }
    }
    
    if (!empty($_SESSION['date_start']) && !empty($_SESSION['date_end'])) {
        $startDate = $_SESSION['date_start'];
        $endDate = $_SESSION['date_end'];
        
        $dateFilter = " AND DATE(tbl_lead_user_regdate) BETWEEN '$startDate' AND '$endDate'";
    }

}


// $sql = "SELECT distinct tbl_lead_user_mobile, tbl_lead_user_profile_id,tbl_lead_user_id,tbl_lead_user_source_id,tbl_lead_user_city_id,tbl_lead_user_assigned_user_id,tbl_lead_user_status,
//         tbl_lead_user_intent,tbl_lead_user_regdate,tbl_lead_user_assigned_date,tbl_lead_user_cdate FROM tbl_lead_user 
//         WHERE tbl_lead_user_status='New' 
//         $cond 
//         $projectFilter $userFilter $dateFilter
//         ORDER BY tbl_lead_user_cdate DESC LIMIT 1000";
$sql= "SELECT 
    tbl_lead_user_mobile, 
    tbl_lead_user_profile_id,
    tbl_lead_user_id,
    tbl_lead_user_source_id,
    tbl_lead_user_city_id,
    tbl_lead_user_assigned_user_id,
    tbl_lead_user_status, 
    tbl_lead_user_intent,
    tbl_lead_user_regdate,
    tbl_lead_user_assigned_date,
    tbl_lead_user_cdate
FROM tbl_lead_user
WHERE $statusprojectFilter $cond $projectFilter $userFilter $dateFilter
  AND tbl_lead_user_cdate = (
      SELECT MAX(t2.tbl_lead_user_cdate)
      FROM tbl_lead_user t2
      WHERE t2.tbl_lead_user_mobile = tbl_lead_user.tbl_lead_user_mobile
        AND t2.tbl_lead_user_status = 'New' $cond 
  )
ORDER BY tbl_lead_user_cdate DESC
";

$result = $conn->query($sql);
$data = [];

if ($result->num_rows > 0) {
    // Fetch the data and store it in an array
    $sl = 1;
    while ($row = $result->fetch_assoc()) {
        $sql_profile = "SELECT * FROM tbl_lead_user_profile 
                       WHERE tbl_lead_user_profile_flag = 0 
                       AND tbl_lead_user_profile_id = '".$row['tbl_lead_user_profile_id']."'";
        $rprofile = $conn->query($sql_profile);

        // Fetch the profile data
        if ($rprofile && $profile = $rprofile->fetch_assoc()) {
            $project_name = get_project_name($row['tbl_lead_user_id']);
            $source_name = get_source_name($row['tbl_lead_user_source_id']);
            $city_name = get_city_name($row['tbl_lead_user_city_id']);
            $as_u_name = get_assigned_user_name($row['tbl_lead_user_assigned_user_id']);
            
            if ($selectedStatus == 'reassignleads') {
                $data[] = [
                '',
                $sl,
                $profile['tbl_lead_user_profile_name'].'-'.$profile['tbl_lead_user_profile_dup_count'],
                $profile['tbl_lead_user_profile_mobile'],
                $project_name,
                $row['tbl_lead_user_status'],
                $row['tbl_lead_user_intent'],
                $source_name,
                $as_u_name,
                $row['tbl_lead_user_regdate'],
                $row['tbl_lead_user_assigned_date'],
                $city_name,
                //'-', // svUser
                //'Varun', // markedBy
               // $row['tbl_lead_user_regdate'],
                //'-', // leasAccessbility
                //'-', // access
                '-', // lastNote
                $row['tbl_lead_user_id'],
            ];
            $sl++;
                
            }
            else {
                $data[] = [
                $sl,
                $profile['tbl_lead_user_profile_name'].'-'.$profile['tbl_lead_user_profile_dup_count'],
                $profile['tbl_lead_user_profile_mobile'],
                $project_name,
                $row['tbl_lead_user_status'],
                $row['tbl_lead_user_intent'],
                $source_name,
                $as_u_name,
                $row['tbl_lead_user_regdate'],
                $row['tbl_lead_user_assigned_date'],
                $city_name,
                //'-', // svUser
                //'Varun', // markedBy
               // $row['tbl_lead_user_regdate'],
               // '-', // leasAccessbility
               // '-', // access
                '-', // lastNote
                $row['tbl_lead_user_id'],
            ];
            $sl++;
            }
            
        }
    }
}
// Unset the session variable after using it
//unset($_SESSION['selectedStatusnew']);
// Close the database connection
$conn->close();

// Return the data as JSON
echo json_encode($data);
?>
