CREATE TABLE email_messages (
message_id TEXT PRIMARY KEY,
provider TEXT NOT NULL,
mailbox TEXT,
thread_key TEXT NOT NULL,
direction TEXT NOT NULL CHECK (direction IN ('inbound','outbound')),
from_email TEXT,
from_name TEXT,
to_emails TEXT,
cc_emails TEXT,
subject TEXT,
text_body TEXT,
html_body TEXT,
in_reply_to TEXT,
references_json TEXT,
labels_json TEXT,
source_event_id TEXT NOT NULL,
received_at TEXT NOT NULL,
answer_state TEXT NOT NULL CHECK (answer_state IN ('none','pending','drafted','suppressed','queued','sent')),
latest_draft_id TEXT,
suppression_reason TEXT,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
FOREIGN KEY (thread_key) REFERENCES email_threads(thread_key) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (source_event_id) REFERENCES events(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
)