-- A ticket can have several people on it, and a message can carry files (pictures, PDFs, models).
CREATE TABLE ticket_assignees (
  ticket_id INT UNSIGNED NOT NULL,
  user_id   INT UNSIGNED NOT NULL,
  PRIMARY KEY (ticket_id, user_id),
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO ticket_assignees (ticket_id, user_id) SELECT id, assigned_to FROM tickets WHERE assigned_to IS NOT NULL;

CREATE TABLE ticket_files (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id   INT UNSIGNED NOT NULL,
  message_id  INT UNSIGNED NOT NULL,
  stored_name VARCHAR(64) NOT NULL,
  file_name   VARCHAR(150) NOT NULL,
  size_bytes  INT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_message (message_id),
  FOREIGN KEY (ticket_id)  REFERENCES tickets(id)         ON DELETE CASCADE,
  FOREIGN KEY (message_id) REFERENCES ticket_messages(id) ON DELETE CASCADE
) ENGINE=InnoDB;
