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

namespace PayRollDAL.Query.Subject
{
    // Indexes recommended:
    //   MSUBJECT: IX_MSUBJECT_TENANTID_STATUS (TENANTID, STATUS)
    public static class SubjectQB
    {
        // ── Picklist (paged, dual dialect) ──────────────────────────────────
        // GB4 parity: SubjectBLL.GetSelectListSubject →
        // GetGenericCriteriaLoadFields<Poco.Subject>("Id,Code,Name", FirstNumber, MaxResult, LoginDTO, CriteriaDTO)

        public const string GET_SELECTLIST_SUBJECT_SQL = @"
            WITH Paged AS (
                SELECT S.SUBJECTID   AS Id,
                       S.SUBJECTCODE AS Code,
                       S.SUBJECTNAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY S.SUBJECTNAME) AS RowNum
                FROM   MSUBJECT S
                WHERE  1 = 1
                AND    (@searchtext IS NULL OR S.SUBJECTCODE LIKE @searchtext OR S.SUBJECTNAME 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_SUBJECT_PG = @"
            WITH Paged AS (
                SELECT S.SUBJECTID   AS ""Id"",
                       S.SUBJECTCODE AS ""Code"",
                       S.SUBJECTNAME AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY S.SUBJECTNAME) AS ""RowNum""
                FROM   MSUBJECT S
                WHERE  1 = 1
                AND    (:searchtext IS NULL OR S.SUBJECTCODE LIKE :searchtext OR S.SUBJECTNAME 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_SUBJECT_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   MSUBJECT S
            WHERE  1 = 1
            AND    (@searchtext IS NULL OR S.SUBJECTCODE LIKE @searchtext OR S.SUBJECTNAME LIKE @searchtext);";

        public const string GET_SELECTLIST_SUBJECT_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   MSUBJECT S
            WHERE  1 = 1
            AND    (:searchtext IS NULL OR S.SUBJECTCODE LIKE :searchtext OR S.SUBJECTNAME LIKE :searchtext);";
    }
}