.-.-.-.-.-

<?php

require_once __DIR__ . '/../Classes/Database.php';
require_once __DIR__ . '/../Classes/Task.php';

class TaskService
{
    private $db;

public function __construct()
    {
        $this->db = new Database();
    }

public function addTask($task)
    {
        $this->db->query('INSERT INTO tasks (schedule_id, service_id, frequency, dates, added_by, updated_by, created_at, updated_at, finished, assigned_to, is_assigned, isPublic, accepted, not_accepted_users, assignedCleaneremail, is_assigned_to_cleaner) VALUES (:schedule_id, :service_id, :frequency, :dates, :added_by, :updated_by, :created_at, :updated_at, :finished, :assigned_to, :is_assigned, :isPublic, :accepted, :not_accepted_users, :assignedCleaneremail, :is_assigned_to_cleaner)');

// Bind values
        $this->db->bind(':schedule_id', $task->getScheduleId());
        $this->db->bind(':service_id', $task->getServiceId());
        $this->db->bind(':frequency', $task->getFrequency());
        $this->db->bind(':dates', $task->getDates());
        $this->db->bind(':added_by', $task->getAddedBy());
        $this->db->bind(':updated_by', $task->getUpdatedBy());
        $this->db->bind(':created_at', $task->getCreatedAt());
        $this->db->bind(':updated_at', $task->getUpdatedAt());
        $this->db->bind(':finished', $task->getFinished());
        $this->db->bind(':assigned_to', $task->getAssignedTo());
        $this->db->bind(':is_assigned', $task->getIsAssigned());
        $this->db->bind(':isPublic', $task->getIsPublic());
        $this->db->bind(':accepted', $task->getAccepted());
        $this->db->bind(':not_accepted_users', $task->getNotAcceptedUsers());
        $this->db->bind(':assignedCleaneremail', $task->getAssignedCleaneremail());
        $this->db->bind(':is_assigned_to_cleaner', $task->getIsAssignedToCleaner());

// Execute
        if ($this->db->execute()) {
            return true;
        } else {
            return false;
        }

}

public function getAllTasks($filters = [])
    {
        $query = '
        SELECT 
            tasks.*, 
            services.name AS service_name,
            services.region AS region,
            schedules.schedule_name,
            CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
            CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
            CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
        FROM tasks 
        LEFT JOIN schedules ON tasks.schedule_id = schedules.id 
        LEFT JOIN services ON tasks.service_id = services.id
        LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
        LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
        LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
        WHERE 1=1
    ';

$params = [];

// Apply filters
        if (!empty($filters['service_name'])) {
            $query .= ' AND services.name LIKE :service_name';
            $params[':service_name'] = '%' . $filters['service_name'] . '%';
        }

if (!empty($filters['region'])) {
            $query .= ' AND services.region LIKE :region';
            $params[':region'] = '%' . $filters['region'] . '%';
        }

if (!empty($filters['frequency'])) {
            $query .= ' AND tasks.frequency LIKE :frequency';
            $params[':frequency'] = '%' . $filters['frequency'] . '%';
        }

if (isset($filters['public'])) {
            $query .= ' AND tasks.isPublic = :public';
            $params[':public'] = $filters['public'];
        }

if (isset($filters['finished'])) {
            $query .= ' AND tasks.finished = :finished';
            $params[':finished'] = $filters['finished'];
        }

if (isset($filters['assigned'])) {
            $query .= ' AND tasks.is_assigned = :assigned';
            $params[':assigned'] = $filters['assigned'];
        }

$this->db->query($query);

// Bind parameters
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }

return $this->db->resultSet();
    }

public function getAllUnassignedTasks($filters = [])
    {
        $query = '
        SELECT 
            tasks.*, 
            services.name AS service_name,
            services.region AS region,
            schedules.schedule_name,
            CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
            CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
            CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
        FROM tasks 
        LEFT JOIN schedules ON tasks.schedule_id = schedules.id 
        LEFT JOIN services ON tasks.service_id = services.id
        LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
        LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
        LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
        WHERE tasks.is_assigned = 0';

$params = [];

if (!empty($filters['region'])) {
            $query .= ' AND services.region LIKE :region';
            $params[':region'] = '%' . $filters['region'] . '%';
        }

if (!empty($filters['frequency'])) {
            $query .= ' AND tasks.frequency LIKE :frequency';
            $params[':frequency'] = '%' . $filters['frequency'] . '%';
        }

if (isset($filters['public'])) {
            $query .= ' AND tasks.isPublic = :public';
            $params[':public'] = $filters['public'];
        }

$this->db->query($query);

// Bind parameters
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }

$tasks = $this->db->resultSet();

// Parse not_accepted_users into an array of user IDs
        foreach ($tasks as &$task) {
            if (!empty($task->not_accepted_users)) {
                $notAcceptedUsers = explode(',', $task->not_accepted_users);
                $task->not_accepted_users = $notAcceptedUsers;
            } else {
                $task->not_accepted_users = []; // Initialize as empty array if no not_accepted_users
            }
        }

return $tasks;
    }

public function getAllOngoingTasks($filters = [])
    {
        $query = '
        SELECT 
            tasks.*, 
            services.name AS service_name,
            services.region AS region,
            schedules.schedule_name,
            CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
            CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
            CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
        FROM tasks 
        LEFT JOIN services ON tasks.service_id = services.id
        LEFT JOIN schedules ON tasks.schedule_id = schedules.id 
        LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
        LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
        LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
        WHERE is_assigned = 1 AND accepted = 1 AND finished = 0
    ';

$params = [];

if (!empty($filters['region'])) {
            $query .= ' AND services.region LIKE :region';
            $params[':region'] = '%' . $filters['region'] . '%';
        }

if (!empty($filters['frequency'])) {
            $query .= ' AND tasks.frequency LIKE :frequency';
            $params[':frequency'] = '%' . $filters['frequency'] . '%';
        }

$this->db->query($query);

// Bind parameters
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }

return $this->db->resultSet();
    }

public function getAllUnacceptedTasks($filters = [])
    {
        $query = '
        SELECT 
            tasks.*, 
            services.name AS service_name,
            services.region AS region,
            schedules.schedule_name,
            CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
            CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
            CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
        FROM tasks 
        LEFT JOIN schedules ON tasks.schedule_id = schedules.id 
        LEFT JOIN services ON tasks.service_id = services.id
        LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
        LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
        LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
        WHERE tasks.is_assigned = 1 
          AND tasks.accepted = 0 
          AND tasks.finished = 0';

$params = [];

if (!empty($filters['region'])) {
            $query .= ' AND services.region LIKE :region';
            $params[':region'] = '%' . $filters['region'] . '%';
        }

if (!empty($filters['frequency'])) {
            $query .= ' AND tasks.frequency LIKE :frequency';
            $params[':frequency'] = '%' . $filters['frequency'] . '%';
        }

$this->db->query($query);

// Bind parameters
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }

return $this->db->resultSet();
    }

public function getTaskByIdAsObject($taskId)
    {
        $query = '
        SELECT 
            tasks.*, 
            services.name AS service_name,
            services.region AS region
        FROM tasks 
        LEFT JOIN services ON tasks.service_id = services.id
        WHERE tasks.id = :id
    ';

$this->db->query($query);
        $this->db->bind(':id', $taskId);
        $taskData = $this->db->single();

if ($taskData) {
            return new Task(
                $taskData->id,
                $taskData->schedule_id,
                $taskData->service_id, // Ensure to use service_id instead of service_name
                $taskData->service_name,
                $taskData->region,
                $taskData->frequency,
                $taskData->dates,
                $taskData->added_by,
                $taskData->updated_by,
                $taskData->created_at,
                $taskData->updated_at,
                $taskData->finished,
                $taskData->assigned_to,
                $taskData->is_assigned,
                $taskData->isPublic,
                $taskData->accepted,
                $taskData->not_accepted_users
            );
        } else {
            return null;
        }
    }

public function getTaskById($taskId)
    {
        $this->db->query('SELECT tasks.*, 
                             services.name AS service_name,
                             services.region AS region,
                             schedules.schedule_name,
                             CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
                             CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
                             CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
                      FROM tasks 
                      LEFT JOIN services ON tasks.service_id = services.id
                      LEFT JOIN schedules ON tasks.schedule_id = schedules.id 
                      LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
                      LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
                      LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
                      WHERE tasks.id = :id');
        $this->db->bind(':id', $taskId);
        return $this->db->single();
    }

public function getSubtasksByTaskId($taskId)
    {
        $query = '
        SELECT 
            s.*, 
            t.service_id, 
            srv.name as service_name, 
            srv.region as service_region
        FROM subtasks s
        LEFT JOIN tasks t ON s.task_id = t.id
        LEFT JOIN services srv ON t.service_id = srv.id
        WHERE s.task_id = :task_id
    ';

$this->db->query($query);
        $this->db->bind(':task_id', $taskId);
        return $this->db->resultSet();
    }

public function getTasksByScheduleId($schedule_id)
    {
        $this->db->query('SELECT tasks.*, 
                        services.name AS service_name,
                        services.region AS region,
                        CONCAT(created_by_user.first_name, " ", created_by_user.last_name) AS created_by_name,
                        CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
                        CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
                      FROM tasks 
                      LEFT JOIN services ON tasks.service_id = services.id
                      LEFT JOIN users AS created_by_user ON tasks.added_by = created_by_user.id
                      LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
                      LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
                      WHERE schedule_id = :schedule_id');
        $this->db->bind(':schedule_id', $schedule_id);
        return $this->db->resultSet();
    }

public function getUnassignedTasks()
    {
        $this->db->query('SELECT tasks.*, 
                        services.name AS service_name,
                        services.region AS region
                      FROM tasks 
                      LEFT JOIN services ON tasks.service_id = services.id
                      WHERE tasks.is_assigned = 0 AND tasks.isPublic = 1');
        return $this->db->resultSet();
    }

public function getPublicTasks()
    {
        $this->db->query('SELECT tasks.*, 
                        services.name AS service_name,
                        services.region AS region
                      FROM tasks 
                      LEFT JOIN services ON tasks.service_id = services.id
                      WHERE tasks.isPublic = 1');
        return $this->db->resultSet();
    }

public function assignTask($task)
    {
        $this->db->query('UPDATE tasks SET assigned_to = :assigned_to, is_assigned = :is_assigned, isPublic = 0, accepted = 1 WHERE id = :id');
        $this->db->bind(':assigned_to', $task->getAssignedTo());
        $this->db->bind(':is_assigned', $task->getIsAssigned());
        $this->db->bind(':id', $task->getId());

if ($this->db->execute()) {
            return true;
        } else {
            return false;
        }
    }

public function rejectTask($task)
    {
        $this->db->query('UPDATE tasks SET assigned_to = null, is_assigned = 0, isPublic = 0,not_accepted_users = :not_accepted_users WHERE id = :id');
        $this->db->bind(':not_accepted_users', $task->getNotAcceptedUsers());
        $this->db->bind(':id', $task->getId());

if ($this->db->execute()) {
            return true;
        } else {
            return false;
        }
    }

public function getTasksByAssignedId($companyId)
    {
        $this->db->query('SELECT tasks.*, 
                        services.name AS service_name,
                        services.region AS region
                      FROM tasks 
                      LEFT JOIN services ON tasks.service_id = services.id
                      WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 1');
        $this->db->bind(':assigned_to', $companyId);
        return $this->db->resultSet();
    }

public function getTasksByAssignedIdNotAccepted($companyId)
    {
        $this->db->query('SELECT tasks.*, 
                        services.name AS service_name,
                        services.region AS region
                      FROM tasks 
                      LEFT JOIN services ON tasks.service_id = services.id
                      WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 0');
        $this->db->bind(':assigned_to', $companyId);
        return $this->db->resultSet();
    }

public function assignTaskToCompany($taskId, $companyId)
    {
        $this->db->query('UPDATE tasks SET assigned_to = :assigned_to,is_assigned = 1, isPublic = 0 WHERE id = :id');
        $this->db->bind(':assigned_to', $companyId);
        $this->db->bind(':id', $taskId);
        return $this->db->execute();
    }

public function makeTaskPublic(int $taskId)
    {
        $this->db->query('UPDATE tasks SET isPublic = 1 WHERE id = :id');
        $this->db->bind(':id', $taskId);
        return $this->db->execute();
    }

public function deleteTask(string $taskId)
    {
        $this->db->query('DELETE FROM tasks WHERE id = :id');
        $this->db->bind(':id', $taskId);
        return $this->db->execute();
    }

public function deleteTaskWithSubtasks(string $taskId)
    {
        try {
            $this->db->beginTransaction();
            error_log("Executing query: DELETE FROM subtasks WHERE task_id = $taskId");
            // Step 1: Delete subtasks
            $this->db->query('DELETE FROM subtasks WHERE task_id = :task_id');
            $this->db->bind(':task_id', $taskId);
            $this->db->execute();

// Step 2: Delete task

error_log("Executing query: DELETE FROM tasks WHERE id = $taskId");
            $this->db->query('DELETE FROM tasks WHERE id = :id');
            $this->db->bind(':id', $taskId);
            $result = $this->db->execute();

$this->db->commit();

return true; // Return true to indicate success
        } catch (PDOException $e) {
            $this->db->rollBack();
            error_log("Error deleting task with subtasks. Task ID: $taskId. Error: " . $e->getMessage());
            return false; // Return false to indicate failure
        }
    }

public function getAllCompanies()
    {
        $this->db->query('SELECT id, first_name,last_name FROM users where role = "subcontractor"');
        return $this->db->resultSet();
    }

public function getServiceNameByTaskId(int $task_id)
    {
        $this->db->query('SELECT service_name FROM tasks WHERE id = :id');
        $this->db->bind(':id', $task_id);
        return $this->db->single();
    }

public function getTasksWithUnassignedSubtasks($userId)
    {
        $this->db->query('
        SELECT DISTINCT t.* 
        FROM tasks t
        JOIN subtasks s ON t.id = s.task_id
        WHERE t.assigned_to = :user_id
          AND s.assigned = FALSE
    ');
        $this->db->bind(':user_id', $userId);
        return $this->db->resultSet();
    }

public function isTaskAssigned($subtask_id)
    {
        // SQL query to join tasks and subtasks tables
        $this->db->query('
        SELECT t.is_assigned 
        FROM tasks t
        JOIN subtasks s ON t.id = s.task_id
        WHERE s.id = :subtask_id
    ');

// Bind the subtask ID to the query
        $this->db->bind(':subtask_id', $subtask_id);

// Execute the query and fetch the result
        $is_assigned = $this->db->single();

// Return the is_assigned property if it exists
        return isset($is_assigned->is_assigned) ? $is_assigned->is_assigned : false;
    }

//get all subtasks

public function getAllSubtasks()
    {
        $this->db->query('SELECT * FROM subtasks');
        return $this->db->resultSet();
    }

//get all subtasks group by subtask_date

// TaskService.php

public function getAllSubtasksForSubContractorsGroupByDate($SubContractorId)
    {
        try {
            // Adjusted SQL query to join subtasks with tasks and services
            $this->db->query('
            SELECT 
                subtasks.subtask_date, 
                subtasks.id, 
                services.id AS service_id,
                services.name AS service_name,
                subtasks.subtask_time, 
                subtasks.assigned, 
                subtasks.updated_at 
            FROM subtasks 
            INNER JOIN tasks ON subtasks.task_id = tasks.id 
            INNER JOIN services ON tasks.service_id = services.id
            WHERE tasks.assigned_to = :assigned_to 
              AND tasks.accepted = 1 
              AND subtasks.assigned = 0 
            ORDER BY subtasks.subtask_date
        ');

$this->db->bind(':assigned_to', $SubContractorId);
            $subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

return $groupedSubtasks;
        } catch (PDOException $e) {
            // Handle database errors appropriately (log, throw further, etc.)
            error_log('Database error: ' . $e->getMessage());
            return []; // Return empty array or handle error as needed
        }
    }

public function getAllSubtasksForSubContractorsGroupByDateassigned($SubContractorId)
    {
        try {
            // Adjusted SQL query to join subtasks with tasks and services based on assigned_to
            $query = "
            SELECT 
                subtasks.subtask_date, 
                subtasks.id, 
                serv.id as service_id,
                serv.name as service_name,
                serv.region as region,
                subtasks.subtask_time, 
                subtasks.assigned, 
                subtasks.updated_at, 
                subtasks.completed,
                subtasks.ignored
            FROM subtasks 
            INNER JOIN tasks ON subtasks.task_id = tasks.id 
            LEFT JOIN services serv ON tasks.service_id = serv.id
            WHERE tasks.assigned_to = :assigned_to 
              AND tasks.accepted = 1 
              AND subtasks.assigned = 1
            ORDER BY subtasks.subtask_date
        ";

$this->db->query($query);
            $this->db->bind(':assigned_to', $SubContractorId);
            $subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

// Store subtask details in the grouped array
                if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_id' => $subtask->service_id,
                    'service_name' => $subtask->service_name,
                    'region' => $subtask->region,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date,
                    'completed' => $subtask->completed,
                    'ignored' => $subtask->ignored
                ];
            }

public function getAllSubtasksGroupByDate($startDate = null, $endDate = null, $subcontractorId = null, $serviceLocationId = null)
    {
        try {
            // Get the current date and calculate the date range if not provided
            if (!$startDate || !$endDate) {
                $currentDate = date('Y-m-d');
                $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
                $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate)));
                $startDate = $startDate ?: $oneWeekBefore;
                $endDate = $endDate ?: $oneWeekAfter;

if ($endDate < $startDate) {
                    //set end date same as start date
                    $endDate = $startDate;
                }
            }

// Adjusted SQL query to include service_name, region, assigned_to user's details, and assignedCleaneremail from tasks table
            $sql = '
        SELECT 
            subtasks.subtask_date, 
            subtasks.id, 
            subtasks.subtask_time, 
            subtasks.assigned, 
            subtasks.updated_at,
            subtasks.completed,
            subtasks.ignored,
            tasks.service_id,
            srv.name AS service_name,
            srv.region AS region,
            tasks.assignedCleaneremail,
            assignedUser.first_name AS assigned_user_first_name,
            assignedUser.last_name AS assigned_user_last_name,
            assignedUser.email AS assigned_user_email
        FROM subtasks 
        INNER JOIN tasks ON subtasks.task_id = tasks.id
        LEFT JOIN services srv ON tasks.service_id = srv.id
        LEFT JOIN users assignedUser ON tasks.assigned_to = assignedUser.id
        WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate';

// Add filters to the SQL query if provided
            if ($subcontractorId) {
                $sql .= ' AND tasks.assigned_to = :subcontractorId';
            }
            if ($serviceLocationId) {
                $sql .= ' AND tasks.service_id = :serviceLocationId';
            }

$sql .= ' ORDER BY subtasks.subtask_date';

$this->db->query($sql);

// Bind the date range to the query
            $this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);

// Bind filters if provided
            if ($subcontractorId) {
                $this->db->bind(':subcontractorId', $subcontractorId);
            }
            if ($serviceLocationId) {
                $this->db->bind(':serviceLocationId', $serviceLocationId);
            }

$subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

// Store subtask details in the grouped array
                if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_name' => $subtask->service_name,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date,
                    'completed' => $subtask->completed,
                    'ignored' => $subtask->ignored,
                    'region' => $subtask->region,
                    'assignedCleaneremail' => $subtask->assignedCleaneremail,
                    'assigned_user_first_name' => $subtask->assigned_user_first_name,
                    'assigned_user_last_name' => $subtask->assigned_user_last_name,
                    'assigned_user_email' => $subtask->assigned_user_email
                ];
            }

public function getTasksAssignedToUsers()
    {
        $query = "
        SELECT 
            t.id, 
            s.name AS service_name, 
            s.region, 
            CONCAT(u.first_name, ' ', u.last_name) as user_name, 
            t.accepted, 
            t.is_assigned 
        FROM tasks t 
        LEFT JOIN services s ON t.service_id = s.id
        LEFT JOIN users u ON t.assigned_to = u.id 
        ORDER BY u.id, t.id;
    ";

$this->db->query($query);
        $tasks = $this->db->resultSet();

$categorizedTasks = [];
        foreach ($tasks as $task) {
            $userName = $task->user_name;
            if (!isset($categorizedTasks[$userName])) {
                $categorizedTasks[$userName] = [];
            }
            $categorizedTasks[$userName][] = $task;
        }

return $categorizedTasks;
    }

public function getPendingTasksWithRejectedUsers()
    {
        $query = "
        SELECT 
            t.id, 
            s.name AS service_name, 
            s.region AS region,
            CONCAT(u.first_name, ' ', u.last_name) as assigned_user, 
            t.accepted, 
            t.not_accepted_users
        FROM tasks t 
        LEFT JOIN users u ON t.assigned_to = u.id 
        LEFT JOIN services s ON t.service_id = s.id
        WHERE t.is_assigned = 0
          AND t.accepted = 0
          AND t.not_accepted_users IS NOT NULL
        ORDER BY t.id;
    ";

$this->db->query($query);
        $tasks = $this->db->resultSet();

return $tasks;
    }

public function getAcceptedTasks()
    {
        $query = "
        SELECT 
            t.id, 
            s.name AS service_name, 
            s.region AS region,
            CONCAT(u.first_name, ' ', u.last_name) as assigned_user, 
            t.accepted, 
            t.is_assigned 
        FROM tasks t 
        LEFT JOIN users u ON t.assigned_to = u.id 
        LEFT JOIN services s ON t.service_id = s.id
        WHERE t.is_assigned = 1
          AND t.accepted = 1
        ORDER BY t.id;
    ";

$this->db->query($query);
        return $this->db->resultSet();
    }

public function getTaskSummaryForUsers()
    {
        $query = "
        SELECT 
            u.id as user_id,
            CONCAT(u.first_name, ' ', u.last_name) as user_name,
            GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') as service_names,
            GROUP_CONCAT(DISTINCT s.region ORDER BY s.region SEPARATOR ', ') as regions,
            COUNT(t.id) as total_tasks,
            SUM(CASE WHEN t.is_assigned = 1 THEN 1 ELSE 0 END) as assigned_tasks,
            SUM(CASE WHEN t.accepted = 1 THEN 1 ELSE 0 END) as accepted_tasks,
            SUM(CASE WHEN t.accepted = 0 AND t.is_assigned = 1 THEN 1 ELSE 0 END) as pending_tasks,
            SUM(case when t.accepted = 0 then 1 else 0 end) as rejected_tasks,
            SUM(case when t.finished = 1 AND t.accepted = 1 then 1 else 0 end) as completed_tasks,
            SUM(case when t.finished = 0 AND t.accepted = 1 then 1 else 0 end) as not_completed_tasks,
            GROUP_CONCAT(
                DISTINCT CONCAT(u2.first_name, ' ', u2.last_name)
                ORDER BY t.updated_at DESC SEPARATOR ', '
            ) as rejected_users
        FROM users u
        LEFT JOIN tasks t ON u.id = t.assigned_to
        LEFT JOIN users u2 ON FIND_IN_SET(u2.id, t.not_accepted_users)
        LEFT JOIN services s ON t.service_id = s.id
        WHERE u.role = 'subcontractor'
        GROUP BY u.id
        ORDER BY u.id;
    ";

$this->db->query($query);
        return $this->db->resultSet();
    }

public function getTaskSummaryForUser($UserId)
    {
        $query = "
        SELECT 
            u.id as user_id,
            CONCAT(u.first_name, ' ', u.last_name) as user_name,
            GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') as service_names,
            GROUP_CONCAT(DISTINCT s.region ORDER BY s.region SEPARATOR ', ') as regions,
            COUNT(t.id) as total_tasks,
            SUM(CASE WHEN t.is_assigned = 1 THEN 1 ELSE 0 END) as assigned_tasks,
            SUM(CASE WHEN t.accepted = 1 THEN 1 ELSE 0 END) as accepted_tasks,
            SUM(CASE WHEN t.accepted = 0 AND t.is_assigned = 1 THEN 1 ELSE 0 END) as pending_tasks,
            SUM(case when t.accepted = 0 then 1 else 0 end) as rejected_tasks,
            SUM(case when t.finished = 1 AND t.accepted = 1 then 1 else 0 end) as completed_tasks,
            SUM(case when t.finished = 0 AND t.accepted = 1 then 1 else 0 end) as not_completed_tasks,
            GROUP_CONCAT(
                DISTINCT CONCAT(u2.first_name, ' ', u2.last_name)
                ORDER BY t.updated_at DESC SEPARATOR ', '
            ) as rejected_users
        FROM users u
        LEFT JOIN tasks t ON u.id = t.assigned_to
        LEFT JOIN users u2 ON FIND_IN_SET(u2.id, t.not_accepted_users)
        LEFT JOIN services s ON t.service_id = s.id
        WHERE u.role = 'subcontractor' AND u.id = :UserId
        GROUP BY u.id
        ORDER BY u.id;
    ";

$this->db->query($query);
        $this->db->bind(':UserId', $UserId);
        return $this->db->resultSet();
    }

public function isTaskAssignedAndAccepted(int $subtask_id)
    {
        // SQL query to join tasks and subtasks tables
        $this->db->query('
        SELECT t.is_assigned, t.accepted
        FROM tasks t
        JOIN subtasks s ON t.id = s.task_id
        WHERE s.id = :subtask_id AND t.is_assigned = 1 AND t.accepted = 1
    ');

// Bind the subtask ID to the query
        $this->db->bind(':subtask_id', $subtask_id);

// Execute the query and fetch the result
        $is_assigned = $this->db->single();

// Return the is_assigned property if it exists
        return isset($is_assigned->is_assigned) ? $is_assigned->is_assigned : false;
    }

public function getTasksByAssignedAndNotCompleted($currentCompanyId)
    {
        $query = "
        SELECT t.*, 
               CONCAT(u.first_name, ' ', u.last_name) AS assigned_to_name,
               s.name AS service_name,
               s.region AS region
        FROM tasks t
        LEFT JOIN users u ON t.assigned_to = u.id
        LEFT JOIN services s ON t.service_id = s.id
        WHERE t.assigned_to = :assigned_to
          AND t.finished = 0
          AND t.accepted = 1
    ";

$this->db->query($query);
        $this->db->bind(':assigned_to', $currentCompanyId);
        return $this->db->resultSet();
    }

public function getTasksByAssignedandnotCompletedAndNotAssigned($currentCompanyId)
    {
        $query = "
        SELECT t.*, 
               CONCAT(u.first_name, ' ', u.last_name) AS assigned_to_name,
               s.name AS service_name,
               s.region AS region
        FROM tasks t
        LEFT JOIN users u ON t.assigned_to = u.id
        LEFT JOIN services s ON t.service_id = s.id
        WHERE t.assigned_to = :assigned_to
          AND t.finished = 0
          AND t.accepted = 1
          AND t.assignedCleaneremail IS NULL
          AND t.is_assigned_to_cleaner = 0
    ";

$this->db->query($query);
        $this->db->bind(':assigned_to', $currentCompanyId);
        return $this->db->resultSet();
    }

public function getAllSubtasksForSubContractorsAssigned(int $companyId)
    {
        try {
            // Adjusted SQL query to join subtasks with tasks based on assigned_to
            $this->db->query('SELECT subtasks.subtask_date, subtasks.id, subtasks.service_name, subtasks.subtask_time, subtasks.assigned, subtasks.updated_at, subtasks.completed
                          FROM subtasks 
                          INNER JOIN tasks ON subtasks.task_id = tasks.id 
                          WHERE tasks.assigned_to = :assigned_to AND tasks.accepted = 1 AND subtasks.assigned = 1 
                          ORDER BY subtasks.subtask_date');
            $this->db->bind(':assigned_to', $companyId);
            $subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

public function getAllSubtasksForSubContractorsGroupByRegion($SubContractorId)
    {
        try {
            $query = "
            SELECT t.region, s.id, serv.name as service_name, s.subtask_date, s.subtask_time, s.assigned, s.updated_at
            FROM tasks t
            LEFT JOIN subtasks s ON t.id = s.task_id
            LEFT JOIN services serv ON t.service_id = serv.id
            WHERE t.assigned_to = :assigned_to
              AND t.accepted = 1
              AND s.assigned = 0
            GROUP BY t.region, s.id, serv.name, s.subtask_date, s.subtask_time, s.assigned, s.updated_at
        ";

$this->db->query($query);
            $this->db->bind(':assigned_to', $SubContractorId);
            $subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $region = $subtask->region;

if (!isset($groupedSubtasks[$region])) {
                    $groupedSubtasks[$region] = [];
                }
                $groupedSubtasks[$region][] = [
                    'id' => $subtask->id,
                    'service_name' => $subtask->service_name,
                    'subtask_date' => $subtask->subtask_date,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'updated_at' => $subtask->updated_at,
                    'region' => $region
                ];
            }

return $groupedSubtasks;
        } catch (PDOException $e) {
            error_log('Database error: ' . $e->getMessage());
            return [];
        }
    }

public function updateTaskStatusAfterMessage($taskId, $email)
    {
        // Update the main task
        $this->db->query('UPDATE tasks SET assignedCleaneremail = :email, is_assigned_to_cleaner = 1 WHERE id = :id');
        $this->db->bind(':email', $email);
        $this->db->bind(':id', $taskId);
        $taskUpdateSuccess = $this->db->execute();

// Update the not completed subtasks
        $this->db->query('UPDATE subtasks SET assigned_to_Mobile = :email, assigned_Message_Rsponse = "success", assigned = 1 WHERE task_id = :id AND completed = 0');
        $this->db->bind(':email', $email);
        $this->db->bind(':id', $taskId);
        $subtaskUpdateSuccess = $this->db->execute();

//check if both subtask and task update is successful
        if ($taskUpdateSuccess && $subtaskUpdateSuccess) {
            return true;
        } else {
            return false;
        }

}

public function getTasksByAssignedAndNotCompletedAndNoCompletedSubtasks($currentCompanyId)
    {
        $query = "
    SELECT t.*, 
           CONCAT(u.first_name, ' ', u.last_name) AS assigned_to_name,
           s.name AS service_name,
           s.region AS region
    FROM tasks t
    LEFT JOIN users u ON t.assigned_to = u.id
    LEFT JOIN services s ON t.service_id = s.id
    LEFT JOIN subtasks st ON t.id = st.task_id
    WHERE t.assigned_to = :assigned_to
      AND t.finished = 0
      AND t.accepted = 1
      AND t.assignedCleaneremail IS NOT NULL
      
    GROUP BY t.id
    ";

$this->db->query($query);
        $this->db->bind(':assigned_to', $currentCompanyId);
        return $this->db->resultSet();
    }

public function getAllSchedules()
    {
        $this->db->query('SELECT * FROM schedules');
        return $this->db->resultSet();
    }

public function getTasksBySchedule(int $selectedScheduleId)
    {
        $this->db->query('SELECT t.*, s.name AS service_name, s.region
                  FROM tasks t
                  JOIN services s ON t.service_id = s.id
                  WHERE t.schedule_id = :schedule_id');
        $this->db->bind(':schedule_id', $selectedScheduleId);
        return $this->db->resultSet();
    }

//    public function getAllTasksByTaskProvider($taskProviderId, $filters = [])
//    {
//        $query = '
//    SELECT
//        tasks.*,
//        services.name AS service_name,
//        services.region AS region,
//        schedules.schedule_name,
//        CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
//        CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
//        CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
//    FROM tasks
//    LEFT JOIN schedules ON tasks.schedule_id = schedules.id
//    LEFT JOIN services ON tasks.service_id = services.id
//    LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
//    LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
//    LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
//    WHERE tasks.added_by = :taskProviderId
//    ';
//
//        $params = [':taskProviderId' => $taskProviderId];
//
//        // Apply filters
//        if (!empty($filters['service_name'])) {
//            $query .= ' AND services.name LIKE :service_name';
//            $params[':service_name'] = '%' . $filters['service_name'] . '%';
//        }
//
//        if (!empty($filters['region'])) {
//            $query .= ' AND services.region LIKE :region';
//            $params[':region'] = '%' . $filters['region'] . '%';
//        }
//
//        if (!empty($filters['frequency'])) {
//            $query .= ' AND tasks.frequency LIKE :frequency';
//            $params[':frequency'] = '%' . $filters['frequency'] . '%';
//        }
//
//        if (isset($filters['public'])) {
//            $query .= ' AND tasks.isPublic = :public';
//            $params[':public'] = $filters['public'];
//        }
//
//        if (isset($filters['finished'])) {
//            $query .= ' AND tasks.finished = :finished';
//            $params[':finished'] = $filters['finished'];
//        }
//
//        if (isset($filters['assigned'])) {
//            $query .= ' AND tasks.is_assigned = :assigned';
//            $params[':assigned'] = $filters['assigned'];
//        }
//
//        $query .= ' ORDER BY tasks.created_at DESC, tasks.id DESC';
//
//        $this->db->query($query);
//
//        // Bind parameters
//        foreach ($params as $key => $value) {
//            $this->db->bind($key, $value);
//        }
//
//        return $this->db->resultSet();
//    }

public function getAllTasksByTaskProvider($taskProviderId, $filters = [])
    {
        $query = '
    SELECT 
        tasks.*, 
        services.name AS service_name,
        services.region AS region,
        schedules.schedule_name,
        CONCAT(added_by_user.first_name, " ", added_by_user.last_name) AS added_by_name,
        CONCAT(updated_by_user.first_name, " ", updated_by_user.last_name) AS updated_by_name,
        CONCAT(assigned_to_user.first_name, " ", assigned_to_user.last_name) AS assigned_to_name
    FROM tasks 
    LEFT JOIN schedules ON tasks.schedule_id = schedules.id 
    LEFT JOIN services ON tasks.service_id = services.id
    LEFT JOIN users AS added_by_user ON tasks.added_by = added_by_user.id
    LEFT JOIN users AS updated_by_user ON tasks.updated_by = updated_by_user.id
    LEFT JOIN users AS assigned_to_user ON tasks.assigned_to = assigned_to_user.id
    ';

$params = []; // Removed taskProviderId param

// Apply filters
        if (!empty($filters['service_name'])) {
            $query .= ' WHERE services.name LIKE :service_name'; // Changed AND to WHERE for the first filter
            $params[':service_name'] = '%' . $filters['service_name'] . '%';
        }

if (!empty($filters['region'])) {
            $query .= isset($params[':service_name']) ? ' AND' : ' WHERE';
            $query .= ' services.region LIKE :region';
            $params[':region'] = '%' . $filters['region'] . '%';
        }

if (!empty($filters['frequency'])) {
            $query .= (isset($params[':service_name']) || isset($params[':region'])) ? ' AND' : ' WHERE';
            $query .= ' tasks.frequency LIKE :frequency';
            $params[':frequency'] = '%' . $filters['frequency'] . '%';
        }

if (isset($filters['public'])) {
            $query .= (isset($params[':service_name']) || isset($params[':region']) || isset($params[':frequency'])) ? ' AND' : ' WHERE';
            $query .= ' tasks.isPublic = :public';
            $params[':public'] = $filters['public'];
        }

if (isset($filters['finished'])) {
            $query .= (isset($params[':service_name']) || isset($params[':region']) || isset($params[':frequency']) || isset($params[':public'])) ? ' AND' : ' WHERE';
            $query .= ' tasks.finished = :finished';
            $params[':finished'] = $filters['finished'];
        }

if (isset($filters['assigned'])) {
            $query .= (isset($params[':service_name']) || isset($params[':region']) || isset($params[':frequency']) || isset($params[':public']) || isset($params[':finished'])) ? ' AND' : ' WHERE';
            $query .= ' tasks.is_assigned = :assigned';
            $params[':assigned'] = $filters['assigned'];
        }

$query .= ' ORDER BY tasks.created_at DESC, tasks.id DESC';

$this->db->query($query);

// Bind parameters
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }

return $this->db->resultSet();
    }

public function getAllSubtasksForTaskProviderGroupByDate($startDate = null, $endDate = null, $serviceLocationId = null, $taskProviderId = null)
    {
        try {
            // Get the current date and calculate the date range if not provided
            if (!$startDate || !$endDate) {
                $currentDate = date('Y-m-d');
                $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
                $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate)));
                $startDate = $startDate ?: $oneWeekBefore;
                $endDate = $endDate ?: $oneWeekAfter;
            }

// SQL query to join subtasks with tasks, services, and schedules
            $sql = '
            SELECT 
                subtasks.subtask_date, 
                subtasks.id, 
                services.id AS service_id,
                services.name AS service_name,
                subtasks.subtask_time, 
                subtasks.assigned, 
                subtasks.completed,
                subtasks.ignored,
                subtasks.updated_at 
            FROM subtasks 
            INNER JOIN tasks ON subtasks.task_id = tasks.id 
            INNER JOIN services ON tasks.service_id = services.id
            INNER JOIN schedules ON tasks.schedule_id = schedules.id
            WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate 
            AND schedules.created_by = :taskProviderId'; // Ensure tasks are created by the task provider

// Add service location filter if provided
            if ($serviceLocationId) {
                $sql .= ' AND tasks.service_id = :serviceLocationId';
            }

$sql .= ' ORDER BY subtasks.subtask_date';

$this->db->query($sql);

// Bind the parameters to the query
            $this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);
            $this->db->bind(':taskProviderId', $taskProviderId);

if ($serviceLocationId) {
                $this->db->bind(':serviceLocationId', $serviceLocationId);
            }

// Execute the query and fetch results
            $subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

// Store subtask details in the grouped array
                if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_id' => $subtask->service_id,
                    'service_name' => $subtask->service_name,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'completed' => $subtask->completed,
                    'ignored' => $subtask->ignored,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date // Include subtask_date in each entry
                ];
            }

public function getAllIncompletedSubtaksGroupByDate($startDate = null, $endDate = null)
    {

try {
            // Get the current date and calculate the date range if not provided
            if (!$startDate || !$endDate) {
                $currentDate = date('Y-m-d');
                $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
                $today = date('Y-m-d', strtotime('+1 day', strtotime($currentDate)));
                $startDate = $startDate ?: $oneWeekBefore;
                $endDate = $endDate ?: $today;
            }

// Adjusted SQL query to join subtasks with tasks, services, and schedules
            $this->db->query('
            SELECT 
                subtasks.subtask_date, 
                subtasks.id, 
                services.id AS service_id,
                services.name AS service_name,
                subtasks.subtask_time, 
                subtasks.assigned, 
                subtasks.completed,
                subtasks.updated_at,
                subtasks.assigned_to_Mobile
            FROM subtasks 
            INNER JOIN tasks ON subtasks.task_id = tasks.id 
            INNER JOIN services ON tasks.service_id = services.id
            INNER JOIN schedules ON tasks.schedule_id = schedules.id
            WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate AND subtasks.completed = 0 AND subtasks.assigned = 1 AND subtasks.ignored = 0
            ORDER BY subtasks.subtask_date
        ');

// Bind the task provider ID and date range to the query

$this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);

$subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

public function getAllSubtasksWithNotesGroupByDate($startDate = null, $endDate = null, $subcontractorId = null, $serviceLocationId = null)
    {
        try {
            // Get the current date and calculate the date range if not provided
            if (!$startDate || !$endDate) {
                $currentDate = date('Y-m-d');
                $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
                $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate)));
                $startDate = $startDate ?: $oneWeekBefore;
                $endDate = $endDate ?: $oneWeekAfter;
                if ($endDate < $startDate) {
                    $endDate = $startDate; // Ensure end date is not before start date
                }
            }
            // SQL query to fetch subtasks and notes from form_submissions
            $sql = '
            SELECT
                subtasks.id,
                subtasks.subtask_date,
                subtasks.subtask_time,
                subtasks.assigned,
                subtasks.updated_at,
                subtasks.completed,
                tasks.service_id,
                srv.name AS service_name,
                srv.region AS region,
                tasks.assignedCleaneremail,
                assignedUser.first_name AS assigned_user_first_name,
                assignedUser.last_name AS assigned_user_last_name,
                assignedUser.email AS assigned_user_email,
                COALESCE(fs.notes, "") AS notes
            FROM subtasks
            INNER JOIN tasks ON subtasks.task_id = tasks.id
            LEFT JOIN services srv ON tasks.service_id = srv.id
            LEFT JOIN users assignedUser ON tasks.assigned_to = assignedUser.id
            LEFT JOIN form_submissions fs ON subtasks.id = fs.subtask_id
            WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate';
            // Add additional filters to the SQL query if provided
            if ($subcontractorId) {
                $sql .= ' AND tasks.assigned_to = :subcontractorId';
            }
            if ($serviceLocationId) {
                $sql .= ' AND tasks.service_id = :serviceLocationId';
            }
            $sql .= ' ORDER BY subtasks.subtask_date';
            $this->db->query($sql);
            // Bind parameters to the query
            $this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);
            // Bind additional filters if provided
            if ($subcontractorId) {
                $this->db->bind(':subcontractorId', $subcontractorId);
            }
            if ($serviceLocationId) {
                $this->db->bind(':serviceLocationId', $serviceLocationId);
            }
            // Execute the query and fetch all results
            $subtasks = $this->db->resultSet();
            // Initialize an array to store grouped subtasks
            $groupedSubtasks = [];
            // Iterate through fetched subtasks and group them by date
            foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;
                // Initialize an array for each date if not already initialized
                if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }
                // Add subtask details to the grouped array
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_name' => $subtask->service_name,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date,
                    'completed' => $subtask->completed,
                    'region' => $subtask->region,
                    'assignedCleaneremail' => $subtask->assignedCleaneremail,
                    'assigned_user_first_name' => $subtask->assigned_user_first_name,
                    'assigned_user_last_name' => $subtask->assigned_user_last_name,
                    'assigned_user_email' => $subtask->assigned_user_email,
                    'notes' => $subtask->notes, // Include notes from form_submissions
                ];
            }
            return $groupedSubtasks; // Return the grouped subtasks array
        } catch (PDOException $e) {
            // Handle database errors appropriately
            error_log('Database error: ' . $e->getMessage());
            return []; // Return empty array on error
        }
    }

public function getAllSubtasksWithNotesGroupByDateForSubContractors($startDate = null, $endDate = null, $subcontractorId = null, $serviceLocationId = null)
    {
        try {
            // Get the current date and calculate the date range if not provided
            if (!$startDate || !$endDate) {
                $currentDate = date('Y-m-d');
                $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
                $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate)));
                $startDate = $startDate ?: $oneWeekBefore;
                $endDate = $endDate ?: $oneWeekAfter;
                if ($endDate < $startDate) {
                    $endDate = $startDate; // Ensure end date is not before start date
                }
            }

// SQL query to fetch subtasks and notes specific to subcontractors
            $sql = '
        SELECT
            subtasks.id,
            subtasks.subtask_date,
            subtasks.subtask_time,
            subtasks.assigned,
            subtasks.updated_at,
            subtasks.completed,
            tasks.service_id,
            srv.name AS service_name,
            srv.region AS region,
            assignedUser.first_name AS assigned_user_first_name,
            assignedUser.last_name AS assigned_user_last_name,
            COALESCE(fs.notes, "") AS notes
        FROM subtasks
        INNER JOIN tasks ON subtasks.task_id = tasks.id
        LEFT JOIN services srv ON tasks.service_id = srv.id
        LEFT JOIN users assignedUser ON tasks.assigned_to = assignedUser.id
        LEFT JOIN form_submissions fs ON subtasks.id = fs.subtask_id
        WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate';

// Filter by subcontractor
            if ($subcontractorId) {
                $sql .= ' AND tasks.assigned_to = :subcontractorId';
            }

// Filter by service location
            if ($serviceLocationId) {
                $sql .= ' AND tasks.service_id = :serviceLocationId';
            }

$sql .= ' ORDER BY subtasks.subtask_date';

// Prepare the query
            $this->db->query($sql);

// Bind parameters to the query
            $this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);

if ($subcontractorId) {
                $this->db->bind(':subcontractorId', $subcontractorId);
            }

if ($serviceLocationId) {
                $this->db->bind(':serviceLocationId', $serviceLocationId);
            }

// Execute the query and fetch results
            $subtasks = $this->db->resultSet();

// Group subtasks by date
            $groupedSubtasks = [];
            foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

// Initialize date group if not already initialized
                if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }

// Add subtask to the date group
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_name' => $subtask->service_name,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date,
                    'completed' => $subtask->completed,
                    'region' => $subtask->region,
                    'assigned_user_first_name' => $subtask->assigned_user_first_name,
                    'assigned_user_last_name' => $subtask->assigned_user_last_name,
                    'notes' => $subtask->notes,
                ];
            }

return $groupedSubtasks;

} catch (PDOException $e) {
            // Log the error and return an empty result set on failure
            error_log('Database error: ' . $e->getMessage());
            return [];
        }
    }

public function getAllSubtasksWithNotesGroupByDateForTeamKidsAdmin($startDate = null, $endDate = null, $serviceLocationId = null)
    {
        try {
            // Get the current date and calculate the date range if not provided
            if (!$startDate || !$endDate) {
                $currentDate = date('Y-m-d');
                $oneWeekBefore = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
                $oneWeekAfter = date('Y-m-d', strtotime('+1 week', strtotime($currentDate)));
                $startDate = $startDate ?: $oneWeekBefore;
                $endDate = $endDate ?: $oneWeekAfter;
                if ($endDate < $startDate) {
                    $endDate = $startDate; // Ensure end date is not before start date
                }
            }

// SQL query to fetch all subtasks and notes, without subcontractor filter
            $sql = '
        SELECT
            subtasks.id,
            subtasks.subtask_date,
            subtasks.subtask_time,
            subtasks.assigned,
            subtasks.updated_at,
            subtasks.completed,
            tasks.service_id,
            srv.name AS service_name,
            srv.region AS region,
            COALESCE(fs.notes, "") AS notes
        FROM subtasks
        INNER JOIN tasks ON subtasks.task_id = tasks.id
        LEFT JOIN services srv ON tasks.service_id = srv.id
        LEFT JOIN form_submissions fs ON subtasks.id = fs.subtask_id
        WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate';

// Filter by service location
            if ($serviceLocationId) {
                $sql .= ' AND tasks.service_id = :serviceLocationId';
            }

$sql .= ' ORDER BY subtasks.subtask_date';

$this->db->query($sql);

// Bind parameters to the query
            $this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);

if ($serviceLocationId) {
                $this->db->bind(':serviceLocationId', $serviceLocationId);
            }

// Execute the query and fetch results
            $subtasks = $this->db->resultSet();

// Group subtasks by date
            $groupedSubtasks = [];
            foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }

// Add subtask to the group, hiding subcontractor details
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_name' => $subtask->service_name,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date,
                    'completed' => $subtask->completed,
                    'region' => $subtask->region,
                    'notes' => $subtask->notes,
                ];
            }

return $groupedSubtasks;

} catch (PDOException $e) {
            error_log('Database error: ' . $e->getMessage());
            return [];
        }
    }

public function getAllSubtasksForSubContractorsGroupByDateReport($startDate, $endDate ,  $serviceLocationId, $currentUserId )
    {
        try {
            // Get the current date and calculate the date range if not provided
            $currentDate = date('Y-m-d');

// If the date range is not provided, set it to one week before and after the current date
            if (!$startDate) {
                $startDate = date('Y-m-d', strtotime('-1 week', strtotime($currentDate)));
            }
            if (!$endDate) {
                $endDate = date('Y-m-d', strtotime('+1 week', strtotime($currentDate)));
            }

// Ensure the end date is not before the start date
            if ($endDate < $startDate) {
                $endDate = $startDate;
            }

// SQL query to fetch subtasks along with task, service, and schedule details
            $sql = '
        SELECT 
            subtasks.subtask_date, 
            subtasks.id, 
            services.id AS service_id,
            services.name AS service_name,
            subtasks.subtask_time, 
            subtasks.assigned, 
            subtasks.completed,
            subtasks.ignored,
            subtasks.updated_at 
        FROM subtasks 
        INNER JOIN tasks ON subtasks.task_id = tasks.id 
        INNER JOIN services ON tasks.service_id = services.id
        INNER JOIN schedules ON tasks.schedule_id = schedules.id
        WHERE subtasks.subtask_date BETWEEN :startDate AND :endDate 
        AND tasks.assigned_to = :currentUserId';

// Add service location filter if provided
            if ($serviceLocationId) {
                $sql .= ' AND tasks.service_id = :serviceLocationId';
            }

$sql .= ' ORDER BY subtasks.subtask_date';

// Prepare the SQL query
            $this->db->query($sql);

// Bind parameters to the query
            $this->db->bind(':startDate', $startDate);
            $this->db->bind(':endDate', $endDate);
            $this->db->bind(':currentUserId', $currentUserId);

if ($serviceLocationId) {
                $this->db->bind(':serviceLocationId', $serviceLocationId);
            }

// Execute the query and fetch results
            $subtasks = $this->db->resultSet();

$groupedSubtasks = [];

foreach ($subtasks as $subtask) {
                $date = $subtask->subtask_date;

// Store subtask details in the grouped array
                if (!isset($groupedSubtasks[$date])) {
                    $groupedSubtasks[$date] = [];
                }
                $groupedSubtasks[$date][] = [
                    'id' => $subtask->id,
                    'service_id' => $subtask->service_id,
                    'service_name' => $subtask->service_name,
                    'subtask_time' => $subtask->subtask_time,
                    'assigned' => $subtask->assigned,
                    'completed' => $subtask->completed,
                    'ignored' => $subtask->ignored,
                    'updated_at' => $subtask->updated_at,
                    'subtask_date' => $date // Include subtask_date in each entry
                ];
            }

return $groupedSubtasks;
        } catch (PDOException $e) {
            // Handle database errors appropriately (log, throw further, etc.)
            error_log('Database error in getAllSubtasksForSubContractorsGroupByDateReport: ' . $e->getMessage());
            return []; // Return empty array or handle error as needed
        }
    }

public function    getAllServicesForDropdown()
    {
        $this->db->query('SELECT id, name FROM services');
        return $this->db->resultSet();
    }

}

/home/emeraadmin/.spamassassin/../public_html/./node_modules/../Service/TaskService.php