-- Stage 5 Final Safety Audit (read-only)

SELECT 'cms_books' AS metric, COUNT(*) AS value FROM cms_books
UNION ALL
SELECT 'cms_contents_book', COUNT(*) FROM cms_contents c
  INNER JOIN cms_content_types t ON t.id = c.content_type_id WHERE t.`key` = 'book'
UNION ALL
SELECT 'book_chapters', COUNT(*) FROM book_chapters
UNION ALL
SELECT 'cms_contents_chapter', COUNT(*) FROM cms_contents c
  INNER JOIN cms_content_types t ON t.id = c.content_type_id WHERE t.`key` = 'chapter'
UNION ALL
SELECT 'book_chapter_relations', COUNT(*) FROM cms_content_relations WHERE relation_type = 'book_chapter'
UNION ALL
SELECT 'chapter_child_relations', COUNT(*) FROM cms_content_relations WHERE relation_type = 'chapter_child'
UNION ALL
SELECT 'products_book_id_nn', COUNT(*) FROM products WHERE book_id IS NOT NULL
UNION ALL
SELECT 'products_book_content_id_nn', COUNT(*) FROM products WHERE book_content_id IS NOT NULL;

SELECT 'mapping_mismatch' AS metric, COUNT(*) AS value
FROM products p
WHERE p.book_id IS NOT NULL
  AND (
    p.book_content_id IS NULL
    OR NOT EXISTS (
      SELECT 1 FROM cms_content_meta m
      WHERE m.content_id = p.book_content_id
        AND m.meta_key = 'source_book_id'
        AND CAST(m.meta_value AS UNSIGNED) = p.book_id
    )
  );

SELECT 'book_content_id_orphan' AS metric, COUNT(*) AS value
FROM products p
WHERE p.book_content_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM cms_contents c WHERE c.id = p.book_content_id);

SELECT 'source_book_id_orphan' AS metric, COUNT(*) AS value
FROM cms_content_meta m
INNER JOIN cms_contents c ON c.id = m.content_id
INNER JOIN cms_content_types t ON t.id = c.content_type_id AND t.`key` = 'book'
WHERE m.meta_key = 'source_book_id'
  AND NOT EXISTS (SELECT 1 FROM cms_books b WHERE b.id = CAST(m.meta_value AS UNSIGNED));

SELECT 'source_chapter_id_orphan' AS metric, COUNT(*) AS value
FROM cms_content_meta m
INNER JOIN cms_contents c ON c.id = m.content_id
INNER JOIN cms_content_types t ON t.id = c.content_type_id AND t.`key` = 'chapter'
WHERE m.meta_key = 'source_chapter_id'
  AND NOT EXISTS (SELECT 1 FROM book_chapters ch WHERE ch.id = CAST(m.meta_value AS UNSIGNED));

SELECT 'relation_orphan' AS metric, COUNT(*) AS value
FROM cms_content_relations r
WHERE NOT EXISTS (SELECT 1 FROM cms_contents c WHERE c.id = r.parent_content_id)
   OR NOT EXISTS (SELECT 1 FROM cms_contents c WHERE c.id = r.child_content_id);