74 lines
3.1 KiB
SQL
74 lines
3.1 KiB
SQL
CREATE TABLE "student_groups" (
|
|
"id" UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
"organization_id" UUID,
|
|
"organization_name" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"week" INTEGER NOT NULL DEFAULT 1,
|
|
"status" TEXT NOT NULL DEFAULT '진행중',
|
|
"platform_name" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "student_groups_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "student_group_members" (
|
|
"id" UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
"group_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "student_group_members_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "student_groups_organization_name_name_key" ON "student_groups"("organization_name", "name");
|
|
CREATE INDEX "student_groups_organization_id_idx" ON "student_groups"("organization_id");
|
|
CREATE INDEX "student_groups_organization_name_idx" ON "student_groups"("organization_name");
|
|
CREATE UNIQUE INDEX "student_group_members_group_id_user_id_key" ON "student_group_members"("group_id", "user_id");
|
|
CREATE INDEX "student_group_members_user_id_idx" ON "student_group_members"("user_id");
|
|
|
|
ALTER TABLE "student_group_members"
|
|
ADD CONSTRAINT "student_group_members_group_id_fkey"
|
|
FOREIGN KEY ("group_id") REFERENCES "student_groups"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "student_group_members"
|
|
ADD CONSTRAINT "student_group_members_user_id_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
INSERT INTO "student_groups" ("organization_id", "organization_name", "name", "week", "status", "platform_name")
|
|
SELECT org."organization_id", org."organization_name", seed."name", seed."week", '진행중', seed."platform_name"
|
|
FROM (
|
|
VALUES
|
|
('그룹 A', 3, 'MK-mini #01'),
|
|
('그룹 B', 2, '시뮬레이션'),
|
|
('그룹 C', 4, 'f1tenth #01')
|
|
) AS seed("name", "week", "platform_name")
|
|
CROSS JOIN (
|
|
SELECT DISTINCT "primary_organization_id" AS "organization_id", "primary_organization_name" AS "organization_name"
|
|
FROM "users"
|
|
WHERE "primary_organization_name" IS NOT NULL
|
|
) org
|
|
ON CONFLICT ("organization_name", "name") DO NOTHING;
|
|
|
|
INSERT INTO "student_group_members" ("group_id", "user_id")
|
|
SELECT g."id", u."id"
|
|
FROM "student_groups" g
|
|
JOIN "users" u ON u."primary_organization_name" = g."organization_name"
|
|
WHERE g."name" = '그룹 A'
|
|
AND u."email" IN ('kms@yu.ac.kr', 'lsy@yu.ac.kr', 'cyj@yu.ac.kr')
|
|
ON CONFLICT ("group_id", "user_id") DO NOTHING;
|
|
|
|
INSERT INTO "student_group_members" ("group_id", "user_id")
|
|
SELECT g."id", u."id"
|
|
FROM "student_groups" g
|
|
JOIN "users" u ON u."primary_organization_name" = g."organization_name"
|
|
WHERE g."name" = '그룹 B'
|
|
AND u."email" IN ('jyr@yu.ac.kr', 'pjh@yu.ac.kr')
|
|
ON CONFLICT ("group_id", "user_id") DO NOTHING;
|
|
|
|
INSERT INTO "student_group_members" ("group_id", "user_id")
|
|
SELECT g."id", u."id"
|
|
FROM "student_groups" g
|
|
JOIN "users" u ON u."primary_organization_name" = g."organization_name"
|
|
WHERE g."name" = '그룹 C'
|
|
AND u."email" IN ('hdy@yu.ac.kr', 'ojh@yu.ac.kr', 'ysj@yu.ac.kr', 'kdh@yu.ac.kr')
|
|
ON CONFLICT ("group_id", "user_id") DO NOTHING;
|