cinchcli-core 0.1.2

use rusqlite::Connection;

pub const CURRENT_SCHEMA_VERSION: i64 = 1;

pub fn apply_migrations(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);",
    )?;
    let current: i64 = conn
        .query_row(
            "SELECT CAST(value AS INTEGER) FROM meta WHERE key='schema_version'",
            [],
            |r| r.get(0),
        )
        .unwrap_or(0);

    if current < 1 {
        migrate_v1(conn)?;
    }
    // Future: if current < 2 { migrate_v2(conn)?; }
    Ok(())
}

fn migrate_v1(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch(
        r#"
        CREATE TABLE clips (
          id           TEXT PRIMARY KEY,
          source       TEXT NOT NULL,
          source_key   TEXT,
          content_type TEXT NOT NULL,
          content      BLOB,
          media_path   TEXT,
          byte_size    INTEGER NOT NULL DEFAULT 0,
          created_at   INTEGER NOT NULL,
          pinned       INTEGER NOT NULL DEFAULT 0,
          pinned_at    INTEGER
        );
        CREATE INDEX clips_created_idx ON clips(created_at DESC);
        CREATE INDEX clips_source_idx  ON clips(source, created_at DESC);
        CREATE INDEX clips_pinned_idx  ON clips(pinned) WHERE pinned = 1;

        CREATE VIRTUAL TABLE clips_fts USING fts5(
            content, content='clips', content_rowid='rowid'
        );

        CREATE TRIGGER clips_ai AFTER INSERT ON clips BEGIN
          INSERT INTO clips_fts(rowid, content) VALUES (new.rowid, COALESCE(new.content, ''));
        END;
        CREATE TRIGGER clips_ad AFTER DELETE ON clips BEGIN
          INSERT INTO clips_fts(clips_fts, rowid, content) VALUES('delete', old.rowid, COALESCE(old.content, ''));
        END;
        CREATE TRIGGER clips_au AFTER UPDATE ON clips BEGIN
          INSERT INTO clips_fts(clips_fts, rowid, content) VALUES('delete', old.rowid, COALESCE(old.content, ''));
          INSERT INTO clips_fts(rowid, content)            VALUES (new.rowid, COALESCE(new.content, ''));
        END;

        CREATE TABLE devices (
          id           TEXT PRIMARY KEY,
          hostname     TEXT NOT NULL,
          nickname     TEXT,
          source_key   TEXT,
          machine_id   TEXT,
          public_key   TEXT,
          paired_at    INTEGER,
          last_push_at INTEGER,
          online       INTEGER NOT NULL DEFAULT 0,
          refreshed_at INTEGER NOT NULL
        );

        CREATE TABLE retention_prefs (device_id TEXT PRIMARY KEY, days INTEGER NOT NULL);
        CREATE TABLE alert_prefs     (source    TEXT PRIMARY KEY, enabled INTEGER NOT NULL);

        INSERT INTO meta(key, value) VALUES('schema_version', '1');
    "#,
    )?;
    Ok(())
}