script

use socialnetwork;

create table users
(
    id         int auto_increment
        primary key,
    first_name varchar(50)  null,
    last_name  varchar(50)  null,
    email      varchar(50)  null,
    password   varchar(255) null,
    gender     varchar(50)  null,
    enabled    boolean  null,
    constraint users_email_uindex
        unique (email)
);

create table authorities
(
    authority varchar(50) null,
    username  varchar(50) null,
    constraint authorities_users_username_fk
        foreign key (username) references users (email)
);

create table connections
(
    user_one_id int auto_increment
        primary key,
    user_two_id int         null,
    status      varchar(50) null,
    constraint connections_users_id_fk
        foreign key (user_one_id) references users (id),
    constraint connections_users_id_fk_2
        foreign key (user_two_id) references users (id)
);

create table photos
(
    id        int auto_increment
        primary key,
    is_public boolean null,
    user_id   int        null,
    constraint photo_user_fk
        foreign key (user_id) references users (id)
);

create table posts
(
    id        int auto_increment
        primary key,
    content   text       null,
    likes     int        null,
    is_public boolean null,
    post_date datetime   null,
    user_id   int        null,
        unique (user_id),
    constraint post_user_fk
        foreign key (user_id) references users (id)
);

create table comments
(
    id           int auto_increment
        primary key,
    content      text     null,
    likes        int      null,
    comment_date datetime null,
    user_id      int      null,
    post_id      int      null,
    constraint comment_post_fk
        foreign key (post_id) references posts (id),
    constraint comment_user_fk
        foreign key (user_id) references users (id)
);

create table user_additional_info
(
    id         int          null,
    image_path varchar(255) null,
    city       varchar(50)  null,
    country    int          null,
    about      text         null,
    constraint user_additional_info_users_id_fk
        foreign key (id) references users (id)
);