import 'dart:io';

import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart';
import 'package:plataforma_educativa/src/models/local_user_model.dart';
import 'package:plataforma_educativa/src/models/scan_model.dart';
import 'package:sqflite/sqflite.dart';

class DBProvider{
  static Database? _database;
  static final DBProvider db = DBProvider._();

  DBProvider._();

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

    return _database!;
  }

  initDB() async{
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, "PlatEduDB.db");
    
    return await openDatabase(
      path,
      version: 1,
      onOpen: (db){},
      onCreate: (db, version) async{
        await db.execute("CREATE TABLE SCANS(idEscuela TEXT, idUsuario TEXT, nombreEscuela TEXT, logoEscuela TEXT, PRIMARY KEY (idEscuela, idUsuario))");
        await db.execute("CREATE TABLE Users(idUsuario TEXT PRIMARY KEY, primerNombre TEXT, segundoNombre TEXT, tipoUsuario TEXT, apellidoPaterno TEXT, apellidoMaterno TEXT, active BOOLEAN, "
            "until DATETIME, secretKey TEXT, publicKey TEXT, grado TEXT, nivel TEXT, token TEXT)");
      }
    );
  }

  Future<int> newScan(ScanModel newScan) async{
    final db = await database;
    final res = db.insert('Scans', newScan.toJson());
    return res;
  }

  Future<int> newUserRaw(LocalUserModel userModel) async{
    final db = await database;
    final res = await db.rawInsert(
      "INSERT OR REPLACE INTO Users (idUsuario, primerNombre, segundoNombre, tipoUsuario, apellidoPaterno, apellidoMaterno, active, until, secretKey, publicKey, grado, nivel, token) "
          "VALUES ('${userModel.idUsuario}', '${userModel.primerNombre}', '${userModel.segundoNombre}', '${userModel.tipoUsuario}', '${userModel.apellidoPaterno}', '${userModel.apellidoMaterno}', "
          "'${userModel.active}', '${userModel.until}', '${userModel.secretKey}', '${userModel.publicKey}', '${userModel.grado}', '${userModel.nivel}', '${userModel.token}')"
    );
    return res;
  }

  Future<ScanModel?> getScanId(int id) async{
    final db = await database;
    final res = await db.query('Scans', where: 'id = ?', whereArgs: [id]);
    return res.isNotEmpty ? ScanModel.fromJson(res.first) : null;
  }

  Future<List<ScanModel>> getAllScans() async{
    final db = await database;
    final res = await db.query('Scans');
    List<ScanModel> list = res.isNotEmpty ? res.map((e) => ScanModel.fromJson(e)).toList() : [];
    return list;
  }

  Future<List<LocalUserModel>> getAllUsers() async{
    final db = await database;
    final res = await db.query('Users');
    List<LocalUserModel> list = res.isNotEmpty ? res.map((e) => LocalUserModel.fromJson(e)).toList() : [];
    return list;
  }

  Future<int> updateScan(ScanModel newScan) async{
    final db = await database;
    final res = await db.update('Scans', newScan.toJson(), where: 'id = ?', whereArgs: [newScan.idEscuela]);
    return res;
  }

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

  Future<int> deleteAll() async{
    final db = await database;
    final res = await db.delete('Scans');
    return res;
  }

  Future<int> deleteAllUsers() async{
    final db = await database;
    final res = await db.delete('Users');
    return res;
  }
}