create database BookShop;
use BookShop;

create table countries (
	id int unsigned not null auto_increment primary key,
    name varchar(50) not null unique
);

create table authors (
	id int unsigned not null auto_increment primary key,
    name varchar(255) not null,
    surname varchar(255) not null,
    countryId int unsigned not null,
    constraint foreign key authors_countryId (countryId)
		references countries (id)
);

create table themes (
	id int unsigned not null auto_increment primary key,
    name varchar(100) not null unique
);

create table books (
	id int unsigned not null auto_increment primary key,
    name varchar(255) not null,
    pages int not null default 0,
    price decimal(10, 2) not null,
    publishDate date not null,
    authorId int unsigned not null,
    themeId int unsigned not null,
    constraint foreign key books_authorId (authorId)
		references authors (id),
	constraint foreign key books_themeId (themeId)
		references themes (id)
);

create table shops (
	id int unsigned not null auto_increment primary key,
    name varchar(255) not null,
    countryId int unsigned not null,
    constraint foreign key shops_countryId (countryId)
		references countries (id)
);

create table sales (
	id int unsigned not null auto_increment primary key,
    price decimal(10, 2) not null,
    quantity int not null,
    saleDate datetime not null default current_timestamp,
    bookId int unsigned not null,
    shopId int unsigned not null,
    constraint foreign key sales_bookId (bookId)
		references books (id),
	constraint foreign key sales_shopId (shopId)
		references shops (id)
);

delimiter //
create procedure setCountries()
begin
	insert into countries (name) values
    ('Россия'), ('Украина'), ('Беларусь');
end//
delimiter ;

delimiter //
create procedure setShops()
begin
	set @i = 1;
    set @maxId = (select max(id) from countries);
	loop1: loop
		insert into shops (name, countryId)
        values (concat('shop', @i), floor(rand() * @maxId) + 1);
        set @i = @i + 1;
        if @i > 10 then leave loop1; end if;
    end loop loop1;
end//
delimiter ;

delimiter //
create procedure setAuthors()
begin
	set @i = 1;
    set @maxId = (select max(id) from countries);
	loop1: loop
		insert into authors (name, surname, countryId)
        values (
			concat('author', @i),
            concat('surname', @i),
            floor(rand() * @maxId) + 1
		);
        set @i = @i + 1;
        if @i > 100 then leave loop1; end if;
    end loop loop1;
end//
delimiter ;

delimiter //
create procedure setThemes()
begin
	insert into themes (name)
    values ('Детектив'), ('Роман'), ('Фэнтези');
end//
delimiter ;

delimiter //
create procedure setBooks()
begin
	set @i = 1;
	loop1: loop
		set @authorId = (
			select id from authors order by rand() limit 1
		);
        set @themeId = (
			select id from themes order by rand() limit 1
        );
		insert into books (
			name, pages, price, publishDate, authorId, themeId
        ) values (
			concat('book', @i), floor(rand() * 500) + 20,
            rand() * 500000 / 100 + 100,
            current_timestamp(), @authorId, @themeId
        );
        set @i = @i + 1;
        if @i > 500 then leave loop1; end if;
    end loop loop1;
end//
delimiter ;

call setBooks();

delimiter //
create procedure setSales()
begin
	set @i = 1;
	loop1: loop
		set @bookId = (select id from books order by rand() limit 1);
        set @price = (select price from books where id = @bookId);
        set @shopId = (select id from shops order by rand() limit 1);
        set @quantity = (select floor(rand() * 100) + 1);
		insert into sales (price, quantity, saleDate, bookId, shopId)
        values (@price, @quantity, current_timestamp(), @bookId, @shopId);
        set @i = @i + 1;
        if @i > 1000 then leave loop1; end if;
    end loop loop1;
end//
delimiter ;

call setCountries();
call setShops();
call setAuthors();
call setThemes();
call setBooks();
call setSales();