using System;
using System.Collections.Generic;
using System.Text;

namespace PayRollDAL.Query.Periodic
{
    // Ported from GB4's PeriodicQueryBuilder — scope limited to the SavePeriodic (single-record)
    // flow: the header row itself plus the two FK auto-lookups it depends on. GB4's bulk
    // "LoadPeriodic"/generate-for-all-employees flow (INSERT_INTO_PERIODIC, INSERT_PERIODICADDON,
    // LOAD_PERIODIC_FOR_MULTIPLE, etc.) is a separate operation and is not covered here.
    public class PeriodicQB
    {
        // GB4: PeriodicBLL.FindMainSubTypeId / PeriodicQueryBuilder.GET_MAIN_SUBTYPEID_FOR_EMPLOYEE
        public const string GET_MAIN_SUBTYPEID_FOR_EMPLOYEE = @"
SELECT ISNULL(MAX(WORKSUBTYPEID), -1)
FROM TMONTHlyATTENDANCE
WHERE EMPLOYEEID = @EmployeeId
      AND OUID = @OuId
      AND PAYPERIODID = @PayPeriodId;
";

        // GB4: PeriodicBLL.FindMMDetailId / PeriodicQueryBuilder.GET_MMDETAILID_FOR_EMPLOYEE.
        // GB4 takes the first row of a potentially multi-row result (IList[0]); TOP 1 mirrors that.
        public const string GET_MMDETAILID_FOR_EMPLOYEE = @"
SELECT TOP 1 a.MMDetailId
FROM TPOSTINGPOSITION a,
     MPAYPERIOD b,
     MPARTY c,
     MPARTYBRANCH d,
     MGCM e
WHERE b.PAYPERIODID = @PayPeriodId
      AND a.EMPLOYEEID = @EmployeeId
      AND a.PARTYID = c.PARTYID
      AND a.PARTYBRANCHID = d.PARTYBRANCHID
      AND a.WORKTYPEID = e.GCMID
      AND e.GCMTYPEID = -1399999995
      AND c.PARTYCODE = @PartyCode
      AND d.PARTYBRANCHCODE = @PartyBranchCode
      AND e.GCMCODE = @WorkTypeCode
      AND a.FROMDATE <= b.TODATE
      AND a.TODATE >= b.FROMDATE;
";

        // Column list matches Periodic.hbm.xml's mapped properties for TPERIODIC exactly.
        // PeriodicAddon (the dynamic addition/deduction child-row bag) is NOT ported here — see
        // the note in PeriodicBLL.Save.
        public const string INSERT_PERIODIC = @"
INSERT INTO TPERIODIC
(PERIODICID, PAYPERIODID, APPLICABLE, OUID, EMPLOYEEID, DAGROUPID, PAYCONFIGURATIONID,
 STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PAYGROUPID,
 MMDETAILID, MAINSUBTYPEID, MONTHLYATTENDANCEID)
VALUES
(@PeriodicId, @PayPeriodId, @PeriodicApplicable, @OUId, @EmployeeId, @DAGroupId, @PayConfigurationId,
 @PeriodicStatus, @PeriodicVersion, @PeriodicCreatedById, @PeriodicCreatedOn, @PeriodicModifiedById, @PeriodicModifiedOn, @PayGroupId,
 @MMDetailId, @MainSubTypeId, @MonthlyAttendanceId);
";

        public const string UPDATE_PERIODIC = @"
UPDATE TPERIODIC SET
    PAYPERIODID = @PayPeriodId,
    APPLICABLE = @PeriodicApplicable,
    OUID = @OUId,
    EMPLOYEEID = @EmployeeId,
    DAGROUPID = @DAGroupId,
    PAYCONFIGURATIONID = @PayConfigurationId,
    STATUS = @PeriodicStatus,
    VERSION = @PeriodicVersion,
    MODIFIEDBYID = @PeriodicModifiedById,
    MODIFIEDON = @PeriodicModifiedOn,
    PAYGROUPID = @PayGroupId,
    MMDETAILID = @MMDetailId,
    MAINSUBTYPEID = @MainSubTypeId,
    MONTHLYATTENDANCEID = @MonthlyAttendanceId
WHERE PERIODICID = @PeriodicId;
";
        // Every filter parameter is nullable: NULL means "not supplied — no filter on this
        // field"; any non-null value (including -1, a real "NONE" FK value in this schema)
        // restricts the result to that exact match.
        public const string GET_PERIODIC_LIST = @"
    SELECT
        p.PERIODICID                AS PeriodicId,
        p.PAYPERIODID               AS PayPeriodId,
        pp.PAYPERIODCODE            AS PayPeriodCode,

        p.APPLICABLE                AS PeriodicApplicable,
        CASE
            WHEN p.APPLICABLE = 5 THEN 'EMPLOYEELEVEL'
            WHEN p.APPLICABLE = 4 THEN 'DAGROUPLEVEL'
            WHEN p.APPLICABLE = 3 THEN 'PAYGROUPLEVEL'
            WHEN p.APPLICABLE = 2 THEN 'PAYCONFIGLEVEL'
            WHEN p.APPLICABLE = 1 THEN 'OULEVEL'
            ELSE 'OVERALL'
        END                          AS PeriodicApplicableValue,

        p.PAYGROUPID                AS PayGroupId,
        pg.PAYGROUPCODE              AS PayGroupCode,
        pg.PAYGROUPNAME              AS PayGroupName,

        p.OUID                      AS OUId,
        ou.ORGANIZATIONUNITCODE      AS OUCode,
        ou.ORGANIZATIONUNITNAME      AS OUName,

        p.EMPLOYEEID                AS EmployeeId,
        e.EMPLOYEECODE               AS EmployeeCode,
        e.EMPLOYEENAME               AS EmployeeName,

        p.DAGROUPID                 AS DAGroupId,
        dag.DAPOINTGROUPCODE         AS DAGroupCode,
        dag.DAPOINTGROUPNAME         AS DAGroupName,

        p.PAYCONFIGURATIONID        AS PayConfigurationId,
        pc.PAYCONFIGURATIONCODE      AS PayConfigurationCode,
        pc.PAYCONFIGURATIONNAME      AS PayConfigurationName,

        p.STATUS                    AS PeriodicStatus,
        p.VERSION                   AS PeriodicVersion,

        p.CREATEDBYID                AS PeriodicCreatedById,
        p.CREATEDON                  AS PeriodicCreatedOn,
        p.MODIFIEDBYID                AS PeriodicModifiedById,
        p.MODIFIEDON                  AS PeriodicModifiedOn
    FROM TPERIODIC p
    LEFT JOIN MPAYPERIOD         pp  ON p.PAYPERIODID         = pp.PAYPERIODID
    LEFT JOIN MPAYGROUP          pg  ON p.PAYGROUPID          = pg.PAYGROUPID
    LEFT JOIN MORGANIZATIONUNIT  ou  ON p.OUID                = ou.OUID
    LEFT JOIN MEMPLOYEE          e   ON p.EMPLOYEEID          = e.EMPLOYEEID
    LEFT JOIN MDAPOINTGROUP      dag ON p.DAGROUPID           = dag.DAPOINTGROUPID
    LEFT JOIN MPAYCONFIGURATION  pc  ON p.PAYCONFIGURATIONID  = pc.PAYCONFIGURATIONID
    WHERE (@payperiodid IS NULL OR p.PAYPERIODID = @payperiodid)
      AND (@applicable IS NULL OR p.APPLICABLE = @applicable)
      AND (@ouid IS NULL OR p.OUID = @ouid)
      AND (@paygroupid IS NULL OR p.PAYGROUPID = @paygroupid)
      AND (@payconfigurationid IS NULL OR p.PAYCONFIGURATIONID = @payconfigurationid)
      AND (@dagroupid IS NULL OR p.DAGROUPID = @dagroupid)
      AND (@employeeid IS NULL OR p.EMPLOYEEID = @employeeid)
    ORDER BY p.PERIODICID;";
    

        // GB4: PeriodicBLL.PeriodicLoad reads PayPeriod.ToDate via Generic.Get<PayPeriod>(...).
        // Single scalar column lookup — kept local rather than routed through a cross-module BLL.
        public const string GET_PAYPERIOD_TODATE = @"
SELECT TODATE
FROM MPAYPERIOD
WHERE PAYPERIODID = @PayPeriodId;
";

        // GB4: PeriodicQueryBuilder.SELECT_PERIODIC_VALUES — addon fields configured as
        // Nature=1 (variable) / ApplicableType=5 (employee level). These drive the dynamic
        // column list spliced into BuildLoadPeriodic above.
        public const string GET_PERIODIC_ADDON_FIELD_DEFAULTS = @"
SELECT
    A.FIELDCODE     AS FieldCode,
    A.DEFAULTVALUE  AS DefaultValue
FROM MADDITIONDEDUCTION A
WHERE A.NATURE = 1
  AND A.APPLICABLETYPE = 5;
";

        // ── LoadPeriodic (bulk generation) ──────────────────────────────────────────────
        //
        // GB4: PeriodicBLL.PeriodicLoad / PeriodicQueryBuilder.{INSERT_INTO_PERIODIC,
        // INSERT_PERIODICADDON_TEMP, INSERT_PERIODICADDON}. Despite the GET verb, this seeds one
        // TPERIODIC row per active/eligible employee for a pay period, plus one TPERIODICADDON
        // row per employee carrying the configured addon-field default values.
        //
        // GB4 used a THIRD statement (INSERT_PERIODICADDON_TEMP) that inserted the same computed
        // PERIODICID set into a table literally named MTEMP — a shared, non-session-scoped scratch
        // table. Two concurrent LoadPeriodic calls (even for different pay periods/tenants) could
        // stomp on each other's rows in MTEMP. That table serves no purpose here other than to
        // avoid recomputing the identical employee-eligibility subquery twice (once for the
        // TPERIODIC insert, once for the TPERIODICADDON insert) — the FROM/WHERE shape is byte-for-
        // byte identical across all three GB4 queries.
        //
        // GB5 fix: compute the eligible-employee set ONCE into a local table variable (@Eligible),
        // scoped to this single batch/connection — never shared across calls, never persisted.
        // Both INSERT statements below then simply read from @Eligible. MTEMP is eliminated
        // entirely; no cross-request concurrency hazard remains.
        //
        // GB4's INSERT_PERIODICADDON also LEFT JOINed a "previous" derived table (prior calendar
        // month's TPERIODIC/TPERIODICADDON/MPAYPERIOD) but never referenced any column from it in
        // the projected SELECT list — FieldName/FieldValue come purely from AdditionDeduction's
        // DefaultValue (built in BLL). That join was dead weight from GB4's copy-paste history and
        // is intentionally NOT ported.
        //
        // :fieldname / :fieldvalue equivalents (@FieldNameList / @FieldValueList) are built in BLL
        // from MADDITIONDEDUCTION.FIELDCODE — validated as safe SQL identifiers (see
        // PeriodicBLL.PeriodicLoad) before being spliced into the column/VALUES lists, since column
        // names cannot be parameterized. All literal values (OUId, PayPeriodId, PayConfigurationId,
        // AutoId, UserId, ToDate, TenantId) ARE parameterized.
        public static string BuildLoadPeriodic(string fieldNameList, string fieldValueList) => $@"
DECLARE @Eligible TABLE (PeriodicId INT NOT NULL, EmployeeId INT NOT NULL);

INSERT INTO @Eligible (PeriodicId, EmployeeId)
SELECT
    (@AutoId + ROW_NUMBER() OVER (ORDER BY emp.EmployeeCode)) AS PeriodicId,
    emp.EMPLOYEEID                                            AS EmployeeId
FROM dbo.Fnemployee(@ToDate) emp
LEFT OUTER JOIN
    (SELECT a.*
     FROM TPAYREVISION a,
          (SELECT EMPLOYEEID, MAX(PAYREVISIONID) PAYREVISIONID
           FROM TPAYREVISION
           WHERE PAYCONFIGURATIONID = @PayConfigurationId
             AND OUID = @OUId AND NATURE = 0
             AND CONVERT(date, EFFECTIVETO)   >= (SELECT CONVERT(date, FROMDATE) FROM MPAYPERIOD WHERE PAYPERIODID = @PayPeriodId)
             AND CONVERT(date, EFFECTIVEFROM) <= (SELECT CONVERT(date, FROMDATE) FROM MPAYPERIOD WHERE PAYPERIODID = @PayPeriodId)
           GROUP BY EMPLOYEEID) b
     WHERE a.PAYREVISIONID = b.PAYREVISIONID) payrevision
    ON (payrevision.EMPLOYEEID = emp.EMPLOYEEID AND payrevision.Nature = 0)
LEFT OUTER JOIN
    (SELECT EmployeeId FROM TPERIODIC WHERE PAYPERIODID = @PayPeriodId AND APPLICABLE = 5) periodic
    ON (periodic.EMPLOYEEID = emp.EMPLOYEEID)
    , MPAYPERIOD payperiod
WHERE emp.PAYCONFIGURATIONID = @PayConfigurationId
  AND payperiod.PAYPERIODID  = @PayPeriodId
  AND emp.WORKOUID            = @OUId
  AND emp.TENANTID             = @TenantId
  AND emp.EmployeeId <> -1
  AND (emp.employeestatus IN (0)
       OR ((CONVERT(date, emp.DATEOFRESIGNATION) >= payperiod.FROMDATE AND CONVERT(date, emp.DATEOFRESIGNATION) <= payperiod.TODATE)
            OR CONVERT(date, emp.DATEOFRESIGNATION) >= payperiod.TODATE))
  AND emp.status IN (1)
  AND periodic.EMPLOYEEID IS NULL
  AND emp.iscontract = 1;

INSERT INTO TPERIODIC
(PERIODICID, PAYPERIODID, APPLICABLE, OUID, EMPLOYEEID, DAGROUPID, PAYCONFIGURATIONID,
 STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PAYGROUPID)
SELECT
    e.PeriodicId,
    @PayPeriodId,
    5,
    @OUId,
    e.EmployeeId,
    -1,
    -1,
    0,
    1,
    @UserId,
    GETDATE(),
    @UserId,
    GETDATE(),
    -1
FROM @Eligible e;

INSERT INTO TPERIODICADDON
(PERIODICID,
 {fieldNameList}
)
SELECT
    e.PeriodicId,
    {fieldValueList}
FROM @Eligible e;
";

        // ── GetPeriodic (Periodic.svc/GetPeriodic) ──────────────────────────────────────
        //
        // GB4: PeriodicDAL.GetPeriodicDetails. Mandatory-PayConfig check + IsPartialProcessRequired
        // branch live in BLL; these two constants cover the two DAL-level query shapes.
        //
        // GB4 read PayConfiguration.IsPartialProcessRequired via a separate NHibernate Get<>() call.
        // GB5 folds that into one round trip alongside the mandatory-PayConfig existence check.
        public const string GET_PAYCONFIG_ISPARTIALPROCESSREQUIRED = @"
SELECT ISPARTIALPROCESSREQUIRED
FROM MPAYCONFIGURATION
WHERE PAYCONFIGURATIONID = @PayConfigurationId;
";

        // GB4: PeriodicQueryBuilder.GET_PERODIC_DETAIL_SORTORDER + GetPeriodicandaddonCriteria.
        // "Standard" path — TPERIODIC-table-based, used when PayConfiguration.IsPartialProcessRequired <> 0.
        // {0} = ORDER BY clause (5 possible outcomes — chosen in BLL from the 4 sort flags, GB4 parity).
        // {1} = additional criteria filter fragment (built in BLL from CriteriaDTO, parameterized).
        public static string BuildGetPeriodicStandard(string orderByClause, string criteriaFilter) => $@"
SELECT
    periodic.PERIODICID          AS PeriodicId,
    periodic.PAYPERIODID         AS PayPeriodId,
    pp.PAYPERIODCODE             AS PayPeriodCode,
    periodic.APPLICABLE          AS PeriodicApplicable,
    ISNULL(periodic.PAYGROUPID, -1)          AS PayGroupId,
    ISNULL(pg.PAYGROUPCODE, 'NONE')          AS PayGroupCode,
    ISNULL(pg.PAYGROUPNAME, 'NONE')          AS PayGroupName,
    ou.OUID                        AS OUId,
    ou.ORGANIZATIONUNITCODE        AS OUCode,
    ou.ORGANIZATIONUNITNAME        AS OUName,
    emp.EMPLOYEEID                  AS EmployeeId,
    emp.EMPLOYEECODE                AS EmployeeCode,
    emp.EMPLOYEENAME                AS EmployeeName,
    ISNULL(dag.DAPOINTGROUPID, -1)  AS DAGroupId,
    ISNULL(dag.DAPOINTGROUPCODE, 'NONE') AS DAGroupCode,
    ISNULL(dag.DAPOINTGROUPNAME, 'NONE') AS DAGroupName,
    ISNULL(pc.PAYCONFIGURATIONID, -1)          AS PayConfigurationId,
    ISNULL(pc.PAYCONFIGURATIONCODE, 'NONE')    AS PayConfigurationCode,
    ISNULL(pc.PAYCONFIGURATIONCODE, 'NONE')    AS PayConfigurationName,
    periodic.STATUS               AS PeriodicStatus,
    periodic.VERSION              AS PeriodicVersion,
    periodic.CREATEDBYID          AS PeriodicCreatedById,
    periodic.CREATEDON            AS PeriodicCreatedOn,
    periodic.MODIFIEDBYID         AS PeriodicModifiedById,
    periodic.MODIFIEDON           AS PeriodicModifiedOn,
    periodic.MMDETAILID           AS MMDetailId,
    ISNULL(periodic.MAINSUBTYPEID, -1)         AS MainSubTypeId,
    periodic.MONTHLYATTENDANCEID  AS MonthlyAttendanceId,
    periodic.TENANTID              AS TenantId
FROM TPERIODIC periodic
LEFT JOIN MPAYPERIOD        pp  ON periodic.PAYPERIODID        = pp.PAYPERIODID
LEFT JOIN MPAYGROUP         pg  ON periodic.PAYGROUPID         = pg.PAYGROUPID
LEFT JOIN MORGANIZATIONUNIT ou  ON periodic.OUID               = ou.OUID
LEFT JOIN MEMPLOYEE         emp ON periodic.EMPLOYEEID         = emp.EMPLOYEEID
LEFT JOIN MDAPOINTGROUP     dag ON periodic.DAGROUPID          = dag.DAPOINTGROUPID
LEFT JOIN MPAYCONFIGURATION pc  ON periodic.PAYCONFIGURATIONID = pc.PAYCONFIGURATIONID
WHERE periodic.PAYPERIODID = @PayPeriodId
  AND periodic.TENANTID = @TenantId
  {criteriaFilter}
{orderByClause}
OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;
";

        // GB4: PeriodicQueryBuilder.GET_PERODIC_DETAIL_SORTORDER_COUNT + GetPeriodicandaddonCriterias.
        // NOTE (GB4 parity, deliberately preserved): GB4's count-path criteria helper
        // (GetPeriodicandaddonCriterias) maps only OUId/Applicable — a narrower filter set than the
        // data path's helper (GetPeriodicandaddonCriteria, 15 fields). This is a genuine GB4
        // inconsistency (Total can reflect a different filter set than the returned rows) — it is
        // called out here, not silently "fixed", since preserving GB4 business behavior was
        // specified. {0} = the narrow (OUId/Applicable-only) criteria filter fragment.
        public static string BuildGetPeriodicStandardCount(string criteriaFilter) => $@"
SELECT COUNT(*)
FROM TPERIODIC periodic
WHERE periodic.PAYPERIODID = @PayPeriodId
  AND periodic.TENANTID = @TenantId
  {criteriaFilter};
";

        // GB4: PeriodicQueryBuilder.LOAD_PERIODIC_FOR_MULTIPLE_NEW + GetPeriodicandaddonCriteriaNew.
        // "New" path — TPOSTINGPOSITION-driven multi-posting-position variant, used when
        // PayConfiguration.IsPartialProcessRequired == 0. Ported verbatim (no -1 wildcard shortcut
        // added — GB4 has none; :payperiodid is a strict equality filter in both the LEFT JOIN and
        // the WHERE clause, same as GB4).
        // {0} = additional criteria filter fragment (built in BLL from CriteriaDTO, parameterized).
        public static string BuildGetPeriodicNew(string criteriaFilter) => $@"
SELECT Isnull(B.PERIODICID, 0)                   AS PeriodicId,
       e.PayPeriodId                             AS PayPeriodId,
       E.PayPeriodCode                           AS PayPeriodCode,
       Isnull(B.APPLICABLE, 5)                   AS PeriodicApplicable,
       Isnull(PAYG.PAYGROUPID, -1)               AS PayGroupId,
       Isnull(PAYG.PAYGROUPCODE, 'NONE')         AS PayGroupCode,
       Isnull(PAYG.PAYGROUPNAME, 'NONE')         AS PayGroupName,
       OU.OUID                                   AS OUId,
       OU.ORGANIZATIONUNITCODE                   AS OUCode,
       OU.ORGANIZATIONUNITNAME                   AS OUName,
       EMP.EMPLOYEEID                            AS EmployeeId,
       EMP.EMPLOYEECODE                          AS EmployeeCode,
       EMP.EMPLOYEENAME                          AS EmployeeName,
       Isnull(DA.DAPOINTGROUPID, -1)             AS DAGroupId,
       Isnull(DA.DAPOINTGROUPCODE, 'NONE')       AS DAGroupCode,
       Isnull(DA.DAPOINTGROUPNAME, 'NONE')       AS DAGroupName,
       Isnull(CONF.PAYCONFIGURATIONID, -1)       AS PayConfigurationId,
       Isnull(CONF.PAYCONFIGURATIONCODE, 'NONE') AS PayConfigurationCode,
       Isnull(CONF.PAYCONFIGURATIONCODE, 'NONE') AS PayConfigurationName,
       Isnull(B.STATUS, 1)                       AS PeriodicStatus,
       Isnull(B.VERSION, 1)                      AS PeriodicVersion,
       Isnull(B.CREATEDBYID, -1)                 AS PeriodicCreatedById,
       Isnull(B.CREATEDON, Getdate())            AS PeriodicCreatedOn,
       Isnull(B.MODIFIEDBYID, -1)                AS PeriodicModifiedById,
       Isnull(B.MODIFIEDON, Getdate())           AS PeriodicModifiedOn,
       ''                                        AS PeriodicApplicableValue,
       postposition.MMDETAILID                   AS MMDetailId,
       Isnull(MAINSUB.GCMID, -1)                 AS MainSubTypeId,
       Isnull(MAINSUB.GCMCODE, 'NONE')           AS MainSubTypeCode,
       Isnull(MAINSUB.GCMNAME, 'NONE')           AS MainSubTypeName,
       Isnull(B.MONTHLYATTENDANCEID, -1)         AS MonthlyAttendanceId,
       PR.PARTYCODE                              AS PartyCode,
       PR.PARTYNAME                              AS PartyName,
       PB.PARTYBRANCHCODE                        AS PartyBranchCode,
       PB.PARTYBRANCHNAME                        AS PartyBranchName,
       WORKTYPE.GCMCODE                          AS WorkTypeCode,
       ''                                        AS WorkSubTypeCode,
       WORKTYPE.GCMID                            AS WorkExcelTypeId,
       WORKTYPE.GCMCODE                          AS WorkExcelTypeCode,
       WORKTYPE.GCMNAME                          AS WorkExcelTypeName,
       g.DOCUMENTNUMBER                                                                     AS OrderNumber,
       Replace(CONVERT(VARCHAR(11), g.DOCUMENTDATE , 106), ' ', '-')                        AS OrderDate,
       Isnull(wagegroup.WAGERATE, 0) as WageRate
FROM   TPOSTINGPOSITION postposition
       LEFT OUTER JOIN TPERIODIC B
         ON( postposition.EMPLOYEEID = b.EMPLOYEEID
             AND postposition.MMDETAILID = B.MMDETAILID
             AND postposition.OUID = B.OUID
             AND B.PAYPERIODID = @PayPeriodId )
       LEFT OUTER JOIN MPAYGROUP PAYG
         ON( B.PAYGROUPID = PAYG.PAYGROUPID )
       LEFT OUTER JOIN MDAPOINTGROUP da
         ON( B.DAGROUPID = DA.DAPOINTGROUPID )
       LEFT OUTER JOIN MGCM MAINSUB
         ON( B.MAINSUBTYPEID = MAINSUB.GCMID )
       LEFT OUTER JOIN MPAYCONFIGURATION CONF
         ON( B.PAYCONFIGURATIONID = CONF.PAYCONFIGURATIONID )
       LEFT OUTER JOIN MPAYPERIOD e
         ON( 1 = 1 )
       OUTER APPLY (SELECT top(1) c.WAGERULEVERSIONID,
                           c.EFFECTIVEFROM,
                           c.EFFECTIVETO,
                           c.WAGERATE,
                           C.WORKSUBTYPEID
                    FROM   TMMDETAIL a,
                           VWAGERULEANDDETAIL c,
                           MSERVICERULE d
                    WHERE  a.DOCUMENTDETAILID = postposition.MMDETAILID
                           AND a.SERVICERULEID = d.PARENTSERVICERULEID
                           AND c.SERVICERULEID = d.SERVICERULEID
                           AND c.EMPLOYEEID = postposition.EMPLOYEEID
                           AND c.WORKTYPEID = postposition.WORKTYPEID
                           AND ( e.FROMDATE BETWEEN c.EFFECTIVEFROM AND c.EFFECTIVETO
                                  OR e.TODATE BETWEEN c.EFFECTIVEFROM AND c.EFFECTIVETO ))wagegroup,
       MGCM WORKTYPE,
       MEMPLOYEE EMP,
       TMMDETAIL c,
       TMMHEAD D,
       MPARTYBRANCH PB,
       MPARTY PR,
       MORGANIZATIONUNIT ou,
       TMMHEAD g,
       MITEM cat
WHERE  1 = 1
       AND postposition.MMDETAILID = c.DOCUMENTDETAILID
       AND C.DOCUMENTID = D.DOCUMENTID
       AND D.PARTYBRANCHID = PB.PARTYBRANCHID
       AND D.PARTYID = PR.PARTYID
       AND postposition.EMPLOYEEID = EMP.EMPLOYEEID
       AND postposition.WORKTYPEID = WORKTYPE.GCMID
       AND postposition.FROMDATE <= e.todate
       AND postposition.todate >= e.fromdate
       AND c.ITEMID = cat.ITEMID
       AND e.PAYPERIODID = @PayPeriodId
       AND Isnull(wagegroup.WAGERULEVERSIONID, -1) <> -1
       AND postposition.POSTINGSTATUS NOT In(3)
       AND c.DOCUMENTID = g.DOCUMENTID
       AND EMP.TENANTID = @TenantId
       {criteriaFilter}
ORDER BY EMP.EMPLOYEECODE
OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;
";

        // NOTE (GB4 parity): GB4's PeriodicDAL.GetPeriodicDetails only takes the "New"
        // (IsPartialProcessRequired==0) branch when Count==false — the Count==true path ALWAYS
        // uses the standard TPERIODIC-based count query above (GET_PERODIC_DETAIL_SORTORDER_COUNT),
        // regardless of IsPartialProcessRequired. There is no GB4 "New"-path count query to port —
        // BuildGetPeriodicStandardCount is reused for both branches' Total, matching GB4 exactly.

        // ── Periodic.svc/Periodic/Service/Based/Export ──────────────────────────────────
        //
        // GB4: DumpPerodicServiceBased.PeriodicServiceBasedExport. GB4 ran
        // LOAD_PERIODIC_FOR_MULTIPLE_NEW into a local SQL Server temp table
        // (#tempperiodicservicebased) and then issued PERIODDIC_SERVICE_BASED_EXPORT as a second
        // SELECT ("a.* ... order by b.SortOrder, a.PeriodicId", a.WorkExcelTypeId=b.GCMID) purely
        // to re-sort the already-materialized row set by the work-type's MGCM.SORTORDER.
        //
        // GB5 does not need the temp-table round trip to reproduce that ordering: the same
        // MGCM join and ORDER BY are added directly onto the identical BuildGetPeriodicNew
        // row shape/joins (no @FirstNumber/@MaxResult paging, no ORDER BY EMP.EMPLOYEECODE —
        // this export always returns every matching row, sorted by work-type SortOrder then
        // PeriodicId, exactly like GB4's second SELECT did over the temp table). This keeps the
        // whole export on ONE query/ONE connection with no multi-statement temp-table sequencing
        // and no connection-pinning concern.
        //
        // {0} = additional criteria filter fragment (same shape/order as BuildGetPeriodicNew).
        public static string BuildPeriodicServiceBasedExport(string criteriaFilter) => $@"
SELECT Isnull(B.PERIODICID, 0)                   AS PeriodicId,
       e.PayPeriodId                             AS PayPeriodId,
       E.PayPeriodCode                           AS PayPeriodCode,
       Isnull(B.APPLICABLE, 5)                   AS PeriodicApplicable,
       Isnull(PAYG.PAYGROUPID, -1)               AS PayGroupId,
       Isnull(PAYG.PAYGROUPCODE, 'NONE')         AS PayGroupCode,
       Isnull(PAYG.PAYGROUPNAME, 'NONE')         AS PayGroupName,
       OU.OUID                                   AS OUId,
       OU.ORGANIZATIONUNITCODE                   AS OUCode,
       OU.ORGANIZATIONUNITNAME                   AS OUName,
       EMP.EMPLOYEEID                            AS EmployeeId,
       EMP.EMPLOYEECODE                          AS EmployeeCode,
       EMP.EMPLOYEENAME                          AS EmployeeName,
       Isnull(DA.DAPOINTGROUPID, -1)             AS DAGroupId,
       Isnull(DA.DAPOINTGROUPCODE, 'NONE')       AS DAGroupCode,
       Isnull(DA.DAPOINTGROUPNAME, 'NONE')       AS DAGroupName,
       Isnull(CONF.PAYCONFIGURATIONID, -1)       AS PayConfigurationId,
       Isnull(CONF.PAYCONFIGURATIONCODE, 'NONE') AS PayConfigurationCode,
       Isnull(CONF.PAYCONFIGURATIONCODE, 'NONE') AS PayConfigurationName,
       Isnull(B.STATUS, 1)                       AS PeriodicStatus,
       Isnull(B.VERSION, 1)                      AS PeriodicVersion,
       Isnull(B.CREATEDBYID, -1)                 AS PeriodicCreatedById,
       Isnull(B.CREATEDON, Getdate())            AS PeriodicCreatedOn,
       Isnull(B.MODIFIEDBYID, -1)                AS PeriodicModifiedById,
       Isnull(B.MODIFIEDON, Getdate())           AS PeriodicModifiedOn,
       ''                                        AS PeriodicApplicableValue,
       postposition.MMDETAILID                   AS MMDetailId,
       Isnull(MAINSUB.GCMID, -1)                 AS MainSubTypeId,
       Isnull(MAINSUB.GCMCODE, 'NONE')           AS MainSubTypeCode,
       Isnull(MAINSUB.GCMNAME, 'NONE')           AS MainSubTypeName,
       Isnull(B.MONTHLYATTENDANCEID, -1)         AS MonthlyAttendanceId,
       PR.PARTYCODE                              AS PartyCode,
       PR.PARTYNAME                              AS PartyName,
       PB.PARTYBRANCHCODE                        AS PartyBranchCode,
       PB.PARTYBRANCHNAME                        AS PartyBranchName,
       WORKTYPE.GCMCODE                          AS WorkTypeCode,
       ''                                        AS WorkSubTypeCode,
       WORKTYPE.GCMID                            AS WorkExcelTypeId,
       WORKTYPE.GCMCODE                          AS WorkExcelTypeCode,
       WORKTYPE.GCMNAME                          AS WorkExcelTypeName,
       g.DOCUMENTNUMBER                                                                     AS OrderNumber,
       Replace(CONVERT(VARCHAR(11), g.DOCUMENTDATE , 106), ' ', '-')                        AS OrderDate,
       Isnull(wagegroup.WAGERATE, 0) as WageRate
FROM   TPOSTINGPOSITION postposition
       LEFT OUTER JOIN TPERIODIC B
         ON( postposition.EMPLOYEEID = b.EMPLOYEEID
             AND postposition.MMDETAILID = B.MMDETAILID
             AND postposition.OUID = B.OUID
             AND B.PAYPERIODID = @PayPeriodId )
       LEFT OUTER JOIN MPAYGROUP PAYG
         ON( B.PAYGROUPID = PAYG.PAYGROUPID )
       LEFT OUTER JOIN MDAPOINTGROUP da
         ON( B.DAGROUPID = DA.DAPOINTGROUPID )
       LEFT OUTER JOIN MGCM MAINSUB
         ON( B.MAINSUBTYPEID = MAINSUB.GCMID )
       LEFT OUTER JOIN MPAYCONFIGURATION CONF
         ON( B.PAYCONFIGURATIONID = CONF.PAYCONFIGURATIONID )
       LEFT OUTER JOIN MPAYPERIOD e
         ON( 1 = 1 )
       OUTER APPLY (SELECT top(1) c.WAGERULEVERSIONID,
                           c.EFFECTIVEFROM,
                           c.EFFECTIVETO,
                           c.WAGERATE,
                           C.WORKSUBTYPEID
                    FROM   TMMDETAIL a,
                           VWAGERULEANDDETAIL c,
                           MSERVICERULE d
                    WHERE  a.DOCUMENTDETAILID = postposition.MMDETAILID
                           AND a.SERVICERULEID = d.PARENTSERVICERULEID
                           AND c.SERVICERULEID = d.SERVICERULEID
                           AND c.EMPLOYEEID = postposition.EMPLOYEEID
                           AND c.WORKTYPEID = postposition.WORKTYPEID
                           AND ( e.FROMDATE BETWEEN c.EFFECTIVEFROM AND c.EFFECTIVETO
                                  OR e.TODATE BETWEEN c.EFFECTIVEFROM AND c.EFFECTIVETO ))wagegroup,
       MGCM WORKTYPE,
       MEMPLOYEE EMP,
       TMMDETAIL c,
       TMMHEAD D,
       MPARTYBRANCH PB,
       MPARTY PR,
       MORGANIZATIONUNIT ou,
       TMMHEAD g,
       MITEM cat
WHERE  1 = 1
       AND postposition.MMDETAILID = c.DOCUMENTDETAILID
       AND C.DOCUMENTID = D.DOCUMENTID
       AND D.PARTYBRANCHID = PB.PARTYBRANCHID
       AND D.PARTYID = PR.PARTYID
       AND postposition.EMPLOYEEID = EMP.EMPLOYEEID
       AND postposition.WORKTYPEID = WORKTYPE.GCMID
       AND postposition.FROMDATE <= e.todate
       AND postposition.todate >= e.fromdate
       AND c.ITEMID = cat.ITEMID
       AND e.PAYPERIODID = @PayPeriodId
       AND Isnull(wagegroup.WAGERULEVERSIONID, -1) <> -1
       AND postposition.POSTINGSTATUS NOT In(3)
       AND c.DOCUMENTID = g.DOCUMENTID
       AND EMP.TENANTID = @TenantId
       {criteriaFilter}
ORDER BY WORKTYPE.SORTORDER, Isnull(B.PERIODICID, 0);
";

        // GB4: DumpPerodicServiceBased.PeriodicServiceBasedExport — AddOnFields lookup
        // (GenericLazy.GetCondition<AddOnField>("Entity.Id", -1399999776, ..., Equal)), filtered
        // to IsMandatory fields only (MADDONFIELDS.ISMANDATORY: 0=YES/mandatory, 1=NO — GB4's
        // `Poco.IsMandatory == 0` filter, despite its "Only Mandatory Fields will come" comment
        // reading oddly against the task's "non-mandatory" framing, actually selects the
        // MANDATORY addon fields under this 0=YES/1=NO encoding; ordered by SORTORDER exactly as
        // GB4 did with AddOnFields.OrderBy(o => o.SortOrder)).
        // -1399999776 is EntityConstant's PERIODICADDON entity id (TPERIODICADDON's owning entity).
        // NOTE: MADDONFIELDS has no TENANTID column (verified against the live DDL,
        // DB/Migrations/20260824_FullBaseSchema_SqlServer) — it is framework-level field-definition
        // metadata scoped only by ENTITYID, matching GB4 (AddOnField has no per-tenant filter either).
        public const string GET_PERIODICADDON_FIELDS = @"
SELECT AF.FIELDNAME AS FieldName
FROM   MADDONFIELDS AF
WHERE  AF.ENTITYID = @EntityId
       AND AF.ISMANDATORY = 0
ORDER BY AF.SORTORDER;
";

        // GB4: PeriodicQueryBuilder.PERIODDIC_SERVICE_BASED_EXPORT_ADDON — GB4's temp-table row
        // (alias "a") carried WorkExcelTypeId = the POSTING POSITION's work type (WORKTYPE.GCMID
        // in the header query above, NOT TPERIODIC.MAINSUBTYPEID) — so the addon query's
        // "a.WorkExcelTypeId=c.GCMID" join is reproduced here via the same
        // TPOSTINGPOSITION/MEMPLOYEE/TMMDETAIL/TMMHEAD chain used by the header query, joined back
        // to TPERIODIC by (EMPLOYEEID, MMDETAILID, OUID, PAYPERIODID) exactly as the header
        // query's TPERIODIC LEFT JOIN does. Only mandatory-flagged addon columns (per
        // GET_PERIODICADDON_FIELDS) are selected — {addonColumns} is a comma-prefixed, validated
        // identifier list (PeriodicBLL.IsSafeIdentifier guard applied before this is spliced in;
        // column names cannot be parameterized in Dapper).
        public static string BuildPeriodicServiceBasedExportAddon(string addonColumns) => $@"
SELECT b.PERIODICID AS PeriodicId
       {addonColumns}
FROM   TPERIODICADDON b
       JOIN TPERIODIC p ON b.PERIODICID = p.PERIODICID
       JOIN TPOSTINGPOSITION postposition
         ON postposition.EMPLOYEEID = p.EMPLOYEEID
        AND postposition.MMDETAILID = p.MMDETAILID
        AND postposition.OUID       = p.OUID
       JOIN MGCM c ON postposition.WORKTYPEID = c.GCMID
       JOIN MEMPLOYEE emp ON p.EMPLOYEEID = emp.EMPLOYEEID
WHERE  p.PAYPERIODID = @PayPeriodId
       AND emp.TENANTID = @TenantId;
";
    }
}