namespace AnalyticsDAL.Query.AnalysisMasterFields
{
    // Required indexes:
    //   IX_MANALYSISMASTERFIELDS_TENANTID_WORKSPACEID ON MANALYSISMASTERFIELDS (TENANTID, WORKSPACEID) -- from amendment migration
    //   IX_MANALYSISMASTERFIELDS_ANALYSISID ON MANALYSISMASTERFIELDS (ANALYSISID, TENANTID)
    public static class AnalysisMasterFieldsQB
    {
        public const string GET_ANALYSIS_MASTER_FIELDS = @"
            SELECT
                amf.MASTERFIELDSID       AS MasterFieldsId,
                amf.ANALYSISID           AS AnalysisId,
                amf.CRITERIAATTRIBUTEID  AS CriteriaAttributeId,
                amf.FIELDNAME            AS FieldName,
                amf.DISPLAYNAME          AS DisplayName,
                amf.DBFIELDNAME          AS DbFieldName,
                amf.FILTERCONDITION      AS FilterCondition,
                amf.FIELDTYPE            AS FieldType,
                amf.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType,
                amf.CRITERIAATTRIBUTENAME AS CriteriaAttributeName,
                amf.VARIABLES            AS Variables,
                amf.ISFILTERREQUIRED     AS IsFilterRequired,
                amf.SORTORDER            AS SortOrder,
                amf.STATUS               AS Status,
                amf.VERSION              AS Version,
                amf.SOURCETYPE           AS SourceType,
                amf.TENANTID             AS TenantId,
                amf.WORKSPACEID          AS WorkspaceId,
                amf.CREATEDBYID          AS CreatedById,
                amf.CREATEDON            AS CreatedOn,
                amf.MODIFIEDBYID         AS ModifiedById,
                amf.MODIFIEDON           AS ModifiedOn,
                uc.USERNAME              AS CreatedByName,
                um.USERNAME              AS ModifiedByName,
                ma.ANALYSISNAME          AS AnalysisName
            FROM   MANALYSISMASTERFIELDS amf
            LEFT JOIN MUSER uc ON uc.USERID = amf.CREATEDBYID
            LEFT JOIN MUSER um ON um.USERID = amf.MODIFIEDBYID
            LEFT JOIN MANALYSIS ma ON ma.ANALYSISID = amf.ANALYSISID
            WHERE  amf.MASTERFIELDSID = @MasterFieldsId
              AND  amf.TENANTID = @TenantId
              AND  amf.STATUS <> 2";

        public const string GET_MASTER_FIELDS_BY_ANALYSIS = @"
            SELECT
                amf.MASTERFIELDSID       AS MasterFieldsId,
                amf.ANALYSISID           AS AnalysisId,
                amf.CRITERIAATTRIBUTEID  AS CriteriaAttributeId,
                amf.FIELDNAME            AS FieldName,
                amf.DISPLAYNAME          AS DisplayName,
                amf.DBFIELDNAME          AS DbFieldName,
                amf.FILTERCONDITION      AS FilterCondition,
                amf.FIELDTYPE            AS FieldType,
                amf.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType,
                amf.CRITERIAATTRIBUTENAME AS CriteriaAttributeName,
                amf.VARIABLES            AS Variables,
                amf.ISFILTERREQUIRED     AS IsFilterRequired,
                amf.SORTORDER            AS SortOrder,
                amf.STATUS               AS Status,
                amf.VERSION              AS Version,
                amf.SOURCETYPE           AS SourceType,
                amf.TENANTID             AS TenantId,
                amf.WORKSPACEID          AS WorkspaceId,
                amf.CREATEDBYID          AS CreatedById,
                amf.CREATEDON            AS CreatedOn,
                amf.MODIFIEDBYID         AS ModifiedById,
                amf.MODIFIEDON           AS ModifiedOn,
                uc.USERNAME              AS CreatedByName,
                um.USERNAME              AS ModifiedByName,
                ma.ANALYSISNAME          AS AnalysisName
            FROM   MANALYSISMASTERFIELDS amf
            LEFT JOIN MUSER uc ON uc.USERID = amf.CREATEDBYID
            LEFT JOIN MUSER um ON um.USERID = amf.MODIFIEDBYID
            LEFT JOIN MANALYSIS ma ON ma.ANALYSISID = amf.ANALYSISID
            WHERE  amf.ANALYSISID = @AnalysisId
              AND  amf.TENANTID = @TenantId
              AND  amf.STATUS <> 2
            ORDER BY amf.SORTORDER, amf.MASTERFIELDSID";

        public const string GET_SELECTLIST_MASTER_FIELDS_BY_ANALYSIS = @"
            SELECT
                amf.MASTERFIELDSID       AS MasterFieldsId,
                amf.FIELDNAME            AS FieldName,
                amf.DISPLAYNAME          AS DisplayName,
                amf.DBFIELDNAME          AS DbFieldName,
                amf.FIELDTYPE            AS FieldType,
                amf.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType
            FROM   MANALYSISMASTERFIELDS amf
            WHERE  amf.ANALYSISID = @AnalysisId
              AND  amf.TENANTID = @TenantId
              AND  amf.STATUS = 1
            ORDER BY amf.SORTORDER, amf.FIELDNAME";

        public const string CHECK_DUPLICATE_FIELDNAME = @"
            SELECT COUNT(1)
            FROM   MANALYSISMASTERFIELDS
            WHERE  ANALYSISID = @AnalysisId
              AND  FIELDNAME   = @FieldName
              AND  TENANTID    = @TenantId
              AND  STATUS     <> 2
              AND  MASTERFIELDSID <> @MasterFieldsId";

        public const string SAVE_ANALYSIS_MASTER_FIELDS = @"
            INSERT INTO MANALYSISMASTERFIELDS
                (ANALYSISID, CRITERIAATTRIBUTEID, FIELDNAME, DISPLAYNAME, DBFIELDNAME,
                 FILTERCONDITION, FIELDTYPE, CRITERIAATTRIBUTETYPE, CRITERIAATTRIBUTENAME,
                 VARIABLES, ISFILTERREQUIRED, SORTORDER, STATUS, VERSION, SOURCETYPE,
                 TENANTID, WORKSPACEID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@AnalysisId, @CriteriaAttributeId, @FieldName, @DisplayName, @DbFieldName,
                 @FilterCondition, @FieldType, @CriteriaAttributeType, @CriteriaAttributeName,
                 @Variables, @IsFilterRequired, @SortOrder, 1, 1, @SourceType,
                 @TenantId, @WorkspaceId, @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE())
            SELECT SCOPE_IDENTITY()";

        public const string UPDATE_ANALYSIS_MASTER_FIELDS = @"
            UPDATE MANALYSISMASTERFIELDS SET
                CRITERIAATTRIBUTEID   = @CriteriaAttributeId,
                FIELDNAME             = @FieldName,
                DISPLAYNAME           = @DisplayName,
                DBFIELDNAME           = @DbFieldName,
                FILTERCONDITION       = @FilterCondition,
                FIELDTYPE             = @FieldType,
                CRITERIAATTRIBUTETYPE = @CriteriaAttributeType,
                CRITERIAATTRIBUTENAME = @CriteriaAttributeName,
                VARIABLES             = @Variables,
                ISFILTERREQUIRED      = @IsFilterRequired,
                SORTORDER             = @SortOrder,
                WORKSPACEID           = @WorkspaceId,
                VERSION               = VERSION + 1,
                MODIFIEDBYID          = @ModifiedById,
                MODIFIEDON            = GETUTCDATE()
            WHERE  MASTERFIELDSID = @MasterFieldsId
              AND  TENANTID = @TenantId";

        public const string DELETE_ANALYSIS_MASTER_FIELDS = @"
            UPDATE MANALYSISMASTERFIELDS SET
                STATUS       = 2,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETUTCDATE()
            WHERE  MASTERFIELDSID = @MasterFieldsId
              AND  TENANTID = @TenantId";
    }
}