CREATE TABLE "age_check" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"imvu_id" bigint,
	"provider" text NOT NULL,
	"method" text DEFAULT '' NOT NULL,
	"status" text NOT NULL,
	"over_eighteen" boolean DEFAULT false NOT NULL,
	"provider_ref" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"checked_at" timestamp with time zone,
	"expires_at" timestamp with time zone
);
--> statement-breakpoint
ALTER TABLE "age_check" ADD CONSTRAINT "age_check_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "age_check_user" ON "age_check" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "age_check_imvu" ON "age_check" USING btree ("imvu_id");--> statement-breakpoint
CREATE UNIQUE INDEX "age_check_provider_ref" ON "age_check" USING btree ("provider","provider_ref");--> statement-breakpoint
-- Existing links' IMVU results become IMVU age checks (expiring at once, so they're checked again soon).
INSERT INTO "age_check" ("id", "user_id", "imvu_id", "provider", "method", "status", "over_eighteen", "checked_at", "expires_at")
SELECT md5(random()::text || "imvu_id"::text), "user_id", "imvu_id", 'imvu',
  CASE WHEN "age_verified" THEN 'imvu_age_verified' WHEN "access_pass" THEN 'imvu_access_pass' ELSE 'imvu_none' END,
  'complete', "age_verified" OR "access_pass", "checked_at", now()
FROM "imvu_link";--> statement-breakpoint
ALTER TABLE "imvu_link" DROP COLUMN "age_verified";--> statement-breakpoint
ALTER TABLE "imvu_link" DROP COLUMN "access_pass";--> statement-breakpoint
ALTER TABLE "imvu_link" DROP COLUMN "checked_at";