using Microsoft.Data.Sqlite;
using FaceRecognitionApp.Models;

namespace FaceRecognitionApp.Services;

public class DatabaseService
{
    private readonly string _connectionString = "Data Source=faces.db";

    public DatabaseService()
    {
        InitializeDatabase();
    }

    private void InitializeDatabase()
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var cmd = connection.CreateCommand();
        cmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS Employees (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                Department TEXT,
                FaceDescriptor BLOB,
                Photo BLOB
            )";
        cmd.ExecuteNonQuery();
    }

    public void AddEmployee(string name, string? dept, byte[] descriptor, byte[] photo)
    {
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var cmd = connection.CreateCommand();
        cmd.CommandText = "INSERT INTO Employees (Name, Department, FaceDescriptor, Photo) VALUES (@n, @d, @desc, @p)";
        cmd.Parameters.AddWithValue("@n", name);
        cmd.Parameters.AddWithValue("@d", (object?)dept ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@desc", descriptor);
        cmd.Parameters.AddWithValue("@p", photo);
        cmd.ExecuteNonQuery();
    }

    public List<Employee> GetAllEmployees()
    {
        var list = new List<Employee>();
        using var connection = new SqliteConnection(_connectionString);
        connection.Open();
        var cmd = connection.CreateCommand();
        cmd.CommandText = "SELECT * FROM Employees";
        using var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            list.Add(new Employee
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                Department = reader.IsDBNull(2) ? null : reader.GetString(2),
                FaceDescriptor = (byte[])reader[3],
                Photo = reader.IsDBNull(4) ? null : (byte[])reader[4]
            });
        }
        return list;
    }
}