-- Remove 'on update CURRENT_TIMESTAMP' from dateLogin
ALTER TABLE  `users` CHANGE  `dateLogin`  `dateLogin` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `mapOfTheDay` DROP INDEX `UniqueTypeDate`;
DELETE FROM mapOfTheDay WHERE ID IN (2224, 2334, 2443, 2469, 2328);
DELETE FROM solutions WHERE ID = 29157;
CREATE UNIQUE INDEX `UniqueTypeDate` ON `mapOfTheDay` ( `mapDate` , `mapType` );

ALTER TABLE `mapOfTheDay` ADD `championPointsWorth` INT NOT NULL DEFAULT '0';

ALTER TABLE `users` ADD `totalMazes` INT NOT NULL DEFAULT '0',
ADD `totalMoves` INT NOT NULL DEFAULT '0';

ALTER TABLE `solutions` ADD `isTiedForHighScore` BOOLEAN NOT NULL DEFAULT '0';

ALTER TABLE  `users` ADD  `challengeTier` INT NOT NULL ,
ADD  `championPoints` INT NOT NULL ,
ADD  `totalWins` INT DEFAULT  '0' NOT NULL ,
ADD  `totalTies` INT DEFAULT  '0' NOT NULL ;

ALTER TABLE  `solutions` ADD  `championPointsWorth` INT DEFAULT  '0' NOT NULL ,
ADD  `isHighScore` TINYINT( 1 ) NOT NULL ;

-- Add the 'mapExpireTime' column
ALTER TABLE `mapOfTheDay` ADD `mapExpireTime` DATETIME NULL DEFAULT NULL AFTER `mapDate`;

UPDATE mapOfTheDay
SET mapExpireTime = DATE_ADD(mapDate, INTERVAL 1 DAY)
WHERE mapType <> 5;

UPDATE mapOfTheDay
SET mapExpireTime = DATE_ADD(mapDate, INTERVAL 2 DAY)
WHERE mapType = 5;

ALTER TABLE `mapOfTheDay` CHANGE `mapExpireTime` `mapExpireTime` DATETIME NOT NULL;

ALTER TABLE `mapOfTheDay` ADD INDEX ( `mapExpireTime` , `mapType` );

-- Move shit from userData table to users
ALTER TABLE `users` ADD `displayColor` VARCHAR( 7 ) NOT NULL DEFAULT '#cccccc' ,
ADD `wallColor` VARCHAR( 7 ) NOT NULL DEFAULT '#666666' ,
ADD `wallEmblem` VARCHAR( 64 ) NOT NULL DEFAULT 'blank.png';

UPDATE users, userData
SET users.displayColor = userData.displayColor,
    users.wallColor = userData.wallColor,
    users.wallEmblem = userData.wallEmblem
WHERE users.ID = userData.userID;

DROP TABLE `userData`;

ALTER TABLE `users` ADD `dateChatGaggedUntil` DATETIME NULL ,
ADD `isOptedOutOfEmails` BOOLEAN NOT NULL DEFAULT '0';

-- Most special maps (and some others) have the map-name in the map code.  Copy it to the name column.
UPDATE maps
INNER JOIN
(
    -- Get the code substring, starting at the map name
    SELECT SUBSTRING(code, LENGTH(SUBSTRING_INDEX(code, '.', 5))+2) AS codeSubstring, Id
    FROM (SELECT code, Id FROM maps) AS mapsInner
) AS mapsOuter ON maps.Id = mapsOuter.Id
INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId
SET maps.name = SUBSTRING(codeSubstring, 1, INSTR(codeSubstring, ':')-2)
WHERE maps.name IS NULL;

-- Rename the remaining maps
UPDATE maps
INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId
SET maps.name = 'Simple'
WHERE mapOfTheDay.mapType = 1
AND (maps.name IS NULL OR maps.name = '');

UPDATE maps
INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId
SET maps.name = 'Normal'
WHERE mapOfTheDay.mapType = 2
AND (maps.name IS NULL OR maps.name = '');

UPDATE maps
INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId
SET maps.name = 'Complex'
WHERE mapOfTheDay.mapType = 3
AND (maps.name IS NULL OR maps.name = '');

UPDATE maps
INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId
SET maps.name = 'Special'
WHERE mapOfTheDay.mapType = 4
AND (maps.name IS NULL OR maps.name = '');

UPDATE maps
INNER JOIN mapOfTheDay ON maps.Id = mapOfTheDay.mapId
SET maps.name = 'Ultra Complex'
WHERE mapOfTheDay.mapType = 5
AND (maps.name IS NULL OR maps.name = '');

-- Need to support bigger solutions for these crazy new unlimited-tower maps
ALTER TABLE `solutions` CHANGE `solution` `solution` VARCHAR( 4095 ) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;

-- Fix Typo
UPDATE unlocks SET name = 'Light Diagonal Cover' WHERE name = 'Light Diagnol Cover'

-- Change index to a unique index
-- If any duplicates exist you can find them with this:
-- select mapID, count(*) from `solutions` group by mapID, userID HAVING count(*) > 1
ALTER TABLE  `solutions` DROP INDEX  `userID` ,
ADD UNIQUE  `userIDmapID` (  `userID` ,  `mapID` )
UPDATE unlocks SET name = 'Light Diagonal Cover' WHERE name = 'Light Diagnol Cover';

-- The new code relies on these being unique, so we'll enforce that through indices.  Make sure you
-- verify these ARE unique in the DB before adding these indices (they weren't in my test DB)
-- Should probably run these two commands by themselves, one at a time
ALTER TABLE `challengeMaps` ADD UNIQUE ( `challengeTier` , `challengeSuborder`);

ALTER TABLE `challenges`
DROP INDEX `mapID` ,
ADD UNIQUE `mapID` ( `mapID` , `ordering` );