-- ACTIVIDAD 2. Creación de tablas
CREATE TABLE autores (
    id_autor INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    nacionalidad VARCHAR(60)
);

CREATE TABLE categorias (
    id_categoria INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(80) NOT NULL UNIQUE,
    descripcion VARCHAR(200)
);

CREATE TABLE lectores (
    id_lector INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    correo VARCHAR(100) UNIQUE,
    telefono VARCHAR(20)
);

CREATE TABLE libros (
    id_libro INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(150) NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    anio_publicacion YEAR,
    precio DECIMAL(10,2) NOT NULL,
    ejemplares_disponibles INT DEFAULT 0,
    id_autor INT,
    id_categoria INT,
    FOREIGN KEY (id_autor) REFERENCES autores(id_autor),
    FOREIGN KEY (id_categoria) REFERENCES categorias(id_categoria)
);

CREATE TABLE prestamos (
    id_prestamo INT PRIMARY KEY AUTO_INCREMENT,
    fecha_prestamo DATE NOT NULL,
    fecha_devolucion DATE,
    estado VARCHAR(20) DEFAULT 'Activo',
    id_libro INT,
    id_lector INT,
    FOREIGN KEY (id_libro) REFERENCES libros(id_libro),
    FOREIGN KEY (id_lector) REFERENCES lectores(id_lector)
);

SHOW TABLES;

-- ACTIVIDAD 3. Modificación de estructuras (ALTER TABLE)
ALTER TABLE libros ADD COLUMN editorial VARCHAR(100);

ALTER TABLE lectores ADD COLUMN direccion VARCHAR(150);

ALTER TABLE libros MODIFY titulo VARCHAR(200) NOT NULL;

ALTER TABLE libros ADD CONSTRAINT chk_libros_precio CHECK (precio >= 0);

ALTER TABLE prestamos MODIFY estado VARCHAR(30) DEFAULT 'Activo';

DESCRIBE libros;
DESCRIBE lectores;
DESCRIBE prestamos;

-- ACTIVIDAD 4. Operaciones adicionales DDL (tabla de pruebas)
CREATE TABLE laboratorio_ddl (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(100)
);
INSERT INTO laboratorio_ddl (descripcion) VALUES
('Registro de prueba 1'), ('Registro de prueba 2'), ('Registro de prueba 3');
SELECT * FROM laboratorio_ddl;
TRUNCATE TABLE laboratorio_ddl;
SELECT * FROM laboratorio_ddl;            -- vacía
SHOW TABLES LIKE 'laboratorio_ddl';       -- la tabla sigue existiendo
DROP TABLE laboratorio_ddl;
-- Debe dar ERROR 1146 (la tabla ya no existe). Descomentar para la captura:
-- SELECT * FROM laboratorio_ddl;
-- TRUNCATE vacía la tabla pero conserva su estructura; DROP elimina la
-- tabla completa (estructura y datos).

-- ACTIVIDAD 5. INSERT
-- Insertar autores
INSERT INTO autores (nombres, apellidos, nacionalidad) VALUES
('Gabriel', 'García Márquez', 'Colombiano'),
('Isabel', 'Allende', 'Chilena'),
('Mario', 'Vargas Llosa', 'Peruana'),
('Rubén', 'Darío', 'Nicaragüense'),
('Sergio', 'Ramírez', 'Nicaragüense');

-- Insertar categorías
INSERT INTO categorias (nombre, descripcion) VALUES
('Novela', 'Obras narrativas extensas de ficción'),
('Poesía', 'Obras escritas en verso'),
('Cuento', 'Narraciones breves de ficción'),
('Ensayo', 'Textos de reflexión y análisis'),
('Crónica', 'Relatos periodísticos y testimoniales');

-- Insertar lectores
INSERT INTO lectores
(nombres, apellidos, correo, telefono, direccion) VALUES
('Ana', 'Martínez', 'ana.martinez@uni.edu.ni', '8855-1001', 'Managua'),
('Luis', 'Pérez', 'luis.perez@uni.edu.ni', '8855-1002', 'Masaya'),
('María', 'Mendoza', 'maria.mendoza@uni.edu.ni', '8855-1003', 'León'),
('Carlos', 'Gómez', 'carlos.gomez@uni.edu.ni', '8855-1004', 'Granada'),
('Sofía', 'Ruiz', 'sofia.ruiz@uni.edu.ni', '8855-1005', 'Estelí'),
('José', 'Morales', 'jose.morales@uni.edu.ni', '8855-1006', 'Matagalpa'),
('Elena', 'Castillo', 'elena.castillo@uni.edu.ni', '8855-1007', 'Chinandega'),
('Pedro', 'López', 'pedro.lopez@uni.edu.ni', '8855-1008', 'Jinotepe');

-- Comprobar los registros
SELECT * FROM autores;
SELECT * FROM categorias;
SELECT * FROM lectores;

-- Insertar libros
INSERT INTO libros
(titulo, isbn, anio_publicacion, precio,
 ejemplares_disponibles, id_autor, id_categoria, editorial)
VALUES
('Cien años de soledad', '9780307474728', 1967, 1250.00, 5, 1, 1, 'Sudamericana'),
('La casa de los espiritus', '9788401352898', 1982, 1100.00, 4, 2, 1, 'Plaza y Janes'),
('La ciudad y los perros', '9788437604947', 1963, 1000.00, 3, 3, 1, 'Seix Barral'),
('Azul', '9788437601234', 2008, 750.00, 6, 4, 2, 'Editorial Universitaria'),
('El reino animal', '9788437605678', 2017, 900.00, 4, 5, 3, 'Alfaguara'),
('El amor en los tiempos del colera', '9780307389732', 1985, 1200.00, 5, 1, 1, 'Oveja Negra'),
('Eva Luna', '9788401352904', 1987, 1050.00, 3, 2, 1, 'Plaza y Janes'),
('La fiesta del chivo', '9788432210452', 2000, 1150.00, 2, 3, 1, 'Alfaguara'),
('Prosas profanas', '9788437609876', 2010, 800.00, 5, 4, 2, 'Editorial Universitaria'),
('Margarita', '9788437612345', 1990, 700.00, 7, 4, 2, 'Editorial Universitaria'),
('Castigo divino', '9789992400011', 2018, 950.00, 4, 5, 5, 'Nueva Nicaragua'),
('Un baile de mascaras', '9789992400028', 2019, 850.00, 3, 5, 1, 'Anama');

-- Insertar 10 prestamos
INSERT INTO prestamos
(fecha_prestamo, fecha_devolucion, estado, id_libro, id_lector)
VALUES
('2026-10-01', '2026-10-10', 'Devuelto', 1, 1),
('2026-10-02', '2026-10-12', 'Devuelto', 2, 2),
('2026-10-03', NULL, 'Vencido', 3, 3),
('2026-10-04', '2026-10-14', 'Devuelto', 4, 4),
('2026-10-05', NULL, 'Vencido', 5, 5),
('2026-10-06', NULL, 'Activo', 6, 6),
('2026-10-07', '2026-10-15', 'Devuelto', 7, 7),
('2026-10-08', NULL, 'Activo', 8, 7),
('2026-10-09', '2026-10-18', 'Devuelto', 9, 1),
('2026-10-10', NULL, 'Activo', 10, 2);

-- Nota: anio_publicacion es YEAR (rango 1901-2155), por eso los años
-- corresponden a la edición del libro.
-- Verificar los registros
SELECT * FROM libros;
SELECT * FROM prestamos;

-- Actividad 6. SELECT
-- Consultas básicas
SELECT * FROM autores;
SELECT * FROM categorias;
SELECT nombres, apellidos FROM lectores;
SELECT * FROM libros;
SELECT titulo, precio FROM libros;
SELECT * FROM libros WHERE precio > 1000;
SELECT * FROM libros WHERE anio_publicacion > 2015;
SELECT * FROM libros WHERE ejemplares_disponibles < 5;

-- Consultas con condiciones
SELECT * FROM libros WHERE precio BETWEEN 500 AND 2000;
SELECT * FROM lectores WHERE apellidos LIKE 'M%';
SELECT * FROM libros WHERE id_categoria IN (1, 2);   -- Novela y Poesía
SELECT * FROM prestamos WHERE estado = 'Activo';
SELECT * FROM prestamos
 WHERE fecha_prestamo BETWEEN '2026-10-03' AND '2026-10-07';
SELECT * FROM libros ORDER BY titulo ASC;
SELECT * FROM libros ORDER BY precio DESC LIMIT 5;

-- Consultas con funciones
SELECT COUNT(*) AS total_libros FROM libros;
SELECT ROUND(AVG(precio), 2) AS promedio_precios FROM libros;
SELECT MAX(precio) AS precio_mas_alto,
       MIN(precio) AS precio_mas_bajo FROM libros;
SELECT COUNT(*) AS total_prestamos FROM prestamos;
SELECT COUNT(*) AS prestamos_activos
  FROM prestamos WHERE estado = 'Activo';

-- Actividad 7. SELECT + INNER JOIN
-- 7.1 Libro con su autor
SELECT l.titulo, a.nombres, a.apellidos
  FROM libros l
  INNER JOIN autores a ON l.id_autor = a.id_autor;

-- 7.2 Libro con su categoría
SELECT l.titulo, c.nombre AS categoria
  FROM libros l
  INNER JOIN categorias c ON l.id_categoria = c.id_categoria;

-- 7.3 Lectores que han realizado préstamos
SELECT DISTINCT lc.id_lector, lc.nombres, lc.apellidos
  FROM lectores lc
  INNER JOIN prestamos p ON lc.id_lector = p.id_lector;

-- 7.4 Lector, libro y fecha del préstamo
SELECT lc.nombres, lc.apellidos, l.titulo, p.fecha_prestamo
  FROM prestamos p
  INNER JOIN lectores lc ON p.id_lector = lc.id_lector
  INNER JOIN libros   l  ON p.id_libro  = l.id_libro;

-- 7.5 Libros actualmente prestados (no devueltos: Activo o Vencido)
SELECT DISTINCT l.titulo, p.estado
  FROM prestamos p
  INNER JOIN libros l ON p.id_libro = l.id_libro
 WHERE p.estado <> 'Devuelto';

-- 7.6 Préstamos de un lector específico (id_lector = 1)
SELECT p.id_prestamo, l.titulo, p.fecha_prestamo, p.fecha_devolucion, p.estado
  FROM prestamos p
  INNER JOIN libros l ON p.id_libro = l.id_libro
 WHERE p.id_lector = 1;

-- 7.7 Libros de una categoría (Novela) con el nombre de su autor
SELECT l.titulo, a.nombres, a.apellidos
  FROM libros l
  INNER JOIN categorias c ON l.id_categoria = c.id_categoria
  INNER JOIN autores    a ON l.id_autor     = a.id_autor
 WHERE c.nombre = 'Novela';

-- 7.8 Préstamos ya devueltos
SELECT p.id_prestamo, lc.nombres, lc.apellidos, l.titulo,
       p.fecha_prestamo, p.fecha_devolucion
  FROM prestamos p
  INNER JOIN lectores lc ON p.id_lector = lc.id_lector
  INNER JOIN libros   l  ON p.id_libro  = l.id_libro
 WHERE p.estado = 'Devuelto';

-- Actividad 8. UPDATE (cada cambio se comprueba con SELECT)
-- 8.1 Nacionalidad de un autor
UPDATE autores SET nacionalidad = 'Colombiana' WHERE id_autor = 1;
SELECT * FROM autores WHERE id_autor = 1;

-- 8.2 Precio de tres libros
UPDATE libros SET precio = 900.00  WHERE id_libro = 1;
UPDATE libros SET precio = 1300.00 WHERE id_libro = 6;
UPDATE libros SET precio = 480.00  WHERE id_libro = 7;
SELECT id_libro, titulo, precio FROM libros WHERE id_libro IN (1, 6, 7);

-- 8.3 Ejemplares disponibles de dos libros
UPDATE libros SET ejemplares_disponibles = ejemplares_disponibles + 3
 WHERE id_libro IN (6, 12);
SELECT id_libro, titulo, ejemplares_disponibles
  FROM libros WHERE id_libro IN (6, 12);

-- 8.4 Correo de un lector
UPDATE lectores SET correo = 'ana.martinez.nuevo@uni.edu.ni'
 WHERE id_lector = 1;
SELECT id_lector, nombres, correo FROM lectores WHERE id_lector = 1;

-- 8.5 Estado de un préstamo: Activo -> Devuelto
UPDATE prestamos
   SET estado = 'Devuelto', fecha_devolucion = '2026-11-12'
 WHERE id_prestamo = 6;
SELECT * FROM prestamos WHERE id_prestamo = 6;

-- 8.6 Fecha de devolución de un préstamo
UPDATE prestamos SET fecha_devolucion = '2026-10-11' WHERE id_prestamo = 1;
SELECT * FROM prestamos WHERE id_prestamo = 1;

-- Actividad 9. DELETE
-- 9.1 Eliminar un préstamo de prueba
DELETE FROM prestamos WHERE id_prestamo = 4;
SELECT * FROM prestamos;

-- 9.2 Intentar eliminar un lector CON préstamos (el lector 3 tiene el préstamo 3)
-- DEBE FALLAR: ERROR 1451 (23000): Cannot delete or update a parent row:
-- a foreign key constraint fails (...fk_prestamos_lector...)
-- (descomentar para tomar la captura del error)
-- DELETE FROM lectores WHERE id_lector = 3;

-- 9.3 ¿Por qué lo impide el gestor?
--     Porque prestamos.id_lector es clave foránea hacia lectores. Si se
--     borrara el lector, sus préstamos quedarían apuntando a un registro
--     que no existe (registros huérfanos) y se perdería la integridad
--     referencial.

-- 9.4 Eliminar un lector SIN préstamos (el lector 8)
DELETE FROM lectores WHERE id_lector = 8;
SELECT * FROM lectores;

-- 9.5 Importancia de las claves foráneas al eliminar:
--     Protegen la coherencia: impiden borrar datos de los que otras tablas
--     dependen (o, con ON DELETE CASCADE/SET NULL, definen qué hacer con
--     los registros relacionados).

-- =====================================================================
-- PARTE III. ANÁLISIS Y RESOLUCIÓN DE PROBLEMAS
-- =====================================================================

-- 1) Insertar un libro con id_autor inexistente.
--    DEBE FALLAR: ERROR 1452 (23000): Cannot add or update a child row:
--    a foreign key constraint fails. El gestor rechaza el INSERT porque
--    el autor 99 no existe en autores.
-- INSERT INTO libros (titulo, isbn, anio_publicacion, precio,
--                     ejemplares_disponibles, id_autor, id_categoria)
-- VALUES ('Libro de prueba', '978-0-0000-0099', 2024, 500.00, 1, 99, 1);

-- 2) Dos categorías con el mismo nombre (UNIQUE).
--    DEBE FALLAR: ERROR 1062 (23000): Duplicate entry 'Novela' for key
--    'categorias.nombre'. UNIQUE impide valores repetidos en esa columna.
-- INSERT INTO categorias (nombre, descripcion) VALUES ('Novela', 'Repetida');

-- 3) ¿Por qué crear primero autores y categorias antes que libros?
--    Porque libros contiene claves foráneas hacia ellas. Al crear la
--    clave