DROP TABLE "PEOPLE";
CREATE TABLE "PEOPLE" (
  "id"  serial,
  "nick" character varying(16) NOT NULL,
  "passwd" character varying(14) NOT NULL,
  "name" character varying(64),
  "surname" character varying(64) NOT NULL,
  "email" character varying(96) NOT NULL,
  CONSTRAINT pk_PEOPLE PRIMARY KEY ("id"),
  CONSTRAINT 1_PEOPLE UNIQUE ("nick"),
  CONSTRAINT 2_PEOPLE UNIQUE ("email")
);

DROP TABLE "GROUPS";
CREATE TABLE "GROUPS" (
  "id"  serial,
  "name" character varying(32) NOT NULL,
  "deflevel"  integer DEFAULT '15',
  CONSTRAINT pk_GROUPS PRIMARY KEY ("id"),
  CONSTRAINT 1_GROUPS UNIQUE ("name")
);

DROP TABLE "TYPES";
CREATE TABLE "TYPES" (
  "id"  serial,
  "descr" character varying(64) NOT NULL,
  "mime" character varying(96) DEFAULT 'application/octet-stream' NOT NULL,
  CONSTRAINT pk_TYPES PRIMARY KEY ("id"),
  CONSTRAINT 1_TYPES UNIQUE ("descr")
);

DROP TABLE "STATUSES";
CREATE TABLE "STATUSES" (
  "id"  serial,
  "descr" character varying(64) NOT NULL,
  CONSTRAINT pk_STATUSES PRIMARY KEY ("id"),
  CONSTRAINT 1_STATUSES UNIQUE ("descr")
);

DROP TABLE "DOCUMENTS";
CREATE TABLE "DOCUMENTS" (
  "id"  serial,
  "title" character varying(255) NOT NULL,
  "type"  integer,
  "published" date NOT NULL,
  "updated" date NOT NULL,
  "descr" character varying(200),
  "url" character varying(128) NOT NULL,
  "status"  integer,
  CONSTRAINT pk_DOCUMENTS PRIMARY KEY ("id"),
  FOREIGN KEY ("type") REFERENCES TYPES ("id"),
  CONSTRAINT 1_DOCUMENTS UNIQUE ("url"),
  FOREIGN KEY ("status") REFERENCES STATUSES ("id")
);

DROP TABLE "AUTHORS";
CREATE TABLE "AUTHORS" (
  "id"  serial,
  "name" character varying(128) NOT NULL,
  CONSTRAINT pk_AUTHORS PRIMARY KEY ("id"),
  CONSTRAINT 1_AUTHORS UNIQUE ("name")
);

DROP TABLE "AD";
CREATE TABLE "AD" (
  "authorID"  integer,
  "docID"  integer,
  FOREIGN KEY ("authorID") REFERENCES AUTHORS ("id"),
  FOREIGN KEY ("docID") REFERENCES DOCUMENTS ("id")
);

DROP TABLE "SECTIONS";
CREATE TABLE "SECTIONS" (
  "id"  serial,
  "descr" character varying(64) NOT NULL,
  CONSTRAINT pk_SECTIONS PRIMARY KEY ("id"),
  CONSTRAINT 1_SECTIONS UNIQUE ("descr")
);

DROP TABLE "SD";
CREATE TABLE "SD" (
  "sectionID"  integer,
  "docID"  integer,
  FOREIGN KEY ("sectionID") REFERENCES SECTIONS ("id"),
  FOREIGN KEY ("docID") REFERENCES DOCUMENTS ("id")
);