-- DB setup, setting up information on the popular
-- N64 game, Zelda, the Ocarina of Time.
-- Data synthesized from zeldadungeon.net

-- Table creation:

-- Lists all temples in the game. ID's work out as follows:
/**
  Deku Tree: 1
  Dodongo's Cavern: 2
  Jabu-Jabu's Belly: 3
  Forest Temple: 4
  Fire Temple: 5
  Ice Cavern: 6
  Water Temple: 7
  Bottom of the Well: 8
  Shadow Temple: 9
  Spirit Temple: 10
  Ganondorf's castle: 11
*/


CREATE TABLE Temple (
  tid INTEGER ,-- numeric ID of this temple: see table above.
  name VARCHAR(255), -- Name of dungeon.
  completion_time INTEGER, -- Fastest the dungeon has been completed, in seconds.
  sage VARCHAR(255), -- the sage associated with this temple (NULL if N/A)

  PRIMARY KEY (tid)
);

CREATE TABLE Enemies (
  name VARCHAR(255), -- Name of enemy.
  first_seen INTEGER REFERENCES Temple(tid), -- First temple in which the enemy is seen.
  location VARCHAR(255), -- Location of the enemy in a room (floor, wall, ceiling, water, flies, underground)

  PRIMARY KEY (name)
);

-- Bosses in the game, mini and final.
-- floor's work as follows: <floor#><Basement b, or Floor f>
-- i.e. b represent floors in the basement, f represent
-- upward increasing floors.
CREATE TABLE Bosses (
  temple_id INTEGER REFERENCES Temple(tid), -- temple_id in which the boss is found
  name VARCHAR(255), -- name of the boss itself.
  dungeon_floor VARCHAR(255), -- floor on which the boss is found.
  final INTEGER, -- whether the boss is a mini boss or a final boss (0 for mini, 1 for final).
  weakspot VARCHAR(255), -- the weakspot of the boss.

  PRIMARY KEY (name)
);

CREATE TABLE Item (
  name VARCHAR(255), -- The name of the item.

  -- The temple in which the item is found (0 if found outside a dungeon).
  -- If temple_id = 13, then the item is what we consider a dungeon item; that is to
  -- say, these items are found in every (well, almost every) dungeon.
  temple_id INTEGER REFERENCES Temple(tid),
  upgrades VARCHAR(255), -- a List of upgrades to the item (NULL if no upgrades available).

  PRIMARY KEY (name)
);

-- Populate Necessary data.

-- Populate the dungeons Table
-- Speed run times found from www.speedrun.com, rounded down to nearest second.
-- These are Any% times (killing the major boss and claiming the major objective)
INSERT INTO Temple(tid, name, completion_time, sage)
VALUES
  (1, 'Deku Tree', 72, NULL),
  (2, 'Dodongo''s Cavern', 132, NULL),
  (3, 'Jabu Jabu''s Belly', 116, NULL),
  (4, 'Forest Temple', 168, 'Saria'),
  (5, 'Fire Temple', 130, 'Darunia'),
  (6, 'Ice Cavern', 88, NULL),
  (7, 'Water Temple', 106, 'Ruto'),
  (8, 'Bottom of the Well', 59, NULL),
  (9, 'Shadow Temple', 99, 'Impa'),
  (10, 'Spirit Temple', 476, 'Nabooru'),
  (11, 'Ganondorf''s Castle', 219, NULL);

INSERT INTO Enemies(name, first_seen, location)
VALUES
  ('Anubis', 10, 'floor'),
  ('Armos', 2, 'floor'),
  ('Baby Dodongo', 2, 'underground'),
  ('Bari', 3, 'ceiling'),
  ('Beamos', 2, 'floor'),
  ('Big Deku Baba', 0, 'floor'),
  ('Big Poe', 0, 'flies'),
  ('Big Skulltula', 4, 'ceiling'),
  ('Biri', 3, 'flies'),
  ('Blade Trap', 2, 'floor'),
  ('Blue Bubble', 4, 'flies'),
  ('Blue Tekite', 7, 'water'),
  ('Business Scrub', 0, 'underground'),
  ('Club Moblin', 0, 'floor'),
  ('Cucco', 0, 'flies'),
  ('Deku Baba', 1, 'floor'),
  ('Deku Scrub', 1, 'underground'),
  ('Dinolfos', 11, 'floor'),
  ('Dodongo', 2, 'floor'),
  ('Door Mimic', 5, 'wall'),
  ('Eye Switch', 9, 'wall'),
  ('Fire Keese', 1, 'flies'),
  ('Floormaster', 9, 'ceiling'),
  ('Flying Jars', 6, 'flies'),
  ('Flying Tile', 5, 'flies'),
  ('Freezard', 6, 'floor'),
  ('Gerudo Guard', 0, 'floor'),
  ('Gerudo Thief', 0, 'floor'),
  ('Gibdo', 8, 'floor'),
  ('Gohma Larva', 1, 'floor'),
  ('Gold Skulltula', 0, 'wall'),
  ('Green Bubble', 4, 'flies'),
  ('Guay', 10, 'flies'),
  ('Ice Keese', 6, 'flies'),
  ('Iron Knuckle', 10, 'floor'),
  ('Keese', 1, 'flies'),
  ('Leever', 10, 'underground'),
  ('Like Like', 5, 'floor'),
  ('Lizalfos', 2, 'floor'),
  ('Mad Scrub', 1, 'underground'),
  ('Moblin', 0, 'floor'),
  ('Octorok', 3, 'water'),
  ('Parasitic Tentacle', 3, 'ceiling'),
  ('Peahat', 0, 'flies'),
  ('Peahat Larva', 0, 'flies'),
  ('Poe', 0, 'flies'),
  ('Red Bubble', 5, 'flies'),
  ('ReDead', 8, 'floor'),
  ('Red Jelly', 3, 'water'),
  ('Red Tekite', 0, 'floor'),
  ('Shabom', 3, 'flies'),
  ('Shell Blade', 7, 'water'),
  ('Skull Kid', 0, 'floor'),
  ('Skulltula', 1, 'ceiling'),
  ('Skullwalltula', 1, 'wall'),
  ('Spike', 7, 'water'),
  ('Stalchild', 0, 'underground'),
  ('Stalfos', 4, 'floor'),
  ('Stinger', 3, 'water'),
  ('Tailpasaran', 3, 'floor'),
  ('Torch Slug', 5, 'floor'),
  ('Wallmaster', 4, 'ceiling'),
  ('White Bubble', 10, 'floor'),
  ('White Wolfos', 6, 'floor'),
  ('Withered Deku Baba', 1, 'floor'),
  ('Wolfos', 4, 'floor');

-- Insert the minibosses into the bosses table.
INSERT INTO Bosses(temple_id, name, dungeon_floor, final, weakspot)
VALUES
  (3, 'Bigocto', '1F', 0, 'back'),
  (4, 'Poe Sisters', '1F', 0, 'face'),
  (5, 'Flare Dancer', 'multiple floors', 0, 'torso'),
  (7, 'Dark Link', '3F', 0, 'torso'),
  (8, 'Dead Hand', '1F', 0, 'face');

-- Insert Bosses into the bosses table.
INSERT INTO Bosses(temple_id, name, dungeon_floor, final, weakspot)
VALUES
  (1, 'Queen Gohma', '2B', 1, 'eye'),
  (2, 'King Dodongo', '1F', 1, 'mouth'),
  (3, 'Barinade', '2F', 1, 'torso'),
  (4, 'Phantom Ganon', '2B', 1, 'horse'),
  (5, 'Volvagia', '1F', 1, 'head'),
  (7, 'Morpha', '3F', 1, 'brain'),
  (9, 'Bongo Bongo', '5B', 1, 'hands'),
  (10, 'Twinrova', '3F', 1, 'head'),
  (11, 'Ganondorf', '6F', 1, 'body'),
  (11, 'Ganon', '1F', 1, 'tail');

INSERT INTO Item(name, temple_id, upgrades)
VALUES
  ('Deku Sticks', 1, NULL),
  ('Deku Nuts', 1, NULL),
  ('Bombs', 2, '2 Bomb Bag Capacity Upgrades'),
  ('Bombchus', 10, NULL),
  ('Boomerang', 3, NULL),
  ('Fairy Ocarina', 0, 'Replaced by Ocarina of Time'),
  ('Magic Beans', 0, NULL),
  ('Fairy Bow', 4, 'Fire, Ice, and Light Arrows'),
  ('Hookshot', 0, 'Replaced by the Longshot'),
  ('Megaton Hammer', 5, NULL),
  ('Lens of Truth', 8, NULL),
  ('Din''s Fire', 0, NULL),
  ('Farore''s Wind', 0, NULL),
  ('Naryu''s Love', 0, NULL),
  ('Empty Bottles', 0, NULL),
  ('Dungeon Map', 12, NULL),
  ('Compass', 12, NULL),
  ('Small Keys', 12, NULL),
  ('Boss Key', 12, NULL);
