proyecto-opendata/user_platform/schemas/001_platform_table_setup.schema.sql

CREATE TABLE roles (
    id_rol SERIAL PRIMARY KEY,
    nombre VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE usuarios (
    id_usuario SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    apellidos VARCHAR(150),
    email VARCHAR(150) UNIQUE NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    activo BOOLEAN DEFAULT TRUE,
    id_rol INT NOT NULL,

    CONSTRAINT fk_usuario_rol
        FOREIGN KEY(id_rol)
        REFERENCES roles(id_rol)
);

CREATE TABLE credenciales (
    id_credencial SERIAL PRIMARY KEY,
    id_usuario INT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    ultimo_login TIMESTAMP,

    CONSTRAINT fk_credencial_usuario
        FOREIGN KEY(id_usuario)
        REFERENCES usuarios(id_usuario)
        ON DELETE CASCADE
);

CREATE TABLE profesores (
    id_profesor SERIAL PRIMARY KEY,
    id_usuario INT UNIQUE NOT NULL,
    especialidad VARCHAR(100),

    CONSTRAINT fk_profesor_usuario
        FOREIGN KEY(id_usuario)
        REFERENCES usuarios(id_usuario)
        ON DELETE CASCADE
);

CREATE TABLE alumnos (
    id_alumno SERIAL PRIMARY KEY,
    id_usuario INT UNIQUE NOT NULL,
    nivel VARCHAR(50),

    CONSTRAINT fk_alumno_usuario
        FOREIGN KEY(id_usuario)
        REFERENCES usuarios(id_usuario)
        ON DELETE CASCADE
);

CREATE TABLE cursos (
    id_curso SERIAL PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    descripcion TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    id_profesor INT NOT NULL,

    CONSTRAINT fk_curso_profesor
        FOREIGN KEY(id_profesor)
        REFERENCES profesores(id_profesor)
);

CREATE TABLE matriculas (
    id_matricula SERIAL PRIMARY KEY,
    id_alumno INT NOT NULL,
    id_curso INT NOT NULL,
    fecha_matricula TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_matricula_alumno
        FOREIGN KEY(id_alumno)
        REFERENCES alumnos(id_alumno)
        ON DELETE CASCADE,

    CONSTRAINT fk_matricula_curso
        FOREIGN KEY(id_curso)
        REFERENCES cursos(id_curso)
        ON DELETE CASCADE,

    CONSTRAINT unique_matricula
        UNIQUE(id_alumno, id_curso)
);

CREATE TABLE actividades (
    id_actividad SERIAL PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT,
    fecha_publicacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_entrega TIMESTAMP,
    puntuacion_maxima NUMERIC(5,2),
    id_curso INT NOT NULL,

    CONSTRAINT fk_actividad_curso
        FOREIGN KEY(id_curso)
        REFERENCES cursos(id_curso)
        ON DELETE CASCADE
);

CREATE TABLE entregas (
    id_entrega SERIAL PRIMARY KEY,
    id_actividad INT NOT NULL,
    id_alumno INT NOT NULL,
    fecha_entrega TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    contenido TEXT,
    estado VARCHAR(50) DEFAULT 'pendiente',

    CONSTRAINT fk_entrega_actividad
        FOREIGN KEY(id_actividad)
        REFERENCES actividades(id_actividad)
        ON DELETE CASCADE,

    CONSTRAINT fk_entrega_alumno
        FOREIGN KEY(id_alumno)
        REFERENCES alumnos(id_alumno)
        ON DELETE CASCADE
);

CREATE TABLE calificaciones (
    id_calificacion SERIAL PRIMARY KEY,
    id_entrega INT UNIQUE NOT NULL,
    nota NUMERIC(5,2),
    observaciones TEXT,
    fecha_calificacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_calificacion_entrega
        FOREIGN KEY(id_entrega)
        REFERENCES entregas(id_entrega)
        ON DELETE CASCADE
);

CREATE TABLE progreso (
    id_progreso SERIAL PRIMARY KEY,
    id_alumno INT NOT NULL,
    id_curso INT NOT NULL,
    porcentaje NUMERIC(5,2) DEFAULT 0,

    CONSTRAINT fk_progreso_alumno
        FOREIGN KEY(id_alumno)
        REFERENCES alumnos(id_alumno)
        ON DELETE CASCADE,

    CONSTRAINT fk_progreso_curso
        FOREIGN KEY(id_curso)
        REFERENCES cursos(id_curso)
        ON DELETE CASCADE,

    CONSTRAINT unique_progreso
        UNIQUE(id_alumno, id_curso)
);

CREATE TABLE horarios (
    id_horario SERIAL PRIMARY KEY,
    id_curso INT NOT NULL,
    dia_semana INT NOT NULL CHECK (dia_semana BETWEEN 1 AND 5),
    hora_inicio TIME NOT NULL,
    hora_fin TIME NOT NULL,
    aula VARCHAR(50),

    CONSTRAINT fk_horario_curso
        FOREIGN KEY(id_curso)
        REFERENCES cursos(id_curso)
        ON DELETE CASCADE
);

CREATE TABLE proyectos (
    id_proyecto SERIAL PRIMARY KEY,
    id_curso INT NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT,
    fecha_entrega TIMESTAMP,
    estado VARCHAR(50) DEFAULT 'en curso',
    porcentaje_completado NUMERIC(5,2) DEFAULT 0,

    CONSTRAINT fk_proyecto_curso
        FOREIGN KEY(id_curso)
        REFERENCES cursos(id_curso)
        ON DELETE CASCADE
);

CREATE TABLE proyectos_estudiantes (
    id_proyecto INT NOT NULL,
    id_alumno INT NOT NULL,

    PRIMARY KEY (id_proyecto, id_alumno),
    CONSTRAINT fk_pe_proyecto
        FOREIGN KEY(id_proyecto)
        REFERENCES proyectos(id_proyecto)
        ON DELETE CASCADE,
    CONSTRAINT fk_pe_alumno
        FOREIGN KEY(id_alumno)
        REFERENCES alumnos(id_alumno)
        ON DELETE CASCADE
);

CREATE TABLE examenes (
    id_examen SERIAL PRIMARY KEY,
    id_curso INT NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    temario TEXT,
    fecha TIMESTAMP NOT NULL,
    duracion_minutos INT NOT NULL,
    modalidad VARCHAR(50),

    CONSTRAINT fk_examen_curso
        FOREIGN KEY(id_curso)
        REFERENCES cursos(id_curso)
        ON DELETE CASCADE
);