CREATE TABLE autores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(100) NOT NULL,
    nacionalidade VARCHAR(50)
);

CREATE TABLE editoras (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(100) NOT NULL,
    cidade VARCHAR(50)
);

CREATE TABLE livros (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(100) NOT NULL,
    autor_id INT,
    editora_id INT,
    ano_publicacao INT,
    FOREIGN KEY (autor_id) REFERENCES autores(id),
    FOREIGN KEY (editora_id) REFERENCES editoras(id)
);

CREATE TABLE emprestimos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    livro_id INT,
    data_emprestimo DATE NOT NULL,
    data_devolucao DATE,
    FOREIGN KEY (livro_id) REFERENCES livros(id)
);

INSERT INTO autores (nome, nacionalidade)
VALUES
('Machado de Assis', 'Brasileiro'),
('Clarice Lispector', 'Brasileira'),
('George Orwell', 'Britânico'),
('J.K. Rowling', 'Britânica');

INSERT INTO editoras (nome, cidade)
VALUES
('Editora Brasil', 'São Paulo'),
('Global Editora', 'Rio de Janeiro'),
('Penguin Books', 'Londres'),
('Rocco', 'Rio de Janeiro');

INSERT INTO livros (titulo, autor_id, editora_id, ano_publicacao)
VALUES
('Dom Casmurro', 1, 1, 1899),
('A Hora da Estrela', 2, 2, 1977),
('1984', 3, 3, 1949),
('Harry Potter e a Pedra Filosofal', 4, 4, 1997);

INSERT INTO emprestimos (livro_id, data_emprestimo, data_devolucao)
VALUES
(1, '2023-10-01', '2023-10-15'),
(2, '2023-10-05', NULL),
(3, '2023-10-10', '2023-10-20'),
(4, '2023-10-12', NULL);

-- 01. Criar uma View que liste todos os livros com seus autores e editoras.
/*CREATE VIEW view_livros_detalhados AS
SELECT
    l.id AS livro_id,
    l.titulo,
    a.nome AS autor,
    e.nome AS editora,
    l.ano_publicacao
FROM
    livros l
JOIN
    autores a ON l.autor_id = a.id
JOIN
    editoras e ON l.editora_id = e.id;


SELECT * FROM view_livros_detalhados;*/


-- 02. Criar uma View que mostre os livros emprestados atualmente (sem data de devolução).
/*CREATE VIEW view_livros_emprestados AS
SELECT
    l.titulo,
    e.data_emprestimo,
    a.nome AS autor
FROM
    emprestimos e
JOIN
    livros l ON e.livro_id = l.id
JOIN
    autores a ON l.autor_id = a.id
WHERE
    e.data_devolucao IS NULL;

SELECT * FROM view_livros_emprestados;*/

-- 03. Criar uma View que liste todos os autores e a quantidade de livros que cada um escreveu.
CREATE VIEW view_autores_quantidade_livros AS
SELECT
    a.nome AS autor,
    COUNT(l.id) AS quantidade_livros
FROM
    autores a
LEFT JOIN
    livros l ON a.id = l.autor_id
GROUP BY
    a.id;

SELECT * FROM view_autores_quantidade_livros;


-- 04. Criar uma View que mostre os livros publicados após o ano 2000.
CREATE VIEW view_livros_recentes AS
SELECT
    l.titulo,
    a.nome AS autor,
    l.ano_publicacao
FROM
    livros l
JOIN
    autores a ON l.autor_id = a.id
WHERE
    l.ano_publicacao < 2000;

SELECT * FROM view_livros_recentes;

-- 05. Criar uma View que liste todos os empréstimos com detalhes do livro, autor e editora.
CREATE VIEW view_emprestimos_detalhados AS
SELECT
    e.id AS emprestimo_id,
    l.titulo,
    a.nome AS autor,
    ed.nome AS editora,
    e.data_emprestimo,
    e.data_devolucao
FROM
    emprestimos e
JOIN
    livros l ON e.livro_id = l.id
JOIN
    autores a ON l.autor_id = a.id
JOIN
    editoras ed ON l.editora_id = ed.id;

SELECT * FROM view_emprestimos_detalhados;


-- 06. Criar uma View que mostre os livros de uma editora específica (ex: "Global Editora").
CREATE VIEW view_livros_global_editora AS
SELECT
    l.titulo,
    a.nome AS autor,
    l.ano_publicacao
FROM
    livros l
JOIN
    autores a ON l.autor_id = a.id
JOIN
    editoras e ON l.editora_id = e.id
WHERE
    e.nome = 'Global Editora';

SELECT * FROM view_livros_global_editora;

-- 07. Criar uma View que liste os livros que nunca foram emprestados.
/*CREATE VIEW view_livros_nao_emprestados AS
SELECT
    l.titulo,
    a.nome AS autor
FROM
    livros l
JOIN
    autores a ON l.autor_id = a.id
LEFT JOIN
    emprestimos e ON l.id = e.livro_id
WHERE
    e.id IS NULL;

SELECT * FROM view_livros_nao_emprestados;*/

-- 08. Criar uma View que mostre os livros mais antigos (ordenados por ano de publicação).
/*CREATE VIEW view_livros_mais_antigos AS
SELECT
    l.titulo,
    a.nome AS autor,
    l.ano_publicacao
FROM
    livros l
JOIN
    autores a ON l.autor_id = a.id
ORDER BY
    l.ano_publicacao ASC;

SELECT * FROM view_livros_mais_antigos;*/

-- 09. Criar uma View que mostre a quantidade de livros emprestados por autor.
/*CREATE VIEW view_emprestimos_por_autor AS
SELECT
    a.nome AS autor,
    COUNT(e.id) AS quantidade_emprestimos
FROM
    autores a
LEFT JOIN
    livros l ON a.id = l.autor_id
LEFT JOIN
    emprestimos e ON l.id = e.livro_id
GROUP BY
    a.id;

SELECT * FROM view_emprestimos_por_autor;*/