namespace FrameworkDAL.Query.DirectAction
{
    // INDEX: MDIRECTACTIONDETAIL — covering index on (DIRECTACTIONID, STATUS)
    //        MDIRECTACTIONDETAIL — covering index on (ACTIONCODE)
    public static class DirectActionConfigQB
    {
        public const string GET_DETAILS_BY_DIRECTACTIONID = @"
            SELECT d.DIRECTACTIONDETAILID  AS DirectActionDetailId,
                   d.DIRECTACTIONID        AS DirectActionId,
                   d.SLNO                  AS Slno,
                   d.ACTIONCODE            AS ActionCode,
                   d.ACTIONLABEL           AS ActionLabel,
                   d.EMAILPLACEHOLDER      AS EmailPlaceholder,
                   d.BUTTONPAYLOADPREFIX   AS ButtonPayloadPrefix,
                   d.APIENDPOINT           AS ApiEndpoint,
                   d.HTTPMETHOD            AS HttpMethod,
                   d.PAYLOADTEMPLATE       AS PayloadTemplate,
                   d.NEEDSINPUT            AS NeedsInput,
                   d.FLOWID                AS FlowId
            FROM   MDIRECTACTIONDETAIL d
            INNER JOIN MDIRECTACTION h ON h.DIRECTACTIONID = d.DIRECTACTIONID
            WHERE  d.DIRECTACTIONID = @DirectActionId
              AND  h.TENANTID       IN (@TenantId, -1)
            ORDER  BY d.SLNO";

        public const string GET_DETAIL_BY_ACTIONCODE = @"
            SELECT d.DIRECTACTIONDETAILID  AS DirectActionDetailId,
                   d.DIRECTACTIONID        AS DirectActionId,
                   d.SLNO                  AS Slno,
                   d.ACTIONCODE            AS ActionCode,
                   d.ACTIONLABEL           AS ActionLabel,
                   d.EMAILPLACEHOLDER      AS EmailPlaceholder,
                   d.BUTTONPAYLOADPREFIX   AS ButtonPayloadPrefix,
                   d.APIENDPOINT           AS ApiEndpoint,
                   d.HTTPMETHOD            AS HttpMethod,
                   d.PAYLOADTEMPLATE       AS PayloadTemplate,
                   d.NEEDSINPUT            AS NeedsInput,
                   d.FLOWID                AS FlowId
            FROM   MDIRECTACTIONDETAIL d
            INNER JOIN MDIRECTACTION h ON h.DIRECTACTIONID = d.DIRECTACTIONID
            WHERE  d.ACTIONCODE = @ActionCode
              AND  h.TENANTID   IN (@TenantId, -1)";

        public const string GET_HEADER_BY_ID = @"
            SELECT h.DIRECTACTIONID       AS DirectActionId,
                   h.DIRECTACTIONCODE     AS DirectActionCode,
                   h.DIRECTACTIONNAME     AS DirectActionName,
                   h.CONTEXTIDFIELD       AS ContextIdField,
                   h.ASSIGNEEUSERIDFIELD  AS AssigneeUserIdField,
                   h.TENANTID             AS TenantId
            FROM   MDIRECTACTION h
            WHERE  h.DIRECTACTIONID = @DirectActionId
              AND  h.STATUS         = 1
              AND  h.TENANTID       IN (@TenantId, -1)";

        // Resolves MDIRECTACTIONDETAIL.FLOWID (int) -> MEIPFLOWDEFINITION.FLOWCODE (string) —
        // the EIP engine looks flows up by FlowCode, never by the int definition id. Only a
        // Published (FLOWSTATUS=2) flow is eligible; tenant-specific row wins over -1=global.
        public const string GET_FLOWCODE_BY_FLOWID = @"
            SELECT TOP 1 FLOWCODE
            FROM   MEIPFLOWDEFINITION
            WHERE  FLOWDEFINITIONID = @FlowId
              AND  (TENANTID = @TenantId OR TENANTID = -1)
              AND  FLOWSTATUS = 2
            ORDER  BY CASE WHEN TENANTID = @TenantId THEN 0 ELSE 1 END";

        public const string GET_ALL_ACTIVE_DETAILS = @"
            SELECT d.DIRECTACTIONDETAILID  AS DirectActionDetailId,
                   d.DIRECTACTIONID        AS DirectActionId,
                   d.SLNO                  AS Slno,
                   d.ACTIONCODE            AS ActionCode,
                   d.ACTIONLABEL           AS ActionLabel,
                   d.EMAILPLACEHOLDER      AS EmailPlaceholder,
                   d.BUTTONPAYLOADPREFIX   AS ButtonPayloadPrefix,
                   d.APIENDPOINT           AS ApiEndpoint,
                   d.HTTPMETHOD            AS HttpMethod,
                   d.PAYLOADTEMPLATE       AS PayloadTemplate,
                   d.NEEDSINPUT            AS NeedsInput,
                   d.FLOWID                AS FlowId
            FROM   MDIRECTACTIONDETAIL d
            INNER JOIN MDIRECTACTION h ON h.DIRECTACTIONID = d.DIRECTACTIONID
            WHERE  h.STATUS   = 1
              AND  h.TENANTID IN (@TenantId, -1)
            ORDER  BY d.DIRECTACTIONID, d.SLNO";
    }
}