-- 004_tickets.sql
CREATE TYPE ticket_status   AS ENUM ('open', 'closed');
CREATE TYPE ticket_priority AS ENUM ('low', 'medium', 'high');

CREATE TABLE tickets (
  id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id  UUID            NOT NULL REFERENCES contacts(id),
  channel_id  UUID            NOT NULL REFERENCES channels(id),
  assigned_to UUID            REFERENCES users(id),
  status      ticket_status   NOT NULL DEFAULT 'open',
  priority    ticket_priority NOT NULL DEFAULT 'medium',
  due_date    TIMESTAMPTZ,
  created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
  closed_at   TIMESTAMPTZ
);

-- Used on every inbound message to find the open ticket for a contact+channel
CREATE INDEX idx_tickets_lookup ON tickets (contact_id, channel_id, status);

-- Prevents duplicate open tickets for the same contact+channel under concurrent load
CREATE UNIQUE INDEX idx_tickets_one_open
  ON tickets (contact_id, channel_id)
  WHERE status = 'open';
