-- Database Structure untuk POSM KCH METRO
CREATE DATABASE IF NOT EXISTS posm_kch_metro;
USE posm_kch_metro;

-- Tabel Users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    role ENUM('admin', 'spv', 'staff', 'sales') NOT NULL,
    jabatan VARCHAR(100),
    no_hp VARCHAR(20)
);

-- Tabel Master Barang
CREATE TABLE master_barang (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode_barang VARCHAR(50) UNIQUE NOT NULL,
    nama_barang VARCHAR(150) NOT NULL,
    brand VARCHAR(100),
    kategori VARCHAR(100),
    satuan VARCHAR(50),
    stok INT DEFAULT 0,
    stok_minimum INT DEFAULT 0,
    lokasi VARCHAR(100)
);

-- Tabel Transaksi (Barang Masuk / Keluar / Permintaan)
CREATE TABLE transaksi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tanggal DATETIME DEFAULT CURRENT_TIMESTAMP,
    id_barang INT,
    jenis_transaksi ENUM('in', 'out') NOT NULL,
    qty INT NOT NULL,
    keterangan TEXT,
    id_user INT,
    outlet_tujuan VARCHAR(150),
    status ENUM('pending_staff', 'pending_spv', 'approved', 'rejected') DEFAULT 'pending_spv',
    catatan_reject TEXT,
    FOREIGN KEY (id_barang) REFERENCES master_barang(id),
    FOREIGN KEY (id_user) REFERENCES users(id)
);

-- Insert Default Admin & Roles
INSERT INTO users (username, password, nama_lengkap, role, jabatan) VALUES 
('admin', '123', 'Administrator', 'admin', 'IT Manager'),
('spv', '123', 'Supervisor', 'spv', 'SPV Gudang'),
('staff', '123', 'Staff Gudang', 'staff', 'Staff Logistik'),
('sales', '123', 'Sales Lapangan', 'sales', 'Sales Representative');