-- Migration: Sync state tables for real-time synchronization
-- Version: 20260118000003
-- Description: Adds tables for WebSocket session management, sync state, and offline queue

-- Device sessions table for WebSocket connections
CREATE TABLE device_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    device_id TEXT NOT NULL,
    device_name TEXT NOT NULL,
    platform TEXT NOT NULL CHECK (platform IN ('cli', 'web', 'ide', 'mobile')),
    user_agent TEXT,
    ip_address INET,
    connected BOOLEAN NOT NULL DEFAULT false,
    last_connected_at TIMESTAMPTZ,
    last_disconnected_at TIMESTAMPTZ,
    settings_version BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, device_id)
);

-- Index for finding active sessions by user
CREATE INDEX idx_device_sessions_user_id ON device_sessions(user_id);
CREATE INDEX idx_device_sessions_connected ON device_sessions(user_id, connected) WHERE connected = true;

-- Sync state table with version vectors for conflict detection
CREATE TABLE sync_state (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    resource_type TEXT NOT NULL CHECK (resource_type IN ('settings', 'api_keys', 'profile')),
    resource_key TEXT NOT NULL,
    version BIGINT NOT NULL DEFAULT 1,
    -- Version vector: JSON object mapping device_id -> version
    version_vector JSONB NOT NULL DEFAULT '{}',
    value JSONB NOT NULL,
    checksum TEXT NOT NULL,
    last_modified_by TEXT NOT NULL,  -- device_id
    last_modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, resource_type, resource_key)
);

-- Index for querying sync state by user and type
CREATE INDEX idx_sync_state_user_type ON sync_state(user_id, resource_type);
CREATE INDEX idx_sync_state_version ON sync_state(user_id, version);

-- Offline queue for storing changes when devices are disconnected
CREATE TABLE sync_queue (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    target_device_id TEXT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB NOT NULL,
    retry_count INT NOT NULL DEFAULT 0,
    max_retries INT NOT NULL DEFAULT 3,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '7 days'),
    delivered_at TIMESTAMPTZ,
    failed_at TIMESTAMPTZ,
    error_message TEXT
);

-- Index for finding pending queue items by device
CREATE INDEX idx_sync_queue_device ON sync_queue(target_device_id, delivered_at) WHERE delivered_at IS NULL;
CREATE INDEX idx_sync_queue_expires ON sync_queue(expires_at) WHERE delivered_at IS NULL;

-- Sync conflicts table for manual resolution
CREATE TABLE sync_conflicts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    resource_type TEXT NOT NULL,
    resource_key TEXT NOT NULL,
    local_device_id TEXT NOT NULL,
    remote_device_id TEXT NOT NULL,
    local_value JSONB NOT NULL,
    remote_value JSONB NOT NULL,
    local_version BIGINT NOT NULL,
    remote_version BIGINT NOT NULL,
    local_timestamp TIMESTAMPTZ NOT NULL,
    remote_timestamp TIMESTAMPTZ NOT NULL,
    resolution_strategy TEXT CHECK (resolution_strategy IN ('use_local', 'use_remote', 'use_newest', 'merge', 'manual')),
    resolved_value JSONB,
    resolved_at TIMESTAMPTZ,
    resolved_by TEXT,  -- device_id or 'system'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for finding unresolved conflicts
CREATE INDEX idx_sync_conflicts_user ON sync_conflicts(user_id) WHERE resolved_at IS NULL;

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_device_session_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for device_sessions updated_at
CREATE TRIGGER trigger_device_sessions_updated_at
    BEFORE UPDATE ON device_sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_device_session_timestamp();

-- Comments
COMMENT ON TABLE device_sessions IS 'WebSocket device sessions for real-time sync';
COMMENT ON TABLE sync_state IS 'Versioned sync state with vector clocks for conflict detection';
COMMENT ON TABLE sync_queue IS 'Offline message queue for disconnected devices';
COMMENT ON TABLE sync_conflicts IS 'Detected conflicts requiring resolution';
COMMENT ON COLUMN sync_state.version_vector IS 'JSON object mapping device_id to version for vector clock';
COMMENT ON COLUMN sync_state.checksum IS 'SHA256 hash of value for integrity verification';
