<?php
class Task extends Model
{
    // Add a new task
    public function addTask($data){
        $stmt = $this->db->prepare("
            INSERT INTO tasks (contact_id, user_id, title, due_date, status)
            VALUES (:contact_id, :user_id, :title, :due_date, :status)
        ");
        return $stmt->execute([
            'contact_id' => $data['contact_id'],
            'user_id' => $data['user_id'],
            'title' => $data['title'],
            'due_date' => $data['due_date'],
            'status' => $data['status'] ?? 'pending'
        ]);
    }

    // Get all tasks by contact
    public function getTasksByContactId($contactId){
        $stmt = $this->db->prepare("
            SELECT t.*, u.name AS user_name
            FROM tasks t
            LEFT JOIN users u ON t.user_id = u.id
            WHERE t.contact_id = ?
            ORDER BY t.due_date ASC
        ");
        $stmt->execute([$contactId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // Get task by ID
    public function getTaskById($id){
        $stmt = $this->db->prepare("
            SELECT * FROM tasks WHERE id = ?
        ");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // Update task
    public function updateTask($id, $userId, $data){
        $stmt = $this->db->prepare("
            UPDATE tasks
            SET title = :title, due_date = :due_date, status = :status, updated_at = NOW()
            WHERE id = :id AND user_id = :user_id
        ");
        return $stmt->execute([
            'title' => $data['title'],
            'due_date' => $data['due_date'],
            'status' => $data['status'],
            'id' => $id,
            'user_id' => $userId
        ]);
    }

    // Delete task
    public function deleteTask($id, $userId){
        $stmt = $this->db->prepare("
            DELETE FROM tasks WHERE id = ? AND user_id = ?
        ");
        return $stmt->execute([$id, $userId]);
    }
    
    // Count all pending tasks
    public function countPendingTasks($userId = null) {
        $sql = "SELECT COUNT(*) FROM tasks WHERE status = 'pending'";
        $params = [];
    
        if ($userId) {
            $sql .= " AND user_id = ?";
            $params[] = $userId;
        }
    
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
    
    // Count tasks due today
    public function countTasksDueToday($userId = null) {
        $sql = "SELECT COUNT(*) FROM tasks WHERE DATE(due_date) = CURDATE()";
        $params = [];
    
        if ($userId) {
            $sql .= " AND user_id = ?";
            $params[] = $userId;
        }
    
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
    
    // Count tasks due this week
    public function countTasksDueThisWeek($userId = null) {
        $sql = "SELECT COUNT(*) FROM tasks 
                WHERE WEEK(due_date, 1) = WEEK(CURDATE(), 1) 
                  AND YEAR(due_date) = YEAR(CURDATE())";
        $params = [];
    
        if ($userId) {
            $sql .= " AND user_id = ?";
            $params[] = $userId;
        }
    
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
    
    public function getTasksByStatus($userId, $status) {
    $stmt = $this->db->prepare("SELECT * FROM tasks WHERE user_id = ? AND status = ? ORDER BY due_date ASC");
    $stmt->execute([$userId, $status]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getTasksDueToday($userId) {
        $stmt = $this->db->prepare("SELECT * FROM tasks WHERE user_id = ? AND DATE(due_date) = CURDATE()");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTasksDueThisWeek($userId) {
        $stmt = $this->db->prepare("SELECT * FROM tasks WHERE user_id = ? 
            AND WEEK(due_date, 1) = WEEK(CURDATE(), 1) 
            AND YEAR(due_date) = YEAR(CURDATE())");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTaskCountsByStatus() {
    $tasks = $this->getAllTasks();

    $counts = [
        'pending' => 0,
        'completed' => 0,
        'overdue' => 0
    ];

    foreach ($tasks as $task) {
        $counts[$task['derived_status']]++;
    }

    return $counts;
}

    public function getAllTasks() {
    $stmt = $this->db->query("
        SELECT 
            tasks.*, 
            contacts.name AS contact_name, 
            users.name AS agent_name,
            CASE 
                WHEN tasks.status = 'completed' THEN 'completed'
                WHEN tasks.status = 'pending' AND tasks.due_date < NOW() THEN 'overdue'
                ELSE 'pending'
            END AS derived_status
        FROM tasks
        LEFT JOIN contacts ON tasks.contact_id = contacts.id
        LEFT JOIN users ON tasks.user_id = users.id
        ORDER BY tasks.due_date ASC
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getAllTasksByStatus($status) {
        $stmt = $this->db->prepare("
            SELECT tasks.*, contacts.name AS contact_name, users.name AS agent_name
            FROM tasks
            LEFT JOIN contacts ON tasks.contact_id = contacts.id
            LEFT JOIN users ON tasks.user_id = users.id
            WHERE tasks.status = ?
            ORDER BY tasks.due_date ASC
        ");
        $stmt->execute([$status]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTasksByDerivedStatus($status) {
    $tasks = $this->getAllTasks();

    return array_filter($tasks, function($task) use ($status) {
        return $task['derived_status'] === $status;
    });
    }
    
    public function getOverdueTasksWithEmails($includeReminded = false) {
    $sql = "
        SELECT tasks.*, users.email, users.name AS user_name, contacts.name AS contact_name
        FROM tasks
        LEFT JOIN users ON tasks.user_id = users.id
        LEFT JOIN contacts ON tasks.contact_id = contacts.id
        WHERE tasks.due_date < NOW() AND tasks.status = 'pending'
    ";

    if (!$includeReminded) {
        $sql .= " AND NOT EXISTS (
            SELECT 1 FROM reminder_logs 
            WHERE reminder_logs.task_id = tasks.id 
              AND DATE(reminder_logs.sent_at) = CURDATE()
        )";
    }

    return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}


    public function adminUpdateTask($id, $userId, $data) {
    // Fetch existing task first
    $stmt = $this->db->prepare("SELECT * FROM tasks WHERE id = ?");
    $stmt->execute([$id]);
    $task = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$task) {
        return false; // task not found
    }

    // Log changes
    foreach (['title', 'due_date', 'status'] as $field) {
    $oldValue = $task[$field];
    $newValue = $data[$field];

    // Normalize datetime for comparison
    if ($field === 'due_date') {
        $oldValue = date('Y-m-d\TH:i', strtotime($oldValue));
        $newValue = date('Y-m-d\TH:i', strtotime($newValue));
    }

    if ($oldValue != $newValue) {
        $this->logTaskUpdate($id, $userId, $field, $task[$field], $data[$field]);
    }
}


    // Now perform update
    $updateStmt = $this->db->prepare("
        UPDATE tasks
        SET title = :title,
            due_date = :due_date,
            status = :status,
            updated_at = NOW()
        WHERE id = :id
    ");
    
    return $updateStmt->execute([
        'title' => $data['title'],
        'due_date' => $data['due_date'],
        'status' => $data['status'],
        'id' => $id
    ]);
}


    public function logTaskUpdate($taskId, $userId, $field, $old, $new) {
    $stmt = $this->db->prepare("
        INSERT INTO task_update_logs (task_id, updated_by, field_changed, old_value, new_value)
        VALUES (:task_id, :updated_by, :field_changed, :old_value, :new_value)
    ");
    return $stmt->execute([
        'task_id' => $taskId,
        'updated_by' => $userId,
        'field_changed' => $field,
        'old_value' => $old,
        'new_value' => $new
    ]);
}


    public function getLogsByTaskId($taskId) {
    $stmt = $this->db->prepare("
        SELECT logs.*, users.name AS updated_by_name
        FROM task_update_logs AS logs
        LEFT JOIN users ON logs.updated_by = users.id
        WHERE logs.task_id = ?
        ORDER BY logs.updated_at DESC
    ");
    $stmt->execute([$taskId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

    public function markTaskAsReminded($taskId) {
    $stmt = $this->db->prepare("UPDATE tasks SET reminded = 1 WHERE id = ?");
    return $stmt->execute([$taskId]);
    }
    
    public function logReminder($taskId, $userId, $ccEmails = null) {
        $stmt = $this->db->prepare("
            INSERT INTO reminder_logs (task_id, sent_by, cc_emails)
            VALUES (?, ?, ?)
        ");
        return $stmt->execute([$taskId, $userId, $ccEmails]);
    }

    public function getReminderLogsByTask($taskId) {
    $stmt = $this->db->prepare("
        SELECT rl.*, u.name AS sender_name 
        FROM reminder_logs rl
        LEFT JOIN users u ON rl.sent_by = u.id
        WHERE rl.task_id = ?
        ORDER BY rl.sent_at DESC
    ");
    $stmt->execute([$taskId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}



}
?>
