BadNote/lib/services/database_service.dart

import 'dart:convert';
import 'dart:io';
import 'dart:ui' show Offset, Size;

import 'package:flutter/foundation.dart' show visibleForTesting;
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:shared_preferences/shared_preferences.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:uuid/uuid.dart';

import '../editor/board/board.dart';
import '../editor/engine/stroke_model.dart';
import '../models/bookmark.dart';
import '../models/document.dart' as doc;
import '../models/ink_point.dart';
import '../models/ink_stroke.dart';
import '../models/note.dart';
import '../models/pen_tool.dart';
import '../models/pointer_device_kind.dart';
import '../models/scratch_link.dart';
import 'vault_service.dart';

class DatabaseService {
  static DatabaseService? _instance;
  late Database _database;

  DatabaseService._();

  static Future<DatabaseService> getInstance() async {
    if (_instance != null) return _instance!;
    final service = DatabaseService._();
    await service._initialize();
    _instance = service;
    return service;
  }

  /// Test-only: drop the cached singleton so the next [getInstance] re-opens a
  /// fresh database (e.g. after pointing PathProviderPlatform at a new temp
  /// dir). Closes the current handle if one is open.
  @visibleForTesting
  static Future<void> resetForTest() async {
    final existing = _instance;
    _instance = null;
    if (existing != null) {
      await existing._database.close();
    }
  }

  Database get database => _database;

  /// Re-resolve the DB location and reopen the singleton there. Called once the
  /// vault root becomes valid at startup so the live database moves from the
  /// legacy app-documents `badnote.db` to the vault cache
  /// `<vault>/.badnote/index.sqlite` (§A.1). No-op-safe: if the resolved path is
  /// unchanged it simply reopens the same file. Closes the previous handle.
  static Future<DatabaseService> reopen() async {
    final existing = _instance;
    if (existing != null) {
      await existing._database.close();
      _instance = null;
    }
    return getInstance();
  }

  Future<void> _initialize() async {
    if (Platform.isLinux || Platform.isWindows || Platform.isMacOS) {
      sqfliteFfiInit();
      databaseFactory = databaseFactoryFfi;
    }

    final dbPath = await _resolveDbPath();
    // Ensure the parent dir exists (the vault's hidden `.badnote/` cache dir is
    // not guaranteed to exist yet on first run).
    await Directory(p.dirname(dbPath)).create(recursive: true);

    _database = await openDatabase(
      dbPath,
      version: 8,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// The application-documents path of the LEGACY (pre-vault) database. This is
  /// the location [DatabaseService] used before the file-based re-architecture;
  /// the one-time migrator reads from here, then renames it to `.premigration`.
  static Future<String> legacyDbPath() async {
    final dir = await getApplicationDocumentsDirectory();
    return p.join(dir.path, 'badnote.db');
  }

  /// Resolve where the live database should live. When a valid vault root is
  /// set, the DB is the vault's rebuildable cache/index at
  /// `<vault>/.badnote/index.sqlite` (§A.1). Otherwise (no vault yet — e.g. a
  /// fresh first run before the gate, or tests) fall back to the legacy
  /// app-documents `badnote.db` so the app still works.
  Future<String> _resolveDbPath() async {
    String? root;
    try {
      final prefs = await SharedPreferences.getInstance();
      root = prefs.getString(VaultService.vaultRootKey);
    } catch (_) {
      // SharedPreferences may be unavailable (e.g. a unit test that mocks only
      // the path provider). Fall back to the legacy app-documents location so
      // the DB still opens — it is never the source of truth anyway.
      root = null;
    }
    if (root != null && root.isNotEmpty && await Directory(root).exists()) {
      return p.join(root, '.badnote', 'index.sqlite');
    }
    return legacyDbPath();
  }

  Future<void> _onCreate(Database db, int version) async {
    // Core tables (original v1)
    await db.execute('''
      CREATE TABLE notes (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        tags TEXT NOT NULL DEFAULT '[]'
      )
    ''');

    await db.execute('''
      CREATE TABLE strokes (
        id TEXT PRIMARY KEY,
        note_id TEXT NOT NULL,
        tool TEXT NOT NULL,
        color INTEGER NOT NULL,
        stroke_width REAL NOT NULL,
        created_at TEXT NOT NULL,
        points TEXT NOT NULL,
        FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
      )
    ''');

    await db.execute('CREATE INDEX idx_strokes_note_id ON strokes(note_id)');

    await _createFtsTable(db);

    // Documents & annotations (originally v2, now part of fresh install)
    await db.execute('''
      CREATE TABLE documents (
        id TEXT PRIMARY KEY,
        filename TEXT NOT NULL,
        doc_type TEXT NOT NULL,
        file_path TEXT NOT NULL,
        page_count INTEGER NOT NULL DEFAULT 0,
        rotation INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    ''');

    await db.execute('''
      CREATE TABLE annotations (
        id TEXT PRIMARY KEY,
        uuid TEXT NOT NULL,
        document_id TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        annotation_json TEXT NOT NULL,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
      )
    ''');

    await db.execute(
      'CREATE INDEX idx_annotations_doc_page ON annotations(document_id, page_number)',
    );

    await db.execute('''
      CREATE TABLE bookmarks (
        id TEXT PRIMARY KEY,
        document_id TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        label TEXT NOT NULL DEFAULT '',
        color INTEGER NOT NULL DEFAULT 4283215696,
        created_at TEXT NOT NULL,
        FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
      )
    ''');

    await db.execute(
      'CREATE INDEX idx_bookmarks_doc ON bookmarks(document_id)',
    );

    await db.execute('''
      CREATE TABLE ocr_results (
        id TEXT PRIMARY KEY,
        document_id TEXT NOT NULL,
        page_number INTEGER NOT NULL,
        ocr_text TEXT NOT NULL DEFAULT '',
        created_at TEXT NOT NULL,
        FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
      )
    ''');

    // Document FTS (v3)
    await db.execute('''
      CREATE VIRTUAL TABLE document_fts USING fts5(
        document_id, page_number, content, tokenize='porter unicode61'
      )
    ''');

    // Scratchpads (v5)
    await db.execute('''
      CREATE TABLE scratchpads (
        id TEXT PRIMARY KEY,
        document_id TEXT UNIQUE NOT NULL,
        strokes_json TEXT NOT NULL DEFAULT '[]',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
      )
    ''');

    await db.execute(
      'CREATE INDEX idx_scratchpads_doc ON scratchpads(document_id)',
    );

    // Editor ink strokes (v6)
    await db.execute('''
      CREATE TABLE ink (
        id TEXT PRIMARY KEY,
        host_kind TEXT NOT NULL,
        host_id TEXT NOT NULL,
        stroke_json TEXT NOT NULL,
        ordinal INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');

    await db.execute(
      'CREATE INDEX idx_ink_host ON ink(host_kind, host_id)',
    );

    // Notebook pages (v6)
    await db.execute('''
      CREATE TABLE notebook_pages (
        id TEXT PRIMARY KEY,
        document_id TEXT NOT NULL,
        ordinal INTEGER NOT NULL,
        source_page_index INTEGER NOT NULL,
        kind TEXT NOT NULL,
        created_at INTEGER NOT NULL
      )
    ''');

    // Sticky-note board cards (v7): F7 双链 + 无限便利贴.
    await _createBoardCardsTable(db);

    // PDF-anchored scratch links (v8).
    await _createScratchLinksTable(db);
  }

  /// PDF-anchored scratch links table (v8). One row per [ScratchLink] anchor.
  /// The anchor [id] doubles as the storage key for its private scratchpad
  /// (reused from the [scratchpads] table — see [saveScratchpad]).
  Future<void> _createScratchLinksTable(DatabaseExecutor db) async {
    await db.execute('''
      CREATE TABLE scratch_links (
        id TEXT PRIMARY KEY,
        document_id TEXT NOT NULL,
        page_index INTEGER NOT NULL,
        nx REAL NOT NULL,
        ny REAL NOT NULL,
        created_at TEXT NOT NULL
      )
    ''');
    await db.execute(
      'CREATE INDEX idx_scratch_links_doc ON scratch_links(document_id)',
    );
  }

  /// Sticky-note board cards table (F7). One row per [BoardCard]; a board is the
  /// set of rows sharing a [board_id]. Geometry is stored as plain columns
  /// (rows, not a blob) so a board round-trips and could be queried later.
  Future<void> _createBoardCardsTable(DatabaseExecutor db) async {
    await db.execute('''
      CREATE TABLE board_cards (
        id TEXT PRIMARY KEY,
        board_id TEXT NOT NULL,
        x REAL NOT NULL,
        y REAL NOT NULL,
        w REAL NOT NULL,
        h REAL NOT NULL,
        text TEXT NOT NULL DEFAULT '',
        ordinal INTEGER NOT NULL DEFAULT 0,
        updated_at INTEGER NOT NULL
      )
    ''');
    await db.execute(
      'CREATE INDEX idx_board_cards_board ON board_cards(board_id)',
    );
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 3) await _migrateV2toV3(db);
    if (oldVersion < 4) {} // v3->v4: version boundary (no-op schema)
    if (oldVersion < 5) await _migrateV4toV5(db);
    if (oldVersion < 6) await _migrateV5toV6(db);
    if (oldVersion < 7) await _migrateV6toV7(db);
    if (oldVersion < 8) await _migrateV7toV8(db);
  }

  Future<void> _migrateV7toV8(Database db) async {
    await db.transaction((txn) async {
      await _createScratchLinksTable(txn);
    });
  }

  Future<void> _migrateV6toV7(Database db) async {
    await db.transaction((txn) async {
      await _createBoardCardsTable(txn);
    });
  }

  Future<void> _migrateV5toV6(Database db) async {
    await db.transaction((txn) async {
      await txn.execute('''
        CREATE TABLE ink (
          id TEXT PRIMARY KEY,
          host_kind TEXT NOT NULL,
          host_id TEXT NOT NULL,
          stroke_json TEXT NOT NULL,
          ordinal INTEGER NOT NULL,
          updated_at INTEGER NOT NULL
        )
      ''');

      await txn.execute(
        'CREATE INDEX idx_ink_host ON ink(host_kind, host_id)',
      );

      await txn.execute('''
        CREATE TABLE notebook_pages (
          id TEXT PRIMARY KEY,
          document_id TEXT NOT NULL,
          ordinal INTEGER NOT NULL,
          source_page_index INTEGER NOT NULL,
          kind TEXT NOT NULL,
          created_at INTEGER NOT NULL
        )
      ''');
    });
  }

  Future<void> _migrateV2toV3(Database db) async {
    // Wrap the whole migration in a transaction: a failure mid-migration
    // (after DROP TABLE annotations) would otherwise destroy data.
    await db.transaction((txn) async {
      // Add uuid column to annotations
      await txn.execute('ALTER TABLE annotations ADD COLUMN uuid TEXT');

      // Generate UUIDs for existing rows
      await txn.rawUpdate(
        "UPDATE annotations SET uuid = hex(randomblob(16)) WHERE uuid IS NULL",
      );

      // Recreate annotations table with UUID primary key
      await txn.execute('''
        CREATE TABLE annotations_new (
          id TEXT PRIMARY KEY,
          uuid TEXT NOT NULL,
          document_id TEXT NOT NULL,
          page_number INTEGER NOT NULL,
          annotation_json TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL,
          FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
        )
      ''');

      await txn.rawInsert('''
        INSERT INTO annotations_new (id, uuid, document_id, page_number, annotation_json, created_at, updated_at)
        SELECT id, uuid, document_id, page_number, annotation_json, created_at, updated_at FROM annotations
      ''');

      await txn.execute('DROP TABLE annotations');
      await txn.execute('ALTER TABLE annotations_new RENAME TO annotations');
      await txn.execute(
        'CREATE INDEX idx_annotations_doc_page ON annotations(document_id, page_number)',
      );

      // Create document FTS table
      await txn.execute('''
        CREATE VIRTUAL TABLE document_fts USING fts5(
          document_id, page_number, content, tokenize='porter unicode61'
        )
      ''');

      // Add rotation column to documents
      await txn.execute(
        'ALTER TABLE documents ADD COLUMN rotation INTEGER NOT NULL DEFAULT 0',
      );
    });
  }

  Future<void> _migrateV4toV5(Database db) async {
    // Wrap in a transaction so a partial failure does not leave the schema
    // in an inconsistent state.
    await db.transaction((txn) async {
      // Create scratchpads table
      await txn.execute('''
        CREATE TABLE scratchpads (
          id TEXT PRIMARY KEY,
          document_id TEXT UNIQUE NOT NULL,
          strokes_json TEXT NOT NULL DEFAULT '[]',
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL,
          FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
        )
      ''');

      await txn.execute(
        'CREATE INDEX idx_scratchpads_doc ON scratchpads(document_id)',
      );
    });
  }

  // ── Notes CRUD ──────────────────────────────────────────────────────

  Future<List<Note>> getAllNotes() async {
    final noteRows = await _database.query('notes', orderBy: 'updated_at DESC');
    final notes = <Note>[];
    for (final row in noteRows) {
      notes.add(await _noteFromRow(row));
    }
    return notes;
  }

  Future<Note?> getNoteById(String id) async {
    final rows = await _database.query(
      'notes',
      where: 'id = ?',
      whereArgs: [id],
    );
    if (rows.isEmpty) return null;
    return _noteFromRow(rows.first);
  }

  Future<void> insertNote(Note note) async {
    // Atomic: the note row, its strokes, and the FTS index must all commit
    // together or not at all.
    await _database.transaction((txn) async {
      await txn.insert('notes', {
        'id': note.id,
        'title': note.title,
        'created_at': note.createdAt.toIso8601String(),
        'updated_at': note.updatedAt.toIso8601String(),
        'tags': jsonEncode(note.tags),
      });

      for (final stroke in note.strokes) {
        await _insertStroke(txn, note.id, stroke);
      }

      await _extractAndIndexNoteContent(txn, note);
    });
  }

  Future<void> updateNote(Note note) async {
    // Atomic: this deletes all strokes then re-inserts them and rebuilds the
    // FTS entry. An interruption mid-way would permanently lose strokes, so
    // the whole sequence must run inside one transaction.
    await _database.transaction((txn) async {
      await txn.update(
        'notes',
        {
          'title': note.title,
          'updated_at': note.updatedAt.toIso8601String(),
          'tags': jsonEncode(note.tags),
        },
        where: 'id = ?',
        whereArgs: [note.id],
      );

      // Replace all strokes for this note
      await txn.delete('strokes', where: 'note_id = ?', whereArgs: [note.id]);
      for (final stroke in note.strokes) {
        await _insertStroke(txn, note.id, stroke);
      }

      await removeFromFts(txn, note.id);
      await _extractAndIndexNoteContent(txn, note);
    });
  }

  Future<void> deleteNote(String id) async {
    await _database.transaction((txn) async {
      await txn.delete('strokes', where: 'note_id = ?', whereArgs: [id]);
      await txn.delete('notes', where: 'id = ?', whereArgs: [id]);
      await removeFromFts(txn, id);
    });
  }

  // ── Strokes ─────────────────────────────────────────────────────────

  Future<void> _insertStroke(
    DatabaseExecutor db,
    String noteId,
    InkStroke stroke,
  ) async {
    await db.insert('strokes', {
      'id': stroke.id,
      'note_id': noteId,
      'tool': stroke.tool.name,
      'color': stroke.color,
      'stroke_width': stroke.strokeWidth,
      'created_at': stroke.createdAt.toIso8601String(),
      'points': jsonEncode(stroke.points.map(_pointToJson).toList()),
    });
  }

  Future<List<InkStroke>> _getStrokesForNote(String noteId) async {
    final rows = await _database.query(
      'strokes',
      where: 'note_id = ?',
      whereArgs: [noteId],
      orderBy: 'created_at ASC',
    );
    return rows.map(_strokeFromRow).toList();
  }

  // ── Serialization helpers ───────────────────────────────────────────

  Map<String, dynamic> _pointToJson(InkPoint p) => {
    'x': p.x,
    'y': p.y,
    'pressure': p.pressure,
    'tilt': p.tilt,
    'timestamp': p.timestamp,
    'pointerDeviceKind': p.pointerDeviceKind.name,
  };

  InkPoint _pointFromJson(Map<String, dynamic> json) => InkPoint(
    x: (json['x'] as num).toDouble(),
    y: (json['y'] as num).toDouble(),
    pressure: (json['pressure'] as num?)?.toDouble() ?? 0.5,
    tilt: (json['tilt'] as num?)?.toDouble() ?? 0.0,
    timestamp: json['timestamp'] as int,
    pointerDeviceKind: _parseDeviceKind(json['pointerDeviceKind'] as String?),
  );

  InputDeviceKind _parseDeviceKind(String? value) {
    if (value == null) return InputDeviceKind.unknown;
    return InputDeviceKind.values.asNameMap()[value] ?? InputDeviceKind.unknown;
  }

  InkStroke _strokeFromRow(Map<String, dynamic> row) {
    final pointsJson = jsonDecode(row['points'] as String) as List;
    return InkStroke(
      id: row['id'] as String,
      points: pointsJson
          .map((p) => _pointFromJson(p as Map<String, dynamic>))
          .toList(),
      tool: _parsePenTool(row['tool'] as String),
      color: row['color'] as int,
      strokeWidth: (row['stroke_width'] as num).toDouble(),
      createdAt: DateTime.parse(row['created_at'] as String),
    );
  }

  PenTool _parsePenTool(String value) {
    return PenTool.values.asNameMap()[value] ?? PenTool.pen;
  }

  Future<Note> _noteFromRow(Map<String, dynamic> row) async {
    final tagsJson = jsonDecode(row['tags'] as String) as List;
    final strokes = await _getStrokesForNote(row['id'] as String);
    return Note(
      id: row['id'] as String,
      title: row['title'] as String,
      strokes: strokes,
      createdAt: DateTime.parse(row['created_at'] as String),
      updatedAt: DateTime.parse(row['updated_at'] as String),
      tags: tagsJson.cast<String>(),
    );
  }

  // ── Full-Text Search (FTS5) ────────────────────────────────────────

  Future<void> _createFtsTable(Database db) async {
    await db.execute('''
      CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
        note_id, title, content, tokenize='porter unicode61'
      )
    ''');
  }

  /// Index a note's text content for full-text search.
  /// [content] should include any typed text, OCR text, etc.
  Future<void> indexNoteContent(
    DatabaseExecutor db,
    String noteId,
    String title,
    String content,
  ) async {
    await db.insert('notes_fts', {
      'note_id': noteId,
      'title': title,
      'content': content,
    });
  }

  /// Extract text content from a note's strokes and index it for FTS.
  /// Concatenates the title with any textContent from strokes.
  Future<void> _extractAndIndexNoteContent(
    DatabaseExecutor db,
    Note note,
  ) async {
    final textParts = <String>[note.title];
    for (final stroke in note.strokes) {
      if (stroke.textContent != null && stroke.textContent!.isNotEmpty) {
        textParts.add(stroke.textContent!);
      }
    }
    final content = textParts.join(' ');
    await indexNoteContent(db, note.id, note.title, content);
  }

  /// Append OCR text to an existing note's FTS entry.
  /// Reads current content, merges with new OCR text, and re-indexes.
  Future<void> appendOcrToFts(String noteId, String ocrText) async {
    if (ocrText.trim().isEmpty) return;

    // Read-modify-write must be atomic: querying the current content, removing
    // the old entry, and re-inserting the merged content all run inside one
    // transaction so a concurrent writer cannot cause a lost update.
    await _database.transaction((txn) async {
      // Read current FTS content
      final rows = await txn.query(
        'notes_fts',
        where: 'note_id = ?',
        whereArgs: [noteId],
      );

      String existingContent = '';
      String existingTitle = '';
      if (rows.isNotEmpty) {
        existingTitle = rows.first['title'] as String? ?? '';
        existingContent = rows.first['content'] as String? ?? '';
      }

      // Merge: append OCR text to existing content
      final mergedContent = existingContent.isEmpty
          ? ocrText
          : '$existingContent $ocrText';

      // Remove old entry and re-insert with merged content
      await removeFromFts(txn, noteId);
      await indexNoteContent(txn, noteId, existingTitle, mergedContent);
    });
  }

  /// Full-text search across indexed notes.
  Future<List<Note>> searchNotes(String query) async {
    if (query.trim().isEmpty) return [];

    // Sanitize query for FTS5: escape special chars and add prefix matching
    final sanitized = query.replaceAll('"', '').replaceAll("'", '').trim();
    if (sanitized.isEmpty) return [];

    final ftsQuery = sanitized
        .split(RegExp(r'\s+'))
        .map((w) => '"$w"*')
        .join(' ');

    final rows = await _database.rawQuery(
      'SELECT note_id FROM notes_fts WHERE notes_fts MATCH ? ORDER BY rank',
      [ftsQuery],
    );

    final notes = <Note>[];
    for (final row in rows) {
      final noteId = row['note_id'] as String;
      final note = await getNoteById(noteId);
      if (note != null) {
        notes.add(note);
      }
    }
    return notes;
  }

  /// Remove a note from the FTS index.
  Future<void> removeFromFts(DatabaseExecutor db, String noteId) async {
    await db.delete('notes_fts', where: 'note_id = ?', whereArgs: [noteId]);
  }

  // ── Document FTS ────────────────────────────────────────────────────

  /// Index a page's text content for document full-text search.
  Future<void> indexDocumentContent(
    String documentId,
    int pageNumber,
    String content,
  ) async {
    // Remove existing entry for this page first
    await _database.delete(
      'document_fts',
      where: 'document_id = ? AND page_number = ?',
      whereArgs: [documentId, pageNumber],
    );
    await _database.insert('document_fts', {
      'document_id': documentId,
      'page_number': pageNumber.toString(),
      'content': content,
    });
  }

  /// Remove a page from the document FTS index.
  Future<void> removeDocumentFromFts(
    DatabaseExecutor db,
    String documentId,
    int pageNumber,
  ) async {
    await db.delete(
      'document_fts',
      where: 'document_id = ? AND page_number = ?',
      whereArgs: [documentId, pageNumber],
    );
  }

  /// Full-text search across indexed document pages.
  Future<List<Map<String, dynamic>>> searchDocuments(String query) async {
    if (query.trim().isEmpty) return [];

    final sanitized = query.replaceAll('"', '').replaceAll("'", '').trim();
    if (sanitized.isEmpty) return [];

    final ftsQuery = sanitized
        .split(RegExp(r'\s+'))
        .map((w) => '"$w"*')
        .join(' ');

    final rows = await _database.rawQuery(
      'SELECT document_id, page_number, content FROM document_fts WHERE document_fts MATCH ? ORDER BY rank',
      [ftsQuery],
    );

    return rows
        .map(
          (row) => {
            'document_id': row['document_id'] as String,
            'page_number': int.parse(row['page_number'] as String),
            'content': row['content'] as String,
          },
        )
        .toList();
  }

  // ── Documents CRUD ─────────────────────────────────────────────────

  Future<void> insertDocument(doc.Document document) async {
    await _database.insert('documents', {
      'id': document.id,
      'filename': document.filename,
      'doc_type': document.docType,
      'file_path': document.filePath,
      'page_count': document.pageCount,
      'rotation': document.rotation,
      'created_at': document.createdAt.toIso8601String(),
      'updated_at': document.updatedAt.toIso8601String(),
    });
  }

  Future<doc.Document?> getDocument(String id) async {
    final rows = await _database.query(
      'documents',
      where: 'id = ?',
      whereArgs: [id],
    );
    if (rows.isEmpty) return null;
    return _documentFromRow(rows.first);
  }

  Future<doc.Document?> getDocumentByPath(String filePath) async {
    final rows = await _database.query(
      'documents',
      where: 'file_path = ?',
      whereArgs: [filePath],
    );
    if (rows.isEmpty) return null;
    return _documentFromRow(rows.first);
  }

  Future<List<doc.Document>> getAllDocuments() async {
    final rows = await _database.query('documents', orderBy: 'updated_at DESC');
    return rows.map(_documentFromRow).toList();
  }

  Future<void> deleteDocument(String id) async {
    await _database.transaction((txn) async {
      await txn.delete(
        'annotations',
        where: 'document_id = ?',
        whereArgs: [id],
      );
      await txn.delete('bookmarks', where: 'document_id = ?', whereArgs: [id]);
      await txn.delete(
        'ocr_results',
        where: 'document_id = ?',
        whereArgs: [id],
      );
      await txn.delete(
        'scratchpads',
        where: 'document_id = ?',
        whereArgs: [id],
      );
      await txn.delete('documents', where: 'id = ?', whereArgs: [id]);
    });
  }

  doc.Document _documentFromRow(Map<String, dynamic> row) {
    return doc.Document(
      id: row['id'] as String,
      filename: row['filename'] as String,
      docType: row['doc_type'] as String,
      filePath: row['file_path'] as String,
      pageCount: row['page_count'] as int,
      rotation: (row['rotation'] as int?) ?? 0,
      createdAt: DateTime.parse(row['created_at'] as String),
      updatedAt: DateTime.parse(row['updated_at'] as String),
    );
  }

  // ── Annotations CRUD ───────────────────────────────────────────────

  Future<void> saveAnnotations(
    String documentId,
    int pageNumber,
    String annotationJson,
  ) async {
    await _database.delete(
      'annotations',
      where: 'document_id = ? AND page_number = ?',
      whereArgs: [documentId, pageNumber],
    );
    await _database.insert('annotations', {
      'id': const Uuid().v4(),
      'uuid': const Uuid().v4(),
      'document_id': documentId,
      'page_number': pageNumber,
      'annotation_json': annotationJson,
      'created_at': DateTime.now().toIso8601String(),
      'updated_at': DateTime.now().toIso8601String(),
    });
  }

  Future<String?> getAnnotations(String documentId, int pageNumber) async {
    final rows = await _database.query(
      'annotations',
      where: 'document_id = ? AND page_number = ?',
      whereArgs: [documentId, pageNumber],
    );
    if (rows.isEmpty) return null;
    return rows.first['annotation_json'] as String;
  }

  Future<void> deleteDocumentAnnotations(String documentId) async {
    await _database.delete(
      'annotations',
      where: 'document_id = ?',
      whereArgs: [documentId],
    );
  }

  // ── Annotation/Bookmark Remapping ──────────────────────────────────

  /// After deleting a page at [deletedIndex], shift all annotations
  /// with page_number > deletedIndex down by 1.
  Future<void> remapAnnotationsAfterDelete(
    String documentId,
    int deletedIndex,
  ) async {
    await _database.rawUpdate(
      'UPDATE annotations SET page_number = page_number - 1 WHERE document_id = ? AND page_number > ?',
      [documentId, deletedIndex],
    );
  }

  /// After inserting a page at [insertedIndex], shift all annotations
  /// with page_number >= insertedIndex up by 1.
  Future<void> remapAnnotationsAfterInsert(
    String documentId,
    int insertedIndex,
  ) async {
    await _database.rawUpdate(
      'UPDATE annotations SET page_number = page_number + 1 WHERE document_id = ? AND page_number >= ?',
      [documentId, insertedIndex],
    );
  }

  /// After deleting a page at [deletedIndex], shift all bookmarks
  /// with page_number > deletedIndex down by 1.
  Future<void> remapBookmarksAfterDelete(
    String documentId,
    int deletedIndex,
  ) async {
    await _database.rawUpdate(
      'UPDATE bookmarks SET page_number = page_number - 1 WHERE document_id = ? AND page_number > ?',
      [documentId, deletedIndex],
    );
  }

  /// After inserting a page at [insertedIndex], shift all bookmarks
  /// with page_number >= insertedIndex up by 1.
  Future<void> remapBookmarksAfterInsert(
    String documentId,
    int insertedIndex,
  ) async {
    await _database.rawUpdate(
      'UPDATE bookmarks SET page_number = page_number + 1 WHERE document_id = ? AND page_number >= ?',
      [documentId, insertedIndex],
    );
  }

  /// Delete all annotations, bookmarks, and OCR data for a specific page.
  Future<void> deletePageData(String documentId, int pageNumber) async {
    await _database.transaction((txn) async {
      await txn.delete(
        'annotations',
        where: 'document_id = ? AND page_number = ?',
        whereArgs: [documentId, pageNumber],
      );
      await txn.delete(
        'bookmarks',
        where: 'document_id = ? AND page_number = ?',
        whereArgs: [documentId, pageNumber],
      );
      await txn.delete(
        'ocr_results',
        where: 'document_id = ? AND page_number = ?',
        whereArgs: [documentId, pageNumber],
      );
      await removeDocumentFromFts(txn, documentId, pageNumber);
    });
  }

  /// Update the stored page count for a document.
  Future<void> updateDocumentPageCount(
    String documentId,
    int newPageCount,
  ) async {
    await _database.update(
      'documents',
      {
        'page_count': newPageCount,
        'updated_at': DateTime.now().toIso8601String(),
      },
      where: 'id = ?',
      whereArgs: [documentId],
    );
  }

  // ── Bookmarks CRUD ─────────────────────────────────────────────────

  Future<void> insertBookmark(Bookmark bookmark) async {
    await _database.insert('bookmarks', {
      'id': bookmark.id,
      'document_id': bookmark.documentId,
      'page_number': bookmark.pageNumber,
      'label': bookmark.label,
      'color': bookmark.color,
      'created_at': bookmark.createdAt.toIso8601String(),
    });
  }

  Future<List<Bookmark>> getBookmarks(String documentId) async {
    final rows = await _database.query(
      'bookmarks',
      where: 'document_id = ?',
      whereArgs: [documentId],
      orderBy: 'page_number ASC',
    );
    return rows.map(_bookmarkFromRow).toList();
  }

  Future<void> deleteBookmark(String id) async {
    await _database.delete('bookmarks', where: 'id = ?', whereArgs: [id]);
  }

  Bookmark _bookmarkFromRow(Map<String, dynamic> row) {
    return Bookmark(
      id: row['id'] as String,
      documentId: row['document_id'] as String,
      pageNumber: row['page_number'] as int,
      label: row['label'] as String,
      color: row['color'] as int,
      createdAt: DateTime.parse(row['created_at'] as String),
    );
  }

  // ── Scratchpad CRUD ────────────────────────────────────────────────

  /// Save scratchpad strokes for a document (upsert).
  Future<void> saveScratchpad(String documentId, String strokesJson) async {
    final now = DateTime.now().toIso8601String();
    await _database.rawInsert(
      '''INSERT INTO scratchpads (id, document_id, strokes_json, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?)
         ON CONFLICT(document_id) DO UPDATE SET strokes_json = excluded.strokes_json, updated_at = excluded.updated_at''',
      [const Uuid().v4(), documentId, strokesJson, now, now],
    );
  }

  /// Load scratchpad strokes for a document.
  Future<List<InkStroke>> loadScratchpad(String documentId) async {
    final rows = await _database.query(
      'scratchpads',
      where: 'document_id = ?',
      whereArgs: [documentId],
    );
    if (rows.isEmpty) return [];
    final json = rows.first['strokes_json'] as String;
    if (json.isEmpty || json == '[]') return [];
    final List<dynamic> list = jsonDecode(json) as List<dynamic>;
    return list
        .map((s) => InkStroke.fromJson(s as Map<String, dynamic>))
        .toList();
  }

  // ── Board cards CRUD (F7 双链 + 无限便利贴) ──────────────────────────

  /// Replace ALL cards for [boardId] with [cards]. Geometry is persisted as
  /// rows (id, x, y, w, h, text) so the board survives restart. The whole
  /// replace runs in one transaction so a crash mid-save cannot leave a
  /// half-written board.
  Future<void> saveBoardCards(
    String boardId,
    List<BoardCard> cards,
  ) async {
    final now = DateTime.now().millisecondsSinceEpoch;
    await _database.transaction((txn) async {
      await txn.delete(
        'board_cards',
        where: 'board_id = ?',
        whereArgs: [boardId],
      );
      for (var i = 0; i < cards.length; i++) {
        final c = cards[i];
        await txn.insert('board_cards', {
          'id': c.id,
          'board_id': boardId,
          'x': c.position.dx,
          'y': c.position.dy,
          'w': c.size.width,
          'h': c.size.height,
          'text': c.text,
          'ordinal': i,
          'updated_at': now,
        });
      }
    });
  }

  /// Load the [Board] for [boardId] (empty board when nothing is stored).
  Future<Board> loadBoard(String boardId) async {
    final rows = await _database.query(
      'board_cards',
      where: 'board_id = ?',
      whereArgs: [boardId],
      orderBy: 'ordinal ASC',
    );
    return Board([
      for (final row in rows)
        BoardCard(
          id: row['id'] as String,
          position: Offset(
            (row['x'] as num).toDouble(),
            (row['y'] as num).toDouble(),
          ),
          size: Size(
            (row['w'] as num).toDouble(),
            (row['h'] as num).toDouble(),
          ),
          text: row['text'] as String? ?? '',
        ),
    ]);
  }

  // ── Scratch links CRUD (PDF-anchored scratchpad tabs) ──────────────────

  /// Insert or replace a [ScratchLink] anchor. The anchor's private scratchpad
  /// lives in the [scratchpads] table keyed by [ScratchLink.id] — saved/loaded
  /// via [saveScratchpad] / [loadScratchpad].
  Future<void> saveScratchLink(ScratchLink link) async {
    await _database.insert(
      'scratch_links',
      {
        'id': link.id,
        'document_id': link.documentId,
        'page_index': link.pageIndex,
        'nx': link.nx,
        'ny': link.ny,
        'created_at': DateTime.now().toIso8601String(),
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// Load all anchors for [documentId], oldest first.
  Future<List<ScratchLink>> loadScratchLinks(String documentId) async {
    final rows = await _database.query(
      'scratch_links',
      where: 'document_id = ?',
      whereArgs: [documentId],
      orderBy: 'created_at ASC',
    );
    return rows
        .map(
          (row) => ScratchLink(
            id: row['id'] as String,
            documentId: row['document_id'] as String,
            pageIndex: row['page_index'] as int,
            nx: (row['nx'] as num).toDouble(),
            ny: (row['ny'] as num).toDouble(),
          ),
        )
        .toList();
  }

  /// Delete an anchor and its private scratchpad (the scratchpad row keyed by
  /// the anchor id), so a deleted anchor leaves no orphaned ink behind.
  Future<void> deleteScratchLink(String id) async {
    await _database.transaction((txn) async {
      await txn.delete('scratch_links', where: 'id = ?', whereArgs: [id]);
      await txn.delete('scratchpads', where: 'document_id = ?', whereArgs: [id]);
    });
  }

  // ── RAW legacy reads (one-time SQLite→sidecar migration, Phase 5) ───────────
  //
  // These operate on an arbitrary [Database] handle (the LEGACY db the migrator
  // opens directly), NOT the live [_database] cache, so the migrator can read
  // pre-migration data without touching the relocated index. They reuse this
  // class's row-parsers so the JSON shapes stay identical to the live reads.

  /// All `documents` rows from [db], oldest first (stable migration order).
  static Future<List<doc.Document>> rawAllDocuments(Database db) async {
    if (!await _tableExists(db, 'documents')) return const [];
    final rows = await db.query('documents', orderBy: 'created_at ASC');
    final dummy = DatabaseService._();
    return rows.map(dummy._documentFromRow).toList();
  }

  /// All `notes` rows (with their `strokes`) from [db], oldest first.
  static Future<List<Note>> rawAllNotes(Database db) async {
    if (!await _tableExists(db, 'notes')) return const [];
    final rows = await db.query('notes', orderBy: 'created_at ASC');
    final dummy = DatabaseService._();
    final notes = <Note>[];
    for (final row in rows) {
      final strokeRows = await db.query(
        'strokes',
        where: 'note_id = ?',
        whereArgs: [row['id'] as String],
        orderBy: 'created_at ASC',
      );
      final strokes = strokeRows.map(dummy._strokeFromRow).toList();
      final tagsJson = jsonDecode(row['tags'] as String) as List;
      notes.add(Note(
        id: row['id'] as String,
        title: row['title'] as String,
        strokes: strokes,
        createdAt: DateTime.parse(row['created_at'] as String),
        updatedAt: DateTime.parse(row['updated_at'] as String),
        tags: tagsJson.cast<String>(),
      ));
    }
    return notes;
  }

  /// Committed editor strokes for [documentId] from [db], grouped by 0-based
  /// page index. Parses the `ink.host_id = "doc:<documentId>:page:<i>"` scheme
  /// (see [EditorRepository.loadDocument]) and decodes each `stroke_json`
  /// straight into an [EditorStroke]. Returns `{}` when there is no `ink` table
  /// or no rows.
  static Future<Map<int, List<EditorStroke>>> rawStrokesByPage(
    Database db,
    String documentId,
  ) async {
    if (!await _tableExists(db, 'ink')) return <int, List<EditorStroke>>{};
    final rows = await db.query(
      'ink',
      where: 'host_kind = ? AND host_id LIKE ?',
      whereArgs: ['page', 'doc:$documentId:page:%'],
      orderBy: 'host_id ASC, ordinal ASC',
    );
    final out = <int, List<EditorStroke>>{};
    for (final row in rows) {
      final hostId = row['host_id'] as String;
      final pageIndex = _pageIndexFromHostId(hostId);
      if (pageIndex == null) continue;
      final json =
          jsonDecode(row['stroke_json'] as String) as Map<String, dynamic>;
      out.putIfAbsent(pageIndex, () => []).add(EditorStroke.fromJson(json));
    }
    return out;
  }

  /// Bookmarks for [documentId] from [db] (empty when no `bookmarks` table).
  static Future<List<Bookmark>> rawBookmarks(
    Database db,
    String documentId,
  ) async {
    if (!await _tableExists(db, 'bookmarks')) return const [];
    final rows = await db.query(
      'bookmarks',
      where: 'document_id = ?',
      whereArgs: [documentId],
      orderBy: 'page_number ASC',
    );
    final dummy = DatabaseService._();
    return rows.map(dummy._bookmarkFromRow).toList();
  }

  /// Scratch-link anchors for [documentId] from [db] (empty when no table).
  static Future<List<ScratchLink>> rawScratchLinks(
    Database db,
    String documentId,
  ) async {
    if (!await _tableExists(db, 'scratch_links')) return const [];
    final rows = await db.query(
      'scratch_links',
      where: 'document_id = ?',
      whereArgs: [documentId],
      orderBy: 'created_at ASC',
    );
    return rows
        .map(
          (row) => ScratchLink(
            id: row['id'] as String,
            documentId: row['document_id'] as String,
            pageIndex: row['page_index'] as int,
            nx: (row['nx'] as num).toDouble(),
            ny: (row['ny'] as num).toDouble(),
          ),
        )
        .toList();
  }

  /// Scratchpad strokes stored under [key] (an anchor id) from [db]. Empty when
  /// there is no `scratchpads` table or no row.
  static Future<List<InkStroke>> rawScratchpad(
    Database db,
    String key,
  ) async {
    if (!await _tableExists(db, 'scratchpads')) return const [];
    final rows = await db.query(
      'scratchpads',
      where: 'document_id = ?',
      whereArgs: [key],
    );
    if (rows.isEmpty) return const [];
    final json = rows.first['strokes_json'] as String;
    if (json.isEmpty || json == '[]') return const [];
    final list = jsonDecode(json) as List<dynamic>;
    return list
        .map((s) => InkStroke.fromJson(s as Map<String, dynamic>))
        .toList();
  }

  /// Raw legacy per-page `annotation_json` blobs for [documentId] from [db],
  /// keyed by page number. These belong to the DEAD pre-editor annotation path
  /// (§1 `annotations` table); the migrator copies them verbatim into the
  /// sidecar's `legacyAnnotations` so nothing is silently dropped.
  static Future<Map<int, String>> rawLegacyAnnotations(
    Database db,
    String documentId,
  ) async {
    if (!await _tableExists(db, 'annotations')) return <int, String>{};
    final rows = await db.query(
      'annotations',
      where: 'document_id = ?',
      whereArgs: [documentId],
      orderBy: 'page_number ASC',
    );
    final out = <int, String>{};
    for (final row in rows) {
      out[row['page_number'] as int] = row['annotation_json'] as String;
    }
    return out;
  }

  /// Parse the 0-based page index out of an `ink.host_id` of the form
  /// `doc:<documentId>:page:<pageIndex>`. Returns null on an unexpected shape.
  static int? _pageIndexFromHostId(String hostId) {
    final i = hostId.lastIndexOf(':page:');
    if (i == -1) return null;
    return int.tryParse(hostId.substring(i + ':page:'.length));
  }

  /// True iff [name] is an existing table in [db]. Lets the raw readers tolerate
  /// a legacy DB that predates a given table (older schema versions).
  static Future<bool> _tableExists(Database db, String name) async {
    final rows = await db.rawQuery(
      "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
      [name],
    );
    return rows.isNotEmpty;
  }
}