-- 2013-02-25

-- 1. Apply DB patches
ALTER TABLE `md_Dealer`
    ADD `type` ENUM('dealer', 'center') NOT NULL DEFAULT 'dealer' AFTER `serviceId`,
    ADD `centerType` ENUM('collision', 'service', 'collision_service') NULL DEFAULT NULL AFTER `type`;

ALTER TABLE `md_Dealer` ADD INDEX `type` (`type`);

ALTER TABLE `md_Dealer`
    CHANGE `phone` `phone` VARCHAR(255) NULL DEFAULT NULL,
    CHANGE `serviceId` `serviceId` VARCHAR(255) NULL DEFAULT NULL;


-- 2. Run import for "Collision and Service Centers" (filtered page)

-- 3. Update Geo Coordinates

-- 4. Manually create page:
--     Collision & Service Council > Find a Collision Center 
--     alias: "find-a-collision-or-service-center"
--     item: "Find a Dealer" (type: Find a Collision or Service Center)

-- 5. Manually update block config (set dealer) for page "Find a Dealer ID 105":


-- 2013-02-28
START TRANSACTION;

ALTER TABLE `md_Dealer`
    CHANGE `centerType` `oldType` ENUM('collision', 'service', 'collision_service') NULL DEFAULT NULL,
    ADD `centerType` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '1 - collision, 2 - service, 3 - collision & service' AFTER `type`;

UPDATE `md_Dealer`
SET `centerType` =
    CASE `oldType`
        WHEN 'collision_service' THEN 3
        WHEN 'collision' THEN 1
        WHEN 'service' THEN 2
        ELSE 0
    END
WHERE `type` = 'center';

ALTER TABLE `md_Dealer`
    DROP `oldType`;

COMMIT;