USE dating_app;

-- Optional profile detail fields. Existing users keep NULL until they update their profile.

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'occupation'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN occupation VARCHAR(120) NULL AFTER bio', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'education'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN education VARCHAR(120) NULL AFTER occupation', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'height_cm'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN height_cm INT NULL AFTER education', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'beliefs'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN beliefs VARCHAR(120) NULL AFTER height_cm', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'smoking'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN smoking VARCHAR(50) NULL AFTER beliefs', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'drinking'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN drinking VARCHAR(50) NULL AFTER smoking', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'pets'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN pets VARCHAR(120) NULL AFTER drinking', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @column_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_profiles' AND COLUMN_NAME = 'exercise'
);
SET @statement = IF(@column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN exercise VARCHAR(50) NULL AFTER pets', 'SELECT 1');
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;