namespace FrameworkDAL.Query.EIPConversation.EIPSession
{
    // INDEX required: IX_TEIUSERSESSION_LOOKUP on (USERIDENTIFIER, TENANTID, CHANNELTYPE) WHERE STATUS = 1
    public static class EIPSessionQB
    {
        public const string GET_ACTIVE_SESSION = @"
            SELECT TOP 1
                SESSIONID       AS SessionId,
                USERIDENTIFIER  AS UserIdentifier,
                TENANTID        AS TenantId,
                CHANNELTYPE     AS ChannelType,
                FLOWCODE        AS FlowCode,
                CURRENTSTEPCODE AS CurrentStepCode,
                CONTEXTJSON     AS ContextJson,
                STARTEDAT       AS StartedAt,
                LASTACTIVITYAT  AS LastActivityAt
            FROM TEIUSERSESSION
            WHERE USERIDENTIFIER = @UserIdentifier
              AND TENANTID       = @TenantId
              AND CHANNELTYPE    = @ChannelType
              AND STATUS         = 1
              AND LASTACTIVITYAT > DATEADD(MINUTE, -30, GETUTCDATE())
            ORDER BY LASTACTIVITYAT DESC";

        public const string UPSERT_SESSION = @"
            MERGE TEIUSERSESSION WITH (HOLDLOCK) AS target
            USING (SELECT @UserIdentifier AS USERIDENTIFIER,
                          @TenantId       AS TENANTID,
                          @ChannelType    AS CHANNELTYPE) AS src
               ON target.USERIDENTIFIER = src.USERIDENTIFIER
              AND target.TENANTID       = src.TENANTID
              AND target.CHANNELTYPE    = src.CHANNELTYPE
              AND target.STATUS         = 1
            WHEN MATCHED THEN
                UPDATE SET
                    CURRENTSTEPCODE = @CurrentStepCode,
                    FLOWCODE        = @FlowCode,
                    CONTEXTJSON     = @ContextJson,
                    LASTACTIVITYAT  = GETUTCDATE()
            WHEN NOT MATCHED THEN
                INSERT (SESSIONID, USERIDENTIFIER, TENANTID, CHANNELTYPE,
                        FLOWCODE, CURRENTSTEPCODE, CONTEXTJSON,
                        STARTEDAT, LASTACTIVITYAT, STATUS)
                VALUES (@SessionId, @UserIdentifier, @TenantId, @ChannelType,
                        @FlowCode, @CurrentStepCode, @ContextJson,
                        GETUTCDATE(), GETUTCDATE(), 1)
            OUTPUT INSERTED.SESSIONID AS SessionId;";

        public const string EXPIRE_SESSION = @"
            UPDATE TEIUSERSESSION
            SET    STATUS         = 0,
                   LASTACTIVITYAT = GETUTCDATE()
            WHERE  USERIDENTIFIER = @UserIdentifier
              AND  TENANTID       = @TenantId
              AND  CHANNELTYPE    = @ChannelType
              AND  STATUS         = 1";

        public const string EXPIRE_STALE_SESSIONS = @"
            UPDATE TEIUSERSESSION
            SET STATUS = 0
            WHERE STATUS = 1
              AND LASTACTIVITYAT < DATEADD(MINUTE, -30, GETUTCDATE())";
    }
}