CREATE DATABASE IF NOT EXISTS rodium CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rodium;
CREATE TABLE roles(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) UNIQUE NOT NULL);
INSERT INTO roles(name) VALUES ('SUPERADMIN'),('MANAGER'),('CASHIER'),('WAITER'),('STOCK ADMIN');
CREATE TABLE branches(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,address TEXT,phone VARCHAR(50),active TINYINT DEFAULT 1);
INSERT INTO branches(name,address,phone) VALUES ('RODIUM MAIN','Alamat cabang utama','');
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,role_id INT NOT NULL,branch_id INT NULL,username VARCHAR(80) UNIQUE NOT NULL,name VARCHAR(120) NOT NULL,password_hash VARCHAR(255) NOT NULL,active TINYINT DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(role_id) REFERENCES roles(id),FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE SET NULL);
INSERT INTO users(role_id,branch_id,username,name,password_hash) VALUES (1,1,'superadmin','Superadmin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC3eGvQ7k0mX1wYyZ3vK');
CREATE TABLE settings(setting_key VARCHAR(100) PRIMARY KEY,setting_value TEXT);
INSERT INTO settings VALUES ('store_name','RODIUM'),('store_address',''),('store_phone',''),('store_email',''),('store_website',''),('store_logo',''),('tax','0'),('service_charge','0'),('receipt_footer','Terima kasih telah berkunjung ke RODIUM.');
CREATE TABLE tables(id INT AUTO_INCREMENT PRIMARY KEY,table_no VARCHAR(30),zone VARCHAR(80),capacity INT DEFAULT 4,status VARCHAR(30) DEFAULT 'AVAILABLE',active TINYINT DEFAULT 1);
CREATE TABLE products(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150),category VARCHAR(100),cost_price DECIMAL(15,2) DEFAULT 0,sell_price DECIMAL(15,2) DEFAULT 0,commission_type ENUM('PERCENT','FIXED') DEFAULT 'PERCENT',commission_value DECIMAL(15,2) DEFAULT 0,stock DECIMAL(15,2) DEFAULT 0,stock_min DECIMAL(15,2) DEFAULT 0,active TINYINT DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE orders(id BIGINT AUTO_INCREMENT PRIMARY KEY,reference_no VARCHAR(80) UNIQUE,branch_id INT,table_id INT,subtotal DECIMAL(15,2),discount DECIMAL(15,2),service_charge DECIMAL(15,2),tax DECIMAL(15,2),total DECIMAL(15,2),status VARCHAR(30),created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE order_items(id BIGINT AUTO_INCREMENT PRIMARY KEY,order_id BIGINT,product_id INT,waiter_id INT,qty DECIMAL(15,2),unit_price DECIMAL(15,2),discount DECIMAL(15,2) DEFAULT 0,subtotal DECIMAL(15,2),commission_rate DECIMAL(15,2),commission_amount DECIMAL(15,2),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE reservations(id BIGINT AUTO_INCREMENT PRIMARY KEY,branch_id INT,customer_name VARCHAR(150),phone VARCHAR(50),reservation_date DATE,reservation_time TIME,guests INT,table_id INT,deposit DECIMAL(15,2),status VARCHAR(30),notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE receivables(id BIGINT AUTO_INCREMENT PRIMARY KEY,customer_name VARCHAR(150),phone VARCHAR(50),reference_no VARCHAR(80),total DECIMAL(15,2),paid DECIMAL(15,2),balance DECIMAL(15,2),due_date DATE,status VARCHAR(30),branch_id INT,notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT,action VARCHAR(80),module VARCHAR(80),reference_id BIGINT NULL,data JSON,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);