
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `acuerdos` (
  `AcuerdoId` int(11) NOT NULL,
  `NombreAcuerdo` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `archivos` (
  `ArchivoId` int(11) NOT NULL,
  `NombreArchivo` varchar(255) NOT NULL,
  `FechaSubida` datetime DEFAULT current_timestamp(),
  `UsuarioId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `cursos` (
  `Codigo` bigint(20) NOT NULL,
  `Nombre` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `estados_academicos` (
  `EstadoAcademicoId` int(11) NOT NULL,
  `EstadoAcademico` varchar(1) NOT NULL,
  `NombreEstado` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `estudiantes` (
  `CodigoEstudiante` bigint(20) NOT NULL,
  `Nombre` varchar(150) NOT NULL,
  `EstadoAcademicoId` int(11) DEFAULT NULL,
  `ProgramaId` int(11) DEFAULT NULL,
  `AcuerdoId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `estudiantescursos` (
  `EstudianteCursoId` int(11) NOT NULL,
  `CodigoEstudianteId` bigint(20) NOT NULL,
  `CodigoCursoId` bigint(20) NOT NULL,
  `ArchivoId` int(11) NOT NULL,
  `Periodo` varchar(20) NOT NULL,
  `Nota` decimal(5,2) NOT NULL,
  `Observaciones` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `programas` (
  `ProgramaId` int(11) NOT NULL,
  `Nombre` varchar(100) NOT NULL,
  `Codigo` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tipos_usuario` (
  `TipoUsuarioId` int(11) NOT NULL,
  `NombreTipo` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `tipos_usuario` (`TipoUsuarioId`, `NombreTipo`) VALUES
(1, 'Administrador'),
(3, 'Docente'),
(2, 'Estudiante');

CREATE TABLE `usuarios` (
  `UsuarioId` int(11) NOT NULL,
  `Usuario` varchar(100) NOT NULL,
  `Nombre` varchar(100) NOT NULL,
  `Apellidos` varchar(100) NOT NULL,
  `Correo` varchar(100) NOT NULL,
  `Clave` varchar(255) NOT NULL,
  `token_verificacion` varchar(255) DEFAULT NULL,
  `verificado` tinyint(1) DEFAULT 0,
  `fecha_registro` date NOT NULL DEFAULT current_timestamp(),
  `TipoUsuarioId` int(11) NOT NULL,
  `estado` enum('A','I') NOT NULL DEFAULT 'A'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `auditoria_eventos` (
  `id_auditoria` bigint(20) NOT NULL,
  `usuario_id` varchar(100) DEFAULT NULL,
  `usuario_nombre` varchar(255) DEFAULT NULL,
  `fecha_evento` datetime NOT NULL DEFAULT current_timestamp(),
  `tipo_evento` enum('INSERT','UPDATE','DELETE','LOGIN','LOGOUT','FAILED_LOGIN') NOT NULL,
  `nombre_tabla` varchar(255) NOT NULL,
  `llave_primaria` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `valores_anteriores` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`valores_anteriores`)),
  `valores_nuevos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`valores_nuevos`)),
  `ip_origen` varchar(45) DEFAULT NULL,
  `aplicacion_origen` varchar(100) DEFAULT NULL,
  `comentario` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `usuarios` (`UsuarioId`, `Usuario`, `Nombre`, `Apellidos`, `Correo`, `Clave`, `token_verificacion`, `verificado`, `fecha_registro`, `TipoUsuarioId`, `estado`) VALUES
(1, 'admin', 'luis', 'pacheco', 'pachecoperezluis@gmail.com', '12345', NULL, 0, '2025-10-05', 1, 'A'),
(2, 'vista', 'mario', 'simus', 'mario@mail.com', '12345', NULL, 0, '2025-10-05', 3, 'A');


ALTER TABLE `acuerdos`
  ADD PRIMARY KEY (`AcuerdoId`),
  ADD UNIQUE KEY `NombreAcuerdo` (`NombreAcuerdo`);

ALTER TABLE `archivos`
  ADD PRIMARY KEY (`ArchivoId`),
  ADD KEY `UsuarioId` (`UsuarioId`);

ALTER TABLE `cursos`
  ADD PRIMARY KEY (`Codigo`);

ALTER TABLE `estados_academicos`
  ADD PRIMARY KEY (`EstadoAcademicoId`);

ALTER TABLE `estudiantes`
  ADD PRIMARY KEY (`CodigoEstudiante`),
  ADD KEY `EstadoAcademicoId` (`EstadoAcademicoId`),
  ADD KEY `ProgramaId` (`ProgramaId`),
  ADD KEY `AcuerdoId` (`AcuerdoId`);

ALTER TABLE `estudiantescursos`
  ADD PRIMARY KEY (`EstudianteCursoId`),
  ADD UNIQUE KEY `CodigoEstudianteId` (`CodigoEstudianteId`,`CodigoCursoId`,`Periodo`,`ArchivoId`),
  ADD KEY `CodigoCursoId` (`CodigoCursoId`),
  ADD KEY `ArchivoId` (`ArchivoId`);

ALTER TABLE `programas`
  ADD PRIMARY KEY (`ProgramaId`),
  ADD UNIQUE KEY `Codigo` (`Codigo`);

ALTER TABLE `tipos_usuario`
  ADD PRIMARY KEY (`TipoUsuarioId`),
  ADD UNIQUE KEY `NombreTipo` (`NombreTipo`);

ALTER TABLE `usuarios`
  ADD PRIMARY KEY (`UsuarioId`),
  ADD UNIQUE KEY `Correo` (`Correo`),
  ADD KEY `TipoUsuarioId` (`TipoUsuarioId`);

ALTER TABLE `acuerdos`
  MODIFY `AcuerdoId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

ALTER TABLE `archivos`
  MODIFY `ArchivoId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=210;

ALTER TABLE `estados_academicos`
  MODIFY `EstadoAcademicoId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=104;

ALTER TABLE `estudiantescursos`
  MODIFY `EstudianteCursoId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21709;

ALTER TABLE `programas`
  MODIFY `ProgramaId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;

ALTER TABLE `tipos_usuario`
  MODIFY `TipoUsuarioId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `usuarios`
  MODIFY `UsuarioId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `archivos`
  ADD CONSTRAINT `archivos_ibfk_1` FOREIGN KEY (`UsuarioId`) REFERENCES `usuarios` (`UsuarioId`);

ALTER TABLE `estudiantes`
  ADD CONSTRAINT `estudiantes_ibfk_1` FOREIGN KEY (`EstadoAcademicoId`) REFERENCES `estados_academicos` (`EstadoAcademicoId`),
  ADD CONSTRAINT `estudiantes_ibfk_2` FOREIGN KEY (`ProgramaId`) REFERENCES `programas` (`ProgramaId`),
  ADD CONSTRAINT `estudiantes_ibfk_3` FOREIGN KEY (`AcuerdoId`) REFERENCES `acuerdos` (`AcuerdoId`);

ALTER TABLE `estudiantescursos`
  ADD CONSTRAINT `estudiantescursos_ibfk_1` FOREIGN KEY (`CodigoEstudianteId`) REFERENCES `estudiantes` (`CodigoEstudiante`) ON DELETE CASCADE,
  ADD CONSTRAINT `estudiantescursos_ibfk_2` FOREIGN KEY (`CodigoCursoId`) REFERENCES `cursos` (`Codigo`) ON DELETE CASCADE,
  ADD CONSTRAINT `estudiantescursos_ibfk_3` FOREIGN KEY (`ArchivoId`) REFERENCES `archivos` (`ArchivoId`) ON DELETE CASCADE;

ALTER TABLE `usuarios`
  ADD CONSTRAINT `usuarios_ibfk_1` FOREIGN KEY (`TipoUsuarioId`) REFERENCES `tipos_usuario` (`TipoUsuarioId`);
COMMIT;

ALTER TABLE `auditoria_eventos`
  ADD PRIMARY KEY (`id_auditoria`),
  ADD KEY `idx_usuario` (`usuario_id`),
  ADD KEY `idx_tabla` (`nombre_tabla`),
  ADD KEY `idx_fecha` (`fecha_evento`);

ALTER TABLE `auditoria_eventos`
  MODIFY `id_auditoria` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=78;
COMMIT;
