namespace AnalyticsDAL.Query.ReportConfig
{
    public static class ReportConfigQB
    {
        // Index: IX_MREPORTCONFIG_REPORTID_TENANTID, UK_MREPORTCONFIG_REPORTID
        public const string GET_REPORTCONFIG = @"
            SELECT  rc.REPORTCONFIGID           AS ReportConfigId,
                    rc.REPORTID                 AS ReportId,
                    rc.DATASOURCETYPE           AS DataSourceType,
                    rc.WORKSPACEID              AS WorkspaceId,
                    rc.ASYNCTHRESHOLDROWS       AS AsyncThresholdRows,
                    rc.MAXEXPORTROWS            AS MaxExportRows,
                    rc.ESTIMATIONQUERY          AS EstimationQuery,
                    rc.CACHEDURATIONMINUTES     AS CacheDurationMinutes,
                    rc.CACHEKEYPARAMS           AS CacheKeyParams,
                    rc.DEFAULTEXPORTFORMATID    AS DefaultExportFormatId,
                    rc.ALLOWEDEXPORTFORMATS     AS AllowedExportFormats,
                    rc.REPORTCATEGORY           AS ReportCategory,
                    rc.PIVOTENGINE              AS PivotEngine,
                    rc.ISSCHEDULABLE            AS IsSchedulable,
                    rc.DEFAULTPRIORITY          AS DefaultPriority,
                    rc.RESULTEXPIRYHOURS        AS ResultExpiryHours,
                    rc.REMARKS                  AS Remarks,
                    rc.VERSION                  AS Version,
                    rc.STATUS                   AS Status,
                    rc.SORTORDER                AS SortOrder,
                    rc.SOURCETYPE               AS SourceType,
                    rc.TENANTID                 AS TenantId,
                    rc.CREATEDBYID              AS CreatedById,
                    rc.CREATEDON                AS CreatedOn,
                    cb.USERNAME                 AS CreatedByName,
                    rc.MODIFIEDBYID             AS ModifiedById,
                    rc.MODIFIEDON               AS ModifiedOn,
                    mb.USERNAME                 AS ModifiedByName
            FROM    MREPORTCONFIG rc
            LEFT JOIN MUSER cb ON cb.USERID = rc.CREATEDBYID
            LEFT JOIN MUSER mb ON mb.USERID = rc.MODIFIEDBYID
            WHERE   rc.REPORTCONFIGID = @ReportConfigId
            AND     rc.TENANTID       = @TenantId
            AND     rc.STATUS        <> 2";

        public const string GET_REPORTCONFIG_BY_REPORT = @"
            SELECT  rc.REPORTCONFIGID           AS ReportConfigId,
                    rc.REPORTID                 AS ReportId,
                    rc.DATASOURCETYPE           AS DataSourceType,
                    rc.WORKSPACEID              AS WorkspaceId,
                    rc.ASYNCTHRESHOLDROWS       AS AsyncThresholdRows,
                    rc.MAXEXPORTROWS            AS MaxExportRows,
                    rc.ESTIMATIONQUERY          AS EstimationQuery,
                    rc.CACHEDURATIONMINUTES     AS CacheDurationMinutes,
                    rc.CACHEKEYPARAMS           AS CacheKeyParams,
                    rc.DEFAULTEXPORTFORMATID    AS DefaultExportFormatId,
                    rc.ALLOWEDEXPORTFORMATS     AS AllowedExportFormats,
                    rc.REPORTCATEGORY           AS ReportCategory,
                    rc.PIVOTENGINE              AS PivotEngine,
                    rc.ISSCHEDULABLE            AS IsSchedulable,
                    rc.DEFAULTPRIORITY          AS DefaultPriority,
                    rc.RESULTEXPIRYHOURS        AS ResultExpiryHours,
                    rc.REMARKS                  AS Remarks,
                    rc.VERSION                  AS Version,
                    rc.STATUS                   AS Status,
                    rc.SORTORDER                AS SortOrder,
                    rc.SOURCETYPE               AS SourceType,
                    rc.TENANTID                 AS TenantId,
                    rc.CREATEDBYID              AS CreatedById,
                    rc.CREATEDON                AS CreatedOn,
                    rc.MODIFIEDBYID             AS ModifiedById,
                    rc.MODIFIEDON               AS ModifiedOn
            FROM    MREPORTCONFIG rc
            WHERE   rc.REPORTID   = @ReportId
            AND     rc.TENANTID   = @TenantId
            AND     rc.STATUS    <> 2";

        public const string CHECK_DUPLICATE_REPORT = @"
            SELECT COUNT(1)
            FROM   MREPORTCONFIG
            WHERE  REPORTID       = @ReportId
            AND    TENANTID       = @TenantId
            AND    STATUS        <> 2
            AND    REPORTCONFIGID <> @ReportConfigId";

        public const string SAVE_REPORTCONFIG = @"
            INSERT INTO MREPORTCONFIG
                (REPORTCONFIGID, REPORTID, DATASOURCETYPE, WORKSPACEID,
                 ASYNCTHRESHOLDROWS, MAXEXPORTROWS, ESTIMATIONQUERY,
                 CACHEDURATIONMINUTES, CACHEKEYPARAMS,
                 DEFAULTEXPORTFORMATID, ALLOWEDEXPORTFORMATS,
                 REPORTCATEGORY, PIVOTENGINE, ISSCHEDULABLE,
                 DEFAULTPRIORITY, RESULTEXPIRYHOURS, REMARKS,
                 VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@ReportConfigId, @ReportId, @DataSourceType, @WorkspaceId,
                 @AsyncThresholdRows, @MaxExportRows, @EstimationQuery,
                 @CacheDurationMinutes, @CacheKeyParams,
                 @DefaultExportFormatId, @AllowedExportFormats,
                 @ReportCategory, @PivotEngine, @IsSchedulable,
                 @DefaultPriority, @ResultExpiryHours, @Remarks,
                 0, 1, @SortOrder, @SourceType, @TenantId,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_REPORTCONFIG = @"
            UPDATE MREPORTCONFIG
            SET    DATASOURCETYPE       = @DataSourceType,
                   WORKSPACEID          = @WorkspaceId,
                   ASYNCTHRESHOLDROWS   = @AsyncThresholdRows,
                   MAXEXPORTROWS        = @MaxExportRows,
                   ESTIMATIONQUERY      = @EstimationQuery,
                   CACHEDURATIONMINUTES = @CacheDurationMinutes,
                   CACHEKEYPARAMS       = @CacheKeyParams,
                   DEFAULTEXPORTFORMATID = @DefaultExportFormatId,
                   ALLOWEDEXPORTFORMATS = @AllowedExportFormats,
                   REPORTCATEGORY       = @ReportCategory,
                   PIVOTENGINE          = @PivotEngine,
                   ISSCHEDULABLE        = @IsSchedulable,
                   DEFAULTPRIORITY      = @DefaultPriority,
                   RESULTEXPIRYHOURS    = @ResultExpiryHours,
                   REMARKS              = @Remarks,
                   SORTORDER            = @SortOrder,
                   STATUS               = @Status,
                   VERSION              = VERSION + 1,
                   MODIFIEDBYID         = @ModifiedById,
                   MODIFIEDON           = GETDATE()
            WHERE  REPORTCONFIGID       = @ReportConfigId
            AND    TENANTID             = @TenantId";

        public const string DELETE_REPORTCONFIG = @"
            UPDATE MREPORTCONFIG
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  REPORTCONFIGID = @ReportConfigId
            AND    TENANTID       = @TenantId";

        // Index: IX_MREPORTCONFIG_REPORTID_TENANTID, IX_MREPORTCONFIG_STATUS
        public const string GET_SELECT_LIST_REPORTCONFIG = @"
            SELECT rc.REPORTCONFIGID AS Value,
                   CAST(rc.REPORTID AS NVARCHAR(20)) + N' — ' +
                   CASE rc.DATASOURCETYPE
                       WHEN 0 THEN N'OLTP (Live)'
                       WHEN 1 THEN N'Report DB'
                       WHEN 2 THEN N'Archive DB'
                       WHEN 3 THEN N'Auto'
                       ELSE N'Unknown'
                   END              AS Label
            FROM   MREPORTCONFIG rc
            WHERE  rc.TENANTID = @TenantId
            AND    rc.STATUS  <> 2
            ORDER  BY rc.SORTORDER, rc.REPORTCONFIGID";
    }
}