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

namespace PayRollDAL.Query.Leave
{
    // Required indexes:
    //   WorkflowTracking: (EntityType, EntityId) INCLUDE (TrackingId, TenantId, ElsaInstanceId, CurrentStep, Status)
    //   WorkflowTracking: (TrackingId)                        -- PK; UPDATE by TrackingId
    //   WorkflowHistory: (EntityType, EntityId, Timestamp)    -- covers history log lookup by entity
    public class LeaveQB
    {
        public const string GET_EMPLOYEEID_BY_USERCODE =
            "SELECT TOP 1 USERID FROM MUSER WHERE USERCODE = @UserCode";

        public const string GET_EMPLOYEEID_BY_USERCODE_PG =
            "SELECT USERID FROM MUSER WHERE USERCODE = @UserCode LIMIT 1";

        public const string GET_LEAVE = @"SELECT
                                            L.LEAVEID                        AS LeaveId,
                                            L.LEAVECODE                      AS LeaveCode,
                                            L.LEAVENAME                      AS LeaveName,
                                            L.ISENCASHABLE                   AS LeaveIsEncashable,
                                            L.ISBALANCEMAINTAINED            AS LeaveIsBalanceMaintained,
                                            L.ISCARRIEDOVER                  AS LeaveIsCarriedOver,
                                            L.CREDITMETHOD                   AS LeaveCreditMethod,
                                            L.LEAVETYPE                      AS LeaveLeaveType,
                                            L.LEAVESALARYPERCENT             AS LeaveLeaveSalaryPercent,
                                            L.INCLUDEHOLIDAY                 AS LeaveIncludeHoliday,
                                            L.SURROUNDINGDAY                 AS LeaveSurroundingDay,
                                            L.ISAPPLICABLEFORLEAVEAPPLICATION AS LeaveIsApplicable,
                                            L.SORTORDER                      AS LeaveSortOrder,
                                            L.STATUS                         AS LeaveStatus,
                                            L.VERSION                        AS LeaveVersion,
                                            L.CREATEDBYID                    AS LeaveCreatedById,
                                            L.CREATEDON                      AS LeaveCreatedOn,
                                            L.MODIFIEDBYID                   AS LeaveModifiedById,
                                            L.MODIFIEDON                     AS LeaveModifiedOn,
                                            L.SOURCETYPE                     AS LeaveSourceType,
                                            L.TENANTID                       AS LeaveTenantId,
                                            L.LEAVECREDITFORMULAID           AS LeaveCreditFormulaId,
                                            L.LEAVESALARYFORMULAID           AS LeaveSalaryFormulaId,
                                            L.DISPLAYCODE                    AS LeaveDisplayCode,
                                            L.ISTRACKINGREQUIRED             AS LeaveIsTrackingRequired,
                                            L.LTALEAVEIDS                    AS LeaveLTALeaveIds,
                                            L.VALIDPERIOD                    AS LeaveValidPeriod
                                        FROM MLEAVE L
                                        WHERE L.LEAVEID = @leaveid;";

        public const string SAVE_LEAVE = @"INSERT INTO MLEAVE
                                                            (
                                                                LEAVEID,
                                                                LEAVECODE,
                                                                LEAVENAME,
                                                                ISENCASHABLE,
                                                                ISBALANCEMAINTAINED,
                                                                ISCARRIEDOVER,
                                                                CREDITMETHOD,
                                                                LEAVETYPE,
                                                                LEAVESALARYPERCENT,
                                                                INCLUDEHOLIDAY,
                                                                SURROUNDINGDAY,
                                                                SORTORDER,
                                                                STATUS,
                                                                VERSION,
                                                                CREATEDBYID,
                                                                CREATEDON,
                                                                MODIFIEDBYID,
                                                                MODIFIEDON,
                                                                SOURCETYPE,
                                                                TENANTID,
                                                                LEAVECREDITFORMULAID,
                                                                ISAPPLICABLEFORLEAVEAPPLICATION,
                                                                LEAVESALARYFORMULAID,
                                                                DISPLAYCODE,
                                                                ISTRACKINGREQUIRED,
                                                                LTALEAVEIDS,
                                                                VALIDPERIOD
                                                            )
                                                            VALUES
                                                            (
                                                                @LeaveId,
                                                                @LeaveCode,
                                                                @LeaveName,
                                                                @LeaveIsEncashable,
                                                                @LeaveIsBalanceMaintained,
                                                                @LeaveIsCarriedOver,
                                                                @LeaveCreditMethod,
                                                                @LeaveLeaveType,
                                                                @LeaveLeaveSalaryPercent,
                                                                @LeaveIncludeHoliday,
                                                                @LeaveSurroundingDay,
                                                                @LeaveSortOrder,
                                                                @LeaveStatus,
                                                                @LeaveVersion,
                                                                @LeaveCreatedById,
                                                                @LeaveCreatedOn,
                                                                @LeaveModifiedById,
                                                                @LeaveModifiedOn,
                                                                @LeaveSourceType,
                                                                @LeaveTenantId,
                                                                @LeaveCreditFormulaId,
                                                                @LeaveIsApplicable,
                                                                @LeaveSalaryFormulaId,
                                                                @LeaveDisplayCode,
                                                                @LeaveIsTrackingRequired,
                                                                @LeaveLTALeaveIds,
                                                                @LeaveValidPeriod
                                                            );";

        public const string UPDATE_LEAVE = @"UPDATE MLEAVE
                                            SET
                                                LEAVECODE       = @LeaveCode,
                                                LEAVENAME       = @LeaveName,
                                                ISENCASHABLE    = @LeaveIsEncashable,
                                                ISBALANCEMAINTAINED = @LeaveIsBalanceMaintained,
                                                ISCARRIEDOVER   = @LeaveIsCarriedOver,
                                                CREDITMETHOD    = @LeaveCreditMethod,
                                                LEAVETYPE       = @LeaveLeaveType,
                                                LEAVESALARYPERCENT = @LeaveLeaveSalaryPercent,
                                                INCLUDEHOLIDAY = @LeaveIncludeHoliday,
                                                SURROUNDINGDAY = @LeaveSurroundingDay,
                                                SORTORDER       = @LeaveSortOrder,
                                                STATUS          = @LeaveStatus,
                                                VERSION         = @LeaveVersion,
                                                MODIFIEDBYID    = @LeaveModifiedById,
                                                MODIFIEDON      = @LeaveModifiedOn,
                                                SOURCETYPE      = @LeaveSourceType,
                                                TENANTID        = @LeaveTenantId,
                                                LEAVECREDITFORMULAID    = @LeaveCreditFormulaId,
                                                ISAPPLICABLEFORLEAVEAPPLICATION = @LeaveIsApplicable,
                                                LEAVESALARYFORMULAID    = @LeaveSalaryFormulaId,
                                                DISPLAYCODE             = @LeaveDisplayCode,
                                                ISTRACKINGREQUIRED      = @LeaveIsTrackingRequired,
                                                LTALEAVEIDS             = @LeaveLTALeaveIds,
                                                VALIDPERIOD             = @LeaveValidPeriod
                                            WHERE
                                                LEAVEID = @LeaveId;";

        public const string DELETE_LEAVE = @"DELETE FROM MLEAVE
                                            WHERE LEAVEID = @leaveid;";

        public const string GET_SELECTLIST_LEAVE = @"";
        public const string CHECK_LEAVE_DUPLICATE = @"
        SELECT 1 FROM MLEAVE WHERE LEAVECODE = @LeaveCode AND TENANTID = @TenantId;
    ";
        // Workflow Config Query (generic table)
        public const string GET_WORKFLOW_CONFIG = @"SELECT 
    TenantId, EntityType, RequiresApproval, WorkflowId 
    FROM ClientWorkflowConfig 
    WHERE TenantId = @TenantId AND EntityType = @EntityType";

        // Leave-specific status update (for entityType="LeaveType")
        public const string UPDATE_LEAVE_STATUS = @"UPDATE MLEAVE SET Status = @Status WHERE LEAVEID = @LeaveId;";  
        // Generic tracking insert (WorkflowTracking table)
        public const string ADD_TRACKING = @"INSERT INTO WorkflowTracking 
    (TrackingId, EntityType, EntityId, TenantId, ElsaInstanceId, CurrentStep, Status, CreatedAt) 
    VALUES 
    (@TrackingId, @EntityType, @EntityId, @TenantId, @ElsaInstanceId, @CurrentStep, @Status, GETUTCDATE())";

        // Generic tracking update
        public const string UPDATE_TRACKING = @"UPDATE WorkflowTracking 
    SET CurrentStep = @CurrentStep, Status = @Status, ElsaInstanceId = @ElsaInstanceId 
    WHERE TrackingId = @TrackingId";

        // Generic history insert (WorkflowHistory table)
        public const string ADD_HISTORY_LOG = @"INSERT INTO WorkflowHistory 
    (Id, EntityType, EntityId, Action, Timestamp) 
    VALUES 
    (@Id, @EntityType, @EntityId, @Action, GETUTCDATE())";

        // Generic tracking get by entity
        public const string GET_TRACKING_BY_ENTITY = @"SELECT
    TrackingId,
    EntityType,
    EntityId,
    TenantId,
    ElsaInstanceId,
    CurrentStep,
    Status,
    CreatedAt
FROM WorkflowTracking
WHERE EntityType = @EntityType AND EntityId = @EntityId";




        public const string GET_ROOTSEMPLOYEEPERMISSIONINFO = @"SELECT
                                                                EMPLOYEEID      AS EmployeeId,
                                                                EMPLOYEECODE    AS EmployeeCode,
                                                                EMPLOYEENAME    AS EmployeeName,
                                                                MAXDURATION     AS MaxDuration,
                                                                MINDURATION     AS MinDuration,
                                                                TOTALDURATION   AS TotalDuration,
                                                                FROMDATE        AS FromDate,
                                                                TODATE          AS ToDate,
                                                                TOTALALLOWED    AS TotalAllowed,
                                                                PAYPROCESSTATUS AS PayProcessStatus,
                                                                USEDDURATION    AS UsedDuration,
                                                                USEDCOUNT       AS UsedCount
                                                            FROM dbo.Z_ROOTS_EMPLOYEE_PERMISSION_INFORMATION
                                                            (@employeeid,@date);";

        public const string GET_SELECTLIST_MLEAVE = @"WITH LeaveList AS
                                                    (
                                                        SELECT
                                                            A.LEAVEID          AS Id,
                                                            A.LEAVECODE        AS Code,
                                                            A.LEAVENAME        AS Name,
                                                            A.ISCARRIEDOVER    AS IsCarriedOver,
                                                            ROW_NUMBER() OVER (ORDER BY A.LEAVEID) AS RowNum
                                                        FROM MLEAVE A
                                                        WHERE A.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name,
                                                        IsCarriedOver
                                                    FROM LeaveList
                                                    WHERE
                                                        (@firstnumber = -1 AND @maxresult = -1)
                                                        OR (RowNum BETWEEN @firstnumber AND @maxresult);";
















        //        // 1️⃣ Get Employee Details with Department and Designation
        //        public const string GET_EMPLOYEE_DETAILS =
        //@"
        //SELECT 
        //    A.Employeeid AS EmployeeId,
        //    A.EmployeeCode AS EmployeeCode,
        //    A.EmployeeName AS EmployeeName,
        //    dept.DEPARTMENTCODE AS DepartmentCode,
        //    dept.DEPARTMENTNAME AS DepartmentName,
        //    gcm.GCMCODE AS DesignationCode, 
        //    gcm.GCMNAME AS DesignationName
        //FROM 
        //    MEMPLOYEE A
        //    INNER JOIN MCODEDEFINE define ON A.CODEDEFINEID = define.CODEDEFINEID
        //    INNER JOIN MDEPARTMENT dept ON A.DEPARTMENTID = dept.DEPARTMENTID
        //    INNER JOIN MGCM gcm ON A.DESIGNATIONID = gcm.GCMID
        //    INNER JOIN MORGANIZATIONUNIT ou ON A.WORKOUID = ou.OUID
        //WHERE 1=1
        //";

        //        // 2️⃣ Get Employee Permission / Timeslip Details
        //        public const string GET_EMPLOYEE_PERMISSION_DETAILS =
        //@"
        //SELECT 
        //    a.EmployeeId          AS EmployeeId,
        //    b.TIMESLIPNUMBER      AS TimeSlipNumber,
        //    b.TIMESLIPDATE        AS TimeSlipDate,
        //    b.ATTENDANCEDATE      AS AttendanceDate,
        //    b.DURATION            AS Duration,
        //    CASE b.type 
        //        WHEN 0 THEN 'Official' 
        //        WHEN 1 THEN 'Personal' 
        //        ELSE 'Other' 
        //    END AS Type,
        //    b.ONDUTYLOCATION      AS Location,
        //    b.Reason              AS Remarks,
        //    b.ApprovedById        AS ApprovedById,
        //    c.EmployeeCode        AS ApprovedByCode,
        //    c.EmployeeName        AS ApprovedByName,
        //    b.TIMESLIPSTARTTIME   AS FromTime,
        //    b.TIMESLIPENDTIME     AS ToTime,
        //    b.Status              AS Status
        //FROM 
        //    MEMPLOYEE a
        //    INNER JOIN TTimeslip b ON a.EmployeeId = b.EmployeeId
        //    LEFT JOIN MEMPLOYEE c ON b.ApprovedById = c.EmployeeId
        //    INNER JOIN MCODEDEFINE define ON a.CODEDEFINEID = define.CODEDEFINEID
        //    INNER JOIN MDEPARTMENT dept ON a.DEPARTMENTID = dept.DEPARTMENTID
        //    INNER JOIN MORGANIZATIONUNIT ou ON a.WORKOUID = ou.OUID
        //WHERE 1=1
        //    -- ✅ Period overlap logic (GB4 style)
        //    AND (
        //        @PeriodFrom IS NULL 
        //        OR b.TIMESLIPSTARTTIME < DATEADD(DAY, 1, @PeriodTo) 
        //        AND b.TIMESLIPENDTIME >= @PeriodFrom
        //    )
        //ORDER BY 
        //    a.EmployeeId, b.ATTENDANCEDATE, b.TIMESLIPNUMBER
        //";


        //        //        // 3️⃣ Get Employee Leave Credit/Debit Details
        //        //        public const string GET_EMPLOYEE_LEAVE_CREDIT_DEBIT_DETAILS =
        //        //        @"
        //        //SELECT 
        //        //    a.EmployeeId AS EmployeeId,
        //        //    b.LEAVENUMBER AS CreditDebitNumber,
        //        //    b.LEAVEDATE AS CreditDebitDate,
        //        //    MIN(c.LEAVEDATE) AS FromDate,
        //        //    MAX(c.VALIDTILL) AS ToDate,
        //        //    SUM(c.NumberofDays) AS NumberofDays,
        //        //    CASE d.BIZTRANSACTIONCLASSID 
        //        //        WHEN -1399999898 THEN 'Credit' 
        //        //        WHEN -1399999816 THEN 'Debit' 
        //        //    END AS Type,
        //        //    e.LEAVETYPE AS LeaveType,
        //        //    e.LEAVECODE AS Category,
        //        //    b.REMARKS AS Remarks,
        //        //    b.Status AS Status
        //        //FROM 
        //        //    MEMPLOYEE a
        //        //    INNER JOIN TLeaveDetail c ON a.EmployeeId = c.EmployeeId
        //        //    INNER JOIN TLeave b ON b.LeaveId = c.LeaveId
        //        //    INNER JOIN MBizTransactionType d ON b.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
        //        //    INNER JOIN MLeave e ON c.LEAVETYPEID = e.LeaveId
        //        //    INNER JOIN MCODEDEFINE define ON a.CODEDEFINEID = define.CODEDEFINEID
        //        //    INNER JOIN MDEPARTMENT dept ON a.DEPARTMENTID = dept.DEPARTMENTID
        //        //    INNER JOIN MORGANIZATIONUNIT ou ON a.WORKOUID = ou.OUID
        //        //WHERE 
        //        //    d.BIZTRANSACTIONCLASSID IN (-1399999898, -1399999816)
        //        //";

        //        //        // 4️⃣ Group By for Leave Credit/Debit
        //        //        public const string GET_EMPLOYEE_LEAVE_CREDIT_DEBIT_DETAILS_GROUP_BY =
        //        //        @"
        //        //GROUP BY
        //        //    a.EmployeeId,
        //        //    b.LEAVENUMBER,
        //        //    b.LEAVEDATE,
        //        //    CASE d.BIZTRANSACTIONCLASSID 
        //        //        WHEN -1399999898 THEN 'Credit' 
        //        //        WHEN -1399999816 THEN 'Debit' 
        //        //    END,
        //        //    e.LEAVECODE,
        //        //    b.REMARKS,
        //        //    e.LEAVETYPE,
        //        //    b.Status
        //        //";

        //        //        // 5️⃣ Get Employee Leave Request Details
        //        //        public const string GET_EMPLOYEE_LEAVE_REQUEST_DETAILS =
        //        //        @"
        //        //SELECT 
        //        //    a.EmployeeId AS EmployeeId,
        //        //    b.LEAVENUMBER AS RequestNumber,
        //        //    b.LEAVEDATE AS RequestDate,
        //        //    MIN(c.LEAVEDATE) AS FromDate,
        //        //    MAX(c.VALIDTILL) AS ToDate,
        //        //    SUM(c.NumberofDays) AS NumberofDays,
        //        //    e.LEAVETYPE AS LeaveType,
        //        //    e.LEAVECODE AS Category,
        //        //    b.REMARKS AS Remarks,
        //        //    b.Status AS Status
        //        //FROM 
        //        //    MEMPLOYEE a
        //        //    INNER JOIN TLeaveDetail c ON a.EmployeeId = c.EmployeeId
        //        //    INNER JOIN TLeave b ON b.LeaveId = c.LeaveId
        //        //    INNER JOIN MBizTransactionType d ON b.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
        //        //    INNER JOIN MLeave e ON c.LEAVETYPEID = e.LeaveId
        //        //    INNER JOIN MCODEDEFINE define ON a.CODEDEFINEID = define.CODEDEFINEID
        //        //    INNER JOIN MDEPARTMENT dept ON a.DEPARTMENTID = dept.DEPARTMENTID
        //        //    INNER JOIN MORGANIZATIONUNIT ou ON a.WORKOUID = ou.OUID
        //        //WHERE 
        //        //    d.BIZTRANSACTIONCLASSID IN (-1399999955)
        //        //";

        //        //        // 6️⃣ Group By for Leave Request
        //        //        public const string GET_EMPLOYEE_LEAVE_REQUEST_DETAILS_GROUP_BY =
        //        //        @"
        //        //GROUP BY
        //        //    a.EmployeeId,
        //        //    b.LEAVENUMBER,
        //        //    b.LEAVEDATE,
        //        //    e.LEAVECODE,
        //        //    b.REMARKS,
        //        //    e.LEAVETYPE,
        //        //    b.Status
        //        //";

        //        // 3️⃣ Get Employee Leave Credit/Debit Details (Complete Query)
        //        public const string GET_EMPLOYEE_LEAVE_CREDIT_DEBIT_DETAILS =
        //@"
        //SELECT 
        //    a.EmployeeId           AS EmployeeId,
        //    b.LEAVENUMBER          AS CreditDebitNumber,
        //    b.LEAVEDATE            AS CreditDebitDate,
        //    MIN(c.LEAVEDATE)       AS FromDate,
        //    MAX(c.VALIDTILL)       AS ToDate,
        //    SUM(c.NumberofDays)    AS NumberofDays,
        //    CASE d.BIZTRANSACTIONCLASSID 
        //        WHEN -1399999898 THEN 'Credit' 
        //        WHEN -1399999816 THEN 'Debit' 
        //    END                    AS Type,
        //    e.LEAVETYPE            AS LeaveType,
        //    e.LEAVECODE            AS Category,
        //    b.REMARKS              AS Remarks,
        //    b.Status               AS Status
        //FROM 
        //    MEMPLOYEE a
        //    INNER JOIN TLeaveDetail c        ON a.EmployeeId = c.EmployeeId
        //    INNER JOIN TLeave b              ON b.LeaveId = c.LeaveId
        //    INNER JOIN MBizTransactionType d ON b.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
        //    INNER JOIN MLeave e              ON c.LEAVETYPEID = e.LeaveId
        //    INNER JOIN MCODEDEFINE define    ON a.CODEDEFINEID = define.CODEDEFINEID
        //    INNER JOIN MDEPARTMENT dept      ON a.DEPARTMENTID = dept.DEPARTMENTID
        //    INNER JOIN MORGANIZATIONUNIT ou  ON a.WORKOUID = ou.OUID
        //WHERE 
        //    d.BIZTRANSACTIONCLASSID IN (-1399999898, -1399999816)
        //GROUP BY
        //    a.EmployeeId,
        //    b.LEAVENUMBER,
        //    b.LEAVEDATE,
        //    CASE d.BIZTRANSACTIONCLASSID 
        //        WHEN -1399999898 THEN 'Credit' 
        //        WHEN -1399999816 THEN 'Debit' 
        //    END,
        //    e.LEAVECODE,
        //    b.REMARKS,
        //    e.LEAVETYPE,
        //    b.Status
        //";

        //        // 5️⃣ Get Employee Leave Request Details (Complete Query)
        //        public const string GET_EMPLOYEE_LEAVE_REQUEST_DETAILS =
        //@"
        //SELECT 
        //    a.EmployeeId           AS EmployeeId,
        //    b.LEAVENUMBER          AS RequestNumber,
        //    b.LEAVEDATE            AS RequestDate,
        //    MIN(c.LEAVEDATE)       AS FromDate,
        //    MAX(c.VALIDTILL)       AS ToDate,
        //    SUM(c.NumberofDays)    AS NumberofDays,
        //    e.LEAVETYPE            AS LeaveType,
        //    e.LEAVECODE            AS Category,
        //    b.REMARKS              AS Remarks,
        //    b.Status               AS Status
        //FROM 
        //    MEMPLOYEE a
        //    INNER JOIN TLeaveDetail c        ON a.EmployeeId = c.EmployeeId
        //    INNER JOIN TLeave b              ON b.LeaveId = c.LeaveId
        //    INNER JOIN MBizTransactionType d ON b.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
        //    INNER JOIN MLeave e              ON c.LEAVETYPEID = e.LeaveId
        //    INNER JOIN MCODEDEFINE define    ON a.CODEDEFINEID = define.CODEDEFINEID
        //    INNER JOIN MDEPARTMENT dept      ON a.DEPARTMENTID = dept.DEPARTMENTID
        //    INNER JOIN MORGANIZATIONUNIT ou  ON a.WORKOUID = ou.OUID
        //WHERE 
        //    d.BIZTRANSACTIONCLASSID IN (-1399999955)
        //GROUP BY
        //    a.EmployeeId,
        //    b.LEAVENUMBER,
        //    b.LEAVEDATE,
        //    e.LEAVECODE,
        //    b.REMARKS,
        //    e.LEAVETYPE,
        //    b.Status
        //";



        #region 🔹 SQL Query Constants

        // 1️⃣ Get Employee Details — supports optional dynamic filters
        public const string GET_EMPLOYEE_DETAILS =
        @"
SELECT 
    A.Employeeid        AS EmployeeId,
    A.EmployeeCode      AS EmployeeCode,
    A.EmployeeName      AS EmployeeName,
    dept.DEPARTMENTCODE AS DepartmentCode,
    dept.DEPARTMENTNAME AS DepartmentName,
    gcm.GCMCODE         AS DesignationCode,
    gcm.GCMNAME         AS DesignationName
FROM 
    MEMPLOYEE A
    INNER JOIN MCODEDEFINE define   ON A.CODEDEFINEID = define.CODEDEFINEID
    INNER JOIN MDEPARTMENT dept     ON A.DEPARTMENTID = dept.DEPARTMENTID
    INNER JOIN MGCM gcm             ON A.DESIGNATIONID = gcm.GCMID
    INNER JOIN MORGANIZATIONUNIT ou ON A.WORKOUID = ou.OUID
WHERE 1=1
    AND (@EmployeeId IS NULL OR A.Employeeid   = @EmployeeId)
    AND (@OuId       IS NULL OR A.WORKOUID     = @OuId)
    AND (@DepartmentId IS NULL OR A.DEPARTMENTID = @DepartmentId)
";

        // 2️⃣ Get Employee Permission / Timeslip Details
        // 2️⃣ Get Employee Permission / Timeslip Details
        public const string GET_EMPLOYEE_PERMISSION_DETAILS =
        @"
SELECT 
    a.EmployeeId        AS EmployeeId,
    b.TIMESLIPNUMBER    AS TimeSlipNumber,
    b.TIMESLIPDATE      AS TimeSlipDate,
    b.CREATEDON         AS CreatedOn,
    b.ATTENDANCEDATE    AS AttendanceDate,
    b.DURATION          AS Duration,
    CASE 
        WHEN b.Type = 0 THEN 'Official'
        WHEN b.Type = 1 THEN 'Personal'
        ELSE 'Other'
    END                 AS Type,
    b.ONDUTYLOCATION    AS Location,
    b.Reason            AS Remarks,
    r.ReasonName        AS Reason,
    b.MODIFIEDBYID      AS ApprovedById,
    c.EmployeeCode      AS ApprovedByCode,
    c.EmployeeName      AS ApprovedByName,
    b.TIMESLIPSTARTTIME AS FromTime,
    b.TIMESLIPENDTIME   AS ToTime,
    b.Status            AS Status,
    CASE b.STATUS
        WHEN 0 THEN 'Pending'
        WHEN 1 THEN 'Approved'
        WHEN 2 THEN 'Rejected'
        ELSE 'Other'
    END                 AS RequestStatus,
    b.MODIFIEDON        AS ApprovedRejectedOn
FROM 
    MEMPLOYEE a
    INNER JOIN TTimeslip b ON a.EmployeeId = b.EmployeeId
    LEFT JOIN MEMPLOYEE c ON b.MODIFIEDBYID = c.EmployeeId
    LEFT JOIN MREASON r ON b.REASONID = r.REASONID
    INNER JOIN MCODEDEFINE define ON a.CODEDEFINEID = define.CODEDEFINEID
    INNER JOIN MDEPARTMENT dept ON a.DEPARTMENTID = dept.DEPARTMENTID
    INNER JOIN MORGANIZATIONUNIT ou ON a.WORKOUID = ou.OUID
WHERE 1=1
    AND (
        (@PeriodFrom IS NULL AND @PeriodTo IS NULL)
        OR (b.ATTENDANCEDATE >= @PeriodFrom AND b.ATTENDANCEDATE <= @PeriodTo)
    )
    AND (@EmployeeId   IS NULL OR a.EmployeeId   = @EmployeeId)
    AND (@OuId         IS NULL OR a.WORKOUID     = @OuId)
    AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId)
ORDER BY 
    a.EmployeeId, b.ATTENDANCEDATE, b.TIMESLIPNUMBER
";

        // 3️⃣ Get Employee Leave Credit/Debit Details
        // 3️⃣ Get Employee Leave Credit/Debit Details
        public const string GET_EMPLOYEE_LEAVE_CREDIT_DEBIT_DETAILS =
        @"
SELECT 
    a.EmployeeId        AS EmployeeId,
    b.LEAVENUMBER       AS CreditDebitNumber,
    b.LEAVEDATE         AS CreditDebitDate,
    MIN(c.LEAVEDATE)    AS FromDate,
    MAX(c.VALIDTILL)    AS ToDate,
    SUM(c.NumberofDays) AS NumberofDays,
    CASE d.BIZTRANSACTIONCLASSID 
        WHEN -1399999898 THEN 'Credit' 
        WHEN -1399999816 THEN 'Debit' 
    END                 AS Type,
    e.LEAVETYPE         AS LeaveType,
    e.LEAVECODE         AS Category,
    b.REMARKS           AS Remarks,
    b.Status            AS Status
FROM 
    MEMPLOYEE a
    INNER JOIN TLeaveDetail c        ON a.EmployeeId = c.EmployeeId
    INNER JOIN TLeave b              ON b.LeaveId = c.LeaveId
    INNER JOIN MBizTransactionType d ON b.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
    INNER JOIN MLeave e              ON c.LEAVETYPEID = e.LeaveId
    INNER JOIN MCODEDEFINE define    ON a.CODEDEFINEID = define.CODEDEFINEID
    INNER JOIN MDEPARTMENT dept      ON a.DEPARTMENTID = dept.DEPARTMENTID
    INNER JOIN MORGANIZATIONUNIT ou  ON a.WORKOUID = ou.OUID
WHERE 
    d.BIZTRANSACTIONCLASSID IN (-1399999898, -1399999816)
    AND (
        (@PeriodFrom IS NULL AND @PeriodTo IS NULL)
        OR (c.LEAVEDATE <= @PeriodTo AND c.VALIDTILL >= @PeriodFrom)
    )
    AND (@EmployeeId   IS NULL OR a.EmployeeId   = @EmployeeId)
    AND (@OuId         IS NULL OR a.WORKOUID     = @OuId)
    AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId)
GROUP BY
    a.EmployeeId,
    b.LEAVENUMBER,
    b.LEAVEDATE,
    CASE d.BIZTRANSACTIONCLASSID
        WHEN -1399999898 THEN 'Credit'
        WHEN -1399999816 THEN 'Debit'
    END,
    e.LEAVECODE,
    b.REMARKS,
    e.LEAVETYPE,
    b.Status
";

        // 4️⃣ Get Employee Leave Request Details
        // 4️⃣ Get Employee Leave Request Details
        public const string GET_EMPLOYEE_LEAVE_REQUEST_DETAILS =
        @"
SELECT 
    a.EmployeeId        AS EmployeeId,
    b.LEAVENUMBER       AS RequestNumber,
    b.LEAVEDATE         AS RequestDate,
    MIN(c.LEAVEDATE)    AS FromDate,
    MAX(c.VALIDTILL)    AS ToDate,
    SUM(c.NumberofDays) AS NumberofDays,
    e.LEAVETYPE         AS LeaveType,
    e.LEAVECODE         AS Category,
    b.REMARKS           AS Remarks,
    b.Status            AS Status
FROM 
    MEMPLOYEE a
    INNER JOIN TLeaveDetail c        ON a.EmployeeId = c.EmployeeId
    INNER JOIN TLeave b              ON b.LeaveId = c.LeaveId
    INNER JOIN MBizTransactionType d ON b.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
    INNER JOIN MLeave e              ON c.LEAVETYPEID = e.LeaveId
    INNER JOIN MCODEDEFINE define    ON a.CODEDEFINEID = define.CODEDEFINEID
    INNER JOIN MDEPARTMENT dept      ON a.DEPARTMENTID = dept.DEPARTMENTID
    INNER JOIN MORGANIZATIONUNIT ou  ON a.WORKOUID = ou.OUID
WHERE 
    d.BIZTRANSACTIONCLASSID IN (-1399999955)
    AND (
        (@PeriodFrom IS NULL AND @PeriodTo IS NULL)
        OR (c.LEAVEDATE <= @PeriodTo AND c.VALIDTILL >= @PeriodFrom)
    )
    AND (@EmployeeId   IS NULL OR a.EmployeeId   = @EmployeeId)
    AND (@OuId         IS NULL OR a.WORKOUID     = @OuId)
    AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId)
GROUP BY
    a.EmployeeId,
    b.LEAVENUMBER,
    b.LEAVEDATE,
    e.LEAVECODE,
    b.REMARKS,
    e.LEAVETYPE,
    b.Status
";

        // 5️⃣ Daily Attendance
        public const string GET_EMPLOYEE_DAILY_ATTENDANCE =
        @"
SELECT
    da.EMPLOYEEID                       AS EmployeeId,
    da.ATTENDANCEDATE                   AS AttendanceDate,
    s.SHIFTCODE                         AS ShiftCode,
    s.SHIFTDESCRIPTION                  AS ShiftName,
    da.INTIME                           AS InTime,
    da.OUTTIME                          AS OutTime,
    da.WORKEDHOURS                      AS WorkedHours,
    da.LATEIN                           AS LateIn,
    da.EARLYOUT                         AS EarlyOut,
    CASE
        WHEN ld.LEAVEID IS NOT NULL AND l.STATUS = 1
        THEN CASE ml2.LEAVETYPE
            WHEN 0 THEN 0   -- WorkingDay
            WHEN 1 THEN 4   -- PaidLeave
            WHEN 2 THEN 8   -- LOP
            WHEN 3 THEN 2   -- WeeklyOff
            WHEN 4 THEN 3   -- Others
            WHEN 5 THEN 3   -- GroupBased
            WHEN 6 THEN 9   -- Holiday
            WHEN 7 THEN 6   -- Compensatory Work
            WHEN 8 THEN 7   -- Compensatory Off
            WHEN 9 THEN 4   -- LTA
            ELSE da.DAYTYPE
        END
        ELSE da.DAYTYPE
    END                                 AS ActualDayType,
    COALESCE(ml2.LEAVECODE, ml.LEAVECODE) AS ActualLeaveType,
    s.SHIFTCODE                         AS ActualShift,
    da.PREVIOUSDAYTYPE                  AS PlannedDayType,
    ps.SHIFTCODE                        AS PlannedShift,
    da.REMARKS                          AS Remarks,
    CAST(da.STATUS AS NVARCHAR(10))     AS Status
FROM
    TDAILYATTENDANCE da
    INNER JOIN MEMPLOYEE a              ON da.EMPLOYEEID = a.EmployeeId
    LEFT  JOIN MSHIFT s                 ON da.SHIFTID = s.SHIFTID
    LEFT  JOIN MSHIFT ps                ON da.PREVIOUSSHIFTID = ps.SHIFTID
    LEFT  JOIN MLEAVE ml                ON da.LEAVEID = ml.LEAVEID
    LEFT  JOIN TLEAVEDETAIL ld          ON ld.EMPLOYEEID = da.EMPLOYEEID
                                       AND ld.LEAVEDATE  = da.ATTENDANCEDATE
    LEFT  JOIN TLEAVE l                 ON l.LEAVEID = ld.LEAVEID
                                       AND l.STATUS  = 1
    LEFT  JOIN MLEAVE ml2               ON ml2.LEAVEID = ld.LEAVETYPEID
    INNER JOIN MCODEDEFINE define       ON a.CODEDEFINEID = define.CODEDEFINEID
    INNER JOIN MDEPARTMENT dept         ON a.DEPARTMENTID = dept.DEPARTMENTID
    INNER JOIN MORGANIZATIONUNIT ou     ON a.WORKOUID = ou.OUID
WHERE 1=1
    AND (
        (@PeriodFrom IS NULL AND @PeriodTo IS NULL)
        OR (da.ATTENDANCEDATE >= @PeriodFrom AND da.ATTENDANCEDATE <= @PeriodTo)
    )
    AND (@EmployeeId   IS NULL OR da.EMPLOYEEID  = @EmployeeId)
    AND (@OuId         IS NULL OR a.WORKOUID     = @OuId)
    AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId)
ORDER BY
    da.EMPLOYEEID, da.ATTENDANCEDATE
";

        // 6️⃣ Leave / OnDuty Request (Day-wise)
        public const string GET_EMPLOYEE_LEAVE_ONDUTY_REQUEST =
@"
SELECT
    ld.EMPLOYEEID                           AS EmployeeId,
    l.LEAVENUMBER                           AS RequestNumber,
    CASE d.BIZTRANSACTIONCLASSID
        WHEN -1399999955 THEN 'Leave'
        WHEN -1399999900 THEN 'On Duty'
        ELSE 'Other'
    END                                     AS RequestType,
    l.CREATEDON                             AS CreatedOn,
    CASE l.STATUS
        WHEN 0 THEN 'Pending'
        WHEN 1 THEN 'Approved'
        WHEN 2 THEN 'Rejected'
        ELSE 'Other'
    END                                     AS RequestStatus,
    MIN(ld.LEAVEDATE)                       AS FromDate,
    MAX(ld.VALIDTILL)                       AS ToDate,
    ml.LEAVECODE                            AS LeaveType,
    ml.LEAVENAME                            AS LeaveName,
    r.REASONNAME                            AS Reason,
    l.REMARKS                               AS Remarks,
    apr.EMPLOYEENAME                        AS ReportingOfficer,
    app.EMPLOYEENAME                        AS ApprovedByName,
    l.MODIFIEDON                            AS ApprovedRejectedOn
FROM
    TLEAVEDETAIL ld
    INNER JOIN TLEAVE l
        ON l.LEAVEID = ld.LEAVEID
    INNER JOIN MBIZTRANSACTIONTYPE d
        ON l.BIZTRANSACTIONTYPEID = d.BIZTRANSACTIONTYPEID
    INNER JOIN MLEAVE ml
        ON ld.LEAVETYPEID = ml.LEAVEID
    INNER JOIN MEMPLOYEE a
        ON ld.EMPLOYEEID = a.EMPLOYEEID
    LEFT JOIN MEMPLOYEE apr
        ON a.REPORTINGTOEMPLOYEEID = apr.EMPLOYEEID
    LEFT JOIN MEMPLOYEE app
        ON l.MODIFIEDBYID = app.EMPLOYEEID
    LEFT JOIN MREASON r
        ON l.REASONID = r.REASONID
    INNER JOIN MCODEDEFINE define
        ON a.CODEDEFINEID = define.CODEDEFINEID
    INNER JOIN MDEPARTMENT dept
        ON a.DEPARTMENTID = dept.DEPARTMENTID
    INNER JOIN MORGANIZATIONUNIT ou
        ON a.WORKOUID = ou.OUID
WHERE
    d.BIZTRANSACTIONCLASSID IN (-1399999955, -1399999900)
    AND (
        (@PeriodFrom IS NULL AND @PeriodTo IS NULL)
        OR (ld.LEAVEDATE <= @PeriodTo AND ld.VALIDTILL >= @PeriodFrom)
    )
    AND (@EmployeeId IS NULL OR ld.EMPLOYEEID = @EmployeeId)
    AND (@OuId IS NULL OR a.WORKOUID = @OuId)
    AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId)
GROUP BY
    ld.EMPLOYEEID,
    l.LEAVENUMBER,
    l.CREATEDON,
    l.STATUS,
    d.BIZTRANSACTIONCLASSID,
    ml.LEAVECODE,
    ml.LEAVENAME,
    r.REASONNAME,
    l.REMARKS,
    apr.EMPLOYEENAME,
    app.EMPLOYEENAME,
    l.MODIFIEDON
ORDER BY
    ld.EMPLOYEEID,
    l.CREATEDON DESC
";

        // 7️⃣ Missed Punch — RECORDTYPE = 1 means missed punch request
        public const string GET_EMPLOYEE_MISSED_PUNCH =
@"
SELECT
    TRY_CAST(f.EMPLOYEEID AS INT)       AS EmployeeId,
    f.PUNCHID                           AS PunchId,
    f.EMPLOYEECODE                      AS EmployeeCode,
    f.EMPLOYEENAME                      AS EmployeeName,
    f.PUNCHATTENDANCEDATE               AS PunchAttendanceDate,
    f.PUNCHPUNCHDATE                    AS PunchPunchDate,
    f.PUNCHPUNCHTIME                    AS PunchPunchTime,
    f.PUNCHPUNCHSTATUS                  AS PunchPunchStatus,
    f.PUNCHCALINTIME                    AS PunchCalInTime,
    f.PUNCHVERIFICATIONSTATUS           AS PunchVerificationStatus,
    f.PUNCHGEOCODE                      AS PunchGeoCode,
    f.PUNCHPUNCHTYPE                    AS PunchPunchType,
    f.PUNCHADDITIONALINFORMATION        AS PunchAdditionalInformation,
    f.PUNCHSITEID                       AS PunchSiteId,
    f.PUNCHSITENAME                     AS PunchSiteName,
    f.DEVICEID                          AS DeviceId,
    f.DEVICENAME                        AS DeviceName,
    f.PUNCHREASONID                     AS PunchReasonId,
    r.REASONNAME                        AS PunchReasonName,
    f.PUNCHRECORDTYPE                   AS PunchRecordType,
    f.PUNCHGEODISTANCE                  AS PunchGeoDistance,
    f.GEOFENCING                        AS GeoFencing,
    f.FUNCTIONREPORTINGTOMAILID         AS FunctionReportingToMailId,
    f.FUNCTIONREPORTINGTOMOBILENUMBER   AS FunctionReportingToMobileNumber,
    f.ADMINREPORTINGTOMAILID            AS AdminReportingToMailId,
    f.ADMINREPORTINGTOMOBILENUMBER      AS AdminReportingToMobileNumber
FROM ACTIVITI..FN_GET_PUNCH_POSTDATA(@DatabaseName, @Type) f
LEFT JOIN MREASON r ON f.PUNCHREASONID = r.REASONID
WHERE
    (@EmployeeId IS NULL OR TRY_CAST(f.EMPLOYEEID AS INT) = @EmployeeId)
    AND (@PeriodFrom IS NULL OR f.PUNCHATTENDANCEDATE >= @PeriodFrom)
    AND (@PeriodTo   IS NULL OR f.PUNCHATTENDANCEDATE <= @PeriodTo)
ORDER BY
    f.EMPLOYEEID,
    f.PUNCHATTENDANCEDATE,
    f.PUNCHPUNCHTIME;
";

        // 8️⃣ Punch Details — RECORDTYPE = 0 means actual punch record
        public const string GET_EMPLOYEE_PUNCH_DETAILS =
        @"
SELECT
    p.EMPLOYEEID        AS EmployeeId,
    p.ATTENDANCEDATE    AS AttendanceDate,
    p.PUNCHDATE         AS PunchDate,
    p.PUNCHTIME         AS PunchTime,
    CASE p.PUNCHSTATUS
        WHEN 0 THEN 'IN'
        WHEN 1 THEN 'OUT'
        ELSE 'Other'
    END                 AS Status,
    mr.REASONNAME           AS Reason,
    p.ADDITIONALINFORMATION AS Remarks,
    p.REASONID          AS ReasonId,
    mr.REASONCODE       AS ReasonCode
FROM TPUNCH p
INNER JOIN MEMPLOYEE a ON p.EMPLOYEEID = a.EMPLOYEEID
LEFT JOIN MREASON mr ON p.REASONID = mr.REASONID
WHERE
    p.RECORDTYPE IN (0,1)
    AND p.ATTENDANCEDATE >= ISNULL(@PeriodFrom, p.ATTENDANCEDATE)
    AND p.ATTENDANCEDATE <= ISNULL(@PeriodTo, p.ATTENDANCEDATE)
    AND (@EmployeeId   IS NULL OR p.EMPLOYEEID   = @EmployeeId)
    AND (@OuId         IS NULL OR a.WORKOUID     = @OuId)
    AND (@DepartmentId IS NULL OR a.DEPARTMENTID = @DepartmentId)
ORDER BY
    p.EMPLOYEEID,
    p.ATTENDANCEDATE,
    p.PUNCHTIME;
";

        #endregion
    }
}