using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace HikvisionAttendanceService.Data;

internal sealed class MySqlAttendanceMachineRepository : IAttendanceMachineRepository
{
    private readonly IHrmsDbConnectionFactory _factory;
    public MySqlAttendanceMachineRepository(IHrmsDbConnectionFactory factory) { _factory = factory; }

    public List<AttendanceMachineRow> GetActiveMachines(string? machineTypeFilter, out string error)
    {
        error = "";
        var rows = new List<AttendanceMachineRow>();
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return rows;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            string sql = "SELECT machine_id,machine_ip,port_number,machine_name,site_id,machine_status,machine_type,status,last_sync_date,total_users " +
                         "FROM attendance_machine WHERE (machine_status='active' OR machine_status='1' OR machine_status=1)";
            if (!string.IsNullOrWhiteSpace(machineTypeFilter))
                sql += " AND UPPER(TRIM(machine_type))=UPPER(TRIM(@machineType))";
            using var cmd = new MySqlCommand(sql, conn);
            if (!string.IsNullOrWhiteSpace(machineTypeFilter))
                cmd.Parameters.AddWithValue("@machineType", machineTypeFilter);
            using var rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                rows.Add(new AttendanceMachineRow
                {
                    MachineId = rd["machine_id"]?.ToString() ?? "",
                    MachineIp = rd["machine_ip"]?.ToString() ?? "",
                    PortNumber = rd["port_number"] == DBNull.Value ? 8000 : Convert.ToInt32(rd["port_number"]),
                    MachineName = rd["machine_name"]?.ToString() ?? "",
                    SiteId = rd["site_id"] == DBNull.Value ? (int?)null : Convert.ToInt32(rd["site_id"]),
                    MachineStatus = rd["machine_status"]?.ToString() ?? "",
                    MachineType = rd["machine_type"]?.ToString() ?? "",
                    Status = rd["status"]?.ToString() ?? "",
                    LastSyncDate = rd["last_sync_date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(rd["last_sync_date"]),
                    TotalUsers = rd["total_users"] == DBNull.Value ? 0 : Convert.ToInt32(rd["total_users"])
                });
            }
        }
        catch (Exception ex) { error = ex.Message; }
        return rows;
    }

    public bool TryGetMachineByIp(string machineIp, out AttendanceMachineRow? row, out string error)
    {
        row = null;
        error = "";
        var wanted = (machineIp ?? "").Trim();
        if (wanted.Length == 0)
        {
            error = "empty machine_ip";
            return false;
        }

        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;

        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT machine_id,machine_ip,port_number,machine_name,site_id,machine_status,machine_type,status,last_sync_date,total_users " +
                "FROM attendance_machine WHERE TRIM(machine_ip)=@machine_ip LIMIT 1", conn);
            cmd.Parameters.AddWithValue("@machine_ip", wanted);
            using var rd = cmd.ExecuteReader();
            if (!rd.Read())
                return false;

            row = ReadAttendanceMachineRow(rd);
            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            return false;
        }
    }

    public bool TryGetMachineByMachineId(string machineId, out AttendanceMachineRow? row, out string error)
    {
        row = null;
        error = "";
        var wanted = (machineId ?? "").Trim();
        if (wanted.Length == 0)
        {
            error = "empty machine_id";
            return false;
        }

        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;

        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT machine_id,machine_ip,port_number,machine_name,site_id,machine_status,machine_type,status,last_sync_date,total_users " +
                "FROM attendance_machine WHERE TRIM(machine_id)=@machine_id LIMIT 1", conn);
            cmd.Parameters.AddWithValue("@machine_id", wanted);
            using var rd = cmd.ExecuteReader();
            if (!rd.Read())
                return false;

            row = ReadAttendanceMachineRow(rd);
            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            return false;
        }
    }

    private static AttendanceMachineRow ReadAttendanceMachineRow(MySqlDataReader rd) =>
        new AttendanceMachineRow
        {
            MachineId = rd["machine_id"]?.ToString() ?? "",
            MachineIp = rd["machine_ip"]?.ToString() ?? "",
            PortNumber = rd["port_number"] == DBNull.Value ? 8000 : Convert.ToInt32(rd["port_number"]),
            MachineName = rd["machine_name"]?.ToString() ?? "",
            SiteId = rd["site_id"] == DBNull.Value ? (int?)null : Convert.ToInt32(rd["site_id"]),
            MachineStatus = rd["machine_status"]?.ToString() ?? "",
            MachineType = rd["machine_type"]?.ToString() ?? "",
            Status = rd["status"]?.ToString() ?? "",
            LastSyncDate = rd["last_sync_date"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(rd["last_sync_date"]),
            TotalUsers = rd["total_users"] == DBNull.Value ? 0 : Convert.ToInt32(rd["total_users"])
        };

    public void UpdateMachineSyncState(string machineIp, string status, DateTime? lastSyncDate, int? totalUsers, out string error)
    {
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "UPDATE attendance_machine SET status=@status,last_sync_date=COALESCE(@last_sync_date,last_sync_date),total_users=COALESCE(@total_users,total_users) WHERE machine_ip=@machine_ip",
                conn);
            cmd.Parameters.AddWithValue("@status", status ?? "");
            cmd.Parameters.AddWithValue("@last_sync_date", (object?)lastSyncDate ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@total_users", (object?)totalUsers ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@machine_ip", machineIp ?? "");
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex) { error = ex.Message; }
    }

    public bool TryGetMachineLastSyncDate(string machineIp, out DateTime? lastSyncDate, out string error)
    {
        lastSyncDate = null;
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT last_sync_date FROM attendance_machine WHERE TRIM(machine_ip)=TRIM(@machine_ip) LIMIT 1",
                conn);
            cmd.Parameters.AddWithValue("@machine_ip", machineIp ?? "");
            var obj = cmd.ExecuteScalar();
            if (obj == null || obj == DBNull.Value)
                return true;
            // MySQL DATETIME has no timezone — keep clock components as site wall-clock.
            var dt = Convert.ToDateTime(obj);
            lastSyncDate = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, DateTimeKind.Unspecified);
            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            return false;
        }
    }

    public bool UpdateMachineLastSyncDate(string machineIp, DateTime lastSyncDate, out string error) =>
        UpdateMachineLastSyncDateInternal("machine_ip", machineIp, lastSyncDate, out error);

    public bool UpdateMachineLastSyncDateByMachineId(string machineId, DateTime lastSyncDate, out string error) =>
        UpdateMachineLastSyncDateInternal("machine_id", machineId, lastSyncDate, out error);

    private bool UpdateMachineLastSyncDateInternal(string keyColumn, string keyValue, DateTime lastSyncDate, out string error)
    {
        error = "";
        var wanted = (keyValue ?? "").Trim();
        if (wanted.Length == 0)
        {
            error = "empty " + keyColumn;
            return false;
        }

        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "UPDATE attendance_machine SET last_sync_date=@last_sync_date WHERE TRIM(" + keyColumn + ")=TRIM(@" + keyColumn + ")",
                conn);
            cmd.Parameters.AddWithValue("@last_sync_date",
                new DateTime(lastSyncDate.Year, lastSyncDate.Month, lastSyncDate.Day,
                    lastSyncDate.Hour, lastSyncDate.Minute, lastSyncDate.Second, DateTimeKind.Unspecified));
            cmd.Parameters.AddWithValue("@" + keyColumn, wanted);
            var rows = cmd.ExecuteNonQuery();
            if (rows <= 0)
            {
                error = "no attendance_machine row matched " + keyColumn + "=" + wanted;
                return false;
            }

            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            return false;
        }
    }
}

internal sealed class MySqlAttendanceLogRepository : IAttendanceLogRepository
{
    private readonly IHrmsDbConnectionFactory _factory;
    public MySqlAttendanceLogRepository(IHrmsDbConnectionFactory factory) { _factory = factory; }

    // Confirmed via SHOW CREATE TABLE / DESCRIBE hrms.attendance_log
    private static readonly (string Param, string Column, string MySqlType)[] AttendanceLogParamMeta =
    {
        ("@ac_no", "ac_no", "varchar(24) NOT NULL"),
        ("@checktime", "checktime", "datetime NOT NULL"),
        ("@processed", "processed", "int NULL"),
        ("@machine_id", "machine_id", "varchar(45) NULL"),
        ("@in_out_type_id", "in_out_type_id", "int NULL"),
        ("@machine_ip", "machine_ip", "varchar(50) NULL"),
        ("@date", "date", "datetime NULL"),
    };

    public bool InsertAttendance(
        string acNo,
        int acNoInt,
        DateTime checkTime,
        int processed,
        string machineId,
        int inOutTypeId,
        string machineIp,
        DateTime dateOnly,
        Action<string>? log,
        out string error)
    {
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;

        // Guard: empty ac_no breaks generated column ac_no_int = CAST(ac_no AS UNSIGNED)
        // ("Truncated incorrect INTEGER value: ''").
        if (string.IsNullOrWhiteSpace(acNo))
        {
            error = "refused insert: ac_no is empty (would break generated ac_no_int CAST)";
            log?.Invoke("[AttendanceInsert] REFUSED: " + error);
            return false;
        }

        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            // Plain INSERT (not REPLACE) until field mapping is confirmed.
            const string sql =
                "INSERT INTO attendance_log (ac_no,checktime,processed,machine_id,in_out_type_id,machine_ip,date) " +
                "VALUES (@ac_no,@checktime,@processed,@machine_id,@in_out_type_id,@machine_ip,@date)";

            using var cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Add("@ac_no", MySqlDbType.VarChar, 24).Value = acNo;
            cmd.Parameters.Add("@checktime", MySqlDbType.DateTime).Value = checkTime;
            cmd.Parameters.Add("@processed", MySqlDbType.Int32).Value = processed;
            cmd.Parameters.Add("@machine_id", MySqlDbType.VarChar, 45).Value =
                string.IsNullOrWhiteSpace(machineId) ? (object)DBNull.Value : machineId;
            cmd.Parameters.Add("@in_out_type_id", MySqlDbType.Int32).Value = inOutTypeId;
            cmd.Parameters.Add("@machine_ip", MySqlDbType.VarChar, 50).Value =
                string.IsNullOrWhiteSpace(machineIp) ? (object)DBNull.Value : machineIp;
            cmd.Parameters.Add("@date", MySqlDbType.DateTime).Value = dateOnly.Date;

            log?.Invoke("[AttendanceInsert] SQL=" + sql);
            log?.Invoke("[AttendanceInsert] NOTE: for_date is GENERATED STORED from checktime; ac_no_int is GENERATED VIRTUAL CAST(ac_no AS UNSIGNED). Neither is bound. cardNo is NOT inserted.");
            log?.Invoke("[AttendanceInsert] Parsed employee int (source employeeNoString) acNoInt=" + acNoInt +
                        " (not a bound param; drives @ac_no string value)");
            foreach (var meta in AttendanceLogParamMeta)
            {
                var p = cmd.Parameters[meta.Param];
                var val = p.Value;
                var runtimeType = val == null || val == DBNull.Value ? "DBNull" : val.GetType().FullName;
                var display = val == null || val == DBNull.Value ? "<DBNull>" : Convert.ToString(val, System.Globalization.CultureInfo.InvariantCulture);
                log?.Invoke(
                    "[AttendanceInsert] param=" + meta.Param +
                    " value=\"" + display + "\"" +
                    " csharpType=" + runtimeType +
                    " column=" + meta.Column +
                    " mysqlType=" + meta.MySqlType);
            }

            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            log?.Invoke("[AttendanceInsert] FAILED err=" + ex.Message +
                        " ac_no=\"" + (acNo ?? "") + "\" acNoInt=" + acNoInt +
                        " machine_id=\"" + (machineId ?? "") + "\" machine_ip=\"" + (machineIp ?? "") + "\"" +
                        " checktime=" + checkTime.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) +
                        " in_out_type_id=" + inOutTypeId);
            return false;
        }
    }

    public bool InsertTerryAttendance(
        string acNo,
        DateTime checkTime,
        int processed,
        string machineId,
        int inOutTypeId,
        string machineIp,
        DateTime dateOnly,
        DateTime forDate,
        Action<string>? log,
        out bool wasDuplicate,
        out string error)
    {
        wasDuplicate = false;
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;

        if (string.IsNullOrWhiteSpace(acNo))
        {
            error = "refused insert: ac_no is empty";
            log?.Invoke("[TerryAttendanceInsert] REFUSED: " + error);
            return false;
        }

        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();

            // Same punch identity as attendance_log conceptual duplicate protection (ac_no + checktime + machine),
            // persisted so contractor punches are not re-inserted across polling cycles / restarts.
            const string existsSql =
                "SELECT 1 FROM terry_attendance_log " +
                "WHERE ac_no=@ac_no AND checktime=@checktime " +
                "AND ((machine_id IS NULL AND @machine_id IS NULL) OR machine_id=@machine_id) " +
                "LIMIT 1";
            using (var existsCmd = new MySqlCommand(existsSql, conn))
            {
                existsCmd.Parameters.Add("@ac_no", MySqlDbType.VarChar, 24).Value = acNo;
                existsCmd.Parameters.Add("@checktime", MySqlDbType.DateTime).Value = checkTime;
                existsCmd.Parameters.Add("@machine_id", MySqlDbType.VarChar, 45).Value =
                    string.IsNullOrWhiteSpace(machineId) ? (object)DBNull.Value : machineId;
                var exists = existsCmd.ExecuteScalar();
                if (exists != null && exists != DBNull.Value)
                {
                    wasDuplicate = true;
                    error = "duplicate punch already in terry_attendance_log";
                    log?.Invoke("[TerryAttendanceInsert] DUPLICATE ac_no=\"" + acNo +
                                "\" checktime=" + checkTime.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) +
                                " machine_id=\"" + (machineId ?? "") + "\"");
                    return false;
                }
            }

            const string sql =
                "INSERT INTO terry_attendance_log " +
                "(checktime,ac_no,processed,machine_id,in_out_type_id,machine_ip,date,for_date) " +
                "VALUES (@checktime,@ac_no,@processed,@machine_id,@in_out_type_id,@machine_ip,@date,@for_date)";

            using var cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Add("@checktime", MySqlDbType.DateTime).Value = checkTime;
            cmd.Parameters.Add("@ac_no", MySqlDbType.VarChar, 24).Value = acNo;
            cmd.Parameters.Add("@processed", MySqlDbType.Int32).Value = processed;
            cmd.Parameters.Add("@machine_id", MySqlDbType.VarChar, 45).Value =
                string.IsNullOrWhiteSpace(machineId) ? (object)DBNull.Value : machineId;
            cmd.Parameters.Add("@in_out_type_id", MySqlDbType.Int32).Value = inOutTypeId;
            cmd.Parameters.Add("@machine_ip", MySqlDbType.VarChar, 50).Value =
                string.IsNullOrWhiteSpace(machineIp) ? (object)DBNull.Value : machineIp;
            cmd.Parameters.Add("@date", MySqlDbType.DateTime).Value = dateOnly.Date;
            cmd.Parameters.Add("@for_date", MySqlDbType.Date).Value = forDate.Date;

            log?.Invoke("[TerryAttendanceInsert] SQL=" + sql);
            log?.Invoke("[TerryAttendanceInsert] ac_no=\"" + acNo +
                        "\" checktime=" + checkTime.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) +
                        " machine_id=\"" + (machineId ?? "") + "\" machine_ip=\"" + (machineIp ?? "") + "\"" +
                        " in_out_type_id=" + inOutTypeId +
                        " date=" + dateOnly.Date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) +
                        " for_date=" + forDate.Date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture));

            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            log?.Invoke("[TerryAttendanceInsert] FAILED err=" + ex.Message +
                        " ac_no=\"" + (acNo ?? "") + "\"" +
                        " machine_id=\"" + (machineId ?? "") + "\" machine_ip=\"" + (machineIp ?? "") + "\"" +
                        " checktime=" + checkTime.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture));
            return false;
        }
    }
}

internal sealed class MySqlEmployeeLookupRepository : IEmployeeLookupRepository
{
    private readonly IHrmsDbConnectionFactory _factory;
    public MySqlEmployeeLookupRepository(IHrmsDbConnectionFactory factory) { _factory = factory; }

    public bool TryGetWorkerTypeBySerialNumberInt(int serialNumberInt, out string? workerType, out string error)
    {
        workerType = null;
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;

        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT worker_type FROM employee WHERE serial_number_int=@serial_number_int LIMIT 1",
                conn);
            cmd.Parameters.AddWithValue("@serial_number_int", serialNumberInt);
            var result = cmd.ExecuteScalar();
            if (result == null || result == DBNull.Value)
                return false;

            workerType = Convert.ToString(result, System.Globalization.CultureInfo.InvariantCulture)?.Trim();
            return true;
        }
        catch (Exception ex)
        {
            error = ex.Message;
            return false;
        }
    }
}

internal sealed class MySqlAttendanceMachineUserRepository : IAttendanceMachineUserRepository
{
    private readonly IHrmsDbConnectionFactory _factory;
    public MySqlAttendanceMachineUserRepository(IHrmsDbConnectionFactory factory) { _factory = factory; }

    public List<AttendanceMachineUserRow> GetActiveUsersByMachine(string machineId, out string error) =>
        QueryUsers(machineId, false, out error);

    public List<AttendanceMachineUserRow> GetPendingDeletionUsersByMachine(string machineId, out string error) =>
        QueryUsers(machineId, true, out error);

    private List<AttendanceMachineUserRow> QueryUsers(string machineId, bool deletionRequested, out string error)
    {
        error = "";
        var rows = new List<AttendanceMachineUserRow>();
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return rows;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT machine_id,serial_number,employee_name,is_deleted,is_deletion_requested FROM attendance_machine_user " +
                "WHERE machine_id=@machine_id AND is_deleted=0 AND is_deletion_requested=@is_deletion_requested",
                conn);
            cmd.Parameters.AddWithValue("@machine_id", machineId ?? "");
            cmd.Parameters.AddWithValue("@is_deletion_requested", deletionRequested ? 1 : 0);
            using var rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                rows.Add(new AttendanceMachineUserRow
                {
                    MachineId = rd["machine_id"]?.ToString() ?? "",
                    SerialNumber = rd["serial_number"]?.ToString() ?? "",
                    EmployeeName = rd["employee_name"]?.ToString() ?? "",
                    IsDeleted = Convert.ToInt32(rd["is_deleted"]) != 0,
                    IsDeletionRequested = Convert.ToInt32(rd["is_deletion_requested"]) != 0
                });
            }
        }
        catch (Exception ex) { error = ex.Message; }
        return rows;
    }

    public bool UpsertMachineUser(string machineId, string serialNumber, string employeeName, string updatedBy, out string error)
    {
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "REPLACE INTO attendance_machine_user (machine_id,serial_number,employee_name,is_deleted,is_deletion_requested,updated_by,updated_at) " +
                "VALUES (@machine_id,@serial_number,@employee_name,0,0,@updated_by,NOW())", conn);
            cmd.Parameters.AddWithValue("@machine_id", machineId ?? "");
            cmd.Parameters.AddWithValue("@serial_number", serialNumber ?? "");
            cmd.Parameters.AddWithValue("@employee_name", employeeName ?? "");
            cmd.Parameters.AddWithValue("@updated_by", string.IsNullOrWhiteSpace(updatedBy) ? "hikvision-service" : updatedBy.Trim());
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex) { error = ex.Message; return false; }
    }

    public bool MarkDeleted(string machineId, string serialNumber, string updatedBy, out string error)
    {
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "UPDATE attendance_machine_user SET is_deleted=1,is_deletion_requested=0,updated_by=@updated_by,updated_at=NOW() " +
                "WHERE machine_id=@machine_id AND serial_number=@serial_number", conn);
            cmd.Parameters.AddWithValue("@updated_by", updatedBy ?? "hikvision-service");
            cmd.Parameters.AddWithValue("@machine_id", machineId ?? "");
            cmd.Parameters.AddWithValue("@serial_number", serialNumber ?? "");
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex) { error = ex.Message; return false; }
    }
}

internal sealed class MySqlAttendanceMachineFaceTemplateRepository : IAttendanceMachineFaceTemplateRepository
{
    private readonly IHrmsDbConnectionFactory _factory;
    public MySqlAttendanceMachineFaceTemplateRepository(IHrmsDbConnectionFactory factory) { _factory = factory; }

    public bool UpsertFaceTemplate(string serialNo, byte[] template, DateTime createdDate, bool isActive, string? sourceMachineId, string? sourceMachineIp, out bool createdNew, out string error)
    {
        error = "";
        createdNew = true;
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();

            // Skip rewrite when an active non-empty template is already stored for this employee.
            using (var existsCmd = new MySqlCommand(
                       "SELECT 1 FROM attendance_machine_face_templates " +
                       "WHERE serial_no=@serial_no AND is_active=1 AND template IS NOT NULL AND LENGTH(template)>0 " +
                       "LIMIT 1", conn))
            {
                existsCmd.Parameters.AddWithValue("@serial_no", serialNo ?? "");
                if (existsCmd.ExecuteScalar() != null)
                {
                    createdNew = false;
                    return true; // already present — do not UPDATE / re-INSERT
                }
            }

            // Insert new, or fill/replace an empty/inactive row for the same serial.
            using (var existsAnyCmd = new MySqlCommand(
                       "SELECT 1 FROM attendance_machine_face_templates WHERE serial_no=@serial_no LIMIT 1", conn))
            {
                existsAnyCmd.Parameters.AddWithValue("@serial_no", serialNo ?? "");
                createdNew = existsAnyCmd.ExecuteScalar() == null;
            }

            if (createdNew)
            {
                using var insert = new MySqlCommand(
                    "INSERT INTO attendance_machine_face_templates (serial_no,template,created_date,is_active) " +
                    "VALUES (@serial_no,@template,@created_date,@is_active)", conn);
                insert.Parameters.AddWithValue("@serial_no", serialNo ?? "");
                insert.Parameters.Add("@template", MySqlDbType.Blob).Value = (object?)template ?? Array.Empty<byte>();
                insert.Parameters.AddWithValue("@created_date", createdDate);
                insert.Parameters.AddWithValue("@is_active", isActive ? 1 : 0);
                insert.ExecuteNonQuery();
            }
            else
            {
                using var update = new MySqlCommand(
                    "UPDATE attendance_machine_face_templates SET template=@template, created_date=@created_date, is_active=@is_active " +
                    "WHERE serial_no=@serial_no", conn);
                update.Parameters.AddWithValue("@serial_no", serialNo ?? "");
                update.Parameters.Add("@template", MySqlDbType.Blob).Value = (object?)template ?? Array.Empty<byte>();
                update.Parameters.AddWithValue("@created_date", createdDate);
                update.Parameters.AddWithValue("@is_active", isActive ? 1 : 0);
                update.ExecuteNonQuery();
                createdNew = true; // treat fill of empty row as a new save for caller logging
            }

            // Best-effort source metadata update for schemas that include these columns.
            if (!string.IsNullOrWhiteSpace(sourceMachineId) || !string.IsNullOrWhiteSpace(sourceMachineIp))
            {
                try
                {
                    using var metaCmd = new MySqlCommand(
                        "UPDATE attendance_machine_face_templates SET source_machine_id=@source_machine_id, source_machine_ip=@source_machine_ip WHERE serial_no=@serial_no",
                        conn);
                    metaCmd.Parameters.AddWithValue("@source_machine_id", (object?)sourceMachineId ?? DBNull.Value);
                    metaCmd.Parameters.AddWithValue("@source_machine_ip", (object?)sourceMachineIp ?? DBNull.Value);
                    metaCmd.Parameters.AddWithValue("@serial_no", serialNo ?? "");
                    metaCmd.ExecuteNonQuery();
                }
                catch
                {
                    // Older schemas may not have source metadata columns; keep core template write successful.
                }
            }
            return true;
        }
        catch (Exception ex) { error = ex.Message; return false; }
    }

    public bool TryGetActiveFaceTemplateBySerial(string serialNo, out AttendanceMachineFaceTemplateRow? row, out string error)
    {
        row = null;
        error = "";
        if (!_factory.TryBuildConnectionString(out var cs, out error))
            return false;
        try
        {
            using var conn = new MySqlConnection(cs);
            conn.Open();
            using var cmd = new MySqlCommand(
                "SELECT serial_no,template,created_date,is_active FROM attendance_machine_face_templates " +
                "WHERE serial_no=@serial_no AND is_active=1 ORDER BY created_date DESC LIMIT 1", conn);
            cmd.Parameters.AddWithValue("@serial_no", serialNo ?? "");
            using var rd = cmd.ExecuteReader();
            if (!rd.Read())
                return false;
            row = new AttendanceMachineFaceTemplateRow
            {
                SerialNo = rd["serial_no"]?.ToString() ?? "",
                Template = rd["template"] == DBNull.Value ? Array.Empty<byte>() : (byte[])rd["template"],
                CreatedDate = rd["created_date"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(rd["created_date"]),
                IsActive = rd["is_active"] != DBNull.Value && Convert.ToInt32(rd["is_active"]) != 0
            };
            return true;
        }
        catch (Exception ex) { error = ex.Message; return false; }
    }
}