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",false);
INSERT INTO DailyBonus VALUES(1, true, false,false, "ws1",false);
INSERT INTO DailyBonus VALUES(1, false,false,true,"ch1",false);
INSERT INTO DailyBonus VALUES(2, false, true,false, "ss3",false);
INSERT INTO DailyBonus VALUES(3, true, false,false, "ws2",false);
INSERT INTO DailyBonus VALUES(3, false, true, false,"ss3",false);
INSERT INTO DailyBonus VALUES(4, true, false,false,"ws1",false);
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;

UPDATE DailyBonus SET CheckLogin = true WHERE PlayerUserName = 2;
UPDATE DailyBonus SET CheckLogin = false WHERE CheckLogin = true;