use score;
-- Create Chats Table
CREATE TABLE score.chats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  idSource int(11),
  userId int(11),
  unreadCount INT,
  muted BOOLEAN,
  isGroup BOOLEAN,	
  lastMessage VARCHAR(255),
  lastMessageAt DATETIME,
  FOREIGN KEY (userId) REFERENCES score.users(id) on delete cascade on update cascade
);

-- Create Messages Table
CREATE TABLE score.messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  chatId int(11),
  idSource int(11),
  userId int(11),
  isMine BOOLEAN,
  seen BOOLEAN,
  value TEXT,
  createdAt DATETIME,
  FOREIGN KEY (chatId) REFERENCES score.chats(id) on delete cascade on update cascade,
  FOREIGN KEY (userId) REFERENCES score.users(id) on delete cascade on update cascade
);

CREATE TABLE messages_files (
    message_id INT,
    files_id INT,
    PRIMARY KEY (message_id, files_id),
    FOREIGN KEY (message_id) REFERENCES messages(id),
    FOREIGN KEY (files_id) REFERENCES files(id)
);

CREATE TABLE chat_group (
    chat_id INT,
    user_id INT,
    PRIMARY KEY (chat_id, user_id),
    FOREIGN KEY (chat_id) REFERENCES chats(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);


CREATE TABLE score.notification (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  icon varchar(255) DEFAULT NULL,
  image varchar(255) DEFAULT NULL,
  title varchar(255) DEFAULT NULL,
  description varchar(255) DEFAULT NULL,
  time datetime NOT NULL,
  link varchar(255) DEFAULT NULL,
  useRouter tinyint(1) DEFAULT 0,
  seen tinyint(1) DEFAULT 0,
  idSource int(11) NOT NULL,
  idReceiver int(11) NOT NULL
);