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)
);
