-- FAQs of a WinterCMS site with the Golem15.FAQ plugin, one JSON object per
-- line: the --file input of faq:import-winter.
--
--   mysql -N -B --raw <database> < winter-export.sql > faqs.jsonl
--
-- Read-only: it selects from the FAQ table and the translate locales table
-- and changes nothing. JSON_OBJECT needs MySQL 5.7.8+ or MariaDB 10.2.3+.
--
-- Check two names on the server before running:
--
-- 1. The FAQ table. This file assumes golem15_faq_faqs:
--      SHOW TABLES LIKE '%faq_faqs';
--
-- 2. The translate locales table that locale_id points at. This file
--    assumes golem15_translate_locales (Golem15.Translate). Check:
--      SHOW TABLES LIKE '%translate_locales';
--    A site on Winter.Translate or RainLab.Translate uses
--    winter_translate_locales or rainlab_translate_locales; change the JOIN
--    line to match.
--
-- locale_code is the locale's code: the command looks the code up among the
-- application's translate locales, so the ids may differ between the sites.
-- Every code must exist there before the import (an unknown code aborts it).
-- content is the stored question and answer list as text; the command
-- decodes it.
SELECT JSON_OBJECT(
    'id', f.id,
    'name', f.name,
    'slug', f.slug,
    'locale_code', l.code,
    'is_active', f.is_active,
    'description', f.description,
    'content', f.content,
    'created_at', f.created_at,
    'updated_at', f.updated_at
)
FROM golem15_faq_faqs f
LEFT JOIN golem15_translate_locales l ON l.id = f.locale_id
ORDER BY f.id;