-- Wave M1: add JWT refresh-token support columns
-- sessions: refresh_token_hash (backfilled), last_refreshed_at
-- users: session_version

-- Step 0: ensure pgcrypto is available (needed for digest() in backfill)
CREATE EXTENSION IF NOT EXISTS pgcrypto;--> statement-breakpoint

-- Step 1: add refresh_token_hash as nullable first (backfill before NOT NULL)
ALTER TABLE "sessions" ADD COLUMN "refresh_token_hash" text;--> statement-breakpoint

-- Step 2: backfill existing rows — SHA-256 hex of the session id text
-- encode(digest(id, 'sha256'), 'hex') requires pgcrypto extension (available on Neon)
UPDATE "sessions" SET "refresh_token_hash" = encode(digest("id"::bytea, 'sha256'), 'hex') WHERE "refresh_token_hash" IS NULL;--> statement-breakpoint

-- Step 3: enforce NOT NULL now that all rows have a value
ALTER TABLE "sessions" ALTER COLUMN "refresh_token_hash" SET NOT NULL;--> statement-breakpoint

-- Step 4: unique index on refresh_token_hash
CREATE UNIQUE INDEX "sessions_refresh_token_hash_unique" ON "sessions" ("refresh_token_hash");--> statement-breakpoint

-- Step 5: last_refreshed_at (nullable — null until first refresh)
ALTER TABLE "sessions" ADD COLUMN "last_refreshed_at" timestamp with time zone;--> statement-breakpoint

-- Step 6: session_version on users (NOT NULL DEFAULT 0 — safe, no backfill needed)
ALTER TABLE "users" ADD COLUMN "session_version" integer NOT NULL DEFAULT 0;
