namespace FrameworkDAL.Query.WorkFlow
{
    /// <summary>
    /// SQL constants for the FrameworkDAL workflow layer.
    /// All queries are fully parameterised — no string concatenation.
    /// </summary>
    public static class WorkFlowQB
    {
        // ── Request-To-Me (tasks assigned to the current user) ────────────────
        // WORKFLOWTASKSTATUS: 0=Pending, 1=Approved, 2=Rejected, 3=Completed
        // Filters: UserId (mandatory), EntityId/Status/FromDate/ToDate (all optional)

        public static readonly string GetWorkflowApprovalList = @"
            SELECT
                -- Task identity
                WT.WORKFLOWTASKID            AS WorkFlowTaskId,
                WT.WORKFLOWINSTANCEID        AS WorkFlowInstanceId,

                -- What is being approved
                WI.OBJECTID                  AS ObjectId,
                WI.ENTITYID                  AS EntityId,
                ME.ENTITYCODE                AS EntityCode,
                ME.ENTITYNAME                AS EntityName,
                WI.DATAJSON                  AS DataJson,
                WI.FACTSJSON                 AS FactsJson,

                -- Which workflow & overall status
                WI.WORKFLOWID                AS WorkflowId,
                WF.WORKFLOWNAME              AS WorkflowName,
                WI.WORKFLOWSTATUS            AS WorkflowStatus,
                CASE WI.WORKFLOWSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS WorkflowStatusName,
                WI.CURRENTAPPROVALLEVEL      AS CurrentApprovalLevel,

                -- Who submitted it
                WI.CREATEDBYID               AS SubmittedById,
                SU.USERCODE                  AS SubmittedByCode,
                SU.USERNAME                  AS SubmittedByName,
                WI.CREATEDON                 AS SubmittedOn,

                -- Current step (this task)
                WT.STEPID                    AS StepId,
                WD.DISPLAYNAME               AS StepName,
                WD.APPROVALLEVEL             AS ApprovalLevel,

                -- Task status
                WT.WORKFLOWTASKSTATUS        AS WorkFlowTaskStatus,
                CASE WT.WORKFLOWTASKSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Approved'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Completed'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS WorkFlowTaskStatusName,

                -- Assigned to (approver)
                WT.ASSIGNEDTOUSERID          AS AssignedToUserId,
                MU.USERCODE                  AS AssignedToUserCode,
                MU.USERNAME                  AS AssignedToUserName,
                WT.ASSIGNEDROLEID            AS AssignedRoleId,
                WT.ASSIGNEDUSERGROUPID       AS AssignedUserGroupId,

                -- Task timing & action
                WT.DUEON                     AS DueOn,
                WT.COMPLETEDON               AS CompletedOn,
                WT.ACTIONTAKEN               AS ActionTaken,
                WT.COMMENT                   AS Comment,
                WT.TENANTID                  AS TenantId,
                WT.CREATEDON                 AS CreatedOn,
                WT.MODIFIEDON                AS ModifiedOn,

                -- Particulars template (for real-time display text generation).
                -- MWORKFLOWCONFIG's per-client override wins when set; otherwise fall back
                -- to MENTITY.WORKFLOWTEMPLATE, the framework-curated default for this entity.
                COALESCE(WC.PARTICULARSTEMPLATE, ME.WORKFLOWTEMPLATE) AS ParticularsTemplate,

                -- Previous level's remark (carried forward so this approver sees what the prior level said)
                PREVREMARK.PreviousRemarks          AS PreviousRemarks,
                PREVREMARK.PreviousApprovalLevel     AS PreviousApprovalLevel,
                PREVREMARK.PreviousActionByUserName  AS PreviousActionByUserName,

                -- Pagination: total matching rows (before OFFSET/FETCH)
                COUNT(*) OVER()              AS TotalCount
            FROM TWORKFLOWTASK WT
            JOIN      TWORKFLOWINSTANCE WI ON WI.WORKFLOWINSTANCEID = WT.WORKFLOWINSTANCEID
            JOIN      MENTITY ME            ON ME.ENTITYID           = WI.ENTITYID
            LEFT JOIN MWORKFLOW WF          ON WF.WORKFLOWID         = WI.WORKFLOWID
            LEFT JOIN MWORKFLOWDETAIL WD    ON WD.WORKFLOWDETAILID   = WT.STEPID
            LEFT JOIN MUSER MU              ON MU.USERID             = WT.ASSIGNEDTOUSERID
            LEFT JOIN MUSER SU              ON SU.USERID             = WI.CREATEDBYID
            LEFT JOIN MWORKFLOWCONFIG WC    ON WC.CLIENTID           = WI.TENANTID
                                          AND WC.ENTITYID            = WI.ENTITYID
                                          AND WC.WORKFLOWID          = WI.WORKFLOWID
            -- Most recent TWORKFLOWHISTORY row from a lower approval level than this task's
            -- level, so this approver can see what the previous level's approver remarked.
            OUTER APPLY (
                SELECT TOP 1
                    H.COMMENT       AS PreviousRemarks,
                    H.APPROVALLEVEL AS PreviousApprovalLevel,
                    HU.USERNAME     AS PreviousActionByUserName
                FROM TWORKFLOWHISTORY H
                LEFT JOIN MUSER HU ON HU.USERID = H.ACTIONBYUSERID
                WHERE H.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND H.APPROVALLEVEL      < WD.APPROVALLEVEL
                ORDER BY H.ACTIONON DESC
            ) PREVREMARK
            WHERE WT.ASSIGNEDTOUSERID = @UserId
              AND (@EntityId IS NULL OR WI.ENTITYID           = @EntityId)
              AND (@Status   IS NULL OR WT.WORKFLOWTASKSTATUS = @Status)
              AND (@FromDate IS NULL OR WT.CREATEDON          >= @FromDate)
              AND (@ToDate   IS NULL OR WT.CREATEDON           < @ToDate)
            ORDER BY WT.CREATEDON DESC
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;";

        // ── Request-By-Me (tasks initiated by the current user) ──────────────
        // WORKFLOWTASKSTATUS: 0=Pending, 1=Approved, 2=Rejected, 3=Completed
        // Filters by WI.CREATEDBYID (the submitter), not the assignee.
        // Filters: UserId (mandatory), EntityId/Status/FromDate/ToDate (all optional)

        public static readonly string RequestByMe = @"
            SELECT
                -- Task identity
                WT.WORKFLOWTASKID            AS WorkFlowTaskId,
                WT.WORKFLOWINSTANCEID        AS WorkFlowInstanceId,

                -- What was submitted
                WI.OBJECTID                  AS ObjectId,
                WI.ENTITYID                  AS EntityId,
                ME.ENTITYCODE                AS EntityCode,
                ME.ENTITYNAME                AS EntityName,
                WI.DATAJSON                  AS DataJson,
                WI.FACTSJSON                 AS FactsJson,

                -- Which workflow & overall status
                WI.WORKFLOWID                AS WorkflowId,
                WF.WORKFLOWNAME              AS WorkflowName,
                WI.WORKFLOWSTATUS            AS WorkflowStatus,
                CASE WI.WORKFLOWSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS WorkflowStatusName,
                WI.CURRENTAPPROVALLEVEL      AS CurrentApprovalLevel,

                -- Who submitted it (= the logged-in user, but shown for consistency)
                WI.CREATEDBYID               AS SubmittedById,
                SU.USERCODE                  AS SubmittedByCode,
                SU.USERNAME                  AS SubmittedByName,
                WI.CREATEDON                 AS SubmittedOn,

                -- Current step this task is at
                WT.STEPID                    AS StepId,
                WD.DISPLAYNAME               AS StepName,
                WD.APPROVALLEVEL             AS ApprovalLevel,

                -- Task status
                WT.WORKFLOWTASKSTATUS        AS WorkFlowTaskStatus,
                CASE WT.WORKFLOWTASKSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Approved'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Completed'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS WorkFlowTaskStatusName,

                -- Who is assigned to approve (current approver)
                WT.ASSIGNEDTOUSERID          AS AssignedToUserId,
                MU.USERCODE                  AS AssignedToUserCode,
                MU.USERNAME                  AS AssignedToUserName,
                WT.ASSIGNEDROLEID            AS AssignedRoleId,
                WT.ASSIGNEDUSERGROUPID       AS AssignedUserGroupId,

                -- Task timing & action
                WT.DUEON                     AS DueOn,
                WT.COMPLETEDON               AS CompletedOn,
                WT.ACTIONTAKEN               AS ActionTaken,
                WT.COMMENT                   AS Comment,
                WT.TENANTID                  AS TenantId,
                WT.CREATEDON                 AS CreatedOn,
                WT.MODIFIEDON                AS ModifiedOn,

                -- Particulars template (for real-time display text generation).
                -- MWORKFLOWCONFIG's per-client override wins when set; otherwise fall back
                -- to MENTITY.WORKFLOWTEMPLATE, the framework-curated default for this entity.
                COALESCE(WC.PARTICULARSTEMPLATE, ME.WORKFLOWTEMPLATE) AS ParticularsTemplate,

                -- Previous level's remark (carried forward so this approver sees what the prior level said)
                PREVREMARK.PreviousRemarks          AS PreviousRemarks,
                PREVREMARK.PreviousApprovalLevel     AS PreviousApprovalLevel,
                PREVREMARK.PreviousActionByUserName  AS PreviousActionByUserName,

                -- Pagination: total matching rows (before OFFSET/FETCH)
                COUNT(*) OVER()              AS TotalCount
            FROM TWORKFLOWTASK WT
            JOIN      TWORKFLOWINSTANCE WI ON WI.WORKFLOWINSTANCEID = WT.WORKFLOWINSTANCEID
            JOIN      MENTITY ME            ON ME.ENTITYID           = WI.ENTITYID
            LEFT JOIN MWORKFLOW WF          ON WF.WORKFLOWID         = WI.WORKFLOWID
            LEFT JOIN MWORKFLOWDETAIL WD    ON WD.WORKFLOWDETAILID   = WT.STEPID
            LEFT JOIN MUSER MU              ON MU.USERID             = WT.ASSIGNEDTOUSERID
            LEFT JOIN MUSER SU              ON SU.USERID             = WI.CREATEDBYID
            LEFT JOIN MWORKFLOWCONFIG WC    ON WC.CLIENTID           = WI.TENANTID
                                          AND WC.ENTITYID            = WI.ENTITYID
                                          AND WC.WORKFLOWID          = WI.WORKFLOWID
            -- Most recent TWORKFLOWHISTORY row from a lower approval level than this task's
            -- level, so this approver can see what the previous level's approver remarked.
            OUTER APPLY (
                SELECT TOP 1
                    H.COMMENT       AS PreviousRemarks,
                    H.APPROVALLEVEL AS PreviousApprovalLevel,
                    HU.USERNAME     AS PreviousActionByUserName
                FROM TWORKFLOWHISTORY H
                LEFT JOIN MUSER HU ON HU.USERID = H.ACTIONBYUSERID
                WHERE H.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND H.APPROVALLEVEL      < WD.APPROVALLEVEL
                ORDER BY H.ACTIONON DESC
            ) PREVREMARK
            -- Filter by workflow instance creator (= submitter), not task assignee
            WHERE WI.CREATEDBYID = @UserId
              AND (@EntityId IS NULL OR WI.ENTITYID           = @EntityId)
              AND (@Status   IS NULL OR WT.WORKFLOWTASKSTATUS = @Status)
              AND (@FromDate IS NULL OR WT.CREATEDON          >= @FromDate)
              AND (@ToDate   IS NULL OR WT.CREATEDON           < @ToDate)
            ORDER BY WT.CREATEDON DESC
            OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;";

        // ── Workflow history: To Me (requests routed to me that I acted on) ─────
        // Filters by H.ACTIONBYUSERID (who acted), not the instance submitter,
        // and excludes ACTION=0 (Submitted) — a self-submission is not a request
        // that came to this user, so it belongs only in the "By Me" history.

        public static readonly string WorkflowHistory = @"
            SELECT
                H.WORKFLOWHISTORYID          AS WorkflowHistoryId,
                H.WORKFLOWINSTANCEID         AS WorkflowInstanceId,
                H.ENTITYID                   AS EntityId,
                ME.ENTITYCODE                AS EntityCode,
                ME.ENTITYNAME                AS EntityName,
                H.OBJECTID                   AS ObjectId,
                H.OUID                       AS OUId,
                H.BIZTRANSACTIONTYPEID       AS BizTransactionTypeId,
                H.WORKFLOWID                 AS WorkflowId,
                WF.WORKFLOWNAME              AS WorkflowName,
                WI.DATAJSON                  AS DataJson,
                H.STEPKEY                    AS StepKey,
                H.APPROVALLEVEL              AS ApprovalLevel,
                WD.DISPLAYNAME               AS StepName,
                H.ACTIONBYUSERID             AS ActionByUserId,
                MU.USERNAME                  AS ActionUserName,
                CASE H.ACTION
                    WHEN 0 THEN 'Submitted'
                    WHEN 1 THEN 'Approved'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Escalated'
                    WHEN 5 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS Action,
                H.COMMENT                    AS Comment,
                H.ACTIONON                   AS ActionOn,
                H.TENANTID                   AS TenantId,
                H.SORTORDER                  AS SortOrder,
                H.STATUS                     AS Status,
                H.VERSION                    AS Version,
                H.SOURCETYPE                 AS SourceType,
                H.CREATEDBYID                AS CreatedById,
                H.CREATEDON                  AS CreatedOn,
                H.MODIFIEDBYID               AS ModifiedById,
                H.MODIFIEDON                 AS ModifiedOn,
                WI.WORKFLOWSTATUS            AS WorkflowStatus,
                CASE WI.WORKFLOWSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS WorkflowStatusName,
                -- WIP fields
                CASE WHEN WP.WIPID IS NOT NULL THEN 1 ELSE 0 END AS IsWip,
                WP.WIPID                     AS WipId,
                WP.STATUS                    AS WipStatus,
                CASE WP.STATUS
                    WHEN 0 THEN 'Cancelled'
                    WHEN 1 THEN 'Pending'
                    WHEN 2 THEN 'Approved'
                    WHEN 3 THEN 'Rejected'
                    WHEN 4 THEN 'Returned'
                    WHEN 5 THEN 'Withdrawn'
                    ELSE NULL
                END                          AS WipStatusName,
                WP.OBJECTID                  AS WipObjectId,
                -- Particulars template (same join as GetWorkFlowApprovalList).
                -- MWORKFLOWCONFIG's per-client override wins when set; otherwise fall back
                -- to MENTITY.WORKFLOWTEMPLATE, the framework-curated default for this entity.
                COALESCE(WC.PARTICULARSTEMPLATE, ME.WORKFLOWTEMPLATE) AS ParticularsTemplate
            FROM TWORKFLOWHISTORY H
            INNER JOIN TWORKFLOWINSTANCE WI ON WI.WORKFLOWINSTANCEID = H.WORKFLOWINSTANCEID
            INNER JOIN MENTITY ME            ON ME.ENTITYID           = H.ENTITYID
            LEFT  JOIN MWORKFLOW WF          ON WF.WORKFLOWID         = H.WORKFLOWID
            LEFT  JOIN MWORKFLOWDETAIL WD
                ON  WD.WORKFLOWDETAILID  = TRY_CAST(H.STEPKEY AS INT)
                AND WD.WORKFLOWID        = H.WORKFLOWID
            LEFT  JOIN MUSER MU          ON MU.USERID = H.ACTIONBYUSERID
            LEFT  JOIN TWORKFLOWWIP WP   ON WP.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
            LEFT  JOIN MWORKFLOWCONFIG WC    ON WC.CLIENTID        = WI.TENANTID
                                           AND WC.ENTITYID         = WI.ENTITYID
                                           AND WC.WORKFLOWID       = WI.WORKFLOWID
            -- H.ACTION <> 0 excludes 'Submitted' rows — a self-submission is not a request
            -- that came TO this user, so it must not appear in the Request-To-Me history.
            WHERE (@userid   IS NULL OR H.ACTIONBYUSERID = @userid)
              AND H.ACTION   <> 0
              AND (@ouid     IS NULL OR H.OUID           = @ouid)
              AND (@entityid IS NULL OR H.ENTITYID       = @entityid)
              AND (@fromdate IS NULL OR H.ACTIONON       >= @fromdate)
              AND (@todate   IS NULL OR H.ACTIONON        < @todate)
            ORDER BY H.ACTIONON DESC;";

        // ── Workflow history: By Me (instances I submitted) ────────────────────
        // Filters by WI.CREATEDBYID (the submitter), not the acting approver.

        public static readonly string WorkflowHistoryByMe = @"
            SELECT
                H.WORKFLOWHISTORYID          AS WorkflowHistoryId,
                H.WORKFLOWINSTANCEID         AS WorkflowInstanceId,
                H.ENTITYID                   AS EntityId,
                ME.ENTITYCODE                AS EntityCode,
                ME.ENTITYNAME                AS EntityName,
                H.OBJECTID                   AS ObjectId,
                H.OUID                       AS OUId,
                H.BIZTRANSACTIONTYPEID       AS BizTransactionTypeId,
                H.WORKFLOWID                 AS WorkflowId,
                WF.WORKFLOWNAME              AS WorkflowName,
                WI.DATAJSON                  AS DataJson,
                H.STEPKEY                    AS StepKey,
                H.APPROVALLEVEL              AS ApprovalLevel,
                WD.DISPLAYNAME               AS StepName,
                H.ACTIONBYUSERID             AS ActionByUserId,
                MU.USERNAME                  AS ActionUserName,
                CASE H.ACTION
                    WHEN 0 THEN 'Submitted'
                    WHEN 1 THEN 'Approved'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Escalated'
                    WHEN 5 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS Action,
                H.COMMENT                    AS Comment,
                H.ACTIONON                   AS ActionOn,
                H.TENANTID                   AS TenantId,
                H.SORTORDER                  AS SortOrder,
                H.STATUS                     AS Status,
                H.VERSION                    AS Version,
                H.SOURCETYPE                 AS SourceType,
                H.CREATEDBYID                AS CreatedById,
                H.CREATEDON                  AS CreatedOn,
                H.MODIFIEDBYID               AS ModifiedById,
                H.MODIFIEDON                 AS ModifiedOn,
                WI.WORKFLOWSTATUS            AS WorkflowStatus,
                CASE WI.WORKFLOWSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                          AS WorkflowStatusName,
                -- WIP fields
                CASE WHEN WP.WIPID IS NOT NULL THEN 1 ELSE 0 END AS IsWip,
                WP.WIPID                     AS WipId,
                WP.STATUS                    AS WipStatus,
                CASE WP.STATUS
                    WHEN 0 THEN 'Cancelled'
                    WHEN 1 THEN 'Pending'
                    WHEN 2 THEN 'Approved'
                    WHEN 3 THEN 'Rejected'
                    WHEN 4 THEN 'Returned'
                    WHEN 5 THEN 'Withdrawn'
                    ELSE NULL
                END                          AS WipStatusName,
                WP.OBJECTID                  AS WipObjectId,
                -- Particulars template (same join as GetWorkFlowApprovalList).
                -- MWORKFLOWCONFIG's per-client override wins when set; otherwise fall back
                -- to MENTITY.WORKFLOWTEMPLATE, the framework-curated default for this entity.
                COALESCE(WC.PARTICULARSTEMPLATE, ME.WORKFLOWTEMPLATE) AS ParticularsTemplate
            FROM TWORKFLOWHISTORY H
            INNER JOIN TWORKFLOWINSTANCE WI ON WI.WORKFLOWINSTANCEID = H.WORKFLOWINSTANCEID
            INNER JOIN MENTITY ME            ON ME.ENTITYID           = H.ENTITYID
            LEFT  JOIN MWORKFLOW WF          ON WF.WORKFLOWID         = H.WORKFLOWID
            LEFT  JOIN MWORKFLOWDETAIL WD
                ON  WD.WORKFLOWDETAILID  = TRY_CAST(H.STEPKEY AS INT)
                AND WD.WORKFLOWID        = H.WORKFLOWID
            LEFT  JOIN MUSER MU          ON MU.USERID = H.ACTIONBYUSERID
            LEFT  JOIN TWORKFLOWWIP WP   ON WP.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
            LEFT  JOIN MWORKFLOWCONFIG WC    ON WC.CLIENTID        = WI.TENANTID
                                           AND WC.ENTITYID         = WI.ENTITYID
                                           AND WC.WORKFLOWID       = WI.WORKFLOWID
            -- Filter by workflow instance creator (= submitter), not the acting approver
            WHERE (@userid   IS NULL OR WI.CREATEDBYID  = @userid)
              AND (@ouid     IS NULL OR H.OUID           = @ouid)
              AND (@entityid IS NULL OR H.ENTITYID       = @entityid)
              AND (@fromdate IS NULL OR H.ACTIONON       >= @fromdate)
              AND (@todate   IS NULL OR H.ACTIONON        < @todate)
            ORDER BY H.ACTIONON DESC;";

        // ── Workflow status (all instances the user is involved in) ───────────

        public static readonly string GetWorkflowStatus = @"
            SELECT
                WI.WORKFLOWINSTANCEID                  AS WorkFlowInstanceId,
                WI.ENTITYID                            AS EntityId,
                WF.WORKFLOWNAME                        AS WorkflowName,
                WI.OBJECTID                            AS ObjectId,
                WI.CURRENTAPPROVALLEVEL                AS CurrentApprovalLevel,
                WI.CURRENTSTEPID                       AS CurrentStepId,
                WD.DISPLAYNAME                         AS DisplayName,
                WI.WORKFLOWSTATUS                      AS WorkflowStatus,
                CASE WI.WORKFLOWSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                                    AS WorkflowStatusName,

                /* Current-step task (latest) */
                CURTASK.WORKFLOWTASKID                 AS CurrentTaskId,
                CURTASK.ASSIGNEDTOUSERID               AS CurrentAssignedUserId,
                MU.USERNAME                            AS CurrentAssignedUserName,
                CURTASK.CREATEDON                      AS CurrentAssignedOn,
                CURTASK.COMPLETEDON                    AS CurrentCompletedOn,
                CURTASK.ACTIONTAKEN                    AS CurrentAction,
                CURTASK.COMMENT                        AS CurrentComment,
                CASE CURTASK.WORKFLOWTASKSTATUS
                    WHEN 3 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 1 THEN 'In Progress'
                    WHEN 0 THEN 'Pending'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Not Started'
                END                                    AS CurrentStepStatus,

                /* Last completed step */
                LASTDONE.LastCompletedStep,
                LASTDONE.LastCompletedBy,
                LASTDONE.LastCompletedOn,

                /* Next pending step */
                NEXTPENDING.NextPendingStep,
                NEXTPENDING.NextPendingWith,

                /* Full step history (pipe-separated) */
                HISTORY.StepHistory

            FROM TWORKFLOWINSTANCE WI
            JOIN MWORKFLOW WF ON WF.WORKFLOWID = WI.WORKFLOWID
            LEFT JOIN MWORKFLOWDETAIL WD ON WD.WORKFLOWDETAILID = WI.CURRENTSTEPID

            /* Latest task at current step */
            OUTER APPLY (
                SELECT TOP 1 *
                FROM TWORKFLOWTASK T
                WHERE T.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND T.STEPID             = WI.CURRENTSTEPID
                ORDER BY T.CREATEDON DESC
            ) CURTASK
            LEFT JOIN MUSER MU ON MU.USERID = CURTASK.ASSIGNEDTOUSERID

            /* Last completed before current */
            OUTER APPLY (
                SELECT TOP 1
                    WD2.DISPLAYNAME AS LastCompletedStep,
                    MU2.USERNAME    AS LastCompletedBy,
                    WT2.COMPLETEDON AS LastCompletedOn
                FROM TWORKFLOWTASK WT2
                JOIN MWORKFLOWDETAIL WD2 ON WD2.WORKFLOWDETAILID = WT2.STEPID
                LEFT JOIN MUSER MU2      ON MU2.USERID = WT2.ASSIGNEDTOUSERID
                WHERE WT2.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND WT2.WORKFLOWTASKSTATUS = 3
                  AND WT2.STEPID            <> WI.CURRENTSTEPID
                ORDER BY WT2.COMPLETEDON DESC
            ) LASTDONE

            /* Next pending */
            OUTER APPLY (
                SELECT TOP 1
                    WD3.DISPLAYNAME AS NextPendingStep,
                    MU3.USERNAME    AS NextPendingWith
                FROM TWORKFLOWTASK WT3
                JOIN MWORKFLOWDETAIL WD3 ON WD3.WORKFLOWDETAILID = WT3.STEPID
                LEFT JOIN MUSER MU3      ON MU3.USERID = WT3.ASSIGNEDTOUSERID
                WHERE WT3.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND WT3.WORKFLOWTASKSTATUS = 0
                ORDER BY WT3.CREATEDON ASC
            ) NEXTPENDING

            /* Pipe-separated history */
            OUTER APPLY (
                SELECT STRING_AGG(
                    CONCAT(
                        WD4.DISPLAYNAME, ' L', WD4.APPROVALLEVEL, ' - ',
                        CASE WT4.WORKFLOWTASKSTATUS
                            WHEN 3 THEN 'Completed'
                            WHEN 2 THEN 'Rejected'
                            WHEN 0 THEN 'Pending'
                            WHEN 4 THEN 'Cancelled'
                            ELSE        'In Progress'
                        END,
                        ' (', ISNULL(MU4.USERNAME, 'N/A'), ')'
                    ), ' | '
                ) AS StepHistory
                FROM TWORKFLOWTASK WT4
                JOIN MWORKFLOWDETAIL WD4 ON WD4.WORKFLOWDETAILID = WT4.STEPID
                LEFT JOIN MUSER MU4      ON MU4.USERID = WT4.ASSIGNEDTOUSERID
                WHERE WT4.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
            ) HISTORY

            WHERE EXISTS (
                SELECT 1 FROM TWORKFLOWTASK TX
                WHERE TX.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND TX.ASSIGNEDTOUSERID   = @userid
            )
              AND (@entityid IS NULL OR WI.ENTITYID = @entityid)
              AND EXISTS (
                SELECT 1 FROM TWORKFLOWTASK TDATE
                WHERE TDATE.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND (@fromdate IS NULL OR TDATE.CREATEDON >= @fromdate)
                  AND (@todate   IS NULL OR TDATE.CREATEDON  < @todate)
              )
            ORDER BY WI.CREATEDON DESC;";

        // ── Current approval status for a specific document ───────────────────

        public static readonly string GetCurrentApprovalStatus = @"
            SELECT
                WI.WORKFLOWINSTANCEID                  AS WorkFlowInstanceId,
                WI.ENTITYID                            AS EntityId,
                WF.WORKFLOWNAME                        AS WorkflowName,
                WI.OBJECTID                            AS ObjectId,
                WI.CURRENTAPPROVALLEVEL                AS CurrentApprovalLevel,
                WI.CURRENTSTEPID                       AS CurrentStepId,
                WD.DISPLAYNAME                         AS DisplayName,
                WD.DISPLAYNAME                         AS CurrentStep,
                WI.WORKFLOWSTATUS                      AS WorkflowStatus,
                CASE WI.WORKFLOWSTATUS
                    WHEN 0 THEN 'Pending'
                    WHEN 1 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 3 THEN 'Returned'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'Unknown'
                END                                    AS WorkflowStatusName,

                /* Who submitted the instance */
                WI.CREATEDBYID                         AS SubmittedById,
                SUB.USERNAME                           AS SubmittedBy,
                WI.CREATEDON                           AS SubmittedOn,
                WI.CREATEDON                           AS CreatedOn,
                WI.MODIFIEDBYID                         AS ModifiedById,
                WI.MODIFIEDON                           AS ModifiedOn,

                /* Last completed step (mirrors GetWorkflowStatus's LASTDONE below) */
                LASTDONE.LastCompletedStep,
                LASTDONE.LastCompletedBy,
                LASTDONE.LastCompletedOn,

                /* Next pending step (mirrors GetWorkflowStatus's NEXTPENDING below) */
                NEXTPENDING.NextPendingStep,
                NEXTPENDING.NextPendingWith,

                /* Who currently has the task */
                CURTASK.WORKFLOWTASKID                 AS CurrentTaskId,
                CURTASK.ASSIGNEDTOUSERID               AS CurrentAssignedUserId,
                MU.USERNAME                            AS CurrentAssignedUserName,
                MU.USERNAME                            AS AssignedTo,
                CURTASK.CREATEDON                      AS CurrentAssignedOn,
                CURTASK.COMPLETEDON                    AS CurrentCompletedOn,
                CURTASK.ACTIONTAKEN                    AS CurrentAction,
                CURTASK.COMMENT                        AS CurrentComment,
                CASE CURTASK.WORKFLOWTASKSTATUS
                    WHEN 3 THEN 'Completed'
                    WHEN 2 THEN 'Rejected'
                    WHEN 0 THEN 'Pending'
                    WHEN 4 THEN 'Cancelled'
                    ELSE        'In Progress'
                END                                    AS CurrentStepStatus,

                /* History */
                HISTORY.StepHistory

            FROM TWORKFLOWINSTANCE WI
            JOIN MWORKFLOW WF ON WF.WORKFLOWID = WI.WORKFLOWID
            LEFT JOIN MWORKFLOWDETAIL WD ON WD.WORKFLOWDETAILID = WI.CURRENTSTEPID
            LEFT JOIN MUSER SUB ON SUB.USERID = WI.CREATEDBYID

            OUTER APPLY (
                SELECT TOP 1 *
                FROM TWORKFLOWTASK T
                WHERE T.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                ORDER BY T.CREATEDON DESC
            ) CURTASK
            LEFT JOIN MUSER MU ON MU.USERID = CURTASK.ASSIGNEDTOUSERID

            /* Last completed before current — same shape as GetWorkflowStatus's LASTDONE */
            OUTER APPLY (
                SELECT TOP 1
                    WD2.DISPLAYNAME AS LastCompletedStep,
                    MU2.USERNAME    AS LastCompletedBy,
                    WT2.COMPLETEDON AS LastCompletedOn
                FROM TWORKFLOWTASK WT2
                JOIN MWORKFLOWDETAIL WD2 ON WD2.WORKFLOWDETAILID = WT2.STEPID
                LEFT JOIN MUSER MU2      ON MU2.USERID = WT2.ASSIGNEDTOUSERID
                WHERE WT2.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND WT2.WORKFLOWTASKSTATUS = 3
                  AND WT2.STEPID            <> WI.CURRENTSTEPID
                ORDER BY WT2.COMPLETEDON DESC
            ) LASTDONE

            /* Next pending — same shape as GetWorkflowStatus's NEXTPENDING. Only finds a row
               once the engine has actually created the next step's TWORKFLOWTASK (STATUS=0);
               a not-yet-reached step with no task row yet still resolves to NULL here — that
               reflects there being nothing to show yet, not a query bug. */
            OUTER APPLY (
                SELECT TOP 1
                    WD3.DISPLAYNAME AS NextPendingStep,
                    MU3.USERNAME    AS NextPendingWith
                FROM TWORKFLOWTASK WT3
                JOIN MWORKFLOWDETAIL WD3 ON WD3.WORKFLOWDETAILID = WT3.STEPID
                LEFT JOIN MUSER MU3      ON MU3.USERID = WT3.ASSIGNEDTOUSERID
                WHERE WT3.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
                  AND WT3.WORKFLOWTASKSTATUS = 0
                  AND WT3.STEPID            <> WI.CURRENTSTEPID
                ORDER BY WT3.CREATEDON ASC
            ) NEXTPENDING

            OUTER APPLY (
                SELECT STRING_AGG(
                    CONCAT(
                        WD4.DISPLAYNAME, ' L', WD4.APPROVALLEVEL, ' - ',
                        CASE WT4.WORKFLOWTASKSTATUS
                            WHEN 3 THEN 'Completed'
                            WHEN 2 THEN 'Rejected'
                            WHEN 0 THEN 'Pending'
                            WHEN 4 THEN 'Cancelled'
                            ELSE        'In Progress'
                        END,
                        ' by ', ISNULL(MU4.USERNAME, 'N/A'),
                        ' at ', FORMAT(WT4.COMPLETEDON, 'dd-MMM-yyyy HH:mm')
                    ), ' | '
                ) AS StepHistory
                FROM TWORKFLOWTASK WT4
                JOIN MWORKFLOWDETAIL WD4 ON WD4.WORKFLOWDETAILID = WT4.STEPID
                LEFT JOIN MUSER MU4      ON MU4.USERID = WT4.ASSIGNEDTOUSERID
                WHERE WT4.WORKFLOWINSTANCEID = WI.WORKFLOWINSTANCEID
            ) HISTORY

            WHERE WI.ENTITYID = @entityid
              AND WI.OBJECTID = @objectid
            ORDER BY WI.CREATEDON DESC;";

        // ── Workflow CRUD ─────────────────────────────────────────────────────

        public const string GET_WORKFLOW = @"
SELECT
    WF.WORKFLOWID              AS WorkflowId,
    WF.WORKFLOWNAME            AS WorkflowName,
    WF.ENTITYID                AS WorkFlowEntityId,
    E.ENTITYNAME               AS EntityName,
    WF.PARTICULARS             AS WorkflowParticulars,
    WF.TENANTID                AS WorkflowTenantId,
    WF.SORTORDER               AS WorkflowSortOrder,
    WF.VERSION                 AS WorkflowVersion,
    WF.STATUS                  AS WorkflowStatus,
    WF.SOURCETYPE              AS WorkflowSourceType,
    WF.CREATEDBYID             AS WorkflowCreatedById,
    WF.CREATEDON               AS WorkflowCreatedOn,
    WF.MODIFIEDBYID            AS WorkflowModifiedById,
    WF.MODIFIEDON              AS WorkflowModifiedOn,

    WFD.WORKFLOWDETAILID       AS WorkflowDetailId,
    WFD.WORKFLOWID             AS WorkflowDetailWorkflowId,
    WFD.SLNO                   AS WorkflowDetailSlNo,
    WFD.APPROVALLEVEL          AS WorkflowApprovalLevel,
    WFD.DISPLAYNAME            AS WorkflowDisplayName,
    WFD.STEPTYPE               AS WorkflowStepType,
    WFD.ISINITIAL              AS WorkflowIsInitial,
    WFD.ISFINAL                AS WorkflowIsFinal,
    WFD.ASSIGNMENTTYPE         AS WorkflowAssignmentType,
    WFD.EVALCONDITION          AS WorkflowEvalCondition,
    WFD.ASSIGNMENTID   AS WorkflowAssignmentId,
    D.WORKFLOWASSIGNMENTNAME   AS WorkflowAssignmentName,
    WFD.PARALLELGROUPKEY       AS WorkflowParallelGroupKey,
    WFD.SLAHOURS               AS WorkflowSlaHours,
    WFD.ESCALATIONRULEGROUPID  AS WorkflowEscalationRuleGroupId,
    WFD.PICKLISTID             AS WorkflowPickListId,
    WFD.DISPLAYLABELID         AS WorkflowDisplayLabelId,
    WFD.ISAUTOAPPROVE          AS WorkflowIsAutoApprove
FROM MWORKFLOW WF
LEFT JOIN MWORKFLOWDETAIL WFD
    ON WF.WORKFLOWID = WFD.WORKFLOWID
LEFT JOIN MENTITY E
    ON WF.ENTITYID = E.ENTITYID
LEFT JOIN MWORKFLOWASSIGNMENT D
    ON WFD.ASSIGNMENTID = D.WORKFLOWASSIGNMENTID
WHERE WF.WORKFLOWID = @WorkflowId";

        public const string SAVE_WORKFLOW = @"
INSERT INTO MWORKFLOW
(
    WORKFLOWNAME,
    ENTITYID,
    PARTICULARS,
    TENANTID,
    SORTORDER,
    VERSION,
    STATUS,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @WorkflowName,
    @WorkflowEntityId,
    @WorkflowParticulars,
    @WorkflowTenantId,
    @WorkflowSortOrder,
    @WorkflowVersion,
    @WorkflowStatus,
    @WorkflowSourceType,
    @WorkflowCreatedById,
    @WorkflowCreatedOn,
    @WorkflowModifiedById,
    @WorkflowModifiedOn
);

SELECT CAST(SCOPE_IDENTITY() AS INT);";

        public const string UPDATE_WORKFLOW = @"UPDATE MWORKFLOW
                                               SET
                                                    WORKFLOWNAME = @WorkflowName,
                                                    ENTITYID = @WorkFlowEntityId,
                                                    PARTICULARS = @WorkflowParticulars,
                                                    TENANTID = @WorkflowTenantId,
                                                    SORTORDER = @WorkflowSortOrder,
                                                    VERSION = @WorkflowVersion,
                                                    STATUS = @WorkflowStatus,
                                                    SOURCETYPE = @WorkflowSourceType,
                                                    MODIFIEDBYID = @WorkflowModifiedById,
                                                    MODIFIEDON = @WorkflowModifiedOn
                                               WHERE
                                                    WORKFLOWID = @WorkflowId;";

        public const string DELETE_WORKFLOW = @"DELETE FROM MWORKFLOW
                                               WHERE WORKFLOWID = @WorkflowId;";

        public const string GET_SELECT_LIST_WORKFLOW = @"
SELECT
    WF.WORKFLOWID            AS WorkflowId,
    WF.WORKFLOWNAME          AS WorkflowName,
    WF.ENTITYID              AS WorkFlowEntityId,
    WF.PARTICULARS           AS WorkflowParticulars,
    WF.TENANTID              AS WorkflowTenantId,
    WF.SORTORDER             AS WorkflowSortOrder,
    WF.VERSION               AS WorkflowVersion,
    WF.STATUS                AS WorkflowStatus,
    WF.SOURCETYPE            AS WorkflowSourceType,
    WF.CREATEDBYID           AS WorkflowCreatedById,
    WF.CREATEDON             AS WorkflowCreatedOn,
    WF.MODIFIEDBYID          AS WorkflowModifiedById,
    WF.MODIFIEDON            AS WorkflowModifiedOn,

    WFD.WORKFLOWDETAILID     AS WorkflowDetailId,
    WFD.WORKFLOWID           AS WorkflowDetailWorkflowId,
    WFD.SLNO                 AS WorkflowDetailSlNo,
    WFD.APPROVALLEVEL        AS WorkflowApprovalLevel,
    WFD.DISPLAYNAME          AS WorkflowDisplayName,
    WFD.STEPTYPE             AS WorkflowStepType,
    WFD.ISINITIAL            AS WorkflowIsInitial,
    WFD.ISFINAL              AS WorkflowIsFinal,
    WFD.ASSIGNMENTTYPE       AS WorkflowAssignmentType,
    WFD.EVALCONDITION        AS WorkflowEvalCondition,
    WFD.ASSIGNMENTID AS WorkflowAssignmentId,
    D.WORKFLOWASSIGNMENTNAME AS WorkflowAssignmentName,
    WFD.PARALLELGROUPKEY     AS WorkflowParallelGroupKey,
    WFD.SLAHOURS             AS WorkflowSlaHours,
    WFD.ESCALATIONRULEGROUPID AS WorkflowEscalationRuleGroupId,
    WFD.PICKLISTID           AS WorkflowPickListId,
    WFD.DISPLAYLABELID       AS WorkflowDisplayLabelId,
    WFD.ISAUTOAPPROVE        AS WorkflowIsAutoApprove
FROM MWORKFLOW WF
LEFT JOIN MWORKFLOWDETAIL WFD
    ON WF.WORKFLOWID = WFD.WORKFLOWID
LEFT JOIN MENTITY E
    ON WF.ENTITYID = E.ENTITYID
LEFT JOIN MWORKFLOWASSIGNMENT D
    ON WFD.ASSIGNMENTID = D.WORKFLOWASSIGNMENTID";

        public const string GET_WORKFLOW_DETAILS = @"
SELECT
    WORKFLOWDETAILID          AS WorkflowDetailId,
    WORKFLOWID                AS WorkflowDetailWorkflowId,
    SLNO                      AS WorkflowDetailSlNo,
    APPROVALLEVEL             AS WorkflowApprovalLevel,
    DISPLAYNAME               AS WorkflowDisplayName,
    STEPTYPE                  AS WorkflowStepType,
    ISINITIAL                 AS WorkflowIsInitial,
    ISFINAL                   AS WorkflowIsFinal,
    ASSIGNMENTTYPE            AS WorkflowAssignmentType,
    EVALCONDITION             AS WorkflowEvalCondition,
    ASSIGNMENTID              AS WorkflowAssignmentId,
    PARALLELGROUPKEY          AS WorkflowParallelGroupKey,
    SLAHOURS                  AS WorkflowSlaHours,
    ESCALATIONRULEGROUPID     AS WorkflowEscalationRuleGroupId,
    PICKLISTID                AS WorkflowPickListId,
    DISPLAYLABELID            AS WorkflowDisplayLabelId,
    ISAUTOAPPROVE             AS WorkflowIsAutoApprove
FROM MWORKFLOWDETAIL
WHERE WORKFLOWID = @WorkflowId";

        public const string SAVE_WORKFLOW_DETAILS = @"
INSERT INTO MWORKFLOWDETAIL
(
    WORKFLOWID,
    SLNO,
    APPROVALLEVEL,
    DISPLAYNAME,
    STEPTYPE,
    ISINITIAL,
    ISFINAL,
    ASSIGNMENTTYPE,
    EVALCONDITION,
    ASSIGNMENTID,
    PARALLELGROUPKEY,
    SLAHOURS,
    ESCALATIONRULEGROUPID,
    PICKLISTID,
    DISPLAYLABELID,
    ISAUTOAPPROVE
)
VALUES
(
    @WorkflowDetailWorkflowId,
    @WorkflowDetailSlNo,
    @WorkflowApprovalLevel,
    @WorkflowDisplayName,
    @WorkflowStepType,
    @WorkflowIsInitial,
    @WorkflowIsFinal,
    @WorkflowAssignmentType,
    @WorkflowEvalCondition,
    @WorkflowAssignmentId,
    @WorkflowParallelGroupKey,
    @WorkflowSlaHours,
    @WorkflowEscalationRuleGroupId,
    @WorkflowPickListId,
    @WorkflowDisplayLabelId,
    @WorkflowIsAutoApprove
)";

        public const string UPDATE_WORKFLOW_DETAILS = @"
UPDATE MWORKFLOWDETAIL
SET
    SLNO = @WorkflowDetailSlNo,
    APPROVALLEVEL = @WorkflowApprovalLevel,
    DISPLAYNAME = @WorkflowDisplayName,
    STEPTYPE = @WorkflowStepType,
    ISINITIAL = @WorkflowIsInitial,
    ISFINAL = @WorkflowIsFinal,
    ASSIGNMENTTYPE = @WorkflowAssignmentType,
    EVALCONDITION = @WorkflowEvalCondition,
    ASSIGNMENTID = @WorkflowAssignmentId,
    PARALLELGROUPKEY = @WorkflowParallelGroupKey,
    SLAHOURS = @WorkflowSlaHours,
    ESCALATIONRULEGROUPID = @WorkflowEscalationRuleGroupId,
    PICKLISTID = @WorkflowPickListId,
    DISPLAYLABELID = @WorkflowDisplayLabelId,
    ISAUTOAPPROVE = @WorkflowIsAutoApprove
WHERE WORKFLOWDETAILID = @WorkflowDetailId";

        public const string DELETE_WORKFLOW_DETAILS = @"DELETE FROM MWORKFLOWDETAIL
                                                        WHERE WORKFLOWID = @WorkflowId;";

        public const string GET_SELECT_LIST_WORKFLOW_DETAILS = @"SELECT
                                                        WFD.WORKFLOWDETAILID       AS WorkflowDetailId,
                                                        WFD.WORKFLOWID             AS WorkflowId,
                                                        WFD.SLNO                   AS WorkflowDetailSlNo,
                                                        WFD.STEPKEY                AS WorkflowStepKey,
                                                        WFD.DISPLAYNAME            AS WorkflowDisplayName,
                                                        WFD.STEPTYPE               AS WorkflowStepType,
                                                        WFD.ISINITIAL              AS WorkflowIsInitial,
                                                        WFD.ISFINAL                AS WorkflowIsFinal,
                                                        WFD.ASSIGNMENTTYPE         AS WorkflowAssignmentType,
                                                        WFD.PARTICULARS            AS WorkflowDetailParticulars,
                                                        WFD.ASSIGNMENTID           AS WorkflowAssignmentId,
                                                        WFD.PARALLELGROUPKEY       AS WorkflowParallelGroupKey,
                                                        WFD.SLAHOURS               AS WorkflowSlaHours,
                                                        WFD.ESCALATIONRULEGROUPID  AS WorkflowEscalationRuleGroupId,
                                                        WFD.PICKLISTID             AS WorkflowPickListId,
                                                        WFD.DISPLAYLABELID         AS WorkflowDisplayLabelId
                                                    FROM MWORKFLOWDETAIL WFD
                                                    WHERE WFD.WORKFLOWID = @WorkflowId
                                                    ORDER BY WFD.SLNO;";
    }
}