CREATE DATABASE IF NOT EXISTS ICQ;
USE ICQ;

CREATE TABLE Users (
    user_id INT,
    user_login varchar(50) NOT null unique,
    user_password varchar(50) not null,
    user_email varchar(30),
    PRIMARY KEY (user_id)
);
CREATE TABLE Messages (
    message_id INT,
    message_from INT NOT null,
    message_to INT NOT null,
    message_body varchar(150),
    message_date date,
	message_not_read bool,
    primary key (message_id),
    FOREIGN KEY (message_from)
        REFERENCES Users (user_id)
        ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE Permissions (
    permission_id INT,
    permission_title varchar(10),
    primary key (permission_id)
);
CREATE TABLE Users_To_Permissions (
    user_id INT,
    permission_id INT,
    primary key (user_id , permission_id),
    FOREIGN KEY (user_id)
        REFERENCES Users (user_id)
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY (permission_id)
        REFERENCES Permissions (permission_id)
        ON UPDATE NO ACTION ON DELETE NO ACTION
);

#DELIMITER $$
#DROP TRIGGER IF EXISTS ICQ.Messages_BINS$$
#USE `ICQ`$$
#CREATE DEFINER=`root`@`localhost` TRIGGER `Messages_BINS` BEFORE INSERT ON `Messages` FOR EACH ROW
#BEGIN
# DECLARE user_id INT;
# SET user_id=(select utp.user_id from Users_To_Permissions utp where utp.user_id=NEW.message_from);
# IF user_id IS NULL THEN
#      SIGNAL sqlstate '45001' SET message_text = "Невозможно выполнить операцию. У пользователя нет прав!";
# END IF;
#END$$
#DELIMITER ;


INSERT INTO Permissions values(1,'Admin');
INSERT INTO Permissions values(2,'Moderator');
INSERT INTO Permissions values(3,'User');

INSERT INTO Users values(1,'User1','1234','u1@gmail.com');
INSERT INTO Users values(2,'User2','1234','u2@gmail.com');
INSERT INTO Users values(3,'User3','1234','u1@gmail.com');
INSERT INTO Users values(4,'User4','1234','u1@gmail.com');
INSERT INTO Users values(5,'User5','1234','u1@gmail.com');
INSERT INTO Users values(6,'Admin1','1234','a1@gmail.com');

INSERT INTO Users_To_Permissions values(1,3),(2,3),(3,3),(4,3),(5,3),(6,1);
INSERT INTO Messages values(1,1,2,"HI! 1",NOW(),true);
INSERT INTO Messages values(2,1,2,"HI! 2",NOW(),true);
INSERT INTO Messages values(3,1,2,"HI! 3",NOW(),true);
INSERT INTO Messages values(4,1,2,"HI! 4",NOW(),false);
INSERT INTO Messages values(5,1,2,"HI! 5",NOW(),true);
INSERT INTO Messages values(6,1,2,"HI! 6",NOW(),false);

INSERT INTO Messages values(7,2,4,"HI!",NOW(),true);
INSERT INTO Messages values(8,2,4,"HI!",NOW(),true);
INSERT INTO Messages values(9,2,4,"HI!",NOW(),false);
INSERT INTO Messages values(10,2,4,"HI!",NOW(),true);

INSERT INTO Messages values(11,3,1,"HI!",NOW(),true);
INSERT INTO Messages values(12,3,1,"HI!",NOW(),true);
INSERT INTO Messages values(13,3,1,"HI!",NOW(),true);
INSERT INTO Messages values(14,3,1,"HI!",NOW(),true);
INSERT INTO Messages values(15,3,1,"HI!",NOW(),true);

INSERT INTO Messages values(16,5,4,"HI!",NOW(),false);
INSERT INTO Messages values(17,5,4,"HI!",NOW(),false);
INSERT INTO Messages values(18,3,1,"HI!",NOW(),true);