-- db updates reset after patch on March 24th.

-- Dialog to challenges:
ALTER TABLE  `challenges` ADD  `dialogStart` VARCHAR( 200 ) DEFAULT NULL ,
ADD  `dialogFail` VARCHAR( 200 ) DEFAULT NULL ,
ADD  `dialogSuccess` VARCHAR( 200 ) DEFAULT NULL ;

-- Add index necessary for stats updates
ALTER TABLE `bluePathery`.`solutions`
ADD INDEX `mapIDMoves` ( `mapID` , `moves` );

-- Remove unnecessary table
DROP TABLE `guestSolutions`;
DROP TABLE `statistics`;

-- ADD FOREIGN KEY CONSTRAINTS --
-- Achievements
DELETE FROM achievements WHERE userID NOT IN (SELECT ID from users);
ALTER TABLE `achievements` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Challenges
ALTER TABLE `challenges` ADD FOREIGN KEY ( `mapID` ) REFERENCES `challengeMaps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Challenge solutions
DELETE FROM challengeSolutions WHERE userID NOT IN (SELECT ID from users);
ALTER TABLE `challengeSolutions` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `challengeSolutions` ADD FOREIGN KEY ( `challengeID` ) REFERENCES `challenges` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Chat
ALTER TABLE `chat` CHANGE `userID` `userID` INT( 11 ) NULL;
UPDATE chat SET userID = NULL WHERE userID <= 0;
ALTER TABLE `chat` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Email Queue
ALTER TABLE `emailQueue` ADD FOREIGN KEY ( `emailID` ) REFERENCES `emails` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Emails
ALTER TABLE `emails` CHANGE `fromUserID` `fromUserID` INT( 11 ) NULL;
UPDATE emails SET fromUserID = NULL WHERE fromUserID <= 0;
ALTER TABLE `emails` ADD FOREIGN KEY ( `fromUserID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- MapOfTheDay
DELETE FROM mapOfTheDay WHERE mapID NOT IN (SELECT ID FROM maps);
ALTER TABLE `mapOfTheDay` ADD FOREIGN KEY ( `mapID` ) REFERENCES `maps` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Solutions
DELETE FROM `solutions` WHERE userID NOT IN (SELECT ID FROM users);
ALTER TABLE `solutions` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `solutions` ADD FOREIGN KEY ( `mapID` ) REFERENCES `maps` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Unlocks
ALTER TABLE `unlocks` ENGINE = InnoDB; -- Looks like this was forgotten?
DELETE FROM unlocks WHERE userID NOT IN (SELECT ID FROM users);
ALTER TABLE `unlocks` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- UserData
ALTER TABLE `userData` ENGINE = InnoDB; -- Looks like this was forgotten?
ALTER TABLE `userData` ADD FOREIGN KEY ( `userID` ) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- END ADD FOREIGN KEY CONSTRAINTS