-- #migration 2026_08_09_179_migrate_to_new_tables
-- Migre card_detail_steps → card_tutos
-- Migre card_guide_mesure_questions → metrics + card_metrics

-- 1. card_tutos depuis card_detail_steps
-- On crée un tuto par step, avec item_id = card_id + '_step_' + step_num
INSERT INTO card_tutos (card_id, item_id, titre, contenu)
SELECT
    s.card_id,
    CONCAT(s.card_id, '_step_', ds.step_num) AS item_id,
    ds.title,
    ds.content
FROM card_detail_steps ds
JOIN card_detail_sections s ON s.id = ds.section_id
WHERE s.type = 'method'
ON DUPLICATE KEY UPDATE titre=VALUES(titre), contenu=VALUES(contenu);

-- 2. metrics depuis card_guide_mesure_questions
INSERT INTO metrics (id, label, type, unit, cadence_jours)
SELECT
    CONCAT(card_id, '_', metric) AS id,
    label,
    type,
    unit,
    cadence_jours
FROM card_guide_mesure_questions
ON DUPLICATE KEY UPDATE label=VALUES(label), unit=VALUES(unit);

-- 3. card_metrics liaisons
INSERT IGNORE INTO card_metrics (card_id, metric_id, sort_order)
SELECT card_id, CONCAT(card_id, '_', metric), sort_order
FROM card_guide_mesure_questions;