CREATE TABLE chat_attachments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  message_id uuid REFERENCES chat_messages(id) ON DELETE CASCADE,
  original_name text NOT NULL,
  content_type text NOT NULL,
  byte_size bigint NOT NULL CHECK (byte_size > 0),
  object_key text NOT NULL UNIQUE,
  state text NOT NULL CHECK (state IN ('pending', 'attached')),
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX chat_attachments_user_state_idx
  ON chat_attachments (user_id, state, expires_at);
CREATE INDEX chat_attachments_message_id_idx
  ON chat_attachments (message_id);