Bagian 2: Pencatatan Stok

-- data-awal.sql — Proyek Database Inventaris Barang
-- Tujuan: mengisi data contoh deterministik (10 barang gudang fiktif)
--   sebagai titik awal konsisten untuk seluruh bagian proyek.
-- Cara pakai: jalankan setelah skema.sql:
--   mysql -u root -p inventaris_db < database/data-awal.sql

INSERT INTO barang (sku, nama, kategori, satuan, stok, stok_minimum, harga) VALUES
  ('AT-001', 'Pulpen Hitam Standard AE7', 'Alat Tulis', 'pcs', 120, 30, 3500.00),
  ('AT-002', 'Pensil 2B Nusantara', 'Alat Tulis', 'pcs', 85, 25, 2500.00),
  ('AT-003', 'Buku Tulis 38 Lembar Sekolah', 'Alat Tulis', 'buku', 40, 12, 4500.00),
  ('KR-001', 'Kertas HVS A4 80gsm', 'Kertas', 'rim', 25, 10, 55000.00),
  ('KR-002', 'Amplop Cokelat Ukuran F4', 'Kertas', 'pak', 18, 12, 15000.00),
  ('EL-001', 'Kalkulator Desktop CD-8', 'Elektronik', 'unit', 8, 3, 95000.00),
  ('EL-002', 'Baterai AA Isi 4', 'Elektronik', 'pack', 60, 20, 18000.00),
  ('KB-001', 'Sabun Cuci Tangan Lavender', 'Kebersihan', 'botol', 45, 15, 12000.00),
  ('KB-002', 'Tisu Wajah 250 Sheet', 'Kebersihan', 'pak', 10, 8, 13500.00),
  ('KP-001', 'Kantong Plastik Kresek M', 'Kemasan', 'pak', 200, 50, 5000.00);

-- skema.sql — Proyek Database Inventaris Barang
-- Tujuan: membuat database inventaris_db beserta kedua tabelnya
--   (barang, transaksi) lengkap dengan primary key, kolom sku yang unik,
--   foreign key bernama, dan check constraint stok/jumlah.
-- Cara pakai: mysql -u root -p < database/skema.sql

DROP DATABASE IF EXISTS inventaris_db;
CREATE DATABASE inventaris_db;
USE inventaris_db;

-- Master barang gudang; sku unik per produk, stok tidak boleh negatif.
CREATE TABLE barang (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(15) NOT NULL UNIQUE,
  nama VARCHAR(100) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  satuan VARCHAR(10) NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  stok_minimum INT NOT NULL DEFAULT 0,
  harga DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  CONSTRAINT chk_barang_stok CHECK (stok >= 0)
);

-- Buku mutasi stok; ENUM tipe membedakan arah gerak barang.
CREATE TABLE transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tipe ENUM('masuk','keluar') NOT NULL,
  barang_id INT NOT NULL,
  jumlah INT NOT NULL,
  tanggal DATE NOT NULL,
  keterangan VARCHAR(100) NOT NULL,
  CONSTRAINT chk_transaksi_jumlah CHECK (jumlah > 0),
  CONSTRAINT fk_transaksi_barang
    FOREIGN KEY (barang_id) REFERENCES barang(id)
);

-- barang-stok-minimum.sql — Proyek Database Inventaris Barang
-- Tujuan: menampilkan barang yang stoknya sudah menyentuh atau turun di
--   bawah stok_minimum beserta kekurangannya, sebagai dasar pemesanan ulang.
-- Cara pakai: mysql -u root -p inventaris_db < queries/barang-stok-minimum.sql

SELECT b.sku, b.nama, b.stok, b.stok_minimum,
       b.stok_minimum - b.stok AS kekurangan
FROM barang b
WHERE b.stok <= b.stok_minimum
ORDER BY kekurangan DESC, b.sku;

-- kartu-stok.sql — Proyek Database Inventaris Barang
-- Tujuan: menyusun kartu stok satu barang: riwayat transaksi berurutan
--   waktu beserta stok berjalan (running total) memakai window function
--   SUM() OVER dengan frame ROWS eksplisit.
-- Cara pakai: mysql -u root -p inventaris_db < queries/kartu-stok.sql

SELECT t.tanggal, t.tipe, t.jumlah, t.keterangan,
       SUM(CASE WHEN t.tipe = 'masuk' THEN t.jumlah ELSE -t.jumlah END)
         OVER (ORDER BY t.tanggal, t.id
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS stok_berjalan
FROM transaksi t
JOIN barang b ON b.id = t.barang_id
WHERE b.sku = 'AT-001'
ORDER BY t.tanggal, t.id;

-- nilai-persediaan.sql — Proyek Database Inventaris Barang
-- Tujuan: menghitung nilai persediaan per kategori (stok x harga),
--   diurutkan dari kategori dengan nilai terbesar.
-- Cara pakai: mysql -u root -p inventaris_db < queries/nilai-persediaan.sql

SELECT b.kategori,
       COUNT(*) AS jumlah_item,
       SUM(b.stok * b.harga) AS nilai_persediaan
FROM barang b
GROUP BY b.kategori
ORDER BY nilai_persediaan DESC;

-- ringkasan-masuk-keluar.sql — Proyek Database Inventaris Barang
-- Tujuan: merangkum total barang masuk dan keluar per kategori per bulan
--   dengan agregasi bersyarat (SUM + CASE) dan DATE_FORMAT.
-- Cara pakai: mysql -u root -p inventaris_db < queries/ringkasan-masuk-keluar.sql

SELECT b.kategori,
       DATE_FORMAT(t.tanggal, '%Y-%m') AS bulan,
       SUM(CASE WHEN t.tipe = 'masuk' THEN t.jumlah ELSE 0 END) AS total_masuk,
       SUM(CASE WHEN t.tipe = 'keluar' THEN t.jumlah ELSE 0 END) AS total_keluar
FROM transaksi t
JOIN barang b ON b.id = t.barang_id
WHERE t.tanggal BETWEEN '2026-01-01' AND '2026-02-28'
GROUP BY b.kategori, DATE_FORMAT(t.tanggal, '%Y-%m')
ORDER BY b.kategori, bulan;

-- pemeriksaan.sql — Proyek Database Inventaris Barang
-- Tujuan: pemeriksaan akhir seluruh fitur proyek (mutasi masuk-keluar,
--   konsistensi stok dengan buku mutasi, ringkasan bulanan, nilai
--   persediaan, batas stok minimum, dan kartu stok).
--   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 inventaris_db;
CREATE DATABASE inventaris_db;
USE inventaris_db;

CREATE TABLE barang (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(15) NOT NULL UNIQUE,
  nama VARCHAR(100) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  satuan VARCHAR(10) NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  stok_minimum INT NOT NULL DEFAULT 0,
  harga DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  CONSTRAINT chk_barang_stok CHECK (stok >= 0)
);
CREATE TABLE transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tipe ENUM('masuk','keluar') NOT NULL,
  barang_id INT NOT NULL,
  jumlah INT NOT NULL,
  tanggal DATE NOT NULL,
  keterangan VARCHAR(100) NOT NULL,
  CONSTRAINT chk_transaksi_jumlah CHECK (jumlah > 0),
  CONSTRAINT fk_transaksi_barang
    FOREIGN KEY (barang_id) REFERENCES barang(id)
);

INSERT INTO barang (sku, nama, kategori, satuan, stok, stok_minimum, harga) VALUES
  ('AT-001', 'Pulpen Hitam Standard AE7', 'Alat Tulis', 'pcs', 100, 30, 3500.00),
  ('KR-001', 'Kertas HVS A4 80gsm', 'Kertas', 'rim', 20, 10, 55000.00),
  ('KB-001', 'Sabun Cuci Tangan Lavender', 'Kebersihan', 'botol', 30, 15, 12000.00);

-- Baris saldo awal: stok data awal dibukukan sebagai transaksi masuk
-- sehingga seluruh mutasi bisa direkonstruksi dari tabel transaksi.
INSERT INTO transaksi (tipe, barang_id, jumlah, tanggal, keterangan) VALUES
  ('masuk', 1, 100, '2025-12-31', 'Saldo awal periode'),
  ('masuk', 2, 20, '2025-12-31', 'Saldo awal periode'),
  ('masuk', 3, 30, '2025-12-31', 'Saldo awal periode');

-- Pemeriksaan 1: barang masuk menambah stok secara atomik.
START TRANSACTION;
INSERT INTO transaksi (tipe, barang_id, jumlah, tanggal, keterangan)
  VALUES ('masuk', 1, 50, '2026-01-10', 'Pembelian PO-2601-01');
UPDATE barang SET stok = stok + 50 WHERE id = 1;
COMMIT;
-- Ekspektasi: AT-001 stok 150
SELECT sku, stok FROM barang WHERE id = 1;

-- Pemeriksaan 2: barang keluar mengurangi stok secara atomik.
START TRANSACTION;
INSERT INTO transaksi (tipe, barang_id, jumlah, tanggal, keterangan)
  VALUES ('keluar', 1, 30, '2026-01-15', 'Penjualan borongan');
UPDATE barang SET stok = stok - 30 WHERE id = 1;
COMMIT;
-- Ekspektasi: AT-001 stok 120
SELECT sku, stok FROM barang WHERE id = 1;

-- Pemeriksaan 3: mutasi barang lain (kertas turun ke batas minimum).
START TRANSACTION;
INSERT INTO transaksi (tipe, barang_id, jumlah, tanggal, keterangan)
  VALUES ('keluar', 2, 10, '2026-01-18', 'Pengiriman cabang Bandung');
UPDATE barang SET stok = stok - 10 WHERE id = 2;
COMMIT;
START TRANSACTION;
INSERT INTO transaksi (tipe, barang_id, jumlah, tanggal, keterangan)
  VALUES ('keluar', 3, 18, '2026-02-05', 'Pengiriman cabang Surabaya');
UPDATE barang SET stok = stok - 18 WHERE id = 3;
COMMIT;
-- Ekspektasi: KR-001 stok 10, KB-001 stok 12
SELECT sku, stok FROM barang ORDER BY sku;

-- Pemeriksaan 4: stok sama dengan saldo buku mutasi (konsistensi ledger).
-- Ekspektasi: 3 baris - stok dan saldo_buku sama: 120, 10, 12
SELECT b.sku, b.stok,
       COALESCE(SUM(CASE WHEN t.tipe = 'masuk' THEN t.jumlah ELSE -t.jumlah END), 0) AS saldo_buku
FROM barang b
LEFT JOIN transaksi t ON t.barang_id = b.id
GROUP BY b.id, b.sku, b.stok
ORDER BY b.sku;

-- Pemeriksaan 5: ringkasan masuk/keluar per kategori per bulan.
-- Ekspektasi: 3 baris - Alat Tulis 2026-01 (50, 30), Kertas 2026-01 (0, 10),
--   Kebersihan 2026-02 (0, 18)
SELECT b.kategori,
       DATE_FORMAT(t.tanggal, '%Y-%m') AS bulan,
       SUM(CASE WHEN t.tipe = 'masuk' THEN t.jumlah ELSE 0 END) AS total_masuk,
       SUM(CASE WHEN t.tipe = 'keluar' THEN t.jumlah ELSE 0 END) AS total_keluar
FROM transaksi t
JOIN barang b ON b.id = t.barang_id
WHERE t.tanggal BETWEEN '2026-01-01' AND '2026-02-28'
GROUP BY b.kategori, DATE_FORMAT(t.tanggal, '%Y-%m')
ORDER BY b.kategori, bulan;

-- Pemeriksaan 6: nilai persediaan total (stok x harga).
-- Ekspektasi: 1114000.00
SELECT SUM(stok * harga) AS nilai_persediaan FROM barang;

-- Pemeriksaan 7: barang yang menyentuh atau di bawah stok_minimum.
-- Ekspektasi: 2 baris - KB-001 (kekurangan 3), KR-001 (kekurangan 0)
SELECT sku, stok, stok_minimum, stok_minimum - stok AS kekurangan
FROM barang
WHERE stok <= stok_minimum
ORDER BY kekurangan DESC, sku;

-- Pemeriksaan 8: kartu stok AT-001; stok berjalan berakhir di angka stok.
-- Ekspektasi: 3 baris - 100, 150, 120
SELECT t.tanggal, t.tipe, t.jumlah,
       SUM(CASE WHEN t.tipe = 'masuk' THEN t.jumlah ELSE -t.jumlah END)
         OVER (ORDER BY t.tanggal, t.id
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS stok_berjalan
FROM transaksi t
JOIN barang b ON b.id = t.barang_id
WHERE b.sku = 'AT-001'
ORDER BY t.tanggal, t.id;

DROP DATABASE inventaris_db;