using OI.Metrology.Server.Models;
using OI.Metrology.Shared.DataModels;
using OI.Metrology.Shared.Models;
using OI.Metrology.Shared.Models.Stateless;
using OI.Metrology.Shared.Repositories;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.Json;

namespace OI.Metrology.Server.Repository;

public class InfinityQSV4Repository : IInfinityQSV4Repository
{

    private record Record(string? Color, string Text);

    private readonly string _MockRoot;
    private readonly string _RepositoryName;
    private readonly AppSettings _AppSettings;
    private readonly IHttpClientFactory _HttpClientFactory;
    private readonly IDbConnectionFactory _DBConnectionFactory;
    private readonly IFileShareRepository _FileShareRepository;
    private readonly string _OpenInsightApplicationProgrammingInterface;

    public InfinityQSV4Repository(AppSettings appSettings, IDbConnectionFactory dbConnectionFactory, IHttpClientFactory httpClientFactory, IFileShareRepository fileShareRepository)
    {
        _AppSettings = appSettings;
        _MockRoot = appSettings.MockRoot;
        _HttpClientFactory = httpClientFactory;
        _DBConnectionFactory = dbConnectionFactory;
        _FileShareRepository = fileShareRepository;
        _RepositoryName = nameof(InfinityQSV4Repository)[..^10];
        _OpenInsightApplicationProgrammingInterface = appSettings.OpenInsightApplicationProgrammingInterface;
    }

    string IInfinityQSV4Repository.GetCommandText(string subGroupId)
    { // cSpell:disable
        List<string> results = [];
        if (string.IsNullOrEmpty(subGroupId))
            throw new ArgumentException(null, nameof(subGroupId));
        results.Add(" select ");
        results.Add("       sd.f_sgrp sd_sgrp, ");
        results.Add("       sd.f_tsno sd_tsno, ");
        results.Add("       dd.f_dsgp dd_dsgp, ");
        results.Add("       dg.f_name gd_name, ");
        results.Add("       dd.f_name dd_name ");
        results.Add(" from [SPCEPIWORLD].[dbo].[SGRP_DSC] sd ");
        results.Add(" join [SPCEPIWORLD].[dbo].[DESC_DAT] dd ");
        results.Add("   on sd.f_dsgp = dd.f_dsgp ");
        results.Add("  and sd.f_desc = dd.f_desc ");
        results.Add(" join [SPCEPIWORLD].[dbo].[DESC_GRP] dg ");
        results.Add("   on dd.f_dsgp = dg.f_dsgp ");
        results.Add($" where sd.f_sgrp = {subGroupId} ");
        results.Add(" for json path ");
        return string.Join(' ', results);
    } // cSpell:enable

    string IInfinityQSV4Repository.GetCommandText(string? subGroupId, string? process, string? job, string? part, string? lot, string? dateTime)
    { // cSpell:disable
        List<string> results = [];
        const string dateTimeFormat = "yyyy-MM-dd HH:mm:ss";
        if (!string.IsNullOrEmpty(dateTime) && (dateTime.Contains('-') || dateTime.Contains(' ') || dateTime.Contains(':')) && dateTime.Length != dateTimeFormat.Length)
            throw new ArgumentException(null, nameof(dateTime));
        results.Add(" select case when iq.sl_loos is null then 0 else iq.sl_loos end + ");
        results.Add("        case when iq.sl_uoos is null then 0 else iq.sl_uoos end + ");
        results.Add("        iq.ev_count as iq_sum, ");
        results.Add("        iq.sl_aflag, ");
        results.Add("        iq.sl_loos, ");
        results.Add("        iq.sl_uoos, ");
        results.Add("        iq.se_sgrp, ");
        results.Add("        iq.se_sgtm, ");
        results.Add("        iq.se_tsno, ");
        results.Add("        iq.td_test, ");
        results.Add("        iq.pr_name, ");
        results.Add("        iq.jd_name, ");
        results.Add("        iq.pl_name, ");
        results.Add("        iq.pd_name, ");
        results.Add("        iq.td_name, ");
        results.Add("        iq.se_val, ");
        results.Add("        iq.sl_eflag, ");
        results.Add("        iq.sl_scal, ");
        results.Add("        iq.sl_sls, ");
        results.Add("        iq.sl_usl ");
        results.Add(" from ( ");
        results.Add(" select ");
        results.Add("       se.f_sgrp se_sgrp, ");
        results.Add("       se.f_sgtm se_sgtm, ");
        results.Add("       se.f_tsno se_tsno, ");
        results.Add("       se.f_val se_val, ");
        results.Add("       pr.f_name pr_name, ");
        results.Add("       jd.f_name jd_name, ");
        results.Add("       pl.f_name pl_name, ");
        results.Add("       pd.f_name pd_name, ");
        results.Add("       td.f_test td_test, ");
        results.Add("       td.f_name td_name, ");
        results.Add("       sl.f_eflag sl_eflag, ");
        results.Add("       sl.f_aflag sl_aflag, ");
        results.Add("       sl.f_scal sl_scal, ");
        results.Add("       sl.f_lsl sl_sls, ");
        results.Add("       sl.f_usl sl_usl, ");
        results.Add("       case when sl.f_aflag is null or sl.f_aflag = 0 then null else ");
        results.Add("         case when round(se.f_val, sl.F_scal, 1) < sl.f_lsl then 1 else 0 end ");
        results.Add("       end as sl_loos, ");
        results.Add("       case when sl.f_aflag is null or sl.f_aflag = 0 then null else ");
        results.Add("         case when round(se.f_val, sl.F_scal, 1) > sl.f_usl then 1 else 0 end ");
        results.Add("       end as sl_uoos, ");
        results.Add("       (select count(ev.f_evnt) ");
        results.Add("       from [spcepiworld].[dbo].[evnt_inf] ev ");
        results.Add("       where ev.f_prcs = pr.f_prcs ");
        results.Add("         and ev.f_part = pd.f_part ");
        results.Add("         and ev.f_sgtm = se.f_sgtm ");
        results.Add("       ) ev_count ");
        results.Add(" from [spcepiworld].[dbo].[sgrp_ext] se ");
        results.Add(" join [spcepiworld].[dbo].[prcs_dat] pr ");
        results.Add("   on se.f_prcs = pr.f_prcs ");
        results.Add(" join [spcepiworld].[dbo].[job_dat] jd ");
        results.Add("   on se.f_job = jd.f_job ");
        results.Add(" join [spcepiworld].[dbo].[part_lot] pl ");
        results.Add("   on se.f_lot = pl.f_lot ");
        results.Add(" join [spcepiworld].[dbo].[part_dat] pd ");
        results.Add("   on se.f_part = pd.f_part ");
        results.Add(" join [spcepiworld].[dbo].[test_dat] td ");
        results.Add("   on se.f_test = td.f_test ");
        results.Add(" left join [spcepiworld].[dbo].[spec_lim] sl ");
        results.Add("   on se.f_part = sl.f_part ");
        results.Add("  and se.f_test = sl.f_test ");
        results.Add(" where se.f_flag = 0 ");
        results.Add("   and (sl.f_prcs is null or se.f_prcs = sl.f_prcs or sl.f_prcs = 0) ");
        if (!string.IsNullOrEmpty(subGroupId))
            results.Add($"   and se.f_sgrp = {subGroupId.Split(" ")[0]} ");
        if (!string.IsNullOrEmpty(process))
            results.Add($"   and pr.f_name = '{process}' ");
        if (!string.IsNullOrEmpty(part))
            results.Add($"   and pd.f_name = '{part}' ");
        if (!string.IsNullOrEmpty(job))
            results.Add($"   and jd.f_name = '{job}' ");
        if (!string.IsNullOrEmpty(lot))
            results.Add($"   and pl.f_name = '{lot}' ");
        if (!string.IsNullOrEmpty(dateTime) && (dateTime.Contains('-') || dateTime.Contains(' ') || dateTime.Contains(':')))
            results.Add($"   and dateadd(HH, -7, (dateadd(SS, convert(bigint, se.f_sgtm), '19700101'))) = '{dateTime}' ");
        results.Add(" ) as iq ");
        results.Add(" order by iq.sl_loos + iq.sl_uoos + iq.ev_count desc, ");
        results.Add("          iq.sl_aflag desc, ");
        results.Add("          iq.se_sgrp, ");
        results.Add("          iq.se_tsno, ");
        results.Add("          iq.td_test ");
        results.Add("   for json path ");
        return string.Join(' ', results);
    } // cSpell:enable

    private JsonElement[] GetAllReactorsAsJsonElementElement()
    {
        JsonElement[]? results;
        HttpClient httpClient = _HttpClientFactory.CreateClient();
        Task<string> task = httpClient.GetStringAsync($"{_OpenInsightApplicationProgrammingInterface}/reactors");
        task.Wait();
        JsonElement? jsonElement = JsonSerializer.Deserialize<JsonElement>(task.Result);
        if (jsonElement is null)
            throw new NullReferenceException(nameof(jsonElement));
        string json = jsonElement.Value.EnumerateObject().First().Value.ToString();
        results = JsonSerializer.Deserialize<JsonElement[]>(json);
        if (results is null)
            throw new NullReferenceException(nameof(results));
        return results;
    }

    private static StringBuilder GetForJsonPath(IDbConnectionFactory dbConnectionFactory, string commandText, bool useIqsConnection)
    {
        StringBuilder stringBuilder = new();
        File.WriteAllText("../../.sql", commandText);
        using DbConnection dbConnection = dbConnectionFactory.GetDbConnection(useIqsConnection);
        DbCommand dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = commandText;
        DbDataReader dbDataReader = dbCommand.ExecuteReader(CommandBehavior.SequentialAccess);
        while (dbDataReader.Read())
            _ = stringBuilder.Append(dbDataReader.GetString(0));
        return stringBuilder;
    }

    private void GetOI(InfinityQSV4 infinityQSV4)
    {
        IInfinityQSV4Repository infinityQSV4Repository = this;
        if (string.IsNullOrEmpty(infinityQSV4.Part))
            throw new ArgumentException(nameof(infinityQSV4.Part));
        string json = infinityQSV4Repository.GetProductionSpecification(infinityQSV4.Part);
        File.WriteAllText("../../.json", json);
        ProdSpecRoot? prodSpec = JsonSerializer.Deserialize(json, ProdSpecRootSourceGenerationContext.Default.ProdSpecRoot);
        if (prodSpec is null)
        {
            if (prodSpec is null)
            { }
        }
    }

    private static List<string> Convert(int[] night)
    {
        List<string> results = new();
        foreach (int reactor in night)
            results.Add(reactor.ToString());
        return results;
    }

    private ReadOnlyDictionary<int, Reactor> GetReactorsMatchingType(string? reactTypeFilter)
    {
        Dictionary<int, Reactor> results = new();
        string json;
        Reactor? reactor;
        JsonElement[]? jsonElements = GetAllReactorsAsJsonElementElement();
        foreach (JsonElement jsonElement in jsonElements)
        {
            json = jsonElement.EnumerateObject().First().Value.ToString();
            if (reactTypeFilter is not null && !json.Contains(reactTypeFilter))
                continue;
            try
            { reactor = JsonSerializer.Deserialize(json, ReactorSourceGenerationContext.Default.Reactor); }
            catch (Exception) { reactor = null; }
            if (reactor is null || (reactTypeFilter is not null && reactor.ReactType != reactTypeFilter))
                continue;
            results.Add(reactor.ReactorNo, reactor);
        }
        return new(results);
    }

    private static InfinityQSV4 GetInfinityQSV4(IDbConnectionFactory dbConnectionFactory, IInfinityQSV4Repository infinityQSV4Repository, string subGroupId)
    {
        InfinityQSV4 result;
        string commandText = infinityQSV4Repository.GetCommandText(subGroupId, process: string.Empty, job: string.Empty, part: string.Empty, lot: string.Empty, dateTime: string.Empty);
        StringBuilder stringBuilder = GetForJsonPath(dbConnectionFactory, commandText, useIqsConnection: false);
        InfinityQSV4[]? results = stringBuilder.Length == 0 ? Array.Empty<InfinityQSV4>() : JsonSerializer.Deserialize(stringBuilder.ToString(), ResultInfinityQSV4SourceGenerationContext.Default.InfinityQSV4Array); // , new JsonSerializerOptions { PropertyNameCaseInsensitive = true }
        if (results is null)
            throw new NullReferenceException(nameof(results));
        result = results.First();
        return result;
    }

    Result<InfinityQSV4[]> IInfinityQSV4Repository.GetData(string subGroupId)
    {
        Result<InfinityQSV4[]>? result;
        if (!string.IsNullOrEmpty(_MockRoot))
        {
            string json = File.ReadAllText(Path.Combine(string.Concat(AppContext.BaseDirectory, _MockRoot), $"{_RepositoryName}-{nameof(IInfinityQSV4Repository.GetData)}.json"));
            result = JsonSerializer.Deserialize(json, ResultInfinityQSV4SourceGenerationContext.Default.ResultInfinityQSV4Array);
            if (result is null)
                throw new NullReferenceException(nameof(result));
        }
        else
        {
            IInfinityQSV4Repository infinityQSV4Repository = this;
            InfinityQSV4 infinityQSV4 = GetInfinityQSV4(_DBConnectionFactory, infinityQSV4Repository, subGroupId);
            GetOI(infinityQSV4);
            string commandText = infinityQSV4Repository.GetCommandText(subGroupId, process: infinityQSV4.Process, job: infinityQSV4.Job, part: infinityQSV4.Part, lot: infinityQSV4.Lot, dateTime: string.Concat(infinityQSV4.SubGroupDateTime));
            StringBuilder stringBuilder = GetForJsonPath(_DBConnectionFactory, commandText, useIqsConnection: false);
            InfinityQSV4[]? results = stringBuilder.Length == 0 ? Array.Empty<InfinityQSV4>() : JsonSerializer.Deserialize(stringBuilder.ToString(), ResultInfinityQSV4SourceGenerationContext.Default.InfinityQSV4Array); // , new JsonSerializerOptions { PropertyNameCaseInsensitive = true }
            if (results is null)
                throw new NullReferenceException(nameof(results));
            result = new()
            {
                Results = results,
                TotalRows = results.Length,
            };
        }
        return result;
    }

    Result<InfinityQSDescriptorV4[]> IInfinityQSV4Repository.GetDescriptors(string subGroupId)
    {
        Result<InfinityQSDescriptorV4[]>? result;
        if (!string.IsNullOrEmpty(_MockRoot))
        {
            string json = File.ReadAllText(Path.Combine(string.Concat(AppContext.BaseDirectory, _MockRoot), $"{_RepositoryName}-{nameof(IInfinityQSV4Repository.GetDescriptors)}.json"));
            result = JsonSerializer.Deserialize(json, ResultInfinityQSDescriptorV4SourceGenerationContext.Default.ResultInfinityQSDescriptorV4Array);
            if (result is null)
                throw new NullReferenceException(nameof(result));
        }
        else
        {
            IInfinityQSV4Repository infinityQSV4Repository = this;
            string commandText = infinityQSV4Repository.GetCommandText(subGroupId);
            StringBuilder stringBuilder = GetForJsonPath(_DBConnectionFactory, commandText, useIqsConnection: false);
            InfinityQSDescriptorV4[]? results = stringBuilder.Length == 0 ? Array.Empty<InfinityQSDescriptorV4>() : JsonSerializer.Deserialize(stringBuilder.ToString(), ResultInfinityQSDescriptorV4SourceGenerationContext.Default.InfinityQSDescriptorV4Array); // , new JsonSerializerOptions { PropertyNameCaseInsensitive = true }
            if (results is null)
                throw new NullReferenceException(nameof(results));
            result = new()
            {
                Results = results,
                TotalRows = results.Length,
            };
        }
        return result;
    }

    string IInfinityQSV4Repository.GetCommandText(InfinityQSV4 infinityQSV4)
    { // cSpell:disable
        List<string> results = [];
        if (string.IsNullOrEmpty(infinityQSV4.Process))
            throw new ArgumentException(nameof(infinityQSV4.Process));
        if (string.IsNullOrEmpty(infinityQSV4.Part))
            throw new ArgumentException(nameof(infinityQSV4.Part));
        results.Add(" select ");
        results.Add(" ev.f_evnt [ev_evnt], ");
        results.Add(" ev.f_sgtm [ev_sgtm], ");
        results.Add(" dateadd(HH, -7, (dateadd(SS, convert(bigint, ev.f_sgtm), '19700101'))) [ev_utc7], ");
        results.Add(" pr.f_name [pr_name], ");
        results.Add(" pd.f_name [pd_name], ");
        results.Add(" td.f_test [td_test], ");
        results.Add(" td.f_name [td_name], ");
        results.Add(" ev.f_name [ev_name] ");
        results.Add(" from [spcepiworld].[dbo].[evnt_inf] ev ");
        results.Add(" join [spcepiworld].[dbo].[prcs_dat] pr ");
        results.Add(" on ev.f_prcs = pr.f_prcs ");
        results.Add(" join [spcepiworld].[dbo].[part_dat] pd ");
        results.Add(" on ev.f_part = pd.f_part ");
        results.Add(" join [spcepiworld].[dbo].[test_dat] td ");
        results.Add(" on ev.f_test = td.f_test ");
        results.Add($" where pr.f_name = '{infinityQSV4.Process}' ");
        results.Add($" and pd.f_name = '{infinityQSV4.Part}' ");
        results.Add($" and ev.f_sgtm = {infinityQSV4.SubGroupDateTime} ");
        results.Add(" for json path ");
        return string.Join(' ', results);
    } // cSpell:enable

    Result<InfinityQSV4[]> IInfinityQSV4Repository.GetHeader(string subGroupId)
    {
        Result<InfinityQSV4[]>? result;
        if (!string.IsNullOrEmpty(_MockRoot))
        {
            string json = File.ReadAllText(Path.Combine(string.Concat(AppContext.BaseDirectory, _MockRoot), $"{_RepositoryName}-{nameof(IInfinityQSV4Repository.GetHeader)}.json"));
            result = JsonSerializer.Deserialize(json, ResultInfinityQSV4SourceGenerationContext.Default.ResultInfinityQSV4Array);
            if (result is null)
                throw new NullReferenceException(nameof(result));
        }
        else
        {
            IInfinityQSV4Repository infinityQSV4Repository = this;
            InfinityQSV4 infinityQSV4 = GetInfinityQSV4(_DBConnectionFactory, infinityQSV4Repository, subGroupId);
            InfinityQSV4[] results = new InfinityQSV4[] { infinityQSV4 };
            result = new()
            {
                Results = results,
                TotalRows = results.Length,
            };
        }
        return result;
    }

    string IInfinityQSV4Repository.GetCommandText(string process, string? part)
    { // cSpell:disable
        List<string> results = [];
        if (string.IsNullOrEmpty(process))
            throw new ArgumentException(null, nameof(process));
        if (string.IsNullOrEmpty(part))
            throw new ArgumentException(null, nameof(part));
        results.Add(" select [f_mean] as ProcessMean, ");
        results.Add("        [f_sp] as ProcessSigma ");
        results.Add(" from [spcepiworld].[dbo].[test_dat] test ");
        results.Add(" join [spcepiworld].[dbo].[ctrl_lim] ctrl ");
        results.Add("   on test.f_test = ctrl.f_test ");
        results.Add("   and test.f_tsgp = 1104848523 /* Product Data */ ");
        results.Add(" join [spcepiworld].[dbo].[part_dat] part ");
        results.Add("   on part.f_part = ctrl.f_part ");
        results.Add("  and ctrl.f_test = 1125073605 /* Average Sum of Defects */ ");
        results.Add(" join [spcepiworld].[dbo].[prcs_dat] process ");
        results.Add("   on process.f_prcs = ctrl.f_prcs ");
        results.Add(" where test.f_name = 'Average Sum of Defects' ");
        results.Add($"       and process.f_name = '{process}' ");
        results.Add($"       and part.f_name = '{part}' ");
        results.Add(" for json path; ");
        return string.Join(' ', results);
    } // cSpell:enable

    string IInfinityQSV4Repository.GetProductDataAverageSumOfDefectsProcessMeanProcessSigma(string process, string? recipe)
    {
        StringBuilder result;
        if (!string.IsNullOrEmpty(_MockRoot))
        {
            string json = File.ReadAllText(Path.Combine(string.Concat(AppContext.BaseDirectory, _MockRoot), $"{_RepositoryName}-{nameof(IInfinityQSV4Repository.GetProductDataAverageSumOfDefectsProcessMeanProcessSigma)}.json"));
            result = new(json);
        }
        else
        {
            IInfinityQSV4Repository infinityQSV4Repository = this;
            string commandText = infinityQSV4Repository.GetCommandText(process, recipe);
            result = GetForJsonPath(_DBConnectionFactory, commandText, useIqsConnection: false);
            if (result.Length == 0)
                result = new("{}");
        }
        return result.ToString();
    }

    string IInfinityQSV4Repository.GetCommandText(List<string> eppReactorNumbers)
    { // cSpell:disable
        List<string> results = [];
        results.Add(" select se.f_sgrp, ");
        results.Add("        dateadd(HH, -7, (dateadd(SS, convert(bigint, se.f_sgrp), '19700101'))) date_time, ");
        results.Add("        iq.pr_name, ");
        results.Add("        iq.pd_name, ");
        results.Add("        max(case ");
        results.Add("         when td.f_test = 1104769646 ");
        results.Add("         then se.f_val ");
        results.Add("         else null ");
        results.Add("         end) as iq_value, ");
        results.Add("        max(case ");
        results.Add("         when td.f_test = 1312288843 ");
        results.Add("         then se.f_val else null ");
        results.Add("         end) as iq_temp_offset_percent ");
        results.Add(" from ( ");
        results.Add(" select ");
        results.Add("       max(se.f_sgrp) se_max_sgrp, ");
        results.Add("       se.f_test se_test, ");
        results.Add("       pr.f_name pr_name, ");
        results.Add("       pd.f_name pd_name ");
        results.Add(" from [spcepiworld].[dbo].[sgrp_ext] se ");
        results.Add(" join [spcepiworld].[dbo].[prcs_dat] pr ");
        results.Add("   on se.f_prcs = pr.f_prcs ");
        results.Add(" join [spcepiworld].[dbo].[part_dat] pd ");
        results.Add("   on se.f_part = pd.f_part ");
        results.Add(" where se.f_flag = 0 ");
        results.Add($"   and pr.f_name in ({string.Join(',', eppReactorNumbers)}) ");
        results.Add("   and pd.f_name = '1090 - Full Load' ");
        results.Add("   and se.f_test in (1104769646, 1312288843) ");
        results.Add(" group by se.f_test, ");
        results.Add("          pr.f_name, ");
        results.Add("          pd.f_name ");
        results.Add(" ) as iq ");
        results.Add(" join [spcepiworld].[dbo].[sgrp_ext] se ");
        results.Add("   on iq.se_max_sgrp = se.f_sgrp ");
        results.Add(" join [spcepiworld].[dbo].[test_dat] td ");
        results.Add("   on iq.se_test = td.f_test ");
        results.Add("  and se.f_test = td.f_test ");
        results.Add(" where se.f_flag = 0 ");
        results.Add("   and td.f_test in (1104769646, 1312288843) ");
        results.Add(" group by se.f_sgrp, ");
        results.Add("          iq.pr_name, ");
        results.Add("          iq.pd_name ");
        results.Add(" order by iq.pr_name ");
        results.Add(" for json path; ");
        return string.Join(' ', results);
    } // cSpell:enable

    List<string[]> IInfinityQSV4Repository.GetEpiProTempVerificationRows(int[] night)
    {
        List<string[]>? results;
        List<string> eppReactorNumbers = new();
        List<string> nightSiftReactors = Convert(night);
        ReadOnlyDictionary<int, Reactor> eppReactors = GetReactorsMatchingType("EPP");
        foreach (KeyValuePair<int, Reactor> keyValuePair in eppReactors)
            eppReactorNumbers.Add($"'{keyValuePair.Key}'");
        if (!string.IsNullOrEmpty(_MockRoot))
        {
            string json = File.ReadAllText(Path.Combine(string.Concat(AppContext.BaseDirectory, _MockRoot), $"{_RepositoryName}-{nameof(IInfinityQSV4Repository.GetEpiProTempVerificationRows)}.json"));
            results = JsonSerializer.Deserialize<List<string[]>>(json);
            if (results is null)
                throw new NullReferenceException(nameof(results));
        }
        else
        {
            bool found;
            results = new();
            int loadedCount;
            Reactor? reactor;
            string loadedRDS;
            int reactorNumber;
            TimeSpan timeSpan;
            List<string> columns;
            List<int> used = new();
            List<int> dayShiftOrder = new();
            long ticks = DateTime.Now.Ticks;
            List<int> nightShiftOrder = new();
            InfinityQS1090FullLoad infinityQS1090FullLoad;
            IInfinityQSV4Repository infinityQSV4Repository = this;
            string commandText = infinityQSV4Repository.GetCommandText(eppReactorNumbers);
            StringBuilder stringBuilder = GetForJsonPath(_DBConnectionFactory, commandText, useIqsConnection: false);
            InfinityQS1090FullLoad[]? infinityQS1090FullLoads = stringBuilder.Length == 0 ? Array.Empty<InfinityQS1090FullLoad>() : JsonSerializer.Deserialize(stringBuilder.ToString(), InfinityQS1090FullLoadArraySourceGenerationContext.Default.InfinityQS1090FullLoadArray);
            if (infinityQS1090FullLoads is null)
                throw new NullReferenceException(nameof(infinityQS1090FullLoads));
            for (int i = 0; i < infinityQS1090FullLoads.Length; i++)
            {
                infinityQS1090FullLoad = infinityQS1090FullLoads[i];
                if (infinityQS1090FullLoad.Reactor is null)
                    continue;
                if (!int.TryParse(infinityQS1090FullLoad.Reactor, out reactorNumber))
                    continue;
                if (!eppReactors.TryGetValue(reactorNumber, out reactor))
                    continue;
                if (!nightSiftReactors.Contains(infinityQS1090FullLoad.Reactor))
                    dayShiftOrder.Add(i);
                else
                    nightShiftOrder.Add(i);
            }
            for (int i = 0; i < infinityQS1090FullLoads.Length; i++)
            {
                if (used.Contains(i) || !dayShiftOrder.Contains(i))
                    continue;
                infinityQS1090FullLoad = infinityQS1090FullLoads[i];
                if (infinityQS1090FullLoad.Reactor is null)
                    continue;
                if (!int.TryParse(infinityQS1090FullLoad.Reactor, out reactorNumber))
                    continue;
                if (!eppReactors.TryGetValue(reactorNumber, out reactor))
                    continue;
                used.Add(i);
                found = false;
                timeSpan = new(ticks - infinityQS1090FullLoad.SubGroupIdFormatted.Ticks);
                loadedCount = reactor.LoadedRDS is null ? 0 : reactor.LoadedRDS.Count;
                loadedRDS = reactor.LoadedRDS is null ? "&nbsp;" : reactor.LoadedRDS[0].ToString();
                results.Add(new string[]{
                    reactor.ReactorNo.ToString(),
                    reactor.E10State,
                    loadedRDS,
                    infinityQS1090FullLoad.Value.ToString(),
                    infinityQS1090FullLoad.TemperatureOffsetPercentage.ToString(),
                    infinityQS1090FullLoad.SubGroupIdFormatted.ToString(),
                    Math.Floor(timeSpan.TotalHours).ToString()
                });
                for (int j = i + 1; j < infinityQS1090FullLoads.Length; j++)
                {
                    if (used.Contains(j) || !nightShiftOrder.Contains(j))
                        continue;
                    infinityQS1090FullLoad = infinityQS1090FullLoads[j];
                    if (infinityQS1090FullLoad.Reactor is null)
                        continue;
                    if (!int.TryParse(infinityQS1090FullLoad.Reactor, out reactorNumber))
                        continue;
                    if (!eppReactors.TryGetValue(reactorNumber, out reactor))
                        continue;
                    used.Add(j);
                    found = true;
                    timeSpan = new(ticks - infinityQS1090FullLoad.SubGroupIdFormatted.Ticks);
                    loadedCount = reactor.LoadedRDS is null ? 0 : reactor.LoadedRDS.Count;
                    loadedRDS = reactor.LoadedRDS is null ? "&nbsp;" : reactor.LoadedRDS[0].ToString();
                    columns = new();
                    columns.AddRange(results[^1]);
                    columns.AddRange(new string[]
                    {
                            "&nbsp;",
                            reactor.ReactorNo.ToString(),
                            reactor.E10State,
                            loadedRDS,
                            infinityQS1090FullLoad.Value.ToString(),
                            infinityQS1090FullLoad.TemperatureOffsetPercentage.ToString(),
                            infinityQS1090FullLoad.SubGroupIdFormatted.ToString(),
                            Math.Floor(timeSpan.TotalHours).ToString()
                    });
                    results[^1] = columns.ToArray();
                    break;
                }
                if (!found)
                {
                    columns = new();
                    columns.AddRange(results[^1]);
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    columns.Add("&nbsp;");
                    results[^1] = columns.ToArray();
                }
            }
        }
        return results;
    }

    string IInfinityQSV4Repository.GetEpiProTempVerification(int[] night)
    {
        StringBuilder result = new();
        IInfinityQSV4Repository infinityQSV4Repository = this;
        List<string[]> collection = infinityQSV4Repository.GetEpiProTempVerificationRows(night);
        foreach (string[] row in collection)
        {
            _ = result.Append("<tr>");
            foreach (string column in row)
                _ = result.Append("<td>").Append(column).Append("</td>");
            _ = result.Append("</tr>");
        }
        return result.ToString();
    }

    List<Reactor> IInfinityQSV4Repository.GetReactors()
    {
        List<Reactor> results = new();
        ReadOnlyDictionary<int, Reactor> keyValuePairs = GetReactorsMatchingType(reactTypeFilter: null);
        foreach (KeyValuePair<int, Reactor> keyValuePair in keyValuePairs)
            results.Add(keyValuePair.Value);
        return results;
    }

    string IInfinityQSV4Repository.GetRunDataSheet(string rds)
    {
        string result;
        HttpClient httpClient = _HttpClientFactory.CreateClient();
        Task<string> task = httpClient.GetStringAsync($"{_OpenInsightApplicationProgrammingInterface}/materials/rds/{rds}");
        task.Wait();
        result = task.Result;
        if (rds == "123456")
        {
            RunDataSheetRoot? runDataSheetRoot = JsonSerializer.Deserialize(result, RunDataSheetRootSourceGenerationContext.Default.RunDataSheetRoot);
            if (runDataSheetRoot is not null)
            {
                List<string> lines = new();
                foreach (char ch in runDataSheetRoot.RunDataSheet.PSN)
                {
                    if (ch is '1')
                        continue;
                }
                result = string.Join(Environment.NewLine, lines);
            }
        }
        return result;
    }

    string IInfinityQSV4Repository.GetProductionSpecification(string part)
    {
        string result;
        HttpClient httpClient = _HttpClientFactory.CreateClient();
        Task<string> task = httpClient.GetStringAsync($"{_OpenInsightApplicationProgrammingInterface}/materials/prod_spec/{part}");
        task.Wait();
        result = task.Result;
        if (part == "4992")
        {
            ProdSpecRoot? prodSpecRoot = JsonSerializer.Deserialize(result, ProdSpecRootSourceGenerationContext.Default.ProdSpecRoot);
            if (prodSpecRoot is not null)
            {
                List<string> lines = new();
                foreach (PrsStage prsStage in prodSpecRoot.ProdSpec.PrsStages)
                {
                    if (prsStage.QaMetTests is null)
                        continue;
                    foreach (QaMetTest qaMetTest in prsStage.QaMetTests)
                    {
                        lines.Add(qaMetTest.Test);
                        // lines.Add(qaMetTest.Slots);
                        lines.Add(qaMetTest.Recipe);
                        lines.Add(qaMetTest.RecipePattern);
                        if (qaMetTest.Test != "THICK_ONLY")
                            continue;
                        lines.Add(qaMetTest.Min.ToString());
                        lines.Add(qaMetTest.Max.ToString());
                    }
                }
                result = string.Join(Environment.NewLine, lines);
            }
        }
        return result;
    }

    private static string GetCommandText(string process, string? part, int? test)
    { // cSpell:disable
        List<string> results = [];
        if (string.IsNullOrEmpty(process))
            throw new ArgumentException(null, nameof(process));
        if (string.IsNullOrEmpty(part))
            throw new ArgumentException(null, nameof(part));
        if (test is null)
            throw new ArgumentException(null, nameof(test));
        results.Add(" select se_max_sgrp, se.f_val se_value ");
        results.Add(" from ( ");
        results.Add("   select ");
        results.Add("         max(se.f_sgrp) se_max_sgrp ");
        results.Add("   from [spcepiworld].[dbo].[sgrp_ext] se ");
        results.Add("   join [spcepiworld].[dbo].[prcs_dat] pr ");
        results.Add("     on se.f_prcs = pr.f_prcs ");
        results.Add("   join [spcepiworld].[dbo].[part_dat] pd ");
        results.Add("     on se.f_part = pd.f_part ");
        results.Add("   where se.f_flag = 0 ");
        results.Add("     and se.f_tsno = 1 ");
        results.Add($"     and pr.f_name = '{process}' ");
        results.Add($"     and pd.f_name = '{part}' ");
        results.Add($"     and se.f_test = {test.Value} ");
        results.Add(" ) as iq ");
        results.Add(" join [spcepiworld].[dbo].[sgrp_ext] se ");
        results.Add("   on se_max_sgrp = se.f_sgrp ");
        results.Add(" where se.f_flag = 0 ");
        results.Add("   and se.f_tsno = 1 ");
        results.Add($"   and se.f_test = {test.Value} ");
        results.Add(" for json path ");
        return string.Join(' ', results);
    } // cSpell:enable

    string IInfinityQSV4Repository.GetLastGroupIdWithValue(string process, string? part, int? test)
    {
        StringBuilder result;
        if (!string.IsNullOrEmpty(_MockRoot))
        {
            string json = File.ReadAllText(Path.Combine(string.Concat(AppContext.BaseDirectory, _MockRoot), $"{_RepositoryName}-{nameof(IInfinityQSV4Repository.GetLastGroupIdWithValue)}.json"));
            result = new(json);
        }
        else
        {
            string commandText = GetCommandText(process, part, test);
            result = GetForJsonPath(_DBConnectionFactory, commandText, useIqsConnection: false);
            if (result.Length == 0)
                result = new("{}");
        }
        return result.ToString();
    }

}