-- Controller workflow, discount approvals, and non-income order EIS receipts

ALTER TABLE users MODIFY role ENUM('warehouse','branch','branch_manager','sales','sales_assistant','admin','super_admin','controller') NOT NULL;

CREATE TABLE IF NOT EXISTS discount_rules (
    rule_id INT AUTO_INCREMENT PRIMARY KEY,
    rule_name VARCHAR(120) NOT NULL,
    discount_type ENUM('percentage','fixed') NOT NULL,
    discount_value DECIMAL(15,2) NOT NULL,
    max_sale_amount DECIMAL(15,2) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_discount_rules_active (is_active, discount_type, discount_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS discount_approval_requests (
    request_id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NOT NULL,
    requested_by INT NOT NULL,
    discount_type ENUM('percentage','fixed') NOT NULL,
    discount_value DECIMAL(15,2) NOT NULL,
    sale_total DECIMAL(15,2) NOT NULL DEFAULT 0,
    reason TEXT DEFAULT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by INT DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL,
    review_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_discount_requests_status (status),
    INDEX idx_discount_requests_branch (branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE orders MODIFY status ENUM('pending','approved','rejected','delivered','completed') NOT NULL DEFAULT 'pending';
ALTER TABLE requests MODIFY status ENUM('pending','converted','approved','rejected','expired') DEFAULT 'pending';
ALTER TABLE quotations MODIFY status ENUM('pending','converted','approved','rejected','expired') DEFAULT 'pending';
ALTER TABLE customer_service_requests MODIFY status ENUM('open','in_progress','waiting_customer','approved','rejected','resolved','closed') NOT NULL DEFAULT 'open';

ALTER TABLE orders ADD COLUMN IF NOT EXISTS payment_method VARCHAR(40) DEFAULT NULL;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS final_payment_amount DECIMAL(10,2) NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS assigned_code VARCHAR(255) DEFAULT NULL;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS eis_transaction_id VARCHAR(80) DEFAULT NULL;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS eis_outbox_id INT DEFAULT NULL;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS completed_by INT DEFAULT NULL;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS completed_at DATETIME DEFAULT NULL;

ALTER TABLE sold_items ADD COLUMN IF NOT EXISTS is_ims_income TINYINT(1) NOT NULL DEFAULT 1;
