asurada 0.1.0

// brain.db 스키마 마이그레이션.
//
// 한 곳에서 모든 마이그레이션을 정의 — 사용자 머신마다 자동 적용.
// 추가 시: SCHEMA_VERSION 올리고 MIGRATIONS 에 추가. 절대 기존 버전 SQL 수정 금지.

use anyhow::{Context, Result};
use rusqlite::Connection;

const SCHEMA_VERSION_TABLE: &str = r#"
CREATE TABLE IF NOT EXISTS _schema_version (
    version INTEGER PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);
"#;

const MIGRATIONS: &[&str] = &[
    // v1: memories core + FTS5
    r#"
    CREATE TABLE IF NOT EXISTS memories (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        text TEXT NOT NULL,
        scope TEXT NOT NULL,        -- 'user' | 'project' | 'tech'
        priority TEXT NOT NULL,     -- 'constraint' | 'strong' | 'preference' | 'info'
        source TEXT NOT NULL,       -- 'user' | 'asurada' | 'imported'
        project TEXT,
        tech TEXT,
        metadata TEXT NOT NULL DEFAULT '{}',
        status TEXT NOT NULL DEFAULT 'active',  -- 'active' | 'proposed' | 'archived' | 'deleted'
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        deleted_at TEXT
    );

    CREATE INDEX IF NOT EXISTS idx_memories_user ON memories(user_id);
    CREATE INDEX IF NOT EXISTS idx_memories_user_scope ON memories(user_id, scope);
    CREATE INDEX IF NOT EXISTS idx_memories_user_status ON memories(user_id, status);
    CREATE INDEX IF NOT EXISTS idx_memories_user_project ON memories(user_id, project);

    -- FTS5 가상 테이블: text 컬럼 키워드 검색.
    CREATE VIRTUAL TABLE IF NOT EXISTS memories_fts USING fts5(
        text,
        content='memories',
        content_rowid='rowid',
        tokenize='unicode61 remove_diacritics 2'
    );

    -- 트리거로 memories 변경 시 fts 자동 동기화.
    CREATE TRIGGER IF NOT EXISTS memories_ai AFTER INSERT ON memories BEGIN
        INSERT INTO memories_fts(rowid, text) VALUES (new.rowid, new.text);
    END;
    CREATE TRIGGER IF NOT EXISTS memories_ad AFTER DELETE ON memories BEGIN
        INSERT INTO memories_fts(memories_fts, rowid, text) VALUES('delete', old.rowid, old.text);
    END;
    CREATE TRIGGER IF NOT EXISTS memories_au AFTER UPDATE ON memories BEGIN
        INSERT INTO memories_fts(memories_fts, rowid, text) VALUES('delete', old.rowid, old.text);
        INSERT INTO memories_fts(rowid, text) VALUES (new.rowid, new.text);
    END;
    "#,
    // v2: events / advice / projects + sync metadata.
    //
    // 양방향 sync (brain.db ↔ Supabase Postgres) 를 위한 메타데이터:
    //   - synced_at: 마지막으로 클라우드와 일치한 시점. NULL = 아직 push 안 됨.
    //   - 각 row 의 updated_at > synced_at 이면 push 후보.
    //   - cloud 의 updated_at > local synced_at 이면 pull 적용.
    r#"
    -- v1 의 memories 에도 sync metadata 추가
    ALTER TABLE memories ADD COLUMN synced_at TEXT;
    CREATE INDEX IF NOT EXISTS idx_memories_unsynced
        ON memories(synced_at) WHERE synced_at IS NULL;

    -- 파일 변경 / 활동 이벤트 (Devist 가 INSERT, Asurada 가 처리)
    CREATE TABLE IF NOT EXISTS events (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        project TEXT NOT NULL,
        event_type TEXT NOT NULL,           -- 'file_changed' | 'file_created' | 'file_deleted' | 'scan' | etc.
        path TEXT,
        payload TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        synced_at TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_events_user ON events(user_id);
    CREATE INDEX IF NOT EXISTS idx_events_project ON events(user_id, project);
    CREATE INDEX IF NOT EXISTS idx_events_unsynced
        ON events(synced_at) WHERE synced_at IS NULL;
    CREATE INDEX IF NOT EXISTS idx_events_created ON events(created_at);

    -- 어드바이스 (Asurada 생성, 사용자가 confirm/intentional 등으로 처리)
    CREATE TABLE IF NOT EXISTS advice (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        project TEXT NOT NULL,
        text TEXT NOT NULL,
        severity TEXT NOT NULL,             -- info | suggest | warn | block
        paths TEXT NOT NULL DEFAULT '[]',   -- JSON array of relative paths
        verifiable INTEGER NOT NULL DEFAULT 0,
        state TEXT NOT NULL DEFAULT 'pending',  -- pending | requested | running | verifying | done | error
        confirmed_at TEXT,
        confirmed_by TEXT,                  -- 'user' | 'apply' | 'verify' | 'audit' | 'intentional'
        metadata TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        synced_at TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_advice_user ON advice(user_id);
    CREATE INDEX IF NOT EXISTS idx_advice_user_project ON advice(user_id, project);
    CREATE INDEX IF NOT EXISTS idx_advice_unsynced
        ON advice(synced_at) WHERE synced_at IS NULL;
    CREATE INDEX IF NOT EXISTS idx_advice_pending
        ON advice(user_id, confirmed_at) WHERE confirmed_at IS NULL;

    -- 프로젝트 레지스트리 (Devist 가 관리, Asurada 가 sync)
    CREATE TABLE IF NOT EXISTS projects (
        user_id TEXT NOT NULL,
        name TEXT NOT NULL,
        path TEXT NOT NULL,
        metadata TEXT NOT NULL DEFAULT '{}',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        synced_at TEXT,
        PRIMARY KEY (user_id, name)
    );
    CREATE INDEX IF NOT EXISTS idx_projects_unsynced
        ON projects(synced_at) WHERE synced_at IS NULL;

    -- Sync 상태: 테이블별 마지막 push/pull 시점
    CREATE TABLE IF NOT EXISTS _sync_state (
        table_name TEXT PRIMARY KEY,
        last_pushed_at TEXT,
        last_pulled_at TEXT,
        updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    "#,
];

pub fn migrate(conn: &Connection) -> Result<()> {
    conn.execute_batch(SCHEMA_VERSION_TABLE)
        .context("create _schema_version")?;

    let current: i64 = conn
        .query_row(
            "SELECT COALESCE(MAX(version), 0) FROM _schema_version",
            [],
            |row| row.get(0),
        )
        .unwrap_or(0);

    for (idx, sql) in MIGRATIONS.iter().enumerate() {
        let version = (idx + 1) as i64;
        if version <= current {
            continue;
        }
        conn.execute_batch(sql)
            .with_context(|| format!("apply migration v{}", version))?;
        conn.execute(
            "INSERT INTO _schema_version (version) VALUES (?1)",
            [version],
        )?;
        tracing::info!("applied migration v{}", version);
    }
    Ok(())
}