72 lines
2.4 KiB
SQL
72 lines
2.4 KiB
SQL
-- =====================================================
|
|
-- Schema Laporan: bahan, stok_masuk, prediksi
|
|
-- Tujuan: mendukung fitur laporan realtime
|
|
-- =====================================================
|
|
|
|
-- 1) TABEL: bahan
|
|
-- Menyimpan stok bahan dengan stok minimum
|
|
CREATE TABLE IF NOT EXISTS bahan (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT NULL,
|
|
nama_bahan VARCHAR(100) NOT NULL,
|
|
stok DOUBLE NOT NULL DEFAULT 0,
|
|
stok_minimum DOUBLE NOT NULL DEFAULT 0,
|
|
unit VARCHAR(20) DEFAULT 'kg',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_bahan_product
|
|
FOREIGN KEY (product_id) REFERENCES products(id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE,
|
|
KEY idx_bahan_nama (nama_bahan),
|
|
KEY idx_bahan_product (product_id)
|
|
);
|
|
|
|
-- 2) TABEL: stok_masuk
|
|
-- Riwayat stok masuk bahan
|
|
CREATE TABLE IF NOT EXISTS stok_masuk (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
bahan_id INT NULL,
|
|
product_id INT NULL,
|
|
tanggal DATE NOT NULL,
|
|
jumlah DOUBLE NOT NULL DEFAULT 0,
|
|
unit VARCHAR(20) DEFAULT 'kg',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_stok_masuk_bahan
|
|
FOREIGN KEY (bahan_id) REFERENCES bahan(id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE,
|
|
CONSTRAINT fk_stok_masuk_product
|
|
FOREIGN KEY (product_id) REFERENCES products(id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE,
|
|
KEY idx_stok_masuk_tanggal (tanggal),
|
|
KEY idx_stok_masuk_bahan (bahan_id),
|
|
KEY idx_stok_masuk_product (product_id)
|
|
);
|
|
|
|
-- 3) TABEL: prediksi
|
|
-- Menyimpan hasil prediksi permintaan
|
|
CREATE TABLE IF NOT EXISTS prediksi (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT NULL,
|
|
nama_produk VARCHAR(100) NOT NULL,
|
|
hasil_prediksi DOUBLE NOT NULL DEFAULT 0,
|
|
estimasi_kebutuhan_bahan TEXT,
|
|
tanggal_prediksi DATE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_prediksi_product
|
|
FOREIGN KEY (product_id) REFERENCES products(id)
|
|
ON DELETE SET NULL
|
|
ON UPDATE CASCADE,
|
|
KEY idx_prediksi_tanggal (tanggal_prediksi),
|
|
KEY idx_prediksi_product (product_id)
|
|
);
|
|
|
|
-- =====================================================
|
|
-- Catatan:
|
|
-- - Jika tabel products sudah menyimpan stok (current_stock, min_stock),
|
|
-- endpoint laporan akan otomatis fallback ke tabel products.
|
|
-- - Pastikan data bahan/produk sinkron agar laporan tampil konsisten.
|
|
-- =====================================================
|