-- =====================================================
-- MÓDULO DE PROYECTOS ADMINISTRATIVOS
-- Migración: update0021.sql
-- Fecha: 2026-01-31
-- Actualizado: 2026-02-01
-- Descripción: Tablas para proyectos administrativos
--              separados de proyectos operativos
--              Asociados a trabajadores de la firma
-- =====================================================

-- Tabla principal de proyectos administrativos
CREATE TABLE IF NOT EXISTS admin_thread (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Usuario asociado (trabajador de la firma o firma principal)
    trabajador_id TEXT NOT NULL,
    trabajador_nombre TEXT NOT NULL,

    -- Tipo de trabajador: 'usuario' (del sistema) o 'firma' (texto libre)
    tipo_trabajador TEXT NOT NULL DEFAULT 'usuario',

    -- Página de registros de cumplimiento (botón 4)
    pagina_registros TEXT NOT NULL DEFAULT 'encargos:general',

    -- Metadatos
    created_by TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    -- Estado
    state TEXT NOT NULL DEFAULT 'opened',
    closed_by TEXT,
    closed_at DATETIME,

    -- Fechas del proyecto
    fecha_inicio TEXT,
    fecha_fin TEXT,

    -- Observaciones adicionales
    observaciones TEXT,

    -- Periodo (año)
    periodo TEXT DEFAULT NULL,

    -- Privacidad (1=privado, 0=público)
    privado INTEGER DEFAULT 0,

    -- Soft delete
    deleted_at DATETIME DEFAULT NULL
);

-- Progreso por sección del proyecto administrativo
CREATE TABLE IF NOT EXISTS admin_thread_progress (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    admin_thread_id INTEGER NOT NULL,

    -- Identificador del botón/sección (1-4)
    seccion_id INTEGER NOT NULL,
    seccion_nombre TEXT NOT NULL,
    ruta_wiki TEXT NOT NULL,

    -- Progreso (calculado desde plugin DO)
    total_requerimientos INTEGER DEFAULT 0,
    requerimientos_terminados INTEGER DEFAULT 0,
    porcentaje_progreso REAL DEFAULT 0,

    -- Control
    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)
);

-- Secuencia para consecutivos
CREATE TABLE IF NOT EXISTS admin_thread_sequence (
    id INTEGER PRIMARY KEY,
    last_id INTEGER NOT NULL DEFAULT 0
);

-- Inicializar secuencia
INSERT OR IGNORE INTO admin_thread_sequence (id, last_id) VALUES (1, 0);

-- Índices para optimización
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_created ON admin_thread(created_at);
CREATE INDEX IF NOT EXISTS idx_admin_thread_deleted ON admin_thread(deleted_at);
CREATE INDEX IF NOT EXISTS idx_admin_thread_tipo ON admin_thread(tipo_trabajador);
CREATE INDEX IF NOT EXISTS idx_admin_thread_progress_thread ON admin_thread_progress(admin_thread_id);

-- Vista con progreso calculado (excluye eliminados)
CREATE VIEW IF NOT EXISTS 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,
    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;