
-- Get original page details
SELECT substr(name, 1, (locate('gift-vouchers', name) - 1)), id, site_id, title, name, description
INTO @pagePrefix, @id, @site, @title, @name, @desc
FROM `page`
WHERE name like '%gift-vouchers%';

-- Rename old gift vouchers page
UPDATE `page`
SET `name` = CONCAT(@pagePrefix, 'gift-vouchers-old/')
WHERE `name` = @name;

-- Get page hero & intro content
SELECT EXTRACTVALUE(substr(cache, locate('<content>', cache)), '/content/module[@type="page-hero"]'),
       EXTRACTVALUE(substr(cache, locate('<content>', cache)), '//module[@type="standard-content"][1]')
INTO @pageHeroData, @pageIntroData
FROM `page`
WHERE `id` = @id;

-- Get site details
SELECT `template_dir` INTO @siteName
FROM `site`
WHERE `id` = @site;

-- Get event master links
SET @baseUrl = '';
SET @eventId = '';

SELECT IF(SUBSTR(cache, LOCATE('https', cache), locate('event_id=', cache) - LOCATE('https', cache)) != '',
        SUBSTR(cache, LOCATE('https', cache), locate('event_id=', cache) - LOCATE('https', cache)),
        CONCAT('https://tickets.', @siteName, '.co.uk/shop/cart.pl?my_action=choose_package')
    ) INTO @baseUrl
FROM `page`
WHERE `id` = @id;

SELECT SUBSTR(cache, LOCATE('event_id=', cache), 25) into @eventId
FROM `page`
WHERE `id` = @id;

-- Build the page
SET @pageHeader = '<?xml version="1.0" encoding="utf8"?>
<!DOCTYPE content [
    <!ELEMENT content (module | modal)*>
    <!ELEMENT module (#PCDATA)>
    <!ELEMENT modal (#PCDATA)>
    <!ATTLIST module key CDATA #REQUIRED>
    <!ATTLIST module type CDATA #REQUIRED>
    <!ATTLIST modal key CDATA #REQUIRED>
]>';

SET @pageHero = CONCAT(
'<module key="ab0e6e3c-b464-44f9-acf4-3c68f859e134" type="page-hero">
    <![CDATA[', @pageHeroData, ']]>
</module>');

SET @pageIntro = CONCAT(
'<module key="02f0c65f-781d-45c6-a2a1-85ec9abd562a" type="standard-content">
    <![CDATA[', @pageIntroData, ']]>
</module>');

-- Try and get image for 50/50
SET @imageId = 0;
SET @filename = '';
SET @altTag = '';

SELECT id, original_filename, alt_tag INTO @imageId, @filename, @altTag
FROM `image`
WHERE `width` = 800 and `height` = 716 and `alt_tag` like '%horse%' limit 1;

-- Build 50/50
SET @fifty50Data = CONCAT(
'{"template_name":"hlg-txt50-img50_1sqr","template_inverse":false,',
'"data":{"title":{"text":"How our Gift Vouchers work","visible":"true"},"paragraph":{"text":"<p>Our Gift Vouchers are available to buy in various denominations that can be redeemed online for full or part-payment for any ticket or restaurant package across 16 racecourses across the country.&nbsp;</p><p>Recipients can choose from a variety of different fixtures. From chart-topping music nights to family fun days, thrilling Group 1 racedays to stylish Ladies Days.</p>","visible":true},',
'"cta":{"label":"Buy Now","target":"_blank","url":"', @baseUrl, @eventId, '","visible":"true"},',
'"image50sqr":{"id":"',@imageId,'","filename":"',@filename,'","alt":"',@altTag,'",',
'"tablet_land":{"id":"","filename":"","alt":""},',
'"tablet_port":{"id":"","filename":"","alt":""},',
'"mobile":{"id":"","filename":"","alt":""}}}}'
);

SET @fifty50 = CONCAT(
'<module key="496cadee-331e-4563-b6a7-ee0c0963d867" type="standard-content">
    <![CDATA[', @fifty50Data, ']]>
</module>');

-- Build Terms
SET @termsData = '{"template_name":"hlg-txt100","template_inverse":false,"data":{"title":{"text":"Title","visible":"false"},"paragraph":{"text":"<h3>Terms &amp; Conditions</h3><p>Gift vouchers are valid for a 12-month period from purchase and are valid to be used as full or part-payment for any online package(s). Exclusions may apply for special offers, including Black Friday and January sale vouchers and these will be clear on the special offer voucher. Any remaining Gift Voucher balance will remain in your account until the expiry date. Gift vouchers are valid for online purchases only. Gift vouchers must be redeemed in advance and are unable to be processed over the phone or in-person at the venue. Gift Vouchers are non-refundable, non-transferable and have no cash alternative. Rewards4Racing vouchers cannot be used alongside Gift Vouchers.</p>","visible":true},"cta":{"label":"Promo Block CTA","target":"_self","url":"/","visible":"false"}},"block_type":"standard-content"}';

SET @terms = CONCAT(
'<module key="4f830f6f-127f-4129-8bd0-7f4e0be80079" type="standard-content">
    <![CDATA[', @termsData, ']]>
</module>');

-- Key facts
SET @keyFactsData = CONCAT('{"template_name":"key-facts-center","template_inverse":false,"data":{"title":{"text":"Title","visible":false},"content":{"text":"Content","visible":false},"items":[{"icon":{"name":"icon-15-racecourses","visible":false},"title":{"text":"Want To Buy A Voucher?","visible":true},"content":{"text":"Content","visible":false},"cta":{"label":"Buy Now","url":"', CONCAT(@baseUrl, @eventId), '","target":"_blank","visible":true}}]}}');

SET @keyFacts = CONCAT(
'<module key="f75ce3e9-f117-4624-a2af-5ebb814614f8" type="key-facts">
    <![CDATA[', @keyFactsData, ']]>
</module>');

-- Salesforce email signup
SET @emailSignup = CONCAT(
'<module key="207706c5-f4ea-48ba-8980-85e44e0a1635" type="salesforce-form">
    {"template_name":"salesforce-form-pre-newsletter","template_inverse":true}
</module>');

SET @pageXML = CONCAT(@pageHeader, '<content>', @pageHero, @PageIntro, @fifty50, @terms, @keyFacts, @emailSignup, '</content>');

-- Add new gift vouchers page
INSERT INTO `page` (`site_id`, `image_id`, `title`, `name`, `description`, `cache`, `parent_page_id`, `previous_page_id`, `next_page_id`, `sitemap_priority`, `sitemap_frequency`, `sitemap_lastmod`, `menu_order`, `include_on_menu`)
VALUES
(@site, NULL, @title, @name, @desc, @pageXML, 0, 0, 0, 0.9, 'monthly', NULL, 0, 1);

