namespace AdminDAL.Query.LocationTrack
{
    public static class LocationTrackQB
    {
        // GB4 parity: LocationTrackQueryBuilder.GET_LOCATION_TRACK_MAP (Type == 0)
        public const string GET_LOCATION_TRACK_MAP = @"
SELECT
    a.userid     as UserId,
    a.deviceid   as DeviceId,
    b.usercode   as UserCode,
    b.username   as UserName,
    c.employeecode as EmployeeCode,
    c.employeename as EmployeeName,
    d.devicecode as DeviceCode,
    d.devicename as DeviceName,
    a.timestamp  as TimeStamp,
    a.geocode    as GeoCode
FROM
    tlocationtrack a
    LEFT OUTER JOIN muser b on (a.userid = b.userid)
    LEFT OUTER JOIN memployee c on (a.userid = c.employeeid)
    LEFT OUTER JOIN mdevice d on (a.deviceid = d.deviceid)
WHERE 1=1";

        // GB4 parity: LocationTrackQueryBuilder.GET_POSITION_MAP (Type == 1) — latest known position per user/device
        public const string GET_POSITION_MAP = @"
SELECT
    a.userid     as UserId,
    a.deviceid   as DeviceId,
    b.usercode   as UserCode,
    b.username   as UserName,
    c.employeecode as EmployeeCode,
    c.employeename as EmployeeName,
    d.devicecode as DeviceCode,
    d.devicename as DeviceName,
    a.timestamp  as TimeStamp,
    a.geocode    as GeoCode
FROM
    (SELECT userid, deviceid, max(timestamp) timestamp FROM tlocationtrack GROUP BY userid, deviceid) sub
    LEFT OUTER JOIN tlocationtrack a on (a.userid = sub.userid and a.deviceid = sub.deviceid and a.timestamp = sub.timestamp)
    LEFT OUTER JOIN muser b on (a.userid = b.userid)
    LEFT OUTER JOIN memployee c on (a.userid = c.employeeid)
    LEFT OUTER JOIN mdevice d on (a.deviceid = d.deviceid)
WHERE 1=1";

        // GB4 parity: LocationTrackQueryBuilder.GET_PUNCH_MAP (Type == 2, SQL Server)
        public const string GET_PUNCH_MAP = @"
SELECT
    a.employeeid as EmployeeId,
    b.employeecode as EmployeeCode, b.employeename as EmployeeName,
    a.attendancedate as AttendanceDate, a.punchdate as PunchDate, a.verificationstatus as VerificationStatus,
    CASE WHEN a.verificationstatus = 0 THEN 'Yes' ELSE 'No' END as VerificationStatusName,
    CASE WHEN a.punchstatus = 0 THEN 'In' ELSE 'Out' END as PunchStatusName,
    a.punchstatus as PunchStatus, a.punchtime as PunchTime,
    a.GEOCODE as GeoCode,
    CASE WHEN a.PUNCHTIME % 60 = 0 THEN CAST(a.PUNCHTIME / 60 AS varchar) + ':00'
         ELSE CAST(a.PUNCHTIME / 60 AS varchar) + ':' +
              CASE WHEN (a.PUNCHTIME % 60) < 10 THEN '0' + CAST(a.PUNCHTIME % 60 AS varchar)
                   ELSE CAST(a.PUNCHTIME % 60 AS varchar) END
    END as CallPunchTime
FROM
    tpunch a
    LEFT OUTER JOIN memployee b on (a.employeeid = b.employeeid)
WHERE 1=1";

        // GB4 parity: LocationTrackQueryBuilder.GET_PUNCH_LOAD (Type == 3) — current-day punch snapshot per employee
        public const string GET_PUNCH_LOAD = @"
SELECT
    ISNULL(a.PUNCHID,0)                                AS PunchId,
    EMP.EMPLOYEEID                                     AS EmployeeId,
    EMP.EMPLOYEECODE                                   AS EmployeeCode,
    EMP.EMPLOYEENAME                                   AS EmployeeName,
    ISNULL(A.ATTENDANCEDATE, CAST(GETDATE() AS DATE))  AS AttendanceDate,
    ISNULL(A.PUNCHDATE, CAST(GETDATE() AS DATE))       AS PunchDate,
    A.VERIFICATIONSTATUS                               AS VerificationStatus,
    CASE WHEN ISNULL(A.VERIFICATIONSTATUS, 0) = 0 THEN 'YES' ELSE 'NO' END AS VerificationStatusName,
    CASE
        WHEN DATEDIFF(MINUTE, DATEADD(minute, FirstPunch.punchtime, FirstPunch.punchdate), GETDATE()) BETWEEN 0 AND (att.SHIFTMINUTES + 480) OR att.SHIFTID IS NULL THEN
            CASE WHEN ISNULL(A.PUNCHSTATUS, 0) = 0 THEN 'IN' ELSE 'OUT' END
        ELSE 'OUT'
    END                                                 AS PunchStatusName,
    CASE
        WHEN DATEDIFF(MINUTE, DATEADD(minute, FirstPunch.punchtime, FirstPunch.punchdate), GETDATE()) BETWEEN 0 AND (att.SHIFTMINUTES + 480) OR att.SHIFTID IS NULL THEN
            ISNULL(A.PUNCHSTATUS, 0)
        ELSE 1
    END                                                 AS PunchStatus,
    ISNULL(A.PUNCHTIME, 0)                              AS PunchTime,
    ISNULL(A.GEOCODE, '')                               AS GeoCode,
    CASE
        WHEN A.PUNCHID IS NULL THEN '00:00'
        ELSE
            CASE WHEN A.PUNCHTIME % 60 = 0 THEN CAST(A.PUNCHTIME / 60 AS VARCHAR) + ':00'
                 ELSE CAST(A.PUNCHTIME / 60 AS VARCHAR) + ':' +
                      CASE WHEN (A.PUNCHTIME % 60) < 10 THEN '0' + CAST(A.PUNCHTIME % 60 AS VARCHAR)
                           ELSE CAST(A.PUNCHTIME % 60 AS VARCHAR) END
            END
    END                                                 AS CallPunchTime,
    CASE a.SITETYPE WHEN 0 THEN a.GEOCODE WHEN 1 THEN ISNULL(pblocation.PARTYBRANCHCODE,a.GEOCODE) WHEN 2 THEN ISNULL(location.LOCATIONCODE,a.GEOCODE) WHEN 3 THEN ISNULL(addlocation.ADDRESSLINE1,a.GEOCODE) ELSE a.GEOCODE END AS LocatoionCode,
    CASE a.SITETYPE WHEN 0 THEN a.GEOCODE WHEN 1 THEN ISNULL(pblocation.PARTYBRANCHNAME,a.GEOCODE) WHEN 2 THEN ISNULL(location.LOCATIONNAME,a.GEOCODE) WHEN 3 THEN ISNULL(addlocation.ADDRESSLINE1,a.GEOCODE) ELSE a.GEOCODE END AS LocatoionName
FROM
    MEMPLOYEE EMP
    LEFT OUTER JOIN TPUNCH A ON (A.EMPLOYEEID = EMP.EMPLOYEEID)
    OUTER APPLY (SELECT * FROM MPARTYBRANCH WHERE PARTYBRANCHID = a.SITEID AND a.SITETYPE = 1) pblocation
    OUTER APPLY (SELECT * FROM MLOCATION WHERE LOCATIONID = a.SITEID AND a.SITETYPE = 2) location
    OUTER APPLY (SELECT * FROM MADDRESS WHERE ADDRESSID = a.SITEID AND a.SITETYPE = 3) addlocation
    CROSS APPLY (SELECT MAX(PUNCHID) PUNCHID FROM TPUNCH WHERE EMPLOYEEID = A.EMPLOYEEID AND PUNCHTYPE = 0) VV
    CROSS APPLY (
        SELECT a.*
        FROM TPUNCH a,
             (SELECT MIN(PUNCHID) PUNCHID
              FROM TPUNCH a,
                   (SELECT ATTENDANCEDATE, EMPLOYEEID
                    FROM TPUNCH A,
                         (SELECT MAX(PUNCHID) PUNCHID FROM TPUNCH WHERE EMPLOYEEID = A.EMPLOYEEID AND PUNCHTYPE = 0) SUB
                    WHERE 1 = 1 AND a.punchid = SUB.punchid) sub
              WHERE sub.ATTENDANCEDATE = a.ATTENDANCEDATE AND sub.EMPLOYEEID = a.EMPLOYEEID AND A.PUNCHTYPE = 0) sub
        WHERE a.PUNCHID = sub.punchid
    ) FirstPunch
    OUTER APPLY (
        SELECT b.SHIFTID, b.STARTTIME, b.ENDTIME, b.SHIFTMINUTES
        FROM TDAILYATTENDANCE a, MSHIFT b
        WHERE a.ATTENDANCEDATE = CAST(GETDATE() AS DATE) AND a.EMPLOYEEID = emp.EMPLOYEEId AND a.SHIFTID = b.SHIFTID
    ) att
WHERE 1 = 1
    AND A.PUNCHID = VV.PUNCHID";

        // GB4 parity: LocationTrackQueryBuilder.GET_PUNCH_LOAD_MOBILE (Type == 4) —
        // same as GET_PUNCH_LOAD but pinned to a caller-supplied @attendancedate instead of GETDATE().
        public const string GET_PUNCH_LOAD_MOBILE = @"
SELECT
    EMP.EMPLOYEEID                                              AS EmployeeId,
    EMP.EMPLOYEECODE                                            AS EmployeeCode,
    EMP.EMPLOYEENAME                                            AS EmployeeName,
    ISNULL(A.ATTENDANCEDATE, CAST(@attendancedate AS DATE))     AS AttendanceDate,
    ISNULL(A.PUNCHDATE, CAST(@attendancedate AS DATE))          AS PunchDate,
    A.VERIFICATIONSTATUS                                        AS VerificationStatus,
    CASE WHEN ISNULL(A.VERIFICATIONSTATUS, 0) = 0 THEN 'YES' ELSE 'NO' END AS VerificationStatusName,
    CASE
        WHEN DATEDIFF(MINUTE, DATEADD(minute, FirstPunch.punchtime, FirstPunch.punchdate), @attendancedate) BETWEEN 0 AND (att.SHIFTMINUTES + 480) OR att.SHIFTID IS NULL THEN
            CASE WHEN ISNULL(A.PUNCHSTATUS, 0) = 0 THEN 'IN' ELSE 'OUT' END
        ELSE 'OUT'
    END                                                          AS PunchStatusName,
    CASE
        WHEN DATEDIFF(MINUTE, DATEADD(minute, FirstPunch.punchtime, FirstPunch.punchdate), @attendancedate) BETWEEN 0 AND (att.SHIFTMINUTES + 480) OR att.SHIFTID IS NULL THEN
            ISNULL(A.PUNCHSTATUS, 0)
        ELSE 1
    END                                                          AS PunchStatus,
    ISNULL(A.PUNCHTIME, 0)                                       AS PunchTime,
    ISNULL(A.GEOCODE, '')                                        AS GeoCode,
    CASE
        WHEN A.PUNCHID IS NULL THEN '00:00'
        ELSE
            CASE WHEN A.PUNCHTIME % 60 = 0 THEN CAST(A.PUNCHTIME / 60 AS VARCHAR) + ':00'
                 ELSE CAST(A.PUNCHTIME / 60 AS VARCHAR) + ':' +
                      CASE WHEN (A.PUNCHTIME % 60) < 10 THEN '0' + CAST(A.PUNCHTIME % 60 AS VARCHAR)
                           ELSE CAST(A.PUNCHTIME % 60 AS VARCHAR) END
            END
    END                                                          AS CallPunchTime
FROM
    MEMPLOYEE EMP
    LEFT OUTER JOIN TPUNCH A ON (A.EMPLOYEEID = EMP.EMPLOYEEID)
    CROSS APPLY (SELECT MAX(PUNCHID) PUNCHID FROM TPUNCH WHERE EMPLOYEEID = A.EMPLOYEEID AND ATTENDANCEDATE = @attendancedate AND PUNCHTYPE = 0) VV
    CROSS APPLY (
        SELECT a.*
        FROM TPUNCH a,
             (SELECT MIN(PUNCHID) PUNCHID
              FROM TPUNCH a,
                   (SELECT ATTENDANCEDATE, EMPLOYEEID
                    FROM TPUNCH A,
                         (SELECT MAX(PUNCHID) PUNCHID FROM TPUNCH WHERE EMPLOYEEID = A.EMPLOYEEID AND ATTENDANCEDATE = @attendancedate AND PUNCHTYPE = 0) SUB
                    WHERE 1 = 1 AND a.punchid = SUB.punchid) sub
              WHERE sub.ATTENDANCEDATE = a.ATTENDANCEDATE AND sub.EMPLOYEEID = a.EMPLOYEEID AND A.PUNCHTYPE = 0) sub
        WHERE a.PUNCHID = sub.punchid
    ) FirstPunch
    OUTER APPLY (
        SELECT b.SHIFTID, b.STARTTIME, b.ENDTIME, b.SHIFTMINUTES
        FROM TDAILYATTENDANCE a, MSHIFT b
        WHERE a.ATTENDANCEDATE = CAST(@attendancedate AS DATE) AND a.EMPLOYEEID = emp.EMPLOYEEId AND a.SHIFTID = b.SHIFTID
    ) att
WHERE 1 = 1
    AND A.PUNCHID = VV.PUNCHID";

        // GB4 parity: LocationTrackQueryBuilder.GET_LOCATION_BASED_ON_GEO_CODE.
        // SQL Server spatial function (geography::Point / STDistance) — kept verbatim, not dialect-abstracted.
        // GB5 hardening: GeoCode ("lat,lng") is parsed server-side once in C# (see LocationTrackDAL)
        // and bound as two decimal parameters instead of GB4's raw string-substitution into the SQL text.
        public const string GET_LOCATION_BASED_ON_GEO_CODE = @"
SELECT TOP (@numberofrecord)
    zz.LOCATIONID       as LocationId,
    zz.LOCATIONCODE     as LocationCode,
    zz.LOCATIONNAME     as LocationName,
    zz.GEOCODE          as GeoCode,
    zz.distanceinMtr    as DistanceInMeter
FROM
    (
        SELECT a.LOCATIONID,
               a.LOCATIONCODE,
               a.LOCATIONNAME,
               a.GEOCODE,
               a.geolocation.STDistance(geography::Point(@lat, @lng, 4326)) distanceinMtr
        FROM   MLOCATION a
        WHERE  1 = 1
               AND ABS(a.geolocation.STDistance(geography::Point(@lat, @lng, 4326))) BETWEEN 0 AND @distancemeter
               AND LOCATIONID <> -1
        UNION ALL
        SELECT ADDRESSID,
               CASE a.OBJECTTYPEID WHEN -1899999858 THEN b.PARTYNAME WHEN -1899999839 THEN c.PARTYBRANCHSHORTNAME ELSE '*' END,
               A.ADDRESSLINE2,
               a.GEOCODE,
               a.geolocation.STDistance(geography::Point(@lat, @lng, 4326)) distanceinMtr
        FROM   MADDRESS a
               LEFT OUTER JOIN MPARTY b on (a.OBJECTTYPEID = -1899999858 and a.OBJECTID = b.PARTYID)
               LEFT OUTER JOIN MPARTYBRANCH c on (a.OBJECTTYPEID = -1899999839 and a.OBJECTID = c.PARTYBRANCHID)
        WHERE  1 = 1
               AND a.OBJECTTYPEID IN (-1899999858, -1899999839)
               AND ABS(a.geolocation.STDistance(geography::Point(@lat, @lng, 4326))) BETWEEN 0 AND @distancemeter
               AND a.ADDRESSID <> -1
    ) zz
WHERE 1 = 1
ORDER BY zz.distanceinMtr ASC";

        // GB5 addition: dashboard "nearby locations" count, used by the advanced endpoint
        // to report how many candidate rows exist inside the wider search radius before capping to @numberofrecord.
        public const string GET_LOCATION_BASED_ON_GEO_CODE_COUNT = @"
SELECT COUNT(1) FROM
(
    SELECT a.LOCATIONID
    FROM   MLOCATION a
    WHERE  1 = 1
           AND ABS(a.geolocation.STDistance(geography::Point(@lat, @lng, 4326))) BETWEEN 0 AND @distancemeter
           AND LOCATIONID <> -1
    UNION ALL
    SELECT ADDRESSID
    FROM   MADDRESS a
    WHERE  1 = 1
           AND a.OBJECTTYPEID IN (-1899999858, -1899999839)
           AND ABS(a.geolocation.STDistance(geography::Point(@lat, @lng, 4326))) BETWEEN 0 AND @distancemeter
           AND a.ADDRESSID <> -1
) zz";
    }
}