-- =============================================
-- Sistema de Notificaciones y Extranet
-- Scripts SQL para crear todas las tablas necesarias
-- Fecha: 2026-04-20
-- =============================================

-- Tabla 1: Comunicados Internos
CREATE TABLE comunicados (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(255) NOT NULL,
    contenido TEXT NOT NULL,
    tipo ENUM('general', 'urgente', 'rh', 'ti', 'operaciones', 'admin') DEFAULT 'general',
    prioridad ENUM('baja', 'media', 'alta', 'critica') DEFAULT 'media',
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NULL,
    archivo_url VARCHAR(500) NULL,
    imagen_url VARCHAR(500) NULL,
    autor_id INT NOT NULL,
    visible_para JSON NULL COMMENT 'Array de roles o empleados',
    fijado BOOLEAN DEFAULT FALSE,
    estado ENUM('borrador', 'publicado', 'archivado') DEFAULT 'borrador',
    vistas INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_comunicados_estado (estado),
    INDEX idx_comunicados_tipo (tipo),
    INDEX idx_comunicados_fecha_inicio (fecha_inicio),
    INDEX idx_comunicados_fijado (fijado),
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 2: Proyectos
CREATE TABLE proyectos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    objetivo TEXT NULL,
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NULL,
    fecha_fin_real DATE NULL,
    estado ENUM('planificacion', 'en_progreso', 'pausado', 'completado', 'cancelado') DEFAULT 'planificacion',
    prioridad ENUM('baja', 'media', 'alta', 'critica') DEFAULT 'media',
    progreso TINYINT UNSIGNED DEFAULT 0 COMMENT '0-100',
    presupuesto DECIMAL(15,2) NULL,
    responsable_id INT NOT NULL,
    departamento_id INT NULL,
    campana_id INT NULL,
    etiquetas JSON NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_proyectos_estado (estado),
    INDEX idx_proyectos_responsable_id (responsable_id),
    FOREIGN KEY (responsable_id) REFERENCES empleados(EMP_ID) ON DELETE CASCADE,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(DEP_ID) ON DELETE SET NULL,
    FOREIGN KEY (campana_id) REFERENCES campanas(CAM_ID) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 3: Tareas de Proyecto
CREATE TABLE tareas_proyecto (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proyecto_id BIGINT UNSIGNED NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    asignado_a INT NULL,
    estado ENUM('pendiente', 'en_progreso', 'revision', 'completada', 'cancelada') DEFAULT 'pendiente',
    prioridad ENUM('baja', 'media', 'alta', 'critica') DEFAULT 'media',
    fecha_vencimiento DATE NULL,
    fecha_completada DATETIME NULL,
    orden INT UNSIGNED DEFAULT 0,
    dependencias JSON NULL COMMENT 'IDs de tareas dependientes',
    tiempo_estimado INT UNSIGNED NULL COMMENT 'Horas',
    tiempo_real INT UNSIGNED NULL COMMENT 'Horas',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_tareas_proyecto_id (proyecto_id),
    INDEX idx_tareas_estado (estado),
    INDEX idx_tareas_asignado_a (asignado_a),
    FOREIGN KEY (proyecto_id) REFERENCES proyectos(id) ON DELETE CASCADE,
    FOREIGN KEY (asignado_a) REFERENCES empleados(EMP_ID) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 4: Eventos Extranet
CREATE TABLE eventos_extranet (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    tipo ENUM('reunion', 'capacitacion', 'celebracion', 'conferencia', 'team_building', 'otro') DEFAULT 'reunion',
    modalidad ENUM('presencial', 'virtual', 'hibrido') DEFAULT 'presencial',
    fecha_inicio DATETIME NOT NULL,
    fecha_fin DATETIME NULL,
    hora_inicio TIME NULL,
    hora_fin TIME NULL,
    lugar VARCHAR(255) NULL,
    link_virtual VARCHAR(500) NULL,
    organizador_id INT NOT NULL,
    departamento_id INT NULL,
    imagen_url VARCHAR(500) NULL,
    cupo_maximo INT UNSIGNED NULL,
    requiere_confirmacion BOOLEAN DEFAULT FALSE,
    estado ENUM('borrador', 'publicado', 'en_curso', 'finalizado', 'cancelado') DEFAULT 'borrador',
    color VARCHAR(7) DEFAULT '#007bff' COMMENT 'Color hexadecimal',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_eventos_fecha_inicio (fecha_inicio),
    INDEX idx_eventos_tipo (tipo),
    INDEX idx_eventos_estado (estado),
    FOREIGN KEY (organizador_id) REFERENCES empleados(EMP_ID) ON DELETE CASCADE,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(DEP_ID) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 5: Asistentes a Eventos
CREATE TABLE asistentes_evento (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    evento_id BIGINT UNSIGNED NOT NULL,
    empleado_id INT NOT NULL,
    estado_confirmacion ENUM('pendiente', 'confirmado', 'rechazado') DEFAULT 'pendiente',
    asistio BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY unique_asistente (evento_id, empleado_id),
    FOREIGN KEY (evento_id) REFERENCES eventos_extranet(id) ON DELETE CASCADE,
    FOREIGN KEY (empleado_id) REFERENCES empleados(EMP_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 6: Galerías
CREATE TABLE galerias (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    evento_id BIGINT UNSIGNED NULL,
    fecha DATE NOT NULL,
    autor_id INT NOT NULL,
    portada_url VARCHAR(500) NULL,
    visible_para JSON NULL,
    total_fotos INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_galerias_fecha (fecha),
    FOREIGN KEY (evento_id) REFERENCES eventos_extranet(id) ON DELETE SET NULL,
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 7: Fotos de Galería
CREATE TABLE fotos_galeria (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    galeria_id BIGINT UNSIGNED NOT NULL,
    archivo_url VARCHAR(500) NOT NULL,
    descripcion VARCHAR(500) NULL,
    orden INT UNSIGNED DEFAULT 0,
    likes INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_fotos_galeria_id (galeria_id),
    INDEX idx_fotos_orden (orden),
    FOREIGN KEY (galeria_id) REFERENCES galerias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 8: Reconocimientos
CREATE TABLE reconocimientos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id INT NOT NULL,
    tipo ENUM('empleado_mes', 'aniversario', 'logro', 'excelencia', 'innovacion', 'trabajo_equipo', 'otro') DEFAULT 'logro',
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NOT NULL,
    otorgado_por INT NOT NULL,
    fecha DATE NOT NULL,
    imagen_url VARCHAR(500) NULL,
    publico BOOLEAN DEFAULT TRUE,
    destacado BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_reconocimientos_empleado_id (empleado_id),
    INDEX idx_reconocimientos_tipo (tipo),
    INDEX idx_reconocimientos_fecha (fecha),
    FOREIGN KEY (empleado_id) REFERENCES empleados(EMP_ID) ON DELETE CASCADE,
    FOREIGN KEY (otorgado_por) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 9: Encuestas
CREATE TABLE encuestas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    autor_id INT NOT NULL,
    fecha_inicio DATETIME NOT NULL,
    fecha_fin DATETIME NULL,
    anonima BOOLEAN DEFAULT TRUE,
    visible_para JSON NULL,
    estado ENUM('borrador', 'activa', 'cerrada') DEFAULT 'borrador',
    total_respuestas INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_encuestas_estado (estado),
    INDEX idx_encuestas_fecha_inicio (fecha_inicio),
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 10: Preguntas de Encuesta
CREATE TABLE preguntas_encuesta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    encuesta_id BIGINT UNSIGNED NOT NULL,
    pregunta TEXT NOT NULL,
    tipo_respuesta ENUM('texto_corto', 'texto_largo', 'opcion_multiple', 'checkbox', 'escala', 'fecha') DEFAULT 'texto_corto',
    opciones JSON NULL COMMENT 'Para opciones múltiples o checkbox',
    escala_min INT NULL,
    escala_max INT NULL,
    obligatoria BOOLEAN DEFAULT FALSE,
    orden INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_preguntas_encuesta_id (encuesta_id),
    INDEX idx_preguntas_orden (orden),
    FOREIGN KEY (encuesta_id) REFERENCES encuestas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 11: Respuestas de Encuesta
CREATE TABLE respuestas_encuesta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    encuesta_id BIGINT UNSIGNED NOT NULL,
    pregunta_id BIGINT UNSIGNED NOT NULL,
    empleado_id INT NULL COMMENT 'NULL si es anónima',
    respuesta TEXT NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_respuestas_encuesta_id (encuesta_id),
    INDEX idx_respuestas_pregunta_id (pregunta_id),
    INDEX idx_respuestas_empleado_id (empleado_id),
    FOREIGN KEY (encuesta_id) REFERENCES encuestas(id) ON DELETE CASCADE,
    FOREIGN KEY (pregunta_id) REFERENCES preguntas_encuesta(id) ON DELETE CASCADE,
    FOREIGN KEY (empleado_id) REFERENCES empleados(EMP_ID) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 12: Documentos Extranet
CREATE TABLE documentos_extranet (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    categoria ENUM('politicas', 'manuales', 'formatos', 'reglamentos', 'procedimientos', 'capacitacion', 'otro') DEFAULT 'otro',
    archivo_url VARCHAR(500) NOT NULL,
    archivo_nombre VARCHAR(255) NOT NULL,
    archivo_tipo VARCHAR(100) NULL,
    archivo_tamano INT UNSIGNED NULL COMMENT 'Bytes',
    version VARCHAR(20) DEFAULT '1.0',
    autor_id INT NOT NULL,
    departamento_id INT NULL,
    visible_para JSON NULL,
    descargas INT UNSIGNED DEFAULT 0,
    destacado BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_documentos_categoria (categoria),
    INDEX idx_documentos_departamento_id (departamento_id),
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(DEP_ID) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 13: Publicaciones del Muro
CREATE TABLE publicaciones_muro (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('comunicado', 'proyecto', 'evento', 'reconocimiento', 'cumpleanos', 'aniversario', 'nuevo_empleado', 'documento', 'encuesta', 'manual') NOT NULL,
    referencia_id BIGINT UNSIGNED NOT NULL COMMENT 'ID del registro origen',
    titulo VARCHAR(255) NOT NULL,
    contenido TEXT NULL,
    imagen_url VARCHAR(500) NULL,
    autor_id INT NULL,
    destacado BOOLEAN DEFAULT FALSE,
    comentarios_habilitados BOOLEAN DEFAULT TRUE,
    total_comentarios INT UNSIGNED DEFAULT 0,
    total_reacciones INT UNSIGNED DEFAULT 0,
    vistas INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_publicaciones_tipo (tipo),
    INDEX idx_created_at_desc (created_at),
    INDEX idx_publicaciones_destacado (destacado),
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 14: Comentarios Extranet
CREATE TABLE comentarios_extranet (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    publicacion_id BIGINT UNSIGNED NOT NULL,
    comentario_padre_id BIGINT UNSIGNED NULL COMMENT 'Para respuestas',
    autor_id INT NOT NULL,
    contenido TEXT NOT NULL,
    total_reacciones INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_comentarios_publicacion_id (publicacion_id),
    INDEX idx_comentarios_comentario_padre_id (comentario_padre_id),
    FOREIGN KEY (publicacion_id) REFERENCES publicaciones_muro(id) ON DELETE CASCADE,
    FOREIGN KEY (comentario_padre_id) REFERENCES comentarios_extranet(id) ON DELETE CASCADE,
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 15: Reacciones Extranet
CREATE TABLE reacciones_extranet (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reaccionable_type VARCHAR(50) NOT NULL COMMENT 'publicaciones_muro, comentarios_extranet',
    reaccionable_id BIGINT UNSIGNED NOT NULL,
    autor_id INT NOT NULL,
    tipo ENUM('like', 'love', 'haha', 'wow', 'sad', 'angry') DEFAULT 'like',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY unique_reaccion (reaccionable_type, reaccionable_id, autor_id),
    INDEX idx_reaccionable (reaccionable_type, reaccionable_id),
    FOREIGN KEY (autor_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla 16: Notificaciones Extranet
CREATE TABLE notificaciones_extranet (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id INT NOT NULL,
    tipo ENUM('comunicado', 'proyecto', 'evento', 'reconocimiento', 'comentario', 'reaccion', 'mencion', 'cumpleanos', 'aniversario', 'sistema') NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    mensaje TEXT NULL,
    referencia_tipo VARCHAR(50) NULL,
    referencia_id BIGINT UNSIGNED NULL,
    url VARCHAR(500) NULL,
    icono VARCHAR(50) NULL,
    color VARCHAR(7) NULL,
    leida BOOLEAN DEFAULT FALSE,
    leida_at TIMESTAMP NULL,
    importante BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    INDEX idx_notificaciones_empleado_id (empleado_id),
    INDEX idx_notificaciones_leida (leida),
    INDEX idx_notif_created_at_desc (created_at),
    FOREIGN KEY (empleado_id) REFERENCES empleados(EMP_ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Script de eliminación (en caso de ser necesario)
-- =============================================
/*
DROP TABLE IF EXISTS notificaciones_extranet;
DROP TABLE IF EXISTS reacciones_extranet;
DROP TABLE IF EXISTS comentarios_extranet;
DROP TABLE IF EXISTS publicaciones_muro;
DROP TABLE IF EXISTS documentos_extranet;
DROP TABLE IF EXISTS respuestas_encuesta;
DROP TABLE IF EXISTS preguntas_encuesta;
DROP TABLE IF EXISTS encuestas;
DROP TABLE IF EXISTS reconocimientos;
DROP TABLE IF EXISTS fotos_galeria;
DROP TABLE IF EXISTS galerias;
DROP TABLE IF EXISTS asistentes_evento;
DROP TABLE IF EXISTS eventos_extranet;
DROP TABLE IF EXISTS tareas_proyecto;
DROP TABLE IF EXISTS proyectos;
DROP TABLE IF EXISTS comunicados;
*/