truncate table movies, genres, movie_genres, customers, cinema_halls, seats, screenings, vouchers, reservations, tickets, prices restart identity  cascade;

drop table if exists first_names cascade;
create table first_names ( first_name varchar);
insert into first_names (first_name) values
    ('James'), ('Willie'), ('Chad'), ('Zachary'), ('Mathew'),
    ('John'), ('Ralph'), ('Jacob'), ('Corey'), ('Tyrone'),
    ('Robert'), ('Lawrence'), ('Lee'), ('Herman'), ('Darren'),
    ('Michael'), ('Nicholas'), ('Melvin'), ('Maurice'), ('Lonnie'),
    ('William'), ('Roy'), ('Alfred'), ('Vernon'), ('Lance'),
    ('David'), ('Benjamin'), ('Kyle'), ('Roberto'), ('Cody'),
    ('Peter'), ('Anton'), ('Ján'), ('Emil'), ('Matúš'),
    ('Bruno'), ('Gabriel'), ('Daniel'), ('Andrej'), ('Marek'),
    ('Erik'), ('Vlado'), ('Igor'), ('Milan'), ('Norbert'),
    ('Ivan'), ('Jozef'), ('Jakub'), ('Henrich'), ('Martin'),
    ('Vít'), ('Hugo'), ('Alfred'), ('Rudolf'), ('Filip'),
    ('Ctibor'), ('Dezider'), ('Marián'), ('Branislav'), ('Karol');

create or replace function random_first_name() returns varchar language sql as $$
    select first_name from first_names order by random()+1 limit 1;
$$;

drop table if exists last_names cascade;
create table last_names ( last_name varchar);
insert into last_names (last_name) values
    ('Smith'), ('Jones'), ('Taylor'), ('Williams'), ('Brown'),
    ('Davies'), ('Evans'), ('Wilson'), ('Thomas'), ('Roberts'),
    ('Johnson'), ('Lewis'), ('Walker'), ('Robinson'), ('Wood'),
    ('Thompson'), ('White'), ('Watson'), ('Jackson'), ('Wright'),
    ('Green'), ('Harris'), ('Cooper'), ('King'), ('Lee'),
    ('Martin'), ('Clarke'), ('James'), ('Morgan'), ('Hughes'),
    ('Edwards'), ('Hill'), ('Moore'), ('Clark'), ('Harrison'),
    ('Scott'), ('Young'), ('Morris'), ('Hall'), ('Ward'),
    ('Turner'), ('Carter'), ('Phillips'), ('Mitchell'), ('Patel'),
    ('Adams'), ('Campbell'), ('Anderson'), ('Allen'), ('Cook'),
    ('Vach'), ('Mihok'), ('Laurinec'), ('Klányi'), ('Vasky'),
    ('Daňo'), ('Blaha'), ('Molnár'), ('Jurečka'), ('Novák'),
    ('Granec'), ('Maštalír'), ('Tóth'), ('Ursíny'), ('Dočolomanský'),
    ('Kotleba'), ('Danko'), ('Sulík'), ('Fico'), ('Harabin'),
    ('Matovič'), ('Slota'), ('Mečiar'), ('Kiska'), ('Fischer'),
    ('Drevo'), ('Kuťka'), ('Jakubec'), ('Kandráč'), ('Kollár');

create or replace function random_last_name() returns varchar language sql as $$
    select last_name from last_names order by random()+1 limit 1;
$$;


drop table if exists months cascade;
create table months ( month varchar(5));
insert into months (month) values
     ('1'), ('2'), ('3'), ('4'), ('5'),
     ('6'), ('7'), ('8'), ('9'), ('10'),
     ('11'), ('12');

create or replace function random_month() returns varchar language sql as $$
    select month from months order by random()+1 limit 1;
$$;

do $$
    declare firstName varchar;
    declare lastName varchar;
    declare date varchar;
    declare year int;
    declare age int;
    begin
        for r in 1..100
            loop
                firstName := random_first_name();
                lastName := random_last_name();
                year := floor(random() * (2004-1950)+1950);
                age := 2022 - year;
                date := year || '-' || random_month() || '-' || floor(random()*28+1);
                insert into "customers" (firstname,lastname, email, dateofbirth)
                select firstName, lastName,
                       firstName || lastName || '@gmail.com',
                       cast(date as date);
            end loop;
end $$;

create or replace function random_customer() returns int language sql as $$
    select customer_id from customers order by random()+1 limit 1;
$$;

insert into genres (name) values
    ('Action'), ('Adventure'), ('Animation'), ('Comedy'),
    ('Crime'), ('Drama'), ('Family'), ('Fantasy'),
    ('Film-Noir'), ('History'), ('Horror'), ('Mystery'),
    ('Romance'), ('Sci-Fi'), ('Thriller'), ('War'),
    ('Western');

insert into movies (name, length, description) values
    ('Interstellar', 180, 'The adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.'),
    ('The Shawshank Redemption', 110, 'Framed in the 1940s for the double murder of his wife and her lover, upstanding banker Andy Dufresne begins a new life at the Shawshank prison, where he puts his accounting skills to work for an amoral warden.'),
    ('Training Day', 140, 'On his first day on the job as a narcotics officer, a rookie cop works with a rogue detective who isn''t what he appears.'),
    ('Atlantis: The Lost Empire', 90, 'The world''s most highly qualified crew of archaeologists and explorers is led by historian Milo Thatch as they board the incredible 1,000-foot submarine Ulysses and head deep into the mysteries of the sea. '),
    ('Just Go with It', 140, 'A plastic surgeon, romancing a much younger schoolteacher, enlists his loyal assistant to pretend to be his soon to be ex-wife, in order to cover up a careless lie'),
    ('Harts War', 110, 'Fourth-generation Army Col. William McNamara is imprisoned in a brutal German POW camp.'),
    ('The Heat', 150, 'Uptight and straight-laced, FBI Special Agent Sarah Ashburn is a methodical investigator with a reputation for excellence--and hyper-arrogance. Shannon Mullins, one of Boston P.D.''s ''finest,'' is foul-mouthed and has a very short fuse, and uses her gut instinct and street smarts to catch the most elusive criminals.'),
    ('Pat a Mat', 160, 'Krasny film veru'),
    ('Matko Kubko', 90, 'Velmi detailne spracovany film joj'),
    ('Iron Man', 170, 'Typek zo zeleza, velmi silny a krasny'),
    ('Hulk', 130, 'Zeleny typek velmi silny, hadze auta na vojakov'),
    ('Thor', 110, 'Ujo vie hadzat blesky a klepe ludi s kladivom'),
    ('Dummy movie', 100, 'Nejaky random text nedbam'),
    ('The asian rice cooker', 70, 'Mmmm rice tasty'),
    ('Redbull', 30, 'Just a bunch of redbull advertisements'),
    ('Living in the jungle', 76, 'Documentary about a jungle and a man, very interesting'),
    ('Computer geek', 88, 'Young IT student and his troubles at school'),
    ('Drug addict', 190, 'Reformed drug addict becomes a top class wolf on Wallstreet'),
    ('Lamp', 120, 'Pure light experience, fascinating'),
    ('The life of Fildo', 250, 'Fildo wants to sleep ale DB projekt treba robit, sadge');

insert into movie_genres (movie_id, genre_id) values
    (1, 2), (1, 6), (1, 14),
    (2, 6), (2, 5),
    (3, 1), (3, 5), (3, 15),
    (4, 2), (4, 7), (4, 2), (4, 14),
    (5, 4), (5, 13),
    (6, 6), (6, 16),
    (7, 1), (7, 4), (7, 5),
    (8,2), (8, 3), (8,4),
    (9, 2), (9, 3), (9, 4),
    (10, 1), (10, 6), (10,11),
    (11, 2), (11, 8),
    (12, 4), (12, 9),
    (13, 7), (13, 15),
    (14, 17), (14, 13),
    (15, 16),
    (16, 3), (16, 7), (16, 8),
    (17, 9), (17, 10), (17, 11),
    (18, 13), (18, 14),
    (19, 14), (19, 7),
    (20, 1), (20, 2), (20,3);

create or replace function random_movie() returns int language sql as $$
    select movie_id from movies order by random()*20 limit 1;
$$;

insert into cinema_halls (halllabel, rowcount, seatsperrow, screentype) values
    ('S1', 10, 16, 'BASIC'), ('S2', 10, 16, 'SUPER_SCREEN'),
    ('S3', 10, 16, 'THREE_DIMENSIONAL'), ('S4', 10, 16, 'BASIC'),
    ('S5', 10, 16, 'THREE_DIMENSIONAL'), ('S6', 10, 16, 'SUPER_SCREEN');

insert into prices (value, customertype, screeningtype) VALUES
    (5.6, 'CHILD', 'BASIC'),(6.2, 'STUDENT', 'BASIC'),(7.0, 'ADULT', 'BASIC'),(6.0, 'SENIOR', 'BASIC'),
    (7.6, 'CHILD', 'THREE_DIMENSIONAL'),(8.2, 'STUDENT', 'THREE_DIMENSIONAL'),(9.0, 'ADULT', 'THREE_DIMENSIONAL'),(8.0, 'SENIOR', 'THREE_DIMENSIONAL'),
    (6.6, 'CHILD', 'SUPER_SCREEN'),(7.2, 'STUDENT', 'SUPER_SCREEN'),(8.0, 'ADULT', 'SUPER_SCREEN'),(7.0, 'SENIOR', 'SUPER_SCREEN');

create or replace function get_price(customer varchar, screen varchar) returns double precision language sql as $$
    select value FROM prices WHERE customertype=customer AND screeningtype=screen;
$$;

create or replace function random_hall() returns int language sql as $$
    select cinemahall_id from cinema_halls order by random()*6+1;
$$;

do $$
    declare row int;
    declare seats int;
    BEGIN
        for hall in 1..6
            loop
                row := (SELECT rowcount from cinema_halls where cinemahall_id=hall);
                seats := (SELECT h.seatsperrow FROM cinema_halls h WHERE cinemahall_id=hall);
                for x in 1..row
                    loop
                        for y in 1..seats
                            loop
                                insert into seats (hall_id, row, seatnumber) values (hall, x, y);
                            end loop;
                    end loop;
            end loop;
    end;
$$;

do $$
    declare ddate varchar;
    declare rows int;
    declare seats int;
    declare seatnum int;
    declare seat int;
    declare hall int;
    BEGIN
        for n in 1..50
            loop
--             2022-03-01 13:50:00.000000
                ddate := 2022 || '-' || random_month() || '-' || floor(random()*28+1) || ' ' || (floor(random()*(22-14)+14)) || ':' || floor(random()*40+10) || ':' || '00.000000';
                hall := random_hall();
                insert into screenings (movie_id, hall_id, screeningday) values
                    (random_movie(),
                     hall,
                     cast(ddate as timestamp)
                     );

                rows := (SELECT c.rowcount FROM cinema_halls c WHERE cinemahall_id=hall);
--                 seats := (SELECT c.seatsperrow FROM cinema_halls c WHERE seatsperrow=hall);
--                 rows := 10;
                seats := 16;

                for x in 1..rows
                    loop
                        for y in 1..seats
                            loop
                                seat := (SELECT seat_id FROM seats WHERE hall_id=hall and row=x and seatnumber=y);
                                insert into tickets (screening_id, seat_id, state) values
                                    (n, seat, 'AVAILABLE');
                            end loop;

                    end loop;
            end loop;
    end;
$$;

create or replace function random_free_ticket(screenID int) returns int language sql as $$
select ticket_id from tickets WHERE state='AVAILABLE' AND screening_id=screenID order by random()+1 limit 1;
$$;


drop table if exists codes cascade;
create table codes ( code varchar(50));
insert into codes (code) values
    ('aezakmi'), ('velmitajne'), ('shhhh'), ('databazySuSuperNEKLAMEM'),
    ('abrakadabra'), ('unodos'), ('ligalegiend'), ('vajco'), ('password');

create or replace function random_code() returns varchar language sql as $$
    select code from codes order by random()+1 limit 1;
$$;


do $$
    declare fname varchar;
    declare lname varchar;
    declare date varchar;
    BEGIN
        for _ in 1..40
            loop
                date := 2022 || '-' || random_month() || '-' || floor(random()*28+1);
                insert into vouchers (customer_id, code, validuntil, used, discountvalue) VALUES
                    (random_customer(), random_code(), cast(date as date), false, random()*4+1);
            end loop;
    end;
$$;

do $$
    declare price double precision;
    declare date varchar;
    declare screentype varchar;
    declare screening int;
    declare ticketID int;
    BEGIN
        for resID in 1..680 loop
                screening := resID%50 + 1;
                date := 2022 || '-' || 1 || '-' || 1 || ' ' || 1 || ':' || 0 || ':' || '00.000000';
                insert into reservations (customer_id, screening_id, begin, pending, voucher_id) VALUES
                    (random_customer(), screening, cast(date as timestamp), false, null);

                screentype := (SELECT ch.screentype FROM screenings s JOIN cinema_halls ch on s.hall_id = ch.cinemahall_id WHERE s.screening_id=screening);
                price := get_price('ADULT', screentype);


                UPDATE reservations SET finalprice=price*4 WHERE reservation_id=resID;

                ticketID := random_free_ticket(resID);
                UPDATE tickets SET state='SOLD', finalprice=price, reservation_id=resID WHERE ticket_id=ticketID;

                for _ in 1..5 loop
                        ticketID := random_free_ticket(screening);
                        UPDATE tickets SET state='SOLD', finalprice=price, reservation_id=resID WHERE ticket_id=ticketID;
                    end loop;

            end loop;
    end
$$;


drop function random_first_name();
drop function random_last_name();
drop function random_code();
drop function random_customer();
drop function random_hall();
drop function random_movie();
drop function get_price(customer varchar, screen varchar);
drop function random_free_ticket(screenID int);
drop table months cascade;
drop table codes;
drop table first_names, last_names cascade;