DELIMITER $$
DROP PROCEDURE IF EXISTS addrandrecords$$

CREATE PROCEDURE addrandrecords (IN name varchar(20), num int)
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS RandomNames (name varchar(20), gender char(1));
    insert into RandomNames (name, gender) VALUES
    ('Szymon','M'), ('Jakub','M' ), ('Filip','M'), ('Kacper','M'), ('Michal','M'), ('Mateusz','M' ), ('Bartosz','M'), ('Wojciech','M'), ('Adam','M'), ('Jan','M'),
    ('Lena','F'), ('Maja','F'), ('Zuzanna','F' ), ('Julia','F' ), ('Wiktoria','F'), ('Amelia','F'), ('Natalia','F'), ('Martyna','F'), ('Anna','F'), ('Zofia','F'); #20 records

    CREATE TEMPORARY TABLE IF NOT EXISTS RandomSurnames (surname varchar(20));
    insert into RandomSurnames (surname) VALUES
    ('Nowak'), ('Wójcik'), ('Kowalczyk'), ('Wozniak'), ('Mazur'), ('Kaczmarek'), ('Krawczyk'), ('Zajac' ), ('Krol'), ('Wieczorek'), ('Walczak'), ('Sikora'), ('Pietrzak'), ('Wilk'), ('Sobczak'); #15 records

    CREATE TEMPORARY TABLE IF NOT EXISTS RandomHobby (nazwa varchar(20), lokacja varchar(20), typ enum('sport', 'nauka', 'inne'));
    insert into RandomHobby (nazwa, lokacja, typ) VALUES
    ('Pilka noza', 'stadion', 'sport'), ('Koszykowka', 'hala', 'sport'), ('Hokej', 'lodowisko', 'sport'), ('Siatkowka', 'hala', 'sport'), ('Plywanie', 'basen', 'sport'), ('Bieganie', 'stadion', 'sport'),
    ('Biologia', 'uniwersytet', 'nauka'), ('Informatyka', 'politechnika', 'nauka'), ('Chemia', 'politechnika', 'nauka'), ('Matematyka', 'politechnika', 'nauka'), ('Psychologia', 'uczelnia', 'nauka'), ('Medycyna', 'uniwersytet', 'nauka'),
    ('Esport', 'dom', 'inne'), ('Muzyka', NULL, 'inne'), ('Nulizmatyka', 'dom', 'inne'), ('Malarstwo', 'uczelnia', 'inne'), ('Ksiazki', 'biblioteka', 'inne'), ('Podrozowanie', NULL, 'inne'); #18 records

    CREATE TEMPORARY TABLE IF NOT EXISTS RandomPetName (name varchar(20), species varchar(20));
    insert into RandomPetName VALUES
    ('Fifek', 'dog'), ('Smokey', 'dog'), ('Sam', 'dog'), ('Kitty', 'cat'), ('Simba', 'cat'), ('Lucky', 'cat'), ('Sammy', 'hamster'), ('Charlie', 'hamster'), ('Oliver', 'fish'), ('Samantha', 'meerkat'), ('Fuppy', 'guinea pig'); #11 records

    CASE name
        WHEN 'hobby' THEN
            WHILE num > 0 DO
                SET @randosoba = (SELECT id FROM osoba ORDER BY RAND() LIMIT 1);
                CASE floor(rand()*3+1)
                    WHEN 1 THEN
                        SET @randtyp = 'sport';
                        SET @randid = (SELECT id FROM sport ORDER BY RAND() LIMIT 1);
                    WHEN 2 THEN
                        SET @randtyp = 'nauka';
                        SET @randid = (SELECT id FROM nauka ORDER BY RAND() LIMIT 1);
                    WHEN 3 THEN
                        SET @randtyp = 'inne';
                        SET @randid = (SELECT id FROM inne ORDER BY RAND() LIMIT 1);
                    END CASE;
                INSERT INTO hobby (osoba, id, typ) VALUES (@randosoba, @randid, @randtyp);
                SET num = num - 1;
            END WHILE ;
        WHEN 'inne' THEN
            WHILE num > 0 DO
                SET @randnazwa = (SELECT nazwa FROM RandomHobby WHERE typ = 'inne' ORDER BY RAND() LIMIT 1);
                SET @randlokacja = (SELECT lokacja FROM RandomHobby WHERE nazwa = @randnazwa);
                CASE floor(rand()*2+1)
                    WHEN 1 THEN SET @randtowarzysze = true;
                    WHEN 2 THEN SET @randtowarzysze = false;
                END CASE;

                INSERT INTO inne (nazwa, lokacja, towarzysze) VALUES (@randnazwa, @randlokacja, @randtowarzysze);
                SET num = num - 1;
            END WHILE;
        WHEN 'nauka' THEN
            WHILE num > 0 DO
                SET @randnazwa = (SELECT nazwa FROM RandomHobby WHERE typ = 'nauka' ORDER BY RAND() LIMIT 1);
                SET @randlokacja = (SELECT lokacja FROM RandomHobby WHERE nazwa = @randnazwa);

                INSERT INTO nauka (nazwa, lokacja) VALUES (@randnazwa, @randlokacja);
                SET num = num - 1;
            END WHILE ;
        WHEN 'osoba' THEN
            WHILE num > 0 DO
                SET @randimię = (SELECT name FROM RandomNames ORDER BY RAND() LIMIT 1);
                SET @randnazwisko = (SELECT surname FROM RandomSurnames ORDER BY RAND() LIMIT 1);
                    SET @MIN = '2001-11-11 00:00:00';
                    SET @MAX = '1969-11-11 00:00:00';
                SET @randdataUrodzenia = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);
                SET @randplec = (SELECT gender FROM RandomNames WHERE name = @randimię);

                INSERT INTO osoba (imię, nazwisko, dataUrodzenia, plec) VALUES (@randimię, @randnazwisko, @randdataUrodzenia, @randplec);
                SET num = num - 1;
            END WHILE;
        WHEN 'sport' THEN
            WHILE num > 0 DO
                SET @randnazwa = (SELECT nazwa FROM RandomHobby WHERE typ = 'sport' ORDER BY RAND() LIMIT 1);
                SET @randlokacja = (SELECT lokacja FROM RandomHobby WHERE nazwa = @randnazwa);
                CASE floor(rand()*3+1)
                    WHEN 1 THEN SET @randtyp = 'indywidualny';
                    WHEN 2 THEN SET @randtyp = 'druzynowy';
                    WHEN 3 THEN SET @randtyp = 'mieszany';
                END CASE;

                INSERT INTO sport (nazwa, typ, lokacja) VALUES (@randnazwa, @randtyp, @randlokacja);
                SET num = num - 1;
            END WHILE ;
        WHEN 'zwierzak' THEN
            WHILE num > 0 DO
                SET @randname = (SELECT name FROM RandomPetName ORDER BY RAND() LIMIT 1);
                SET @randspecies = (SELECT species FROM RandomPetName WHERE name = @randname);
                CASE floor(rand()*2+1)
                    WHEN 1 THEN SET @randsex = 'f';
                    WHEN 2 THEN SET @randsex = 'm';
                END CASE;
                SET @randid = (SELECT id FROM osoba ORDER BY RAND() LIMIT 1);
                    SET @MIN = '2019-11-11 00:00:00';
                    SET @MAX = '1980-11-11 00:00:00';
                SET @randbirth = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);
                CASE floor(rand()*3+1)
                    WHEN 1 THEN
                        SET @MIN = '2019-11-11 00:00:00';
                        SET @MAX = @randbirth;
                        SET @randdeath = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);
                    ELSE SET @randdeath = NULL;
                END CASE;

                INSERT INTO zwierzak (name, species, sex, birth, death, id) VALUES (@randname, @randspecies, @randsex, @randbirth, @randdeath, @randid);
                SET num = num - 1;
            END WHILE ;
        ELSE SHOW TABLES;

    END CASE;

    DROP TEMPORARY TABLE RandomNames;
    DROP TEMPORARY TABLE RandomSurnames;
    DROP TEMPORARY TABLE RandomHobby;
    DROP TEMPORARY TABLE RandomPetName;
END $$

DELIMITER ;

call addrandrecords('osoba', 1);