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

namespace PayRollDAL.Query.PayTaxType
{
    public static class PayTaxTypeQB
    {
        public const string GET_PAYTAXTYPE = @"SELECT
    A.PAYTAXTYPEID AS PayTaxTypeId,
    A.PAYTAXTYPECODE AS PayTaxTypeCode,
    A.PAYTAXTYPENAME AS PayTaxTypeName,
    A.REMARKS AS PayTaxTypeRemarks,

    A.PERIODID AS PeriodId,
    P.PERIODCODE AS PeriodCode,
    P.PERIODNAME AS PeriodName,

    A.SOURCETYPE AS PayTaxTypeSourceType,
    A.SORTORDER AS PayTaxTypeSortOrder,
    A.STATUS AS PayTaxTypeStatus,
    A.VERSION AS PayTaxTypeVersion,

    A.CREATEDBYID AS PayTaxTypeCreatedById,
    A.CREATEDON AS PayTaxTypeCreatedOn,
    A.MODIFIEDBYID AS PayTaxTypeModifiedById,
    A.MODIFIEDON AS PayTaxTypeModifiedOn,

    -- Child: PayTaxTypeDetail (must come after all parent columns)
    D.PAYTAXTYPEDETAILID AS PayTaxTypeDetailId,
    D.SLNO AS PayTaxTypeDetailSlNo,
    D.FIELDNAME AS PayTaxTypeDetailFieldName,
    D.DISPLAYNAME AS PayTaxTypeDetailDisplayName,
    D.FIELDREMARKS AS PayTaxTypeDetailFieldRemarks,
    D.TYPE AS PayTaxTypeDetailType,
    D.DATATYPE AS PayTaxTypeDetailDataType,
    D.SIZE AS PayTaxTypeDetailSize,
    D.ISDETAIL AS PayTaxTypeDetailIsDetail,
    D.ISATTACHMENT AS PayTaxTypeDetailIsAttachment,
    D.SECTION AS PayTaxTypeDetailSection,
    D.DISPLAYORDER AS PayTaxTypeDetailDisplayOrder,
    D.ENUMVALUES AS PayTaxTypeDetailEnumValues

FROM MPAYTAXTYPE A
LEFT JOIN MPERIOD P            ON A.PERIODID = P.PERIODID
LEFT JOIN MPAYTAXTYPEDETAIL D  ON A.PAYTAXTYPEID = D.PAYTAXTYPEID
WHERE A.PAYTAXTYPEID = @paytaxtypeid;
";

        public const string UPDATE_PAYTAXTYPE_PERIOD = @"UPDATE MPAYTAXTYPE
SET
    PERIODID   = @periodid,
    SOURCETYPE = 5
WHERE
    PAYTAXTYPEID = @paytaxtypeid;";

        // ── Picklist (paged, dual-dialect) ──────────────────────────────────
        // Indexes recommended:
        //   MPAYTAXTYPE: IX_MPAYTAXTYPE_CODE (PAYTAXTYPECODE), IX_MPAYTAXTYPE_NAME (PAYTAXTYPENAME)

        public const string GET_SELECTLIST_PAYTAXTYPE_SQL = @"
WITH Paged AS (
    SELECT A.PAYTAXTYPEID   AS Id,
           A.PAYTAXTYPECODE AS Code,
           A.PAYTAXTYPENAME AS Name,
           ROW_NUMBER() OVER (ORDER BY A.PAYTAXTYPENAME) AS RowNum
    FROM   MPAYTAXTYPE A
    WHERE  1 = 1 /*FILTER*/
)
SELECT Id, Code, Name
FROM   Paged
WHERE  (@firstnumber = -1 AND @maxresult = -1)
   OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_PAYTAXTYPE_PG = @"
WITH Paged AS (
    SELECT A.PAYTAXTYPEID   AS ""Id"",
           A.PAYTAXTYPECODE AS ""Code"",
           A.PAYTAXTYPENAME AS ""Name"",
           ROW_NUMBER() OVER (ORDER BY A.PAYTAXTYPENAME) AS ""RowNum""
    FROM   MPAYTAXTYPE A
    WHERE  1 = 1 /*FILTER*/
)
SELECT ""Id"", ""Code"", ""Name""
FROM   Paged
WHERE  (:firstnumber = -1 AND :maxresult = -1)
   OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_SELECTLIST_PAYTAXTYPE_COUNT_SQL = @"
SELECT COUNT(*)
FROM   MPAYTAXTYPE A
WHERE  1 = 1 /*FILTER*/;";

        public const string GET_SELECTLIST_PAYTAXTYPE_COUNT_PG = @"
SELECT COUNT(*)
FROM   MPAYTAXTYPE A
WHERE  1 = 1 /*FILTER*/;";
    }
}