CREATE TABLE IF NOT EXISTS email_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  message_id TEXT NOT NULL UNIQUE,
  from_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  body TEXT NOT NULL,
  customer_external_id TEXT NULL,
  interaction_id TEXT NULL,
  queue_id TEXT NULL,
  priority INTEGER NOT NULL DEFAULT 3,
  payload_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS ix_email_messages_message_id ON email_messages (message_id);
CREATE INDEX IF NOT EXISTS ix_email_messages_from_email ON email_messages (from_email);
CREATE INDEX IF NOT EXISTS ix_email_messages_subject ON email_messages (subject);
CREATE INDEX IF NOT EXISTS ix_email_messages_customer_external_id ON email_messages (customer_external_id);
CREATE INDEX IF NOT EXISTS ix_email_messages_interaction_id ON email_messages (interaction_id);
CREATE INDEX IF NOT EXISTS ix_email_messages_queue_id ON email_messages (queue_id);
CREATE INDEX IF NOT EXISTS ix_email_messages_priority ON email_messages (priority);
CREATE INDEX IF NOT EXISTS ix_email_messages_created_at ON email_messages (created_at);