ALTER TABLE maintenance_jobs
    ADD COLUMN complaint_source ENUM('Driver','Fleet Office','Maintenance Office','Other') NULL AFTER driver_id,
    ADD COLUMN complaint_title VARCHAR(180) NULL AFTER complaint_source,
    ADD COLUMN complaint_description TEXT NULL AFTER complaint_title,
    ADD COLUMN maintenance_comment TEXT NULL AFTER complaint_description,
    ADD COLUMN quote_amount DECIMAL(14,2) NULL AFTER cost_of_repair,
    ADD COLUMN vetted_amount DECIMAL(14,2) NULL AFTER quote_amount,
    ADD COLUMN approved_amount DECIMAL(14,2) NULL AFTER vetted_amount,
    ADD COLUMN workshop_days INT NULL AFTER approved_amount,
    ADD COLUMN expected_completion_date DATE NULL AFTER workshop_days,
    ADD COLUMN workshop_received_date DATE NULL AFTER expected_completion_date,
    ADD COLUMN work_started_date DATE NULL AFTER workshop_received_date,
    ADD COLUMN work_completed_date DATE NULL AFTER work_started_date,
    ADD COLUMN inspection_date DATE NULL AFTER work_completed_date,
    ADD COLUMN inspection_status ENUM('Pending','Confirmed','Rejected') NULL DEFAULT 'Pending' AFTER inspection_date,
    ADD COLUMN inspection_comment TEXT NULL AFTER inspection_status,
    ADD COLUMN internal_control_status ENUM('Pending','Validated','Rejected') NULL DEFAULT 'Pending' AFTER inspection_comment,
    ADD COLUMN internal_control_comment TEXT NULL AFTER internal_control_status,
    ADD COLUMN requisition_status ENUM('Pending','Raised','Approved','Paid') NULL DEFAULT 'Pending' AFTER internal_control_comment,
    ADD COLUMN closure_date DATE NULL AFTER requisition_status,
    ADD COLUMN workflow_stage ENUM(
        'Complaint Logged',
        'Maintenance Review',
        'Work Order Raised',
        'Quote Submitted',
        'Quote Vetted',
        'Job Order Approved',
        'Workshop In Progress',
        'Inspection',
        'Internal Control',
        'Closed',
        'Suspended',
        'Cancelled'
    ) NOT NULL DEFAULT 'Complaint Logged' AFTER closure_date,
    MODIFY COLUMN status ENUM('Pending','Approved','Rejected','In Progress','Completed','Suspended','Cancelled','Closed','Paid') NULL DEFAULT 'Pending';

CREATE TABLE IF NOT EXISTS maintenance_job_actions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    maintenance_job_id BIGINT UNSIGNED NOT NULL,
    action_code VARCHAR(60) NOT NULL,
    action_label VARCHAR(120) NOT NULL,
    stage_from VARCHAR(80) NULL,
    stage_to VARCHAR(80) NULL,
    amount DECIMAL(14,2) NULL,
    comment TEXT NULL,
    acted_by BIGINT UNSIGNED NULL,
    acted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_maintenance_job_actions_job (maintenance_job_id),
    CONSTRAINT fk_maintenance_job_actions_job
        FOREIGN KEY (maintenance_job_id) REFERENCES maintenance_jobs(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;