<?php
/**
 * DokuWiki Plugin do (Helper Component)
 *
 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
 * @author  Andreas Gohr <gohr@cosmocode.de>
 * @author  Adrian Lang <lang@cosmocode.de>
 * @author  Dominik Eckelmann <eckelmann@cosmocode.de>
 */

// must be run within Dokuwiki
if (!defined('DOKU_INC')) {
    die();
}

class helper_plugin_do extends DokuWiki_Plugin
{

    /** @var helper_plugin_sqlite */
    private $db = null;
    private $db_b3p = null;

    /**
     * Constructor. Initializes the SQLite DB Connection
     */
    public function __construct()
    {
        $this->db = plugin_load('helper', 'sqlite');
        if (!$this->db) {
            msg('The do plugin requires the sqlite plugin. Please install it');
            return;
        }
        if (!$this->db->init('do', dirname(__FILE__) . '/db/')) {
            $this->db = null;
        }

        // Inicializamos la conexion de la base de datos "b3p".
        $this->db_b3p = plugin_load('helper', 'sqlite');
        if (!$this->db_b3p) {
            msg('Error al conectar la base de datos b3p en helper.');
            return;
        }
        
        if (!$this->db_b3p->init('b3p', dirname(dirname(__FILE__)). '/bez'.'/db/')) {
            $this->db_b3p = null;
        }

    }

    /**
     * Delete the all tasks from a given page id
     *
     * @param string $id page id
     */
    public function cleanPageTasks($id)
    {
        if (!$this->db) {
            return;
        }
        $this->db->query('DELETE FROM tasks WHERE page = ?', $id);
        $this->db->query('DELETE FROM task_assignees WHERE page = ?', $id);
    }

    /**
     * Save a task.
     *
     * @param array $data task informations as key value array.
     *                    keys are: page, md5, date, user, text, creator
     */
    public function saveTask($data)
    {
        if (!$this->db) {
            return;
        }

        $date = !empty($data['date']) ? $data['date'] : null;

        $this->db->query(
            'INSERT INTO tasks (page,md5,date,text,creator,pos)
             VALUES (?, ?, ?, ?, ?, ?)',
            $data['page'],
            $data['md5'],
            $date,
            $data['text'],
            $data['creator'],
            $data['pos']
        );
        foreach ((array)$data['users'] as $userName) {
            $this->db->query(
                'INSERT INTO task_assignees (page,md5,user)
                 VALUES (?,?,?)',
                $data['page'],
                $data['md5'],
                $userName
            );
        }
    }
    
    /**
     * Funcion encargada de guardar la cantidad de requerimientos de submodulos. R**G
     */
    public function actualizarProgresoProyecto($pageId)
    {
        $fechaActual = date('Y-m-d H:i:s');

        if (!$this->db_b3p || !$this->db) {
            return array();
        }

        // Consultamos el total de requerimientos del proyecto.
        $submodulos = $this->db->query(
            'SELECT count(1) FROM tasks WHERE page = ?',
            $pageId
        );
        $resultadoCantidad = $this->db->res2single($submodulos);

        // Actualizamos la informacion en la tabla Proyectos de BEZ.
        $this->db_b3p->query(
            'UPDATE progreso_proyecto 
                SET total_requerimientos = ?, 
                    requerimientos_terminados = CASE WHEN requerimientos_terminados IS NULL THEN 0 ELSE requerimientos_terminados END,
                    fecha_actualizacion = ?
                WHERE ruta_submodulo = ?',
            intval($resultadoCantidad),
            $fechaActual,
            $pageId
        );
    }

    /**
     * Funcion que actualiza el esta de completado o no de requerimientos. R**G
     */
    public function actualizarProgresoReq($id)
    {
        $fechaActual = date('Y-m-d H:i:s');
        $resCantidadTotal = 0;
        $resultadoCantidad = 0;
        $porceFinal = 0;

        if (!$this->db_b3p || !$this->db) {
            return array();
        }

        // Consultamos la cantidad de requerimientos.
        $cantidadTotal = $this->db_b3p->query(
            'SELECT total_requerimientos
                FROM progreso_proyecto 
                WHERE ruta_submodulo = ?',
            $id
        );
        $resCantidadTotal = $this->db_b3p->res2single($cantidadTotal);
        
        // Consultamos los requerimientos finalizados.
        $reqFinalizados = $this->db->query(
            'SELECT count(1) AS cantidad 
                FROM task_status WHERE page = ?
                AND msg IS NOT NULL 
                AND msg IN ("Completado", "No Aplica")',
            $id
        );
        $resultadoCantidad = $this->db->res2single($reqFinalizados);

        // Calculamos el porcentaje de avance.
        if ($resultadoCantidad != 0 and $resCantidadTotal != 0){
            $porceFinal = ($resultadoCantidad / $resCantidadTotal) * 100;
        }

        // Actualizamos la informacion en la tabla Proyectos de BEZ.
        $this->db_b3p->query(
            'UPDATE progreso_proyecto 
                SET requerimientos_terminados = ?, 
                    porcentaje_progreso = ?,
                    fecha_actualizacion = ?
                WHERE ruta_submodulo = ?',
            intval($resultadoCantidad),
            $porceFinal,
            $fechaActual,
            $id
        );
    }

    /**
     * Actualiza el progreso de admin_thread_progress cuando la página pertenece
     * a un proyecto administrativo (proyectos:administrativos:X:seccion). R**G
     */
    public function actualizarProgresoAdminThread($pageId)
    {
        // Solo procesar páginas de proyectos administrativos
        if (strpos($pageId, 'proyectos:administrativos:') !== 0) {
            return;
        }

        if (!$this->db_b3p || !$this->db) {
            return;
        }

        // Extraer adminThreadId del pageId (formato: proyectos:administrativos:{id}:{seccion})
        $parts = explode(':', $pageId);
        if (count($parts) < 4 || !is_numeric($parts[2])) {
            return;
        }
        $adminThreadId = intval($parts[2]);

        // Buscar la sección que corresponde a esta ruta_wiki
        $resSec = $this->db_b3p->query(
            'SELECT seccion_id FROM admin_thread_progress WHERE admin_thread_id = ? AND ruta_wiki = ?',
            $adminThreadId, $pageId
        );
        $arrSec = $this->db_b3p->res2arr($resSec);

        if (empty($arrSec)) {
            return;
        }

        $seccionId = intval($arrSec[0]['seccion_id']);

        // Contar total de tareas en la página (tabla DO)
        $resTotal = $this->db->query('SELECT COUNT(*) as total FROM tasks WHERE page = ?', $pageId);
        $arrTotal = $this->db->res2arr($resTotal);
        $total = intval($arrTotal[0]['total']);

        // Contar tareas completadas (tabla DO)
        $resComp = $this->db->query(
            'SELECT COUNT(*) as completadas FROM task_status WHERE page = ? AND msg IS NOT NULL AND msg IN ("Completado", "No Aplica")',
            $pageId
        );
        $arrComp = $this->db->res2arr($resComp);
        $completadas = intval($arrComp[0]['completadas']);

        // Calcular porcentaje
        $porcentaje = $total > 0 ? round(($completadas / $total) * 100, 2) : 0;

        // Actualizar admin_thread_progress (tabla BEZ)
        $this->db_b3p->query(
            'UPDATE admin_thread_progress
                SET total_requerimientos = ?,
                    requerimientos_terminados = ?,
                    porcentaje_progreso = ?,
                    updated_at = datetime("now")
                WHERE admin_thread_id = ? AND seccion_id = ?',
            $total, $completadas, $porcentaje, $adminThreadId, $seccionId
        );
    }

    /**
     * Funcion encargada de acutalizar el campo archivo del task status. R**G
     */
    public function actualizarArchivoTaskStatus($md5, $filename, $pageId, $publicPath)
    {
        if (!$this->db_b3p || !$this->db) {
            return array();
        }
        // Actualizamos la informacion en la tabla task status para el archivo.
        $this->db->query(
            'INSERT OR IGNORE INTO task_filename (page,md5,filename,path)
             VALUES (?, ?, ?, ?)',
            $pageId,
            $md5,
            $filename,
            $publicPath
        );
    }
    
    /**
     * Funcion encargada de traer el archivo. R**G
     */
    public function getArchivoTaskStatus($page, $md5)
    {
        if (!$this->db) {
            return array();
        }

        $res = $this->db->query(
           'SELECT
                path AS ruta_archivo,
                filename AS nombre_archivo
            FROM task_filename
            WHERE page = ? AND md5 = ?
            LIMIT 1',
            $page,
            $md5
        );

        return $this->db->res2arr($res);
    }

    /**
     * Recorre archivos en carpeta (incluyendo subcarpetas) y devuelve las URLs públicas.
     */
    private function getFilesDir($project)
    {
        $namespace = (string) $project;
        $colonPos = strrpos($namespace, ':');
        if ($colonPos === false) {
            return []; // Namespace invalido, no escanear
        }
        $baseNamespace = substr($namespace, 0, $colonPos);
        if (empty($baseNamespace)) {
            return []; // Path vacio, no escanear raiz de media
        }
        $folderPath = str_replace(':', '/', $baseNamespace);
        $path = DOKU_INC . 'data/media/' . $folderPath;

        $urls = [];

        if (!is_dir($path)) {
            return $urls; // Carpeta no existe, retornar vacio sin echo (no corromper JSON)
        }

        // Archivos del sistema que NO deben enviarse a la IA
        $archivosExcluidos = ['.htaccess', '_dummy', '.gitignore', '.gitkeep', 'Thumbs.db', '.DS_Store'];

        // Solo formatos que la API de IA acepta. Otros formatos (docx, doc, etc.)
        // se omiten silenciosamente para no bloquear el procesamiento de los válidos.
        $extensionesPermitidas = ['pdf', 'xls', 'xlsx'];

        // Limites para evitar sobrecarga
        $maxArchivos = 20;                    // Maximo 20 archivos por modulo
        $maxTamanoArchivo = 10 * 1024 * 1024; // 10MB por archivo

        $scanDir = function ($dir, $namespaceBase) use (&$scanDir, &$urls, $archivosExcluidos, $extensionesPermitidas, $maxArchivos, $maxTamanoArchivo) {
            $handle = opendir($dir);
            if (!$handle) return;
            while (($file = readdir($handle)) !== false) {
                if ($file == '.' || $file == '..') continue;

                // Filtrar archivos del sistema
                if (in_array($file, $archivosExcluidos)) continue;

                $filepath = $dir . '/' . $file;

                if (is_file($filepath)) {
                    // Limite de cantidad alcanzado
                    if (count($urls) >= $maxArchivos) break;

                    // Filtrar por extension permitida (solo documentos, no imagenes)
                    $extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));
                    if (!in_array($extension, $extensionesPermitidas)) continue;

                    // Filtrar archivos demasiado grandes
                    if (filesize($filepath) > $maxTamanoArchivo) continue;

                    // Convertir ruta fisica a namespace de dokuwiki
                    $relativePath = str_replace(DOKU_INC . 'data/media/', '', $filepath);
                    $id = str_replace('/', ':', $relativePath);
                    $urls[] = ml($id); // URL publica
                } elseif (is_dir($filepath)) {
                    // Seguir recorriendo subcarpeta (si no se alcanzo el limite)
                    if (count($urls) < $maxArchivos) {
                        $scanDir($filepath, $namespaceBase . ':' . $file);
                    }
                }
            }
            closedir($handle);
        };

        $scanDir($path, $baseNamespace);

        return $urls;
    }

    /**
     * Funcion encargada de traer la ruta del archivo de todo el proyecto. R**G
     */
    public function getFilesProject($project)
    { 
        if (!$this->db) {
            return array();
        }

        // 1. Consultamos archivos de comentarios.
        $archivo_raiz = $this->getFilesDir($project);

        return $archivo_raiz;
    }

    /**
     * Funcion encargada de eliminar registro de archivo de task filename R**G
     */
    public function eliminarArchivoTaskStatus($page, $md5)
    {
        if (!$this->db) {
            return array();
        }
        
        $this->db->query(
            'DELETE FROM task_filename
             WHERE page = ?
               AND md5  = ?',
            $page, $md5
        );
    }

    /**
     * Load all tasks with given filters.
     *
     * Filters are:
     *  - ns        for namespace filters
     *  - id
     *  - status    can be done or undone to filter for (un)completed tasks
     *  - limit     limit the results to a given number of results
     *  - user      all tasks to a given user
     *  - md5       a single task
     *
     * @param array $args        filters to apply
     * @param bool  $checkAccess yes: check if item is hidden or blocked by ACL, false: skip this check
     *
     * @return array filtered result.
     */
    public function loadTasks($args = null, $checkAccess = true)
    {
        if (!$this->db) {
            return array();
        }
        $where = ' WHERE 1=1';
        $limit = '';
        if (isset($args)) {
            if (isset($args['ns'])) {
                // Whatever you do here, test it against the following mappings
                // (current ID has to be NS1:NS2:PAGE):
                // '..:..' => ''
                // '.'     => 'NS1:NS2:'
                // 'NS3'  => 'NS1:NS2:NS3'
                // ':NS4'  => 'NS4'
                // ':'     => ''

                global $ID;
                $ns = trim(resolve_id(getNS($ID), $args['ns'], false), ':');
                if (strlen($ns) > 0) {
                    // Do not match NSbla with NS, but only NS:bla
                    $ns .= ':';
                }

                $where .= sprintf(' AND A.page LIKE %s', $this->db->quote_string($ns . '%'));
            }

            if (isset($args['id'])) {
                global $ID;
                if (!is_array($args['id'])) {
                    $args['id'] = array($args['id']);
                }
                $exists = false;
                resolve_pageid(getNS($ID), $args['id'][0], $exists);
                $where .= sprintf(' AND A.page = %s', $this->db->quote_string($args['id'][0]));
            }

            if (isset($args['status'])) {
                $status = utf8_strtolower($args['status'][0]);
                if ($status == 'done') {
                    $where .= ' AND B.status IS NOT null';
                } elseif ($status == 'undone') {
                    $where .= ' AND B.status IS null';
                }
            }

            if (isset($args['from']) && isset($args['to'])) {
                // regex to match YYYY-MM-DD
                $dateRegex = '/^\d{4}-([0]\d|1[0-2])-([0-2]\d|3[01])$/';
                if (!preg_match($dateRegex, $args['from'][0]) || !preg_match($dateRegex, $args['to'][0])) {
                    return array();
                }
                $where .= sprintf(" AND A.date >= '%s' AND  A.date <= '%s' ", $args['from'][0], $args['to'][0]);
            }

            if (isset($args['limit'])) {
                $limit = ' LIMIT ' . intval($args['limit'][0]);
            }

            if (isset($args['md5'])) {
                if (!is_array($args['md5'])) {
                    $args['md5'] = array($args['md5']);
                }
                $where .= ' AND A.md5 = ' . $this->db->quote_string($args['md5'][0]);
            }

            $argn = array('user', 'creator');
            foreach ($argn as $n) {
                if (isset($args[$n])) {
                    if (!is_array($args[$n])) {
                        $args[$n] = array($args[$n]);
                    }

                    // replace current user's placeholder
                    $args[$n] = array_map(
                        function ($user) {
                            return (strtolower($user) === '@user@' && $_SERVER['REMOTE_USER']) ?
                                $_SERVER['REMOTE_USER'] :
                                $user;
                        },
                        $args[$n]
                    );

                    $search = $n;

                    /** @var DokuWiki_Auth_Plugin $auth */
                    global $auth;
                    if ($auth && !$auth->isCaseSensitive()) {
                        $search = "lower($search)";
                        $args[$n] = array_map('utf8_strtolower', $args[$n]);
                    }
                    $args[$n] = $this->db->quote_and_join($args[$n]);

                    $where .= sprintf(' AND %s in (%s)', $search, $args[$n]);
                }
            }

        }
        if ($checkAccess) {
            $where .= ' AND GETACCESSLEVEL(A.page) >= ' . AUTH_READ;
        }
        $query = 'SELECT A.page     AS page,
                        A.md5      AS md5,
                        A.date     AS date,
                        A.text     AS text,
                        A.creator  AS creator,
                        B.msg      AS msg,
                        B.status   AS status,
                        B.closedby AS closedby,
                        C.user     AS user
                   FROM tasks A LEFT JOIN task_status B
                     ON A.page = B.page
                     AND A.md5 = B.md5
                   LEFT JOIN task_assignees C
                     ON A.page = C.page
                     AND A.md5 = C.md5
                     ' . $where . '
                   ORDER BY A.page, A.pos' . $limit;
        $res = $this->db->query($query);
        $res = $this->db->res2arr($res);

        // merge assignees into users array
        $result = array();
        foreach ($res as $row) {
            $key = $row['page'] . $row['md5'];
            if (!isset($result[$key])) {
                $result[$key] = $row;
                unset($result[$key]['user']);
                $result[$key]['users'] = array();
            }

            if ($row['user'] !== null) {
                $result[$key]['users'][] = $row['user'];
            }
        }

        return array_values($result);
    }

    /**
     * Toggles a tasks status.
     *
     * @param string $page      page id of the task
     * @param string $md5       tasks md5 hash
     * @param string $commitmsg a optional message to the task completion
     *
     * @return bool|string|int
     *          false on undone a task
     *          or timestamp on task completion
     *          or -2 if not allowed
     */
    public function toggleTaskStatus($page, $md5, $commitmsg = '')
    {
        global $ID;

        if (!$this->db) {
            return -2;
        } //not allowed
        $md5 = trim($md5);
        if (!$page || !$md5) {
            return -2;
        } //not allowed

        $commitmsg = strip_tags($commitmsg);

        $res = $this->db->query(
            'SELECT A.page AS page,
                    B.status AS status
               FROM tasks A LEFT JOIN task_status B
                 ON A.page = B.page
                 AND A.md5 = B.md5
              WHERE A.page = ?
                AND A.md5  = ?',
            $page, $md5
        );
        $stat = $this->db->res2row($res);
        if ($stat == false) {
            return -2; //not allowed, task don't exist
        }
        $stat = $stat['status'];

        // load task details and determine notify receivers
        $task = $this->loadTasks(array('id' => $ID, 'md5' => $md5))[0];
        $recs = (array)$task['users'];
        $recs[] = $task['creator'];
        $recs = array_unique($recs);
        $recs = array_diff($recs, array($_SERVER['REMOTE_USER']));

        $name = $_SERVER['REMOTE_USER'];
        if (!$stat) {
            // close the task
            $stat = date('Y-m-d', time());
            $this->db->query(
                'INSERT INTO task_status
                     (page, md5, status, closedby, msg)
                 VALUES
                     (?, ?, ?, ?, ?)',
                $page, $md5, $stat, $name, $commitmsg
            );

            $this->sendMail($recs, 'close', $task, $name, $commitmsg);
            return $stat;
        } else {
            // reopen the task
            $this->db->query(
                'DELETE FROM task_status
                 WHERE page = ?
                   AND md5  = ?',
                $page, $md5
            );
            $this->sendMail($recs, 'reopen', $task, $name);
            return false;
        }
    }

    /**
     * Build the NICC-styled HTML email wrapper
     *
     * @param string $badge       Badge text for the header
     * @param string $bodyContent Inner HTML content
     * @param string $wikiTitle   Wiki title for the header subtitle
     * @return string Complete HTML email
     */
    private function _buildNiccHtml($badge, $bodyContent, $wikiTitle) {
        $year = date('Y');
        $wikiTitle = htmlspecialchars($wikiTitle);
        $badge = htmlspecialchars($badge);
        return '<html>
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head>
<body style="margin:0; padding:0; background-color:#f0eeea; font-family:Arial,sans-serif;">
<table width="100%" cellpadding="0" cellspacing="0" border="0" style="background:#f0eeea; padding:20px 0;">
<tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" border="0" style="max-width:600px; background:#ffffff;">
    <tr><td style="height:4px; background:#e94560; font-size:0; line-height:0;">&nbsp;</td></tr>
    <tr><td style="background:#1a1a2e; padding:18px 30px;">
        <table width="100%" cellpadding="0" cellspacing="0" border="0"><tr>
            <td><table cellpadding="0" cellspacing="0" border="0"><tr>
                <td style="width:36px; height:36px; background:#e94560; text-align:center; vertical-align:middle;">
                    <span style="font-family:Georgia,serif; font-size:18px; font-weight:bold; color:#ffffff;">N</span>
                </td>
                <td style="padding-left:12px;">
                    <span style="font-family:Georgia,serif; font-size:17px; font-weight:bold; color:#f8f7f4; letter-spacing:1px;">NICC</span><br>
                    <span style="font-family:Arial,sans-serif; font-size:10px; color:#a0a0b8; letter-spacing:1px; text-transform:uppercase;">' . $wikiTitle . '</span>
                </td>
            </tr></table></td>
            <td align="right" valign="middle">
                <span style="font-family:Arial,sans-serif; font-size:10px; font-weight:bold; color:#f59e0b; letter-spacing:1px; text-transform:uppercase; border:1px solid rgba(245,158,11,0.4); padding:4px 10px;">' . $badge . '</span>
            </td>
        </tr></table>
    </td></tr>
    <tr><td style="background:#f8f7f4; padding:28px 30px;">
        ' . $bodyContent . '
    </td></tr>
</table>
<table width="100%" cellpadding="0" cellspacing="0" border="0" style="max-width:600px; margin:0 auto;">
    <tr><td style="background:#1a1a2e; padding:20px 30px;">
        <table width="100%" cellpadding="0" cellspacing="0" border="0">
            <tr><td style="padding-bottom:12px; border-bottom:1px solid rgba(255,255,255,0.08);">
                <table width="100%" cellpadding="0" cellspacing="0" border="0"><tr>
                    <td valign="top" style="font-family:Georgia,serif; font-size:14px; font-weight:bold; color:#f8f7f4;">SIE Asesorias<br><span style="font-family:Arial,sans-serif; font-size:10px; font-weight:normal; color:#6a6a82;">Sistemas de Gestion Integrados</span></td>
                    <td valign="top" align="right">
                        <a href="mailto:proyectos@sieasesorias.com" style="font-family:Arial,sans-serif; font-size:11px; color:#a0a0b8; text-decoration:none; display:block; margin-bottom:2px;">proyectos@sieasesorias.com</a>
                        <a href="https://sieasesorias.com" style="font-family:Arial,sans-serif; font-size:11px; color:#a0a0b8; text-decoration:none; display:block;">sieasesorias.com</a>
                    </td>
                </tr></table>
            </td></tr>
            <tr><td style="padding-top:12px; text-align:center; font-family:Arial,sans-serif; font-size:10px; color:#4a4a62; line-height:1.6;">&copy; ' . $year . ' SIE Asesorias &middot; Todos los derechos reservados<br><span style="color:#e94560;">NICC</span> &mdash; Plataforma de Gestion Empresarial</td></tr>
        </table>
    </td></tr>
    <tr><td style="height:3px; background:#e94560; font-size:0; line-height:0;">&nbsp;</td></tr>
</table>
</td></tr></table>
</body></html>';
    }

    /**
     * Build styled HTML content for a task email
     *
     * @param string $action  'open', 'close', or 'reopen'
     * @param string $user    User who triggered the action
     * @param array  $task    Task data array
     * @param string $date    Due date string
     * @param string $taskUrl URL to the task
     * @param string $msg     Close message (only for 'close')
     * @return string HTML body content
     */
    private function _buildTaskHtml($action, $user, $task, $date, $taskUrl, $msg = '') {
        $user = htmlspecialchars($user);
        $taskText = htmlspecialchars($task['text']);
        $date = htmlspecialchars($date);
        $taskUrl = htmlspecialchars($taskUrl);
        $msg = htmlspecialchars($msg);

        // Colors and text per action type
        switch ($action) {
            case 'close':
                $borderColor = '#27ae60';
                $bgColor = '#eafaf1';
                $headline = $user . ' ha cerrado una tarea';
                $badge = 'Tarea Cerrada';
                break;
            case 'reopen':
                $borderColor = '#f59e0b';
                $bgColor = '#fef9e7';
                $headline = $user . ' ha reabierto una tarea';
                $badge = 'Tarea';
                break;
            default: // open
                $borderColor = '#1976d2';
                $bgColor = '#e8f0fe';
                $headline = $user . ' le ha asignado una nueva tarea';
                $badge = 'Tarea';
                break;
        }

        // Info band
        $html = '<table width="100%" cellpadding="0" cellspacing="0" border="0" style="margin-bottom:20px;">
<tr><td style="border-left:4px solid ' . $borderColor . '; background:' . $bgColor . '; padding:14px 18px;">
    <span style="font-family:Arial,sans-serif; font-size:14px; color:#1a1a2e; font-weight:bold;">' . $headline . '</span>
</td></tr></table>';

        // Task details table
        $html .= '<table width="100%" cellpadding="0" cellspacing="0" border="0" style="margin-bottom:20px;">
<tr>
    <td style="padding:8px 0; font-family:Arial,sans-serif; font-size:12px; color:#6a6a82; text-transform:uppercase; letter-spacing:0.5px; width:120px; vertical-align:top;">Descripci&oacute;n</td>
    <td style="padding:8px 0; font-family:Arial,sans-serif; font-size:14px; color:#1a1a2e; font-weight:bold;">' . $taskText . '</td>
</tr>
<tr>
    <td style="padding:8px 0; font-family:Arial,sans-serif; font-size:12px; color:#6a6a82; text-transform:uppercase; letter-spacing:0.5px; vertical-align:top;">Fecha l&iacute;mite</td>
    <td style="padding:8px 0; font-family:Arial,sans-serif; font-size:14px; color:#1a1a2e;">' . $date . '</td>
</tr>';

        // Close message row
        if ($action === 'close' && $msg !== '') {
            $html .= '<tr>
    <td style="padding:8px 0; font-family:Arial,sans-serif; font-size:12px; color:#6a6a82; text-transform:uppercase; letter-spacing:0.5px; vertical-align:top;">Mensaje</td>
    <td style="padding:8px 0; font-family:Arial,sans-serif; font-size:14px; color:#1a1a2e;">' . $msg . '</td>
</tr>';
        }

        $html .= '</table>';

        // CTA button
        $html .= '<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr><td align="center" style="padding:10px 0 0;">
    <a href="' . $taskUrl . '" style="display:inline-block; background:#e94560; color:#ffffff; font-family:Arial,sans-serif; font-size:14px; font-weight:bold; text-decoration:none; padding:12px 32px; letter-spacing:0.5px;">Ver Tarea</a>
</td></tr></table>';

        return $html;
    }

    /**
     * Notify assignees or creators of new tasks and status changes
     *
     * @param array  $receivers list of user names to notify
     * @param string $type      type of notification (open|reopen|close)
     * @param array  $task
     * @param string $user      user who triggered the notification
     * @param string $msg       the closing message if any
     */
    public function sendMail($receivers, $type, $task, $user = '', $msg = '')
    {
        global $conf;
        /** @var DokuWiki_Auth_Plugin $auth */
        global $auth;

        if (!$auth) {
            return;
        }
        if (!$this->getConf('notify_assignee')) {
            return;
        }
        $receivers = (array)$receivers;
        if (!count($receivers)) {
            return;
        }

        // prepare subject
        $subj = '[' . $conf['title'] . '] ';
        $subj .= sprintf($this->getLang('mail_' . $type), $task['text']);

        // prepare text
        $text = file_get_contents($this->localFN('mail_' . $type));
        $text = str_replace(
            array(
                '@USER@',
                '@DATE@',
                '@TASK@',
                '@TASKURL@',
                '@MSG@',
                '@DOKUWIKIURL@'
            ),
            array(
                !empty($user) ? $user : $this->getLang('someone'),
                isset($task['date']) ? $task['date'] : $this->getLang('nodue'),
                $task['text'],
                wl($task['page'], '', true, '&') . '#plgdo__' . $task['md5'],
                $msg,
                DOKU_URL
            ),
            $text
        );

        // build HTML body
        $taskUrl = wl($task['page'], '', true, '&') . '#plgdo__' . $task['md5'];
        $taskDate = isset($task['date']) ? $task['date'] : $this->getLang('nodue');
        $bodyContent = $this->_buildTaskHtml($type, $user, $task, $taskDate, $taskUrl, $msg);
        $htmlBody = $this->_buildNiccHtml(
            ($type === 'close') ? 'Tarea Cerrada' : 'Tarea',
            $bodyContent,
            $conf['title']
        );

        // send mails
        foreach ($receivers as $receiver) {
            $info = $auth->getUserData($receiver);
            if (!$info || empty($info['mail'])) {
                continue;
            }
            $to = $info['name'] . ' <' . $info['mail'] . '>';
            $mail = new Mailer();
            $mail->to($to);
            $mail->from($conf['mailfrom']);
            $mail->subject($subj);
            $mail->setBody($text, null, null, $htmlBody);
            $mail->send();
        }
    }

    /**
     * load all page stats from a given page.
     *
     * Note: doesn't check ACL
     *
     * @param string $page page id
     *
     * @return array
     */
    public function getAllPageStatuses($page)
    {
        if (!$this->db) {
            return array();
        }
        if (!$page) {
            return array();
        }

        $res = $this->db->query(
           'SELECT
                A.page     AS page,
                A.creator  AS creator,
                A.md5      AS md5,
                B.status   AS status,
                B.closedby AS closedby,
                B.msg      AS msg
            FROM tasks A
            LEFT JOIN task_status B
            ON A.page = B.page
            AND A.md5 = B.md5
            WHERE A.page = ?',
            $page
        );

        return $this->db->res2arr($res);
    }

    /**
     * Get information about the number of tasks on a specific id.
     *
     * result keys are
     *   count  - number of all tasks
     *   done   - number of all finished tasks
     *   undone - number of all tasks to do
     *
     * @param string $id String  Id of the wiki page - if no id is given the current page will be used.
     *
     * @return array
     */
    public function getPageTaskCount($id = '')
    {
        if (!$id) {
            global $ID;
            $id = $ID;
        }
        if (auth_quickaclcheck($id) < AUTH_READ) {
            $tasks = array();
        } else {
            //for improving performance skip the access check in the query
            $tasks = $this->loadTasks(array('id' => $id), $checkAccess = false);
        }

        $result = array(
            'count' => count($tasks),
            'done' => 0,
            'undone' => 0,
            'late' => 0,
        );

        foreach ($tasks as $task) {
            if (empty($task['status'])) {
                $result['undone']++;
            } else {
                $result['done']++;
            }
            if (!empty($task['date']) && empty($task['status'])) {
                if (strtotime($task['date']) < time()) {
                    $result['late']++;
                }
            }
        }

        return $result;
    }

    /**
     * displays a small page task status view
     *
     * @param string $id     page id
     * @param bool   $return if true return html, otherwise print the html
     *
     * @return string|void
     */
    public function tpl_pageTasks($id = '', $return = false)
    {
        $count = $this->getPageTaskCount($id);
        if ($count['count'] == 0) {
            return;
        }

        if ($count['undone'] == 0) { // all tasks done
            $class = 'do_done';
            $title = $this->getLang('title_alldone');
        } elseif ($count['late'] == 0) { // open tasks - no late
            $class = 'do_undone';
            $title = sprintf($this->getLang('title_intime'), $count['undone']);
        } else { // late tasks
            $class = 'do_late';
            $title = sprintf($this->getLang('title_late'), $count['undone'], $count['late']);
        }

        $out = '<div class="plugin__do_pagetasks" title="' . $title . '"><span class="' . $class . '">';
        $out .= $count['undone'];
        $out .= '</span></div>';

        if ($return) {
            return $out;
        }
        echo $out;
    }

    /**
     * Get the html for an icon showing the user's open tasks
     *
     * If the user has open tasks, a js-overlay is shown on click.
     *
     * @return string the icon-html
     */
    public function tpl_getUserTasksIconHTML()
    {
        global $INPUT;
        if (!$INPUT->server->has('REMOTE_USER')) {
            return '';
        }
        $user = $INPUT->server->str('REMOTE_USER');
        $tasks = $this->loadTasks(array('status' => array('undone'), 'user' => $user));
        $num = count($tasks);

        $svg = inlineSVG(__DIR__ . '/pix/clipboard-text.svg');

        $doInner = '<span class="a11y">' . $this->getLang('prefix_tasks_user') . " </span>$svg<span class=\"num\">" . count($tasks) . '</span>';
        if ($user && $num > 0) {
            $title = sprintf($this->getLang('tasks_user_intime'), $num);
            $link = '<button class="plugin__do_usertasks" title="' . $title . '">' . $doInner . '</button>';
        } else {
            $title = $this->getLang('tasks_user_none');
            $link = '<span class="plugin__do_usertasks noopentasks" title="' . $title . '">' . $doInner . '</span>';
        }

        return $link;
    }

    /**
     * Get a pretty userlink
     *
     * @param string $user users loginname
     *
     * @return string username with possible links
     */
    public function getPrettyUser($user)
    {
        $userpage = $this->getConf('userpage');
        if ($userpage !== '' && $user !== '') {
            return p_get_renderer('xhtml')->internallink(
                sprintf($userpage, $user),
                '', '', true, 'navigation'
            );

        } else {
            return editorinfo($user);
        }
    }
}

