-- Adds the LinkedIn Downloader page and its item in the English main menu.
--
-- For the live database: run once after deploying app/Core/LinkedinMedia.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',
    'LinkedIn Downloader',
    'linkedin-downloader',
    'LinkedIn Video Downloader — Save Videos, Photos and Documents',
    'Download LinkedIn videos in HD, photos at full size and document posts page by page. Paste a post, profile or company link — free, no sign-in.',
    'linkedin downloader, linkedin video download, download linkedin video, linkedin photo download, linkedin document download',
    '',
    'LinkedIn Downloader',
    'Paste a link to any public LinkedIn post and save what is in it — video in HD with its sound, photos at full size, and documents page by page. Or paste a profile or company link to see its latest posts.',
    'Paste a LinkedIn post, profile or company link…',
    'Search',
    'linkedin',
    '<h2 class="ql-align-center">Save what a LinkedIn post holds</h2><p>Paste the address of a public LinkedIn post and download its video, photos or document pages in the best quality LinkedIn keeps. Paste a profile or company link instead to browse the pictures and videos in its latest posts.</p>',
    '<h2 class="ql-align-center">What this downloader does</h2>',
    '["<h3>Videos, photos and documents</h3><p>Paste a post and get its video in the best quality LinkedIn published, every photo at full size, and a document post page by page.</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>Profiles and company pages</h3><p>Paste a profile or company link to see its cover, 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 LinkedIn</h2><ol><li>Open the post on LinkedIn and copy its link (the three dots, then Copy link to post).</li><li>Paste it into the box above and press Search.</li><li>Choose Video, Audio, Photo or a document page, and the file is saved to your device.</li></ol>',
    '<h2 class="ql-align-center">Frequently Asked Questions</h2>',
    '[{"title":"Which LinkedIn links work?","description":"<p>Post links copied with the Share button, the feed/update links, lnkd.in short links, profile links (linkedin.com/in/…) and company pages. Country addresses such as pk.linkedin.com work too.</p>"},{"title":"Can I download a document post as a PDF?","description":"<p>LinkedIn only hands the file itself to signed-in members, so a document post is offered page by page, as pictures.</p>"},{"title":"Why does a post say it is for members only?","description":"<p>Some posts are shown only to people who are signed in to LinkedIn. Only posts LinkedIn shows to everyone can be downloaded here.</p>"},{"title":"Do I need a LinkedIn 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 = 'linkedin-downloader' AND language_id = @lang);

SET @page := (SELECT id FROM pages WHERE slug = 'linkedin-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 = '/linkedin-downloader');

-- Straight after X 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 = '/twitter-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, 'LinkedIn', '/linkedin-downloader', '_self', @after + 1, NOW(), NOW(), NULL, NULL
FROM DUAL
WHERE @menu IS NOT NULL AND @page IS NOT NULL AND @has_item = 0;