/*
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
 */
/**
 * Author:  Skylar Ittner
 * Created: Apr 26, 2019
 */

ALTER TABLE `terranquest`.`inventory`
DROP FOREIGN KEY `fk_inventory_players`;

ALTER TABLE `terranquest`.`player_badges`
DROP FOREIGN KEY `fk_achievements_has_players_players1`;

ALTER TABLE `terranquest`.`locations`
DROP FOREIGN KEY `fk_locations_players1`;

ALTER TABLE `terranquest`.`claimedcodes`
DROP FOREIGN KEY `fk_claimedcodes_players1`;

ALTER TABLE `terranquest`.`munzee`
DROP FOREIGN KEY `fk_munzee_players1`;

ALTER TABLE `terranquest`.`messages`
DROP FOREIGN KEY `fk_messages_players1`;

ALTER TABLE `terranquest`.`artifacts`
DROP FOREIGN KEY `fk_artifacts_players1`;

ALTER TABLE `terranquest`.`private_messages`
DROP FOREIGN KEY `fk_private_messages_players1`,
DROP FOREIGN KEY `fk_private_messages_players2`;

ALTER TABLE `terranquest`.`inventory`
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;

ALTER TABLE `terranquest`.`players`
CHANGE COLUMN `uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;

ALTER TABLE `terranquest`.`player_badges`
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;

ALTER TABLE `terranquest`.`locations`
CHANGE COLUMN `owneruuid` `ownerid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;

ALTER TABLE `terranquest`.`claimedcodes`
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;

ALTER TABLE `terranquest`.`munzee`
CHANGE COLUMN `player_uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;

ALTER TABLE `terranquest`.`messages`
CHANGE COLUMN `message` `message` VARCHAR(500) COLLATE 'utf8mb4_bin' NOT NULL ,
CHANGE COLUMN `uuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;

ALTER TABLE `terranquest`.`artifacts`
CHANGE COLUMN `currentlife` `currentlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
CHANGE COLUMN `maxlife` `maxlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
CHANGE COLUMN `playeruuid` `accountid` VARCHAR(60) CHARACTER SET 'utf8' NULL DEFAULT NULL ;

ALTER TABLE `terranquest`.`private_messages`
CHANGE COLUMN `from_uuid` `from_id` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL DEFAULT 0 ,
CHANGE COLUMN `to_uuid` `to_id` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL ;

ALTER TABLE `terranquest`.`inventory`
ADD CONSTRAINT `fk_inventory_players`
  FOREIGN KEY (`accountid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`player_badges`
ADD CONSTRAINT `fk_achievements_has_players_players1`
  FOREIGN KEY (`accountid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`locations`
ADD CONSTRAINT `fk_locations_players1`
  FOREIGN KEY (`ownerid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`claimedcodes`
ADD CONSTRAINT `fk_claimedcodes_players1`
  FOREIGN KEY (`accountid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`munzee`
ADD CONSTRAINT `fk_munzee_players1`
  FOREIGN KEY (`accountid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`messages`
ADD CONSTRAINT `fk_messages_players1`
  FOREIGN KEY (`accountid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`artifacts`
ADD CONSTRAINT `fk_artifacts_players1`
  FOREIGN KEY (`accountid`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`private_messages`
ADD CONSTRAINT `fk_private_messages_players1`
  FOREIGN KEY (`from_id`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_private_messages_players2`
  FOREIGN KEY (`to_id`)
  REFERENCES `terranquest`.`players` (`accountid`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `terranquest`.`items`
ADD COLUMN `weight` INT(3) NOT NULL DEFAULT 1 AFTER `itemcode`;

ALTER TABLE `terranquest`.`artifacts`
CHANGE COLUMN `currentlife` `currentlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ,
CHANGE COLUMN `maxlife` `maxlife` DECIMAL(7,2) NOT NULL DEFAULT 100 ;

ALTER TABLE `terranquest`.`players`
ADD COLUMN `stealth` TINYINT(1) NOT NULL DEFAULT 0 AFTER `kick`;

ALTER TABLE `terranquest`.`locations`
CHANGE COLUMN `osmid` `osmid` VARCHAR(20) NOT NULL ,
ADD COLUMN `lastactivity` DATETIME NULL DEFAULT NULL AFTER `data`;

ALTER TABLE `terranquest`.`messages`
CHANGE COLUMN `message` `message` TEXT COLLATE 'utf8mb4_bin' NOT NULL ;

ALTER TABLE `terranquest`.`private_messages`
CHANGE COLUMN `message` `message` TEXT NOT NULL ;

ALTER TABLE `terranquest`.`items`
ADD COLUMN `icon` VARCHAR(100) NULL DEFAULT NULL AFTER `weight`,
ADD COLUMN `color` VARCHAR(45) NULL DEFAULT NULL AFTER `icon`;

CREATE TABLE IF NOT EXISTS `terranquest`.`player_stats` (
  `accountid` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL,
  `claims` INT(11) NOT NULL DEFAULT 0,
  `attacks` INT(11) NOT NULL DEFAULT 0,
  `defends` INT(11) NOT NULL DEFAULT 0,
  `scans` INT(11) NOT NULL DEFAULT 0,
  `distancekm` DECIMAL(8,3) NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`accountid`),
  UNIQUE INDEX `accountid_UNIQUE` (`accountid` ASC),
  CONSTRAINT `fk_player_stats_players1`
    FOREIGN KEY (`accountid`)
    REFERENCES `terranquest`.`players` (`accountid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

ALTER TABLE `terranquest`.`badges`
DROP COLUMN `badgesid`,
CHANGE COLUMN `badgename` `name` VARCHAR(50) CHARACTER SET 'utf8' NOT NULL ,
CHANGE COLUMN `badgedesc` `desc` VARCHAR(400) CHARACTER SET 'utf8' NOT NULL ,
ADD COLUMN `icon` VARCHAR(100) NOT NULL AFTER `desc`,
ADD COLUMN `color` VARCHAR(45) NOT NULL DEFAULT 'white' AFTER `icon`,
ADD COLUMN `background` VARCHAR(255) NULL DEFAULT '#008000' AFTER `color`;

INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (1,'Beta Tester','Took part in the version 1 beta.','game-icon game-icon-ladybug','black','#5bca6c');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (2,'Developer','Contributed to TerranQuest code.','game-icon game-icon-computing','#32d74a','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (3,'Administrator','A TerranQuest administrator.','game-icon game-icon-processor','#00ff3b','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (4,'Moderator','Wielder of the banhammer.','game-icon game-icon-thor-hammer','red','white');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (5,'Alpha Tester','Helped during the version 1 alpha.','game-icon game-icon-spotted-bug','black','#ff5c31');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (6,'Migrator','Played TerranQuest version 1.','game-icon game-icon-upgrade','black','#00c853');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (7,'Attacker','Attacked places 1,000 times.','game-icon game-icon-targeting','red','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (8,'Assassin','Attacked places 10,000 times.','game-icon game-icon-multiple-targets','red','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (9,'Defender','Defended places 1,000 times.','game-icon game-icon-shield','green','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (10,'Guard','Defended places 10,000 times.','game-icon game-icon-shield-reflect','green','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (11,'Finder','Scanned 250 codes.','game-icon game-icon-target-laser','white','black');
INSERT INTO `badges` (`badgeid`,`name`,`desc`,`icon`,`color`,`background`) VALUES (12,'Hoarder','Scanned 1,000 codes.','game-icon game-icon-open-treasure-chest','#ff6f00','white');