namespace AnalyticsDAL.Query.AnalysisObject
{
    public static class AnalysisObjectQB
    {
        // Index: IX_MANALYSISOBJECT_ANALYSISID_TENANTID (ANALYSISID, TENANTID)

        public const string GET_ANALYSISOBJECT = @"
            SELECT  ao.ANALYSISOBJECTID          AS AnalysisObjectId,
                    ao.ANALYSISID                AS AnalysisId,
                    a.ANALYSISCODE               AS AnalysisCode,
                    a.ANALYSISNAME               AS AnalysisName,
                    ao.SLNO                      AS AnalysisObjectSlNo,
                    ao.DBOBJECTID                AS DBObjectId,
                    db.DBOBJECTNAME              AS DBObjectName,
                    ao.FILTEREXPRESSION          AS AnalysisObjectFilterExpression,
                    ao.VARIABLES                 AS AnalysisObjectVariables,
                    ao.TENANTID                  AS TenantId,
                    ao.WORKSPACEID               AS WorkspaceId
            FROM    MANALYSISOBJECT ao
            LEFT JOIN MANALYSIS a  ON a.ANALYSISID  = ao.ANALYSISID
            LEFT JOIN DBOBJECT  db ON db.DBOBJECTID = ao.DBOBJECTID
            WHERE   ao.ANALYSISOBJECTID = @AnalysisObjectId
            AND     ao.TENANTID         = @TenantId";

        public const string GET_ANALYSISOBJECTS_BY_ANALYSIS = @"
            SELECT  ao.ANALYSISOBJECTID          AS AnalysisObjectId,
                    ao.ANALYSISID                AS AnalysisId,
                    a.ANALYSISCODE               AS AnalysisCode,
                    a.ANALYSISNAME               AS AnalysisName,
                    ao.SLNO                      AS AnalysisObjectSlNo,
                    ao.DBOBJECTID                AS DBObjectId,
                    db.DBOBJECTNAME              AS DBObjectName,
                    ao.FILTEREXPRESSION          AS AnalysisObjectFilterExpression,
                    ao.VARIABLES                 AS AnalysisObjectVariables,
                    ao.TENANTID                  AS TenantId,
                    ao.WORKSPACEID               AS WorkspaceId
            FROM    MANALYSISOBJECT ao
            LEFT JOIN MANALYSIS a  ON a.ANALYSISID  = ao.ANALYSISID
            LEFT JOIN DBOBJECT  db ON db.DBOBJECTID = ao.DBOBJECTID
            WHERE   ao.ANALYSISID = @AnalysisId
            AND     ao.TENANTID   = @TenantId
            ORDER BY ao.SLNO";

        public const string CHECK_DUPLICATE = @"
            SELECT COUNT(1)
            FROM   MANALYSISOBJECT
            WHERE  ANALYSISID       = @AnalysisId
            AND    DBOBJECTID       = @DbObjectId
            AND    TENANTID         = @TenantId
            AND    ANALYSISOBJECTID <> @AnalysisObjectId";

        public const string SAVE_ANALYSISOBJECT = @"
            INSERT INTO MANALYSISOBJECT
                (ANALYSISOBJECTID, ANALYSISID, SLNO, DBOBJECTID,
                 FILTEREXPRESSION, VARIABLES, TENANTID, WORKSPACEID)
            VALUES
                (@AnalysisObjectId, @AnalysisId, @AnalysisObjectSlNo, @DBObjectId,
                 @AnalysisObjectFilterExpression, @AnalysisObjectVariables, @TenantId, @WorkspaceId)";

        public const string UPDATE_ANALYSISOBJECT = @"
            UPDATE MANALYSISOBJECT
            SET    SLNO             = @AnalysisObjectSlNo,
                   DBOBJECTID       = @DBObjectId,
                   FILTEREXPRESSION = @AnalysisObjectFilterExpression,
                   VARIABLES        = @AnalysisObjectVariables,
                   WORKSPACEID      = @WorkspaceId
            WHERE  ANALYSISOBJECTID = @AnalysisObjectId
            AND    TENANTID         = @TenantId";

        public const string DELETE_ANALYSISOBJECT = @"
            DELETE FROM MANALYSISOBJECT
            WHERE  ANALYSISOBJECTID = @AnalysisObjectId
            AND    TENANTID         = @TenantId";
    }
}