USE db_abos_v0.1;

-- Adds bank/credit-card contact & address fields to dim_financial_entities (2026-07-16).
-- Purely additive — all columns NULL, no existing column/type touched, no view or stored
-- procedure references these fields today (checked vw_statement_reconciliation_detail and
-- sp_process_ai_bank_statement, neither selects/inserts them).

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_contact_number'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_contact_number VARCHAR(50) NULL AFTER bank_or_issuer_name',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_address'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_address VARCHAR(500) NULL AFTER bank_contact_number',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_fax_number'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_fax_number VARCHAR(50) NULL AFTER bank_address',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_email'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_email VARCHAR(255) NULL AFTER bank_fax_number',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'connected_phone_no'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN connected_phone_no VARCHAR(50) NULL AFTER bank_email',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'connected_email'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN connected_email VARCHAR(255) NULL AFTER connected_phone_no',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'given_address'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN given_address VARCHAR(500) NULL AFTER connected_email',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;