using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;

namespace PayRollDAL.CustomeCode.ReassignAttendanceDate
{
    public class ReassignAttendanceDateDAL : IReassignAttendanceDateDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public ReassignAttendanceDateDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        public async Task<string> GetSelectListReassignAttendanceDate(
            int OuId, int EmployeeId, DateTime FromDate, DateTime ToDate,
            LoginDTO LoginDTO, bool IsCount = false)
        {
            if (IsCount)
                return JsonConvert.SerializeObject(new List<object>());

            var parameters = new { OUID = OuId, EMPLOYEEID = EmployeeId, FROMDATE = FromDate, TODATE = ToDate };

            // TPUNCH PK  : PUNCHID  (not TPUNCHID)
            // DEVICELOGS.EMPLOYEECODE = MEMPLOYEE.TEMPORARYCARDNUMBER (not EMPLOYEECODE)
            // DIRECTION / SERIALNO / ITEMCODE do not exist in TPUNCH — inserted as NULL
            // All 3 statements in one batch so IDENTITY_INSERT scope is preserved on the same connection.

            string batchSql;

            if (OuId == -1 && EmployeeId == -1)
            {
                // Format 1: all OUs, all employees
                batchSql = @"
DELETE DL
FROM   DEVICELOGS DL
WHERE  DL.LOGDATETIME >= @FROMDATE
  AND  DL.LOGDATETIME <= @TODATE;

DELETE TP
FROM   TPUNCH TP
WHERE  TP.PUNCHDATE >= @FROMDATE
  AND  TP.PUNCHDATE <= @TODATE
  AND  TP.ORIGINALPUNCHTIME = 0
  AND  TP.REASONID = -1
  AND  TP.VERIFICATIONSTATUS = 0;

SET IDENTITY_INSERT DEVICELOGS ON;

INSERT INTO DEVICELOGS (ID, EMPLOYEECODE, LOGDATETIME, DIRECTION, SERIALNO, MODIFIEDON, ITEMCODE)
SELECT TP.PUNCHID,
       E.TEMPORARYCARDNUMBER,
       TP.PUNCHDATE,
       CAST(TP.PUNCHSTATUS AS VARCHAR(10)),
       '',
       GETDATE(),
       ''
FROM   TPUNCH TP
INNER  JOIN MEMPLOYEE E ON E.EMPLOYEEID = TP.EMPLOYEEID
WHERE  TP.PUNCHDATE >= @FROMDATE
  AND  TP.PUNCHDATE <= @TODATE
  AND  NOT EXISTS (
           SELECT 1 FROM DEVICELOGS DL2 WHERE DL2.ID = TP.PUNCHID
       );

SET IDENTITY_INSERT DEVICELOGS OFF;";
            }
            else if (OuId != -1 && EmployeeId == -1)
            {
                // Format 3: one OU, all its employees
                batchSql = @"
DELETE DL
FROM   DEVICELOGS DL
INNER  JOIN MEMPLOYEE E ON E.TEMPORARYCARDNUMBER = DL.EMPLOYEECODE COLLATE DATABASE_DEFAULT
WHERE  E.WORKOUID = @OUID
  AND  DL.LOGDATETIME >= @FROMDATE
  AND  DL.LOGDATETIME <= @TODATE;

DELETE TP
FROM   TPUNCH TP
INNER  JOIN MEMPLOYEE E ON E.EMPLOYEEID = TP.EMPLOYEEID
WHERE  E.WORKOUID = @OUID
  AND  TP.PUNCHDATE >= @FROMDATE
  AND  TP.PUNCHDATE <= @TODATE
  AND  TP.ORIGINALPUNCHTIME = 0
  AND  TP.REASONID = -1
  AND  TP.VERIFICATIONSTATUS = 0;

SET IDENTITY_INSERT DEVICELOGS ON;

INSERT INTO DEVICELOGS (ID, EMPLOYEECODE, LOGDATETIME, DIRECTION, SERIALNO, MODIFIEDON, ITEMCODE)
SELECT TP.PUNCHID,
       E.TEMPORARYCARDNUMBER,
       TP.PUNCHDATE,
       CAST(TP.PUNCHSTATUS AS VARCHAR(10)),
       '',
       GETDATE(),
       ''
FROM   TPUNCH TP
INNER  JOIN MEMPLOYEE E ON E.EMPLOYEEID = TP.EMPLOYEEID
WHERE  E.WORKOUID = @OUID
  AND  TP.PUNCHDATE >= @FROMDATE
  AND  TP.PUNCHDATE <= @TODATE
  AND  NOT EXISTS (
           SELECT 1 FROM DEVICELOGS DL2 WHERE DL2.ID = TP.PUNCHID
       );

SET IDENTITY_INSERT DEVICELOGS OFF;";
            }
            else
            {
                // Format 2: one specific employee
                batchSql = @"
DELETE DL
FROM   DEVICELOGS DL
INNER  JOIN MEMPLOYEE E ON E.TEMPORARYCARDNUMBER = DL.EMPLOYEECODE COLLATE DATABASE_DEFAULT
WHERE  E.EMPLOYEEID = @EMPLOYEEID
  AND  DL.LOGDATETIME >= @FROMDATE
  AND  DL.LOGDATETIME <= @TODATE;

DELETE TP
FROM   TPUNCH TP
WHERE  TP.EMPLOYEEID = @EMPLOYEEID
  AND  TP.PUNCHDATE >= @FROMDATE
  AND  TP.PUNCHDATE <= @TODATE
  AND  TP.ORIGINALPUNCHTIME = 0
  AND  TP.REASONID = -1
  AND  TP.VERIFICATIONSTATUS = 0;

SET IDENTITY_INSERT DEVICELOGS ON;

INSERT INTO DEVICELOGS (ID, EMPLOYEECODE, LOGDATETIME, DIRECTION, SERIALNO, MODIFIEDON, ITEMCODE)
SELECT TP.PUNCHID,
       E.TEMPORARYCARDNUMBER,
       TP.PUNCHDATE,
       CAST(TP.PUNCHSTATUS AS VARCHAR(10)),
       '',
       GETDATE(),
       ''
FROM   TPUNCH TP
INNER  JOIN MEMPLOYEE E ON E.EMPLOYEEID = TP.EMPLOYEEID
WHERE  TP.EMPLOYEEID = @EMPLOYEEID
  AND  TP.PUNCHDATE >= @FROMDATE
  AND  TP.PUNCHDATE <= @TODATE
  AND  NOT EXISTS (
           SELECT 1 FROM DEVICELOGS DL2 WHERE DL2.ID = TP.PUNCHID
       );

SET IDENTITY_INSERT DEVICELOGS OFF;";
            }

            await _QueryExecutor.ExecuteAsync(LoginDTO, batchSql, parameters);

            return JsonConvert.SerializeObject(new List<object> { new { Success = true } });
        }
    }
}