Bagian 1: Perancangan Skema

-- =============================================================
-- Nama berkas : data-awal.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Mengisi keuangan_db dengan data contoh deterministik
--               (3 akun, 9 kategori, 28 transaksi Januari-Maret 2026,
--               12 baris anggaran Januari-Februari 2026).
-- Cara pakai  : mysql -u root -p keuangan_db < database/data-awal.sql
--               (jalankan setelah database/skema.sql)
-- =============================================================

USE keuangan_db;

INSERT INTO akun (id, nama, jenis) VALUES
  (1, 'Dompet Tunai', 'tunai'),
  (2, 'Rekening Bank Sejahtera', 'rekening'),
  (3, 'Dompet Digital PayKuy', 'dompet digital');

INSERT INTO kategori (id, nama, tipe) VALUES
  (1, 'Gaji', 'masuk'),
  (2, 'Bonus', 'masuk'),
  (3, 'Makan', 'keluar'),
  (4, 'Transportasi', 'keluar'),
  (5, 'Belanja', 'keluar'),
  (6, 'Hiburan', 'keluar'),
  (7, 'Tagihan', 'keluar'),
  (8, 'Kesehatan', 'keluar'),
  (9, 'Pendidikan', 'keluar');

INSERT INTO transaksi (id, tanggal, tipe, kategori_id, akun_id, jumlah, keterangan) VALUES
  (1,  '2026-01-02', 'masuk',  1, 2, 8500000.00, 'Gaji bulan Desember'),
  (2,  '2026-01-03', 'keluar', 7, 2, 1200000.00, 'Sewa kos bulan Januari'),
  (3,  '2026-01-05', 'keluar', 3, 1, 58000.00, 'Makan siang di warung'),
  (4,  '2026-01-08', 'keluar', 3, 1, 62000.00, 'Belanja bahan makanan'),
  (5,  '2026-01-10', 'keluar', 4, 1, 18000.00, 'Ojek ke kantor'),
  (6,  '2026-01-12', 'keluar', 5, 1, 350000.00, 'Seragam kerja'),
  (7,  '2026-01-15', 'keluar', 3, 1, 95000.00, 'Makan malam keluarga'),
  (8,  '2026-01-18', 'keluar', 6, 1, 120000.00, 'Tiket bioskop'),
  (9,  '2026-01-22', 'keluar', 4, 1, 22000.00, 'Bus pulang kerja'),
  (10, '2026-01-25', 'keluar', 3, 1, 55000.00, 'Jajan kopi dan kue'),
  (11, '2026-01-28', 'keluar', 4, 3, 65000.00, 'Isi saldo transportasi online'),
  (12, '2026-02-02', 'masuk',  1, 2, 8500000.00, 'Gaji bulan Januari'),
  (13, '2026-02-03', 'keluar', 7, 2, 1200000.00, 'Sewa kos bulan Februari'),
  (14, '2026-02-05', 'keluar', 3, 1, 75000.00, 'Makan siang rapat tim'),
  (15, '2026-02-07', 'keluar', 5, 1, 420000.00, 'Belanja pakaian diskon'),
  (16, '2026-02-09', 'keluar', 3, 1, 88000.00, 'Makan malam ulang tahun teman'),
  (17, '2026-02-11', 'keluar', 4, 1, 20000.00, 'Ojek ke klien'),
  (18, '2026-02-13', 'keluar', 6, 1, 145000.00, 'Konser musik indie'),
  (19, '2026-02-15', 'masuk',  2, 2, 1500000.00, 'Bonus penyelesaian proyek'),
  (20, '2026-02-17', 'keluar', 3, 1, 68000.00, 'Makan siang di kafe'),
  (21, '2026-02-19', 'keluar', 6, 1, 60000.00, 'Langganan streaming bulanan'),
  (22, '2026-02-21', 'keluar', 8, 1, 180000.00, 'Konsultasi dokter gigi'),
  (23, '2026-02-26', 'keluar', 4, 1, 35000.00, 'Bensin motor'),
  (24, '2026-02-28', 'keluar', 6, 1, 95000.00, 'Nonton bola di stadion'),
  (25, '2026-03-01', 'masuk',  1, 2, 8500000.00, 'Gaji bulan Februari'),
  (26, '2026-03-02', 'keluar', 7, 2, 1200000.00, 'Sewa kos bulan Maret'),
  (27, '2026-03-04', 'keluar', 3, 1, 66000.00, 'Makan siang'),
  (28, '2026-03-06', 'keluar', 4, 1, 24000.00, 'Ojek ke kantor');

INSERT INTO anggaran (id, kategori_id, bulan, batas) VALUES
  (1,  3, '2026-01-01', 300000.00),
  (2,  4, '2026-01-01', 150000.00),
  (3,  5, '2026-01-01', 500000.00),
  (4,  6, '2026-01-01', 250000.00),
  (5,  7, '2026-01-01', 1200000.00),
  (6,  8, '2026-01-01', 200000.00),
  (7,  3, '2026-02-01', 300000.00),
  (8,  4, '2026-02-01', 150000.00),
  (9,  5, '2026-02-01', 500000.00),
  (10, 6, '2026-02-01', 250000.00),
  (11, 7, '2026-02-01', 1200000.00),
  (12, 8, '2026-02-01', 200000.00);

-- =============================================================
-- Nama berkas : skema.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Membangun ulang database keuangan_db beserta empat
--               tabel (akun, kategori, transaksi, anggaran) lengkap
--               dengan foreign key, constraint CHECK, dan UNIQUE.
-- Cara pakai  : mysql -u root -p < database/skema.sql
--               (lanjutkan dengan database/data-awal.sql)
-- =============================================================

DROP DATABASE IF EXISTS keuangan_db;
CREATE DATABASE keuangan_db;
USE keuangan_db;

CREATE TABLE akun (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(40) NOT NULL UNIQUE,
  jenis ENUM('tunai', 'rekening', 'dompet digital') NOT NULL
);

CREATE TABLE kategori (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(40) NOT NULL UNIQUE,
  tipe ENUM('masuk', 'keluar') NOT NULL
);

CREATE TABLE transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  tipe ENUM('masuk', 'keluar') NOT NULL,
  kategori_id INT NOT NULL,
  akun_id INT NOT NULL,
  jumlah DECIMAL(12, 2) NOT NULL,
  keterangan VARCHAR(100),
  CONSTRAINT fk_transaksi_kategori
    FOREIGN KEY (kategori_id) REFERENCES kategori (id),
  CONSTRAINT fk_transaksi_akun
    FOREIGN KEY (akun_id) REFERENCES akun (id),
  CONSTRAINT chk_transaksi_jumlah CHECK (jumlah > 0)
);

CREATE TABLE anggaran (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kategori_id INT NOT NULL,
  bulan DATE NOT NULL,
  batas DECIMAL(12, 2) NOT NULL,
  CONSTRAINT fk_anggaran_kategori
    FOREIGN KEY (kategori_id) REFERENCES kategori (id),
  CONSTRAINT chk_anggaran_batas CHECK (batas > 0),
  CONSTRAINT uq_anggaran_kategori_bulan UNIQUE (kategori_id, bulan)
);

-- =============================================================
-- Nama berkas : arus-kas-bulanan.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Merangkum arus kas per bulan — total masuk, total
--               keluar, dan selisih bersihnya.
-- Cara pakai  : mysql -u root -p keuangan_db < queries/arus-kas-bulanan.sql
-- =============================================================

USE keuangan_db;

SELECT
  DATE_FORMAT(tanggal, '%Y-%m') AS bulan,
  SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE 0 END) AS total_masuk,
  SUM(CASE WHEN tipe = 'keluar' THEN jumlah ELSE 0 END) AS total_keluar,
  SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE -jumlah END) AS arus_kas_bersih
FROM transaksi
GROUP BY bulan
ORDER BY bulan;

-- =============================================================
-- Nama berkas : cek-anggaran.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Menampilkan kategori keluar yang pemakaiannya sudah
--               melampaui batas anggaran pada bulan anggaran itu.
-- Cara pakai  : mysql -u root -p keuangan_db < queries/cek-anggaran.sql
-- =============================================================

USE keuangan_db;

SELECT
  DATE_FORMAT(a.bulan, '%Y-%m') AS periode,
  k.nama AS kategori,
  a.batas,
  SUM(t.jumlah) AS terpakai,
  SUM(t.jumlah) - a.batas AS kelebihan
FROM anggaran a
JOIN kategori k ON k.id = a.kategori_id
JOIN transaksi t
  ON t.kategori_id = a.kategori_id
 AND t.tipe = 'keluar'
 AND DATE_FORMAT(t.tanggal, '%Y-%m') = DATE_FORMAT(a.bulan, '%Y-%m')
GROUP BY a.id, periode, k.nama, a.batas
HAVING SUM(t.jumlah) > a.batas
ORDER BY periode, k.nama;

-- =============================================================
-- Nama berkas : pengeluaran-per-kategori.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Merangkum pengeluaran per kategori keluar beserta
--               jumlah transaksi dan persentase terhadap total.
-- Cara pakai  : mysql -u root -p keuangan_db < queries/pengeluaran-per-kategori.sql
-- =============================================================

USE keuangan_db;

SELECT
  k.nama AS kategori,
  COUNT(t.id) AS jumlah_transaksi,
  COALESCE(SUM(t.jumlah), 0) AS total_pengeluaran,
  ROUND(COALESCE(SUM(t.jumlah), 0)
        / (SELECT SUM(jumlah) FROM transaksi WHERE tipe = 'keluar') * 100, 1) AS persen
FROM kategori k
LEFT JOIN transaksi t
  ON t.kategori_id = k.id AND t.tipe = 'keluar'
WHERE k.tipe = 'keluar'
GROUP BY k.id, k.nama
ORDER BY total_pengeluaran DESC, k.nama;

-- =============================================================
-- Nama berkas : saldo-kumulatif.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Menghitung saldo berjalan dari seluruh transaksi —
--               pemasukan menambah, pengeluaran mengurangi.
-- Cara pakai  : mysql -u root -p keuangan_db < queries/saldo-kumulatif.sql
-- =============================================================

USE keuangan_db;

SELECT
  id,
  tanggal,
  keterangan,
  jumlah,
  SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE -jumlah END)
    OVER (ORDER BY tanggal, id ROWS UNBOUNDED PRECEDING) AS saldo_kumulatif
FROM transaksi
ORDER BY tanggal, id;

-- =============================================================
-- Nama berkas : pemeriksaan.sql
-- Proyek      : Database Keuangan Pribadi (Course MySQL)
-- Tujuan      : Uji akhir otomatis — memeriksa struktur, data awal,
--               deteksi anggaran terlampaui, arus kas bulanan, dan
--               konsistensi saldo kumulatif. Ekspektasi tiap query
--               dituliskan sebagai komentar di atasnya.
-- Cara pakai  : mysql -h 127.0.0.1 -P 3306 -u root < tests/pemeriksaan.sql
--               (jalankan setelah database/skema.sql dan data-awal.sql)
-- =============================================================

USE keuangan_db;

-- Pemeriksaan 1: struktur database.
-- Ekspektasi: jumlah_tabel = 4 (akun, kategori, transaksi, anggaran).
SELECT COUNT(*) AS jumlah_tabel
FROM information_schema.tables
WHERE table_schema = 'keuangan_db';

-- Pemeriksaan 2: data awal termuat lengkap.
-- Ekspektasi: akun = 3, kategori = 9, transaksi = 28, anggaran = 12.
SELECT
  (SELECT COUNT(*) FROM akun) AS akun,
  (SELECT COUNT(*) FROM kategori) AS kategori,
  (SELECT COUNT(*) FROM transaksi) AS transaksi,
  (SELECT COUNT(*) FROM anggaran) AS anggaran;

-- Pemeriksaan 3: deteksi anggaran terlampaui pada data awal.
-- Ekspektasi: 1 baris — periode 2026-02, kategori Hiburan,
-- batas 250000.00, terpakai 300000.00, kelebihan 50000.00.
SELECT
  DATE_FORMAT(a.bulan, '%Y-%m') AS periode,
  k.nama AS kategori,
  a.batas,
  SUM(t.jumlah) AS terpakai,
  SUM(t.jumlah) - a.batas AS kelebihan
FROM anggaran a
JOIN kategori k ON k.id = a.kategori_id
JOIN transaksi t
  ON t.kategori_id = a.kategori_id
 AND t.tipe = 'keluar'
 AND DATE_FORMAT(t.tanggal, '%Y-%m') = DATE_FORMAT(a.bulan, '%Y-%m')
GROUP BY a.id, periode, k.nama, a.batas
HAVING SUM(t.jumlah) > a.batas
ORDER BY periode, k.nama;

-- Pemeriksaan 4: arus kas bulanan lengkap tiga bulan.
-- Ekspektasi: 3 baris — 2026-01, 2026-02, 2026-03.
SELECT
  DATE_FORMAT(tanggal, '%Y-%m') AS bulan,
  SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE 0 END) AS total_masuk,
  SUM(CASE WHEN tipe = 'keluar' THEN jumlah ELSE 0 END) AS total_keluar,
  SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE -jumlah END) AS arus_kas_bersih
FROM transaksi
GROUP BY bulan
ORDER BY bulan;

-- Pemeriksaan 5: saldo akhir window function sama dengan total bersih.
-- Ekspektasi: total_bersih = saldo_terakhir = 21279000.00.
SELECT
  (SELECT SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE -jumlah END)
   FROM transaksi) AS total_bersih,
  (SELECT saldo FROM (
      SELECT id,
             SUM(CASE WHEN tipe = 'masuk' THEN jumlah ELSE -jumlah END)
               OVER (ORDER BY tanggal, id ROWS UNBOUNDED PRECEDING) AS saldo
      FROM transaksi
   ) running ORDER BY id DESC LIMIT 1) AS saldo_terakhir;

-- Pemeriksaan 6: kategori tanpa pengeluaran tetap tampil dengan nol.
-- Ekspektasi: 1 baris — kategori Pendidikan, 0 transaksi, 0.00, 0.0 persen.
SELECT
  k.nama AS kategori,
  COUNT(t.id) AS jumlah_transaksi,
  COALESCE(SUM(t.jumlah), 0) AS total_pengeluaran,
  ROUND(COALESCE(SUM(t.jumlah), 0)
        / (SELECT SUM(jumlah) FROM transaksi WHERE tipe = 'keluar') * 100, 1) AS persen
FROM kategori k
LEFT JOIN transaksi t
  ON t.kategori_id = k.id AND t.tipe = 'keluar'
WHERE k.tipe = 'keluar'
GROUP BY k.id, k.nama
HAVING COUNT(t.id) = 0
ORDER BY k.nama;