CREATE TABLE chats (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL DEFAULT 'New chat',
  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'))
);

INSERT INTO chats(title, created_at, updated_at)
SELECT
  COALESCE(
    NULLIF(substr((SELECT content FROM chat_messages WHERE role = 'user' ORDER BY id LIMIT 1), 1, 60), ''),
    'Conversation'
  ),
  MIN(created_at),
  MAX(created_at)
FROM chat_messages
HAVING COUNT(*) > 0;

ALTER TABLE chat_messages
ADD COLUMN chat_id INTEGER REFERENCES chats(id);

UPDATE chat_messages
SET chat_id = (SELECT id FROM chats ORDER BY id LIMIT 1)
WHERE chat_id IS NULL;

CREATE INDEX chat_messages_chat_id ON chat_messages(chat_id, id);
CREATE INDEX chats_updated_at ON chats(updated_at DESC, id DESC);