palmoil-mobile/lib/services/database_helper.dart

import 'dart:io';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/palm_record.dart';
import '../models/palm_correction.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;
  DatabaseHelper._internal();

  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'palm_oil_history.db');
    return await openDatabase(
      path,
      version: 4,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        image_path TEXT,
        ripeness_class TEXT,
        confidence REAL,
        timestamp TEXT,
        x1 REAL,
        y1 REAL,
        x2 REAL,
        y2 REAL,
        detections TEXT,
        batch_id TEXT,
        inference_ms INTEGER
      )
    ''');
    await db.execute('CREATE INDEX idx_history_batch_id ON history(batch_id)');
    await _createCorrectionsTable(db);
  }

  Future<void> _createCorrectionsTable(Database db) async {
    await db.execute('''
      CREATE TABLE corrections (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        original_record_id INTEGER,
        corrected_class TEXT,
        corrected_detections TEXT,
        timestamp TEXT
      )
    ''');
    await db.execute('CREATE INDEX idx_corrections_original_record_id ON corrections(original_record_id)');
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      await db.execute('ALTER TABLE history ADD COLUMN detections TEXT');
    }
    if (oldVersion < 3) {
      await db.execute('ALTER TABLE history ADD COLUMN batch_id TEXT');
      await db.execute('ALTER TABLE history ADD COLUMN inference_ms INTEGER');
      await db.execute('CREATE INDEX idx_history_batch_id ON history(batch_id)');
    }
    if (oldVersion < 4) {
      await _createCorrectionsTable(db);
    }
  }

  Future<int> insertRecord(PalmRecord record) async {
    Database db = await database;
    return await db.insert('history', record.toMap());
  }

  Future<List<PalmRecord>> getAllRecords() async {
    Database db = await database;
    List<Map<String, dynamic>> maps = await db.query('history', orderBy: 'timestamp DESC');
    return List.generate(maps.length, (i) {
      return PalmRecord.fromMap(maps[i]);
    });
  }

  Future<List<PalmRecord>> getRecordsByBatch(String batchId) async {
    Database db = await database;
    List<Map<String, dynamic>> maps = await db.query(
      'history',
      where: 'batch_id = ?',
      whereArgs: [batchId],
      orderBy: 'timestamp ASC',
    );
    return List.generate(maps.length, (i) {
      return PalmRecord.fromMap(maps[i]);
    });
  }

  Future<int> deleteRecord(PalmRecord record) async {
    Database db = await database;
    await _deleteImageFile(record.imagePath);
    return await db.delete('history', where: 'id = ?', whereArgs: [record.id]);
  }

  Future<int> deleteBatch(String batchId) async {
    Database db = await database;
    final records = await getRecordsByBatch(batchId);
    for (final record in records) {
      await _deleteImageFile(record.imagePath);
    }
    return await db.delete('history', where: 'batch_id = ?', whereArgs: [batchId]);
  }

  Future<int> clearAllRecords() async {
    Database db = await database;
    final records = await getAllRecords();
    for (final record in records) {
      await _deleteImageFile(record.imagePath);
    }
    return await db.delete('history');
  }

  Future<int> insertCorrection(PalmCorrection correction) async {
    Database db = await database;
    return await db.insert('corrections', correction.toMap());
  }

  Future<int> deleteCorrectionsForRecord(int recordId) async {
    Database db = await database;
    return await db.delete('corrections', where: 'original_record_id = ?', whereArgs: [recordId]);
  }

  Future<PalmCorrection?> getLatestCorrection(int recordId) async {
    Database db = await database;
    List<Map<String, dynamic>> maps = await db.query(
      'corrections',
      where: 'original_record_id = ?',
      whereArgs: [recordId],
      orderBy: 'timestamp DESC',
      limit: 1,
    );
    if (maps.isEmpty) return null;
    return PalmCorrection.fromMap(maps.first);
  }

  Future<void> _deleteImageFile(String imagePath) async {
    try {
      final file = File(imagePath);
      if (await file.exists()) {
        await file.delete();
      }
    } catch (_) {
      // A missing/locked file shouldn't block the DB delete.
    }
  }
}