1     1
      +--------users------+
      |                   |
     *|                   |*
    tweets--------------likes
          1            *
    

CREATE DATABASE IF NOT EXISTS tweeties;

USE tweeties;

DROP TABLE IF EXISTS likes, tweeties, users;

-- 
-- USERS
-- 

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE CHECK(username REGEXP '^[^@]+@[^@]+\\.[^@]+$'), -- CHECK
    pwd VARCHAR(64) NOT NULL
);

--
-- TRIGGER
--

-- Pour pouvoir faire des ; dans le trigger
DELIMITER &&

    -- Comment éviter de répéter le même code dans 2 triggers?
    -- https://mariadb.com/kb/en/stored-procedures/
    --
    --
    --
    -- CREATE OR REPLACE PROCEDURE validate (in pwd VARCHAR(100))
    -- BEGIN
    --     IF( pwd NOT REGEXP '\\d+' ) THEN
    --         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 number.';
    --     END IF;
    -- 
    --     IF( BINARY pwd NOT REGEXP '[a-z]+' ) THEN
    --         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 lower case letter.';
    --     END IF;
    -- 
    --     IF( BINARY pwd NOT REGEXP '[A-Z]+' ) THEN
    --         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 upper case letter.';
    --     END IF;
    -- 
    --     IF( CHAR_LENGTH(pwd) < 8) THEN
    --         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 8 characters long.';
    --     END IF;
    -- END&&
    --
    --
    --
    -- Puis dans le trigger
    --
    -- call validate(new.pwd);

    CREATE OR REPLACE TRIGGER users_insert_hash_password BEFORE INSERT ON users FOR EACH ROW 
    BEGIN

        IF( new.pwd NOT REGEXP '\\d+' ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 number.';
        END IF;

        IF( BINARY new.pwd NOT REGEXP '[a-z]+' ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 lower case letter.';
        END IF;

        IF( BINARY new.pwd NOT REGEXP '[A-Z]+' ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 upper case letter.';
        END IF;

        IF( CHAR_LENGTH(new.pwd) < 8) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 8 characters long.';
        END IF;

        SET new.pwd = SHA2(new.pwd, 256);

    END&&

    CREATE OR REPLACE TRIGGER users_update_hash_password BEFORE UPDATE ON users FOR EACH ROW 
    BEGIN

        IF( new.pwd NOT REGEXP '\\d+' ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 number.';
        END IF;

        IF( BINARY new.pwd NOT REGEXP '[a-z]+' ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 lower case letter.';
        END IF;

        IF( BINARY new.pwd NOT REGEXP '[A-Z]+' ) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 1 upper case letter.';
        END IF;

        IF( CHAR_LENGTH(new.pwd) < 8) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must contain at least 8 characters long.';
        END IF;

        SET new.pwd = SHA2(new.pwd, 256);
    END&&

DELIMITER ;



INSERT INTO 
    users(username, pwd) 
VALUES  
    ('alice@mail.com', 'pwdpwdA1'), 
    ('bob@mail.com', 'pwdpwdB2'), 
    ('charlie@mail.com', 'pwdpwdC3');


--
-- TWEETIES
--

CREATE TABLE tweeties(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    text VARCHAR(240) NOT NULL CHECK(trim(text) != ''), -- CHECK
    created_at DATETIME NOT NULL DEFAULT NOW(),
    updated_at DATETIME,
    user_id INT UNSIGNED NOT NULL,

    -- CHECK
    CONSTRAINT created_less_than_updated CHECK(ifnull(created_at < updated_at, true)),

    FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE CASCADE
);

DELIMITER &&

CREATE OR REPLACE TRIGGER tweeties_create_created_at BEFORE INSERT ON tweeties FOR EACH ROW 
BEGIN
    SET new.created_at = NOW();
END&&

CREATE OR REPLACE TRIGGER tweeties_update_updated_at BEFORE UPDATE ON tweeties FOR EACH ROW 
BEGIN
    SET new.updated_at = NOW();
END&&

DELIMITER ;

INSERT INTO tweeties (text, created_at, user_id)
VALUES  
    ('My first tweetie', DATE_ADD(NOW(), INTERVAL -36 HOUR), 1),
    ("Another tweetie", DATE_ADD(NOW(), INTERVAL -5 SECOND), 1),
    ("Tweetie, here I am!!!1!11!!", DATE_ADD(NOW(), INTERVAL -49 HOUR), 2);

--
-- LIKES
--

CREATE TABLE likes (
    user_id INT UNSIGNED NOT NULL,
    tweety_id INT UNSIGNED NOT NULL,

    PRIMARY KEY (user_id, tweety_id),

    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE,

    FOREIGN KEY (tweety_id) REFERENCES tweeties(id)
    ON DELETE CASCADE
);

INSERT INTO 
    likes
VALUES 
    (1, 1), 
    (2, 1), 
    (2, 2);

--
-- VIEW
-- 

CREATE OR REPLACE VIEW tweets AS
SELECT 
    tweeties.id,
    tweeties.text,
    tweeties.user_id,
    username,
    COALESCE(updated_at, created_at) AS `last_edit`,
    COUNT(likes.user_id) AS `likes_count` 
FROM tweeties 
INNER JOIN users ON user_id = users.id
LEFT JOIN likes ON tweeties.id = likes.tweety_id
GROUP BY tweeties.id
ORDER BY last_edit DESC;

--
-- TESTS
--

SELECT * FROM users;

-- Pas email
-- INSERT INTO users VALUES (DEFAULT, 'a', 'pwdPWD123');

-- Pas password valide
-- INSERT INTO users VALUES (DEFAULT, 'a@a.a', 'pwd');

-- Text vide
-- INSERT INTO tweeties(text, user_id) VALUES ('   ', 1);

-- Password update trigger
-- password invalide
-- UPDATE users SET pwd = 'non' WHERE id = 1;

-- password hash apres update
UPDATE 
    users
SET 
    pwd = 'pwdaAa111'
WHERE id = 1;

SELECT * FROM users WHERE id = 1;

-- Tweeties

SELECT * FROM tweets;

-- created_at trigger
-- insert into tweeties(text, created_at, user_id) values ('mauvaise created at', DATE_ADD(NOW(), INTERVAL 36 YEAR), 1);

-- updated_at trigger
UPDATE 
    tweeties
SET
    text = UPPER(text)
WHERE id = 1;

-- invalid created_at
-- UPDATE tweeties SET created_at = NOW() WHERE id = 1;

SELECT * FROM tweets;