-- Buat database jika belum ada (opsional)
CREATE DATABASE IF NOT EXISTS db_penjualan_sabun;
USE db_penjualan_sabun;

-- 1. Tabel Users (Pengguna Aplikasi & Hak Akses)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'sales', 'gudang', 'akuntan') NOT NULL DEFAULT 'sales',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Tambahkan user default (Password: admin123)
INSERT INTO users (username, password_hash, full_name, role) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator', 'admin')
ON DUPLICATE KEY UPDATE id=id;

-- 2. Tabel Customers (Data Pelanggan / Toko / Distributor)
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(30) NOT NULL UNIQUE,
    customer_name VARCHAR(150) NOT NULL,
    contact_person VARCHAR(100),
    phone VARCHAR(25),
    address TEXT,
    credit_limit DECIMAL(15, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Tabel Products (Data Produk Sabun Cair & Atribut Satuan)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(30) NOT NULL UNIQUE,
    product_name VARCHAR(150) NOT NULL,
    unit VARCHAR(20) NOT NULL DEFAULT 'Botol', -- Contoh: Botol, Jerigen, Liter
    selling_price DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Tabel Chart of Accounts / COA (Daftar Akun Akuntansi Standar)
CREATE TABLE IF NOT EXISTS chart_of_accounts (
    account_code VARCHAR(20) PRIMARY KEY, -- Contoh: '1100', '4100'
    account_name VARCHAR(150) NOT NULL,
    account_type ENUM('Asset', 'Liability', 'Equity', 'Revenue', 'Expense') NOT NULL,
    normal_balance ENUM('Debit', 'Credit') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO chart_of_accounts (account_code, account_name, account_type, normal_balance) VALUES
('1100', 'Kas dan Setara Kas', 'Asset', 'Debit'),
('1120', 'Piutang Usaha', 'Asset', 'Debit'),
('1130', 'Persediaan Barang (Sabun Cair)', 'Asset', 'Debit'),
('2100', 'Utang Usaha', 'Liability', 'Credit'),
('3100', 'Modal Disetor', 'Equity', 'Credit'),
('4100', 'Pendapatan Penjualan Sabun', 'Revenue', 'Credit'),
('5100', 'Harga Pokok Penjualan (HPP)', 'Expense', 'Debit'),
('6100', 'Beban Operasional Umum', 'Expense', 'Debit');



-- 5. Tabel Sales Orders (Header SO)
CREATE TABLE IF NOT EXISTS sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    so_number VARCHAR(30) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status ENUM('Draft', 'Approved', 'Processed', 'Completed', 'Cancelled') NOT NULL DEFAULT 'Draft',
    total_amount DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_so_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Tabel Sales Order Details (Detail Item SO)
CREATE TABLE IF NOT EXISTS sales_order_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    so_id INT NOT NULL,
    product_id INT NOT NULL,
    qty_ordered INT NOT NULL,
    unit_price DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    subtotal DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_sod_so FOREIGN KEY (so_id) REFERENCES sales_orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_sod_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Tabel Delivery Orders (Header DO / Surat Jalan)
CREATE TABLE IF NOT EXISTS delivery_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    do_number VARCHAR(30) NOT NULL UNIQUE,
    so_id INT NOT NULL,
    delivery_date DATE NOT NULL,
    driver_name VARCHAR(100) DEFAULT NULL,
    status ENUM('Pending', 'Shipped', 'Delivered') NOT NULL DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_do_so FOREIGN KEY (so_id) REFERENCES sales_orders(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Tabel Delivery Order Details (Detail Item DO / Barang yang dikirim)
CREATE TABLE IF NOT EXISTS delivery_order_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    do_id INT NOT NULL,
    product_id INT NOT NULL,
    qty_shipped INT NOT NULL,
    CONSTRAINT fk_dod_do FOREIGN KEY (do_id) REFERENCES delivery_orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_dod_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Tabel Invoices (Header Faktur Penjualan)
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(30) NOT NULL UNIQUE,
    do_id INT NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE DEFAULT NULL,
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    grand_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('Unpaid', 'Partial', 'Paid', 'Cancelled') NOT NULL DEFAULT 'Unpaid',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_inv_do FOREIGN KEY (do_id) REFERENCES delivery_orders(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. Tabel Invoice Details
CREATE TABLE IF NOT EXISTS invoice_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    subtotal DECIMAL(15,2) NOT NULL,
    CONSTRAINT fk_invd_inv FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_invd_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. Tabel Journal Entries (Header Jurnal Umum)
CREATE TABLE IF NOT EXISTS journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_number VARCHAR(30) NOT NULL UNIQUE,
    journal_date DATE NOT NULL,
    reference_type VARCHAR(30) DEFAULT NULL, -- Cth: 'Invoice', 'Payment'
    reference_id INT DEFAULT NULL,
    description TEXT DEFAULT NULL,
    total_debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. Tabel Journal Details (Rincian Debit / Kredit)
CREATE TABLE IF NOT EXISTS journal_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    account_code VARCHAR(20) NOT NULL,
    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_jd_journal FOREIGN KEY (journal_id) REFERENCES journal_entries(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 13. Tabel Payments (Header Pelunasan Piutang / Kas Masuk)
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_number VARCHAR(30) NOT NULL UNIQUE,
    invoice_id INT NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(50) DEFAULT 'Transfer Bank',
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pay_inv FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;