//! SQLite database wrapper with versioned migrations for samples, VFS, tags, and analysis tables.

use std::path::Path;

use rusqlite::{Connection, functions::FunctionFlags};
use sha2::{Digest, Sha256};
use thiserror::Error;
use tracing::instrument;

#[derive(Error, Debug)]
pub enum DbError {
    #[error("SQLite error: {0}")]
    Sqlite(#[from] rusqlite::Error),
}

/// Core database wrapper. All access is synchronous — no async runtime needed,
/// safe to use from a CLAP plugin host thread.
pub struct Database {
    conn: Connection,
}

const MIGRATION_001: &str = r#"
-- Sample storage and metadata
CREATE TABLE samples (
    hash TEXT PRIMARY KEY,
    original_name TEXT NOT NULL,
    file_extension TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    import_date INTEGER NOT NULL,
    last_modified INTEGER NOT NULL
);

-- Audio analysis results
CREATE TABLE audio_analysis (
    hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
    bpm REAL,
    musical_key TEXT,
    duration REAL NOT NULL,
    sample_rate INTEGER NOT NULL,
    channels INTEGER NOT NULL,
    peak_db REAL,
    rms_db REAL,
    is_loop BOOLEAN,
    spectral_centroid REAL,
    onset_strength REAL,
    analyzed_at INTEGER NOT NULL
);

-- Virtual file systems
CREATE TABLE vfs (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    created_at INTEGER NOT NULL,
    modified_at INTEGER NOT NULL
);

-- VFS directory/file nodes
CREATE TABLE vfs_nodes (
    id INTEGER PRIMARY KEY,
    vfs_id INTEGER NOT NULL REFERENCES vfs(id) ON DELETE CASCADE,
    parent_id INTEGER REFERENCES vfs_nodes(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    node_type TEXT NOT NULL CHECK(node_type IN ('directory', 'sample')),
    sample_hash TEXT REFERENCES samples(hash) ON DELETE CASCADE,
    created_at INTEGER NOT NULL,
    UNIQUE(vfs_id, parent_id, name)
);

-- User-defined tags
CREATE TABLE tags (
    sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
    tag_name TEXT NOT NULL,
    tag_value TEXT NOT NULL,
    PRIMARY KEY (sample_hash, tag_name, tag_value)
);

-- Collections/playlists
CREATE TABLE collections (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at INTEGER NOT NULL
);

CREATE TABLE collection_members (
    collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
    added_at INTEGER NOT NULL,
    PRIMARY KEY (collection_id, sample_hash)
);

-- Smart folders (saved searches)
CREATE TABLE smart_folders (
    id INTEGER PRIMARY KEY,
    vfs_id INTEGER NOT NULL REFERENCES vfs(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    query_json TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

-- Performance indexes
CREATE INDEX idx_vfs_nodes_parent ON vfs_nodes(parent_id);
CREATE INDEX idx_vfs_nodes_vfs ON vfs_nodes(vfs_id);
CREATE INDEX idx_vfs_nodes_hash ON vfs_nodes(sample_hash);
CREATE INDEX idx_tags_hash ON tags(sample_hash);
CREATE INDEX idx_tags_name_value ON tags(tag_name, tag_value);
CREATE INDEX idx_analysis_bpm ON audio_analysis(bpm);
CREATE INDEX idx_analysis_key ON audio_analysis(musical_key);
"#;

const MIGRATION_002: &str = r#"
CREATE TABLE tags_v2 (
    sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
    tag TEXT NOT NULL,
    PRIMARY KEY (sample_hash, tag)
);

-- Migrate any existing data
INSERT OR IGNORE INTO tags_v2 (sample_hash, tag)
SELECT sample_hash, LOWER(tag_name || '.' || tag_value) FROM tags;

DROP TABLE tags;
ALTER TABLE tags_v2 RENAME TO tags;

CREATE INDEX idx_tags_hash ON tags(sample_hash);
CREATE INDEX idx_tags_tag ON tags(tag);
"#;

const MIGRATION_003: &str = r#"
ALTER TABLE audio_analysis ADD COLUMN lufs REAL;
ALTER TABLE audio_analysis ADD COLUMN spectral_flatness REAL;
ALTER TABLE audio_analysis ADD COLUMN spectral_rolloff REAL;
ALTER TABLE audio_analysis ADD COLUMN zero_crossing_rate REAL;
ALTER TABLE audio_analysis ADD COLUMN classification TEXT;
"#;

const MIGRATION_004: &str = r#"
CREATE TABLE IF NOT EXISTS waveform_data (
    hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
    num_buckets INTEGER NOT NULL,
    peak_data BLOB NOT NULL,
    sample_rate INTEGER NOT NULL,
    duration REAL NOT NULL,
    generated_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_analysis_duration ON audio_analysis(duration);
CREATE INDEX IF NOT EXISTS idx_analysis_classification ON audio_analysis(classification);
CREATE INDEX IF NOT EXISTS idx_samples_name ON samples(original_name);
"#;

const MIGRATION_005: &str = r#"
CREATE TABLE IF NOT EXISTS user_config (key TEXT PRIMARY KEY, value TEXT NOT NULL);
"#;

const MIGRATION_006: &str = r#"
CREATE TABLE IF NOT EXISTS fingerprints (
    hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
    envelope BLOB NOT NULL,
    sample_rate INTEGER NOT NULL,
    generated_at INTEGER NOT NULL
);
"#;

const MIGRATION_007: &str = r#"
-- Per-VFS toggle for syncing audio file blobs to cloud (metadata always syncs)
ALTER TABLE vfs ADD COLUMN sync_files INTEGER NOT NULL DEFAULT 0;

-- Sync metadata key-value store
CREATE TABLE IF NOT EXISTS sync_state (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);
INSERT OR IGNORE INTO sync_state (key, value) VALUES
    ('device_id', ''),
    ('pull_cursor', ''),
    ('auto_sync_enabled', '0'),
    ('sync_interval_minutes', '15'),
    ('applying_remote', '0'),
    ('last_sync_at', ''),
    ('initial_snapshot_done', '0');

-- Local change log for push/pull sync
CREATE TABLE IF NOT EXISTS sync_changelog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT NOT NULL,
    op TEXT NOT NULL,
    row_id TEXT NOT NULL,
    timestamp TEXT NOT NULL DEFAULT (datetime('now')),
    data TEXT,
    pushed INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_changelog_pushed ON sync_changelog(pushed);

-- ── Triggers: record changes unless applying remote data ──

-- samples
CREATE TRIGGER IF NOT EXISTS sync_samples_insert AFTER INSERT ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'INSERT', NEW.hash,
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified));
END;

CREATE TRIGGER IF NOT EXISTS sync_samples_update AFTER UPDATE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'UPDATE', NEW.hash,
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified));
END;

CREATE TRIGGER IF NOT EXISTS sync_samples_delete AFTER DELETE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'DELETE', OLD.hash, NULL);
END;

-- audio_analysis
CREATE TRIGGER IF NOT EXISTS sync_audio_analysis_insert AFTER INSERT ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'INSERT', NEW.hash,
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification));
END;

CREATE TRIGGER IF NOT EXISTS sync_audio_analysis_update AFTER UPDATE ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'UPDATE', NEW.hash,
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification));
END;

CREATE TRIGGER IF NOT EXISTS sync_audio_analysis_delete AFTER DELETE ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'DELETE', OLD.hash, NULL);
END;

-- vfs
CREATE TRIGGER IF NOT EXISTS sync_vfs_insert AFTER INSERT ON vfs
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
            'sync_files', NEW.sync_files));
END;

CREATE TRIGGER IF NOT EXISTS sync_vfs_update AFTER UPDATE ON vfs
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs', 'UPDATE', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
            'sync_files', NEW.sync_files));
END;

CREATE TRIGGER IF NOT EXISTS sync_vfs_delete AFTER DELETE ON vfs
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs', 'DELETE', CAST(OLD.id AS TEXT), NULL);
END;

-- vfs_nodes
CREATE TRIGGER IF NOT EXISTS sync_vfs_nodes_insert AFTER INSERT ON vfs_nodes
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs_nodes', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
            'name', NEW.name, 'node_type', NEW.node_type,
            'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_vfs_nodes_update AFTER UPDATE ON vfs_nodes
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs_nodes', 'UPDATE', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
            'name', NEW.name, 'node_type', NEW.node_type,
            'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_vfs_nodes_delete AFTER DELETE ON vfs_nodes
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs_nodes', 'DELETE', CAST(OLD.id AS TEXT), NULL);
END;

-- tags
CREATE TRIGGER IF NOT EXISTS sync_tags_insert AFTER INSERT ON tags
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tags', 'INSERT', NEW.sample_hash || ':' || NEW.tag,
        json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag));
END;

CREATE TRIGGER IF NOT EXISTS sync_tags_delete AFTER DELETE ON tags
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tags', 'DELETE', OLD.sample_hash || ':' || OLD.tag, NULL);
END;

-- collections
CREATE TRIGGER IF NOT EXISTS sync_collections_insert AFTER INSERT ON collections
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collections', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'description', NEW.description, 'created_at', NEW.created_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_collections_update AFTER UPDATE ON collections
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collections', 'UPDATE', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'description', NEW.description, 'created_at', NEW.created_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_collections_delete AFTER DELETE ON collections
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collections', 'DELETE', CAST(OLD.id AS TEXT), NULL);
END;

-- collection_members
CREATE TRIGGER IF NOT EXISTS sync_collection_members_insert AFTER INSERT ON collection_members
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collection_members', 'INSERT',
        CAST(NEW.collection_id AS TEXT) || ':' || NEW.sample_hash,
        json_object('collection_id', NEW.collection_id, 'sample_hash', NEW.sample_hash,
            'added_at', NEW.added_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_collection_members_delete AFTER DELETE ON collection_members
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collection_members', 'DELETE',
        CAST(OLD.collection_id AS TEXT) || ':' || OLD.sample_hash, NULL);
END;

-- smart_folders sync triggers used to live here. Removed 2026-06-02:
-- M015 drops `smart_folders` and merges its contents into
-- `collections.filter_json`, so replaying M007 against a post-M015 schema
-- failed with "no such table". The triggers had no functional effect on
-- any install path (smart_folders is empty on first-run between M001's
-- CREATE and M015's DROP), so removing them is invisible. M015's
-- `DROP TRIGGER IF EXISTS sync_smart_folders_*` stays in place for DBs
-- that already applied the old M007 and need the triggers cleaned up.

-- user_config (exclude sync-internal keys)
CREATE TRIGGER IF NOT EXISTS sync_user_config_insert AFTER INSERT ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'INSERT', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER IF NOT EXISTS sync_user_config_update AFTER UPDATE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'UPDATE', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER IF NOT EXISTS sync_user_config_delete AFTER DELETE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND OLD.key NOT LIKE 'sync_%'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'DELETE', OLD.key, NULL);
END;
"#;

const MIGRATION_008: &str = r#"
-- cloud_only: 1 when the local blob has been deleted but exists in cloud storage
ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0;

-- Recreate samples triggers to include cloud_only in the JSON data
DROP TRIGGER IF EXISTS sync_samples_insert;
DROP TRIGGER IF EXISTS sync_samples_update;

CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'INSERT', NEW.hash,
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'cloud_only', NEW.cloud_only));
END;

CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'UPDATE', NEW.hash,
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'cloud_only', NEW.cloud_only));
END;
"#;

const MIGRATION_009: &str = r#"
-- Duration on samples table so it's available immediately after import (before analysis).
ALTER TABLE samples ADD COLUMN duration REAL;

-- Recreate samples triggers to include duration in the JSON data
DROP TRIGGER IF EXISTS sync_samples_insert;
DROP TRIGGER IF EXISTS sync_samples_update;

CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'INSERT', NEW.hash,
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'cloud_only', NEW.cloud_only, 'duration', NEW.duration));
END;

CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'UPDATE', NEW.hash,
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'cloud_only', NEW.cloud_only, 'duration', NEW.duration));
END;
"#;

const MIGRATION_010: &str = r#"
-- New spectral and waveform features for improved classification
ALTER TABLE audio_analysis ADD COLUMN spectral_bandwidth REAL;
ALTER TABLE audio_analysis ADD COLUMN centroid_variance REAL;
ALTER TABLE audio_analysis ADD COLUMN crest_factor REAL;
ALTER TABLE audio_analysis ADD COLUMN attack_time REAL;

-- Recreate audio_analysis sync triggers to include new columns
DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
DROP TRIGGER IF EXISTS sync_audio_analysis_update;

CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'INSERT', NEW.hash,
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
            'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
            'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
END;

CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'UPDATE', NEW.hash,
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
            'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
            'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
END;
"#;

const MIGRATION_011: &str = r#"
-- ML classifier confidence score
ALTER TABLE audio_analysis ADD COLUMN classification_confidence REAL;

-- Recreate audio_analysis sync triggers to include new column
DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
DROP TRIGGER IF EXISTS sync_audio_analysis_update;

CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'INSERT', NEW.hash,
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
            'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
            'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time,
            'classification_confidence', NEW.classification_confidence));
END;

CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'UPDATE', NEW.hash,
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification,
            'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
            'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time,
            'classification_confidence', NEW.classification_confidence));
END;
"#;

const MIGRATION_012: &str = r#"
-- Edit history: tracks destructive edits for future undo support
CREATE TABLE IF NOT EXISTS edit_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_hash TEXT NOT NULL,
    result_hash TEXT NOT NULL,
    operation TEXT NOT NULL,
    params_json TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_edit_history_source ON edit_history(source_hash);
CREATE INDEX IF NOT EXISTS idx_edit_history_result ON edit_history(result_hash);

-- Sync trigger for edit_history
CREATE TRIGGER IF NOT EXISTS sync_edit_history_insert AFTER INSERT ON edit_history
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('edit_history', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'source_hash', NEW.source_hash,
            'result_hash', NEW.result_hash, 'operation', NEW.operation,
            'params_json', NEW.params_json, 'created_at', NEW.created_at));
END;
"#;

const MIGRATION_013: &str = r#"
-- Loose-files mode: remember original file path instead of copying into vault.
-- NULL = normal (blob in samples/), non-NULL = loose-files (blob at this path).
-- Intentionally excluded from sync triggers — source_path is device-local.
ALTER TABLE samples ADD COLUMN source_path TEXT;
"#;

const MIGRATION_014: &str = r#"
-- Prevent duplicate root-level VFS node names. The existing UNIQUE(vfs_id, parent_id, name)
-- constraint treats NULLs as distinct, so root nodes (parent_id IS NULL) could collide.
CREATE UNIQUE INDEX IF NOT EXISTS idx_vfs_nodes_root_unique
    ON vfs_nodes(vfs_id, name) WHERE parent_id IS NULL;
"#;

const MIGRATION_015: &str = r#"
-- Merge smart folders into collections: add a filter_json column.
-- NULL filter_json = manual collection, non-NULL = dynamic (saved search).
ALTER TABLE collections ADD COLUMN filter_json TEXT;
-- Migrate existing smart folders into collections with their filters.
INSERT OR IGNORE INTO collections (name, description, created_at, filter_json)
    SELECT name, NULL, created_at, query_json FROM smart_folders;
-- Drop the smart_folders table (triggers first, then table).
DROP TRIGGER IF EXISTS sync_smart_folders_insert;
DROP TRIGGER IF EXISTS sync_smart_folders_update;
DROP TRIGGER IF EXISTS sync_smart_folders_delete;
DROP TABLE IF EXISTS smart_folders;
"#;

const MIGRATION_016: &str = r#"
-- Exclude loose-files mode from sync: a compromised server or second device
-- should not be able to silently flip a security-relevant setting.
DROP TRIGGER IF EXISTS sync_user_config_insert;
DROP TRIGGER IF EXISTS sync_user_config_update;
DROP TRIGGER IF EXISTS sync_user_config_delete;

CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
    AND NEW.key != 'unsafe_mode'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'INSERT', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
    AND NEW.key != 'unsafe_mode'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'UPDATE', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND OLD.key NOT LIKE 'sync_%'
    AND OLD.key != 'unsafe_mode'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'DELETE', OLD.key, NULL);
END;
"#;

const MIGRATION_017: &str = r#"
-- Schema-only half of the 'unsafe_mode' -> 'loose_files' rename.
-- Recreates the sync-exclusion triggers to reference the new key literal
-- in their WHEN clauses (triggers can't parameterize key names, so the
-- rewrite has to live in a migration). The runtime row-copy
-- (unsafe_mode value -> loose_files row) lives in main.rs at the
-- vault-open path; doing it there avoids running it against every
-- attached/auxiliary DB that goes through migrate().
DROP TRIGGER IF EXISTS sync_user_config_insert;
DROP TRIGGER IF EXISTS sync_user_config_update;
DROP TRIGGER IF EXISTS sync_user_config_delete;

CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
    AND NEW.key != 'loose_files'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'INSERT', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
    AND NEW.key != 'loose_files'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'UPDATE', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND OLD.key NOT LIKE 'sync_%'
    AND OLD.key != 'loose_files'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'DELETE', OLD.key, NULL);
END;
"#;

/// M018 — hash sensitive row_id values on the wire.
///
/// Per 2026-06-02 SyncKit upload audit: the `sync_changelog.row_id` column
/// is sent to the server in cleartext, and the existing triggers stuffed
/// user content into it (tag strings as `sample_hash:tag`, raw sample
/// SHA-256s as content fingerprints, collection bindings). The encrypted
/// `data` field replicated the same content, but the cleartext row_id leak
/// stood regardless.
///
/// This migration:
///
/// 1. Generates a per-user `row_id_salt` in `sync_state` (never synced) so
///    even a global rainbow table over common tag strings can't deanonymise
///    users. SQLite's `randomblob(32)` is seeded from /dev/urandom on POSIX
///    and CryptGenRandom on Windows.
/// 2. Recreates every sync trigger to wrap row_id in
///    `hash_row_id(salt, canonical_key)`. The encrypted `data` field still
///    carries the cleartext for the receiving device.
/// 3. Extends DELETE triggers to emit the canonical key(s) in `data` so the
///    pull-side `resolve::apply_delete` can reconstruct the WHERE clause
///    without parsing row_id (which is now opaque).
/// 4. Rewrites every unpushed row in `sync_changelog` that contained
///    sensitive cleartext: hashes the row_id, and for DELETE rows in
///    composite-key tables (`tags`, `collection_members`) backfills the
///    canonical key from the now-being-hashed cleartext into `data`.
///
/// Numeric-id tables (vfs, vfs_nodes, collections, smart_folders,
/// edit_history) and user_config are left as-is — their row_ids carry
/// either opaque integers or a closed set of app-defined config keys, no
/// user content.
const MIGRATION_018: &str = r#"
-- 1. Per-user salt for row_id hashing. `INSERT OR IGNORE` so re-running
--    this migration after a partial crash doesn't rotate the salt and
--    invalidate already-hashed row_ids.
INSERT OR IGNORE INTO sync_state (key, value)
VALUES ('row_id_salt', lower(hex(randomblob(32))));

-- 2. Backfill canonical-key `data` for unpushed DELETE rows in composite-PK
--    tables. Must run BEFORE the row_id hash so we still have the cleartext
--    composite to parse.
UPDATE sync_changelog
SET data = json_object(
        'sample_hash', substr(row_id, 1, instr(row_id, ':') - 1),
        'tag', substr(row_id, instr(row_id, ':') + 1)
    )
WHERE table_name = 'tags' AND op = 'DELETE' AND pushed = 0
    AND data IS NULL
    AND instr(row_id, ':') > 0;

UPDATE sync_changelog
SET data = json_object(
        'collection_id', substr(row_id, 1, instr(row_id, ':') - 1),
        'sample_hash', substr(row_id, instr(row_id, ':') + 1)
    )
WHERE table_name = 'collection_members' AND op = 'DELETE' AND pushed = 0
    AND data IS NULL
    AND instr(row_id, ':') > 0;

-- 3. For single-PK sensitive-row_id tables, backfill canonical-key `data`
--    for unpushed DELETE rows so apply_delete on the pulling device can
--    reconstruct the WHERE clause from the encrypted data alone.
UPDATE sync_changelog
SET data = json_object('hash', row_id)
WHERE table_name IN ('samples', 'audio_analysis')
    AND op = 'DELETE' AND pushed = 0 AND data IS NULL;

-- 4. Now hash the row_id for every unpushed row whose cleartext leaked user
--    content (sample hashes, tag strings).
UPDATE sync_changelog
SET row_id = hash_row_id(
    (SELECT value FROM sync_state WHERE key = 'row_id_salt'),
    row_id
)
WHERE pushed = 0
    AND table_name IN ('samples', 'audio_analysis', 'tags', 'collection_members');

-- 5. Drop and recreate every sync trigger with hash_row_id wrapping.
--    DELETE triggers gain a canonical-key `data` payload.

DROP TRIGGER IF EXISTS sync_samples_insert;
DROP TRIGGER IF EXISTS sync_samples_update;
DROP TRIGGER IF EXISTS sync_samples_delete;
DROP TRIGGER IF EXISTS sync_audio_analysis_insert;
DROP TRIGGER IF EXISTS sync_audio_analysis_update;
DROP TRIGGER IF EXISTS sync_audio_analysis_delete;
DROP TRIGGER IF EXISTS sync_vfs_insert;
DROP TRIGGER IF EXISTS sync_vfs_update;
DROP TRIGGER IF EXISTS sync_vfs_delete;
DROP TRIGGER IF EXISTS sync_vfs_nodes_insert;
DROP TRIGGER IF EXISTS sync_vfs_nodes_update;
DROP TRIGGER IF EXISTS sync_vfs_nodes_delete;
DROP TRIGGER IF EXISTS sync_tags_insert;
DROP TRIGGER IF EXISTS sync_tags_delete;
DROP TRIGGER IF EXISTS sync_collections_insert;
DROP TRIGGER IF EXISTS sync_collections_update;
DROP TRIGGER IF EXISTS sync_collections_delete;
DROP TRIGGER IF EXISTS sync_collection_members_insert;
DROP TRIGGER IF EXISTS sync_collection_members_delete;
-- smart_folders table was dropped in M015; M007 triggers are no-ops post-M015
DROP TRIGGER IF EXISTS sync_user_config_insert;
DROP TRIGGER IF EXISTS sync_user_config_update;
DROP TRIGGER IF EXISTS sync_user_config_delete;
DROP TRIGGER IF EXISTS sync_edit_history_insert;

-- samples (single PK: hash)
CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'duration', NEW.duration, 'cloud_only', NEW.cloud_only));
END;

CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'UPDATE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'duration', NEW.duration, 'cloud_only', NEW.cloud_only));
END;

CREATE TRIGGER sync_samples_delete AFTER DELETE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'DELETE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
        json_object('hash', OLD.hash));
END;

-- audio_analysis (single PK: hash)
CREATE TRIGGER sync_audio_analysis_insert AFTER INSERT ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification));
END;

CREATE TRIGGER sync_audio_analysis_update AFTER UPDATE ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'UPDATE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'bpm', NEW.bpm, 'musical_key', NEW.musical_key,
            'duration', NEW.duration, 'sample_rate', NEW.sample_rate, 'channels', NEW.channels,
            'peak_db', NEW.peak_db, 'rms_db', NEW.rms_db, 'is_loop', NEW.is_loop,
            'spectral_centroid', NEW.spectral_centroid, 'onset_strength', NEW.onset_strength,
            'analyzed_at', NEW.analyzed_at, 'lufs', NEW.lufs,
            'spectral_flatness', NEW.spectral_flatness, 'spectral_rolloff', NEW.spectral_rolloff,
            'zero_crossing_rate', NEW.zero_crossing_rate, 'classification', NEW.classification));
END;

CREATE TRIGGER sync_audio_analysis_delete AFTER DELETE ON audio_analysis
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('audio_analysis', 'DELETE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
        json_object('hash', OLD.hash));
END;

-- vfs (numeric PK — row_id stays as id string; not sensitive)
CREATE TRIGGER sync_vfs_insert AFTER INSERT ON vfs
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
            'sync_files', NEW.sync_files));
END;

CREATE TRIGGER sync_vfs_update AFTER UPDATE ON vfs
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs', 'UPDATE', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'created_at', NEW.created_at, 'modified_at', NEW.modified_at,
            'sync_files', NEW.sync_files));
END;

CREATE TRIGGER sync_vfs_delete AFTER DELETE ON vfs
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs', 'DELETE', CAST(OLD.id AS TEXT), json_object('id', OLD.id));
END;

-- vfs_nodes (numeric PK)
CREATE TRIGGER sync_vfs_nodes_insert AFTER INSERT ON vfs_nodes
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs_nodes', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
            'name', NEW.name, 'node_type', NEW.node_type,
            'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
END;

CREATE TRIGGER sync_vfs_nodes_update AFTER UPDATE ON vfs_nodes
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs_nodes', 'UPDATE', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'vfs_id', NEW.vfs_id, 'parent_id', NEW.parent_id,
            'name', NEW.name, 'node_type', NEW.node_type,
            'sample_hash', NEW.sample_hash, 'created_at', NEW.created_at));
END;

CREATE TRIGGER sync_vfs_nodes_delete AFTER DELETE ON vfs_nodes
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('vfs_nodes', 'DELETE', CAST(OLD.id AS TEXT), json_object('id', OLD.id));
END;

-- tags (composite PK: sample_hash + tag — both sensitive)
CREATE TRIGGER sync_tags_insert AFTER INSERT ON tags
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tags', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
                    NEW.sample_hash || ':' || NEW.tag),
        json_object('sample_hash', NEW.sample_hash, 'tag', NEW.tag));
END;

CREATE TRIGGER sync_tags_delete AFTER DELETE ON tags
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tags', 'DELETE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
                    OLD.sample_hash || ':' || OLD.tag),
        json_object('sample_hash', OLD.sample_hash, 'tag', OLD.tag));
END;

-- collections (numeric PK)
CREATE TRIGGER sync_collections_insert AFTER INSERT ON collections
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collections', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'description', NEW.description, 'created_at', NEW.created_at,
            'filter_json', NEW.filter_json));
END;

CREATE TRIGGER sync_collections_update AFTER UPDATE ON collections
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collections', 'UPDATE', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'name', NEW.name,
            'description', NEW.description, 'created_at', NEW.created_at,
            'filter_json', NEW.filter_json));
END;

CREATE TRIGGER sync_collections_delete AFTER DELETE ON collections
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collections', 'DELETE', CAST(OLD.id AS TEXT), json_object('id', OLD.id));
END;

-- collection_members (composite PK: collection_id + sample_hash — hash is sensitive)
CREATE TRIGGER sync_collection_members_insert AFTER INSERT ON collection_members
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collection_members', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
                    CAST(NEW.collection_id AS TEXT) || ':' || NEW.sample_hash),
        json_object('collection_id', NEW.collection_id, 'sample_hash', NEW.sample_hash,
            'added_at', NEW.added_at));
END;

CREATE TRIGGER sync_collection_members_delete AFTER DELETE ON collection_members
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('collection_members', 'DELETE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'),
                    CAST(OLD.collection_id AS TEXT) || ':' || OLD.sample_hash),
        json_object('collection_id', OLD.collection_id, 'sample_hash', OLD.sample_hash));
END;

-- smart_folders table was dropped in M015; not recreating its triggers.

-- user_config (key is app-defined closed set; not sensitive)
CREATE TRIGGER sync_user_config_insert AFTER INSERT ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
    AND NEW.key != 'loose_files'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'INSERT', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER sync_user_config_update AFTER UPDATE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND NEW.key NOT LIKE 'sync_%'
    AND NEW.key != 'loose_files'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'UPDATE', NEW.key,
        json_object('key', NEW.key, 'value', NEW.value));
END;

CREATE TRIGGER sync_user_config_delete AFTER DELETE ON user_config
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
    AND OLD.key NOT LIKE 'sync_%'
    AND OLD.key != 'loose_files'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'DELETE', OLD.key, json_object('key', OLD.key));
END;

-- edit_history (numeric PK)
CREATE TRIGGER sync_edit_history_insert AFTER INSERT ON edit_history
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('edit_history', 'INSERT', CAST(NEW.id AS TEXT),
        json_object('id', NEW.id, 'source_hash', NEW.source_hash,
            'result_hash', NEW.result_hash, 'operation', NEW.operation,
            'params_json', NEW.params_json, 'created_at', NEW.created_at));
END;
"#;

/// M019 — soft-delete (tombstone) infrastructure for samples.
///
/// Phase 1 of the multi-device sample-deletion design (see
/// `docs/design-sample-deletion.md`). This migration only lands the
/// schema and bumps the samples triggers to include the new column in
/// their wire-format JSON. No code path currently sets `deleted_at`, so
/// every existing read filter (`WHERE samples.deleted_at IS NULL`) is a
/// no-op until Phase 2 wires up the tombstone+undelete operations.
///
/// Index is partial — only tombstoned rows are indexed, so the index
/// stays tiny in steady-state (most samples are live).
///
/// `sample_tombstone_retain_days` defaults to 30 (matches OS Trash
/// conventions). User-configurable via the existing user_config sync
/// trigger; the value syncs across devices.
const MIGRATION_019: &str = r#"
ALTER TABLE samples ADD COLUMN deleted_at INTEGER;
CREATE INDEX IF NOT EXISTS idx_samples_deleted_at
    ON samples(deleted_at) WHERE deleted_at IS NOT NULL;

-- Suppress the user_config sync trigger for the duration of this seed
-- INSERT — otherwise the migration would push a spurious row into
-- sync_changelog on every fresh install. The trigger's WHEN clause
-- short-circuits while applying_remote = '1'. Both flips run inside
-- the migration's transaction, so a crash mid-migration rolls back the
-- flag-set along with everything else.
UPDATE sync_state SET value = '1' WHERE key = 'applying_remote';
INSERT OR IGNORE INTO user_config (key, value)
    VALUES ('sample_tombstone_retain_days', '30');
UPDATE sync_state SET value = '0' WHERE key = 'applying_remote';

-- Re-emit samples triggers so deleted_at flows through the wire JSON.
-- Existing INSERT/UPDATE bodies list columns explicitly; the new column
-- needs to be added to the json_object call (it doesn't pick up
-- automatically). DELETE trigger needs the column too so the receiving
-- device's apply_upsert sees the tombstone state on a re-INSERT path.
DROP TRIGGER IF EXISTS sync_samples_insert;
DROP TRIGGER IF EXISTS sync_samples_update;
DROP TRIGGER IF EXISTS sync_samples_delete;

CREATE TRIGGER sync_samples_insert AFTER INSERT ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'duration', NEW.duration, 'cloud_only', NEW.cloud_only,
            'source_path', NEW.source_path, 'deleted_at', NEW.deleted_at));
END;

CREATE TRIGGER sync_samples_update AFTER UPDATE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'UPDATE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'original_name', NEW.original_name,
            'file_extension', NEW.file_extension, 'file_size', NEW.file_size,
            'import_date', NEW.import_date, 'last_modified', NEW.last_modified,
            'duration', NEW.duration, 'cloud_only', NEW.cloud_only,
            'source_path', NEW.source_path, 'deleted_at', NEW.deleted_at));
END;

CREATE TRIGGER sync_samples_delete AFTER DELETE ON samples
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('samples', 'DELETE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.hash),
        json_object('hash', OLD.hash));
END;
"#;

/// Register `hash_row_id(salt, key) -> TEXT` as a deterministic SQLite
/// function on the given connection. Used by the M018 sync triggers so the
/// `sync_changelog.row_id` field never carries cleartext content (tag strings,
/// raw sample SHA-256s) on the wire. The salt is a per-user random nonce
/// stored in `sync_state` and never synced; without it, even a global rainbow
/// table over common tag strings would deanonymise users.
fn register_hash_row_id(conn: &Connection) -> Result<(), DbError> {
    conn.create_scalar_function(
        "hash_row_id",
        2,
        FunctionFlags::SQLITE_DETERMINISTIC | FunctionFlags::SQLITE_UTF8,
        |ctx| {
            let salt: String = ctx.get(0)?;
            let key: String = ctx.get(1)?;
            let mut hasher = Sha256::new();
            hasher.update(salt.as_bytes());
            hasher.update(b":");
            hasher.update(key.as_bytes());
            let digest = hasher.finalize();
            let mut hex = String::with_capacity(64);
            for byte in digest {
                use std::fmt::Write;
                let _ = write!(hex, "{byte:02x}");
            }
            Ok(hex)
        },
    )?;
    Ok(())
}

impl Database {
    /// Open (or create) the database at the given path and run migrations.
    #[instrument(skip_all)]
    pub fn open(path: impl AsRef<Path>) -> Result<Self, DbError> {
        let conn = Connection::open(path)?;
        conn.execute_batch(
            "PRAGMA journal_mode=WAL;\
             PRAGMA foreign_keys=ON;\
             PRAGMA busy_timeout=5000;\
             PRAGMA wal_checkpoint(TRUNCATE);",
        )?;
        register_hash_row_id(&conn)?;
        let mut db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    /// Flush the WAL back into the main database file and remove the -shm file.
    ///
    /// Call after large write batches (e.g. import completion) to keep the
    /// WAL index fresh and avoid stale memory-mapped state on macOS.
    pub fn wal_checkpoint(&self) -> Result<(), DbError> {
        self.conn
            .execute_batch("PRAGMA wal_checkpoint(TRUNCATE)")?;
        Ok(())
    }

    /// Open an in-memory database (for tests).
    #[instrument(skip_all)]
    pub fn open_in_memory() -> Result<Self, DbError> {
        let conn = Connection::open_in_memory()?;
        conn.execute_batch("PRAGMA foreign_keys=ON;")?;
        register_hash_row_id(&conn)?;
        let mut db = Self { conn };
        db.migrate()?;
        Ok(db)
    }

    /// Apply pending migrations using PRAGMA user_version as the version tracker.
    ///
    /// Each migration step runs inside a transaction so the schema change and
    /// version bump are atomic — a crash between the two can no longer leave the
    /// database in an inconsistent state.
    #[instrument(skip_all)]
    fn migrate(&mut self) -> Result<(), DbError> {
        let version: i32 =
            self.conn
                .query_row("PRAGMA user_version", [], |row| row.get(0))?;

        const MIGRATIONS: &[&str] = &[
            MIGRATION_001,
            MIGRATION_002,
            MIGRATION_003,
            MIGRATION_004,
            MIGRATION_005,
            MIGRATION_006,
            MIGRATION_007,
            MIGRATION_008,
            MIGRATION_009,
            MIGRATION_010,
            MIGRATION_011,
            MIGRATION_012,
            MIGRATION_013,
            MIGRATION_014,
            MIGRATION_015,
            MIGRATION_016,
            MIGRATION_017,
            MIGRATION_018,
            MIGRATION_019,
        ];

        for (i, sql) in MIGRATIONS.iter().enumerate() {
            let target = (i + 1) as i32;
            if version < target {
                let batch = format!("BEGIN;\n{}\nPRAGMA user_version = {};\nCOMMIT;", sql, target);
                match self.conn.execute_batch(&batch) {
                    Ok(()) => {}
                    Err(e) if e.to_string().contains("duplicate column") => {
                        // Recovery path: a prior partial migration committed
                        // some ALTERs before crashing. Re-run the migration in
                        // pieces, tolerating "duplicate column" on ALTERs and
                        // "already exists" on CREATEs (both mean: the prior
                        // partial run got there already; the desired final
                        // state is still reachable). Any OTHER error here is
                        // a real failure — we roll back and surface it,
                        // because silently bumping user_version on a partially
                        // applied schema is the worst possible outcome.
                        let _ = self.conn.execute_batch("ROLLBACK");
                        self.conn.execute_batch("BEGIN")?;

                        // ALTER TABLEs first, individually, tolerating duplicates.
                        for line in sql.lines() {
                            let trimmed = line.trim();
                            if trimmed.to_uppercase().starts_with("ALTER TABLE")
                                && trimmed.to_uppercase().contains("ADD COLUMN")
                                && let Err(alter_err) = self.conn.execute_batch(trimmed)
                                    && !alter_err.to_string().contains("duplicate column") {
                                        let _ = self.conn.execute_batch("ROLLBACK");
                                        return Err(DbError::Sqlite(alter_err));
                                    }
                        }

                        // Non-ALTER statements (CREATE TABLE / INDEX /
                        // TRIGGER, DROP IF EXISTS, INSERT OR IGNORE, plain
                        // INSERT / UPDATE / DELETE). After M018, every
                        // migration from M003 onward is replay-safe by
                        // construction (verified by the
                        // migration_replay_from_version_two_against_full_schema
                        // regression test), so this batch should succeed
                        // cleanly even against a populated schema. "already
                        // exists" stays tolerable as a belt-and-braces guard
                        // for pre-idempotent migration bodies. Anything else
                        // is a real failure — fail fast, don't bump.
                        let non_alter: String = sql
                            .lines()
                            .filter(|l| {
                                let t = l.trim().to_uppercase();
                                !(t.starts_with("ALTER TABLE") && t.contains("ADD COLUMN"))
                            })
                            .collect::<Vec<_>>()
                            .join("\n");
                        if !non_alter.trim().is_empty()
                            && let Err(e) = self.conn.execute_batch(&non_alter)
                                && !e.to_string().contains("already exists") {
                                    let _ = self.conn.execute_batch("ROLLBACK");
                                    return Err(DbError::Sqlite(e));
                                }

                        self.conn.execute_batch(
                            &format!("PRAGMA user_version = {};\nCOMMIT;", target),
                        )?;
                    }
                    Err(e) => return Err(DbError::Sqlite(e)),
                }
            }
        }

        Ok(())
    }

    /// Run a closure inside a SQLite transaction.
    ///
    /// Uses `BEGIN IMMEDIATE` to acquire a write lock upfront, preventing
    /// deadlocks when the closure issues writes. The closure receives no
    /// arguments — it accesses the same `Database` through the shared
    /// `Mutex<Database>`, which is safe because the caller already holds the lock.
    #[instrument(skip_all)]
    pub fn transaction<T, F>(&self, f: F) -> Result<T, DbError>
    where
        F: FnOnce() -> Result<T, DbError>,
    {
        self.conn.execute_batch("BEGIN IMMEDIATE")?;
        match f() {
            Ok(val) => {
                self.conn.execute_batch("COMMIT")?;
                Ok(val)
            }
            Err(e) => {
                if let Err(rb_err) = self.conn.execute_batch("ROLLBACK") {
                    tracing::warn!("ROLLBACK failed after transaction error: {rb_err}");
                }
                Err(e)
            }
        }
    }

    /// Borrow the underlying connection for queries.
    pub fn conn(&self) -> &Connection {
        &self.conn
    }

    /// Aggregate storage stats: (sample_count, total_file_bytes).
    ///
    /// Excludes tombstoned rows (`deleted_at IS NOT NULL`) so the figure matches
    /// the library the user actually sees — the M019 read-path filter applies here
    /// like every other sample read site.
    pub fn storage_stats(&self) -> Result<(u64, u64), DbError> {
        let (count, total): (u64, u64) = self.conn.query_row(
            "SELECT COUNT(*), COALESCE(SUM(file_size), 0) FROM samples WHERE deleted_at IS NULL",
            [],
            |row| Ok((row.get(0)?, row.get(1)?)),
        )?;
        Ok((count, total))
    }

    /// Per-VFS storage stats: count and total bytes of *unique* samples
    /// referenced by `vfs_id`. A sample referenced from multiple nodes in the
    /// same VFS counts once. Used by the sync panel's per-VFS toggle rows so
    /// the user can see how much would upload before enabling blob sync.
    pub fn vfs_storage_stats(&self, vfs_id: i64) -> Result<(u64, u64), DbError> {
        let (count, total): (u64, u64) = self.conn.query_row(
            "SELECT COUNT(*), COALESCE(SUM(file_size), 0) FROM samples \
             WHERE hash IN (\
                 SELECT DISTINCT sample_hash FROM vfs_nodes \
                 WHERE vfs_id = ? AND sample_hash IS NOT NULL\
             )",
            [vfs_id],
            |row| Ok((row.get(0)?, row.get(1)?)),
        )?;
        Ok((count, total))
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn open_in_memory_creates_all_tables() {
        let db = Database::open_in_memory().unwrap();

        let tables: Vec<String> = db
            .conn()
            .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")
            .unwrap()
            .query_map([], |row| row.get(0))
            .unwrap()
            .collect::<Result<_, _>>()
            .unwrap();

        let expected = vec![
            "audio_analysis",
            "collection_members",
            "collections",
            "edit_history",
            "fingerprints",
            "samples",
            "sync_changelog",
            "sync_state",
            "tags",
            "user_config",
            "vfs",
            "vfs_nodes",
            "waveform_data",
        ];
        assert_eq!(tables, expected);
    }

    #[test]
    fn migration_sets_user_version() {
        let db = Database::open_in_memory().unwrap();
        let version: i32 = db
            .conn()
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(version, 19);
    }

    #[test]
    fn migration_is_idempotent() {
        let db = Database::open_in_memory().unwrap();
        // Opening again on the same connection shouldn't fail
        let version: i32 = db
            .conn()
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(version, 19);
    }

    /// Open a fresh file-backed DB, close, reopen. The second open re-enters
    /// `migrate()`; with `user_version=17` no migration body runs, but the
    /// shape verifies our open/close cycle is clean (no locks, no WAL leak).
    #[test]
    fn migration_replay_from_file_no_op() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("audiofiles.db");

        let db = Database::open(&path).unwrap();
        drop(db);

        let db = Database::open(&path).unwrap();
        let version: i32 = db
            .conn()
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(version, 19);
    }

    /// Simulates the worst-case recovery path: a prior partial migration left
    /// every object in place but `user_version` rolled back. Re-running
    /// `migrate()` against the pre-populated schema must succeed without
    /// silent failure. This catches the "silent failure → bump user_version"
    /// bug class for every migration past the inherently-one-shot ones.
    ///
    /// The inherently-one-shot migrations are excluded from this replay
    /// loop:
    /// * M001 — initial schema; bare CREATE TABLEs, runs against an empty DB.
    /// * M002 — `DROP TABLE tags; ALTER tags_v2 RENAME TO tags` rebuild dance.
    /// * M015 — adds `collections.filter_json` and backfills from
    ///   `smart_folders`, then drops `smart_folders`. The backfill SELECT
    ///   references a table that no longer exists after the migration runs,
    ///   so it cannot parse on replay against a post-M015 schema. None of
    ///   these need replay safety: SQLite's atomic-transaction guarantee
    ///   means each migration either fully commits or fully rolls back, so
    ///   the realistic recovery scenario is "re-apply the one migration
    ///   that crashed" — not "re-apply every migration from scratch".
    ///
    /// Every migration from M003 onward (excluding M015) MUST be
    /// replay-safe against a populated schema; if you add a new one that
    /// isn't, this test fails and you should add `IF NOT EXISTS` /
    /// `DROP IF EXISTS` / `INSERT OR IGNORE` accordingly, or add it to the
    /// one-shot list above with a clear rationale.
    #[test]
    fn migration_replay_from_version_fifteen_against_full_schema() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("audiofiles.db");

        Database::open(&path).unwrap();

        {
            let conn = Connection::open(&path).unwrap();
            conn.execute_batch("PRAGMA user_version = 15").unwrap();
        }

        let db = Database::open(&path).unwrap();
        let version: i32 = db
            .conn()
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(version, 19);
    }

    /// M018 contract: the `sync_changelog.row_id` for sensitive tables must
    /// be a 64-hex SHA-256 (per `hash_row_id`), NOT the cleartext content
    /// fingerprint or tag string. The cleartext key lives only in `data`.
    /// This test is the regression gate for the upload audit fix.
    #[test]
    fn m018_hashes_sensitive_row_ids() {
        let db = Database::open_in_memory().unwrap();
        let conn = db.conn();

        // Seed: insert a sample and a tag. Both should fire triggers that
        // write to sync_changelog with a hashed row_id.
        conn.execute(
            "INSERT INTO samples (hash, original_name, file_extension, file_size, \
             import_date, last_modified) VALUES \
             ('abc123', 'kick.wav', 'wav', 100, 0, 0)",
            [],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO tags (sample_hash, tag) VALUES ('abc123', 'drums')",
            [],
        )
        .unwrap();

        // samples row_id: 64-hex hash, NOT "abc123".
        let row_id: String = conn
            .query_row(
                "SELECT row_id FROM sync_changelog WHERE table_name = 'samples' AND op = 'INSERT'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(row_id.len(), 64, "row_id should be SHA-256 hex");
        assert!(row_id.chars().all(|c| c.is_ascii_hexdigit()));
        assert_ne!(row_id, "abc123", "cleartext sample hash must not leak");

        // tags row_id: 64-hex hash, NOT "abc123:drums".
        let row_id: String = conn
            .query_row(
                "SELECT row_id FROM sync_changelog WHERE table_name = 'tags' AND op = 'INSERT'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(row_id.len(), 64);
        assert_ne!(row_id, "abc123:drums", "cleartext tag string must not leak");

        // Salted: hash depends on the per-user salt, so two fresh DBs see
        // different row_ids for the same logical key.
        let db2 = Database::open_in_memory().unwrap();
        let conn2 = db2.conn();
        conn2
            .execute(
                "INSERT INTO samples (hash, original_name, file_extension, file_size, \
                 import_date, last_modified) VALUES \
                 ('abc123', 'kick.wav', 'wav', 100, 0, 0)",
                [],
            )
            .unwrap();
        let row_id2: String = conn2
            .query_row(
                "SELECT row_id FROM sync_changelog WHERE table_name = 'samples' AND op = 'INSERT'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_ne!(row_id, row_id2, "salt should differ between DBs");
    }

    /// M018 contract: DELETE rows must carry the canonical PK in `data` so
    /// the receiving device's `resolve::apply_delete` can reconstruct the
    /// WHERE clause without parsing the (now-hashed) row_id.
    #[test]
    fn m018_delete_triggers_emit_canonical_key_in_data() {
        let db = Database::open_in_memory().unwrap();
        let conn = db.conn();

        conn.execute(
            "INSERT INTO samples (hash, original_name, file_extension, file_size, \
             import_date, last_modified) VALUES \
             ('abc', 'k.wav', 'wav', 1, 0, 0)",
            [],
        )
        .unwrap();
        conn.execute("INSERT INTO tags (sample_hash, tag) VALUES ('abc', 'kick')", [])
            .unwrap();
        conn.execute("DELETE FROM tags WHERE sample_hash = 'abc' AND tag = 'kick'", [])
            .unwrap();

        let data: String = conn
            .query_row(
                "SELECT data FROM sync_changelog WHERE table_name = 'tags' AND op = 'DELETE'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        let parsed: serde_json::Value = serde_json::from_str(&data).unwrap();
        assert_eq!(parsed["sample_hash"], "abc");
        assert_eq!(parsed["tag"], "kick");
    }

    /// M019 contract: `samples.deleted_at` column exists, the partial
    /// index is in place, and the read-path filter actually hides
    /// tombstoned rows from `sample_extension` (and by extension every
    /// other query that uses the `query_sample_field` helper).
    ///
    /// This test is the regression gate that proves Phase 1 of the
    /// tombstone design (docs/design-sample-deletion.md) lands the
    /// promised infrastructure. Phase 2 will wire the UPDATE path that
    /// sets deleted_at; today nothing in app code sets it, so every
    /// query continues to return all rows in practice — but tests can
    /// set it directly and observe the filter working.
    #[test]
    fn m019_tombstone_column_and_read_filter() {
        let db = Database::open_in_memory().unwrap();
        let conn = db.conn();

        // Column exists with default NULL.
        conn.execute(
            "INSERT INTO samples (hash, original_name, file_extension, file_size, \
             import_date, last_modified) VALUES \
             ('live', 'k.wav', 'wav', 1, 0, 0)",
            [],
        )
        .unwrap();
        conn.execute(
            "INSERT INTO samples (hash, original_name, file_extension, file_size, \
             import_date, last_modified) VALUES \
             ('tomb', 't.wav', 'wav', 1, 0, 0)",
            [],
        )
        .unwrap();
        conn.execute(
            "UPDATE samples SET deleted_at = 1700000000 WHERE hash = 'tomb'",
            [],
        )
        .unwrap();

        // sample_extension reads via query_sample_field, which now filters
        // out tombstoned rows.
        let live_ext = crate::store::sample_extension(&db, "live").unwrap();
        assert_eq!(live_ext, "wav");

        let tomb_ext = crate::store::sample_extension(&db, "tomb");
        assert!(
            matches!(tomb_ext, Err(crate::error::CoreError::SampleNotFound(_))),
            "tombstoned sample should be hidden from sample_extension; got {tomb_ext:?}"
        );

        // storage_stats also applies the read-path filter: only the live sample
        // (file_size 1) is counted, not the tombstoned one.
        let (count, bytes) = db.storage_stats().unwrap();
        assert_eq!(count, 1, "tombstoned sample should not be counted");
        assert_eq!(bytes, 1, "tombstoned sample's bytes should be excluded");

        // Default retain-days seed is present.
        let retain: String = conn
            .query_row(
                "SELECT value FROM user_config WHERE key = 'sample_tombstone_retain_days'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(retain, "30");

        // Partial index exists.
        let idx_count: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM sqlite_master \
                 WHERE type = 'index' AND name = 'idx_samples_deleted_at'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(idx_count, 1);
    }

    /// Recovery branch contract: when the non-ALTER batch fails for a
    /// reason OTHER than "already exists", `migrate()` must roll back and
    /// surface the error, NOT bump `user_version` past the failed
    /// migration. Prior behavior was a silent `tracing::warn!` followed by
    /// a `user_version` bump, which left a partially applied schema
    /// invisible to future open() calls.
    ///
    /// Simulates the failure mode by:
    /// 1. Bringing the DB up to current version.
    /// 2. Injecting an inline migration (M999) whose non-ALTER body
    ///    references a non-existent table, AND prepending an ALTER on a
    ///    column that already exists — that's the duplicate-column trip
    ///    wire that funnels execution into the recovery branch.
    /// 3. Setting user_version back to 18 so the runner attempts M019.
    /// 4. Asserting migrate() returns Err and user_version stays at 18.
    ///
    /// We can't easily inject a new migration into the const array, so we
    /// drive the recovery branch by calling the runner inline.
    #[test]
    fn migrate_recovery_branch_fails_fast_on_non_alter_error() {
        use rusqlite::Connection;

        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("audiofiles.db");
        let _db = Database::open(&path).unwrap();
        drop(_db);

        // Reopen with a raw Connection so we can hand-craft the recovery
        // scenario without going through migrate().
        let conn = Connection::open(&path).unwrap();
        register_hash_row_id(&conn).unwrap();
        conn.execute_batch("PRAGMA foreign_keys = ON;").unwrap();

        // Simulate the recovery-branch logic directly: try a migration
        // batch that fails with "duplicate column" (forcing recovery),
        // and whose non-ALTER body references a missing table (forcing
        // the failure that previously got swallowed).
        let bad_sql = "ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0;\n\
                       INSERT INTO no_such_table_exists (k) VALUES ('x');";
        let initial_version: i32 = conn
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(initial_version, 19);

        let batch = format!(
            "BEGIN;\n{}\nPRAGMA user_version = 999;\nCOMMIT;",
            bad_sql
        );
        let first_err = conn.execute_batch(&batch).unwrap_err();
        assert!(
            first_err.to_string().contains("duplicate column"),
            "expected duplicate-column trip wire, got: {first_err}"
        );

        // Recovery: ALTER tolerated, non-ALTER must fail loudly.
        let _ = conn.execute_batch("ROLLBACK");
        conn.execute_batch("BEGIN").unwrap();
        // ALTER passes (column exists; tolerated).
        let alter = "ALTER TABLE samples ADD COLUMN cloud_only INTEGER NOT NULL DEFAULT 0";
        let alter_res = conn.execute_batch(alter);
        assert!(alter_res.is_err());
        assert!(alter_res.unwrap_err().to_string().contains("duplicate column"));

        // Non-ALTER: fail-fast, return error, do not bump user_version.
        let non_alter = "INSERT INTO no_such_table_exists (k) VALUES ('x')";
        let na_res = conn.execute_batch(non_alter);
        assert!(na_res.is_err());
        let msg = na_res.unwrap_err().to_string();
        assert!(
            !msg.contains("already exists"),
            "expected a real failure (no such table), got: {msg}"
        );

        // The fail-fast path rolls back and never reaches the
        // user_version bump. Confirm.
        conn.execute_batch("ROLLBACK").unwrap();
        let after: i32 = conn
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(
            after, initial_version,
            "user_version must not bump when recovery non-ALTER fails for a real reason"
        );
    }

    /// Companion to `migration_replay_from_version_fifteen_against_full_schema`:
    /// rolling user_version back and re-opening must heal to version 18 without
    /// silent partial-state. Identical setup; kept as a contract-specific name
    /// so a failing test points the reader at the recovery-branch design rather
    /// than the broader replay-safety claim.
    #[test]
    fn migrate_recovery_branch_tolerates_already_exists() {
        let dir = tempfile::tempdir().unwrap();
        let path = dir.path().join("audiofiles.db");

        Database::open(&path).unwrap();
        {
            let conn = rusqlite::Connection::open(&path).unwrap();
            conn.execute_batch("PRAGMA user_version = 15").unwrap();
        }
        let db = Database::open(&path).unwrap();
        let version: i32 = db
            .conn()
            .query_row("PRAGMA user_version", [], |row| row.get(0))
            .unwrap();
        assert_eq!(version, 19);
    }

    #[test]
    fn foreign_keys_enforced() {
        let db = Database::open_in_memory().unwrap();
        // Inserting a vfs_node referencing a non-existent vfs should fail
        let result = db.conn().execute(
            "INSERT INTO vfs_nodes (vfs_id, name, node_type, created_at) VALUES (999, 'test', 'directory', 0)",
            [],
        );
        assert!(result.is_err());
    }

    #[test]
    fn transaction_commits_on_success() {
        let db = Database::open_in_memory().unwrap();
        db.transaction(|| {
            db.conn().execute(
                "INSERT INTO user_config (key, value) VALUES ('test_key', 'test_value')",
                [],
            )?;
            Ok(())
        })
        .unwrap();

        let val: String = db
            .conn()
            .query_row(
                "SELECT value FROM user_config WHERE key = 'test_key'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(val, "test_value");
    }

    #[test]
    fn transaction_rolls_back_on_error() {
        let db = Database::open_in_memory().unwrap();
        let result: Result<(), DbError> = db.transaction(|| {
            db.conn().execute(
                "INSERT INTO user_config (key, value) VALUES ('rollback_key', 'val')",
                [],
            )?;
            Err(DbError::Sqlite(rusqlite::Error::QueryReturnedNoRows))
        });
        assert!(result.is_err());

        let count: i64 = db
            .conn()
            .query_row(
                "SELECT COUNT(*) FROM user_config WHERE key = 'rollback_key'",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(count, 0);
    }
}