<?php
/**
 * Helper class for BEZ Tax Calendar plugin
 * Calendario Tributario DIAN Centralizado
 */

use dokuwiki\Extension\Plugin;

if (file_exists(DOKU_PLUGIN . 'bez/meta/path_helper.php')) require_once(DOKU_PLUGIN . 'bez/meta/path_helper.php');

class helper_plugin_beztaxcalendar extends Plugin {

    protected $db = null;
    protected $sqlite = null;
    protected $dbInitialized = false;

    /**
     * Obtener conexión a base de datos SQLite de BEZ
     * Usa el helper de BEZ para obtener la conexion correcta (base de datos 'b3p')
     */
    public function getDB() {
        if ($this->dbInitialized) {
            return $this->sqlite;
        }

        $this->dbInitialized = true;

        // Usar el helper de BEZ para obtener la conexion correcta
        $bezDb = plugin_load('helper', 'bez_db');
        if ($bezDb) {
            try {
                $this->sqlite = $bezDb->getDB(false);
                if ($this->sqlite) {
                    return $this->sqlite;
                }
            } catch (Exception $e) {
                // Continuar con metodo alternativo
            }
        }

        // Metodo alternativo: inicializar directamente con 'b3p'
        $sqlite = plugin_load('helper', 'sqlite');
        if (!$sqlite) {
            msg('SQLite plugin not available', -1);
            return null;
        }

        // La base de datos de BEZ se llama 'b3p', no 'bez'
        $dbDir = (function_exists('bez_plugin_base') ? bez_plugin_base() : DOKU_PLUGIN) . 'bez/db/';
        if (!is_dir($dbDir)) {
            msg('BEZ plugin database directory not found: ' . $dbDir, -1);
            return null;
        }

        if (!$sqlite->init('b3p', $dbDir)) {
            msg('Cannot initialize SQLite database b3p', -1);
            return null;
        }

        // Verificar adapter si existe (compatibilidad con sqlite plugin)
        if (method_exists($sqlite, 'getAdapter') && $sqlite->getAdapter()) {
            $sqlite->getAdapter()->setUseNativeAlter(true);
        }

        $this->sqlite = $sqlite;
        return $this->sqlite;
    }

    // ================================================================
    // CONTROL DE VERSIÓN Y VALIDACIONES
    // ================================================================

    /**
     * Obtener estado del calendario para un año
     */
    public function getCalendarStatus($year) {
        $db = $this->getDB();
        if (!$db) return null;

        $res = $db->query("SELECT * FROM tax_calendar_status WHERE year = ?", $year);
        return $db->res2row($res);
    }

    /**
     * Verificar si el calendario está disponible y actualizado
     */
    public function validateCalendarForGeneration($year) {
        $result = [
            'valid' => false,
            'status' => 'unknown',
            'alerts' => [],
            'warnings' => [],
            'can_proceed' => false,
            'calendar_info' => null
        ];

        $status = $this->getCalendarStatus($year);

        if (!$status) {
            $result['status'] = 'not_found';
            $result['alerts'][] = [
                'type' => 'error',
                'code' => 'CALENDAR_NOT_FOUND',
                'message' => sprintf($this->getLang('alert_no_calendar'), $year),
                'action' => 'upload',
                'action_label' => $this->getLang('upload_calendar') . ' ' . $year
            ];
            return $result;
        }

        $result['calendar_info'] = $status;

        // Verificar estado del calendario
        if ($status['status'] === 'draft') {
            $result['status'] = 'draft';
            $result['warnings'][] = [
                'type' => 'warning',
                'code' => 'CALENDAR_DRAFT',
                'message' => sprintf($this->getLang('alert_calendar_draft'), $year),
                'action' => 'verify',
                'action_label' => $this->getLang('btn_verify')
            ];
        }

        if ($status['status'] === 'archived') {
            $result['status'] = 'archived';
            $result['alerts'][] = [
                'type' => 'warning',
                'code' => 'CALENDAR_ARCHIVED',
                'message' => "El calendario {$year} está archivado (año pasado)."
            ];
        }

        // Nota: la verificacion de antiguedad de carga fue removida
        // porque no aportaba valor - el calendario se carga 1 vez por año

        // Verificar cantidad de datos
        if ($status['total_deadlines'] < 50) {
            $result['warnings'][] = [
                'type' => 'warning',
                'code' => 'CALENDAR_INCOMPLETE',
                'message' => sprintf($this->getLang('alert_calendar_incomplete'), $status['total_deadlines']),
                'action' => 'view',
                'action_label' => $this->getLang('btn_view')
            ];
        }

        // Determinar si puede proceder
        $hasBlockingAlerts = !empty(array_filter($result['alerts'], function($a) {
            return $a['type'] === 'error';
        }));
        $result['valid'] = !$hasBlockingAlerts;
        $result['can_proceed'] = !$hasBlockingAlerts;
        $result['status'] = $status['status'];

        return $result;
    }

    /**
     * Obtener resumen del estado de calendarios
     */
    public function getCalendarsOverview() {
        $currentYear = (int) date('Y');
        $nextYear = $currentYear + 1;

        $overview = [
            'current_year' => $currentYear,
            'calendars' => [],
            'alerts' => []
        ];

        // Obtener calendarios de los últimos 2 años y próximo año
        $years = [$currentYear - 1, $currentYear, $nextYear];

        foreach ($years as $year) {
            $status = $this->getCalendarStatus($year);
            $overview['calendars'][$year] = $status ?: [
                'year' => $year,
                'status' => 'not_loaded',
                'total_deadlines' => 0,
                'is_current' => $year == $currentYear
            ];
            if ($status) {
                $overview['calendars'][$year]['is_current'] = $year == $currentYear;
            }
        }

        // Alertas globales
        if (!isset($overview['calendars'][$currentYear]['loaded_at'])) {
            $overview['alerts'][] = [
                'type' => 'error',
                'message' => sprintf($this->getLang('alert_no_calendar'), $currentYear),
                'action' => 'upload',
                'action_url' => "?do=admin&page=beztaxcalendar&tab=upload&year={$currentYear}"
            ];
        }

        // Verificar fin de año
        $daysToYearEnd = $this->daysUntilYearEnd();
        $alertDays = $this->getConfig('alert_days_before_year_end', 60);

        if ($daysToYearEnd <= $alertDays) {
            if (!isset($overview['calendars'][$nextYear]['loaded_at'])) {
                $overview['alerts'][] = [
                    'type' => 'warning',
                    'message' => sprintf($this->getLang('alert_year_ending'), $daysToYearEnd, $nextYear),
                    'action' => 'upload',
                    'action_url' => "?do=admin&page=beztaxcalendar&tab=upload&year={$nextYear}"
                ];
            }
        }

        return $overview;
    }

    /**
     * Obtener años disponibles con estado
     */
    public function getAvailableYearsWithStatus() {
        $db = $this->getDB();
        if (!$db) return [];

        $currentYear = (int) date('Y');

        $res = $db->query(
            "SELECT year, status, total_deadlines, loaded_at, version
             FROM tax_calendar_status
             ORDER BY year DESC"
        );
        $loaded = $db->res2arr($res);

        $years = [];

        foreach ($loaded as $row) {
            $years[$row['year']] = [
                'year' => $row['year'],
                'loaded' => true,
                'status' => $row['status'],
                'total_deadlines' => $row['total_deadlines'],
                'loaded_at' => $row['loaded_at'],
                'version' => $row['version'],
                'is_current' => $row['year'] == $currentYear
            ];
        }

        // Asegurar que año actual y siguiente estén en la lista
        foreach ([$currentYear, $currentYear + 1] as $y) {
            if (!isset($years[$y])) {
                $years[$y] = [
                    'year' => $y,
                    'loaded' => false,
                    'status' => 'not_loaded',
                    'total_deadlines' => 0,
                    'is_current' => $y == $currentYear
                ];
            }
        }

        krsort($years);
        return $years;
    }

    // ================================================================
    // CALENDARIO MAESTRO
    // ================================================================

    /**
     * Obtener calendario maestro de un año
     */
    public function getMasterCalendar($year) {
        $db = $this->getDB();
        if (!$db) return [];

        $res = $db->query(
            "SELECT tcm.*, o.name as obligation_name, o.frequency
             FROM tax_calendar_master tcm
             JOIN tax_obligations o ON tcm.obligation_code = o.code
             WHERE tcm.year = ?
             ORDER BY o.display_order, tcm.period",
            $year
        );
        return $db->res2arr($res);
    }

    /**
     * Obtener fecha de vencimiento del calendario maestro
     */
    public function getMasterDeadline($obligationCode, $year, $period, $nitLastDigit) {
        $db = $this->getDB();
        if (!$db) return null;

        // Sanitizar: solo dígitos 0-9 son válidos
        $nitLastDigit = (int) $nitLastDigit;
        if ($nitLastDigit < 0 || $nitLastDigit > 9) $nitLastDigit = 0;
        $digitColumn = "digit_" . $nitLastDigit;

        $this->ensureCalendarActiveColumn();
        $res = $db->query(
            "SELECT *, {$digitColumn} as deadline_date
             FROM tax_calendar_master
             WHERE obligation_code = ? AND year = ? AND period = ? AND COALESCE(is_active, 1) = 1",
            $obligationCode, $year, $period
        );
        return $db->res2row($res);
    }

    /**
     * Actualizar estado del calendario después de carga
     */
    public function updateCalendarStatus($year, $data, $userId) {
        $db = $this->getDB();
        if (!$db) return false;

        // Calcular estadísticas
        $res = $db->query(
            "SELECT
                COUNT(DISTINCT obligation_code) as total_obligations,
                COUNT(*) as total_deadlines,
                MIN(COALESCE(digit_1, digit_2, digit_3, digit_4, digit_5)) as first_deadline,
                MAX(COALESCE(digit_0, digit_9, digit_8, digit_7, digit_6)) as last_deadline
             FROM tax_calendar_master
             WHERE year = ?",
            $year
        );
        $stats = $db->res2row($res);

        $existing = $this->getCalendarStatus($year);

        if ($existing) {
            $version = ($data['increment_version'] ?? false)
                ? $existing['version'] + 1
                : $existing['version'];

            $db->query(
                "UPDATE tax_calendar_status SET
                 status = COALESCE(?, status),
                 version = ?,
                 source_filename = COALESCE(?, source_filename),
                 source_file_hash = COALESCE(?, source_file_hash),
                 total_obligations = ?,
                 total_deadlines = ?,
                 first_deadline = ?,
                 last_deadline = ?,
                 notes = COALESCE(?, notes),
                 updated_at = ?
                 WHERE year = ?",
                $data['status'] ?? null,
                $version,
                $data['filename'] ?? null,
                $data['file_hash'] ?? null,
                $stats['total_obligations'],
                $stats['total_deadlines'],
                $stats['first_deadline'],
                $stats['last_deadline'],
                $data['notes'] ?? null,
                date('Y-m-d H:i:s'),
                $year
            );
        } else {
            $db->query(
                "INSERT INTO tax_calendar_status
                 (year, status, version, source_filename, source_file_hash,
                  total_obligations, total_deadlines, first_deadline, last_deadline,
                  loaded_by, loaded_at, notes)
                 VALUES (?, ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                $year,
                $data['status'] ?? 'draft',
                $data['filename'] ?? null,
                $data['file_hash'] ?? null,
                $stats['total_obligations'],
                $stats['total_deadlines'],
                $stats['first_deadline'],
                $stats['last_deadline'],
                $userId,
                date('Y-m-d H:i:s'),
                $data['notes'] ?? null
            );
        }

        return true;
    }

    /**
     * Verificar/Aprobar calendario
     */
    public function verifyCalendar($year, $userId) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query(
            "UPDATE tax_calendar_status SET
             status = 'active',
             verified_by = ?,
             verified_at = ?,
             updated_at = ?
             WHERE year = ?",
            $userId, date('Y-m-d H:i:s'), date('Y-m-d H:i:s'), $year
        );

        return true;
    }

    // ================================================================
    // OBLIGACIONES
    // ================================================================

    /**
     * Obtener todas las obligaciones activas
     */
    public function getObligations() {
        $db = $this->getDB();
        if (!$db) return [];

        $res = $db->query(
            "SELECT * FROM tax_obligations WHERE is_active = 1 ORDER BY display_order"
        );
        return $db->res2arr($res);
    }

    /**
     * Obtener obligación por código
     */
    public function getObligation($code) {
        $db = $this->getDB();
        if (!$db) return null;

        $res = $db->query("SELECT * FROM tax_obligations WHERE code = ?", $code);
        return $db->res2row($res);
    }

    /**
     * Obtener TODAS las obligaciones (incluyendo inactivas) para editor
     */
    public function getAllObligations() {
        $db = $this->getDB();
        if (!$db) return [];
        $res = $db->query("SELECT * FROM tax_obligations ORDER BY display_order, code");
        return $db->res2arr($res);
    }

    /**
     * Obtener obligación por ID
     */
    public function getObligationById($id) {
        $db = $this->getDB();
        if (!$db) return null;
        $res = $db->query("SELECT * FROM tax_obligations WHERE id = ?", (int)$id);
        return $db->res2row($res);
    }

    /**
     * Agregar nueva obligación tributaria
     */
    public function addObligation($code, $name, $description, $frequency, $taxpayerType) {
        $db = $this->getDB();
        if (!$db) return false;

        // Verificar duplicado
        $existing = $this->getObligation($code);
        if ($existing) return ['ok' => false, 'error' => 'Ya existe una obligacion con el codigo: ' . $code];

        $maxOrder = $db->query("SELECT MAX(display_order) FROM tax_obligations");
        $order = ((int)$db->res2single($maxOrder)) + 1;

        $db->query(
            "INSERT INTO tax_obligations (code, name, description, frequency, taxpayer_type, is_active, display_order, created_at)
             VALUES (?, ?, ?, ?, ?, 1, ?, ?)",
            $code, $name, $description, $frequency, $taxpayerType, $order, date('Y-m-d H:i:s')
        );
        return ['ok' => true];
    }

    /**
     * Actualizar obligación tributaria
     */
    public function updateObligation($id, $name, $description, $frequency, $taxpayerType, $isActive) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query(
            "UPDATE tax_obligations SET name = ?, description = ?, frequency = ?, taxpayer_type = ?, is_active = ?, updated_at = ? WHERE id = ?",
            $name, $description, $frequency, $taxpayerType, $isActive ? 1 : 0, date('Y-m-d H:i:s'), (int)$id
        );
        return true;
    }

    /**
     * Eliminar obligación tributaria (CASCADE: elimina calendar_master y client_obligations)
     */
    public function deleteObligation($id) {
        $db = $this->getDB();
        if (!$db) return false;

        $ob = $this->getObligationById($id);
        if (!$ob) return false;

        $code = $ob['code'];

        // CASCADE: eliminar datos relacionados
        $db->query("DELETE FROM tax_calendar_master WHERE obligation_code = ?", $code);
        $db->query("DELETE FROM client_obligations WHERE obligation_code = ?", $code);
        $db->query("DELETE FROM client_deadline_exceptions WHERE obligation_code = ?", $code);
        $db->query("DELETE FROM tax_task_log WHERE obligation_code = ?", $code);
        $db->query("DELETE FROM tax_obligations WHERE id = ?", (int)$id);

        return true;
    }

    // ================================================================
    // CALENDARIO MAESTRO - VALIDACIÓN SELECTIVA (is_active)
    // ================================================================

    /**
     * Asegurar que la columna is_active existe en tax_calendar_master
     */
    protected $calendarActiveColumnReady = false;
    protected function ensureCalendarActiveColumn() {
        if ($this->calendarActiveColumnReady) return;
        $db = $this->getDB();
        if (!$db) return;
        // Verificar si la columna ya existe
        $res = $db->query("PRAGMA table_info(tax_calendar_master)");
        $cols = $db->res2arr($res);
        $hasActive = false;
        if (is_array($cols)) {
            foreach ($cols as $col) {
                if (($col['name'] ?? '') === 'is_active') {
                    $hasActive = true;
                    break;
                }
            }
        }
        if (!$hasActive) {
            try {
                $db->query("ALTER TABLE tax_calendar_master ADD COLUMN is_active INTEGER DEFAULT 1");
            } catch (\Exception $e) {
                // Race condition: otra request ya agregó la columna - ignorar
            }
        }
        $this->calendarActiveColumnReady = true;
    }

    /**
     * Toggle is_active de una fila del calendario
     * @param int $id ID de la fila
     * @param bool $active true=activo, false=inactivo
     */
    public function toggleCalendarRowActive($id, $active) {
        $db = $this->getDB();
        if (!$db) return false;
        $this->ensureCalendarActiveColumn();
        $db->query("UPDATE tax_calendar_master SET is_active = ?, updated_at = ? WHERE id = ?",
            $active ? 1 : 0, date('Y-m-d H:i:s'), (int)$id);
        return true;
    }

    /**
     * Toggle is_active de TODAS las filas de una obligación para un año
     * @param int $year Año
     * @param string $obligationCode Código de obligación
     * @param bool $active true=activo, false=inactivo
     */
    public function toggleObligationActive($year, $obligationCode, $active) {
        $db = $this->getDB();
        if (!$db) return false;
        $this->ensureCalendarActiveColumn();
        $db->query("UPDATE tax_calendar_master SET is_active = ?, updated_at = ? WHERE year = ? AND obligation_code = ?",
            $active ? 1 : 0, date('Y-m-d H:i:s'), $year, $obligationCode);
        return true;
    }

    // ================================================================
    // CALENDARIO MAESTRO - CRUD
    // ================================================================

    /**
     * Agregar fila al calendario maestro
     */
    public function addCalendarRow($year, $obligationCode, $period, $periodName, $dates, $notes = '') {
        $db = $this->getDB();
        if (!$db) return false;

        // $dates indexed: 0=digit_1, 1=digit_2, ..., 8=digit_9, 9=digit_0
        $db->query(
            "INSERT INTO tax_calendar_master (year, obligation_code, period, period_name,
             digit_1, digit_2, digit_3, digit_4, digit_5, digit_6, digit_7, digit_8, digit_9, digit_0,
             notes, created_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            $year, $obligationCode, $period, $periodName,
            $dates[0] ?? '', $dates[1] ?? '', $dates[2] ?? '', $dates[3] ?? '', $dates[4] ?? '',
            $dates[5] ?? '', $dates[6] ?? '', $dates[7] ?? '', $dates[8] ?? '', $dates[9] ?? '',
            $notes, date('Y-m-d H:i:s')
        );
        $this->ensureCalendarActiveColumn();
        return true;
    }

    /**
     * Actualizar fila del calendario maestro (fechas y notas)
     */
    public function updateCalendarRow($id, $period, $periodName, $dates, $notes = '') {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query(
            "UPDATE tax_calendar_master SET period = ?, period_name = ?,
             digit_1 = ?, digit_2 = ?, digit_3 = ?, digit_4 = ?, digit_5 = ?,
             digit_6 = ?, digit_7 = ?, digit_8 = ?, digit_9 = ?, digit_0 = ?,
             notes = ?, updated_at = ? WHERE id = ?",
            $period, $periodName,
            $dates[0] ?? '', $dates[1] ?? '', $dates[2] ?? '', $dates[3] ?? '', $dates[4] ?? '',
            $dates[5] ?? '', $dates[6] ?? '', $dates[7] ?? '', $dates[8] ?? '', $dates[9] ?? '',
            $notes, date('Y-m-d H:i:s'), (int)$id
        );
        return true;
    }

    /**
     * Eliminar fila del calendario maestro
     */
    public function deleteCalendarRow($id) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query("DELETE FROM tax_calendar_master WHERE id = ?", (int)$id);
        return true;
    }

    /**
     * Obtener filas del calendario maestro por obligación y año
     */
    public function getCalendarRowsByObligation($year, $obligationCode) {
        $db = $this->getDB();
        if (!$db) return [];

        $this->ensureCalendarActiveColumn();
        $res = $db->query(
            "SELECT *, COALESCE(is_active, 1) as is_active FROM tax_calendar_master WHERE year = ? AND obligation_code = ? ORDER BY period",
            $year, $obligationCode
        );
        return $db->res2arr($res);
    }

    /**
     * Obtener una fila del calendario por ID
     */
    public function getCalendarRowById($id) {
        $db = $this->getDB();
        if (!$db) return null;

        $res = $db->query("SELECT * FROM tax_calendar_master WHERE id = ?", (int)$id);
        return $db->res2row($res);
    }

    // ================================================================
    // CONFIGURACIÓN POR CLIENTE
    // ================================================================

    /**
     * Obtener obligaciones de un cliente
     */
    public function getClientObligations($clientId) {
        $db = $this->getDB();
        if (!$db) return [];

        $res = $db->query(
            "SELECT o.*,
                    COALESCE(co.is_active, 0) as is_assigned,
                    co.notes as client_notes
             FROM tax_obligations o
             LEFT JOIN client_obligations co
                ON o.code = co.obligation_code AND co.client_id = ?
             WHERE o.is_active = 1
             ORDER BY o.display_order",
            $clientId
        );
        return $db->res2arr($res);
    }

    /**
     * Asignar/desasignar obligación a cliente
     */
    public function setClientObligation($clientId, $obligationCode, $active, $notes = '', $userId = '') {
        $db = $this->getDB();
        if (!$db) return false;

        $res = $db->query(
            "SELECT id FROM client_obligations WHERE client_id = ? AND obligation_code = ?",
            $clientId, $obligationCode
        );
        $existing = $db->res2row($res);

        if ($existing) {
            $db->query(
                "UPDATE client_obligations SET is_active = ?, notes = ?, updated_at = ? WHERE id = ?",
                $active ? 1 : 0, $notes, date('Y-m-d H:i:s'), $existing['id']
            );
        } else if ($active) {
            $db->query(
                "INSERT INTO client_obligations (client_id, obligation_code, is_active, notes, created_by)
                 VALUES (?, ?, 1, ?, ?)",
                $clientId, $obligationCode, $notes, $userId
            );
        }

        return true;
    }

    /**
     * Obtener excepciones de un cliente
     */
    public function getClientExceptions($clientId, $year = null) {
        $db = $this->getDB();
        if (!$db) return [];

        if ($year) {
            $res = $db->query(
                "SELECT cde.*, o.name as obligation_name
                 FROM client_deadline_exceptions cde
                 JOIN tax_obligations o ON cde.obligation_code = o.code
                 WHERE cde.client_id = ? AND cde.year = ?
                 ORDER BY cde.year DESC, o.display_order, cde.period",
                $clientId, $year
            );
        } else {
            $res = $db->query(
                "SELECT cde.*, o.name as obligation_name
                 FROM client_deadline_exceptions cde
                 JOIN tax_obligations o ON cde.obligation_code = o.code
                 WHERE cde.client_id = ?
                 ORDER BY cde.year DESC, o.display_order, cde.period",
                $clientId
            );
        }
        return $db->res2arr($res);
    }

    /**
     * Agregar excepción de fecha para cliente
     */
    public function addClientException($clientId, $year, $obligationCode, $period, $exceptionType, $customDate = null, $reason = '', $userId = '') {
        $db = $this->getDB();
        if (!$db) return false;

        $res = $db->query(
            "SELECT id FROM client_deadline_exceptions
             WHERE client_id = ? AND year = ? AND obligation_code = ? AND period = ?",
            $clientId, $year, $obligationCode, $period
        );
        $existing = $db->res2row($res);

        if ($existing) {
            $db->query(
                "UPDATE client_deadline_exceptions
                 SET exception_type = ?, custom_date = ?, reason = ?
                 WHERE id = ?",
                $exceptionType, $customDate, $reason, $existing['id']
            );
        } else {
            $db->query(
                "INSERT INTO client_deadline_exceptions
                 (client_id, year, obligation_code, period, exception_type, custom_date, reason, created_by)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                $clientId, $year, $obligationCode, $period, $exceptionType, $customDate, $reason, $userId
            );
        }

        return true;
    }

    /**
     * Eliminar excepción de cliente
     */
    public function removeClientException($exceptionId) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query("DELETE FROM client_deadline_exceptions WHERE id = ?", $exceptionId);
        return true;
    }

    // ================================================================
    // FECHA EFECTIVA (MAESTRO + EXCEPCIONES)
    // ================================================================

    /**
     * Obtener fecha efectiva de vencimiento para un cliente
     */
    public function getEffectiveDeadline($clientId, $obligationCode, $year, $period) {
        $db = $this->getDB();
        if (!$db) return null;

        // Obtener cliente y su NIT
        $client = $this->getClient($clientId);
        if (!$client) return null;

        $nitLastDigit = $this->extractNitLastDigit($client['nit'] ?? '');

        // Verificar si hay excepción para este cliente
        $res = $db->query(
            "SELECT * FROM client_deadline_exceptions
             WHERE client_id = ? AND year = ? AND obligation_code = ? AND period = ?",
            $clientId, $year, $obligationCode, $period
        );
        $exception = $db->res2row($res);

        if ($exception) {
            switch ($exception['exception_type']) {
                case 'exclude':
                    return [
                        'excluded' => true,
                        'reason' => $exception['reason']
                    ];

                case 'change_date':
                    return [
                        'deadline_date' => $exception['custom_date'],
                        'source' => 'exception',
                        'reason' => $exception['reason']
                    ];
            }
        }

        // Si no hay excepción, usar calendario maestro
        $master = $this->getMasterDeadline($obligationCode, $year, $period, $nitLastDigit);

        if (!$master) return null;

        return [
            'deadline_date' => $master['deadline_date'],
            'period_name' => $master['period_name'],
            'source' => 'master',
            'notes' => $master['notes']
        ];
    }

    /**
     * Obtener todos los vencimientos efectivos de un cliente para un año
     */
    public function getClientEffectiveCalendar($clientId, $year) {
        $db = $this->getDB();
        if (!$db) return [];

        $client = $this->getClient($clientId);
        if (!$client) return [];

        $this->ensureCalendarActiveColumn();
        $nitLastDigit = (int) $this->extractNitLastDigit($client['nit'] ?? '');
        if ($nitLastDigit < 0 || $nitLastDigit > 9) $nitLastDigit = 0;
        $digitColumn = "digit_" . $nitLastDigit;

        $res = $db->query(
            "SELECT
                tcm.obligation_code,
                o.name as obligation_name,
                tcm.period,
                tcm.period_name,
                tcm.{$digitColumn} as master_date,
                tcm.notes as master_notes,
                cde.exception_type,
                cde.custom_date,
                cde.reason as exception_reason,
                CASE
                    WHEN cde.exception_type = 'exclude' THEN NULL
                    WHEN cde.exception_type = 'change_date' THEN cde.custom_date
                    ELSE tcm.{$digitColumn}
                END as effective_date,
                CASE
                    WHEN cde.exception_type IS NOT NULL THEN 'exception'
                    ELSE 'master'
                END as source
             FROM client_obligations co
             JOIN tax_obligations o ON co.obligation_code = o.code
             JOIN tax_calendar_master tcm ON co.obligation_code = tcm.obligation_code AND tcm.year = ?
             LEFT JOIN client_deadline_exceptions cde
                ON co.client_id = cde.client_id
                AND tcm.obligation_code = cde.obligation_code
                AND tcm.year = cde.year
                AND tcm.period = cde.period
             WHERE co.client_id = ?
               AND co.is_active = 1
               AND o.is_active = 1
               AND COALESCE(tcm.is_active, 1) = 1
               AND (cde.exception_type IS NULL OR cde.exception_type != 'exclude')
             ORDER BY effective_date ASC",
            $year, $clientId
        );

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

    // ================================================================
    // GENERACIÓN DE TAREAS
    // ================================================================

    /**
     * Vista previa de tareas a generar
     */
    public function previewTaskGeneration($clientId, $projectId, $obligations, $year, $periods) {
        $preview = [];

        foreach ($obligations as $obligationCode) {
            foreach ($periods as $period) {
                $effective = $this->getEffectiveDeadline($clientId, $obligationCode, $year, $period);

                if (!$effective || isset($effective['excluded'])) {
                    continue;
                }

                $alreadyGenerated = $this->isTaskAlreadyGenerated($clientId, $projectId, $obligationCode, $year, $period);
                $obligation = $this->getObligation($obligationCode);

                $preview[] = [
                    'obligation_code' => $obligationCode,
                    'obligation_name' => $obligation['name'],
                    'period' => $period,
                    'period_name' => $effective['period_name'] ?? $period,
                    'deadline_date' => $effective['deadline_date'],
                    'source' => $effective['source'],
                    'already_exists' => $alreadyGenerated,
                    'can_generate' => !$alreadyGenerated && !empty($effective['deadline_date'])
                ];
            }
        }

        return $preview;
    }

    /**
     * Generar tareas masivamente
     */
    /**
     * Obtiene el coordinador de un proyecto (thread) por su ID.
     * Si no tiene coordinador, retorna null.
     */
    public function getProjectCoordinator($projectId) {
        $db = $this->getDB();
        if (!$db) return null;
        $res = $db->query("SELECT coordinator FROM thread WHERE id = ? AND deleted_at IS NULL", $projectId);
        $row = $db->res2row($res);
        return ($row && !empty($row['coordinator'])) ? $row['coordinator'] : null;
    }

    public function generateTasks($clientId, $projectId, $obligations, $year, $periods, $userId) {
        $db = $this->getDB();
        $client = $this->getClient($clientId);

        if (!$client) {
            throw new \Exception("Cliente no encontrado");
        }

        // Prioridad de asignación:
        // 1. Responsable override del cliente (si existe y el usuario aún es válido)
        // 2. Coordinador del proyecto
        // 3. Fallback: usuario actual (quien genera las tareas)
        $responsibleOverride = $this->getClientResponsible($clientId);
        // Validar que el responsable override aún existe en el sistema
        if ($responsibleOverride) {
            global $auth;
            $userData = $auth ? $auth->getUserData($responsibleOverride) : false;
            if (!$userData) {
                $responsibleOverride = null; // Usuario eliminado, ignorar override
            }
        }
        $coordinator = $this->getProjectCoordinator($projectId);
        $assignee = $responsibleOverride ?: $coordinator ?: $userId;

        $generated = [];
        $skipped = [];

        foreach ($obligations as $obligationCode) {
            foreach ($periods as $period) {
                // Verificar si ya existe
                if ($this->isTaskAlreadyGenerated($clientId, $projectId, $obligationCode, $year, $period)) {
                    $skipped[] = ['code' => $obligationCode, 'period' => $period, 'reason' => 'already_exists'];
                    continue;
                }

                // Obtener fecha efectiva
                $effective = $this->getEffectiveDeadline($clientId, $obligationCode, $year, $period);

                if (!$effective || isset($effective['excluded'])) {
                    $skipped[] = ['code' => $obligationCode, 'period' => $period, 'reason' => 'excluded'];
                    continue;
                }

                if (empty($effective['deadline_date'])) {
                    $skipped[] = ['code' => $obligationCode, 'period' => $period, 'reason' => 'no_date'];
                    continue;
                }

                // Obtener info de obligación
                $obligation = $this->getObligation($obligationCode);

                // Obtener o crear el task_program para esta obligación
                $taskProgramId = $this->getOrCreateTaskProgram($obligation['name'], $userId);

                // Crear tarea en BEZ — asignada al coordinador del proyecto
                $taskData = [
                    'thread_id' => $projectId,
                    'content' => "{$obligation['name']} - {$effective['period_name']} {$year}",
                    'description' => $this->buildTaskDescription($client, $obligation, $effective, $year),
                    'plan_date' => $effective['deadline_date'],
                    'assignee' => $assignee,
                    'type' => 'correction',
                    'state' => 'opened',
                    'task_program_id' => $taskProgramId
                ];

                $taskId = $this->createBezTask($taskData, $userId);

                // Registrar en log
                $db->query(
                    "INSERT INTO tax_task_log
                     (client_id, project_id, obligation_code, year, period, deadline_date, task_id, source, generated_by)
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    $clientId, $projectId, $obligationCode, $year, $period,
                    $effective['deadline_date'], $taskId, $effective['source'], $userId
                );

                $generated[] = [
                    'obligation' => $obligation['name'],
                    'period' => $effective['period_name'],
                    'deadline' => $effective['deadline_date'],
                    'task_id' => $taskId
                ];
            }
        }

        return [
            'generated' => $generated,
            'skipped' => $skipped,
            'total_generated' => count($generated),
            'total_skipped' => count($skipped)
        ];
    }

    /**
     * Construir descripción de la tarea
     */
    protected function buildTaskDescription($client, $obligation, $effective, $year) {
        $desc = "**OBLIGACIÓN TRIBUTARIA**\n\n";
        $desc .= "**Impuesto:** {$obligation['name']}\n";
        $desc .= "**Período:** {$effective['period_name']} {$year}\n";
        $desc .= "**Fecha límite:** {$effective['deadline_date']}\n";
        $desc .= "**Cliente:** {$client['name']}\n";
        $desc .= "**NIT:** {$client['nit']}\n\n";

        if ($effective['source'] === 'exception') {
            $desc .= "**NOTA:** Fecha personalizada - {$effective['reason']}\n";
        }

        return $desc;
    }

    /**
     * Verificar si tarea ya fue generada
     */
    public function isTaskAlreadyGenerated($clientId, $projectId, $obligationCode, $year, $period) {
        $db = $this->getDB();
        if (!$db) return false;

        $res = $db->query(
            "SELECT id FROM tax_task_log
             WHERE client_id = ? AND project_id = ? AND obligation_code = ? AND year = ? AND period = ?",
            $clientId, $projectId, $obligationCode, $year, $period
        );
        $log = $db->res2row($res);
        return $log !== false && $log !== null;
    }

    /**
     * Obtener o crear un task_program por nombre
     * Si ya existe, devuelve su ID. Si no, lo crea y devuelve el nuevo ID.
     */
    protected function getOrCreateTaskProgram($programName, $userId) {
        $db = $this->getDB();
        if (!$db) return null;

        // Buscar si ya existe
        $res = $db->query("SELECT id FROM task_program WHERE name = ?", $programName);
        $existing = $db->res2row($res);

        if ($existing) {
            return $existing['id'];
        }

        // No existe, crear nuevo
        $now = date('c');
        $db->query(
            "INSERT INTO task_program (name, count, added_by, added_date) VALUES (?, ?, ?, ?)",
            $programName,
            0,  // count se actualizará automáticamente por el trigger
            $userId,
            $now
        );

        // Obtener el ID del nuevo registro
        $res = $db->query("SELECT last_insert_rowid() as id");
        $row = $db->res2row($res);
        return $row['id'];
    }

    /**
     * Crear tarea en BEZ
     */
    protected function createBezTask($data, $userId) {
        $db = $this->getDB();

        // Fecha/hora actual en formato ISO8601
        $now = date('c');

        // Generar content_html desde el contenido wiki
        // Usar la descripción completa si está disponible, sino el content simple
        $wikiContent = isset($data['description']) ? $data['description'] : $data['content'];
        $content_html = p_render('xhtml', p_get_instructions($wikiContent), $info);

        // Si el HTML está vacío, crear un párrafo mínimo
        if (empty(trim($content_html))) {
            $content_html = '<p>' . hsc($data['content']) . '</p>';
        }

        // Obtener task_program_id si está definido
        $taskProgramId = isset($data['task_program_id']) ? $data['task_program_id'] : null;

        // Insertar la tarea con TODOS los campos requeridos
        $db->query(
            "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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            $data['thread_id'],
            $wikiContent,
            $content_html,
            $data['plan_date'],
            $data['assignee'],
            $data['type'],
            $data['state'],
            $userId,
            $now,
            $now,
            $now,
            1,  // all_day_event = true (tareas tributarias son de día completo)
            0,  // private = false
            0,  // lock = false
            $taskProgramId,
            'calendar'  // source = calendario tributario
        );

        // Obtener el último ID insertado
        $res = $db->query("SELECT last_insert_rowid() as id");
        $row = $db->res2row($res);
        $taskId = $row['id'];

        // Agregar participantes (original_poster y assignee)
        $this->addTaskParticipant($taskId, $userId, true, false);  // original_poster
        if ($userId !== $data['assignee']) {
            $this->addTaskParticipant($taskId, $data['assignee'], false, true);  // assignee
        } else {
            // Si es el mismo usuario, actualizar para que sea ambos
            $db->query(
                "UPDATE task_participant SET assignee = 1 WHERE task_id = ? AND user_id = ?",
                $taskId, $userId
            );
        }

        return $taskId;
    }

    /**
     * Agregar participante a una tarea
     */
    protected function addTaskParticipant($taskId, $userId, $isOriginalPoster = false, $isAssignee = false) {
        $db = $this->getDB();
        $now = date('c');

        $db->query(
            "INSERT OR REPLACE INTO task_participant (
                task_id,
                user_id,
                original_poster,
                assignee,
                commentator,
                subscribent,
                added_by,
                added_date,
                removed
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            $taskId,
            $userId,
            $isOriginalPoster ? 1 : 0,
            $isAssignee ? 1 : 0,
            0,  // commentator
            1,  // subscribent (recibe notificaciones)
            $userId,
            $now,
            0   // removed = false
        );
    }

    // ================================================================
    // CLIENTES (LABELS)
    // ================================================================

    /**
     * Obtener cliente por ID (desde tabla label de BEZ)
     */
    public function getClient($clientId) {
        $db = $this->getDB();
        if (!$db) return null;

        $res = $db->query("SELECT * FROM label WHERE id = ?", $clientId);
        return $db->res2row($res);
    }

    /**
     * Obtener todos los clientes activos
     */
    public function getClients() {
        $db = $this->getDB();
        if (!$db) return [];

        $res = $db->query("SELECT * FROM label ORDER BY name");
        return $db->res2arr($res);
    }

    /**
     * Obtener lista de usuarios del sistema DokuWiki
     * @return array [{username, fullname, email}]
     */
    public function getSystemUsers() {
        global $auth;
        if (!$auth) return [];

        $users = [];
        $list = $auth->retrieveUsers();
        if (is_array($list)) {
            foreach ($list as $login => $info) {
                $users[] = [
                    'username' => $login,
                    'fullname' => $info['name'] ?? $login,
                    'email'    => $info['mail'] ?? '',
                ];
            }
        }
        usort($users, function($a, $b) {
            return strcasecmp($a['fullname'], $b['fullname']);
        });
        return $users;
    }

    /**
     * Asegurar que la tabla client_responsible existe (una sola vez)
     */
    protected $clientResponsibleTableReady = false;
    protected function ensureClientResponsibleTable() {
        if ($this->clientResponsibleTableReady) return;
        $db = $this->getDB();
        if (!$db) return;
        $db->query("CREATE TABLE IF NOT EXISTS client_responsible (
            client_id INTEGER PRIMARY KEY,
            responsible TEXT NOT NULL,
            updated_at TEXT,
            updated_by TEXT
        )");
        $this->clientResponsibleTableReady = true;
    }

    /**
     * Obtener el responsable override de un cliente
     * @return string|null username del responsable o null si no hay override
     */
    public function getClientResponsible($clientId) {
        $db = $this->getDB();
        if (!$db) return null;

        $this->ensureClientResponsibleTable();

        $res = $db->query("SELECT responsible FROM client_responsible WHERE client_id = ?", $clientId);
        $row = $db->res2row($res);
        return ($row && !empty($row['responsible'])) ? $row['responsible'] : null;
    }

    /**
     * Guardar el responsable override de un cliente
     */
    public function setClientResponsible($clientId, $responsible, $userId = '') {
        $db = $this->getDB();
        if (!$db) return false;

        $this->ensureClientResponsibleTable();

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

        if (empty($responsible)) {
            // Eliminar override — usará coordinador del proyecto
            $db->query("DELETE FROM client_responsible WHERE client_id = ?", $clientId);
        } else {
            $existing = $db->query("SELECT client_id FROM client_responsible WHERE client_id = ?", $clientId);
            if ($db->res2row($existing)) {
                $db->query("UPDATE client_responsible SET responsible = ?, updated_at = ?, updated_by = ? WHERE client_id = ?",
                    $responsible, $now, $userId, $clientId);
            } else {
                $db->query("INSERT INTO client_responsible (client_id, responsible, updated_at, updated_by) VALUES (?, ?, ?, ?)",
                    $clientId, $responsible, $now, $userId);
            }
        }
        return true;
    }

    // ================================================================
    // PROYECTOS (THREADS)
    // ================================================================

    /**
     * Obtener proyectos de un cliente
     */
    public function getClientProjects($clientId) {
        $db = $this->getDB();
        if (!$db) return [];

        $res = $db->query(
            "SELECT t.* FROM thread t
             JOIN thread_label tl ON t.id = tl.thread_id
             WHERE tl.label_id = ? AND t.state = 'opened'
             ORDER BY t.id DESC",
            $clientId
        );
        return $db->res2arr($res);
    }

    // ================================================================
    // UTILIDADES
    // ================================================================

    /**
     * Extraer último dígito del NIT (antes del dígito de verificación)
     */
    public function extractNitLastDigit($nit) {
        if ($nit === null || $nit === '') return 0;
        $nit = preg_replace('/[^0-9]/', '', (string)$nit);
        if (strlen($nit) === 0) return 0;

        // Si tiene dígito de verificación (10+ dígitos), tomar penúltimo
        if (strlen($nit) >= 10) {
            return (int) substr($nit, -2, 1);
        }

        return (int) substr($nit, -1);
    }

    /**
     * Obtener configuración
     */
    public function getConfig($key, $default = null) {
        $db = $this->getDB();
        if (!$db) return $default;

        $res = $db->query("SELECT value FROM tax_calendar_config WHERE key = ?", $key);
        $row = $db->res2row($res);
        return $row ? $row['value'] : $default;
    }

    /**
     * Guardar configuración
     */
    public function setConfig($key, $value) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query(
            "INSERT OR REPLACE INTO tax_calendar_config (key, value, updated_at) VALUES (?, ?, ?)",
            $key, $value, date('Y-m-d H:i:s')
        );
        return true;
    }

    /**
     * Obtener todas las configuraciones
     */
    public function getAllConfig() {
        $db = $this->getDB();
        if (!$db) return [];

        $res = $db->query("SELECT * FROM tax_calendar_config ORDER BY key");
        $rows = $db->res2arr($res);

        $config = [];
        foreach ($rows as $row) {
            $config[$row['key']] = [
                'value' => $row['value'],
                'description' => $row['description']
            ];
        }
        return $config;
    }

    /**
     * Días desde una fecha
     */
    protected function daysSince($date) {
        if (empty($date)) return 999;
        $then = new \DateTime($date);
        $now = new \DateTime();
        return $now->diff($then)->days;
    }

    /**
     * Días hasta fin de año
     */
    protected function daysUntilYearEnd() {
        $now = new \DateTime();
        $yearEnd = new \DateTime(date('Y') . '-12-31');
        return $now->diff($yearEnd)->days;
    }

    /**
     * Obtener períodos disponibles según frecuencia
     */
    public function getPeriodsForFrequency($frequency) {
        switch ($frequency) {
            case 'monthly':
                return [
                    '01' => 'Enero', '02' => 'Febrero', '03' => 'Marzo',
                    '04' => 'Abril', '05' => 'Mayo', '06' => 'Junio',
                    '07' => 'Julio', '08' => 'Agosto', '09' => 'Septiembre',
                    '10' => 'Octubre', '11' => 'Noviembre', '12' => 'Diciembre'
                ];
            case 'bimonthly':
                return [
                    '01-02' => 'Enero-Febrero', '03-04' => 'Marzo-Abril',
                    '05-06' => 'Mayo-Junio', '07-08' => 'Julio-Agosto',
                    '09-10' => 'Septiembre-Octubre', '11-12' => 'Noviembre-Diciembre'
                ];
            case 'quarterly':
                return [
                    'Q1' => 'Ene-Abr', 'Q2' => 'May-Ago', 'Q3' => 'Sep-Dic'
                ];
            case 'annual':
                return ['annual' => 'Anual'];
            default:
                return [];
        }
    }

    /**
     * Obtener periodos disponibles en el calendario maestro para una obligacion y ano
     * Solo devuelve periodos que tienen al menos una fecha cargada
     */
    public function getAvailablePeriodsFromCalendar($obligationCode, $year) {
        $db = $this->getDB();
        if (!$db) return [];

        $this->ensureCalendarActiveColumn();
        $res = $db->query(
            "SELECT DISTINCT period, period_name
             FROM tax_calendar_master
             WHERE obligation_code = ? AND year = ?
             AND COALESCE(is_active, 1) = 1
             AND (COALESCE(digit_1,'') != '' OR COALESCE(digit_2,'') != '' OR COALESCE(digit_3,'') != ''
                  OR COALESCE(digit_4,'') != '' OR COALESCE(digit_5,'') != '' OR COALESCE(digit_6,'') != ''
                  OR COALESCE(digit_7,'') != '' OR COALESCE(digit_8,'') != '' OR COALESCE(digit_9,'') != ''
                  OR COALESCE(digit_0,'') != '')
             ORDER BY period",
            $obligationCode, $year
        );

        $periods = [];
        while ($row = $db->res2row($res)) {
            $periods[$row['period']] = $row['period_name'];
        }

        return $periods;
    }

    /**
     * Guardar archivo Excel del calendario en la BD
     */
    public function saveCalendarFile($year, $version, $filename, $fileContent, $userId) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query(
            "INSERT OR REPLACE INTO tax_calendar_files
             (year, version, filename, file_content, file_size, uploaded_by)
             VALUES (?, ?, ?, ?, ?, ?)",
            $year, $version, $filename, $fileContent, strlen($fileContent), $userId
        );

        return true;
    }

    /**
     * Obtener archivo Excel del calendario
     */
    public function getCalendarFile($year, $version = null) {
        $db = $this->getDB();
        if (!$db) return null;

        if ($version) {
            $res = $db->query(
                "SELECT * FROM tax_calendar_files WHERE year = ? AND version = ?",
                $year, $version
            );
        } else {
            $res = $db->query(
                "SELECT * FROM tax_calendar_files WHERE year = ? ORDER BY version DESC LIMIT 1",
                $year
            );
        }

        return $db->res2row($res);
    }

    /**
     * Registrar log de carga de calendario
     */
    public function logCalendarUpload($year, $filename, $stats, $userId, $status = 'completed', $errorDetails = null) {
        $db = $this->getDB();
        if (!$db) return false;

        $db->query(
            "INSERT INTO tax_calendar_upload_log
             (year, filename, records_loaded, records_updated, records_error, uploaded_by, status, error_details)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            $year,
            $filename,
            $stats['deadlines_new'] ?? 0,
            $stats['deadlines_updated'] ?? 0,
            $stats['errors'] ?? 0,
            $userId,
            $status,
            $errorDetails
        );

        return true;
    }
}
