im.doctor/src/server/resources/schema/refactor/schema.sql

SET FOREIGN_KEY_CHECKS=0

DROP TABLE IF EXISTS `topics` CASCADE
;

DROP TABLE IF EXISTS `p2p_messages` CASCADE
;

DROP TABLE IF EXISTS `group_messages` CASCADE
;

DROP TABLE IF EXISTS `sync_log` CASCADE
;

DROP TABLE IF EXISTS `muc_messages` CASCADE
;

DROP TABLE IF EXISTS `participants` CASCADE
;

DROP TABLE IF EXISTS `sessions` CASCADE
;

DROP TABLE IF EXISTS `doctors` CASCADE
;

DROP TABLE IF EXISTS `patients` CASCADE
;

CREATE TABLE `topics`
(
	`id` INTEGER NOT NULL,
	`session_id` VARCHAR(50) NOT NULL,
	`name` VARCHAR(50),
	`create_time` TIMESTAMP(0),
	`end_by` VARCHAR(50),
	`start_message_id` INTEGER,
	`end_message_id` INTEGER,
	CONSTRAINT `PK_topics` PRIMARY KEY (`id`)
)
;

CREATE TABLE `p2p_messages`
(
	`id` INTEGER NOT NULL,
	`session_id` VARCHAR(50) NOT NULL,
	`sender_id` VARCHAR(50) NOT NULL,
	`content_type` INTEGER NOT NULL,
	`content` VARCHAR(1024),
	`timestamp` TIMESTAMP(0),
	CONSTRAINT `PK_messages` PRIMARY KEY (`id`)
)
;

CREATE TABLE `group_messages`
(
	`id` INTEGER NOT NULL,
	`session_id` VARCHAR(50) NOT NULL,
	`sender_id` VARCHAR(50) NOT NULL,
	`content_type` INTEGER NOT NULL,
	`content` VARCHAR(1024),
	`timestamp` TIMESTAMP(0),
	CONSTRAINT `PK_messages` PRIMARY KEY (`id`)
)
;

CREATE TABLE `sync_log`
(
	`event` VARCHAR(50) NOT NULL,
	`last_sync_time` DATE NOT NULL,
	`is_success` TINYINT,
	`message` VARCHAR(50)
)
;

CREATE TABLE `muc_messages`
(
	`id` INTEGER NOT NULL,
	`session_id` VARCHAR(50) NOT NULL,
	`sender_id` VARCHAR(50) NOT NULL,
	`content_type` INTEGER NOT NULL,
	`content` VARCHAR(1024),
	`timestamp` TIMESTAMP(0),
	CONSTRAINT `PK_messages` PRIMARY KEY (`id`)
)
;

CREATE TABLE `participants`
(
	`session_id` VARCHAR(50) NOT NULL,
	`participaint_id` VARCHAR(50) NOT NULL,
	`member_type` INTEGER NOT NULL,
	`receiving` TINYINT,
	CONSTRAINT `PK_participants` PRIMARY KEY (`session_id`,`participaint_id`)
)
;

CREATE TABLE `sessions`
(
	`id` VARCHAR(50) NOT NULL,
	`name` VARCHAR(50) NOT NULL,
	`type` INTEGER NOT NULL,
	`create_date` DATE NOT NULL,
	CONSTRAINT `PK_sessions` PRIMARY KEY (`id`)
)
;

CREATE TABLE `doctors`
(
	`id` VARCHAR(50) NOT NULL,
	`name` VARCHAR(30) NOT NULL,
	`sex` INTEGER NOT NULL,
	`birthdate` DATE,
	`avatar` VARCHAR(255),
	`level` INTEGER NOT NULL,
	`last_sync_time` TIMESTAMP(0) NOT NULL,
	`locked` TINYINT NOT NULL,
	CONSTRAINT `PK_doctors` PRIMARY KEY (`id`)
)
;

CREATE TABLE `patients`
(
	`id` VARCHAR(50) NOT NULL,
	`name` VARCHAR(30),
	`sex` INTEGER,
	`avatar` VARCHAR(255),
	`birthdate` DATE,
	`general_dr` VARCHAR(50),
	`health_dr` VARCHAR(50),
	CONSTRAINT `PK_users` PRIMARY KEY (`id`)
)
;

ALTER TABLE `topics` 
 ADD INDEX `IXFK_topics_sessions` (`session_id` ASC)
;

ALTER TABLE `p2p_messages` 
 ADD INDEX `IXFK_messages_participants` (`session_id` ASC,`sender_id` ASC)
;

ALTER TABLE `p2p_messages` 
 ADD INDEX `IXFK_messages_sessions` (`session_id` ASC)
;

ALTER TABLE `group_messages` 
 ADD INDEX `IXFK_messages_participants` (`session_id` ASC,`sender_id` ASC)
;

ALTER TABLE `group_messages` 
 ADD INDEX `IXFK_messages_sessions` (`session_id` ASC)
;

ALTER TABLE `muc_messages` 
 ADD INDEX `IXFK_messages_participants` (`session_id` ASC,`sender_id` ASC)
;

ALTER TABLE `muc_messages` 
 ADD INDEX `IXFK_messages_sessions` (`session_id` ASC)
;

ALTER TABLE `participants` 
 ADD INDEX `IXFK_participants_doctors` (`participaint_id` ASC)
;

ALTER TABLE `participants` 
 ADD INDEX `IXFK_participants_patients` (`participaint_id` ASC)
;

SET FOREIGN_KEY_CHECKS=1