/*
create table if not exists users (
    id int not null auto_increment,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    username varchar(255) not null,
    password varchar(255) not null,
    primary key (id)
);

create table if not exists addresses (
    id integer not null auto_increment,
    user_id integer not null,
    street_address varchar(255) not null,
    city varchar(255) not null,
    state varchar(255) not null,
    zip_code varchar(255) not null,
    primary key (id),
    foreign key (user_id) references users(id)
);

create table if not exists products (
    id integer not null auto_increment,
    name varchar(255) not null,
    price decimal(5,2),
    primary key (id)
);

-- Would be okay to combine "orders" and "order_items"
create table if not exists orders (
    id integer not null auto_increment,
    user_id integer not null,
    address_id integer not null,
    primary key (id),
    foreign key (user_id) references users(id),
    foreign key (address_id) references addresses(id)
);

create table if not exists order_items (
    order_id integer not null,
    product_id integer not null,
    quantity integer not null,
    foreign key (order_id) references orders(id),
    foreign key (product_id) references products(id)
);

create table if not exists product_reviews (
    product_id integer not null,
    user_id integer not null,
    comment varchar(255) not null,
    foreign key (product_id) references products(id),
    foreign key (user_id) references users(id)
);

create table if not exists likes (
    review_id integer not null,
    user_id integer not null,
    foreign key (review_id) references product_reviews(id),
    foreign key (user_id) references users(id)
);

alter table product_reviews ADD id integer not null auto_increment primary key;
*/

create table if not exists messages (
    message_id integer not null auto_increment primary key,
    recipient_id integer not null,
    sender_id integer not null,
    message varchar(255) not null,
    sent_at timestamp default current_timestamp,
    foreign key (recipient_id) references users(id),
    foreign key (sender_id) references users(id)
);

/* if you want to do a group message system, here's an idea of the structure
create table if not exists groups (
    id integer not null auto_increment primary key,
    name varchar(255)
);
create table if not exists messages (
    id integer not null auto_increment primary key,
    recipient_id integer,
    group_id integer,
    sender_id integer not null,
    message varchar(255) not null,
    sent_at timestamp default current_timestamp,
    foreign key (recipient_id) references users(id),
    foreign key (sender_id) references users(id),
    foreign key (group_id) references groups(id)
);
create table if not exists groups_members (
    group_id integer not null,
    user_id integer not null,
    foreign key (group_id) references groups(id),
    foreign key (user_id) references users(id)
);
create table if not exists message_reactions (
    message_id integer not null,
    user_id integer not null,
    reaction integer not null,
    foreign key (message_id) references message(id),
    foreign key (user_id) references users(id)
);
*/
