Tul xxx Tul

Viewing: TaskModel.php

<?php
require_once __DIR__ . '/../app/Database.php';
require_once __DIR__ . '/ProjectModel.php';

class TaskModel {
    private $pdo;
    public function __construct() {
        $this->pdo = Database::getConnection();
    }

    private function getCurrentUserId(): ?int {
        return current_user_id();
    }

    private function isAdmin(): bool {
        return is_admin();
    }

    private function nextPositionForQuadrant(int $urgent, int $important): int {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT COALESCE(MAX(position),0)+1
                    FROM tasks t
                    WHERE t.urgent=? AND t.important=? AND t.in_distributor=0 AND (t.status IS NULL OR t.status <> "done")
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND EXISTS (
                                SELECT 1 FROM projects p
                                WHERE p.id = t.project_id AND (
                                    p.user_id = ?
                                    OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                    OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                                )
                            )
                        )
                      )';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$urgent, $important, $userId, $userId, $userId, $userId, $userId, $userId]);
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) {
            try {
                // Fallback (older schema): compute within user's tasks only
                $userId = $this->getCurrentUserId();
                $stmt = $this->pdo->prepare('SELECT COALESCE(MAX(position),0)+1 FROM tasks WHERE urgent=? AND important=? AND in_distributor=0 AND (status IS NULL OR status <> "done") AND user_id = ?');
                $stmt->execute([$urgent, $important, $userId]);
                return (int)$stmt->fetchColumn();
            } catch (PDOException $e2) { return 0; }
        }
    }

    public function countByQuadrant(int $quadrant): int {
        $map = [1 => [1,1], 2 => [0,1], 3 => [1,0], 4 => [0,0]];
        [$urgent, $important] = $map[$quadrant] ?? [0,0];
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT COUNT(*)
                    FROM tasks t
                    WHERE t.urgent = ? AND t.important = ? AND t.in_distributor = 0 AND (t.status IS NULL OR t.status <> "done")
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND EXISTS (
                                SELECT 1 FROM projects p WHERE p.id = t.project_id AND (
                                    p.user_id = ?
                                    OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                    OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                                )
                            )
                        )
                      )';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$urgent, $important, $userId, $userId, $userId, $userId, $userId, $userId]);
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) { return 0; }
    }

    public function countDoneOnDate(string $date): int {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT COUNT(*) FROM tasks t
                    WHERE DATE(t.completed_at) = ?
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND EXISTS (
                                SELECT 1 FROM projects p WHERE p.id = t.project_id AND (
                                    p.user_id = ?
                                    OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                    OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                                )
                            )
                        )
                      )';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$date, $userId, $userId, $userId, $userId, $userId, $userId]);
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) { return 0; }
    }

    public function canAddToQuadrant(int $quadrant): bool {
        return $this->countByQuadrant($quadrant) < 5;
    }

    public function returnToDistributor(int $taskId): bool {
        try {
            // También limpiar project_id para que aparezca en la bandeja
            $stmt = $this->pdo->prepare('UPDATE tasks SET urgent = 0, important = 0, position = NULL, in_distributor = 1, project_id = NULL WHERE id = ?');
            return $stmt->execute([$taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function createTask(string $title, int $isQuick = 0, ?string $dueAt = null, ?string $labels = null, ?int $projectId = null): bool {
        [$urgent,$important] = [0,0];
        if ($labels) {
            $low = strtolower($labels);
            if (strpos($low,'urgente') !== false) $urgent = 1;
            if (strpos($low,'importante') !== false) $important = 1;
        }
        $position = $this->nextPositionForQuadrant($urgent,$important);
        $userId = $this->getCurrentUserId();
        try {
             $stmt = $this->pdo->prepare('INSERT INTO tasks (title, due_at, labels, project_id, urgent, important, position, user_id, guests) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL)');
            return $stmt->execute([$title, $dueAt, $labels, $projectId, $urgent, $important, $position, $userId]);
        } catch (PDOException $e) {
            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, due_at, urgent, important, user_id) VALUES (?, ?, ?, ?, ?)');
            return $stmt->execute([$title, $dueAt, $urgent, $important, $userId]);
        }
    }

    public function createTaskWithDuration(string $title, ?string $labels = null, ?int $durationMinutes = null, int $userId): bool {
        $urgent = 0;
        $important = 0;
        if ($durationMinutes === 2) { // 2 min tasks go to Q1 and must NOT be in distributor
            $urgent = 1;
            $important = 1;
        } else {
            if ($labels) {
                $labelArray = explode(',', $labels);
                if (in_array('Urgente', $labelArray)) $urgent = 1;
                if (in_array('Importante', $labelArray)) $important = 1;
            }
        }
        $position = $this->nextPositionForQuadrant($urgent,$important);
        try {
            if ($durationMinutes === 2) {
                // Garantizar que se guarde fuera de la bandeja (in_distributor = 0)
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, in_distributor, user_id) VALUES (?, ?, ?, 1, 1, ?, 0, ?)');
                $ok = $stmt->execute([$title, $durationMinutes, $labels, $position, $userId]);
                // Además, asignar due_at a HOY para evitar que reglas de limpieza lo envíen a la bandeja
                if ($ok) {
                    try {
                        $taskId = (int)$this->pdo->lastInsertId();
                        $today = date('Y-m-d') . ' 00:00:00';
                        $upd = $this->pdo->prepare('UPDATE tasks SET due_at = ? WHERE id = ?');
                        $upd->execute([$today, $taskId]);
                    } catch (Throwable $e2) { /* Ignorar si falla por esquema antiguo */ }
                }
                return $ok;
            } else {
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)');
                return $stmt->execute([$title, $durationMinutes, $labels, $urgent, $important, $position, $userId]);
            }
        } catch (PDOException $e) {
            // Fallback if new columns don't exist yet
            if ($durationMinutes === 2) {
                // Sin columna in_distributor, pero mantendrá Q1 por urgent/important y posición
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position) VALUES (?, ?, ?, 1, 1, ?)');
                $ok = $stmt->execute([$title, $durationMinutes, $labels, $position]);
                if ($ok) {
                    try {
                        $taskId = (int)$this->pdo->lastInsertId();
                        $today = date('Y-m-d') . ' 00:00:00';
                        $upd = $this->pdo->prepare('UPDATE tasks SET due_at = ? WHERE id = ?');
                        $upd->execute([$today, $taskId]);
                    } catch (Throwable $e3) { /* Ignorar si no existe due_at */ }
                }
                return $ok;
            } else {
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important) VALUES (?, ?, ?, ?, ?)');
                return $stmt->execute([$title, $durationMinutes, $labels, $urgent, $important]);
            }
        }
    }

    public function createTaskInDistributor(string $title, ?string $labels = null, ?int $durationMinutes = null, int $userId): bool {
        try {
            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, in_distributor, user_id) VALUES (?, ?, ?, 1, ?)');
            return $stmt->execute([$title, $durationMinutes, $labels, $userId]);
        } catch (PDOException $e) {
            // Fallback if new columns don't exist yet
            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, in_distributor) VALUES (?, ?, ?, 1)');
            return $stmt->execute([$title, $durationMinutes, $labels]);
        }
    }

    /**
     * Crea una tarea en la bandeja de distribución (Acciones sin Priorizar) pero asociada a un proyecto.
     * - in_distributor = 1
     * - project_id = dado
     * - urgent = 0, important = 0 (sin cuadrante)
     * - position = NULL
     */
    public function createTaskInDistributorForProject(string $title, ?string $labels = null, ?int $durationMinutes = null, ?int $projectId = null): bool {
        $userId = $this->getCurrentUserId();
        try {
            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, project_id, urgent, important, position, in_distributor, user_id) VALUES (?, ?, ?, ?, 0, 0, NULL, 1, ?)');
            return $stmt->execute([$title, $durationMinutes, $labels, $projectId, $userId]);
        } catch (PDOException $e) {
            try {
                // Fallback si no existen user_id o in_distributor
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, project_id, urgent, important, position, in_distributor) VALUES (?, ?, ?, ?, 0, 0, NULL, 1)');
                return $stmt->execute([$title, $durationMinutes, $labels, $projectId]);
            } catch (PDOException $e2) {
                try {
                    // Fallback mínimo de esquema antiguo
                    $stmt = $this->pdo->prepare('INSERT INTO tasks (title, project_id) VALUES (?, ?)');
                    return $stmt->execute([$title, $projectId]);
                } catch (PDOException $e3) {
                    return false;
                }
            }
        }
    }

    /**
     * Crea una tarea directamente en Q1 (Hacer hoy), preservando duración y etiquetas.
     * Marca urgent=1, important=1, in_distributor=0 y calcula la posición.
     */
    public function createTaskInQ1(string $title, ?string $labels = null, ?int $durationMinutes = null, int $userId, bool $completedOnCreate = false, ?string $completedAt = null): bool {
        $position = $this->nextPositionForQuadrant(1, 1);
        try {
            if ($completedOnCreate) {
                // Intentar crear marcando como completada desde el inicio
                if ($completedAt) {
                    // Establecer due_at igual a completed_at
                    $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, in_distributor, user_id, status, completed_at, due_at) VALUES (?, ?, ?, 1, 1, ?, 0, ?, "done", ?, ?)');
                    return $stmt->execute([$title, $durationMinutes, $labels, $position, $userId, $completedAt, $completedAt]);
                } else {
                    // Usar NOW() para completed_at y due_at
                    $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, in_distributor, user_id, status, completed_at, due_at) VALUES (?, ?, ?, 1, 1, ?, 0, ?, "done", NOW(), NOW())');
                    return $stmt->execute([$title, $durationMinutes, $labels, $position, $userId]);
                }
            }
            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, in_distributor, user_id) VALUES (?, ?, ?, 1, 1, ?, 0, ?)');
            return $stmt->execute([$title, $durationMinutes, $labels, $position, $userId]);
        } catch (PDOException $e) {
            try {
                // Fallback si faltan columnas como user_id/in_distributor/status/completed_at
                if ($completedOnCreate) {
                    if ($completedAt) {
                        // Esquema antiguo: puede que no exista due_at; intentamos incluirlo y si falla, caemos más abajo
                        try {
                            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, status, completed_at, due_at) VALUES (?, ?, ?, 1, 1, ?, "done", ?, ?)');
                            return $stmt->execute([$title, $durationMinutes, $labels, $position, $completedAt, $completedAt]);
                        } catch (PDOException $e3) {
                            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, status, completed_at) VALUES (?, ?, ?, 1, 1, ?, "done", ?)');
                            return $stmt->execute([$title, $durationMinutes, $labels, $position, $completedAt]);
                        }
                    } else {
                        try {
                            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, status, completed_at, due_at) VALUES (?, ?, ?, 1, 1, ?, "done", NOW(), NOW())');
                            return $stmt->execute([$title, $durationMinutes, $labels, $position]);
                        } catch (PDOException $e4) {
                            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position, status, completed_at) VALUES (?, ?, ?, 1, 1, ?, "done", NOW())');
                            return $stmt->execute([$title, $durationMinutes, $labels, $position]);
                        }
                    }
                }
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, urgent, important, position) VALUES (?, ?, ?, 1, 1, ?)');
                return $stmt->execute([$title, $durationMinutes, $labels, $position]);
            } catch (PDOException $e2) {
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title) VALUES (?)');
                return $stmt->execute([$title]);
            }
        }
    }

    /**
     * Crea una tarea con duración asociada a un proyecto específico.
     * Usa el usuario actual de la sesión y calcula cuadrante/posición similar a otros creados.
     */
    public function createTaskWithDurationForProject(string $title, ?int $durationMinutes, ?string $labels, ?int $projectId): bool {
        $urgent = 0;
        $important = 0;
        if ($durationMinutes === 2) {
            $urgent = 1; $important = 1;
        } else if ($labels) {
            $labelArray = explode(',', $labels);
            if (in_array('Urgente', $labelArray)) $urgent = 1;
            if (in_array('Importante', $labelArray)) $important = 1;
        }
        $position = $this->nextPositionForQuadrant($urgent, $important);
        $userId = $this->getCurrentUserId();
        try {
            $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, project_id, urgent, important, position, user_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
            return $stmt->execute([$title, $durationMinutes, $labels, $projectId, $urgent, $important, $position, $userId]);
        } catch (PDOException $e) {
            try {
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, duration_minutes, labels, project_id, urgent, important, position) VALUES (?, ?, ?, ?, ?, ?, ?)');
                return $stmt->execute([$title, $durationMinutes, $labels, $projectId, $urgent, $important, $position]);
            } catch (PDOException $e2) {
                $stmt = $this->pdo->prepare('INSERT INTO tasks (title, project_id) VALUES (?, ?)');
                return $stmt->execute([$title, $projectId]);
            }
        }
    }

    // setters de asignaciones
    public function setResponsible(int $taskId, int $userId): bool {
        // Campo 'responsible' eliminado; método conservado por compatibilidad.
        return true;
    }

    public function clearResponsible(int $taskId): bool {
        // Campo 'responsible' eliminado; método conservado por compatibilidad.
        return true;
    }

    public function setGuests(int $taskId, array $userIds): bool {
        $guests = implode(',', $userIds);
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET guests = ? WHERE id = ?');
            return $stmt->execute([$guests, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    // Actualiza el usuario responsable (tasks.user_id)
    public function setUser(int $taskId, ?int $userId): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET user_id = ? WHERE id = ?');
            return $stmt->execute([$userId, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function listDistributor(): array {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT t.*, p.name AS project_name
                    FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.in_distributor = 1
                      AND (t.status IS NULL OR t.status <> "done")
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )
                    ORDER BY t.created_at ASC';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    /**
     * Lista acciones sin proyecto (project_id IS NULL), sin importar si están en la bandeja o en cuadrantes.
     * Mantiene el cuadrante original y aplica control de acceso por tarea.
     */
    public function listUnassigned(): array {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT t.*
                    FROM tasks t
                    WHERE t.project_id IS NULL
                      AND (t.status IS NULL OR t.status <> "done")
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                      )
                    ORDER BY t.created_at ASC';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function listByQuadrant(int $quadrant, bool $onlyQuick = false): array {
        $map = [1 => [1,1], 2 => [0,1], 3 => [1,0], 4 => [0,0]];
        [$urgent, $important] = $map[$quadrant] ?? [0,0];
        $userId = $this->getCurrentUserId();
        try {
            $params = [$urgent, $important];
            $sql = 'SELECT t.*, p.name AS project_name
                    FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.urgent = ? AND t.important = ? AND t.in_distributor = 0 AND (t.status IS NULL OR t.status <> "done")
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )';
            if ($quadrant === 1) { $sql .= ' AND (t.labels IS NULL OR LOWER(t.labels) NOT LIKE "%adicional%")'; }
            if ($onlyQuick) { $sql .= ' AND t.duration_minutes = 2'; }
            $sql .= ' ORDER BY CASE WHEN t.duration_minutes=2 THEN 0 ELSE 1 END, COALESCE(t.position, t.created_at) ASC';
            $params = array_merge($params, [$userId, $userId, $userId, $userId, $userId, $userId]);
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function setQuadrant(int $taskId, int $quadrant): bool {
        // Verificar si el cuadrante puede recibir más tareas
        if (!$this->canAddToQuadrant($quadrant)) {
            return false;
        }
        
        $map = [1 => [1,1], 2 => [0,1], 3 => [1,0], 4 => [0,0]];
        [$urgent, $important] = $map[$quadrant] ?? [0,0];
        $position = $this->nextPositionForQuadrant($urgent,$important);
        $stmt = $this->pdo->prepare('UPDATE tasks SET urgent = ?, important = ?, position = ?, in_distributor = 0 WHERE id = ?'); // Set in_distributor to 0
        return $stmt->execute([$urgent, $important, $position, $taskId]);
    }

    public function listMits(): array {
        // MITs para la agenda de hoy (con control de acceso)
        $userId = $this->getCurrentUserId();
        try {
            $sql = 'SELECT t.*, p.name AS project_name
                    FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.mit = 1
                      AND t.in_distributor = 0
                      AND t.urgent = 1 AND t.important = 1
                      AND (t.due_at IS NULL OR DATE(t.due_at) = CURDATE())
                      AND (t.status IS NULL OR t.status <> "done" OR DATE(t.completed_at) = CURDATE())
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )
                    ORDER BY t.created_at ASC';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function listQ1Tasks(): array {
        // Mostrar solo tareas de hoy en Q1, ocultando las completadas en días anteriores (con control de acceso)
        $userId = $this->getCurrentUserId();
        try {
            $sql = 'SELECT t.*, p.name AS project_name
                    FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.urgent = 1 AND t.important = 1 AND t.in_distributor = 0
                      AND (
                            t.status IS NULL OR t.status <> "done" OR DATE(t.completed_at) = CURDATE()
                          )
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )
                    ORDER BY CASE WHEN t.duration_minutes=2 THEN 0 ELSE 1 END,
                             COALESCE(t.position, t.created_at) ASC';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function listScheduledBetween(string $start, string $end): array {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT t.id, t.title, t.due_at, t.mit, t.status, p.name AS project_name
                    FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.due_at IS NOT NULL AND DATE(t.due_at) BETWEEN ? AND ?
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )
                    ORDER BY t.due_at ASC';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$start, $end, $userId, $userId, $userId, $userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function countDoneToday(): int {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT COUNT(*) FROM tasks t
                    WHERE DATE(t.completed_at) = CURDATE()
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND EXISTS (
                                SELECT 1 FROM projects p WHERE p.id = t.project_id AND (
                                    p.user_id = ?
                                    OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                    OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                                )
                            )
                        )
                      )';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId]);
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) { return 0; }
    }

    public function countDoneTodayQ1(): int {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT COUNT(*) FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.urgent = 1 AND t.important = 1 AND t.in_distributor = 0 AND t.status = "done" AND DATE(t.completed_at) = CURDATE()
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId]);
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) { return 0; }
    }

    public function countPendingQ1(): int {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT COUNT(*) FROM tasks t
                    LEFT JOIN projects p ON p.id = t.project_id
                    WHERE t.urgent = 1 AND t.important = 1 AND t.in_distributor = 0 AND (t.status IS NULL OR t.status != "done")
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        OR (
                            t.project_id IS NOT NULL AND p.id = t.project_id AND (
                                p.user_id = ?
                                OR FIND_IN_SET(?, COALESCE(p.guests, "")) > 0
                                OR EXISTS (SELECT 1 FROM project_invites pi WHERE pi.project_id = p.id AND pi.user_id = ?)
                            )
                        )
                      )';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId]);
            return (int)$stmt->fetchColumn();
        } catch (PDOException $e) { return 0; }
    }

    public function listQuickTasks(): array {
        try {
            $userId = $this->getCurrentUserId();
            $sql = 'SELECT t.* FROM tasks t
                    WHERE t.duration_minutes = 2
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                      )
                    ORDER BY t.created_at DESC';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function basicStats(): array {
        try {
            $userId = $this->getCurrentUserId();
            $sqlBase = 'FROM tasks t WHERE (
                            t.user_id = ?
                            OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                            OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                        )';
            $stmt = $this->pdo->prepare('SELECT COUNT(*) ' . $sqlBase);
            $stmt->execute([$userId, $userId, $userId]);
            $total = (int)$stmt->fetchColumn();

            $stmt = $this->pdo->prepare("SELECT COUNT(*) " . $sqlBase . " AND t.status = 'done'");
            $stmt->execute([$userId, $userId, $userId]);
            $done = (int)$stmt->fetchColumn();

            $stmt = $this->pdo->prepare('SELECT COUNT(*) ' . $sqlBase . ' AND t.mit = 1');
            $stmt->execute([$userId, $userId, $userId]);
            $deep = (int)$stmt->fetchColumn();

            return ['total' => $total, 'done' => $done, 'done_percent' => $total ? round($done*100/$total) : 0, 'mits' => $deep];
        } catch (PDOException $e) {
            return ['total' => 0, 'done' => 0, 'done_percent' => 0, 'mits' => 0];
        }
    }

    public function searchTasks(string $q): array {
        try {
            $userId = $this->getCurrentUserId();
            $like = '%' . $q . '%';
            $sql = 'SELECT t.*
                    FROM tasks t
                    WHERE (t.title LIKE ? OR t.labels LIKE ? OR t.status = ?)
                      AND (
                        t.user_id = ?
                        OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                        OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                      )
                    ORDER BY t.created_at DESC
                    LIMIT 50';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$like, $like, $q, $userId, $userId, $userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            try {
                // Fallback without labels column
                $stmt = $this->pdo->prepare('SELECT * FROM tasks WHERE title LIKE ? OR status = ? ORDER BY created_at DESC LIMIT 50');
                $stmt->execute(['%' . $q . '%', $q]);
                return $stmt->fetchAll(PDO::FETCH_ASSOC);
            } catch (PDOException $e2) { return []; }
        }
    }

    public function listByProject(int $projectId): array {
        try {
            $userId = $this->getCurrentUserId();

            // Obtener el responsable del proyecto
            $projectModel = new ProjectModel();
            $project = $projectModel->findById($projectId);
            $projectResponsibleId = isset($project['user_id']) ? (int)$project['user_id'] : null;

            // Si el usuario actual es el responsable del proyecto, puede ver todas las tareas
            if ($userId === $projectResponsibleId) {
                $sql = 'SELECT t.*
                        FROM tasks t
                        WHERE t.project_id = ?
                        ORDER BY t.position ASC, t.created_at ASC';
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([$projectId]);
            } else {
                // De lo contrario, solo ve las tareas donde es responsable o invitado
                $sql = 'SELECT t.*
                        FROM tasks t
                        WHERE t.project_id = ?
                          AND (
                            t.user_id = ?
                            OR FIND_IN_SET(?, COALESCE(t.guests, "")) > 0
                            OR EXISTS (SELECT 1 FROM task_invites ti WHERE ti.task_id = t.id AND ti.user_id = ?)
                          )
                        ORDER BY t.position ASC, t.created_at ASC';
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([$projectId, $userId, $userId, $userId]);
            }

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) { return []; }
    }

    public function setProject(int $taskId, ?int $projectId): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET project_id = ? WHERE id = ?');
            return $stmt->execute([$projectId, $taskId]);
        } catch (PDOException $e) {
            return false;
        }
    }

    public function markDone(int $taskId): bool {
        try {
            $stmt = $this->pdo->prepare("UPDATE tasks SET status='done', completed_at = NOW() WHERE id = ?");
            return $stmt->execute([$taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function markAsPending(int $taskId): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET status = ?, completed_at = NULL WHERE id = ?');
            return $stmt->execute(['todo', $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function toggleMit(int $taskId): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET mit = NOT mit WHERE id = ?');
            return $stmt->execute([$taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function planTask(int $taskId, ?string $date): bool {
        try {
            $dueAt = $date ? ($date . ' 00:00:00') : null;
            if ($dueAt === null) {
                $stmt = $this->pdo->prepare('UPDATE tasks SET due_at = NULL WHERE id = ?');
                return $stmt->execute([$taskId]);
            }
            $stmt = $this->pdo->prepare('UPDATE tasks SET due_at = ? WHERE id = ?');
            return $stmt->execute([$dueAt, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function setDueDate(int $taskId, ?string $dueAt): bool {
        try {
            // Normalizar valores comunes de entrada para due_at
            if ($dueAt !== null) {
                $trim = trim((string)$dueAt);
                if ($trim === '' || strtolower($trim) === 'null' || $trim === '0000-00-00 00:00:00') {
                    $dueAt = null;
                } else {
                    // Aceptar formato 'YYYY-MM-DDTHH:MM' convirtiéndolo a 'YYYY-MM-DD HH:MM:SS'
                    if (strpos($trim, 'T') !== false && strlen($trim) >= 16) {
                        $ts = str_replace('T', ' ', substr($trim, 0, 16)) . ':00';
                        $dueAt = $ts;
                    }
                }
            }

            $this->pdo->beginTransaction();

            $stmt = $this->pdo->prepare('UPDATE tasks SET due_at = ? WHERE id = ?');
            $stmt->execute([$dueAt, $taskId]);

            if ($dueAt !== null) {
                // Si la fecha planificada NO es hoy, mover automáticamente a Q2 (Programar)
                $dateOnly = date('Y-m-d', strtotime($dueAt));
                $today = date('Y-m-d');
                if ($dateOnly !== $today) {
                    $position = $this->nextPositionForQuadrant(0, 1);
                    $upd = $this->pdo->prepare('UPDATE tasks SET urgent = 0, important = 1, in_distributor = 0, position = ? WHERE id = ?');
                    $upd->execute([$position, $taskId]);
                }
            }

            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            if ($this->pdo->inTransaction()) { $this->pdo->rollBack(); }
            return false;
        }
    }

    public function assignToProject(int $taskId, int $projectId): bool {
        try {
            // Asignar a proyecto sin alterar cuadrante ni bandeja
            $stmt = $this->pdo->prepare('UPDATE tasks SET project_id = ? WHERE id = ?');
            return $stmt->execute([$projectId, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function getTaskById(int $taskId): ?array {
        try {
            $stmt = $this->pdo->prepare('SELECT * FROM tasks WHERE id = ?');
            $stmt->execute([$taskId]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result ?: null;
        } catch (PDOException $e) { return null; }
    }

    public function updateTask(int $taskId, string $title, ?int $durationMinutes, ?string $labels, ?string $dueAt, int $mit): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET title = ?, duration_minutes = ?, labels = ?, due_at = ?, mit = ?, updated_at = NOW() WHERE id = ?');
            return $stmt->execute([$title, $durationMinutes, $labels, $dueAt, $mit, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function deleteTask(int $taskId): bool {
        try {
            $stmt = $this->pdo->prepare('DELETE FROM tasks WHERE id = ?');
            return $stmt->execute([$taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function updateTaskPositions(int $quadrant, array $positions): bool {
        try {
            // Mapear cuadrante a valores urgent/important
            $map = [1 => [1,1], 2 => [0,1], 3 => [1,0], 4 => [0,0]];
            [$urgent, $important] = $map[$quadrant] ?? [0,0];
            
            // Iniciar transacción
            $this->pdo->beginTransaction();
            
            // Actualizar cada posición
            foreach ($positions as $pos) {
                $taskId = intval($pos['task_id']);
                $position = intval($pos['position']);
                
                $stmt = $this->pdo->prepare('UPDATE tasks SET position = ? WHERE id = ? AND urgent = ? AND important = ?');
                $stmt->execute([$position, $taskId, $urgent, $important]);
            }
            
            // Confirmar transacción
            $this->pdo->commit();
            return true;
            
        } catch (PDOException $e) {
            // Revertir transacción en caso de error
            $this->pdo->rollBack();
            return false;
        }
    }

    /**
     * Actualiza el orden de tareas dentro de un proyecto específico.
     * orderedTaskIds debe ser un array de IDs en el nuevo orden (1..n)
     */
    public function updateProjectTaskOrder(int $projectId, array $orderedTaskIds): bool {
        try {
            $this->pdo->beginTransaction();
            $position = 1;
            $stmt = $this->pdo->prepare('UPDATE tasks SET position = ? WHERE id = ? AND project_id = ?');
            foreach ($orderedTaskIds as $taskId) {
                $taskId = (int)$taskId;
                if ($taskId <= 0) continue;
                $stmt->execute([$position++, $taskId, $projectId]);
            }
            $this->pdo->commit();
            return true;
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }
    }

    public function markAsDone(int $taskId): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET status = ?, completed_at = NOW() WHERE id = ?');
            return $stmt->execute(['done', $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function updateStatus(int $taskId, string $status): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET status = ? WHERE id = ?');
            return $stmt->execute([$status, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function updateNotes(int $taskId, string $notes): bool {
        try {
            $stmt = $this->pdo->prepare('UPDATE tasks SET notes = ? WHERE id = ?');
            return $stmt->execute([$notes, $taskId]);
        } catch (PDOException $e) { return false; }
    }

    public function getQuadrant(int $taskId): int {
        try {
            $stmt = $this->pdo->prepare('SELECT urgent, important FROM tasks WHERE id = ?');
            $stmt->execute([$taskId]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$result) return 0;
            
            $urgent = (int)$result['urgent'];
            $important = (int)$result['important'];
            
            // Mapear a cuadrante
            if ($urgent && $important) return 1;
            if (!$urgent && $important) return 2;
            if ($urgent && !$important) return 3;
            if (!$urgent && !$important) return 4;
            
            return 0;
        } catch (PDOException $e) { return 0; }
    }
    
    /**
     * Promueve automáticamente tareas de Q2 (Programar) a Q1 (Hacer)
     * cuando su fecha due_at es hoy o anterior. Respeta el límite de 5 en Q1.
     * Devuelve la cantidad de tareas promovidas.
     */
    public function promoteDueQ2TasksToQ1(): int {
        try {
            // Capacidad disponible en Q1
            $currentQ1 = $this->countByQuadrant(1);
            $available = 5 - $currentQ1;
            if ($available <= 0) return 0;

            // Seleccionar elegibles en Q2 con due_at = hoy y no completadas
            if ($this->isAdmin()) {
                $sql = 'SELECT id FROM tasks
                        WHERE urgent = 0 AND important = 1 AND in_distributor = 0
                          AND (status IS NULL OR status != "done")
                          AND due_at IS NOT NULL
                          AND DATE(due_at) = CURDATE()
                        ORDER BY due_at ASC, created_at ASC
                        LIMIT ' . (int)$available;
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute();
            } else {
                $userId = $this->getCurrentUserId();
                $sql = 'SELECT id FROM tasks
                        WHERE urgent = 0 AND important = 1 AND in_distributor = 0
                          AND (status IS NULL OR status != "done")
                          AND due_at IS NOT NULL
                          AND DATE(due_at) = CURDATE()
                          AND user_id = ?
                        ORDER BY due_at ASC, created_at ASC
                        LIMIT ' . (int)$available;
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([$userId]);
            }
            $ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
            if (!$ids) return 0;

            $this->pdo->beginTransaction();
            $updated = 0;
            $upd = $this->pdo->prepare('UPDATE tasks SET urgent = 1, important = 1, in_distributor = 0, position = ? WHERE id = ?');
            // Obtener posición base una sola vez y luego incrementar localmente para evitar colisiones
            $nextPos = $this->nextPositionForQuadrant(1, 1);
            foreach ($ids as $taskId) {
                $upd->execute([$nextPos++, (int)$taskId]);
                $updated++;
            }
            $this->pdo->commit();
            return $updated;
        } catch (PDOException $e) {
            if ($this->pdo->inTransaction()) { $this->pdo->rollBack(); }
            return 0;
        }
    }

    /**
     * Envía a la bandeja de distribución todas las tareas que están en Q1 pero sin fecha (due_at NULL o 0000-00-00 00:00:00).
     * No afecta tareas completadas. Devuelve la cantidad de filas actualizadas.
     */
    public function moveUndatedQ1ToDistributor(): int {
        try {
            if ($this->isAdmin()) {
                $sql = 'UPDATE tasks
                        SET urgent = 0,
                            important = 0,
                            position = NULL,
                            in_distributor = 1
                        WHERE urgent = 1 AND important = 1 AND in_distributor = 0
                          AND (due_at IS NULL OR due_at = "0000-00-00 00:00:00")
                          AND (duration_minutes IS NULL OR duration_minutes <> 2)
                          AND (status IS NULL OR status != "done")';
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute();
                return (int)$stmt->rowCount();
            }
            $userId = $this->getCurrentUserId();
            $sql = 'UPDATE tasks
                    SET urgent = 0,
                        important = 0,
                        position = NULL,
                        in_distributor = 1
                    WHERE urgent = 1 AND important = 1 AND in_distributor = 0
                      AND (due_at IS NULL OR due_at = "0000-00-00 00:00:00")
                      AND (duration_minutes IS NULL OR duration_minutes <> 2)
                      AND (status IS NULL OR status != "done")
                      AND user_id = ?';
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId]);
            return (int)$stmt->rowCount();
        } catch (PDOException $e) {
            return 0;
        }
    }
}
User / IP	: 216.73.216.159
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64