208 lines
15 KiB
SQL
208 lines
15 KiB
SQL
CREATE TYPE "public"."group_role" AS ENUM('owner', 'member');--> statement-breakpoint
|
|
CREATE TYPE "public"."event_role" AS ENUM('owner', 'manager', 'moderator', 'viewer');--> statement-breakpoint
|
|
CREATE TYPE "public"."platform_role" AS ENUM('super_admin', 'admin', 'moderator', 'viewer');--> statement-breakpoint
|
|
CREATE TYPE "public"."event_create_policy" AS ENUM('open', 'invite', 'admin_only');--> statement-breakpoint
|
|
CREATE TYPE "public"."entitlement_source" AS ENUM('signup_default', 'invite', 'code', 'platform');--> statement-breakpoint
|
|
CREATE TYPE "public"."invite_kind" AS ENUM('email', 'code');--> statement-breakpoint
|
|
CREATE TYPE "public"."invite_status" AS ENUM('pending', 'accepted', 'revoked', 'expired');--> statement-breakpoint
|
|
CREATE TYPE "public"."photo_processing_status" AS ENUM('uploading', 'processing', 'ready', 'failed');--> statement-breakpoint
|
|
CREATE TYPE "public"."photo_visibility" AS ENUM('pending', 'public', 'hidden', 'private', 'rejected');--> statement-breakpoint
|
|
CREATE TABLE "groups" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"slug" text NOT NULL,
|
|
"created_by_user_id" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "group_memberships" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"group_id" uuid NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"role" "group_role" DEFAULT 'member' NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "event_memberships" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"event_id" uuid NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"role" "event_role" DEFAULT 'viewer' NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "platform_administrators" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"role" "platform_role" DEFAULT 'admin' NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "deployment_settings" (
|
|
"id" text PRIMARY KEY DEFAULT 'default' NOT NULL,
|
|
"open_signup" boolean DEFAULT true NOT NULL,
|
|
"event_create_policy" "event_create_policy" DEFAULT 'open' NOT NULL,
|
|
"default_event_limit" integer DEFAULT 1 NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "entitlements" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"group_id" uuid NOT NULL,
|
|
"event_limit" integer,
|
|
"expires_at" timestamp with time zone,
|
|
"complimentary" boolean DEFAULT false NOT NULL,
|
|
"source" "entitlement_source" DEFAULT 'signup_default' NOT NULL,
|
|
"granted_by_user_id" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "invites" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"kind" "invite_kind" NOT NULL,
|
|
"status" "invite_status" DEFAULT 'pending' NOT NULL,
|
|
"email" text,
|
|
"token_hash" text NOT NULL,
|
|
"reusable" boolean DEFAULT false NOT NULL,
|
|
"max_uses" integer DEFAULT 1 NOT NULL,
|
|
"used_count" integer DEFAULT 0 NOT NULL,
|
|
"group_id" uuid,
|
|
"event_id" uuid,
|
|
"group_role" "group_role",
|
|
"event_role" "event_role",
|
|
"grant_unlimited_events" boolean DEFAULT false NOT NULL,
|
|
"grant_event_limit" integer,
|
|
"grant_complimentary" boolean DEFAULT false NOT NULL,
|
|
"created_by_user_id" text,
|
|
"accepted_by_user_id" text,
|
|
"expires_at" timestamp with time zone,
|
|
"accepted_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "guests" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"event_id" uuid NOT NULL,
|
|
"display_name" text,
|
|
"email" text,
|
|
"notify_when_ready" boolean DEFAULT false NOT NULL,
|
|
"notified_at" timestamp with time zone,
|
|
"note" text,
|
|
"token_hash" text NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "submissions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"event_id" uuid NOT NULL,
|
|
"guest_id" uuid NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
CREATE TABLE "audit_events" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"group_id" uuid,
|
|
"event_id" uuid,
|
|
"actor_user_id" text,
|
|
"action" text NOT NULL,
|
|
"subject_type" text NOT NULL,
|
|
"subject_id" text NOT NULL,
|
|
"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);--> statement-breakpoint
|
|
ALTER TABLE "events" ADD COLUMN "group_id" uuid;--> statement-breakpoint
|
|
ALTER TABLE "events" ADD COLUMN "listed" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "events" ADD COLUMN "gallery_released_at" timestamp with time zone;--> statement-breakpoint
|
|
INSERT INTO "groups" ("id", "name", "slug", "created_by_user_id")
|
|
SELECT gen_random_uuid(), COALESCE(u.name, 'Group') || '''s group', 'group-' || substr(replace(e.host_user_id, '-', ''), 1, 16), e.host_user_id
|
|
FROM (SELECT DISTINCT host_user_id FROM events) e
|
|
LEFT JOIN "user" u ON u.id = e.host_user_id
|
|
WHERE NOT EXISTS (SELECT 1 FROM groups g WHERE g.created_by_user_id = e.host_user_id);--> statement-breakpoint
|
|
UPDATE events SET group_id = g.id
|
|
FROM groups g
|
|
WHERE g.created_by_user_id = events.host_user_id;--> statement-breakpoint
|
|
INSERT INTO group_memberships (group_id, user_id, role)
|
|
SELECT g.id, g.created_by_user_id, 'owner'
|
|
FROM groups g
|
|
WHERE g.created_by_user_id IS NOT NULL;--> statement-breakpoint
|
|
INSERT INTO event_memberships (event_id, user_id, role)
|
|
SELECT e.id, e.host_user_id, 'owner' FROM events e;--> statement-breakpoint
|
|
INSERT INTO entitlements (group_id, event_limit, complimentary, source)
|
|
SELECT g.id, 1, false, 'signup_default' FROM groups g;--> statement-breakpoint
|
|
ALTER TABLE "events" ALTER COLUMN "group_id" SET NOT NULL;--> statement-breakpoint
|
|
DROP INDEX IF EXISTS "events_host_user_id_idx";--> statement-breakpoint
|
|
ALTER TABLE "events" DROP COLUMN "host_user_id";--> statement-breakpoint
|
|
ALTER TABLE "photos" ADD COLUMN "submission_id" uuid;--> statement-breakpoint
|
|
ALTER TABLE "photos" ADD COLUMN "processing_status" "photo_processing_status" DEFAULT 'uploading' NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "photos" ADD COLUMN "visibility" "photo_visibility" DEFAULT 'pending' NOT NULL;--> statement-breakpoint
|
|
INSERT INTO guests (id, event_id, display_name, token_hash, notify_when_ready)
|
|
SELECT gen_random_uuid(), e.id, NULL, 'migrated-' || replace(e.id::text, '-', ''), false
|
|
FROM events e
|
|
WHERE EXISTS (SELECT 1 FROM photos p WHERE p.event_id = e.id);--> statement-breakpoint
|
|
INSERT INTO submissions (id, event_id, guest_id)
|
|
SELECT gen_random_uuid(), g.event_id, g.id
|
|
FROM guests g
|
|
WHERE g.token_hash LIKE 'migrated-%';--> statement-breakpoint
|
|
UPDATE photos p SET submission_id = s.id
|
|
FROM submissions s
|
|
WHERE s.event_id = p.event_id;--> statement-breakpoint
|
|
UPDATE photos SET
|
|
processing_status = CASE
|
|
WHEN status = 'uploading' THEN 'uploading'::photo_processing_status
|
|
WHEN status = 'processing' THEN 'processing'::photo_processing_status
|
|
ELSE 'ready'::photo_processing_status
|
|
END,
|
|
visibility = CASE
|
|
WHEN status = 'approved' THEN 'public'::photo_visibility
|
|
WHEN status = 'rejected' THEN 'rejected'::photo_visibility
|
|
ELSE 'pending'::photo_visibility
|
|
END;--> statement-breakpoint
|
|
ALTER TABLE "photos" ALTER COLUMN "submission_id" SET NOT NULL;--> statement-breakpoint
|
|
DROP INDEX IF EXISTS "photos_event_status_idx";--> statement-breakpoint
|
|
ALTER TABLE "photos" DROP COLUMN "contributor_name";--> statement-breakpoint
|
|
ALTER TABLE "photos" DROP COLUMN "status";--> statement-breakpoint
|
|
DROP TYPE "public"."photo_status";--> statement-breakpoint
|
|
INSERT INTO deployment_settings (id) VALUES ('default') ON CONFLICT (id) DO NOTHING;--> statement-breakpoint
|
|
ALTER TABLE "groups" ADD CONSTRAINT "groups_created_by_user_id_user_id_fk" FOREIGN KEY ("created_by_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "group_memberships" ADD CONSTRAINT "group_memberships_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "group_memberships" ADD CONSTRAINT "group_memberships_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "event_memberships" ADD CONSTRAINT "event_memberships_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "event_memberships" ADD CONSTRAINT "event_memberships_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "platform_administrators" ADD CONSTRAINT "platform_administrators_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "entitlements" ADD CONSTRAINT "entitlements_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "entitlements" ADD CONSTRAINT "entitlements_granted_by_user_id_user_id_fk" FOREIGN KEY ("granted_by_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invites" ADD CONSTRAINT "invites_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invites" ADD CONSTRAINT "invites_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invites" ADD CONSTRAINT "invites_created_by_user_id_user_id_fk" FOREIGN KEY ("created_by_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invites" ADD CONSTRAINT "invites_accepted_by_user_id_user_id_fk" FOREIGN KEY ("accepted_by_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "guests" ADD CONSTRAINT "guests_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "submissions" ADD CONSTRAINT "submissions_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "submissions" ADD CONSTRAINT "submissions_guest_id_guests_id_fk" FOREIGN KEY ("guest_id") REFERENCES "public"."guests"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "photos" ADD CONSTRAINT "photos_submission_id_submissions_id_fk" FOREIGN KEY ("submission_id") REFERENCES "public"."submissions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "events" ADD CONSTRAINT "events_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_event_id_events_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."events"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_actor_user_id_user_id_fk" FOREIGN KEY ("actor_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "groups_slug_idx" ON "groups" USING btree ("slug");--> statement-breakpoint
|
|
CREATE INDEX "groups_created_by_user_id_idx" ON "groups" USING btree ("created_by_user_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "group_memberships_group_user_idx" ON "group_memberships" USING btree ("group_id","user_id");--> statement-breakpoint
|
|
CREATE INDEX "group_memberships_user_idx" ON "group_memberships" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "event_memberships_event_user_idx" ON "event_memberships" USING btree ("event_id","user_id");--> statement-breakpoint
|
|
CREATE INDEX "event_memberships_user_idx" ON "event_memberships" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "platform_administrators_user_idx" ON "platform_administrators" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "events_group_id_idx" ON "events" USING btree ("group_id");--> statement-breakpoint
|
|
CREATE INDEX "events_listed_idx" ON "events" USING btree ("listed","status");--> statement-breakpoint
|
|
CREATE INDEX "entitlements_group_id_idx" ON "entitlements" USING btree ("group_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "invites_token_hash_idx" ON "invites" USING btree ("token_hash");--> statement-breakpoint
|
|
CREATE INDEX "invites_email_idx" ON "invites" USING btree ("email");--> statement-breakpoint
|
|
CREATE INDEX "invites_group_id_idx" ON "invites" USING btree ("group_id");--> statement-breakpoint
|
|
CREATE INDEX "invites_event_id_idx" ON "invites" USING btree ("event_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "guests_event_token_idx" ON "guests" USING btree ("event_id","token_hash");--> statement-breakpoint
|
|
CREATE INDEX "guests_event_id_idx" ON "guests" USING btree ("event_id");--> statement-breakpoint
|
|
CREATE INDEX "submissions_event_id_idx" ON "submissions" USING btree ("event_id");--> statement-breakpoint
|
|
CREATE INDEX "submissions_guest_id_idx" ON "submissions" USING btree ("guest_id");--> statement-breakpoint
|
|
CREATE INDEX "photos_submission_id_idx" ON "photos" USING btree ("submission_id");--> statement-breakpoint
|
|
CREATE INDEX "photos_event_visibility_idx" ON "photos" USING btree ("event_id","visibility");--> statement-breakpoint
|
|
CREATE INDEX "audit_events_group_id_idx" ON "audit_events" USING btree ("group_id");--> statement-breakpoint
|
|
CREATE INDEX "audit_events_event_id_idx" ON "audit_events" USING btree ("event_id");--> statement-breakpoint
|
|
CREATE INDEX "audit_events_created_at_idx" ON "audit_events" USING btree ("created_at");
|