-- ==============================================================================
-- UCall Database Schema (MySQL / MariaDB Compatible)
-- Migration 0003: Auth Sessions, Token Rotation, Call Events & Idempotency
-- ==============================================================================

-- 1. Refresh Token Sessions with Reuse Detection
CREATE TABLE IF NOT EXISTS auth_sessions (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    device_id VARCHAR(36),
    token_hash VARCHAR(64) NOT NULL,
    token_family_id VARCHAR(36) NOT NULL,
    is_revoked BOOLEAN NOT NULL DEFAULT FALSE,
    expires_at DATETIME(6) NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_auth_sessions_hash (token_hash),
    INDEX idx_auth_sessions_user (user_id, is_revoked),
    INDEX idx_auth_sessions_family (token_family_id),
    CONSTRAINT fk_auth_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Idempotency Keys Registry
CREATE TABLE IF NOT EXISTS idempotency_keys (
    key_id VARCHAR(128) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    action VARCHAR(32) NOT NULL,
    resource_id VARCHAR(36) NOT NULL,
    response_payload JSON,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (user_id, key_id, action),
    INDEX idx_idempotency_created (created_at),
    CONSTRAINT fk_idempotency_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Detailed Call Lifecycle Events Ledger
CREATE TABLE IF NOT EXISTS call_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    call_id VARCHAR(36) NOT NULL,
    event_type VARCHAR(32) NOT NULL,
    endpoint_id VARCHAR(36),
    payload JSON,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_call_events_call (call_id, created_at),
    CONSTRAINT fk_call_events_call FOREIGN KEY (call_id) REFERENCES call_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;