import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import '../model/student.dart';
import 'package:excel/excel.dart';
import 'dart:io';
import 'package:path_provider/path_provider.dart';
import 'package:permission_handler/permission_handler.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<String?> generateExcel(bool includeCardId, {String? filiere, String? matiere}) async {
    var status = await Permission.storage.status;
    if (!status.isGranted) {
      await Permission.storage.request();
    }

    final List<Map<String, dynamic>> users = await queryAllUsers();
    if (users.isEmpty) {
      return null;
    }

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

    List<String> headerStrings = ['ID Étudiant', 'Prénom', 'Nom', 'Promotion'];
    if (includeCardId) {
      headerStrings.add('ID Carte');
    }
    List<CellValue> headers = headerStrings.map((header) => TextCellValue(header)).toList();
    sheetObject.appendRow(headers);

    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);
    }

    Directory? directory = await getExternalStorageDirectory();
    if (directory != null) {
      String finalPath = directory.path;
      String fileName = 'etudiants.xlsx';

      if (filiere != null && filiere.isNotEmpty && matiere != null && matiere.isNotEmpty) {
        finalPath = join(directory.path, filiere);
        fileName = '$matiere.xlsx';
      }

      await Directory(finalPath).create(recursive: true);
      String filePath = join(finalPath, fileName);

      File(filePath)
        ..createSync(recursive: true)
        ..writeAsBytesSync(excel.save()!);
      return filePath;
    }
    return null;
  }
}