using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.WorkFlow;
using GB5Shared.Query.WorkFlow;
using GB5Shared.QueryExecutor;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;

namespace GB5Shared.WorkFlow.WorkFlowRunTime
{
    public class WorkFlowRunTime : IWorkFlowRunTime
    {
        private readonly IQueryExecutor _qe;

        public WorkFlowRunTime(IQueryExecutor queryExecutor)
        {
            _qe = queryExecutor;
        }

        // ── Config resolution ─────────────────────────────────────────────────

        public async Task<List<WorkflowConfigDTO>> GetWorkflowConfigsAsync(
            int entityId,
            int clientId,
            int ouId,
            int bizTransactionClassId,
            int bizTransactionId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                // If any MWORKFLOWCONFIG row for this entity/client has OUID = -1 (wildcard),
                // pass effectiveOuId = -1 so the main query skips the OUID filter entirely.
                var hasWildcardOu = await _qe.ExecuteScalarAsync<int>(
                    loginDTO, WorkFlowQB.HasWildcardOuConfig,
                    new
                    {
                        entityid              = entityId,
                        clientid              = clientId,
                        biztransactionclassid = bizTransactionClassId
                    },
                    dbTransaction);

                var effectiveOuId = hasWildcardOu > 0 ? -1 : ouId;

                var param = new
                {
                    entityid              = entityId,
                    clientid              = clientId,
                    ouid                  = effectiveOuId,
                    biztransactionclassid = bizTransactionClassId,
                    biztransactionid      = bizTransactionId
                };

                var result = await _qe.QueryAsync<WorkflowConfigDTO>(
                    loginDTO, WorkFlowQB.GetWorkflowConfigs, param, dbTransaction);

                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        // ── Definition load ───────────────────────────────────────────────────

        public async Task<WorkflowDefinition?> GetWorkflowByIdAsync(
            int workflowId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var workflow = await _qe.QuerySingleAsync<WorkflowDefinition>(
                    loginDTO, WorkFlowQB.GetWorkflowById,
                    new { workflowid = workflowId }, dbTransaction);

                if (workflow == null)
                    return null;

                return await AttachStepsAndTransitions(workflow, loginDTO, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<WorkflowDefinition?> GetActiveDefinitionAsync(
            int entityId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var workflow = await _qe.QuerySingleAsync<WorkflowDefinition>(
                    loginDTO, WorkFlowQB.GetActiveDefinition,
                    new { entityid = entityId, tenantid = loginDTO.ClientId }, dbTransaction);

                if (workflow == null)
                    return null;

                return await AttachStepsAndTransitions(workflow, loginDTO, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        private async Task<WorkflowDefinition> AttachStepsAndTransitions(
            WorkflowDefinition workflow,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            // Load steps
            var steps = (await _qe.QueryAsync<WorkflowStep>(
                loginDTO, WorkFlowQB.GetWorkFlowSteps,
                new { workflowid = workflow.WorkflowId }, dbTransaction)).ToList();

            // Populate StrategyTypeId from RuleExpression for direct-id strategies
            foreach (var step in steps)
            {
                if ((step.StrategyType == 0 || step.StrategyType == 1) &&
                    int.TryParse(step.RuleExpression, out int sid))
                {
                    step.StrategyTypeId = sid;
                }
                // AssignmentValue alias
                step.AssignmentValue = step.RuleExpression;
            }

            workflow.Steps = steps;
            return workflow;
        }

        public async Task<GetWorkFlowDTO?> GetActiveWorkflowAsync(
            int entityId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                return await _qe.QuerySingleAsync<GetWorkFlowDTO>(
                    loginDTO, WorkFlowQB.GetActiveWorkflow,
                    new { entityid = entityId, tenantid = loginDTO.ClientId }, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<List<ConditionDTO>> GetConditionAsync(
            int? conditionGroupId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var result = await _qe.QueryAsync<ConditionDTO>(
                    loginDTO, WorkFlowQB.GetCondition,
                    new { conditiongroupid = conditionGroupId }, dbTransaction);

                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<IDictionary<int, WorkflowRuleGroup>> GetRuleGroupsAsync(
            IEnumerable<int> ruleGroupIds,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var ids = ruleGroupIds.Distinct().ToArray();
                if (ids.Length == 0)
                    return new Dictionary<int, WorkflowRuleGroup>();

                var groups = (await _qe.QueryAsync<WorkflowRuleGroup>(
                    loginDTO,
                    "SELECT * FROM WorkflowRuleGroup WHERE RuleGroupId IN @ids",
                    new { ids }, dbTransaction)).ToList();

                var rules = (await _qe.QueryAsync<WorkflowRule>(
                    loginDTO,
                    "SELECT * FROM WorkflowRule WHERE RuleGroupId IN @ids",
                    new { ids }, dbTransaction)).ToList();

                foreach (var g in groups)
                    g.Rules = rules.Where(r => r.RuleGroupId == g.RuleGroupId).ToList();

                return groups.ToDictionary(g => (int)g.RuleGroupId);
            }
            catch (Exception) { throw; }
        }

        // ── BizTransactionType lookup ─────────────────────────────────────────

        public async Task<int> GetBizTransactionTypeIdAsync(
            int bizTransactionClassId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var result = await _qe.QuerySingleAsync<int?>(
                    loginDTO,
                    WorkFlowQB.GetBizTransactionTypeId,
                    new { biztransactionclassid = bizTransactionClassId },
                    dbTransaction);

                return result ?? -1;
            }
            catch (Exception) { throw; }
        }

        // ── Instance CRUD ─────────────────────────────────────────────────────

        public async Task DeleteDuplicateInstanceAsync(
            int entityId,
            int objectId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var param = new { tenantid = loginDTO.ClientId, entityid = entityId, objectid = objectId };
                await _qe.ExecuteAsync(loginDTO, WorkFlowQB.DeleteDuplicateTasks,   param, dbTransaction);
                await _qe.ExecuteAsync(loginDTO, WorkFlowQB.DeleteDuplicateInstance, param, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<IReadOnlyList<WorkflowInstance>> CancelPendingInstancesAsync(
            int entityId,
            int objectId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var param = new { tenantid = loginDTO.ClientId, entityid = entityId, objectid = objectId };

                var pending = (await _qe.QueryAsync<WorkflowInstance>(
                    loginDTO, WorkFlowQB.GetPendingInstancesForObject, param, dbTransaction)).ToList();

                if (pending.Count == 0)
                    return pending;

                await _qe.ExecuteAsync(loginDTO, WorkFlowQB.CancelPendingTasks,     param, dbTransaction);
                await _qe.ExecuteAsync(loginDTO, WorkFlowQB.CancelPendingInstances, param, dbTransaction);

                // WIP-backed instances also need their TWORKFLOWWIP row closed out.
                // Status=0 is WorkflowWipDTO's own documented (but previously unused) "Cancelled"
                // value — 5 is already reserved there for a distinct "Withdrawn" concept, so this
                // deliberately does NOT follow the Approve=2/1, Reject=3/2, Return=4/3 pairing.
                // LastAction=5 matches WorkflowAction.Cancel, the same enum TWORKFLOWHISTORY.ACTION uses.
                foreach (var instance in pending)
                {
                    var wip = await GetWipByInstanceIdAsync(instance.WorkflowInstanceId, loginDTO, dbTransaction);
                    if (wip != null)
                        await UpdateWipStatusAsync(wip.WipId, 0, 5, 0, loginDTO.UserId, loginDTO, dbTransaction);
                }

                return pending;
            }
            catch (Exception) { throw; }
        }

        public async Task<WorkflowInstance> InsertInstanceAsync(
            WorkflowInstance instance,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var param = new
                {
                    tenantid              = loginDTO.ClientId,
                    entityid              = instance.EntityId,
                    objectid              = instance.ObjectId,
                    ouid                  = loginDTO.WorkOUId,
                    biztransactiontypeid  = instance.BizTransactionTypeId,
                    workflowid            = instance.WorkflowId,
                    datajson              = instance.DataJson is string alreadyJson
                                              ? alreadyJson
                                              : JsonConvert.SerializeObject(instance.DataJson),
                    factsjson             = instance.FactsJson,
                    currentstepid         = instance.CurrentStepId,
                    currentapprovallevel  = instance.CurrentApprovalLevel,
                    workflowstatus        = instance.WorkflowStatus,
                    createdbyid           = loginDTO.UserId,
                    modifiedbyid          = loginDTO.UserId
                };

                var id = await _qe.QuerySingleAsync<long>(
                    loginDTO, WorkFlowQB.InsertInstance, param, dbTransaction);

                instance.WorkflowInstanceId = (int)id;
                return instance;
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateInstanceAsync(
            WorkflowInstance instance,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                instance.ModifiedOn = DateTime.UtcNow;

                const string sql = @"
                    UPDATE TWORKFLOWINSTANCE
                    SET CURRENTSTEPID        = @CurrentStepId,
                        CURRENTAPPROVALLEVEL = @CurrentApprovalLevel,
                        WORKFLOWSTATUS       = @WorkflowStatus,
                        MODIFIEDON           = @ModifiedOn
                    WHERE WORKFLOWINSTANCEID = @WorkflowInstanceId;";

                await _qe.ExecuteAsync(loginDTO, sql, instance, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<WorkflowInstance?> GetInstanceAsync(
            long instanceId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    SELECT
                        WORKFLOWINSTANCEID    AS WorkflowInstanceId,
                        TENANTID              AS TenantId,
                        ENTITYID              AS EntityId,
                        OBJECTID              AS ObjectId,
                        OUID                  AS OUId,
                        BIZTRANSACTIONTYPEID  AS BizTransactionTypeId,
                        WORKFLOWID            AS WorkflowId,
                        DATAJSON              AS DataJson,
                        FACTSJSON             AS FactsJson,
                        CURRENTSTEPID         AS CurrentStepId,
                        CURRENTAPPROVALLEVEL  AS CurrentApprovalLevel,
                        WORKFLOWSTATUS        AS WorkflowStatus,
                        SORTORDER             AS SortOrder,
                        STATUS                AS Status,
                        VERSION               AS Version,
                        SOURCETYPE            AS SourceType,
                        CREATEDBYID           AS CreatedById,
                        CREATEDON             AS CreatedOn,
                        MODIFIEDBYID          AS ModifiedById,
                        MODIFIEDON            AS ModifiedOn
                    FROM TWORKFLOWINSTANCE
                    WHERE WORKFLOWINSTANCEID = @workflowinstanceid;";

                return await _qe.QuerySingleAsync<WorkflowInstance>(
                    loginDTO, sql, new { workflowinstanceid = instanceId }, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<WorkflowInstance?> GetInstanceAsync(
            long clientId,
            string entityName,
            long entityId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    SELECT
                        WORKFLOWINSTANCEID    AS WorkflowInstanceId,
                        TENANTID              AS TenantId,
                        ENTITYID              AS EntityId,
                        OBJECTID              AS ObjectId,
                        OUID                  AS OUId,
                        BIZTRANSACTIONTYPEID  AS BizTransactionTypeId,
                        WORKFLOWID            AS WorkflowId,
                        DATAJSON              AS DataJson,
                        FACTSJSON             AS FactsJson,
                        CURRENTSTEPID         AS CurrentStepId,
                        CURRENTAPPROVALLEVEL  AS CurrentApprovalLevel,
                        WORKFLOWSTATUS        AS WorkflowStatus,
                        SORTORDER             AS SortOrder,
                        STATUS                AS Status,
                        VERSION               AS Version,
                        SOURCETYPE            AS SourceType,
                        CREATEDBYID           AS CreatedById,
                        CREATEDON             AS CreatedOn,
                        MODIFIEDBYID          AS ModifiedById,
                        MODIFIEDON            AS ModifiedOn
                    FROM TWORKFLOWINSTANCE
                    WHERE TENANTID = @tenantid
                      AND ENTITYID = @entityid;";

                return await _qe.QuerySingleAsync<WorkflowInstance>(
                    loginDTO, sql,
                    new { tenantid = loginDTO.ClientId, entityid = entityId },
                    dbTransaction);
            }
            catch (Exception) { throw; }
        }

        // ── Task CRUD ─────────────────────────────────────────────────────────

        public async Task<WorkflowTask> InsertTaskAsync(
            WorkflowTask task,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var param = new
                {
                    workflowinstanceid   = task.WorkflowInstanceId,
                    stepid               = task.StepId,
                    assignedtouserid     = task.AssignedToUserId,
                    assignedroleid       = task.AssignedRoleId,
                    assignedusergroupid  = task.AssignedUserGroupId,
                    dueon                = task.DueOn,
                    comment              = task.Comment,
                    tenantid             = loginDTO.ClientId,
                    createdbyid          = loginDTO.UserId,
                    modifiedbyid         = loginDTO.UserId
                };

                var id = await _qe.QuerySingleAsync<int>(
                    loginDTO, WorkFlowQB.InsertTask, param, dbTransaction);

                task.WorkflowTaskId = id;
                return task;
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateTaskAsync(
            WorkflowTask task,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    UPDATE TWORKFLOWTASK
                    SET WORKFLOWTASKSTATUS = @WorkflowTaskStatus,
                        COMPLETEDON        = @CompletedOn,
                        ACTIONTAKEN        = @ActionTaken,
                        COMMENT            = @Comment
                    WHERE WORKFLOWTASKID   = @WorkflowTaskId;";

                await _qe.ExecuteAsync(loginDTO, sql, task, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<WorkflowTask?> GetTaskAsync(
            int taskId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    SELECT
                        WORKFLOWTASKID       AS WorkflowTaskId,
                        WORKFLOWINSTANCEID   AS WorkflowInstanceId,
                        STEPID               AS StepId,
                        WORKFLOWTASKSTATUS   AS WorkflowTaskStatus,
                        ASSIGNEDTOUSERID     AS AssignedToUserId,
                        ASSIGNEDROLEID       AS AssignedRoleId,
                        ASSIGNEDUSERGROUPID  AS AssignedUserGroupId,
                        DUEON                AS DueOn,
                        COMPLETEDON          AS CompletedOn,
                        ACTIONTAKEN          AS ActionTaken,
                        COMMENT              AS Comment,
                        TENANTID             AS TenantId,
                        SORTORDER            AS SortOrder,
                        STATUS               AS Status,
                        VERSION              AS Version,
                        SOURCETYPE           AS SourceType,
                        CREATEDBYID          AS CreatedById,
                        CREATEDON            AS CreatedOn,
                        MODIFIEDBYID         AS ModifiedById,
                        MODIFIEDON           AS ModifiedOn
                    FROM TWORKFLOWTASK
                    WHERE WORKFLOWTASKID = @workflowtaskid;";

                return await _qe.QuerySingleAsync<WorkflowTask>(
                    loginDTO, sql, new { workflowtaskid = taskId }, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<IReadOnlyList<WorkflowTask>> GetPendingTasksForLevelAsync(
            int instanceId,
            int approvalLevel,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    SELECT
                        T.WORKFLOWTASKID      AS WorkflowTaskId,
                        T.WORKFLOWINSTANCEID  AS WorkflowInstanceId,
                        T.STEPID              AS StepId,
                        T.WORKFLOWTASKSTATUS  AS WorkflowTaskStatus,
                        T.ASSIGNEDTOUSERID    AS AssignedToUserId,
                        T.ASSIGNEDROLEID      AS AssignedRoleId,
                        T.ASSIGNEDUSERGROUPID AS AssignedUserGroupId,
                        T.DUEON               AS DueOn,
                        T.COMPLETEDON         AS CompletedOn,
                        T.ACTIONTAKEN         AS ActionTaken,
                        T.COMMENT             AS Comment,
                        T.TENANTID            AS TenantId
                    FROM TWORKFLOWTASK T
                    INNER JOIN MWORKFLOWDETAIL WD ON WD.WORKFLOWDETAILID = T.STEPID
                    WHERE T.WORKFLOWINSTANCEID = @workflowinstanceid
                      AND WD.APPROVALLEVEL     = @approvallevel
                      AND T.WORKFLOWTASKSTATUS = 0;";

                var list = await _qe.QueryAsync<WorkflowTask>(
                    loginDTO, sql,
                    new { workflowinstanceid = instanceId, approvallevel = approvalLevel },
                    dbTransaction);

                return list.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<IReadOnlyList<WorkflowTask>> GetPendingTasksForStepAsync(
            int instanceId,
            long stepId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    SELECT
                        WORKFLOWTASKID       AS WorkflowTaskId,
                        WORKFLOWINSTANCEID   AS WorkflowInstanceId,
                        STEPID               AS StepId,
                        WORKFLOWTASKSTATUS   AS WorkflowTaskStatus,
                        ASSIGNEDTOUSERID     AS AssignedToUserId,
                        ASSIGNEDROLEID       AS AssignedRoleId,
                        ASSIGNEDUSERGROUPID  AS AssignedUserGroupId,
                        DUEON                AS DueOn,
                        COMPLETEDON          AS CompletedOn,
                        ACTIONTAKEN          AS ActionTaken,
                        COMMENT              AS Comment,
                        TENANTID             AS TenantId
                    FROM TWORKFLOWTASK
                    WHERE WORKFLOWINSTANCEID = @workflowinstanceid
                      AND STEPID             = @stepid
                      AND WORKFLOWTASKSTATUS = 0;";

                var list = await _qe.QueryAsync<WorkflowTask>(
                    loginDTO, sql,
                    new { workflowinstanceid = instanceId, stepid = stepId },
                    dbTransaction);

                return list.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<IReadOnlyList<WorkflowTask>> GetInboxTasksAsync(
            long userId,
            IEnumerable<string>? roles,
            IEnumerable<string>? pools,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                const string sql = @"
                    SELECT WORKFLOWTASKID AS WorkflowTaskId,
                           WORKFLOWINSTANCEID AS WorkflowInstanceId,
                           STEPID AS StepId,
                           WORKFLOWTASKSTATUS AS WorkflowTaskStatus,
                           ASSIGNEDTOUSERID AS AssignedToUserId,
                           ASSIGNEDROLEID AS AssignedRoleId,
                           ASSIGNEDUSERGROUPID AS AssignedUserGroupId,
                           DUEON AS DueOn,
                           COMPLETEDON AS CompletedOn,
                           ACTIONTAKEN AS ActionTaken,
                           COMMENT AS Comment,
                           TENANTID AS TenantId
                    FROM TWORKFLOWTASK
                    WHERE WORKFLOWTASKSTATUS = 0
                      AND ASSIGNEDTOUSERID = @userid;";

                var list = await _qe.QueryAsync<WorkflowTask>(
                    loginDTO, sql, new { userid = userId }, dbTransaction);

                return list.ToList();
            }
            catch (Exception) { throw; }
        }

        // ── History ───────────────────────────────────────────────────────────

        public async Task InsertHistoryAsync(
            WorkflowHistory history,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var param = new
                {
                    workflowinstanceid   = history.InstanceId,
                    entityid             = history.EntityId,
                    objectid             = history.ObjectId,
                    ouid                 = loginDTO.WorkOUId != 0 ? (int?)loginDTO.WorkOUId : (int?)null,
                    biztransactiontypeid = history.BiztransactionTypeId,
                    workflowid           = history.WorkflowId,
                    datajson             = JsonConvert.SerializeObject(history.DataJson),
                    stepkey              = history.StepKey,
                    approvallevel        = history.ApprovalLevel,
                    action               = history.Action,
                    actionbyuserid       = loginDTO.UserId,
                    comment              = history.Comment,
                    tenantid             = loginDTO.ClientId,
                    createdbyid          = loginDTO.UserId,
                    modifiedbyid         = loginDTO.UserId
                };

                await _qe.ExecuteAsync(loginDTO, WorkFlowQB.InsertHistory, param, dbTransaction);
            }
            catch (Exception) { throw; }
        }

        // ── Delegation ────────────────────────────────────────────────────────

        public async Task<IReadOnlyList<UserDelegation>> GetUserDelegationsAsync(
            long principalUserId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var list = await _qe.QueryAsync<UserDelegation>(
                    loginDTO, WorkFlowQB.GetUserDelegations,
                    new { principalUserId }, dbTransaction);

                return list.ToList();
            }
            catch (SqlException ex) when (ex.Number == 208)
            {
                // Delegation table does not exist yet — delegation is optional.
                // Return empty so ResolveEffectiveUserAsync uses the original UserId.
                return Array.Empty<UserDelegation>();
            }
            catch (Exception) { throw; }
        }

        // ── WIP (Form-Based Approval) ─────────────────────────────────────────

        public async Task<int> InsertWipAsync(
            int    entityId,
            int    tenantId,
            string dataJson,
            string? loginJson,
            string? apiEndpoint,
            int    createdById,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var param = new
                {
                    entityid     = entityId,
                    tenantid     = tenantId,
                    datajson     = dataJson,
                    loginjson    = loginJson,
                    apiendpoint  = apiEndpoint,
                    createdbyid  = createdById,
                    modifiedbyid = createdById
                };

                var wipId = await _qe.QuerySingleAsync<int>(
                    loginDTO, WorkFlowQB.InsertWip, param, dbTransaction);

                return wipId;
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateWipInstanceIdAsync(
            int wipId,
            int workflowInstanceId,
            int modifiedById,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                await _qe.ExecuteAsync(loginDTO, WorkFlowQB.UpdateWipInstanceId,
                    new { wipid = wipId, workflowinstanceid = workflowInstanceId, modifiedbyid = modifiedById },
                    dbTransaction);
            }
            catch (Exception) { throw; }
        }

        public async Task<WorkflowWipDTO?> GetWipByInstanceIdAsync(
            int instanceId,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                return await _qe.QuerySingleAsync<WorkflowWipDTO?>(
                    loginDTO, WorkFlowQB.GetWipByInstanceId,
                    new { workflowinstanceid = instanceId }, dbTransaction);
            }
            catch (Exception) { return null; }
        }

        // ── Auto-approve ──────────────────────────────────────────────────────

        public async Task<IReadOnlyList<OverdueAutoApproveTaskDTO>> GetOverdueAutoApproveTasksAsync(
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            try
            {
                var result = await _qe.QueryAsync<OverdueAutoApproveTaskDTO>(
                    loginDTO, WorkFlowQB.GetOverdueAutoApproveTasks, new { }, dbTransaction);
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateWipStatusAsync(
            int  wipId,
            byte status,
            byte lastAction,
            int  objectId,
            int  modifiedById,
            LoginDTO loginDTO,
            DbTransaction dbTransaction)
        {
            await _qe.ExecuteAsync(loginDTO, WorkFlowQB.UpdateWipStatus,
                new
                {
                    wipid        = wipId,
                    status       = status,
                    lastaction   = lastAction,
                    objectid     = objectId,
                    modifiedbyid = modifiedById
                }, dbTransaction);
        }
    }
}