USE dating_app;

-- Country is stored on the profile so discovery can rank same-city users first,
-- then users in the same country. Matching percentages are calculated from
-- existing interests and Feed answers, so no persisted score column is needed.
SET @column_exists = (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'user_profiles'
      AND column_name = 'country'
);
SET @statement = IF(
    @column_exists = 0,
    'ALTER TABLE user_profiles ADD COLUMN country VARCHAR(120) NULL AFTER city',
    'SELECT 1'
);
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;

SET @index_exists = (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'user_profiles'
      AND index_name = 'ix_user_profiles_city_country'
);
SET @statement = IF(
    @index_exists = 0,
    'CREATE INDEX ix_user_profiles_city_country ON user_profiles (city, country)',
    'SELECT 1'
);
PREPARE migration_statement FROM @statement;
EXECUTE migration_statement;
DEALLOCATE PREPARE migration_statement;