-- ===========================================
-- Migration 001 : Mise a jour event types V1.1
-- ===========================================
-- Ajoute les nouveaux evenements identifies par les agents Ecole et QALIOPI
-- Remplace les anciens evenements QALIOPI (suivi_*.updated, alerte.decrochage)
-- par les noms corrects (document.status_changed, formation.completion, etc.)
-- Corrige l'URL de base de QALIOPI
-- ===========================================

-- -------------------------------------------
-- 1. Corriger l'URL de QALIOPI
-- -------------------------------------------
UPDATE apps SET base_url = 'https://gestion.altheaprovence.com' WHERE slug = 'qaliopi';

-- -------------------------------------------
-- 2. Supprimer les anciens event types QALIOPI (et leurs subscriptions)
--    Seulement si aucun event n'a ete publie avec ces types
-- -------------------------------------------

-- Desactiver les subscriptions aux anciens types
UPDATE subscriptions SET is_active = 0
WHERE event_type_id IN (SELECT id FROM event_types WHERE slug IN ('suivi_financement.updated', 'suivi_formation.updated', 'alerte.decrochage'));

-- Desactiver les anciens types (soft delete — on ne supprime pas pour l'audit)
UPDATE event_types SET is_active = 0
WHERE slug IN ('suivi_financement.updated', 'suivi_formation.updated', 'alerte.decrochage');

-- Supprimer aussi eleve.updated (fusionne dans inscription.updated)
UPDATE event_types SET is_active = 0 WHERE slug = 'eleve.updated';
UPDATE subscriptions SET is_active = 0
WHERE event_type_id IN (SELECT id FROM event_types WHERE slug = 'eleve.updated');

-- -------------------------------------------
-- 3. Ajouter les nouveaux event types Ecole
-- -------------------------------------------

INSERT IGNORE INTO event_types (slug, description, source_app_id) VALUES
('contrat.declined',          'Contrat refuse par le signataire',                        (SELECT id FROM apps WHERE slug = 'ecole')),
('reclamation.created',       'Reclamation qualite identifiee (indicateur 31 Qualiopi)', (SELECT id FROM apps WHERE slug = 'ecole')),
('reclamation.resolved',      'Reclamation qualite resolue',                             (SELECT id FROM apps WHERE slug = 'ecole')),
('user.created',              'Nouveau compte utilisateur cree',                         (SELECT id FROM apps WHERE slug = 'ecole'))
;

-- -------------------------------------------
-- 4. Ajouter les nouveaux event types QALIOPI
-- -------------------------------------------

INSERT IGNORE INTO event_types (slug, description, source_app_id) VALUES
('document.status_changed',   'Statut document change dans le suivi QALIOPI',           (SELECT id FROM apps WHERE slug = 'qaliopi')),
('paiement.retard_detected',  'Retard ou probleme de paiement detecte',                 (SELECT id FROM apps WHERE slug = 'qaliopi')),
('formation.completion',      'Avancement Teachizy atteint 100%',                       (SELECT id FROM apps WHERE slug = 'qaliopi')),
('inscription.archived',      'Commande archivee dans le dashboard QALIOPI',            (SELECT id FROM apps WHERE slug = 'qaliopi'))
;

-- Ajouter ticket.status_changed pour Tableau Joyeux
INSERT IGNORE INTO event_types (slug, description, source_app_id) VALUES
('ticket.status_changed',     'Ticket interne change de statut',                        (SELECT id FROM apps WHERE slug = 'tableau'))
;

-- -------------------------------------------
-- 5. Ajouter les subscriptions manquantes
-- -------------------------------------------

-- QALIOPI s'abonne aux nouveaux evenements Ecole
INSERT IGNORE INTO subscriptions (app_id, event_type_id, callback_url, secret) VALUES
((SELECT id FROM apps WHERE slug = 'qaliopi'), (SELECT id FROM event_types WHERE slug = 'contrat.declined'),     'https://gestion.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_qaliopi_64chars_0000000000000000'),
((SELECT id FROM apps WHERE slug = 'qaliopi'), (SELECT id FROM event_types WHERE slug = 'reclamation.created'),  'https://gestion.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_qaliopi_64chars_0000000000000000'),
((SELECT id FROM apps WHERE slug = 'qaliopi'), (SELECT id FROM event_types WHERE slug = 'reclamation.resolved'), 'https://gestion.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_qaliopi_64chars_0000000000000000'),
((SELECT id FROM apps WHERE slug = 'qaliopi'), (SELECT id FROM event_types WHERE slug = 'user.created'),         'https://gestion.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_qaliopi_64chars_0000000000000000')
;

-- Ecole s'abonne aux nouveaux evenements QALIOPI (remplace les anciens)
INSERT IGNORE INTO subscriptions (app_id, event_type_id, callback_url, secret) VALUES
((SELECT id FROM apps WHERE slug = 'ecole'), (SELECT id FROM event_types WHERE slug = 'document.status_changed'),  'https://ecole.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_ecole_64chars_00000000000000000'),
((SELECT id FROM apps WHERE slug = 'ecole'), (SELECT id FROM event_types WHERE slug = 'paiement.retard_detected'), 'https://ecole.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_ecole_64chars_00000000000000000'),
((SELECT id FROM apps WHERE slug = 'ecole'), (SELECT id FROM event_types WHERE slug = 'formation.completion'),     'https://ecole.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_ecole_64chars_00000000000000000'),
((SELECT id FROM apps WHERE slug = 'ecole'), (SELECT id FROM event_types WHERE slug = 'inscription.archived'),     'https://ecole.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_ecole_64chars_00000000000000000')
;

-- Tableau Joyeux s'abonne aussi aux paiements echoues (creation ticket auto)
INSERT IGNORE INTO subscriptions (app_id, event_type_id, callback_url, secret) VALUES
((SELECT id FROM apps WHERE slug = 'tableau'), (SELECT id FROM event_types WHERE slug = 'paiement.failed'),        'https://manager.altheaprovence.com/api/webhook/central', 'CHANGE_ME_subscription_secret_tableau_64chars_000000000000000')
;

-- -------------------------------------------
-- 6. Mettre a jour les callback_url QALIOPI (ancien domaine)
-- -------------------------------------------
UPDATE subscriptions SET callback_url = 'https://gestion.altheaprovence.com/api/webhook/central'
WHERE app_id = (SELECT id FROM apps WHERE slug = 'qaliopi')
AND callback_url LIKE '%gestionqaliopi%';