198 lines
7.7 KiB
SQL
198 lines
7.7 KiB
SQL
-- RJLResaka MySQL schema
|
|
-- Compatible with MySQL Workbench / MariaDB
|
|
|
|
CREATE DATABASE IF NOT EXISTS rjlresaka
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE rjlresaka;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
full_name VARCHAR(120) NOT NULL,
|
|
username VARCHAR(60) NOT NULL UNIQUE,
|
|
email VARCHAR(120) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
avatar_color VARCHAR(20) NOT NULL DEFAULT '#1877f2',
|
|
bio VARCHAR(255) NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id INT NOT NULL,
|
|
token VARCHAR(120) NOT NULL UNIQUE,
|
|
expires_at DATETIME NOT NULL,
|
|
used TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_password_reset_user
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
name VARCHAR(120) NULL,
|
|
avatar_color VARCHAR(20) NOT NULL DEFAULT '#E7F3FF',
|
|
is_group TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_by INT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_conversation_creator
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
ON DELETE SET NULL
|
|
);
|
|
|
|
ALTER TABLE conversations ADD COLUMN IF NOT EXISTS name VARCHAR(120) NULL;
|
|
ALTER TABLE conversations ADD COLUMN IF NOT EXISTS avatar_color VARCHAR(20) NOT NULL DEFAULT '#E7F3FF';
|
|
ALTER TABLE conversations ADD COLUMN IF NOT EXISTS is_group TINYINT(1) NOT NULL DEFAULT 0;
|
|
ALTER TABLE conversations ADD COLUMN IF NOT EXISTS created_by INT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS conversation_participants (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
conversation_id INT NOT NULL,
|
|
user_id INT NOT NULL,
|
|
role VARCHAR(30) NOT NULL DEFAULT 'member',
|
|
joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_conv_part_conversation
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_conv_part_user
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT uq_conv_part UNIQUE (conversation_id, user_id)
|
|
);
|
|
|
|
ALTER TABLE conversation_participants ADD COLUMN IF NOT EXISTS role VARCHAR(30) NOT NULL DEFAULT 'member';
|
|
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
conversation_id INT NOT NULL,
|
|
sender_id INT NOT NULL,
|
|
body TEXT NULL,
|
|
attachment_name VARCHAR(255) NULL,
|
|
attachment_path VARCHAR(255) NULL,
|
|
attachment_type VARCHAR(120) NULL,
|
|
attachment_size BIGINT NULL,
|
|
is_edited TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_deleted TINYINT(1) NOT NULL DEFAULT 0,
|
|
seen_at DATETIME NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_message_conversation
|
|
FOREIGN KEY (conversation_id) REFERENCES conversations(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_message_sender
|
|
FOREIGN KEY (sender_id) REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS message_reactions (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
message_id INT NOT NULL,
|
|
user_id INT NOT NULL,
|
|
emoji VARCHAR(20) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_reaction_message
|
|
FOREIGN KEY (message_id) REFERENCES messages(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_reaction_user
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT uq_message_reaction UNIQUE (message_id, user_id, emoji)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS friend_requests (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
sender_id INT NOT NULL,
|
|
receiver_id INT NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
responded_at DATETIME NULL,
|
|
CONSTRAINT fk_friend_request_sender
|
|
FOREIGN KEY (sender_id) REFERENCES users(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_friend_request_receiver
|
|
FOREIGN KEY (receiver_id) REFERENCES users(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT uq_friend_request UNIQUE (sender_id, receiver_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS friends (
|
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
|
user_one_id INT NOT NULL,
|
|
user_two_id INT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_friends_user_one
|
|
FOREIGN KEY (user_one_id) REFERENCES users(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT fk_friends_user_two
|
|
FOREIGN KEY (user_two_id) REFERENCES users(id)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT uq_friends_pair UNIQUE (user_one_id, user_two_id)
|
|
);
|
|
|
|
INSERT INTO users (full_name, username, email, password_hash, avatar_color, bio)
|
|
SELECT 'Demo User', 'demo', 'demo@rjlresaka.app', '$2a$10$u6N8G6s8wWC4b7A9iI5L8e2ZfQFlA95zT4zWS3TzFmpXQxwCLWv0W', '#1877f2', 'Compte de démonstration'
|
|
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'demo@rjlresaka.app');
|
|
|
|
INSERT INTO users (full_name, username, email, password_hash, avatar_color, bio)
|
|
SELECT 'Alice Miora', 'alice', 'alice@rjlresaka.app', '$2a$10$u6N8G6s8wWC4b7A9iI5L8e2ZfQFlA95zT4zWS3TzFmpXQxwCLWv0W', '#42b72a', 'Étudiante L3 GL'
|
|
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'alice@rjlresaka.app');
|
|
|
|
INSERT INTO users (full_name, username, email, password_hash, avatar_color, bio)
|
|
SELECT 'Junior Ranaivo', 'junior', 'junior@rjlresaka.app', '$2a$10$u6N8G6s8wWC4b7A9iI5L8e2ZfQFlA95zT4zWS3TzFmpXQxwCLWv0W', '#ff8a00', 'Compte de test pour la messagerie'
|
|
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'junior@rjlresaka.app');
|
|
|
|
INSERT INTO friends (user_one_id, user_two_id)
|
|
SELECT LEAST(u1.id, u2.id), GREATEST(u1.id, u2.id)
|
|
FROM users u1, users u2
|
|
WHERE u1.email = 'demo@rjlresaka.app'
|
|
AND u2.email = 'alice@rjlresaka.app'
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM friends f
|
|
WHERE f.user_one_id = LEAST(u1.id, u2.id)
|
|
AND f.user_two_id = GREATEST(u1.id, u2.id)
|
|
);
|
|
|
|
INSERT INTO friend_requests (sender_id, receiver_id, status)
|
|
SELECT sender.id, receiver.id, 'pending'
|
|
FROM users sender, users receiver
|
|
WHERE sender.email = 'junior@rjlresaka.app'
|
|
AND receiver.email = 'demo@rjlresaka.app'
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM friend_requests fr
|
|
WHERE fr.sender_id = sender.id AND fr.receiver_id = receiver.id
|
|
);
|
|
|
|
INSERT INTO conversations (name, avatar_color, is_group, created_by)
|
|
SELECT 'Groupe Projet Final', '#E7F3FF', 1, owner.id
|
|
FROM users owner
|
|
WHERE owner.email = 'demo@rjlresaka.app'
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM conversations c WHERE c.name = 'Groupe Projet Final' AND c.is_group = 1
|
|
);
|
|
|
|
INSERT INTO conversation_participants (conversation_id, user_id, role)
|
|
SELECT c.id, u.id,
|
|
CASE WHEN u.email = 'demo@rjlresaka.app' THEN 'admin' ELSE 'member' END
|
|
FROM conversations c
|
|
JOIN users u ON u.email IN ('demo@rjlresaka.app', 'alice@rjlresaka.app', 'junior@rjlresaka.app')
|
|
WHERE c.name = 'Groupe Projet Final' AND c.is_group = 1
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM conversation_participants cp
|
|
WHERE cp.conversation_id = c.id AND cp.user_id = u.id
|
|
);
|
|
|
|
INSERT INTO messages (conversation_id, sender_id, body)
|
|
SELECT c.id, owner.id, 'Bienvenue dans le groupe du projet final RJLResaka !'
|
|
FROM conversations c
|
|
JOIN users owner ON owner.email = 'demo@rjlresaka.app'
|
|
WHERE c.name = 'Groupe Projet Final' AND c.is_group = 1
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM messages m WHERE m.conversation_id = c.id AND m.sender_id = owner.id
|
|
);
|
|
|
|
-- Mot de passe démo attendu: demo123
|