-- The generic profile save/get handlers (apps/users/src/handlers/profile.rs)
-- have always read and written every non-COMPANY/JOB_SEEKER role's profile
-- table (INSERT/SELECT ... WHERE user_role_profile_id = ...), but this
-- linking column was never migrated onto any of the profession tables — it
-- only ever existed in a disabled migration (20260415010003, .skip). Every
-- profile save/submit and document upload for these 10 professions has
-- therefore always failed with "column user_role_profile_id does not exist".
ALTER TABLE photographer_profiles         ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE tutor_profiles                ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE makeup_artist_profiles        ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE developer_profiles            ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE video_editor_profiles         ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE graphic_designer_profiles     ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE social_media_manager_profiles ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE fitness_trainer_profiles      ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE catering_service_profiles     ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;
ALTER TABLE ugc_content_creator_profiles  ADD COLUMN IF NOT EXISTS user_role_profile_id UUID REFERENCES user_role_profiles(id) ON DELETE CASCADE;

-- Backfill for any rows created by earlier (failed) save attempts — the
-- user_role_profiles row itself was created successfully by
-- get_or_create_user_role_profile_id() even though the subsequent profile
-- table write then failed, so a matching row may already exist.
UPDATE photographer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'PHOTOGRAPHER' AND p.user_role_profile_id IS NULL;
UPDATE tutor_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'TUTOR' AND p.user_role_profile_id IS NULL;
UPDATE makeup_artist_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'MAKEUP_ARTIST' AND p.user_role_profile_id IS NULL;
UPDATE developer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'DEVELOPER' AND p.user_role_profile_id IS NULL;
UPDATE video_editor_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'VIDEO_EDITOR' AND p.user_role_profile_id IS NULL;
UPDATE graphic_designer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'GRAPHIC_DESIGNER' AND p.user_role_profile_id IS NULL;
UPDATE social_media_manager_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'SOCIAL_MEDIA_MANAGER' AND p.user_role_profile_id IS NULL;
UPDATE fitness_trainer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'FITNESS_TRAINER' AND p.user_role_profile_id IS NULL;
UPDATE catering_service_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'CATERING_SERVICES' AND p.user_role_profile_id IS NULL;
UPDATE ugc_content_creator_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'UGC_CONTENT_CREATOR' AND p.user_role_profile_id IS NULL;

-- For any rows still unlinked (profile table row exists but no matching
-- user_role_profiles row was ever created), create one now so existing data
-- isn't orphaned by the NOT NULL below.
INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'PHOTOGRAPHER', 'DRAFT' FROM photographer_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE photographer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'PHOTOGRAPHER' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'TUTOR', 'DRAFT' FROM tutor_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE tutor_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'TUTOR' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'MAKEUP_ARTIST', 'DRAFT' FROM makeup_artist_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE makeup_artist_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'MAKEUP_ARTIST' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'DEVELOPER', 'DRAFT' FROM developer_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE developer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'DEVELOPER' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'VIDEO_EDITOR', 'DRAFT' FROM video_editor_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE video_editor_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'VIDEO_EDITOR' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'GRAPHIC_DESIGNER', 'DRAFT' FROM graphic_designer_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE graphic_designer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'GRAPHIC_DESIGNER' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'SOCIAL_MEDIA_MANAGER', 'DRAFT' FROM social_media_manager_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE social_media_manager_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'SOCIAL_MEDIA_MANAGER' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'FITNESS_TRAINER', 'DRAFT' FROM fitness_trainer_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE fitness_trainer_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'FITNESS_TRAINER' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'CATERING_SERVICES', 'DRAFT' FROM catering_service_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE catering_service_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'CATERING_SERVICES' AND p.user_role_profile_id IS NULL;

INSERT INTO user_role_profiles (user_id, role_key, status)
    SELECT user_id, 'UGC_CONTENT_CREATOR', 'DRAFT' FROM ugc_content_creator_profiles WHERE user_role_profile_id IS NULL
    ON CONFLICT (user_id, role_key) DO NOTHING;
UPDATE ugc_content_creator_profiles p SET user_role_profile_id = urp.id
    FROM user_role_profiles urp WHERE p.user_id = urp.user_id AND urp.role_key = 'UGC_CONTENT_CREATOR' AND p.user_role_profile_id IS NULL;

CREATE INDEX IF NOT EXISTS idx_photographer_profiles_urp ON photographer_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_tutor_profiles_urp ON tutor_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_makeup_artist_profiles_urp ON makeup_artist_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_developer_profiles_urp ON developer_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_video_editor_profiles_urp ON video_editor_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_graphic_designer_profiles_urp ON graphic_designer_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_social_media_manager_profiles_urp ON social_media_manager_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_fitness_trainer_profiles_urp ON fitness_trainer_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_catering_service_profiles_urp ON catering_service_profiles(user_role_profile_id);
CREATE INDEX IF NOT EXISTS idx_ugc_content_creator_profiles_urp ON ugc_content_creator_profiles(user_role_profile_id);
