console.sql

/*
criação das tabelas
*/
create table usuario
(
    registro serial primary key,
    nome     varchar(100) not null,
    telefone varchar(20)
);

create table livro
(
    id                serial primary key,
    titulo            varchar(200) not null,
    autor             varchar(100) not null,
    ano_de_publicacao integer -- apenas o ano
);

create table categoria
(
    id   serial primary key,
    nome varchar(50) unique not null
);

create table livro_categoria
(
    id_livro     integer not null,
    id_categoria integer not null,

    /*
    Decidi colocar chave composta no lugar de id próprio + unique.
    Se eu tento criar sem chave primária dá erro.
    */
    primary key (id_livro, id_categoria),

    /*
    se deletar algum livro, todas as referências dessa tabela vão junto.
    */
    foreign key (id_livro) references livro (id) on delete cascade,
    foreign key (id_categoria) references categoria (id) on delete cascade
);

create table exemplar
(
    id    serial primary key,
    livro integer not null,
    foreign key (livro) references livro (id) on delete cascade
);

/*
havia uma tabela de histórico dos empréstimos, mas ela torna-se
desnecessária considerando que a tabela de empréstimo é o histórico.
*/
create table emprestimo
(
    id              serial primary key,
    id_exemplar     integer not null,
    usuario         integer not null,
    data_emprestimo date    not null,
    data_devolucao  date, -- null = empréstimo corrente
    foreign key (id_exemplar) references exemplar (id) on delete cascade,
    foreign key (usuario) references usuario (registro) on delete cascade
);

/*
médoto mais simples que achei para garantir que
o empréstimo do exemplar será registrado para
uma única pessoa por vez.
*/
create unique index emprestimos_correntes
    on emprestimo (id_exemplar)
    where data_devolucao is null;

/*
inserção de dados
*/
INSERT INTO usuario (nome, telefone)
VALUES ('João Silva', '84987654321'),
       ('Maria Santos', '84976543210'),
       ('Pedro Oliveira', '84965432109'),
       ('Ana Costa', '84954321098'),
       ('Carlos Souza', '84943210987'),
       ('Lucia Ferreira', '84932109876'),
       ('Roberto Lima', '84921098765'),
       ('Juliana Alves', '84910987654');

INSERT INTO livro (titulo, autor, ano_de_publicacao)
VALUES ('1984', 'George Orwell', 1949),
       ('O Senhor dos Anéis', 'J.R.R. Tolkien', 1954),
       ('Dom Casmurro', 'Machado de Assis', 1899),
       ('Harry Potter e a Pedra Filosofal', 'J.K. Rowling', 1997),
       ('O Pequeno Príncipe', 'Antoine de Saint-Exupéry', 1943),
       ('Cem Anos de Solidão', 'Gabriel García Márquez', 1967),
       ('A Revolução dos Bichos', 'George Orwell', 1945),
       ('O Hobbit', 'J.R.R. Tolkien', 1937),
       ('Memórias Póstumas de Brás Cubas', 'Machado de Assis', 1881),
       ('O Código Da Vinci', 'Dan Brown', 2003),
       ('Clean Code', 'Robert C. Martin', 2008),
       ('Design Patterns', 'Gang of Four', 1994),
       ('Sapiens', 'Yuval Noah Harari', 2011),
       ('A Arte da Guerra', 'Sun Tzu', -500),
       ('O Alquimista', 'Paulo Coelho', 1988);

INSERT INTO categoria (nome)
VALUES ('Ficção'),
       ('Fantasia'),
       ('Clássico'),
       ('Romance'),
       ('Aventura'),
       ('Tecnologia'),
       ('História'),
       ('Filosofia'),
       ('Autoajuda'),
       ('Mistério');

INSERT INTO livro_categoria (id_livro, id_categoria)
VALUES (1, 1),
       (1, 3),
       (2, 2),
       (2, 5),
       (3, 3),
       (3, 4),
       (4, 2),
       (4, 5),
       (5, 1),
       (5, 3),
       (6, 1),
       (6, 3),
       (7, 1),
       (7, 3),
       (8, 2),
       (8, 5),
       (9, 3),
       (10, 10),
       (10, 5),
       (11, 6),
       (12, 6),
       (13, 7),
       (13, 8),
       (14, 8),
       (14, 3),
       (15, 9),
       (15, 1);

INSERT INTO exemplar (livro)
VALUES (1),
       (1),
       (1),
       (2),
       (2),
       (3),
       (3),
       (4),
       (4),
       (4),
       (4),
       (5),
       (5),
       (5),
       (6),
       (7),
       (7),
       (8),
       (8),
       (9),
       (10),
       (10),
       (11),
       (12),
       (13),
       (13),
       (14),
       (15),
       (15);

INSERT INTO emprestimo (id_exemplar, usuario, data_emprestimo, data_devolucao)
VALUES (1, 1, '2024-12-01', '2024-12-15'),
       (8, 2, '2024-11-10', '2024-11-25'),
       (4, 3, '2024-12-05', '2024-12-20'),
       (12, 4, '2024-11-15', '2024-11-30'),
       (23, 5, '2024-12-10', '2024-12-24'),
       (6, 6, '2024-11-01', '2024-11-15'),
       (25, 7, '2024-12-01', '2024-12-15'),
       (18, 1, '2024-12-20', '2025-01-05'),
       (2, 2, '2024-12-15', '2024-12-30');

INSERT INTO emprestimo (id_exemplar, usuario, data_emprestimo, data_devolucao)
VALUES (9, 1, '2025-01-15', NULL),
       (21, 2, '2025-01-18', NULL),
       (16, 3, '2025-01-20', NULL),
       (28, 4, '2025-01-22', NULL),
       (24, 5, '2025-01-10', NULL),
       (15, 8, '2025-01-25', NULL);

/*
consultas sql
*/
-- Selecione todos os livros cadastrados.
SELECT *
FROM livro;

-- Selecione todos os usuários com os seus telefones.
SELECT registro, nome, telefone
FROM usuario;

-- Liste todos os exemplares com o título do livro correspondente
SELECT e.id, l.titulo, l.autor
FROM exemplar e
         JOIN livro l ON e.livro = l.id;

-- Encontre todos os livros da categoria "Ficção"
SELECT l.titulo, l.autor, c.nome AS categoria
FROM livro l
         JOIN livro_categoria lc ON l.id = lc.id_livro
         JOIN categoria c ON lc.id_categoria = c.id
WHERE c.nome = 'Ficção';

-- Liste todos os empréstimos ativos (não devolvidos)
SELECT emp.id, u.nome AS usuario, l.titulo, emp.data_emprestimo
FROM emprestimo emp
         JOIN usuario u ON emp.usuario = u.registro
         JOIN exemplar e ON emp.id_exemplar = e.id
         JOIN livro l ON e.livro = l.id
WHERE emp.data_devolucao IS NULL;

-- Liste todos os empréstimos finalizados (devolvidos)
SELECT emp.id, u.nome AS usuario, l.titulo, emp.data_emprestimo, emp.data_devolucao
FROM emprestimo emp
         JOIN usuario u ON emp.usuario = u.registro
         JOIN exemplar e ON emp.id_exemplar = e.id
         JOIN livro l ON e.livro = l.id
WHERE emp.data_devolucao IS NOT NULL;

-- Conte quantos exemplares cada livro possui
SELECT l.titulo, l.autor, COUNT(e.id) AS total_exemplares
FROM livro l
         LEFT JOIN exemplar e ON l.id = e.livro
GROUP BY l.id, l.titulo, l.autor
ORDER BY total_exemplares DESC;

-- Liste os exemplares disponíveis (não estão emprestados)
SELECT e.id, l.titulo, l.autor
FROM exemplar e
         JOIN livro l ON e.livro = l.id
WHERE NOT EXISTS (SELECT 1
                  FROM emprestimo emp
                  WHERE emp.id_exemplar = e.id
                    AND emp.data_devolucao IS NULL);

-- Encontre os livros mais emprestados
SELECT l.titulo, l.autor, COUNT(emp.id) AS total_emprestimos
FROM livro l
         JOIN exemplar e ON l.id = e.livro
         JOIN emprestimo emp ON e.id = emp.id_exemplar
GROUP BY l.id, l.titulo, l.autor
ORDER BY total_emprestimos DESC
LIMIT 10;

-- Liste os usuários que têm empréstimos ativos
SELECT DISTINCT u.registro, u.nome, u.telefone
FROM usuario u
         JOIN emprestimo emp ON u.registro = emp.usuario
WHERE emp.data_devolucao IS NULL;

-- Mostre o histórico completo de empréstimos de um utilizador específico
SELECT l.titulo,
       emp.data_emprestimo,
       emp.data_devolucao,
       CASE
           WHEN emp.data_devolucao IS NULL THEN 'Em andamento'
           ELSE 'Devolvido'
           END AS status
FROM emprestimo emp
         JOIN exemplar e ON emp.id_exemplar = e.id
         JOIN livro l ON e.livro = l.id
WHERE emp.usuario = 1
ORDER BY emp.data_emprestimo DESC;

-- Liste todas as categorias e quantos livros cada uma possui
SELECT c.nome AS categoria, COUNT(lc.id_livro) AS quantidade_livros
FROM categoria c
         LEFT JOIN livro_categoria lc ON c.id = lc.id_categoria
GROUP BY c.id, c.nome
ORDER BY quantidade_livros DESC;

-- Encontre livros que nunca foram emprestados
SELECT l.id, l.titulo, l.autor
FROM livro l
WHERE NOT EXISTS (SELECT 1
                  FROM exemplar e
                           JOIN emprestimo emp ON e.id = emp.id_exemplar
                  WHERE e.livro = l.id);

-- Calcule quantos dias cada empréstimo ativo está em aberto
SELECT u.nome                             AS usuario,
       l.titulo,
       emp.data_emprestimo,
       CURRENT_DATE - emp.data_emprestimo AS dias_emprestado
FROM emprestimo emp
         JOIN usuario u ON emp.usuario = u.registro
         JOIN exemplar e ON emp.id_exemplar = e.id
         JOIN livro l ON e.livro = l.id
WHERE emp.data_devolucao IS NULL
ORDER BY dias_emprestado DESC;

-- Liste todos os livros com as suas categorias (mesmo sem categoria)
SELECT l.titulo,
       l.autor,
       l.ano_de_publicacao,
       STRING_AGG(c.nome, ', ') AS categorias
FROM livro l
         LEFT JOIN livro_categoria lc ON l.id = lc.id_livro
         LEFT JOIN categoria c ON lc.id_categoria = c.id
GROUP BY l.id, l.titulo, l.autor, l.ano_de_publicacao
ORDER BY l.titulo;