CREATE TYPE notifyType AS ENUM('email', 'message');


CREATE TABLE "blog_black_list" (
  "ID" BIGSERIAL NOT NULL,
  "userID" int8 NOT NULL,
  PRIMARY KEY("ID")
);


CREATE TABLE "friends_flags" (
  "userID" int8 NOT NULL,
  "friendID" int8 NOT NULL,
  "flag" bool NOT NULL DEFAULT False,
  PRIMARY KEY("userID","friendID")
);


CREATE TABLE "friends" (
  "userID" int8 NOT NULL,
  "friendID" int8 NOT NULL,
  PRIMARY KEY("userID","friendID")
);


CREATE TABLE "blog_subscribe" (
  "ID" BIGSERIAL NOT NULL,
  "userID" int8 NOT NULL,
  "blogID" int8 NOT NULL,
  "notify_type" notifyType NOT NULL DEFAULT 'email',
  PRIMARY KEY("ID")
);


CREATE TABLE "blog" (
  "ID" BIGSERIAL NOT NULL,
  "userID" int8 NOT NULL,
  "title" char NOT NULL,
  "text" text NOT NULL,
  "description" char,
  "date" timestamp with time zone NOT NULL,
  "IP" inet NOT NULL,
  "is_comments" bool NOT NULL DEFAULT True,
  "comment_for_friends" bool NOT NULL DEFAULT False,
  "read_for_friends" bool NOT NULL DEFAULT False,
  "deny_for_all" bool NOT NULL DEFAULT False,
  PRIMARY KEY("ID")
);

COMMENT ON COLUMN "blog"."title" IS 'тема блога';

CREATE TABLE "blog_comment" (
  "ID" BIGSERIAL NOT NULL,
  "blogID" int8 NOT NULL,
  "userID" int8 NOT NULL,
  "body" text NOT NULL,
  "date" timestamp with time zone NOT NULL,
  "IP" inet NOT NULL,
  PRIMARY KEY("ID")
);


CREATE TABLE "users" (
  "ID" BIGSERIAL NOT NULL,
  "nickname" char NOT NULL,
  "name" char NOT NULL,
  PRIMARY KEY("ID")
);



ALTER TABLE "blog_black_list" ADD CONSTRAINT "Ref_-01" FOREIGN KEY ("userID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "friends_flags" ADD CONSTRAINT "Ref_01" FOREIGN KEY ("userID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "friends_flags" ADD CONSTRAINT "Ref_02" FOREIGN KEY ("friendID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "friends" ADD CONSTRAINT "Ref_03" FOREIGN KEY ("userID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "friends" ADD CONSTRAINT "Ref_04" FOREIGN KEY ("friendID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "blog_subscribe" ADD CONSTRAINT "Ref_-07" FOREIGN KEY ("blogID")
    REFERENCES "blog"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "blog_subscribe" ADD CONSTRAINT "Ref_-06" FOREIGN KEY ("userID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "blog" ADD CONSTRAINT "Ref_-08" FOREIGN KEY ("userID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "blog_comment" ADD CONSTRAINT "Ref_-05" FOREIGN KEY ("userID")
    REFERENCES "users"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;

ALTER TABLE "blog_comment" ADD CONSTRAINT "Ref_00" FOREIGN KEY ("blogID")
    REFERENCES "blog"("ID")
      MATCH SIMPLE
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      NOT DEFERRABLE;