drop table Band_Arist;
drop table Events;
drop table Ratings;
drop table Songs;
drop table Albums;
drop table Artist;
drop table Country;
drop table Users;
drop table Genres;
drop table Band;

create table Band(
id int NOT NULL AUTO_INCREMENT,
band_name varchar(24),
PRIMARY KEY (id)
);

create table Genres(
id int NOT NULL AUTO_INCREMENT,
genre_name varchar(24),
PRIMARY KEY (id)
);

create table Users(
id int NOT NULL AUTO_INCREMENT,
user_name varchar(24),
password varchar(24),
first_name varchar(24),
last_name varchar(24),
email varchar(24),
isAdmin boolean,
isActive boolean,
PRIMARY KEY (id)
);

create table Country(
id int NOT NULL AUTO_INCREMENT,
country_name varchar(24),
PRIMARY KEY (id)
);

create table Artist(
id int NOT NULL AUTO_INCREMENT,
artist_name varchar(24),
artist_surname varchar(24),
artist_pseudo varchar(24),
country_id int,
PRIMARY KEY (id),
FOREIGN KEY (country_id) REFERENCES Country(id) ON DELETE CASCADE
);

create table Albums(
id int NOT NULL AUTO_INCREMENT,
artist_id int,
album_name varchar(24),
album_cover_url varchar(24),
sold_quantity int,
PRIMARY KEY (id),
FOREIGN KEY (artist_id) REFERENCES Artist(id) ON DELETE CASCADE
);

create table Songs
(
id int NOT NULL AUTO_INCREMENT,
song_name varchar(24),
song_time int,
album_id int,
genre_id int,
PRIMARY KEY (id),
FOREIGN KEY (album_id) REFERENCES Albums(id) ON DELETE CASCADE,
FOREIGN KEY (genre_id) REFERENCES Genres(id) ON DELETE CASCADE
);

create table Ratings(
id int NOT NULL AUTO_INCREMENT,
song_id int,
rate int,
user_id int,
PRIMARY KEY (id),
FOREIGN KEY (song_id) REFERENCES Songs(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);


create table Events(
id int NOT NULL AUTO_INCREMENT,
event_name varchar(24),
event_date date,
band_id int,
country_id int,
price int,
PRIMARY KEY (id),
FOREIGN KEY (band_id) REFERENCES band(id) ON DELETE CASCADE,
FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE CASCADE
);

create table Band_Arist(
id int NOT NULL AUTO_INCREMENT,
artist_id int,
band_id int,
PRIMARY KEY (id),
FOREIGN KEY (artist_id) REFERENCES Artist(id) ON DELETE CASCADE,
FOREIGN KEY (band_id) REFERENCES band(id) ON DELETE CASCADE
);

insert into band values (null, 'band1');
insert into band values (null, 'band2');
insert into band values (null, 'band3');

insert into genres values (null, 'genre1');
insert into genres values (null, 'genre2');
insert into genres values (null, 'genre3');

insert into users values (null, 'username1', 'password1', 'fname1', 'sname1', 'email1', true,true);
insert into users values (null, 'username2', 'password2', 'fname2', 'sname2', 'email2', true,true);
insert into users values (null, 'username3', 'password3', 'fname3', 'sname3', 'email3', 1,1);

insert into country values (null, 'country1');
insert into country values (null, 'country2');
insert into country values (null, 'country3');

insert into artist values (null, 'name1', 'surname1', 'pseudo1', 1);
insert into artist values (null, 'name2', 'surname2', 'pseudo2', 2);
insert into artist values (null, 'name3', 'surname3', 'pseudo3', 3);

insert into albums values (null, 1, 'albumname1', 'url1', 10);
insert into albums values (null, 2, 'albumname2', 'url2', 20);
insert into albums values (null, 3, 'albumname3', 'url3', 30);

insert into songs values (null, 'song1', 1, 1, 1);
insert into songs values (null, 'song2', 1, 1, 1);
insert into songs values (null, 'song3', 1, 1, 1);
insert into songs values (null, 'song4', 1, 2, 2);
insert into songs values (null, 'song5', 1, 2, 2);
insert into songs values (null, 'song6', 1, 2, 2);
insert into songs values (null, 'song7', 1, 3, 3);
insert into songs values (null, 'song8', 1, 3, 3);
insert into songs values (null, 'song9', 1, 3, 3);

insert into ratings values (null, 1, 1, 1);
insert into ratings values (null, 1, 2, 2);
insert into ratings values (null, 2, 3, 3);
insert into ratings values (null, 2, 4, 1);
insert into ratings values (null, 3, 5, 2);
insert into ratings values (null, 3, 6, 3);
insert into ratings values (null, 4, 7, 1);
insert into ratings values (null, 4, 8, 2);
insert into ratings values (null, 5, 9, 3);
insert into ratings values (null, 5, 1, 1);
insert into ratings values (null, 6, 2, 2);
insert into ratings values (null, 6, 3, 3);
insert into ratings values (null, 7, 4, 1);
insert into ratings values (null, 7, 5, 2);
insert into ratings values (null, 8, 6, 3);
insert into ratings values (null, 8, 7, 1);
insert into ratings values (null, 9, 8, 2);
insert into ratings values (null, 9, 9, 3);