pipu-music/services/api/migrations/00015_onboarding_demographics.sql

-- +goose Up
-- Keep the original group and year; approximate current age from its midpoint.
-- +goose StatementBegin
CREATE FUNCTION rec_age_group(choice jsonb) RETURNS text LANGUAGE sql STABLE AS $$
    WITH age AS (
        SELECT CASE choice->>'age_group'
            WHEN 'under_13' THEN 6 WHEN '13_19' THEN 16 WHEN '20_29' THEN 24
            WHEN '30_39' THEN 34 WHEN '40_49' THEN 44 WHEN '50_59' THEN 54
            WHEN '60_plus' THEN 65 END
            + GREATEST(0, EXTRACT(YEAR FROM CURRENT_DATE)::int
                - COALESCE((choice->>'age_group_year')::int, EXTRACT(YEAR FROM CURRENT_DATE)::int)) AS n
    )
    SELECT CASE WHEN n IS NULL THEN NULL WHEN n < 13 THEN 'under_13' WHEN n < 20 THEN '13_19'
        WHEN n < 30 THEN '20_29' WHEN n < 40 THEN '30_39' WHEN n < 50 THEN '40_49'
        WHEN n < 60 THEN '50_59' ELSE '60_plus' END FROM age
$$;
-- +goose StatementEnd
CREATE INDEX user_profiles_rec_gender_idx ON user_profiles_rec ((onboarding->>'gender'));

-- +goose Down
DROP INDEX user_profiles_rec_gender_idx;
DROP FUNCTION rec_age_group(jsonb);