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

use std::path::Path;

use rusqlite::Connection;
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 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 idx_analysis_duration ON audio_analysis(duration);
CREATE INDEX idx_analysis_classification ON audio_analysis(classification);
CREATE INDEX idx_samples_name ON samples(original_name);
"#;

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

const MIGRATION_006: &str = r#"
CREATE TABLE 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 sync_state (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);
INSERT 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 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 idx_changelog_pushed ON sync_changelog(pushed);

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

-- samples
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));
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));
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', OLD.hash, NULL);
END;

-- audio_analysis
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));
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));
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', OLD.hash, NULL);
END;

-- vfs
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), NULL);
END;

-- vfs_nodes
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), NULL);
END;

-- tags
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', 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', OLD.sample_hash || ':' || OLD.tag, NULL);
END;

-- collections
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));
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));
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), NULL);
END;

-- collection_members
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',
        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',
        CAST(OLD.collection_id AS TEXT) || ':' || OLD.sample_hash, NULL);
END;

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

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

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

-- user_config (exclude sync-internal keys)
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_%'
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_%'
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_%'
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 idx_edit_history_source ON edit_history(source_hash);
CREATE INDEX idx_edit_history_result ON edit_history(result_hash);

-- Sync trigger for edit_history
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;
"#;

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;
"#;

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);",
        )?;
        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;")?;
        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,
        ];

        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") => {
                        // Partial prior migration left some columns already added.
                        // Re-run each ALTER TABLE individually, skipping duplicates.
                        let _ = self.conn.execute_batch("ROLLBACK");
                        self.conn.execute_batch("BEGIN")?;
                        for line in sql.lines() {
                            let trimmed = line.trim();
                            if trimmed.to_uppercase().starts_with("ALTER TABLE")
                                && trimmed.to_uppercase().contains("ADD COLUMN")
                            {
                                if let Err(alter_err) = self.conn.execute_batch(trimmed) {
                                    if !alter_err.to_string().contains("duplicate column") {
                                        let _ = self.conn.execute_batch("ROLLBACK");
                                        return Err(DbError::Sqlite(alter_err));
                                    }
                                }
                            } else if !trimmed.is_empty() && !trimmed.starts_with("--") {
                                // Non-ALTER statements (CREATE TABLE, triggers, etc.)
                                // Use execute_batch to handle multi-line statements
                                // that may span multiple lines.
                            }
                        }
                        // Re-run the full batch minus ALTER TABLEs for triggers/tables
                        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() {
                            // Ignore "already exists" errors from prior partial runs;
                            // log anything else as a warning.
                            if let Err(e) = self.conn.execute_batch(&non_alter) {
                                let msg = e.to_string();
                                if !msg.contains("already exists") {
                                    tracing::warn!(
                                        migration = target,
                                        "Non-ALTER migration statement failed: {msg}"
                                    );
                                }
                            }
                        }
                        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).
    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",
            [],
            |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, 17);
    }

    #[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, 17);
    }

    #[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);
    }
}