namespace MMDAL.Query.ForeCast
{
    public static class ForeCastQB
    {
        public const string GET_SELECTLIST_FORECAST_SQL = @"
                                                    WITH Paged AS (
                                                        SELECT
                                                            f.FORECASTID     AS Id,
                                                            f.FORECASTNUMBER AS ForeCastNumber,
                                                            f.FORECASTDATE   AS ForeCastDate,
                                                            ROW_NUMBER() OVER (ORDER BY f.FORECASTID DESC) AS RowNum
                                                        FROM MFORECAST f
                                                        WHERE 1 = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        ForeCastNumber,
                                                        ForeCastDate
                                                    FROM Paged
                                                    WHERE
                                                          (@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        public const string GET_SELECTLIST_FORECAST_PG = @"
                                                    WITH Paged AS (
                                                        SELECT
                                                            f.FORECASTID     AS ""Id"",
                                                            f.FORECASTNUMBER AS ""ForeCastNumber"",
                                                            f.FORECASTDATE   AS ""ForeCastDate"",
                                                            ROW_NUMBER() OVER (ORDER BY f.FORECASTID DESC) AS ""RowNum""
                                                        FROM TFORECAST f
                                                        WHERE 1 = 1
                                                    )
                                                    SELECT
                                                        ""Id"",
                                                        ""ForeCastNumber"",
                                                        ""ForeCastDate""
                                                    FROM Paged
                                                    WHERE
                                                          (:firstnumber = -1 AND :maxresult = -1)
                                                       OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
                                                    ";

        public const string GET_SELECTLIST_FORECAST_ORACLE = @";";
        public const string GET_SELECTLIST_FORECAST_MYSQL = @";";

        // GB4 parity: ForeCast.svc/Version/SelectList — ForeCastBLL.GetSelectListForeCastVersion
        // against ForeCastDataQueryBuilder.FORECAST_SELECTLIT_VERSION (distinct FCVERSION for a ForeCast).
        public const string GET_SELECTLIST_FORECAST_VERSION_SQL = @"
            SELECT DISTINCT b.FCVERSION AS FcVersion
            FROM MFORECAST a
            JOIN TFORECASTDATA b ON a.FORECASTID = b.FORECASTID
            WHERE b.FORECASTID = @ForeCastId
            ORDER BY b.FCVERSION ASC;";

        public const string GET_SELECTLIST_FORECAST_VERSION_PG = @"
            SELECT DISTINCT b.""FCVERSION"" AS ""FcVersion""
            FROM ""TFORECAST"" a
            JOIN ""TFORECASTDATA"" b ON a.""FORECASTID"" = b.""FORECASTID""
            WHERE b.""FORECASTID"" = :ForeCastId
            ORDER BY b.""FCVERSION"" ASC;";
    }
}