-- Workflow Engine
-- workflow_templates: configurable workflow definitions
-- workflow_template_steps: ordered steps per template
-- workflow_runs: runtime instances
-- workflow_run_steps: runtime step instances

CREATE TABLE IF NOT EXISTS workflow_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    module VARCHAR(50) NOT NULL,
    description TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workflow_template_steps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    step_order INT NOT NULL,
    label VARCHAR(100) NOT NULL,
    role_id INT NOT NULL,
    can_delegate TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES workflow_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workflow_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    module VARCHAR(50) NOT NULL,
    record_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','approved','rejected','returned','cancelled') NOT NULL DEFAULT 'pending',
    created_by BIGINT UNSIGNED NOT NULL,
    current_step_order INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES workflow_templates(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS workflow_run_steps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    run_id BIGINT UNSIGNED NOT NULL,
    template_step_id BIGINT UNSIGNED NOT NULL,
    step_order INT NOT NULL,
    label VARCHAR(100) NOT NULL,
    role_id INT NOT NULL,
    assigned_user_id BIGINT UNSIGNED NULL,
    delegate_user_id BIGINT UNSIGNED NULL,
    status ENUM('pending','approved','rejected','returned','skipped') NOT NULL DEFAULT 'pending',
    comment TEXT NULL,
    action_by BIGINT UNSIGNED NULL,
    acted_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (run_id) REFERENCES workflow_runs(id) ON DELETE CASCADE,
    FOREIGN KEY (template_step_id) REFERENCES workflow_template_steps(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default Vehicle Requisition workflow template
INSERT IGNORE INTO workflow_templates (id, name, module, description, is_active)
VALUES (1, 'Vehicle Requisition', 'vehicle_request', 'Standard 3-step approval for vehicle requests', 1);

INSERT IGNORE INTO workflow_template_steps (id, template_id, step_order, label, role_id, can_delegate)
VALUES
    (1, 1, 1, 'Supervisor Approval', 1, 1),
    (2, 1, 2, 'Fleet Officer Review', 6, 1),
    (3, 1, 3, 'Fleet Manager Approval', 7, 1);

-- Permissions
INSERT IGNORE INTO permissions (code, description) VALUES
    ('workflow.templates.view', 'View workflow templates'),
    ('workflow.templates.manage', 'Create/edit/delete workflow templates'),
    ('workflow.approve_custom', 'Approve workflow steps across modules');