USE promptx;

-- For existing v4 database.
-- Run only if these columns/tables do not already exist.

ALTER TABLE prompts
  MODIFY COLUMN status ENUM('published','draft','disabled') NOT NULL DEFAULT 'published';

ALTER TABLE comments
  MODIFY COLUMN status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending';

ALTER TABLE support_tickets
  MODIFY COLUMN status ENUM('open','waiting_admin','waiting_user','closed') NOT NULL DEFAULT 'open';

CREATE TABLE IF NOT EXISTS ticket_messages(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ticket_id BIGINT UNSIGNED NOT NULL,
 sender_type ENUM('user','admin') NOT NULL,
 sender_id BIGINT UNSIGNED NOT NULL,
 message TEXT NULL,
 attachment_path VARCHAR(1000) NULL,
 attachment_type VARCHAR(20) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
 INDEX(ticket_id,created_at)
) ENGINE=InnoDB;