-- badHero - bad examples...


CREATE TABLE badHero (
  name varchar(50),
  powers varchar(250),
  villain varchar(50),
  plot varchar(50),
  mission varchar(50),
  age int
);

INSERT INTO badHero VALUES(
  'The Plumber',
  'Sewer snake of doom, unclogging, ability to withstand smells',
  'Septic Slime Master',
  'Overcome Chicago with slime',
  'Stop the Septic Slime',
  37
  );

INSERT INTO badHero VALUES (
  'Binary Boy',
  'Hexidecimation beam, obfuscation',
  'Octal',
  'Eliminate the numerals 8 and 9',
  'Make the world safe for binary representation',
  19
);

INSERT INTO badHero VALUES (
  'The Janitor',
  'Mighty Mop',
  'Septic Slim Master',
  'Overcome New York with slime',
  'Stop the Septic Slime',
  41
);
  

SELECT * FROM badHero;  

-- hero - contains only hero data and a foreign key link to mission
CREATE TABLE hero (
  heroID INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  birthday DATE,
  missionID INTEGER
);

INSERT INTO hero VALUES (
  null, 'The Plumber', '1976-10-16', 1);
INSERT INTO hero VALUES (
  null, 'Binary Boy', '1988-1-27', 2);
INSERT INTO hero VALUES (
  null, 'The Janitor', '1964-8-30', 1);

SELECT * FROM hero;

-- view to see age formatted correctly
DROP VIEW IF EXISTS heroAgeView;
CREATE VIEW heroAgeView AS
  SELECT
    name as 'hero',
    CONCAT(
      YEAR(FROM_DAYS(DATEDIFF(NOW(), birthday))),
      ' years, ',
      MONTH(FROM_DAYS(DATEDIFF(NOW(), birthday))), 
      ' months'
    ) AS 'age'
  FROM
    hero;

-- mission table - contains all data relevant to mission
CREATE TABLE mission (
  missionID INTEGER PRIMARY KEY AUTO_INCREMENT,
  description VARCHAR(50),
  villain VARCHAR(30),
  plot VARCHAR(50)
);

INSERT INTO mission VALUES (
  null, 'Stop the septic slime', 
  'Septic Slime Master', 
  'Overcome city with Slime');
  
INSERT INTO mission VALUES (
  null, 'Make the world safe for Binary representation',
  'Octal', 
  'Eliminate the numerals 8 and 9');

SELECT * FROM mission;

-- standard query for joining hero and mission
SELECT 
  hero.name AS 'hero',
  mission.description AS 'mission'
FROM hero, mission
WHERE
  hero.missionID = mission.missionID;

-- view to store hero / mission query
DROP VIEW IF EXISTS heroMissionView;
CREATE VIEW heroMissionView AS
  SELECT 
    hero.name AS 'hero',
    mission.description AS 'mission',
    mission.villain AS 'villian',
    mission.plot AS 'plot'
  FROM hero, mission
  WHERE
    hero.missionID = mission.missionID;

SELECT * FROM heroMissionView;

-- power - has no foreign keys because it's part of many / many join
CREATE TABLE power (
  powerID INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

INSERT INTO power VALUES(null, 'Sewer snake of doom');
INSERT INTO power VALUES(null, 'unclogging');
INSERT INTO power VALUES(null, 'ability to withstand smells');
INSERT INTO power VALUES(null, 'Hexidecimation Beam');
INSERT INTO power VALUES(null, 'obfuscation');
INSERT INTO power VALUES(null, 'Mighty Mop');

SELECT * FROM power;

-- hero_power is a link table combining hero and power
-- it contains only foreign keys
CREATE TABLE hero_power (
  hero_powerID INTEGER PRIMARY KEY AUTO_INCREMENT,
  heroID INTEGER,
  powerID INTEGER
);

INSERT INTO hero_power VALUES (null, 1, 1);
INSERT INTO hero_power VALUES (null, 1, 2);
INSERT INTO hero_power VALUES (null, 1, 3);
INSERT INTO hero_power VALUES (null, 2, 4);
INSERT INTO hero_power VALUES (null, 2, 5);

SELECT * FROM hero_power;

-- The user can't read the hero_power table directly. Use a view
-- to replace keys with fields from the relevant table
SELECT
  hero.name AS 'hero',
  power.name AS 'power'
FROM
  hero, power, hero_power
WHERE 
  hero.heroID = hero_power.heroID
AND
  power.powerID = hero_power.powerID;

-- View to encapsulate hero_power query
DROP VIEW IF EXISTS heroPowerView;
CREATE VIEW heroPowerView AS
  SELECT
    hero.name AS 'hero',
    power.name AS 'power'
  FROM
    hero, power, hero_power
  WHERE 
    hero.heroID = hero_power.heroID
  AND
    power.powerID = hero_power.powerID;

SELECT * FROM heroPowerView;