CREATE TABLE IF NOT EXISTS loan_proposal_status_reasons (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    type ENUM('rejected', 'cancelled') NOT NULL,
    label VARCHAR(150) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

SET @ddl := IF(
    @has_status_reason_id = 0,
    'ALTER TABLE loan_proposals ADD COLUMN status_reason_id INT UNSIGNED NULL AFTER status',
    'SELECT 1'
);

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

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

SET @ddl := IF(
    @has_status_reason_label = 0,
    'ALTER TABLE loan_proposals ADD COLUMN status_reason_label VARCHAR(150) NULL AFTER status_reason_id',
    'SELECT 1'
);

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

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

SET @ddl := IF(
    @has_status_reason_index = 0,
    'ALTER TABLE loan_proposals ADD INDEX idx_loan_proposals_status_reason_id (status_reason_id)',
    'SELECT 1'
);

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