-- =========================================================
-- Ticketing multi-mairie - Donnees de demonstration
-- =========================================================


USE daproaoto;

START TRANSACTION;

-- ---------------------------------------------------------
-- Mairies de demo
-- ---------------------------------------------------------
INSERT INTO mairies (nom, code, adresse, email_contact, actif)
VALUES
  ('Mairie Demo', 'MAIRIE_DEMO', '1 place de la Republique, 00000 Demo', 'contact@mairie-demo.fr', 1),
  ('Mairie Rivière', 'MAIRIE_RIVIERE', '10 rue des Ecoles, 11111 Riviere', 'contact@mairie-riviere.fr', 1)
ON DUPLICATE KEY UPDATE
  nom = VALUES(nom),
  adresse = VALUES(adresse),
  email_contact = VALUES(email_contact),
  actif = VALUES(actif);

-- ---------------------------------------------------------
-- Domaines de demo (mairie + global)
-- ---------------------------------------------------------
INSERT INTO domaines (mairie_id, nom, description, actif, created_by)
SELECT m.id, 'Voirie', 'Routes, trottoirs, signalisation', 1, NULL
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1 FROM domaines d WHERE d.mairie_id = m.id AND d.nom = 'Voirie'
  );

INSERT INTO domaines (mairie_id, nom, description, actif, created_by)
SELECT m.id, 'Batiment', 'Batiments communaux et equipements', 1, NULL
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1 FROM domaines d WHERE d.mairie_id = m.id AND d.nom = 'Batiment'
  );

INSERT INTO domaines (mairie_id, nom, description, actif, created_by)
SELECT m.id, 'Informatique', 'Materiel, logiciels, reseau', 1, NULL
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1 FROM domaines d WHERE d.mairie_id = m.id AND d.nom = 'Informatique'
  );

INSERT INTO domaines (mairie_id, nom, description, actif, created_by)
SELECT NULL, 'Urgence', 'Domaine global - traitement prioritaire', 1, NULL
WHERE NOT EXISTS (
  SELECT 1 FROM domaines d WHERE d.mairie_id IS NULL AND d.nom = 'Urgence'
);

-- ---------------------------------------------------------
-- Demandeurs de demo
-- ---------------------------------------------------------
INSERT INTO demandeurs (mairie_id, nom, prenom, telephone, email, adresse)
SELECT m.id, 'Moreau', 'Jean', '0601020304', 'jean.moreau@example.fr', '2 rue des Lilas, Demo'
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1
    FROM demandeurs d
    WHERE d.mairie_id = m.id AND d.nom = 'Moreau' AND d.prenom = 'Jean'
  );

INSERT INTO demandeurs (mairie_id, nom, prenom, telephone, email, adresse)
SELECT m.id, 'Dubois', 'Emma', '0605060708', 'emma.dubois@example.fr', '18 avenue de la Gare, Demo'
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1
    FROM demandeurs d
    WHERE d.mairie_id = m.id AND d.nom = 'Dubois' AND d.prenom = 'Emma'
  );

-- ---------------------------------------------------------
-- Templates email
-- ---------------------------------------------------------
INSERT INTO email_templates (mairie_id, code, sujet, contenu, actif, updated_by)
SELECT m.id, 'assignation_ticket',
       'Nouvelle intervention assignee - {{reference}}',
       'Bonjour {{intervenant_prenom}},\n\nUne nouvelle demande vous a ete assignee.\nReference: {{reference}}\nTitre: {{titre}}\nLieu: {{lieu}}\n\nMerci.',
       1, NULL
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1 FROM email_templates et WHERE et.mairie_id = m.id AND et.code = 'assignation_ticket'
  );

INSERT INTO email_templates (mairie_id, code, sujet, contenu, actif, updated_by)
SELECT m.id, 'ticket_cloture',
       'Ticket cloture - {{reference}}',
       'Bonjour,\n\nLe ticket {{reference}} est maintenant cloture.\n\nCordialement,\nService technique',
       1, NULL
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1 FROM email_templates et WHERE et.mairie_id = m.id AND et.code = 'ticket_cloture'
  );

INSERT INTO email_templates (mairie_id, code, sujet, contenu, actif, updated_by)
SELECT m.id, 'information_demandeur',
       'Votre demande a ete traitee - {{reference}}',
       'Bonjour {{demandeur_prenom}},\n\nVotre demande {{reference}} a ete traitee.\nMerci de votre signalement.\n\nCordialement,\nVotre mairie',
       1, NULL
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1 FROM email_templates et WHERE et.mairie_id = m.id AND et.code = 'information_demandeur'
  );

-- ---------------------------------------------------------
-- Journal email de demo
-- ---------------------------------------------------------
INSERT INTO emails_log (
  mairie_id, ticket_id, type_email, destinataire, sujet, contenu, statut, envoye_le, created_at
)
SELECT
  m.id, t.id, 'assignation', 'intervenant1@mairie-demo.fr',
  'Nouvelle intervention assignee - TCK-DEMO-0001',
  'Notification d''assignation pour le ticket TCK-DEMO-0001',
  'envoye', NOW() - INTERVAL 1 DAY, NOW() - INTERVAL 1 DAY
FROM mairies m
JOIN tickets t ON t.mairie_id = m.id AND t.reference = 'TCK-DEMO-0001'
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (
    SELECT 1
    FROM emails_log el
    WHERE el.mairie_id = m.id
      AND el.ticket_id = t.id
      AND el.type_email = 'assignation'
      AND el.destinataire = 'intervenant1@mairie-demo.fr'
  );

COMMIT;
