namespace FrameworkDAL.Query.EIPConversation.EIPRoutingAdmin
{
    // INDEX required on MEIPROUTINGRULE:
    //   IX_MEIPROUTINGRULE_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (PRIORITY, CHANNELTYPE)
    public static class EIPRoutingAdminQB
    {
        // =====================================================
        // List all active routing rules for a tenant, ordered by priority
        // =====================================================
        public const string GET_ROUTE_LIST = @"
            SELECT
                R.ROUTINGRULEID      AS RoutingRuleId,
                R.TENANTID           AS TenantId,
                R.CHANNELTYPE        AS ChannelType,
                R.FLOWDEFINITIONID   AS FlowId,
                F.FLOWCODE           AS TargetFlowCode,
                ISNULL(R.PRIORITY, 1) AS PriorityOrder,
                R.MATCHVALUE         AS Pattern,
                R.MATCHTYPE          AS MatchType,
                R.STATUS             AS Status,
                R.VERSION            AS Version,
                R.CREATEDBYID        AS CreatedById,
                CB.EMPLOYEENAME      AS CreatedByName,
                R.CREATEDON          AS CreatedOn,
                R.MODIFIEDBYID       AS ModifiedById,
                MB.EMPLOYEENAME      AS ModifiedByName,
                R.MODIFIEDON         AS ModifiedOn
            FROM MEIPROUTINGRULE R
            LEFT JOIN MEIPFLOWDEFINITION F ON R.FLOWDEFINITIONID = F.FLOWDEFINITIONID
            LEFT JOIN MEMPLOYEE CB ON R.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON R.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE R.TENANTID = @TenantId
              AND R.STATUS   = 1
              AND (@ChannelType IS NULL OR R.CHANNELTYPE = @ChannelType)
            ORDER BY ISNULL(R.PRIORITY, 1) ASC";

        // =====================================================
        // Single routing rule by ID
        // =====================================================
        public const string GET_ROUTE = @"
            SELECT
                R.ROUTINGRULEID      AS RoutingRuleId,
                R.TENANTID           AS TenantId,
                R.CHANNELTYPE        AS ChannelType,
                R.FLOWDEFINITIONID   AS FlowId,
                F.FLOWCODE           AS TargetFlowCode,
                ISNULL(R.PRIORITY, 1) AS PriorityOrder,
                R.MATCHVALUE         AS Pattern,
                R.MATCHTYPE          AS MatchType,
                R.STATUS             AS Status,
                R.VERSION            AS Version,
                R.CREATEDBYID        AS CreatedById,
                CB.EMPLOYEENAME      AS CreatedByName,
                R.CREATEDON          AS CreatedOn,
                R.MODIFIEDBYID       AS ModifiedById,
                MB.EMPLOYEENAME      AS ModifiedByName,
                R.MODIFIEDON         AS ModifiedOn
            FROM MEIPROUTINGRULE R
            LEFT JOIN MEIPFLOWDEFINITION F ON R.FLOWDEFINITIONID = F.FLOWDEFINITIONID
            LEFT JOIN MEMPLOYEE CB ON R.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON R.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE R.ROUTINGRULEID = @RoutingRuleId
              AND R.TENANTID      = @TenantId
              AND R.STATUS        = 1";

        // =====================================================
        // Insert — ROUTINGRULEID is a plain INT PK (NOT IDENTITY, confirmed live via
        // INFORMATION_SCHEMA 2026-08-13) — the caller must supply it via
        // AUTONUMBERCONSTANT.EIPROUTINGRULE, same convention as MEIPFLOWDEFINITION.
        // =====================================================
        public const string INSERT_ROUTE = @"
            INSERT INTO MEIPROUTINGRULE (
                ROUTINGRULEID,
                TENANTID,
                CHANNELTYPE,
                FLOWDEFINITIONID,
                PRIORITY,
                MATCHVALUE,
                MATCHTYPE,
                STATUS,
                VERSION,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES (
                @RoutingRuleId,
                @TenantId,
                @ChannelType,
                @FlowId,
                @PriorityOrder,
                @Pattern,
                @MatchType,
                1,
                0,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE()
            )";

        // =====================================================
        // Update routing rule
        // =====================================================
        public const string UPDATE_ROUTE = @"
            UPDATE MEIPROUTINGRULE
            SET
                CHANNELTYPE      = @ChannelType,
                FLOWDEFINITIONID = @FlowId,
                PRIORITY         = @PriorityOrder,
                MATCHVALUE       = @Pattern,
                MATCHTYPE        = @MatchType,
                VERSION          = @Version,
                MODIFIEDBYID     = @ModifiedById,
                MODIFIEDON       = GETDATE()
            WHERE ROUTINGRULEID = @RoutingRuleId
              AND TENANTID      = @TenantId
              AND STATUS        = 1";

        // =====================================================
        // Soft-delete
        // =====================================================
        public const string DELETE_ROUTE = @"
            UPDATE MEIPROUTINGRULE
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE ROUTINGRULEID = @RoutingRuleId
              AND TENANTID      = @TenantId
              AND STATUS        = 1";
    }
}