set foreign_key_checks = 0;


CREATE TABLE Quiz(
    QID int AUTO_INCREMENT,
    QuizName varchar(50),
    QuestionNum int NOT NULL,
    Question varchar(500),
    Answer1 varchar(500),
    Answer2 varchar(500),
    Answer3 varchar(500),
    Answer4 varchar(500),
    PRIMARY KEY(QID, QuizName)
);

CREATE TABLE UserResults(
    QID int AUTO_INCREMENT,
    QuizName varchar(50),
    UserName varchar (50),
    Result varchar(50),
    PRIMARY KEY(UserName, QID),
    FOREIGN KEY (QID) REFERENCES Quiz(QID),
    FOREIGN KEY (UserName) REFERENCES User(UserName)
);

CREATE TABLE QuizResults(
    QID int AUTO_INCREMENT,
    QuizName varchar(50), 
    ResultNum int,
    Result varchar(50),
    PRIMARY KEY(QID, QuizName),
    FOREIGN KEY (QID) REFERENCES Quiz(QID)
);


DELIMITER //
CREATE OR REPLACE PROCEDURE checkResultExists (NewQuizName VARCHAR(50), NewUserName VARCHAR(50), NewResult VARCHAR(50))
BEGIN
    DECLARE count INT;
    -- Checks if row exists
    SELECT COUNT(*) INTO count
    FROM UserResults 
    WHERE UserName = NewUserName AND QuizName = NewQuizName;
    
    -- If exists replace only the results
    IF count > 0 THEN
        UPDATE UserResults
        SET Result = NewResult
        WHERE UserName = NewUserName AND QuizName = NewQuizName;

    -- Else insert entire new row from variables
    ELSE
        INSERT INTO UserResults(QuizName, UserName, Result)
        VALUES (NewQuizName, NewUserName, NewResult);
    END IF;
END//
DELIMITER ;

set foreign_key_checks = 1;

------------------------------------------- LEAGUE ---------------------------------------------------
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 1, 'What is your ideal role in a group?', 'The fearless leader, charging ahead', 'The strategic mastermind, planning everything', 'The emotional heart, keeping everyone together', 'The wildcard, unpredictable and free-spirited');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 2, 'How do you handle conflict?', 'Face it head-on, no hesitation', 'Outsmart your opponent before they make a move', 'Try to find peace or help others through it', 'Cause a little chaos and see what happens');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 3, 'Pick a place you would feel most at home.', 'A battlefield under the open sky', 'A high-tech control room full of gadgets', 'A serene forest or temple', 'A chaotic city street during a festival');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 4, 'What motivates you most?', 'Glory and honor', 'Knowledge and control', 'Loyalty and love', 'Freedom and fun');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 5, 'How would your friends describe you?', 'Brave and protective', 'Clever and calculating', 'Kind and nurturing', 'Energetic and unpredictable');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 6, 'Choose a weapon or power.', 'Giant sword or hammer', 'Magical energy or high-tech devices', 'Healing abilities or spiritual magic', 'Explosives or unpredictable spells');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 7, 'In a video game, would you rather be?', 'A tanky frontliner', 'A sniper or strategist', 'A support or healer', 'A high-damage wildcard');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 8, 'What is your biggest flaw?', 'Too stubborn or reckless', 'Overly controlling or secretive', 'Too trusting or sensitive', 'Impulsive and chaotic');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 9, 'What type of story do you like best?', 'A Hero journey full of battles and honor', 'Political intrigue and clever twists', 'Emotional stories of sacrifice and connection', 'Dark comedies or wild adventures');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('League', 10, 'Choose an animal companion.', 'A lion or wolf', 'A raven or fox', 'A deer or owl', 'A monkey or chameleon');

insert into QuizResults(QuizName, ResultNum, Result) values ('League', 1, 'Ezreal');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 2, 'Jhin');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 3, 'Kai''Sa');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 4, 'Varus');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 5, 'Viego');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 6, 'Rell');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 7, 'Jayce');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 8, 'Lucian');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 9, 'Nautilus');
insert into QuizResults(QuizName, ResultNum, Result) values ('League', 10, 'Miss Fortune');



----------------------------------------- VALORANT ----------------------------------------------------------
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 1, 'What is your playstyle in games?', 'Aggressive and fearless—first in, last out', 'Strategic and methodical—always planning ahead', 'Supportive and team-oriented—making everyone better', 'Chaotic and unpredictable—throwing off your enemies');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 2, 'What role do you usually prefer in Valorant?', 'Duelist', 'Controller', 'Initiator', 'Sentinel');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 3, 'How do you like to enter a site?', 'Flash in and take space', 'Smoke angles and clear methodically', 'Disrupt enemy setups and force rotation', 'Lurk and catch flanks');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 4, 'Pick your ideal power:', 'Blazing fast reflexes and deadly aim', 'Smoke manipulation or toxic control', 'Scouting and revealing enemies', 'Traps and healing');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 5, "What is your vibe in team comms?", 'Hyped up and aggressive', 'Calm, low voice, always giving info', 'Tactical and directive', 'Quiet but clutch when it matters');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 6, 'You hear footsteps behind you. What do you do?', 'Turn around and peek—gun out', 'Set a trap or use utility', 'Call it out and reposition', 'Rotate early and ambush');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 7, 'Which quote fits you best?', 'Let us make some chaos.', 'Every angle matters.', 'Information wins fights.', 'They will never see me coming.');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 8, 'Choose your favorite Valorant map.', 'Fracture', 'Bind', 'Haven', 'Ascent');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 9, 'You win a clutch. What do you do?', 'Yell, spray the ground', 'Quiet fist pump—back to business', 'Celebrate with your squad', 'Teabag and run away laughing');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Val', 10, 'What motivates you most?', 'Outplaying the enemy', 'Controlling the battlefield', 'Being the glue that holds the team', 'Having fun and confusing your enemies');

insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 1, 'Jett');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 2, 'Clove');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 3, 'Sove');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 4, 'Reyna');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 5, 'Tejo');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 6, 'Cypher');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 7, 'Vyse');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 8, 'Killjoy');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 9, 'Yoru');
insert into QuizResults(QuizName, ResultNum, Result) values ('Val', 10, 'Omen');


-------------------------------------- R6 ------------------------------------------------------------
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 1, 'What is your preferred playstyle in a tactical shooter?', 'Breach and clear—straight into the fight', 'Set traps and control the flow', 'Gather intel and call the shots', 'Anchor down and hold the site');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 2, 'You are attacking. What do you do first?', 'Breach a wall or door, full force', 'Drone the area and look for roamers', 'Wait for the right call, then move in', 'Flank from a weird angle and surprise them');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 3, 'How do you feel about gadgets?', 'Big explosions = big fun', 'I want gear that outsmarts the enemy', 'I use it to protect my team or track enemies', 'Give me tech that lets me move silently or creatively');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 4, 'What role do you play most often?', 'Entry fragger', 'Roamer', 'Support', 'Lurker or anti-roam');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 5, 'What describes your personality best?', 'Loud and bold', 'Quiet and cunning', 'Calculated and loyal', 'Unpredictable and experimental');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 6, 'Pick your loadout style:', 'Shotgun and breaching charges', 'SMG with traps', 'AR with drones or cameras', 'Silenced pistol and sneaky gadgets');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 7, 'How do you handle pressure in clutch moments?', 'Rush and overwhelm', 'Outthink and bait', 'Communicate and lead', 'Stay hidden, strike when it counts');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 8, 'Choose your go-to attack strategy:', 'Explosive push with breaching team', 'Entry denial via vertical play', 'Gadget synergy and teamwork', 'Unorthodox flanks and timing');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 9, 'Which real-world skill do you relate to most?', 'Demolitions', 'Surveillance and recon', 'Leadership and coordination', 'Covert operations');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('R6', 10, 'Choose a motto:', 'Go loud or go home.', 'One step ahead.', 'We do this together.', 'They never saw it coming.');

insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 1, 'Ash');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 2, 'Doc');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 3, 'Ace');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 4, 'Lesion');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 5, 'Buck');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 6, 'Azami');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 7, 'Dokkaebi');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 8, 'Bandit');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 9, 'Blackbeard');
insert into QuizResults(QuizName, ResultNum, Result) values ('R6', 10, 'Valkyrie');


----------------------------- DARK SOULS ---------------------------------------------------------------------------
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 1, 'What is your ideal combat style?', 'Heavy and devastating, one hit at a time', 'Quick, precise, and fluid', 'Long reach with versatile combos', 'Keep distance and use sorcery');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 2, 'How do you feel about stamina management?', 'I will burn it all for one big blow', 'I like to be efficient and agile', 'I trade stamina for spacing and control', 'I do not need much I will cast from afar');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 3, 'You see a big knight approaching. You:', 'Tank his hit and break his poise', 'Keep your spacing and poke', 'Roll back and start casting', 'Parry and punch his soul out');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 4, 'Pick a stat to max out first:', 'Strength', 'Dexterity', 'Intelligence', 'Faith');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 5, 'What is your fashion souls aesthetic?', 'Heavy armor and cape—bring the drama', 'Ronin or ninja-esque—sleek and agile', 'Knightly or militaristic—classic and disciplined', 'Robed and glowing—arcane elegance');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 6, 'Choose a boss fight tactic:', 'Dodge, counter with heavy hit', 'Parry, roll, slice—repeat', 'Exploit their weakness with spells', 'Go full aggro, no fear');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 7, 'What is your reaction when you die in a Souls game?', 'Analyze and adapt', 'Break your controller', 'I need a better spell or ring', 'Laugh maniacally and run back in');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 8, 'Favorite weapon feature:', 'Hyper armor', 'Bleed damage', 'Sweep and thrust mix', 'Magic scaling');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 9, 'Pick a location to fight in:', 'A crumbling castle battleground', 'A moonlit bridge or rooftop', 'A narrow corridor or rampart', 'A chaotic arena with fire everywhere');
insert into Quiz(QuizName, QuestionNum, Question, Answer1, Answer2, Answer3, Answer4) values ('Souls', 10, 'Which quote fits your mindset?', 'Strike first, end it fast.', 'Keep your distance and wear them down.', 'Control the elements, control the fight.', 'Fists solve everything.');

insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 1, 'Greatswords');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 2, 'Reapers');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 3, 'Magic');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 4, 'Miracles');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 5, 'Katanas');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 6, 'Axes');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 7, 'Straight Swords');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 8, 'Hammers');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 9, 'Spears');
insert into QuizResults(QuizName, ResultNum, Result) values ('Souls', 10, 'Bows');