-- ==========================================
-- ACTIVIDAD SQL
-- EJERCICIO 2: SISTEMA DE BIBLIOTECA
-- RELACION MUCHOS A MUCHOS (N:M)
-- ==========================================


-- Eliminar vistas y tablas si ya existen
-- (para poder ejecutar el script varias veces sin errores)

DROP VIEW IF EXISTS vista_catalogo_completo;
DROP VIEW IF EXISTS vista_autores_prolificos;
DROP TABLE IF EXISTS LIBRO_AUTOR;
DROP TABLE IF EXISTS LIBRO;
DROP TABLE IF EXISTS AUTOR;



-- ==========================================
-- CREACION DE TABLAS
-- ==========================================


-- Tabla de autores

CREATE TABLE AUTOR (
    id_autor INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL
);



-- Tabla de libros

CREATE TABLE LIBRO (
    id_libro INTEGER PRIMARY KEY,
    titulo TEXT NOT NULL,
    anio INTEGER
);



-- Tabla intermedia para resolver la relacion N:M
-- Un libro puede tener varios autores y un autor puede escribir varios libros

CREATE TABLE LIBRO_AUTOR (
    id_libro INTEGER,
    id_autor INTEGER,

    PRIMARY KEY (id_libro, id_autor),

    FOREIGN KEY (id_libro) REFERENCES LIBRO(id_libro),
    FOREIGN KEY (id_autor) REFERENCES AUTOR(id_autor)
);



-- ==========================================
-- INSERCION DE DATOS DE PRUEBA
-- ==========================================


INSERT INTO AUTOR VALUES
(1, 'Gabriel Garcia Marquez'),
(2, 'Isabel Allende'),
(3, 'Jorge Luis Borges'),
(4, 'Mario Vargas Llosa');


INSERT INTO LIBRO VALUES
(1, 'Cien años de soledad', 1967),
(2, 'El coronel no tiene quien le escriba', 1961),
(3, 'La casa de los espiritus', 1982),
(4, 'Antologia Latinoamericana', 2024),
(5, 'Manual de Bases de Datos', 2025);


-- Relacion entre libros y autores

INSERT INTO LIBRO_AUTOR VALUES
(1, 1),   -- Garcia Marquez escribio "Cien años de soledad"
(2, 1),   -- Garcia Marquez escribio "El coronel..."
(3, 2),   -- Allende escribio "La casa de los espiritus"
(4, 2),   -- Allende participo en "Antologia Latinoamericana"
(4, 4),   -- Vargas Llosa participo en "Antologia Latinoamericana"
(5, 1),   -- Garcia Marquez participo en "Manual de Bases de Datos"
(5, 3);   -- Borges participo en "Manual de Bases de Datos"



-- ==========================================
-- PARTE 4: CREACION DE VISTAS
-- ==========================================



-- ==========================================
-- VISTA 2.1
-- Catalogo completo de la biblioteca
-- Muestra:
-- titulo del libro
-- año de publicacion
-- nombre del autor
-- ==========================================


CREATE VIEW vista_catalogo_completo AS

SELECT
    LIBRO.titulo AS Libro,
    LIBRO.anio AS Anio_Publicacion,
    AUTOR.nombre AS Autor

FROM LIBRO

INNER JOIN LIBRO_AUTOR
ON LIBRO.id_libro = LIBRO_AUTOR.id_libro

INNER JOIN AUTOR
ON LIBRO_AUTOR.id_autor = AUTOR.id_autor;



-- Ver resultado de la vista

SELECT * FROM vista_catalogo_completo;



-- ==========================================
-- VISTA 2.2
-- Autores prolificos
-- Muestra autores con exactamente dos libros
-- ==========================================


CREATE VIEW vista_autores_prolificos AS

SELECT
    AUTOR.id_autor,
    AUTOR.nombre AS Autor,
    COUNT(LIBRO_AUTOR.id_libro) AS Cantidad_Libros

FROM AUTOR

INNER JOIN LIBRO_AUTOR
ON AUTOR.id_autor = LIBRO_AUTOR.id_autor

GROUP BY AUTOR.id_autor, AUTOR.nombre

HAVING COUNT(LIBRO_AUTOR.id_libro) = 2;



-- Ver resultado de la vista

SELECT * FROM vista_autores_prolificos;



-- ==========================================
-- CONSULTAS DE PRUEBA
-- ==========================================


-- Mostrar todos los autores

SELECT * FROM AUTOR;



-- Mostrar todos los libros

SELECT * FROM LIBRO;



-- Mostrar la relacion entre libros y autores

SELECT
    LIBRO.titulo AS Libro,
    AUTOR.nombre AS Autor

FROM LIBRO_AUTOR

INNER JOIN LIBRO
ON LIBRO_AUTOR.id_libro = LIBRO.id_libro

INNER JOIN AUTOR
ON LIBRO_AUTOR.id_autor = AUTOR.id_autor;