-- https://www.phpmyadmin.net/
--

START TRANSACTION;

--
-- Database: `sistem_informasi_bisnis`
--
CREATE DATABASE IF NOT EXISTS `sistem_informasi_bisnis` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `sistem_informasi_bisnis`;

-- --------------------------------------------------------

--
-- Table structure for table `kas_kecil`
--

CREATE TABLE `kas_kecil` (
  `id_kas` int(11) NOT NULL,
  `tanggal` date NOT NULL,
  `keterangan` text NOT NULL,
  `tipe` enum('masuk','keluar') NOT NULL,
  `jumlah` decimal(15,2) NOT NULL,
  `saldo` decimal(15,2) NOT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `master_pelanggan`
--

CREATE TABLE `master_pelanggan` (
  `id_pelanggan` int(11) NOT NULL,
  `kode_pelanggan` varchar(20) NOT NULL,
  `nama_pelanggan` varchar(100) NOT NULL,
  `alamat` text DEFAULT NULL,
  `telepon` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `master_pelanggan`
--

INSERT INTO `master_pelanggan` (`id_pelanggan`, `kode_pelanggan`, `nama_pelanggan`, `alamat`, `telepon`, `email`) VALUES
(1, 'PEL-001', 'Toko Maju Jaya', 'Jl. Gajah Mada No. 10', '081234567890', 'maju.jaya@example.com'),
(2, 'PEL-002', 'CV. Sinar Terang', 'Jl. Pahlawan No. 5', '081298765432', 'sinar.terang@example.com');

-- --------------------------------------------------------

--
-- Table structure for table `master_produk`
--

CREATE TABLE `master_produk` (
  `id_produk` int(11) NOT NULL,
  `kode_produk` varchar(20) NOT NULL,
  `nama_produk` varchar(100) NOT NULL,
  `type_produk` varchar(50) DEFAULT NULL,
  `merk_produk` varchar(50) DEFAULT NULL,
  `kategori_produk` varchar(50) DEFAULT NULL,
  `satuan` varchar(20) NOT NULL,
  `harga_beli` decimal(15,2) NOT NULL DEFAULT 0.00,
  `harga_jual` decimal(15,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `master_produk`
--

INSERT INTO `master_produk` (`id_produk`, `kode_produk`, `nama_produk`, `type_produk`, `merk_produk`, `kategori_produk`, `satuan`, `harga_beli`, `harga_jual`) VALUES
(1, 'PROD-001', 'Keramik Putih Polos 40x40', 'KW1', 'Milan', 'Keramik Lantai', 'kotak', 55000.00, 65000.00),
(2, 'PROD-002', 'Semen Tiga Roda 40kg', 'PCC', 'Tiga Roda', 'Bahan Bangunan', 'unit', 60000.00, 70000.00),
(3, 'PROD-003', 'Pipa PVC 4 inch', 'AW', 'Wavin', 'Pipa & Fitting', 'batang', 85000.00, 100000.00);

-- --------------------------------------------------------

--
-- Table structure for table `master_supplier`
--

CREATE TABLE `master_supplier` (
  `id_supplier` int(11) NOT NULL,
  `kode_supplier` varchar(20) NOT NULL,
  `nama_supplier` varchar(100) NOT NULL,
  `alamat` text DEFAULT NULL,
  `telepon` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `master_supplier`
--

INSERT INTO `master_supplier` (`id_supplier`, `kode_supplier`, `nama_supplier`, `alamat`, `telepon`, `email`) VALUES
(1, 'SUP-001', 'PT. Bangun Jaya Persada', 'Jl. Industri No. 12', '021-1234567', 'sales@bjp.com'),
(2, 'SUP-002', 'UD. Sumber Rejeki', 'Jl. Merdeka No. 100', '021-7654321', 'sumber.rejeki@example.com');

-- --------------------------------------------------------

--
-- Table structure for table `master_user`
--

CREATE TABLE `master_user` (
  `id_user` int(11) NOT NULL,
  `nama_lengkap` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','kasir','user') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `master_user`
--

INSERT INTO `master_user` (`id_user`, `nama_lengkap`, `username`, `password`, `role`) VALUES
(1, 'Administrator', 'admin', '$2y$10$T0dGmxLzT5N.oH1F/Z.A7uPqo.IeX3mU51/0Pz5vV0xL1u6qE9mYm', 'admin'), -- Password: admin889
(2, 'Kasir Utama', 'kasir', '$2y$10$wE9K2s.K2ZfCjV1qJ/JbPu.eK2d7A/bZ6M7rR9sO8gN/iU2qG3h.u', 'kasir'), -- Password: kasir123
(3, 'User Gudang', 'user', '$2y$10$8.XyE2Yh/jJ9qZ3wL6kHw.sN/tJ1fX3rD5gT6zC8oV7uI1pG/eO.e', 'user'); -- Password: user123

-- --------------------------------------------------------

--
-- Table structure for table `stok`
--

CREATE TABLE `stok` (
  `id_stok` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `jumlah_stok` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `stok`
--
DELIMITER $$
CREATE TRIGGER `after_insert_produk_add_stok` AFTER INSERT ON `master_produk` FOR EACH ROW BEGIN
    INSERT INTO stok (id_produk, jumlah_stok) VALUES (NEW.id_produk, 0);
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `stok_history`
--

CREATE TABLE `stok_history` (
  `id_history` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `tanggal` datetime NOT NULL,
  `tipe` enum('masuk','keluar','retur_beli','retur_jual') NOT NULL,
  `jumlah` int(11) NOT NULL,
  `stok_akhir` int(11) NOT NULL,
  `nomor_dokumen` varchar(50) DEFAULT NULL,
  `keterangan` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transaksi_pembelian`
--

CREATE TABLE `transaksi_pembelian` (
  `id_pembelian` int(11) NOT NULL,
  `no_faktur_beli` varchar(50) NOT NULL,
  `tgl_transaksi` date NOT NULL,
  `id_supplier` int(11) NOT NULL,
  `total_pembelian` decimal(15,2) NOT NULL,
  `status_pembayaran` enum('Lunas','Belum Lunas') NOT NULL,
  `keterangan` text DEFAULT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `transaksi_pembelian`
--
DELIMITER $$
CREATE TRIGGER `after_pembelian_delete` AFTER DELETE ON `transaksi_pembelian` FOR EACH ROW BEGIN
    -- Hapus utang terkait
    DELETE FROM utang WHERE no_referensi = OLD.no_faktur_beli;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_pembelian_insert_utang` AFTER INSERT ON `transaksi_pembelian` FOR EACH ROW BEGIN
    IF NEW.status_pembayaran = 'Belum Lunas' THEN
        INSERT INTO utang (id_supplier, no_referensi, tanggal_utang, jumlah_utang, sisa_utang, status)
        VALUES (NEW.id_supplier, NEW.no_faktur_beli, NEW.tgl_transaksi, NEW.total_pembelian, NEW.total_pembelian, 'Belum Lunas');
    END IF;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_pembelian_update` AFTER UPDATE ON `transaksi_pembelian` FOR EACH ROW BEGIN
    -- Jika faktur tidak ada di utang dan status menjadi belum lunas, insert
    IF NEW.status_pembayaran = 'Belum Lunas' AND (SELECT COUNT(*) FROM utang WHERE no_referensi = NEW.no_faktur_beli) = 0 THEN
        INSERT INTO utang (id_supplier, no_referensi, tanggal_utang, jumlah_utang, sisa_utang, status)
        VALUES (NEW.id_supplier, NEW.no_faktur_beli, NEW.tgl_transaksi, NEW.total_pembelian, NEW.total_pembelian, 'Belum Lunas');
    -- Jika status berubah menjadi Lunas, update utang
    ELSEIF NEW.status_pembayaran = 'Lunas' THEN
        UPDATE utang SET sisa_utang = 0, status = 'Lunas' WHERE no_referensi = NEW.no_faktur_beli;
    -- Jika status berubah menjadi Belum Lunas (dari lunas), update utang
    ELSEIF OLD.status_pembayaran = 'Lunas' AND NEW.status_pembayaran = 'Belum Lunas' THEN
         UPDATE utang SET sisa_utang = NEW.total_pembelian, status = 'Belum Lunas' WHERE no_referensi = NEW.no_faktur_beli;
    END IF;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `transaksi_pembelian_detail`
--

CREATE TABLE `transaksi_pembelian_detail` (
  `id_pembelian_detail` int(11) NOT NULL,
  `id_pembelian` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `jumlah_produk` int(11) NOT NULL,
  `satuan` varchar(20) NOT NULL,
  `harga_satuan_produk` decimal(15,2) NOT NULL,
  `harga_total_produk` decimal(15,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `transaksi_pembelian_detail`
--
DELIMITER $$
CREATE TRIGGER `after_pembelian_detail_delete` AFTER DELETE ON `transaksi_pembelian_detail` FOR EACH ROW BEGIN
    DECLARE current_stock INT;
    
    -- Update Master Produk Harga Beli
    UPDATE master_produk SET harga_beli = 0 WHERE id_produk = OLD.id_produk;
    
    -- Update Stok
    UPDATE stok SET jumlah_stok = jumlah_stok - OLD.jumlah_produk WHERE id_produk = OLD.id_produk;
    
    -- Insert Stok History
    SELECT jumlah_stok INTO current_stock FROM stok WHERE id_produk = OLD.id_produk;
    INSERT INTO stok_history (id_produk, tanggal, tipe, jumlah, stok_akhir, nomor_dokumen, keterangan)
    VALUES (OLD.id_produk, NOW(), 'masuk', -OLD.jumlah_produk, current_stock, (SELECT no_faktur_beli FROM transaksi_pembelian WHERE id_pembelian = OLD.id_pembelian), 'Hapus Detail Pembelian');
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_pembelian_detail_insert` AFTER INSERT ON `transaksi_pembelian_detail` FOR EACH ROW BEGIN
    DECLARE current_stock INT;

    -- Update Master Produk Harga Beli (Harga Modal)
    UPDATE master_produk SET harga_beli = NEW.harga_satuan_produk WHERE id_produk = NEW.id_produk;

    -- Update Stok
    UPDATE stok SET jumlah_stok = jumlah_stok + NEW.jumlah_produk WHERE id_produk = NEW.id_produk;

    -- Insert Stok History
    SELECT jumlah_stok INTO current_stock FROM stok WHERE id_produk = NEW.id_produk;
    INSERT INTO stok_history (id_produk, tanggal, tipe, jumlah, stok_akhir, nomor_dokumen, keterangan)
    VALUES (NEW.id_produk, NOW(), 'masuk', NEW.jumlah_produk, current_stock, (SELECT no_faktur_beli FROM transaksi_pembelian WHERE id_pembelian = NEW.id_pembelian), 'Pembelian');
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `transaksi_penjualan`
--

CREATE TABLE `transaksi_penjualan` (
  `id_penjualan` int(11) NOT NULL,
  `no_faktur_jual` varchar(50) NOT NULL,
  `tgl_transaksi` date NOT NULL,
  `id_pelanggan` int(11) NOT NULL,
  `total_penjualan` decimal(15,2) NOT NULL,
  `status_pembayaran` enum('Lunas','Belum Lunas') NOT NULL,
  `keterangan` text DEFAULT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `transaksi_penjualan`
--
DELIMITER $$
CREATE TRIGGER `after_penjualan_delete` AFTER DELETE ON `transaksi_penjualan` FOR EACH ROW BEGIN
    -- Hapus piutang terkait
    DELETE FROM piutang WHERE no_referensi = OLD.no_faktur_jual;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_penjualan_insert_piutang` AFTER INSERT ON `transaksi_penjualan` FOR EACH ROW BEGIN
    IF NEW.status_pembayaran = 'Belum Lunas' THEN
        INSERT INTO piutang (id_pelanggan, no_referensi, tanggal_piutang, jumlah_piutang, sisa_piutang, status)
        VALUES (NEW.id_pelanggan, NEW.no_faktur_jual, NEW.tgl_transaksi, NEW.total_penjualan, NEW.total_penjualan, 'Belum Lunas');
    END IF;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_penjualan_update` AFTER UPDATE ON `transaksi_penjualan` FOR EACH ROW BEGIN
    -- Jika faktur tidak ada di piutang dan status menjadi belum lunas, insert
    IF NEW.status_pembayaran = 'Belum Lunas' AND (SELECT COUNT(*) FROM piutang WHERE no_referensi = NEW.no_faktur_jual) = 0 THEN
        INSERT INTO piutang (id_pelanggan, no_referensi, tanggal_piutang, jumlah_piutang, sisa_piutang, status)
        VALUES (NEW.id_pelanggan, NEW.no_faktur_jual, NEW.tgl_transaksi, NEW.total_penjualan, NEW.total_penjualan, 'Belum Lunas');
    -- Jika status berubah menjadi Lunas, update piutang
    ELSEIF NEW.status_pembayaran = 'Lunas' THEN
        UPDATE piutang SET sisa_piutang = 0, status = 'Lunas' WHERE no_referensi = NEW.no_faktur_jual;
    -- Jika status berubah menjadi Belum Lunas (dari lunas), update piutang
    ELSEIF OLD.status_pembayaran = 'Lunas' AND NEW.status_pembayaran = 'Belum Lunas' THEN
         UPDATE piutang SET sisa_piutang = NEW.total_penjualan, status = 'Belum Lunas' WHERE no_referensi = NEW.no_faktur_jual;
    END IF;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `transaksi_penjualan_detail`
--

CREATE TABLE `transaksi_penjualan_detail` (
  `id_penjualan_detail` int(11) NOT NULL,
  `id_penjualan` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `jumlah_produk` int(11) NOT NULL,
  `satuan` varchar(20) NOT NULL,
  `harga_satuan_produk` decimal(15,2) NOT NULL,
  `harga_total_produk` decimal(15,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `transaksi_penjualan_detail`
--
DELIMITER $$
CREATE TRIGGER `after_penjualan_detail_delete` AFTER DELETE ON `transaksi_penjualan_detail` FOR EACH ROW BEGIN
    DECLARE current_stock INT;
    
    -- Update Stok
    UPDATE stok SET jumlah_stok = jumlah_stok + OLD.jumlah_produk WHERE id_produk = OLD.id_produk;
    
    -- Insert Stok History
    SELECT jumlah_stok INTO current_stock FROM stok WHERE id_produk = OLD.id_produk;
    INSERT INTO stok_history (id_produk, tanggal, tipe, jumlah, stok_akhir, nomor_dokumen, keterangan)
    VALUES (OLD.id_produk, NOW(), 'keluar', -OLD.jumlah_produk, current_stock, (SELECT no_faktur_jual FROM transaksi_penjualan WHERE id_penjualan = OLD.id_penjualan), 'Hapus Detail Penjualan');
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `after_penjualan_detail_insert` AFTER INSERT ON `transaksi_penjualan_detail` FOR EACH ROW BEGIN
    DECLARE current_stock INT;
    
    -- Update Stok
    UPDATE stok SET jumlah_stok = jumlah_stok - NEW.jumlah_produk WHERE id_produk = NEW.id_produk;
    
    -- Insert Stok History
    SELECT jumlah_stok INTO current_stock FROM stok WHERE id_produk = NEW.id_produk;
    INSERT INTO stok_history (id_produk, tanggal, tipe, jumlah, stok_akhir, nomor_dokumen, keterangan)
    VALUES (NEW.id_produk, NOW(), 'keluar', NEW.jumlah_produk, current_stock, (SELECT no_faktur_jual FROM transaksi_penjualan WHERE id_penjualan = NEW.id_penjualan), 'Penjualan');
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `transaksi_retur_pembelian`
--

CREATE TABLE `transaksi_retur_pembelian` (
  `id_retur_pembelian` int(11) NOT NULL,
  `no_retur_pembelian` varchar(50) NOT NULL,
  `tgl_retur` date NOT NULL,
  `id_pembelian` int(11) NOT NULL,
  `id_supplier` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `jumlah` int(11) NOT NULL,
  `keterangan` text DEFAULT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `transaksi_retur_pembelian`
--
DELIMITER $$
CREATE TRIGGER `after_retur_pembelian_insert` AFTER INSERT ON `transaksi_retur_pembelian` FOR EACH ROW BEGIN
    DECLARE current_stock INT;

    -- Mengurangi stok
    UPDATE stok SET jumlah_stok = jumlah_stok - NEW.jumlah WHERE id_produk = NEW.id_produk;

    -- Mencatat history stok
    SELECT jumlah_stok INTO current_stock FROM stok WHERE id_produk = NEW.id_produk;
    INSERT INTO stok_history (id_produk, tanggal, tipe, jumlah, stok_akhir, nomor_dokumen, keterangan)
    VALUES (NEW.id_produk, NOW(), 'retur_beli', NEW.jumlah, current_stock, NEW.no_retur_pembelian, 'Retur Pembelian');
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `transaksi_retur_penjualan`
--

CREATE TABLE `transaksi_retur_penjualan` (
  `id_retur_penjualan` int(11) NOT NULL,
  `no_retur_penjualan` varchar(50) NOT NULL,
  `tgl_retur` date NOT NULL,
  `id_penjualan` int(11) NOT NULL,
  `id_pelanggan` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `jumlah` int(11) NOT NULL,
  `keterangan` text DEFAULT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `transaksi_retur_penjualan`
--
DELIMITER $$
CREATE TRIGGER `after_retur_penjualan_insert` AFTER INSERT ON `transaksi_retur_penjualan` FOR EACH ROW BEGIN
    DECLARE current_stock INT;

    -- Menambah stok
    UPDATE stok SET jumlah_stok = jumlah_stok + NEW.jumlah WHERE id_produk = NEW.id_produk;

    -- Mencatat history stok
    SELECT jumlah_stok INTO current_stock FROM stok WHERE id_produk = NEW.id_produk;
    INSERT INTO stok_history (id_produk, tanggal, tipe, jumlah, stok_akhir, nomor_dokumen, keterangan)
    VALUES (NEW.id_produk, NOW(), 'retur_jual', NEW.jumlah, current_stock, NEW.no_retur_penjualan, 'Retur Penjualan');
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `utang`
--

CREATE TABLE `utang` (
  `id_utang` int(11) NOT NULL,
  `id_supplier` int(11) NOT NULL,
  `no_referensi` varchar(50) NOT NULL,
  `tanggal_utang` date NOT NULL,
  `jumlah_utang` decimal(15,2) NOT NULL,
  `sisa_utang` decimal(15,2) NOT NULL,
  `status` enum('Lunas','Belum Lunas') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `utang_pembayaran`
--

CREATE TABLE `utang_pembayaran` (
  `id_pembayaran_utang` int(11) NOT NULL,
  `id_utang` int(11) NOT NULL,
  `tanggal_bayar` date NOT NULL,
  `jumlah_bayar` decimal(15,2) NOT NULL,
  `keterangan` text DEFAULT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `utang_pembayaran`
--
DELIMITER $$
CREATE TRIGGER `after_pembayaran_utang_insert` AFTER INSERT ON `utang_pembayaran` FOR EACH ROW BEGIN
    DECLARE sisa DECIMAL(15,2);
    UPDATE utang SET sisa_utang = sisa_utang - NEW.jumlah_bayar WHERE id_utang = NEW.id_utang;
    
    SELECT sisa_utang INTO sisa FROM utang WHERE id_utang = NEW.id_utang;
    IF sisa <= 0 THEN
        UPDATE utang SET status = 'Lunas' WHERE id_utang = NEW.id_utang;
        UPDATE transaksi_pembelian SET status_pembayaran = 'Lunas' WHERE no_faktur_beli = (SELECT no_referensi FROM utang WHERE id_utang = NEW.id_utang);
    END IF;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `piutang`
--

CREATE TABLE `piutang` (
  `id_piutang` int(11) NOT NULL,
  `id_pelanggan` int(11) NOT NULL,
  `no_referensi` varchar(50) NOT NULL,
  `tanggal_piutang` date NOT NULL,
  `jumlah_piutang` decimal(15,2) NOT NULL,
  `sisa_piutang` decimal(15,2) NOT NULL,
  `status` enum('Lunas','Belum Lunas') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `piutang_pembayaran`
--

CREATE TABLE `piutang_pembayaran` (
  `id_pembayaran_piutang` int(11) NOT NULL,
  `id_piutang` int(11) NOT NULL,
  `tanggal_bayar` date NOT NULL,
  `jumlah_bayar` decimal(15,2) NOT NULL,
  `keterangan` text DEFAULT NULL,
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `piutang_pembayaran`
--
DELIMITER $$
CREATE TRIGGER `after_pembayaran_piutang_insert` AFTER INSERT ON `piutang_pembayaran` FOR EACH ROW BEGIN
    DECLARE sisa DECIMAL(15,2);
    UPDATE piutang SET sisa_piutang = sisa_piutang - NEW.jumlah_bayar WHERE id_piutang = NEW.id_piutang;
    
    SELECT sisa_piutang INTO sisa FROM piutang WHERE id_piutang = NEW.id_piutang;
    IF sisa <= 0 THEN
        UPDATE piutang SET status = 'Lunas' WHERE id_piutang = NEW.id_piutang;
        UPDATE transaksi_penjualan SET status_pembayaran = 'Lunas' WHERE no_faktur_jual = (SELECT no_referensi FROM piutang WHERE id_piutang = NEW.id_piutang);
    END IF;
END
$$
DELIMITER ;

--
-- Indexes for dumped tables
--

ALTER TABLE `kas_kecil`
  ADD PRIMARY KEY (`id_kas`),
  ADD KEY `fk_kas_user` (`id_user`);

ALTER TABLE `master_pelanggan`
  ADD PRIMARY KEY (`id_pelanggan`),
  ADD UNIQUE KEY `kode_pelanggan` (`kode_pelanggan`);

ALTER TABLE `master_produk`
  ADD PRIMARY KEY (`id_produk`),
  ADD UNIQUE KEY `kode_produk` (`kode_produk`);

ALTER TABLE `master_supplier`
  ADD PRIMARY KEY (`id_supplier`),
  ADD UNIQUE KEY `kode_supplier` (`kode_supplier`);

ALTER TABLE `master_user`
  ADD PRIMARY KEY (`id_user`),
  ADD UNIQUE KEY `username` (`username`);

ALTER TABLE `stok`
  ADD PRIMARY KEY (`id_stok`),
  ADD KEY `fk_stok_produk` (`id_produk`);

ALTER TABLE `stok_history`
  ADD PRIMARY KEY (`id_history`),
  ADD KEY `fk_history_produk` (`id_produk`);

ALTER TABLE `transaksi_pembelian`
  ADD PRIMARY KEY (`id_pembelian`),
  ADD UNIQUE KEY `no_faktur_beli` (`no_faktur_beli`),
  ADD KEY `fk_pembelian_supplier` (`id_supplier`),
  ADD KEY `fk_pembelian_user` (`id_user`);

ALTER TABLE `transaksi_pembelian_detail`
  ADD PRIMARY KEY (`id_pembelian_detail`),
  ADD KEY `fk_detail_pembelian` (`id_pembelian`),
  ADD KEY `fk_detail_pembelian_produk` (`id_produk`);

ALTER TABLE `transaksi_penjualan`
  ADD PRIMARY KEY (`id_penjualan`),
  ADD UNIQUE KEY `no_faktur_jual` (`no_faktur_jual`),
  ADD KEY `fk_penjualan_pelanggan` (`id_pelanggan`),
  ADD KEY `fk_penjualan_user` (`id_user`);

ALTER TABLE `transaksi_penjualan_detail`
  ADD PRIMARY KEY (`id_penjualan_detail`),
  ADD KEY `fk_detail_penjualan` (`id_penjualan`),
  ADD KEY `fk_detail_penjualan_produk` (`id_produk`);

ALTER TABLE `transaksi_retur_pembelian`
  ADD PRIMARY KEY (`id_retur_pembelian`),
  ADD UNIQUE KEY `no_retur_pembelian` (`no_retur_pembelian`),
  ADD KEY `fk_retur_pembelian_pembelian` (`id_pembelian`),
  ADD KEY `fk_retur_pembelian_supplier` (`id_supplier`),
  ADD KEY `fk_retur_pembelian_produk` (`id_produk`),
  ADD KEY `fk_retur_pembelian_user` (`id_user`);

ALTER TABLE `transaksi_retur_penjualan`
  ADD PRIMARY KEY (`id_retur_penjualan`),
  ADD UNIQUE KEY `no_retur_penjualan` (`no_retur_penjualan`),
  ADD KEY `fk_retur_penjualan_penjualan` (`id_penjualan`),
  ADD KEY `fk_retur_penjualan_pelanggan` (`id_pelanggan`),
  ADD KEY `fk_retur_penjualan_produk` (`id_produk`),
  ADD KEY `fk_retur_penjualan_user` (`id_user`);

ALTER TABLE `utang`
  ADD PRIMARY KEY (`id_utang`),
  ADD KEY `fk_utang_supplier` (`id_supplier`);

ALTER TABLE `utang_pembayaran`
  ADD PRIMARY KEY (`id_pembayaran_utang`),
  ADD KEY `fk_pembayaran_utang` (`id_utang`),
  ADD KEY `fk_pembayaran_utang_user` (`id_user`);
  
ALTER TABLE `piutang`
  ADD PRIMARY KEY (`id_piutang`),
  ADD KEY `fk_piutang_pelanggan` (`id_pelanggan`);

ALTER TABLE `piutang_pembayaran`
  ADD PRIMARY KEY (`id_pembayaran_piutang`),
  ADD KEY `fk_pembayaran_piutang` (`id_piutang`),
  ADD KEY `fk_pembayaran_piutang_user` (`id_user`);


--
-- AUTO_INCREMENT for dumped tables
--

ALTER TABLE `kas_kecil` MODIFY `id_kas` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `master_pelanggan` MODIFY `id_pelanggan` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `master_produk` MODIFY `id_produk` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `master_supplier` MODIFY `id_supplier` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `master_user` MODIFY `id_user` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `stok` MODIFY `id_stok` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `stok_history` MODIFY `id_history` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaksi_pembelian` MODIFY `id_pembelian` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaksi_pembelian_detail` MODIFY `id_pembelian_detail` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaksi_penjualan` MODIFY `id_penjualan` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaksi_penjualan_detail` MODIFY `id_penjualan_detail` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaksi_retur_pembelian` MODIFY `id_retur_pembelian` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `transaksi_retur_penjualan` MODIFY `id_retur_penjualan` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `utang` MODIFY `id_utang` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `utang_pembayaran` MODIFY `id_pembayaran_utang` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `piutang` MODIFY `id_piutang` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `piutang_pembayaran` MODIFY `id_pembayaran_piutang` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

ALTER TABLE `kas_kecil`
  ADD CONSTRAINT `fk_kas_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`) ON DELETE CASCADE;

ALTER TABLE `stok`
  ADD CONSTRAINT `fk_stok_produk` FOREIGN KEY (`id_produk`) REFERENCES `master_produk` (`id_produk`) ON DELETE CASCADE;

ALTER TABLE `stok_history`
  ADD CONSTRAINT `fk_history_produk` FOREIGN KEY (`id_produk`) REFERENCES `master_produk` (`id_produk`) ON DELETE CASCADE;

ALTER TABLE `transaksi_pembelian`
  ADD CONSTRAINT `fk_pembelian_supplier` FOREIGN KEY (`id_supplier`) REFERENCES `master_supplier` (`id_supplier`),
  ADD CONSTRAINT `fk_pembelian_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`);

ALTER TABLE `transaksi_pembelian_detail`
  ADD CONSTRAINT `fk_detail_pembelian` FOREIGN KEY (`id_pembelian`) REFERENCES `transaksi_pembelian` (`id_pembelian`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_detail_pembelian_produk` FOREIGN KEY (`id_produk`) REFERENCES `master_produk` (`id_produk`);

ALTER TABLE `transaksi_penjualan`
  ADD CONSTRAINT `fk_penjualan_pelanggan` FOREIGN KEY (`id_pelanggan`) REFERENCES `master_pelanggan` (`id_pelanggan`),
  ADD CONSTRAINT `fk_penjualan_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`);

ALTER TABLE `transaksi_penjualan_detail`
  ADD CONSTRAINT `fk_detail_penjualan` FOREIGN KEY (`id_penjualan`) REFERENCES `transaksi_penjualan` (`id_penjualan`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_detail_penjualan_produk` FOREIGN KEY (`id_produk`) REFERENCES `master_produk` (`id_produk`);

ALTER TABLE `transaksi_retur_pembelian`
  ADD CONSTRAINT `fk_retur_pembelian_pembelian` FOREIGN KEY (`id_pembelian`) REFERENCES `transaksi_pembelian` (`id_pembelian`),
  ADD CONSTRAINT `fk_retur_pembelian_produk` FOREIGN KEY (`id_produk`) REFERENCES `master_produk` (`id_produk`),
  ADD CONSTRAINT `fk_retur_pembelian_supplier` FOREIGN KEY (`id_supplier`) REFERENCES `master_supplier` (`id_supplier`),
  ADD CONSTRAINT `fk_retur_pembelian_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`);

ALTER TABLE `transaksi_retur_penjualan`
  ADD CONSTRAINT `fk_retur_penjualan_pelanggan` FOREIGN KEY (`id_pelanggan`) REFERENCES `master_pelanggan` (`id_pelanggan`),
  ADD CONSTRAINT `fk_retur_penjualan_penjualan` FOREIGN KEY (`id_penjualan`) REFERENCES `transaksi_penjualan` (`id_penjualan`),
  ADD CONSTRAINT `fk_retur_penjualan_produk` FOREIGN KEY (`id_produk`) REFERENCES `master_produk` (`id_produk`),
  ADD CONSTRAINT `fk_retur_penjualan_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`);

ALTER TABLE `utang`
  ADD CONSTRAINT `fk_utang_supplier` FOREIGN KEY (`id_supplier`) REFERENCES `master_supplier` (`id_supplier`) ON DELETE CASCADE;

ALTER TABLE `utang_pembayaran`
  ADD CONSTRAINT `fk_pembayaran_utang` FOREIGN KEY (`id_utang`) REFERENCES `utang` (`id_utang`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_pembayaran_utang_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`);

ALTER TABLE `piutang`
  ADD CONSTRAINT `fk_piutang_pelanggan` FOREIGN KEY (`id_pelanggan`) REFERENCES `master_pelanggan` (`id_pelanggan`) ON DELETE CASCADE;

ALTER TABLE `piutang_pembayaran`
  ADD CONSTRAINT `fk_pembayaran_piutang` FOREIGN KEY (`id_piutang`) REFERENCES `piutang` (`id_piutang`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_pembayaran_piutang_user` FOREIGN KEY (`id_user`) REFERENCES `master_user` (`id_user`);
COMMIT;
