using CommunityToolkit.Mvvm.ComponentModel;
using HMI.Base;
using HMI.Core;
using HMI.Core.Setting;
using HMI.Core.Util;
using HMI.UI.Util;
using HMI.Util;
using Microsoft.Win32;
using Panuon.WPF.UI;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Windows.Controls;
using System.Windows.Controls.Primitives;

namespace Plugin.DataViewer
{
    public class TestResult
    {
        [ExcelColumn("SN")]
        public string sn { get; set; }

        [ExcelColumn("穴位号")]
        public string position { get; set; }

        [ExcelColumn("型号")]
        public string model { get; set; }

        [ExcelColumn("测试结果")]
        public string result { get; set; }

        [ExcelColumn("测试时间")]
        public string time { get; set; }
    }

    public class SerialData
    {
        [ExcelColumn("SN")]
        public string sn { get; set; }

        [ExcelColumn("穴位号")]
        public string position { get; set; }

        [ExcelColumn("型号")]
        public string model { get; set; }

        [ExcelColumn("测试项")]
        public string step { get; set; }

        [ExcelColumn("测试值")]
        public string value { get; set; }

        [ExcelColumn("下限")]
        public string limitDown { get; set; }

        [ExcelColumn("上限")]
        public string limitUp { get; set; }
        [ExcelColumn("结果")]
        public string status { get; set; }

        [ExcelColumn("单位")]
        public string unit { get; set; }

        [ExcelColumn("测试时间")]
        public string time { get; set; }
    }

    public class SortParam
    {
        public string name { get; set; }
        public ListSortDirection direction { get; set; }
        public string sort { get; set; }
    }

    public class ResultData
    {
        public string sn { get; set; }
        public string position { get; set; }
    }

    /// <summary>
    /// DataViewerControl.xaml 的交互逻辑
    /// </summary>
    [ObservableObject]
    public partial class DataViewerControl : SimpleMaskControl
    {
        [ObservableProperty]
        private DataView resultView;

        [ObservableProperty]
        private ObservableCollection<ResultData> resultData;

        [ObservableProperty]
        private long currentPage = 1;

        [ObservableProperty]
        private long totalPage = 0;

        private int pageSize = 30;

        [ObservableProperty]
        private long totalCount;

        [ObservableProperty]
        private long passCount;

        [ObservableProperty]
        private long failCount;

        [ObservableProperty]
        private long testItemPage = 1;

        [ObservableProperty]
        private long testItemTotalPage = 0;

        [ObservableProperty]
        private object selectedResult;

        [ObservableProperty]
        private string selectedStatus;

        private SortParam sorter;
        private SortParam itemSorter;
        public DataViewerControl()
        {
            DataContext = this;
            InitializeComponent();
            Reset();
        }

        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;
        }

        private void Reset()
        {
            var today = DateTime.Now;
            var startTime = today.AddDays(-7);
            var end = today.AddDays(1);

            snInput.Text = string.Empty;
            positionInput.Text = string.Empty;
            modelInput.Text = string.Empty;
            resultInput.Text = string.Empty;

            startTimeInput.SelectedDateTime = DateTime.Parse(startTime.ToString("yyyy-MM-dd 00:00:00"));
            endTimeInput.SelectedDateTime = DateTime.Parse(end.ToString("yyyy-MM-dd 00:00:00"));
        }

        private void Button_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            var btn = sender as Button;
            if (btn.Name == "resetBtn")
            {
                Reset();
                if (CurrentPage == 1)
                {
                    SelectPage(1, this.pageSize);
                }
                else
                {
                    CurrentPage = 1;
                }
                return;
            }
            if (btn.Name == "srhBtn")
            {
                if (CurrentPage == 1)
                {
                    SelectPage(1, this.pageSize);
                }
                else
                {
                    CurrentPage = 1;
                }
                return;
            }

            if (btn.Name == "srhItemBtn")
            {
                if (TestItemPage == 1)
                {
                    RefreshTestItem(this.selectResultId, 1, this.pageSize);
                }
                else
                {
                    TestItemPage = 1;
                }
                return;
            }

            if (btn.Name == "exportBtn")
            {
                TryExport();
            }

            if (btn.Name == "exportItemBtn")
            {
                TryExportItem();
            }
        }

        private void TryExport()
        {
            SaveFileDialog sfd = new SaveFileDialog
            {
                Filter = "Excel 文件 (*.xls)|*.xls"
            };
            if (sfd.ShowDialog() != true)
            {
                return;
            }
            string filePath = sfd.FileName;

            DateTime? startTime = startTimeInput.SelectedDateTime, endTime = endTimeInput.SelectedDateTime;

            topMask.ShowMask(this, () =>
            {
                var table = SelectResult(startTime, endTime);

                ExcelHelper.ExportExcel<TestResult>(filePath, table);

            }, (ex) =>
            {
                log.Error("Export Result Failed.", ex);
                Dispatcher.Invoke(() =>
                {
                    MsgBoxHelper2.Error($"导出失败！{ex.Message}");
                });
            }, "导出中,请稍后...");
        }

        private void TryExportItem()
        {
            SaveFileDialog sfd = new SaveFileDialog
            {
                Filter = "Excel 文件 (*.xls)|*.xls"
            };
            if (sfd.ShowDialog() != true)
            {
                return;
            }
            string filePath = sfd.FileName;

            DateTime? startTime = startTimeInput.SelectedDateTime, endTime = endTimeInput.SelectedDateTime;

            topMask.ShowMask(this, () =>
            {
                var table = SelectItem(startTime, endTime);

                ExcelHelper.ExportExcel<SerialData>(filePath, table);

            }, (ex) =>
            {
                log.Error("Export Result Failed.", ex);
                Dispatcher.Invoke(() =>
                {
                    MsgBoxHelper2.Error($"导出失败！{ex.Message}");
                });
            }, "导出中,请稍后...");
        }

        private DataTable SelectResult(DateTime? startTime, DateTime? endTime)
        {
            var files = GetFiles(startTime, endTime);
            if (files == null || files.Length <= 0)
            {
                return new DataTable();
            }
            using (var helper = new Sqlite3Helper(files[0]))
            {
                for (int i = 0; i < files.Length; i++)
                {
                    var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                    helper.Execute(attach);
                }

                for (int i = 0; i < files.Length; i++)
                {
                    if (!helper.HasData($"SELECT 1 FROM pragma_table_info('testResult','db{i}') WHERE name = 'resultId';"))
                    {
                        helper.Execute($"ALTER TABLE db{i}.testResult ADD resultId TEXT;");
                    }
                }

                string sql = string.Join(" UNION ALL ", GetResultSqlsByFiles(files, startTime, endTime));
                string sortName = "time", sortDirection = "DESC";
                string baseSql = $"SELECT id,sn,model,station,position,result,time,resultId FROM ({sql}) as _temp";



                sql = $"{baseSql} ORDER BY {sortName} {sortDirection}";

                return helper.GetDataTable(sql);

                //for (int i = 0; i < files.Length; i++)
                //{
                //    helper.Execute($"DETACH DATABASE db{i}");
                //}
            }
        }

        private DataTable SelectItem(DateTime? startTime, DateTime? endTime)
        {
            if (string.IsNullOrEmpty(this.selectResultId))
            {
                return new DataTable();
            }
            var files = GetFiles(startTime, endTime);
            if (files == null || files.Length <= 0)
            {
                return new DataTable();
            }
            using (var helper = new Sqlite3Helper(files[0]))
            {
                for (int i = 0; i < files.Length; i++)
                {
                    var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                    helper.Execute(attach);
                }

                for (int i = 0; i < files.Length; i++)
                {
                    if (!helper.HasData($"SELECT 1 FROM pragma_table_info('serialData','db{i}') WHERE name = 'resultId';"))
                    {
                        helper.Execute($"ALTER TABLE db{i}.serialData ADD resultId TEXT;");
                    }
                }

                string sql = string.Join(" UNION ALL ", GetTestItemSqlsByFiles(this.selectResultId, files));

                string sortName = "time", sortDirection = "DESC";

                string baseSql = $"SELECT sn, position, model,step, limitDown, limitUp,unit, value,status,time FROM ({sql}) as _temp";

                sql = $"{baseSql} ORDER BY {sortName} {sortDirection}";

                return helper.GetDataTable(sql);


                //for (int i = 0; i < files.Length; i++)
                //{
                //    helper.Execute($"DETACH DATABASE db{i}");
                //}
            }
        }

        private void SelectPage(long page, long pageSize)
        {
            try
            {
                var files = GetFiles(startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime);
                if (files == null || files.Length <= 0)
                {
                    return;
                }
                using (var helper = new Sqlite3Helper(files[0]))
                {
                    for (int i = 0; i < files.Length; i++)
                    {
                        var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                        helper.Execute(attach);
                    }

                    for (int i = 0; i < files.Length; i++)
                    {
                        if (!helper.HasData($"SELECT 1 FROM pragma_table_info('testResult','db{i}') WHERE name = 'resultId';"))
                        {
                            helper.Execute($"ALTER TABLE db{i}.testResult ADD resultId TEXT;");
                        }
                    }

                    string sql = string.Join(" UNION ALL ", GetResultSqlsByFiles(files, startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime));
                    string sortName = "time", sortDirection = "DESC";

                    if (sorter != null)
                    {
                        sortName = sorter.name;
                        sortDirection = sorter.sort;
                    }
                    string countSql = $"SELECT COUNT(1) FROM ({sql}) as _temp";
                    long total = (long)helper.SelectOne(countSql);

                    string baseSql = $"SELECT id,sn,model,station,position,result,time,resultId FROM ({sql}) as _temp";



                    sql = $"{baseSql} ORDER BY {sortName} {sortDirection} LIMIT {pageSize} OFFSET {(page - 1) * pageSize}";

                    var dt = helper.GetDataTable(sql);

                    TotalPage = total / pageSize + (total % pageSize > 0 ? 1 : 0);
                    resultTable.ItemsSource = dt.DefaultView;

                    sql = $"SELECT result,COUNT(1) as num FROM ({baseSql}) GROUP BY result";
                    var nums = helper.GetDataTable(sql);

                    long all = 0, pass = 0, fail = 0;
                    foreach (DataRow row in nums.Rows)
                    {
                        if ("ok".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase)
                            || "pass".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase))
                        {
                            pass += (long)row["num"];
                            all += (long)row["num"];
                        }
                        if ("ng".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase)
                            || "fail".Equals(row["result"].ToString(), StringComparison.OrdinalIgnoreCase))
                        {
                            fail += (long)row["num"];
                            all += (long)row["num"];
                        }
                    }

                    TotalCount = all;
                    PassCount = pass;
                    FailCount = fail;

                    //for (int i = 0; i < files.Length; i++)
                    //{
                    //    helper.Execute($"DETACH DATABASE db{i}");
                    //}
                }
            }
            catch (Exception ex)
            {
                log.Error("Select TestResult Error.", ex);

            }
        }

        private void RefreshTestItem(string resultId, long page, int pageSize)
        {
            try
            {
                if (string.IsNullOrEmpty(resultId))
                {
                    testItemTable.ItemsSource = new DataTable().DefaultView;
                    return;
                }

                var files = GetFiles(startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime);
                if (files == null || files.Length <= 0)
                {
                    return;
                }

                string sql = string.Join(" UNION ALL ", GetTestItemSqlsByFiles(resultId, files));
                string sortName = "time", sortDirection = "DESC";
                if (itemSorter != null)
                {
                    sortName = itemSorter.name;
                    sortDirection = itemSorter.sort;
                }

                mask2.ShowMask(null, () =>
                {
                    using (var helper = new Sqlite3Helper(files[0]))
                    {
                        for (int i = 0; i < files.Length; i++)
                        {
                            var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                            helper.Execute(attach);
                        }

                        for (int i = 0; i < files.Length; i++)
                        {
                            if (!helper.HasData($"SELECT 1 FROM pragma_table_info('serialData','db{i}') WHERE name = 'resultId';"))
                            {
                                helper.Execute($"ALTER TABLE db{i}.serialData ADD resultId TEXT;");
                            }
                        }


                        string countSql = $"SELECT COUNT(1) FROM ({sql}) as _temp";
                        long total = (long)helper.SelectOne(countSql);

                        string baseSql = $"SELECT sn, model,step, limitDown, limitUp,unit, value,status,time FROM ({sql}) as _temp";

                        sql = $"{baseSql} ORDER BY {sortName} {sortDirection} LIMIT {pageSize} OFFSET {(page - 1) * pageSize}";

                        var dt = helper.GetDataTable(sql);


                        //for (int i = 0; i < files.Length; i++)
                        //{
                        //    helper.Execute($"DETACH DATABASE db{i}");
                        //}

                        Dispatcher.BeginInvoke(() =>
                        {
                            TestItemTotalPage = total / pageSize + (total % pageSize > 0 ? 1 : 0);
                            testItemTable.ItemsSource = dt.DefaultView;
                        }, System.Windows.Threading.DispatcherPriority.Normal);
                    }
                }, (ex) =>
                {
                    log.Error("Select TestItem Error.", ex);
                },
                "查询中...");
            }
            catch (Exception ex)
            {
                log.Error("Try Select TestItem Error.", ex);
            }
        }

        public string[] GetResultSqlsByFiles(string[] files, DateTime? startTime, DateTime? endTime)
        {
            string sn = string.Empty, position = string.Empty, model = string.Empty, result = string.Empty;
            Dispatcher.Invoke(() =>
            {
                sn = snInput.Text;
                position = positionInput.Text;
                model = modelInput.Text;
                result = resultInput.Text;
            });

            string[] sqls = new string[files.Length];
            List<string> querys = new List<string>();
            if (!string.IsNullOrEmpty(sn))
            {
                querys.Add($"sn = '{sn}'");
            }

            if (!string.IsNullOrEmpty(position))
            {
                querys.Add($"position = '{position}'");
            }

            if (!string.IsNullOrEmpty(model))
            {
                querys.Add($"model = '{model}'");
            }

            if (!string.IsNullOrEmpty(result))
            {
                querys.Add($"result = '{result}'");
            }

            if (startTime != null)
            {
                querys.Add($"time >= '{startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}'");
            }

            if (endTime != null)
            {
                querys.Add($"time < '{endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}'");
            }

            string segment = "";
            if (querys.Count > 0)
            {
                segment = $" WHERE {string.Join(" AND ", querys)}";
            }

            for (int i = 0; i < files.Length; i++)
            {
                sqls[i] = $"SELECT id,sn,model,station,position,result,time,resultId FROM db{i}.testResult {segment}";
            }
            return sqls;
        }

        private string[] GetTestItemSqlsByFiles(string resultId, string[] files)
        {
            List<string> querys = new List<string>() {
                $"resultId = '{resultId}'"
            };
            string step = string.Empty, status = string.Empty;
            Dispatcher.Invoke(() =>
            {
                step = stepInput.Text;
                status = statusInput.Text;
            });


            if (!string.IsNullOrEmpty(step))
            {
                querys.Add($"step = '{step}'");
            }

            if (!string.IsNullOrEmpty(status))
            {
                querys.Add($"status = '{status}'");
            }

            string segment = string.Join(" AND ", querys); ;

            string[] sqls = new string[files.Length];
            for (int i = 0; i < files.Length; i++)
            {
                sqls[i] = $"SELECT sn, position, model,step, limitDown, limitUp,unit, value,status,time FROM db{i}.serialData WHERE {segment}";
            }
            return sqls;
        }

        public string[] GetSource(string[] files, string name, string tableName, string value)
        {
            try
            {
                if (files == null || files.Length <= 0)
                {
                    return new string[0];
                }
                string[] GetModelSqls()
                {
                    string[] sqls = new string[files.Length];
                    for (int i = 0; i < files.Length; i++)
                    {
                        sqls[i] = $"SELECT DISTINCT {name} FROM db{i}.{tableName} WHERE {name} LIKE '%{value}%'";
                    }
                    return sqls;
                }

                using (var helper = new Sqlite3Helper(files[0]))
                {
                    for (int i = 0; i < files.Length; i++)
                    {
                        var attach = $"ATTACH DATABASE '{files[i]}' AS 'db{i}'";
                        helper.Execute(attach);
                    }
                    string sql = string.Join(" UNION ", GetModelSqls());
                    sql = $"SELECT DISTINCT {name} FROM ({sql}) LIMIT 10";

                    var dt = helper.GetDataTable(sql);
                    return dt.Select().Select(row => row[name].ToString()).ToArray();
                }
                return new string[0];
            }
            catch (Exception ex)
            {
                return new string[0];
            }
        }


        private void SearchTextChanged(object sender, Panuon.WPF.SearchTextChangedRoutedEventArgs e)
        {
            SearchBox sb = sender as SearchBox;
            string tag = sb.Tag.ToString(), value = sb.Text;
            var files = GetFiles(startTimeInput.SelectedDateTime, endTimeInput.SelectedDateTime);
            Task.Run(() =>
            {
                string[] items = new string[0];
                if (tag == "step" || tag == "status")
                {
                    items = GetSource(files, tag, "serialData", value);
                }
                else
                {
                    items = GetSource(files, tag, "testResult", value);
                }
                Dispatcher.BeginInvoke(new Action(() =>
                {
                    sb.ItemsSource = items;
                }), System.Windows.Threading.DispatcherPriority.Normal);
            });
        }

        private void Grid_MouseLeftButtonUp(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            var border = sender as Border;
            var header = border.Tag as DataGridColumnHeader;
            var col = header.Column as ICustomSortColumn;

            if (col == null) return;

            col.UpdateOther(null);

            var dataGrid = col.GetOwner();

            if (col.IsAsc == null)
            {
                col.IsAsc = true;

                if (dataGrid == resultTable)
                {
                    sorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Ascending, sort = "ASC" };
                }
                else { 
                    itemSorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Ascending, sort = "ASC" };
                }
                
            }
            else if (col.IsAsc == true)
            {
                col.IsAsc = false;
                if (dataGrid == resultTable)
                {
                    sorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Descending, sort = "DESC" };
                }
                else
                {
                    itemSorter = new SortParam { name = header.Column.SortMemberPath, direction = ListSortDirection.Descending, sort = "DESC" };
                }
            }
            else
            {
                col.IsAsc = null;
                if (dataGrid == resultTable)
                {
                    sorter = null;
                }
                else
                {
                    itemSorter = null;
                }
            }

            if (dataGrid == resultTable)
            {
                if (CurrentPage == 1)
                {
                    SelectPage(1, this.pageSize);
                }
                else
                {
                    CurrentPage = 1;
                }
            }
            else
            {
                if (TestItemPage == 1)
                {
                    RefreshTestItem(this.selectResultId, 1, this.pageSize);
                }
                else
                {
                    TestItemPage = 1;
                }
            }
        }

        private void page_CurrentPageChanged(object sender, Panuon.WPF.SelectedValueChangedRoutedEventArgs<int> e)
        {
            if (sender == page)
            {
                SelectPage(e.NewValue, this.pageSize);
            }
            else
            {
                RefreshTestItem(this.selectResultId, e.NewValue, this.pageSize);
            }
        }

        private string selectResultId;

        partial void OnSelectedResultChanged(object value)
        {
            if (value != null)
            {
                return;
            }
            var selectedRow = value as DataRowView;
            var resultId = selectedRow?["resultId"]?.ToString();
            this.SelectedStatus = selectedRow?["result"]?.ToString();
            this.selectResultId = resultId;

            if (TestItemPage == 1)
            {
                RefreshTestItem(resultId, 1, this.pageSize);
            }
            else
            {
                TestItemPage = 1;
            }
        }

        private void resultTable_MouseDoubleClick(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            var selectedRow = this.SelectedResult as DataRowView;
            var resultId = selectedRow?["resultId"]?.ToString();
            this.SelectedStatus = selectedRow?["result"]?.ToString();
            this.selectResultId = resultId;

            if (TestItemPage == 1)
            {
                RefreshTestItem(resultId, 1, this.pageSize);
            }
            else
            {
                TestItemPage = 1;
            }
        }
    }
}