-- ==============================================================================
-- UCall Database Schema (MySQL / MariaDB Compatible)
-- Migration 0005: Durable Per-Recipient Monotonic Event Log & Race-Free Cursor Sync
-- ==============================================================================

-- 1. Per-Recipient Mailbox Counter (Transactionally locked with SELECT ... FOR UPDATE)
CREATE TABLE IF NOT EXISTS user_mailboxes (
    user_id VARCHAR(36) PRIMARY KEY,
    last_seq BIGINT NOT NULL DEFAULT 0,
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    CONSTRAINT fk_mailbox_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Add per-recipient sequential order to messages table
ALTER TABLE messages ADD COLUMN IF NOT EXISTS recipient_seq BIGINT DEFAULT NULL;
CREATE INDEX IF NOT EXISTS idx_messages_recipient_seq ON messages (recipient_user_id, recipient_seq);