CREATE TABLE ticket (
  id INT AUTO_INCREMENT NOT NULL,
  utilisateur_id INT NOT NULL,
  magasin_id INT NOT NULL,
  assigne_par_id INT DEFAULT NULL,
  assigne_id INT DEFAULT NULL,
  numero VARCHAR(30) NOT NULL,
  sujet VARCHAR(255) NOT NULL,
  description LONGTEXT NOT NULL,
  statut VARCHAR(30) NOT NULL,
  priorite VARCHAR(20) NOT NULL,
  categorie VARCHAR(30) NOT NULL,
  date_time_creation DATETIME NOT NULL,
  date_time_modification DATETIME NOT NULL,
  INDEX IDX_97A0ADA3A250A9DC (assigne_par_id),
  INDEX IDX_97A0ADA38E7B8AB0 (assigne_id),
  INDEX idx_ticket_magasin (magasin_id),
  INDEX idx_ticket_utilisateur (utilisateur_id),
  INDEX idx_ticket_statut (statut),
  INDEX idx_ticket_priorite (priorite),
  INDEX idx_ticket_categorie (categorie),
  INDEX idx_ticket_date_creation (date_time_creation),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE ticket_message (
  id INT AUTO_INCREMENT NOT NULL,
  ticket_id INT NOT NULL,
  utilisateur_id INT NOT NULL,
  message LONGTEXT NOT NULL,
  date_time_creation DATETIME NOT NULL,
  INDEX IDX_BA71692DFB88E14F (utilisateur_id),
  INDEX idx_ticket_message_ticket (ticket_id),
  INDEX idx_ticket_message_date (date_time_creation),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE ticket_piece_jointe (
  id INT AUTO_INCREMENT NOT NULL,
  ticket_id INT DEFAULT NULL,
  ticket_message_id INT DEFAULT NULL,
  nom VARCHAR(255) NOT NULL,
  chemin_fichier VARCHAR(500) NOT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  taille INT DEFAULT NULL,
  date_upload DATETIME NOT NULL,
  INDEX idx_ticket_piece_ticket (ticket_id),
  INDEX idx_ticket_piece_message (ticket_message_id),
  INDEX idx_ticket_piece_date (date_upload),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE ticket ADD CONSTRAINT FK_97A0ADA3FB88E14F FOREIGN KEY (utilisateur_id) REFERENCES utilisateur (id);
ALTER TABLE ticket ADD CONSTRAINT FK_97A0ADA320096AE3 FOREIGN KEY (magasin_id) REFERENCES magasin (id);
ALTER TABLE ticket ADD CONSTRAINT FK_97A0ADA3A250A9DC FOREIGN KEY (assigne_par_id) REFERENCES utilisateur (id);
ALTER TABLE ticket ADD CONSTRAINT FK_97A0ADA38E7B8AB0 FOREIGN KEY (assigne_id) REFERENCES utilisateur (id);
ALTER TABLE ticket_message ADD CONSTRAINT FK_BA71692D700047D2 FOREIGN KEY (ticket_id) REFERENCES ticket (id) ON DELETE CASCADE;
ALTER TABLE ticket_message ADD CONSTRAINT FK_BA71692DFB88E14F FOREIGN KEY (utilisateur_id) REFERENCES utilisateur (id);
ALTER TABLE ticket_piece_jointe ADD CONSTRAINT FK_6B87ED7F700047D2 FOREIGN KEY (ticket_id) REFERENCES ticket (id) ON DELETE CASCADE;
ALTER TABLE ticket_piece_jointe ADD CONSTRAINT FK_6B87ED7FC5E9817D FOREIGN KEY (ticket_message_id) REFERENCES ticket_message (id) ON DELETE CASCADE;
