練習題目

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE "USER" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "name" varchar(50) NOT NULL,
  "email" varchar(320) UNIQUE NOT NULL,
  "role" varchar(20) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);
CREATE TABLE "SKILL" (
  "id" uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  "name" varchar(50) UNIQUE NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO "SKILL" (name) VALUES ('重訓'), ('瑜伽'), ('有氧運動'), ('復健訓練');

CREATE TABLE "COACH" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "experience_years" integer,
  "description" text,
  "profile_image_url" varchar(2048),
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  UNIQUE("user_id")
);

CREATE TABLE "COACH_LINK_SKILL" (
  "coach_id" uuid NOT NULL REFERENCES "COACH"(id),
  "skill_id" uuid NOT NULL REFERENCES "SKILL"(id),
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  PRIMARY KEY ("coach_id", "skill_id")
);

CREATE TABLE "CREDIT_PACKAGE" (
  "id" serial PRIMARY KEY,
  "name" varchar(50) NOT NULL,
  "credit_amount" integer NOT NULL,
  "price" numeric(10,2) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "CREDIT_PURCHASE" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "credit_package_id" integer NOT NULL REFERENCES "CREDIT_PACKAGE"(id),
  "purchased_credits" integer NOT NULL,
  "price_paid" numeric(10,2) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "purchase_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "COURSE" (
  "id" serial PRIMARY KEY,
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "skill_id" uuid NOT NULL REFERENCES "SKILL"(id),
  "name" varchar(100) NOT NULL,
  "description" text,
  "start_at" timestamp NOT NULL,
  "end_at" timestamp NOT NULL,
  "max_participants" integer NOT NULL,
  "meeting_url" varchar(2048) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "COURSE_BOOKING" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "course_id" integer NOT NULL REFERENCES "COURSE"(id),
  "booking_at" timestamp NOT NULL,
  "status" varchar(20) NOT NULL,
  "join_at" timestamp,
  "leave_at" timestamp,
  "cancelled_at" timestamp,
  "cancellation_reason" varchar(255),
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "BLOG_POST" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "title" varchar(255) NOT NULL,
  "content" text NOT NULL,
  "featured_image_url" varchar(2048),
  "category" varchar(20) NOT NULL,
  "spend_minutes" smallint NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE TABLE "COMMENT" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (gen_random_uuid()),
  "blog_post_id" uuid NOT NULL REFERENCES "BLOG_POST"(id),
  "user_id" uuid NOT NULL REFERENCES "USER"(id),
  "content" text NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updated_at" timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);