f33WWDdA

1CREATE TABLE IF NOT EXISTS supplicants (
2  supplicant_id serial8,
3  name VARCHAR NOT NULL,
4  telephone_number VARCHAR NOT NULL,
5  PRIMARY KEY (supplicant_id)
6) WITHOUT OIDS;
7
8CREATE TABLE IF NOT EXISTS users (
9  user_id serial8,
10  name VARCHAR NOT NULL,
11  TYPE VARCHAR NOT NULL,
12  competence VARCHAR NOT NULL,
13  login VARCHAR NOT NULL,
14  password VARCHAR NOT NULL,
15  personal_data json,
16  PRIMARY KEY (user_id),
17  UNIQUE (login)
18) WITHOUT OIDS;
19
20CREATE TABLE IF NOT EXISTS cases (
21  case_id serial8,
22  category VARCHAR NOT NULL,
23  title VARCHAR NOT NULL,
24  description VARCHAR NOT NULL,
25  s_id int8,
26  t_id int8,
27  STATUS VARCHAR NOT NULL DEFAULT 'Ожидает назначения ответственных',
28  supplicant_id int8 NOT NULL,
29  dispatcher_id int8 NOT NULL,
30  last_update TIMESTAMP NOT NULL,
31  PRIMARY KEY (case_id)
32) WITHOUT OIDS;
33 
34CREATE TABLE IF NOT EXISTS documents (
35  document_id VARCHAR NOT NULL,
36  title VARCHAR NOT NULL,
37  case_id int8 NOT NULL,
38  PRIMARY KEY (document_id)
39) WITHOUT OIDS;
40 
41ALTER TABLE documents ADD CONSTRAINT documents_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
42ALTER TABLE cases ADD CONSTRAINT cases_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
43ALTER TABLE cases ADD CONSTRAINT cases_t_id_foreign FOREIGN KEY (t_id) REFERENCES users (user_id) ;
44ALTER TABLE cases ADD CONSTRAINT cases_supplicant_id_foreign FOREIGN KEY (supplicant_id) REFERENCES supplicants (supplicant_id) ;
45ALTER TABLE cases ADD CONSTRAINT cases_dispatcher_id_foreign FOREIGN KEY (dispatcher_id) REFERENCES users (user_id) ;
46 
47CREATE TABLE IF NOT EXISTS points_feed (
48  event_id serial8,
49  points DECIMAL NOT NULL DEFAULT 0.0,
50  s_id int8 NOT NULL,
51  t_id int8 NOT NULL,
52  reason VARCHAR NOT NULL,
53  date_time TIMESTAMP NOT NULL,
54  PRIMARY KEY (event_id)
55) WITHOUT OIDS;
56 
57ALTER TABLE points_feed ADD CONSTRAINT points_feed_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
58ALTER TABLE points_feed ADD CONSTRAINT points_feed_t_id_foreign FOREIGN KEY (t_id) REFERENCES users (user_id) ;
59 
60CREATE TABLE IF NOT EXISTS duty_feed (
61  duty_id serial8,
62  s_id int8 NOT NULL,
63  DATE DATE NOT NULL,
64  STATUS VARCHAR NOT NULL DEFAULT 'Ожидается',
65  PRIMARY KEY (duty_id)
66) WITHOUT OIDS;
67 
68ALTER TABLE duty_feed ADD CONSTRAINT duty_feed_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;