using Microsoft.Extensions.Caching.Memory;
using OI.Metrology.Shared.DataModels.RDS;
using OI.Metrology.Shared.Repositories;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace OI.Metrology.Archive.Repositories;

#nullable disable

public class RdsMaxRepo : IRdsMaxRepo
{

    private readonly IMemoryCache _MemoryCache;
    private readonly Models.AppSettings _AppSettings;

    public RdsMaxRepo(Models.AppSettings appSettings, IMemoryCache memoryCache)
    {
        _MemoryCache = memoryCache;
        _AppSettings = appSettings;
    }

    public RdsMaxRepo(string json, IMemoryCache memoryCache)
    {
        _MemoryCache = memoryCache;
        Models.AppSettings appSettings = System.Text.Json.JsonSerializer.Deserialize<Models.AppSettings>(json);
        _AppSettings = appSettings;
    }

    protected void CacheItem(string key, object v)
    {
        System.Diagnostics.Debug.WriteLine("CacheItem: " + key);

        _ = _MemoryCache.Set(key, v, new MemoryCacheEntryOptions().SetSlidingExpiration(TimeSpan.FromMinutes(1)));
    }

    private static string GetMaxRdsSql()
    {
        StringBuilder result = new();
        _ = result.Append(" select ").
                Append("        rt.reactor_type ").
                Append("       , rr.reactor ").
                Append("       , rt.ls_id ").
                Append("       , rt.zone      ").
                Append("       , rr.load_lock_side ").
                Append("       , rr.rds_no ").
                Append("       , rr.wo_no ").
                Append("       , rr.ps_no ").
                Append("       , rr.recipe_name ").
                Append("       , rr.recipe_no ").
                Append("       , rr.spec_type ").
                Append("       , rt.spec_thick_mtool ").
                Append("       , rt.spec_thick_mrecipe ").
                Append("       , rt.spec_res_mtool ").
                Append("       , rt.spec_res_mrecipe ").
                Append("       , rt.spec_cres_mtool ").
                Append("       , rt.spec_cres_mrecipe ").
                Append("       , ci.ci_no ").
                Append("       , cr.mv_no ").
                Append("       , cr.scan_tool ").
                Append("       , cr.spec_surfscan_recipe ").
                Append("       , cr.spec_ss_samp_qty ").
                Append(" from ( ").
                Append("   select rds_no ").
                Append("   from lsl2sql.dbo.react_run qa ").
                Append("   inner join ( ").
                Append("     select max(qb.rds_no) max_rds_no, qb.reactor ").
                Append("     from lsl2sql.dbo.react_run qb ").
                Append("     where qb.load_sig != '' ").
                Append("       and qb.load_sig_dtm > @load_sig_dtm ").
                Append("     group by qb.reactor ").
                Append("   ) qb ").
                Append("   on qa.rds_no = qb.max_rds_no   ").
                Append(" ) as qc ").
                Append(" inner join lsl2sql.dbo.react_run rr ").
                Append("   on qc.rds_no = rr.rds_no ").
                Append(" left join [lsl2sql].[dbo].[rds_test] rt ").
                Append("   on rr.rds_no = rt.rds_no ").
                Append("   and rr.ps_no = rt.ps_no ").
                Append(" left join [lsl2sql].[dbo].[clean_insp] ci ").
                Append("   on rr.rds_no = ci.rds_no ").
                Append("   and ci.stage = 'LWI' ").
                Append(" left join [lsl2sql].[dbo].[clean_insp_scan_results] cr ").
                Append("   on ci.ci_no = cr.ci_no ").
                Append(" order by ").
                Append("         rr.reactor ").
                Append("       , rt.ls_id ").
                Append("       , rt.zone ").
                Append(" for json path ");
        return result.ToString();
    }

    private string GetMaxRDSJson(string commandText)
    {
        StringBuilder result = new();
        try
        {
            using DbConnection dbConnection = SqlClientFactory.Instance.CreateConnection();
            dbConnection.ConnectionString = _AppSettings.Oi2SqlConnectionString;
            dbConnection.Open();
            using DbCommand dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = commandText;
            using DbDataReader reader = dbCommand.ExecuteReader(CommandBehavior.SequentialAccess);
            while (reader.Read())
                _ = result.Append(reader.GetString(0));
            dbConnection.Close();
        }
        catch (Exception)
        { }
        return result.ToString();
    }

    public Max[] GetMaxRDS()
    {
        Max[] cached;
        string cacheKey = "GetMaxRDS";
        if (_MemoryCache.TryGetValue(cacheKey, out cached))
            return cached;

        string loadSigDTM = DateTime.Now.AddDays(-90).ToString("yyyy-MM-dd 00:00:00.000");
        string sql = GetMaxRdsSql();
        string commandText = sql.Replace("@load_sig_dtm", $"'{loadSigDTM}'");
        string json = GetMaxRDSJson(commandText);
        Max[] r = System.Text.Json.JsonSerializer.Deserialize<Max[]>(json);

        CacheItem(cacheKey, r);

        return r;
    }

    private static Dictionary<int, List<Max>> GetReactorToRecords(Max[] collection)
    {
        Dictionary<int, List<Max>> results = new();
        foreach (Max max in collection)
        {
            if (!results.ContainsKey(max.Reactor))
                results.Add(max.Reactor, new());
            results[max.Reactor].Add(max);
        }
        return results;
    }

    private static (string[], int[], int[], int[]) GetDistinct(Max[] collection)
    {
        List<string> layerCollection = new();
        List<int> comboCollection = new();
        List<int> zoneCollection = new();
        List<int> cleanCollection = new();
        foreach (Max max in collection)
        {
            if (!string.IsNullOrEmpty(max.LsId) && max.LsId[0] == 'L' && !layerCollection.Contains(max.LsId))
                layerCollection.Add(max.LsId);
            if (!string.IsNullOrEmpty(max.LsId) && max.LsId[0] != 'L' && int.TryParse(max.LsId, out int comboCheck) && !comboCollection.Contains(comboCheck))
                comboCollection.Add(comboCheck);
            if (!string.IsNullOrEmpty(max.Zone) && int.TryParse(max.Zone, out int zoneCheck) && !zoneCollection.Contains(zoneCheck))
                zoneCollection.Add(zoneCheck);
            if (max.MvNo.HasValue && !cleanCollection.Contains(max.MvNo.Value))
                cleanCollection.Add(max.MvNo.Value);
        }
        return new(layerCollection.OrderBy(l => l).ToArray(), comboCollection.OrderBy(l => l).ToArray(), zoneCollection.OrderBy(l => l).ToArray(), cleanCollection.OrderBy(l => l).ToArray());
    }

    private static Max[] GetLayerZoneCollection(Dictionary<string, List<(string, Max)>> layerZoneToCollection)
    {
        Max[] results;
        List<(string Sort, Max Max)> collection = new();
        foreach (KeyValuePair<string, List<(string, Max)>> keyValuePair in layerZoneToCollection)
            collection.AddRange(keyValuePair.Value);
        results = (from l in collection orderby l.Sort select l.Max).ToArray();
        return results;
    }

    private static Max[] GetLayerZoneCollection(string[] layerCollection, int[] comboCollection, int[] zoneCollection, List<Max> collection)
    {
        Max[] results;
        string key;
        string sort;
        string distinct;
        List<string> distinctCollection = new();
        Dictionary<string, List<(string, Max)>> layerZoneToCollection = new();
        foreach (string layer in layerCollection)
        {
            layerZoneToCollection.Add(string.Concat(layer, '.'), new());
            foreach (int zone in zoneCollection)
                layerZoneToCollection.Add(string.Concat(layer, '.', zone), new());
        }
        foreach (int combo in comboCollection)
        {
            layerZoneToCollection.Add(string.Concat(combo, '.'), new());
            foreach (int zone in zoneCollection)
                layerZoneToCollection.Add(string.Concat(combo, '.', zone), new());
        }
        foreach (Max max in collection)
        {
            if (string.IsNullOrEmpty(max.LsId))
                continue;
            sort = string.Concat(max.LsId.PadLeft(2, 'Z'), '.', max.Zone);
            key = string.Concat(max.LsId, '.', max.Zone);
            distinct = string.Concat(max.LsId, '\t', max.Zone, '\t', max.LoadLockSide, '\t', max.RdsNo, '\t', max.PsNo, '\t', max.RecipeName, '\t', max.SpecType, '\t', max.SpecThickMtool, '\t', max.SpecThickMrecipe, '\t', max.SpecResMtool, '\t', max.SpecResMrecipe, '\t', max.SpecCresMtool, '\t', max.SpecCresMrecipe);
            if (distinctCollection.Contains(distinct))
                continue;
            distinctCollection.Add(distinct);
            layerZoneToCollection[key].Add(new(sort, max));
        }
        results = GetLayerZoneCollection(layerZoneToCollection);
        return results;
    }

    private static Max[] GetCleanCollection(Dictionary<int, List<(int, Max)>> layerZoneToCollection)
    {
        Max[] results;
        List<(int Sort, Max Max)> collection = new();
        foreach (KeyValuePair<int, List<(int, Max)>> keyValuePair in layerZoneToCollection)
            collection.AddRange(keyValuePair.Value);
        results = (from l in collection orderby l.Sort select l.Max).ToArray();
        return results;
    }

    private static Max[] GetCleanCollection(int[] cleanCollection, List<Max> collection)
    {
        Max[] results;
        int key;
        string distinct;
        List<string> distinctCollection = new();
        Dictionary<int, List<(int, Max)>> cleanToCollection = new();
        foreach (int clean in cleanCollection)
            cleanToCollection.Add(clean, new());
        foreach (Max max in collection)
        {
            if (max.MvNo is null)
                continue;
            key = max.MvNo.Value;
            distinct = string.Concat(max.MvNo, '\t', max.ScanTool, '\t', max.SpecSurfscanRecipe, '\t', max.SpecSsSampQty);
            if (distinctCollection.Contains(distinct))
                continue;
            distinctCollection.Add(distinct);
            cleanToCollection[key].Add(new(key, max));
        }
        results = GetCleanCollection(cleanToCollection);
        return results;
    }

    public List<string[]> Convert(Max[] collection)
    {
        List<string[]> results = new();
        string[] row;
        Max[] cleanCollectionB;
        List<string> res = new();
        Max[] layerZoneCollection;
        List<string> clean = new();
        List<string> thick = new();
        List<string> centerRes = new();
        StringBuilder stringBuilder = new();
        _ = stringBuilder.Append($"Reactor\tReactorType\tWO\tRDS\tPSN\tName\tSpecType\tRes\tThick\tCenter Res\tScan");
        row = stringBuilder.ToString().Split('\t');
        results.Add(row);
        Dictionary<int, List<Max>> reactorToRecords = GetReactorToRecords(collection);
        (string[] layerCollection, int[] comboCollection, int[] zoneCollection, int[] cleanCollection) = GetDistinct(collection);
        foreach (KeyValuePair<int, List<Max>> reactorToRecord in reactorToRecords)
        {
            res.Clear();
            clean.Clear();
            thick.Clear();
            centerRes.Clear();
            _ = stringBuilder.Clear();
            foreach (Max max in reactorToRecord.Value)
            {
                _ = stringBuilder.Append($"R{max.Reactor}\t{max.ReactorType}\t{max.WoNo}\t{max.RdsNo}\t{max.PsNo}\t{max.RecipeName}\t{max.SpecType}\t");
                break;
            }
            layerZoneCollection = GetLayerZoneCollection(layerCollection, comboCollection, zoneCollection, reactorToRecord.Value);
            foreach (Max max in layerZoneCollection)
            {
                res.Add(string.Concat(max.LsId, '.', max.Zone, " - ", max.SpecResMtool, " {", max.SpecResMrecipe, '}'));
                thick.Add(string.Concat(max.LsId, '.', max.Zone, " - ", max.SpecThickMtool, " {", max.SpecThickMrecipe, '}'));
                centerRes.Add(string.Concat(max.LsId, '.', max.Zone, " - ", max.SpecCresMtool, " {", max.SpecCresMrecipe, '}'));
            }
            _ = stringBuilder.Append($"{string.Join("<br />", res.ToArray())}\t");
            _ = stringBuilder.Append($"{string.Join("<br />", thick.ToArray())}\t");
            _ = stringBuilder.Append($"{string.Join("<br />", centerRes.ToArray())}\t");
            cleanCollectionB = GetCleanCollection(cleanCollection, reactorToRecord.Value);
            foreach (Max max in cleanCollectionB)
                clean.Add(string.Concat(max.MvNo, " - {", max.SpecSurfscanRecipe, "} [", max.SpecSsSampQty, "] ", max.ScanTool));
            _ = stringBuilder.Append($"{string.Join("<br />", clean.ToArray())}");
            row = stringBuilder.ToString().Split('\t');
            results.Add(row);
        }
        return results;
    }

}