-- Collapse back to one conversation per (user, persona). Where a persona has
-- several, the most recently updated one wins and the rest are dropped —
-- the v1 schema has nowhere to put them.

CREATE TABLE persona_chat_conversations_old (
    user_id INTEGER NOT NULL,
    persona_id TEXT NOT NULL,
    messages_json TEXT NOT NULL DEFAULT '[]',
    turn_count INTEGER NOT NULL DEFAULT 0,
    updated_at BIGINT NOT NULL,
    PRIMARY KEY (user_id, persona_id)
);

INSERT INTO persona_chat_conversations_old (
    user_id, persona_id, messages_json, turn_count, updated_at
)
SELECT user_id, persona_id, messages_json, turn_count, updated_at
FROM persona_chat_conversations c
WHERE c.updated_at = (
    SELECT MAX(c2.updated_at)
    FROM persona_chat_conversations c2
    WHERE c2.user_id = c.user_id AND c2.persona_id = c.persona_id
)
GROUP BY user_id, persona_id;

DROP INDEX IF EXISTS idx_persona_chat_updated;
DROP INDEX IF EXISTS idx_persona_chat_persona;
DROP TABLE persona_chat_conversations;
ALTER TABLE persona_chat_conversations_old RENAME TO persona_chat_conversations;

CREATE INDEX idx_persona_chat_updated
    ON persona_chat_conversations (user_id, updated_at DESC);