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

namespace PayRollDAL.Query.Skill
{
    // Indexes recommended:
    //   MSKILL: IX_MSKILL_TENANTID_STATUS (TENANTID, STATUS)
    public static class SkillQB
    {
        // ── Picklist (paged, dual dialect) ──────────────────────────────────
        // GB4 parity: SkillBLL.GetSelectListSkill →
        // GetGenericCriteriaLoadFields<Poco.Skill>("Id,Code,Name", FirstNumber, MaxResult, LoginDTO, CriteriaDTO)

        public const string GET_SELECTLIST_SKILL_SQL = @"
            WITH Paged AS (
                SELECT S.SKILLID   AS Id,
                       S.SKILLCODE AS Code,
                       S.SKILLNAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY S.SKILLNAME) AS RowNum
                FROM   MSKILL S
                WHERE  1 = 1
                AND    (@searchtext IS NULL OR S.SKILLCODE LIKE @searchtext OR S.SKILLNAME LIKE @searchtext)
            )
            SELECT Id, Code, Name
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_SKILL_PG = @"
            WITH Paged AS (
                SELECT S.SKILLID   AS ""Id"",
                       S.SKILLCODE AS ""Code"",
                       S.SKILLNAME AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY S.SKILLNAME) AS ""RowNum""
                FROM   MSKILL S
                WHERE  1 = 1
                AND    (:searchtext IS NULL OR S.SKILLCODE LIKE :searchtext OR S.SKILLNAME LIKE :searchtext)
            )
            SELECT ""Id"", ""Code"", ""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_SKILL_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   MSKILL S
            WHERE  1 = 1
            AND    (@searchtext IS NULL OR S.SKILLCODE LIKE @searchtext OR S.SKILLNAME LIKE @searchtext);";

        public const string GET_SELECTLIST_SKILL_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   MSKILL S
            WHERE  1 = 1
            AND    (:searchtext IS NULL OR S.SKILLCODE LIKE :searchtext OR S.SKILLNAME LIKE :searchtext);";
    }
}