import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/palm_record.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: 2,
      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
      )
    ''');
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      await db.execute('ALTER TABLE history ADD COLUMN detections TEXT');
    }
  }

  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<int> deleteRecord(int id) async {
    Database db = await database;
    return await db.delete('history', where: 'id = ?', whereArgs: [id]);
  }

  Future<int> clearAllRecords() async {
    Database db = await database;
    return await db.delete('history');
  }
}