content: import 'package:flutter/material.dart';
  import 'package:sqflite/sqflite.dart';
  import 'package:path/path.dart' as p;
  import 'package:path_provider/path_provider.dart';
  import 'dart:io';
  
  import 'package:todo/model/todo_model.dart';
  import 'package:todo/model/task_model.dart';
  import 'package:sqflite_common/sqlite_api.dart';
  import 'package:sqflite_common_ffi/sqflite_ffi.dart';
  
  class DBProvider {
    static Database? _database;
  
    DBProvider._();
    static final DBProvider db = DBProvider._();
  
    var todos = [
      Todo(
        "Vegetables",
        parent: '1',
      ),
      Todo(
        "Birthday gift",
        parent: '1',
      ),
      Todo("Chocolate cookies", parent: '1', isCompleted: 1),
      Todo(
        "20 pushups",
        parent: '2',
      ),
      Todo(
        "Tricep",
        parent: '2',
      ),
      Todo(
        "15 burpees (3 sets)",
        parent: '2',
      ),
    ];
  
    var tasks = [
      Task('Shopping',
          id: '1',
          color: Colors.purple.value,
          codePoint: Icons.shopping_cart.codePoint),
      Task('Workout',
          id: '2',
          color: Colors.pink.value,
          codePoint: Icons.fitness_center.codePoint),
    ];
  
    Future<Database> get database async {
      return _database ?? await initDB();
    }
  
    get _dbPath async {
      String documentsDirectory = await _localPath;
      return p.join(documentsDirectory, "Todo.db");
    }
  
    Future<bool> dbExists() async {
      return File(await _dbPath).exists();
    }
  
    initDB() async {
      String path = await _dbPath;
      if (Platform.isLinux || Platform.isMacOS || Platform.isWindows) {
        sqfliteFfiInit();
        var db = await databaseFactoryFfi.openDatabase(path);
        await db.execute("CREATE TABLE IF NOT EXISTS Task ("
            "id TEXT PRIMARY KEY,"
            "name TEXT,"
            "color INTEGER,"
            "code_point INTEGER"
            ")");
        await db.execute("CREATE TABLE IF NOT EXISTS Todo ("
            "id TEXT PRIMARY KEY,"
            "name TEXT,"
            "parent TEXT,"
            "completed INTEGER NOT NULL DEFAULT 0"
            ")");
        return db;
      }
      return await openDatabase(path, version: 1, onOpen: (db) {},
          onCreate: (Database db, int version) async {
        print("DBProvider:: onCreate()");
        await db.execute("CREATE TABLE IF NOT EXISTS Task ("
            "id TEXT PRIMARY KEY,"
            "name TEXT,"
            "color INTEGER,"
            "code_point INTEGER"
            ")");
        await db.execute("CREATE TABLE IF NOT EXISTS Todo ("
            "id TEXT PRIMARY KEY,"
            "name TEXT,"
            "parent TEXT,"
            "completed INTEGER NOT NULL DEFAULT 0"
            ")");
      });
    }
  
    insertBulkTask(List<Task> tasks) async {
      final db = await database;
      tasks.forEach((it) async {
        var res = await db.insert("Task", it.toJson());
        print("Task ${it.id} = $res");
      });
    }
  
    insertBulkTodo(List<Todo> todos) async {
      final db = await database;
      todos.forEach((it) async {
        var res = await db.insert("Todo", it.toJson());
        print("Todo ${it.id} = $res");
      });
    }
  
    Future<List<Task>> getAllTask() async {
      final db = await database;
      var result = await db.query('Task');
      return result.map((it) => Task.fromJson(it)).toList();
    }
  
    Future<List<Todo>> getAllTodo() async {
      final db = await database;
      var result = await db.query('Todo');
      return result.map((it) => Todo.fromJson(it)).toList();
    }
  
    Future<int> updateTodo(Todo todo) async {
      final db = await database;
      return db
          .update('Todo', todo.toJson(), where: 'id = ?', whereArgs: [todo.id]);
    }
  
    Future<int> removeTodo(Todo todo) async {
      final db = await database;
      return db.delete('Todo', where: 'id = ?', whereArgs: [todo.id]);
    }
  
    Future<int> insertTodo(Todo todo) async {
      final db = await database;
      return db.insert('Todo', todo.toJson());
    }
  
    Future<int> insertTask(Task task) async {
      final db = await database;
      return db.insert('Task', task.toJson());
    }
  
    Future<void> removeTask(Task task) async {
      final db = await database;
      return db.transaction<void>((txn) async {
        await txn.delete('Todo', where: 'parent = ?', whereArgs: [task.id]);
        await txn.delete('Task', where: 'id = ?', whereArgs: [task.id]);
      });
    }
  
    Future<int> updateTask(Task task) async {
      final db = await database;
      return db
          .update('Task', task.toJson(), where: 'id = ?', whereArgs: [task.id]);
    }
  
    Future<String> get _localPath async {
      final directory = await getApplicationDocumentsDirectory();
      return directory.path;
    }
  
    closeDB() {
      _database?.close();
    }
  }
  