-- ======================================================================
-- SISTEMA DE REGISTRO DIARIO DOCENTE - BASE DE DATOS COMPLETA (3FN)
-- ======================================================================

CREATE DATABASE IF NOT EXISTS `parte-diario-docente` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `parte-diario-docente`;

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "-05:00";

-- --------------------------------------------------------
-- 1. SEGURIDAD Y USUARIOS (Módulo Base conectado a Shield)
-- --------------------------------------------------------

DROP TABLE IF EXISTS `personal`;
CREATE TABLE `personal` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED NOT NULL, -- Conexión directa con la tabla 'users' de Shield
  `dni` varchar(20) NOT NULL,
  `apellido_paterno` varchar(75) NOT NULL,
  `apellido_materno` varchar(75) NOT NULL,
  `primer_nombre` varchar(75) NOT NULL,
  `segundo_nombre` varchar(75) DEFAULT NULL,
  `fecha_nacimiento` date NOT NULL,
  `genero` enum('M', 'F', 'Otro') NOT NULL,
  `celular` varchar(20) DEFAULT NULL,
  `telefono_fijo` varchar(20) DEFAULT NULL,
  `correo_personal` varchar(255) DEFAULT NULL, 
  `direccion_domicilio` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_dni` (`dni`),
  UNIQUE KEY `uk_shield_user` (`user_id`),
  CONSTRAINT `fk_personal_shield_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 0. INSERTS SEGUROS EN LA TABLA DE SHIELD
-- Si el ID ya existe, MySQL simplemente lo ignorará y no romperá el script.
INSERT IGNORE INTO `users` (`id`, `username`, `status`, `active`, `created_at`, `updated_at`) VALUES
(1, 'admin', NULL, 1, NOW(), NOW()),
(2, 'jonatan', NULL, 1, NOW(), NOW()),
(3, 'mario', NULL, 1, NOW(), NOW()),
(4, 'clemente', NULL, 1, NOW(), NOW()),
(5, 'cristian', NULL, 1, NOW(), NOW());

-- MIGRACIÓN DE INSERTS OPTIMIZADA: Datos separados correctamente y DNIs ficticios corregidos
INSERT INTO `personal` (`id`, `user_id`, `dni`, `apellido_paterno`, `apellido_materno`, `primer_nombre`, `segundo_nombre`, `fecha_nacimiento`, `genero`, `celular`, `telefono_fijo`, `correo_personal`, `direccion_domicilio`) VALUES
(1, 1, '00000000', 'Del Sistema', 'Administrador', 'Admin', NULL, '1990-01-01', 'M', NULL, NULL, NULL, NULL), 
(2, 2, '11111111', 'Vilca', 'Quisocala', 'Jonatan', 'Valdo', '1990-01-01', 'M', NULL, NULL, NULL, NULL),
(3, 3, '22222222', 'Cutipa', 'Ortega', 'Mario', NULL, '1990-01-01', 'M', NULL, NULL, NULL, NULL),
(4, 4, '33333333', 'Vilcapaza', 'Larico', 'Clemente', NULL, '1990-01-01', 'M', NULL, NULL, NULL, NULL),
(5, 5, '44444444', 'Yaguno', 'Mamani', 'Cristian', 'Rony', '1990-01-01', 'M', NULL, NULL, NULL, NULL);

-- --------------------------------------------------------
-- 2. CATÁLOGOS ACADÉMICOS
-- --------------------------------------------------------

DROP TABLE IF EXISTS `programas`;
CREATE TABLE `programas` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` varchar(150) NOT NULL,
  `coordinador_personal_id` int UNSIGNED DEFAULT NULL, 
  `estado_activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_prog_coord_personal` (`coordinador_personal_id`),
  CONSTRAINT `fk_prog_coord_personal` FOREIGN KEY (`coordinador_personal_id`) REFERENCES `personal` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `programas` (`id`, `nombre`, `coordinador_personal_id`) VALUES 
(1, 'Arquitectura de Plataformas y Servicios de TI', 1);

DROP TABLE IF EXISTS `docentes`;
CREATE TABLE `docentes` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `personal_id` int UNSIGNED NOT NULL, 
  `programa_id` int UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_personal_docente` (`personal_id`), 
  KEY `fk_doc_prog_const` (`programa_id`),
  CONSTRAINT `fk_doc_personal_const` FOREIGN KEY (`personal_id`) REFERENCES `personal` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_doc_prog_const` FOREIGN KEY (`programa_id`) REFERENCES `programas` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `docentes` (`id`, `personal_id`, `programa_id`) VALUES
(1, 2, 1), -- Jonatan
(2, 3, 1), -- Mario
(3, 4, 1), -- Clemente
(4, 5, 1); -- Cristian

DROP TABLE IF EXISTS `periodos_academicos`;
CREATE TABLE `periodos_academicos` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `codigo` varchar(20) NOT NULL,
  `fecha_inicio` date NOT NULL,
  `fecha_fin` date NOT NULL,
  `es_actual` tinyint(1) DEFAULT '0',
  `estado_activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `periodos_academicos` (`id`, `codigo`, `fecha_inicio`, `fecha_fin`, `es_actual`) VALUES
(1, '2026-I', '2026-04-13', '2026-08-07', 1),
(2, '2026-II', '2026-08-15', '2026-12-15', 0);

DROP TABLE IF EXISTS `cursos`;
CREATE TABLE `cursos` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `codigo_curso` varchar(20) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `ciclo` enum('I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX', 'X') NOT NULL, 
  `creditos` tinyint UNSIGNED NOT NULL DEFAULT '0', 
  `horas_teoria_semanales` tinyint UNSIGNED DEFAULT '0',
  `horas_practica_semanales` tinyint UNSIGNED DEFAULT '0',
  `tipo_materia` enum('Teórico', 'Práctico', 'Laboratorio', 'Virtual') NOT NULL DEFAULT 'Teórico',
  `programa_id` int UNSIGNED NOT NULL,
  `estado_activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_codigo_curso` (`codigo_curso`),
  KEY `fk_cur_prog_const` (`programa_id`),
  CONSTRAINT `fk_cur_prog_const` FOREIGN KEY (`programa_id`) REFERENCES `programas` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `cursos` (`id`, `codigo_curso`, `nombre`, `ciclo`, `creditos`, `horas_teoria_semanales`, `horas_practica_semanales`, `tipo_materia`, `programa_id`) VALUES
(1, 'WEB01', 'Desarrollo de Prototipos Web', 'III', 4, 3, 2, 'Teórico', 1),
(2, 'APP01', 'Aplicaciones Móviles', 'IV', 4, 2, 3, 'Teórico', 1),
(3, 'MULT01', 'Desarrollo Multimedia', 'V', 4, 2, 3, 'Teórico', 1);

-- --------------------------------------------------------
-- 3. PLANIFICACIÓN Y HORARIOS
-- --------------------------------------------------------

DROP TABLE IF EXISTS `feriados`;
CREATE TABLE `feriados` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `fecha` date NOT NULL,
  `descripcion` varchar(100) DEFAULT NULL,
  `es_laborable` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_fecha` (`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `horario_academico`;
CREATE TABLE `horario_academico` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `hora_inicio_jornada` time NOT NULL DEFAULT '07:00:00',
  `hora_fin_jornada` time NOT NULL DEFAULT '22:00:00',
  `dias_laborales` set('lunes','martes','miercoles','jueves','viernes','sabado','domingo') NOT NULL DEFAULT 'lunes,martes,miercoles,jueves,viernes',
  `tolerancia_default` int DEFAULT '20',
  `usar_franjas_academicas` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `horario_academico` (`id`, `hora_inicio_jornada`, `hora_fin_jornada`, `tolerancia_default`, `usar_franjas_academicas`) 
VALUES (1, '08:00:00', '13:00:00', 20, 0);

DROP TABLE IF EXISTS `franjas_academicas`;
CREATE TABLE `franjas_academicas` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` varchar(50) NOT NULL,
  `duracion_minutos` int NOT NULL DEFAULT '45',
  `hora_inicio` time NOT NULL,
  `hora_fin` time NOT NULL,
  `tipo` enum('hora_clase','recreo','almuerzo') DEFAULT 'hora_clase',
  `estado_activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_hora_inicio` (`hora_inicio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `franjas_academicas` (`nombre`, `duracion_minutos`, `hora_inicio`, `hora_fin`, `tipo`) VALUES
('Bloque 1', 45, '08:00:00', '08:45:00', 'hora_clase'),
('Bloque 2', 45, '08:45:00', '09:30:00', 'hora_clase'),
('Recreo', 10, '09:30:00', '09:40:00', 'recreo');

DROP TABLE IF EXISTS `disponibilidad_docente`;
CREATE TABLE `disponibilidad_docente` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `docente_id` int UNSIGNED NOT NULL,
  `dia_semana` enum('lunes','martes','miercoles','jueves','viernes','sabado','domingo') NOT NULL,
  `hora_inicio` time NOT NULL,
  `hora_fin` time NOT NULL,
  `estado_activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_dispo_docente` (`docente_id`),
  CONSTRAINT `fk_dispo_doc_const` FOREIGN KEY (`docente_id`) REFERENCES `docentes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 4. OPERACIONAL (Transacciones Core)
-- --------------------------------------------------------

DROP TABLE IF EXISTS `asignaciones`;
CREATE TABLE `asignaciones` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `curso_id` int UNSIGNED NOT NULL,
  `docente_id` int UNSIGNED NOT NULL,
  `periodo_id` int UNSIGNED NOT NULL,
  `fecha_asignacion` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_curso_docente_periodo` (`curso_id`,`docente_id`,`periodo_id`),
  KEY `fk_asig_cur_const` (`curso_id`),
  KEY `fk_asig_doc_const` (`docente_id`),
  KEY `fk_asig_per_const` (`periodo_id`),
  CONSTRAINT `fk_asig_cur_const` FOREIGN KEY (`curso_id`) REFERENCES `cursos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_asig_doc_const` FOREIGN KEY (`docente_id`) REFERENCES `docentes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_asig_per_const` FOREIGN KEY (`periodo_id`) REFERENCES `periodos_academicos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `asignaciones` (`id`, `curso_id`, `docente_id`, `periodo_id`) VALUES
(1, 1, 1, 1), (2, 2, 4, 1), (3, 3, 2, 1);

DROP TABLE IF EXISTS `horarios`;
CREATE TABLE `horarios` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `asignacion_id` int UNSIGNED NOT NULL,
  `dia_semana` enum('lunes','martes','miercoles','jueves','viernes','sabado','domingo') NOT NULL,
  `hora_inicio` time NOT NULL,
  `hora_fin` time NOT NULL,
  `aula` varchar(50) DEFAULT NULL,
  `estado_activo` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_hor_asig_const` (`asignacion_id`),
  CONSTRAINT `fk_hor_asig_const` FOREIGN KEY (`asignacion_id`) REFERENCES `asignaciones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `horarios` (`id`, `asignacion_id`, `dia_semana`, `hora_inicio`, `hora_fin`) VALUES
(1, 1, 'lunes', '09:40:00', '10:25:00'),
(2, 2, 'lunes', '08:00:00', '08:45:00');

DROP TABLE IF EXISTS `registros_sesion`;
CREATE TABLE `registros_sesion` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `horario_id` int UNSIGNED NOT NULL,
  `fecha_clase` date NOT NULL,
  `hora_envio_sistema` datetime NOT NULL,
  `tema_silabo_semana` tinyint UNSIGNED NOT NULL, 
  `actividad_realizada` text NOT NULL, 
  `tareas_dejadas` text DEFAULT NULL, 
  `observacion` text,
  `estado_registro` enum('en_tiempo', 'fuera_de_plazo') NOT NULL DEFAULT 'en_tiempo',
  `estado_revision_coordinador` enum('Pendiente', 'Aprobado', 'Observado') DEFAULT 'Pendiente',
  `comentario_coordinador` text DEFAULT NULL,
  `dispositivo_origen` enum('web', 'app') DEFAULT 'web',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_horario_fecha` (`horario_id`,`fecha_clase`),
  CONSTRAINT `fk_reg_hor_const` FOREIGN KEY (`horario_id`) REFERENCES `horarios` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `registros_sesion` (`id`, `horario_id`, `fecha_clase`, `hora_envio_sistema`, `tema_silabo_semana`, `actividad_realizada`, `estado_registro`) VALUES
(1, 2, '2026-05-07', '2026-05-07 15:59:03', 5, 'Se desarrollo UX y UI', 'fuera_de_plazo');

-- --------------------------------------------------------
-- 5. AUDITORÍA Y UTILIDADES
-- --------------------------------------------------------

DROP TABLE IF EXISTS `config_sistema`;
CREATE TABLE `config_sistema` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `clave` varchar(50) NOT NULL,
  `valor` varchar(255) NOT NULL,
  `descripcion` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_clave_config` (`clave`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `config_sistema` (`clave`, `valor`) VALUES ('tolerancia_minutos', '20');

DROP TABLE IF EXISTS `auditoria`;
CREATE TABLE `auditoria` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED DEFAULT NULL, 
  `tabla_afectada` varchar(50) NOT NULL,
  `accion` enum('insercion','actualizacion','eliminacion','login','logout') NOT NULL,
  `descripcion_cambio` text,
  `fecha_evento` datetime DEFAULT CURRENT_TIMESTAMP,
  `direccion_ip` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aud_users_shield` (`user_id`),
  CONSTRAINT `fk_aud_users_shield` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `notificaciones`;
CREATE TABLE `notificaciones` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED NOT NULL,
  `titulo` varchar(100) NOT NULL,
  `mensaje` text NOT NULL,
  `fue_leida` tinyint(1) DEFAULT '0',
  `fecha_envio` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_not_users_shield` (`user_id`),
  CONSTRAINT `fk_not_users_shield` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 6. SOLICITUDES DE CONTRASENA
-- --------------------------------------------------------

DROP TABLE IF EXISTS `solicitudes_password`;
CREATE TABLE `solicitudes_password` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int UNSIGNED NOT NULL,
  `estado` enum('pendiente','atendida','rechazada') DEFAULT 'pendiente',
  `comentario` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `atendida_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sol_password_user` (`user_id`),
  CONSTRAINT `fk_sol_password_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;