create table countries (
    country_id int auto_increment,
    country_name varchar(40),
    primary key (country_id)
);

create table cities (
    city_id int auto_increment,
    city_name varchar(40) not null,
    country_id int,
    primary key (city_id),
    foreign key (country_id) references countries(country_id)
);

create table body_types (
    body_type_id int auto_increment,
    body_type_name varchar(40),
    primary key (body_type_id)
);

create table hair_colors (
    hair_color_id int auto_increment,
    hair_color_name varchar(40),
    primary key (hair_color_id)
);

create table breast_sizes (
    breast_size_id int auto_increment,
    breast_size_name varchar(40),
    primary key (breast_size_id)
);

create table availabilities (
    availability_id int auto_increment,
    availability_name varchar(40),
    primary key (availability_id)
);

create table persons (
    person_id int auto_increment,
    person_email nvarchar(50) not null,
    person_password nvarchar(40) not null,
    person_name nvarchar(40) not null,
    person_birth_date date,
    person_height int,
    person_phone_number varchar(4),
    person_city_id int,
    person_body_type_id int,
    person_hair_color_id int,
    person_breast_size_id int,
    person_availability_id int,
    person_comment nvarchar(15000) not null,
    person_profile_picture varchar(255) not null,
    primary key (person_id),

    foreign key (person_city_id) references cities(city_id),
    foreign key (person_body_type_id) references body_types(body_type_id),
    foreign key (person_hair_color_id) references hair_colors(hair_color_id),
    foreign key (person_breast_size_id) references breast_sizes(breast_size_id),
    foreign key (person_availability_id) references availabilities(availability_id)
);



insert into countries(country_name) values('Georgia');
insert into cities(city_name, country_id) values('Tbilisi', 1);
insert into cities(city_name, country_id) values('Batumi', 1);
insert into cities(city_name, country_id) values('Kutaisi', 1);

insert into body_types(body_type_name) values("Chubby");
insert into body_types(body_type_name) values("Skinny");
insert into body_types(body_type_name) values("Athlete");
insert into body_types(body_type_name) values("Medium");

insert into breast_sizes(breast_size_name) values("(A) Small");
insert into breast_sizes(breast_size_name) values("(B) Medium");
insert into breast_sizes(breast_size_name) values("(C) Big");

insert into hair_colors(hair_color_name) values("Blonde");
insert into hair_colors(hair_color_name) values("Black");
insert into hair_colors(hair_color_name) values("Brunette");
insert into hair_colors(hair_color_name) values("Red");
insert into hair_colors(hair_color_name) values("Honey Brown");
insert into hair_colors(hair_color_name) values("Gold");
insert into hair_colors(hair_color_name) values("Silver");

insert into availabilities(availability_name) values ("Incall");
insert into availabilities(availability_name) values ("Outcall");
insert into availabilities(availability_name) values ("Incall/Outcall");