namespace AnalyticsDAL.Query.ReportConfig
{
    public static class ReportDataSourceRuleQB
    {
        // Index: IX_MREPORTDATASOURCERULE_REPORTID
        public const string GET_RULE = @"
            SELECT  r.DATASOURCERULEID  AS DataSourceRuleId,
                    r.REPORTID          AS ReportId,
                    r.SLNO              AS SlNo,
                    r.RULETYPE          AS RuleType,
                    r.THRESHOLDVALUE    AS ThresholdValue,
                    r.TARGETDATASOURCE  AS TargetDataSource,
                    r.RULEPRIORITY      AS RulePriority,
                    r.REMARKS           AS Remarks
            FROM    MREPORTDATASOURCERULE r
            WHERE   r.DATASOURCERULEID = @DataSourceRuleId";

        public const string GET_RULES_BY_REPORT = @"
            SELECT  r.DATASOURCERULEID  AS DataSourceRuleId,
                    r.REPORTID          AS ReportId,
                    r.SLNO              AS SlNo,
                    r.RULETYPE          AS RuleType,
                    r.THRESHOLDVALUE    AS ThresholdValue,
                    r.TARGETDATASOURCE  AS TargetDataSource,
                    r.RULEPRIORITY      AS RulePriority,
                    r.REMARKS           AS Remarks
            FROM    MREPORTDATASOURCERULE r
            WHERE   r.REPORTID = @ReportId
            ORDER BY r.RULEPRIORITY, r.SLNO";

        public const string GET_MAX_SLNO = @"
            SELECT ISNULL(MAX(SLNO), 0)
            FROM   MREPORTDATASOURCERULE
            WHERE  REPORTID = @ReportId";

        public const string SAVE_RULE = @"
            INSERT INTO MREPORTDATASOURCERULE
                (DATASOURCERULEID, REPORTID, SLNO, RULETYPE,
                 THRESHOLDVALUE, TARGETDATASOURCE, RULEPRIORITY, REMARKS)
            VALUES
                (@DataSourceRuleId, @ReportId, @SlNo, @RuleType,
                 @ThresholdValue, @TargetDataSource, @RulePriority, @Remarks)";

        public const string UPDATE_RULE = @"
            UPDATE MREPORTDATASOURCERULE
            SET    RULETYPE         = @RuleType,
                   THRESHOLDVALUE   = @ThresholdValue,
                   TARGETDATASOURCE = @TargetDataSource,
                   RULEPRIORITY     = @RulePriority,
                   REMARKS          = @Remarks
            WHERE  DATASOURCERULEID = @DataSourceRuleId
            AND    REPORTID         = @ReportId";

        public const string DELETE_RULE = @"
            DELETE FROM MREPORTDATASOURCERULE
            WHERE  DATASOURCERULEID = @DataSourceRuleId
            AND    REPORTID         = @ReportId";

        public const string DELETE_ALL_RULES_BY_REPORT = @"
            DELETE FROM MREPORTDATASOURCERULE
            WHERE  REPORTID = @ReportId";

        // Index: IX_MREPORTDATASOURCERULE_REPORTID
        public const string GET_SELECT_LIST_DATASOURCERULE = @"
            SELECT r.DATASOURCERULEID AS Value,
                   CASE r.RULETYPE
                       WHEN 0 THEN N'Date Range'
                       WHEN 1 THEN N'Estimated Rows'
                       WHEN 2 THEN N'Time of Day'
                       WHEN 3 THEN N'Manual'
                       ELSE N'Rule ' + CAST(r.SLNO AS NVARCHAR(10))
                   END + N' → ' +
                   CASE r.TARGETDATASOURCE
                       WHEN 0 THEN N'OLTP'
                       WHEN 1 THEN N'Report DB'
                       WHEN 2 THEN N'Archive DB'
                       ELSE N'OLTP'
                   END              AS Label
            FROM   MREPORTDATASOURCERULE r
            WHERE  r.REPORTID = @ReportId
            ORDER  BY r.RULEPRIORITY, r.SLNO";
    }
}