Bagian 4: Laporan dan Query Lanjutan

-- data-awal.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: mengisi data contoh deterministik (buku dan anggota fiktif)
--   sebagai titik awal konsisten untuk seluruh bagian proyek.
-- Cara pakai: jalankan setelah skema.sql:
--   mysql -u root -p perpustakaan_db < database/data-awal.sql

INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2),
  ('BK-004', 'Dilan 1990', 'Pidi Baiq', 2014, 4),
  ('BK-005', 'Atomic Habits', 'James Clear', 2018, 2),
  ('BK-006', 'Matematika untuk SMA Kelas X', 'Tim Pusat Kurikulum', 2019, 6),
  ('BK-007', 'Kamus Besar Bahasa Indonesia', 'Badan Pengembangan Bahasa', 2016, 2),
  ('BK-008', 'Fisika Terapan untuk SMK', 'Ratna Wijaya', 2021, 3);

INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002'),
  ('Andi Wijaya', 'XI-IPA-1', '081234560003'),
  ('Dewi Lestari', 'XI-IPS-1', '081234560004'),
  ('Rina Marlina', 'XII-IPA-1', '081234560005'),
  ('Tono Sutrisno', 'XII-IPS-1', NULL),
  ('Sari Puspita', 'X-IPS-1', '081234560007'),
  ('Joko Prasetyo', 'XI-IPS-2', NULL);

-- skema.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: membuat database perpustakaan_db beserta keempat tabelnya
--   (buku, anggota, peminjaman, detail_peminjaman) lengkap dengan
--   primary key, foreign key, dan check constraint.
-- Cara pakai: mysql -u root -p < database/skema.sql

DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

-- Katalog buku; kolom stok menyimpan jumlah eksemplar yang tersedia di rak.
CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);

-- Anggota perpustakaan (siswa); telepon boleh kosong (NULL).
CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);

-- Satu baris per transaksi peminjaman; tanggal_kembali NULL berarti masih dipinjam.
CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);

-- Rincian buku per peminjaman; jembatan banyak-ke-banyak antara buku dan peminjaman.
CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

-- laporan-anggota-aktif.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menampilkan frekuensi peminjaman dan total buku yang dipinjam
--   tiap anggota, termasuk anggota yang belum pernah meminjam.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-anggota-aktif.sql

SELECT a.id, a.nama, a.kelas,
       COUNT(DISTINCT p.id) AS jumlah_peminjaman,
       COALESCE(SUM(dp.jumlah), 0) AS total_buku
FROM anggota a
LEFT JOIN peminjaman p ON p.anggota_id = a.id
LEFT JOIN detail_peminjaman dp ON dp.peminjaman_id = p.id
GROUP BY a.id, a.nama, a.kelas
ORDER BY jumlah_peminjaman DESC, a.nama;

-- laporan-buku-terpopuler.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: memeringkat buku berdasarkan total eksemplar yang dipinjam,
--   termasuk buku yang belum pernah dipinjam, memakai window function RANK().
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-buku-terpopuler.sql

SELECT b.kode_buku, b.judul,
       COALESCE(SUM(dp.jumlah), 0) AS total_dipinjam,
       RANK() OVER (ORDER BY COALESCE(SUM(dp.jumlah), 0) DESC) AS peringkat
FROM buku b
LEFT JOIN detail_peminjaman dp ON dp.buku_kode = b.kode_buku
GROUP BY b.kode_buku, b.judul
ORDER BY total_dipinjam DESC, b.judul;

-- laporan-terlambat.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menemukan peminjaman yang melewati batas tanggal_harus_kembali
--   dan belum dikembalikan, dihitung terhadap tanggal acuan literal.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-terlambat.sql
-- Ganti tanggal acuan '2026-02-28' sesuai hari laporan dijalankan.

SELECT p.id, a.nama, a.kelas, p.tanggal_pinjam, p.tanggal_harus_kembali,
       DATEDIFF('2026-02-28', p.tanggal_harus_kembali) AS hari_terlambat
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
  AND p.tanggal_harus_kembali < '2026-02-28'
ORDER BY hari_terlambat DESC;

-- stok-tersedia.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menampilkan stok tiap judul berdampingan dengan jumlah eksemplar
--   yang sedang dipinjam, sehingga kecukupan koleksi terlihat sekali baca.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/stok-tersedia.sql

SELECT b.kode_buku, b.judul, b.stok AS stok_tersedia,
       COALESCE(dipinjam.sedang, 0) AS sedang_dipinjam
FROM buku b
LEFT JOIN (
  SELECT dp.buku_kode, SUM(dp.jumlah) AS sedang
  FROM detail_peminjaman dp
  JOIN peminjaman p ON p.id = dp.peminjaman_id
  WHERE p.tanggal_kembali IS NULL
  GROUP BY dp.buku_kode
) dipinjam ON dipinjam.buku_kode = b.kode_buku
ORDER BY b.kode_buku;

-- pemeriksaan.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: pemeriksaan akhir seluruh fitur proyek (skema, peminjaman,
--   pengembalian, laporan terpopuler, dan deteksi keterlambatan).
--   Berkas ini MANDIRI: membangun skenario uji kecilnya sendiri,
--   lalu membersihkan database setelah selesai.
-- Cara pakai: mysql -u root -p < tests/pemeriksaan.sql
-- Ekspektasi tiap hasil ditulis sebagai komentar di atas query terkait.

DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);
CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);
CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);
CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2);
INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002');

-- Pemeriksaan 1: peminjaman mengurangi stok secara atomik.
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (1, '2026-01-05', '2026-01-12');
INSERT INTO detail_peminjaman VALUES (1, 'BK-001', 2);
UPDATE buku SET stok = stok - 2 WHERE kode_buku = 'BK-001';
COMMIT;
-- Ekspektasi: BK-001 stok 1
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-001';

-- Pemeriksaan 2: pengembalian mengembalikan stok dan menutup pinjaman.
UPDATE peminjaman SET tanggal_kembali = '2026-01-10' WHERE id = 1;
UPDATE buku SET stok = stok + 2 WHERE kode_buku = 'BK-001';
-- Ekspektasi: BK-001 stok 3
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-001';
-- Ekspektasi: peminjaman 1 sudah memiliki tanggal_kembali
SELECT id, tanggal_kembali FROM peminjaman WHERE id = 1;

-- Pemeriksaan 3: peminjaman kedua (id 2) selesai, peminjaman ketiga (id 3) terbuka.
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-01-15', '2026-01-22');
INSERT INTO detail_peminjaman VALUES (2, 'BK-002', 1), (2, 'BK-001', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-002';
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
UPDATE peminjaman SET tanggal_kembali = '2026-01-20' WHERE id = 2;
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-002';
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-001';
COMMIT;
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-02-01', '2026-02-08');
INSERT INTO detail_peminjaman VALUES (3, 'BK-001', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
COMMIT;
-- Ekspektasi: BK-001 total 4, BK-002 total 1, BK-003 total 0
SELECT b.kode_buku,
       COALESCE(SUM(dp.jumlah), 0) AS total_dipinjam
FROM buku b
LEFT JOIN detail_peminjaman dp ON dp.buku_kode = b.kode_buku
GROUP BY b.kode_buku
ORDER BY total_dipinjam DESC, b.kode_buku;

-- Pemeriksaan 4: hanya peminjaman 3 (harus kembali 2026-02-08) yang terlambat.
-- Ekspektasi: 1 baris - peminjaman 3, 20 hari terlambat
SELECT p.id, DATEDIFF('2026-02-28', p.tanggal_harus_kembali) AS hari_terlambat
FROM peminjaman p
WHERE p.tanggal_kembali IS NULL
  AND p.tanggal_harus_kembali < '2026-02-28'
ORDER BY p.id;

-- Pemeriksaan 5: stok akhir konsisten dengan seluruh mutasi.
-- Ekspektasi: BK-001 stok 2, BK-002 stok 2, BK-003 stok 2
SELECT kode_buku, stok FROM buku ORDER BY kode_buku;

DROP DATABASE perpustakaan_db;