95MrfHSG

1CREATE TABLE IF NOT EXISTS supplicants ( 
2  supplicant_id int8 not null,
3  name varchar,
4  address varchar,
5  telephone_number varchar,
6  email varchar,
7  PRIMARY KEY (supplicant_id)
8) WITHOUT OIDS;
9
10CREATE TABLE IF NOT EXISTS cases ( 
11  case_id int8 not null,
12  category varchar,
13  description text,
14  supplicant_id int8,
15  case_status varchar not null,
16  operator_id int8,
17  industry_affiliation varchar,
18  source_of_knowledge_jc varchar,
19  application_date_time timestamp,
20  meeting_date_time timestamp,
21  done bool not null,
22  PRIMARY KEY (case_id)
23) WITHOUT OIDS;
24
25ALTER TABLE cases ADD CONSTRAINT cases_supplicant_id_foreign FOREIGN KEY (supplicant_id) REFERENCES supplicants (supplicant_id) ;
26
27CREATE TABLE IF NOT EXISTS documents ( 
28  document_id int8 not null,
29  title text,
30  case_id int8 not null,
31  type varchar,
32  data_id int8,
33  PRIMARY KEY (document_id)
34) WITHOUT OIDS;
35
36ALTER TABLE documents ADD CONSTRAINT documents_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
37
38CREATE TABLE IF NOT EXISTS site_users ( 
39  user_id int8 not null,
40  name varchar,
41  role int8,
42  st_login varchar not null,
43  personal_data text,
44  password_hash varchar,
45  cookie text,
46  PRIMARY KEY (user_id)
47) WITHOUT OIDS;
48
49CREATE TABLE IF NOT EXISTS applications ( 
50  application_id int8 not null,
51  title varchar,
52  case_id int8 not null,
53  description text,
54  addressee_user_id int8 not null,
55  status varchar not null,
56  date_time timestamp,
57  PRIMARY KEY (application_id)
58) WITHOUT OIDS;
59
60ALTER TABLE applications ADD CONSTRAINT applications_addressee_user_id_foreign FOREIGN KEY (addressee_user_id) REFERENCES site_users (user_id) ;
61ALTER TABLE applications ADD CONSTRAINT applications_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
62
63CREATE TABLE IF NOT EXISTS administration ( 
64  admin_id int8 not null,
65  name varchar not null,
66  type varchar,
67  email varchar,
68  user_id int8 not null,
69  PRIMARY KEY (admin_id)
70) WITHOUT OIDS;
71
72ALTER TABLE administration ADD CONSTRAINT administration_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
73
74CREATE TABLE IF NOT EXISTS notifications ( 
75  notification_id int8 not null,
76  text text not null,
77  status varchar not null,
78  user_id int8 not null,
79  date_time timestamp,
80  event_data json,
81  PRIMARY KEY (notification_id)
82) WITHOUT OIDS;
83
84ALTER TABLE notifications ADD CONSTRAINT notifications_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
85
86CREATE TABLE IF NOT EXISTS teachers ( 
87  teacher_id int8 not null,
88  competence varchar,
89  user_id int8 not null,
90  PRIMARY KEY (teacher_id)
91) WITHOUT OIDS;
92
93ALTER TABLE teachers ADD CONSTRAINT teachers_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
94
95CREATE TABLE IF NOT EXISTS case_teachers_relations ( 
96  relation_id int8 not null,
97  case_id int8 not null,
98  teacher_id int8 not null,
99  role varchar,
100  PRIMARY KEY (relation_id)
101) WITHOUT OIDS;
102
103ALTER TABLE case_teachers_relations ADD CONSTRAINT case_teachers_relations_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
104ALTER TABLE case_teachers_relations ADD CONSTRAINT case_teachers_relations_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
105
106CREATE TABLE IF NOT EXISTS students ( 
107  student_id int8 not null,
108  teacher_id int8,
109  functional_group varchar,
110  points decimal,
111  user_id int8 not null,
112  subgroup_name varchar,
113  PRIMARY KEY (student_id)
114) WITHOUT OIDS;
115
116CREATE TABLE IF NOT EXISTS students_subgroup ( 
117  name varchar not null,
118  admin_id int8 not null,
119  PRIMARY KEY (name)
120) WITHOUT OIDS;
121
122ALTER TABLE students ADD CONSTRAINT students_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
123ALTER TABLE students ADD CONSTRAINT students_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
124ALTER TABLE students ADD CONSTRAINT students_subgroup_name_foreign FOREIGN KEY (subgroup_name) REFERENCES students_subgroup (name) ;
125
126ALTER TABLE students_subgroup ADD CONSTRAINT students_subgroup_admin_id_foreign FOREIGN KEY (admin_id) REFERENCES students (student_id) ;
127
128CREATE TABLE IF NOT EXISTS case_student_relations ( 
129  relation_id int8 not null,
130  case_id int8 not null,
131  student_id int8 not null,
132  role varchar,
133  PRIMARY KEY (relation_id)
134) WITHOUT OIDS;
135
136ALTER TABLE case_student_relations ADD CONSTRAINT case_student_relations_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
137ALTER TABLE case_student_relations ADD CONSTRAINT case_student_relations_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
138
139CREATE TABLE IF NOT EXISTS rejected_requests ( 
140  request_id int8 not null,
141  reason text,
142  operator_id int8,
143  supplicant_name varchar,
144  request_text text,
145  date_time timestamp,
146  PRIMARY KEY (request_id)
147) WITHOUT OIDS;
148
149ALTER TABLE rejected_requests ADD CONSTRAINT rejected_requests_operator_id_foreign FOREIGN KEY (operator_id) REFERENCES students (student_id) ;
150
151CREATE TABLE IF NOT EXISTS points_feed ( 
152  event_id int8 not null,
153  student_id int8 not null,
154  teacher_id int8 not null,
155  points decimal not null,
156  reason text,
157  date_time timestamp,
158  PRIMARY KEY (event_id)
159) WITHOUT OIDS;
160
161ALTER TABLE points_feed ADD CONSTRAINT points_feed_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
162ALTER TABLE points_feed ADD CONSTRAINT points_feed_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
163
164CREATE TABLE IF NOT EXISTS cases_feed ( 
165  event_id int8 not null,
166  case_id int8 not null,
167  case_status varchar not null,
168  date_time timestamp,
169  event_data json,
170  done bool not null,
171  PRIMARY KEY (event_id)
172) WITHOUT OIDS;
173
174ALTER TABLE cases_feed ADD CONSTRAINT cases_feed_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
175
176CREATE TABLE IF NOT EXISTS duty_roster ( 
177  duty_id int8 not null,
178  student_id int8 not null,
179  class_room varchar,
180  start_date_time timestamp,
181  finish_date_time timestamp,
182  PRIMARY KEY (duty_id)
183) WITHOUT OIDS;
184
185ALTER TABLE duty_roster ADD CONSTRAINT duty_roster_student_id_foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;