using System;

namespace GB5Shared.Query.WorkFlow
{
    /// <summary>
    /// All SQL constants for the workflow engine (GB5Shared layer).
    /// Every query is parameterised — no string concatenation.
    /// </summary>
    public static class WorkFlowQB
    {
        // ── Config resolution ─────────────────────────────────────────────────

        /// <summary>
        /// Returns 1 if any MWORKFLOWCONFIG row has OUID = -1 (wildcard OU)
        /// for the given entity / client / biztransactionclass.
        /// The C# caller uses this to decide whether to skip the OUID filter
        /// in GetWorkflowConfigs (pass @ouid = -1 when true).
        /// </summary>
        public const string HasWildcardOuConfig = @"
            SELECT CAST(CASE WHEN EXISTS (
                SELECT 1 FROM MWORKFLOWCONFIG
                WHERE  OUID                  = -1
                  AND (CLIENTID              = @clientid              OR CLIENTID              = -1)
                  AND (ENTITYID              = @entityid              OR ENTITYID              = -1)
                  AND (BIZTRANSACTIONCLASSID = @biztransactionclassid OR BIZTRANSACTIONCLASSID = -1)
            ) THEN 1 ELSE 0 END AS INT);";

        /// <summary>
        /// Finds all MWORKFLOWCONFIG rows that could apply for the given combination
        /// of ClientId / EntityId / OUId / BizTransactionClassId / BizTransactionId.
        /// Wildcard (-1) rows are included.
        /// When @ouid = -1 the OU filter is skipped entirely (caller determined a
        /// wildcard-OU config exists via HasWildcardOuConfig).
        /// Ordering: most-specific rows first so the engine can pick the first match
        /// after evaluating EVALCONDITION.
        /// </summary>
        public const string GetWorkflowConfigs = @"
            SELECT
                WC.WORKFLOWCONFIGID         AS WorkflowConfigId,
                WC.CLIENTID                 AS ClientId,
                WC.ENTITYID                 AS EntityId,
                WC.OUID                     AS OuId,
                WC.BIZTRANSACTIONCLASSID    AS BizTransactionClassId,
                1 - WC.ISBIZTRANSACTIONWISE AS IsBizTransactionWise,
                WC.BIZTRANSACTIONID         AS BizTransactionId,
                1 - WC.ISFORMBASEDAPPROVAL  AS IsFormBasedApproval,
                WC.WORKFLOWID               AS WorkflowId,
                WC.EVALCONDITION            AS EvalCondition,
                WC.REMARKS                  AS Remarks,
                WC.CALLBACKENDPOINT         AS CallbackEndpoint,
                WC.CREATEDON                AS CreatedDate
            FROM MWORKFLOWCONFIG WC
            WHERE
                -- Client match (exact or wildcard)
                (WC.CLIENTID = @clientid OR WC.CLIENTID = -1)
                -- Entity match (exact or wildcard)
                AND (WC.ENTITYID = @entityid OR WC.ENTITYID = -1)
                -- OU match: @ouid = -1 means skip filter (a wildcard-OU config exists)
                AND (@ouid = -1 OR WC.OUID = @ouid OR WC.OUID = -1)
                -- BizTransactionClass match (exact or wildcard)
                AND (WC.BIZTRANSACTIONCLASSID = @biztransactionclassid OR WC.BIZTRANSACTIONCLASSID = -1)
                -- BizTransaction match: only apply when ISBIZTRANSACTIONWISE = 0 (0=Yes)
                AND (
                    WC.ISBIZTRANSACTIONWISE = 1
                    OR (WC.ISBIZTRANSACTIONWISE = 0 AND WC.BIZTRANSACTIONID = @biztransactionid)
                )
            ORDER BY
                -- Most-specific first: prefer exact matches over wildcards
                CASE WHEN WC.CLIENTID              = @clientid              THEN 0 ELSE 1 END,
                CASE WHEN WC.OUID                  = @ouid                  THEN 0 ELSE 1 END,
                CASE WHEN WC.BIZTRANSACTIONCLASSID = @biztransactionclassid THEN 0 ELSE 1 END,
                CASE WHEN WC.BIZTRANSACTIONID      = @biztransactionid      THEN 0 ELSE 1 END;";

        /// <summary>
        /// Resolves MBIZTRANSACTIONTYPE.BIZTRANSACTIONTYPEID from a BizTransactionClassId.
        /// Used when creating TWORKFLOWINSTANCE to satisfy the FK constraint.
        /// Returns NULL when no matching row exists.
        /// </summary>
        public const string GetBizTransactionTypeId = @"
            SELECT TOP 1 BIZTRANSACTIONTYPEID
            FROM   MBIZTRANSACTIONTYPE
            WHERE  BIZTRANSACTIONCLASSID = @biztransactionclassid;";

        // ── Workflow definition ───────────────────────────────────────────────

        /// <summary>
        /// Returns the active workflow definition header for a given WorkflowId.
        /// Uses workflowid directly (caller already resolved it via MWORKFLOWCONFIG).
        /// </summary>
        public const string GetWorkflowById = @"
            SELECT TOP 1
                WORKFLOWID              AS WorkflowId,
                WORKFLOWNAME            AS WorkflowName,
                ENTITYID                AS EntityId,
                PARTICULARS             AS Particulars,
                TENANTID                AS TenantId,
                SORTORDER               AS SortOrder,
                VERSION                 AS Version,
                STATUS                  AS Status,
                SOURCETYPE              AS SourceType,
                CREATEDBYID             AS CreatedById,
                CREATEDON               AS CreatedOn,
                MODIFIEDBYID            AS ModifiedById,
                MODIFIEDON              AS ModifiedOn
            FROM MWORKFLOW
            WHERE WORKFLOWID = @workflowid
              AND STATUS = 1
            ORDER BY VERSION DESC;";

        /// <summary>
        /// Kept for backward compat: find active workflow by EntityId + TenantId.
        /// (Used by old CheckWorkFlowApplicability path.)
        /// </summary>
        public const string GetActiveWorkflow = @"
            SELECT TOP 1
                W.WORKFLOWID            AS WorkFlowId,
                W.WORKFLOWNAME          AS WorkFlowName,
                W.ENTITYID              AS EntityId,
                W.VERSION               AS Version,
                W.STATUS                AS Status,
                W.TENANTID              AS TenantId,
                W.SOURCETYPE            AS SourceType,
                W.CREATEDBYID           AS CreatedById,
                W.CREATEDON             AS CreatedOn,
                W.MODIFIEDBYID          AS ModifiedById,
                W.MODIFIEDON            AS ModifiedOn
            FROM MWORKFLOW W
            INNER JOIN MENTITY E ON E.ENTITYID = W.ENTITYID
            WHERE E.ENTITYID  = @entityid
              AND W.TENANTID  = @tenantid
              AND W.STATUS    = 1
            ORDER BY W.VERSION DESC;";

        /// <summary>
        /// Returns the latest active workflow definition header by EntityId + TenantId.
        /// Same shape as GetWorkflowById — used by GetActiveDefinitionAsync.
        /// </summary>
        public const string GetActiveDefinition = @"
            SELECT TOP 1
                WORKFLOWID              AS WorkflowId,
                WORKFLOWNAME            AS WorkflowName,
                ENTITYID                AS EntityId,
                PARTICULARS             AS Particulars,
                TENANTID                AS TenantId,
                SORTORDER               AS SortOrder,
                VERSION                 AS Version,
                STATUS                  AS Status,
                SOURCETYPE              AS SourceType,
                CREATEDBYID             AS CreatedById,
                CREATEDON               AS CreatedOn,
                MODIFIEDBYID            AS ModifiedById,
                MODIFIEDON              AS ModifiedOn
            FROM MWORKFLOW
            WHERE ENTITYID = @entityid
              AND TENANTID  = @tenantid
              AND STATUS    = 1
            ORDER BY VERSION DESC;";

        // ── Workflow steps (MWORKFLOWDETAIL + MWORKFLOWASSIGNMENT) ───────────

        /// <summary>
        /// Loads all steps for a workflow including joined assignment info.
        /// STEPKEY is generated from WORKFLOWDETAILID to ensure uniqueness.
        /// </summary>
        public const string GetWorkFlowSteps = @"
            SELECT
                -- MWORKFLOWDETAIL
                WD.WORKFLOWDETAILID                             AS WorkflowDetailId,
                WD.WORKFLOWID                                   AS WorkflowId,
                WD.SLNO                                         AS SlNo,
                WD.APPROVALLEVEL                                AS ApprovalLevel,
                CAST(WD.WORKFLOWDETAILID AS NVARCHAR(20))       AS StepKey,
                WD.DISPLAYNAME                                  AS DisplayName,
                WD.STEPTYPE                                     AS StepType,
                1 - WD.ISINITIAL                                AS IsInitial,
                1 - WD.ISFINAL                                  AS IsFinal,
                WD.ASSIGNMENTTYPE                               AS AssignmentType,
                WD.EVALCONDITION                                AS EvalCondition,
                WD.ASSIGNMENTID                                 AS AssignmentId,
                WD.PARALLELGROUPKEY                             AS ParallelGroupKey,
                WD.SLAHOURS                                     AS SlaHours,
                1 - WD.ISAUTOAPPROVE                            AS IsAutoApprove,
                WD.ESCALATIONRULEGROUPID                        AS EscalationRuleGroupId,
                WD.PICKLISTID                                   AS PickListId,
                WD.DISPLAYLABELID                               AS DisplayLabelId,

                -- MWORKFLOWASSIGNMENT (LEFT JOIN — step might have no assignment)
                WA.WORKFLOWASSIGNMENTID                         AS WorkflowAssignmentId,
                WA.WORKFLOWASSIGNMENTNAME                       AS WorkflowAssignmentName,
                WA.STRATEGYTYPE                                 AS StrategyType,
                WA.RULEEXPRESSION                               AS RuleExpression,
                WA.DESCRIPTION                                  AS AssignmentDescription,
                WA.TENANTID                                     AS AssignmentTenantId

            FROM MWORKFLOWDETAIL WD
            LEFT JOIN MWORKFLOWASSIGNMENT WA
                ON WD.ASSIGNMENTID = WA.WORKFLOWASSIGNMENTID

            WHERE WD.WORKFLOWID = @workflowid

            ORDER BY WD.APPROVALLEVEL ASC, WD.SLNO ASC;";



        public const string GetCondition = @"
            SELECT
                C.CONDITIONID           AS ConditionId,
                C.CONDITIONGROUPID      AS ConditionGroupId,
                C.ENTITYMEMBERID        AS EntityMemberId,
                E.MEMBERNAME            AS EntityMemberName,
                C.OPERATOR              AS Operator,
                C.VALUE                 AS Value,
                C.VALUETYPE             AS ValueType,
                C.SORTORDER             AS SortOrder
            FROM MCONDITION C
            JOIN MENTITYMEMBERS E ON C.ENTITYMEMBERID = E.ENTITYMEMBERID
            WHERE C.CONDITIONGROUPID = @conditiongroupid
              AND C.STATUS = 1
            ORDER BY C.SORTORDER;";

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

        public const string InsertInstance = @"
            INSERT INTO DBO.TWORKFLOWINSTANCE
            (
                TENANTID, ENTITYID, OBJECTID, OUID,
                BIZTRANSACTIONTYPEID, WORKFLOWID, DATAJSON, FACTSJSON,
                CURRENTSTEPID, CURRENTAPPROVALLEVEL,
                WORKFLOWSTATUS, CREATEDBYID, MODIFIEDBYID
            )
            VALUES
            (
                @tenantid, @entityid, @objectid, @ouid,
                @biztransactiontypeid, @workflowid, @datajson, @factsjson,
                @currentstepid, @currentapprovallevel,
                @workflowstatus, @createdbyid, @modifiedbyid
            );
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

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

        public const string InsertTask = @"
            INSERT INTO DBO.TWORKFLOWTASK
            (
                WORKFLOWINSTANCEID, STEPID,
                ASSIGNEDTOUSERID, ASSIGNEDROLEID, ASSIGNEDUSERGROUPID,
                DUEON, COMMENT, TENANTID, CREATEDBYID, MODIFIEDBYID
            )
            VALUES
            (
                @workflowinstanceid, @stepid,
                @assignedtouserid, @assignedroleid, @assignedusergroupid,
                @dueon, @comment, @tenantid, @createdbyid, @modifiedbyid
            );
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

        // ── Duplicate cleanup (called before inserting a new instance) ───────

        public const string DeleteDuplicateTasks = @"
            DELETE T
            FROM DBO.TWORKFLOWTASK T
            JOIN DBO.TWORKFLOWINSTANCE I ON I.WORKFLOWINSTANCEID = T.WORKFLOWINSTANCEID
            WHERE I.TENANTID  = @tenantid
              AND I.ENTITYID  = @entityid
              AND I.OBJECTID  = @objectid
              AND I.WORKFLOWSTATUS = 0;";

        public const string DeleteDuplicateInstance = @"
            DELETE FROM DBO.TWORKFLOWINSTANCE
            WHERE TENANTID       = @tenantid
              AND ENTITYID       = @entityid
              AND OBJECTID       = @objectid
              AND WORKFLOWSTATUS = 0
              AND NOT EXISTS (
                  SELECT 1 FROM DBO.TWORKFLOWHISTORY H
                  WHERE H.WORKFLOWINSTANCEID = TWORKFLOWINSTANCE.WORKFLOWINSTANCEID
              );";

        // ── Cancel-on-delete / cancel-on-resubmit ────────────────────────────
        // Unlike DeleteDuplicateInstance above (which only ever removes true
        // orphans with no history), these cancel REAL, already-submitted pending
        // instances — used when the underlying record is deleted or edited while
        // its approval is still pending.

        public const string GetPendingInstancesForObject = @"
            SELECT
                WORKFLOWINSTANCEID   AS WorkflowInstanceId,
                WORKFLOWID           AS WorkflowId,
                OUID                 AS OUID,
                BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                DATAJSON             AS DataJson
            FROM DBO.TWORKFLOWINSTANCE
            WHERE TENANTID       = @tenantid
              AND ENTITYID       = @entityid
              AND OBJECTID       = @objectid
              AND WORKFLOWSTATUS = 0;";

        public const string CancelPendingTasks = @"
            UPDATE T
            SET WORKFLOWTASKSTATUS = 4,
                ACTIONTAKEN        = 5,
                COMPLETEDON        = GETUTCDATE()
            FROM DBO.TWORKFLOWTASK T
            JOIN DBO.TWORKFLOWINSTANCE I ON I.WORKFLOWINSTANCEID = T.WORKFLOWINSTANCEID
            WHERE I.TENANTID  = @tenantid
              AND I.ENTITYID  = @entityid
              AND I.OBJECTID  = @objectid
              AND I.WORKFLOWSTATUS = 0
              AND T.WORKFLOWTASKSTATUS = 0;";

        public const string CancelPendingInstances = @"
            UPDATE DBO.TWORKFLOWINSTANCE
            SET WORKFLOWSTATUS = 4
            WHERE TENANTID       = @tenantid
              AND ENTITYID       = @entityid
              AND OBJECTID       = @objectid
              AND WORKFLOWSTATUS = 0;";

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

        public const string InsertHistory = @"
            INSERT INTO DBO.TWORKFLOWHISTORY
            (
                WORKFLOWINSTANCEID, ENTITYID, OBJECTID, OUID,
                BIZTRANSACTIONTYPEID, WORKFLOWID, DATAJSON,
                STEPKEY, APPROVALLEVEL, ACTION, ACTIONBYUSERID, COMMENT,
                TENANTID, SORTORDER, STATUS, VERSION, SOURCETYPE,
                CREATEDBYID, MODIFIEDBYID
            )
            VALUES
            (
                @workflowinstanceid, @entityid, ISNULL(@objectid,-1), ISNULL(@ouid,-1),
                ISNULL(@biztransactiontypeid,-1), @workflowid, @datajson,
                @stepkey, @approvallevel, @action, @actionbyuserid, @comment,
                ISNULL(@tenantid,-1), 0, 1, 1, 1,
                @createdbyid, @modifiedbyid
            );";

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

        public const string GetUserDelegations = @"
            SELECT
                DelegationId,
                PrincipalUserId,
                DelegateUserId,
                StartsOn,
                EndsOn
            FROM UserDelegation
            WHERE PrincipalUserId = @principalUserId
              AND StartsOn <= GETUTCDATE()
              AND EndsOn   >= GETUTCDATE();";

        // ── Post-completion entity update ─────────────────────────────────────

        /// <summary>
        /// Resolves the DB table name for a given EntityId via MENTITY → DBOBJECT.
        /// Returns DBOBJECTNAME (e.g. 'TTASK').
        /// </summary>
        public const string GetDbObjectNameByEntityId = @"
            SELECT DB.DBOBJECTNAME
            FROM   MENTITY  ME
            INNER JOIN DBOBJECT DB ON DB.DBOBJECTID = ME.DBOBJECTID
            WHERE  ME.ENTITYID = @entityid;";

        /// <summary>
        /// Friendly display name for an EntityId (e.g. "Leave", "Service Request").
        /// Used as a fallback in the WorkFlowActions response for entities that don't
        /// have a hardcoded display name in WorkFlowBLL.
        /// </summary>
        public const string GetEntityNameByEntityId = @"
            SELECT ME.ENTITYNAME
            FROM   MENTITY ME
            WHERE  ME.ENTITYID = @entityid;";

        public const string ProcessTenantWorkflows = @"
            SELECT
                WI.ENTITYID         AS EntityId,
                ME.ENTITYCODE       AS EntityCode,
                ME.ENTITYNAME       AS EntityName,
                WI.OBJECTID         AS ObjectId,
                ME.DBOBJECTID       AS DbObjectId,
                DB.DBOBJECTNAME     AS DbObjectName
            FROM TWORKFLOWINSTANCE WI
            INNER JOIN MENTITY  ME ON WI.ENTITYID   = ME.ENTITYID
            INNER JOIN DBOBJECT DB ON ME.DBOBJECTID = DB.DBOBJECTID
            INNER JOIN TWORKFLOWTASK WT ON WI.WORKFLOWINSTANCEID = WT.WORKFLOWINSTANCEID
            WHERE WT.WORKFLOWTASKID = @workflowtaskid
              AND WI.WORKFLOWSTATUS = 0;";

        // ── Primary-key discovery (dynamic post-approval entity update) ───────

        public const string GetPrimaryKeyColumnAsync = @"
            SELECT COLUMN_NAME AS ColumnName
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
            JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS KU
                ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
            WHERE TC.TABLE_NAME      = @tablename
              AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY';";

        public const string GetPrimaryKeyColumnAsync_pg = @"
            SELECT a.attname AS ColumnName
            FROM pg_index i
            JOIN pg_attribute a
                ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
            WHERE i.indrelid  = @tablename::regclass
              AND i.indisprimary = TRUE
            LIMIT 1;";

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

        /// <summary>
        /// Inserts a new WIP row and returns WIPID.
        /// WORKFLOWINSTANCEID is set to -1 initially; update via UpdateWipInstanceId after instance creation.
        /// </summary>
        public const string InsertWip = @"
            INSERT INTO DBO.TWORKFLOWWIP
            (
                ENTITYID, OBJECTID, TENANTID, WORKFLOWINSTANCEID,
                DATAJSON, LOGINJSON, LASTACTION, STATUS, SOURCETYPE, APIENDPOINT,
                CREATEDBYID, MODIFIEDBYID
            )
            VALUES
            (
                @entityid, -1, @tenantid, -1,
                @datajson, @loginjson, 0, 1, 1, @apiendpoint,
                @createdbyid, @modifiedbyid
            );
            SELECT CAST(SCOPE_IDENTITY() AS INT);";

        /// <summary>Binds the workflow instance ID to the WIP row after the instance is created.</summary>
        public const string UpdateWipInstanceId = @"
            UPDATE DBO.TWORKFLOWWIP
            SET WORKFLOWINSTANCEID = @workflowinstanceid,
                MODIFIEDBYID       = @modifiedbyid,
                MODIFIEDON         = GETDATE()
            WHERE WIPID = @wipid;";

        /// <summary>Returns the WIP row linked to a given workflow instance (for action handling).</summary>
        public const string GetWipByInstanceId = @"
            SELECT
                WIPID               AS WipId,
                ENTITYID            AS EntityId,
                OBJECTID            AS ObjectId,
                TENANTID            AS TenantId,
                WORKFLOWINSTANCEID  AS WorkflowInstanceId,
                DATAJSON            AS DataJson,
                DATAHASH            AS DataHash,
                RESUBMISSIONCOUNT   AS ResubmissionCount,
                LASTACTION          AS LastAction,
                SORTORDER           AS SortOrder,
                STATUS              AS Status,
                VERSION             AS Version,
                SOURCETYPE          AS SourceType,
                APIENDPOINT         AS ApiEndpoint,
                LOGINJSON           AS LoginJson,
                CREATEDBYID         AS CreatedById,
                CREATEDON           AS CreatedOn,
                MODIFIEDBYID        AS ModifiedById,
                MODIFIEDON          AS ModifiedOn
            FROM DBO.TWORKFLOWWIP
            WHERE WORKFLOWINSTANCEID = @workflowinstanceid;";

        /// <summary>Updates STATUS and LASTACTION; optionally sets OBJECTID after the real entity is saved.</summary>
        public const string UpdateWipStatus = @"
            UPDATE DBO.TWORKFLOWWIP
            SET STATUS       = @status,
                LASTACTION   = @lastaction,
                OBJECTID     = CASE WHEN @objectid <> 0 THEN @objectid ELSE OBJECTID END,
                MODIFIEDBYID = @modifiedbyid,
                MODIFIEDON   = GETDATE()
            WHERE WIPID = @wipid;";

        // ── Auto-approve: overdue pending tasks ───────────────────────────

        /// <summary>
        /// Returns all pending tasks whose DueOn has passed and whose step
        /// is marked ISAUTOAPPROVE = 0 (0=Yes). Used by the background auto-approve job.
        ///</summary>
        public const string GetOverdueAutoApproveTasks = @"
            SELECT
                T.WORKFLOWTASKID       AS WorkflowTaskId,
                T.WORKFLOWINSTANCEID   AS WorkflowInstanceId,
                I.TENANTID             AS TenantId,
                I.OUID                 AS OuId,
                I.ENTITYID             AS EntityId,
                T.ASSIGNEDTOUSERID     AS AssignedToUserId,
                T.DUEON                AS DueOn,
                D.SLAHOURS             AS SlaHours
            FROM DBO.TWORKFLOWTASK T
            INNER JOIN DBO.TWORKFLOWINSTANCE I
                ON I.WORKFLOWINSTANCEID = T.WORKFLOWINSTANCEID
            INNER JOIN DBO.MWORKFLOWDETAIL D
                ON D.WORKFLOWDETAILID = T.STEPID
            WHERE T.WORKFLOWTASKSTATUS = 0
              AND T.DUEON IS NOT NULL
              AND T.DUEON < GETUTCDATE()
              AND D.ISAUTOAPPROVE = 0
              AND I.WORKFLOWSTATUS = 0;";

        // ── Backward-compat check (old MWORKFLOWRULE path) ───────────────────

        public const string CheckWorkFlowRequired = @"
            SELECT
                a.WORKFLOWRULEID          AS WorkFlowRuleId,
                a.WORKFLOWRULECODE        AS WorkFlowRuleCode,
                a.WORKFLOWRULENAME        AS WorkFlowRuleName,
                a.WORKFLOWDEPLOYMENTID    AS WorkFlowDeploymentId,
                b.ENTITYID                AS EntityId,
                b.ENTITYCODE              AS EntityCode,
                b.ENTITYNAME              AS EntityName,
                a.EVALCONDITION           AS EvalCondition,
                a.WORKFLOWDEPLOYMENTERPID AS WorkFlowDeploymentErpId
            FROM MWORKFLOWRULE a
            INNER JOIN MENTITY b ON a.ENTITYID = b.ENTITYID
            WHERE a.ENTITYID = @entityid;";

        // ── Monitor dashboard ─────────────────────────────────────────────────

        /// <summary>
        /// Aggregate status counts for the workflow monitoring dashboard.
        /// WorkflowStatus: 0=Pending, 1=Approved, 2=Rejected, 3=Returned.
        /// Overdue = pending instance that has at least one task past its DueOn.
        /// </summary>
        public const string GetMonitorCounts = @"
            SELECT
                SUM(CASE WHEN WI.WORKFLOWSTATUS = 0 THEN 1 ELSE 0 END) AS PendingCount,
                SUM(CASE WHEN WI.WORKFLOWSTATUS = 1 THEN 1 ELSE 0 END) AS ApprovedCount,
                SUM(CASE WHEN WI.WORKFLOWSTATUS = 2 THEN 1 ELSE 0 END) AS RejectedCount,
                SUM(CASE WHEN WI.WORKFLOWSTATUS = 3 THEN 1 ELSE 0 END) AS ReturnedCount,
                SUM(CASE
                        WHEN WI.WORKFLOWSTATUS = 0
                         AND OD.WORKFLOWINSTANCEID IS NOT NULL
                        THEN 1 ELSE 0
                    END)                                                 AS OverdueCount,
                COUNT(*)                                                 AS TotalCount
            FROM TWORKFLOWINSTANCE WI
            LEFT JOIN (
                SELECT DISTINCT WORKFLOWINSTANCEID
                FROM   TWORKFLOWTASK
                WHERE  WORKFLOWTASKSTATUS = 0
                  AND  DUEON IS NOT NULL
                  AND  DUEON < GETUTCDATE()
            ) OD ON OD.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
            WHERE WI.TENANTID = @TenantId
              AND (@EntityId = -1 OR WI.ENTITYID = @EntityId)
              AND (@DateFrom IS NULL OR WI.CREATEDON >= @DateFrom)
              AND (@DateTo   IS NULL OR WI.CREATEDON <= DATEADD(DAY, 1, @DateTo));";

        /// <summary>
        /// Paginated instance list for the workflow monitoring dashboard.
        /// Returns one row per workflow instance with current approver and overdue flag.
        /// Required indexes: TWORKFLOWINSTANCE(TENANTID, ENTITYID, CREATEDON),
        ///                   TWORKFLOWTASK(WORKFLOWINSTANCEID, WORKFLOWTASKSTATUS).
        /// </summary>
        public const string GetMonitorInstances = @"
            SELECT
                WI.WORKFLOWINSTANCEID    AS WorkflowInstanceId,
                WI.ENTITYID              AS EntityId,
                ME.ENTITYNAME            AS EntityName,
                WI.OBJECTID              AS ObjectId,
                WF.WORKFLOWNAME          AS WorkflowName,
                MU.USERNAME              AS CurrentApproverName,
                WI.WORKFLOWSTATUS        AS WorkflowStatus,
                WI.CREATEDON             AS CreatedOn,
                WT_LAST.MODIFIEDON       AS LastActionOn,
                CASE
                    WHEN WI.WORKFLOWSTATUS = 0
                     AND EXISTS (
                             SELECT 1 FROM TWORKFLOWTASK WT2
                             WHERE  WT2.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                               AND  WT2.WORKFLOWTASKSTATUS = 0
                               AND  WT2.DUEON IS NOT NULL
                               AND  WT2.DUEON < GETUTCDATE()
                         )
                    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
                END                      AS IsOverdue
            FROM TWORKFLOWINSTANCE WI
            LEFT JOIN MENTITY ME       ON ME.ENTITYID          = WI.ENTITYID
            LEFT JOIN MWORKFLOW WF     ON WF.WORKFLOWID        = WI.WORKFLOWID
            LEFT JOIN (
                SELECT WT.WORKFLOWINSTANCEID,
                       WT.ASSIGNEDTOUSERID,
                       WT.MODIFIEDON,
                       ROW_NUMBER() OVER (
                           PARTITION BY WT.WORKFLOWINSTANCEID
                           ORDER BY     WT.WORKFLOWTASKID DESC
                       ) AS RN
                FROM TWORKFLOWTASK WT
                WHERE WT.WORKFLOWTASKSTATUS = 0
            ) WT_LAST ON WT_LAST.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                     AND WT_LAST.RN = 1
            LEFT JOIN MUSER MU          ON MU.USERID            = WT_LAST.ASSIGNEDTOUSERID
            WHERE WI.TENANTID = @TenantId
              AND (@EntityId = -1 OR WI.ENTITYID = @EntityId)
              AND (@DateFrom IS NULL OR WI.CREATEDON >= @DateFrom)
              AND (@DateTo   IS NULL OR WI.CREATEDON <= DATEADD(DAY, 1, @DateTo))
            ORDER BY WI.CREATEDON DESC
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;";
    }
}