CREATE TABLE "messages" (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conversation_id INTEGER NOT NULL,
message_id TEXT NOT NULL,
sender TEXT NOT NULL,
message_type TEXT NOT NULL DEFAULT 'text',
content TEXT,
media_url TEXT,
media_mime_type TEXT,
chatbot_id INTEGER NOT NULL,
created_at TEXT NOT NULL,
status TEXT DEFAULT 'sent',
delivered_at TEXT,
read_at TEXT,
sender_user_id TEXT,
metadata TEXT,
UNIQUE(chatbot_id, message_id),
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
FOREIGN KEY (chatbot_id) REFERENCES chatbots(id) ON DELETE CASCADE
)