import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import '../model/student.dart';
import 'package:excel/excel.dart';

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._instance();
  static Database? _database;

  DatabaseHelper._instance();

  Future<Database> get db async {
    _database ??= await initDb();
    return _database!;
  }

  Future<Database> initDb() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'student_university.db');
    return await openDatabase(path, version: 1, onCreate: _onCreate);
  }

  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE IF NOT EXISTS STUDENT (
        cardId TEXT PRIMARY KEY,
        studentId TEXT,
        firstName TEXT,
        name TEXT,
        promotion TEXT
      )
    ''');
  }

  Future<int> insertUser(Student student) async {
    Database db = await instance.db;
    return await db.insert('student', student.toMap());
  }

  Future<List<Map<String, dynamic>>> queryAllUsers() async {
    Database db = await instance.db;
    return await db.query('student');
  }

  Future<int> updateUser(Student student) async {
    Database db = await instance.db;
    return await db.update('student', student.toMap(), where: 'cardId = ?', whereArgs: [student.cardId]);
  }

  Future<int> deleteUser(String cardId) async {
    Database db = await instance.db;
    return await db.delete('student', where: 'cardId = ?', whereArgs: [cardId]);
  }

  Future<int> clear() async {
    Database db = await instance.db;
    return await db.delete('student');
  }

  Future<List<int>?> generateExcel(bool includeCardId) async {
    final List<Map<String, dynamic>> users = await queryAllUsers();
    if (users.isEmpty) {
      return null;
    }

    final excel = Excel.createExcel();
    Sheet sheetObject = excel['Students'];

    excel.delete('Sheet1');

    List<String> headerStrings = ['ID Étudiant', 'Prénom', 'Nom', 'Promotion'];
    if (includeCardId) {
      headerStrings.add('ID Carte');
    }

    var headerStyle = CellStyle(
        backgroundColorHex: ExcelColor.yellow300,
        fontFamily: getFontFamily(FontFamily.Calibri),
        bold: true,
        fontColorHex: ExcelColor.deepOrange600
    );

    for (var i = 0; i < headerStrings.length; i++) {
      var cell = sheetObject.cell(CellIndex.indexByColumnRow(columnIndex: i, rowIndex: 0));
      cell.value = TextCellValue(headerStrings[i]);
      cell.cellStyle = headerStyle;
    }

    for (var user in users) {
      List<String> rowStrings = [
        user['studentId'] ?? '',
        user['firstName'] ?? '',
        user['name'] ?? '',
        user['promotion'] ?? '',
      ];
      if (includeCardId) {
        rowStrings.add(user['cardId'] ?? '');
      }

      List<CellValue> row = rowStrings.map((cell) => TextCellValue(cell)).toList();
      sheetObject.appendRow(row);
    }

    return excel.save();
  }
}