journaler/lib/db.dart

import 'dart:io' show Platform, Directory;
import 'package:flutter/material.dart';
import 'package:path/path.dart' as path;
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

const settingsDir = '.journaler';
const dbFileName = 'journaler.db';

// Add this at the top level
typedef ShowMessageCallback = void Function(String message);

class DB {
  static late Database db;
  static bool _hasIcuSupport = false;
  static ShowMessageCallback? _showMessage;

  // Add this to register the callback
  static void registerMessageCallback(ShowMessageCallback callback) {
    _showMessage = callback;
  }

  // Add this method to check ICU status
  static bool get hasIcuSupport => _hasIcuSupport;

  static const String _baseSchema = '''
CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  date TEXT DEFAULT CURRENT_TIMESTAMP,
  content TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_notes_date ON notes (date);
CREATE UNIQUE INDEX IF NOT EXISTS idx_notes_date_unique ON notes (date);

CREATE TABLE IF NOT EXISTS scratches (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  date TEXT DEFAULT CURRENT_TIMESTAMP,
  content TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_scratches_date ON scratches (date);
CREATE UNIQUE INDEX IF NOT EXISTS idx_scratches_date_unique ON scratches (date);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY NOT NULL,
  value TEXT NOT NULL
);
''';

  static const String _ftsSchemaWithIcu = '''
-- Create virtual FTS5 table with Unicode tokenizer
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
  content,
  date,
  content='notes',
  content_rowid='id',
  tokenize='unicode61 remove_diacritics 2 tokenchars "\u0401\u0451\u0410-\u044f"'
);
''';

  static const String _ftsSchemaBasic = '''
-- Create virtual FTS5 table with basic tokenizer
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
  content,
  date,
  content='notes',
  content_rowid='id',
  tokenize='unicode61'
);
''';

  static const String _ftsTriggers = '''
-- Trigger to keep FTS table in sync with notes table when inserting
CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, content, date) VALUES (new.id, new.content, new.date);
END;

-- Trigger to keep FTS table in sync when deleting notes
CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
  DELETE FROM notes_fts WHERE rowid = old.id;
END;

-- Trigger to keep FTS table in sync when updating notes
CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
  UPDATE notes_fts SET content = new.content, date = new.date WHERE rowid = old.id;
END;
''';

  // Add this method to check ICU status with visible feedback
  static Future<bool> checkAndShowIcuStatus() async {
    final status = _hasIcuSupport;
    final message =
        status
            ? 'ICU support is enabled - Full Unicode search available'
            : 'ICU support is not available - Unicode search will be limited\n'
                'Looking for sqlite3_icu.dll in the application directory';
    debugPrint(message);
    _showMessage?.call(message);
    return status;
  }

  static Future<bool> _checkIcuSupport(Database db) async {
    try {
      debugPrint(
        '\n================== CHECKING UNICODE SUPPORT ==================',
      );

      // Test Unicode support with a simple query
      try {
        debugPrint('Testing Unicode support...');
        // Test with some Cyrillic characters
        await db.rawQuery("SELECT 'тест' LIKE '%ест%'");
        await db.rawQuery("SELECT 'ТЕСТ' LIKE '%ЕСТ%'");

        final message = 'Unicode support is available';
        debugPrint(message);
        _showMessage?.call(message);
        return true;
      } catch (e, stackTrace) {
        final message = 'Unicode support test failed';
        debugPrint('$message:');
        debugPrint('Error: $e');
        debugPrint('Stack trace: $stackTrace');

        // Try to get SQLite version and compile options for debugging
        try {
          final version = await db.rawQuery('SELECT sqlite_version()');
          final compileOpts = await db.rawQuery('PRAGMA compile_options');
          debugPrint('SQLite version: $version');
          debugPrint('SQLite compile options: $compileOpts');
        } catch (e) {
          debugPrint('Could not get SQLite info: $e');
        }

        _showMessage?.call(message);
        return false;
      }
    } catch (e, stackTrace) {
      final message = 'Failed to test Unicode support';
      debugPrint('$message:');
      debugPrint('Error: $e');
      debugPrint('Stack trace: $stackTrace');
      _showMessage?.call(message);
      return false;
    } finally {
      debugPrint('=====================================================\n');
    }
  }

  static Future<void> _recreateFtsTable(Database db, bool useIcu) async {
    debugPrint('Updating FTS table configuration...');

    try {
      // Start a transaction to ensure data safety
      await db.transaction((txn) async {
        // First, create a temporary table with the new configuration
        final tempTableName = 'notes_fts_temp';
        final schema = useIcu ? _ftsSchemaWithIcu : _ftsSchemaBasic;

        // Create temp table with new configuration
        await txn.execute(schema.replaceAll('notes_fts', tempTableName));

        // Copy data from old FTS table if it exists
        try {
          debugPrint('Copying existing FTS data to temporary table...');
          await txn.execute('''
            INSERT INTO $tempTableName(rowid, content, date)
            SELECT rowid, content, date FROM notes_fts
          ''');
          debugPrint('Data copied successfully');
        } catch (e) {
          debugPrint('No existing FTS data to copy: $e');
        }

        // Drop old triggers
        debugPrint('Updating triggers...');
        await txn.execute('DROP TRIGGER IF EXISTS notes_ai');
        await txn.execute('DROP TRIGGER IF EXISTS notes_ad');
        await txn.execute('DROP TRIGGER IF EXISTS notes_au');

        // Drop old FTS table
        await txn.execute('DROP TABLE IF EXISTS notes_fts');

        // Rename temp table to final name
        await txn.execute('ALTER TABLE $tempTableName RENAME TO notes_fts');

        // Create new triggers
        await txn.execute(_ftsTriggers);

        // Rebuild FTS index from notes table to ensure consistency
        debugPrint('Rebuilding FTS index from notes table...');
        await txn.execute('''
          INSERT OR REPLACE INTO notes_fts(rowid, content, date)
          SELECT id, content, date FROM notes
        ''');

        debugPrint('FTS table update completed successfully');
      });
    } catch (e, stackTrace) {
      debugPrint('Error updating FTS table:');
      debugPrint('Error: $e');
      debugPrint('Stack trace: $stackTrace');
      rethrow;
    }
  }

  static Future<String> _getDatabasePath() async {
    debugPrint('Attempting to get database path...');
    if (Platform.isWindows || Platform.isLinux) {
      // Get user's home directory
      final home =
          Platform.environment['HOME'] ?? Platform.environment['USERPROFILE'];
      if (home == null) {
        throw Exception('Could not find home directory');
      }
      debugPrint('Home directory found: $home');

      final dbDir = Directory(path.join(home, settingsDir));
      if (!await dbDir.exists()) {
        await dbDir.create(recursive: true);
        debugPrint('$settingsDir directory created');
      } else {
        debugPrint('$settingsDir directory already exists');
      }

      return path.join(dbDir.path, dbFileName);
    } else {
      // Default path for other platforms
      final databasesPath = await databaseFactoryFfi.getDatabasesPath();
      debugPrint('Using default databases path: $databasesPath');
      return path.join(databasesPath, dbFileName);
    }
  }

  static Future<void> init() async {
    debugPrint('Starting database initialization...');

    // Initialize SQLite FFI
    sqfliteFfiInit();
    final databaseFactory = databaseFactoryFfi;

    // Create a temporary database to check version
    try {
      debugPrint(
        '\n================== SQLITE VERSION CHECK ==================',
      );
      final tempDb = await databaseFactory.openDatabase(
        ':memory:',
        options: OpenDatabaseOptions(
          version: 1,
          onCreate: (db, version) async {
            final results = await db.rawQuery('SELECT sqlite_version()');
            debugPrint('SQLite version: ${results.first.values.first}');

            final compileOpts = await db.rawQuery('PRAGMA compile_options');
            debugPrint('SQLite compile options:');
            for (var opt in compileOpts) {
              debugPrint('  ${opt.values.first}');
            }
          },
        ),
      );
      await tempDb.close();
      debugPrint('=====================================================\n');
    } catch (e, stackTrace) {
      debugPrint('Error checking SQLite version:');
      debugPrint('Error: $e');
      debugPrint('Stack trace: $stackTrace');
    }

    await databaseFactory.setDatabasesPath(
      await databaseFactory.getDatabasesPath(),
    );

    final dbPath = await _getDatabasePath();
    debugPrint('Database path: $dbPath');

    try {
      db = await databaseFactory.openDatabase(
        dbPath,
        options: OpenDatabaseOptions(
          version: 2,
          onConfigure: (db) async {
            debugPrint('Configuring database...');
            await db.execute('PRAGMA foreign_keys = ON');
            debugPrint('Database configured');
          },
          onCreate: (db, version) async {
            debugPrint('Creating database schema...');
            await db.execute(_baseSchema);

            // Check for Unicode support on first creation
            _hasIcuSupport = await _checkIcuSupport(db);
            await _recreateFtsTable(db, _hasIcuSupport);

            debugPrint('Database schema created successfully');
          },
          onOpen: (db) async {
            debugPrint('Database opened, checking Unicode support...');
            try {
              _hasIcuSupport = await _checkIcuSupport(db);
              debugPrint('Unicode support check completed: $_hasIcuSupport');
            } catch (e, stackTrace) {
              debugPrint('Error during Unicode support check:');
              debugPrint('Error: $e');
              debugPrint('Stack trace: $stackTrace');
              rethrow;
            }
          },
          onUpgrade: (db, oldVersion, newVersion) async {
            debugPrint('Upgrading database from $oldVersion to $newVersion');

            if (oldVersion < 2) {
              // Check for Unicode support during upgrade
              _hasIcuSupport = await _checkIcuSupport(db);
              await _recreateFtsTable(db, _hasIcuSupport);
            }
          },
        ),
      );

      // Store Unicode support status in settings for future reference
      await setSetting('has_icu_support', _hasIcuSupport.toString());
      debugPrint(
        'Database opened and initialized (Unicode support: $_hasIcuSupport)',
      );
    } catch (e, stackTrace) {
      debugPrint('Failed to initialize database:');
      debugPrint('Error: $e');
      debugPrint('Stack trace: $stackTrace');
      rethrow;
    }
  }

  // Settings Management
  static Future<String?> getSetting(String key) async {
    final List<Map<String, dynamic>> maps = await db.query(
      'settings',
      columns: ['value'],
      where: 'key = ?',
      whereArgs: [key],
    );
    if (maps.isNotEmpty) {
      return maps.first['value'] as String?;
    }
    return null;
  }

  static Future<void> setSetting(String key, String value) async {
    await db.insert('settings', {
      'key': key,
      'value': value,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
    debugPrint("Setting updated: $key = $value");
  }

  // Search notes using FTS
  static Future<List<Map<String, dynamic>>> searchNotes(String query) async {
    try {
      if (query.trim().isEmpty) {
        return [];
      }

      // Split into individual terms, filter empty ones
      List<String> terms =
          query
              .trim()
              .split(RegExp(r'\s+'))
              .where((term) => term.isNotEmpty)
              .toList();

      if (terms.isEmpty) {
        return [];
      }

      // Process terms for FTS5 query using proper tokenization
      String ftsQuery = terms
          .map((term) {
            // Remove dangerous characters but preserve Unicode
            String sanitizedTerm = term.replaceAll(RegExp(r'''['"]'''), '');
            if (sanitizedTerm.isEmpty) return '';

            // Use proper FTS5 syntax: each word becomes a separate token with prefix matching
            List<String> words = sanitizedTerm.split(RegExp(r'\s+'));
            return words.map((word) => '$word*').join(' OR ');
          })
          .where((term) => term.isNotEmpty)
          .join(' AND ');

      if (ftsQuery.isEmpty) {
        debugPrint('Query was sanitized to empty string');
        return [];
      }

      ftsQuery = ftsQuery.replaceAll('-', ' ');
      debugPrint('FTS query: "$ftsQuery"');

      // Execute the FTS query
      final List<Map<String, dynamic>> results = await db.rawQuery(
        '''
        SELECT n.id, n.date, n.content,
               snippet(notes_fts, -1, '<b>', '</b>', '...', 64) as snippet
        FROM notes_fts
        JOIN notes n ON notes_fts.rowid = n.id
        WHERE notes_fts MATCH ?
        ORDER BY rank
        ''',
        [ftsQuery],
      );

      debugPrint('Search returned ${results.length} results');
      return results;
    } catch (e, stackTrace) {
      debugPrint('Search failed: $e');
      debugPrint('Stack trace: $stackTrace');
      return [];
    }
  }
}