//! The ordered migration log: every schema version this build can produce.
//!
//! Extracted from the former `db.rs`; the parent re-exports [`SCHEMA_VERSION`].

use rusqlite::Connection;
use rusqlite::functions::FunctionFlags;
use sha2::{Digest, Sha256};

use super::DbError;

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;
-- `classification` was added here. Removed when the sample-class label was
-- retired (docs/ml_classifier.md) rather than dropped in a later migration, so no
-- database ever creates the column in the first place.
";

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);
-- idx_analysis_classification was created here and again in M028; both removed
-- with the column itself.
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));
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));
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
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,
            '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,
            '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"
-- Added classification_confidence and recreated the sync triggers to carry it.
-- The column is retired (docs/ml_classifier.md), so the ALTER is gone; the trigger
-- recreate stays because M018 and M032 both build on the bodies below.
--
-- Recreate audio_analysis sync triggers
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,
            '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,
            'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
            'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
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.
///
/// The `sync_changelog.row_id` column is sent to the server in cleartext, so a
/// trigger must never put user content into it (tag strings as
/// `sample_hash:tag`, raw sample SHA-256s as content fingerprints, collection
/// bindings). The encrypted `data` field carries that content instead.
///
/// 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));
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));
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;
";

const MIGRATION_020: &str = r"
-- Phase 0 of the hybrid tag classifier: persist the 35-element feature vector
-- (9 scalar + 26 MFCC) per sample as the foundation for the rules + k-NN pipeline.
-- The vector is deterministic DSP (non-reversible to audio), stored as a JSON array
-- of f64. feat_version stamps the extraction layout so stale vectors can be recomputed
-- rather than silently mixed.
CREATE TABLE IF NOT EXISTS sample_features (
    hash         TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE,
    feat_version INTEGER NOT NULL,
    vector       TEXT NOT NULL,
    computed_at  INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sample_features_version ON sample_features(feat_version);

-- Sync triggers (mirror audio_analysis: hashed row_id so the sample SHA-256 never
-- goes on the wire; DELETE carries the canonical key in `data`).
CREATE TRIGGER IF NOT EXISTS sync_sample_features_insert AFTER INSERT ON sample_features
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('sample_features', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'feat_version', NEW.feat_version,
            'vector', NEW.vector, 'computed_at', NEW.computed_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_sample_features_update AFTER UPDATE ON sample_features
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('sample_features', 'UPDATE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.hash),
        json_object('hash', NEW.hash, 'feat_version', NEW.feat_version,
            'vector', NEW.vector, 'computed_at', NEW.computed_at));
END;

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

const MIGRATION_021: &str = r"
-- Phase 1 of the hybrid tag classifier: deterministic tag rules (Layer A).
-- Ordered IF/THEN rules over sample metadata + DSP features. Ships empty.
CREATE TABLE IF NOT EXISTS tag_rules (
    id         TEXT PRIMARY KEY,
    name       TEXT NOT NULL,
    enabled    INTEGER NOT NULL DEFAULT 1,
    priority   INTEGER NOT NULL,
    match_mode TEXT NOT NULL,
    conditions TEXT NOT NULL,
    actions    TEXT NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_tag_rules_priority ON tag_rules(priority);

-- Sync triggers (opaque non-sensitive id => cleartext row_id, like collections).
CREATE TRIGGER IF NOT EXISTS sync_tag_rules_insert AFTER INSERT ON tag_rules
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_rules', 'INSERT', NEW.id,
        json_object('id', NEW.id, 'name', NEW.name, 'enabled', NEW.enabled,
            'priority', NEW.priority, 'match_mode', NEW.match_mode,
            'conditions', NEW.conditions, 'actions', NEW.actions,
            'created_at', NEW.created_at));
END;

CREATE TRIGGER IF NOT EXISTS sync_tag_rules_update AFTER UPDATE ON tag_rules
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_rules', 'UPDATE', NEW.id,
        json_object('id', NEW.id, 'name', NEW.name, 'enabled', NEW.enabled,
            'priority', NEW.priority, 'match_mode', NEW.match_mode,
            'conditions', NEW.conditions, 'actions', NEW.actions,
            'created_at', NEW.created_at));
END;

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

const MIGRATION_022: &str = r"
-- Phase 1: tag provenance. Records which machine source applied each tag so
-- manual tags stay sticky (a tag with NO row here is manual). Reconciliation
-- only ever touches rule-sourced tags.
CREATE TABLE IF NOT EXISTS tag_provenance (
    sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
    tag         TEXT NOT NULL,
    source      TEXT NOT NULL,   -- 'rule' | 'ml' | 'cluster' (manual = no row)
    rule_id     TEXT,            -- tag_rules.id when source = 'rule'
    PRIMARY KEY (sample_hash, tag)
);

-- Sync triggers (composite PK with sensitive sample_hash + tag => hashed row_id,
-- mirroring tags; UPDATE supported because reconciliation re-stamps source/rule_id).
CREATE TRIGGER IF NOT EXISTS sync_tag_provenance_insert AFTER INSERT ON tag_provenance
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_provenance', '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,
            'source', NEW.source, 'rule_id', NEW.rule_id));
END;

CREATE TRIGGER IF NOT EXISTS sync_tag_provenance_update AFTER UPDATE ON tag_provenance
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_provenance', 'UPDATE',
        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,
            'source', NEW.source, 'rule_id', NEW.rule_id));
END;

CREATE TRIGGER IF NOT EXISTS sync_tag_provenance_delete AFTER DELETE ON tag_provenance
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_provenance', '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;
";

const MIGRATION_023: &str = r"
-- Phase 3: per-tag ML thresholds (Layer B policy). Absent tag => default policy in code.
CREATE TABLE IF NOT EXISTS tag_policy (
    tag              TEXT PRIMARY KEY,
    review_threshold REAL NOT NULL,
    auto_threshold   REAL NOT NULL
);

-- Sync triggers (tag string is sensitive => hashed row_id, mirroring tags; UPDATE
-- supported because set_policy upserts).
CREATE TRIGGER IF NOT EXISTS sync_tag_policy_insert AFTER INSERT ON tag_policy
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_policy', 'INSERT',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.tag),
        json_object('tag', NEW.tag, 'review_threshold', NEW.review_threshold,
            'auto_threshold', NEW.auto_threshold));
END;

CREATE TRIGGER IF NOT EXISTS sync_tag_policy_update AFTER UPDATE ON tag_policy
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_policy', 'UPDATE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), NEW.tag),
        json_object('tag', NEW.tag, 'review_threshold', NEW.review_threshold,
            'auto_threshold', NEW.auto_threshold));
END;

CREATE TRIGGER IF NOT EXISTS sync_tag_policy_delete AFTER DELETE ON tag_policy
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('tag_policy', 'DELETE',
        hash_row_id((SELECT value FROM sync_state WHERE key = 'row_id_salt'), OLD.tag),
        json_object('tag', OLD.tag));
END;
";

const MIGRATION_024: &str = r"
-- Phase 6: optional trained head (per-library distilled logistic-regression classifier).
-- A local, regenerable cache derived entirely from sample_features + tags; NOT synced
-- (no changelog triggers) because it rebuilds from the exemplar store on any device.
-- Singleton row (id = 1); the whole model travels as one JSON blob.
CREATE TABLE IF NOT EXISTS trained_head (
    id             INTEGER PRIMARY KEY CHECK (id = 1),
    feat_version   INTEGER NOT NULL,
    exemplar_count INTEGER NOT NULL,
    model          TEXT    NOT NULL,
    trained_at     INTEGER NOT NULL
);
";

const MIGRATION_025: &str = r"
-- Phase 7: classifier layers (.afcl file sharing). Imported exemplars/rules are grouped
-- into removable, weightable layers; the user's own data is the implicit 'local' layer
-- (not a row here). Local-only for now (no sync triggers): the .afcl file is the portable
-- artifact and re-imports per device, cross-device sync of imported layers is a follow-up.
CREATE TABLE IF NOT EXISTS classifier_layers (
    id          TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    kind        TEXT NOT NULL,          -- 'imported' | 'official'
    weight      REAL NOT NULL DEFAULT 1.0,
    enabled     INTEGER NOT NULL DEFAULT 1,
    source      TEXT,                   -- .afcl filename / provenance
    imported_at INTEGER NOT NULL
);

-- Imported exemplars: feature vector + tags only (no audio, no sample row), so they live
-- here rather than in sample_features (which FKs to samples).
CREATE TABLE IF NOT EXISTS classifier_exemplars (
    id           INTEGER PRIMARY KEY,
    layer_id     TEXT NOT NULL REFERENCES classifier_layers(id) ON DELETE CASCADE,
    feat_version INTEGER NOT NULL,
    vector       TEXT NOT NULL,         -- JSON array of 35 f64
    tags         TEXT NOT NULL          -- JSON array of String
);
CREATE INDEX IF NOT EXISTS idx_classifier_exemplars_layer ON classifier_exemplars(layer_id);

-- Membership of imported rules in a layer. The rules themselves are ordinary tag_rules
-- rows (added disabled); this join lets a layer be removed in one action.
CREATE TABLE IF NOT EXISTS classifier_layer_rules (
    layer_id TEXT NOT NULL REFERENCES classifier_layers(id) ON DELETE CASCADE,
    rule_id  TEXT NOT NULL REFERENCES tag_rules(id) ON DELETE CASCADE,
    PRIMARY KEY (layer_id, rule_id)
);
";

const MIGRATION_026: &str = r"
-- Phase 7c: sync the classifier-layer tables across the user's own devices.
-- classifier_exemplars is recreated with a TEXT primary key: the M025 INTEGER autoincrement
-- id collides across devices (each device numbers from 1), which row-level sync can't
-- reconcile. Imported exemplars are re-importable, so dropping any M025 rows is acceptable.
DROP TABLE IF EXISTS classifier_exemplars;
CREATE TABLE classifier_exemplars (
    id           TEXT PRIMARY KEY,      -- globally unique ('<layer_id>#<n>')
    layer_id     TEXT NOT NULL REFERENCES classifier_layers(id) ON DELETE CASCADE,
    feat_version INTEGER NOT NULL,
    vector       TEXT NOT NULL,
    tags         TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_classifier_exemplars_layer ON classifier_exemplars(layer_id);

-- Sync triggers (opaque non-sensitive ids => cleartext row_id, like tag_rules/collections;
-- the JSON payload, which carries tag strings, is encrypted on the wire). recursive_triggers
-- is off, so FK cascades don't fire these, afcl::remove_layer deletes children explicitly.
CREATE TRIGGER IF NOT EXISTS sync_classifier_layers_insert AFTER INSERT ON classifier_layers
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_layers', 'INSERT', NEW.id,
        json_object('id', NEW.id, 'name', NEW.name, 'kind', NEW.kind, 'weight', NEW.weight,
            'enabled', NEW.enabled, 'source', NEW.source, 'imported_at', NEW.imported_at));
END;
CREATE TRIGGER IF NOT EXISTS sync_classifier_layers_update AFTER UPDATE ON classifier_layers
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_layers', 'UPDATE', NEW.id,
        json_object('id', NEW.id, 'name', NEW.name, 'kind', NEW.kind, 'weight', NEW.weight,
            'enabled', NEW.enabled, 'source', NEW.source, 'imported_at', NEW.imported_at));
END;
CREATE TRIGGER IF NOT EXISTS sync_classifier_layers_delete AFTER DELETE ON classifier_layers
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_layers', 'DELETE', OLD.id, json_object('id', OLD.id));
END;

CREATE TRIGGER IF NOT EXISTS sync_classifier_exemplars_insert AFTER INSERT ON classifier_exemplars
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_exemplars', 'INSERT', NEW.id,
        json_object('id', NEW.id, 'layer_id', NEW.layer_id, 'feat_version', NEW.feat_version,
            'vector', NEW.vector, 'tags', NEW.tags));
END;
CREATE TRIGGER IF NOT EXISTS sync_classifier_exemplars_delete AFTER DELETE ON classifier_exemplars
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_exemplars', 'DELETE', OLD.id, json_object('id', OLD.id));
END;

CREATE TRIGGER IF NOT EXISTS sync_classifier_layer_rules_insert AFTER INSERT ON classifier_layer_rules
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_layer_rules', 'INSERT', NEW.layer_id || ':' || NEW.rule_id,
        json_object('layer_id', NEW.layer_id, 'rule_id', NEW.rule_id));
END;
CREATE TRIGGER IF NOT EXISTS sync_classifier_layer_rules_delete AFTER DELETE ON classifier_layer_rules
WHEN (SELECT value FROM sync_state WHERE key = 'applying_remote') != '1'
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('classifier_layer_rules', 'DELETE', OLD.layer_id || ':' || OLD.rule_id,
        json_object('layer_id', OLD.layer_id, 'rule_id', OLD.rule_id));
END;
";

const MIGRATION_027: &str = r"
-- FTS5 trigram index over vfs_nodes.name for fast substring search.
--
-- The trigram tokenizer lets `name LIKE '%query%'` use the index instead of
-- scanning all of vfs_nodes on every keystroke, which is the scale cliff the
-- ultra-fuzz audit flagged. The table is content-bearing (standalone) and keyed
-- by rowid = vfs_nodes.id, kept in sync by triggers that fire on EVERY vfs_nodes
-- change -- including remote sync applies (no applying_remote guard), because
-- the local derived index must always mirror the local rows. The FTS table and
-- its shadow tables are local-only and never synced (no sync_changelog triggers).
CREATE VIRTUAL TABLE IF NOT EXISTS vfs_nodes_fts USING fts5(
    name,
    tokenize = 'trigram'
);

-- Backfill from existing rows.
INSERT INTO vfs_nodes_fts(rowid, name) SELECT id, name FROM vfs_nodes;

CREATE TRIGGER IF NOT EXISTS vfs_nodes_fts_insert AFTER INSERT ON vfs_nodes BEGIN
    INSERT INTO vfs_nodes_fts(rowid, name) VALUES (NEW.id, NEW.name);
END;

CREATE TRIGGER IF NOT EXISTS vfs_nodes_fts_delete AFTER DELETE ON vfs_nodes BEGIN
    DELETE FROM vfs_nodes_fts WHERE rowid = OLD.id;
END;

CREATE TRIGGER IF NOT EXISTS vfs_nodes_fts_update AFTER UPDATE OF name ON vfs_nodes BEGIN
    DELETE FROM vfs_nodes_fts WHERE rowid = OLD.id;
    INSERT INTO vfs_nodes_fts(rowid, name) VALUES (NEW.id, NEW.name);
END;
";

const MIGRATION_028: &str = r"
-- Indexes for the search filter columns on audio_analysis. A text-less global
-- filter (e.g. a BPM range with no name query) otherwise full-scans
-- audio_analysis; these let the planner seek instead. musical_key is an
-- equality/IN filter (most selective); bpm/peak_db/duration are range filters.
-- All additive and idempotent. (An index on the retired classification column
-- was here too; removed with the column.)
CREATE INDEX IF NOT EXISTS idx_analysis_musical_key ON audio_analysis(musical_key);
CREATE INDEX IF NOT EXISTS idx_analysis_bpm ON audio_analysis(bpm);
CREATE INDEX IF NOT EXISTS idx_analysis_peak_db ON audio_analysis(peak_db);
CREATE INDEX IF NOT EXISTS idx_analysis_duration ON audio_analysis(duration);
";

const MIGRATION_029: &str = r"
-- Enforce root-node name uniqueness at the engine level. SQLite's UNIQUE
-- treats every NULL as distinct, so the table-level UNIQUE(vfs_id, parent_id,
-- name) does NOT cover root nodes (parent_id IS NULL); uniqueness there was
-- guarded only by a COUNT-then-INSERT check, which is not atomic. Because the
-- DB is shared across the CLAP host thread and the GUI thread, two concurrent
-- same-name root creates could both pass the COUNT and both insert. A partial
-- unique index makes that race unrepresentable; the COUNT check stays as the
-- friendly-error fast path. Additive and idempotent.
CREATE UNIQUE INDEX IF NOT EXISTS idx_vfs_root_name
    ON vfs_nodes(vfs_id, name) WHERE parent_id IS NULL;
";

const MIGRATION_030: &str = r#"
-- HLC (hybrid logical clock) conflict resolution for sync. The local changelog
-- gains an `hlc` column: the sync layer stamps each pending row with a minted
-- HLC before push (the trigger can't compute one), and the same value is the
-- "local pending" clock for conflict detection. `hlc_ledger` records the
-- committed HLC per (table, row_id), from our own pushes and applied remotes,
-- so a stale remote change (older HLC) can be dropped instead of clobbering a
-- newer local value (the prior blind last-writer-wins). Both are local-only
-- (not synced, no triggers); additive and idempotent.
ALTER TABLE sync_changelog ADD COLUMN hlc TEXT;
CREATE TABLE IF NOT EXISTS hlc_ledger (
    table_name TEXT NOT NULL,
    row_id     TEXT NOT NULL,
    hlc        TEXT NOT NULL,
    PRIMARY KEY (table_name, row_id)
);
"#;

const MIGRATION_031: &str = r#"
-- Single source of truth for "a sample the user can see". Soft-delete
-- (deleted_at set, row retained for the tombstone-retention window) is live, so
-- any membership/listing/count query that reads the join tables (tags,
-- collection_members, vfs_nodes) must exclude tombstoned samples. That filter
-- was opt-in per query and drifted: search.rs and rules.rs filtered, but
-- tags/collections/list_full_tree did not, surfacing deleted samples in tag and
-- collection views and recreating dangling mirror symlinks. Defining the filter
-- once as a view means callers say `FROM live_samples` and the predicate lives
-- in exactly one place. Local-only derived object; no triggers, not synced.
CREATE VIEW IF NOT EXISTS live_samples AS
    SELECT * FROM samples WHERE deleted_at IS NULL;
"#;

const MIGRATION_032: &str = r"
-- M018 recreated the audio_analysis sync triggers (to salt row_id) but copied
-- the pre-M011 column list, silently dropping spectral_bandwidth,
-- centroid_variance, crest_factor, and attack_time (and the since-retired
-- classification_confidence) from the sync_changelog payload. The initial
-- snapshot still carries them, but ongoing edits to those columns never
-- propagate across devices. Recreate
-- the insert/update triggers with M018's salted row_id AND the full column set.
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',
        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,
            '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',
        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,
            'spectral_bandwidth', NEW.spectral_bandwidth, 'centroid_variance', NEW.centroid_variance,
            'crest_factor', NEW.crest_factor, 'attack_time', NEW.attack_time));
END;
";

const MIGRATION_033: &str = r"
-- Generate the user_config export filter from the ConfigKey registry instead of
-- a hand-maintained trigger predicate. `config_key_policy` is seeded at every
-- open from `audiofiles_core::config_key::ConfigKey::ALL` (see
-- Database::seed_config_key_policy); the triggers below enqueue a changelog row
-- only for keys the registry marks replicated. This closes the CHRONIC where the
-- old `NEW.key != 'loose_files'` denylist never covered mirror_path /
-- mirror_enabled / import_preflight_disabled, letting a hostile server steer a
-- local filesystem write root across the sync boundary (fuzz-2026-07-21 #3).
-- Unknown keys are absent from the policy table and therefore never exported,
-- the filter fails closed.
CREATE TABLE IF NOT EXISTS config_key_policy (
    key TEXT PRIMARY KEY,
    replicated INTEGER NOT NULL
);

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 EXISTS (SELECT 1 FROM config_key_policy p WHERE p.key = NEW.key AND p.replicated = 1)
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 EXISTS (SELECT 1 FROM config_key_policy p WHERE p.key = NEW.key AND p.replicated = 1)
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 EXISTS (SELECT 1 FROM config_key_policy p WHERE p.key = OLD.key AND p.replicated = 1)
BEGIN
    INSERT INTO sync_changelog (table_name, op, row_id, data)
    VALUES ('user_config', 'DELETE', OLD.key, json_object('key', OLD.key));
END;
";

pub(super) const MIGRATION_034: &str = r"
-- Normalise musical_key to the '<note> major' / '<note> minor' spelling.
--
-- detect_bpm_key stored stratum_dsp's compact DJ-style name ('Am', 'C#m', 'C')
-- straight through with no normalisation, while search::compatible_keys matches
-- on ends_with('minor') against a table of '<note> minor' strings and
-- analysis::suggest builds its tag by replacing a space that was never there.
-- Key-compatible search therefore matched nothing and the tag came out as
-- 'key.am'. The detector now normalises at the boundary; this rewrites rows
-- written before that.
--
-- substr drops only the trailing minor marker; replace(.., 'm', '') would be
-- equivalent today only because no note name contains an 'm', which is not a
-- property worth depending on. Rows already canonical contain a space and are
-- excluded by the WHERE clause, so re-running is a no-op.
--
-- Suppressed from the changelog: audio_analysis carries a sync UPDATE trigger,
-- so without this every analysed sample would enqueue a row on upgrade. Worse
-- than the volume, replicating it is wrong. Every device runs this migration
-- itself and converges on the same value, while a peer still on the old code
-- would receive canonical values and keep writing 'Am' from its own detector,
-- leaving a vault holding both spellings. The UPDATE is a no-op when the key is
-- absent, which is the case on any vault where sync was never configured (the
-- row is seeded by the sync crate, not here), and the trigger's WHEN clause
-- compares NULL and never fires there either.
UPDATE sync_state SET value = '1' WHERE key = 'applying_remote';

UPDATE audio_analysis
SET musical_key = CASE
    WHEN musical_key LIKE '%m'
        THEN substr(musical_key, 1, length(musical_key) - 1) || ' minor'
    ELSE musical_key || ' major'
END
WHERE musical_key IS NOT NULL
  AND musical_key NOT LIKE '% major'
  AND musical_key NOT LIKE '% minor'
  AND musical_key != '';

UPDATE sync_state SET value = '0' WHERE key = 'applying_remote';
";

pub(super) const MIGRATION_035: &str = r"
-- Rewrite key.* tags written from the pre-M034 key spelling.
--
-- M034 fixed audio_analysis.musical_key, but tags are user-accepted copies of
-- what analysis::suggest proposed at the time, so a vault can still hold
-- 'key.am' and 'key.c-sharpm'. suggest builds the tag as
-- lowercase -> ' ' to '-' -> '#' to '-sharp', so the compact 'Am' became
-- 'key.am' and 'C#m' became 'key.c-sharpm', against 'key.a-minor' and
-- 'key.c-sharp-minor' now.
--
-- The 24 legacy spellings are enumerated rather than pattern-matched. A LIKE
-- rule broad enough to catch 'key.c' would also catch any user tag in the key
-- namespace, and silently mangling a hand-written tag is worse than leaving a
-- stale one.
CREATE TEMP TABLE legacy_key_tags (legacy TEXT PRIMARY KEY, canonical TEXT NOT NULL);
INSERT INTO legacy_key_tags (legacy, canonical) VALUES
    ('key.c',        'key.c-major'),
    ('key.c-sharp',  'key.c-sharp-major'),
    ('key.d',        'key.d-major'),
    ('key.d-sharp',  'key.d-sharp-major'),
    ('key.e',        'key.e-major'),
    ('key.f',        'key.f-major'),
    ('key.f-sharp',  'key.f-sharp-major'),
    ('key.g',        'key.g-major'),
    ('key.g-sharp',  'key.g-sharp-major'),
    ('key.a',        'key.a-major'),
    ('key.a-sharp',  'key.a-sharp-major'),
    ('key.b',        'key.b-major'),
    ('key.cm',       'key.c-minor'),
    ('key.c-sharpm', 'key.c-sharp-minor'),
    ('key.dm',       'key.d-minor'),
    ('key.d-sharpm', 'key.d-sharp-minor'),
    ('key.em',       'key.e-minor'),
    ('key.fm',       'key.f-minor'),
    ('key.f-sharpm', 'key.f-sharp-minor'),
    ('key.gm',       'key.g-minor'),
    ('key.g-sharpm', 'key.g-sharp-minor'),
    ('key.am',       'key.a-minor'),
    ('key.a-sharpm', 'key.a-sharp-minor'),
    ('key.bm',       'key.b-minor');

-- Suppressed for the same reason as M034; see the note there.
UPDATE sync_state SET value = '1' WHERE key = 'applying_remote';

-- Insert-then-delete rather than UPDATE: (sample_hash, tag) is the primary key,
-- and a sample already carrying both spellings would collide. OR IGNORE keeps
-- the existing canonical row in that case.
INSERT OR IGNORE INTO tags (sample_hash, tag)
SELECT t.sample_hash, m.canonical
FROM tags t
JOIN legacy_key_tags m ON t.tag = m.legacy;

DELETE FROM tags WHERE tag IN (SELECT legacy FROM legacy_key_tags);

UPDATE sync_state SET value = '0' WHERE key = 'applying_remote';

DROP TABLE legacy_key_tags;
";

const MIGRATION_036: &str = r"
-- Indexes for the measured browse axes, same reasoning as M028: a range filter
-- with no text query otherwise full-scans audio_analysis, and these three are
-- now first-class browse dimensions rather than columns nothing queried.
-- Additive and idempotent.
CREATE INDEX IF NOT EXISTS idx_analysis_spectral_centroid ON audio_analysis(spectral_centroid);
CREATE INDEX IF NOT EXISTS idx_analysis_spectral_flatness ON audio_analysis(spectral_flatness);
CREATE INDEX IF NOT EXISTS idx_analysis_attack_time ON audio_analysis(attack_time);
";

const MIGRATION_037: &str = r"
-- Cover the browse-list sort order, which is the worst-case list load: opening
-- the browser with no filter applied.
--
-- `search_global` and `search_dir` both end `ORDER BY n.node_type, n.name
-- LIMIT 500`, and nothing indexed that pair. SQLite therefore scanned every
-- vfs_nodes row, built a temp B-tree over all of them, sorted, and discarded
-- all but 500. SEARCH_RESULT_LIMIT bounds what comes back, never the work
-- underneath it, so the cost grew with the library while the result set did not.
--
-- Measured on a 40,200-node database, unfiltered `search_global`:
--   before  63.04 ms   SCAN n + USE TEMP B-TREE FOR ORDER BY
--   after    0.81 ms   SCAN n USING INDEX idx_vfs_nodes_sort, no temp B-tree
-- The sort disappears from the plan: SQLite walks the index in order and stops
-- once the LIMIT is met.
--
-- The index costs about 4.6 MB at 40k nodes, so roughly 34 MB at the 289k-node
-- library the extrapolation was aimed at. That is the trade, and it is the right
-- way round: the list load is on the path a user waits for, and disk is not.
--
-- Additive and idempotent.
CREATE INDEX IF NOT EXISTS idx_vfs_nodes_sort ON vfs_nodes(node_type, name);
";

const MIGRATION_038: &str = r"
-- The persisted k-nearest-neighbour graph over the similarity features.
--
-- Derived data, on the waveform_data model (M004): recomputable from
-- audio_analysis, and machine-dependent besides, because the distances are
-- normalized against this library's global feature ranges. So it carries no
-- sync triggers at all, where every hand-entered table near it carries three.
--
-- What it buys: a similarity query answers from a table read instead of a
-- VP-tree build, and the neighbourhood becomes a structure other things can be
-- built on (chains, regions, 'what is unlike everything') rather than a ranking
-- computed and thrown away per ask.
CREATE TABLE IF NOT EXISTS sample_neighbours (
    hash           TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
    neighbour_hash TEXT NOT NULL,
    distance       REAL NOT NULL,
    rank           INTEGER NOT NULL,
    PRIMARY KEY (hash, neighbour_hash)
);
CREATE INDEX IF NOT EXISTS idx_sample_neighbours_rank ON sample_neighbours(hash, rank);
-- The back-edge index. A delete has to find every source pointing AT the gone
-- sample, which is the one access this table makes against the grain of its
-- primary key.
CREATE INDEX IF NOT EXISTS idx_sample_neighbours_back ON sample_neighbours(neighbour_hash);

-- Single-row provenance: the k the edges were computed for, and the
-- normalization ranges they were computed under. A range that has since widened
-- invalidates every stored distance in principle, so the ranges are what the
-- refresh pass compares against to choose rebuild over incremental update.
CREATE TABLE IF NOT EXISTS neighbour_graph_meta (
    id       INTEGER PRIMARY KEY CHECK (id = 1),
    k        INTEGER NOT NULL,
    ranges   TEXT NOT NULL,
    stale    INTEGER NOT NULL DEFAULT 0,
    built_at INTEGER NOT NULL
);

-- Samples whose out-edges need recomputing: freshly analysed, or left short by
-- a deleted neighbour. Drained by the refresh pass on the next similarity
-- query, which is a point where a VP-tree is being built anyway. Persisted
-- rather than held in worker memory so a restart mid-import does not lose track
-- of which sources are behind.
CREATE TABLE IF NOT EXISTS neighbour_graph_dirty (
    hash TEXT PRIMARY KEY REFERENCES samples(hash) ON DELETE CASCADE
);

-- Out-edges cascade with the sample row. Back-edges do not: they name a hash
-- that is not the row's own, so no foreign key connects them to the delete.
-- Without this the table accumulates edges pointing at samples that are gone,
-- and the sources holding them are silently short of k.
CREATE TRIGGER IF NOT EXISTS neighbour_graph_delete_back_edges
AFTER DELETE ON samples
BEGIN
    INSERT OR IGNORE INTO neighbour_graph_dirty (hash)
        SELECT hash FROM sample_neighbours WHERE neighbour_hash = OLD.hash;
    DELETE FROM sample_neighbours WHERE neighbour_hash = OLD.hash;
END;
";

const MIGRATION_039: &str = r"
-- Clusters become first-class named objects.
--
-- Before this, `cluster_library` produced a grouping and then threw it away:
-- `apply_cluster_tag` wrote tags and the pile itself stopped existing. The
-- grouping was the valuable part. A persisted cluster is what answers 'what is
-- this pile' for the population the filename rules cannot answer at all.
--
-- Membership is derived, on the `sample_neighbours` model in M038: it is
-- recomputable from `sample_features`, and machine-dependent besides, because
-- k-means runs over features standardized against this library's own ranges.
-- So neither table carries sync triggers. The user's name is NOT derived, and
-- whether it should sync is a separate decision, filed rather than answered
-- here.
CREATE TABLE IF NOT EXISTS clusters (
    id              INTEGER PRIMARY KEY,
    -- NULL until the user names it. Naming is the whole point; an unnamed
    -- cluster is a pile still waiting for a word.
    name            TEXT,
    -- The member nearest the centroid: a playable representative, and the key a
    -- re-run matches on to carry the name across. Nullable because deleting the
    -- representative sample must not destroy the name the user typed.
    medoid_hash     TEXT REFERENCES samples(hash) ON DELETE SET NULL,
    -- The run that produced it: the k asked for, the feature extractor the
    -- vectors came from, and when. A cluster built under a stale
    -- `feature_version` is comparable to nothing built since.
    k               INTEGER NOT NULL,
    feature_version INTEGER NOT NULL,
    run_at          INTEGER NOT NULL,
    named_at        INTEGER
);
CREATE INDEX IF NOT EXISTS idx_clusters_medoid ON clusters(medoid_hash);

CREATE TABLE IF NOT EXISTS cluster_members (
    cluster_id  INTEGER NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
    sample_hash TEXT NOT NULL REFERENCES samples(hash) ON DELETE CASCADE,
    PRIMARY KEY (cluster_id, sample_hash)
);
-- 'which pile is this sample in' is the read the detail pane makes, and it runs
-- against the grain of the primary key.
CREATE INDEX IF NOT EXISTS idx_cluster_members_hash ON cluster_members(sample_hash);
";

/// 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.
pub(super) 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(())
}

/// Every migration, in order. Index + 1 is the `PRAGMA user_version` a database
/// carries once that migration has been applied, so the list's length is the
/// schema version this build produces.
///
/// At module scope rather than inside [`Database::migrate`] so [`SCHEMA_VERSION`]
/// can be derived from it: the guard against opening a newer vault and the
/// migration runner have to agree on one number, and deriving it is how they
/// cannot drift.
pub(super) 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,
    MIGRATION_020,
    MIGRATION_021,
    MIGRATION_022,
    MIGRATION_023,
    MIGRATION_024,
    MIGRATION_025,
    MIGRATION_026,
    MIGRATION_027,
    MIGRATION_028,
    MIGRATION_029,
    MIGRATION_030,
    MIGRATION_031,
    MIGRATION_032,
    MIGRATION_033,
    MIGRATION_034,
    MIGRATION_035,
    MIGRATION_036,
    MIGRATION_037,
    MIGRATION_038,
    MIGRATION_039,
];

/// The schema version this build produces, and the highest one it can read.
///
/// A vault reporting more than this was written by a newer audiofiles and is
/// refused; see [`DbError::VaultTooNew`].
pub const SCHEMA_VERSION: i32 = MIGRATIONS.len() as i32;