using FrameworkDAL.DTO.SystemJob;
using FrameworkDAL.Query.SystemJob;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.SystemJob
{
    public class SystemJobDAL : ISystemJobDAL
    {
        // Mirrors GB5Shared.ActionProcessor.WebhookEndpointResolver's own placeholder regex —
        // that resolver is the only thing that ever calls these URLs, and it resolves {BaseURI}
        // only. Used here to detect any OTHER named placeholder (e.g. {Type}/{FirstNumber}) that
        // would be left as literal text in the URL if a job were actually submitted.
        private static readonly Regex _placeholder = new(@"\{[^}]+\}", RegexOptions.Compiled);

        private readonly IQueryExecutor _QueryExecutor;

        public SystemJobDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        // The frontend's "no date filter" default sends an empty string, which model binding
        // resolves to DateTime.MinValue (year 0001) rather than null — SQL Server's DATETIME
        // floor is 1/1/1753, so binding that value as a parameter throws SqlDateTime overflow
        // even though the SQL's own "@DateFrom IS NULL" guard is written to handle "no filter".
        private static DateTime? NormalizeDate(DateTime? value) =>
            value == DateTime.MinValue ? null : value;

        // Every SysJob timestamp is written with GETUTCDATE(), but Dapper materializes it as
        // DateTimeKind.Unspecified — serialized without a 'Z'/offset, so the browser's DatePipe
        // renders it as local time instead of UTC, showing the wrong clock time entirely (not
        // just a formatting nit). Stamping Kind=Utc here is what makes Newtonsoft emit the 'Z'.
        private static DateTime AsUtc(DateTime value) =>
            DateTime.SpecifyKind(value, DateTimeKind.Utc);

        private static void StampUtcTimestamps(IEnumerable<SystemJobListDTO> rows)
        {
            foreach (var row in rows)
            {
                row.SubmittedOn = AsUtc(row.SubmittedOn);
                row.StartTime   = AsUtc(row.StartTime);
                row.EndTime     = AsUtc(row.EndTime);
                row.CancelledAt = AsUtc(row.CancelledAt);
            }
        }

        public async Task<string> CheckJobEligibility(int WebServiceId, LoginDTO LoginDTO)
        {
            try
            {
                var result = new SysJobEligibilityDTO { Eligible = false };

                // Real WebServiceIds in this schema, like most other IDs here, are large
                // negative sentinel-style numbers — only -1/0 mean "unset". A "<= 0" guard
                // would reject every real, correctly-wired row (caught live testing this).
                if (WebServiceId == -1 || WebServiceId == 0)
                {
                    result.Reason = "This report has no background-execution route configured yet.";
                    return JsonConvert.SerializeObject(result);
                }

                var rows = await _QueryExecutor.QueryAsync<MWebServiceRouteDTO>(
                    LoginDTO, SystemJobQB.CHECK_JOB_ELIGIBILITY, new { WebServiceId });
                var route = rows.FirstOrDefault();

                // Mirror IWebhookEndpointResolver's own priority — SECONDURITEMPLATE, when set,
                // is what actually gets called (several real reports are wired that way, with
                // URITEMPLATE left holding the legacy GB4 route), so either being usable is
                // enough for the job to actually run.
                bool IsUsableTemplate(string template) =>
                    !string.IsNullOrWhiteSpace(template)
                    && !string.Equals(template, "NONE", StringComparison.OrdinalIgnoreCase);

                if (route is null || (!IsUsableTemplate(route.UriTemplate) && !IsUsableTemplate(route.SecondUriTemplate)))
                {
                    result.Reason = "This report has no background-execution route configured yet.";
                    return JsonConvert.SerializeObject(result);
                }

                // IWebhookEndpointResolver only ever resolves the literal {BaseURI} token — it has
                // no concept of named placeholders like {Type}/{FirstNumber}/{MaxResult}. Some
                // real reports (e.g. MM pivot/register reports whose GB5-native SECONDURITEMPLATE
                // is a GET query-string template, not a plain path) still carry these, and calling
                // them today would send a URL with the literal "{Type}" text still in it. Until
                // that resolver supports per-name substitution, treat any such route as ineligible
                // rather than let the job silently fail or return wrong data.
                var effectiveTemplate = IsUsableTemplate(route.SecondUriTemplate)
                    ? route.SecondUriTemplate
                    : route.UriTemplate;
                var withoutBaseUri = effectiveTemplate.Replace("{BaseURI}", "", StringComparison.OrdinalIgnoreCase);
                if (_placeholder.IsMatch(withoutBaseUri))
                {
                    result.Reason = "This report's background-execution route needs per-request parameters that aren't supported for scheduled/background runs yet.";
                    return JsonConvert.SerializeObject(result);
                }

                result.Eligible = true;
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSystemJob(int SysJobId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = SystemJobQB.GET_SYSTEMJOB;
                var parameters = new { SysJobId, SysJobTenantId = LoginDTO.ClientId };
                IEnumerable<SystemJobDTO> SystemJobDTOs = await _QueryExecutor.QueryAsync<SystemJobDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(SystemJobDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListSystemJob(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = SystemJobQB.GET_SELECTLIST_SYSTEMJOB;
                var Result = await _QueryExecutor.QueryAsync<SystemJobPicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveSystemJob(SystemJobDTO SystemJobDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = SystemJobQB.SAVE_SYSTEMJOB;
                return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, Sql, SystemJobDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateSystemJob(SystemJobDTO SystemJobDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string sql = SystemJobQB.UPDATE_SYSTEMJOB;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, SystemJobDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeleteSystemJob(int SysJobId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SystemJobQB.DELETE_SYSTEMJOB;
                var parameter = new { SysJobId, SysJobTenantId = LoginDTO.ClientId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameter);

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

        public async Task<string> GetListSystemJob(int? RunStatus, string SysJobType, string QueueName, DateTime? DateFrom, DateTime? DateTo, string SearchText, int FirstNumber, int MaxResult, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SystemJobQB.GET_LIST_SYSTEMJOB;
                var parameters = new
                {
                    SysJobTenantId = LoginDTO.ClientId,
                    UserId         = LoginDTO.UserId,
                    RunStatus,
                    SysJobType,
                    QueueName,
                    DateFrom = NormalizeDate(DateFrom),
                    DateTo   = NormalizeDate(DateTo),
                    SearchText,
                    FirstNumber,
                    MaxResult
                };
                IEnumerable<SystemJobListDTO> result = await _QueryExecutor.QueryAsync<SystemJobListDTO>(LoginDTO, sql, parameters);
                StampUtcTimestamps(result);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> GetListCountSystemJob(int? RunStatus, string SysJobType, string QueueName, DateTime? DateFrom, DateTime? DateTo, string SearchText, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SystemJobQB.GET_LIST_COUNT_SYSTEMJOB;
                var parameters = new
                {
                    SysJobTenantId = LoginDTO.ClientId,
                    UserId         = LoginDTO.UserId,
                    RunStatus,
                    SysJobType,
                    QueueName,
                    DateFrom = NormalizeDate(DateFrom),
                    DateTo   = NormalizeDate(DateTo),
                    SearchText
                };
                return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetJobSummary(DateTime? DateFrom, DateTime? DateTo, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SystemJobQB.GET_JOB_SUMMARY;
                var parameters = new { SysJobTenantId = LoginDTO.ClientId, DateFrom = NormalizeDate(DateFrom), DateTo = NormalizeDate(DateTo) };
                var result = await _QueryExecutor.QuerySingleAsync<SystemJobSummaryDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> CancelSystemJob(int SysJobId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = SystemJobQB.CANCEL_JOB;
                var parameters = new { SysJobId, SysJobTenantId = LoginDTO.ClientId, SysJobModifiedById = LoginDTO.UserId };
                int rows = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, parameters);

                if (rows > 0)
                    return Result<string>.Success("Job cancelled successfully.");
                else
                    return Result<string>.Failure("Job not found or cannot be cancelled (must be Pending or InProgress).");
            }
            catch (Exception ex)
            {
                throw new Exception("Error while cancelling SystemJob", ex);
            }
        }
    }
}