SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `workflow_modules` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `module` VARCHAR(50) NOT NULL,
    `display_name` VARCHAR(100) NOT NULL,
    `table_name` VARCHAR(100) NOT NULL COMMENT 'DB table that stores the records',
    `creator_column` VARCHAR(100) NOT NULL DEFAULT 'created_by' COMMENT 'Column holding the creator user ID',
    `status_column` VARCHAR(100) NOT NULL DEFAULT 'status' COMMENT 'Column to update on approval/rejection',
    `approved_value` VARCHAR(50) NOT NULL DEFAULT 'approved' COMMENT 'Status value when fully approved',
    `rejected_value` VARCHAR(50) NOT NULL DEFAULT 'rejected' COMMENT 'Status value when rejected',
    `returned_value` VARCHAR(50) NOT NULL DEFAULT 'returned' COMMENT 'Status value when returned for revision',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed existing vehicle_request module
INSERT IGNORE INTO `workflow_modules` (`module`, `display_name`, `table_name`, `creator_column`, `status_column`, `approved_value`, `rejected_value`, `returned_value`, `is_active`)
VALUES ('vehicle_request', 'Vehicle Request', 'vehicle_requests', 'staff_id', 'status', 'approved', 'rejected', 'returned', 1);

SET FOREIGN_KEY_CHECKS = 1;