· 5 years ago · Aug 18, 2020, 08:14 AM
1DROP DATABASE IF EXISTS ${const_env.database_name};
2CREATE DATABASE ${const_env.database_name} character set UTF8 collate utf8_bin;
3
4use ${const_env.database_name};
5
6CREATE TABLE TOPIC(
7 topic_id NVARCHAR(50) PRIMARY KEY NOT NULL,
8 topic_name text charset utf8mb4 not null ,
9 parent NVARCHAR(50),
10 level int not null,
11 branch NVARCHAR(50) not null,
12 create_at long NOT NULL,
13 language text,
14 highlight_level int default 1,
15 type int default 2,
16 constraint fk_parent_to_topic foreign key (parent) references TOPIC(topic_id)
17);
18
19CREATE TABLE AUTHOR (
20 id varchar(50) NOT NULL,
21 name text charset utf8mb4 null,
22 avatar text COLLATE utf8_bin,
23 doctor_id varchar(50) DEFAULT NULL,
24 created_at mediumtext COLLATE utf8_bin,
25 status tinyint(1) DEFAULT '1',
26 topic_id varchar(50) DEFAULT NULL,
27 PRIMARY KEY (id),
28 KEY FK_AUTHOR_TO_TOPIC (topic_id),
29 CONSTRAINT FK_AUTHOR_TO_TOPIC FOREIGN KEY (topic_id) REFERENCES TOPIC (topic_id)
30)
31
32CREATE TABLE NEWS_HEADER(
33 news_header_id NVARCHAR(50) PRIMARY KEY NOT NULL,
34 title text charset utf8mb4 not null ,
35 image TEXT,
36 video TEXT,
37 summary text charset utf8mb4 not null ,
38 create_at long NOT NULL,
39 topic_id NVARCHAR(50) NOT NULL,
40 author_id NVARCHAR(50) NULL,
41 status_publish boolean NOT NULL,
42 number_viewers int default 0,
43 highlight_level INT ,
44 video_type TEXT,
45 title_en text charset utf8mb4 null ,
46 title_fr text charset utf8mb4 null ,
47 summary_en text charset utf8mb4 null ,
48 summary_fr text charset utf8mb4 null ,
49 language text,
50 type int default 2,
51 status tinyint(1) default 1,
52 CONSTRAINT FK_NEWS_TO_TOPIC FOREIGN KEY (topic_id) REFERENCES TOPIC(topic_id)
53);
54
55CREATE TABLE NEWS_RECOMMENDATION (
56 news_recommendation_id varchar(50) CHARACTER SET utf8 NOT NULL PRIMARY KEY,
57 user_id varchar(50) CHARACTER SET utf8 NOT NULL,
58 viewed tinyint(4) NOT NULL,
59 news_header_id varchar(50) CHARACTER SET utf8 NOT NULL,
60 rating float NOT NULL,
61 type_member text COLLATE utf8_bin,
62 CONSTRAINT FK_NEWS_RECOMMENDATION_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER (news_header_id)
63);
64
65CREATE TABLE NEWS_CONTENT(
66 news_content_id NVARCHAR(50) PRIMARY KEY NOT NULL,
67 content TEXT charset utf8mb4 not null ,
68 create_at long NOT NULL,
69 news_header_id NVARCHAR(50) NOT NULL,
70 status_publish boolean NOT NULL,
71 content_en text,
72 content_fr text,
73 CONSTRAINT FK_NEWS_CONTENT_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER(news_header_id)
74);
75
76
77CREATE TABLE NEWS_COMMENT(
78 news_comment_id NVARCHAR(50) PRIMARY KEY NOT NULL,
79 user_id NVARCHAR(50) NOT NULL,
80 comment TEXT charset utf8mb4 not null ,
81 create_at long NOT NULL,
82 status_publish boolean NOT NULL ,
83 news_header_id NVARCHAR(50) NOT NULL,
84 type_member text,
85 CONSTRAINT FK_NEWS_CONMENT_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER(news_header_id)
86);
87
88
89CREATE TABLE NEWS_RATING(
90 news_rating_id NVARCHAR(50) PRIMARY KEY NOT NULL,
91 user_id NVARCHAR(50) NOT NULL,
92 status int NOT NULL,
93 create_at long NOT NULL,
94 news_header_id NVARCHAR(50) NOT NULL,
95 type_member text,
96 type_rating text,
97 comment_id NVARCHAR(50),
98 CONSTRAINT FK_NEWS_RATING_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER(news_header_id)
99);
100
101CREATE TABLE NEWS_VIEW(
102 id NVARCHAR(50) PRIMARY KEY NOT NULL,
103 user_id NVARCHAR(50),
104 news_header_id NVARCHAR(50),
105 count INT,
106 type_member VARCHAR(50),
107 device_id VARCHAR(255),
108 UNIQUE KEY unique_user_header (user_id,news_header_id),
109 UNIQUE KEY unique_device_header (device_id,news_header_id),
110 CONSTRAINT FK_VIEWS_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER(news_header_id)
111);
112
113CREATE TABLE NEWS_SHARE(
114 news_share_id NVARCHAR(50) PRIMARY KEY NOT NULL,
115 from_user_id NVARCHAR(50) NOT NULL,
116 to_user_id NVARCHAR(50),
117 comment TEXT charset utf8mb4 null ,
118 status boolean NOT NULL,
119 create_at long NOT NULL,
120 news_header_id NVARCHAR(50) NOT NULL,
121 from_type text,
122 to_type text,
123 CONSTRAINT FK_NEWS_SHARE_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER(news_header_id)
124);
125
126
127CREATE TABLE TOPIC_TRACKING(
128 topic_tracking_id NVARCHAR(50) PRIMARY KEY NOT NULL,
129 user_id NVARCHAR(50) NOT NULL,
130 topic_id NVARCHAR(50) NOT NULL,
131 status boolean NOT NULL,
132 create_at long NOT NULL,
133 type_member text,
134 CONSTRAINT FK_TOPIC_TRACKING_TO_TOPIC FOREIGN KEY (topic_id) REFERENCES TOPIC(topic_id),
135 UNIQUE KEY UNIQUE_TOPIC_TRACKING (user_id,topic_id)
136);
137
138CREATE TABLE RELATION_NEWS_TOPIC(
139 id nvarchar(50) primary key not null,
140 news_header_id nvarchar(50) not null,
141 topic_id nvarchar(50) not null,
142 status tinyint(1) default 1,
143 created_at long not null,
144 CONSTRAINT FK_RNT_TO_NEWS_HEADER FOREIGN KEY (news_header_id) REFERENCES NEWS_HEADER (news_header_id),
145 CONSTRAINT FK_RNT_TO_TOPIC FOREIGN KEY (topic_id) REFERENCES TOPIC (topic_id)
146);
147
148CREATE TABLE PROFESSIONAL(
149 id VARCHAR(50) PRIMARY KEY NOT NULL,
150 name text not null,
151 name_en text,
152 name_fr text
153);
154
155CREATE TABLE SPECIALITY (
156 id NVARCHAR(45) PRIMARY KEY NOT NULL,
157 name text NOT NULL,
158 professional_id VARCHAR(50) NOT NULL,
159 name_en text,
160 name_fr text,
161 CONSTRAINT FK_SPECIALITY_TO_PROFESSIONAL FOREIGN KEY (professional_id) REFERENCES PROFESSIONAL(id)
162);
163
164CREATE TABLE CERTIFICATION(
165 id NVARCHAR(45) not null,
166 speciality_id NVARCHAR(45) not null,
167 professional_id VARCHAR(50) not null,
168 image NVARCHAR(255) not null,
169 PRIMARY KEY (id),
170 CONSTRAINT FK_CERTIFICATION_TO_SPECIALITY FOREIGN KEY (speciality_id) REFERENCES SPECIALITY(id),
171 CONSTRAINT FK_CERTIFICATION_TO_PROFESSIONAL FOREIGN KEY (professional_id) REFERENCES PROFESSIONAL(id)
172);
173
174CREATE TABLE ADDRESS (
175 id varchar(50) NOT NULL,
176 name text ,
177 address_type int(11) DEFAULT NULL,
178 type text ,
179 slug text ,
180 name_with_type text ,
181 code int(11) DEFAULT NULL,
182 path text ,
183 path_with_type text ,
184 parent_code text ,
185 PRIMARY KEY (id)
186);
187
188CREATE TABLE DOCTOR (
189 id NVARCHAR(45) NOT NULL,
190 name text charset utf8mb4 not null ,
191 age text NOT NULL,
192 email text NOT NULL,
193 phone_number VARCHAR(45) NOT NULL,
194 birthday NVARCHAR(45) not null,
195 password text not null,
196 working_address text not null,
197 certification_id NVARCHAR(45) ,
198 create_at long not null,
199 verify_code varchar(50),
200 verified TINYINT(1),
201 avatar text,
202 type_deepcare TINYINT(1),
203 sex TINYINT(1),
204 cover text,
205 first_name text charset utf8mb4 not null ,
206 last_name text charset utf8mb4 not null ,
207 academic_rank_name text,
208 degree_name text,
209 language_name text,
210 training_process text,
211 research_work text,
212 working_process text,
213 organization text,
214 ethnic text,
215 device_token text,
216 year_start_work long,
217 description text,
218 rating float default 0,
219 province text,
220 county text,
221 wards text,
222 address text,
223 thanks int default 0,
224 consultancy int default 0,
225 verify_code_create_at long,
226 verify_code_used tinyint(1) default 0,
227 address_string text,
228 status tinyint(1) default 0,
229 OS text,
230 language text,
231 timeZone text,
232 APNS_Token text,
233 login_time long,
234 device_name text charset utf8mb4 null ,
235 select_topic tinyint(1) default 0,
236 highlight tinyint(1) default 0,
237 receive_message tinyint(1) default 0,
238 receive_request tinyint(1) default 0,
239 PRIMARY KEY (id),
240 CONSTRAINT phone_number_unique UNIQUE (phone_number),
241 CONSTRAINT FOREIGN KEY (certification_id) references CERTIFICATION(id)
242);
243
244CREATE TABLE HOSPITAL (
245 id nvarchar(50) primary key not null,
246 name text not null,
247 address text not null,
248 note text ,
249 fix_number text not null,
250 position text ,
251 work_time text ,
252 time_morning int ,
253 time_afternoon int,
254 created_at long ,
255 avatar text,
256 level int default 0,
257 parent text default null,
258 branch text default null,
259 booking_confirm tinyint(1) default 0,
260 website text COLLATE utf8_bin,
261 rating float DEFAULT '0',
262 \`long\` float DEFAULT NULL,
263 lat float DEFAULT NULL,
264 director text COLLATE utf8_bin,
265 description text charset utf8mb4 null ,
266 speciality text COLLATE utf8_bin,
267 highlight int(11) DEFAULT '0',
268 topic_id nvarchar(50),
269 hidden_doctor tinyint(1) default 0
270);
271
272CREATE TABLE POSITIONS(
273 id nvarchar(50) primary key not null,
274 name text ,
275 created_at long ,
276 status TINYINT(1) default 1,
277 name_en text,
278 name_fr text
279
280);
281
282CREATE TABLE RELATION_DOCTOR_HOSPITAL (
283 id varchar(50) CHARACTER SET utf8 NOT NULL,
284 doctor_id varchar(50) CHARACTER SET utf8 NOT NULL,
285 hospital_id varchar(50) CHARACTER SET utf8 NOT NULL,
286 created_at mediumtext COLLATE utf8_bin,
287 examination_price int(11) DEFAULT NULL,
288 online_price int(11) DEFAULT NULL,
289 department text COLLATE utf8_bin,
290 is_default tinyint(1) DEFAULT '0',
291 PRIMARY KEY (id),
292 KEY fk_doctor_to_hospital (hospital_id),
293 KEY fk_rdh_to_doctor (doctor_id),
294 CONSTRAINT fk_doctor_to_hospital FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (id),
295 CONSTRAINT fk_rdh_to_doctor FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
296);
297
298
299CREATE TABLE RDH_POSITION (
300 id nvarchar(50) primary key not null,
301 position_id nvarchar(50) not null,
302 rdh_id nvarchar(50) not null,
303 created_at long ,
304 CONSTRAINT fk_RDHP_to_positions foreign key (position_id) references POSITIONS(id),
305 CONSTRAINT fk_RDHP_to_RDH foreign key (rdh_id) references RELATION_DOCTOR_HOSPITAL(id)
306);
307
308CREATE TABLE RELATION_DOCTOR (
309 id nvarchar(50) primary key not null,
310 doctor_id nvarchar(50) not null,
311 rdhp_id nvarchar(50) not null,
312 created_at long ,
313 CONSTRAINT fk_RD_to_RDHP foreign key (rdhp_id) references RDH_POSITION(id),
314 CONSTRAINT fk_RD_to_DOCTOR foreign key (doctor_id) references DOCTOR(id)
315);
316
317
318CREATE TABLE ROLE (
319 id varchar(50) NOT NULL,
320 name text ,
321 type text,
322 name_en text,
323 name_fr text,
324 role_type text,
325 PRIMARY KEY (id)
326);
327
328CREATE TABLE ASSIGN_DEEPCARE_ROLE (
329 id nvarchar(50) PRIMARY KEY NOT NULL,
330 doctor_id NVARCHAR(50) NOT NULL,
331 role_id varchar(50) NOT NULL,
332 status boolean default true,
333 CONSTRAINT FK_ASSIGN_DEEPCARE_ROLE_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
334);
335
336
337CREATE TABLE POSITION_ROLE(
338 id nvarchar(50) primary key,
339 position_id nvarchar(50),
340 role_id nvarchar(50),
341 CONSTRAINT FK_POSITION_ROLE_TO_POSITIONS FOREIGN KEY (position_id) REFERENCES POSITIONS (id),
342 CONSTRAINT FK_POSITION_ROLE_TO_ROLE FOREIGN KEY (role_id) REFERENCES ROLE (id)
343);
344
345CREATE TABLE ROLE_MANAGE (
346 id nvarchar(50) NOT NULL,
347 rdhp_id nvarchar(50) NOT NULL,
348 rd_id nvarchar(50) DEFAULT NULL,
349 created_at mediumtext ,
350 role_id nvarchar(50) DEFAULT NULL,
351 enable tinyint(1) default 1,
352 PRIMARY KEY (id),
353 CONSTRAINT FK_ROLE_MANAGE_TO_ROLE FOREIGN KEY (role_id) REFERENCES ROLE (id),
354 CONSTRAINT fk_Role_to_RD FOREIGN KEY (rd_id) REFERENCES RELATION_DOCTOR (id),
355 CONSTRAINT fk_Role_to_RDHP FOREIGN KEY (rdhp_id) REFERENCES RDH_POSITION (id)
356);
357
358CREATE TABLE WORK_REQUEST(
359 id nvarchar(50) NOT NULL,
360 rdhp_from nvarchar(50) DEFAULT NULL,
361 rdhp_to nvarchar(50) DEFAULT NULL,
362 from_type varchar(50) DEFAULT NULL,
363 to_type varchar(50) DEFAULT NULL,
364 status text,
365 rd_id nvarchar(50),
366 created_at mediumtext,
367 enable tinyint(1),
368 PRIMARY KEY (id),
369 CONSTRAINT FK_WORK_REQUEST2_TO_RDHP FOREIGN KEY (rdhp_to) REFERENCES RDH_POSITION (id),
370 CONSTRAINT FK_WORK_REQUEST_TO_RDHP FOREIGN KEY (rdhp_from) REFERENCES RDH_POSITION (id)
371);
372
373CREATE TABLE WORK_REQUEST_DETAIL(
374 id nvarchar(50) NOT NULL,
375 work_request_id nvarchar(50) DEFAULT NULL,
376 role_id nvarchar(50) DEFAULT NULL,
377 PRIMARY KEY (id),
378 CONSTRAINT FK_WORK_REQUEST_DETAIL_TO_WORK_REQUEST FOREIGN KEY (work_request_id) REFERENCES WORK_REQUEST (id)
379);
380
381CREATE TABLE USER(
382 id NVARCHAR(50) not null,
383 name text charset utf8mb4 not null ,
384 email text not null,
385 age text not null,
386 avatar text,
387 phone text,
388 prefix varchar(10) default "+84",
389 sex boolean,
390 date_birth long,
391 your_doctor text,
392 general_status int,
393 parent_id NVARCHAR(50),
394 cover text,
395 password text,
396 first_name text,
397 last_name text,
398 province text,
399 county text,
400 wards text,
401 address text,
402 verify_code text,
403 verified tinyint(1),
404 verify_code_create_at long,
405 verify_code_used tinyint(1) default 0,
406 device_token text,
407 address_string text charset utf8mb4 null ,
408 status tinyint(1) default 1,
409 OS text,
410 language text,
411 timeZone text,
412 APNS_Token text,
413 login_time long,
414 created_time bigint,
415 device_name text charset utf8mb4 null ,
416 select_topic tinyint(1) default 0,
417 receive_message tinyint(1) default 0,
418 receive_request tinyint(1) default 0,
419 primary key (id)
420);
421
422CREATE TABLE RELATION_USER_HOSPITAL(
423 id nvarchar(50) NOT NULL,
424 user_id nvarchar(50) DEFAULT NULL,
425 hospital_id nvarchar(50) DEFAULT NULL,
426 created_at long ,
427 doctor_id nvarchar(50),
428 updated_at long,
429 PRIMARY KEY (id),
430 CONSTRAINT FK_RUH_TO_HOSPITAL FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (id),
431 CONSTRAINT FK_RUH_TO_USER FOREIGN KEY (user_id) REFERENCES USER (id),
432 CONSTRAINT FK_RUH_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
433);
434
435CREATE TABLE FRIEND(
436 id nvarchar(50) NOT NULL,
437 user_from_id nvarchar(50) DEFAULT NULL,
438 doctor_from_id nvarchar(50) DEFAULT NULL,
439 user_to_id nvarchar(50) DEFAULT NULL,
440 doctor_to_id nvarchar(50) DEFAULT NULL,
441 type_from text ,
442 type_to text ,
443 PRIMARY KEY (id),
444 CONSTRAINT FK_FRIEND_TO_DOCTOR FOREIGN KEY (doctor_from_id) REFERENCES DOCTOR (id),
445 CONSTRAINT FK_FRIEND_TO_DOCTOR2 FOREIGN KEY (doctor_to_id) REFERENCES DOCTOR (id),
446 CONSTRAINT FK_FRIEND_TO_USER FOREIGN KEY (user_from_id) REFERENCES USER (id),
447 CONSTRAINT FK_FRIEND_TO_USER2 FOREIGN KEY (user_to_id) REFERENCES USER (id)
448);
449
450CREATE TABLE HOSPITAL_SPECIALITY(
451 id nvarchar(50) NOT NULL,
452 name text,
453 name_en text,
454 name_fr text,
455 professional_id varchar(50) DEFAULT NULL,
456 status tinyint(1) DEFAULT '1',
457 created_at mediumtext,
458 ICD text,
459 PRIMARY KEY (id),
460 CONSTRAINT FK_HOSPITAL_SPECIALITY_TO_PROFESSIONAL FOREIGN KEY (professional_id) REFERENCES PROFESSIONAL (id)
461);
462
463CREATE TABLE RELATION_HOSPITAL_SPECIALITY (
464 id varchar(50) CHARACTER SET utf8 NOT NULL,
465 hospital_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
466 speciality_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
467 total int(11) DEFAULT '0',
468 status tinyint(1) DEFAULT '1',
469 created_at mediumtext COLLATE utf8_bin,
470 description text COLLATE utf8_bin,
471 image text COLLATE utf8_bin,
472 highlight tinyint(1) DEFAULT '0',
473 PRIMARY KEY (id),
474 KEY FK_RHS_TO_HOSPITAL (hospital_id),
475 KEY FK_RHS_TO_SPECIALITY (speciality_id),
476 CONSTRAINT FK_RHS_TO_SPECIALITY FOREIGN KEY (speciality_id) REFERENCES HOSPITAL_SPECIALITY (id)
477);
478
479
480CREATE TABLE RELATION_DOCTOR_SPECIALITY (
481 id varchar(50) CHARACTER SET utf8 NOT NULL,
482 doctor_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
483 speciality_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
484 status tinyint(1) DEFAULT NULL,
485 created_at mediumtext COLLATE utf8_bin,
486 PRIMARY KEY (id),
487 KEY FK_RDS_TO_DOCTOR (doctor_id),
488 KEY FK_RDS_TO_RHS (speciality_id),
489 CONSTRAINT FK_RDS_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id),
490 CONSTRAINT FK_RDS_TO_RHS FOREIGN KEY (speciality_id) REFERENCES RELATION_HOSPITAL_SPECIALITY (id)
491);
492
493CREATE TABLE RELATIONSHIP (
494 id NVARCHAR(50) PRIMARY KEY,
495 name text,
496 name_en text,
497 status tinyint(1),
498 created_at long,
499 name_fr text
500);
501
502CREATE TABLE FAMILY (
503 id NVARCHAR(45) PRIMARY KEY,
504 patient_id VARCHAR(45),
505 name text charset utf8mb4 null ,
506 date_birth long,
507 sex tinyint(1),
508 email text,
509 phone text,
510 prefix varchar(10) default "+84",
511 address text,
512 address_string text,
513 avatar text,
514 cover text,
515 relation nvarchar(50),
516 status tinyint(1) default 1,
517 created_at long,
518 first_name text charset utf8mb4 null ,
519 last_name text charset utf8mb4 null ,
520 ethnic nvarchar(50) default "Kinh",
521 CONSTRAINT FK_FAMILY_TO_RELATIONSHIP FOREIGN KEY (relation) REFERENCES RELATIONSHIP(id)
522);
523
524
525CREATE TABLE QUESTION (
526 id nvarchar(45) NOT NULL,
527 user_id nvarchar(45) NOT NULL,
528 family_id nvarchar(45),
529 title nvarchar(45) NOT NULL,
530 question text charset utf8mb4 not null ,
531 object_images nvarchar(255) NOT NULL,
532 status nvarchar(45) NOT NULL,
533 status_publish INT NOT NULL,
534 status_approve TINYINT NOT NULL,
535 reason_cancel text charset utf8mb4 null ,
536 speciality_id nvarchar(45) NOT NULL,
537 created_at long NOT NULL,
538 type_member nvarchar(45) NOT NULL,
539 language nvarchar(45),
540 hide_name tinyint(1) default 0,
541 alias text,
542 type varchar(255) default 'CAU_HOI',
543 PRIMARY KEY (id),
544 constraint fk_question_to_speciality foreign key (speciality_id) references SPECIALITY(id),
545 constraint fk_question_to_family foreign key (family_id) references FAMILY(id)
546 );
547
548
549CREATE TABLE ANSWERS (
550 id nvarchar(45) not null,
551 user_id nvarchar(45) not null,
552 answer text charset utf8mb4 not null ,
553 images text not null,
554 question_id nvarchar(45),
555 answer_id nvarchar(45),
556 created_at long not null,
557 type_member nvarchar(45) not null,
558 Primary key (id),
559 constraint fk_answer_to_question foreign key(question_id) references QUESTION(id),
560 constraint FK_ANSWERS_TO_ANSWERS FOREIGN KEY (answer_id) REFERENCES ANSWERS (id)
561 );
562
563CREATE TABLE QUESTION_RATING (
564 id nvarchar(45) not null,
565 user_id nvarchar(45) not null,
566 status int not null,
567 created_at long not null,
568 question_id nvarchar(45) ,
569 answers_id nvarchar(45) ,
570 type_rating nvarchar(45) not null,
571 type_member nvarchar(45) not null,
572 primary key (id),
573 constraint fk_QR_to_question foreign key (question_id) references QUESTION(id),
574 constraint fk_QR_to_answers foreign key (answers_id) references ANSWERS(id)
575);
576
577CREATE TABLE QUESTION_SHARE (
578 id nvarchar(45) not null,
579 from_user_id nvarchar(45) not null,
580 to_user_id nvarchar(45) not null,
581 comment text charset utf8mb4 null ,
582 status boolean not null,
583 created_at long not null,
584 question_id nvarchar(45) not null,
585 from_type nvarchar(45) not null,
586 to_type nvarchar(45) not null,
587 primary key (id),
588 constraint fk_QS_to_question foreign key (question_id) references QUESTION(id)
589);
590
591CREATE TABLE QUESTION_FOLLOW(
592 id nvarchar(45) not null,
593 user_id nvarchar(45) not null,
594 status int not null,
595 created_at long not null,
596 question_id nvarchar(45) not null,
597 type_member text,
598 primary key (id),
599 constraint fk_QF_to_question foreign key (question_id) references QUESTION(id)
600
601);
602
603CREATE TABLE SUGGEST_ANSWERS(
604 id NVARCHAR(50) not null primary key,
605 name TEXT not null
606);
607
608CREATE TABLE REASON_CANCEL(
609 id NVARCHAR(50) not null primary key,
610 name TEXT not null
611);
612
613CREATE TABLE CHAT_RELATION(
614 id varchar(50) PRIMARY KEY NOT NULL,
615 user_id varchar(50) DEFAULT NULL,
616 target_user_id varchar(50) DEFAULT NULL,
617 deleted tinyint(1) DEFAULT NULL,
618 target_deleted tinyint(1) DEFAULT NULL,
619 blocked tinyint(1) DEFAULT NULL,
620 target_blocked tinyint(1) DEFAULT NULL,
621 firebase_id varchar(50) DEFAULT NULL,
622 user_type tinyint(1) DEFAULT NULL,
623 target_type tinyint(1) DEFAULT NULL
624);
625CREATE TABLE DOCTOR_RATE (
626 id nvarchar(50) NOT NULL,
627 user_id nvarchar(50) DEFAULT NULL,
628 doctor_id nvarchar(50) DEFAULT NULL,
629 rating int(11) DEFAULT NULL,
630 type_member text ,
631 created_at mediumtext ,
632 PRIMARY KEY (id),
633 CONSTRAINT FK_DOCTOR_RATE_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
634);
635
636
637CREATE TABLE PATIENT_RECORD (
638 id varchar(100) PRIMARY KEY,
639 user_id nvarchar(50),
640 parent_id nvarchar(50),
641 created_time long,
642 doctor_id nvarchar(50),
643 type_doctor varchar(20) default 'BAC_SI',
644 to_doctor_id nvarchar(50) default null,
645 CONSTRAINT FK_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
646 CONSTRAINT FK_PARENT_PATIENTRECORD_TO_USER FOREIGN KEY (parent_id) REFERENCES USER (id),
647 CONSTRAINT FK_PATIENTRECORD_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
648 );
649
650 CREATE TABLE HEALTH_RECORD (
651 id varchar(100) PRIMARY KEY,
652 type varchar(50),
653 name text,
654 created_time long,
655 close_time long,
656 doctor_close nvarchar(50) default null,
657 status boolean,
658 patient_record_id varchar(100),
659 doctor_id nvarchar(50),
660 type_doctor varchar(20) default 'BAC_SI',
661 to_doctor_id nvarchar(50) default null,
662 CONSTRAINT FK_DOCTOR_CLOSE_HEALTH_RECORD_TO_DOCTOR FOREIGN KEY (doctor_close) REFERENCES DOCTOR (id),
663 CONSTRAINT FK_HEALTH_RECORD_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
664 CONSTRAINT FK_HEALTHRECORD_TO_PATIENTRECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
665 CONSTRAINT FK_DOCTOR_HEALTH_RECORD_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
666 );
667
668 CREATE TABLE HISTORY_HEALTH_RECORD (
669 id varchar(100) PRIMARY KEY,
670 action_name varchar(30),
671 table_names varchar(50),
672 index_type varchar(50),
673 created_time long,
674 health_record_id varchar(100),
675 user_id nvarchar(50),
676 doctor_id nvarchar(50),
677 type_doctor varchar(20),
678 to_doctor_id nvarchar(50),
679 status boolean default true,
680 CONSTRAINT FK_HISTORY_HEALTH_RECORD_TO_USER FOREIGN KEY (user_id) REFERENCES USER (id),
681 CONSTRAINT FK_HISTORY_HEALTH_RECORD_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id),
682 CONSTRAINT FK_HISTORY_HEALTH_RECORD_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id)
683 );
684
685 CREATE TABLE APPOINTMENT_HEALTH_RECORD (
686 id varchar(100) PRIMARY KEY,
687 appointment_id text default null,
688 health_record_id varchar(100),
689 created_time long,
690 status VARCHAR(20) default 'WAITING',
691 CONSTRAINT FK_APPOINTMENT_HEALTH_RECORD_TO_HEALTH_RECORD FOREIGN KEY (health_record_id) REFERENCES HEALTH_RECORD (id)
692 );
693
694 CREATE TABLE UNIT (
695 id varchar(100) PRIMARY KEY,
696 vi text,
697 en text,
698 fr text
699 );
700
701
702 CREATE TABLE INDEX_CATEGORY (
703 id varchar(100) PRIMARY KEY,
704 name text
705 );
706
707
708
709 CREATE TABLE INDEX_GROUP (
710 id varchar(100) PRIMARY KEY,
711 name text,
712 number_order int,
713 index_category_id varchar(100),
714 CONSTRAINT FK_INDEX_GROUP_TO_INDEX_CATEGORY FOREIGN KEY (index_category_id) REFERENCES INDEX_CATEGORY (id)
715 );
716
717
718
719 CREATE TABLE INDEX_NAME (
720 id varchar(100) PRIMARY KEY,
721 vi text,
722 en text,
723 fr text,
724 index_group_id varchar(100),
725 number_order int,
726 type text,
727 index_category_id varchar(100) NULL,
728 status boolean default true,
729 CONSTRAINT FK_INDEX_NAME_TO_INDEX_GROUP FOREIGN KEY (index_group_id) REFERENCES INDEX_GROUP (id),
730 CONSTRAINT FK_INDEX_NAME_TO_INDEX_CATEGORY FOREIGN KEY (index_category_id) REFERENCES INDEX_CATEGORY (id)
731 );
732
733
734
735 CREATE TABLE INDEX_INFO (
736 id varchar(100) PRIMARY KEY,
737 index_id varchar(100),
738 unit_id varchar(100),
739 max text,
740 min text,
741 mid varchar(100),
742 CONSTRAINT FK_INDEX_INFO_TO_INDEX_NAME FOREIGN KEY (index_id) REFERENCES INDEX_NAME (id),
743 CONSTRAINT FK_UNIT_INDEX_INFO_TO_UNIT FOREIGN KEY (unit_id) REFERENCES UNIT (id)
744 );
745
746
747 CREATE TABLE INDEX_DATA (
748 id varchar(100) PRIMARY KEY,
749 index_name_id varchar(100),
750 health_record_id varchar(100),
751 patient_record_id varchar(100),
752 value text,
753 type_creator text,
754 doctor_id nvarchar(50),
755 type_doctor varchar(20) default 'BAC_SI',
756 to_doctor_id nvarchar(50) default null,
757 time long,
758 created_time long,
759 status boolean,
760 CONSTRAINT FK_INDEX_DATA_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
761 CONSTRAINT FK_NAME_INDEX_DATA_TO_INDEX_INFO FOREIGN KEY (index_name_id) REFERENCES INDEX_INFO (id),
762 CONSTRAINT FK_HEALTH_RECORD_INDEX_DATA_TO_HEALTH_RECORD FOREIGN KEY (health_record_id) REFERENCES HEALTH_RECORD (id),
763 CONSTRAINT FK_PATIENT_RECORD_INDEX_DATA_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
764 CONSTRAINT FK_DOCTOR_INDEX_DATA_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
765
766 );
767
768 CREATE TABLE ALLERGY_DATA (
769 id varchar(100) PRIMARY KEY,
770 vi text,
771 en text,
772 fr text,
773 created_time long,
774 status boolean
775 );
776
777 CREATE TABLE ALLERGY (
778 id varchar(100) PRIMARY KEY,
779 value_id varchar(100),
780 other text charset utf8mb4 null ,
781 level text,
782 time long,
783 note text charset utf8mb4 null,
784 created_time long,
785 patient_record_id varchar(100),
786 type_creator text,
787 doctor_id nvarchar(50),
788 type_doctor varchar(20) default 'BAC_SI',
789 to_doctor_id nvarchar(50) default null,
790 status boolean,
791 CONSTRAINT FK_ALLERGY_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
792 CONSTRAINT FK_NAME_ALLERGY_TO_ALLERGY_DATA FOREIGN KEY (value_id) REFERENCES ALLERGY_DATA (id),
793 CONSTRAINT FK_PATIENT_ALLERGY_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
794 CONSTRAINT FK_DOCTOR_ALLERGY_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
795 );
796
797 CREATE TABLE VACCINE (
798 id varchar(100) PRIMARY KEY,
799 vi text,
800 en text,
801 fr text,
802 created_time long,
803 status boolean
804 );
805
806 CREATE TABLE IMMNIZATION (
807 id varchar(100) PRIMARY KEY,
808 name text charset utf8mb4 null ,
809 VACCINE_id varchar(100),
810 other text charset utf8mb4 null ,
811 time long,
812 created_time long,
813 patient_record_id varchar(100),
814 type_creator text,
815 doctor_id nvarchar(50),
816 type_doctor varchar(20) default 'BAC_SI',
817 to_doctor_id nvarchar(50) default null,
818 status boolean,
819 CONSTRAINT FK_IMMNIZATION_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
820 CONSTRAINT FK_VACCINE_IMMNIZATIONTO_TO_VACCINE FOREIGN KEY (VACCINE_id) REFERENCES VACCINE (id),
821 CONSTRAINT FK_PATIENT_RECORD_IMMNIZATIONTO_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
822 CONSTRAINT FK_DOCTOR_IMMNIZATION_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
823 );
824
825 CREATE TABLE DIET (
826 id varchar(100) PRIMARY KEY,
827 name text charset utf8mb4 null ,
828 time long,
829 created_time long,
830 patient_record_id varchar(100),
831 type_creator text,
832 doctor_id nvarchar(50),
833 type_doctor varchar(20) default 'BAC_SI',
834 to_doctor_id nvarchar(50) default null,
835 status boolean default true,
836 CONSTRAINT FK_DIET_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
837 CONSTRAINT FK_DIET_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
838 CONSTRAINT FK_DOCTOR_DIET_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
839 );
840
841 CREATE TABLE ADDICTIVE_SUBSTACE (
842 id varchar(100) PRIMARY KEY,
843 name text,
844 time long,
845 created_time long,
846 patient_record_id varchar(100),
847 type_creator text,
848 doctor_id nvarchar(50),
849 type_doctor varchar(20) default 'BAC_SI',
850 to_doctor_id nvarchar(50) default null,
851 status boolean default true,
852 CONSTRAINT FK_ADDICTIVE_SUBSTACE_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
853 CONSTRAINT FK_ADDICTIVE_SUBSTACE_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
854 CONSTRAINT FK_DOCTOR_ADDICTIVE_SUBSTACE_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
855 );
856
857 CREATE TABLE STIMULANT (
858 id varchar(100) PRIMARY KEY,
859 alcohol text,
860 alcohol_level text,
861 tobaco text,
862 tobaco_level text,
863 sexually_active text,
864 sexually_active_level text,
865 created_time long,
866 patient_record_id varchar(100),
867 type_creator text,
868 CONSTRAINT FK_STIMULANT_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id)
869 );
870
871 CREATE TABLE DISEASE_SYMPTOM_DATA (
872 id varchar(100) PRIMARY KEY,
873 symptom_vi text,
874 symptom_en text,
875 symptom_fr text,
876 symptom_code text,
877 disease_vi text,
878 disease_en text,
879 disease_fr text,
880 disease_code text,
881 status boolean
882 );
883
884 CREATE TABLE MEDICAL_HISTORY (
885 id varchar(100) PRIMARY KEY,
886 other text charset utf8mb4 null ,
887 begin_time long,
888 end_time long,
889 status boolean,
890 created_time long,
891 patient_record_id varchar(100),
892 disease_id varchar(100),
893 type_creator text,
894 doctor_id nvarchar(50),
895 type_doctor varchar(20) default 'BAC_SI',
896 to_doctor_id nvarchar(50) default null,
897 note text charset utf8mb4 null ,
898 type text,
899 CONSTRAINT FK_MEDICAL_HISTORY_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
900 CONSTRAINT FK_PARIENT_MEDICAL_HISTORY_TO_PATIENT_RECORD FOREIGN KEY (patient_record_id) REFERENCES PATIENT_RECORD (id),
901 CONSTRAINT FK_DISEASE_MEDICAL_HISTORY_TO_DISEASE_SYMPTOM_DATA FOREIGN KEY (disease_id) REFERENCES DISEASE_SYMPTOM_DATA (id),
902 CONSTRAINT FK_DOCTOR_MEDICAL_HISTORY_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
903 );
904
905 CREATE TABLE TREATMENT_MEDICINE (
906 id varchar(100) PRIMARY KEY,
907 vi text,
908 en text,
909 fr text,
910 medical_history_id varchar(100),
911 CONSTRAINT FK_TREATMENT_MEDICINE_TO_MEDICAL_HISTORY FOREIGN KEY (medical_history_id) REFERENCES MEDICAL_HISTORY (id)
912 );
913
914 CREATE TABLE SYMPTOM (
915 id varchar(100) PRIMARY KEY,
916 other text charset utf8mb4 null ,
917 type text,
918 begin_time long,
919 end_time long,
920 created_time long,
921 health_record_id varchar(100),
922 symptom_id varchar(100),
923 type_creator text,
924 doctor_id nvarchar(50),
925 type_doctor varchar(20) default 'BAC_SI',
926 to_doctor_id nvarchar(50) default null,
927 status boolean,
928 note text charset utf8mb4 null ,
929 CONSTRAINT FK_SYMPTOM_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
930 CONSTRAINT FK_HEALTH_ROCORD_SYMPTOM_TO_HEALTH_RECORD FOREIGN KEY (health_record_id) REFERENCES HEALTH_RECORD (id),
931 CONSTRAINT FK_SYMPTOM_NAME_TO_DISEASE_SYMPTOM_DATA FOREIGN KEY (symptom_id) REFERENCES DISEASE_SYMPTOM_DATA (id),
932 CONSTRAINT FK_DOCTOR_SYMPTOM_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
933
934 );
935
936 CREATE TABLE IMAGE_ANALYSATION (
937 id varchar(100) PRIMARY KEY,
938 title text charset utf8mb4 null ,
939 content text charset utf8mb4 not null ,
940 image text,
941 type text,
942 time long,
943 created_time long,
944 health_record_id varchar(100),
945 doctor_id nvarchar(50),
946 type_creator text,
947 type_doctor varchar(20) default 'BAC_SI',
948 to_doctor_id nvarchar(50) default null,
949 status boolean,
950 CONSTRAINT FK_IMAGE_ANALYSATION_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
951 CONSTRAINT FK_HEALTH_RECORD_IMAGE_ANALYSATION_TO_HEALTH_RECORD FOREIGN KEY (health_record_id) REFERENCES HEALTH_RECORD (id),
952 CONSTRAINT FK_DOCTOR_IMAGE_ANALYSATION_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
953 );
954
955 CREATE TABLE PROGNOSIS_ADVICE (
956 id varchar(100) PRIMARY KEY,
957 title text charset utf8mb4 null ,
958 content text charset utf8mb4 not null ,
959 image text,
960 created_time long,
961 health_record_id varchar(100),
962 doctor_id nvarchar(50),
963 type_creator text,
964 type_doctor varchar(20) default 'BAC_SI',
965 to_doctor_id nvarchar(50) default null,
966 CONSTRAINT FK_PROGNOSIS_ADVICE_N_TO_DOCTOR FOREIGN KEY (to_doctor_id) REFERENCES DOCTOR (id),
967 status boolean default true,
968 CONSTRAINT FK_HEALTH_RECORD_PROGNOSIS_ADVICE_TO_HEALTH_RECORD FOREIGN KEY (health_record_id) REFERENCES HEALTH_RECORD (id),
969 CONSTRAINT FK_DOCTOR_PROGNOSIS_ADVICE_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
970 );
971
972 CREATE TABLE ETHNIC (
973 id varchar(100) PRIMARY KEY,
974 vi text,
975 en text,
976 status boolean default true
977 );
978
979
980 CREATE TABLE SCHEDULE (
981 id varchar(50) CHARACTER SET utf8 NOT NULL,
982 hospital_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
983 monMorningStart mediumtext COLLATE utf8_bin,
984 monMorningEnd mediumtext COLLATE utf8_bin,
985 monAfternoonStart mediumtext COLLATE utf8_bin,
986 monAfternoonEnd mediumtext COLLATE utf8_bin,
987 tueMorningStart mediumtext COLLATE utf8_bin,
988 tueMorningEnd mediumtext COLLATE utf8_bin,
989 tueAfternoonStart mediumtext COLLATE utf8_bin,
990 tueAfternoonEnd mediumtext COLLATE utf8_bin,
991 wedMorningStart mediumtext COLLATE utf8_bin,
992 wedMorningEnd mediumtext COLLATE utf8_bin,
993 wedAfternoonStart mediumtext COLLATE utf8_bin,
994 wedAfternoonEnd mediumtext COLLATE utf8_bin,
995 thuMorningStart mediumtext COLLATE utf8_bin,
996 thuMorningEnd mediumtext COLLATE utf8_bin,
997 thuAfternoonStart mediumtext COLLATE utf8_bin,
998 thuAfternoonEnd mediumtext COLLATE utf8_bin,
999 friMorningStart mediumtext COLLATE utf8_bin,
1000 friMorningEnd mediumtext COLLATE utf8_bin,
1001 friAfternoonStart mediumtext COLLATE utf8_bin,
1002 friAfternoonEnd mediumtext COLLATE utf8_bin,
1003 satMorningStart mediumtext COLLATE utf8_bin,
1004 satMorningEnd mediumtext COLLATE utf8_bin,
1005 satAfternoonStart mediumtext COLLATE utf8_bin,
1006 satAfternoonEnd mediumtext COLLATE utf8_bin,
1007 sunMorningStart mediumtext COLLATE utf8_bin,
1008 sunMorningEnd mediumtext COLLATE utf8_bin,
1009 sunAfternoonStart mediumtext COLLATE utf8_bin,
1010 sunAfternoonEnd mediumtext COLLATE utf8_bin,
1011 status tinyint(1) DEFAULT '1',
1012 created_at mediumtext COLLATE utf8_bin,
1013 PRIMARY KEY (id),
1014 UNIQUE KEY UNIQUE_HOSPITAL (hospital_id),
1015 CONSTRAINT SCHEDULE_ibfk_1 FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (id)
1016 );
1017
1018 CREATE TABLE SERVICE (
1019 id varchar(50) CHARACTER SET utf8 NOT NULL,
1020 name text COLLATE utf8_bin,
1021 name_en text COLLATE utf8_bin,
1022 name_fr text COLLATE utf8_bin,
1023 status tinyint(1) DEFAULT '1',
1024 created_at mediumtext COLLATE utf8_bin,
1025 PRIMARY KEY (id)
1026 );
1027
1028 CREATE TABLE RELATION_SERVICE_HOSPITAL (
1029 id varchar(50) CHARACTER SET utf8 NOT NULL,
1030 hospital_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1031 service_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1032 status tinyint(1) DEFAULT '1',
1033 created_at mediumtext COLLATE utf8_bin,
1034 PRIMARY KEY (id),
1035 UNIQUE KEY UNIQUE_HOSPITAL_SERVICE (hospital_id,service_id),
1036 KEY service_id (service_id),
1037 CONSTRAINT RELATION_SERVICE_HOSPITAL_ibfk_1 FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (id),
1038 CONSTRAINT RELATION_SERVICE_HOSPITAL_ibfk_2 FOREIGN KEY (service_id) REFERENCES SERVICE (id)
1039 );
1040
1041 CREATE TABLE HOSPITAL_IMAGES (
1042 id varchar(50) CHARACTER SET utf8 NOT NULL,
1043 hospital_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1044 url text COLLATE utf8_bin,
1045 type text COLLATE utf8_bin,
1046 status tinyint(1) DEFAULT '1',
1047 created_at mediumtext COLLATE utf8_bin,
1048 order_by int default 0,
1049 PRIMARY KEY (id),
1050 KEY hospital_id (hospital_id),
1051 CONSTRAINT HOSPITAL_IMAGES_ibfk_1 FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (id)
1052 );
1053
1054 CREATE TABLE SALE_PACKAGE (
1055 id varchar(50) CHARACTER SET utf8 NOT NULL,
1056 relation_service_hospital_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1057 name text COLLATE utf8_bin,
1058 name_en text COLLATE utf8_bin,
1059 name_fr text COLLATE utf8_bin,
1060 description text COLLATE utf8_bin,
1061 description_en text COLLATE utf8_bin,
1062 description_fr text COLLATE utf8_bin,
1063 price mediumtext COLLATE utf8_bin,
1064 status tinyint(1) DEFAULT '1',
1065 created_at mediumtext COLLATE utf8_bin,
1066 highlight tinyint(1) DEFAULT '1',
1067 images text COLLATE utf8_bin,
1068 sex int(11) DEFAULT NULL,
1069 test int(11) DEFAULT NULL,
1070 examination int(11) DEFAULT NULL,
1071 ageStart int(11) DEFAULT NULL,
1072 ageEnd int(11) DEFAULT NULL,
1073 PRIMARY KEY (id),
1074 KEY relation_service_hospital_id (relation_service_hospital_id),
1075 CONSTRAINT SALE_PACKAGE_ibfk_1 FOREIGN KEY (relation_service_hospital_id) REFERENCES RELATION_SERVICE_HOSPITAL (id) );
1076
1077 CREATE TABLE RELATION_SALE_PACKAGE_SPECIALITY (
1078 id varchar(50) CHARACTER SET utf8 NOT NULL,
1079 sale_package_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1080 relation_hospital_speciality varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1081 PRIMARY KEY (id),
1082 KEY sale_package_id (sale_package_id),
1083 KEY relation_hospital_speciality (relation_hospital_speciality),
1084 CONSTRAINT RELATION_SALE_PACKAGE_SPECIALITY_ibfk_1 FOREIGN KEY (sale_package_id) REFERENCES SALE_PACKAGE (id),
1085 CONSTRAINT RELATION_SALE_PACKAGE_SPECIALITY_ibfk_2 FOREIGN KEY (relation_hospital_speciality) REFERENCES RELATION_HOSPITAL_SPECIALITY (id)
1086 );
1087
1088 CREATE TABLE SALE_PACKAGE_IMAGES (
1089 id varchar(50) CHARACTER SET utf8 NOT NULL,
1090 sale_package_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1091 url text COLLATE utf8_bin,
1092 type text COLLATE utf8_bin,
1093 status tinyint(1) DEFAULT '1',
1094 created_at mediumtext COLLATE utf8_bin,
1095 PRIMARY KEY (id),
1096 KEY sale_package_id (sale_package_id),
1097 CONSTRAINT SALE_PACKAGE_IMAGES_ibfk_1 FOREIGN KEY (sale_package_id) REFERENCES SALE_PACKAGE (id)
1098 );
1099
1100 CREATE TABLE SALE_PACKAGE_DETAIL (
1101 id varchar(50) CHARACTER SET utf8 NOT NULL,
1102 sale_package_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1103 detail text COLLATE utf8_bin,
1104 detail_en text COLLATE utf8_bin,
1105 detail_fr text COLLATE utf8_bin,
1106 status tinyint(1) DEFAULT NULL,
1107 created_at mediumtext COLLATE utf8_bin,
1108 PRIMARY KEY (id),
1109 KEY sale_package_id (sale_package_id),
1110 CONSTRAINT SALE_PACKAGE_DETAIL_ibfk_1 FOREIGN KEY (sale_package_id) REFERENCES SALE_PACKAGE (id)
1111 );
1112
1113 CREATE TABLE SALE_PACKAGE_SUB_DETAIL (
1114 id varchar(50) CHARACTER SET utf8 NOT NULL,
1115 sale_package_detail_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1116 sub_detail text COLLATE utf8_bin,
1117 sub_detail_en text COLLATE utf8_bin,
1118 sub_detail_fr text COLLATE utf8_bin,
1119 status tinyint(1) DEFAULT NULL,
1120 created_at mediumtext COLLATE utf8_bin,
1121 PRIMARY KEY (id),
1122 KEY sale_package_detail_id (sale_package_detail_id),
1123 CONSTRAINT SALE_PACKAGE_SUB_DETAIL_ibfk_1 FOREIGN KEY (sale_package_detail_id) REFERENCES SALE_PACKAGE_DETAIL (id)
1124 );
1125
1126 CREATE TABLE PRICE (
1127 id varchar(50) CHARACTER SET utf8 NOT NULL,
1128 sale_package_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1129 name text COLLATE utf8_bin,
1130 name_en text COLLATE utf8_bin,
1131 name_fr text COLLATE utf8_bin,
1132 status tinyint(1) DEFAULT '1',
1133 created_at mediumtext COLLATE utf8_bin,
1134 PRIMARY KEY (id),
1135 KEY sale_package_id (sale_package_id),
1136 CONSTRAINT PRICE_ibfk_1 FOREIGN KEY (sale_package_id) REFERENCES SALE_PACKAGE (id)
1137 );
1138
1139 CREATE TABLE VOUCHER(
1140 id VARCHAR(50) PRIMARY KEY NOT NULL,
1141 voucher_type VARCHAR(50) NOT NULL,
1142 begin long,
1143 end long,
1144 created_time long,
1145 code text,
1146 value int,
1147 apply varchar(50) default 'ALL',
1148 apply_per_user int default 1,
1149 currency text,
1150 unlimit boolean default false,
1151 slot int,
1152 hospital_id NVARCHAR(50) NULL,
1153 doctor_id NVARCHAR(45) NULL,
1154 package_id NVARCHAR(50) null,
1155 status boolean default true,
1156 CONSTRAINT fk_voucher_to_hospital FOREIGN KEY (hospital_id) references HOSPITAL(id),
1157 CONSTRAINT fk_voucher_to_doctor FOREIGN KEY (doctor_id) references DOCTOR(id)
1158 );
1159 CREATE TABLE BANK_ACCOUNT (
1160 id varchar(50) CHARACTER SET utf8 NOT NULL,
1161 user_id varchar(50) CHARACTER SET utf8 DEFAULT NULL,
1162 type text COLLATE utf8_bin,
1163 account_number text COLLATE utf8_bin,
1164 card_number text COLLATE utf8_bin,
1165 bank_name text COLLATE utf8_bin,
1166 bank_code text COLLATE utf8_bin,
1167 account_type text COLLATE utf8_bin,
1168 bank_avatar text COLLATE utf8_bin,
1169 bank_branch text COLLATE utf8_bin,
1170 card_name text COLLATE utf8_bin,
1171 type_member text COLLATE utf8_bin,
1172 created_at mediumtext COLLATE utf8_bin,
1173 active tinyint(1) DEFAULT '1',
1174 updated_at mediumtext COLLATE utf8_bin,
1175 PRIMARY KEY (id)
1176 );
1177 CREATE TABLE DOCTOR_INCOME(
1178 id VARCHAR(50) PRIMARY KEY NOT NULL,
1179 hospital_id NVARCHAR(50) NOT NULL,
1180 doctor_id NVARCHAR(45) NOT NULL,
1181 appointment_id NVARCHAR(50)not null,
1182 created_time long,
1183 currency text,
1184 init_price float,
1185 discount float,
1186 last_price float,
1187 is_pay boolean default false,
1188 status boolean default true,
1189 CONSTRAINT fk_income_to_hospital FOREIGN KEY (hospital_id) references HOSPITAL(id),
1190 CONSTRAINT fk_income_to_doctor FOREIGN KEY (doctor_id) references DOCTOR(id)
1191 );
1192
1193 CREATE TABLE PATIENT_TOTAL_COST(
1194 id VARCHAR(50) PRIMARY KEY NOT NULL,
1195 user_id NVARCHAR(45) NOT NULL,
1196 appointment_id NVARCHAR(50)not null,
1197 created_time long,
1198 currency text,
1199 init_price float,
1200 discount float,
1201 last_price float,
1202 is_pay boolean default false,
1203 status boolean default true
1204 );
1205 CREATE TABLE SPECIALITY_FOLLOW (
1206 user_id varchar(50) CHARACTER SET utf8 NOT NULL,
1207 speciality_id varchar(45) CHARACTER SET utf8 NOT NULL,
1208 status varchar(45) COLLATE utf8_bin NOT NULL,
1209 PRIMARY KEY (user_id,speciality_id),
1210 KEY fk_SPECIALITY_FOLLOW_user1_idx (user_id),
1211 KEY fk_SPECIALITY_FOLLOW_speciality1_idx (speciality_id),
1212 CONSTRAINT FK_SPECIALITY_FOLLOW_TO_SPECIALITY FOREIGN KEY (speciality_id) REFERENCES SPECIALITY (id),
1213 CONSTRAINT FK_SPECIALITY_FOLLOW_TO_USER FOREIGN KEY (user_id) REFERENCES USER (id)
1214 );
1215 CREATE TABLE ANSWER_QUESTION_ASSIGNMENT (
1216 doctor_id varchar(45) CHARACTER SET utf8 NOT NULL,
1217 question_id varchar(45) CHARACTER SET utf8 NOT NULL,
1218 created_at varchar(45) COLLATE utf8_bin NOT NULL,
1219 status varchar(45) COLLATE utf8_bin NOT NULL,
1220 reason_deny text charset utf8mb4 null ,
1221 PRIMARY KEY (doctor_id,question_id),
1222 KEY FK_ANSWER_QUESTION_ASSIGNMENT_TO_DOCTOR_IDX (doctor_id),
1223 KEY FK_ANSWER_QUESTION_ASSIGNMENT_TO_QUESTION_IDX (question_id),
1224 CONSTRAINT FK_ANSWER_QUESTION_ASSIGNMENT_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id),
1225 CONSTRAINT FK_ANSWER_QUESTION_ASSIGNMENT_TO_QUESTION FOREIGN KEY (question_id) REFERENCES QUESTION (id)
1226 ) ;
1227 CREATE TABLE DOCTOR_ASSIGN (
1228 id varchar(45) COLLATE utf8_bin NOT NULL,
1229 doctor_id varchar(45) CHARACTER SET utf8 NOT NULL,
1230 created_at varchar(45) COLLATE utf8_bin NOT NULL,
1231 status varchar(45) COLLATE utf8_bin NOT NULL,
1232 PRIMARY KEY (id),
1233 KEY FK_DOCTOR_ASSIGN_TO_DOCTOR_IDX (doctor_id),
1234 CONSTRAINT FK_DOCTOR_ASSIGN_TO_DOCTOR FOREIGN KEY (doctor_id) REFERENCES DOCTOR (id)
1235 ) ;
1236 CREATE TABLE CHECK_OTP (
1237 id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1238 phone text COLLATE utf8_bin,
1239 verify_code text COLLATE utf8_bin,
1240 created_at mediumtext COLLATE utf8_bin,
1241 status tinyint(1) DEFAULT '0',
1242 PRIMARY KEY (id)
1243 );
1244
1245 CREATE TABLE NGANLUONG_TRANSACTION(
1246 id nvarchar(50) primary key,
1247 transaction_info text,
1248 price int,
1249 payment_id int,
1250 payment_type tinyint(1),
1251 error_text text,
1252 secure_code text,
1253 token_nl text,
1254 order_code text
1255 );
1256
1257 CREATE TABLE DC_WALLET(
1258 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
1259 money BIGINT not null DEFAULT 0,
1260 currency varchar(10) DEFAULT 'VND',
1261 user_id varchar(100) not null,
1262 user_type varchar(20) not null,
1263 last_modify BIGINT,
1264 is_expired boolean null,
1265 reason_expired text null
1266 );
1267
1268 CREATE TABLE TRANSACTION_HISTORY(
1269 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
1270 price BIGINT default 0,
1271 souce_nganluong BIGINT default 0,
1272 souce_dc_wallet BIGINT default 0,
1273 w_money BIGINT default 0,
1274 currency varchar(10) DEFAULT 'VND',
1275 service_id varchar(100) null,
1276 service_type varchar(30) null,
1277 transaction_type varchar(20) not null,
1278 note text ,
1279 created_time BIGINT,
1280 user_id varchar(100) not null,
1281 user_type varchar(20) not null,
1282 nganluong_transaction_id int
1283 );
1284
1285 CREATE TABLE OTHER_PROVIDER(
1286 id nvarchar(50) primary key not null,
1287 name text not null,
1288 order_provider BIGINT ,
1289 address text,
1290 note text ,
1291 fix_number text,
1292 created_time long ,
1293 avatar text,
1294 director text ,
1295 description text ,
1296 highlight int default 0,
1297 type_provider varchar(30) default 'OTHER',
1298 expand_json text
1299 );
1300
1301 CREATE TABLE PRODUCT_PROVIDER(
1302 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
1303 hospital_id nvarchar(50) null,
1304 other_provider_id nvarchar(50) null,
1305 type_provider varchar(30) default 'HOSPITAL',
1306 status boolean default true,
1307 CONSTRAINT fk_product_provider_to_hospital FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (id),
1308 CONSTRAINT fk_other_provider_to_other_provider FOREIGN KEY (other_provider_id) REFERENCES OTHER_PROVIDER (id)
1309 );
1310
1311 create table PRODUCT_CATEGORY(
1312 id varchar(50) primary key not null,
1313 name_vi text,
1314 name_en text,
1315 image text not null,
1316 expand_json_c text ,
1317 description_vi text,
1318 description_en text,
1319 category_id varchar(50) null,
1320 product_type varchar(30) default 'SERVICE',
1321 level int,
1322 status boolean default true,
1323 CONSTRAINT FK_PRODUCT_CATEGORY_TO_CATEGORY FOREIGN KEY (category_id) REFERENCES PRODUCT_CATEGORY(id)
1324 );
1325
1326
1327 create table PROVIDER_CATEGORY(
1328 id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
1329 category_id varchar(50) null,
1330 provider_id INT(11) null,
1331 status boolean default true,
1332 CONSTRAINT FK_CATEGORY_TO_CATEGORY FOREIGN KEY (category_id) REFERENCES PRODUCT_CATEGORY(id),
1333 CONSTRAINT FK_CATEGORY_TO_PRODUCT_PROVIDER FOREIGN KEY (provider_id) REFERENCES PRODUCT_PROVIDER(id)
1334 );
1335
1336
1337 create table PRODUCT (
1338 id varchar(50) primary key not null,
1339 order_product BIGINT,
1340 name_vi text charset utf8mb4 not null ,
1341 name_en text charset utf8mb4 null ,
1342 summary_vi text charset utf8mb4 null ,
1343 summary_en text charset utf8mb4 null ,
1344 parent_product_id varchar(50) null,
1345 product_type varchar(30) default 'SERVICE',
1346 product_category_id INT(11) null,
1347 need_confirm boolean,
1348 start_time BIGINT,
1349 end_time BIGINT,
1350 limit_time boolean,
1351 slot int,
1352 limit_slot boolean,
1353 highlight boolean,
1354 inital_fee BIGINT,
1355 min_fee BIGINT,
1356 max_fee BIGINT,
1357 favored_fee int null,
1358 max_discount_value int null,
1359 currency varchar(10) DEFAULT 'VND',
1360 public_fee boolean,
1361 description text charset utf8mb4 null ,
1362 fix_address boolean,
1363 apply_gender varchar(20),
1364 apply_age text,
1365 number_of_test int,
1366 number_of_examination_item int,
1367 expand_json text charset utf8mb4 null ,
1368 status boolean default true,
1369 CONSTRAINT FK_PRODUCT_TO_CATEGORY FOREIGN KEY (product_category_id) REFERENCES PROVIDER_CATEGORY(id),
1370 CONSTRAINT FK_PRODUCT_TO_PRODUCT FOREIGN KEY (parent_product_id) REFERENCES PRODUCT(id)
1371 );
1372
1373 create table PRODUCT_VOUCHER(
1374 id varchar(50) primary key not null,
1375 begin BIGINT,
1376 end BIGINT,
1377 created_time BIGINT,
1378 code text,
1379 value int,
1380 voucher_type VARCHAR(50) ,
1381 max_discount_value int,
1382 currency text,
1383 provider_id NVARCHAR(50) ,
1384 product_category_id NVARCHAR(50) ,
1385 product_id NVARCHAR(50) ,
1386 rule text not null,
1387 apply varchar(20) default 'SERVICE',
1388 status boolean default true
1389 );
1390
1391 create table BUY_PRODUCT(
1392 id varchar(50) primary key not null,
1393 user_id nvarchar(50) not null,
1394 user_type varchar(20) default 'BENH_NHAN',
1395 product_id varchar(50) not null,
1396 ref_product_json text,
1397 ref_provider_json text,
1398 slot int,
1399 voucher_id varchar(50),
1400 created_time BIGINT,
1401 payment_status varchar(30),
1402 method_payment varchar(30),
1403 is_confirm_payment boolean default false,
1404 unit_price int,
1405 total_price int,
1406 money_nganluong int,
1407 money_dc_wallet int,
1408 voucher_value int,
1409 voucher_type VARCHAR(50) ,
1410 max_discount_value BIGINT,
1411 use_time BIGINT ,
1412 user_phone text,
1413 use_address text charset utf8mb4 null ,
1414 note text charset utf8mb4 null,
1415 need_confirm boolean default true,
1416 time_confirm BIGINT,
1417 user_confirm text,
1418 user_confirm_type varchar(30),
1419 time_cancel text,
1420 type_user_cancel varchar(30),
1421 reason_cancel text charset utf8mb4 null ,
1422 status varchar(30),
1423 CONSTRAINT FK_BUY_PRODUCT_TO_PRODUCT FOREIGN KEY (product_id) REFERENCES PRODUCT(id)
1424 );
1425
1426
1427 CREATE trigger TRIGGER_UPDATE_DOCTOR_RATE after insert on DOCTOR_RATE for each row
1428 update DOCTOR as d set rating = (select AVG(rating) from DOCTOR_RATE as dr where dr.doctor_id = d.id) where d.id = NEW.doctor_id ;
1429
1430 CREATE trigger TRIGGER_UPDATE_DOCTOR_RATE_V2 after update on DOCTOR_RATE for each row
1431 update DOCTOR as d set rating = (select AVG(rating) from DOCTOR_RATE as dr where dr.doctor_id = d.id) where d.id = NEW.doctor_id;
1432
1433 create trigger TRIGGER_UPDATE_SPECIALITY_TOTAL after insert on RELATION_DOCTOR_SPECIALITY for each row
1434 update RELATION_HOSPITAL_SPECIALITY set total = total + 1 where id = new.speciality_id;
1435
1436 create trigger TRIGGER_UPDATE_SPECIALITY_TOTALV2 after delete on RELATION_DOCTOR_SPECIALITY for each row
1437 update RELATION_HOSPITAL_SPECIALITY set total = total - 1 where id = old.speciality_id;
1438
1439 create trigger TRIGGER_UPDATE_DOCTOR_BANK_AMOUNT after insert on BANK_ACCOUNT for each row
1440 if (new.type = 'full' or new.type = 'account') then update DOCTOR set bank_amount = bank_amount + 1 where id = new.user_id; end if;
1441
1442 create trigger TRIGGER_UPDATE_DOCTOR_BANK_AMOUNTV2 after delete on BANK_ACCOUNT for each row
1443 update DOCTOR set bank_amount = bank_amount - 1 where id = old.user_id;
1444
1445 create trigger TRIGGER_UPDATE_DOCTOR_BANK_AMOUNTV3 after update on BANK_ACCOUNT for each row
1446 if (new.active = 0) then update DOCTOR set bank_amount = bank_amount - 1 where id = new.user_id; end if;
1447
1448 delimiter |
1449 CREATE trigger TRIGGER_CHECK_EXIST_TRANSACTION BEFORE INSERT on TRANSACTION_HISTORY
1450 for each row begin
1451 DECLARE isExist integer;
1452 SET isExist = (select COUNT(id) from TRANSACTION_HISTORY as trh where trh.user_id = new.user_id and trh.currency = new.currency and trh.transaction_type = new.transaction_type and trh.service_id = new.service_id );
1453 if isExist <> 0 then
1454 INSERT TRANSACTION_HISTORY(price, souce_nganluong, souce_dc_wallet, w_money, currency, service_id, service_type, transaction_type, note, created_time, user_id, user_type, nganluong_transaction_id )
1455 value (new.price, new.souce_nganluong, new.souce_dc_wallet,new.w_money, new.currency, new.service_id, new.service_type, new.transaction_type, new.note, new.created_time, new.user_id, new.user_type, new.nganluong_transaction_id );
1456 end if;
1457 end;
1458 |
1459 delimiter ;
1460
1461 delimiter |
1462 CREATE trigger TRIGGER_UPDATE_DC_WALLET after insert on TRANSACTION_HISTORY
1463 for each row begin
1464 DECLARE isExist integer;
1465 SET isExist = (select COUNT(id) from DC_WALLET as dc_w1 where dc_w1.user_id = new.user_id and dc_w1.currency = new.currency and dc_w1.is_expired = 0 );
1466 if isExist <=> 0 AND NEW.transaction_type <=> 'MONEY_IN' then
1467 INSERT DC_WALLET(money, currency, user_id, user_type, last_modify) value (NEW.w_money, NEW.currency,NEW.user_id, NEW.user_type,NEW.created_time);
1468 else
1469 Update DC_WALLET as dc_w set last_modify = new.created_time, money = (select SUM(w_money) from TRANSACTION_HISTORY as th where dc_w.user_id = th.user_id and dc_w.currency = new.currency ) where dc_w.user_id = NEW.user_id and dc_w.currency = new.currency and dc_w.is_expired = 0;
1470 end if;
1471 end;
1472 |
1473 delimiter ;