#1
create table cities
(
	id int(11) primary key auto_increment,
    name varchar(30) not null,
    country_name varchar(80) not null
);

#2
create table users
(
	id int(11) primary key auto_increment,
    username varchar(50) not null unique,
    password varchar(255) not null,
    first_name varchar(50) not null,
    last_name varchar(50),
    age int(11) not null ,
    check (age>=18),
    money decimal(15,2) not null,
    city_id int(11) not null,
    constraint fk_users_cities foreign key(city_id) references cities(id),
    register_date date not null
);

#3
create table orders
(
	id int(11) primary key auto_increment,
    user_id int(11) not null,
    constraint fk_orders_users foreign key(user_id) references users(id),
    order_date date not null,
    is_completed boolean default false
);

#4
create table plants
(
	id int(11) primary key auto_increment,
    name varchar(50) not null,
    price decimal(15,2) not null,
    color varchar(50),
    quantity int(11) default 0
);

#5
create table info_plants
(
	id int(11) primary key auto_increment,
    plant_id int(11) not null,
    constraint fk_info_plants_plants foreign key(plant_id) references plants(id),
    family varchar(50) not null,
    genus varchar(40) not null,
    purpose varchar(60)
);

#6
create table plants_orders
(
	plant_id int(11) not null,
    constraint fk_plants_orders_plants foreign key (plant_id) references plants(id),
    order_id int(11)  not null,
    constraint fk_plants_orders_orders foreign key (order_id) references orders(id),
    constraint ck_plants_orders primary key(plant_id,order_id)
);