using FrameworkDAL.DTO.SysJobRun;
using FrameworkDAL.Query.SysJobRun;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.SysJobRun
{
    public class SysJobRunDAL : ISysJobRunDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public SysJobRunDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        // See FrameworkDAL.CustomCode.SystemJob.SystemJobDAL.NormalizeDate — the frontend's "no
        // date filter" default sends an empty string, which binds to DateTime.MinValue rather
        // than null, and SQL Server's DATETIME floor (1/1/1753) throws SqlDateTime overflow on it.
        private static DateTime? NormalizeDate(DateTime? value) =>
            value == DateTime.MinValue ? null : value;

        // See SystemJobDAL.AsUtc — same GETUTCDATE()-but-Kind-Unspecified issue: without this,
        // the browser renders StartedAt/EndedAt as local time instead of UTC.
        private static void StampUtcTimestamps(IEnumerable<SysJobRunDTO> rows)
        {
            foreach (var row in rows)
            {
                row.StartedAt = DateTime.SpecifyKind(row.StartedAt, DateTimeKind.Utc);
                if (row.EndedAt.HasValue)
                    row.EndedAt = DateTime.SpecifyKind(row.EndedAt.Value, DateTimeKind.Utc);
            }
        }

        public async Task<string> GetSysJobRun(int SysJobRunId, LoginDTO LoginDTO)
        {
            string json = "";
            try
            {
                string sql = SysJobRunQB.GET_SYSJOBRUN;
                var parameters = new { SysJobRunId, TenantId = LoginDTO.ClientId };
                IEnumerable<SysJobRunDTO> result = await _queryExecutor.QueryAsync<SysJobRunDTO>(LoginDTO, sql, parameters);
                json = JsonConvert.SerializeObject(result);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveSysJobRun(SysJobRunDTO SysJobRunDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SysJobRunQB.SAVE_SYSJOBRUN;
                return await _queryExecutor.ExecuteAsync(LoginDTO, sql, SysJobRunDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateSysJobRun(SysJobRunDTO SysJobRunDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SysJobRunQB.UPDATE_SYSJOBRUN;
                return await _queryExecutor.ExecuteAsync(LoginDTO, sql, SysJobRunDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeleteSysJobRun(int SysJobRunId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SysJobRunQB.DELETE_SYSJOBRUN;
                int result = await _queryExecutor.ExecuteAsync(LoginDTO, sql, new { SysJobRunId, TenantId = LoginDTO.ClientId });

                if (result > 0)
                {
                    return Result<string>.Success("SysJobRun deleted successfully.");
                }
                else
                {
                    return Result<string>.Failure("SysJobRun not found or could not be deleted.");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting SysJobRun", ex);
            }
        }

        public async Task<string> GetListSysJobRun(int? SysJobId, int? Status, DateTime? DateFrom, DateTime? DateTo, int FirstNumber, int MaxResult, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SysJobRunQB.GET_LIST_SYSJOBRUN;
                var parameters = new { TenantId = LoginDTO.ClientId, SysJobId, Status, DateFrom = NormalizeDate(DateFrom), DateTo = NormalizeDate(DateTo), FirstNumber, MaxResult };
                IEnumerable<SysJobRunDTO> result = await _queryExecutor.QueryAsync<SysJobRunDTO>(LoginDTO, sql, parameters);
                StampUtcTimestamps(result);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> GetListCountSysJobRun(int? SysJobId, int? Status, DateTime? DateFrom, DateTime? DateTo, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SysJobRunQB.GET_LIST_COUNT_SYSJOBRUN;
                var parameters = new { TenantId = LoginDTO.ClientId, SysJobId, Status, DateFrom = NormalizeDate(DateFrom), DateTo = NormalizeDate(DateTo) };
                return await _queryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}