pinakes/migrations/sqlite/V16__sync_system.sql

-- V16: Cross-Device Sync System
-- Adds device registration, change tracking, and chunked upload support

-- Sync devices table
CREATE TABLE sync_devices (
    id TEXT PRIMARY KEY NOT NULL,
    user_id TEXT NOT NULL,
    name TEXT NOT NULL,
    device_type TEXT NOT NULL,
    client_version TEXT NOT NULL,
    os_info TEXT,
    device_token_hash TEXT NOT NULL UNIQUE,
    last_sync_at TEXT,
    last_seen_at TEXT NOT NULL,
    sync_cursor INTEGER DEFAULT 0,
    enabled INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_sync_devices_user ON sync_devices(user_id);
CREATE INDEX idx_sync_devices_token ON sync_devices(device_token_hash);

-- Sync log table - tracks all changes for sync
CREATE TABLE sync_log (
    id TEXT PRIMARY KEY NOT NULL,
    sequence INTEGER NOT NULL UNIQUE,
    change_type TEXT NOT NULL,
    media_id TEXT,
    path TEXT NOT NULL,
    content_hash TEXT,
    file_size INTEGER,
    metadata_json TEXT,
    changed_by_device TEXT,
    timestamp TEXT NOT NULL,
    FOREIGN KEY (media_id) REFERENCES media_items(id) ON DELETE SET NULL,
    FOREIGN KEY (changed_by_device) REFERENCES sync_devices(id) ON DELETE SET NULL
);

CREATE INDEX idx_sync_log_sequence ON sync_log(sequence);
CREATE INDEX idx_sync_log_path ON sync_log(path);
CREATE INDEX idx_sync_log_timestamp ON sync_log(timestamp);

-- Sequence counter for sync log
CREATE TABLE sync_sequence (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    current_value INTEGER NOT NULL DEFAULT 0
);
INSERT INTO sync_sequence (id, current_value) VALUES (1, 0);

-- Device sync state - tracks sync status per device per file
CREATE TABLE device_sync_state (
    device_id TEXT NOT NULL,
    path TEXT NOT NULL,
    local_hash TEXT,
    server_hash TEXT,
    local_mtime INTEGER,
    server_mtime INTEGER,
    sync_status TEXT NOT NULL,
    last_synced_at TEXT,
    conflict_info_json TEXT,
    PRIMARY KEY (device_id, path),
    FOREIGN KEY (device_id) REFERENCES sync_devices(id) ON DELETE CASCADE
);

CREATE INDEX idx_device_sync_status ON device_sync_state(device_id, sync_status);

-- Upload sessions for chunked uploads
CREATE TABLE upload_sessions (
    id TEXT PRIMARY KEY NOT NULL,
    device_id TEXT NOT NULL,
    target_path TEXT NOT NULL,
    expected_hash TEXT NOT NULL,
    expected_size INTEGER NOT NULL,
    chunk_size INTEGER NOT NULL,
    chunk_count INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    last_activity TEXT NOT NULL,
    FOREIGN KEY (device_id) REFERENCES sync_devices(id) ON DELETE CASCADE
);

CREATE INDEX idx_upload_sessions_device ON upload_sessions(device_id);
CREATE INDEX idx_upload_sessions_status ON upload_sessions(status);
CREATE INDEX idx_upload_sessions_expires ON upload_sessions(expires_at);

-- Upload chunks - tracks received chunks
CREATE TABLE upload_chunks (
    upload_id TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    offset INTEGER NOT NULL,
    size INTEGER NOT NULL,
    hash TEXT NOT NULL,
    received_at TEXT NOT NULL,
    PRIMARY KEY (upload_id, chunk_index),
    FOREIGN KEY (upload_id) REFERENCES upload_sessions(id) ON DELETE CASCADE
);

-- Sync conflicts
CREATE TABLE sync_conflicts (
    id TEXT PRIMARY KEY NOT NULL,
    device_id TEXT NOT NULL,
    path TEXT NOT NULL,
    local_hash TEXT NOT NULL,
    local_mtime INTEGER NOT NULL,
    server_hash TEXT NOT NULL,
    server_mtime INTEGER NOT NULL,
    detected_at TEXT NOT NULL,
    resolved_at TEXT,
    resolution TEXT,
    FOREIGN KEY (device_id) REFERENCES sync_devices(id) ON DELETE CASCADE
);

CREATE INDEX idx_sync_conflicts_device ON sync_conflicts(device_id);
CREATE INDEX idx_sync_conflicts_unresolved ON sync_conflicts(device_id, resolved_at) WHERE resolved_at IS NULL;