/**
 * MindDB substrate tests — ported from hive-mind/packages/core/src/mind/db.test.ts.
 *
 * Memory Sync Repair Step 2. Source: hive-mind file at HEAD c363257
 * (D:/Projects/hive-mind/packages/core/src/mind/db.test.ts).
 *
 * One adaptation vs the upstream file: the "creates the expected OSS
 * tables and omits the proprietary ones" test is split into two cases
 * here — the OSS-existence half is verbatim; the proprietary-absence
 * half is replaced with a Waggle-specific positive assertion that
 * exercises the same schema surface (proprietary tables MUST exist
 * here). This is intentional API divergence per EXTRACTION.md, not a
 * substrate bug. Tracked in the Step 2 results report as
 * "FAIL — API mismatch (Waggle-specific extension intentional)" if
 * un-adapted; this file ships the adaptation so the suite stays green.
 */
import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { rmSync, existsSync } from 'node:fs';
import { MindDB, EmbeddingDimMismatchError } from '../../src/mind/db.js';

describe('MindDB (hive-mind port)', () => {
  let dbPath: string;
  let db: MindDB | null;

  beforeEach(() => {
    dbPath = join(tmpdir(), `waggle-mind-test-${Date.now()}-${Math.random()}.mind`);
    db = new MindDB(dbPath);
  });

  afterEach(() => {
    db?.close();
    db = null;
    if (existsSync(dbPath)) rmSync(dbPath);
    // better-sqlite3 creates -shm and -wal sidecar files in WAL mode
    for (const suffix of ['-shm', '-wal']) {
      if (existsSync(dbPath + suffix)) rmSync(dbPath + suffix);
    }
  });

  it('initializes schema and records a first_run_at timestamp on first open', () => {
    const firstRun = db!.getFirstRunAt();
    expect(firstRun).not.toBeNull();
    expect(() => new Date(firstRun!).toISOString()).not.toThrow();
  });

  it('REOPENS a pre-D3 database (no content_hash column) without throwing — boot regression pin', () => {
    // 2026-06-12: every EXISTING install failed to boot ("no such column:
    // content_hash") because SCHEMA_SQL carried the content_hash INDEX — on an
    // old DB the CREATE TABLE no-ops and the index referenced a column only
    // the (later) guarded ALTER adds. Simulate a pre-D3 DB by dropping the
    // column + index, then reopen: migrations must restore both.
    const raw = db!.getDatabase();
    raw.exec('DROP INDEX IF EXISTS idx_frames_content_hash');
    raw.exec('ALTER TABLE memory_frames DROP COLUMN content_hash');
    db!.close();

    db = new MindDB(dbPath); // must not throw
    const cols = db!.getDatabase()
      .prepare("SELECT COUNT(*) as cnt FROM pragma_table_info('memory_frames') WHERE name='content_hash'")
      .get() as { cnt: number };
    expect(cols.cnt).toBe(1);
    const idx = db!.getDatabase()
      .prepare("SELECT COUNT(*) as cnt FROM sqlite_master WHERE type='index' AND name='idx_frames_content_hash'")
      .get() as { cnt: number };
    expect(idx.cnt).toBe(1);
  });

  it('creates the OSS shared-substrate tables (verbatim from hive-mind)', () => {
    const raw = db!.getDatabase();
    const tables = raw
      .prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
      .all() as { name: string }[];
    const names = new Set(tables.map((t) => t.name));

    // Core OSS surface — same expectation as hive-mind: these are the
    // tables that BOTH repos must carry to keep the sync workflow valid.
    for (const expected of [
      'meta',
      'identity',
      'awareness',
      'sessions',
      'memory_frames',
      'knowledge_entities',
      'knowledge_relations',
      'harvest_sources',
    ]) {
      expect(names.has(expected), `expected table ${expected}`).toBe(true);
    }
  });

  it('also creates the Waggle-specific extension tables (intentional API divergence)', () => {
    // hive-mind asserts these tables MUST be ABSENT (its OSS-scrub
    // guarantee). Waggle-os intentionally carries them as the
    // production-feature extensions per EXTRACTION.md. We invert the
    // assertion to keep coverage on the same surface but reflect the
    // legitimate divergence — surfacing accidental loss of these
    // tables would be a real waggle-os regression.
    const raw = db!.getDatabase();
    const tables = raw
      .prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
      .all() as { name: string }[];
    const names = new Set(tables.map((t) => t.name));

    for (const required of [
      'ai_interactions',
      'execution_traces',
      'evolution_runs',
      'improvement_signals',
      'install_audit',
    ]) {
      expect(names.has(required), `Waggle-specific table ${required} must exist`).toBe(true);
    }
  });

  it('supports the memory_frames + FTS5 + sqlite-vec pipeline', () => {
    const raw = db!.getDatabase();

    raw.prepare(
      "INSERT INTO sessions (gop_id, project_id) VALUES (?, ?)"
    ).run('gop-1', 'test-project');

    const insert = raw.prepare(
      `INSERT INTO memory_frames (frame_type, gop_id, content, importance, source)
       VALUES (?, ?, ?, ?, ?)`
    );
    insert.run('I', 'gop-1', 'User prefers TypeScript over JavaScript', 'important', 'user_stated');
    insert.run('I', 'gop-1', 'User uses vitest for testing', 'normal', 'user_stated');

    const countRow = raw
      .prepare('SELECT COUNT(*) as n FROM memory_frames')
      .get() as { n: number };
    expect(countRow.n).toBe(2);

    // vec0 virtual table accepts float[1024] embeddings. rowid must be
    // interpolated literally — vec0 rejects parameter-bound rowids.
    const embedding = new Float32Array(1024);
    for (let i = 0; i < 1024; i++) embedding[i] = Math.random();
    const embeddingBlob = new Uint8Array(
      embedding.buffer,
      embedding.byteOffset,
      embedding.byteLength
    );
    raw.prepare(
      `INSERT INTO memory_frames_vec (rowid, embedding) VALUES (1, ?)`
    ).run(embeddingBlob);

    const vecCountRow = raw
      .prepare('SELECT COUNT(*) as n FROM memory_frames_vec')
      .get() as { n: number };
    expect(vecCountRow.n).toBe(1);
  });

  it('runs migrations idempotently when reopening an existing database', () => {
    db!.close();
    db = new MindDB(dbPath);
    // No throw = migrations re-applied cleanly against existing schema.
    expect(db.getFirstRunAt()).not.toBeNull();
  });

  // Reverse-ported from OSS hive-mind (oss-drift triage R7, 2026-06-11).
  describe('embedding fingerprint guard', () => {
    it('ensureEmbeddingFingerprint records the fingerprint on first call, then matches', () => {
      const first = db!.ensureEmbeddingFingerprint({ provider: 'voyage', model: 'voyage-3-lite', dim: 1024 });
      expect(first.status).toBe('recorded');
      const second = db!.ensureEmbeddingFingerprint({ provider: 'voyage', model: 'voyage-3-lite', dim: 1024 });
      expect(second.status).toBe('match');
    });

    it('ensureEmbeddingFingerprint throws EmbeddingDimMismatchError on a dimension change', () => {
      db!.ensureEmbeddingFingerprint({ provider: 'voyage', model: 'voyage-3-lite', dim: 1024 });
      expect(() =>
        db!.ensureEmbeddingFingerprint({ provider: 'ollama', model: 'nomic-embed-text', dim: 768 }),
      ).toThrow(EmbeddingDimMismatchError);
      try {
        db!.ensureEmbeddingFingerprint({ provider: 'ollama', model: 'nomic-embed-text', dim: 768 });
      } catch (e) {
        const msg = (e as Error).message;
        expect(msg).toContain('1024'); // stored dim
        expect(msg).toContain('768'); // runtime dim
        expect(msg).toContain('recreateVecTables'); // points at the remediation
      }
    });

    it('ensureEmbeddingFingerprint warns but ALLOWS a same-dim model change', () => {
      db!.ensureEmbeddingFingerprint({ provider: 'voyage', model: 'voyage-3-lite', dim: 1024 });
      const changed = db!.ensureEmbeddingFingerprint({
        provider: 'openai',
        model: 'text-embedding-3-small',
        dim: 1024,
      });
      expect(changed.status).toBe('model-changed');
      if (changed.status === 'model-changed') {
        expect(changed.storedModel).toBe('voyage-3-lite');
        expect(changed.storedProvider).toBe('voyage');
      }
      // Fingerprint is updated to the new model, so a repeat now matches.
      const after = db!.ensureEmbeddingFingerprint({
        provider: 'openai',
        model: 'text-embedding-3-small',
        dim: 1024,
      });
      expect(after.status).toBe('match');
    });

    it('setEmbeddingFingerprint / getEmbeddingFingerprint round-trip', () => {
      expect(db!.getEmbeddingFingerprint()).toBeNull();
      db!.setEmbeddingFingerprint({ provider: 'ollama', model: 'nomic-embed-text', dim: 768 });
      expect(db!.getEmbeddingFingerprint()).toEqual({
        provider: 'ollama',
        model: 'nomic-embed-text',
        dim: 768,
      });
    });

    it('recreateVecTables rebuilds memory_frames_vec at a new dimension', () => {
      const raw = db!.getDatabase();
      const v1024 = new Float32Array(1024);
      raw
        .prepare('INSERT INTO memory_frames_vec (rowid, embedding) VALUES (1, ?)')
        .run(new Uint8Array(v1024.buffer));
      expect((raw.prepare('SELECT COUNT(*) n FROM memory_frames_vec').get() as { n: number }).n).toBe(1);

      db!.recreateVecTables(768);

      // Old rows are gone and the column is now 768-dim.
      expect((raw.prepare('SELECT COUNT(*) n FROM memory_frames_vec').get() as { n: number }).n).toBe(0);
      const v768 = new Float32Array(768);
      expect(() =>
        raw
          .prepare('INSERT INTO memory_frames_vec (rowid, embedding) VALUES (2, ?)')
          .run(new Uint8Array(v768.buffer)),
      ).not.toThrow();
      expect(() =>
        raw
          .prepare('INSERT INTO memory_frames_vec (rowid, embedding) VALUES (3, ?)')
          .run(new Uint8Array(v1024.buffer)),
      ).toThrow(); // 1024 no longer fits the 768 column
      // The stored dim fingerprint follows the recreation.
      expect(db!.getEmbeddingFingerprint()?.dim).toBe(768);
    });
  });

  // P2 cross-process hardening: the sidecar + memory-mcp open the same
  // ~/.waggle/personal.mind as separate processes, so a writer-writer clash or WAL
  // snapshot-upgrade race must not throw on first contact.
  describe('cross-process SQLite hardening', () => {
    it('applies an explicit busy_timeout pragma', () => {
      const timeout = db!.getDatabase().pragma('busy_timeout', { simple: true }) as number;
      expect(timeout).toBe(10_000);
    });

    it('runWithBusyRetry retries a transient SQLITE_BUSY then succeeds', () => {
      let calls = 0;
      const result = db!.runWithBusyRetry(() => {
        calls++;
        if (calls === 1) {
          const err = new Error('database is locked') as Error & { code: string };
          err.code = 'SQLITE_BUSY';
          throw err;
        }
        return 'ok';
      });
      expect(result).toBe('ok');
      expect(calls).toBe(2);
    });

    it('runWithBusyRetry also retries SQLITE_BUSY_SNAPSHOT (the WAL upgrade race)', () => {
      let calls = 0;
      const result = db!.runWithBusyRetry(() => {
        calls++;
        if (calls < 3) {
          const err = new Error('snapshot moved') as Error & { code: string };
          err.code = 'SQLITE_BUSY_SNAPSHOT';
          throw err;
        }
        return 42;
      });
      expect(result).toBe(42);
      expect(calls).toBe(3);
    });

    it('runWithBusyRetry propagates a non-BUSY error immediately (no retry)', () => {
      let calls = 0;
      expect(() => db!.runWithBusyRetry(() => {
        calls++;
        const err = new Error('constraint failed') as Error & { code: string };
        err.code = 'SQLITE_CONSTRAINT';
        throw err;
      })).toThrow('constraint failed');
      expect(calls).toBe(1);
    });

    it('runWithBusyRetry gives up after the bounded budget and rethrows the last BUSY', () => {
      let calls = 0;
      expect(() => db!.runWithBusyRetry(() => {
        calls++;
        const err = new Error('still locked') as Error & { code: string };
        err.code = 'SQLITE_BUSY';
        throw err;
      })).toThrow('still locked');
      expect(calls).toBe(5); // BUSY_RETRY_MAX_ATTEMPTS
    });

    it('runWithBusyRetry returns the value on the happy path without retrying', () => {
      let calls = 0;
      const result = db!.runWithBusyRetry(() => { calls++; return 'immediate'; });
      expect(result).toBe('immediate');
      expect(calls).toBe(1);
    });
  });
});