26-medium-database.sql

-- Drop existing resources
DROP TABLE countries CASCADE;

DROP TABLE users CASCADE;

DROP SEQUENCE user_sequence;

-- Create resource
CREATE TABLE "users" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "username" varchar UNIQUE NOT NULL,
  "password" varchar NOT NULL,
  "name" varchar NOT NULL,
  "role" varchar NOT NULL,
  "gender" varchar(10) NOT NULL,
  "avatar" varchar,
  "created_at" timestamp DEFAULT 'now()',
  "updated_at" timestamp ON UPDATE
);

CREATE TABLE "posts" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "title" varchar(200) DEFAULT '',
  "body" text DEFAULT '',
  "og_image" varchar,
  "slug" varchar UNIQUE NOT NULL,
  "published" boolean,
  "created_by" integer,
  "created_at" timestamp DEFAULT 'now()',
  "updated_at" timestamp
);

CREATE TABLE "claps" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "post_id" integer,
  "user_id" integer,
  "counter" integer DEFAULT 0,
  "created_at" timestamp DEFAULT 'now()',
  "updated_at" timestamp
);

CREATE TABLE "comments" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "post_id" integer,
  "user_id" integer,
  "content" text,
  "parent_id" integer,
  "visible" boolean,
  "created_at" timestamp DEFAULT 'now()',
  "updated_at" timestamp
);

CREATE TABLE "user_lists" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "user_id" integer,
  "title" varchar(100),
  "created_at" timestamp DEFAULT 'now()',
  "updated_at" timestamp
);

CREATE TABLE "user_list_entry" (
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "user_list_id" integer,
  "post_id" integer,
  "created_at" timestamp DEFAULT 'now()',
  "updated_at" timestamp
);

CREATE UNIQUE INDEX ON "claps" ("post_id", "user_id");

CREATE INDEX ON "claps" ("post_id");

CREATE INDEX ON "comments" ("post_id");

CREATE INDEX ON "comments" ("visible");

CREATE UNIQUE INDEX ON "user_lists" ("user_id", "title");

CREATE INDEX ON "user_lists" ("user_id");

ALTER TABLE "posts" ADD FOREIGN KEY ("created_by") REFERENCES "users" ("id");

ALTER TABLE "claps" ADD FOREIGN KEY ("post_id") REFERENCES "posts" ("id");

ALTER TABLE "claps" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");

ALTER TABLE "comments" ADD FOREIGN KEY ("post_id") REFERENCES "posts" ("id");

ALTER TABLE "comments" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");

ALTER TABLE "comments" ADD FOREIGN KEY ("parent_id") REFERENCES "comments" ("id");

ALTER TABLE "user_lists" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");

ALTER TABLE "user_list_entry" ADD FOREIGN KEY ("user_list_id") REFERENCES "user_lists" ("id");

ALTER TABLE "user_list_entry" ADD FOREIGN KEY ("post_id") REFERENCES "posts" ("id");