EnvelopeGenerator/EnvelopeGenerator.Jobs/Jobs/APIBackendJobs/APIEnvelopeJob.cs

using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using EnvelopeGenerator.Domain.Constants;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using Quartz;

namespace EnvelopeGenerator.Jobs.APIBackendJobs;

public class APIEnvelopeJob(ILogger<APIEnvelopeJob>? logger = null) : IJob
{
    private readonly ILogger<APIEnvelopeJob> _logger = logger ?? NullLogger<APIEnvelopeJob>.Instance;

    public async Task Execute(IJobExecutionContext context)
    {
        var jobId = context.JobDetail.Key.ToString();
        _logger.LogDebug("API Envelopes - Starting job {JobId}", jobId);

        try
        {
            var connectionString = context.MergedJobDataMap.GetString(Value.DATABASE);
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                _logger.LogWarning("API Envelopes - Connection string missing");
                return;
            }

            await using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync(context.CancellationToken);

            await ProcessInvitationsAsync(connection, context.CancellationToken);
            await ProcessWithdrawnAsync(connection, context.CancellationToken);

            _logger.LogDebug("API Envelopes - Completed job {JobId} successfully", jobId);
        }
        catch (System.Exception ex)
        {
            _logger.LogError(ex, "API Envelopes job failed");
        }
        finally
        {
            _logger.LogDebug("API Envelopes execution for {JobId} ended", jobId);
        }
    }

    private async Task ProcessInvitationsAsync(SqlConnection connection, System.Threading.CancellationToken cancellationToken)
    {
        const string sql = "SELECT GUID FROM TBSIG_ENVELOPE WHERE SOURCE = 'API' AND STATUS = 1003 ORDER BY GUID";
        var envelopeIds = new List<int>();

        await using (var command = new SqlCommand(sql, connection))
        await using (var reader = await command.ExecuteReaderAsync(cancellationToken))
        {
            while (await reader.ReadAsync(cancellationToken))
            {
                if (reader[0] is int id)
                {
                    envelopeIds.Add(id);
                }
            }
        }

        if (envelopeIds.Count == 0)
        {
            _logger.LogDebug("SendInvMail - No envelopes found");
            return;
        }

        _logger.LogInformation("SendInvMail - Found {Count} envelopes", envelopeIds.Count);
        var total = envelopeIds.Count;
        var current = 1;

        foreach (var id in envelopeIds)
        {
            _logger.LogInformation("SendInvMail - Processing Envelope {EnvelopeId} ({Current}/{Total})", id, current, total);
            try
            {
                // Placeholder for invitation email sending logic.
                _logger.LogDebug("SendInvMail - Marking envelope {EnvelopeId} as queued", id);
                const string updateSql = "UPDATE TBSIG_ENVELOPE SET CURRENT_WORK_APP = @App WHERE GUID = @Id";
                await using var updateCommand = new SqlCommand(updateSql, connection);
                updateCommand.Parameters.AddWithValue("@App", "signFLOW_API_EnvJob_InvMail");
                updateCommand.Parameters.AddWithValue("@Id", id);
                await updateCommand.ExecuteNonQueryAsync(cancellationToken);
            }
            catch (System.Exception ex)
            {
                _logger.LogWarning(ex, "SendInvMail - Unhandled exception while working envelope {EnvelopeId}", id);
            }

            current++;
            _logger.LogInformation("SendInvMail - Envelope finalized");
        }
    }

    private async Task ProcessWithdrawnAsync(SqlConnection connection, System.Threading.CancellationToken cancellationToken)
    {
        const string sql = @"SELECT ENV.GUID, REJ.COMMENT AS REJECTION_REASON FROM 
                (SELECT * FROM TBSIG_ENVELOPE WHERE STATUS = 1009 AND SOURCE = 'API') ENV INNER JOIN
                (SELECT MAX(GUID) GUID, ENVELOPE_ID, MAX(ADDED_WHEN) ADDED_WHEN, MAX(ACTION_DATE) ACTION_DATE, COMMENT FROM TBSIG_ENVELOPE_HISTORY WHERE STATUS = 1009 GROUP BY ENVELOPE_ID, COMMENT ) REJ ON ENV.GUID = REJ.ENVELOPE_ID LEFT JOIN
                (SELECT * FROM TBSIG_ENVELOPE_HISTORY WHERE STATUS = 3004 ) M_Send ON ENV.GUID = M_Send.ENVELOPE_ID
                WHERE M_Send.GUID IS NULL";

        var withdrawn = new List<(int EnvelopeId, string Reason)>();
        await using (var command = new SqlCommand(sql, connection))
        await using (var reader = await command.ExecuteReaderAsync(cancellationToken))
        {
            while (await reader.ReadAsync(cancellationToken))
            {
                var id = reader.GetInt32(0);
                var reason = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
                withdrawn.Add((id, reason));
            }
        }

        if (withdrawn.Count == 0)
        {
            _logger.LogDebug("WithdrawnEnv - No envelopes found");
            return;
        }

        _logger.LogInformation("WithdrawnEnv - Found {Count} envelopes", withdrawn.Count);
        var total = withdrawn.Count;
        var current = 1;

        foreach (var (envelopeId, reason) in withdrawn)
        {
            _logger.LogInformation("WithdrawnEnv - Processing Envelope {EnvelopeId} ({Current}/{Total})", envelopeId, current, total);
            try
            {
                // Log withdrawn mail trigger placeholder
                const string insertHistory = "INSERT INTO TBSIG_ENVELOPE_HISTORY (ENVELOPE_ID, STATUS, USER_REFERENCE, ADDED_WHEN, ACTION_DATE, COMMENT) VALUES (@EnvelopeId, @Status, @UserReference, GETDATE(), GETDATE(), @Comment)";
                await using var insertCommand = new SqlCommand(insertHistory, connection);
                insertCommand.Parameters.AddWithValue("@EnvelopeId", envelopeId);
                insertCommand.Parameters.AddWithValue("@Status", 3004);
                insertCommand.Parameters.AddWithValue("@UserReference", "API");
                insertCommand.Parameters.AddWithValue("@Comment", reason ?? string.Empty);
                await insertCommand.ExecuteNonQueryAsync(cancellationToken);
            }
            catch (System.Exception ex)
            {
                _logger.LogWarning(ex, "WithdrawnEnv - Unhandled exception while working envelope {EnvelopeId}", envelopeId);
            }

            current++;
            _logger.LogInformation("WithdrawnEnv - Envelope finalized");
        }
    }
}