CREATE TABLE question (
    id SERIAL PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT[] NOT NULL,
    tag TEXT,
    picture TEXT NOT NULL
    
);

CREATE TABLE player (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE lobby (
    code VARCHAR(4) NOT NULL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    creator_id INTEGER REFERENCES player(id),
    numberOfQuestions INTEGER,
    secondsPerRound INTEGER,
    pointsToWin INTEGER,
    started boolean DEFAULT FALSE,
    state JSONB NOT NULL DEFAULT '{}',
    tags TEXT[]
);

CREATE TABLE lobby_players (
    lobby VARCHAR(4) REFERENCES lobby(code),
    player INTEGER REFERENCES player(id),
    score INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (lobby, player)
);

INSERT INTO question (question, answer, picture, tag) VALUES
-- with pictures type 0
-- Athletes
('Who is this famous athlete?', ARRAY['cristiano ronaldo', 'ronaldo'], 'assets/016.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['serena williams', 'williams'], 'assets/017.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['lionel messi', 'messi'], 'assets/018.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['rafael nadal', 'nadal'], 'assets/019.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['simone biles', 'biles'], 'assets/020.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['michael jordan', 'jordan'], 'assets/021.jpg', 'athlete'),
('Who is this famous boxer?', ARRAY['muhammad ali', 'cassius clay', 'ali', 'clay'], 'assets/022.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['tiger woods', 'woods'], 'assets/023.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['shaquille o neal', 'shaq', 'o neal'], 'assets/024.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['roger federer', 'federer'], 'assets/025.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['novak djokovic', 'djokovic'], 'assets/026.jpg', 'athlete'),
('Who is this famous athlete?', ARRAY['michael phelps', 'phelps'], 'assets/027.jpg', 'athlete'),

-- Actors
('Who is this famous actress?', ARRAY['zendaya', 'zendaya coleman', 'coleman'], 'assets/000.jpg', 'actor'),
('Who is this famous actor?', ARRAY['leonardo dicaprio', 'leo dicaprio', 'dicaprio'], 'assets/001.jpg', 'actor'),
('Who is this famous actress?', ARRAY['jennifer lawrence', 'lawrence'], 'assets/002.jpg', 'actor'),
('Who is this famous actor?', ARRAY['denzel washington', 'washington'], 'assets/003.jpg', 'actor'),
('Who is this famous actor?', ARRAY['will smith', 'smith'], 'assets/004.jpg', 'actor'),
('Who is this famous actor?', ARRAY['brad pitt', 'pitt'], 'assets/005.jpg', 'actor'),
('Who is this famous actress?', ARRAY['scarlett johansson', 'johansson'], 'assets/006.jpg', 'actor'),
('Who is this famous actor?', ARRAY['keanu reeves', 'reeves'], 'assets/007.jpg', 'actor'),
('Who is this famous actor?', ARRAY['chris hemsworth', 'hemsworth'], 'assets/008.jpg', 'actor'),
('Who is this famous actress?', ARRAY['angelina jolie', 'jolie'], 'assets/009.jpg', 'actor'),
('Who is this famous actress?', ARRAY['julia roberts', 'roberts'], 'assets/010.jpg', 'actor'),
('Who is this famous actress?', ARRAY['cate blanchett', 'blanchett'], 'assets/011.jpg', 'actor'),
('Who is this famous actor?', ARRAY['morgan freeman', 'freeman'], 'assets/012.jpg', 'actor'),
('Who is this famous actor?', ARRAY['cillian murphy', 'murphy'], 'assets/013.jpg', 'actor'),
('Who is this famous actress?', ARRAY['cate blanchett', 'blanchett'], 'assets/014.jpg', 'actor'),
('Who is this famous actress?', ARRAY['meryl streep', 'streep'], 'assets/015.jpg', 'actor'),
('Who is this famous actor?', ARRAY['christian bale', 'bale'], 'assets/069.jpg', 'actor'),

-- Video Games
('What video game is this Character from?', ARRAY['among us'], 'assets/050.png', 'video game'),
('What is the name of this video game?', ARRAY['doom'], 'assets/051.jpg', 'video game'),
('What video game is this picture from?', ARRAY['skyrim', 'elder scrolls v', 'the elder scrolls v'], 'assets/052.jpg', 'video game'),
('What is the name of this video game?', ARRAY['tetris'], 'assets/053.jpg', 'video game'),
('Which video game is this picture from?', ARRAY['gta 5', 'grand theft auto 5', 'grand theft auto v', 'gta v'], 'assets/054.jpg', 'video game'),
('Which video game is this picture from?', ARRAY['fortnite'], 'assets/055.jpg', 'video game'),
('Which video game is this picture from?', ARRAY['super mario bros', 'mario bros'], 'assets/056.jpg', 'video game'),

-- Movies
('Which iconic movie is this scene from?', ARRAY['titanic'], 'assets/057.jpg', 'movie'),
('Which iconic movie is this scene from?', ARRAY['the godfather'], 'assets/058.jpg', 'movie'),
('Which iconic movie is this scene from?', ARRAY['star wars'], 'assets/059.jpg', 'movie'),
('Which famous movie is this scene from?', ARRAY['inception'], 'assets/060.jpg', 'movie'),
('Which iconic movie is this scene from?', ARRAY['jurassic park'], 'assets/061.jpg', 'movie'),
('Who is this character from The Matrix?', ARRAY['morpheus'], 'assets/062.png', 'movie'),
('Who is this famous movie character"?', ARRAY['darth vader', 'vader'], 'assets/063.jpg', 'movie'),
('From which movie is this scene?', ARRAY['captain america the first avenger', 'captain america', 'the first avenger', 'marvels the first avenger'], 'assets/064.jpg', 'movie'),
('Which movie is this character from?', ARRAY['harry potter'], 'assets/065.jpg', 'movie'),
('Who is this character from Harry Potter?', ARRAY['dobby'], 'assets/066.jpg', 'movie'),
('Who is this character from The Dark Knight?', ARRAY['joker'], 'assets/067.jpg', 'movie'),
('Who is this character from The Silence of The Lambs?', ARRAY['hannibal lecter', 'hannibal', 'lecter'], 'assets/068.jpg', 'movie'),
('What is the name of this character?', ARRAY['gorr', 'gorr the god butcher'], 'assets/070.jpg', 'movie'),

-- Comic/Anime/Animated movies
('Who is this character from The Lion King?', ARRAY['mufasa'], 'assets/028.jpg', 'animation'),
('Who is this famous cartoon character?', ARRAY['garfield'], 'assets/029.jpg', 'animation'),
('Who is this famous cartoon character?', ARRAY['odie'], 'assets/030.jpg', 'animation'),
('Who is this famous cartoon character?', ARRAY['bugs bunny'], 'assets/031.jpg', 'animation'),
('Who is this character from Dragonball?', ARRAY['vegeta'], 'assets/032.jpg', 'animation'),
('Who is this famous cartoon character?', ARRAY['winnie the pooh', 'winnie pooh'], 'assets/033.jpg', 'animation'),

-- Places
('In which city is this statue located?', ARRAY['rio', 'rio de janeiro', 'de janeiro'], 'assets/071.jpg', 'place'),
('What is the name of this building?', ARRAY['the roman colosseum', 'the colosseum'], 'assets/072.jpg', 'place'),
('What is the name of this building?', ARRAY['big ben'], 'assets/073.jpg', 'place'),
('What is the name of this statue?', ARRAY['statue of liberty'], 'assets/074.jpg', 'place'),
('What is the name of this building?', ARRAY['sagrada familia'], 'assets/075.jpg', 'place'),
('In which country is this mountain located?', ARRAY['nepal'], 'assets/076.jpg', 'place'),
('What is the name of this famous bridge?', ARRAY['golden gate bridge'], 'assets/077.jpg', 'place'),
('What is the name of this mountain?', ARRAY['mount everest', 'everest'], 'assets/078.jpg', 'place'),

-- Series
('Who is this character from Breaking Bad?', ARRAY['walter white', 'white'], 'assets/079.jpg', 'series'),
('Which series is this scene from?', ARRAY['game of thrones'], 'assets/080.jpg', 'series'),
('Which series is this scene from?', ARRAY['the walking dead'], 'assets/081.jpg', 'series'),
('Which series is this scene from?', ARRAY['big bang theory', 'the big bang theory'], 'assets/082.jpg', 'series'),
('Which series is this scene from?', ARRAY['queens gambit'], 'assets/083.jpg', 'series'),
('Which series is this scene from?', ARRAY['peaky blinders'], 'assets/084.jpg', 'series'),
('Which series is this scene from?', ARRAY['suits'], 'assets/085.jpg', 'series'),
('Who is this character form the Mandalorian?', ARRAY['grogu'], 'assets/086.jpg', 'series'),
('Who is this character from Stranger Things?', ARRAY['eleven'], 'assets/087.jpg', 'series'),
('Which series is this scene from?', ARRAY['the office'], 'assets/088.jpg', 'series'),

-- Flags
('Which country does this flag belong to?', ARRAY['uk', 'united kingdom', 'britain'], 'assets/035.png', 'flag'),
('Which country does this flag belong to?', ARRAY['france'], 'assets/036.png', 'flag'),
('Which country does this flag belong to?', ARRAY['germany'], 'assets/037.png', 'flag'),
('Which country does this flag belong to?', ARRAY['italy'], 'assets/038.png', 'flag'),
('Which country does this flag belong to?', ARRAY['spain'], 'assets/039.png', 'flag'),
('Which country does this flag belong to?', ARRAY['portugal'], 'assets/040.png', 'flag'),
('Which country does this flag belong to?', ARRAY['sweden'], 'assets/041.png', 'flag'),
('Which country does this flag belong to?', ARRAY['norway'], 'assets/042.png', 'flag'),
('Which country does this flag belong to?', ARRAY['netherlands'], 'assets/043.png', 'flag'),
('Which country does this flag belong to?', ARRAY['russia'], 'assets/044.png', 'flag'),
('Which country does this flag belong to?', ARRAY['monaco'], 'assets/045.png', 'flag'),
('Which country does this flag belong to?', ARRAY['poland'], 'assets/046.png', 'flag'),
('Which country does this flag belong to?', ARRAY['japan'], 'assets/047.png', 'flag'),
('Which country does this flag belong to?', ARRAY['brazil'], 'assets/048.png', 'flag'),
('Which country does this flag belong to?', ARRAY['canada'], 'assets/049.png', 'flag'),

-- Animals
('Who is this famous dog?', ARRAY['lassie'], 'assets/034.jpg', 'animal'),
('What is this breed of cat called?', ARRAY['bengal cat', 'bengal'], 'assets/089.jpg', 'animal'),
('What is this breed of dog called?', ARRAY['german shepherd', 'shepherd'], 'assets/091.jpg', 'animal'),
('What is this breed of cat called?', ARRAY['siamese cat', 'siamese'], 'assets/090.jpg', 'animal'),

-- Lyrics
('Who sings this song?', ARRAY['imagine dragons'], 'assets/092.png', 'lyrics'),
('Who sings this song?', ARRAY['lady gaga'], 'assets/094.png', 'lyrics'),
('Who sings this song?', ARRAY['fall out boy'], 'assets/096.png', 'lyrics'),
('Who sings this song?', ARRAY['eminem'], 'assets/097.png', 'lyrics'),
('Who sings this song?', ARRAY['adele'], 'assets/098.png', 'lyrics'),
('Who sings this famous song?', ARRAY['luis fonsi', 'fonsi', 'daddy yankee', 'yankee'], 'assets/099.png', 'lyrics'),
('Complete the song lyrics', ARRAY['im the bad guy'], 'assets/095.png', 'lyrics'),
('What is the name of this famous song by Michael Jackson?', ARRAY['smooth criminal'], 'assets/093.png', 'lyrics');
