SET @proposal_number_column_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'loan_proposals'
      AND COLUMN_NAME = 'proposal_number'
);

SET @ddl := IF(
    @proposal_number_column_exists = 0,
    'ALTER TABLE loan_proposals ADD COLUMN proposal_number VARCHAR(80) NULL AFTER external_reference',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @proposal_number_index_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'loan_proposals'
      AND INDEX_NAME = 'uq_loan_proposals_proposal_number'
);

SET @ddl := IF(
    @proposal_number_index_exists = 0,
    'ALTER TABLE loan_proposals ADD UNIQUE KEY uq_loan_proposals_proposal_number (proposal_number)',
    'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;