-- Adds the X (Twitter) Downloader page and its item in the English main menu.
--
-- For the live database: run once after deploying app/Core/TwitterMedia.php
-- and app/Core/Downloaders.php (without them the page opens, but its search
-- box has no downloader behind it). Safe to run twice: nothing is added when
-- the page or the menu item already exists.
--
-- The page is published with search-engine indexing off, like the Reddit
-- page; switch it on in Pages once you are happy with the wording.

SET @lang := (SELECT id FROM languages WHERE attribute = 'en' ORDER BY id LIMIT 1);

INSERT INTO pages (language_id, is_published, allow_search_indexing, page_name, slug, meta_title, meta_description, meta_keywords, featured_image, banner_title, banner_description, input_placeholder, search_button_text, downloader_type, content_description, feature_heading, feature_descriptions, instructions_description, faq_heading, faq_tabs, created_at, updated_at, created_by, updated_by)
SELECT @lang,
    '1',
    '0',
    'X (Twitter) Downloader',
    'twitter-downloader',
    'X (Twitter) Video Downloader — Save Videos, GIFs and Photos',
    'Download X (Twitter) videos in HD, GIFs as MP4 and photos at full size. Paste a post link or an account name — free, no sign-in.',
    'x downloader, twitter downloader, twitter video download, x video downloader, download twitter gif, twitter photo download',
    '',
    'X (Twitter) Downloader',
    'Paste a link to any public X post and save what is in it — video in HD with its sound, GIFs and photos at full size. Or type an account name to see its latest posts.',
    'Paste an X post link, or an account name…',
    'Search',
    'twitter',
    '<h2 class="ql-align-center">Save what an X post holds</h2><p>Paste the address of a public post on X (Twitter) and download its video, GIF or photos in the best quality X keeps. Type an account name instead to browse the photos and videos in its latest posts.</p>',
    '<h2 class="ql-align-center">What this downloader does</h2>',
    '["<h3>Videos, GIFs and photos</h3><p>One box handles them all &mdash; paste a post and get its video in the best quality X published, every photo at the size it was uploaded, and GIFs as MP4 files.</p>","<h3>Sound on its own</h3><p>Every video post also offers its audio as a separate MP3, for when the sound is what you are after.</p>","<h3>Whole accounts</h3><p>Type an account name or paste its link to see its banner, profile picture and the photos and videos from its latest posts, ready to save one by one.</p>"]',
    '<h2 class="ql-align-center">How to download from X (Twitter)</h2><ol><li>Open the post on X and copy its link (Share, then Copy link).</li><li>Paste it into the box above and press Search.</li><li>Choose Video, Audio, Photo or GIF, and the file is saved to your device.</li></ol>',
    '<h2 class="ql-align-center">Frequently Asked Questions</h2>',
    '[{"title":"Does it work with twitter.com links?","description":"<p>Yes. X and Twitter links are the same thing, and so are the fxtwitter, vxtwitter and t.co links people share. Paste any of them.</p>"},{"title":"What quality are the videos?","description":"<p>The highest X published for that video &mdash; up to 1080p. The quality is shown on the download button.</p>"},{"title":"Can I download from a private account?","description":"<p>No. Posts from a protected account are shown only to the people it has approved, so they cannot be downloaded here.</p>"},{"title":"Do I need an X account?","description":"<p>No. Nothing to sign in to and nothing to install &mdash; paste a link and download.</p>"}]',
    NOW(), NOW(), NULL, NULL
FROM DUAL
WHERE @lang IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM pages WHERE slug = 'twitter-downloader' AND language_id = @lang);

SET @page := (SELECT id FROM pages WHERE slug = 'twitter-downloader' AND language_id = @lang LIMIT 1);
SET @menu := (SELECT id FROM menus WHERE language_id = @lang ORDER BY id LIMIT 1);
SET @has_item := (SELECT COUNT(*) FROM menu_items WHERE menu_id = @menu AND url = '/twitter-downloader');

-- Straight after Reddit when the menu has it, otherwise at the end.
SET @after := COALESCE(
    (SELECT sort_order FROM menu_items WHERE menu_id = @menu AND parent_id IS NULL AND url = '/reddit-downloader' LIMIT 1),
    (SELECT MAX(sort_order) FROM menu_items WHERE menu_id = @menu AND parent_id IS NULL)
);

-- Make room: everything after that point moves down one place.
UPDATE menu_items SET sort_order = sort_order + 1
WHERE menu_id = @menu AND parent_id IS NULL AND sort_order > @after AND @has_item = 0 AND @page IS NOT NULL;

INSERT INTO menu_items (menu_id, parent_id, item_type, reference_id, title, url, target, sort_order, created_at, updated_at, created_by, updated_by)
SELECT @menu, NULL, 'page', @page, 'X', '/twitter-downloader', '_self', @after + 1, NOW(), NOW(), NULL, NULL
FROM DUAL
WHERE @menu IS NOT NULL AND @page IS NOT NULL AND @has_item = 0;