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

namespace PayRollDAL.Query.Punch
{
    public class PunchQB
    {
        public const string SAVE_MISSED_PUNCH = @"INSERT INTO TPUNCH
                                                (
                                                    EMPLOYEEID,
                                                    ATTENDANCEDATE,
                                                    PUNCHDATE,
                                                    PUNCHTIME,
                                                    PUNCHSTATUS,
                                                    BIOMETRIC,
                                                    VERIFICATIONSTATUS,
                                                    GEOCODE,
                                                    PUNCHTYPE,
                                                    ADDITIONALINFORMATION,
                                                    SITETYPE,
                                                    SITEID,
                                                    GEOLOCATION,
                                                    DEVICEID,
                                                    ORIGINALPUNCHTIME,
                                                    REASONID,
                                                    RECORDTYPE,
                                                    GEODISTANCE
                                                )
                                                VALUES
                                                (
                                                    @EmployeeId,
                                                    @PunchAttendanceDate,
                                                    @PunchPunchDate,
                                                    @PunchPunchTime,
                                                    @PunchPunchStatus,
                                                    @PunchBiometric,
                                                    @PunchVerificationStatus,
                                                    @PunchGeoCode,
                                                    @PunchPunchType,
                                                    @PunchAdditionalInformation,
                                                    @PunchSiteType,
                                                    @PunchSiteId,
                                                    @PunchGeolocation,
                                                    @DeviceId,
                                                    @PunchOriginalPunchTime,
                                                    @PunchReasonId,
                                                    @PunchRecordType,
                                                    @PunchGeoDistance
                                                );";


        public const string UPDATE_MISSED_PUNCH = @"UPDATE TPUNCH SET
                                                    EMPLOYEEID              = @EmployeeId,
                                                    ATTENDANCEDATE          = @PunchAttendanceDate,
                                                    PUNCHDATE               = @PunchPunchDate,
                                                    PUNCHTIME               = @PunchPunchTime,
                                                    PUNCHSTATUS             = @PunchPunchStatus,
                                                    BIOMETRIC               = @PunchBiometric,
                                                    VERIFICATIONSTATUS      = @PunchVerificationStatus,
                                                    GEOCODE                 = @PunchGeoCode,
                                                    PUNCHTYPE               = @PunchPunchType,
                                                    ADDITIONALINFORMATION   = @PunchAdditionalInformation,
                                                    SITETYPE                = @PunchSiteType,
                                                    SITEID                  = @PunchSiteId,
                                                    GEOLOCATION             = @PunchGeolocation,
                                                    DEVICEID                = @DeviceId,
                                                    ORIGINALPUNCHTIME       = @PunchOriginalPunchTime,
                                                    REASONID                = @PunchReasonId,
                                                    RECORDTYPE              = @PunchRecordType,
                                                    GEODISTANCE             = @PunchGeoDistance
                                                WHERE PUNCHID               = @PunchId;";

        #region MissingPunchInsert / MissingPunchInsertNew

        public const string GET_EMPLOYEE_TEMPORARYCARDNUMBER =
            @"SELECT TEMPORARYCARDNUMBER FROM MEMPLOYEE WHERE EMPLOYEEID = @EmployeeId";

        // MissingPunchInsert (legacy): pulls raw device punches from the configured
        // "Punch Download" database, {0} is an optional "AND Employeecode = @EmployeeCode" filter.
        public const string GET_DEVICELOG_FROM_PUNCH_DB =
            @"SELECT ID as Id, Employeecode as EmployeeCode, LogDateTime as LogDateTime,
                     Direction as Direction, serialno as SerialNo, MODIFIEDON as ModifiedOn, ITEMCODE as ItemCode
              FROM   devicelogs
              WHERE 1=1
              AND LogDateTime >= @FromDate and LogDateTime <= @ToDate
              {0}";

        public const string DELETE_DEVICELOG_FROM_PUNCH_DB =
            @"DELETE FROM devicelogs WHERE ID = @Id";

        public const string DELETE_TPUNCH_BEFORE_INSERTING =
            @"DELETE FROM TPUNCH WHERE EMPLOYEEID = @EmployeeId AND PUNCHDATE >= @FromDate AND PUNCHDATE <= @ToDate";

        public const string INSERT_DEVICELOG_FROM_PUNCH_DB =
            @"SET IDENTITY_INSERT devicelogs ON;
              INSERT INTO devicelogs (ID, EmployeeCode, LogDateTime, Direction, serialno, MODIFIEDON, ITEMCODE)
              VALUES (@Id, @EmployeeCode, @LogDateTime, @Direction, @SerialNo, @ModifiedOn, @ItemCode);
              SET IDENTITY_INSERT devicelogs OFF;";

        // MissingPunchInsertNew: resolves the replicated devicelog(s) linked-server table
        // reference for the client's tenant from the replication configuration tables.
        // {0} = target table name (DEVICELOG or DEVICELOGS, resolved per client in the BLL).
        public const string FIND_PUNCH_SERVER_CONFIG =
            @"SELECT DISTINCT CASE
                  WHEN A.FROMSERVERID = A.TOSERVERID THEN TODB.CONNECTIONNAME + '..{0}'
                  ELSE '[' + B.SERVERIP + '].PUNCHDB.[DBO].{0}'
              END AS ConnectionName
              FROM TREPLICATE A, GB4SYSTEM..MSERVER B, GB4SYSTEM..MSERVERCONFIG C,
                   GB4SYSTEM..MSERVERCONFIG TODB, MDATASET D, MDATASETDETAIL E
              WHERE 1=1
              AND A.FROMSERVERID = B.SERVERID
              AND A.FROMDATABASEID = C.SERVERCONFIGID
              AND A.TODATABASEID = TODB.SERVERCONFIGID
              AND A.DATASETID = D.DATASETID
              AND D.DATASETID = E.DATASETID
              AND E.DBOBJECTID in (-1099836029, -1099761676)";

        // Inserts device-log rows that exist on the linked-server table ({1} = ConnectionName)
        // but not yet locally, straight into the live {0} (DEVICELOG/DEVICELOGS) table.
        public const string MISSING_PUNCH_INSERT_IN_DEVICELOG =
            @"SET IDENTITY_INSERT {0} ON;

              INSERT INTO {0} (ID, EMPLOYEECODE, LOGDATETIME, DIRECTION, SERIALNO, MODIFIEDON, ITEMCODE)
              SELECT A.ID, A.EMPLOYEECODE, A.LOGDATETIME, A.DIRECTION, A.SERIALNO, A.MODIFIEDON, A.ITEMCODE
              FROM {1} A
              LEFT OUTER JOIN {0} B ON (A.ID = B.ID),
              MEMPLOYEE C
              WHERE B.ID IS NULL
              AND A.EMPLOYEECODE COLLATE DATABASE_DEFAULT = C.TEMPORARYCARDNUMBER COLLATE DATABASE_DEFAULT
              AND A.LOGDATETIME >= @FromDate
              AND A.LOGDATETIME <= @ToDate
              AND (C.EMPLOYEEID = @EmployeeId OR @EmployeeId = -1);

              SET IDENTITY_INSERT {0} OFF;";

        // Stages, into devicelogs2, whichever {0}-table punches for the date range (and
        // optional employee, {1}) still have no matching TPUNCH row. Single batch so the
        // #temp table stays valid across all three statements on one connection.
        public const string STAGE_MISSING_PUNCHES_TO_DEVICELOGS2 =
            @"select a.*, b.EMPLOYEEID, b.TEMPORARYCARDNUMBER, b.EMPLOYEENAME,
                     cast(a.Logdatetime as date) as PunchDate,
                     DATEDIFF(MINUTE, CAST(A.LOGDATETIME AS DATE), LOGDATETIME) AS PUNCHTIME
              into #temp
              from {0} a, MEMPLOYEE b
              where a.Employeecode = b.TEMPORARYCARDNUMBER
              and cast(a.Logdatetime as date) >= @FromDate
              and cast(a.Logdatetime as date) <= @ToDate
              {1};

              delete from devicelogs2;

              insert into devicelogs2 (Employeecode, LogDateTime, Direction, serialno, ITEMCODE)
              select Employeecode, LogDateTime, Direction, serialno, ITEMCODE
              from #temp a
              left outer join TPUNCH b On (a.EMPLOYEEID=b.EMPLOYEEID and a.PunchDate=b.PUNCHDATE and a.PUNCHTIME=b.PUNCHTIME)
              where 1=1
              and b.PUNCHID is null
              order by a.EMPLOYEEID, a.PunchDate;";

        #endregion
    }
}