using HMI.Base;
using HMI.Core.Util;
using HMI.DataViewer.Entity;
using HMI.UI.Converter;
using HMI.UI.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Forms;

namespace HMI.DataViewer
{
    

    /// <summary>
    /// UserControl1.xaml 的交互逻辑
    /// </summary>
    public partial class DataViewer : PluginControl
    {
        public DataViewer()
        {
            DataContext = this;
            InitializeComponent();
            DefaultDate();
        }

        private string SourceFilePath;

        private ObservableCollection<DataBase_Setting> settings = new ObservableCollection<DataBase_Setting>();
        public ObservableCollection<DataBase_Setting> Settings
        {
            get => settings;
            set { Set(ref settings, value); }
        }

        private ObservableCollection<QueryData> datas = new ObservableCollection<QueryData>();
        public ObservableCollection<QueryData> Datas
        {
            get => datas;
            set { Set(ref datas, value); }
        }

        private Dictionary<string, DataBase_Setting> Map = new Dictionary<string, DataBase_Setting>();

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            System.Windows.Controls.Button btn = sender as System.Windows.Controls.Button;
            switch (btn.Name)
            {
                case "addBtn":
                    OpenSettingModal(-1, new DataBase_Setting());
                    break;
                case "editBtn":
                    EditSettingModal();
                    break;
                case "rfhBtn":
                    LoadData();
                    break;
                case "delBtn":
                    DeleteDatasource();
                    break;
                case "srhBtn":
                    SearchData();
                    break;
                case "resetBtn":
                    ResetDataTable();
                    break;
                case "exportBtn":
                    ExportData();
                    break;
                default:
                    break;
            }
        }

        private void DeleteDatasource()
        {
            try
            {
                var key = programInput.Text;
                if (string.IsNullOrEmpty(key))
                {
                    ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.SelectDatasourceFirst"));
                    return;
                }
                if (!Map.ContainsKey(key))
                {
                    ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
                    return;
                }
                DataBase_Setting setting = Map[key];
                if (MsgBoxHelper.WarnMessage(Window.GetWindow(this), this.lang("HMI.MakeSureDeleteSingle", setting.Program))
                    == MessageBoxResult.OK)
                {
                    var temp = XmlHelper.DeepClone(Settings);
                    temp.RemoveAt(Settings.IndexOf(setting));
                    XmlHelper.SerializeToXml(SourceFilePath, temp);
                    ToastHelper.Success(Window.GetWindow(this), this.lang("App.DeleteSuccess"));
                    LoadData();
                }
            }
            catch (Exception ex)
            {
                log.Error("Delete Datasource Failed.", ex);
                ToastHelper.Success(Window.GetWindow(this), $"{this.lang("App.DeleteFailed")}: {ex.Message}");
            }
        }

        private void EditSettingModal()
        {
            var key = programInput.Text;
            if (string.IsNullOrEmpty(key))
            {
                ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.SelectDatasourceFirst"));
                return;
            }
            if (!Map.ContainsKey(key))
            {
                ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
                return;
            }
            DataBase_Setting setting = Map[key];
            OpenSettingModal(Settings.IndexOf(setting), setting);
        }

        private void OpenSettingModal(int index, DataBase_Setting data)
        {
            DbSettingModal modal = new DbSettingModal(log)
            {
                Title = this.lang("Dv.AddDatasource"),
                Owner = Window.GetWindow(this)
            };
            if (modal.Open(index, data, Settings, SourceFilePath) == true)
            {
                LoadData();
            }
        }

        private void DefaultDate()
        {
            var now = DateTime.Now;
            startTimeInput.SelectedDateTime = DateTime.Parse(now.ToString("yyyy-MM-dd"));
            endTimeInput.SelectedDateTime = DateTime.Parse(now.AddDays(1).ToString("yyyy-MM-dd"));
        }

        private void ResetDataTable()
        {
            DefaultDate();
            dataTable.ItemsSource = null;
            foreach (var data in Datas)
            {
                data.Value = null;
            }
        }

        public override void SetBaseFolder(string folder)
        {
            base.SetBaseFolder(folder);
            SourceFilePath = Path.Combine(BaseFolder, "SourceData.setting");
        }

        private void PluginControl_Loaded(object sender, RoutedEventArgs e)
        {
            LoadData();
            Loaded -= PluginControl_Loaded;
        }

        private void LoadData()
        {
            ObservableCollection<DataBase_Setting> settings = new ObservableCollection<DataBase_Setting>();
            if (File.Exists(SourceFilePath))
            {
                settings = XmlHelper.DeserializeFromXml<ObservableCollection<DataBase_Setting>>(SourceFilePath);
            }
            List<string> names = new List<string>();

            Map.Clear();
            foreach (var data in settings)
            {
                Map[data.Program] = data;
                names.Add(data.Program);
            }
            programInput.ItemsSource = names;
            //ResetInput();
            UpdateAdvanced();
            Settings = settings;
        }

        private void ResetInput()
        {
            programInput.Text = string.Empty;
            Datas.Clear();

            var temp = dataTable.Columns[0];
            HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
            dataTable.Columns.Clear();
            dataTable.Columns.Add(temp);
            currentColumns?.Clear();
        }

        private void UpdateAdvanced()
        {
            var key = programInput.Text;
            if (!Map.ContainsKey(key))
            {
                ResetInput();
                return;
            }
            DataBase_Setting setting = Map[key];
            Datas = new ObservableCollection<QueryData>(
                setting?.Condition?.Select(d => new QueryData { Key = d.Key, Name = d.Desc, Condition = "=" }).ToList());

            var temp = dataTable.Columns[0];
            HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
            dataTable.Columns.Clear();
            dataTable.Columns.Add(temp);

            List<DataGridTextColumn> columns = new List<DataGridTextColumn>();
            foreach (var data in Datas)
            {
                columns.Add(AddDataGridTextColumn(data.Name, data.Key));
            }
            currentColumns = columns;
        }

        private void programInput_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (e.AddedItems == null || e.AddedItems.Count <= 0) return;
            var key = e.AddedItems[0].ToString();
            if (!Map.ContainsKey(key))
            {
                ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
                programInput.Text = string.Empty;
                return;
            }
            DataBase_Setting setting = Map[key];
            Datas = new ObservableCollection<QueryData>(
                setting?.Condition?.Select(d => new QueryData { Key = d.Key, Name = d.Desc, Condition = "=" }).ToList());

            var temp = dataTable.Columns[0];
            HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
            dataTable.Columns.Clear();
            dataTable.Columns.Add(temp);

            List<DataGridTextColumn> columns = new List<DataGridTextColumn>();
            foreach (var data in Datas)
            {
                columns.Add(AddDataGridTextColumn(data.Name, data.Key));
            }
            currentColumns = columns;
        }

        private List<DataGridTextColumn> currentColumns;

        private DataGridTextColumn AddDataGridTextColumn(string header, string bindingPath)
        {
            DataGridTextColumn textColumn = new DataGridTextColumn
            {
                Header = header,
                Binding = new System.Windows.Data.Binding(bindingPath)
                {
                    Converter = DateStrConverter.Instance
                },
                Width = new DataGridLength(1, DataGridLengthUnitType.Star)
            };
            dataTable.Columns.Add(textColumn);
            return textColumn;
        }

        private void dataTable_PreviewMouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            //// 获取点击的元素
            //DependencyObject dep = (DependencyObject)e.OriginalSource;

            //// 遍历找到 DataGridColumnHeader
            //while (dep != null && !(dep is DataGridColumnHeader) && !(dep is DataGridRow))
            //{
            //    dep = VisualTreeHelper.GetParent(dep);
            //}
            //HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);

            //if (dep is DataGridColumnHeader columnHeader)
            //{
            //    // 获取列索引
            //    int columnIndex = columnHeader.Column.DisplayIndex;

            //    // 清除已有选择
            //    dataTable.SelectedCells.Clear();

            //    // 遍历所有行，选中该列的所有单元格
            //    foreach (var item in dataTable.Items)
            //    {
            //        DataGridCellInfo cellInfo = new DataGridCellInfo(item, columnHeader.Column);
            //        dataTable.SelectedCells.Add(cellInfo);
            //    }
            //    HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, columnHeader.Column);

            //    e.Handled = true; // 防止默认行为
            //}
            //else if (dep is DataGridRow row)
            //{
            //    // 清除已有选择
            //    dataTable.SelectedCells.Clear();
            //    dataTable.SelectedItem = row.Item;
            //    e.Handled = true;
            //}
        }

        private DataTable DoSearchData(DataBase_Setting setting, List<QueryData> datas)
        {
            string conditon = "WHERE 1=1 ";
            string fields = string.Join(",", Datas?.Select(d => $"[{d.Key}]").ToList());
            if (datas != null && datas.Count > 0)
            {
                conditon = "WHERE " + string.Join(" AND ", datas.Select(d => $"[{d.Key}] {d.Condition} '{d.Value}'").ToList());
            }

            string timeCol = setting.TimeCol;
            if (string.IsNullOrEmpty(timeCol))
            {
                timeCol = "time";
            }

            if (!string.IsNullOrEmpty(startTimeInput.Text))
            {
                conditon += $" AND {timeCol} >= '{startTimeInput.Text}'";
            }
            if (!string.IsNullOrEmpty(endTimeInput.Text))
            {
                conditon += $" AND {timeCol} < '{endTimeInput.Text}'";
            }

            string sql = $"SELECT {fields} FROM {setting.TableName} {conditon}";

            DataTable dt = new DataTable();

            if (string.IsNullOrEmpty(setting.Pattern))
            {
                try
                {
                    using (var helper = new Sqlite3Helper(setting.DBFilePath))
                    {
                        dt = helper.GetDataTable(sql);
                    }
                }
                catch (Exception ex)
                {
                    log.Error($"Select Data Failed. SQL: {sql}", ex);
                    throw ex;
                }
            }
            else
            {
                // 通配符
                try
                {
                    string dir = Path.GetDirectoryName(setting.DBFilePath);
                    string startName = $"Data_{startTimeInput.SelectedDateTime?.ToString("yyyyMMdd")}";
                    string endName = $"Data_{endTimeInput.SelectedDateTime?.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();
                    if (oldFiles == null || oldFiles.Length <= 0)
                    {
                        return dt;
                    }

                    foreach (string file in oldFiles)
                    {
                        try
                        {
                            using (var helper = new SqliteHelper(file))
                            {
                                var temp = helper.GetDataTable(sql);
                                dt.Merge(temp);
                            }
                        }
                        catch
                        {

                        }
                    }
                    return dt;
                }
                catch (Exception ex)
                {
                    log.Error($"Select Data Failed. SQL: {sql}", ex);
                    throw ex;
                }
            }
            //HMI.UI.DpUtil.DataGridHelper.SetSelectedColumn(dataTable, null);
            //dt.
            return dt;
        }

        private void SearchData()
        {
            string program = programInput.Text;
            if (!Map.ContainsKey(program))
            {
                ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
                return;
            }

            var datas = Datas?.Where(d => !string.IsNullOrEmpty(d.Value)).ToList();

            if ((datas == null || datas.Count <= 0) &&
                (string.IsNullOrEmpty(startTimeInput.Text) || string.IsNullOrEmpty(endTimeInput.Text)))
            {
                ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.QueryCantBeEmpty"));
                return;
            }

            DataBase_Setting setting = Map[program];

            try
            {
                dataTable.ItemsSource = DoSearchData(setting, datas).DefaultView;
            }
            catch (Exception ex)
            {
                ToastHelper.Error(Window.GetWindow(this), $"{this.lang("App.QueryFailed")}: {ex.Message}");
            }
        }

        private void ExportData()
        {
            int maxRow = 1048575;
            try
            {
                string program = programInput.Text;
                if (string.IsNullOrEmpty(program))
                {
                    ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.SelectDatasourceFirst"));
                    return;
                }

                if (!Map.ContainsKey(program))
                {
                    ToastHelper.Error(Window.GetWindow(this), this.lang("App.ParameterException"));
                    return;
                }

                var datas = Datas?.Where(d => !string.IsNullOrEmpty(d.Value)).ToList();
                if ((datas == null || datas.Count <= 0) &&
                    (string.IsNullOrEmpty(startTimeInput.Text) || string.IsNullOrEmpty(endTimeInput.Text)))
                {
                    ToastHelper.Warning(Window.GetWindow(this), this.lang("Dv.QueryCantBeEmpty"));
                    return;
                }

                DataBase_Setting setting = Map[program];
                DataTable dt;
                try
                {
                    dt = DoSearchData(setting, datas);
                }
                catch (Exception ex)
                {
                    ToastHelper.Error(Window.GetWindow(this), $"{this.lang("App.QueryFailed")}: {ex.Message}");
                    return;
                }

                if (currentColumns == null) return;
                var dataView = dt?.DefaultView;
                if (dataView == null) return;

                string fname = string.Empty;
                SaveFileDialog sfd = new SaveFileDialog();

                sfd.Filter = "";
                sfd.DefaultExt = "xlsx";
                sfd.FileName = setting.Program;
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    fname = sfd.FileName;
                }

                /*导出当前dataGridView中的所有数据到xls文件  
                1.引入库文件,新建lib文件夹，复制相关文件  
                2.在项目中添加对这几个dll的引用  
                3.在内存中建立 excel表文件  */
                using (FileStream fs = new FileStream(fname, FileMode.Create))
                {

                    XSSFWorkbook workbook = new XSSFWorkbook();

                    int row = 1;
                    for (int i = 0; i < dataView.Count; i++)
                    {
                        int sheetIndex = sheetIndex = i / maxRow;
                        string sheetName = $"sheet{sheetIndex + 1}";
                        var sheet = (XSSFSheet)workbook.GetSheet(sheetName);
                        if (sheet == null)
                        {
                            sheet = (XSSFSheet)workbook.CreateSheet(sheetName);
                            IRow row1 = sheet.CreateRow(0);
                            for (int j = 0; j < currentColumns.Count; j++)
                            {
                                row1.CreateCell(j).SetCellValue(currentColumns[j].Header.ToString());
                            }
                            row = 1;
                        }


                        IRow rowTemp = sheet.CreateRow(row++);
                        for (int j = 0; j < currentColumns.Count; j++)
                        {
                            string name = ((System.Windows.Data.Binding)currentColumns[j].Binding).Path.Path;
                            var value = dataView[i][name].ToString();
                            rowTemp.CreateCell(j).SetCellValue(value);
                        }
                    }

                    workbook.Write(fs);
                }
                ToastHelper.Success(Window.GetWindow(this), this.lang("App.ExportSuccess"));
            }
            catch (Exception ex)
            {
                log.Error("Export Data Failed.", ex);
                ToastHelper.Error(Window.GetWindow(this), $"{this.lang("App.ExportFailed")}: {ex.Message}");
            }
        }

        private void MenuItem_Click(object sender, RoutedEventArgs e)
        {
            DataGridColumn column = HMI.UI.DpUtil.DataGridHelper.GetSelectedColumn(dataTable);
        }
    }
}