namespace AnalyticsDAL.Query.AnalysisWorkspace
{
    public static class AnalysisWorkspaceQB
    {
        // Index: UK_MANALYSISWORKSPACE_ANALYSISIDWORKSPACEID
        public const string GET_ANALYSIS_WORKSPACE = @"
            SELECT  aw.ANALYSISWORKSPACEID  AS AnalysisWorkspaceId,
                    aw.ANALYSISID           AS AnalysisId,
                    aw.WORKSPACEID          AS WorkspaceId,
                    aw.ISCROSSWORKSPACE     AS IsCrossWorkspace,
                    aw.DATASOURCETYPE       AS DataSourceType,
                    aw.OVERRIDEDATASOURCEID AS OverrideDataSourceId,
                    a.ANALYSISCODE          AS AnalysisCode,
                    a.ANALYSISNAME          AS AnalysisName,
                    w.WORKSPACENAME         AS WorkspaceName
            FROM    MANALYSISWORKSPACE aw
            JOIN    MANALYSIS           a  ON a.ANALYSISID  = aw.ANALYSISID
            JOIN    MWORKSPACE          w  ON w.WORKSPACEID = aw.WORKSPACEID
            WHERE   aw.ANALYSISWORKSPACEID = @AnalysisWorkspaceId";

        public const string GET_WORKSPACES_BY_ANALYSIS = @"
            SELECT  aw.ANALYSISWORKSPACEID  AS AnalysisWorkspaceId,
                    aw.ANALYSISID           AS AnalysisId,
                    aw.WORKSPACEID          AS WorkspaceId,
                    aw.ISCROSSWORKSPACE     AS IsCrossWorkspace,
                    aw.DATASOURCETYPE       AS DataSourceType,
                    aw.OVERRIDEDATASOURCEID AS OverrideDataSourceId,
                    a.ANALYSISCODE          AS AnalysisCode,
                    a.ANALYSISNAME          AS AnalysisName,
                    w.WORKSPACENAME         AS WorkspaceName
            FROM    MANALYSISWORKSPACE aw
            JOIN    MANALYSIS           a  ON a.ANALYSISID  = aw.ANALYSISID
            JOIN    MWORKSPACE          w  ON w.WORKSPACEID = aw.WORKSPACEID
            WHERE   aw.ANALYSISID = @AnalysisId
            ORDER BY w.WORKSPACENAME";

        public const string CHECK_DUPLICATE = @"
            SELECT COUNT(1)
            FROM   MANALYSISWORKSPACE
            WHERE  ANALYSISID           = @AnalysisId
            AND    WORKSPACEID          = @WorkspaceId
            AND    ANALYSISWORKSPACEID <> @AnalysisWorkspaceId";

        public const string SAVE_ANALYSIS_WORKSPACE = @"
            INSERT INTO MANALYSISWORKSPACE
                (ANALYSISWORKSPACEID, ANALYSISID, WORKSPACEID, ISCROSSWORKSPACE, DATASOURCETYPE, OVERRIDEDATASOURCEID)
            VALUES
                (@AnalysisWorkspaceId, @AnalysisId, @WorkspaceId, @IsCrossWorkspace, @DataSourceType, @OverrideDataSourceId)";

        public const string UPDATE_ANALYSIS_WORKSPACE = @"
            UPDATE MANALYSISWORKSPACE
            SET    ISCROSSWORKSPACE      = @IsCrossWorkspace,
                   DATASOURCETYPE        = @DataSourceType,
                   OVERRIDEDATASOURCEID  = @OverrideDataSourceId
            WHERE  ANALYSISWORKSPACEID = @AnalysisWorkspaceId";

        public const string DELETE_ANALYSIS_WORKSPACE = @"
            DELETE FROM MANALYSISWORKSPACE
            WHERE  ANALYSISWORKSPACEID = @AnalysisWorkspaceId";
    }
}