create table user(
usr_id INT NOT NULL AUTO_INCREMENT,
email varchar(70) NOT NULL UNIQUE,
password blob NOT NULL,
display_name varchar(4000),
birth_date char(10) NOT NULL,
gitlab_API_key varchar(20),
img_ref varchar(100),
personal_quote TEXT,
PRIMARY KEY(usr_id)
);


create table conversation(
conv_id INT NOT NULL AUTO_INCREMENT,
title varchar(200),
usr1_id INT NOT NULL,
usr2_id INT NOT NULL,
PRIMARY KEY(conv_id),
FOREIGN KEY(usr1_id) REFERENCES user(usr_id),
FOREIGN KEY(usr2_id) REFERENCES user(usr_id)
);

create table user_conversation(
usr_id INT NOT NULL,
conv_id INT NOT NULL,
FOREIGN KEY(usr_id) REFERENCES user(usr_id),
FOREIGN KEY(conv_id) REFERENCES conversation(conv_id),
PRIMARY KEY(usr_id, conv_id)
);

create table message(
msg_id INT NOT NULL AUTO_INCREMENT,
conv_id INT NOT NULL,
from_id INT NOT NULL,
create_date char(10) NOT NULL,
content_txt TEXT NOT NULL,
PRIMARY KEY(msg_id),
FOREIGN KEY(conv_id) REFERENCES conversation(conv_id),
FOREIGN KEY(from_id) REFERENCES user(usr_id)
);

create table post(
post_id INT NOT NULL AUTO_INCREMENT,
usr_id INT NOT NULL,
create_date char(10) NOT NULL,
content_txt TEXT NOT NULL,
PRIMARY KEY(post_id),
FOREIGN KEY(usr_id) REFERENCES user(usr_id)
);

create table post_fav(
usr_id INT NOT NULL,
post_id INT NOT NULL,
PRIMARY KEY(usr_id, post_id),
FOREIGN KEY(usr_id) REFERENCES user(usr_id),
FOREIGN KEY(post_id) REFERENCES post(post_id)
);

create table repo(
repo_id INT NOT NULL,
name_change_date char(10),
PRIMARY KEY(repo_id)
);

create table repo_fav(
usr_id INT NOT NULL,
repo_id INT NOT NULL,
PRIMARY KEY(usr_id, repo_id),
FOREIGN KEY(usr_id) REFERENCES user(usr_id),
FOREIGN KEY(repo_id) REFERENCES repo(repo_id)
);

create table user_follow(
stalker_id INT NOT NULL,
subject_id INT NOT NULL,
PRIMARY KEY(stalker_id, subject_id),
FOREIGN KEY(stalker_id) REFERENCES user(usr_id),
FOREIGN KEY(subject_id) REFERENCES user(usr_id)
);

create table login(
ssh_key varchar(512) NOT NULL,
usr_id INT NOT NULL,
create_date char(10) NOT NULL,
expire_date char(10)
);

alter table user modify display_name NOT NULL varchar(4000) CHARACTER SET utf8 COLLATE utf8_unicode_ci;