Utopia-Canteen-System/Services/EmployeeLookupService.cs

using MySqlConnector;
using UtopiaCanteenSystem.Models;

namespace UtopiaCanteenSystem.Services;

/// <summary>
/// Looks up employee from local HRMS MySQL: employee_rfid_tag.manufacturer_serial → employee (parent_document_id = serial_number) → department.
/// Uses IConfigService.GetHrmsLookupConnectionString(); separate from production sync connection.
/// </summary>
public class EmployeeLookupService : IEmployeeLookupService
{
    private readonly IConfigService _configService;

    public EmployeeLookupService(IConfigService configService)
    {
        _configService = configService;
    }

    /// <inheritdoc />
    public async Task<HrmsEmployeeInfo?> GetEmployeeByRfidAsync(string rfid, CancellationToken cancellationToken = default)
    
    {
        var connectionString = _configService.GetHrmsLookupConnectionString();
        if (string.IsNullOrWhiteSpace(connectionString))
            return null;

        const string sql = @"
            SELECT 
              e.id AS parent_document_id,
              e.serial_number AS employee_id,
              e.concatenated_name AS first_name,
              '' AS middle_name,
              r.uind_serial AS uind_serial,
              r.function_id AS function_id,
              r.department_id AS tag_department_id,
              r.date_time_created AS tag_date_time_created,
              r.created_by AS tag_created_by,
              d.title AS department_title,
              d.department_type,
              r.location_site_id AS location_site_id,
              r.grade_type AS grade_type,
              e.position_title AS position_title
            FROM employee_rfid_tag r
            JOIN employee e ON e.id = r.parent_document_id
            LEFT JOIN department d ON d.id = e.department_id
            WHERE r.manufacturer_serial = @rfid
              AND r.parent_document_type = 'Employee'
            LIMIT 1";

        await using var conn = new MySqlConnection(connectionString);
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@rfid", rfid?.Trim() ?? string.Empty);

        await using var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
        if (!await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            return null;

        var parentDocumentId = GetString(reader, 0);
        return new HrmsEmployeeInfo
        {
            ParentDocumentId = parentDocumentId,
            EmployeeId = GetString(reader, 1),
            FirstName = GetString(reader, 2),
            MiddleName = GetString(reader, 3),
            UindSerial = GetString(reader, 4),
            FunctionId = GetInt(reader, 5),
            DepartmentId = GetInt(reader, 6),
            TagCreatedAtUtc = GetDateTimeNullable(reader, 7),
            TagCreatedBy = GetString(reader, 8),
            DepartmentTitle = GetString(reader, 9),
            DepartmentType = GetString(reader, 10),
            LocationSiteId = GetString(reader, 11),
            GradeType = GetString(reader, 12),
            PositionTitle = GetString(reader, 13)
        };
    }

    /// <inheritdoc />
    public async Task<string?> GetLocationSiteIdByEmployeeSerialAsync(string employeeSerial, CancellationToken cancellationToken = default)
    {
        var serial = employeeSerial?.Trim() ?? string.Empty;
        if (string.IsNullOrEmpty(serial))
            return null;

        var connectionString = _configService.GetHrmsLookupConnectionString();
        if (string.IsNullOrWhiteSpace(connectionString))
            return null;

        const string sql = @"
            SELECT location_site_id
            FROM employee
            WHERE serial_number = @serial
            LIMIT 1";

        await using var conn = new MySqlConnection(connectionString);
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@serial", serial);

        await using var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
        if (!await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            return null;

        if (reader.IsDBNull(0))
            return null;

        var raw = reader.GetValue(0)?.ToString()?.Trim();
        return string.IsNullOrEmpty(raw) ? null : raw;
    }

    /// <inheritdoc />
    public async Task<bool> IsMenuItemAuthorizedForRfidAsync(string rfid, int menuItemId, CancellationToken cancellationToken = default)
    {
        var card = rfid?.Trim() ?? string.Empty;
        if (string.IsNullOrEmpty(card) || menuItemId <= 0)
            return false;

        var connectionString = _configService.GetHrmsLookupConnectionString();
        if (string.IsNullOrWhiteSpace(connectionString))
            return false;

        const string sql = @"
            SELECT 1
            FROM employee_rfid_tag r
            JOIN employee_menu_item_tag em ON em.employee_rfid_tag_id = r.id
            WHERE r.manufacturer_serial = @rfid
              AND em.item_id = @itemId
            LIMIT 1";

        await using var conn = new MySqlConnection(connectionString);
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        await using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@rfid", card);
        cmd.Parameters.AddWithValue("@itemId", menuItemId);

        var exists = await cmd.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
        return exists != null && exists != DBNull.Value;
    }

    private static string GetString(MySqlDataReader reader, int ordinal)
    {
        if (reader.IsDBNull(ordinal)) return string.Empty;
        var v = reader.GetValue(ordinal);
        return v?.ToString() ?? string.Empty;
    }

    private static int GetInt(MySqlDataReader reader, int ordinal)
    {
        try
        {
            if (reader.IsDBNull(ordinal)) return 0;
            var v = reader.GetValue(ordinal);
            if (v is int i) return i;
            if (v is long l) return (int)l;
            return int.TryParse(v?.ToString(), out var parsed) ? parsed : 0;
        }
        catch
        {
            return 0;
        }
    }

    private static DateTime? GetDateTimeNullable(MySqlDataReader reader, int ordinal)
    {
        try
        {
            if (reader.IsDBNull(ordinal)) return null;
            var v = reader.GetValue(ordinal);
            if (v is DateTime dt) return dt;
            if (DateTime.TryParse(v?.ToString(), out var parsed)) return parsed;
            return null;
        }
        catch
        {
            return null;
        }
    }
}