.-.-.-.-.-

<?php

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

class ScheduleService
{
    private $db;

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

public function addSchedule($schedule)
    {
        $this->db->query('INSERT INTO schedules (schedule_name,created_by, completed, created_at, updated_at) VALUES (:schedule_name,  :created_by,:completed, :created_at, :updated_at)');
        $this->db->bind(':schedule_name', $schedule->getScheduleName());
        $this->db->bind(':created_by', $schedule->getCreatedBy());
        $this->db->bind(':completed', $schedule->getCompleted());
        $this->db->bind(':created_at', $schedule->getCreatedAt());
        $this->db->bind(':updated_at', $schedule->getUpdatedAt());

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

public function getAllSchedules($filters = [])
    {
        $query = "SELECT schedules.*, 
                CONCAT(users_created.first_name, ' ', users_created.last_name) AS created_by
                
              FROM schedules 
              LEFT JOIN users AS users_created ON schedules.created_by = users_created.id
              
              WHERE 1=1";

$params = [];

if (isset($filters['completed']) && $filters['completed'] !== '') {
            $query .= " AND schedules.completed = :completed";
            $params[':completed'] = $filters['completed'];
        }
        if (!empty($filters['date_from'])) {
            $query .= " AND schedules.created_at >= :date_from";
            $params[':date_from'] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $query .= " AND schedules.created_at <= :date_to";
            $params[':date_to'] = $filters['date_to'];
        }

$query .= " ORDER BY schedules.created_at DESC";

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

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

public function getAllSchedulesByTaskProvider($taskproviderid, $filters = [])
    {
        $query = "SELECT schedules.*, 
                CONCAT(users_created.first_name, ' ', users_created.last_name) AS created_by
                
              FROM schedules 
              LEFT JOIN users AS users_created ON schedules.created_by = users_created.id
              
              WHERE 1=1 AND schedules.created_by = :created_by";

$params = [':created_by' => $taskproviderid];

$query .= " AND schedules.created_by = :created_by ORDER BY schedules.created_at DESC";

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

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

public function hasAssignedTasks($scheduleId)
    {
        $this->db->query('SELECT COUNT(*) AS assigned_task_count FROM tasks WHERE schedule_id = :schedule_id AND is_assigned = 1');
        $this->db->bind(':schedule_id', $scheduleId);
        $result = $this->db->single();
        return $result->assigned_task_count > 0;
    }

//has assigned and accepted tasks
    public function hasAssignedAcceptedTasks($scheduleId)
    {
        $this->db->query('SELECT COUNT(*) AS assigned_task_count FROM tasks WHERE schedule_id = :schedule_id AND is_assigned = 1 AND accepted = 1');
        $this->db->bind(':schedule_id', $scheduleId);
        $result = $this->db->single();
        return $result->assigned_task_count > 0;
    }

public function hasSubmittedSubTasks($scheduleId)
    {
        $this->db->query('SELECT COUNT(*) AS submitted_subtask_count FROM subtasks 
                          JOIN tasks ON subtasks.task_id = tasks.id
                          WHERE tasks.schedule_id = :schedule_id AND subtasks.completed = 1');
        $this->db->bind(':schedule_id', $scheduleId);
        $result = $this->db->single();
        return $result->submitted_subtask_count > 0;

}

//has assigned and accepted tasks and completed subtasks
    public function hasAssignedAcceptedCompletedSubtasks($scheduleId)
    {
        $this->db->query('SELECT COUNT(*) AS assigned_task_count FROM tasks 
                          JOIN subtasks ON tasks.id = subtasks.task_id
                          WHERE tasks.schedule_id = :schedule_id AND tasks.is_assigned = 1 AND tasks.accepted = 1 AND subtasks.completed = 1');
        $this->db->bind(':schedule_id', $scheduleId);
        $result = $this->db->single();
        return $result->assigned_task_count > 0;
    }

public function deleteScheduleWithTasks($scheduleId) {
        try {
            $this->db->beginTransaction();

// Delete subtasks
            $this->db->query('DELETE subtasks FROM subtasks
                              JOIN tasks ON subtasks.task_id = tasks.id
                              WHERE tasks.schedule_id = :schedule_id');
            $this->db->bind(':schedule_id', $scheduleId);
            $this->db->execute();

// Delete tasks
            $this->db->query('DELETE FROM tasks WHERE schedule_id = :schedule_id');
            $this->db->bind(':schedule_id', $scheduleId);
            $this->db->execute();

// Delete schedule
            $this->db->query('DELETE FROM schedules WHERE id = :id');
            $this->db->bind(':id', $scheduleId);
            $result = $this->db->execute();

$this->db->commit();

return $result; // Return the result of the delete operation
        } catch (PDOException $e) {
            $this->db->rollBack();
            error_log("Error deleting schedule with tasks. Schedule ID: $scheduleId. Error: " . $e->getMessage());
            return $e->getMessage(); // Return false to indicate failure
        }
    }

public function deleteScheduleWithTasksByAdmin($scheduleId, $userId)
    {
        try {
            // Begin a transaction
            $this->db->beginTransaction();

// get user role
            $this->db->query('SELECT role FROM users WHERE id = :id');
            $this->db->bind(':id', $userId);
            $result = $this->db->single();
            $role = $result->role;

// Check if the user is an admin
            if ($role != 'emera_admin') {
                // Check if the user has assigned tasks
                if ($this->hasAssignedTasks($scheduleId)) {
                    return "You cannot delete a schedule with assigned tasks";
                }
            } else {

// Delete form submissions related to subtasks
                $this->db->query('DELETE FROM form_submissions 
                          WHERE subtask_id IN (
                              SELECT id FROM subtasks 
                              WHERE task_id IN (
                                  SELECT id FROM tasks WHERE schedule_id = :schedule_id
                              )
                          )');
                $this->db->bind(':schedule_id', $scheduleId);
                $this->db->execute();

// Delete subtasks
                $this->db->query('DELETE subtasks FROM subtasks 
                          JOIN tasks ON subtasks.task_id = tasks.id 
                          WHERE tasks.schedule_id = :schedule_id');
                $this->db->bind(':schedule_id', $scheduleId);
                $this->db->execute();

// Delete tasks
                $this->db->query('DELETE FROM tasks WHERE schedule_id = :schedule_id');
                $this->db->bind(':schedule_id', $scheduleId);
                $this->db->execute();

// Delete the schedule
                $this->db->query('DELETE FROM schedules WHERE id = :id');
                $this->db->bind(':id', $scheduleId);
                $result = $this->db->execute();

// Commit the transaction
                $this->db->commit();
                return $result; // Return the result of the delete operation
            }
        } catch (PDOException $e) {
            // Rollback the transaction on error
            $this->db->rollBack();
            error_log("Error deleting schedule with tasks. Schedule ID: $scheduleId. Error: " . $e->getMessage());
            return $e->getMessage(); // Return error message to indicate failure
        }

}

public function addScheduleRetuenlastinsertedid($schedule)
    {
        $this->db->query('INSERT INTO schedules (schedule_name,created_by,completed, created_at, updated_at) VALUES (:schedule_name, :created_by, :completed, :created_at, :updated_at)');
        $this->db->bind(':schedule_name', $schedule->getScheduleName());

$this->db->bind(':created_by', $schedule->getCreatedBy());

$this->db->bind(':completed', $schedule->getCompleted());
        $this->db->bind(':created_at', $schedule->getCreatedAt());
        $this->db->bind(':updated_at', $schedule->getUpdatedAt());

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

/home/emeraadmin/www/./node_modules/../cgi-bin/../Service/ScheduleService.php