<?php
/**
 * Helper class for taskmassivas plugin
 * Handles database operations for bulk task creation
 *
 * Formato del template:
 * año | cliente | codigo del proyecto - sie | tipo | descripción | enero | febrero | ... | diciembre
 */

use dokuwiki\Extension\Plugin;

class helper_plugin_taskmassivas extends Plugin {

    protected $db = null;
    protected $dbInitialized = false;
    protected $userExistsCache = [];

    /**
     * Get database connection (same as BEZ plugin)
     */
    public function getDB() {
        // Si ya se inicializó, retornar el resultado cacheado
        if ($this->dbInitialized) {
            return $this->db;
        }

        $this->dbInitialized = true;

        // Use sqlite plugin directly (same as BEZ)
        $sqlite = plugin_load('helper', 'sqlite');
        if (!$sqlite) {
            $this->db = null;
            return null;
        }

        $pluginDir = DOKU_PLUGIN . 'bez/db/';
        $result = $sqlite->init('b3p', $pluginDir);

        // Si init() retorna false, guardamos null; si no, guardamos el objeto $sqlite
        $this->db = ($result === false) ? null : $sqlite;

        return $this->db;
    }

    /**
     * Get all open projects (threads)
     */
    public function getProjects() {
        $db = $this->getDB();
        if (!$db) return [];

        $sql = "SELECT t.id, t.title, l.name as client_name
                FROM thread t
                LEFT JOIN thread_label tl ON t.id = tl.thread_id
                LEFT JOIN label l ON tl.label_id = l.id
                WHERE t.state = 'opened'
                AND (t.deleted_at IS NULL OR t.deleted_at = '')
                ORDER BY t.id DESC";

        $result = $db->query($sql);
        return $db->res2arr($result) ?: [];
    }

    /**
     * Get all projects (open and closed) for matching by code
     */
    public function getAllProjects() {
        $db = $this->getDB();
        if (!$db) return [];

        $sql = "SELECT t.id, t.title, l.name as client_name, l.nit as client_nit
                FROM thread t
                LEFT JOIN thread_label tl ON t.id = tl.thread_id
                LEFT JOIN label l ON tl.label_id = l.id
                WHERE (t.deleted_at IS NULL OR t.deleted_at = '')
                ORDER BY t.id DESC";

        $result = $db->query($sql);
        return $db->res2arr($result) ?: [];
    }

    /**
     * Get project by ID
     */
    public function getProject($projectId) {
        $db = $this->getDB();
        if (!$db) return null;

        $sql = "SELECT * FROM thread WHERE id = ?";
        $result = $db->query($sql, $projectId);
        return $db->res2row($result);
    }

    /**
     * Find project by code (ID number) or by title (fallback)
     * Returns project data including client name, NIT and verification digit (DV)
     */
    public function findProjectByCode($code) {
        $db = $this->getDB();
        if (!$db) return null;

        $code = trim($code);
        if (empty($code)) return null;

        // 1. Intentar por ID numérico (extraer dígitos de "SIE-001" o "1")
        $numericId = preg_replace('/[^0-9]/', '', $code);
        if (!empty($numericId)) {
            $sql = "SELECT t.*, l.name as client_name, l.nit as client_nit, l.codigo_verificacion as client_dv
                    FROM thread t
                    LEFT JOIN thread_label tl ON t.id = tl.thread_id
                    LEFT JOIN label l ON tl.label_id = l.id
                    WHERE t.id = ? AND (t.deleted_at IS NULL OR t.deleted_at = '')";
            $result = $db->query($sql, intval($numericId));
            $row = $db->res2row($result);
            if ($row) return $row;
        }

        // 2. Fallback: buscar por título del proyecto (match exacto primero)
        $sql = "SELECT t.*, l.name as client_name, l.nit as client_nit, l.codigo_verificacion as client_dv
                FROM thread t
                LEFT JOIN thread_label tl ON t.id = tl.thread_id
                LEFT JOIN label l ON tl.label_id = l.id
                WHERE LOWER(t.title) = LOWER(?) AND (t.deleted_at IS NULL OR t.deleted_at = '')";
        $result = $db->query($sql, $code);
        $row = $db->res2row($result);
        if ($row) return $row;

        // 3. Fallback: buscar por título parcial (solo si el código tiene 4+ caracteres para evitar falsos positivos)
        if (mb_strlen($code) >= 4) {
            $escapedCode = str_replace(['%', '_'], ['\\%', '\\_'], $code);
            $sql = "SELECT t.*, l.name as client_name, l.nit as client_nit, l.codigo_verificacion as client_dv
                    FROM thread t
                    LEFT JOIN thread_label tl ON t.id = tl.thread_id
                    LEFT JOIN label l ON tl.label_id = l.id
                    WHERE LOWER(t.title) LIKE LOWER(?) ESCAPE '\\'
                    AND (t.deleted_at IS NULL OR t.deleted_at = '')
                    LIMIT 1";
            $result = $db->query($sql, '%' . $escapedCode . '%');
            return $db->res2row($result);
        }

        return null;
    }

    /**
     * Get or create task program by name
     * Usa INSERT OR IGNORE para evitar race conditions con concurrencia
     */
    public function getOrCreateTaskProgram($programName, $userId) {
        $db = $this->getDB();
        if (!$db) return null;

        $programName = trim($programName);
        if (empty($programName)) {
            return null;
        }

        // INSERT OR IGNORE evita race condition: si otro proceso insertó
        // entre el SELECT y el INSERT, simplemente ignora el duplicado
        $now = date('Y-m-d H:i:s');
        $db->query("INSERT OR IGNORE INTO task_program (name, count, added_by, added_date) VALUES (?, 0, ?, ?)",
            $programName, $userId, $now);

        // Siempre recuperar por nombre (funciona tanto si se insertó como si ya existía)
        $result = $db->query("SELECT id FROM task_program WHERE name = ?", $programName);
        $row = $db->res2row($result);
        return $row ? $row['id'] : null;
    }

    /**
     * Get all task programs
     */
    public function getTaskPrograms() {
        $db = $this->getDB();
        if (!$db) return [];

        $sql = "SELECT id, name FROM task_program ORDER BY name";
        $result = $db->query($sql);
        return $db->res2arr($result) ?: [];
    }

    /**
     * Get all clients (labels)
     */
    public function getClients() {
        $db = $this->getDB();
        if (!$db) return [];

        $sql = "SELECT id, name, nit FROM label ORDER BY name";
        $result = $db->query($sql);
        return $db->res2arr($result) ?: [];
    }

    /**
     * Find client by name (partial match)
     */
    public function findClientByName($name) {
        $db = $this->getDB();
        if (!$db) return null;

        $name = trim($name);
        if (empty($name)) return null;

        // Try exact match first
        $sql = "SELECT id, name, nit FROM label WHERE LOWER(name) = LOWER(?)";
        $result = $db->query($sql, $name);
        $row = $db->res2row($result);
        if ($row) return $row;

        // Try partial match (escapar caracteres especiales de LIKE)
        $escapedName = str_replace(['%', '_'], ['\\%', '\\_'], $name);
        $sql = "SELECT id, name, nit FROM label WHERE LOWER(name) LIKE LOWER(?) ESCAPE '\\'";
        $result = $db->query($sql, '%' . $escapedName . '%');
        return $db->res2row($result);
    }

    /**
     * Check if user exists (con cache para evitar N llamadas al auth backend)
     */
    public function userExists($username) {
        global $auth;
        if (!$auth) return false;

        $username = trim($username);
        if (empty($username)) return false;

        // Cache: evita llamar getUserData() 12 veces por la misma fila
        if (isset($this->userExistsCache[$username])) {
            return $this->userExistsCache[$username];
        }

        $user = $auth->getUserData($username);
        $exists = !empty($user);
        $this->userExistsCache[$username] = $exists;

        return $exists;
    }

    /**
     * Get all users from DokuWiki auth
     * Returns array with username and full name
     */
    public function getUsers() {
        global $auth;
        if (!$auth) return [];

        $users = [];

        // Try to get users using retrieveUsers method (works with authplain)
        if (method_exists($auth, 'retrieveUsers')) {
            $start = 0;
            $limit = 0; // 0 = all users
            $filter = [];

            $userList = $auth->retrieveUsers($start, $limit, $filter);

            if (!empty($userList)) {
                foreach ($userList as $username => $userData) {
                    $users[] = [
                        'username' => $username,
                        'name' => isset($userData['name']) ? $userData['name'] : $username,
                        'mail' => isset($userData['mail']) ? $userData['mail'] : ''
                    ];
                }
            }
        }

        // Sort by username
        usort($users, function($a, $b) {
            return strcasecmp($a['username'], $b['username']);
        });

        return $users;
    }

    /**
     * Create a task in BEZ
     * Usa transacción para garantizar atomicidad (task + participants + program count)
     */
    public function createTask($data, $userId) {
        $db = $this->getDB();
        if (!$db) return null;

        $now = date('Y-m-d H:i:s');

        // Prepare content HTML (fuera de transacción, es read-only)
        $contentHtml = $this->renderWikiToHtml($data['content']);

        // Get or create program if specified (fuera de transacción para evitar deadlock)
        $programId = null;
        if (!empty($data['program'])) {
            $programId = $this->getOrCreateTaskProgram($data['program'], $userId);
        }

        // Determine assignee
        $assignee = !empty($data['assignee']) ? $data['assignee'] : $userId;

        // Determine type
        $validTypes = ['correction', 'corrective', 'preventive', 'program', 'critical', 'critica'];
        $type = in_array($data['type'], $validTypes) ? $data['type'] : 'correction';
        if ($type === 'critica') $type = 'critical';

        // === TRANSACCIÓN: task + participants + program count ===
        $db->query("BEGIN IMMEDIATE");

        try {
            $sql = "INSERT INTO task (
                thread_id, content, content_html, plan_date, assignee, type, state,
                original_poster, create_date, last_activity_date, last_modification_date,
                all_day_event, private, lock, task_program_id, source
            ) VALUES (?, ?, ?, ?, ?, ?, 'opened', ?, ?, ?, ?, 1, 0, 0, ?, 'bulk')";

            $db->query($sql,
                $data['project_id'],
                $data['content'],
                $contentHtml,
                $data['plan_date'],
                $assignee,
                $type,
                $userId,
                $now,
                $now,
                $now,
                $programId
            );

            // Obtener ID inmediatamente después del INSERT (dentro de la transacción)
            $result = $db->query("SELECT last_insert_rowid() as id");
            $row = $db->res2row($result);
            $taskId = $row ? $row['id'] : null;

            if (!$taskId) {
                $db->query("ROLLBACK");
                return null;
            }

            // Add participants
            $this->addTaskParticipant($taskId, $userId, true, ($assignee == $userId));
            if ($assignee != $userId) {
                $this->addTaskParticipant($taskId, $assignee, false, true);
            }

            // Update program count
            if ($programId) {
                $db->query("UPDATE task_program SET count = count + 1 WHERE id = ?", $programId);
            }

            $db->query("COMMIT");
            return $taskId;

        } catch (\Exception $e) {
            $db->query("ROLLBACK");
            return null;
        }
    }

    /**
     * Add task participant
     */
    protected function addTaskParticipant($taskId, $participantUserId, $isOriginalPoster, $isAssignee) {
        $db = $this->getDB();
        if (!$db) return;

        $now = date('Y-m-d H:i:s');
        $sql = "INSERT OR REPLACE INTO task_participant
                (task_id, user_id, original_poster, assignee, commentator, subscribent, added_by, added_date)
                VALUES (?, ?, ?, ?, 0, 1, ?, ?)";

        $db->query($sql,
            $taskId,
            $participantUserId,
            $isOriginalPoster ? 1 : 0,
            $isAssignee ? 1 : 0,
            $participantUserId,
            $now
        );
    }

    /**
     * Render wiki syntax to HTML
     * Con fallback robusto si el parser DokuWiki no está disponible
     */
    protected function renderWikiToHtml($wikiText) {
        if (function_exists('p_render') && function_exists('p_get_instructions')) {
            try {
                $instructions = p_get_instructions($wikiText);
                if ($instructions) {
                    $info = [];
                    $html = p_render('xhtml', $instructions, $info);
                    if (!empty($html)) {
                        return $html;
                    }
                }
            } catch (\Exception $e) {
                // Fallback silencioso
            }
        }
        // Fallback: convertir texto plano a HTML básico
        return '<p>' . nl2br(htmlspecialchars($wikiText)) . '</p>';
    }

    /**
     * Log bulk upload
     * @param int $projectId Project ID (0 for multiple)
     * @param string $filename Original filename
     * @param int $tasksCreated Number of tasks created
     * @param int $tasksSkipped Number of tasks skipped
     * @param int $errors Number of errors
     * @param string $userId User who uploaded
     * @param array $taskIds Array of created task IDs
     * @return int|null Log entry ID
     */
    public function logUpload($projectId, $filename, $tasksCreated, $tasksSkipped, $errors, $userId, $taskIds = []) {
        $db = $this->getDB();
        if (!$db) return null;

        // Ensure table exists
        $this->ensureLogTable();

        $now = date('Y-m-d H:i:s');
        $taskIdsJson = !empty($taskIds) ? json_encode($taskIds) : null;

        $sql = "INSERT INTO bulk_task_log (project_id, filename, tasks_created, tasks_skipped, errors, uploaded_by, uploaded_at, task_ids)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        $db->query($sql,
            $projectId,
            $filename,
            $tasksCreated,
            $tasksSkipped,
            $errors,
            $userId,
            $now,
            $taskIdsJson
        );

        // Get the inserted ID
        $result = $db->query("SELECT last_insert_rowid() as id");
        $row = $db->res2row($result);
        return $row ? $row['id'] : null;
    }

    /**
     * Get upload logs
     */
    public function getUploadLogs($limit = 50) {
        $db = $this->getDB();
        if (!$db) return [];

        $this->ensureLogTable();

        $sql = "SELECT l.*, t.title as project_title
                FROM bulk_task_log l
                LEFT JOIN thread t ON l.project_id = t.id
                ORDER BY l.uploaded_at DESC
                LIMIT ?";

        $result = $db->query($sql, $limit);
        return $db->res2arr($result) ?: [];
    }

    /**
     * Ensure log table exists with all columns
     * Se ejecuta una sola vez por request gracias al flag
     */
    protected $logTableEnsured = false;

    protected function ensureLogTable() {
        // Evitar ejecutar CREATE TABLE + PRAGMA en cada llamada
        if ($this->logTableEnsured) return;
        $this->logTableEnsured = true;

        $db = $this->getDB();
        if (!$db) return;

        $db->query("CREATE TABLE IF NOT EXISTS bulk_task_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_id INTEGER,
            filename TEXT NOT NULL,
            tasks_created INTEGER DEFAULT 0,
            tasks_skipped INTEGER DEFAULT 0,
            errors INTEGER DEFAULT 0,
            uploaded_by TEXT NOT NULL,
            uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            task_ids TEXT DEFAULT NULL,
            reverted INTEGER DEFAULT 0,
            reverted_at DATETIME DEFAULT NULL,
            reverted_by TEXT DEFAULT NULL
        )");

        // Verificar columnas existentes para agregar las que falten
        $existingCols = [];
        try {
            $result = $db->query("PRAGMA table_info(bulk_task_log)");
            $rows = $db->res2arr($result);
            foreach ($rows as $row) {
                $existingCols[] = $row['name'];
            }
        } catch (\Exception $e) {
            return; // Si PRAGMA falla, no intentar ALTER
        }

        // Columnas a agregar (hardcoded, no user input — seguro para interpolación)
        // Nota: SQLite no soporta parámetros en DDL (ALTER TABLE)
        $migrations = [
            ['task_ids',    'TEXT DEFAULT NULL'],
            ['reverted',    'INTEGER DEFAULT 0'],
            ['reverted_at', 'TEXT DEFAULT NULL'],
            ['reverted_by', 'TEXT DEFAULT NULL'],
        ];

        foreach ($migrations as $migration) {
            $colName = $migration[0];
            $colType = $migration[1];
            if (!in_array($colName, $existingCols)) {
                try {
                    // Valores hardcoded (no user input), seguro para concatenación
                    $db->query("ALTER TABLE bulk_task_log ADD COLUMN {$colName} {$colType}");
                } catch (\Exception $e) {
                    // Column might already exist
                }
            }
        }
    }

    /**
     * Clear old log entries (without task_ids or all)
     * @param bool $onlyWithoutIds Only delete entries without task_ids
     * @return int Number of deleted entries
     */
    public function clearLogs($onlyWithoutIds = true) {
        $db = $this->getDB();
        if (!$db) return 0;

        if ($onlyWithoutIds) {
            $sql = "DELETE FROM bulk_task_log WHERE task_ids IS NULL OR task_ids = ''";
        } else {
            $sql = "DELETE FROM bulk_task_log";
        }

        $db->query($sql);

        // Get affected rows (SQLite way)
        $result = $db->query("SELECT changes() as count");
        $row = $db->res2row($result);
        return $row ? $row['count'] : 0;
    }

    /**
     * Revert a bulk upload (delete created tasks)
     * @param int $logId Log entry ID
     * @param string $userId User performing the revert
     * @return array Result with success status and message
     */
    public function revertUpload($logId, $userId) {
        $db = $this->getDB();
        if (!$db) {
            return ['success' => false, 'message' => 'No se pudo conectar a la base de datos'];
        }

        // Get log entry
        $result = $db->query("SELECT * FROM bulk_task_log WHERE id = ?", $logId);
        $log = $db->res2row($result);

        if (!$log) {
            return ['success' => false, 'message' => 'Registro de carga no encontrado'];
        }

        if (!empty($log['reverted']) && $log['reverted'] == 1) {
            return ['success' => false, 'message' => 'Esta carga ya fue revertida'];
        }

        if (empty($log['task_ids'])) {
            return ['success' => false, 'message' => 'No hay IDs de tareas registrados para revertir'];
        }

        $taskIds = json_decode($log['task_ids'], true);
        if (empty($taskIds)) {
            return ['success' => false, 'message' => 'No se pudieron leer los IDs de tareas'];
        }

        // Soft delete tasks + decrementar contadores de programa
        $deleted = 0;
        $errors = 0;
        $skippedModified = 0;
        $programCounters = []; // program_id => cantidad a decrementar

        $db->query("BEGIN IMMEDIATE");
        try {
            foreach ($taskIds as $taskId) {
                try {
                    // Obtener datos de la tarea antes de marcar como eliminada
                    $taskResult = $db->query(
                        "SELECT task_program_id, state, create_date, last_modification_date FROM task WHERE id = ?",
                        $taskId
                    );
                    $taskRow = $db->res2row($taskResult);

                    // Solo revertir tareas que estén abiertas (no ya eliminadas)
                    if ($taskRow && $taskRow['state'] !== 'deleted') {
                        // Verificar si la tarea fue modificada después de ser creada
                        // (un usuario la editó manualmente — no borrar)
                        if (!empty($taskRow['last_modification_date']) &&
                            !empty($taskRow['create_date']) &&
                            $taskRow['last_modification_date'] > $taskRow['create_date']) {
                            $skippedModified++;
                            continue;
                        }

                        $db->query("UPDATE task SET state = 'deleted' WHERE id = ?", $taskId);
                        $deleted++;

                        // Acumular decrementos por programa
                        if (!empty($taskRow['task_program_id'])) {
                            $pid = $taskRow['task_program_id'];
                            $programCounters[$pid] = isset($programCounters[$pid]) ? $programCounters[$pid] + 1 : 1;
                        }
                    }
                } catch (\Exception $e) {
                    $errors++;
                }
            }

            // Decrementar contadores de programa (evitar negativos)
            foreach ($programCounters as $programId => $decrement) {
                $db->query("UPDATE task_program SET count = MAX(0, count - ?) WHERE id = ?", $decrement, $programId);
            }

            // Mark log as reverted (dentro de la transacción para consistencia)
            $now = date('Y-m-d H:i:s');
            $db->query("UPDATE bulk_task_log SET reverted = 1, reverted_at = ?, reverted_by = ? WHERE id = ?",
                $now, $userId, $logId);

            $db->query("COMMIT");
        } catch (\Exception $e) {
            $db->query("ROLLBACK");
            return ['success' => false, 'message' => 'Error durante la reversión: ' . $e->getMessage()];
        }

        $msg = "Se eliminaron {$deleted} tareas";
        if ($skippedModified > 0) {
            $msg .= " ({$skippedModified} tareas modificadas por usuarios fueron conservadas)";
        }
        if ($errors > 0) {
            $msg .= " ({$errors} errores)";
        }

        return [
            'success' => true,
            'message' => $msg,
            'deleted' => $deleted,
            'skipped_modified' => $skippedModified,
            'errors' => $errors
        ];
    }

    /**
     * Get a single upload log entry
     */
    public function getUploadLog($logId) {
        $db = $this->getDB();
        if (!$db) return null;

        $result = $db->query("SELECT * FROM bulk_task_log WHERE id = ?", $logId);
        return $db->res2row($result);
    }

    /**
     * Get month names in Spanish
     */
    public function getMonthNames() {
        return [
            1 => 'enero',
            2 => 'febrero',
            3 => 'marzo',
            4 => 'abril',
            5 => 'mayo',
            6 => 'junio',
            7 => 'julio',
            8 => 'agosto',
            9 => 'septiembre',
            10 => 'octubre',
            11 => 'noviembre',
            12 => 'diciembre'
        ];
    }

    /**
     * Normalize column name for matching
     */
    public function normalizeColumnName($name) {
        $name = mb_strtolower(trim($name));
        $name = preg_replace('/\s+/', ' ', $name);

        // Remove accents
        $accents = [
            'á' => 'a', 'é' => 'e', 'í' => 'i', 'ó' => 'o', 'ú' => 'u',
            'ñ' => 'n', 'ü' => 'u'
        ];
        $name = strtr($name, $accents);

        return $name;
    }

    /**
     * Parse date from various formats
     */
    public function parseDate($value, $year = null) {
        if (empty($value)) return null;

        $value = trim($value);

        // If already in correct format YYYY-MM-DD
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $value, $m)) {
            // Validar que la fecha es real (no 2026-02-30)
            if (checkdate(intval($m[2]), intval($m[3]), intval($m[1]))) {
                return $value;
            }
            return null;
        }

        // Try DD/MM/YYYY
        if (preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/', $value, $m)) {
            $d = intval($m[1]); $mo = intval($m[2]); $y = intval($m[3]);
            if (checkdate($mo, $d, $y)) {
                return sprintf('%04d-%02d-%02d', $y, $mo, $d);
            }
            return null;
        }

        // Try DD-MM-YYYY
        if (preg_match('/^(\d{1,2})-(\d{1,2})-(\d{4})$/', $value, $m)) {
            $d = intval($m[1]); $mo = intval($m[2]); $y = intval($m[3]);
            if (checkdate($mo, $d, $y)) {
                return sprintf('%04d-%02d-%02d', $y, $mo, $d);
            }
            return null;
        }

        // Try DD/MM (without year, use provided year or current)
        if (preg_match('/^(\d{1,2})\/(\d{1,2})$/', $value, $m)) {
            $useYear = $year ?: intval(date('Y'));
            $d = intval($m[1]); $mo = intval($m[2]);
            if (checkdate($mo, $d, $useYear)) {
                return sprintf('%04d-%02d-%02d', $useYear, $mo, $d);
            }
            return null;
        }

        // Try just day number (use month from context and year)
        if (preg_match('/^(\d{1,2})$/', $value, $m) && $year) {
            // This will be handled by the caller who knows the month context
            return $value; // Return as-is, caller will handle
        }

        // Try Excel serial date (numeric)
        // Excel tiene un bug histórico: trata 1900 como año bisiesto (serial 60 = 29-feb-1900)
        // Para fechas > serial 60, restar 1 día extra para compensar
        if (is_numeric($value) && $value > 40000 && $value < 60000) {
            $serial = floatval($value);
            $adjustedSerial = ($serial > 60) ? $serial - 1 : $serial;
            $unix = ($adjustedSerial - 25568) * 86400;
            $parsedDate = date('Y-m-d', $unix);
            // Validar que la fecha resultante es razonable (no shadow $year)
            $parsedYear = intval(date('Y', $unix));
            if ($parsedYear >= 2000 && $parsedYear <= 2100) {
                return $parsedDate;
            }
        }

        // Try strtotime (último recurso)
        $timestamp = strtotime($value);
        if ($timestamp !== false) {
            return date('Y-m-d', $timestamp);
        }

        return null;
    }

    /**
     * Build date from day, month, year
     */
    public function buildDate($day, $month, $year) {
        if (empty($day) || empty($month) || empty($year)) return null;

        $day = intval($day);
        $month = intval($month);
        $year = intval($year);

        if ($day < 1 || $day > 31 || $month < 1 || $month > 12) return null;

        // Validar que la fecha realmente existe en el calendario
        // (ej: 31 de febrero no es válido)
        if (!checkdate($month, $day, $year)) return null;

        return sprintf('%04d-%02d-%02d', $year, $month, $day);
    }

    /**
     * Validate task data
     */
    public function validateTaskData($data, $rowNum) {
        $errors = [];
        $warnings = [];

        // Required: content
        if (empty($data['content'])) {
            $errors[] = $this->getLang('error_no_content');
        }

        // Required: plan_date
        if (empty($data['plan_date'])) {
            $errors[] = $this->getLang('error_no_date');
        }

        // Required: project_id
        if (empty($data['project_id'])) {
            $errors[] = $this->getLang('error_no_project');
        }

        // Optional: assignee - validate if provided
        if (!empty($data['assignee']) && !$this->userExists($data['assignee'])) {
            $warnings[] = $this->getLang('error_user_not_found') . ': ' . $data['assignee'];
        }

        return [
            'valid' => empty($errors),
            'errors' => $errors,
            'warnings' => $warnings
        ];
    }
}
