CREATE table Player (
  USERNAME INT PRIMARY KEY,
  name VARCHAR (30),
  email VARCHAR (100),
  address VARCHAR (100),
  balance INT

  ) ENGINE = INNODB;

CREATE table PowerUp (
  ID VARCHAR(5) PRIMARY KEY,
  PowerUpName VARCHAR (30),
  cost INT
  ) ENGINE = INNODB;

CREATE table Games (
  PlayerUserName INT PRIMARY KEY,
  WordStew BOOLEAN,
  SweetieSqueeze BOOLEAN,
  CharacterHunt BOOLEAN,
  FOREIGN KEY (PlayerUserName) REFERENCES Player(USERNAME)
  ) ENGINE = INNODB;

CREATE table GamePowerUp (
  PlayerUserName INT,
  PowerUpID VARCHAR(5),
  counter INT,
  FOREIGN KEY (PlayerUserName) REFERENCES Player(USERNAME),
  FOREIGN KEY (PowerUpID) REFERENCES PowerUp(ID)
) ENGINE = INNODB;

CREATE table DailyBonus (
  PlayerUserName INT,
  PlaysWordStew BOOLEAN,
  PlaysSweetieSqueeze BOOLEAN,
  PlaysCharacterHunt BOOLEAN,
  PowerUp VARCHAR(5),
  CheckLogin BOOLEAN,
  FOREIGN KEY (PowerUp) REFERENCES GamePowerUp(PowerUpID),
  FOREIGN KEY (PlayerUserName) REFERENCES Player(USERNAME)
) ENGINE = INNODB;

INSERT INTO Player VALUES (1, "Euan", "euan@example.com", "12 cresent avenue", 0);
INSERT INTO Player VALUES (2, "Lewis", "lewis@example.com", "15 mars road", 5);
INSERT INTO Player VALUES (3, "Kamil", "kamil@example.com", "36 dodger street", 3);
INSERT INTO Player VALUES (4, "Josie", "josie@example.com", "78 smith street", 1);
INSERT INTO Player VALUES (5, "Mark", "mark@example.com", "9 thompson street", 0);

INSERT INTO PowerUp VALUES ("ss1", "Clear row", 2);
INSERT INTO PowerUp VALUES ("ss2", "Clear column", 2);
INSERT INTO PowerUp VALUES ("ss3", "Clear colour", 4);
INSERT INTO PowerUp VALUES ("ws1", "Give clue", 2);
INSERT INTO PowerUp VALUES ("ws2", "Give word", 5);
INSERT INTO PowerUp VALUES ("ch1", "Extra turn",4);
INSERT INTO PowerUp VALUES ("ch2", "Give character",2);

INSERT INTO Games VALUES (1, true, false,true);
INSERT INTO Games VALUES (2, true, false,false);
INSERT INTO Games VALUES (3, false, true,false);
INSERT INTO Games VALUES (4, true, false,true);
INSERT INTO Games VALUES (5, true, true,false);

INSERT INTO GamePowerUp VALUES(1, "ss1" ,1);
INSERT INTO GamePowerUp VALUES(1, "ws1" ,1);
INSERT INTO GamePowerUp VALUES(1, "ch1",0);
INSERT INTO GamePowerUp VALUES(2, "ss1" ,1);
INSERT INTO GamePowerUp VALUES(2, "ws1" ,1);
INSERT INTO GamePowerUp VALUES(3, "ss3" ,0);
INSERT INTO GamePowerUp VALUES(4, "ws1" ,0);
INSERT INTO GamePowerUp VALUES(4, "ch2" ,1);
INSERT INTO GamePowerUp VALUES(5, "ws2", 0);


INSERT INTO DailyBonus VALUES(1, false, true,false, "ss1",true);
INSERT INTO DailyBonus VALUES(1, true, false,false, "ws1",true);
INSERT INTO DailyBonus VALUES(1, false,false,true,"ch1",false);
INSERT INTO DailyBonus VALUES(2, false, false,false, "ss3",false);
INSERT INTO DailyBonus VALUES(3, true, false,false, "ws2",true);
INSERT INTO DailyBonus VALUES(3, false, true, false,"ss3",false);
INSERT INTO DailyBonus VALUES(4, true, false,false,"ws1",true);
INSERT INTO DailyBonus VALUES(4, false, false,true, "ch2",false);
INSERT INTO DailyBonus VALUES(5, true, false,false,"ws1",false);

UPDATE Player SET email = "LewisWilson@gmail.com" WHERE name = "Lewis";
ALTER TABLE Player DROP COLUMN Address;




//COMMANDS

SELECT PlayerUserName FROM Games WHERE SweetieSqueeze = true;

SELECT counter AS "2B. Number of give clue’ power-ups Lewis has got" FROM GamePowerUp WHERE PowerUpID = "ws1" AND PlayerUserName = 2;


SELECT COUNT(counter) AS "2C. The total number of ‘clear row’ power-ups all users have got" FROM GamePowerUp WHERE PowerUpID = "ss1";


SELECT email AS "2D. The email address of Euan." FROM Player WHERE name = "Euan";


SELECT COUNT(SweetieSqueeze AND WordStew) AS "2E. The number of users who are signed up to both games." FROM Games WHERE SweetieSqueeze = true AND WordStew = true;

SELECT name, balance FROM Player WHERE name = "Kamil";
SELECT * FROM Player WHERE balance > 0;

SELECT * FROM DailyBonus WHERE CheckLogin = true;

SELECT email AS "3a. Showing updated email"  FROM Player WHERE name = "Lewis";

SELECT * FROM Games;
SELECT * FROM PowerUp;

SELECT * FROM Player;