DROP TABLE IF EXISTS monsters;
DROP TABLE IF EXISTS weapons;
DROP TABLE IF EXISTS armor;
DROP TABLE IF EXISTS accessories;
DROP VIEW IF EXISTS monsterView;
DROP VIEW IF EXISTS weaponView;
DROP VIEW IF EXISTS armorView;
DROP VIEW IF EXISTS accessoryView;

CREATE TABLE monsters (
  monstersID integer not null auto_increment primary key,
  name varchar(30),
  hitpoints integer,
  attack integer,
  defense integer,
  speed integer,
  special varchar(40)
);

INSERT INTO monsters VALUES (null, 'Slime', 10, 5, 5, 1, 'none');
INSERT INTO monsters VALUES (null, 'Steelclad Scorpion', 67, 12, 38, 1, '+15 DEF');
INSERT INTO monsters VALUES (null, 'Hefty Hellion', 210, 24, 18, 1, '+150 HP');
INSERT INTO monsters VALUES (null, 'Zealot', 110, 9, 14, 2, '+1 SPD');
INSERT INTO monsters VALUES (null, 'Raider', 90, 15, 10, 1, 'none');
INSERT INTO monsters VALUES (null, 'Centipede', 40, 8, 10, 3, '+2 SPD');
INSERT INTO monsters VALUES (null, 'Henchman', 90, 15, 20, 1, 'none');
INSERT INTO monsters VALUES (null, 'Demonic Baron', 250, 35, 20, 2, '+20 ATK');
INSERT INTO monsters VALUES (null, 'Mothman', 125, 20, 40, 2, '+20 DEF');
INSERT INTO monsters VALUES (null, 'Spider Demon', 300, 20, 30, 5, '+4 SPD');
INSERT INTO monsters VALUES (null, 'Magitek Knight', 600, 60, 60, 10, 'x2 to stats');
INSERT INTO monsters VALUES (null, 'Golem', 1000, 40, 200, 3, '+500 HP, +300 DEF');



CREATE TABLE weapons (
  weaponsID integer not null auto_increment primary key,
  name varchar(30),
  attack integer
);

INSERT INTO weapons VALUES (null, 'Wooden Sword', 2);
INSERT INTO weapons VALUES (null, 'Cutlass', 5);
INSERT INTO weapons VALUES (null, 'Thorn Whip', 8);
INSERT INTO weapons VALUES (null, 'Serrated Cleaver', 15);
INSERT INTO weapons VALUES (null, 'Flanged Mace', 18);
INSERT INTO weapons VALUES (null, 'Swift Spear', 24);
INSERT INTO weapons VALUES (null, 'Magic Bayonet', 30);
INSERT INTO weapons VALUES (null, 'Demon Dagger', 35);
INSERT INTO weapons VALUES (null, 'Masamune', 50);
INSERT INTO weapons VALUES (null, 'Executioner Axe', 65);
INSERT INTO weapons VALUES (null, 'Zodiac Spear', 100);



CREATE TABLE armor (
  armorID integer not null auto_increment primary key,
  name varchar(30),
  defense integer
);

INSERT INTO armor VALUES (null, 'Leather Armor', 2);
INSERT INTO armor VALUES (null, 'Ring Mail', 6);
INSERT INTO armor VALUES (null, 'Splint Mail', 9);
INSERT INTO armor VALUES (null, 'Steel Cuirass', 14);
INSERT INTO armor VALUES (null, 'Full Plate Mail', 22);
INSERT INTO armor VALUES (null, 'Ornate Armor', 30);
INSERT INTO armor VALUES (null, 'Chaos Chainmail', 40);
INSERT INTO armor VALUES (null, 'Balrog Skin', 60);
INSERT INTO armor VALUES (null, 'Dragonhide', 100);
INSERT INTO armor VALUES (null, 'Sacred Skin', 400);



CREATE TABLE accessories (
  accessoriesID integer not null auto_increment primary key,
  name varchar(30),
  hitpoints integer,
  attack integer,
  defense integer,
  speed integer
);

INSERT INTO accessories VALUES (null, 'Thorn Bracelet', 0, 1, 0, 0);
INSERT INTO accessories VALUES (null, 'Wolf Amulet', 30, 0, 0, 0);
INSERT INTO accessories VALUES (null, 'Steel Gauntlet', 0, 0, 3, 0);
INSERT INTO accessories VALUES (null, 'Glowing Ring', 10, 2, 2, 0);
INSERT INTO accessories VALUES (null, 'Swift Belt', 0, 0, 0, 1);
INSERT INTO accessories VALUES (null, 'Barrier Ring', 50, 0, 10, 0);
INSERT INTO accessories VALUES (null, 'Black Belt', 0, 0, 0, 4);
INSERT INTO accessories VALUES (null, 'Cursed Amulet', -25, -5, -5, 0);
INSERT INTO accessories VALUES (null, 'Earrings', 25, 0, 10, 0);
INSERT INTO accessories VALUES (null, 'Golden Circlet', 100, 10, 10, 1);
INSERT INTO accessories VALUES (null, 'Muscle Belt', 0, 50, 0, 0);
INSERT INTO accessories VALUES (null, 'Running Shoes', 0, 0, 0, 3);


CREATE VIEW monsterView AS
SELECT
  monsters.name AS 'Monster',
  monsters.hitpoints AS 'HP',
  monsters.attack AS 'ATK',
  monsters.defense AS 'DEF',
  monsters.speed AS 'SPD',
  monsters.special AS 'Special'
FROM
  monsters;

SELECT * FROM monsterView;



CREATE VIEW weaponView AS
SELECT
  weapons.name AS 'Weapon',
  weapons.attack AS 'ATK'
FROM
  weapons;

SELECT * FROM weaponView;



CREATE VIEW armorView AS
SELECT
  armor.name AS 'Armor',
  armor.defense AS 'DEF'
FROM
  armor;
  
SELECT * FROM armorView;



CREATE VIEW accessoryView AS
SELECT
  accessories.name AS 'Accessory',
  accessories.hitpoints AS 'HP',
  accessories.attack AS 'ATK',
  accessories.defense AS 'DEF',
  accessories.speed AS 'SPD'
FROM
  accessories;

SELECT * FROM accessoryView;