-- 005_messages.sql
CREATE TYPE message_direction AS ENUM ('inbound', 'outbound');

CREATE TABLE messages (
  id            UUID              PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id     UUID              NOT NULL REFERENCES tickets(id),
  direction     message_direction NOT NULL,
  content       TEXT              NOT NULL,
  wa_message_id VARCHAR(255)      UNIQUE,
  sequence      INTEGER           NOT NULL,
  created_at    TIMESTAMPTZ       NOT NULL DEFAULT NOW()
);

-- For fast message ordering within a ticket
CREATE INDEX idx_messages_ticket_seq ON messages (ticket_id, sequence);
