Bagian 4: Analisis Penjualan

-- data-awal.sql — Proyek Database Penjualan
-- Tujuan: mengisi data contoh deterministik (10 produk dan 8 pelanggan
--   fiktif) sebagai titik awal konsisten untuk seluruh bagian proyek.
-- Cara pakai: jalankan setelah skema.sql:
--   mysql -u root -p penjualan_db < database/data-awal.sql

INSERT INTO pelanggan (nama, email, kota) VALUES
  ('Budi Santoso', '[email protected]', 'Bandung'),
  ('Siti Rahmawati', '[email protected]', 'Jakarta'),
  ('Andi Wijaya', '[email protected]', 'Surabaya'),
  ('Dewi Lestari', '[email protected]', 'Yogyakarta'),
  ('Rina Marlina', '[email protected]', 'Bandung'),
  ('Tono Sutrisno', '[email protected]', 'Medan'),
  ('Sari Puspita', '[email protected]', 'Semarang'),
  ('Joko Prasetyo', '[email protected]', 'Denpasar');

INSERT INTO produk (sku, nama, kategori, harga, stok) VALUES
  ('TS-001', 'Kaos Polos Katun Combed 30s', 'Pakaian', 45000.00, 50),
  ('TS-002', 'Kemeja Flanel Lengan Panjang', 'Pakaian', 95000.00, 30),
  ('JA-001', 'Jaket Hoodie Zipper Unisex', 'Pakaian', 125000.00, 20),
  ('CP-001', 'Celana Chino Slim Fit', 'Pakaian', 85000.00, 35),
  ('SN-001', 'Sneakers Kanvas Klasik', 'Sepatu', 120000.00, 25),
  ('SN-002', 'Sandal Gunung Dewasa', 'Sepatu', 65000.00, 40),
  ('TA-001', 'Tas Ransel Laptop 15.6 inch', 'Tas', 145000.00, 15),
  ('TA-002', 'Totebag Kanvas Belajar', 'Tas', 35000.00, 60),
  ('AK-001', 'Topi Baseball Hitam', 'Aksesoris', 28000.00, 45),
  ('AK-002', 'Ikat Pinggang Kulit Sintetis', 'Aksesoris', 55000.00, 25);

-- skema.sql — Proyek Database Penjualan
-- Tujuan: membuat database penjualan_db beserta keempat tabelnya
--   (pelanggan, produk, pesanan, detail_pesanan) lengkap dengan primary key,
--   kolom unik email/sku, foreign key bernama, ENUM status, PK komposit,
--   dan check constraint stok/jumlah.
-- Cara pakai: mysql -u root -p < database/skema.sql

DROP DATABASE IF EXISTS penjualan_db;
CREATE DATABASE penjualan_db;
USE penjualan_db;

-- Pelanggan toko online; email unik sebagai identitas kontak.
CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  email VARCHAR(80) NOT NULL UNIQUE,
  kota VARCHAR(30) NOT NULL
);

-- Katalog produk; harga DECIMAL agar nilai rupiah pasti.
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(15) NOT NULL UNIQUE,
  nama VARCHAR(100) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_produk_stok CHECK (stok >= 0)
);

-- Satu baris per pesanan; status mengikuti daur hidup pesanan.
CREATE TABLE pesanan (
  no_pesanan INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan_id INT NOT NULL,
  tanggal DATE NOT NULL,
  status ENUM('baru','dibayar','dikirim','selesai','dibatalkan') NOT NULL DEFAULT 'baru',
  CONSTRAINT fk_pesanan_pelanggan
    FOREIGN KEY (pelanggan_id) REFERENCES pelanggan(id)
);

-- Rincian produk per pesanan; harga_satuan adalah snapshot harga saat
-- pesanan dibuat, bukan harga terbaru di tabel produk.
CREATE TABLE detail_pesanan (
  pesanan_id INT NOT NULL,
  produk_id INT NOT NULL,
  jumlah INT NOT NULL,
  harga_satuan DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id),
  CONSTRAINT chk_detail_jumlah CHECK (jumlah > 0),
  CONSTRAINT fk_detail_pesanan
    FOREIGN KEY (pesanan_id) REFERENCES pesanan(no_pesanan),
  CONSTRAINT fk_detail_produk
    FOREIGN KEY (produk_id) REFERENCES produk(id)
);

-- distribusi-status.sql — Proyek Database Penjualan
-- Tujuan: menghitung banyaknya pesanan pada tiap status daur hidup
--   (baru, dibayar, dikirim, selesai, dibatalkan).
-- Cara pakai: mysql -u root -p penjualan_db < queries/distribusi-status.sql

SELECT status, COUNT(*) AS jumlah_pesanan
FROM pesanan
GROUP BY status
ORDER BY status;

-- omzet-per-bulan.sql — Proyek Database Penjualan
-- Tujuan: menjumlahkan omzet per bulan dari rincian pesanan yang statusnya
--   sudah pasti menghasilkan uang (dibayar, dikirim, selesai) memakai
--   DATE_FORMAT atas tanggal literal dan harga_satuan snapshot.
-- Cara pakai: mysql -u root -p penjualan_db < queries/omzet-per-bulan.sql

SELECT DATE_FORMAT(p.tanggal, '%Y-%m') AS bulan,
       SUM(d.jumlah * d.harga_satuan) AS omzet
FROM pesanan p
JOIN detail_pesanan d ON d.pesanan_id = p.no_pesanan
WHERE p.status IN ('dibayar','dikirim','selesai')
GROUP BY DATE_FORMAT(p.tanggal, '%Y-%m')
ORDER BY bulan;

-- pelanggan-teratas.sql — Proyek Database Penjualan
-- Tujuan: menampilkan pelanggan berdasarkan jumlah pesanan aktif dan total
--   belanja (jumlah x harga_satuan snapshot); pesanan dibatalkan dikecualikan.
--   Pelanggan tanpa transaksi tetap tampil dengan nilai nol.
-- Cara pakai: mysql -u root -p penjualan_db < queries/pelanggan-teratas.sql

SELECT pl.nama, pl.kota,
       COUNT(DISTINCT p.no_pesanan) AS jumlah_pesanan,
       COALESCE(SUM(d.jumlah * d.harga_satuan), 0) AS total_belanja
FROM pelanggan pl
LEFT JOIN pesanan p ON p.pelanggan_id = pl.id AND p.status <> 'dibatalkan'
LEFT JOIN detail_pesanan d ON d.pesanan_id = p.no_pesanan
GROUP BY pl.id, pl.nama, pl.kota
ORDER BY total_belanja DESC, pl.nama;

-- produk-terlaris.sql — Proyek Database Penjualan
-- Tujuan: memeringkat produk berdasarkan total unit terjual (pesanan aktif,
--   termasuk yang berstatus baru) memakai window function RANK().
--   Produk tanpa penjualan tetap tampil dengan angka nol.
-- Cara pakai: mysql -u root -p penjualan_db < queries/produk-terlaris.sql

SELECT p.sku, p.nama,
       COALESCE(SUM(dj.jumlah), 0) AS total_terjual,
       RANK() OVER (ORDER BY COALESCE(SUM(dj.jumlah), 0) DESC) AS peringkat
FROM produk p
LEFT JOIN (
  SELECT d.produk_id, d.jumlah
  FROM detail_pesanan d
  JOIN pesanan pe ON pe.no_pesanan = d.pesanan_id
  WHERE pe.status <> 'dibatalkan'
) dj ON dj.produk_id = p.id
GROUP BY p.id, p.sku, p.nama
ORDER BY total_terjual DESC, p.sku;

-- pemeriksaan.sql — Proyek Database Penjualan
-- Tujuan: pemeriksaan akhir seluruh fitur proyek (pembuatan pesanan atomik,
--   penghitungan total, perubahan status, pembatalan yang memulihkan stok,
--   omzet per bulan, produk terlaris, dan distribusi status).
--   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 penjualan_db;
CREATE DATABASE penjualan_db;
USE penjualan_db;

CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  email VARCHAR(80) NOT NULL UNIQUE,
  kota VARCHAR(30) NOT NULL
);
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(15) NOT NULL UNIQUE,
  nama VARCHAR(100) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_produk_stok CHECK (stok >= 0)
);
CREATE TABLE pesanan (
  no_pesanan INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan_id INT NOT NULL,
  tanggal DATE NOT NULL,
  status ENUM('baru','dibayar','dikirim','selesai','dibatalkan') NOT NULL DEFAULT 'baru',
  CONSTRAINT fk_pesanan_pelanggan
    FOREIGN KEY (pelanggan_id) REFERENCES pelanggan(id)
);
CREATE TABLE detail_pesanan (
  pesanan_id INT NOT NULL,
  produk_id INT NOT NULL,
  jumlah INT NOT NULL,
  harga_satuan DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id),
  CONSTRAINT chk_detail_jumlah CHECK (jumlah > 0),
  CONSTRAINT fk_detail_pesanan
    FOREIGN KEY (pesanan_id) REFERENCES pesanan(no_pesanan),
  CONSTRAINT fk_detail_produk
    FOREIGN KEY (produk_id) REFERENCES produk(id)
);

INSERT INTO pelanggan (nama, email, kota) VALUES
  ('Budi Santoso', '[email protected]', 'Bandung'),
  ('Siti Rahmawati', '[email protected]', 'Jakarta'),
  ('Andi Wijaya', '[email protected]', 'Surabaya');
INSERT INTO produk (sku, nama, kategori, harga, stok) VALUES
  ('TS-001', 'Kaos Polos Katun Combed 30s', 'Pakaian', 45000.00, 20),
  ('TA-002', 'Totebag Kanvas Belajar', 'Tas', 35000.00, 15),
  ('AK-001', 'Topi Baseball Hitam', 'Aksesoris', 28000.00, 10);

-- Pemeriksaan 1: pesanan dibuat atomik - rincian masuk, stok berkurang.
START TRANSACTION;
INSERT INTO pesanan (pelanggan_id, tanggal, status)
  VALUES (2, '2026-01-10', 'baru');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan) VALUES
  (LAST_INSERT_ID(), 1, 2, 45000.00),
  (LAST_INSERT_ID(), 3, 1, 28000.00);
UPDATE produk SET stok = stok - 2 WHERE id = 1;
UPDATE produk SET stok = stok - 1 WHERE id = 3;
COMMIT;
-- Ekspektasi: TS-001 stok 18, AK-001 stok 9
SELECT sku, stok FROM produk WHERE id IN (1, 3) ORDER BY id;

-- Pemeriksaan 2: total pesanan dihitung dari rincian (SUM jumlah x harga).
-- Ekspektasi: pesanan 1 total 118000.00
SELECT d.pesanan_id, SUM(d.jumlah * d.harga_satuan) AS total
FROM detail_pesanan d
GROUP BY d.pesanan_id
ORDER BY d.pesanan_id;

-- Pemeriksaan 3: status pesanan berubah dari baru menjadi dibayar.
UPDATE pesanan SET status = 'dibayar' WHERE no_pesanan = 1 AND status = 'baru';
-- Ekspektasi: pesanan 1 status dibayar
SELECT no_pesanan, status FROM pesanan WHERE no_pesanan = 1;

-- Pemeriksaan 4: pembatalan pesanan mengembalikan status dan stok.
START TRANSACTION;
INSERT INTO pesanan (pelanggan_id, tanggal, status)
  VALUES (3, '2026-01-15', 'baru');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan)
  VALUES (2, 2, 1, 35000.00);
UPDATE produk SET stok = stok - 1 WHERE id = 2;
COMMIT;
START TRANSACTION;
UPDATE pesanan SET status = 'dibatalkan' WHERE no_pesanan = 2 AND status = 'baru';
UPDATE produk SET stok = stok + 1 WHERE id = 2;
COMMIT;
-- Ekspektasi: pesanan 2 dibatalkan, TA-002 stok pulih ke 15
SELECT no_pesanan, status FROM pesanan WHERE no_pesanan = 2;
SELECT sku, stok FROM produk WHERE id = 2;

-- Pemeriksaan 5: pesanan ketiga (Budi) selesai di bulan Februari.
START TRANSACTION;
INSERT INTO pesanan (pelanggan_id, tanggal, status)
  VALUES (1, '2026-02-05', 'baru');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan)
  VALUES (3, 1, 1, 45000.00);
UPDATE produk SET stok = stok - 1 WHERE id = 1;
COMMIT;
UPDATE pesanan SET status = 'selesai' WHERE no_pesanan = 3 AND status = 'baru';
-- Ekspektasi: pesanan 3 selesai
SELECT no_pesanan, status FROM pesanan WHERE no_pesanan = 3;

-- Pemeriksaan 6: omzet per bulan hanya dari pesanan yang menghasilkan uang.
-- Ekspektasi: 2026-01 omzet 118000.00 (pesanan 2 dibatalkan dikecualikan),
--   2026-02 omzet 45000.00
SELECT DATE_FORMAT(p.tanggal, '%Y-%m') AS bulan,
       SUM(d.jumlah * d.harga_satuan) AS omzet
FROM pesanan p
JOIN detail_pesanan d ON d.pesanan_id = p.no_pesanan
WHERE p.status IN ('dibayar','dikirim','selesai')
GROUP BY DATE_FORMAT(p.tanggal, '%Y-%m')
ORDER BY bulan;

-- Pemeriksaan 7: produk terlaris dengan RANK(); pesanan dibatalkan dikecualikan.
-- Ekspektasi: TS-001 3 unit peringkat 1, AK-001 1 unit peringkat 2,
--   TA-002 0 unit peringkat 3
SELECT p.sku,
       COALESCE(SUM(dj.jumlah), 0) AS total_terjual,
       RANK() OVER (ORDER BY COALESCE(SUM(dj.jumlah), 0) DESC) AS peringkat
FROM produk p
LEFT JOIN (
  SELECT d.produk_id, d.jumlah
  FROM detail_pesanan d
  JOIN pesanan pe ON pe.no_pesanan = d.pesanan_id
  WHERE pe.status <> 'dibatalkan'
) dj ON dj.produk_id = p.id
GROUP BY p.id, p.sku
ORDER BY total_terjual DESC, p.sku;

-- Pemeriksaan 8: distribusi status seluruh pesanan.
-- Ekspektasi: dibatalkan 1, dibayar 1, selesai 1
SELECT status, COUNT(*) AS jumlah_pesanan
FROM pesanan
GROUP BY status
ORDER BY status;

DROP DATABASE penjualan_db;