-- Add token family support to user_sessions for refresh token rotation
ALTER TABLE "user_sessions"
  ADD COLUMN IF NOT EXISTS "family_id" UUID,
  ADD COLUMN IF NOT EXISTS "family_created_at" TIMESTAMP,
  ADD COLUMN IF NOT EXISTS "parent_refresh_token" VARCHAR(500),
  ADD COLUMN IF NOT EXISTS "is_replaced" BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS "replaced_by_token" VARCHAR(500),
  ADD COLUMN IF NOT EXISTS "is_revoked" BOOLEAN NOT NULL DEFAULT FALSE;

-- Backfill existing sessions: each existing session becomes its own family
UPDATE "user_sessions"
SET "family_id" = gen_random_uuid(),
    "family_created_at" = COALESCE("created_at", NOW())
WHERE "family_id" IS NULL;

-- Make family columns NOT NULL after backfill
ALTER TABLE "user_sessions"
  ALTER COLUMN "family_id" SET NOT NULL,
  ALTER COLUMN "family_created_at" SET NOT NULL;

-- Indexes for family lookups and reuse detection
CREATE INDEX IF NOT EXISTS "user_sessions_family_id_idx" ON "user_sessions" ("family_id");
CREATE INDEX IF NOT EXISTS "user_sessions_parent_refresh_token_idx" ON "user_sessions" ("parent_refresh_token");