import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:my_attendance/models/attendance_entry.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;
  DatabaseHelper._internal();

  static Database? _database;

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

  Future<Database> _initDatabase() async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, 'attendance.db');

    return await openDatabase(
      path,
      version: 2,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE attendance(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        loginTime TEXT,
        logoutTime TEXT,
        notes TEXT,
        entryType TEXT NOT NULL,
        leaveType TEXT
      )
    ''');
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      await db.execute('DROP TABLE IF EXISTS attendance');
      await _onCreate(db, newVersion);
    }
  }

  Future<int> insertAttendance(AttendanceEntry entry) async {
    final db = await database;
    return await db.insert('attendance', entry.toMap());
  }

  Future<int> updateAttendance(AttendanceEntry entry) async {
    final db = await database;
    return await db.update(
      'attendance',
      entry.toMap(),
      where: 'id = ?',
      whereArgs: [entry.id],
    );
  }

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

  Future<AttendanceEntry?> getAttendanceForDate(DateTime date) async {
    final db = await database;
    final dateString = date.toIso8601String().substring(0, 10);
    final List<Map<String, dynamic>> maps = await db.query(
      'attendance',
      where: 'date = ?',
      whereArgs: [dateString],
    );

    if (maps.isNotEmpty) {
      return AttendanceEntry.fromMap(maps.first);
    }
    return null;
  }

  Future<List<AttendanceEntry>> getAttendanceHistory({int limit = 10}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'attendance',
      orderBy: 'date DESC',
      limit: limit,
    );

    return List.generate(maps.length, (i) {
      return AttendanceEntry.fromMap(maps[i]);
    });
  }

  Future<void> resetDatabase() async {
    final db = await database;
    await db.delete('attendance');
  }

  Future<List<AttendanceEntry>> getAttendanceInRange(
    DateTime start,
    DateTime end,
  ) async {
    final db = await database;
    final startDate = start.toIso8601String().substring(0, 10);
    final endDate = end.toIso8601String().substring(0, 10);

    final List<Map<String, dynamic>> maps = await db.query(
      'attendance',
      where: 'date >= ? AND date <= ?',
      whereArgs: [startDate, endDate],
    );

    return List.generate(maps.length, (i) {
      return AttendanceEntry.fromMap(maps[i]);
    });
  }
}