using CommunityToolkit.Mvvm.ComponentModel;
using CommunityToolkit.Mvvm.Input;
using HMI.Base;
using HMI.Core.Entity;
using HMI.Core.Manager;
using HMI.Core.Setting;
using HMI.Core.Util;
using HMI.UI.Util;
using Microsoft.Win32;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using Panuon.WPF.UI;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Windows;

namespace Plugin.QualityReport
{
    public class CMKData
    {
        public object[] values = new object[5];
        public object d1 { get => values[0]; set => values[0] = value; }
        public object d2 { get => values[1]; set => values[1] = value; }
        public object d3 { get => values[2]; set => values[2] = value; }
        public object d4 { get => values[3]; set => values[3] = value; }
        public object d5 { get => values[4]; set => values[4] = value; }
    }

    public class MSAData
    {
        public object[] values = new object[9];
        public object d1 { get => values[0]; set => values[0] = value; }
        public object d2 { get => values[1]; set => values[1] = value; }
        public object d3 { get => values[2]; set => values[2] = value; }
        public object d4 { get => values[3]; set => values[3] = value; }
        public object d5 { get => values[4]; set => values[4] = value; }
        public object d6 { get => values[5]; set => values[5] = value; }
        public object d7 { get => values[6]; set => values[6] = value; }
        public object d8 { get => values[7]; set => values[7] = value; }
        public object d9 { get => values[8]; set => values[8] = value; }
    }

    public class RepeatData
    {
        public string Title { get; set; }

        public object[] values = new object[10];
        public object d1 { get => values[0]; set => values[0] = value; }
        public object d2 { get => values[1]; set => values[1] = value; }
        public object d3 { get => values[2]; set => values[2] = value; }
        public object d4 { get => values[3]; set => values[3] = value; }
        public object d5 { get => values[4]; set => values[4] = value; }
        public object d6 { get => values[5]; set => values[5] = value; }
        public object d7 { get => values[6]; set => values[6] = value; }
        public object d8 { get => values[7]; set => values[7] = value; }
        public object d9 { get => values[8]; set => values[8] = value; }
        public object d10 { get => values[9]; set => values[9] = value; }
    }

    /// <summary>
    /// QualityReport.xaml 的交互逻辑
    /// </summary>
    [ObservableObject]
    public partial class QualityReport : SimpleMaskControl
    {
        [ObservableProperty]
        private string model;

        [ObservableProperty]
        private List<ProductData> models;

        [ObservableProperty]
        private DateTime startTimeCMK;

        [ObservableProperty]
        private DateTime endTimeCMK;

        [ObservableProperty]
        private string testItemCMK = "TestItem";

        [ObservableProperty]
        private List<CMKData> datasourceCMK = null;

        [ObservableProperty]
        private List<MSAData> datasourceMSA = null;

        [ObservableProperty]
        private List<RepeatData> datasourceRepeat = null;

        [ObservableProperty]
        private string snr;

        [ObservableProperty]
        private ObservableCollection<string> selectedSnr = new ObservableCollection<string> { };

        [ObservableProperty]
        private ObservableCollection<string> selectedSnrRepeat = new ObservableCollection<string> { };

        public QualityReport()
        {
            DataContext = this;
            InitializeComponent();
            StartTimeCMK = DateTime.Today;
            EndTimeCMK = DateTime.Today.AddDays(1);
        }

        public override void Active()
        {
            base.Active();
            Models = ProductManager.GetProducts<List<ProductData>>()
                ?? new List<ProductData>();
        }

        private string[] GetFiles(DateTime? startTime, DateTime? endTime)
        {
            string dir = ParamSetting.DataLogFolder;
            string startName = $"Data_{startTime?.ToString("yyyyMMdd")}";
            string endName = $"Data_{endTime?.ToString("yyyyMMdd")}";
            var oldFiles = Directory.GetFiles(dir, "Data_*.db");
            oldFiles = oldFiles.Where(file =>
            {
                var fileName = Path.GetFileNameWithoutExtension(file);
                bool flag = true;
                flag &= (string.Compare(fileName, startName) == 1 || fileName == startName);
                flag &= (string.Compare(endName, fileName) == 1 || fileName == endName);
                return flag;
            }).ToArray();
            return oldFiles;
        }

        public DataTable SelectByFiles(string[] files, Func<string, string, string> ConcatAction, SortParam sorter, PageParam page)
        {
            if (files == null || files.Length <= 0)
            {
                return new DataTable();
            }
            if (files.Length < Sqlite3Helper.MAX_ATTACHED)
            {
                using (var helper = new Sqlite3Helper(files[0]))
                {
                    string[] sqls = new string[files.Length];
                    for (int i = 0; i < files.Length; i++)
                    {
                        var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                        helper.Execute(attach);
                        sqls[i] = ConcatAction(files[i], $"db{i}");
                    }
                    string sql = string.Join(" UNION ALL ", sqls);

                    sql = $"SELECT * FROM ({sql}) as _temp";
                    if (sorter != null)
                    {
                        sql = $"{sql} ORDER BY {sorter.field} {sorter.sort}";
                    }
                    if (page != null)
                    {
                        sql = $"{sql} LIMIT {page.limit} OFFSET {page.offset}";
                    }
                    return helper.GetDataTable(sql);
                }
            }
            else
            {
                DataTable dt = new DataTable();
                Sqlite3Helper helper = null;
                string[] sqls = new string[Sqlite3Helper.MAX_ATTACHED];

                var fff = () =>
                {
                    if (helper != null)
                    {
                        string sql = string.Join(" UNION ALL ", sqls.Where(s => s != null));

                        sql = $"SELECT * FROM ({sql}) as _temp";


                        dt.Merge(helper.GetDataTable(sql));
                        sqls = new string[Sqlite3Helper.MAX_ATTACHED];
                        helper.Dispose();
                        helper = null;
                    }
                };

                try
                {
                    for (int i = 0; i < files.Length; i++)
                    {
                        if (i % Sqlite3Helper.MAX_ATTACHED == 0)
                        {
                            fff();
                            helper = new Sqlite3Helper(files[i]);
                        }
                        var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                        helper.Execute(attach);
                        sqls[i % Sqlite3Helper.MAX_ATTACHED] = ConcatAction(files[i], $"db{i}");
                    }
                    fff();
                    if (dt.Rows.Count > 0)
                    {
                        if (sorter != null)
                        {
                            dt.DefaultView.Sort = $"{sorter.field} {sorter.sort}";
                        }
                        if (page != null)
                        {
                            var rows = dt.Rows.Cast<DataRow>();
                            var curRows = rows.Skip(page.offset).Take(page.limit).ToArray();
                            var sb = dt.Clone();
                            if (dt.Rows.Count > page.offset)
                            {
                                if (dt.Rows.Count < page.limit)
                                {
                                    sb = dt;
                                }
                                else
                                {
                                    for (int i = page.offset; i < page.offset + page.limit; i++)
                                    {
                                        sb.ImportRow(dt.Rows[i]);
                                    }
                                }
                            }
                            dt = sb;
                            // dt = new DataTable(curRows);
                        }
                    }
                }
                finally
                {
                    helper?.Dispose();
                }
                return dt;
            }
        }


        [RelayCommand]
        public void QueryCMK()
        {
            try
            {
                if (string.IsNullOrEmpty(Model) || string.IsNullOrEmpty(TestItemCMK) || StartTimeCMK == null || EndTimeCMK == null)
                {
                    ToastHelper.Error("请输入型号、测试项、开始时间和结束时间！");
                    return;
                }
                string[] files = GetFiles(StartTimeCMK, EndTimeCMK);


                DataTable dt = SelectByFiles(files, (file, db) =>
                {
                    return $"SELECT value,time FROM {db}.serialData WHERE model='{Model}' AND step='{TestItemCMK}' AND time>='{StartTimeCMK:yyyy-MM-dd HH:mm:ss}' AND time<'{EndTimeCMK:yyyy-MM-dd HH:mm:ss}'";
                }, new SortParam { field = "time", sort = "DESC" }, new PageParam { limit = 50, offset = 0 });
                List<CMKData> list = new List<CMKData>();
                object[] values = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i % 5 == 0)
                    {
                        values = new object[5];
                        list.Add(new CMKData { values = values });
                    }
                    values[i % 5] = dt.Rows[i]["value"];
                }
                DatasourceCMK = list;
            }
            catch (Exception ex)
            {

            }
        }

        [RelayCommand]
        public void ExportCMK()
        {
            try
            {
                if (string.IsNullOrEmpty(Model) || string.IsNullOrEmpty(TestItemCMK) || StartTimeCMK == null || EndTimeCMK == null)
                {
                    ToastHelper.Error("请输入型号、测试项、开始时间和结束时间！");
                    return;
                }
                string[] files = GetFiles(StartTimeCMK, EndTimeCMK);


                DataTable dt = SelectByFiles(files, (file, db) =>
                {
                    return $"SELECT value,time FROM {db}.serialData WHERE model='{Model}' AND step='{TestItemCMK}' AND time>='{StartTimeCMK:yyyy-MM-dd HH:mm:ss}' AND time<'{EndTimeCMK:yyyy-MM-dd HH:mm:ss}'";
                }, new SortParam { field = "time", sort = "DESC" }, new PageParam { limit = 50, offset = 0 });
                List<CMKData> list = new List<CMKData>();
                object[] values = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i % 5 == 0)
                    {
                        values = new object[5];
                        list.Add(new CMKData { values = values });
                    }
                    values[i % 5] = dt.Rows[i]["value"];
                }

                using (FileStream fs = File.OpenRead(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Libs", "Static", "CMK模板.xls")))
                {
                    IWorkbook workbook = new HSSFWorkbook(fs);
                    ISheet sheet = workbook.GetSheet("CMK");
                    for (int i = 0; i < list.Count; i++)
                    {
                        int num = i + 9;
                        if (num > 24)
                        {
                            break;
                        }
                        IRow row = sheet.GetRow(num);
                        CMKData data = list[i];
                        for (int j = 0; j < 5; j++)
                        {
                            if (data.values[j] != null)
                            {
                                row.GetCell(j).SetCellValue(double.Parse(data.values[j].ToString()));
                            }
                        }
                    }
                    sheet.ForceFormulaRecalculation = true;//执行公式

                    //方法二
                    //workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

                    SaveFileDialog sfd = new SaveFileDialog
                    {
                        Filter = "Excel文件 (*.xls)|*.xls",
                    };
                    if (sfd.ShowDialog() == true)
                    {
                        using (FileStream sfs = new FileStream(sfd.FileName, FileMode.OpenOrCreate))
                        {
                            workbook.Write(sfs);
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }

        [RelayCommand]
        private void AddSnr()
        {
            if (SelectedSnr.Count >= 9)
            {
                ToastHelper.Warning("最多选取9个产品");
                return;
            }

            if (SelectedSnr.Contains(Snr))
            {
                ToastHelper.Warning($"条码：[{Snr}] 已被选中");
                return;
            }
            SelectedSnr.Add(Snr);
        }

        [RelayCommand]
        private void AddSnr2Repeat()
        {
            if (SelectedSnrRepeat.Count >= 10)
            {
                ToastHelper.Warning("最多选取10个产品");
                return;
            }

            if (SelectedSnrRepeat.Contains(Snr))
            {
                ToastHelper.Warning($"条码：[{Snr}] 已被选中");
                return;
            }
            SelectedSnrRepeat.Add(Snr);
        }

        [RelayCommand]
        public void RemoveSN(object obj)
        {
            SelectedSnr.Remove(obj.ToString());
        }

        [RelayCommand]
        public void QueryMSA()
        {
            try
            {
                if (string.IsNullOrEmpty(Model) || SelectedSnr.Count <= 0 || StartTimeCMK == null || EndTimeCMK == null)
                {
                    ToastHelper.Error("请输入型号、选中的条码、开始时间和结束时间！");
                    return;
                }
                string[] files = GetFiles(StartTimeCMK, EndTimeCMK);
                Dictionary<string, List<int>> map = new Dictionary<string, List<int>>();
                foreach (var sn in SelectedSnr)
                {
                    List<int> datas = new List<int>();
                    DataTable dt = SelectByFiles(files, (file, db) =>
                {
                    return $"SELECT result,time FROM {db}.testResult WHERE model='{Model}' AND sn='{sn}' AND time>='{StartTimeCMK:yyyy-MM-dd HH:mm:ss}' AND time<'{EndTimeCMK:yyyy-MM-dd HH:mm:ss}'";
                }, new SortParam { field = "time", sort = "DESC" }, new PageParam { limit = 10, offset = 0 });

                    datas = dt.Rows.Cast<DataRow>().Select(r =>
                    string.Equals(r["result"].ToString(), "passed", StringComparison.OrdinalIgnoreCase)
                    || string.Equals(r["result"].ToString(), "ok", StringComparison.OrdinalIgnoreCase) ? 1 : 0)
                        .ToList();
                    map[sn] = datas;
                }
                List<MSAData> temp = new List<MSAData>() { new MSAData() };

                int i = 0;
                foreach (var key in map.Keys)
                {
                    temp[0].values[i] = key;
                    var vals = map[key];
                    for (int j = 0; j < vals.Count; j++)
                    {
                        if (temp.Count < j + 2)
                        {
                            temp.Add(new MSAData());
                        }
                        temp[j + 1].values[i] = vals[j];
                    }
                    i++;
                }
                DatasourceMSA = temp;

                //RefreshMSAGrid(map);
            }
            catch (Exception ex)
            {

            }
        }


        [RelayCommand]
        public void ExportMSA()
        {
            try
            {
                if (string.IsNullOrEmpty(Model) || SelectedSnr.Count <= 0 || StartTimeCMK == null || EndTimeCMK == null)
                {
                    ToastHelper.Error("请输入型号、选中的条码、开始时间和结束时间！");
                    return;
                }

                string[] files = GetFiles(StartTimeCMK, EndTimeCMK);
                Dictionary<string, List<int>> map = new Dictionary<string, List<int>>();
                foreach (var sn in SelectedSnr)
                {
                    List<int> datas = new List<int>();
                    DataTable dt = SelectByFiles(files, (file, db) =>
                    {
                        return $"SELECT result,time FROM {db}.testResult WHERE model='{Model}' AND sn='{sn}' AND time>='{StartTimeCMK:yyyy-MM-dd HH:mm:ss}' AND time<'{EndTimeCMK:yyyy-MM-dd HH:mm:ss}'";
                    }, new SortParam { field = "time", sort = "DESC" }, new PageParam { limit = 10, offset = 0 });

                    datas = dt.Rows.Cast<DataRow>().Select(r =>
                    string.Equals(r["result"].ToString(), "passed", StringComparison.OrdinalIgnoreCase)
                    || string.Equals(r["result"].ToString(), "ok", StringComparison.OrdinalIgnoreCase) ? 1 : 0)
                        .ToList();
                    map[sn] = datas;
                }
                using (FileStream fs = File.OpenRead(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Libs", "Static", "MSA模板.xlsx")))
                {
                    IWorkbook workbook = new XSSFWorkbook(fs);
                    ISheet sheet = workbook.GetSheet("数据输入");

                    int column = 2;
                    foreach (string snr in map.Keys)
                    {
                        if (column > 10)
                        {
                            break;
                        }
                        var datas = map[snr];
                        sheet.GetRow(9).GetCell(column).SetCellValue(snr);

                        for (int i = 0; i < datas.Count; i++)
                        {
                            if (i > 49) break;
                            sheet.GetRow(i + 11).GetCell(column).SetCellValue(datas[i]);
                        }
                        column++;
                    }
                    sheet.ForceFormulaRecalculation = true;//执行公式

                    //方法二
                    //workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

                    SaveFileDialog sfd = new SaveFileDialog
                    {
                        Filter = "Excel文件 (*.xlsx)|*.xlsx",
                    };
                    if (sfd.ShowDialog() == true)
                    {
                        using (FileStream sfs = new FileStream(sfd.FileName, FileMode.OpenOrCreate))
                        {
                            workbook.Write(sfs);
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }

        [RelayCommand]
        public void QueryRepeat()
        {
            try
            {
                if (string.IsNullOrEmpty(Model) || SelectedSnrRepeat.Count <= 0 || string.IsNullOrEmpty(TestItemCMK) || StartTimeCMK == null || EndTimeCMK == null)
                {
                    ToastHelper.Error("请输入型号、选中的条码、测试项、开始时间和结束时间！");
                    return;
                }

                string[] files = GetFiles(StartTimeCMK, EndTimeCMK);
                Dictionary<string, List<double>> map = new Dictionary<string, List<double>>();
                foreach (var sn in SelectedSnrRepeat)
                {
                    DataTable dt = SelectByFiles(files, (file, db) =>
                    {
                        return $"SELECT value,time FROM {db}.serialData WHERE model='{Model}' And step='{TestItemCMK}' AND sn='{sn}' AND time>='{StartTimeCMK:yyyy-MM-dd HH:mm:ss}' AND time<'{EndTimeCMK:yyyy-MM-dd HH:mm:ss}'";
                    }, new SortParam { field = "time", sort = "DESC" }, new PageParam { limit = 9, offset = 0 });

                    var datas = dt.Rows.Cast<DataRow>().Select(r => Math.Round(double.Parse(r["value"].ToString()), 3, MidpointRounding.AwayFromZero)).ToList();
                    map[sn] = datas;
                }
                List<RepeatData> temp = new List<RepeatData>() { new RepeatData { Title = "条码" } };

                int i = 0;
                foreach (var key in map.Keys)
                {
                    temp[0].values[i] = key;
                    var vals = map[key];
                    for (int j = 0; j < vals.Count; j++)
                    {
                        if (temp.Count < j + 2)
                        {
                            temp.Add(new RepeatData());
                        }
                        temp[j + 1].values[i] = vals[j];
                    }
                    i++;
                }
                DatasourceRepeat = temp;
            }
            catch (Exception ex)
            {
                log.Error("重复性查询失败", ex);
                MsgBoxHelper2.Error($"重复性查询失败: {ex.Message}");
            }
        }


        [RelayCommand]
        public void ExportRepeat()
        {
            try
            {
                if (string.IsNullOrEmpty(Model) || SelectedSnrRepeat.Count <= 0 || string.IsNullOrEmpty(TestItemCMK) || StartTimeCMK == null || EndTimeCMK == null)
                {
                    ToastHelper.Error("请输入型号、选中的条码、测试项、开始时间和结束时间！");
                    return;
                }
                string[] files = GetFiles(StartTimeCMK, EndTimeCMK);
                Dictionary<string, List<double>> map = new Dictionary<string, List<double>>();
                foreach (var sn in SelectedSnrRepeat)
                {
                    DataTable dt = SelectByFiles(files, (file, db) =>
                    {
                        return $"SELECT value,time FROM {db}.serialData WHERE model='{Model}' And step='{TestItemCMK}' AND sn='{sn}' AND time>='{StartTimeCMK:yyyy-MM-dd HH:mm:ss}' AND time<'{EndTimeCMK:yyyy-MM-dd HH:mm:ss}'";
                    }, new SortParam { field = "time", sort = "DESC" }, new PageParam { limit = 9, offset = 0 });

                    var datas = dt.Rows.Cast<DataRow>().Select(r => Math.Round(double.Parse(r["value"].ToString()), 3, MidpointRounding.AwayFromZero)).ToList();
                    map[sn] = datas;
                }
                List<RepeatData> temp = new List<RepeatData>() { new RepeatData() };

                int i = 0;
                foreach (var key in map.Keys)
                {
                    temp[0].values[i] = key;
                    var vals = map[key];
                    for (int j = 0; j < vals.Count; j++)
                    {
                        if (temp.Count < j + 2)
                        {
                            temp.Add(new RepeatData());
                        }
                        temp[j + 1].values[i] = vals[j];
                    }
                    i++;
                }

                using (FileStream fs = File.OpenRead(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Libs", "Static", "重复性模板.xls")))
                {
                    IWorkbook workbook = new HSSFWorkbook(fs);
                    ISheet sheet = workbook.GetSheet("sheet1");

                    sheet.GetRow(10).GetCell(2).SetCellValue(TestItemCMK);

                    int column = 2;
                    foreach (string snr in map.Keys)
                    {
                        if (column > 11)
                        {
                            break;
                        }
                        var datas = map[snr];
                        sheet.GetRow(11).GetCell(column).SetCellValue(snr);

                        for (int j = 0; j < datas.Count; j++)
                        {
                            if (j > 8) break;
                            int row = j + 13;
                            if (j > 2 && j <= 5)
                            {
                                row = j + 15;
                            }
                            else if (j > 5)
                            {
                                row = j + 17;
                            }
                            sheet.GetRow(row).GetCell(column).SetCellValue(datas[j]);
                        }
                        column++;
                    }
                    sheet.ForceFormulaRecalculation = true;//执行公式

                    //方法二
                    //workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

                    SaveFileDialog sfd = new SaveFileDialog
                    {
                        Filter = "Excel文件 (*.xls)|*.xls",
                    };
                    if (sfd.ShowDialog() == true)
                    {
                        using (FileStream sfs = new FileStream(sfd.FileName, FileMode.OpenOrCreate))
                        {
                            workbook.Write(sfs);
                            ToastHelper.Success("导出成功");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("重复性导出失败", ex);
                MsgBoxHelper2.Error($"导出失败: {ex.Message}");
            }
        }

        private void SearchTextChanged(object sender, Panuon.WPF.SearchTextChangedRoutedEventArgs e)
        {
            SearchBox box = (SearchBox)sender;
            try
            {
                string[] files = GetFiles(StartTimeCMK, EndTimeCMK).OrderBy(s => s).Take(7).ToArray();

                if (box.Tag.ToString() == "Step")
                {
                    DataTable dt = SelectByFiles(files, (file, db) =>
                    {
                        return $"SELECT DISTINCT step FROM {db}.serialData WHERE model='{Model}' And step LIKE '%{TestItemCMK}%'";
                    }, null, null);

                    var datas = dt.Rows.Cast<DataRow>().Select(r => r["step"].ToString()).Distinct().ToList();
                    box.ItemsSource = datas;
                }
                else
                {
                    DataTable dt = SelectByFiles(files, (file, db) =>
                    {
                        return $"SELECT DISTINCT sn FROM {db}.testResult WHERE model='{Model}' And sn LIKE '%{Snr}%'";
                    }, null, null);

                    var datas = dt.Rows.Cast<DataRow>().Select(r => r["sn"].ToString()).Distinct().ToList();
                    box.ItemsSource = datas;
                }
            }
            catch
            {
                box.ItemsSource = new string[] { };
            }
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {

        }
    }
}