DROP TABLE IF EXISTS vt_SettingsContent;
DROP TABLE IF EXISTS vt_WideSlideContent;
DROP TABLE IF EXISTS vt_SideBlockContent;
DROP TABLE IF EXISTS vt_ProductContent;
DROP TABLE IF EXISTS vt_ProductCategoryContent;
DROP TABLE IF EXISTS vt_DContactReasonContent;

CREATE TABLE `vt_SettingsContent` (
 `name` varchar(255) NOT NULL,
 `lang` varchar(3) NOT NULL,
 `value` text NOT NULL,
 `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`name`,`lang`),
 KEY `lang` (`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

-- TODO: do not forget to update list of translated settings fields
INSERT INTO `vt_SettingsContent` (`name`, `lang`, `value`, `changed`)
  SELECT
    t.`name`,
    l.name AS `lang`,
    t.`value`,
    t.`changed`
  FROM `vt_DLanguage` l
    INNER JOIN `vt_Settings` t ON 1
  WHERE t.name IN ('footer');

--

DROP TABLE IF EXISTS vt_DLanguage;
CREATE TABLE IF NOT EXISTS `vt_DLanguage` (
  `name` varchar(3) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `icon` varchar(64) NOT NULL DEFAULT '',
  `locale` varchar(16) NOT NULL,
  `dir` enum('ltr','rtl') DEFAULT NULL,
  `def` enum('y','n') NOT NULL DEFAULT 'n',
  `enabled` enum('y','n') NOT NULL DEFAULT 'y',
  `sorter` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `vt_DLanguage` (`name`, `title`, `icon`, `locale`, `dir`, `def`, `enabled`, `sorter`) VALUES
  ('de', 'Deutsch', 'images/language/de.gif', 'de_CH', NULL, 'y', 'y', 0),
  ('en', 'English', 'images/language/us.gif', 'en_US', NULL, 'n', 'y', 1);

ALTER TABLE `vt_DLanguage`
ADD PRIMARY KEY (`name`), ADD KEY `enabled` (`enabled`);

--

CREATE TABLE `vt_WideSlideContent` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `linkText` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `vt_WideSlideContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vt_WideSlide` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vt_WideSlideContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `vt_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `vt_WideSlideContent` (`id`, `lang`, `title`, `description`, `url`, `linkText`)
SELECT
  t.`id`,
  l.name as `lang`,
  t.`title`,
  t.`description`,
  t.`url`,
  t.`linkText`
FROM `vt_DLanguage` l
INNER JOIN `vt_WideSlide` t ON 1;

ALTER TABLE `vt_WideSlide`
  DROP `title`,
  DROP `description`,
  DROP `url`,
  DROP `linkText`;

--

CREATE TABLE `vt_SideBlockContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `content` text NOT NULL,
  `linkTitle` varchar(255) NOT NULL DEFAULT '',
  `linkUrl` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `vt_SideBlockContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vt_SideBlock` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vt_SideBlockContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `vt_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `vt_SideBlockContent` (`id`, `lang`, `title`, `content`, `linkTitle`, `linkUrl`)
SELECT
  t.`id`,
  l.name as `lang`,
  t.`title`,
  t.`content`,
  t.`linkTitle`,
  t.`linkUrl`
FROM `vt_DLanguage` l
INNER JOIN `vt_SideBlock` t ON 1;

ALTER TABLE `vt_SideBlock`
  DROP `title`,
  DROP `content`,
  DROP `linkTitle`,
  DROP `linkUrl`;

--

CREATE TABLE `vt_ProductContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `rawDescription` text NOT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `vt_ProductContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vt_Product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vt_ProductContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `vt_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `vt_ProductContent` (`id`, `lang`, `title`, `description`, `rawDescription`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`,
    t.`description`,
    t.`rawDescription`
  FROM `vt_DLanguage` l
    INNER JOIN `vt_Product` t ON 1;

ALTER TABLE `vt_Product`
DROP `title`,
DROP `description`,
DROP `rawDescription`;

--

CREATE TABLE `vt_ProductCategoryContent` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `vt_ProductCategoryContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vt_ProductCategory` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vt_ProductCategoryContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `vt_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `vt_ProductCategoryContent` (`id`, `lang`, `title`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`
  FROM `vt_DLanguage` l
    INNER JOIN `vt_ProductCategory` t ON 1;

ALTER TABLE `vt_ProductCategory`
DROP `title`;

--

CREATE TABLE `vt_ProductImageContent` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `vt_ProductImageContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vt_ProductImage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vt_ProductImageContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `vt_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `vt_ProductImageContent` (`id`, `lang`, `title`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`
  FROM `vt_DLanguage` l
    INNER JOIN `vt_ProductImage` t ON 1;

ALTER TABLE `vt_ProductImage`
DROP `title`;

--

CREATE TABLE `vt_DContactReasonContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `vt_DContactReasonContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `vt_DContactReason` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `vt_DContactReasonContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `vt_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `vt_DContactReasonContent` (`id`, `lang`, `title`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`
  FROM `vt_DLanguage` l
    INNER JOIN `vt_DContactReason` t ON 1;

ALTER TABLE `vt_DContactReason`
DROP `title`;

--

UPDATE `vt_PageContent` SET `language` = 'en' WHERE `language` = 'eng';
UPDATE `vt_DraftPageContent` SET `language` = 'en' WHERE `language` = 'eng';

CREATE TEMPORARY TABLE `tmpPageContent` SELECT * FROM vt_PageContent;

INSERT INTO vt_PageContent (`idPage`, `language`, `title`, `menuTitle`, `header`, `keywords`, `description`)
SELECT `idPage`, 'de' AS `language`, `title`, `menuTitle`, `header`, `keywords`, `description`   FROM `tmpPageContent` tc WHERE `language` = 'en'
ON DUPLICATE KEY UPDATE `title` = tc.`title`, `menuTitle` = tc.`menuTitle`, `header` = tc.`header`, `keywords` = tc.`keywords`, `description` = tc.`description`;


CREATE TEMPORARY TABLE `tmpDraftPageContent` SELECT * FROM vt_DraftPageContent;

INSERT INTO vt_DraftPageContent (`idPage`, `language`, `title`, `menuTitle`, `header`, `keywords`, `description`)
  SELECT `idPage`, 'de' AS `language`, `title`, `menuTitle`, `header`, `keywords`, `description`   FROM `tmpDraftPageContent` tc WHERE `language` = 'en'
ON DUPLICATE KEY UPDATE `title` = tc.`title`, `menuTitle` = tc.`menuTitle`, `header` = tc.`header`, `keywords` = tc.`keywords`, `description` = tc.`description`;

--

UPDATE `vt_HtmlBlockContent` SET `language` = 'en' WHERE `language` = 'eng';
UPDATE `vt_DraftHtmlBlockContent` SET `language` = 'en' WHERE `language` = 'eng';

DELETE FROM vt_HtmlBlockContent WHERE `language` = 'de';
CREATE TEMPORARY TABLE `tmpHtmlBlockContent` SELECT * FROM vt_HtmlBlockContent;
INSERT INTO vt_HtmlBlockContent (`idBlock`, `language`, `content`, `rawContent`, `added`, `changed`)
  SELECT `idBlock`, 'de' AS `language`, `content`, `rawContent`, `added`, `changed` FROM `tmpHtmlBlockContent` tc WHERE `language` = 'en';

DELETE FROM vt_DraftHtmlBlockContent WHERE `language` = 'de';
CREATE TEMPORARY TABLE `tmpDraftHtmlBlockContent` SELECT * FROM vt_DraftHtmlBlockContent;
INSERT INTO vt_DraftHtmlBlockContent (`idBlock`, `language`, `content`, `rawContent`, `added`, `changed`)
  SELECT `idBlock`, 'de' AS `language`, `content`, `rawContent`, `added`, `changed` FROM `tmpDraftHtmlBlockContent` tc WHERE `language` = 'en';

--

ALTER TABLE `vt_PageContent`
    ADD `idParent` int(11) NOT NULL DEFAULT '0' AFTER `header`,
    ADD `alias` varchar(255) DEFAULT NULL AFTER `idParent`,

    ADD `redirectType` enum('page','url') DEFAULT NULL,
    ADD `redirectPageId` int(11) NOT NULL,
    ADD `redirectStatus` int(11) NOT NULL DEFAULT '301',
    ADD `redirectAlias` varchar(255) NOT NULL,
    ADD `redirectAnchor` varchar(64) NOT NULL,

    ADD `customUrlType` enum('page','url') DEFAULT NULL,
    ADD `customUrlPageId` int(11) NOT NULL,
    ADD `customUrlAlias` varchar(255) NOT NULL,
    ADD `customUrlAnchor` varchar(64) NOT NULL;


UPDATE `vt_PageContent` pc
INNER JOIN `vt_Page` p ON p.id = pc.idPage
SET
    pc.`idParent` = p.`idParent`,
    pc.`alias` = p.`alias`,

    pc.`redirectType` = p.`redirectType`,
    pc.`redirectPageId` = p.`redirectPageId`,
    pc.`redirectStatus` = p.`redirectStatus`,
    pc.`redirectAlias` = p.`redirectAlias`,
    pc.`redirectAnchor` = p.`redirectAnchor`,

    pc.`customUrlType` = p.`customUrlType`,
    pc.`customUrlPageId` = p.`customUrlPageId`,
    pc.`customUrlAlias` = p.`customUrlAlias`,
    pc.`customUrlAnchor` = p.`customUrlAnchor`;



ALTER TABLE `vt_Page` DROP INDEX `id_parent`;
ALTER TABLE `vt_PageContent` ADD UNIQUE KEY `idParent_alias_lang` (`idParent`, `alias`, `language`);

ALTER TABLE `vt_Page`
    DROP `alias`,

    DROP `redirectType`,
    DROP `redirectPageId`,
    DROP `redirectStatus`,
    DROP `redirectAlias`,
    DROP `redirectAnchor`,

    DROP `customUrlType`,
    DROP `customUrlPageId`,
    DROP `customUrlAlias`,
    DROP `customUrlAnchor`;

--

ALTER TABLE `vt_DraftPageContent`
    ADD `idParent` int(11) NOT NULL DEFAULT '0' AFTER `header`,
    ADD `alias` varchar(255) DEFAULT NULL AFTER `idParent`,

    ADD `redirectType` enum('page','url') DEFAULT NULL,
    ADD `redirectPageId` int(11) NOT NULL,
    ADD `redirectStatus` int(11) NOT NULL DEFAULT '301',
    ADD `redirectAlias` varchar(255) NOT NULL,
    ADD `redirectAnchor` varchar(64) NOT NULL,

    ADD `customUrlType` enum('page','url') DEFAULT NULL,
    ADD `customUrlPageId` int(11) NOT NULL,
    ADD `customUrlAlias` varchar(255) NOT NULL,
    ADD `customUrlAnchor` varchar(64) NOT NULL;


UPDATE `vt_DraftPageContent` pc
INNER JOIN `vt_DraftPage` p ON p.id = pc.idPage
SET
    pc.`idParent` = p.`idParent`,
    pc.`alias` = p.`alias`,

    pc.`redirectType` = p.`redirectType`,
    pc.`redirectPageId` = p.`redirectPageId`,
    pc.`redirectStatus` = p.`redirectStatus`,
    pc.`redirectAlias` = p.`redirectAlias`,
    pc.`redirectAnchor` = p.`redirectAnchor`,

    pc.`customUrlType` = p.`customUrlType`,
    pc.`customUrlPageId` = p.`customUrlPageId`,
    pc.`customUrlAlias` = p.`customUrlAlias`,
    pc.`customUrlAnchor` = p.`customUrlAnchor`;

ALTER TABLE `vt_DraftPage`
    DROP `alias`,

    DROP `redirectType`,
    DROP `redirectPageId`,
    DROP `redirectStatus`,
    DROP `redirectAlias`,
    DROP `redirectAnchor`,

    DROP `customUrlType`,
    DROP `customUrlPageId`,
    DROP `customUrlAlias`,
    DROP `customUrlAnchor`;

--