-- RODIUM v1.2 enhancement migration. BACKUP DATABASE FIRST; run against existing RODIUM database.
CREATE TABLE IF NOT EXISTS cashier_shifts (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, branch_id INT NULL, user_id INT NOT NULL,
 opening_cash DECIMAL(15,2) NOT NULL DEFAULT 0, expected_cash DECIMAL(15,2) NOT NULL DEFAULT 0,
 actual_cash DECIMAL(15,2) NULL, difference_amount DECIMAL(15,2) NULL,
 opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, closed_at DATETIME NULL,
 notes TEXT NULL, status ENUM('OPEN','CLOSED') NOT NULL DEFAULT 'OPEN', INDEX(user_id,status), INDEX(branch_id,opened_at)
);
CREATE TABLE IF NOT EXISTS order_payments (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT NOT NULL, payment_method ENUM('CASH','QRIS','TRANSFER','CARD','RECEIVABLE') NOT NULL,
 amount DECIMAL(15,2) NOT NULL, reference_no VARCHAR(120) NULL, status ENUM('PENDING','PAID','FAILED') NOT NULL DEFAULT 'PAID',
 paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, user_id INT NULL, notes VARCHAR(255) NULL, INDEX(order_id), INDEX(paid_at)
);
CREATE TABLE IF NOT EXISTS receivable_payments (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, receivable_id BIGINT NOT NULL, amount DECIMAL(15,2) NOT NULL,
 payment_method VARCHAR(30) NOT NULL DEFAULT 'CASH', reference_no VARCHAR(120) NULL, user_id INT NULL,
 paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, notes VARCHAR(255) NULL, INDEX(receivable_id)
);
CREATE TABLE IF NOT EXISTS commission_payouts (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, waiter_id INT NOT NULL, branch_id INT NULL, period_start DATE NOT NULL, period_end DATE NOT NULL,
 amount DECIMAL(15,2) NOT NULL, paid_by INT NULL, paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 method VARCHAR(30) NOT NULL DEFAULT 'CASH', reference_no VARCHAR(120) NULL, notes VARCHAR(255) NULL, INDEX(waiter_id,period_start,period_end)
);
CREATE TABLE IF NOT EXISTS stock_counts (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, branch_id INT NULL, product_id INT NOT NULL, system_qty DECIMAL(15,3) NOT NULL,
 actual_qty DECIMAL(15,3) NOT NULL, difference_qty DECIMAL(15,3) NOT NULL, notes VARCHAR(255) NULL,
 counted_by INT NULL, counted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, status ENUM('DRAFT','POSTED') NOT NULL DEFAULT 'POSTED', INDEX(product_id,counted_at)
);
CREATE TABLE IF NOT EXISTS stock_movements (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, branch_id INT NULL, movement_type ENUM('IN','OUT','OPNAME','WASTE','TRANSFER') NOT NULL,
 qty DECIMAL(15,3) NOT NULL, reference_no VARCHAR(120) NULL, notes VARCHAR(255) NULL, user_id INT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX(product_id,created_at)
);
CREATE TABLE IF NOT EXISTS app_permissions (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(50) NOT NULL, permission_key VARCHAR(100) NOT NULL,
 allowed TINYINT(1) NOT NULL DEFAULT 0, UNIQUE KEY role_permission(role_name,permission_key)
);
CREATE TABLE IF NOT EXISTS print_jobs (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT NULL, destination ENUM('KITCHEN','BAR','BOTTLE','RECEIPT') NOT NULL,
 payload MEDIUMTEXT NOT NULL, status ENUM('QUEUED','PRINTED','FAILED') NOT NULL DEFAULT 'QUEUED', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, printed_at DATETIME NULL
);
CREATE TABLE IF NOT EXISTS qris_settings (
 id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NULL, merchant_name VARCHAR(150) NULL, qris_image VARCHAR(255) NULL,
 provider VARCHAR(40) NOT NULL DEFAULT 'STATIC', provider_merchant_id VARCHAR(150) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1
);
INSERT IGNORE INTO app_permissions(role_name,permission_key,allowed) VALUES
('SUPERADMIN','users.manage',1),('SUPERADMIN','settings.manage',1),('SUPERADMIN','orders.discount',1),('SUPERADMIN','orders.void',1),('SUPERADMIN','reports.profit',1),('SUPERADMIN','stock.opname',1),('SUPERADMIN','commission.pay',1),('SUPERADMIN','cashier.close',1),('MANAGER','users.manage',0),('MANAGER','orders.discount',1),('MANAGER','orders.void',1),('MANAGER','reports.profit',1),('MANAGER','stock.opname',1),('MANAGER','commission.pay',1),('MANAGER','cashier.close',1),('CASHIER','cashier.close',1),('CASHIER','receivables.pay',1),('STOCK ADMIN','stock.opname',1),('WAITER','orders.discount',0);