CREATE DATABASE IF NOT EXISTS nama_database;
USE nama_database;

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) UNIQUE NOT NULL,
 password VARCHAR(255) NOT NULL,
 nama VARCHAR(100) NOT NULL,
 role VARCHAR(30) DEFAULT 'admin',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- password: admin123
INSERT INTO users(username,password,nama,role) VALUES
('admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC2vH5L7kz2Q5Q9w7QmO','Administrator','admin');

CREATE TABLE products (
 id INT AUTO_INCREMENT PRIMARY KEY,
 kode VARCHAR(50) UNIQUE NOT NULL,
 nama VARCHAR(150) NOT NULL,
 harga_modal DECIMAL(15,2) DEFAULT 0,
 margin DECIMAL(8,2) DEFAULT 0,
 harga_jual DECIMAL(15,2) DEFAULT 0,
 stock INT DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stores (
 id INT AUTO_INCREMENT PRIMARY KEY,
 kode VARCHAR(50) UNIQUE NOT NULL,
 nama_toko VARCHAR(150) NOT NULL,
 alamat TEXT,
 telepon VARCHAR(50),
 termin_hari INT DEFAULT 30,
 limit_kredit DECIMAL(15,2) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stock_movements (
 id INT AUTO_INCREMENT PRIMARY KEY,
 product_id INT NOT NULL,
 jenis ENUM('Masuk','Keluar') NOT NULL,
 qty INT NOT NULL,
 keterangan VARCHAR(255),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 invoice VARCHAR(50) UNIQUE NOT NULL,
 store_id INT NOT NULL,
 total DECIMAL(15,2) NOT NULL,
 status_pengiriman VARCHAR(30) DEFAULT 'Belum Dikirim',
 tanggal DATE NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(store_id) REFERENCES stores(id) ON DELETE RESTRICT
);

CREATE TABLE receivables (
 id INT AUTO_INCREMENT PRIMARY KEY,
 order_id INT NOT NULL,
 store_id INT NOT NULL,
 invoice VARCHAR(50) NOT NULL,
 total DECIMAL(15,2) NOT NULL,
 dibayar DECIMAL(15,2) DEFAULT 0,
 sisa DECIMAL(15,2) NOT NULL,
 jatuh_tempo DATE NOT NULL,
 status VARCHAR(30) DEFAULT 'Belum Lunas',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
 FOREIGN KEY(store_id) REFERENCES stores(id) ON DELETE RESTRICT
);

CREATE TABLE payments (
 id INT AUTO_INCREMENT PRIMARY KEY,
 receivable_id INT NOT NULL,
 nominal DECIMAL(15,2) NOT NULL,
 tanggal DATE NOT NULL,
 keterangan VARCHAR(255),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(receivable_id) REFERENCES receivables(id) ON DELETE CASCADE
);