/* Proyecto de Aplicación Práctica - Fundamentos de Base de Datos */
/* Sistema de Gestión de Inscripciones Académicas */
/* Motor: SQLite (ejecutado en myCompiler - www.mycompiler.io/es/new/sql) */

/* 1. CONFIGURACIÓN */
/* En SQLite la base de datos se crea automáticamente al ejecutar el script. */
/* Se activa la verificación de claves foráneas: */
PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS Inscripcion;
DROP TABLE IF EXISTS Curso;
DROP TABLE IF EXISTS Estudiante;

/* 2. CREACIÓN DE TABLAS */

CREATE TABLE Estudiante (
    id_estudiante INTEGER PRIMARY KEY,
    nombre        VARCHAR(50)  NOT NULL,
    apellido      VARCHAR(50)  NOT NULL,
    correo        VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Curso (
    id_curso  INTEGER PRIMARY KEY,
    nombre    VARCHAR(100) NOT NULL,
    creditos  INT          NOT NULL CHECK (creditos > 0),
    semestre  VARCHAR(20)  NOT NULL
);

/* Tabla intermedia: resuelve la relación N:M entre Estudiante y Curso */
CREATE TABLE Inscripcion (
    id_inscripcion INTEGER PRIMARY KEY,
    id_estudiante  INT  NOT NULL,
    id_curso       INT  NOT NULL,
    fecha          DATE NOT NULL,
    FOREIGN KEY (id_estudiante) REFERENCES Estudiante(id_estudiante),
    FOREIGN KEY (id_curso)      REFERENCES Curso(id_curso),
    UNIQUE (id_estudiante, id_curso) /* evita inscripciones duplicadas */
);

/* 3. INSERCIÓN DE DATOS DE EJEMPLO */

INSERT INTO Estudiante (id_estudiante, nombre, apellido, correo) VALUES
(1, 'Ana',     'Pérez',     'ana.perez@email.com'),
(2, 'Carlos',  'Gómez',     'carlos.gomez@email.com'),
(3, 'María',   'López',     'maria.lopez@email.com'),
(4, 'José',    'Ramírez',   'jose.ramirez@email.com'),
(5, 'Lucía',   'Fernández', 'lucia.fernandez@email.com'),
(6, 'Andrés',  'Guerrero',  'andres.guerrero@email.com');

INSERT INTO Curso (id_curso, nombre, creditos, semestre) VALUES
(1, 'Bases de Datos I',        4, '2026-1'),
(2, 'Programación en Python',  3, '2026-1'),
(3, 'Redes de Computadoras',   3, '2026-1'),
(4, 'Matemática Discreta',     3, '2026-2');

INSERT INTO Inscripcion (id_estudiante, id_curso, fecha) VALUES
(1, 1, '2026-03-01'),
(2, 1, '2026-03-02'),
(3, 2, '2026-03-03'),
(4, 2, '2026-03-04'),
(5, 3, '2026-03-05'),
(1, 2, '2026-03-06'),
(2, 3, '2026-03-07'),
(6, 1, '2026-03-08'),
(6, 4, '2026-09-01'),
(3, 4, '2026-09-02');

/* 4. CONSULTAS ÚTILES */

/* 4.1 Listado de estudiantes por curso (ejemplo: curso 1) */
SELECT e.nombre, e.apellido
FROM Estudiante e
JOIN Inscripcion i ON e.id_estudiante = i.id_estudiante
WHERE i.id_curso = 1;

/* 4.2 Listado general de estudiantes agrupado por curso */
SELECT c.nombre AS curso, e.nombre || ' ' || e.apellido AS estudiante, i.fecha
FROM Inscripcion i
JOIN Curso c      ON c.id_curso = i.id_curso
JOIN Estudiante e ON e.id_estudiante = i.id_estudiante
ORDER BY c.nombre, e.apellido;

/* 4.3 Cursos inscritos por estudiante (ejemplo: estudiante 1) */
SELECT c.nombre, c.creditos, c.semestre
FROM Curso c
JOIN Inscripcion i ON c.id_curso = i.id_curso
WHERE i.id_estudiante = 1;

/* 4.4 Número total de inscritos por curso */
SELECT c.nombre, COUNT(i.id_estudiante) AS total_inscritos
FROM Curso c
LEFT JOIN Inscripcion i ON c.id_curso = i.id_curso
GROUP BY c.nombre
ORDER BY total_inscritos DESC, c.nombre;

/* 5. MODIFICACIÓN DE DATOS */

/* 5.1 Actualizar el correo de un estudiante */
UPDATE Estudiante
SET correo = 'maria.lopez2026@email.com'
WHERE id_estudiante = 3;

/* 5.2 Cambiar el semestre de un curso */
UPDATE Curso
SET semestre = '2026-2'
WHERE id_curso = 3;

/* 5.3 Eliminar una inscripción (retiro de un curso) */
DELETE FROM Inscripcion
WHERE id_estudiante = 2 AND id_curso = 3;

/* 5.4 Verificación de los cambios */
SELECT c.nombre, c.semestre, COUNT(i.id_estudiante) AS total_inscritos
FROM Curso c
LEFT JOIN Inscripcion i ON c.id_curso = i.id_curso
GROUP BY c.nombre, c.semestre
ORDER BY total_inscritos DESC, c.nombre;