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

class LocalDatabase {
  static Database? _database;
  static const String _dbName = 'packet.db';
  static const int _dbVersion = 1;

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

  static Future<Database> _initDatabase() async {
    final path = join(await getDatabasesPath(), _dbName);
    return openDatabase(
      path,
      version: _dbVersion,
      onCreate: _onCreate,
    );
  }

  static Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE registro (
        hash TEXT PRIMARY KEY,
        titulo TEXT,
        hora_envio TEXT NOT NULL,
        primera_conf TEXT,
        ultima_conf TEXT,
        destino_id INTEGER
      )
    ''');

    await db.execute('''
      CREATE TABLE destinos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nombre TEXT NOT NULL,
        url TEXT NOT NULL,
        hash TEXT NOT NULL,
        activo INTEGER DEFAULT 1
      )
    ''');

    await db.execute('''
      CREATE TABLE bibliotecas (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nombre TEXT NOT NULL,
        url TEXT NOT NULL,
        endpoint TEXT NOT NULL,
        h_biblioteca TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE etiquetas_cache (
        h_maestro TEXT PRIMARY KEY,
        h_global TEXT,
        mrf TEXT,
        ref TEXT,
        nombre_es TEXT,
        nombre_en TEXT,
        grupo TEXT,
        biblioteca_id INTEGER
      )
    ''');

    await db.execute('''
      CREATE TABLE packs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nombre TEXT NOT NULL,
        icono TEXT DEFAULT '📦',
        tags TEXT NOT NULL
      )
    ''');

    await db.execute('''
      CREATE TABLE pendientes (
        hash TEXT PRIMARY KEY,
        titulo TEXT,
        contenido BLOB NOT NULL,
        intentos INTEGER DEFAULT 0,
        ultimo_intento TEXT,
        proximo_intento TEXT,
        destino_id INTEGER
      )
    ''');

    await db.execute('''
      CREATE TABLE app_state (
        key TEXT PRIMARY KEY,
        value TEXT
      )
    ''');

    // Insert default HST biblioteca
    await db.insert('bibliotecas', {
      'nombre': 'HST',
      'url': 'https://tzrtech.org',
      'endpoint': '/api/tags',
      'h_biblioteca': 'b7149f9e2106c566032aeb29a26e4c6cdd5f5c16b4421025c58166ee345740d1',
    });
  }

  // App State methods
  static Future<void> setState(String key, String value) async {
    final db = await database;
    await db.insert(
      'app_state',
      {'key': key, 'value': value},
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  static Future<String?> getState(String key) async {
    final db = await database;
    final result = await db.query(
      'app_state',
      where: 'key = ?',
      whereArgs: [key],
    );
    if (result.isEmpty) return null;
    return result.first['value'] as String?;
  }
}