/**
 * Tests for SQLite State Management
 */

import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import * as fs from 'fs';
import * as path from 'path';
import * as os from 'os';
import {
  openStateDb,
  deleteStateDb,
  stateDbExists,
  searchEntities,
  searchEntitiesPrefix,
  getEntityByName,
  getEntitiesByAlias,
  getAllEntitiesFromDb,
  getEntityIndexFromDb,
  recordEntityMention,
  getEntityRecency,
  getAllRecency,
  setWriteState,
  getWriteState,
  deleteWriteState,
  getStateDbMetadata,
  isEntityDataStale,
  escapeFts5Query,
  FLYWHEEL_DIR,
  STATE_DB_FILENAME,
  SCHEMA_VERSION,
} from '../src/sqlite.js';
import type { StateDb } from '../src/sqlite.js';
import type { EntityIndex, EntityWithAliases } from '../src/types.js';

describe('SQLite State Management', () => {
  let testVaultPath: string;
  let stateDb: StateDb;

  beforeEach(() => {
    // Create a temporary directory for testing
    testVaultPath = fs.mkdtempSync(path.join(os.tmpdir(), 'vault-test-'));
  });

  afterEach(() => {
    // Close database and clean up
    if (stateDb) {
      stateDb.close();
    }
    if (fs.existsSync(testVaultPath)) {
      fs.rmSync(testVaultPath, { recursive: true });
    }
  });

  describe('Database Initialization', () => {
    it('should create database in .flywheel directory', () => {
      stateDb = openStateDb(testVaultPath);

      const expectedPath = path.join(testVaultPath, FLYWHEEL_DIR, STATE_DB_FILENAME);
      expect(fs.existsSync(expectedPath)).toBe(true);
    });

    it('should report database exists after creation', () => {
      expect(stateDbExists(testVaultPath)).toBe(false);
      stateDb = openStateDb(testVaultPath);
      expect(stateDbExists(testVaultPath)).toBe(true);
    });

    it('should delete database and WAL files', { timeout: 15_000 }, () => {
      stateDb = openStateDb(testVaultPath);
      stateDb.close();

      deleteStateDb(testVaultPath);
      expect(stateDbExists(testVaultPath)).toBe(false);
    });

    it('should have correct schema version', () => {
      stateDb = openStateDb(testVaultPath);
      const metadata = getStateDbMetadata(stateDb);
      expect(metadata.schemaVersion).toBe(SCHEMA_VERSION);
    });

    it('note_links has weight column with default 1.0', () => {
      stateDb = openStateDb(testVaultPath);
      const cols = stateDb.db.prepare(
        "SELECT name, dflt_value FROM pragma_table_info('note_links') WHERE name = 'weight'"
      ).get() as { name: string; dflt_value: string };
      expect(cols).toBeDefined();
      expect(cols.dflt_value).toBe('1.0');
    });

    it('note_links has weight_updated_at column', () => {
      stateDb = openStateDb(testVaultPath);
      const col = stateDb.db.prepare(
        "SELECT name FROM pragma_table_info('note_links') WHERE name = 'weight_updated_at'"
      ).get();
      expect(col).toBeDefined();
    });

    it('P42: wikilink_applications unique index collates note_path NOCASE', () => {
      stateDb = openStateDb(testVaultPath);
      // Insert a mixed-case duplicate pair. With NOCASE on note_path the
      // second insert violates the unique constraint and is rejected.
      stateDb.db.exec(`
        INSERT INTO wikilink_applications (entity, note_path)
          VALUES ('Flywheel', 'tech/flywheel/Flywheel.md');
      `);
      expect(() => {
        stateDb.db.exec(`
          INSERT INTO wikilink_applications (entity, note_path)
            VALUES ('flywheel', 'tech/flywheel/flywheel.md');
        `);
      }).toThrow(/UNIQUE constraint failed/);

      const rows = stateDb.db
        .prepare('SELECT COUNT(*) as c FROM wikilink_applications')
        .get() as { c: number };
      expect(rows.c).toBe(1);
    });

    it('P42: v42 migration upgrades a pre-owner_scope memories table without crashing', () => {
      // Regression test for the 2.12.6 startup crash: SCHEMA_SQL must not
      // contain index statements that reference a column added by a later
      // migration. A real user vault hit this when v2.12.6 booted on a v41
      // DB and `CREATE INDEX ... ON memories(key, owner_scope)` from
      // SCHEMA_SQL fired before the v42 migration could ALTER TABLE the
      // column in. Synthesize that state and assert the migration recovers.
      stateDb = openStateDb(testVaultPath);
      stateDb.db.exec('DROP INDEX IF EXISTS idx_memories_key_owner_scope');
      stateDb.db.exec('DROP INDEX IF EXISTS idx_memories_owner_scope');
      stateDb.db.exec('ALTER TABLE memories DROP COLUMN owner_scope');
      stateDb.db.exec('DELETE FROM schema_version WHERE version >= 42');
      stateDb.close();
      // ts-friendly clear so afterEach doesn't double-close.
      stateDb = undefined as unknown as StateDb;

      // Reopen — must not throw the "no such column: owner_scope" error.
      stateDb = openStateDb(testVaultPath);

      const col = stateDb.db
        .prepare("SELECT name FROM pragma_table_info('memories') WHERE name = 'owner_scope'")
        .get() as { name: string } | undefined;
      expect(col?.name).toBe('owner_scope');

      const indexes = stateDb.db
        .prepare(
          "SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'memories' AND name LIKE '%owner_scope%' ORDER BY name",
        )
        .all() as Array<{ name: string }>;
      expect(indexes.map(i => i.name)).toEqual([
        'idx_memories_key_owner_scope',
        'idx_memories_owner_scope',
      ]);

      const version = stateDb.db
        .prepare('SELECT MAX(version) as v FROM schema_version')
        .get() as { v: number };
      expect(version.v).toBeGreaterThanOrEqual(42);
    });

    it('P42: v39 migration collapses legacy mixed-case wikilink_applications rows', () => {
      // Hand-seed a pre-v39 state: drop the NOCASE index, recreate the v38
      // shape (only entity is COLLATE NOCASE), insert rows that collide
      // only on path casing, and rewind schema_version so the v39 migration
      // re-runs on reopen.
      stateDb = openStateDb(testVaultPath);
      stateDb.db.exec('DROP INDEX IF EXISTS idx_wl_apps_unique');
      stateDb.db.exec(`
        CREATE UNIQUE INDEX idx_wl_apps_unique
        ON wikilink_applications(entity COLLATE NOCASE, note_path)
      `);
      stateDb.db.exec(`
        INSERT INTO wikilink_applications (entity, note_path) VALUES
          ('EntA', 'tech/flywheel/Flywheel.md'),
          ('EntA', 'tech/flywheel/flywheel.md'),
          ('EntB', 'tech/flywheel/FLYWHEEL.md');
      `);
      stateDb.db.exec('DELETE FROM schema_version WHERE version >= 39');
      stateDb.close();

      stateDb = openStateDb(testVaultPath);
      // EntA rows collapse to one; EntB row stays distinct.
      const rows = stateDb.db
        .prepare(
          `SELECT entity, LOWER(note_path) AS p, COUNT(*) AS c
           FROM wikilink_applications
           GROUP BY LOWER(entity), LOWER(note_path)`,
        )
        .all() as Array<{ entity: string; p: string; c: number }>;
      const counts = Object.fromEntries(rows.map(r => [r.entity.toLowerCase(), r.c]));
      expect(counts.enta).toBe(1);
      expect(counts.entb).toBe(1);

      const version = stateDb.db
        .prepare('SELECT MAX(version) as v FROM schema_version')
        .get() as { v: number };
      expect(version.v).toBeGreaterThanOrEqual(39);

      // Re-inserting a mixed-case duplicate now fails the new NOCASE constraint.
      expect(() => {
        stateDb.db.exec(`
          INSERT INTO wikilink_applications (entity, note_path)
            VALUES ('enta', 'TECH/FLYWHEEL/FLYWHEEL.MD')
        `);
      }).toThrow(/UNIQUE constraint failed/);
    });
  });

  describe('Entity Operations', () => {
    beforeEach(() => {
      stateDb = openStateDb(testVaultPath);
    });

    it('should insert and retrieve entities', () => {
      const entity: EntityWithAliases = {
        name: 'TypeScript',
        path: 'tech/typescript.md',
        aliases: ['TS'],
        hubScore: 10,
      };

      stateDb.insertEntity.run(
        entity.name,
        entity.name.toLowerCase(),
        entity.path,
        'technologies',
        JSON.stringify(entity.aliases),
        entity.hubScore,
        null
      );

      const result = getEntityByName(stateDb, 'TypeScript');
      expect(result).not.toBeNull();
      expect(result!.name).toBe('TypeScript');
      expect(result!.category).toBe('technologies');
      expect(result!.aliases).toEqual(['TS']);
    });

    it('should search entities with FTS5', () => {
      // Insert test entities
      stateDb.insertEntity.run('TypeScript', 'typescript', 'tech/typescript.md', 'technologies', '["TS"]', 10, null);
      stateDb.insertEntity.run('JavaScript', 'javascript', 'tech/javascript.md', 'technologies', '["JS"]', 8, null);
      stateDb.insertEntity.run('Python', 'python', 'tech/python.md', 'technologies', '[]', 5, null);

      // Search for "technologies" - should match all three
      const results = searchEntities(stateDb, 'technologies');
      expect(results.length).toBe(3);

      // Search for exact word "typescript"
      const tsResults = searchEntities(stateDb, 'typescript');
      expect(tsResults.length).toBe(1);
      expect(tsResults[0].name).toBe('TypeScript');
    });

    it('should search entities by prefix', () => {
      stateDb.insertEntity.run('TypeScript', 'typescript', 'tech/typescript.md', 'technologies', '[]', 10, null);
      stateDb.insertEntity.run('Types', 'types', 'tech/types.md', 'concepts', '[]', 5, null);
      stateDb.insertEntity.run('Python', 'python', 'tech/python.md', 'technologies', '[]', 5, null);

      const results = searchEntitiesPrefix(stateDb, 'type');
      expect(results.length).toBe(2);
      expect(results.some(r => r.name === 'TypeScript')).toBe(true);
      expect(results.some(r => r.name === 'Types')).toBe(true);
    });

    it('should replace all entities from EntityIndex', () => {
      const index: EntityIndex = {
        technologies: [
          { name: 'React', path: 'tech/react.md', aliases: ['ReactJS'] },
          { name: 'Vue', path: 'tech/vue.md', aliases: [] },
        ],
        acronyms: [],
        people: [
          { name: 'John Doe', path: 'people/john.md', aliases: ['JD'] },
        ],
        projects: [],
        organizations: [],
        locations: [],
        concepts: [],
        animals: [],
        media: [],
        events: [],
        documents: [],
        vehicles: [],
        health: [],
        finance: [],
        food: [],
        hobbies: [],
        other: [],
        _metadata: {
          total_entities: 3,
          generated_at: new Date().toISOString(),
          vault_path: testVaultPath,
          source: 'test',
        },
      };

      const count = stateDb.replaceAllEntities(index);
      expect(count).toBe(3);

      const allEntities = getAllEntitiesFromDb(stateDb);
      expect(allEntities.length).toBe(3);

      const metadata = getStateDbMetadata(stateDb);
      expect(metadata.entityCount).toBe(3);
    });

    it('should convert database entities back to EntityIndex', () => {
      const index: EntityIndex = {
        technologies: [{ name: 'React', path: 'tech/react.md', aliases: [] }],
        acronyms: [{ name: 'API', path: 'concepts/api.md', aliases: [] }],
        people: [],
        projects: [],
        organizations: [],
        locations: [],
        concepts: [],
        animals: [],
        media: [],
        events: [],
        documents: [],
        vehicles: [],
        health: [],
        finance: [],
        food: [],
        hobbies: [],
        other: [],
        _metadata: {
          total_entities: 2,
          generated_at: new Date().toISOString(),
          vault_path: testVaultPath,
          source: 'test',
        },
      };

      stateDb.replaceAllEntities(index);

      const result = getEntityIndexFromDb(stateDb);
      expect(result.technologies.length).toBe(1);
      expect(result.acronyms.length).toBe(1);
      expect(result._metadata.total_entities).toBe(2);
    });
  });

  describe('Entity Alias Lookup', () => {
    beforeEach(() => {
      stateDb = openStateDb(testVaultPath);
    });

    it('should find entities by alias', () => {
      stateDb.insertEntity.run('TypeScript', 'typescript', 'tech/typescript.md', 'technologies', '["TS", "typescript"]', 10, null);
      stateDb.insertEntity.run('JavaScript', 'javascript', 'tech/javascript.md', 'technologies', '["JS"]', 8, null);

      const results = getEntitiesByAlias(stateDb, 'TS');
      expect(results.length).toBe(1);
      expect(results[0].name).toBe('TypeScript');
    });

    it('should be case-insensitive', () => {
      stateDb.insertEntity.run('TypeScript', 'typescript', 'tech/typescript.md', 'technologies', '["TS"]', 10, null);

      const results = getEntitiesByAlias(stateDb, 'ts');
      expect(results.length).toBe(1);
      expect(results[0].name).toBe('TypeScript');
    });

    it('should find multiple entities with the same alias', () => {
      stateDb.insertEntity.run('Production Environment', 'production environment', 'ops/prod.md', 'concepts', '["production"]', 5, null);
      stateDb.insertEntity.run('Production Line', 'production line', 'manufacturing/line.md', 'concepts', '["production"]', 3, null);

      const results = getEntitiesByAlias(stateDb, 'production');
      expect(results.length).toBe(2);
    });

    it('should return empty array for non-existent alias', () => {
      stateDb.insertEntity.run('TypeScript', 'typescript', 'tech/typescript.md', 'technologies', '["TS"]', 10, null);

      const results = getEntitiesByAlias(stateDb, 'nonexistent');
      expect(results.length).toBe(0);
    });

    it('should handle null aliases_json', () => {
      stateDb.insertEntity.run('TypeScript', 'typescript', 'tech/typescript.md', 'technologies', null, 10, null);

      const results = getEntitiesByAlias(stateDb, 'typescript');
      expect(results.length).toBe(0);
    });
  });

  describe('Recency Operations', () => {
    beforeEach(() => {
      stateDb = openStateDb(testVaultPath);
    });

    it('should record entity mentions', () => {
      const now = new Date();
      recordEntityMention(stateDb, 'TypeScript', now);

      const recency = getEntityRecency(stateDb, 'TypeScript');
      expect(recency).not.toBeNull();
      expect(recency!.mentionCount).toBe(1);
      expect(recency!.lastMentionedAt).toBe(now.getTime());
    });

    it('should increment mention count on subsequent mentions', () => {
      recordEntityMention(stateDb, 'TypeScript');
      recordEntityMention(stateDb, 'TypeScript');
      recordEntityMention(stateDb, 'TypeScript');

      const recency = getEntityRecency(stateDb, 'TypeScript');
      expect(recency!.mentionCount).toBe(3);
    });

    it('should get all recency data ordered by most recent', () => {
      recordEntityMention(stateDb, 'TypeScript', new Date(1000));
      recordEntityMention(stateDb, 'React', new Date(3000));
      recordEntityMention(stateDb, 'Vue', new Date(2000));

      const all = getAllRecency(stateDb);
      expect(all.length).toBe(3);
      expect(all[0].entityNameLower).toBe('react');
      expect(all[1].entityNameLower).toBe('vue');
      expect(all[2].entityNameLower).toBe('typescript');
    });
  });

  describe('Write State Operations', () => {
    beforeEach(() => {
      stateDb = openStateDb(testVaultPath);
    });

    it('should store and retrieve write state', () => {
      const state = {
        hash: 'abc123',
        message: 'Test commit',
        timestamp: Date.now(),
      };

      setWriteState(stateDb, 'last_commit', state);

      const result = getWriteState<typeof state>(stateDb, 'last_commit');
      expect(result).toEqual(state);
    });

    it('should update existing write state', () => {
      setWriteState(stateDb, 'key1', { value: 1 });
      setWriteState(stateDb, 'key1', { value: 2 });

      const result = getWriteState<{ value: number }>(stateDb, 'key1');
      expect(result!.value).toBe(2);
    });

    it('should delete write state', () => {
      setWriteState(stateDb, 'temp_key', { data: 'temp' });
      expect(getWriteState(stateDb, 'temp_key')).not.toBeNull();

      deleteWriteState(stateDb, 'temp_key');
      expect(getWriteState(stateDb, 'temp_key')).toBeNull();
    });
  });

  describe('Metadata and Staleness', () => {
    beforeEach(() => {
      stateDb = openStateDb(testVaultPath);
    });

    it('should report data as stale when no entities built', () => {
      expect(isEntityDataStale(stateDb)).toBe(true);
    });

    it('should not report data as stale immediately after build', () => {
      const index: EntityIndex = {
        technologies: [{ name: 'Test', path: 'test.md', aliases: [] }],
        acronyms: [],
        people: [],
        projects: [],
        organizations: [],
        locations: [],
        concepts: [],
        animals: [],
        media: [],
        events: [],
        documents: [],
        vehicles: [],
        health: [],
        finance: [],
        food: [],
        hobbies: [],
        other: [],
        _metadata: {
          total_entities: 1,
          generated_at: new Date().toISOString(),
          vault_path: testVaultPath,
          source: 'test',
        },
      };

      stateDb.replaceAllEntities(index);
      expect(isEntityDataStale(stateDb, 60 * 60 * 1000)).toBe(false);
    });
  });

  describe('FTS5 Query Escaping', () => {
    it('should strip special characters and OR-join quoted tokens', () => {
      expect(escapeFts5Query('test (query)')).toBe('"test" OR "query"');
      expect(escapeFts5Query('test: value')).toBe('"test" OR "value"');
    });

    it('should extract quoted phrases and OR-join remaining tokens', () => {
      expect(escapeFts5Query('test "value"')).toBe('"value" "test"');
      expect(escapeFts5Query('"project alpha" beta gamma')).toBe('"project alpha" "beta" OR "gamma"');
    });

    it('should wrap single tokens in quotes', () => {
      expect(escapeFts5Query('test')).toBe('"test"');
    });

    it('should preserve prefix-search * outside the quote (regression)', () => {
      expect(escapeFts5Query('test' + '*')).toBe('"test"*');
    });

    it('should skip explicit boolean operators', () => {
      expect(escapeFts5Query('test AND value')).toBe('"test" OR "value"');
      expect(escapeFts5Query('test OR value')).toBe('"test" OR "value"');
    });

    // Regression: 2026-05-08 morning-briefing job failed with `no such column: 05`
    // because the date `2026-05-08` was unquoted, so SQLite parsed `05` as a
    // column reference. With the fix each numeric token is quoted as a phrase.
    it('should safely handle date-formatted queries', () => {
      const result = escapeFts5Query('2026-05-08');
      expect(result).toBe('"2026" OR "05" OR "08"');
      expect(result).not.toContain(' 05 ');
      expect(result).not.toMatch(/^05/);
    });

    it('should safely handle dotted filenames', () => {
      expect(escapeFts5Query('file.txt')).toBe('"file" OR "txt"');
    });

    it('should safely handle plus-containing tokens like C++', () => {
      // The strip pass removes `+`, leaving the bare letter; quoting prevents
      // SQLite from interpreting it as anything other than a literal.
      expect(escapeFts5Query('C++')).toBe('"C"');
    });

    it('should filter the FTS5 NEAR keyword to prevent operator injection', () => {
      // NEAR alone is filtered, leaving an empty query.
      expect(escapeFts5Query('NEAR')).toBe('');
      // NEAR mixed with real tokens: NEAR is dropped, others quoted.
      expect(escapeFts5Query('NEAR foo bar')).toBe('"foo" OR "bar"');
    });
  });

  describe('Bulk Operations Performance', () => {
    beforeEach(() => {
      stateDb = openStateDb(testVaultPath);
    });

    it('should efficiently insert 1000 entities', () => {
      const entities: EntityWithAliases[] = Array.from({ length: 1000 }, (_, i) => ({
        name: `Entity${i}`,
        path: `entities/entity${i}.md`,
        aliases: [`E${i}`, `Ent${i}`],
        hubScore: Math.floor(Math.random() * 100),
      }));

      const start = performance.now();
      const count = stateDb.bulkInsertEntities(entities, 'concepts');
      const duration = performance.now() - start;

      expect(count).toBe(1000);
      expect(duration).toBeLessThan(1000); // Should complete in under 1s
    });

    it('should efficiently search 10k entities', () => {
      // Insert 10k entities
      const categories = ['technologies', 'concepts', 'people', 'projects', 'other'] as const;
      for (const category of categories) {
        const entities: EntityWithAliases[] = Array.from({ length: 2000 }, (_, i) => ({
          name: `${category.slice(0, 4)}${i}`,
          path: `${category}/${category}${i}.md`,
          aliases: [],
          hubScore: 0,
        }));
        stateDb.bulkInsertEntities(entities, category);
      }

      // Search should be fast - use prefix search for "tech*"
      const start = performance.now();
      const results = searchEntitiesPrefix(stateDb, 'tech');
      const duration = performance.now() - start;

      expect(results.length).toBeGreaterThan(0);
      expect(duration).toBeLessThan(100); // Should complete in under 100ms
    });
  });
});
