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

namespace PayRollDAL.Query.WorkLoad
{
    // Indexes recommended:
    //   MWORKLOAD: IX_MWORKLOAD_OUID_TENANTID (OUID, TENANTID)
    public static class WorkLoadQB
    {
        // ── Picklist (paged, dual dialect) ──────────────────────────────────
        // GB4 parity: WorkLoadDAL.GetSelectListWorkLoad → WorkLoadQueryBuilder.GET_WORKLOAD_SELECT_LIST
        // Name = "<UnitGCMName> - <FromDate>" when a UNIT is assigned, filtered by the caller's OU + Tenant

        public const string GET_SELECTLIST_WORKLOAD_SQL = @"
            WITH Paged AS (
                SELECT W.WORKLOADID      AS Id,
                       W.WORKLOADVERSION AS WorkLoadingVersion,
                       CASE WHEN W.UNITID <> -1
                            THEN G.GCMNAME + ' - ' + CONVERT(varchar, W.FROMDATE, 106)
                       END               AS Name,
                       ROW_NUMBER() OVER (ORDER BY W.FROMDATE DESC) AS RowNum
                FROM   MWORKLOAD W
                INNER JOIN MGCM G ON W.UNITID = G.GCMID
                WHERE  W.OUID = @ouid
                AND    W.TENANTID = @tenantid
                AND    (@searchtext IS NULL OR G.GCMNAME LIKE @searchtext)
            )
            SELECT Id, WorkLoadingVersion, Name
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_WORKLOAD_PG = @"
            WITH Paged AS (
                SELECT W.""WORKLOADID""      AS ""Id"",
                       W.""WORKLOADVERSION"" AS ""WorkLoadingVersion"",
                       CASE WHEN W.""UNITID"" <> -1
                            THEN G.""GCMNAME"" || ' - ' || TO_CHAR(W.""FROMDATE"", 'DD Mon YYYY')
                       END                   AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY W.""FROMDATE"" DESC) AS ""RowNum""
                FROM   ""MWORKLOAD"" W
                INNER JOIN ""MGCM"" G ON W.""UNITID"" = G.""GCMID""
                WHERE  W.""OUID"" = :ouid
                AND    W.""TENANTID"" = :tenantid
                AND    (:searchtext IS NULL OR G.""GCMNAME"" LIKE :searchtext)
            )
            SELECT ""Id"", ""WorkLoadingVersion"", ""Name""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        // ── Count — real COUNT(*), not GB4's hardcoded "0" ───────────────────

        public const string GET_SELECTLIST_WORKLOAD_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   MWORKLOAD W
            INNER JOIN MGCM G ON W.UNITID = G.GCMID
            WHERE  W.OUID = @ouid
            AND    W.TENANTID = @tenantid
            AND    (@searchtext IS NULL OR G.GCMNAME LIKE @searchtext);";

        public const string GET_SELECTLIST_WORKLOAD_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   ""MWORKLOAD"" W
            INNER JOIN ""MGCM"" G ON W.""UNITID"" = G.""GCMID""
            WHERE  W.""OUID"" = :ouid
            AND    W.""TENANTID"" = :tenantid
            AND    (:searchtext IS NULL OR G.""GCMNAME"" LIKE :searchtext);";
    }
}