<?php

use dokuwiki\Extension\AdminPlugin;

/**
 * BEZ Database Repair - Diagnostico y reparacion completa de base de datos
 *
 * Este plugin verifica y repara la estructura de la base de datos del plugin BEZ,
 * asegurando compatibilidad con todas las versiones y migraciones.
 *
 * Funcionalidades:
 * - Diagnostico completo de estructura
 * - Backup automatico antes de reparar
 * - Creacion de tablas faltantes
 * - Adicion de columnas faltantes
 * - Actualizacion de vistas
 * - Actualizacion de version de BD
 */
class admin_plugin_bezdbrepair extends AdminPlugin
{
    /** @var helper_plugin_sqlite */
    protected $sqlite = null;

    /** @var bool */
    protected $db_initialized = false;

    /** @var string */
    protected $backupDir;

    /** @var array Mensajes de log */
    protected $log = [];

    /** Version objetivo de la base de datos BEZ */
    const TARGET_DB_VERSION = 31;

    /** Version objetivo de la base de datos DO */
    const TARGET_DO_DB_VERSION = 8;

    /**
     * Verifica la contraseña del usuario actual contra su password real.
     * Reemplaza la anterior CONFIRM_PASSWORD hardcodeada por seguridad.
     */
    protected function verifyUserPassword($password)
    {
        global $auth;
        $user = $_SERVER['REMOTE_USER'] ?? '';
        if (empty($user) || empty($password)) {
            return false;
        }
        return $auth->checkPass($user, $password);
    }

    /** @var BezDbDirectConnection Conexion a BD do */
    protected $sqlite_do = null;

    /** @var bool */
    protected $db_do_initialized = false;

    /**
     * Columnas que deben existir en cada tabla
     */
    protected $required_columns = [
        'thread' => [
            'deleted_at' => [
                'sql' => 'ALTER TABLE thread ADD COLUMN deleted_at TEXT DEFAULT NULL',
                'version' => 15
            ],
            'year' => [
                'sql' => 'ALTER TABLE thread ADD COLUMN year TEXT DEFAULT NULL',
                'version' => 24
            ]
        ],
        'label' => [
            'nit' => [
                'sql' => 'ALTER TABLE label ADD COLUMN nit TEXT DEFAULT NULL',
                'version' => 19
            ],
            'codigo_verificacion' => [
                'sql' => 'ALTER TABLE label ADD COLUMN codigo_verificacion TEXT DEFAULT NULL',
                'version' => 19
            ]
        ],
        'admin_thread' => [
            'tipo_trabajador' => [
                'sql' => "ALTER TABLE admin_thread ADD COLUMN tipo_trabajador TEXT NOT NULL DEFAULT 'usuario'",
                'version' => 21
            ],
            'pagina_registros' => [
                'sql' => "ALTER TABLE admin_thread ADD COLUMN pagina_registros TEXT NOT NULL DEFAULT 'encargos:general'",
                'version' => 21
            ],
            'periodo' => [
                'sql' => 'ALTER TABLE admin_thread ADD COLUMN periodo TEXT DEFAULT NULL',
                'version' => 21
            ],
            'privado' => [
                'sql' => 'ALTER TABLE admin_thread ADD COLUMN privado INTEGER DEFAULT 0',
                'version' => 21
            ],
            'ruta_manual_calidad' => [
                'sql' => 'ALTER TABLE admin_thread ADD COLUMN ruta_manual_calidad TEXT DEFAULT NULL',
                'version' => 26
            ],
            'ruta_codigo_etica' => [
                'sql' => 'ALTER TABLE admin_thread ADD COLUMN ruta_codigo_etica TEXT DEFAULT NULL',
                'version' => 26
            ]
        ],
        'task' => [
            'source' => [
                'sql' => "ALTER TABLE task ADD COLUMN source TEXT DEFAULT 'manual'",
                'version' => 29
            ]
        ],
        'admin_thread_task' => [
            'content_html' => [
                'sql' => 'ALTER TABLE admin_thread_task ADD COLUMN content_html TEXT',
                'version' => 31
            ],
            'seccion_id' => [
                'sql' => 'ALTER TABLE admin_thread_task ADD COLUMN seccion_id INTEGER DEFAULT NULL',
                'version' => 31
            ]
        ],
        'admin_thread_comment' => [
            'updated_at' => [
                'sql' => 'ALTER TABLE admin_thread_comment ADD COLUMN updated_at DATETIME DEFAULT NULL',
                'version' => 31
            ],
            'updated_by' => [
                'sql' => 'ALTER TABLE admin_thread_comment ADD COLUMN updated_by TEXT DEFAULT NULL',
                'version' => 31
            ],
            'parent_id' => [
                'sql' => 'ALTER TABLE admin_thread_comment ADD COLUMN parent_id INTEGER DEFAULT NULL',
                'version' => 31
            ]
        ],
        'bulk_task_log' => [
            'task_ids' => [
                'sql' => 'ALTER TABLE bulk_task_log ADD COLUMN task_ids TEXT DEFAULT NULL',
                'version' => 22
            ],
            'reverted' => [
                'sql' => 'ALTER TABLE bulk_task_log ADD COLUMN reverted INTEGER DEFAULT 0',
                'version' => 22
            ],
            'reverted_at' => [
                'sql' => 'ALTER TABLE bulk_task_log ADD COLUMN reverted_at DATETIME DEFAULT NULL',
                'version' => 22
            ],
            'reverted_by' => [
                'sql' => 'ALTER TABLE bulk_task_log ADD COLUMN reverted_by TEXT DEFAULT NULL',
                'version' => 22
            ]
        ],
        // Columna is_active en tax_calendar_master (validación selectiva de filas)
        'tax_calendar_master' => [
            'is_active' => [
                'sql' => 'ALTER TABLE tax_calendar_master ADD COLUMN is_active INTEGER DEFAULT 1',
                'version' => 28
            ]
        ],
        // Columnas de progreso_proyecto (en caso de que tabla exista pero falten columnas)
        'progreso_proyecto' => [
            'fecha_insercion' => [
                'sql' => 'ALTER TABLE progreso_proyecto ADD COLUMN fecha_insercion DATETIME DEFAULT CURRENT_TIMESTAMP',
                'version' => 19
            ],
            'fecha_actualizacion' => [
                'sql' => 'ALTER TABLE progreso_proyecto ADD COLUMN fecha_actualizacion DATETIME DEFAULT CURRENT_TIMESTAMP',
                'version' => 19
            ],
            'procesos_ejecucion' => [
                'sql' => 'ALTER TABLE progreso_proyecto ADD COLUMN procesos_ejecucion INTEGER DEFAULT 0',
                'version' => 19
            ],
            'mostrar_submodulo' => [
                'sql' => 'ALTER TABLE progreso_proyecto ADD COLUMN mostrar_submodulo INTEGER DEFAULT 1',
                'version' => 19
            ]
        ]
    ];

    /**
     * Tablas que deben crearse si no existen
     */
    protected $required_tables = [];

    /**
     * Tablas BASE de BEZ (criticas, se crean primero)
     */
    protected $base_tables = [];

    /**
     * Constructor
     */
    public function __construct()
    {
        global $conf;
        $this->backupDir = $conf['savedir'] . '/backup_bezdb';
        $this->initBaseTables();
        $this->initRequiredTables();
    }

    /**
     * Inicializa las definiciones de tablas BASE de BEZ
     * Estas son las tablas fundamentales sin las cuales el plugin no funciona
     */
    protected function initBaseTables()
    {
        // Tabla principal de proyectos/threads
        $this->base_tables['thread'] = "CREATE TABLE IF NOT EXISTS thread (
            id INTEGER NOT NULL PRIMARY KEY,
            original_poster TEXT NOT NULL,
            coordinator TEXT NULL,
            closed_by TEXT NULL,
            private BOOLEAN NOT NULL DEFAULT 0,
            lock BOOLEAN NOT NULL DEFAULT 0,
            type TEXT NOT NULL DEFAULT 'issue',
            state TEXT NOT NULL DEFAULT 'proposal',
            create_date TEXT NOT NULL,
            last_activity_date TEXT NOT NULL,
            last_modification_date TEXT NOT NULL,
            close_date TEXT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            content_html TEXT NOT NULL,
            task_count INTEGER NOT NULL DEFAULT 0,
            task_count_closed INTEGER NOT NULL DEFAULT 0,
            task_sum_cost REAL,
            deleted_at TEXT DEFAULT NULL
        )";

        // Participantes de thread
        $this->base_tables['thread_participant'] = "CREATE TABLE IF NOT EXISTS thread_participant (
            thread_id INTEGER NOT NULL,
            user_id TEXT NOT NULL,
            original_poster BOOLEAN NOT NULL DEFAULT 0,
            coordinator BOOLEAN NOT NULL DEFAULT 0,
            commentator BOOLEAN NOT NULL DEFAULT 0,
            task_assignee BOOLEAN NOT NULL DEFAULT 0,
            subscribent BOOLEAN NOT NULL DEFAULT 0,
            added_by TEXT NOT NULL,
            added_date TEXT NOT NULL,
            PRIMARY KEY (thread_id, user_id)
        )";

        // Comentarios de thread
        $this->base_tables['thread_comment'] = "CREATE TABLE IF NOT EXISTS thread_comment (
            id INTEGER NOT NULL PRIMARY KEY,
            thread_id INTEGER NOT NULL,
            type TEXT NOT NULL DEFAULT 'comment',
            author TEXT NOT NULL,
            create_date TEXT NOT NULL,
            last_modification_date TEXT NOT NULL,
            content TEXT NOT NULL,
            content_html TEXT NOT NULL,
            task_count INTEGER NOT NULL DEFAULT 0
        )";

        // Etiquetas/Clientes
        $this->base_tables['label'] = "CREATE TABLE IF NOT EXISTS label (
            id INTEGER NOT NULL PRIMARY KEY,
            name TEXT UNIQUE NOT NULL,
            color TEXT NULL,
            count INTEGER NOT NULL DEFAULT 0,
            added_by TEXT NOT NULL,
            added_date TEXT NOT NULL,
            nit TEXT DEFAULT NULL,
            codigo_verificacion TEXT DEFAULT NULL
        )";

        // Relacion thread-label
        $this->base_tables['thread_label'] = "CREATE TABLE IF NOT EXISTS thread_label (
            thread_id INTEGER NOT NULL,
            label_id INTEGER NOT NULL,
            PRIMARY KEY (thread_id, label_id)
        )";

        // Programas de tareas
        $this->base_tables['task_program'] = "CREATE TABLE IF NOT EXISTS task_program (
            id INTEGER NOT NULL PRIMARY KEY,
            name TEXT UNIQUE NOT NULL,
            count INTEGER NOT NULL DEFAULT 0,
            added_by TEXT NOT NULL,
            added_date TEXT NOT NULL
        )";

        // Tareas
        $this->base_tables['task'] = "CREATE TABLE IF NOT EXISTS task (
            id INTEGER NOT NULL PRIMARY KEY,
            original_poster TEXT NOT NULL,
            assignee TEXT NOT NULL,
            closed_by TEXT NULL,
            private BOOLEAN NOT NULL DEFAULT 0,
            lock BOOLEAN NOT NULL DEFAULT 0,
            state TEXT NOT NULL DEFAULT 'opened',
            type TEXT NOT NULL DEFAULT 'correction',
            create_date TEXT NOT NULL,
            last_activity_date TEXT NOT NULL,
            last_modification_date TEXT NOT NULL,
            close_date TEXT,
            cost REAL,
            plan_date TEXT NOT NULL,
            all_day_event INTEGER NOT NULL DEFAULT 0,
            start_time TEXT NULL,
            finish_time TEXT NULL,
            content TEXT NOT NULL,
            content_html TEXT NOT NULL,
            thread_id INTEGER,
            thread_comment_id INTEGER,
            task_program_id INTEGER
        )";

        // Participantes de tarea
        $this->base_tables['task_participant'] = "CREATE TABLE IF NOT EXISTS task_participant (
            task_id INTEGER NOT NULL,
            user_id TEXT NOT NULL,
            original_poster BOOLEAN NOT NULL DEFAULT 0,
            assignee BOOLEAN NOT NULL DEFAULT 0,
            commentator BOOLEAN NOT NULL DEFAULT 0,
            subscribent BOOLEAN NOT NULL DEFAULT 0,
            added_by TEXT NOT NULL,
            added_date TEXT NOT NULL,
            PRIMARY KEY (task_id, user_id)
        )";

        // Comentarios de tarea
        $this->base_tables['task_comment'] = "CREATE TABLE IF NOT EXISTS task_comment (
            id INTEGER NOT NULL PRIMARY KEY,
            task_id INTEGER NOT NULL,
            author TEXT NOT NULL,
            create_date TEXT NOT NULL,
            last_modification_date TEXT NOT NULL,
            content TEXT NOT NULL,
            content_html TEXT NOT NULL
        )";

        // Tokens de autenticacion
        $this->base_tables['authentication_token'] = "CREATE TABLE IF NOT EXISTS authentication_token (
            page_id TEXT NOT NULL,
            token TEXT NOT NULL,
            generated_by TEXT NOT NULL,
            generation_date TEXT NOT NULL,
            expire_date TEXT,
            PRIMARY KEY (page_id, token)
        )";

        // Suscripciones
        $this->base_tables['subscription'] = "CREATE TABLE IF NOT EXISTS subscription (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            entity_type TEXT NOT NULL,
            entity_id INTEGER NOT NULL,
            created_at TEXT NOT NULL,
            UNIQUE(user_id, entity_type, entity_id)
        )";
    }

    /**
     * Inicializa las definiciones de tablas requeridas
     */
    protected function initRequiredTables()
    {
        // Tabla thread_sequence (version 16)
        $this->required_tables['thread_sequence'] = [
            'version' => 16,
            'sql' => "CREATE TABLE IF NOT EXISTS thread_sequence (
                id INTEGER PRIMARY KEY,
                last_id INTEGER NOT NULL DEFAULT 0
            )",
            'post_sql' => "INSERT OR IGNORE INTO thread_sequence (id, last_id)
                SELECT 1, COALESCE(MAX(id), 0) FROM thread"
        ];

        // Tabla task_priority_config (version 17)
        $this->required_tables['task_priority_config'] = [
            'version' => 17,
            'sql' => "CREATE TABLE IF NOT EXISTS task_priority_config (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                priority_level INTEGER NOT NULL UNIQUE,
                days_from INTEGER,
                days_to INTEGER,
                background_color TEXT NOT NULL DEFAULT '#FFFFFF',
                border_color TEXT NOT NULL DEFAULT '#CCCCCC',
                label TEXT NOT NULL,
                notify_superuser INTEGER DEFAULT 0,
                notify_assignee INTEGER DEFAULT 1,
                notify_creator INTEGER DEFAULT 0,
                is_active INTEGER DEFAULT 1,
                sort_order INTEGER DEFAULT 0
            )",
            'post_sql' => "INSERT OR IGNORE INTO task_priority_config
                (priority_level, days_from, days_to, background_color, border_color, label, notify_assignee, sort_order) VALUES
                (0, 31, NULL, '#EEF6F0', '#B0D2B6', 'Lejana', 0, 6),
                (1, 15, 30, '#E8F5E9', '#81C784', 'Normal', 0, 5),
                (2, 7, 14, '#FFF9C4', '#FFF176', 'Proxima', 0, 4),
                (3, 1, 6, '#FFECB3', '#FFD54F', 'Urgente', 1, 3),
                (4, 0, 0, '#FFCDD2', '#EF5350', 'Hoy vence', 1, 2),
                (5, NULL, -1, '#F8E8E8', '#F0AFAD', 'Vencida', 1, 1)"
        ];

        // Tablas del calendario tributario (version 18)
        $this->required_tables['tax_obligations'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_obligations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                code TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                description TEXT,
                frequency TEXT NOT NULL DEFAULT 'monthly',
                taxpayer_type TEXT DEFAULT 'all',
                is_active INTEGER DEFAULT 1,
                display_order INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )",
            'post_sql' => "INSERT OR IGNORE INTO tax_obligations (code, name, frequency, display_order) VALUES
                ('RETEFUENTE', 'Retencion en la Fuente', 'monthly', 1),
                ('IVA_BIMESTRAL', 'IVA Bimestral', 'bimonthly', 2),
                ('IVA_CUATRIMESTRAL', 'IVA Cuatrimestral', 'quarterly', 3),
                ('RENTA', 'Impuesto de Renta', 'annual', 4),
                ('ICA', 'Industria y Comercio', 'bimonthly', 5),
                ('GMF', 'Gravamen Movimientos Financieros', 'weekly', 6),
                ('RETEICA', 'Retencion de ICA', 'monthly', 7),
                ('RETEIVA', 'Retencion de IVA', 'monthly', 8)"
        ];

        $this->required_tables['tax_calendar_master'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_calendar_master (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                year INTEGER NOT NULL,
                obligation_code TEXT NOT NULL,
                period TEXT NOT NULL,
                period_name TEXT,
                digit_1 DATE,
                digit_2 DATE,
                digit_3 DATE,
                digit_4 DATE,
                digit_5 DATE,
                digit_6 DATE,
                digit_7 DATE,
                digit_8 DATE,
                digit_9 DATE,
                digit_0 DATE,
                notes TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(year, obligation_code, period)
            )"
        ];

        $this->required_tables['tax_calendar_status'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_calendar_status (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                year INTEGER NOT NULL UNIQUE,
                status TEXT NOT NULL DEFAULT 'draft',
                version INTEGER DEFAULT 1,
                source_filename TEXT,
                file_hash TEXT,
                loaded_by TEXT,
                loaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                verified_by TEXT,
                verified_at DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )"
        ];

        $this->required_tables['tax_calendar_files'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_calendar_files (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                year INTEGER NOT NULL,
                filename TEXT NOT NULL,
                file_content BLOB,
                file_hash TEXT,
                uploaded_by TEXT,
                uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )"
        ];

        $this->required_tables['tax_calendar_config'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_calendar_config (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                config_key TEXT NOT NULL UNIQUE,
                config_value TEXT,
                description TEXT,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )",
            'post_sql' => "INSERT OR IGNORE INTO tax_calendar_config (config_key, config_value, description) VALUES
                ('alert_days_before_year_end', '60', 'Dias antes de fin de año para alertar sobre calendario faltante'),
                ('warning_days_since_load', '30', 'Dias desde ultima carga para mostrar advertencia'),
                ('require_verification', '0', 'Requerir verificacion antes de usar calendario'),
                ('calendar_storage_type', 'database', 'Tipo de almacenamiento: database o local_path'),
                ('calendar_storage_path', '', 'Ruta local para almacenar calendarios'),
                ('calendar_filename_pattern', 'calendario_dian_{year}.xlsx', 'Patron de nombre de archivo')"
        ];

        $this->required_tables['tax_calendar_upload_log'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_calendar_upload_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                year INTEGER NOT NULL,
                filename TEXT,
                action TEXT NOT NULL,
                records_processed INTEGER DEFAULT 0,
                records_created INTEGER DEFAULT 0,
                records_updated INTEGER DEFAULT 0,
                errors_count INTEGER DEFAULT 0,
                error_details TEXT,
                performed_by TEXT,
                performed_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )"
        ];

        $this->required_tables['client_obligations'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS client_obligations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER NOT NULL,
                obligation_code TEXT NOT NULL,
                is_active INTEGER DEFAULT 1,
                notes TEXT,
                assigned_by TEXT,
                assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(client_id, obligation_code),
                FOREIGN KEY (client_id) REFERENCES label(id) ON DELETE CASCADE
            )"
        ];

        $this->required_tables['client_deadline_exceptions'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS client_deadline_exceptions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER NOT NULL,
                year INTEGER NOT NULL,
                obligation_code TEXT NOT NULL,
                period TEXT NOT NULL,
                exception_type TEXT NOT NULL DEFAULT 'change_date',
                custom_date DATE,
                reason TEXT,
                created_by TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (client_id) REFERENCES label(id) ON DELETE CASCADE
            )"
        ];

        $this->required_tables['client_custom_obligations'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS client_custom_obligations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                description TEXT,
                frequency TEXT NOT NULL DEFAULT 'monthly',
                is_active INTEGER DEFAULT 1,
                created_by TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (client_id) REFERENCES label(id) ON DELETE CASCADE
            )"
        ];

        $this->required_tables['tax_task_log'] = [
            'version' => 18,
            'sql' => "CREATE TABLE IF NOT EXISTS tax_task_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER NOT NULL,
                project_id INTEGER NOT NULL,
                task_id INTEGER NOT NULL,
                obligation_code TEXT NOT NULL,
                year INTEGER NOT NULL,
                period TEXT NOT NULL,
                deadline_date DATE,
                generated_by TEXT,
                generated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(project_id, obligation_code, year, period)
            )"
        ];

        // =============================================
        // Tabla de log de notificaciones de vencimiento (version 27)
        // =============================================
        $this->required_tables['task_deadline_notification_log'] = [
            'version' => 27,
            'sql' => "CREATE TABLE IF NOT EXISTS task_deadline_notification_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id INTEGER NOT NULL,
                priority_level INTEGER NOT NULL,
                notified_date TEXT NOT NULL,
                recipient TEXT NOT NULL,
                UNIQUE(task_id, priority_level, notified_date, recipient)
            )"
        ];

        // =============================================
        // Tabla de progreso de proyecto (version 19)
        // =============================================

        $this->required_tables['progreso_proyecto'] = [
            'version' => 19,
            'sql' => "CREATE TABLE IF NOT EXISTS progreso_proyecto (
                id_progreso INTEGER PRIMARY KEY AUTOINCREMENT,
                proyecto_id INTEGER NOT NULL,
                nombre_submodulo TEXT NOT NULL,
                ruta_submodulo TEXT,
                procesos_ejecucion INTEGER DEFAULT 0,
                mostrar_submodulo INTEGER DEFAULT 1,
                total_requerimientos INTEGER DEFAULT 0,
                requerimientos_terminados INTEGER DEFAULT 0,
                porcentaje_progreso REAL DEFAULT 0,
                fecha_insercion DATETIME DEFAULT CURRENT_TIMESTAMP,
                fecha_actualizacion DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (proyecto_id) REFERENCES thread(id) ON DELETE CASCADE
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS progreso_proyecto_idx_proyecto ON progreso_proyecto(proyecto_id)"
        ];

        // =============================================
        // Tablas del Control Administrativo (version 20)
        // =============================================

        // Grupos de control (ej: Sistema de Calidad)
        $this->required_tables['admin_control_groups'] = [
            'version' => 20,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_control_groups (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                nombre TEXT NOT NULL,
                descripcion TEXT,
                orden INTEGER DEFAULT 0,
                activo INTEGER DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )",
            'post_sql' => "INSERT OR IGNORE INTO admin_control_groups (id, nombre, descripcion, orden) VALUES
                (1, 'Sistema de Calidad', 'Control de actividades del sistema de gestion de calidad', 1)"
        ];

        // Items/componentes de cada grupo
        $this->required_tables['admin_control_items'] = [
            'version' => 20,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_control_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                grupo_id INTEGER NOT NULL,
                nombre TEXT NOT NULL,
                ruta_wiki TEXT NOT NULL,
                orden INTEGER DEFAULT 0,
                tipo TEXT DEFAULT 'actividad',
                activo INTEGER DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (grupo_id) REFERENCES admin_control_groups(id) ON DELETE CASCADE
            )",
            'post_sql' => "INSERT OR IGNORE INTO admin_control_items (id, grupo_id, nombre, ruta_wiki, orden, tipo) VALUES
                (1, 1, 'Responsabilidades del Personal', 'varios:menu:1', 1, 'actividad'),
                (2, 1, 'Compromiso Etico', 'varios:menu:2', 2, 'actividad'),
                (3, 1, 'Recursos Humanos', 'varios:menu:4', 3, 'actividad'),
                (4, 1, 'Registros de cumplimiento general', 'encargos:general', 4, 'registro')"
        ];

        // Aplicadores (firma principal o trabajadores)
        $this->required_tables['admin_control_aplicadores'] = [
            'version' => 20,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_control_aplicadores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                tipo TEXT NOT NULL DEFAULT 'firma',
                nombre TEXT NOT NULL,
                usuario_id TEXT,
                es_principal INTEGER DEFAULT 0,
                pagina_discusion TEXT,
                activo INTEGER DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )",
            'post_sql' => "INSERT OR IGNORE INTO admin_control_aplicadores (id, tipo, nombre, es_principal) VALUES
                (1, 'firma', 'Firma Principal', 1)"
        ];

        // Progreso por aplicador y componente
        $this->required_tables['admin_control_progress'] = [
            'version' => 20,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_control_progress (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                aplicador_id INTEGER NOT NULL,
                item_id INTEGER NOT NULL,
                total_requerimientos INTEGER DEFAULT 0,
                requerimientos_terminados INTEGER DEFAULT 0,
                porcentaje_progreso REAL DEFAULT 0,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (aplicador_id) REFERENCES admin_control_aplicadores(id) ON DELETE CASCADE,
                FOREIGN KEY (item_id) REFERENCES admin_control_items(id) ON DELETE CASCADE,
                UNIQUE(aplicador_id, item_id)
            )"
        ];

        // Historial de exportaciones de comentarios
        $this->required_tables['admin_control_export_log'] = [
            'version' => 20,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_control_export_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                aplicador_id INTEGER,
                pagina_origen TEXT NOT NULL,
                pagina_destino TEXT NOT NULL,
                comentarios_exportados INTEGER DEFAULT 0,
                exportado_por TEXT NOT NULL,
                exportado_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (aplicador_id) REFERENCES admin_control_aplicadores(id)
            )"
        ];

        // =============================================
        // Tabla de log de tareas masivas (taskmassivas)
        // =============================================

        $this->required_tables['bulk_task_log'] = [
            'version' => 20,
            'sql' => "CREATE TABLE IF NOT EXISTS bulk_task_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER DEFAULT 0,
                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
            )"
        ];

        // =============================================
        // Tablas de Proyectos Administrativos (version 21)
        // =============================================

        // Proyectos administrativos (asociados a trabajadores)
        $this->required_tables['admin_thread'] = [
            'version' => 21,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                trabajador_id TEXT NOT NULL,
                trabajador_nombre TEXT NOT NULL,
                tipo_trabajador TEXT NOT NULL DEFAULT 'usuario',
                pagina_registros TEXT NOT NULL DEFAULT 'encargos:general',
                created_by TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                state TEXT NOT NULL DEFAULT 'opened',
                closed_by TEXT,
                closed_at DATETIME,
                fecha_inicio TEXT,
                fecha_fin TEXT,
                observaciones TEXT,
                periodo TEXT DEFAULT NULL,
                privado INTEGER DEFAULT 0,
                deleted_at DATETIME DEFAULT NULL
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS idx_admin_thread_trabajador ON admin_thread(trabajador_id);
                CREATE INDEX IF NOT EXISTS idx_admin_thread_state ON admin_thread(state);
                CREATE INDEX IF NOT EXISTS idx_admin_thread_tipo ON admin_thread(tipo_trabajador);
                CREATE INDEX IF NOT EXISTS idx_admin_thread_deleted ON admin_thread(deleted_at)"
        ];

        // Progreso por seccion del proyecto administrativo
        $this->required_tables['admin_thread_progress'] = [
            'version' => 21,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread_progress (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                admin_thread_id INTEGER NOT NULL,
                seccion_id INTEGER NOT NULL,
                seccion_nombre TEXT NOT NULL,
                ruta_wiki TEXT NOT NULL,
                total_requerimientos INTEGER DEFAULT 0,
                requerimientos_terminados INTEGER DEFAULT 0,
                porcentaje_progreso REAL DEFAULT 0,
                aporta_progreso INTEGER DEFAULT 1,
                visible INTEGER DEFAULT 1,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE,
                UNIQUE(admin_thread_id, seccion_id)
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS idx_admin_thread_progress_thread ON admin_thread_progress(admin_thread_id)"
        ];

        // Secuencia para consecutivos de proyectos administrativos
        $this->required_tables['admin_thread_sequence'] = [
            'version' => 21,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread_sequence (
                id INTEGER PRIMARY KEY,
                last_id INTEGER NOT NULL DEFAULT 0
            )",
            'post_sql' => "INSERT OR IGNORE INTO admin_thread_sequence (id, last_id) VALUES (1, 0)"
        ];

        // =============================================
        // Tabla de Preferencia de Proyecto Administrativo (version 25)
        // =============================================

        // Preferencia de proyecto (solo uno a la vez, global)
        $this->required_tables['admin_thread_preferencia'] = [
            'version' => 25,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread_preferencia (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                admin_thread_id INTEGER NOT NULL UNIQUE,
                marcado_por TEXT NOT NULL,
                marcado_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS idx_admin_thread_pref_thread ON admin_thread_preferencia(admin_thread_id); INSERT OR IGNORE INTO admin_thread_preferencia (admin_thread_id, marcado_por) VALUES (1, 'system')"
        ];

        // Tareas del proyecto administrativo (version 30)
        $this->required_tables['admin_thread_task'] = [
            'version' => 30,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread_task (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                admin_thread_id INTEGER NOT NULL,
                content TEXT NOT NULL,
                content_html TEXT,
                assignee TEXT,
                plan_date TEXT,
                state TEXT DEFAULT 'opened',
                created_by TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                closed_at DATETIME,
                closed_by TEXT,
                seccion_id INTEGER DEFAULT NULL,
                FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS idx_admin_task_thread ON admin_thread_task(admin_thread_id); CREATE INDEX IF NOT EXISTS idx_admin_task_state ON admin_thread_task(state); CREATE INDEX IF NOT EXISTS idx_admin_task_seccion ON admin_thread_task(seccion_id)"
        ];

        // Seguimiento (comentarios) del proyecto administrativo (version 30)
        $this->required_tables['admin_thread_comment'] = [
            'version' => 30,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread_comment (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                admin_thread_id INTEGER NOT NULL,
                content TEXT NOT NULL,
                content_html TEXT,
                author TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT NULL,
                updated_by TEXT DEFAULT NULL,
                parent_id INTEGER DEFAULT NULL,
                FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS idx_admin_comment_thread ON admin_thread_comment(admin_thread_id); CREATE INDEX IF NOT EXISTS idx_admin_comment_parent ON admin_thread_comment(parent_id)"
        ];

        // Participantes del proyecto administrativo (version 30)
        $this->required_tables['admin_thread_participant'] = [
            'version' => 30,
            'sql' => "CREATE TABLE IF NOT EXISTS admin_thread_participant (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                admin_thread_id INTEGER NOT NULL,
                user_id TEXT NOT NULL,
                role TEXT DEFAULT 'participant',
                added_by TEXT NOT NULL,
                added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(admin_thread_id, user_id),
                FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
            )",
            'post_sql' => "CREATE INDEX IF NOT EXISTS idx_admin_participant_thread ON admin_thread_participant(admin_thread_id)"
        ];

        // Proyecto administrativo por defecto: Firma Contable (01/01/2025)
        // post_sql solo corre al crear la tabla (instalación nueva), así que es seguro insertar.
        // Para instalaciones existentes, AdminThreadFactory::ensureDefaultProject() se encarga
        // y solo crea el proyecto si NO existe ningún otro proyecto administrativo.
        $this->required_tables['admin_thread']['post_sql'] =
            "INSERT OR IGNORE INTO admin_thread (id, trabajador_id, trabajador_nombre, tipo_trabajador, pagina_registros, created_by, created_at, fecha_inicio, periodo, privado)
             VALUES (1, 'firma_principal', 'Firma Contable', 'firma', 'encargos:general', 'system', '2025-01-01 00:00:00', '2025-01-01', '2025', 0)";
        $this->required_tables['admin_thread_progress']['post_sql'] =
            "INSERT OR IGNORE INTO admin_thread_progress (admin_thread_id, seccion_id, seccion_nombre, ruta_wiki, aporta_progreso) VALUES (1, 1, 'Responsabilidades de la Dirección', 'proyectos:administrativos:1:1_responsabilidades', 1);"
            . " INSERT OR IGNORE INTO admin_thread_progress (admin_thread_id, seccion_id, seccion_nombre, ruta_wiki, aporta_progreso) VALUES (1, 2, 'Requisitos Éticos', 'proyectos:administrativos:1:2_requisitos_eticos', 1);"
            . " INSERT OR IGNORE INTO admin_thread_progress (admin_thread_id, seccion_id, seccion_nombre, ruta_wiki, aporta_progreso) VALUES (1, 3, 'Recursos Humanos', 'proyectos:administrativos:1:3_recursos_humanos', 1);"
            . " INSERT OR IGNORE INTO admin_thread_progress (admin_thread_id, seccion_id, seccion_nombre, ruta_wiki, aporta_progreso) VALUES (1, 4, 'Registros de Cumplimiento General', 'encargos:general', 0)";
        // Asegurar que el sequence refleje el ID 1 usado por el proyecto por defecto
        $this->required_tables['admin_thread_sequence']['post_sql'] =
            "INSERT OR IGNORE INTO admin_thread_sequence (id, last_id) VALUES (1, 1)";

        // =============================================
        // Tabla de archivos de tareas - Plugin DO (version 8)
        // =============================================
        $this->required_tables['task_filename'] = [
            'version' => 8,
            'sql' => "CREATE TABLE IF NOT EXISTS task_filename (
                page TEXT NOT NULL,
                md5 TEXT NOT NULL,
                filename TEXT,
                path TEXT,
                PRIMARY KEY (page, md5)
            )"
        ];
    }

    /**
     * Obtiene la conexion a la base de datos
     * SIEMPRE usa conexion directa PDO para evitar errores de migracion
     * Esto permite reparar BD corruptas o incompletas
     */
    protected function getDatabase()
    {
        if ($this->db_initialized) {
            return $this->sqlite;
        }

        $this->db_initialized = true;

        $dbFile = $this->getDatabasePath();

        // Si el archivo no existe, crear uno nuevo
        if (!file_exists($dbFile)) {
            $this->log[] = 'Advertencia: Archivo de base de datos no existe, se creara uno nuevo: ' . $dbFile;
            // Touch the file to create it
            touch($dbFile);
        }

        // SIEMPRE usar conexion directa PDO para evitar que las migraciones fallen
        // Esto es CRITICO para poder reparar BD corruptas o incompletas
        try {
            $this->sqlite = $this->getDirectConnection($dbFile);
            $this->log[] = 'Conexion directa PDO establecida correctamente';

            // Verificar y crear tabla opts si no existe
            $this->ensureOptsTable();

        } catch (Exception $e) {
            $this->log[] = 'Error conexion directa: ' . $e->getMessage();
            $this->sqlite = null;
        }

        return $this->sqlite;
    }

    /**
     * Asegura que la tabla opts exista (requerida para el sistema de versiones)
     */
    protected function ensureOptsTable()
    {
        if (!$this->sqlite) return;

        try {
            // Verificar si la tabla opts existe
            $result = $this->sqlite->query("SELECT name FROM sqlite_master WHERE type='table' AND name='opts'");
            $row = $this->sqlite->res2row($result);

            if (!$row) {
                // Crear tabla opts
                $this->sqlite->query("CREATE TABLE IF NOT EXISTS opts (opt TEXT NOT NULL PRIMARY KEY, val NOT NULL DEFAULT '')");
                $this->sqlite->query("INSERT OR IGNORE INTO opts (opt, val) VALUES ('dbversion', '0')");
                $this->log[] = 'Tabla opts creada';
            }
        } catch (Exception $e) {
            $this->log[] = 'Error creando tabla opts: ' . $e->getMessage();
        }
    }

    /**
     * Conexion directa PDO al archivo SQLite (para reparaciones)
     */
    protected function getDirectConnection($dbFile)
    {
        // Crear un wrapper simple que imite la interfaz de sqlite helper
        return new BezDbDirectConnection($dbFile);
    }

    /**
     * Obtiene la ruta del archivo de base de datos BEZ
     */
    protected function getDatabasePath()
    {
        global $conf;
        return $conf['metadir'] . '/b3p.sqlite3';
    }

    /**
     * Obtiene la ruta del archivo de base de datos DO
     */
    protected function getDoDatabasePath()
    {
        global $conf;
        return $conf['metadir'] . '/do.sqlite3';
    }

    /**
     * Obtiene la conexion a la base de datos DO
     */
    protected function getDoDatabase()
    {
        if ($this->db_do_initialized) {
            return $this->sqlite_do;
        }

        $this->db_do_initialized = true;
        $dbFile = $this->getDoDatabasePath();

        if (!file_exists($dbFile)) {
            $this->log[] = 'BD do.sqlite3 no encontrada: ' . $dbFile;
            return null;
        }

        try {
            $this->sqlite_do = $this->getDirectConnection($dbFile);
            $this->log[] = 'Conexion a do.sqlite3 establecida';
        } catch (Exception $e) {
            $this->log[] = 'Error conexion do.sqlite3: ' . $e->getMessage();
            $this->sqlite_do = null;
        }

        return $this->sqlite_do;
    }

    /**
     * Obtiene la version actual de la BD DO
     */
    protected function getCurrentDoDbVersion()
    {
        $db = $this->getDoDatabase();
        if (!$db) return 0;

        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='opts'");
            $tableExists = $db->res2row($result);
            if (!$tableExists) return 0;

            $result = $db->query("SELECT val FROM opts WHERE opt='dbversion'");
            $row = $db->res2row($result);
            return $row ? (int)$row['val'] : 0;
        } catch (Exception $e) {
            return 0;
        }
    }

    /**
     * Diagnostica el estado de la base de datos DO
     */
    protected function diagnoseDoDatabase()
    {
        $issues = [];
        $db = $this->getDoDatabase();

        if (!$db) {
            return ['error' => 'No se pudo conectar a la base de datos DO'];
        }

        $currentVersion = $this->getCurrentDoDbVersion();

        // Verificar version
        if ($currentVersion < self::TARGET_DO_DB_VERSION) {
            $issues[] = [
                'type' => 'version',
                'db' => 'do',
                'current' => $currentVersion,
                'target' => self::TARGET_DO_DB_VERSION,
                'status' => 'outdated',
                'fixable' => true
            ];
        }

        // Verificar tabla tasks
        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='tasks'");
            $tableExists = $db->res2row($result);

            if (!$tableExists) {
                $issues[] = [
                    'type' => 'table',
                    'db' => 'do',
                    'table' => 'tasks',
                    'status' => 'missing',
                    'fixable' => true
                ];
            } else {
                // Verificar duplicados en tasks
                $result = $db->query('SELECT COUNT(*) as cnt FROM (SELECT page, md5 FROM tasks GROUP BY page, md5 HAVING COUNT(*) > 1)');
                $row = $db->res2row($result);
                if ($row && $row['cnt'] > 0) {
                    // Contar total de registros duplicados
                    $result2 = $db->query('SELECT SUM(cnt - 1) as total FROM (SELECT COUNT(*) as cnt FROM tasks GROUP BY page, md5 HAVING cnt > 1)');
                    $row2 = $db->res2row($result2);
                    $issues[] = [
                        'type' => 'duplicates',
                        'db' => 'do',
                        'table' => 'tasks',
                        'count' => $row2['total'],
                        'status' => 'has_duplicates',
                        'fixable' => true,
                        'note' => 'Hay ' . $row2['total'] . ' registros duplicados que impiden crear indice UNIQUE'
                    ];
                }

                // Verificar indice UNIQUE
                $result = $db->query("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_tasks_page_md5'");
                $indexExists = $db->res2row($result);
                if (!$indexExists) {
                    $issues[] = [
                        'type' => 'index',
                        'db' => 'do',
                        'table' => 'tasks',
                        'index' => 'idx_tasks_page_md5',
                        'status' => 'missing',
                        'fixable' => true
                    ];
                }
            }
        } catch (Exception $e) {
            $issues[] = [
                'type' => 'error',
                'db' => 'do',
                'message' => $e->getMessage()
            ];
        }

        // Verificar tabla task_status
        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='task_status'");
            if (!$db->res2row($result)) {
                $issues[] = [
                    'type' => 'table',
                    'db' => 'do',
                    'table' => 'task_status',
                    'status' => 'missing',
                    'fixable' => true
                ];
            }
        } catch (Exception $e) {}

        // Verificar tabla task_filename (version 8)
        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='task_filename'");
            if (!$db->res2row($result)) {
                $issues[] = [
                    'type' => 'table',
                    'db' => 'do',
                    'table' => 'task_filename',
                    'status' => 'missing',
                    'fixable' => true
                ];
            }
        } catch (Exception $e) {}

        return $issues;
    }

    /**
     * Repara la base de datos DO
     */
    protected function repairDoDatabase($createBackup = true)
    {
        $results = [];
        $db = $this->getDoDatabase();

        if (!$db) {
            return ['error' => 'No se pudo conectar a la base de datos DO'];
        }

        // Crear backup
        if ($createBackup) {
            $dbPath = $this->getDoDatabasePath();
            if (file_exists($dbPath) && filesize($dbPath) > 0) {
                $backupResult = $this->createDoBackup();
                $results[] = [
                    'type' => 'backup',
                    'db' => 'do',
                    'status' => $backupResult['success'] ? 'created' : 'warning',
                    'success' => true,
                    'file' => $backupResult['success'] ? basename($backupResult['file']) : $backupResult['error']
                ];
            }
        }

        // PASO 1: Eliminar duplicados de tasks
        try {
            $result = $db->query('SELECT COUNT(*) as cnt FROM (SELECT page, md5 FROM tasks GROUP BY page, md5 HAVING COUNT(*) > 1)');
            $row = $db->res2row($result);

            if ($row && $row['cnt'] > 0) {
                // Contar antes
                $result = $db->query('SELECT COUNT(*) as total FROM tasks');
                $before = $db->res2row($result)['total'];

                // Eliminar duplicados manteniendo solo el registro mas reciente (por rowid)
                $db->query("DELETE FROM tasks WHERE rowid NOT IN (
                    SELECT MAX(rowid) FROM tasks GROUP BY page, md5
                )");

                // Contar despues
                $result = $db->query('SELECT COUNT(*) as total FROM tasks');
                $after = $db->res2row($result)['total'];
                $deleted = $before - $after;

                $results[] = [
                    'type' => 'duplicates',
                    'db' => 'do',
                    'table' => 'tasks',
                    'status' => 'removed',
                    'success' => true,
                    'message' => "Eliminados $deleted registros duplicados"
                ];
            }
        } catch (Exception $e) {
            $results[] = [
                'type' => 'duplicates',
                'db' => 'do',
                'table' => 'tasks',
                'status' => 'error',
                'success' => false,
                'message' => $e->getMessage()
            ];
        }

        // PASO 2: Crear indice UNIQUE si no existe
        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_tasks_page_md5'");
            if (!$db->res2row($result)) {
                $db->query("CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_page_md5 ON tasks(page, md5)");
                $results[] = [
                    'type' => 'index',
                    'db' => 'do',
                    'index' => 'idx_tasks_page_md5',
                    'status' => 'created',
                    'success' => true
                ];
            }
        } catch (Exception $e) {
            $results[] = [
                'type' => 'index',
                'db' => 'do',
                'index' => 'idx_tasks_page_md5',
                'status' => 'error',
                'success' => false,
                'message' => $e->getMessage()
            ];
        }

        // PASO 3: Crear indice para task_status si no existe
        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='index' AND name='idx_task_status_page_md5'");
            if (!$db->res2row($result)) {
                $db->query("CREATE UNIQUE INDEX IF NOT EXISTS idx_task_status_page_md5 ON task_status(page, md5)");
                $results[] = [
                    'type' => 'index',
                    'db' => 'do',
                    'index' => 'idx_task_status_page_md5',
                    'status' => 'created',
                    'success' => true
                ];
            }
        } catch (Exception $e) {
            // Ignorar si falla
        }

        // PASO 4: Crear tabla task_filename si no existe
        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='task_filename'");
            if (!$db->res2row($result)) {
                $db->query("CREATE TABLE IF NOT EXISTS task_filename (
                    page TEXT NOT NULL,
                    md5 TEXT NOT NULL,
                    filename TEXT,
                    path TEXT,
                    PRIMARY KEY (page, md5)
                )");
                $results[] = [
                    'type' => 'table',
                    'db' => 'do',
                    'table' => 'task_filename',
                    'status' => 'created',
                    'success' => true
                ];
            }
        } catch (Exception $e) {
            $results[] = [
                'type' => 'table',
                'db' => 'do',
                'table' => 'task_filename',
                'status' => 'error',
                'success' => false,
                'message' => $e->getMessage()
            ];
        }

        // PASO 5: Actualizar version
        $currentVersion = $this->getCurrentDoDbVersion();
        if ($currentVersion < self::TARGET_DO_DB_VERSION) {
            try {
                $db->query("UPDATE opts SET val = ? WHERE opt = 'dbversion'", self::TARGET_DO_DB_VERSION);
                $results[] = [
                    'type' => 'version',
                    'db' => 'do',
                    'from' => $currentVersion,
                    'to' => self::TARGET_DO_DB_VERSION,
                    'status' => 'updated',
                    'success' => true
                ];
            } catch (Exception $e) {
                $results[] = [
                    'type' => 'version',
                    'db' => 'do',
                    'status' => 'error',
                    'success' => false,
                    'message' => $e->getMessage()
                ];
            }
        }

        return $results;
    }

    /**
     * Crea backup de la BD DO
     */
    /**
     * Crea un backup de la base de datos DO
     * Automaticamente elimina backups anteriores (mantiene solo 1)
     */
    protected function createDoBackup()
    {
        $dbPath = $this->getDoDatabasePath();

        if (!file_exists($dbPath)) {
            return ['success' => false, 'error' => 'BD DO no encontrada'];
        }

        if (!is_dir($this->backupDir)) {
            if (!mkdir($this->backupDir, 0755, true)) {
                return ['success' => false, 'error' => 'No se pudo crear directorio de backup'];
            }
        }

        // Eliminar backups anteriores de DO (mantener solo el nuevo)
        $oldBackups = glob($this->backupDir . '/do_backup_*.sqlite3');
        foreach ($oldBackups as $oldFile) {
            $infoFile = str_replace('.sqlite3', '_info.txt', $oldFile);
            @unlink($oldFile);
            @unlink($infoFile);
        }

        $timestamp = date('Y-m-d_H-i-s');
        $backupFile = $this->backupDir . '/do_backup_' . $timestamp . '.sqlite3';

        if (copy($dbPath, $backupFile)) {
            // Crear archivo de info
            $infoFile = $this->backupDir . '/do_backup_' . $timestamp . '_info.txt';
            $info = "Backup de Base de Datos DO\n";
            $info .= "============================\n\n";
            $info .= "Fecha: " . date('Y-m-d H:i:s') . "\n";
            $info .= "Usuario: " . ($_SERVER['REMOTE_USER'] ?? 'unknown') . "\n";
            $info .= "Archivo original: " . $dbPath . "\n";
            $info .= "Tamaño: " . filesize($dbPath) . " bytes\n";
            $info .= "Version BD: " . $this->getCurrentDoDbVersion() . "\n";
            file_put_contents($infoFile, $info);

            return ['success' => true, 'file' => $backupFile, 'size' => filesize($backupFile)];
        }

        return ['success' => false, 'error' => 'No se pudo copiar el archivo'];
    }

    public function getMenuText($language)
    {
        return $this->getLang('menu');
    }

    public function getMenuSort()
    {
        return 5;
    }

    public function forAdminOnly()
    {
        return true;
    }

    /**
     * Crea un backup de la base de datos
     */
    /**
     * Crea un backup de la base de datos B3P
     * Automaticamente elimina backups anteriores (mantiene solo 1)
     */
    protected function createBackup()
    {
        $dbPath = $this->getDatabasePath();

        if (!file_exists($dbPath)) {
            return ['success' => false, 'error' => 'Base de datos no encontrada'];
        }

        // Crear directorio de backup si no existe
        if (!is_dir($this->backupDir)) {
            if (!mkdir($this->backupDir, 0755, true)) {
                return ['success' => false, 'error' => 'No se pudo crear directorio de backup'];
            }
        }

        // Eliminar backups anteriores de B3P (mantener solo el nuevo)
        $oldBackups = glob($this->backupDir . '/b3p_backup_*.sqlite3');
        foreach ($oldBackups as $oldFile) {
            $infoFile = str_replace('.sqlite3', '_info.txt', $oldFile);
            @unlink($oldFile);
            @unlink($infoFile);
        }

        $timestamp = date('Y-m-d_H-i-s');
        $backupFile = $this->backupDir . '/b3p_backup_' . $timestamp . '.sqlite3';

        // Copiar archivo
        if (copy($dbPath, $backupFile)) {
            // Crear archivo de info
            $infoFile = $this->backupDir . '/b3p_backup_' . $timestamp . '_info.txt';
            $info = "Backup de Base de Datos BEZ\n";
            $info .= "============================\n\n";
            $info .= "Fecha: " . date('Y-m-d H:i:s') . "\n";
            $info .= "Usuario: " . ($_SERVER['REMOTE_USER'] ?? 'unknown') . "\n";
            $info .= "Archivo original: " . $dbPath . "\n";
            $info .= "Tamaño: " . filesize($dbPath) . " bytes\n";
            $info .= "Version BD: " . $this->getCurrentDbVersion() . "\n";
            file_put_contents($infoFile, $info);

            return [
                'success' => true,
                'file' => $backupFile,
                'size' => filesize($backupFile)
            ];
        }

        return ['success' => false, 'error' => 'No se pudo copiar el archivo'];
    }

    /**
     * Lista todos los backups disponibles (B3P y DO)
     */
    protected function listBackups()
    {
        $backups = [];

        if (!is_dir($this->backupDir)) {
            return $backups;
        }

        // Backups de B3P
        $files = glob($this->backupDir . '/b3p_backup_*.sqlite3');
        foreach ($files as $file) {
            $basename = basename($file);
            preg_match('/b3p_backup_(\d{4}-\d{2}-\d{2}_\d{2}-\d{2}-\d{2})\.sqlite3/', $basename, $matches);
            $timestamp = isset($matches[1]) ? str_replace('_', ' ', $matches[1]) : 'unknown';

            $backups[] = [
                'file' => $file,
                'name' => $basename,
                'type' => 'b3p',
                'timestamp' => $timestamp,
                'size' => filesize($file),
                'info_file' => str_replace('.sqlite3', '_info.txt', $file)
            ];
        }

        // Backups de DO
        $files = glob($this->backupDir . '/do_backup_*.sqlite3');
        foreach ($files as $file) {
            $basename = basename($file);
            preg_match('/do_backup_(\d{4}-\d{2}-\d{2}_\d{2}-\d{2}-\d{2})\.sqlite3/', $basename, $matches);
            $timestamp = isset($matches[1]) ? str_replace('_', ' ', $matches[1]) : 'unknown';

            $backups[] = [
                'file' => $file,
                'name' => $basename,
                'type' => 'do',
                'timestamp' => $timestamp,
                'size' => filesize($file),
                'info_file' => str_replace('.sqlite3', '_info.txt', $file)
            ];
        }

        // Ordenar por fecha descendente
        usort($backups, function($a, $b) {
            return strcmp($b['timestamp'], $a['timestamp']);
        });

        return $backups;
    }

    /**
     * Limpia backups antiguos, manteniendo solo el mas reciente de cada tipo
     */
    protected function cleanOldBackups()
    {
        $deleted = ['b3p' => 0, 'do' => 0];

        if (!is_dir($this->backupDir)) {
            return $deleted;
        }

        // Limpiar backups antiguos de B3P (mantener solo 1)
        $b3pFiles = glob($this->backupDir . '/b3p_backup_*.sqlite3');
        if (count($b3pFiles) > 1) {
            // Ordenar por fecha (mas reciente primero)
            usort($b3pFiles, function($a, $b) {
                return filemtime($b) - filemtime($a);
            });

            // Eliminar todos excepto el primero (mas reciente)
            for ($i = 1; $i < count($b3pFiles); $i++) {
                $infoFile = str_replace('.sqlite3', '_info.txt', $b3pFiles[$i]);
                if (unlink($b3pFiles[$i])) {
                    $deleted['b3p']++;
                    if (file_exists($infoFile)) {
                        unlink($infoFile);
                    }
                }
            }
        }

        // Limpiar backups antiguos de DO (mantener solo 1)
        $doFiles = glob($this->backupDir . '/do_backup_*.sqlite3');
        if (count($doFiles) > 1) {
            usort($doFiles, function($a, $b) {
                return filemtime($b) - filemtime($a);
            });

            for ($i = 1; $i < count($doFiles); $i++) {
                $infoFile = str_replace('.sqlite3', '_info.txt', $doFiles[$i]);
                if (unlink($doFiles[$i])) {
                    $deleted['do']++;
                    if (file_exists($infoFile)) {
                        unlink($infoFile);
                    }
                }
            }
        }

        return $deleted;
    }

    /**
     * Elimina un backup especifico
     */
    protected function deleteBackup($backupFile)
    {
        if (!file_exists($backupFile)) {
            return ['success' => false, 'error' => 'Archivo no encontrado'];
        }

        // Verificar que el archivo esta en el directorio de backups
        $realPath = realpath($backupFile);
        $backupDirReal = realpath($this->backupDir);

        if (strpos($realPath, $backupDirReal) !== 0) {
            return ['success' => false, 'error' => 'Archivo fuera del directorio de backups'];
        }

        // Eliminar archivo principal
        if (unlink($backupFile)) {
            // Eliminar archivo de info si existe
            $infoFile = str_replace('.sqlite3', '_info.txt', $backupFile);
            if (file_exists($infoFile)) {
                unlink($infoFile);
            }
            return ['success' => true, 'file' => basename($backupFile)];
        }

        return ['success' => false, 'error' => 'No se pudo eliminar el archivo'];
    }

    /**
     * Elimina todos los backups
     */
    protected function deleteAllBackups()
    {
        $deleted = 0;

        if (!is_dir($this->backupDir)) {
            return $deleted;
        }

        $files = glob($this->backupDir . '/*_backup_*.sqlite3');
        foreach ($files as $file) {
            $infoFile = str_replace('.sqlite3', '_info.txt', $file);
            if (unlink($file)) {
                $deleted++;
                if (file_exists($infoFile)) {
                    unlink($infoFile);
                }
            }
        }

        return $deleted;
    }

    /**
     * Restaura un backup
     */
    protected function restoreBackup($backupFile)
    {
        $dbPath = $this->getDatabasePath();

        if (!file_exists($backupFile)) {
            return ['success' => false, 'error' => 'Archivo de backup no encontrado'];
        }

        // Crear backup del estado actual antes de restaurar
        $preRestoreBackup = $this->createBackup();
        if (!$preRestoreBackup['success']) {
            return ['success' => false, 'error' => 'No se pudo crear backup previo a restauracion'];
        }

        // Cerrar conexion actual
        $this->sqlite = null;
        $this->db_initialized = false;

        // Copiar backup sobre la BD actual
        if (copy($backupFile, $dbPath)) {
            return [
                'success' => true,
                'message' => 'Backup restaurado exitosamente'
            ];
        }

        return ['success' => false, 'error' => 'No se pudo restaurar el backup'];
    }

    /**
     * Verifica si una tabla existe
     */
    protected function tableExists($table)
    {
        $db = $this->getDatabase();
        if (!$db) return false;

        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name=?", $table);
            $row = $db->res2row($result);
            return !empty($row);
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Obtiene las columnas de una tabla
     */
    protected function getTableColumns($table)
    {
        $db = $this->getDatabase();
        if (!$db) return [];

        try {
            $result = $db->query("PRAGMA table_info($table)");
            $columns = [];
            $rows = $db->res2arr($result);
            foreach ($rows as $row) {
                $columns[] = $row['name'];
            }
            return $columns;
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * Verifica si una columna existe en una tabla
     */
    protected function columnExists($table, $column)
    {
        $columns = $this->getTableColumns($table);
        return in_array($column, $columns);
    }

    /**
     * Obtiene la version actual de la BD
     */
    protected function getCurrentDbVersion()
    {
        $db = $this->getDatabase();
        if (!$db) return 0;

        try {
            // Primero verificar si la tabla opts existe
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='opts'");
            $tableExists = $db->res2row($result);

            if (!$tableExists) {
                // La tabla opts no existe, la BD esta sin inicializar
                return 0;
            }

            $result = $db->query("SELECT val FROM opts WHERE opt='dbversion'");
            $row = $db->res2row($result);
            return $row ? (int)$row['val'] : 0;
        } catch (Exception $e) {
            $this->log[] = 'Error obteniendo version: ' . $e->getMessage();
            return 0;
        }
    }

    /**
     * Actualiza la version de la BD
     */
    protected function updateDbVersion($version)
    {
        $db = $this->getDatabase();
        if (!$db) return false;

        try {
            $db->query("UPDATE opts SET val = ? WHERE opt = 'dbversion'", $version);
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Verifica si la vista thread_view necesita actualizacion
     */
    protected function viewNeedsUpdate()
    {
        $db = $this->getDatabase();
        if (!$db) return false;

        try {
            $result = $db->query("SELECT sql FROM sqlite_master WHERE type='view' AND name='thread_view'");
            $row = $db->res2row($result);
            if (!$row) return true; // Vista no existe

            // Verificar si incluye filtro deleted_at y columna year
            $sql = $row['sql'];
            return strpos($sql, 'deleted_at') === false || strpos($sql, 'thread.year') === false;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Diagnostica el estado completo de la base de datos
     */
    protected function diagnose()
    {
        $issues = [];
        $db = $this->getDatabase();

        if (!$db) {
            return ['error' => 'No se pudo conectar a la base de datos. Logs: ' . implode('; ', $this->log)];
        }

        $currentVersion = $this->getCurrentDbVersion();

        // Verificar version
        if ($currentVersion < self::TARGET_DB_VERSION) {
            $issues[] = [
                'type' => 'version',
                'current' => $currentVersion,
                'target' => self::TARGET_DB_VERSION,
                'status' => 'outdated',
                'fixable' => true
            ];
        }

        // NUEVO: Verificar tablas BASE de BEZ (criticas)
        foreach ($this->base_tables as $table => $sql) {
            if (!$this->tableExists($table)) {
                $issues[] = [
                    'type' => 'base_table',
                    'table' => $table,
                    'status' => 'missing_critical',
                    'version' => 1,
                    'fixable' => true,  // AHORA ES REPARABLE
                    'note' => 'Tabla base de BEZ - Se puede crear automaticamente'
                ];
            }
        }

        // Verificar tablas adicionales faltantes
        foreach ($this->required_tables as $table => $config) {
            if (!$this->tableExists($table)) {
                $issues[] = [
                    'type' => 'table',
                    'table' => $table,
                    'status' => 'missing',
                    'version' => $config['version'],
                    'fixable' => true
                ];
            }
        }

        // Verificar columnas faltantes
        foreach ($this->required_columns as $table => $columns) {
            if ($this->tableExists($table)) {
                foreach ($columns as $column => $config) {
                    if (!$this->columnExists($table, $column)) {
                        $issues[] = [
                            'type' => 'column',
                            'table' => $table,
                            'column' => $column,
                            'status' => 'missing',
                            'version' => $config['version'],
                            'fixable' => true
                        ];
                    }
                }
            }
        }

        // Verificar vista thread_view
        if ($this->tableExists('thread')) {
            if ($this->viewNeedsUpdate()) {
                $issues[] = [
                    'type' => 'view',
                    'view' => 'thread_view',
                    'status' => 'needs_update',
                    'fixable' => true
                ];
            }

            // Verificar vista task_view
            if (!$this->viewExists('task_view')) {
                $issues[] = [
                    'type' => 'view',
                    'view' => 'task_view',
                    'status' => 'missing',
                    'fixable' => true
                ];
            }

            // Verificar vista thread_comment_view
            if (!$this->viewExists('thread_comment_view')) {
                $issues[] = [
                    'type' => 'view',
                    'view' => 'thread_comment_view',
                    'status' => 'missing',
                    'fixable' => true
                ];
            }
        }

        // Verificar vista admin_thread_view (debe incluir columnas de rutas)
        if ($this->tableExists('admin_thread') && $this->tableExists('admin_thread_progress')) {
            if (!$this->viewExists('admin_thread_view')) {
                $issues[] = [
                    'type' => 'view',
                    'view' => 'admin_thread_view',
                    'status' => 'missing',
                    'fixable' => true
                ];
            } elseif ($this->columnExists('admin_thread', 'ruta_manual_calidad')) {
                // Si la columna existe pero la vista no la incluye, necesita actualización
                try {
                    $db = $this->getDatabase();
                    $result = $db->query("SELECT sql FROM sqlite_master WHERE type='view' AND name='admin_thread_view'");
                    $row = $db->res2row($result);
                    if ($row && strpos($row['sql'], 'ruta_manual_calidad') === false) {
                        $issues[] = [
                            'type' => 'view',
                            'view' => 'admin_thread_view',
                            'status' => 'needs_update',
                            'fixable' => true
                        ];
                    }
                } catch (Exception $e) {
                    // Ignorar errores de diagnóstico
                }
            }
        }

        // Verificar tareas con source sin clasificar (calendar/bulk que siguen como 'manual')
        if ($this->tableExists('task') && $this->columnExists('task', 'source')) {
            $unclassified = 0;
            try {
                $db = $this->getDatabase();
                // Tareas del calendario que no tienen source='calendar'
                if ($this->tableExists('tax_task_log')) {
                    $res = $db->query("SELECT COUNT(*) as cnt FROM task WHERE source = 'manual' AND id IN (SELECT task_id FROM tax_task_log WHERE task_id IS NOT NULL)");
                    $row = $db->res2row($res);
                    if ($row && intval($row['cnt']) > 0) $unclassified += intval($row['cnt']);
                }
            } catch (Exception $e) {
                // Ignorar errores de diagnóstico
            }
            if ($unclassified > 0) {
                $issues[] = [
                    'type' => 'data',
                    'table' => 'task',
                    'column' => 'source',
                    'status' => 'unclassified',
                    'count' => $unclassified,
                    'fixable' => true,
                    'note' => "$unclassified tareas tributarias sin clasificar como 'calendar'"
                ];
            }
        }

        // Verificar indices importantes
        $requiredIndexes = [
            'thread' => ['thread_ix_last_activity_date'],
            'thread_comment' => ['thread_comment_ix_thread_id'],
            'task' => ['task_ix_thread_id_thread_comment_id', 'task_ix_task_program_id'],
            'label' => ['label_ix_name']
        ];

        foreach ($requiredIndexes as $table => $indexes) {
            if ($this->tableExists($table)) {
                foreach ($indexes as $indexName) {
                    if (!$this->indexExists($indexName)) {
                        $issues[] = [
                            'type' => 'index',
                            'table' => $table,
                            'index' => $indexName,
                            'status' => 'missing',
                            'fixable' => true
                        ];
                    }
                }
            }
        }

        return $issues;
    }

    /**
     * Verifica si una vista existe
     */
    protected function viewExists($viewName)
    {
        $db = $this->getDatabase();
        if (!$db) return false;

        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='view' AND name=?", $viewName);
            $row = $db->res2row($result);
            return !empty($row);
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Verifica si un indice existe
     */
    protected function indexExists($indexName)
    {
        $db = $this->getDatabase();
        if (!$db) return false;

        try {
            $result = $db->query("SELECT name FROM sqlite_master WHERE type='index' AND name=?", $indexName);
            $row = $db->res2row($result);
            return !empty($row);
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Repara todos los problemas encontrados
     */
    protected function repair($createBackup = true)
    {
        $results = [];
        $db = $this->getDatabase();

        if (!$db) {
            return ['error' => 'No se pudo conectar a la base de datos. Logs: ' . implode('; ', $this->log)];
        }

        // Crear backup antes de reparar (solo si el archivo existe y tiene contenido)
        $dbPath = $this->getDatabasePath();
        if ($createBackup && file_exists($dbPath) && filesize($dbPath) > 0) {
            $backupResult = $this->createBackup();
            if ($backupResult['success']) {
                $results[] = [
                    'type' => 'backup',
                    'status' => 'created',
                    'success' => true,
                    'file' => basename($backupResult['file'])
                ];
            } else {
                $results[] = [
                    'type' => 'backup',
                    'status' => 'warning',
                    'success' => true, // Continuar de todas formas
                    'message' => 'Backup fallido pero continuando: ' . $backupResult['error']
                ];
            }
        }

        // PASO 1: Crear tablas BASE de BEZ primero
        foreach ($this->base_tables as $table => $sql) {
            if (!$this->tableExists($table)) {
                try {
                    $db->query($sql);
                    $results[] = [
                        'type' => 'base_table',
                        'table' => $table,
                        'status' => 'created',
                        'success' => true
                    ];
                } catch (Exception $e) {
                    $results[] = [
                        'type' => 'base_table',
                        'table' => $table,
                        'status' => 'error',
                        'success' => false,
                        'message' => $e->getMessage()
                    ];
                }
            }
        }

        // PASO 2: Crear indices para tablas base
        $baseIndexes = [
            "CREATE INDEX IF NOT EXISTS thread_ix_last_activity_date ON thread (last_activity_date)",
            "CREATE INDEX IF NOT EXISTS thread_comment_ix_thread_id ON thread_comment (thread_id)",
            "CREATE INDEX IF NOT EXISTS task_ix_thread_id_thread_comment_id ON task (thread_id, thread_comment_id)",
            "CREATE INDEX IF NOT EXISTS task_ix_task_program_id ON task (task_program_id)",
            "CREATE INDEX IF NOT EXISTS label_ix_name ON label (name)"
        ];

        foreach ($baseIndexes as $indexSql) {
            try {
                $db->query($indexSql);
            } catch (Exception $e) {
                // Ignorar errores de indices ya existentes
            }
        }

        // PASO 3: Crear tablas adicionales
        foreach ($this->required_tables as $table => $config) {
            if (!$this->tableExists($table)) {
                try {
                    $db->query($config['sql']);

                    // Ejecutar SQL post-creacion si existe
                    if (!empty($config['post_sql'])) {
                        // Dividir si hay multiples statements
                        $postStatements = explode(';', $config['post_sql']);
                        foreach ($postStatements as $stmt) {
                            $stmt = trim($stmt);
                            if (!empty($stmt)) {
                                $db->query($stmt);
                            }
                        }
                    }

                    $results[] = [
                        'type' => 'table',
                        'table' => $table,
                        'status' => 'created',
                        'success' => true
                    ];
                } catch (Exception $e) {
                    $results[] = [
                        'type' => 'table',
                        'table' => $table,
                        'status' => 'error',
                        'success' => false,
                        'message' => $e->getMessage()
                    ];
                }
            }
        }

        // PASO 4: Agregar columnas faltantes
        foreach ($this->required_columns as $table => $columns) {
            if ($this->tableExists($table)) {
                foreach ($columns as $column => $config) {
                    if (!$this->columnExists($table, $column)) {
                        try {
                            $db->query($config['sql']);
                            $results[] = [
                                'type' => 'column',
                                'table' => $table,
                                'column' => $column,
                                'status' => 'added',
                                'success' => true
                            ];
                        } catch (Exception $e) {
                            // Ignorar error de columna duplicada
                            if (strpos($e->getMessage(), 'duplicate column') !== false) {
                                $results[] = [
                                    'type' => 'column',
                                    'table' => $table,
                                    'column' => $column,
                                    'status' => 'already_exists',
                                    'success' => true
                                ];
                            } else {
                                $results[] = [
                                    'type' => 'column',
                                    'table' => $table,
                                    'column' => $column,
                                    'status' => 'error',
                                    'success' => false,
                                    'message' => $e->getMessage()
                                ];
                            }
                        }
                    }
                }
            }
        }

        // PASO 4B: Popular columna year en threads existentes que no la tengan
        if ($this->columnExists('thread', 'year')) {
            try {
                $db->query("UPDATE thread SET year = CASE
                    WHEN title LIKE '%Año: ____%' AND SUBSTR(title, INSTR(title, 'Año: ') + 5, 4) GLOB '[0-9][0-9][0-9][0-9]'
                        THEN SUBSTR(title, INSTR(title, 'Año: ') + 5, 4)
                    ELSE SUBSTR(create_date, 1, 4)
                END
                WHERE year IS NULL");
                $results[] = [
                    'type' => 'data',
                    'table' => 'thread',
                    'column' => 'year',
                    'status' => 'populated',
                    'success' => true
                ];
            } catch (Exception $e) {
                $results[] = [
                    'type' => 'data',
                    'table' => 'thread',
                    'column' => 'year',
                    'status' => 'error',
                    'success' => false,
                    'message' => $e->getMessage()
                ];
            }
        }

        // PASO 4C: Popular columna source en tareas existentes
        if ($this->columnExists('task', 'source')) {
            // Backfill tareas del calendario tributario
            if ($this->tableExists('tax_task_log')) {
                try {
                    $db->query("UPDATE task SET source = 'calendar' WHERE source = 'manual' AND id IN (SELECT task_id FROM tax_task_log WHERE task_id IS NOT NULL)");
                    $results[] = [
                        'type' => 'data',
                        'table' => 'task',
                        'column' => 'source',
                        'status' => 'calendar_backfill',
                        'success' => true
                    ];
                } catch (Exception $e) {
                    $results[] = [
                        'type' => 'data',
                        'table' => 'task',
                        'column' => 'source',
                        'status' => 'error',
                        'success' => false,
                        'message' => 'Calendar backfill: ' . $e->getMessage()
                    ];
                }
            }

            // Backfill tareas de carga masiva (JSON)
            if ($this->tableExists('bulk_task_log')) {
                try {
                    $stmt = $db->query("SELECT id, task_ids FROM bulk_task_log WHERE task_ids IS NOT NULL AND task_ids != '' AND (reverted IS NULL OR reverted = 0)");
                    $rows = $db->res2arr($stmt);
                    $bulkCount = 0;
                    $pdo = $db->getAdapter()->getPdo();
                    $updateStmt = $pdo->prepare("UPDATE task SET source = 'bulk' WHERE id = ? AND source = 'manual'");
                    foreach ($rows as $row) {
                        $taskIds = @json_decode($row['task_ids'], true);
                        if (!is_array($taskIds)) continue;
                        foreach ($taskIds as $taskId) {
                            $taskId = intval($taskId);
                            if ($taskId > 0) {
                                $updateStmt->execute([$taskId]);
                                $bulkCount++;
                            }
                        }
                    }
                    $results[] = [
                        'type' => 'data',
                        'table' => 'task',
                        'column' => 'source',
                        'status' => 'bulk_backfill',
                        'success' => true,
                        'message' => "$bulkCount tareas procesadas"
                    ];
                } catch (Exception $e) {
                    $results[] = [
                        'type' => 'data',
                        'table' => 'task',
                        'column' => 'source',
                        'status' => 'error',
                        'success' => false,
                        'message' => 'Bulk backfill: ' . $e->getMessage()
                    ];
                }
            }
        }

        // PASO 5: Crear/Actualizar vistas
        $this->repairViews($db, $results);

        // PASO 6: Actualizar version de la BD
        $currentVersion = $this->getCurrentDbVersion();
        if ($currentVersion < self::TARGET_DB_VERSION) {
            if ($this->updateDbVersion(self::TARGET_DB_VERSION)) {
                $results[] = [
                    'type' => 'version',
                    'from' => $currentVersion,
                    'to' => self::TARGET_DB_VERSION,
                    'status' => 'updated',
                    'success' => true
                ];
            }
        }

        return $results;
    }

    /**
     * Repara las vistas de la base de datos
     */
    protected function repairViews($db, &$results)
    {
        // Vista thread_comment_view
        if ($this->tableExists('thread_comment') && $this->tableExists('thread')) {
            if (!$this->viewExists('thread_comment_view')) {
                try {
                    $db->query("CREATE VIEW IF NOT EXISTS thread_comment_view AS
                        SELECT thread_comment.*,
                               thread.coordinator AS coordinator
                        FROM thread_comment
                        JOIN thread ON thread_comment.thread_id = thread.id");
                    $results[] = ['type' => 'view', 'view' => 'thread_comment_view', 'status' => 'created', 'success' => true];
                } catch (Exception $e) {
                    $results[] = ['type' => 'view', 'view' => 'thread_comment_view', 'status' => 'error', 'success' => false, 'message' => $e->getMessage()];
                }
            }
        }

        // Vista task_view
        if ($this->tableExists('task')) {
            if (!$this->viewExists('task_view')) {
                try {
                    $db->query("CREATE VIEW IF NOT EXISTS task_view AS
                        SELECT
                            task.*,
                            task_program.name AS task_program_name,
                            thread.coordinator AS coordinator,
                            CASE WHEN task.state = 'done' THEN NULL
                                 WHEN task.plan_date >= date('now', '+1 month') THEN '2'
                                 WHEN task.plan_date >= date('now') THEN '1'
                                 ELSE '0' END AS priority
                        FROM task
                        LEFT JOIN task_program ON task.task_program_id = task_program.id
                        LEFT JOIN thread ON task.thread_id = thread.id");
                    $results[] = ['type' => 'view', 'view' => 'task_view', 'status' => 'created', 'success' => true];
                } catch (Exception $e) {
                    $results[] = ['type' => 'view', 'view' => 'task_view', 'status' => 'error', 'success' => false, 'message' => $e->getMessage()];
                }
            }
        }

        // Vista thread_view (con soporte para deleted_at si existe)
        if ($this->tableExists('thread')) {
            $needsUpdate = $this->viewNeedsUpdate();
            $hasDeletedAt = $this->columnExists('thread', 'deleted_at');

            if ($needsUpdate || !$this->viewExists('thread_view')) {
                try {
                    $db->query("DROP VIEW IF EXISTS thread_view");

                    // Verificar si la columna year existe en thread
                    $hasYear = $this->columnExists('thread', 'year');

                    if ($hasDeletedAt) {
                        // Version con deleted_at
                        $yearCol = $hasYear ? "thread.year,\n                                   " : "";
                        $viewSql = "CREATE VIEW thread_view AS
                            SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by,
                                   thread.private, thread.lock, thread.type,
                                   {$yearCol}thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date,
                                   thread.title, thread.content, thread.content_html,
                                   thread.task_count, thread.task_count_closed, thread.task_sum_cost,
                                   thread.deleted_at,
                                   label.id AS label_id,
                                   label.name AS label_name,
                                   (SELECT MAX(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority,
                                   CASE WHEN thread.state = 'proposal' THEN 0
                                        WHEN thread.state = 'opened' AND thread.task_count = 0 THEN 1
                                        WHEN thread.state = 'opened' THEN 2
                                        WHEN thread.state = 'closed' THEN 3
                                        WHEN thread.state = 'rejected' THEN 4 END AS sort,
                                   CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done'
                                        ELSE thread.state END AS state,
                                   (SELECT COUNT(*) FROM task WHERE type = 'corrective' AND thread_id=thread.id) AS corrective_count,
                                   (SELECT COUNT(*) FROM task WHERE type = 'preventive' AND thread_id=thread.id) AS preventive_count
                            FROM thread
                            LEFT JOIN thread_label ON thread.id = thread_label.thread_id
                            LEFT JOIN label ON label.id = thread_label.label_id
                            WHERE thread.deleted_at IS NULL";
                    } else {
                        // Version sin deleted_at
                        $yearCol = $hasYear ? "thread.year,\n                                   " : "";
                        $viewSql = "CREATE VIEW thread_view AS
                            SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by,
                                   thread.private, thread.lock, thread.type,
                                   {$yearCol}thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date,
                                   thread.title, thread.content, thread.content_html,
                                   thread.task_count, thread.task_count_closed, thread.task_sum_cost,
                                   label.id AS label_id,
                                   label.name AS label_name,
                                   (SELECT MIN(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority,
                                   CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done'
                                        ELSE thread.state END AS state
                            FROM thread
                            LEFT JOIN thread_label ON thread.id = thread_label.thread_id
                            LEFT JOIN label ON label.id = thread_label.label_id";
                    }

                    $db->query($viewSql);
                    $results[] = ['type' => 'view', 'view' => 'thread_view', 'status' => 'updated', 'success' => true];
                } catch (Exception $e) {
                    $results[] = ['type' => 'view', 'view' => 'thread_view', 'status' => 'error', 'success' => false, 'message' => $e->getMessage()];
                }
            }
        }

        // Vista admin_thread_view (con rutas de documentos si las columnas existen)
        if ($this->tableExists('admin_thread') && $this->tableExists('admin_thread_progress')) {
            $needsRecreate = false;

            // Verificar si la vista incluye las columnas nuevas
            if ($this->viewExists('admin_thread_view')) {
                try {
                    $result = $db->query("SELECT sql FROM sqlite_master WHERE type='view' AND name='admin_thread_view'");
                    $row = $db->res2row($result);
                    if ($row && strpos($row['sql'], 'ruta_manual_calidad') === false) {
                        $needsRecreate = true;
                    }
                } catch (Exception $e) {
                    $needsRecreate = true;
                }
            } else {
                $needsRecreate = true;
            }

            if ($needsRecreate) {
                try {
                    $db->query("DROP VIEW IF EXISTS admin_thread_view");

                    $hasRutas = $this->columnExists('admin_thread', 'ruta_manual_calidad');
                    $rutasCols = $hasRutas ? "at.ruta_manual_calidad,\n                        at.ruta_codigo_etica," : "";

                    $db->query("CREATE VIEW admin_thread_view AS
                        SELECT
                            at.id,
                            at.trabajador_id,
                            at.trabajador_nombre,
                            at.tipo_trabajador,
                            at.pagina_registros,
                            at.created_by,
                            at.created_at,
                            at.updated_at,
                            at.state,
                            at.closed_by,
                            at.closed_at,
                            at.fecha_inicio,
                            at.fecha_fin,
                            at.observaciones,
                            at.periodo,
                            at.privado,
                            {$rutasCols}
                            COALESCE(SUM(CASE WHEN atp.aporta_progreso = 1 THEN atp.total_requerimientos ELSE 0 END), 0) AS total_tareas,
                            COALESCE(SUM(CASE WHEN atp.aporta_progreso = 1 THEN atp.requerimientos_terminados ELSE 0 END), 0) AS tareas_completadas,
                            CASE
                                WHEN SUM(CASE WHEN atp.aporta_progreso = 1 THEN atp.total_requerimientos ELSE 0 END) > 0
                                THEN ROUND((SUM(CASE WHEN atp.aporta_progreso = 1 THEN atp.requerimientos_terminados ELSE 0 END) * 100.0) /
                                     SUM(CASE WHEN atp.aporta_progreso = 1 THEN atp.total_requerimientos ELSE 0 END), 2)
                                ELSE 0
                            END AS porcentaje_total
                        FROM admin_thread at
                        LEFT JOIN admin_thread_progress atp ON at.id = atp.admin_thread_id
                        WHERE at.deleted_at IS NULL
                        GROUP BY at.id");

                    $results[] = ['type' => 'view', 'view' => 'admin_thread_view', 'status' => 'updated', 'success' => true];
                } catch (Exception $e) {
                    $results[] = ['type' => 'view', 'view' => 'admin_thread_view', 'status' => 'error', 'success' => false, 'message' => $e->getMessage()];
                }
            }
        }
    }

    /**
     * Verifica integridad de la base de datos
     */
    protected function checkIntegrity()
    {
        $db = $this->getDatabase();
        if (!$db) return ['error' => 'No se pudo conectar'];

        try {
            $result = $db->query("PRAGMA integrity_check");
            $row = $db->res2row($result);
            return $row && $row['integrity_check'] === 'ok';
        } catch (Exception $e) {
            return false;
        }
    }

    public function html()
    {
        global $INPUT, $ID;

        $db = $this->getDatabase();

        echo '<h1>' . $this->getLang('title') . '</h1>';
        echo '<p>' . $this->getLang('description') . '</p>';

        // CSS integrado
        $this->printStyles();

        if (!$db) {
            echo '<div class="bezrepair-error">Error: No se pudo conectar a la base de datos BEZ.</div>';
            return;
        }

        $action = $INPUT->str('action');
        $sectok = $INPUT->str('sectok');

        // Acceso directo via GET (desde niccadminmenu u otro enlace)
        if ($_SERVER['REQUEST_METHOD'] === 'GET' && $action === 'filelisting_repair') {
            $this->showFilelistingRepairForm();
            return;
        }

        // Procesar acciones POST
        if ($_SERVER['REQUEST_METHOD'] === 'POST' && checkSecurityToken()) {
            switch ($action) {
                case 'diagnose':
                    $issues = $this->diagnose();
                    $this->showDiagnosisResults($issues);
                    return;

                case 'repair':
                    $results = $this->repair(true);
                    $this->showRepairResults($results);
                    return;

                case 'repair_no_backup':
                    $results = $this->repair(false);
                    $this->showRepairResults($results);
                    return;

                case 'backup':
                    $result = $this->createBackup();
                    $this->showBackupResult($result);
                    return;

                case 'restore':
                    $backupFile = $INPUT->str('backup_file');
                    if ($backupFile && file_exists($backupFile)) {
                        $result = $this->restoreBackup($backupFile);
                        $this->showRestoreResult($result);
                        return;
                    }
                    break;

                // =====================================================
                // ACCIONES PARA BD DO
                // =====================================================
                case 'diagnose_do':
                    $issues = $this->diagnoseDoDatabase();
                    $this->showDoDiagnosisResults($issues);
                    return;

                case 'repair_do':
                    $results = $this->repairDoDatabase(true);
                    $this->showDoRepairResults($results);
                    return;

                // =====================================================
                // ACCIONES PARA BACKUPS (requieren contraseña)
                // =====================================================
                case 'delete_backup':
                    $backupFile = $INPUT->str('backup_file');
                    $password = $INPUT->str('confirm_password');
                    if ($backupFile) {
                        if (!$this->verifyUserPassword($password)) {
                            $this->showPasswordError('Contraseña incorrecta. No se eliminó el backup.');
                            return;
                        }
                        $result = $this->deleteBackup($backupFile);
                        $this->showDeleteBackupResult($result);
                        return;
                    }
                    break;

                case 'delete_all_backups':
                    $password = $INPUT->str('confirm_password');
                    if (!$this->verifyUserPassword($password)) {
                        $this->showPasswordError('Contraseña incorrecta. No se eliminaron los backups.');
                        return;
                    }
                    $deleted = $this->deleteAllBackups();
                    $this->showDeleteAllBackupsResult($deleted);
                    return;

                case 'clean_old_backups':
                    $password = $INPUT->str('confirm_password');
                    if (!$this->verifyUserPassword($password)) {
                        $this->showPasswordError('Contraseña incorrecta. No se limpiaron los backups.');
                        return;
                    }
                    $deleted = $this->cleanOldBackups();
                    $this->showCleanBackupsResult($deleted);
                    return;

                // =====================================================
                // REPARACIÓN FILELISTING CONTROL ADMINISTRATIVO
                // =====================================================
                case 'repair_admin_filelisting':
                    $this->repairAdminFilelisting();
                    return;
            }
        }

        // Vista principal
        $this->showMainForm();
    }

    protected function showMainForm()
    {
        global $ID;

        // Estado actual de la BD
        echo '<div class="bezrepair-section">';
        echo '<h2>Estado Actual de la Base de Datos</h2>';

        $currentVersion = $this->getCurrentDbVersion();
        $integrity = $this->checkIntegrity();

        echo '<table class="bezrepair-info-table">';
        echo '<tr><td><strong>Archivo:</strong></td><td>' . htmlspecialchars($this->getDatabasePath()) . '</td></tr>';
        echo '<tr><td><strong>Version actual:</strong></td><td>' . $currentVersion . '</td></tr>';
        echo '<tr><td><strong>Version objetivo:</strong></td><td>' . self::TARGET_DB_VERSION . '</td></tr>';
        echo '<tr><td><strong>Integridad:</strong></td><td>';
        if ($integrity === true) {
            echo '<span class="status-ok">OK</span>';
        } elseif ($integrity === false) {
            echo '<span class="status-error">ERROR - Base de datos corrupta</span>';
        } else {
            echo '<span class="status-warning">No verificable</span>';
        }
        echo '</td></tr>';
        echo '</table>';

        if ($integrity === false) {
            echo '<div class="bezrepair-warning">';
            echo '<strong>ADVERTENCIA:</strong> La base de datos parece estar corrupta. ';
            echo 'Se recomienda crear un backup antes de intentar cualquier reparacion.';
            echo '</div>';
        }

        echo '</div>';

        // =====================================================
        // SECCION BD DO (Plugin de tareas)
        // =====================================================
        $doDbPath = $this->getDoDatabasePath();
        if (file_exists($doDbPath)) {
            echo '<div class="bezrepair-section">';
            echo '<h2>Base de Datos DO (Plugin Tareas)</h2>';

            $doVersion = $this->getCurrentDoDbVersion();
            $doIssues = $this->diagnoseDoDatabase();
            $hasDoDuplicates = false;
            $doDuplicateCount = 0;

            foreach ($doIssues as $issue) {
                if (isset($issue['type']) && $issue['type'] === 'duplicates') {
                    $hasDoDuplicates = true;
                    $doDuplicateCount = $issue['count'];
                }
            }

            echo '<table class="bezrepair-info-table">';
            echo '<tr><td><strong>Archivo:</strong></td><td>' . htmlspecialchars($doDbPath) . '</td></tr>';
            echo '<tr><td><strong>Version actual:</strong></td><td>' . $doVersion . '</td></tr>';
            echo '<tr><td><strong>Version objetivo:</strong></td><td>' . self::TARGET_DO_DB_VERSION . '</td></tr>';
            echo '<tr><td><strong>Estado:</strong></td><td>';

            if (empty($doIssues) || (isset($doIssues['error']))) {
                if (isset($doIssues['error'])) {
                    echo '<span class="status-error">' . htmlspecialchars($doIssues['error']) . '</span>';
                } else {
                    echo '<span class="status-ok">OK</span>';
                }
            } else {
                $issueCount = count($doIssues);
                echo '<span class="status-warning">' . $issueCount . ' problema(s) encontrado(s)</span>';
            }
            echo '</td></tr>';

            if ($hasDoDuplicates) {
                echo '<tr><td><strong>Duplicados:</strong></td><td>';
                echo '<span class="status-error">' . $doDuplicateCount . ' registros duplicados en tasks</span>';
                echo '</td></tr>';
            }
            echo '</table>';

            if ($hasDoDuplicates) {
                echo '<div class="bezrepair-warning">';
                echo '<strong>PROBLEMA DETECTADO:</strong> Hay ' . $doDuplicateCount . ' registros duplicados en la tabla tasks. ';
                echo 'Esto impide crear el indice UNIQUE y causa el error "UNIQUE constraint failed". ';
                echo 'Use el boton "Reparar BD DO" para eliminar duplicados y actualizar la estructura.';
                echo '</div>';
            }

            // Botones para BD DO
            echo '<div class="bezrepair-actions" style="margin-top: 15px;">';

            echo '<form method="post" style="display: inline-block; margin-right: 10px;">';
            echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
            echo '<input type="hidden" name="action" value="diagnose_do">';
            echo '<button type="submit" class="button">';
            echo '<span class="icon">🔍</span> Diagnosticar BD DO';
            echo '</button>';
            echo '</form>';

            echo '<form method="post" style="display: inline-block; margin-right: 10px;" onsubmit="return confirm(\'¿Reparar la base de datos DO? Se eliminaran duplicados y se crearan indices faltantes.\');">';
            echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
            echo '<input type="hidden" name="action" value="repair_do">';
            echo '<button type="submit" class="button primary">';
            echo '<span class="icon">🔧</span> Reparar BD DO';
            echo '</button>';
            echo '</form>';

            echo '</div>';
            echo '</div>';
        }

        // Botones de accion para BD BEZ
        echo '<div class="bezrepair-section">';
        echo '<h2>Acciones BD BEZ</h2>';

        echo '<div class="bezrepair-actions">';

        // Boton Diagnosticar
        echo '<form method="post" style="display: inline-block; margin-right: 10px;">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="diagnose">';
        echo '<button type="submit" class="button">';
        echo '<span class="icon">🔍</span> Diagnosticar';
        echo '</button>';
        echo '</form>';

        // Boton Crear Backup
        echo '<form method="post" style="display: inline-block; margin-right: 10px;">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="backup">';
        echo '<button type="submit" class="button secondary">';
        echo '<span class="icon">💾</span> Crear Backup';
        echo '</button>';
        echo '</form>';

        // Boton Reparar (con backup)
        echo '<form method="post" style="display: inline-block; margin-right: 10px;" onsubmit="return confirm(\'¿Desea reparar la base de datos? Se creara un backup automaticamente.\');">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="repair">';
        echo '<button type="submit" class="button primary">';
        echo '<span class="icon">🔧</span> Reparar (con backup)';
        echo '</button>';
        echo '</form>';

        echo '</div>';
        echo '</div>';

        // =====================================================
        // SECCIÓN: REPARACIÓN FILELISTING CONTROL ADMINISTRATIVO
        // =====================================================
        echo '<div class="bezrepair-section">';
        echo '<h2>Reparar Filelisting - Control Administrativo</h2>';
        echo '<p style="color:#888; font-size:0.9em;">Corrige las rutas de filelisting en las p&aacute;ginas de proyectos administrativos existentes ';
        echo '(Responsabilidades, Requisitos &Eacute;ticos, Recursos Humanos, Registros de Cumplimiento). ';
        echo 'Convierte rutas relativas o incorrectas a rutas absolutas seg&uacute;n el destino del proyecto.</p>';
        echo '<div class="bezrepair-actions">';
        echo '<form method="post" style="display: inline-block;" onsubmit="return confirm(\'Reparar filelisting de todas las paginas de control administrativo?\');">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="repair_admin_filelisting">';
        echo '<button type="submit" class="button primary">';
        echo '<span class="icon">&#128295;</span> Reparar Filelisting Administrativo';
        echo '</button>';
        echo '</form>';
        echo '</div>';
        echo '</div>';

        // Lista de backups
        $backups = $this->listBackups();
        echo '<div class="bezrepair-section">';
        echo '<h2>Backups Disponibles</h2>';

        if (!empty($backups)) {
            // Contar backups por tipo
            $b3pCount = count(array_filter($backups, function($b) { return $b['type'] === 'b3p'; }));
            $doCount = count(array_filter($backups, function($b) { return $b['type'] === 'do'; }));

            echo '<p>Total: ' . count($backups) . ' backups (B3P: ' . $b3pCount . ', DO: ' . $doCount . ')</p>';

            echo '<table class="bezrepair-table">';
            echo '<thead><tr>';
            echo '<th>Tipo</th>';
            echo '<th>Fecha</th>';
            echo '<th>Archivo</th>';
            echo '<th>Tamaño</th>';
            echo '<th>Acciones</th>';
            echo '</tr></thead>';
            echo '<tbody>';

            foreach ($backups as $backup) {
                echo '<tr>';
                // Tipo con color
                $typeClass = $backup['type'] === 'b3p' ? 'status-ok' : 'status-warning';
                $typeLabel = strtoupper($backup['type']);
                echo '<td><span class="' . $typeClass . '">' . $typeLabel . '</span></td>';
                echo '<td>' . htmlspecialchars($backup['timestamp']) . '</td>';
                echo '<td>' . htmlspecialchars($backup['name']) . '</td>';
                echo '<td>' . number_format($backup['size'] / 1024, 2) . ' KB</td>';
                echo '<td style="white-space: nowrap;">';

                // Boton Restaurar (solo para B3P por ahora)
                if ($backup['type'] === 'b3p') {
                    echo '<form method="post" style="display:inline; margin-right: 5px;" onsubmit="return confirm(\'¿Restaurar este backup?\');">';
                    echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
                    echo '<input type="hidden" name="action" value="restore">';
                    echo '<input type="hidden" name="backup_file" value="' . htmlspecialchars($backup['file']) . '">';
                    echo '<button type="submit" class="button small">Restaurar</button>';
                    echo '</form>';
                }

                // Boton Eliminar (con prompt de contraseña)
                echo '<button type="button" class="button small" style="background:#dc3545; border-color:#c82333; color:white;" ';
                echo 'onclick="var pwd=prompt(\'Ingrese la contraseña para eliminar este backup:\'); if(pwd){';
                echo 'var f=document.createElement(\'form\'); f.method=\'post\'; f.action=\'\';';
                echo 'f.innerHTML=\'<input type=hidden name=sectok value=' . getSecurityToken() . '>';
                echo '<input type=hidden name=action value=delete_backup>';
                echo '<input type=hidden name=backup_file value=' . htmlspecialchars($backup['file']) . '>';
                echo '<input type=hidden name=confirm_password value=\'+pwd+\'>\';';
                echo 'document.body.appendChild(f); f.submit();}">Eliminar</button>';

                echo '</td>';
                echo '</tr>';
            }

            echo '</tbody></table>';

            // Botones de limpieza
            echo '<div class="bezrepair-actions" style="margin-top: 15px;">';

            // Limpiar antiguos (mantener solo 1 de cada tipo)
            if ($b3pCount > 1 || $doCount > 1) {
                echo '<button type="button" class="button" style="margin-right: 10px;" ';
                echo 'onclick="var pwd=prompt(\'Ingrese la contraseña para limpiar backups antiguos:\'); if(pwd){';
                echo 'var f=document.createElement(\'form\'); f.method=\'post\'; f.action=\'\';';
                echo 'f.innerHTML=\'<input type=hidden name=sectok value=' . getSecurityToken() . '>';
                echo '<input type=hidden name=action value=clean_old_backups>';
                echo '<input type=hidden name=confirm_password value=\'+pwd+\'>\';';
                echo 'document.body.appendChild(f); f.submit();}">';
                echo '<span class="icon">🧹</span> Limpiar Antiguos (mantener 1 de cada tipo)';
                echo '</button>';
            }

            // Eliminar todos (con prompt de contraseña)
            echo '<button type="button" class="button" style="background:#dc3545; border-color:#c82333; color:white;" ';
            echo 'onclick="var pwd=prompt(\'ADVERTENCIA: Esta accion eliminara TODOS los backups.\\nIngrese la contraseña para confirmar:\'); if(pwd){';
            echo 'var f=document.createElement(\'form\'); f.method=\'post\'; f.action=\'\';';
            echo 'f.innerHTML=\'<input type=hidden name=sectok value=' . getSecurityToken() . '>';
            echo '<input type=hidden name=action value=delete_all_backups>';
            echo '<input type=hidden name=confirm_password value=\'+pwd+\'>\';';
            echo 'document.body.appendChild(f); f.submit();}">';
            echo '<span class="icon">🗑️</span> Eliminar Todos';
            echo '</button>';

            echo '</div>';
        } else {
            echo '<p>No hay backups disponibles.</p>';
        }

        echo '</div>';

        // Informacion sobre migraciones
        echo '<div class="bezrepair-section">';
        echo '<h2>Migraciones Incluidas</h2>';
        echo '<table class="bezrepair-table">';
        echo '<thead><tr><th>Version</th><th>Descripcion</th><th>Estado</th></tr></thead>';
        echo '<tbody>';

        $migrations = [
            15 => 'Soft delete de proyectos (columna deleted_at en thread)',
            16 => 'Tabla thread_sequence para control de consecutivos',
            17 => 'Tabla task_priority_config para semaforizacion de tareas',
            18 => 'Modulo calendario tributario DIAN (10 tablas: tax_*, client_*)',
            19 => 'Columnas NIT/DV en clientes + tabla progreso_proyecto',
            20 => 'Control Administrativo (5 tablas) + bulk_task_log para tareas masivas',
            21 => 'Proyectos Administrativos (admin_thread, admin_thread_progress, admin_thread_sequence)',
            22 => 'Reversion de cargas masivas (task_ids, reverted en bulk_task_log)',
            23 => 'Columnas fecha_insercion y fecha_actualizacion en progreso_proyecto',
            24 => 'Vista thread_view actualizada con corrective_count/preventive_count',
            25 => 'Tabla admin_thread_preferencia para proyecto preferido',
            26 => 'Columnas ruta_manual_calidad y ruta_codigo_etica en admin_thread',
            27 => 'Tabla task_deadline_notification_log para evitar duplicados de notificacion',
            28 => 'Columna is_active en tax_calendar_master (validacion selectiva)',
            29 => 'Columna source en task (clasificacion de origen: manual/calendar/bulk)',
            30 => 'Tareas, seguimiento y participantes en proyectos administrativos (3 tablas)',
            31 => 'Editor rico en tareas/comentarios, clasificacion por seccion, edicion y respuestas'
        ];

        foreach ($migrations as $version => $description) {
            $status = $currentVersion >= $version ? '<span class="status-ok">Aplicada</span>' : '<span class="status-pending">Pendiente</span>';
            echo '<tr>';
            echo '<td>' . $version . '</td>';
            echo '<td>' . htmlspecialchars($description) . '</td>';
            echo '<td>' . $status . '</td>';
            echo '</tr>';
        }

        echo '</tbody></table>';
        echo '</div>';
    }

    protected function showDiagnosisResults($issues)
    {
        global $ID;

        echo '<h2>Resultado del Diagnostico</h2>';

        if (isset($issues['error'])) {
            echo '<div class="bezrepair-error">' . htmlspecialchars($issues['error']) . '</div>';
            return;
        }

        if (empty($issues)) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Base de datos en perfecto estado</strong><br>';
            echo 'No se encontraron problemas. La estructura esta actualizada a la version ' . self::TARGET_DB_VERSION . '.';
            echo '</div>';
            echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
            return;
        }

        echo '<div class="bezrepair-warning">';
        echo 'Se encontraron <strong>' . count($issues) . ' problemas</strong> que requieren atencion.';
        echo '</div>';

        echo '<table class="bezrepair-table">';
        echo '<thead><tr><th>Tipo</th><th>Elemento</th><th>Estado</th><th>Version</th><th>Reparable</th></tr></thead>';
        echo '<tbody>';

        $hasFixable = false;
        foreach ($issues as $issue) {
            if ($issue['fixable'] ?? false) $hasFixable = true;

            echo '<tr>';
            echo '<td>' . ucfirst($issue['type']) . '</td>';

            // Elemento
            if ($issue['type'] === 'version') {
                echo '<td>Version de BD</td>';
            } elseif ($issue['type'] === 'column' || $issue['type'] === 'data') {
                echo '<td>' . htmlspecialchars(($issue['table'] ?? '') . '.' . ($issue['column'] ?? '')) . '</td>';
            } elseif ($issue['type'] === 'view') {
                echo '<td>' . htmlspecialchars($issue['view']) . '</td>';
            } else {
                echo '<td>' . htmlspecialchars($issue['table'] ?? '') . '</td>';
            }

            // Estado
            echo '<td class="status-' . $issue['status'] . '">' . strtoupper($issue['status']) . '</td>';

            // Version
            if ($issue['type'] === 'version') {
                echo '<td>' . $issue['current'] . ' → ' . $issue['target'] . '</td>';
            } else {
                echo '<td>' . ($issue['version'] ?? '-') . '</td>';
            }

            // Reparable
            echo '<td>';
            if ($issue['fixable'] ?? false) {
                echo '<span class="status-ok">Si</span>';
            } else {
                echo '<span class="status-error">No</span>';
            }
            if (!empty($issue['note'])) {
                echo '<br><small>' . htmlspecialchars($issue['note']) . '</small>';
            }
            echo '</td>';

            echo '</tr>';
        }

        echo '</tbody></table>';

        // Botones
        echo '<div class="bezrepair-actions" style="margin-top: 20px;">';

        if ($hasFixable) {
            echo '<form method="post" style="display:inline-block; margin-right: 10px;" onsubmit="return confirm(\'¿Reparar los problemas encontrados?\\nSe creara un backup automaticamente.\');">';
            echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
            echo '<input type="hidden" name="action" value="repair">';
            echo '<button type="submit" class="button primary">🔧 Reparar Ahora</button>';
            echo '</form>';
        }

        echo '<a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a>';
        echo '</div>';
    }

    protected function showRepairResults($results)
    {
        echo '<h2>Resultado de la Reparacion</h2>';

        if (isset($results['error'])) {
            echo '<div class="bezrepair-error">' . htmlspecialchars($results['error']) . '</div>';
            echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
            return;
        }

        $successCount = 0;
        $errorCount = 0;

        echo '<table class="bezrepair-table">';
        echo '<thead><tr><th>Tipo</th><th>Elemento</th><th>Resultado</th></tr></thead>';
        echo '<tbody>';

        foreach ($results as $result) {
            $success = $result['success'] ?? false;
            if ($success) $successCount++;
            else $errorCount++;

            echo '<tr>';
            echo '<td>' . ucfirst($result['type']) . '</td>';

            // Elemento
            if ($result['type'] === 'backup') {
                echo '<td>Backup: ' . htmlspecialchars($result['file'] ?? '') . '</td>';
            } elseif ($result['type'] === 'column' || $result['type'] === 'data') {
                echo '<td>' . htmlspecialchars(($result['table'] ?? '') . '.' . ($result['column'] ?? '')) . '</td>';
            } elseif ($result['type'] === 'version') {
                echo '<td>Version: ' . $result['from'] . ' → ' . $result['to'] . '</td>';
            } elseif ($result['type'] === 'view') {
                echo '<td>' . htmlspecialchars($result['view']) . '</td>';
            } else {
                echo '<td>' . htmlspecialchars($result['table'] ?? '') . '</td>';
            }

            // Resultado
            echo '<td class="status-' . $result['status'] . '">';
            echo strtoupper($result['status']);
            if (!empty($result['message'])) {
                echo '<br><small>' . htmlspecialchars($result['message']) . '</small>';
            }
            echo '</td>';

            echo '</tr>';
        }

        echo '</tbody></table>';

        // Resumen
        if ($errorCount === 0) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Reparacion completada exitosamente</strong><br>';
            echo 'Se aplicaron ' . $successCount . ' cambios sin errores.';
            echo '</div>';
        } else {
            echo '<div class="bezrepair-warning">';
            echo 'Reparacion completada con ' . $errorCount . ' errores.';
            echo '</div>';
        }

        // Botones
        echo '<div class="bezrepair-actions" style="margin-top: 20px;">';
        echo '<form method="post" style="display:inline-block; margin-right: 10px;">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="diagnose">';
        echo '<button type="submit" class="button">Verificar de nuevo</button>';
        echo '</form>';
        echo '<a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a>';
        echo '</div>';
    }

    protected function showBackupResult($result)
    {
        echo '<h2>Resultado del Backup</h2>';

        if ($result['success']) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Backup creado exitosamente</strong><br>';
            echo 'Archivo: ' . htmlspecialchars(basename($result['file'])) . '<br>';
            echo 'Tamaño: ' . number_format($result['size'] / 1024, 2) . ' KB';
            echo '</div>';
        } else {
            echo '<div class="bezrepair-error">';
            echo '<strong>Error al crear backup:</strong> ' . htmlspecialchars($result['error']);
            echo '</div>';
        }

        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    protected function showRestoreResult($result)
    {
        echo '<h2>Resultado de la Restauracion</h2>';

        if ($result['success']) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Backup restaurado exitosamente</strong><br>';
            echo 'Se creo un backup del estado anterior antes de restaurar.';
            echo '</div>';
        } else {
            echo '<div class="bezrepair-error">';
            echo '<strong>Error al restaurar:</strong> ' . htmlspecialchars($result['error']);
            echo '</div>';
        }

        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    // =====================================================
    // METODOS PARA MOSTRAR RESULTADOS DE BD DO
    // =====================================================

    /**
     * Muestra resultados del diagnostico de BD DO
     */
    protected function showDoDiagnosisResults($issues)
    {
        echo '<h2>Diagnostico de Base de Datos DO</h2>';

        if (isset($issues['error'])) {
            echo '<div class="bezrepair-error">' . htmlspecialchars($issues['error']) . '</div>';
            echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
            return;
        }

        if (empty($issues)) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Base de datos DO en perfecto estado</strong><br>';
            echo 'No se encontraron problemas. Version: ' . self::TARGET_DO_DB_VERSION;
            echo '</div>';
            echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
            return;
        }

        echo '<div class="bezrepair-warning">';
        echo 'Se encontraron <strong>' . count($issues) . ' problemas</strong> en la BD DO.';
        echo '</div>';

        echo '<table class="bezrepair-table">';
        echo '<thead><tr><th>Tipo</th><th>Elemento</th><th>Estado</th><th>Detalles</th></tr></thead>';
        echo '<tbody>';

        foreach ($issues as $issue) {
            echo '<tr>';
            echo '<td>' . ucfirst($issue['type'] ?? 'unknown') . '</td>';

            // Elemento
            if (isset($issue['table'])) {
                echo '<td>' . htmlspecialchars($issue['table']) . '</td>';
            } elseif (isset($issue['index'])) {
                echo '<td>' . htmlspecialchars($issue['index']) . '</td>';
            } elseif ($issue['type'] === 'version') {
                echo '<td>Version de BD</td>';
            } else {
                echo '<td>-</td>';
            }

            // Estado
            $statusClass = 'status-' . ($issue['status'] ?? 'unknown');
            echo '<td class="' . $statusClass . '">' . strtoupper($issue['status'] ?? 'UNKNOWN') . '</td>';

            // Detalles
            echo '<td>';
            if ($issue['type'] === 'version') {
                echo $issue['current'] . ' → ' . $issue['target'];
            } elseif ($issue['type'] === 'duplicates') {
                echo $issue['count'] . ' registros duplicados';
            } elseif (isset($issue['note'])) {
                echo htmlspecialchars($issue['note']);
            } else {
                echo '-';
            }
            echo '</td>';

            echo '</tr>';
        }

        echo '</tbody></table>';

        // Botones
        echo '<div class="bezrepair-actions" style="margin-top: 20px;">';
        echo '<form method="post" style="display:inline-block; margin-right: 10px;" onsubmit="return confirm(\'¿Reparar la base de datos DO?\');">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="repair_do">';
        echo '<button type="submit" class="button primary">🔧 Reparar BD DO</button>';
        echo '</form>';
        echo '<a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a>';
        echo '</div>';
    }

    /**
     * Muestra resultados de la reparacion de BD DO
     */
    protected function showDoRepairResults($results)
    {
        echo '<h2>Resultado de Reparacion BD DO</h2>';

        if (isset($results['error'])) {
            echo '<div class="bezrepair-error">' . htmlspecialchars($results['error']) . '</div>';
            echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
            return;
        }

        $successCount = 0;
        $errorCount = 0;

        echo '<table class="bezrepair-table">';
        echo '<thead><tr><th>Tipo</th><th>Elemento</th><th>Resultado</th></tr></thead>';
        echo '<tbody>';

        foreach ($results as $result) {
            $success = $result['success'] ?? false;
            if ($success) $successCount++;
            else $errorCount++;

            echo '<tr>';
            echo '<td>' . ucfirst($result['type'] ?? 'unknown') . '</td>';

            // Elemento
            if ($result['type'] === 'backup') {
                echo '<td>Backup: ' . htmlspecialchars($result['file'] ?? '') . '</td>';
            } elseif ($result['type'] === 'duplicates') {
                echo '<td>Duplicados en ' . htmlspecialchars($result['table'] ?? 'tasks') . '</td>';
            } elseif ($result['type'] === 'version') {
                echo '<td>Version: ' . ($result['from'] ?? '?') . ' → ' . ($result['to'] ?? '?') . '</td>';
            } elseif (isset($result['index'])) {
                echo '<td>Indice: ' . htmlspecialchars($result['index']) . '</td>';
            } elseif (isset($result['table'])) {
                echo '<td>Tabla: ' . htmlspecialchars($result['table']) . '</td>';
            } else {
                echo '<td>-</td>';
            }

            // Resultado
            $statusClass = 'status-' . ($result['status'] ?? 'unknown');
            echo '<td class="' . $statusClass . '">';
            echo strtoupper($result['status'] ?? 'UNKNOWN');
            if (!empty($result['message'])) {
                echo '<br><small>' . htmlspecialchars($result['message']) . '</small>';
            }
            echo '</td>';

            echo '</tr>';
        }

        echo '</tbody></table>';

        // Resumen
        if ($errorCount === 0) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Reparacion de BD DO completada exitosamente</strong><br>';
            echo 'Se aplicaron ' . $successCount . ' cambios sin errores.';
            echo '</div>';
        } else {
            echo '<div class="bezrepair-warning">';
            echo 'Reparacion completada con ' . $errorCount . ' errores.';
            echo '</div>';
        }

        // Botones
        echo '<div class="bezrepair-actions" style="margin-top: 20px;">';
        echo '<form method="post" style="display:inline-block; margin-right: 10px;">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="diagnose_do">';
        echo '<button type="submit" class="button">Verificar de nuevo</button>';
        echo '</form>';
        echo '<a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a>';
        echo '</div>';
    }

    // =====================================================
    // METODOS PARA MOSTRAR RESULTADOS DE BACKUPS
    // =====================================================

    /**
     * Muestra error de contraseña incorrecta
     */
    protected function showPasswordError($message)
    {
        echo '<h2>Error de Autenticación</h2>';
        echo '<div class="bezrepair-error">';
        echo '<strong>⚠ Acceso Denegado:</strong> ' . htmlspecialchars($message);
        echo '</div>';
        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    /**
     * Muestra resultado de eliminar un backup
     */
    protected function showDeleteBackupResult($result)
    {
        echo '<h2>Eliminar Backup</h2>';

        if ($result['success']) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Backup eliminado exitosamente</strong><br>';
            echo 'Archivo: ' . htmlspecialchars($result['file']);
            echo '</div>';
        } else {
            echo '<div class="bezrepair-error">';
            echo '<strong>Error:</strong> ' . htmlspecialchars($result['error']);
            echo '</div>';
        }

        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    /**
     * Muestra resultado de eliminar todos los backups
     */
    protected function showDeleteAllBackupsResult($deleted)
    {
        echo '<h2>Eliminar Todos los Backups</h2>';

        if ($deleted > 0) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Backups eliminados</strong><br>';
            echo 'Se eliminaron ' . $deleted . ' archivos de backup.';
            echo '</div>';
        } else {
            echo '<div class="bezrepair-info">';
            echo 'No habia backups para eliminar.';
            echo '</div>';
        }

        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    /**
     * Muestra resultado de limpiar backups antiguos
     */
    protected function showCleanBackupsResult($deleted)
    {
        echo '<h2>Limpiar Backups Antiguos</h2>';

        $total = $deleted['b3p'] + $deleted['do'];

        if ($total > 0) {
            echo '<div class="bezrepair-success">';
            echo '<strong>✓ Backups antiguos eliminados</strong><br>';
            echo 'Se mantiene solo el backup mas reciente de cada tipo.<br>';
            echo '<ul>';
            if ($deleted['b3p'] > 0) {
                echo '<li>B3P: ' . $deleted['b3p'] . ' backups antiguos eliminados</li>';
            }
            if ($deleted['do'] > 0) {
                echo '<li>DO: ' . $deleted['do'] . ' backups antiguos eliminados</li>';
            }
            echo '</ul>';
            echo '</div>';
        } else {
            echo '<div class="bezrepair-info">';
            echo 'No habia backups antiguos para eliminar. Ya solo existe un backup de cada tipo.';
            echo '</div>';
        }

        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    /**
     * Repara filelisting de paginas de control administrativo.
     * Usa AdminThreadFactory::repararPaginasExistentes().
     */
    protected function repairAdminFilelisting()
    {
        global $ID;

        echo '<h2>Reparaci&oacute;n de Filelisting - Control Administrativo</h2>';

        $db = $this->getDatabase();
        if (!$db) {
            echo '<div class="bezrepair-error">Error: No se pudo conectar a la base de datos.</div>';
            echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
            return;
        }

        try {
            $factory = new \dokuwiki\plugin\bez\mdl\AdminThreadFactory($db);
            $resultado = $factory->repararPaginasExistentes();

            if ($resultado['reparados'] > 0) {
                echo '<div class="bezrepair-success">';
                echo '<strong>&#10003; Reparaci&oacute;n completada</strong><br>';
                echo 'P&aacute;ginas reparadas: ' . $resultado['reparados'] . '<br>';
                if ($resultado['errores'] > 0) {
                    echo 'Errores: ' . $resultado['errores'] . '<br>';
                }
                echo '</div>';
            } else {
                echo '<div class="bezrepair-info">';
                echo 'No se encontraron p&aacute;ginas que necesiten reparaci&oacute;n.';
                echo '</div>';
            }

            // Mostrar detalles
            if (!empty($resultado['detalles'])) {
                echo '<div class="bezrepair-section" style="margin-top: 15px;">';
                echo '<h3>Detalle de operaciones</h3>';
                echo '<ul style="font-family: monospace; font-size: 0.85em;">';
                foreach ($resultado['detalles'] as $detalle) {
                    echo '<li>' . htmlspecialchars($detalle) . '</li>';
                }
                echo '</ul>';
                echo '</div>';
            }

        } catch (\Exception $e) {
            echo '<div class="bezrepair-error">';
            echo '<strong>Error:</strong> ' . htmlspecialchars($e->getMessage());
            echo '</div>';
        }

        echo '<p><a href="?do=admin&page=bezdbrepair" class="button">&laquo; Volver</a></p>';
    }

    /**
     * Muestra formulario dedicado de reparacion de filelisting.
     * Accesible via GET: ?do=admin&page=bezdbrepair&action=filelisting_repair
     */
    protected function showFilelistingRepairForm()
    {
        global $ID;

        echo '<div class="bezrepair-section">';
        echo '<h2>&#128295; Reparar Filelisting - Control Administrativo</h2>';
        echo '<p>Esta herramienta corrige las rutas de <code>filelisting</code> en las p&aacute;ginas de proyectos administrativos existentes ';
        echo '(Responsabilidades, Requisitos &Eacute;ticos, Recursos Humanos, Registros de Cumplimiento).</p>';
        echo '<p>Convierte rutas relativas (<code>...:dir</code>) o incorrectas (<code>proyectos:administrativos:X:archivos:dir</code>) ';
        echo 'a rutas absolutas seg&uacute;n el destino del proyecto (<code>:varios:menu:dir</code>, <code>:varios:menu1:dir</code>, etc.).</p>';

        echo '<div class="bezrepair-actions" style="margin-top: 20px;">';
        echo '<form method="post" action="' . wl($ID, ['do' => 'admin', 'page' => 'bezdbrepair']) . '" ';
        echo 'onsubmit="return confirm(\'¿Reparar filelisting de todas las paginas de control administrativo?\');">';
        echo '<input type="hidden" name="sectok" value="' . getSecurityToken() . '">';
        echo '<input type="hidden" name="action" value="repair_admin_filelisting">';
        echo '<button type="submit" class="button primary" style="font-size: 1.1em; padding: 10px 25px;">';
        echo '&#128295; Ejecutar Reparaci&oacute;n de Filelisting';
        echo '</button>';
        echo '</form>';
        echo '</div>';

        echo '<div style="margin-top: 20px; padding: 15px; background: #fffbeb; border: 1px solid #f59e0b; border-radius: 6px;">';
        echo '<strong>&#9888; Nota:</strong> Esta operaci&oacute;n modifica las p&aacute;ginas wiki directamente. ';
        echo 'Se recomienda hacer un backup antes de ejecutar.';
        echo '</div>';

        echo '</div>';

        echo '<p style="margin-top: 20px;"><a href="?do=admin" class="button">&laquo; Volver al men&uacute;</a>';
        echo ' &nbsp; <a href="?do=admin&page=bezdbrepair" class="button">Ir a Reparar BD BEZ</a></p>';
    }

    protected function printStyles()
    {
        echo '<style>
            .bezrepair-section {
                background: #fff;
                border: 1px solid #ddd;
                border-radius: 8px;
                padding: 20px;
                margin: 20px 0;
            }
            .bezrepair-section h2 {
                margin-top: 0;
                padding-bottom: 10px;
                border-bottom: 1px solid #eee;
                color: #333;
            }
            .bezrepair-table {
                border-collapse: collapse;
                width: 100%;
                margin: 15px 0;
            }
            .bezrepair-table th, .bezrepair-table td {
                border: 1px solid #ddd;
                padding: 10px;
                text-align: left;
            }
            .bezrepair-table th {
                background: #f5f5f5;
                font-weight: 600;
            }
            .bezrepair-table tr:hover {
                background: #fafafa;
            }
            .bezrepair-info-table {
                margin: 15px 0;
            }
            .bezrepair-info-table td {
                padding: 8px 15px 8px 0;
            }
            .bezrepair-actions {
                margin: 20px 0;
            }
            .button {
                display: inline-block;
                padding: 10px 20px;
                background: #f5f5f5;
                border: 1px solid #ccc;
                border-radius: 4px;
                color: #333;
                text-decoration: none;
                cursor: pointer;
                font-size: 14px;
            }
            .button:hover {
                background: #e9e9e9;
            }
            .button.primary {
                background: #2196F3;
                border-color: #1976D2;
                color: white;
            }
            .button.primary:hover {
                background: #1976D2;
            }
            .button.secondary {
                background: #4CAF50;
                border-color: #388E3C;
                color: white;
            }
            .button.secondary:hover {
                background: #388E3C;
            }
            .button.small {
                padding: 5px 10px;
                font-size: 12px;
            }
            .button .icon {
                margin-right: 5px;
            }
            .status-ok, .status-created, .status-added, .status-updated {
                color: #28a745;
                font-weight: bold;
            }
            .status-missing, .status-error, .status-failed, .status-missing_critical {
                color: #dc3545;
                font-weight: bold;
            }
            .status-outdated, .status-needs_update, .status-pending {
                color: #ffc107;
                font-weight: bold;
            }
            .status-warning {
                color: #fd7e14;
                font-weight: bold;
            }
            .status-already_exists {
                color: #6c757d;
            }
            .bezrepair-success {
                background: #d4edda;
                border: 1px solid #c3e6cb;
                color: #155724;
                padding: 15px;
                border-radius: 6px;
                margin: 15px 0;
            }
            .bezrepair-error {
                background: #f8d7da;
                border: 1px solid #f5c6cb;
                color: #721c24;
                padding: 15px;
                border-radius: 6px;
                margin: 15px 0;
            }
            .bezrepair-warning {
                background: #fff3cd;
                border: 1px solid #ffc107;
                color: #856404;
                padding: 15px;
                border-radius: 6px;
                margin: 15px 0;
            }
            .bezrepair-info {
                background: #d1ecf1;
                border: 1px solid #bee5eb;
                color: #0c5460;
                padding: 15px;
                border-radius: 6px;
                margin: 15px 0;
            }
        </style>';
    }

    public function handle()
    {
        // El procesamiento se hace en html()
    }
}

/**
 * Clase wrapper para conexion directa PDO a SQLite
 * Usada cuando el sistema normal de migraciones falla
 */
class BezDbDirectConnection
{
    /** @var PDO */
    protected $pdo;

    /** @var string */
    protected $dbFile;

    public function __construct($dbFile)
    {
        $this->dbFile = $dbFile;
        $this->pdo = new PDO('sqlite:' . $dbFile);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /**
     * Ejecuta una consulta SQL
     * Compatible con helper_plugin_sqlite: acepta parametros individuales
     * o un array de parametros como segundo argumento.
     */
    public function query()
    {
        $args = func_get_args();
        $sql = array_shift($args);

        // Aplanar: si el unico argumento restante es un array, usarlo directamente
        // Esto imita el comportamiento de helper_plugin_sqlite::query()
        if (count($args) === 1 && is_array($args[0])) {
            $args = $args[0];
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(empty($args) ? null : $args);

        return $stmt;
    }

    /**
     * Convierte resultado a array
     */
    public function res2arr($stmt)
    {
        if (!$stmt) return [];
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Convierte resultado a una fila
     */
    public function res2row($stmt)
    {
        if (!$stmt) return null;
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * Convierte resultado a un valor
     */
    public function res2single($stmt)
    {
        if (!$stmt) return null;
        $row = $stmt->fetch(PDO::FETCH_NUM);
        return $row ? $row[0] : null;
    }

    /**
     * Obtiene el archivo de base de datos
     */
    public function getDbFile()
    {
        return $this->dbFile;
    }
}
