namespace PayRollDAL.Query.TLeave
{
    public static class TLeaveQB
    {
        public const string SAVE_TLEAVE = @"
            INSERT INTO TLEAVE
            (
                LEAVEID,
                OUID,
                PERIODID,
                BIZTRANSACTIONTYPEID,
                LEAVENUMBER,
                LEAVEDATE,
                REFERENCENUMBER,
                REFERENCEDATE,
                REMARKS,
                NUMBEROFDAYS,
                TYPEOFLEAVE,
                REASONID,
                STATUS,
                VERSION,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @TLeaveId,
                @OUId,
                @PeriodId,
                @BizTransactionTypeId,
                @TLeaveLeaveNumber,
                @TLeaveLeaveDate,
                @TLeaveReferenceNumber,
                @TLeaveReferenceDate,
                @TLeaveRemarks,
                @TLeaveNumberOfDays,
                @TLeaveTypeOfLeave,
                @ReasonId,
                @TLeaveStatus,
                @TLeaveVersion,
                @UserId,
                @TLeaveCreatedOn,
                @UserId,
                @TLeaveModifiedOn
            )";

        public const string UPDATE_TLEAVE = @"
            UPDATE TLEAVE
            SET
                OUID                  = @OUId,
                PERIODID              = @PeriodId,
                BIZTRANSACTIONTYPEID  = @BizTransactionTypeId,
                LEAVENUMBER           = @TLeaveLeaveNumber,
                LEAVEDATE             = @TLeaveLeaveDate,
                REFERENCENUMBER       = @TLeaveReferenceNumber,
                REFERENCEDATE         = @TLeaveReferenceDate,
                REMARKS               = @TLeaveRemarks,
                NUMBEROFDAYS          = @TLeaveNumberOfDays,
                TYPEOFLEAVE           = @TLeaveTypeOfLeave,
                REASONID              = @ReasonId,
                STATUS                = @TLeaveStatus,
                VERSION               = VERSION + 1,
                MODIFIEDBYID          = @UserId,
                MODIFIEDON            = @TLeaveModifiedOn
            WHERE
                LEAVEID               = @TLeaveId";

        public const string SAVE_TLEAVE_DETAIL = @"
            INSERT INTO TLEAVEDETAIL
            (
                LEAVEDETAILID,
                LEAVEID,
                SLNO,
                EMPLOYEEID,
                LEAVETYPEID,
                LEAVEDATE,
                VALIDTILL,
                NUMBEROFDAYS,
                LEAVESTATUS,
                DAYTYPE,
                MMDETAILID
            )
            VALUES
            (
                @TLeaveDetailId,
                @TLeaveId,
                @TLeaveDetailSlNo,
                @EmployeeId,
                @LeaveTypeId,
                @TLeaveDetailLeaveDate,
                @TLeaveDetailValidTill,
                @TLeaveDetailNumberOfDays,
                @TLeaveDetailLeaveStatus,
                @TLeaveDetailLeaveDayType,
                @MMDetailId
            )";

        public const string DELETE_TLEAVE_DETAILS = @"
            DELETE FROM TLEAVEDETAIL
            WHERE  LEAVEID = @TLeaveId";

        public const string GET_TLEAVE = @"
            SELECT
                T.LEAVEID                       AS TLeaveId,
                T.OUID                          AS OUId,
                OU.ORGANIZATIONUNITCODE         AS OUCode,
                OU.ORGANIZATIONUNITNAME         AS OUName,
                T.PERIODID                      AS PeriodId,
                P.PERIODCODE                    AS PeriodCode,
                P.PERIODNAME                    AS PeriodName,
                T.BIZTRANSACTIONTYPEID          AS BizTransactionTypeId,
                BTT.BIZTRANSACTIONTYPECODE      AS BizTransactionTypeCode,
                BTT.BIZTRANSACTIONTYPENAME      AS BizTransactionTypeName,
                T.LEAVENUMBER                   AS TLeaveLeaveNumber,
                T.LEAVEDATE                     AS TLeaveLeaveDate,
                T.REFERENCENUMBER               AS TLeaveReferenceNumber,
                T.REFERENCEDATE                 AS TLeaveReferenceDate,
                T.REMARKS                       AS TLeaveRemarks,
                T.NUMBEROFDAYS                  AS TLeaveNumberOfDays,
                T.TYPEOFLEAVE                   AS TLeaveTypeOfLeave,
                T.REASONID                      AS ReasonId,
                R.REASONCODE                    AS ReasonCode,
                R.REASONNAME                    AS ReasonName,
                T.STATUS                        AS TLeaveStatus,
                T.VERSION                       AS TLeaveVersion,
                T.CREATEDBYID                   AS TLeaveCreatedById,
                T.CREATEDON                     AS TLeaveCreatedOn,
                T.MODIFIEDBYID                  AS TLeaveModifiedById,
                T.MODIFIEDON                    AS TLeaveModifiedOn
            FROM   TLEAVE T
            LEFT JOIN MORGANIZATIONUNIT  OU  ON OU.OUID                    = T.OUID
            LEFT JOIN MPERIOD            P   ON P.PERIODID                 = T.PERIODID
            LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID  = T.BIZTRANSACTIONTYPEID
            LEFT JOIN MREASON            R   ON R.REASONID                 = T.REASONID
            WHERE  T.LEAVEID = @TLeaveId";

        public const string CHECK_DUPLICATE_LEAVE_DATES = @"
            SELECT
                SUM(D.NUMBEROFDAYS)  AS DaysCount,
                T.LEAVEID            AS Id,
                T.LEAVENUMBER        AS LeaveNumber,
                D.LEAVEDATE          AS LeaveDate
            FROM   TLEAVEDETAIL D
            INNER JOIN TLEAVE   T ON T.LEAVEID = D.LEAVEID
            WHERE  D.EMPLOYEEID  = @EmployeeId
            AND    D.LEAVEDATE   = @LeaveDate
            AND    T.STATUS      IN (0, 1)
            AND    T.LEAVEID     != @ExcludeTLeaveId
            GROUP  BY T.LEAVEID, T.LEAVENUMBER, D.LEAVEDATE";

        public const string DELETE_TLEAVE = @"
            DELETE FROM TLEAVE
            WHERE  LEAVEID = @TLeaveId";

        public const string UPDATE_TLEAVE_STATUS = @"
            UPDATE TLEAVE
            SET    STATUS       = @Status,
                   MODIFIEDBYID = @UserId,
                   MODIFIEDON   = @ModifiedOn
            WHERE  LEAVEID      = @TLeaveId";

        public const string GET_LEAVE_TYPE_BY_ID = @"
            SELECT LEAVETYPE
            FROM   MLEAVE
            WHERE  LEAVEID = @LeaveTypeId";

        public const string GET_TLEAVE_DETAILS_BY_LEAVE = @"
            SELECT
                D.LEAVEDETAILID             AS TLeaveDetailId,
                D.LEAVEID                   AS TLeaveId,
                D.SLNO                      AS TLeaveDetailSlNo,
                D.EMPLOYEEID                AS EmployeeId,
                E.EMPLOYEECODE              AS EmployeeCode,
                E.EMPLOYEENAME              AS EmployeeName,
                D.LEAVETYPEID               AS LeaveTypeId,
                L.LEAVECODE                 AS LeaveTypeCode,
                L.LEAVENAME                 AS LeaveTypeName,
                D.LEAVEDATE                 AS TLeaveDetailLeaveDate,
                D.VALIDTILL                 AS TLeaveDetailValidTill,
                D.NUMBEROFDAYS              AS TLeaveDetailNumberOfDays,
                D.LEAVESTATUS               AS TLeaveDetailLeaveStatus,
                D.DAYTYPE                   AS TLeaveDetailLeaveDayType,
                D.MMDETAILID                AS MMDetailId,
                MH.DOCUMENTNUMBER           AS MMDetailDocumentNumber
            FROM   TLEAVEDETAIL D
            LEFT JOIN MEMPLOYEE  E  ON E.EMPLOYEEID        = D.EMPLOYEEID
            LEFT JOIN MLEAVE     L  ON L.LEAVEID           = D.LEAVETYPEID
            LEFT JOIN TMMDETAIL  MM ON MM.DOCUMENTDETAILID = D.MMDETAILID
            LEFT JOIN TMMHEAD    MH ON MH.DOCUMENTID       = MM.DOCUMENTID
            WHERE  D.LEAVEID = @TLeaveId
            ORDER  BY D.SLNO";

        public const string GET_SELECTLIST_TLEAVE = @"
WITH TLeaveList AS
(
    SELECT DISTINCT
        T.LEAVEID AS Id,
        T.LEAVENUMBER AS LeaveNumber,
        T.LEAVEDATE AS LeaveDate,
        ISNULL(T.REFERENCENUMBER, '0') AS ReferenceNumber,
        ISNULL(T.REFERENCEDATE, T.LEAVEDATE) AS ReferenceDate,
        ISNULL(E.EMPLOYEECODE, '') AS EmployeeCode,
        ISNULL(E.EMPLOYEENAME, '') AS EmployeeName,
        ISNULL(D.EMPLOYEEID, 0) AS EmployeeId,
        ISNULL(L.LEAVECODE, '') AS LeaveCode,
        ISNULL(L.LEAVENAME, '') AS LeaveName,
        ISNULL(D.LEAVEDATE, T.LEAVEDATE) AS LeaveFromDate,
        ISNULL(D.VALIDTILL, T.LEAVEDATE) AS LeaveValidTill,
        ISNULL(T.REMARKS, '') AS LeaveRemarks,
        ISNULL(D.NUMBEROFDAYS, 0) AS NumberofDays,
        SUM(ISNULL(D.NUMBEROFDAYS, 0)) OVER (PARTITION BY T.LEAVEID) AS DaysCount,
        ISNULL(D.LEAVESTATUS, 0) AS LeaveStatus,
        ROW_NUMBER() OVER (ORDER BY T.LEAVEID DESC) AS RowNum
    FROM TLEAVE T
    INNER JOIN TLEAVEDETAIL D ON D.LEAVEID = T.LEAVEID
    LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = D.EMPLOYEEID
    LEFT JOIN MLEAVE L ON L.LEAVEID = D.LEAVETYPEID
    WHERE T.STATUS IN (0, 1)  -- Changed from T.STATUS = 1
    {DYNAMIC_FILTERS}
)
SELECT
    Id,
    LeaveNumber,
    LeaveDate,
    ReferenceNumber,
    ReferenceDate,
    EmployeeCode,
    EmployeeName,
    EmployeeId,
    LeaveCode,
    LeaveName,
    LeaveFromDate,
    LeaveValidTill,
    LeaveRemarks,
    NumberofDays,
    DaysCount,
    LeaveStatus
FROM TLeaveList
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult)
ORDER BY LeaveNumber DESC";

        // ─────────────────────────────────────────────────────────────────
        // GetTotalLeaveTaken — total leave days taken per employee per month.
        // Migrated from GB4 LeaveQueryBuilder.GET_TOTAL_LEAVE_TAKEN.
        //
        // GB4 joined against DIMDATE (a date-dimension table) purely to derive
        // a month bucket (dt.monthyear) and to drive the date-range filter.
        // GB5 has no DIMDATE table in this module — the same result is
        // produced directly from TLEAVEDETAIL.LEAVEDATE via FORMAT(), which
        // also removes the CROSS JOIN row multiplication DIMDATE caused.
        //
        // GB4's unused OUTER APPLY "opening" balance subquery (computed but
        // never selected) has been dropped — dead computation, not carried
        // forward (MigrationClaude.md §14 code-hygiene rules).
        //
        // BizTransactionClassId IN (LEAVEDEBIT, LEAVE) = Leave debit / Leave request classes —
        // matches GB4's TotalTakenLeave SUM CASE. Resolved from
        // GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT at static-init time
        // instead of hardcoded literals, so these strings are `static readonly`, not `const`.
        // ─────────────────────────────────────────────────────────────────
        // GB4 parity fix: emp.WORKOUID IN(:ouid) is an always-applied filter in GB4
        // (LeaveQueryBuilder.cs line 1763) sourced from CriteriaDTO's "ouid" field —
        // GB4 defaults to an empty string when absent (which matches nothing). GB5
        // reads @ouid from the same criteria field, defaulting to the caller's
        // logged-in WorkOUId when absent (a safer default than GB4's "match nothing").
        //
        // GB4 parity fix: joins MLEAVE E ON E.LEAVEID = B.LEAVETYPEID and filters
        // e.LEAVETYPE IN (1,2) (LeaveQueryBuilder.cs line 1765) — this was dropped in
        // the initial migration and is restored here.
        //
        // GB4 parity fix: FirstNumber/MaxResult now genuinely page the result set via
        // ROW_NUMBER(), matching GET_SELECTLIST_TLEAVE's paging convention in this file
        // (GB4 passed FirstNumber/MaxResult into GenericDal.ExceuteSelectQuery, which
        // paginated — the initial migration accepted these parameters but never applied
        // them).
        public static readonly string GET_TOTAL_LEAVE_TAKEN = $@"
WITH TotalLeaveTaken AS
(
    SELECT
        FORMAT(B.LEAVEDATE, 'yyyy-MM')       AS PeriodType,
        emp.EMPLOYEEID                       AS EmployeeId,
        emp.EMPLOYEECODE                     AS EmployeeCode,
        emp.EMPLOYEENAME                     AS EmployeeName,
        emp.WORKOUID                         AS OUId,
        org.ORGANIZATIONUNITCODE             AS OUCode,
        org.ORGANIZATIONUNITNAME             AS OUName,
        d.DEPARTMENTID                       AS DepartmentId,
        d.DEPARTMENTCODE                     AS DepartmentCode,
        d.DEPARTMENTNAME                     AS DepartmentName,
        emp.DESIGNATIONID                    AS DesignationId,
        g.GCMCODE                            AS DesignationCode,
        g.GCMNAME                            AS DesignationName,
        emp.THUMBNAIL                        AS ThumbNail,
        SUM(CASE
                WHEN C.BIZTRANSACTIONCLASSID IN ({GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVEDEBIT}, {GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVE})
                THEN B.NUMBEROFDAYS
                ELSE 0
            END)                              AS TotalTakenLeave,
        emp.TENANTID                          AS TenantId,
        ROW_NUMBER() OVER (ORDER BY MAX(B.LEAVEDATE) DESC) AS RowNum
    FROM      MEMPLOYEE emp
    INNER JOIN MORGANIZATIONUNIT  org ON org.OUID          = emp.WORKOUID
    INNER JOIN MDEPARTMENT        d   ON d.DEPARTMENTID    = emp.DEPARTMENTID
    INNER JOIN MGCM               g   ON g.GCMID           = emp.DESIGNATIONID
    INNER JOIN TLEAVEDETAIL       B   ON B.EMPLOYEEID      = emp.EMPLOYEEID
    INNER JOIN TLEAVE             A   ON A.LEAVEID         = B.LEAVEID
                                      AND A.STATUS IN (0, 1)
    INNER JOIN MBIZTRANSACTIONTYPE C  ON C.BIZTRANSACTIONTYPEID = A.BIZTRANSACTIONTYPEID
                                      AND C.BIZTRANSACTIONCLASSID IN ({GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVEDEBIT}, {GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVE})
    INNER JOIN MLEAVE              E  ON E.LEAVEID         = B.LEAVETYPEID
                                      AND E.LEAVETYPE IN (1, 2)
    WHERE   emp.WORKOUID     = @ouid
    AND     emp.TENANTID     = @tenantid
    AND     emp.BASICON     <> 4
    AND     emp.EMPLOYEESTATUS = 0
    AND     B.LEAVEDATE     >= @periodfromdate
    AND     B.LEAVEDATE     <= @periodtodate
    AND     (@employeeid     IS NULL OR emp.EMPLOYEEID   = @employeeid)
    AND     (@departmentid   IS NULL OR emp.DEPARTMENTID = @departmentid)
    AND     (@designationid  IS NULL OR emp.DESIGNATIONID = @designationid)
    AND     (@payconfigurationid IS NULL OR emp.PAYCONFIGURATIONID = @payconfigurationid)
    GROUP BY
        FORMAT(B.LEAVEDATE, 'yyyy-MM'),
        emp.EMPLOYEEID, emp.EMPLOYEECODE, emp.EMPLOYEENAME, emp.WORKOUID,
        org.ORGANIZATIONUNITCODE, org.ORGANIZATIONUNITNAME,
        d.DEPARTMENTID, d.DEPARTMENTCODE, d.DEPARTMENTNAME,
        emp.DESIGNATIONID, g.GCMCODE, g.GCMNAME, emp.THUMBNAIL, emp.TENANTID
)
SELECT PeriodType, EmployeeId, EmployeeCode, EmployeeName, OUId, OUCode, OUName,
       DepartmentId, DepartmentCode, DepartmentName, DesignationId, DesignationCode,
       DesignationName, ThumbNail, TotalTakenLeave, TenantId
FROM TotalLeaveTaken
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult)
ORDER BY RowNum";

        public static readonly string GET_TOTAL_LEAVE_TAKEN_COUNT = $@"
SELECT COUNT(*) FROM (
    SELECT emp.EMPLOYEEID
    FROM      MEMPLOYEE emp
    INNER JOIN MORGANIZATIONUNIT  org ON org.OUID          = emp.WORKOUID
    INNER JOIN MDEPARTMENT        d   ON d.DEPARTMENTID    = emp.DEPARTMENTID
    INNER JOIN MGCM               g   ON g.GCMID           = emp.DESIGNATIONID
    INNER JOIN TLEAVEDETAIL       B   ON B.EMPLOYEEID      = emp.EMPLOYEEID
    INNER JOIN TLEAVE             A   ON A.LEAVEID         = B.LEAVEID
                                      AND A.STATUS IN (0, 1)
    INNER JOIN MBIZTRANSACTIONTYPE C  ON C.BIZTRANSACTIONTYPEID = A.BIZTRANSACTIONTYPEID
                                      AND C.BIZTRANSACTIONCLASSID IN ({GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVEDEBIT}, {GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVE})
    INNER JOIN MLEAVE              E  ON E.LEAVEID         = B.LEAVETYPEID
                                      AND E.LEAVETYPE IN (1, 2)
    WHERE   emp.WORKOUID     = @ouid
    AND     emp.TENANTID     = @tenantid
    AND     emp.BASICON     <> 4
    AND     emp.EMPLOYEESTATUS = 0
    AND     B.LEAVEDATE     >= @periodfromdate
    AND     B.LEAVEDATE     <= @periodtodate
    AND     (@employeeid     IS NULL OR emp.EMPLOYEEID   = @employeeid)
    AND     (@departmentid   IS NULL OR emp.DEPARTMENTID = @departmentid)
    AND     (@designationid  IS NULL OR emp.DESIGNATIONID = @designationid)
    AND     (@payconfigurationid IS NULL OR emp.PAYCONFIGURATIONID = @payconfigurationid)
    GROUP BY
        FORMAT(B.LEAVEDATE, 'yyyy-MM'),
        emp.EMPLOYEEID
) AS Total";

        // ─────────────────────────────────────────────────────────────────
        // Leave Credit Posting (GB4 parity: TLeaveBLL.TLeavePostingProcess /
        // LeaveQueryBuilder.EMPLOYEES_COUNT + TLEAVE_INERTQUERY_FIELDS_SQL family).
        //
        // GB4 built the 9 optional employee filters (EmployeeTypeId, PayGroupId,
        // StaffTypeId, UnitId, WorkTypeId, DivisionId, PayConfigurationId, ShiftId,
        // DepartmentId) via raw string concatenation of the id value directly into
        // the SQL text — a SQL-injection shape. Rewritten here as the same
        // "(@param IS NULL OR col = @param)" pattern TLeaveQB.GET_TOTAL_LEAVE_TAKEN
        // already established for this module.
        //
        // DivisionId is NOT a direct MEMPLOYEE column (unlike GB4's schema) — it
        // lives on MORGANIZATIONUNIT, joined via d.WORKOUID = ou.OUID.
        // ─────────────────────────────────────────────────────────────────

        private const string CREDIT_POSTING_OPTIONAL_FILTERS = @"
            AND (@employeetypeid     IS NULL OR d.EMPLOYEETYPEID     = @employeetypeid)
            AND (@paygroupid         IS NULL OR d.PAYGROUPID         = @paygroupid)
            AND (@stafftypeid        IS NULL OR d.STAFFTYPEID        = @stafftypeid)
            AND (@unitid             IS NULL OR d.UNITID             = @unitid)
            AND (@worktypeid         IS NULL OR d.WORKTYPEID         = @worktypeid)
            AND (@divisionid         IS NULL OR ou.DIVISIONID        = @divisionid)
            AND (@payconfigurationid IS NULL OR d.PAYCONFIGURATIONID = @payconfigurationid)
            AND (@shiftid            IS NULL OR d.SHIFTID            = @shiftid)
            AND (@departmentid       IS NULL OR d.DEPARTMENTID       = @departmentid)";

        public const string COUNT_ELIGIBLE_EMPLOYEES_BY_PAYPROCESS = @"
            SELECT COUNT(*)
            FROM   TPAYPROCESS a
            INNER JOIN TPAYPROCESSADDON b ON a.PAYPROCESSID = b.PAYPROCESSID
            INNER JOIN MPAYPERIOD       c ON a.PAYPERIODID  = c.PAYPERIODID
            INNER JOIN MEMPLOYEE        d ON a.EMPLOYEEID   = d.EMPLOYEEID
            INNER JOIN MORGANIZATIONUNIT ou ON d.WORKOUID   = ou.OUID
            WHERE  c.FROMDATE >= @fromdate
            AND    c.TODATE   <= @todate
            AND    d.STATUS = 1
            AND    (d.EMPLOYEESTATUS = 0 OR (d.DATEOFRESIGNATION >= @fromdate AND d.DATEOFRESIGNATION <= @todate))
            " + CREDIT_POSTING_OPTIONAL_FILTERS + @"
            AND    d.WORKOUID = @workouid";

        public const string COUNT_ELIGIBLE_EMPLOYEES_BY_DATEOFJOINING = @"
            SELECT COUNT(*)
            FROM   MEMPLOYEE d
            INNER JOIN MORGANIZATIONUNIT ou ON d.WORKOUID = ou.OUID
            WHERE  d.EMPLOYEESTATUS = 0
            AND    d.BASICON <> 4
            AND    d.DATEOFJOINING <= @joiningdate
            AND    d.STATUS = 1
            AND    (d.EMPLOYEESTATUS = 0 OR (d.DATEOFRESIGNATION >= @joiningdate AND d.DATEOFRESIGNATION <= @joiningdate))
            " + CREDIT_POSTING_OPTIONAL_FILTERS + @"
            AND    d.WORKOUID = @workouid";

        public const string COUNT_ELIGIBLE_EMPLOYEES_BY_DAILYATTENDANCE = @"
            SELECT COUNT(*)
            FROM   TDAILYATTENDANCE a
            INNER JOIN MEMPLOYEE d ON a.EMPLOYEEID = d.EMPLOYEEID
            INNER JOIN MORGANIZATIONUNIT ou ON d.WORKOUID = ou.OUID
            WHERE  d.EMPLOYEESTATUS = 0
            AND    d.BASICON <> 4
            AND    a.ATTENDANCEDATE >= @attendancedate
            AND    a.ATTENDANCEDATE <= @attendancetodate
            AND    d.STATUS = 1
            AND    (d.EMPLOYEESTATUS = 0 OR (d.DATEOFRESIGNATION >= @attendancetodate AND d.DATEOFRESIGNATION <= @attendancetodate))
            " + CREDIT_POSTING_OPTIONAL_FILTERS + @"
            AND    d.WORKOUID = @workouid";

        public const string INSERT_TLEAVE_HEADER_FOR_POSTING = @"
            INSERT INTO TLEAVE
            (
                LEAVEID, OUID, PERIODID, BIZTRANSACTIONTYPEID, LEAVENUMBER, LEAVEDATE,
                REFERENCENUMBER, REFERENCEDATE, REMARKS, NUMBEROFDAYS, STATUS, VERSION,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TYPEOFLEAVE
            )
            VALUES
            (
                @TLeaveId, @OUId, @PeriodId, @BizTransactionTypeId, @TLeaveLeaveNumber, CAST(GETDATE() AS DATE),
                @TLeaveLeaveNumber, GETDATE(), '', 0, 1, 1,
                @UserId, GETDATE(), @UserId, GETDATE(), @TLeaveTypeOfLeave
            )";

        // RegularCreditType = 0 — credit computed from a completed pay-process run,
        // using the dynamic per-formula addon column SUM(b.F_<FormulaCode>).
        // FormulaCode is validated (see TLeaveDAL) against ^[A-Za-z0-9_]+$ before being
        // interpolated here — Dapper cannot parameterize a column identifier.
        public const string INSERT_TLEAVEDETAIL_FROM_PAYPROCESS_SELECT = @"
            INSERT INTO TLEAVEDETAIL (LEAVEDETAILID, LEAVEID, SLNO, EMPLOYEEID, LEAVETYPEID, LEAVEDATE, VALIDTILL, NUMBEROFDAYS, LEAVESTATUS, DAYTYPE)
            SELECT
                (@detailstartid + ROW_NUMBER() OVER (ORDER BY a.EMPLOYEEID)) AS LEAVEDETAILID,
                @tleaveid AS LEAVEID,
                (ROW_NUMBER() OVER (ORDER BY a.EMPLOYEEID)) AS SLNO,
                a.EMPLOYEEID,
                @leavetypeid AS LEAVETYPEID,
                @leavedate AS LEAVEDATE,
                @validtilldate AS VALIDTILL,
                ROUND(((CAST(SUM(b.{0}) AS NUMERIC(18,4)) / @noofdaysperleaveday) * @leavedays), 0) AS NUMBEROFDAYS,
                0, 0
            FROM   TPAYPROCESS a
            INNER JOIN TPAYPROCESSADDON b ON a.PAYPROCESSID = b.PAYPROCESSID
            INNER JOIN MPAYPERIOD       c ON a.PAYPERIODID  = c.PAYPERIODID
            INNER JOIN MEMPLOYEE        d ON a.EMPLOYEEID   = d.EMPLOYEEID
            INNER JOIN MORGANIZATIONUNIT ou ON d.WORKOUID   = ou.OUID
            WHERE  d.WORKOUID = @workouid
            AND    c.FROMDATE >= @fromdate
            AND    c.TODATE   <= @todate
            AND    d.STATUS = 1
            AND    (d.EMPLOYEESTATUS = 0 OR (d.DATEOFRESIGNATION >= @fromdate AND d.DATEOFRESIGNATION <= @todate))
            AND    (@typeofleave <> 1 OR (d.DATEOFRESIGNATION >= @resignationfrom AND d.DATEOFRESIGNATION <= @resignationto))
            " + CREDIT_POSTING_OPTIONAL_FILTERS + @"
            GROUP  BY a.EMPLOYEEID";

        // RegularCreditType = 1 — credit for every active employee joined on/before a cutoff date.
        public const string INSERT_TLEAVEDETAIL_FROM_EMPLOYEE_SELECT = @"
            INSERT INTO TLEAVEDETAIL (LEAVEDETAILID, LEAVEID, SLNO, EMPLOYEEID, LEAVETYPEID, LEAVEDATE, VALIDTILL, NUMBEROFDAYS, LEAVESTATUS, DAYTYPE)
            SELECT
                (@detailstartid + ROW_NUMBER() OVER (ORDER BY d.EMPLOYEEID)) AS LEAVEDETAILID,
                @tleaveid AS LEAVEID,
                (ROW_NUMBER() OVER (ORDER BY d.EMPLOYEEID)) AS SLNO,
                d.EMPLOYEEID,
                @leavetypeid AS LEAVETYPEID,
                @leavedate AS LEAVEDATE,
                @validtilldate AS VALIDTILL,
                @leavedays AS NUMBEROFDAYS,
                0, 0
            FROM   MEMPLOYEE d
            INNER JOIN MORGANIZATIONUNIT ou ON d.WORKOUID = ou.OUID
            WHERE  d.EMPLOYEESTATUS = 0
            AND    d.BASICON <> 4
            AND    d.WORKOUID = @workouid
            AND    d.DATEOFJOINING <= @joiningdate
            AND    d.STATUS = 1
            AND    (d.EMPLOYEESTATUS = 0 OR (d.DATEOFRESIGNATION >= @joiningdate AND d.DATEOFRESIGNATION <= @joiningdate))
            " + CREDIT_POSTING_OPTIONAL_FILTERS + @"
            GROUP  BY d.EMPLOYEEID";

        // RegularCreditType = 2 — credit computed from daily-attendance day-type counts.
        public const string INSERT_TLEAVEDETAIL_FROM_DAILYATTENDANCE_SELECT = @"
            INSERT INTO TLEAVEDETAIL (LEAVEDETAILID, LEAVEID, SLNO, EMPLOYEEID, LEAVETYPEID, LEAVEDATE, VALIDTILL, NUMBEROFDAYS, LEAVESTATUS, DAYTYPE)
            SELECT
                (@detailstartid + ROW_NUMBER() OVER (ORDER BY d.EMPLOYEEID)) AS LEAVEDETAILID,
                @tleaveid AS LEAVEID,
                (ROW_NUMBER() OVER (ORDER BY d.EMPLOYEEID)) AS SLNO,
                d.EMPLOYEEID,
                @leavetypeid AS LEAVETYPEID,
                @leavedate AS LEAVEDATE,
                @validtilldate AS VALIDTILL,
                FLOOR((SUM(CASE WHEN a.DAYTYPE IN (1, 5) THEN 0.5 WHEN a.DAYTYPE = 0 THEN 1 ELSE 0 END) / @noofdaysperleaveday)) * @leavedays AS NUMBEROFDAYS,
                0, 0
            FROM   TDAILYATTENDANCE a
            INNER JOIN MEMPLOYEE d ON a.EMPLOYEEID = d.EMPLOYEEID
            INNER JOIN MORGANIZATIONUNIT ou ON d.WORKOUID = ou.OUID
            WHERE  d.EMPLOYEESTATUS = 0
            AND    d.BASICON <> 4
            AND    d.WORKOUID = @workouid
            AND    a.ATTENDANCEDATE >= @attendancedate
            AND    a.ATTENDANCEDATE <= @attendancetodate
            AND    d.STATUS = 1
            AND    (d.EMPLOYEESTATUS = 0 OR (d.DATEOFRESIGNATION >= @attendancetodate AND d.DATEOFRESIGNATION <= @attendancetodate))
            " + CREDIT_POSTING_OPTIONAL_FILTERS + @"
            GROUP  BY d.EMPLOYEEID";

        // ─────────────────────────────────────────────────────────────────
        // Leave Debit Posting / "Make Zero" (GB4 parity: TLeaveBLL.TleavePostingMakingZero /
        // LeaveQueryBuilder.LEAVEBALANCEFOR_MAKEZERO). Zeroes out each employee's opening
        // leave balance for a leave type by posting the offsetting number of days.
        //
        // GB4's live SQL does NOT filter by employee status/resignation date — that clause
        // is commented out inside the SQL string itself (a deliberate change per an existing
        // GB4 code comment tied to a support ticket). Preserved as-is here, not carried
        // forward as an oversight.
        //
        // BIZTRANSACTIONCLASSID set bound from BIZTRANSACTIONCLASSCONSTANT (LEAVECREDIT,
        // LEAVEDEBIT, LEAVE) instead of GB4's magic numbers. The Credit/Debit HAVING sign
        // flip is expressed as one parameterized clause via @iscredit instead of two
        // near-duplicate SQL strings.
        // ─────────────────────────────────────────────────────────────────
        public static readonly string GET_LEAVE_BALANCE_FOR_MAKEZERO = @"
            SELECT
                emp.EMPLOYEEID                    AS EmployeeId,
                emp.EMPLOYEECODE                  AS EmployeeCode,
                emp.EMPLOYEENAME                  AS EmployeeName,
                ISNULL(MAX(opening.opening), 0)   AS LeaveBalance,
                e.LEAVECODE                       AS LeaveTypeCode,
                e.LEAVENAME                       AS LeaveTypeName,
                e.LEAVEID                         AS LeaveTypeId
            FROM   MEMPLOYEE emp
            INNER JOIN MORGANIZATIONUNIT ou ON emp.WORKOUID = ou.OUID
            LEFT OUTER JOIN MLEAVE e ON (1 = 1)
            OUTER APPLY
            (
                SELECT SUM(CASE
                               WHEN bt.BIZTRANSACTIONCLASSID = @leavecreditclassid THEN td.NUMBEROFDAYS
                               ELSE -1 * td.NUMBEROFDAYS
                           END) AS opening
                FROM   TLEAVEDETAIL td
                INNER JOIN TLEAVE t              ON td.LEAVEID = t.LEAVEID
                INNER JOIN MBIZTRANSACTIONTYPE bt ON t.BIZTRANSACTIONTYPEID = bt.BIZTRANSACTIONTYPEID
                WHERE  td.EMPLOYEEID = emp.EMPLOYEEID
                AND    td.LEAVETYPEID = e.LEAVEID
                AND    td.LEAVEDATE <= @leavedate
                AND    t.STATUS IN (0, 1)
                AND    bt.BIZTRANSACTIONCLASSID IN (@leavecreditclassid, @leavedebitclassid, @leaveclassid)
            ) opening
            WHERE  1 = 1
            AND    emp.WORKOUID = @workouid
            AND    e.LEAVETYPE = 1
            AND    e.LEAVEID = @leavetypeid
            " + CREDIT_POSTING_OPTIONAL_FILTERS.Replace("d.EMPLOYEETYPEID", "emp.EMPLOYEETYPEID")
                                                .Replace("d.PAYGROUPID", "emp.PAYGROUPID")
                                                .Replace("d.STAFFTYPEID", "emp.STAFFTYPEID")
                                                .Replace("d.UNITID", "emp.UNITID")
                                                .Replace("d.WORKTYPEID", "emp.WORKTYPEID")
                                                .Replace("d.PAYCONFIGURATIONID", "emp.PAYCONFIGURATIONID")
                                                .Replace("d.SHIFTID", "emp.SHIFTID")
                                                .Replace("d.DEPARTMENTID", "emp.DEPARTMENTID") + @"
            GROUP  BY emp.EMPLOYEEID, emp.EMPLOYEECODE, emp.EMPLOYEENAME, e.LEAVECODE, e.LEAVENAME, e.LEAVETYPE, e.LEAVEID
            HAVING (@iscredit = 1 AND ISNULL(MAX(opening.opening), 0) < 0)
                OR (@iscredit = 0 AND ISNULL(MAX(opening.opening), 0) > 0)";

        public const string INSERT_TLEAVEDETAIL_FOR_MAKEZERO = @"
            INSERT INTO TLEAVEDETAIL (LEAVEDETAILID, LEAVEID, SLNO, EMPLOYEEID, LEAVETYPEID, LEAVEDATE, VALIDTILL, NUMBEROFDAYS, LEAVESTATUS, DAYTYPE)
            VALUES (@TLeaveDetailId, @TLeaveId, @TLeaveDetailSlNo, @EmployeeId, @LeaveTypeId, @TLeaveDetailLeaveDate, @TLeaveDetailValidTill, @TLeaveDetailNumberOfDays, 0, 0)";

        // GB4 parity: TLeaveDAL.GetTLeaveDetails / LeaveQueryBuilder.GET_LEAVE_DETAIL_REPORT_NEW
        public const string GET_TLEAVEDETAILS = @"
SELECT
    LEAVE.OUID              AS OUId,
    LEAVE.LEAVEID           AS LeaveId,
    LEAVE.LEAVENUMBER       AS LeaveNumber,
    LEAVE.LEAVEDATE         AS LeaveDate,
    LEAVE.REFERENCENUMBER   AS ReferenceNumber,
    LEAVE.REFERENCEDATE     AS ReferenceDate,
    LEAVE.REMARKS           AS Remarks,
    LEAVE.NUMBEROFDAYS      AS TotalNoofDays,
    CASE WHEN LEAVEDETAIL.DAYTYPE = 0 THEN 1
         WHEN LEAVEDETAIL.DAYTYPE = 1 THEN 0.5
         WHEN LEAVEDETAIL.DAYTYPE = 2 THEN 0.5 ELSE 0 END AS NoofDays,
    EMP.EMPLOYEEID          AS EmployeeId,
    EMP.EMPLOYEECODE        AS EmployeeCode,
    EMP.EMPLOYEENAME        AS EmployeeName,
    CASE WHEN LEAVE.STATUS = 0 THEN 'Pending'
         WHEN LEAVE.STATUS = 1 THEN 'Active'
         WHEN LEAVE.STATUS = 2 THEN 'Deleted'
         WHEN LEAVE.STATUS = 3 THEN 'Rejected'
         WHEN LEAVE.STATUS = 4 THEN 'InActive'
         WHEN LEAVE.STATUS = 5 THEN 'Archived'
    ELSE '' END AS Status,
    CASE WHEN LEAVEDETAIL.DAYTYPE = 0 THEN 'Full Day'
         WHEN LEAVEDETAIL.DAYTYPE = 1 THEN 'First Half'
         WHEN LEAVEDETAIL.DAYTYPE = 2 THEN 'Second Half'
    END AS DayType,
    CASE WHEN LEAVEDETAIL.LEAVESTATUS = 0 THEN 'Pending'
         WHEN LEAVEDETAIL.LEAVESTATUS = 1 THEN 'Availed'
         WHEN LEAVEDETAIL.LEAVESTATUS = 2 THEN 'Final'
    END AS LeaveStatus,
    CASE WHEN LEAVE.STATUS = 0 THEN 'Pending'
         WHEN LEAVE.STATUS = 1 THEN 'Approved'
         WHEN LEAVE.STATUS = 2 THEN 'Resend'
         WHEN LEAVE.STATUS = 3 THEN 'Rejected'
    ELSE '' END AS StatusName,
    U.USERCODE              AS AdminReportingToCode,
    U.USERNAME              AS AdminReportingToName,
    E.EMPLOYEECODE          AS ReportingToEmployeeCode,
    E.EMPLOYEENAME          AS ReporttingToEmployeeName,
    L.LEAVECODE             AS LeaveCode,
    L.LEAVENAME             AS LeaveName,
    LEAVEDETAIL.LEAVEDATE   AS FromDate,
    LEAVEDETAIL.VALIDTILL   AS ToDate,
    CC.FromDate             AS LeaveFromDate,
    CC.ToDate               AS LeaveToDate,
    LEAVE.CREATEDBYID       AS CreatedById,
    LEAVE.CREATEDON         AS CreatedOn,
    CREATED.USERCODE        AS CreatedByCode,
    CREATED.USERNAME        AS CreatedByName,
    LEAVE.MODIFIEDBYID      AS ModifiedById,
    LEAVE.MODIFIEDON        AS ModifiedOn,
    MODIFIED.USERCODE       AS ModifiedByCode,
    MODIFIED.USERNAME       AS ModifiedByName,
    R.REASONCODE            AS ReasonCode,
    R.REASONNAME            AS ReasonName
FROM
    TLEAVE LEAVE
    INNER JOIN TLEAVEDETAIL LEAVEDETAIL ON LEAVE.LEAVEID = LEAVEDETAIL.LEAVEID
    CROSS APPLY (SELECT MIN(LEAVEDATE) AS FromDate, MAX(LEAVEDATE) AS ToDate
                 FROM TLEAVEDETAIL CC WHERE CC.LEAVEID = LEAVEDETAIL.LEAVEID) CC
    INNER JOIN MEMPLOYEE EMP           ON LEAVEDETAIL.EMPLOYEEID = EMP.EMPLOYEEID
    INNER JOIN MBIZTRANSACTIONTYPE BIZ ON LEAVE.BIZTRANSACTIONTYPEID = BIZ.BIZTRANSACTIONTYPEID
    LEFT JOIN  MUSER U                 ON EMP.ADMINREPORTINGTOID = U.USERID
    LEFT JOIN  MEMPLOYEE E             ON EMP.REPORTINGTOEMPLOYEEID = E.EMPLOYEEID
    LEFT JOIN  MLEAVE L                ON LEAVEDETAIL.LEAVETYPEID = L.LEAVEID
    LEFT JOIN  MPAYCONFIGURATION PAY   ON EMP.PAYCONFIGURATIONID = PAY.PAYCONFIGURATIONID
    LEFT JOIN  MUSER CREATED           ON LEAVE.CREATEDBYID = CREATED.USERID
    LEFT JOIN  MUSER MODIFIED          ON LEAVE.MODIFIEDBYID = MODIFIED.USERID
    LEFT JOIN  MREASON R               ON LEAVE.REASONID = R.REASONID
WHERE 1 = 1";
    }
}