-- Chat pinned messages
CREATE TABLE IF NOT EXISTS message_pin (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  incident_id uuid NOT NULL REFERENCES incident(id) ON DELETE CASCADE,
  message_id uuid NOT NULL REFERENCES message(id) ON DELETE CASCADE,
  pinned_by_user_id bigint NOT NULL REFERENCES app_user(id),
  pinned_at timestamptz NOT NULL DEFAULT now(),
  sort_order int NOT NULL DEFAULT 100,
  UNIQUE (incident_id, message_id)
);

CREATE INDEX IF NOT EXISTS idx_message_pin_incident ON message_pin(incident_id);
CREATE INDEX IF NOT EXISTS idx_message_pin_msg ON message_pin(message_id);
