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

namespace PayRollDAL.Query.ServiceRule
{
    // Indexes recommended:
    //   MSERVICERULE: IX_MSERVICERULE_PARTY (PARTYID, PARTYBRANCHID, WORKTYPEID, STATUS)
    //   MSERVICERULE: IX_MSERVICERULE_CODE  (SERVICERULECODE, SERVICERULENAME)
    public static class ServiceRuleQB
    {
        // ── Picklist (paged, dual dialect) ────────────────────────────────

        public const string GET_SELECTLIST_SERVICERULE_SQL = @"
            WITH Paged AS (
                SELECT a.SERVICERULEID   AS Id,
                       a.SERVICERULECODE AS Code,
                       a.SERVICERULENAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY a.SERVICERULENAME) AS RowNum
                FROM   MSERVICERULE a
                WHERE  1 = 1";

        public const string GET_SELECTLIST_SERVICERULE_SQL_FOOTER = @"
            )
            SELECT Id, Code, Name
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_SERVICERULE_PG = @"
            WITH Paged AS (
                SELECT a.SERVICERULEID   AS ""Id"",
                       a.SERVICERULECODE AS ""Code"",
                       a.SERVICERULENAME AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY a.SERVICERULENAME) AS ""RowNum""
                FROM   MSERVICERULE a
                WHERE  1 = 1";

        public const string GET_SELECTLIST_SERVICERULE_PG_FOOTER = @"
            )
            SELECT ""Id"", ""Code"", ""Name""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        // ── Count (real count — GB4's count branch was a hardcoded no-op) ──

        public const string GET_SELECTLIST_SERVICERULE_COUNT_SQL = @"
            SELECT COUNT(*) FROM (
                SELECT a.SERVICERULEID AS Id
                FROM   MSERVICERULE a
                WHERE  1 = 1";

        public const string GET_SELECTLIST_SERVICERULE_COUNT_FOOTER = @"
            ) x;";
    }
}