-- BASE DE DATOS: ie_barrio_santander
CREATE DATABASE IF NOT EXISTS ie_barrio_santander;
USE ie_barrio_santander;

-- Tabla: Roles
CREATE TABLE Roles (
    id_rol INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    nombre_rol VARCHAR(50) UNIQUE,
    descripcion TEXT
);

-- Insertar roles básicos
INSERT INTO Roles (nombre_rol, descripcion) VALUES
('Super Admin', 'Administrador del sistema con permisos totales'),
('Administrativo', 'Personal administrativo de la institución educativa'),
('Estudiante', 'Estudiantes matriculados en la institución'),
('Docente', 'Profesores y personal docente de la institución'),
('Empleado', 'Personal de apoyo y servicios generales'),
('Coordinador', 'Coordinadores académicos y de convivencia'),
('Padre de Familia', 'Padres y acudientes de los estudiantes');

-- Tabla: Estudiantes
CREATE TABLE Estudiantes (
    id_estudiante INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    grado VARCHAR(50),
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);

-- Tabla: Docentes
CREATE TABLE Docentes (
    id_docente INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    especialidad VARCHAR(100),
    nivel VARCHAR(50),
    hoja_vida LONGBLOB,
    pdfs LONGBLOB,
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);

-- Tabla: Administrativos
CREATE TABLE Administrativos (
    id_administrativo INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    area VARCHAR(100),
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);

-- Tabla: Empleados
CREATE TABLE Empleados (
    id_empleado INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    cargo VARCHAR(100),
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);

-- Tabla: Super_Admin
CREATE TABLE Super_Admin (
    id_super_admin INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);

-- Tabla: Padres_Familia
CREATE TABLE Padres_Familia (
    id_padre INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    tipo_relacion VARCHAR(50),
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);

-- Tabla: Coordinadores
CREATE TABLE Coordinadores (
    id_coordinador INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT UNIQUE,
    area_coordinacion VARCHAR(100),
    FOREIGN KEY (id_usuario) REFERENCES Usuarios(id_usuario)
);
INSERT INTO Super_Admin (id_usuario) VALUES (LAST_INSERT_ID());
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '6°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '7°');
INSERT INTO Docentes (id_usuario, especialidad) VALUES (LAST_INSERT_ID(), 'Primaria');
INSERT INTO Docentes (id_usuario, especialidad) VALUES (LAST_INSERT_ID(), 'Secundaria');
INSERT INTO Administrativos (id_usuario, area) VALUES (LAST_INSERT_ID(), 'Administración');
INSERT INTO Empleados (id_usuario, cargo) VALUES (LAST_INSERT_ID(), 'Mantenimiento');
INSERT INTO Empleados (id_usuario, cargo) VALUES (LAST_INSERT_ID(), 'Limpieza');
INSERT INTO Padres_Familia (id_usuario, tipo_relacion) VALUES (LAST_INSERT_ID(), 'Acudiente');
INSERT INTO Padres_Familia (id_usuario, tipo_relacion) VALUES (LAST_INSERT_ID(), 'Acudiente');
INSERT INTO Coordinadores (id_usuario, area_coordinacion) VALUES (LAST_INSERT_ID(), 'Coordinación Académica');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '10°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '10°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '4°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '11°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '8°');
INSERT INTO Estudiantes (id_usuario, grado) VALUES (LAST_INSERT_ID(), '8°');
-- Tabla: Noticias
CREATE TABLE Noticias (
    id_noticia INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200),
    contenido TEXT,
    fecha_publicacion DATE,
    autor_id INT,
    imagen_destacada VARCHAR(255),
    FOREIGN KEY (autor_id) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Noticias (titulo, contenido, imagen_destacada, fecha_publicacion, autor_id) VALUES
('Inicio del Año Escolar 2025', 'Con gran entusiasmo damos la bienvenida a todos nuestros estudiantes al nuevo año escolar 2025. Este año promete ser lleno de aprendizajes y crecimiento.', 'images/noticia1.jpg', '2025-01-15', 1),
('Celebración del Día del Maestro', 'El pasado 15 de mayo celebramos con alegría el Día del Maestro, reconociendo la labor incansable de nuestros docentes.', 'images/noticia2.jpg', '2025-05-16', 1),
('Resultados Olimpiadas Matemáticas', 'Felicitamos a nuestros estudiantes que obtuvieron excelentes resultados en las Olimpiadas Matemáticas Regionales.', 'images/noticia3.jpg', '2025-03-20', 1),
('Proyecto de Reciclaje Escolar', 'Iniciamos nuestro proyecto de reciclaje escolar para crear conciencia ambiental en toda la comunidad educativa.', 'images/noticia4.jpg', '2025-02-10', 1),
('Feria de Ciencias 2025', 'La Feria de Ciencias de este año fue un éxito total, con proyectos innovadores de nuestros estudiantes.', 'images/noticia5.jpg', '2025-04-25', 1);

-- Tabla: Eventos
CREATE TABLE Eventos (
    id_evento INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200),
    descripcion TEXT,
    fecha_evento DATE,
    lugar VARCHAR(100),
    creado_por INT,
    FOREIGN KEY (creado_por) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Eventos (titulo, descripcion, fecha_evento, lugar, creado_por) VALUES
('Reunión de Padres de Familia', 'Reunión general para informar sobre el progreso académico de los estudiantes', '2025-02-15', 'Auditorio Principal', 1),
('Día del Estudiante', 'Celebración especial para todos nuestros estudiantes con actividades recreativas', '2025-03-15', 'Patio Central', 1),
('Olimpiadas Deportivas', 'Competencias deportivas interclases para promover la actividad física', '2025-04-20', 'Canchas Deportivas', 1),
('Feria del Libro', 'Exposición y venta de libros para fomentar la lectura', '2025-05-10', 'Biblioteca', 1),
('Graduación Promoción 2025', 'Ceremonia de graduación de los estudiantes de grado 11', '2025-11-30', 'Auditorio Principal', 1),
('Semana de la Ciencia', 'Actividades científicas y experimentos para todos los grados', '2025-06-15', 'Laboratorios', 1),
('Festival de Música', 'Presentación de talentos musicales de nuestros estudiantes', '2025-07-20', 'Auditorio Principal', 1),
('Jornada de Salud', 'Chequeos médicos y charlas sobre salud para toda la comunidad', '2025-08-25', 'Enfermería', 1);

-- Tabla: Documentos
CREATE TABLE Documentos (
    id_documento INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200),
    descripcion TEXT,
    categoria VARCHAR(50),
    archivo_url VARCHAR(255),
    publicado_por INT,
    fecha_subida DATE,
    FOREIGN KEY (publicado_por) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Documentos (titulo, descripcion, categoria, archivo_url, publicado_por, fecha_subida) VALUES
('Manual de Convivencia 2025', 'Documento oficial de convivencia escolar actualizado', 'Institucional', 'uploads/documents/manual_convivencia_2025.pdf', 1, '2025-01-15'),
('PEI 2025', 'Proyecto Educativo Institucional actualizado', 'Institucional', 'uploads/documents/pei_2025.pdf', 1, '2025-01-20'),
('Calendario Académico', 'Calendario de actividades del año escolar 2025', 'Académico', 'uploads/documents/calendario_2025.pdf', 1, '2025-01-25'),
('Reglamento Estudiantil', 'Normas y reglamentos para estudiantes', 'Académico', 'uploads/documents/reglamento_estudiantil.pdf', 1, '2025-02-01'),
('Guía de Padres', 'Información importante para padres de familia', 'Padres', 'uploads/documents/guia_padres.pdf', 1, '2025-02-05'),
('Protocolo de Seguridad', 'Medidas de seguridad y protocolos de emergencia', 'Seguridad', 'uploads/documents/protocolo_seguridad.pdf', 1, '2025-02-10'),
('Plan de Mejoramiento', 'Plan de mejoramiento institucional 2025', 'Institucional', 'uploads/documents/plan_mejoramiento.pdf', 1, '2025-02-15'),
('Informe de Gestión 2024', 'Informe anual de gestión institucional', 'Institucional', 'uploads/documents/informe_gestion_2024.pdf', 1, '2025-02-20'),
('Estadísticas Académicas', 'Reporte de rendimiento académico 2024', 'Académico', 'uploads/documents/estadisticas_2024.xlsx', 1, '2025-02-25'),
('Presupuesto 2025', 'Presupuesto institucional para el año 2025', 'Administrativo', 'uploads/documents/presupuesto_2025.pdf', 1, '2025-03-01');

-- Tabla: Galeria
CREATE TABLE Galeria (
    id_imagen INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200),
    descripcion TEXT,
    imagen_url VARCHAR(255),
    fecha_subida DATE,
    publicada_por INT,
    FOREIGN KEY (publicada_por) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Galeria (titulo, descripcion, imagen_url, fecha_subida, publicada_por) VALUES
('Acto de Apertura 2025', 'Ceremonia de apertura del año escolar 2025', 'uploads/images/galeria1.jpg', '2025-01-15', 1),
('Día del Maestro', 'Celebración del Día del Maestro con actividades especiales', 'uploads/images/galeria2.jpg', '2025-01-20', 1),
('Olimpiadas Deportivas', 'Competencias deportivas interclases', 'uploads/images/galeria3.jpg', '2025-01-25', 1),
('Feria de Ciencias', 'Proyectos científicos de nuestros estudiantes', 'uploads/images/galeria4.jpg', '2025-02-01', 1),
('Festival de Música', 'Presentación de talentos musicales', 'uploads/images/galeria5.jpg', '2025-02-05', 1),
('Laboratorio de Ciencias', 'Estudiantes realizando experimentos', 'uploads/images/galeria6.jpg', '2025-02-10', 1),
('Biblioteca Escolar', 'Espacio de lectura y aprendizaje', 'uploads/images/galeria7.jpg', '2025-02-15', 1),
('Patio de Recreo', 'Área de recreación y esparcimiento', 'uploads/images/galeria8.jpg', '2025-02-20', 1),
('Aula de Tecnología', 'Sala de informática y tecnología', 'uploads/images/galeria9.jpg', '2025-02-25', 1),
('Graduación 2024', 'Ceremonia de graduación de la promoción 2024', 'uploads/images/galeria10.jpg', '2025-03-01', 1);

-- Tabla: Programas
CREATE TABLE Programas (
    id_programa INT AUTO_INCREMENT PRIMARY KEY,
    nombre_programa VARCHAR(100),
    descripcion TEXT,
    duracion VARCHAR(50)
);

INSERT INTO Programas (nombre_programa, descripcion, duracion) VALUES
('Programa de Lectura Comprensiva', 'Desarrollo de habilidades de lectura y comprensión lectora', '1 año'),
('Matemáticas Avanzadas', 'Programa especializado en matemáticas para estudiantes destacados', '2 años'),
('Inglés Conversacional', 'Programa de inglés práctico para mejorar la comunicación', '1 año'),
('Arte y Creatividad', 'Desarrollo de habilidades artísticas y creativas', '1 año'),
('Tecnología e Informática', 'Programa de alfabetización digital y programación básica', '2 años');

-- Tabla: Inscripciones
CREATE TABLE Inscripciones (
    id_inscripcion INT AUTO_INCREMENT PRIMARY KEY,
    nombre_estudiante VARCHAR(100),
    correo VARCHAR(100),
    programa_id INT,
    fecha_solicitud DATE,
    estado VARCHAR(50),
    FOREIGN KEY (programa_id) REFERENCES Programas(id_programa)
);

INSERT INTO Inscripciones (nombre_estudiante, correo, programa_id, fecha_solicitud, estado) VALUES
('María González', 'maria.gonzalez@iebs.edu.co', 1, '2025-01-20', 'Aprobada'),
('Carlos Rodríguez', 'carlos.rodriguez@iebs.edu.co', 2, '2025-01-22', 'Pendiente'),
('Ana López', 'ana.lopez@iebs.edu.co', 3, '2025-01-25', 'Aprobada'),
('Juan Martínez', 'juan.martinez@iebs.edu.co', 4, '2025-01-28', 'Rechazada'),
('Laura Pérez', 'laura.perez@iebs.edu.co', 5, '2025-02-01', 'Aprobada'),
('Roberto García', 'roberto.garcia@email.com', 1, '2025-02-05', 'Pendiente'),
('Carmen Herrera', 'carmen.herrera@email.com', 2, '2025-02-10', 'Aprobada'),
('Miguel Torres', 'miguel.torres@iebs.edu.co', 3, '2025-02-15', 'Aprobada'),
('Patricia Vargas', 'patricia.vargas@iebs.edu.co', 4, '2025-02-20', 'Pendiente'),
('Fernando Silva', 'fernando.silva@iebs.edu.co', 5, '2025-02-25', 'Aprobada');

-- Tabla: Testimonios
CREATE TABLE Testimonios (
    id_testimonio INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    contenido TEXT,
    fecha DATE,
    rol VARCHAR(50),
    aprobado BOOLEAN
);

INSERT INTO Testimonios (nombre, contenido, fecha, rol, aprobado) VALUES
('María González', 'La institución me ha ayudado a crecer académicamente y como persona. Los profesores son excelentes.', '2025-01-15', 'Estudiante', 1),
('Carlos Rodríguez', 'Me siento muy feliz estudiando aquí. He aprendido mucho y tengo grandes amigos.', '2025-01-20', 'Estudiante', 1),
('Ana López', 'Es un honor trabajar en esta institución. Los estudiantes son maravillosos y la comunidad es muy acogedora.', '2025-01-25', 'Docente', 1),
('Juan Martínez', 'La calidad educativa y el compromiso de todos hacen de esta una excelente institución.', '2025-02-01', 'Docente', 1),
('Laura Pérez', 'Nuestro compromiso es formar ciudadanos íntegros y preparados para el futuro.', '2025-02-05', 'Coordinadora', 1),
('Roberto García', 'Como padre, estoy muy satisfecho con la educación que recibe mi hijo en esta institución.', '2025-02-10', 'Padre de Familia', 1),
('Carmen Herrera', 'La comunicación con los docentes es excelente y mi hija está muy contenta estudiando aquí.', '2025-02-15', 'Madre de Familia', 1),
('Miguel Torres', 'Me siento parte importante de esta gran familia educativa.', '2025-02-20', 'Personal de Servicios', 1),
('Patricia Vargas', 'Es gratificante contribuir al mantenimiento de un ambiente limpio y agradable.', '2025-02-25', 'Personal de Servicios', 1),
('Fernando Silva', 'Trabajar aquí me ha permitido crecer profesionalmente y contribuir al desarrollo educativo.', '2025-03-01', 'Administrativo', 1);

-- Tabla: Contacto
CREATE TABLE Contacto (
    id_contacto INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    correo VARCHAR(100),
    mensaje TEXT,
    fecha_envio TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO Contacto (nombre, correo, mensaje, fecha_envio) VALUES
('Pedro Ramírez', 'pedro.ramirez@email.com', 'Consulta sobre matrículas: Me gustaría saber cuándo inician las matrículas para el próximo año escolar.', '2025-01-10 10:30:00'),
('Sofía Mendoza', 'sofia.mendoza@email.com', 'Información sobre programas: Quisiera conocer más sobre los programas extracurriculares disponibles.', '2025-01-12 14:20:00'),
('Luis Castro', 'luis.castro@email.com', 'Sugerencia de mejora: Sugiero implementar más actividades deportivas para los estudiantes.', '2025-01-15 09:15:00'),
('Elena Ruiz', 'elena.ruiz@email.com', 'Felicitaciones: Quiero felicitar a toda la institución por el excelente trabajo que realizan.', '2025-01-18 16:45:00'),
('Diego Morales', 'diego.morales@email.com', 'Consulta académica: Necesito información sobre el programa de matemáticas avanzadas.', '2025-01-20 11:30:00'),
('Valentina Jiménez', 'valentina.jimenez@email.com', 'Solicitud de visita: Me gustaría programar una visita para conocer las instalaciones.', '2025-01-22 13:20:00'),
('Andrés Herrera', 'andres.herrera@email.com', 'Información de contacto: Necesito los números de contacto de la coordinación académica.', '2025-01-25 10:10:00'),
('Camila Ortiz', 'camila.ortiz@email.com', 'Consulta sobre horarios: ¿Cuáles son los horarios de atención de la secretaría?', '2025-01-28 15:30:00'),
('Ricardo Vargas', 'ricardo.vargas@email.com', 'Sugerencia de seguridad: Sugiero mejorar la iluminación en el parqueadero.', '2025-02-01 08:45:00'),
('Natalia Silva', 'natalia.silva@email.com', 'Agradecimiento: Gracias por la excelente atención recibida en la secretaría.', '2025-02-05 12:20:00');

-- Tabla: Archivos
CREATE TABLE Archivos (
    id_archivo INT AUTO_INCREMENT PRIMARY KEY,
    nombre_archivo VARCHAR(100),
    archivo_url VARCHAR(255),
    tipo_contenido VARCHAR(50),
    subido_por INT,
    FOREIGN KEY (subido_por) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Archivos (nombre_archivo, archivo_url, tipo_contenido, subido_por) VALUES
('presentacion_institucional.pptx', 'uploads/documents/presentacion_institucional.pptx', 'presentacion', 1),
('manual_procedimientos.pdf', 'uploads/documents/manual_procedimientos.pdf', 'documento', 1),
('estadisticas_2024.xlsx', 'uploads/documents/estadisticas_2024.xlsx', 'hoja_calculo', 1),
('video_institucional.mp4', 'uploads/videos/video_institucional.mp4', 'video', 1),
('fotos_graduacion.zip', 'uploads/images/fotos_graduacion.zip', 'archivo', 1);

-- Tabla: Comentarios
CREATE TABLE Comentarios (
    id_comentario INT AUTO_INCREMENT PRIMARY KEY,
    contenido TEXT,
    autor_id INT,
    tipo_objeto VARCHAR(50),
    id_objeto INT,
    fecha DATE,
    FOREIGN KEY (autor_id) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Comentarios (contenido, autor_id, tipo_objeto, id_objeto, fecha) VALUES
('Excelente noticia sobre el inicio del año escolar', 1, 'noticia', 1, '2025-01-15'),
('Me siento muy emocionado por el nuevo año', 2, 'noticia', 1, '2025-01-15'),
('Felicitaciones a todos los docentes', 3, 'noticia', 2, '2025-01-15'),
('Gracias por el reconocimiento', 4, 'noticia', 2, '2025-01-15'),
('Excelentes resultados en matemáticas', 5, 'noticia', 3, '2025-01-15'),
('Muy orgullosa de los logros de nuestros estudiantes', 6, 'noticia', 3, '2025-01-15'),
('Proyecto muy importante para el medio ambiente', 7, 'noticia', 4, '2025-01-15'),
('Todos debemos participar en el reciclaje', 8, 'noticia', 4, '2025-01-15'),
('La feria de ciencias fue increíble', 9, 'noticia', 5, '2025-01-15'),
('Proyectos muy innovadores', 10, 'noticia', 5, '2025-01-15');

-- Tabla: Encuestas
CREATE TABLE Encuestas (
    id_encuesta INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200),
    descripcion TEXT,
    fecha_creacion DATE,
    activa BOOLEAN
);

INSERT INTO Encuestas (titulo, descripcion, fecha_creacion, activa) VALUES
('Satisfacción Estudiantil', 'Encuesta para medir la satisfacción de los estudiantes con la institución', '2025-01-15', 1),
('Evaluación Docente', 'Evaluación del desempeño docente por parte de los estudiantes', '2025-02-01', 1),
('Sugerencias de Mejora', 'Recopilación de sugerencias para mejorar la institución', '2025-03-01', 1),
('Actividades Extracurriculares', 'Preferencias de actividades extracurriculares', '2025-04-01', 1),
('Infraestructura', 'Evaluación de las instalaciones de la institución', '2025-05-01', 1);

-- Tabla: Respuestas
CREATE TABLE Respuestas (
    id_respuesta INT AUTO_INCREMENT PRIMARY KEY,
    encuesta_id INT,
    usuario_id INT,
    respuesta TEXT,
    fecha_respuesta DATE,
    FOREIGN KEY (encuesta_id) REFERENCES Encuestas(id_encuesta),
    FOREIGN KEY (usuario_id) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Respuestas (encuesta_id, usuario_id, respuesta, fecha_respuesta) VALUES
(1, 2, '¿Cómo calificarías tu experiencia en la institución? - Excelente', '2025-01-16'),
(1, 3, '¿Cómo calificarías tu experiencia en la institución? - Muy buena', '2025-01-16'),
(1, 4, '¿Cómo calificarías tu experiencia en la institución? - Excelente', '2025-01-16'),
(1, 5, '¿Cómo calificarías tu experiencia en la institución? - Buena', '2025-01-16'),
(2, 2, '¿Cómo calificarías el desempeño de tus docentes? - Excelente', '2025-01-17'),
(2, 3, '¿Cómo calificarías el desempeño de tus docentes? - Muy bueno', '2025-01-17'),
(2, 4, '¿Cómo calificarías el desempeño de tus docentes? - Excelente', '2025-01-17'),
(2, 5, '¿Cómo calificarías el desempeño de tus docentes? - Bueno', '2025-01-17'),
(3, 6, '¿Qué sugerencias tienes para mejorar la institución? - Más actividades deportivas', '2025-01-18'),
(3, 7, '¿Qué sugerencias tienes para mejorar la institución? - Mejorar la biblioteca', '2025-01-18');

-- Tabla: Recursos
CREATE TABLE Recursos (
    id_recurso INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200),
    descripcion TEXT,
    tipo VARCHAR(50),
    url VARCHAR(255),
    curso_asociado VARCHAR(100),
    fecha_publicacion DATE
);

INSERT INTO Recursos (titulo, descripcion, tipo, url, curso_asociado, fecha_publicacion) VALUES
('Guía de Matemáticas Básicas', 'Material de apoyo para estudiantes de primaria', 'Documento', 'uploads/documents/guia_matematicas.pdf', 'Matemáticas', '2025-01-15'),
('Videos de Ciencias Naturales', 'Colección de videos educativos sobre ciencias', 'Video', 'https://youtube.com/playlist/ciencias', 'Ciencias Naturales', '2025-01-20'),
('Ejercicios de Inglés', 'Actividades interactivas para practicar inglés', 'Interactivo', 'uploads/documents/ejercicios_ingles.pdf', 'Inglés', '2025-01-25'),
('Manual de Convivencia', 'Documento oficial de convivencia escolar', 'Documento', 'uploads/documents/manual_convivencia.pdf', 'Convivencia', '2025-02-01'),
('Biblioteca Digital', 'Acceso a libros digitales y recursos literarios', 'Enlace', 'https://biblioteca.iebs.edu.co', 'Lenguaje', '2025-02-05'),
('Tutorial de Programación', 'Guía básica de programación para estudiantes', 'Video', 'https://youtube.com/tutorial_programacion', 'Tecnología', '2025-02-10'),
('Actividades de Arte', 'Manual de actividades artísticas para docentes', 'Documento', 'uploads/documents/actividades_arte.pdf', 'Arte', '2025-02-15'),
('Recursos de Historia', 'Material didáctico para clases de historia', 'Documento', 'uploads/documents/recursos_historia.pdf', 'Ciencias Sociales', '2025-02-20'),
('Laboratorio Virtual', 'Simulaciones de laboratorio para ciencias', 'Interactivo', 'https://labvirtual.iebs.edu.co', 'Ciencias Naturales', '2025-02-25'),
('Guía de Técnicas de Estudio', 'Estrategias para mejorar el aprendizaje', 'Documento', 'uploads/documents/tecnicas_estudio.pdf', 'Metodología', '2025-03-01');

-- Tabla: Calendario
CREATE TABLE Calendario (
    id_calendario INT AUTO_INCREMENT PRIMARY KEY,
    fecha DATE,
    descripcion TEXT,
    tipo_evento VARCHAR(50),
    creado_por INT,
    FOREIGN KEY (creado_por) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Calendario (fecha, descripcion, tipo_evento, creado_por) VALUES
('2025-01-20', 'Inicio del año escolar 2025', 'Académico', 1),
('2025-02-15', 'Reunión general de padres de familia', 'Administrativo', 1),
('2025-03-15', 'Celebración del Día del Estudiante', 'Recreativo', 1),
('2025-04-20', 'Competencias deportivas', 'Deportivo', 1),
('2025-05-10', 'Exposición y venta de libros', 'Cultural', 1),
('2025-06-15', 'Actividades científicas', 'Académico', 1),
('2025-07-20', 'Presentación musical', 'Cultural', 1),
('2025-08-25', 'Chequeos médicos', 'Salud', 1),
('2025-05-15', 'Celebración del Día del Maestro', 'Recreativo', 1),
('2025-11-30', 'Ceremonia de graduación', 'Académico', 1);

-- Tabla: Historial
CREATE TABLE Historial (
    id_actividad INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT,
    descripcion TEXT,
    fecha_hora DATETIME,
    FOREIGN KEY (usuario_id) REFERENCES Usuarios(id_usuario)
);

INSERT INTO Historial (usuario_id, descripcion, fecha_hora) VALUES
(1, 'Creación de usuario estudiante', '2025-01-15 10:30:00'),
(1, 'Creación de usuario estudiante', '2025-01-15 10:35:00'),
(1, 'Creación de usuario docente', '2025-01-15 10:40:00'),
(1, 'Creación de usuario docente', '2025-01-15 10:45:00'),
(1, 'Creación de usuario coordinador', '2025-01-15 10:50:00'),
(1, 'Creación de usuario padre de familia', '2025-01-15 10:55:00'),
(1, 'Creación de usuario padre de familia', '2025-01-15 11:00:00'),
(1, 'Creación de usuario personal de servicios', '2025-01-15 11:05:00'),
(1, 'Creación de usuario personal de servicios', '2025-01-15 11:10:00'),
(1, 'Creación de usuario administrativo', '2025-01-15 11:15:00'),
(1, 'Publicación de noticia', '2025-01-15 11:20:00'),
(1, 'Publicación de noticia', '2025-01-15 11:25:00'),
(1, 'Publicación de noticia', '2025-01-15 11:30:00'),
(1, 'Publicación de noticia', '2025-01-15 11:35:00'),
(1, 'Publicación de noticia', '2025-01-15 11:40:00');

-- Crear tabla PQRSF
CREATE TABLE IF NOT EXISTS PQRSF (
    id_pqrsf INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('Petición', 'Queja', 'Reclamo', 'Sugerencia', 'Felicitación') NOT NULL,
    nombre_completo VARCHAR(100) NOT NULL,
    correo VARCHAR(100) NOT NULL,
    asunto VARCHAR(150) NOT NULL,
    mensaje TEXT NOT NULL,
    fecha_envio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado ENUM('Pendiente', 'En proceso', 'Resuelta') DEFAULT 'Pendiente',
    respuesta TEXT NULL,
    fecha_respuesta TIMESTAMP NULL,
    respondido_por INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices para optimizar consultas
    INDEX idx_estado (estado),
    INDEX idx_tipo (tipo),
    INDEX idx_fecha_envio (fecha_envio),
    INDEX idx_correo (correo),
    
    -- Clave foránea opcional para el usuario que responde
    FOREIGN KEY (respondido_por) REFERENCES Usuarios(id_usuario) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar datos de ejemplo para pruebas
INSERT INTO PQRSF (tipo, nombre_completo, correo, asunto, mensaje, estado) VALUES
('Petición', 'María González Pérez', 'maria.gonzalez@email.com', 'Solicitud de información sobre matrículas', 'Buenos días, me gustaría obtener información detallada sobre el proceso de matrícula para el año 2025, incluyendo fechas, requisitos y costos. Agradezco su pronta respuesta.', 'Pendiente'),
('Queja', 'Carlos Rodríguez Silva', 'carlos.rodriguez@email.com', 'Problema con el servicio de transporte escolar', 'El servicio de transporte escolar ha presentado retrasos constantes durante la última semana, lo que afecta la puntualidad de mi hijo. Solicitamos una pronta solución a esta situación.', 'En proceso'),
('Reclamo', 'Ana López Martínez', 'ana.lopez@email.com', 'Reclamo por calificación injusta', 'Considero que la calificación asignada a mi hijo en el área de matemáticas no refleja su desempeño real. Solicito una revisión detallada de las evaluaciones y una explicación clara de los criterios utilizados.', 'Pendiente'),
('Sugerencia', 'Roberto Díaz Herrera', 'roberto.diaz@email.com', 'Propuesta para mejorar la comunicación', 'Sugiero implementar una plataforma digital para mejorar la comunicación entre padres, estudiantes y docentes. Esto facilitaría el seguimiento académico y la resolución de dudas.', 'Resuelta'),
('Felicitación', 'Laura Vargas Castro', 'laura.vargas@email.com', 'Reconocimiento al equipo docente', 'Quiero felicitar al equipo docente por el excelente trabajo realizado durante el año escolar. El compromiso y dedicación de los profesores ha sido fundamental para el desarrollo académico de mi hija.', 'Resuelta'),
('Petición', 'Pedro Martínez Ruiz', 'pedro.martinez@email.com', 'Solicitud de beca estudiantil', 'Solicito información sobre los programas de becas disponibles para estudiantes de bajos recursos. Mi familia atraviesa una situación económica difícil y necesitamos apoyo para continuar con la educación de mi hijo.', 'Pendiente'),
('Queja', 'Carmen Silva Torres', 'carmen.silva@email.com', 'Problema con la cafetería escolar', 'La calidad de los alimentos en la cafetería ha disminuido considerablemente. Los estudiantes se quejan constantemente y algunos han presentado malestares estomacales. Urge una revisión del servicio.', 'En proceso'),
('Sugerencia', 'Fernando Moreno López', 'fernando.moreno@email.com', 'Propuesta de actividades extracurriculares', 'Sugiero implementar más actividades deportivas y culturales para los estudiantes. Esto contribuiría a su desarrollo integral y mejoraría el ambiente escolar.', 'Pendiente'),
('Felicitación', 'Patricia Rojas Díaz', 'patricia.rojas@email.com', 'Agradecimiento por el apoyo durante la pandemia', 'Quiero agradecer a toda la institución por el esfuerzo realizado durante la pandemia. La adaptación a la educación virtual fue ejemplar y el apoyo constante a las familias fue invaluable.', 'Resuelta'),
('Reclamo', 'Jorge Silva Castro', 'jorge.silva@email.com', 'Reclamo por daños en material escolar', 'El material escolar de mi hijo fue dañado durante una actividad en el aula. Considero que la institución debe asumir la responsabilidad y reponer los elementos afectados.', 'Pendiente');

-- Mostrar información de la tabla creada
DESCRIBE PQRSF;

-- Mostrar conteo de registros
SELECT COUNT(*) as total_pqrsf FROM PQRSF;

-- Mostrar distribución por tipo
SELECT tipo, COUNT(*) as cantidad FROM PQRSF GROUP BY tipo;

-- Mostrar distribución por estado
SELECT estado, COUNT(*) as cantidad FROM PQRSF GROUP BY estado;

-- Trigger: Crear registro en tabla específica según rol al registrar en Usuarios
DELIMITER $$
DROP TRIGGER IF EXISTS trg_usuarios_after_insert $$
    DECLARE v_id_rol_docente INT;
    DECLARE v_id_rol_admin INT;
    DECLARE v_id_rol_empleado INT;
    DECLARE v_id_rol_super_admin INT;
    DECLARE v_id_rol_coordinador INT;
    DECLARE v_id_rol_padre INT;

    SELECT id_rol INTO v_id_rol_estudiante FROM Roles WHERE nombre_rol = 'Estudiante' LIMIT 1;
    SELECT id_rol INTO v_id_rol_docente FROM Roles WHERE nombre_rol = 'Docente' LIMIT 1;
    SELECT id_rol INTO v_id_rol_admin FROM Roles WHERE nombre_rol = 'Administrativo' LIMIT 1;
    SELECT id_rol INTO v_id_rol_empleado FROM Roles WHERE nombre_rol = 'Empleado' LIMIT 1;
    SELECT id_rol INTO v_id_rol_super_admin FROM Roles WHERE nombre_rol = 'Super Admin' LIMIT 1;
    SELECT id_rol INTO v_id_rol_coordinador FROM Roles WHERE nombre_rol = 'Coordinador' LIMIT 1;
    SELECT id_rol INTO v_id_rol_padre FROM Roles WHERE nombre_rol = 'Padre de Familia' LIMIT 1;

    IF NEW.tipo_usuario = v_id_rol_estudiante THEN
        IF NOT EXISTS (SELECT 1 FROM Estudiantes WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Estudiantes (id_usuario, grado)
            VALUES (NEW.id_usuario, NULL);
        END IF;
    ELSEIF NEW.tipo_usuario = v_id_rol_docente THEN
        IF NOT EXISTS (SELECT 1 FROM Docentes WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Docentes (id_usuario, especialidad, nivel, hoja_vida, pdfs)
            VALUES (NEW.id_usuario, NULL, NULL, NULL, NULL);
        END IF;
    ELSEIF NEW.tipo_usuario = v_id_rol_admin THEN
        IF NOT EXISTS (SELECT 1 FROM Administrativos WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Administrativos (id_usuario, area)
            VALUES (NEW.id_usuario, NULL);
        END IF;
    ELSEIF NEW.tipo_usuario = v_id_rol_empleado THEN
        IF NOT EXISTS (SELECT 1 FROM Empleados WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Empleados (id_usuario, cargo)
            VALUES (NEW.id_usuario, NULL);
        END IF;
    ELSEIF NEW.tipo_usuario = v_id_rol_super_admin THEN
        IF NOT EXISTS (SELECT 1 FROM Super_Admin WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Super_Admin (id_usuario)
            VALUES (NEW.id_usuario);
        END IF;
    ELSEIF NEW.tipo_usuario = v_id_rol_coordinador THEN
        IF NOT EXISTS (SELECT 1 FROM Coordinadores WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Coordinadores (id_usuario, area_coordinacion)
            VALUES (NEW.id_usuario, NULL);
        END IF;
    ELSEIF NEW.tipo_usuario = v_id_rol_padre THEN
        IF NOT EXISTS (SELECT 1 FROM Padres_Familia WHERE id_usuario = NEW.id_usuario) THEN
            INSERT INTO Padres_Familia (id_usuario, tipo_relacion)
            VALUES (NEW.id_usuario, NULL);
        END IF;
    END IF;
END $$
DELIMITER ;