CREATE TABLE "post" (
	"id" serial NOT NULL,
	"tripcode" varchar(30) NOT NULL,
	"text" varchar(2000) NOT NULL,
	"time" TIMESTAMP NOT NULL,
	"email" varchar(50) NOT NULL,
	"title" varchar(30) NOT NULL,
	"ip_id" integer NOT NULL,
	CONSTRAINT post_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "thread" (
	"id" integer NOT NULL,
	"pinned_post_id" integer NOT NULL UNIQUE,
	"title" varchar(30) NOT NULL
) WITH (
  OIDS=FALSE
);



CREATE TABLE "attachment" (
	"data" BINARY(1073741824) NOT NULL UNIQUE,
	"id" integer NOT NULL,
	CONSTRAINT attachment_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "thread_post" (
	"post_id" integer NOT NULL,
	"thread_id" integer NOT NULL,
	CONSTRAINT thread_post_pk PRIMARY KEY ("post_id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "post_answer" (
	"post_id" integer NOT NULL,
	"answer_post_id" integer NOT NULL
) WITH (
  OIDS=FALSE
);



CREATE TABLE "board" (
	"id" serial NOT NULL,
	"title" varchar(30) NOT NULL,
	"active" BOOLEAN NOT NULL,
	CONSTRAINT board_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "user" (
	"id" serial NOT NULL,
	"name" varchar(30) NOT NULL,
	"tripcode" varchar(30) NOT NULL UNIQUE,
	"role_id" integer NOT NULL,
	"password" BINARY(20) NOT NULL UNIQUE,
	CONSTRAINT user_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "tag" (
	"id" serial NOT NULL,
	"name" varchar(30) NOT NULL UNIQUE,
	CONSTRAINT tag_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "post_tag" (
	"post_id" integer NOT NULL,
	"tag_id" integer NOT NULL
) WITH (
  OIDS=FALSE
);



CREATE TABLE "post_attachment" (
	"attachment_id" integer NOT NULL,
	"post_id" integer NOT NULL,
	"thumbnail" BINARY(1048576) NOT NULL,
	"sha1" BINARY(20) NOT NULL
) WITH (
  OIDS=FALSE
);



CREATE TABLE "user_role" (
	"id" serial NOT NULL UNIQUE,
	"title" varchar(30) NOT NULL UNIQUE,
	"view" BOOLEAN NOT NULL,
	"posting" BOOLEAN NOT NULL,
	"pinning" BOOLEAN NOT NULL,
	"editing" BOOLEAN NOT NULL,
	"deleting" BOOLEAN NOT NULL,
	"super" BOOLEAN NOT NULL,
	CONSTRAINT user_role_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "ip_address" (
	"id" serial NOT NULL UNIQUE,
	"type" integer NOT NULL,
	"address" varchar(50) NOT NULL UNIQUE,
	"role_id" integer,
	CONSTRAINT ip_address_pk PRIMARY KEY ("id")
) WITH (
  OIDS=FALSE
);






ALTER TABLE "thread_post" ADD CONSTRAINT "thread_post_fk0" FOREIGN KEY ("post_id") REFERENCES "post"("id");
ALTER TABLE "thread_post" ADD CONSTRAINT "thread_post_fk1" FOREIGN KEY ("thread_id") REFERENCES "thread"("id");

ALTER TABLE "post_answer" ADD CONSTRAINT "post_answer_fk0" FOREIGN KEY ("post_id") REFERENCES "post"("id");
ALTER TABLE "post_answer" ADD CONSTRAINT "post_answer_fk1" FOREIGN KEY ("answer_post_id") REFERENCES "post"("id");


ALTER TABLE "user" ADD CONSTRAINT "user_fk0" FOREIGN KEY ("role_id") REFERENCES "user_role"("id");


ALTER TABLE "post_tag" ADD CONSTRAINT "post_tag_fk0" FOREIGN KEY ("post_id") REFERENCES "post"("id");
ALTER TABLE "post_tag" ADD CONSTRAINT "post_tag_fk1" FOREIGN KEY ("tag_id") REFERENCES "tag"("id");

ALTER TABLE "post_attachment" ADD CONSTRAINT "post_attachment_fk0" FOREIGN KEY ("attachment_id") REFERENCES "attachment"("id");
ALTER TABLE "post_attachment" ADD CONSTRAINT "post_attachment_fk1" FOREIGN KEY ("post_id") REFERENCES "post"("id");


ALTER TABLE "ip_address" ADD CONSTRAINT "ip_address_fk0" FOREIGN KEY ("role_id") REFERENCES "user_role"("id");