-- Ticket workflow, assignees, comments. Run as MySQL root on existing DBs.
-- mysql -u root -p androidcast_crashes < sql/migrations/004_ticket_workflow.sql

ALTER TABLE tickets
    ADD COLUMN workflow_state VARCHAR(32) NOT NULL DEFAULT 'triage' AFTER tags_json,
    ADD COLUMN assignees_json LONGTEXT NOT NULL DEFAULT '[]' AFTER workflow_state;

CREATE TABLE IF NOT EXISTS ticket_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id BIGINT UNSIGNED NOT NULL,
    author_username VARCHAR(64) NOT NULL,
    body LONGTEXT NOT NULL,
    created_at_ms BIGINT NOT NULL,
    updated_at_ms BIGINT NOT NULL,
    KEY idx_ticket_comments_ticket (ticket_id),
    KEY idx_ticket_comments_created (created_at_ms),
    CONSTRAINT fk_ticket_comments_ticket FOREIGN KEY (ticket_id) REFERENCES tickets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
