-- =========================================================
-- 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);

-- ---------------------------------------------------------
-- Utilisateurs de demo
-- Mot de passe commun: ChangeMe123!
-- Hash bcrypt genere a titre d'exemple
-- ---------------------------------------------------------
INSERT INTO users (mairie_id, nom, prenom, email, telephone, password_hash, role, actif)
SELECT m.id, 'Martin', 'Alice', 'admin@mairie-demo.fr', '0102030405',
       '$2y$10$7fzwQmq6hScq9v7kYQkBge20sq9Xxxy5JQfJ3ZW6gI6JBiQY2ejl2',
       'Administrateur', 1
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.email = 'admin@mairie-demo.fr');

INSERT INTO users (mairie_id, nom, prenom, email, telephone, password_hash, role, actif)
SELECT m.id, 'Petit', 'Luc', 'moderateur@mairie-demo.fr', '0102030406',
       '$2y$10$7fzwQmq6hScq9v7kYQkBge20sq9Xxxy5JQfJ3ZW6gI6JBiQY2ejl2',
       'Moderateur', 1
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.email = 'moderateur@mairie-demo.fr');

INSERT INTO users (mairie_id, nom, prenom, email, telephone, password_hash, role, actif)
SELECT m.id, 'Durand', 'Nora', 'intervenant1@mairie-demo.fr', '0102030407',
       '$2y$10$7fzwQmq6hScq9v7kYQkBge20sq9Xxxy5JQfJ3ZW6gI6JBiQY2ejl2',
       'Utilisateur', 1
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.email = 'intervenant1@mairie-demo.fr');

INSERT INTO users (mairie_id, nom, prenom, email, telephone, password_hash, role, actif)
SELECT m.id, 'Bernard', 'Leo', 'intervenant2@mairie-demo.fr', '0102030408',
       '$2y$10$7fzwQmq6hScq9v7kYQkBge20sq9Xxxy5JQfJ3ZW6gI6JBiQY2ejl2',
       'Utilisateur', 1
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.email = 'intervenant2@mairie-demo.fr');

INSERT INTO users (mairie_id, nom, prenom, email, telephone, password_hash, role, actif)
SELECT m.id, 'Reader', 'Ina', 'lecteur@mairie-demo.fr', '0102030409',
       '$2y$10$7fzwQmq6hScq9v7kYQkBge20sq9Xxxy5JQfJ3ZW6gI6JBiQY2ejl2',
       'Lecteur', 1
FROM mairies m
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.email = 'lecteur@mairie-demo.fr');

-- ---------------------------------------------------------
-- 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'
  );

-- ---------------------------------------------------------
-- Tickets de demo
-- ---------------------------------------------------------
INSERT INTO tickets (
  mairie_id, reference, demandeur_id, domaine_id, createur_id, statut,
  titre, description, lieu_adresse, latitude, longitude, date_prevue
)
SELECT
  m.id,
  'TCK-DEMO-0001',
  d.id,
  dom.id,
  u.id,
  'en_cours',
  'Nid de poule dangereux',
  'Signalement d''un nid de poule important devant l''ecole primaire.',
  'Rue des Ecoles, Demo',
  48.8566100,
  2.3522200,
  CURDATE() + INTERVAL 2 DAY
FROM mairies m
JOIN demandeurs d ON d.mairie_id = m.id AND d.nom = 'Moreau' AND d.prenom = 'Jean'
JOIN domaines dom ON dom.mairie_id = m.id AND dom.nom = 'Voirie'
JOIN users u ON u.mairie_id = m.id AND u.email = 'moderateur@mairie-demo.fr'
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM tickets t WHERE t.reference = 'TCK-DEMO-0001');

INSERT INTO tickets (
  mairie_id, reference, demandeur_id, domaine_id, createur_id, statut,
  titre, description, lieu_adresse, latitude, longitude, date_prevue, date_cloture
)
SELECT
  m.id,
  'TCK-DEMO-0002',
  d.id,
  dom.id,
  u.id,
  'fait',
  'Panne de videoprojecteur',
  'Le videoprojecteur de la salle du conseil ne demarre plus.',
  'Hotel de ville, salle du conseil, Demo',
  48.8572000,
  2.3515000,
  CURDATE() - INTERVAL 1 DAY,
  NOW() - INTERVAL 2 HOUR
FROM mairies m
JOIN demandeurs d ON d.mairie_id = m.id AND d.nom = 'Dubois' AND d.prenom = 'Emma'
JOIN domaines dom ON dom.mairie_id = m.id AND dom.nom = 'Informatique'
JOIN users u ON u.mairie_id = m.id AND u.email = 'admin@mairie-demo.fr'
WHERE m.code = 'MAIRIE_DEMO'
  AND NOT EXISTS (SELECT 1 FROM tickets t WHERE t.reference = 'TCK-DEMO-0002');

-- ---------------------------------------------------------
-- Assignations de demo
-- ---------------------------------------------------------
INSERT INTO ticket_assignations (ticket_id, user_id, assigne_par, assigne_le)
SELECT t.id, u1.id, mdr.id, NOW() - INTERVAL 1 DAY
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users u1 ON u1.mairie_id = m.id AND u1.email = 'intervenant1@mairie-demo.fr'
JOIN users mdr ON mdr.mairie_id = m.id AND mdr.email = 'moderateur@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0001'
  AND NOT EXISTS (
    SELECT 1 FROM ticket_assignations ta WHERE ta.ticket_id = t.id AND ta.user_id = u1.id
  );

INSERT INTO ticket_assignations (ticket_id, user_id, assigne_par, assigne_le)
SELECT t.id, u2.id, mdr.id, NOW() - INTERVAL 1 DAY
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users u2 ON u2.mairie_id = m.id AND u2.email = 'intervenant2@mairie-demo.fr'
JOIN users mdr ON mdr.mairie_id = m.id AND mdr.email = 'moderateur@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0001'
  AND NOT EXISTS (
    SELECT 1 FROM ticket_assignations ta WHERE ta.ticket_id = t.id AND ta.user_id = u2.id
  );

-- ---------------------------------------------------------
-- Commentaires de demo
-- ---------------------------------------------------------
INSERT INTO ticket_commentaires (ticket_id, user_id, commentaire, visible_lecteur, created_at)
SELECT t.id, mdr.id, 'Demande enregistree et transmise aux equipes.', 1, NOW() - INTERVAL 1 DAY
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users mdr ON mdr.mairie_id = m.id AND mdr.email = 'moderateur@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0001'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_commentaires tc
    WHERE tc.ticket_id = t.id AND tc.commentaire = 'Demande enregistree et transmise aux equipes.'
  );

INSERT INTO ticket_commentaires (ticket_id, user_id, commentaire, visible_lecteur, created_at)
SELECT t.id, i1.id, 'Prise en charge effectuee. Intervention planifiee sous 48h.', 1, NOW() - INTERVAL 12 HOUR
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users i1 ON i1.mairie_id = m.id AND i1.email = 'intervenant1@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0001'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_commentaires tc
    WHERE tc.ticket_id = t.id AND tc.commentaire = 'Prise en charge effectuee. Intervention planifiee sous 48h.'
  );

-- ---------------------------------------------------------
-- Historique des statuts de demo
-- ---------------------------------------------------------
INSERT INTO ticket_statuts_historique (ticket_id, ancien_statut, nouveau_statut, change_par, change_le, commentaire)
SELECT t.id, NULL, 'saisi', mdr.id, t.created_at, 'Creation du ticket'
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users mdr ON mdr.mairie_id = m.id AND mdr.email = 'moderateur@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0001'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_statuts_historique h
    WHERE h.ticket_id = t.id AND h.nouveau_statut = 'saisi'
  );

INSERT INTO ticket_statuts_historique (ticket_id, ancien_statut, nouveau_statut, change_par, change_le, commentaire)
SELECT t.id, 'saisi', 'en_cours', i1.id, NOW() - INTERVAL 12 HOUR, 'Intervenant en charge'
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users i1 ON i1.mairie_id = m.id AND i1.email = 'intervenant1@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0001'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_statuts_historique h
    WHERE h.ticket_id = t.id AND h.nouveau_statut = 'en_cours'
  );

INSERT INTO ticket_statuts_historique (ticket_id, ancien_statut, nouveau_statut, change_par, change_le, commentaire)
SELECT t.id, NULL, 'saisi', adm.id, t.created_at, 'Creation du ticket'
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users adm ON adm.mairie_id = m.id AND adm.email = 'admin@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0002'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_statuts_historique h
    WHERE h.ticket_id = t.id AND h.nouveau_statut = 'saisi'
  );

INSERT INTO ticket_statuts_historique (ticket_id, ancien_statut, nouveau_statut, change_par, change_le, commentaire)
SELECT t.id, 'saisi', 'en_cours', i2.id, NOW() - INTERVAL 1 DAY, 'Intervention debutee'
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users i2 ON i2.mairie_id = m.id AND i2.email = 'intervenant2@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0002'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_statuts_historique h
    WHERE h.ticket_id = t.id AND h.nouveau_statut = 'en_cours'
  );

INSERT INTO ticket_statuts_historique (ticket_id, ancien_statut, nouveau_statut, change_par, change_le, commentaire)
SELECT t.id, 'en_cours', 'fait', i2.id, NOW() - INTERVAL 2 HOUR, 'Intervention terminee'
FROM tickets t
JOIN mairies m ON m.id = t.mairie_id AND m.code = 'MAIRIE_DEMO'
JOIN users i2 ON i2.mairie_id = m.id AND i2.email = 'intervenant2@mairie-demo.fr'
WHERE t.reference = 'TCK-DEMO-0002'
  AND NOT EXISTS (
    SELECT 1
    FROM ticket_statuts_historique h
    WHERE h.ticket_id = t.id AND h.nouveau_statut = 'fait'
  );

-- ---------------------------------------------------------
-- 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;
