CREATE TABLE IF NOT EXISTS api_keys (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    name TEXT NOT NULL,
    key_hash TEXT NOT NULL UNIQUE,
    scopes TEXT NOT NULL DEFAULT 'sync:read,sync:write',
    created_at TEXT NOT NULL,
    expires_at TEXT,
    last_used_at TEXT,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash);

CREATE TABLE IF NOT EXISTS sync_snapshots (
    id TEXT PRIMARY KEY,
    device_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_sync_snapshots_device ON sync_snapshots(device_id);

CREATE TABLE IF NOT EXISTS sync_files (
    snapshot_id TEXT NOT NULL,
    path TEXT NOT NULL,
    hash TEXT NOT NULL,
    size_bytes INTEGER NOT NULL DEFAULT 0,
    modified_at TEXT NOT NULL,
    PRIMARY KEY (snapshot_id, path),
    FOREIGN KEY(snapshot_id) REFERENCES sync_snapshots(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_sync_files_hash ON sync_files(hash);