-- Update push notification token field to support multiple devices
-- Step 1: Add new push_tokens JSON array column
ALTER TABLE users ADD COLUMN push_tokens JSONB DEFAULT NULL;

-- Step 2: Migrate existing single push_token data to the new array format
UPDATE users
SET
    push_tokens = CASE
        WHEN push_token IS NOT NULL
        AND push_token != '' THEN jsonb_build_array (push_token)
        ELSE NULL
    END
WHERE
    push_token IS NOT NULL;

-- Step 3: Drop the old single push_token column
ALTER TABLE users DROP COLUMN IF EXISTS push_token;

-- Step 4: Add index for better performance on push_tokens
CREATE INDEX IF NOT EXISTS idx_users_push_tokens ON users USING GIN (push_tokens)
WHERE
    push_tokens IS NOT NULL
    AND push_tokens != 'null'::jsonb
    AND jsonb_array_length(push_tokens) > 0
    AND deleted_at IS NULL;

-- Step 5: Add comment for documentation
COMMENT ON COLUMN users.push_tokens IS 'JSON array of Najva push notification tokens from different user devices';