-- =========================================================
-- Ticketing multi-mairie - Schema MySQL 8
-- =========================================================


CREATE DATABASE IF NOT EXISTS daproaoto
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE daproaoto;

-- ---------------------------------------------------------
-- Mairies
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS mairies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(150) NOT NULL,
  code VARCHAR(50) NOT NULL,
  adresse VARCHAR(255) NULL,
  email_contact VARCHAR(190) NULL,
  actif TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_mairies_code (code),
  UNIQUE KEY uk_mairies_nom (nom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Domaines d'intervention
-- mairie_id NULL = domaine global (créé par GrandAdministrateur)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS domaines (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mairie_id BIGINT UNSIGNED NULL,
  nom VARCHAR(120) NOT NULL,
  description VARCHAR(255) NULL,
  actif TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_domaines_mairie
    FOREIGN KEY (mairie_id) REFERENCES mairies(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_domaines_created_by
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  UNIQUE KEY uk_domaines_mairie_nom (mairie_id, nom),
  KEY idx_domaines_actif (actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Demandeurs (citoyen, agent, etc.)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS demandeurs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mairie_id BIGINT UNSIGNED NOT NULL,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100) NOT NULL,
  telephone VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  adresse VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_demandeurs_mairie
    FOREIGN KEY (mairie_id) REFERENCES mairies(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  KEY idx_demandeurs_mairie_nom (mairie_id, nom, prenom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Tickets
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS tickets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mairie_id BIGINT UNSIGNED NOT NULL,
  reference VARCHAR(40) NOT NULL,
  demandeur_id BIGINT UNSIGNED NOT NULL,
  domaine_id BIGINT UNSIGNED NOT NULL,
  createur_id BIGINT UNSIGNED NOT NULL,
  statut ENUM('saisi', 'en_cours', 'fait', 'annule') NOT NULL DEFAULT 'saisi',
  titre VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  lieu_adresse VARCHAR(255) NOT NULL,
  latitude DECIMAL(10, 7) NULL,
  longitude DECIMAL(10, 7) NULL,
  date_prevue DATE NULL,
  date_cloture DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tickets_mairie
    FOREIGN KEY (mairie_id) REFERENCES mairies(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_tickets_demandeur
    FOREIGN KEY (demandeur_id) REFERENCES demandeurs(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_tickets_domaine
    FOREIGN KEY (domaine_id) REFERENCES domaines(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_tickets_createur
    FOREIGN KEY (createur_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE KEY uk_tickets_reference (reference),
  KEY idx_tickets_mairie_statut (mairie_id, statut),
  KEY idx_tickets_domaine (domaine_id),
  KEY idx_tickets_createur (createur_id),
  KEY idx_tickets_date_prevue (date_prevue),
  KEY idx_tickets_geo (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Assignations multi-intervenants
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS ticket_assignations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  assigne_par BIGINT UNSIGNED NOT NULL,
  assigne_le DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ticket_assignations_ticket
    FOREIGN KEY (ticket_id) REFERENCES tickets(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ticket_assignations_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_ticket_assignations_assigne_par
    FOREIGN KEY (assigne_par) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE KEY uk_ticket_user_unique (ticket_id, user_id),
  KEY idx_assignations_user (user_id),
  KEY idx_assignations_date (assigne_le)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Commentaires des tickets
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS ticket_commentaires (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  commentaire TEXT NOT NULL,
  visible_lecteur TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ticket_commentaires_ticket
    FOREIGN KEY (ticket_id) REFERENCES tickets(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ticket_commentaires_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  KEY idx_commentaires_ticket_date (ticket_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Historique des changements de statut
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS ticket_statuts_historique (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  ancien_statut ENUM('saisi', 'en_cours', 'fait', 'annule') NULL,
  nouveau_statut ENUM('saisi', 'en_cours', 'fait', 'annule') NOT NULL,
  change_par BIGINT UNSIGNED NOT NULL,
  change_le DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  commentaire VARCHAR(255) NULL,
  CONSTRAINT fk_statuts_ticket
    FOREIGN KEY (ticket_id) REFERENCES tickets(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_statuts_change_par
    FOREIGN KEY (change_par) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  KEY idx_statuts_ticket_date (ticket_id, change_le)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Modèles de mails par mairie
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mairie_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(60) NOT NULL,
  sujet VARCHAR(255) NOT NULL,
  contenu TEXT NOT NULL,
  actif TINYINT(1) NOT NULL DEFAULT 1,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_templates_mairie
    FOREIGN KEY (mairie_id) REFERENCES mairies(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_email_templates_updated_by
    FOREIGN KEY (updated_by) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  UNIQUE KEY uk_email_templates_mairie_code (mairie_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Journal des envois email
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS emails_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mairie_id BIGINT UNSIGNED NOT NULL,
  ticket_id BIGINT UNSIGNED NULL,
  type_email ENUM('assignation', 'prise_en_charge', 'cloture', 'information_demandeur', 'autre') NOT NULL,
  destinataire VARCHAR(190) NOT NULL,
  sujet VARCHAR(255) NOT NULL,
  contenu TEXT NOT NULL,
  statut ENUM('en_attente', 'envoye', 'erreur') NOT NULL DEFAULT 'en_attente',
  erreur TEXT NULL,
  envoye_le DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_emails_log_mairie
    FOREIGN KEY (mairie_id) REFERENCES mairies(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_emails_log_ticket
    FOREIGN KEY (ticket_id) REFERENCES tickets(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  KEY idx_emails_log_mairie_date (mairie_id, created_at),
  KEY idx_emails_log_statut (statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- Données minimales d'initialisation
-- IMPORTANT: remplacer le hash du mot de passe avant production
-- ---------------------------------------------------------
INSERT INTO mairies (nom, code, email_contact)
VALUES ('Mairie Demo', 'MAIRIE_DEMO', 'contact@mairie-demo.fr')
ON DUPLICATE KEY UPDATE nom = VALUES(nom);
