-- 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 `qw_DLanguage` ADD COLUMN `adm_def` enum('y','n') DEFAULT 'n'  AFTER `def`;

INSERT INTO `qw_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 `qw_DLanguage` SET `name` = 'en', `adm_def` = 'y', locale = 'en-US' WHERE `name` = 'eng';

-- 

DROP TABLE IF EXISTS qw_SettingsContent;
CREATE TABLE `qw_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 `qw_SettingsContent_ibfk_1` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


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

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

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

  );
-- DELETE FROM `hs_SettingsContent` WHERE lang = 'en';

--

UPDATE `qw_PageContent` SET `language` = 'en' WHERE `language` = 'eng';
UPDATE `qw_DraftPageContent` SET `language` = 'en' WHERE `language` = 'eng';

CREATE TEMPORARY TABLE `tmpPageContent` SELECT * FROM qw_PageContent;
INSERT INTO qw_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 qw_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 qw_DraftPageContent;
INSERT INTO qw_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 qw_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 `qw_HtmlBlockContent` SET `language` = 'en' WHERE `language` = 'eng';
UPDATE `qw_DraftHtmlBlockContent` SET `language` = 'en' WHERE `language` = 'eng';

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

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

--

CREATE TABLE `qw_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 `qw_WideSlideContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_WideSlide` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qw_WideSlideContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qw_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 `qw_DLanguage` l
    INNER JOIN `qw_WideSlide` t ON 1;

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

CREATE TABLE `qw_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 `qw_SideBlockContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_SideBlock` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qw_SideBlockContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

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

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

--

CREATE TABLE `qw_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 `qw_DContactReasonContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_DContactReason` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qw_DContactReasonContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qw_DContactReasonContent` (`id`, `lang`, `title`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`
  FROM `qw_DLanguage` l
    INNER JOIN `qw_DContactReason` t ON 1;

ALTER TABLE `qw_DContactReason`
DROP `title`;

--

CREATE TABLE `qw_PostCategoryContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `metaTitle` varchar(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qw_PostCategoryContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_PostCategory` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qw_PostCategoryContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qw_PostCategoryContent` (`id`, `lang`, `title`, `metaTitle`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`,
    t.`metaTitle`
  FROM `qw_DLanguage` l
    INNER JOIN `qw_PostCategory` t ON 1;

ALTER TABLE `qw_PostCategory`
DROP `title`,
DROP `metaTitle`;

--

CREATE TABLE `qw_PostContent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang` varchar(3) NOT NULL,

  `title` varchar(255) NOT NULL,
  `metaTitle` varchar(255) NOT NULL DEFAULT '',
  `content` mediumtext NOT NULL,
  `rawContent` mediumtext NOT NULL,
  `excerpt` text NOT NULL,
  `author` varchar(255) DEFAULT NULL,
  `metaKeywords` text,
  `metaDescription` text,

  PRIMARY KEY (`id`,`lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qw_PostContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_Post` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qw_PostContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `qw_PostContent` (`id`, `lang`, `title`, `metaTitle`, `content`, `rawContent`, `excerpt`,
                              `author`, `metaKeywords`,`metaDescription`)
  SELECT
    t.`id`,
    l.name as `lang`,
    t.`title`,
    t.`metaTitle`,
    t.`content`,
    t.`rawContent`,
    t.`excerpt`,
    t.`author`,
    t.`metaKeywords`,
    t.`metaDescription`
  FROM `qw_DLanguage` l
    INNER JOIN `qw_Post` t ON 1;

ALTER TABLE `qw_Post`
DROP `title`,
DROP `metaTitle`,
DROP `content`,
DROP `rawContent`,
DROP `excerpt`,
DROP `author`,
DROP `metaKeywords`,
DROP `metaDescription`;

-- ----------------
-- Gallery
-- ----------------

CREATE TABLE `qw_GalleryContent` (
  `id`              INT(11)      UNSIGNED NOT NULL AUTO_INCREMENT,
  `lang`            VARCHAR(3)   NOT NULL,
  `title`           VARCHAR(255) NOT NULL DEFAULT '',
  `metaTitle`       VARCHAR(255) NOT NULL,
  `metaKeywords`    TEXT         NOT NULL,
  `metaDescription` TEXT         NOT NULL,
  `description`     TEXT         NOT NULL,
  `rawDescription`  TEXT         NOT NULL,
  PRIMARY KEY (`id`, `lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qw_GalleryContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_Gallery` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `qw_GalleryContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `qw_GalleryContent` (`id`, `lang`, `title`, `metaTitle`, `metaKeywords`, `metaDescription`, `description`, `rawDescription`)
  SELECT
    t.`id`,
    l.name AS `lang`,
    t.`title`,
    t.`metaTitle`,
    t.`metaKeywords`,
    t.`metaDescription`,
    t.`description`,
    t.`rawDescription`
  FROM `qw_DLanguage` l
    INNER JOIN `qw_Gallery` t ON 1;

ALTER TABLE `qw_Gallery`
  DROP `title`,
  DROP `metaTitle`,
  DROP `metaKeywords`,
  DROP `metaDescription`,
  DROP `description`,
  DROP `rawDescription`;


CREATE TABLE `qw_GalleryImageContent` (
  `id`          INT(11)      UNSIGNED NOT NULL AUTO_INCREMENT,
  `lang`        VARCHAR(3)   NOT NULL,
  `title`       VARCHAR(255) NOT NULL DEFAULT '',
  `description` TEXT         NOT NULL,
  PRIMARY KEY (`id`, `lang`),
  KEY `lang` (`lang`),
  CONSTRAINT `qw_GalleryImageContent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `qw_GalleryImage` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `qw_GalleryImageContent_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `qw_DLanguage` (`name`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `qw_GalleryImageContent` (`id`, `lang`, `title`, `description`)
  SELECT
    t.`id`,
    l.name AS `lang`,
    t.`title`,
    t.`description`
  FROM `qw_DLanguage` l
    INNER JOIN `qw_GalleryImage` t ON 1;

ALTER TABLE `qw_GalleryImage`
  DROP `title`,
  DROP `description`;