57 lines
1.7 KiB
SQL
57 lines
1.7 KiB
SQL
INSERT INTO "organizations" ("id", "name", "type", "domain", "is_active", "created_at", "updated_at")
|
|
VALUES
|
|
('11111111-1111-4111-8111-111111111111', '퓨처드라이브', 'COMPANY', 'futuredrive.net', true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
|
|
('22222222-2222-4222-8222-222222222222', '영남대학교', 'SCHOOL', 'yu.ac.kr', true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
|
|
ON CONFLICT ("name", "type") DO UPDATE
|
|
SET
|
|
"domain" = EXCLUDED."domain",
|
|
"is_active" = true,
|
|
"updated_at" = CURRENT_TIMESTAMP;
|
|
|
|
UPDATE "user_affiliations"
|
|
SET "is_primary" = false,
|
|
"updated_at" = CURRENT_TIMESTAMP
|
|
WHERE "ends_at" IS NULL;
|
|
|
|
UPDATE "user_affiliations"
|
|
SET "is_primary" = true,
|
|
"updated_at" = CURRENT_TIMESTAMP
|
|
WHERE "organization_id" = '11111111-1111-4111-8111-111111111111'
|
|
AND "ends_at" IS NULL;
|
|
|
|
INSERT INTO "user_affiliations" (
|
|
"id",
|
|
"user_id",
|
|
"organization_id",
|
|
"department",
|
|
"position",
|
|
"is_primary",
|
|
"starts_at",
|
|
"created_at",
|
|
"updated_at"
|
|
)
|
|
SELECT
|
|
lower(
|
|
substr(md5("users"."id" || ':futuredrive'), 1, 8) || '-' ||
|
|
substr(md5("users"."id" || ':futuredrive'), 9, 4) || '-4' ||
|
|
substr(md5("users"."id" || ':futuredrive'), 14, 3) || '-8' ||
|
|
substr(md5("users"."id" || ':futuredrive'), 18, 3) || '-' ||
|
|
substr(md5("users"."id" || ':futuredrive'), 21, 12)
|
|
),
|
|
"users"."id",
|
|
'11111111-1111-4111-8111-111111111111',
|
|
NULL,
|
|
NULL,
|
|
true,
|
|
CURRENT_TIMESTAMP,
|
|
CURRENT_TIMESTAMP,
|
|
CURRENT_TIMESTAMP
|
|
FROM "users"
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM "user_affiliations"
|
|
WHERE "user_affiliations"."user_id" = "users"."id"
|
|
AND "user_affiliations"."organization_id" = '11111111-1111-4111-8111-111111111111'
|
|
AND "user_affiliations"."ends_at" IS NULL
|
|
);
|