import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import '../models/registered_device.dart';

const _tableName = 'siab_devices';

class SiabDatabase {
  static Database? _db;

  static Future<Database> get database async {
    if (_db != null) return _db!;
    _db = await _init();
    return _db!;
  }

  static Future<Database> _init() async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, 'siab_devices.db');
    return openDatabase(
      path,
      version: 2,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE $_tableName (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uuid TEXT NOT NULL UNIQUE,
            label TEXT NOT NULL
          )
        ''');
      },
      onUpgrade: (db, oldVersion, newVersion) async {
        // Simple destructive migration: drop old table and recreate with new schema.
        if (oldVersion < 2) {
          await db.execute('DROP TABLE IF EXISTS $_tableName');
          await db.execute('''
            CREATE TABLE $_tableName (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              uuid TEXT NOT NULL UNIQUE,
              label TEXT NOT NULL
            )
          ''');
        }
      },
    );
  }

  Future<int> insert(RegisteredDevice device) async {
    final db = await database;
    return db.insert(
      _tableName,
      device.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<List<RegisteredDevice>> getAll() async {
    final db = await database;
    final maps = await db.query(_tableName, orderBy: 'label ASC');
    return maps.map(RegisteredDevice.fromMap).toList();
  }

  Future<RegisteredDevice?> getByUuid(String uuid) async {
    final db = await database;
    final maps = await db.query(
      _tableName,
      where: 'uuid = ?',
      whereArgs: [uuid],
    );
    if (maps.isEmpty) return null;
    return RegisteredDevice.fromMap(maps.first);
  }

  Future<int> update(RegisteredDevice device) async {
    if (device.id == null) return 0;
    final db = await database;
    return db.update(
      _tableName,
      device.toMap(),
      where: 'id = ?',
      whereArgs: [device.id],
    );
  }

  Future<int> delete(int id) async {
    final db = await database;
    return db.delete(_tableName, where: 'id = ?', whereArgs: [id]);
  }

  Future<int> deleteByUuid(String uuid) async {
    final db = await database;
    return db.delete(_tableName, where: 'uuid = ?', whereArgs: [uuid]);
  }
}