-- Adds the social-handle + subscription-plan columns to `users`.
--
--   handle  — case-insensitive (citext) URL slug, e.g. /u/satya-das.
--             Auto-generated from the email-local part on signup; user can
--             change it later via PATCH /users/me. Unique across the table.
--   plan    — 'free' (default) or 'premium'. Controls limits enforced at
--             the service layer (bookmarks count, collection counts).
--             No checkout flow yet — admin flips it with SQL.
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS handle CITEXT,
    ADD COLUMN IF NOT EXISTS plan VARCHAR(16) NOT NULL DEFAULT 'free'
        CHECK (plan IN ('free', 'premium'));

-- Backfill handles for any rows that already exist. The recipe mirrors the
-- generator the auth service uses: slugify the local-part of the email and
-- append a 6-char random suffix to make collisions vanishingly unlikely.
UPDATE users
SET handle = LOWER(REGEXP_REPLACE(SPLIT_PART(email::text, '@', 1), '[^a-z0-9]', '', 'g'))
             || '-' || substr(md5(id::text), 1, 6)
WHERE handle IS NULL;

ALTER TABLE users ALTER COLUMN handle SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'users_handle_key'
    ) THEN
        ALTER TABLE users ADD CONSTRAINT users_handle_key UNIQUE (handle);
    END IF;
END$$;