|
1
|
use score;
|
|
2
|
-- Create Chats Table
|
|
3
|
CREATE TABLE score.chats (
|
|
4
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
5
|
idSource int(11),
|
|
6
|
userId int(11),
|
|
7
|
unreadCount INT,
|
|
8
|
muted BOOLEAN,
|
|
9
|
isGroup BOOLEAN,
|
|
10
|
lastMessage VARCHAR(255),
|
|
11
|
lastMessageAt DATETIME,
|
|
12
|
FOREIGN KEY (userId) REFERENCES score.users(id) on delete cascade on update cascade
|
|
13
|
);
|
|
14
|
|
|
15
|
-- Create Messages Table
|
|
16
|
CREATE TABLE score.messages (
|
|
17
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
18
|
chatId int(11),
|
|
19
|
idSource int(11),
|
|
20
|
userId int(11),
|
|
21
|
isMine BOOLEAN,
|
|
22
|
seen BOOLEAN,
|
|
23
|
value TEXT,
|
|
24
|
createdAt DATETIME,
|
|
25
|
FOREIGN KEY (chatId) REFERENCES score.chats(id) on delete cascade on update cascade,
|
|
26
|
FOREIGN KEY (userId) REFERENCES score.users(id) on delete cascade on update cascade
|
|
27
|
);
|
|
28
|
|
|
29
|
CREATE TABLE messages_files (
|
|
30
|
message_id INT,
|
|
31
|
files_id INT,
|
|
32
|
PRIMARY KEY (message_id, files_id),
|
|
33
|
FOREIGN KEY (message_id) REFERENCES messages(id),
|
|
34
|
FOREIGN KEY (files_id) REFERENCES files(id)
|
|
35
|
);
|
|
36
|
|
|
37
|
CREATE TABLE chat_group (
|
|
38
|
chat_id INT,
|
|
39
|
user_id INT,
|
|
40
|
PRIMARY KEY (chat_id, user_id),
|
|
41
|
FOREIGN KEY (chat_id) REFERENCES chats(id),
|
|
42
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
43
|
);
|
|
44
|
|
|
45
|
|
|
46
|
CREATE TABLE score.notification (
|
|
47
|
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
48
|
icon varchar(255) DEFAULT NULL,
|
|
49
|
image varchar(255) DEFAULT NULL,
|
|
50
|
title varchar(255) DEFAULT NULL,
|
|
51
|
description varchar(255) DEFAULT NULL,
|
|
52
|
time datetime NOT NULL,
|
|
53
|
link varchar(255) DEFAULT NULL,
|
|
54
|
useRouter tinyint(1) DEFAULT 0,
|
|
55
|
seen tinyint(1) DEFAULT 0,
|
|
56
|
idSource int(11) NOT NULL,
|
|
57
|
idReceiver int(11) NOT NULL
|
|
58
|
);
|