-- patch added to simplify functionality transfer to future projects
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Patch "000-initial-multilang.sql" already applied to project !';

ALTER TABLE `qs_DLanguage` ADD COLUMN `adm_def` enum('y','n') DEFAULT 'n'  AFTER `def`;

INSERT INTO `qs_DLanguage` (`name`, `title`, `shortTitle`, `icon`, `locale`, `def`, `enabled`, `sorter`) VALUES
  ('ua', 'Ukrainian', 'UA', 'images/language/ua.gif', 'uk-UA', 'n', 'y', 1),
  ('ru', 'Russian', 'RU', 'images/language/ru.gif', 'ru-RU', 'n', 'y', 1);

UPDATE `qs_DLanguage` SET `name` = 'en', `adm_def` = 'y', locale = 'en-US' WHERE `name` = 'eng';

-- 

DROP TABLE IF EXISTS qs_SettingsContent;
CREATE TABLE `qs_SettingsContent` (
  `name` varchar(255) NOT NULL,
  `lang` varchar(3) NOT NULL,
  `value` text NOT NULL,
  `changed` datetime NOT NULL DEFAULT '2016-01-01 00:00:00',
  PRIMARY KEY (`name`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qs_SettingsContent_ibfk_1` FOREIGN KEY (`lang`) REFERENCES `qs_DLanguage` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- TODO: do not forget to update list of translated settings fields
INSERT INTO `qs_SettingsContent` (`name`, `lang`, `value`, `changed`)
  SELECT
    t.`name`,
    l.name AS `lang`,
    t.`value`,
    t.`changed`
  FROM `qs_DLanguage` l
    INNER JOIN `qs_Settings` t ON 1
  WHERE l.name != 'en' AND t.name IN (
    'header',
    'footer',

    'contactFormEmailUserSubject',
    'contactFormEmailUserFrom',
    'contactFormEmailUserBody',

    'newsletterFormUserEmailSubject',
    'newsletterFormUserEmailFrom',
    'newsletterFormUserEmailBody',

    'forgotPasswordEmailSubject',
    'forgotPasswordEmailFrom',
    'forgotPasswordEmailBody',

    'userRegistrationEmailSubject',
    'userRegistrationEmailFrom',
    'userRegistrationEmailBody'
  );
-- DELETE FROM `hs_SettingsContent` WHERE lang = 'en';

--

UPDATE `qs_PageContent` SET `language` = 'en' WHERE `language` = 'eng';
UPDATE `qs_DraftPageContent` SET `language` = 'en' WHERE `language` = 'eng';

CREATE TEMPORARY TABLE `tmpPageContent` SELECT * FROM qs_PageContent;
INSERT INTO qs_PageContent (`idPage`, `language`, `title`, `menuTitle`, `header`, `keywords`, `description`)
  SELECT `idPage`, 'ua' 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`;
INSERT INTO qs_PageContent (`idPage`, `language`, `title`, `menuTitle`, `header`, `keywords`, `description`)
  SELECT `idPage`, 'ru' 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 qs_DraftPageContent;
INSERT INTO qs_DraftPageContent (`idPage`, `language`, `title`, `menuTitle`, `header`, `keywords`, `description`)
  SELECT `idPage`, 'ua' 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`;
INSERT INTO qs_DraftPageContent (`idPage`, `language`, `title`, `menuTitle`, `header`, `keywords`, `description`)
  SELECT `idPage`, 'ru' 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 `qs_HtmlBlockContent` SET `language` = 'en' WHERE `language` = 'eng';
UPDATE `qs_DraftHtmlBlockContent` SET `language` = 'en' WHERE `language` = 'eng';

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

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

--

CREATE TABLE `qs_WideSlideContent` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `linkText` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qs_WideSlideContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qs_WideSlide` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qs_WideSlideContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qs_DLanguage` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qs_WideSlideContent` (`id`, `lang`, `title`, `image`, `description`, `url`, `linkText`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`,
    t.`image`,
    t.`description`,
    t.`url`,
    t.`linkText`
  FROM `qs_DLanguage` l
    INNER JOIN `qs_WideSlide` t ON 1;

ALTER TABLE `qs_WideSlide`
DROP `title`,
DROP `image`,
DROP `description`,
DROP `url`,
DROP `linkText`;
--

CREATE TABLE `qs_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 `qs_SideBlockContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qs_SideBlock` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qs_SideBlockContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qs_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

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

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

--

CREATE TABLE `qs_NewsContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  `metaTitle` varchar(255) NOT NULL DEFAULT '',
  `metaDescription` text NOT NULL,
  `metaKeywords` text NOT NULL,
  `introduction` text NOT NULL,
  `content` mediumtext NOT NULL,
  `rawContent` mediumtext NOT NULL,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qs_NewsContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qs_News` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qs_NewsContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qs_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qs_NewsContent` (`id`, `lang`, `title`, `metaTitle`, `metaDescription`, `metaKeywords`, `introduction`, `content`, `rawContent`, `url`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`,
    t.`metaTitle`,
    t.`metaDescription`,
    t.`metaKeywords`,
    t.`introduction`,
    t.`content`,
    t.`rawContent`,
    t.`url`
  FROM `qs_DLanguage` l
    INNER JOIN `qs_News` t ON 1;

ALTER TABLE `qs_News`
DROP `title`,
DROP `metaTitle`,
DROP `metaDescription`,
DROP `metaKeywords`,
DROP `introduction`,
DROP `content`,
DROP `rawContent`,
DROP `url`;

--

CREATE TABLE `qs_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 `qs_DContactReasonContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qs_DContactReason` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qs_DContactReasonContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qs_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qs_DContactReasonContent` (`id`, `lang`, `title`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`
  FROM `qs_DLanguage` l
    INNER JOIN `qs_DContactReason` t ON 1;

ALTER TABLE `qs_DContactReason`
DROP `title`;

--

CREATE TABLE `qs_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 `qs_DContactReasonContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qs_DContactReason` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qs_DContactReasonContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qs_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qs_DContactReasonContent` (`id`, `lang`, `title`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`
  FROM `qs_DLanguage` l
    INNER JOIN `qs_DContactReason` t ON 1;

ALTER TABLE `qs_DContactReason`
DROP `title`;