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

namespace PayRollDAL.Query.TDSSection
{
    public static class TDSSectionQB
    {
        // SQL Server query
        public const string GET_SELECTLISTTDSSECTIONDETAIL_SQL = @"
            SELECT 
                b.TDSSECTIONID               AS TDSSectionId,
                b.TDSSECTIONDETAILID         AS TDSSectionDetailId,
                b.SECTIONDETAIL              AS TDSSectionDetailSectionDetail,
                b.REMARKS                    AS TDSSectionDetailRemarks,
                b.PARTICULARS                AS TDSSectionDetailParticulars
            FROM MTDSSECTION a
            INNER JOIN MTDSSECTIONDETAIL b
                ON a.TDSSECTIONID = b.TDSSECTIONID
            INNER JOIN MPAYTAXTYPE c
                ON a.PAYTAXTYPEID = c.PAYTAXTYPEID
            WHERE 1=1";

        // PostgreSQL query
        public const string GET_SELECTLISTTDSSECTIONDETAIL_PG = @"
            SELECT 
                b.""TDSSECTIONID""               AS ""TDSSectionId"",
                b.""TDSSECTIONDETAILID""         AS ""TDSSectionDetailId"",
                b.""SECTIONDETAIL""              AS ""TDSSectionDetailSectionDetail"",
                b.""REMARKS""                    AS ""TDSSectionDetailRemarks"",
                b.""PARTICULARS""                AS ""TDSSectionDetailParticulars""
            FROM ""MTDSSECTION"" a
            INNER JOIN ""MTDSSECTIONDETAIL"" b
                ON a.""TDSSECTIONID"" = b.""TDSSECTIONID""
            INNER JOIN ""MPAYTAXTYPE"" c
                ON a.""PAYTAXTYPEID"" = c.""PAYTAXTYPEID""
            WHERE 1=1";

        // ── Picklist (paged, dual dialect) ──────────────────────────────────
        // GB4 parity: TDSSectionBLL.GetSelectListTDSSection →
        // GetGenericCriteriaLoadFields<Poco.TDSSection>("Id,TDSSections,Section", FirstNumber, MaxResult, LoginDTO, CriteriaDTO)

        public const string GET_SELECTLIST_TDSSECTION_SQL = @"
            WITH Paged AS (
                SELECT T.TDSSECTIONID AS Id,
                       T.TDSSECTION   AS TDSSections,
                       T.SECTION      AS Section,
                       ROW_NUMBER() OVER (ORDER BY T.TDSSECTION) AS RowNum
                FROM   MTDSSECTION T
                WHERE  1 = 1
                AND    (@searchtext IS NULL OR T.TDSSECTION LIKE @searchtext OR T.SECTION LIKE @searchtext)
            )
            SELECT Id, TDSSections, Section
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_TDSSECTION_PG = @"
            WITH Paged AS (
                SELECT T.""TDSSECTIONID"" AS ""Id"",
                       T.""TDSSECTION""   AS ""TDSSections"",
                       T.""SECTION""      AS ""Section"",
                       ROW_NUMBER() OVER (ORDER BY T.""TDSSECTION"") AS ""RowNum""
                FROM   ""MTDSSECTION"" T
                WHERE  1 = 1
                AND    (:searchtext IS NULL OR T.""TDSSECTION"" LIKE :searchtext OR T.""SECTION"" LIKE :searchtext)
            )
            SELECT ""Id"", ""TDSSections"", ""Section""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_SELECTLIST_TDSSECTION_COUNT_SQL = @"
            SELECT COUNT(*)
            FROM   MTDSSECTION T
            WHERE  1 = 1
            AND    (@searchtext IS NULL OR T.TDSSECTION LIKE @searchtext OR T.SECTION LIKE @searchtext);";

        public const string GET_SELECTLIST_TDSSECTION_COUNT_PG = @"
            SELECT COUNT(*)
            FROM   ""MTDSSECTION"" T
            WHERE  1 = 1
            AND    (:searchtext IS NULL OR T.""TDSSECTION"" LIKE :searchtext OR T.""SECTION"" LIKE :searchtext);";
    }
}