-- =====================================================
-- Script de migration pour récupérer les ical_uid manquants
-- depuis Microsoft 365 via les calendar_event_id existants
-- =====================================================

-- 1. Créer une table temporaire pour stocker les mappings M365
CREATE TEMPORARY TABLE temp_m365_mappings (
    calendar_event_id VARCHAR(255) NOT NULL,
    ical_uid VARCHAR(255) NOT NULL,
    PRIMARY KEY (calendar_event_id)
);

-- 2. Identifier les appointments sans ical_uid mais avec calendar_event_id
SELECT 
    'Appointments manquant ical_uid:' as info,
    COUNT(*) as count
FROM appointments 
WHERE calendar_event_id IS NOT NULL 
  AND (ical_uid IS NULL OR ical_uid = '');

-- 3. Lister les calendar_event_id à traiter
SELECT DISTINCT calendar_event_id
FROM appointments 
WHERE calendar_event_id IS NOT NULL 
  AND (ical_uid IS NULL OR ical_uid = '')
ORDER BY created_at DESC;

-- 4. Une fois les données M365 récupérées via N8N, mettre à jour
-- (Cette requête sera exécutée après avoir populé temp_m365_mappings)
/*
UPDATE appointments a
INNER JOIN temp_m365_mappings m ON a.calendar_event_id = m.calendar_event_id
SET a.ical_uid = m.ical_uid,
    a.updated_at = NOW()
WHERE a.ical_uid IS NULL OR a.ical_uid = '';
*/

-- 5. Vérification post-migration
-- SELECT 
--     'Post-migration:' as info,
--     COUNT(*) as total_appointments,
--     SUM(CASE WHEN ical_uid IS NOT NULL AND ical_uid != '' THEN 1 ELSE 0 END) as with_ical_uid,
--     SUM(CASE WHEN calendar_event_id IS NOT NULL THEN 1 ELSE 0 END) as with_calendar_id
-- FROM appointments
-- WHERE status IN ('CONFIRMED', 'PENDING');