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

namespace MMDAL.Query.BinType
{
    // Indexes recommended:
    //   MBINTYPE: IX_MBINTYPE_TENANTID_STATUS (TENANTID, STATUS)
    public static class BinTypeQB
    {
        // ── Picklist (paged, dual dialect) ──────────────────────────────────
        // GB4 parity: BinTypeBLL.GetSelectListBinType →
        // GetGenericCriteriaLoadFields<Poco.BinType>("Id,Code,Name", -1, -1, LoginDTO, CriteriaDTO)

        public const string GET_SELECTLIST_BINTYPE_SQL = @"
            WITH Paged AS (
                SELECT B.BINTYPEID   AS Id,
                       B.BINTYPECODE AS Code,
                       B.BINTYPENAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY B.BINTYPENAME) AS RowNum
                FROM   MBINTYPE B
                WHERE  1 = 1
                AND    (@searchtext IS NULL OR B.BINTYPECODE LIKE @searchtext OR B.BINTYPENAME 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_BINTYPE_PG = @"
            WITH Paged AS (
                SELECT B.""BINTYPEID""   AS ""Id"",
                       B.""BINTYPECODE"" AS ""Code"",
                       B.""BINTYPENAME"" AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY B.""BINTYPENAME"") AS ""RowNum""
                FROM   ""MBINTYPE"" B
                WHERE  1 = 1
                AND    (:searchtext IS NULL OR B.""BINTYPECODE"" LIKE :searchtext OR B.""BINTYPENAME"" 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_BINTYPE_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   MBINTYPE B
            WHERE  1 = 1
            AND    (@searchtext IS NULL OR B.BINTYPECODE LIKE @searchtext OR B.BINTYPENAME LIKE @searchtext);";

        public const string GET_SELECTLIST_BINTYPE_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   ""MBINTYPE"" B
            WHERE  1 = 1
            AND    (:searchtext IS NULL OR B.""BINTYPECODE"" LIKE :searchtext OR B.""BINTYPENAME"" LIKE :searchtext);";
    }
}