/**
 * One-time backfill + DROP for adopters migrating flat jsonb category/tag columns.
 * Run inside a single transaction after contentTaxonomyMigrationSql() (spec §5).
 */
export const contentTaxonomyBackfillSql = (): string =>
  `
INSERT INTO content_terms (taxonomy, slug, name, depth)
SELECT DISTINCT 'category', value, value, 0
FROM (
  SELECT jsonb_array_elements_text(category) AS value FROM content_entries
  UNION
  SELECT jsonb_array_elements_text(category) AS value FROM content_revisions
) s
WHERE value IS NOT NULL AND value <> ''
ON CONFLICT DO NOTHING;

INSERT INTO content_terms (taxonomy, slug, name, depth)
SELECT DISTINCT 'tag', value, value, 0
FROM (
  SELECT jsonb_array_elements_text(tag) AS value FROM content_entries
  UNION
  SELECT jsonb_array_elements_text(tag) AS value FROM content_revisions
) s
WHERE value IS NOT NULL AND value <> ''
ON CONFLICT DO NOTHING;

INSERT INTO content_entry_terms (entry_id, term_id)
SELECT e.id, t.id
FROM content_entries e,
     jsonb_array_elements_text(e.category) AS v(value)
JOIN content_terms t ON t.taxonomy = 'category' AND t.slug = v.value
ON CONFLICT DO NOTHING;

INSERT INTO content_entry_terms (entry_id, term_id)
SELECT e.id, t.id
FROM content_entries e,
     jsonb_array_elements_text(e.tag) AS v(value)
JOIN content_terms t ON t.taxonomy = 'tag' AND t.slug = v.value
ON CONFLICT DO NOTHING;

UPDATE content_revisions r
SET term_ids = (
  SELECT COALESCE(jsonb_agg(t.id), '[]'::jsonb)
  FROM content_terms t
  WHERE (t.taxonomy = 'category' AND t.slug = ANY (
    ARRAY(SELECT jsonb_array_elements_text(r.category))
  ))
  OR (t.taxonomy = 'tag' AND t.slug = ANY (
    ARRAY(SELECT jsonb_array_elements_text(r.tag))
  ))
);

ALTER TABLE content_entries DROP COLUMN IF EXISTS category, DROP COLUMN IF EXISTS tag;
ALTER TABLE content_revisions DROP COLUMN IF EXISTS category, DROP COLUMN IF EXISTS tag;
`.trim()