using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.Objective
{
    public static class ObjectiveQB
    {
        // ── Picklist (paged, dual-dialect) ──────────────────────────────────
        // Mirrors GB4 ObjectiveQueryBuilder.GET_OBJECTIVE_SELECTLIST (MOBJECTIVE,
        // OBJECTIVEID <> -1 sentinel row excluded), ported to GB5's paged/dual-dialect
        // SelectList convention (see PayTaxTypeQB.GET_SELECTLIST_PAYTAXTYPE_SQL).
        // Indexes recommended:
        //   MOBJECTIVE: IX_MOBJECTIVE_NAME (OBJECTIVENAME)

        public const string GET_SELECTLIST_OBJECTIVE_SQL = @"
WITH Paged AS (
    SELECT obj.OBJECTIVEID   AS Id,
           obj.OBJECTIVENAME AS Name,
           obj.DESCRIPTION   AS Description,
           ROW_NUMBER() OVER (ORDER BY obj.OBJECTIVENAME) AS RowNum
    FROM   MOBJECTIVE obj
    WHERE  obj.OBJECTIVEID <> -1 /*FILTER*/
)
SELECT Id, Name, Description
FROM   Paged
WHERE  (@firstnumber = -1 AND @maxresult = -1)
   OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_OBJECTIVE_PG = @"
WITH Paged AS (
    SELECT obj.OBJECTIVEID   AS ""Id"",
           obj.OBJECTIVENAME AS ""Name"",
           obj.DESCRIPTION   AS ""Description"",
           ROW_NUMBER() OVER (ORDER BY obj.OBJECTIVENAME) AS ""RowNum""
    FROM   MOBJECTIVE obj
    WHERE  obj.OBJECTIVEID <> -1 /*FILTER*/
)
SELECT ""Id"", ""Name"", ""Description""
FROM   Paged
WHERE  (:firstnumber = -1 AND :maxresult = -1)
   OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_SELECTLIST_OBJECTIVE_COUNT_SQL = @"
SELECT COUNT(*)
FROM   MOBJECTIVE obj
WHERE  obj.OBJECTIVEID <> -1 /*FILTER*/;";

        public const string GET_SELECTLIST_OBJECTIVE_COUNT_PG = @"
SELECT COUNT(*)
FROM   MOBJECTIVE obj
WHERE  obj.OBJECTIVEID <> -1 /*FILTER*/;";
    }
}