-- ---------------------------------------------------------------------
-- Support ticket conversation thread (admin <-> customer replies + internal notes)
-- Run this once against the live database to enable the Support Tickets admin module.
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS support_ticket_replies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT UNSIGNED NOT NULL,
    author_type ENUM('admin', 'customer') NOT NULL,
    author_admin_id INT UNSIGNED NULL, -- set when author_type = admin
    message TEXT NOT NULL,
    is_internal_note TINYINT(1) NOT NULL DEFAULT 0, -- internal-only note, not visible to customer
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (author_admin_id) REFERENCES admin_users(id),
    INDEX idx_ticket_replies (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;