-- Slice 8b-4b · Requisition Issue — schema patch
-- Adds fields recorded at issue time. issued_on already exists from 8b-4a.

SET @sql := (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE service_requisitions
        ADD COLUMN issued_by       INT NOT NULL DEFAULT 0 AFTER issued_on,
        ADD COLUMN issued_by_name  VARCHAR(120) NOT NULL DEFAULT "" AFTER issued_by,
        ADD COLUMN issue_amount    DECIMAL(20,2) NOT NULL DEFAULT 0.00 AFTER issued_by_name,
        ADD COLUMN issue_reference VARCHAR(100) NOT NULL DEFAULT "" AFTER issue_amount,
        ADD COLUMN issue_notes     VARCHAR(500) NOT NULL DEFAULT "" AFTER issue_reference',
    'SELECT "service_requisitions.issued_by already exists"'
) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'service_requisitions' AND column_name = 'issued_by');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
