using HotelPms.Share.Data;
using HotelPms.Share.Util;
using HotelPms.Share.Windows.Util;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace HotelPms.SourceFactory
{
    public partial class TableToExcel : Form
    {
        public TableToExcel()
        {
            InitializeComponent();
        }

        private void OpenExcel_Click(object sender, EventArgs e)
        {
            openFileDialog1.InitialDirectory = Application.StartupPath;
            if (openFileDialog1.ShowDialog() != DialogResult.OK) { return; }
            txtExcelPath.Text = openFileDialog1.FileName;

            cmbSheet.Items.Clear();
            using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
            {
                for (int i = 0; i < excel.Workbook.NumberOfSheets; i++)
                {
                    cmbSheet.Items.Add(excel.Workbook.GetSheetAt(i).SheetName);
                }
            }

        }

        private void btnCopySheet_Click(object sender, EventArgs e)
        {
            using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
            {
                ISheet sheet = excel.Workbook.GetSheetAt(excel.Workbook.GetSheetIndex(cmbSheet.SelectedItem.ToString()));
                sheet.CopyTo(excel.Workbook, txtNewSheet.Text, true, true);   //2.5.2のNET5.0版にBUGがあって、keepFormulasを使えない 2.5.3でFixした
                //sheet.CopyTo(excel.Workbook, txtNewSheet.Text, true, false);
                excel.Save();

                MessageBox.Show("OK");
            }
            
        }

        private void btnOpenDB_Click(object sender, EventArgs e)
        {
            using (DataAccess dataAccess = new MsSqlNet(new DBConnectItem() { HostName = txtHost.Text, UserID = txtUser.Text, Password = txtPassword.Text, DBName = txtDB.Text }))
            {
                using (DataTable data = dataAccess.GetTableList())
                {
                    cmbTable.Items.Clear();
                    foreach(DataRow row in data.Rows)
                    {
                        cmbTable.Items.Add(row["Name"]);
                    }

                    if (cmbTable.Items.Count > 0) { cmbTable.SelectedIndex = 0; }
                }
            }
        }

        private void CopyPage(NPOIExcel excel, int beginRowdx, int pageRowCount)
        {
            int j = beginRowdx;
            for (int i = 0; i < pageRowCount; i++)
            {
                excel.Sheet.CopyRow(i, j);
                j++;
            }
        }

        private void SetTableToSheet(DataTable data, NPOIExcel excel, string sheetName)
        {
            excel.SetCurrentSheet(excel.Workbook.GetSheetIndex(sheetName));

            int pageRowCount = 50;
            int pageNo = 1;

            int rowIdx = 7;
            foreach (DataRow row in data.Rows)
            {
                if (rowIdx % pageRowCount == 0)
                {
                    //ページ最後の空行
                    pageNo++;

                    //ページコピー
                    if ((excel.Sheet.LastRowNum + 1) < pageNo * pageRowCount)
                    {
                        CopyPage(excel, rowIdx + 1, pageRowCount);
                    }

                    rowIdx += 7;
                }

                excel.SetCell($"G{rowIdx - 3}", sheetName);
                excel.SetCell($"AC{rowIdx - 2}", DateTime.Now.ToString("yyyy/MM/dd"));

                excel.SetCell($"D{rowIdx}", row["Field"].ToString());  //TableName
                int len = CConvert.ToInt(row["max_length"]);
                string type = row["type"].ToString().ToUpper();
                if (type == "NVARCHAR") { len /= 2; }
                else if (type == "INT" || type == "DATETIME" || type == "SMALLDATETIME" || type == "SMALLINT" || type == "TINYINT" || type == "BIT") { len = -1; }
                string lenText = (len == -1 ? string.Empty : $"({len})");

                excel.SetCell($"S{rowIdx}", $"{row["type"].ToString().ToUpper()}{lenText}");  //型(長さ)
                excel.SetCell($"X{rowIdx}", row["PKFlg"].ToString() == "1" ? "〇" : string.Empty);  //PK
                rowIdx++;  //次の行番号
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if(cmbSheet.SelectedIndex == -1)
            {
                MessageBox.Show("シートを選んでください。");
                cmbSheet.Focus();
                return; 
            }

            if (cmbTable.SelectedIndex == -1)
            {
                MessageBox.Show("テーブルを選んでください。");
                cmbSheet.Focus();
                return;
            }

            using (MsSqlNet dataAccess = new MsSqlNet(new DBConnectItem() { HostName = txtHost.Text, UserID = txtUser.Text, Password = txtPassword.Text, DBName = txtDB.Text }))
            {
                using (DataTable data = dataAccess.GetFieldList(cmbTable.SelectedItem.ToString()))
                {
                    using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
                    {
                        SetTableToSheet(data, excel, cmbSheet.SelectedItem.ToString());
                        excel.Save();
                        MessageBox.Show("OK");
                    }
                }
            }
        }

        private bool ExistsKey(string data, Dictionary<string, int> keyDict)
        {
            if (keyDict.Count == 0) { return true; }
            foreach(KeyValuePair<string, int> item in keyDict)
            {
                if (data.StartsWith(item.Key)) { return true; }
            }
            return false;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Dictionary<string, int> keyDict = GetTableKey();

            using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
            {
                excel.SetCurrentSheet(excel.Workbook.GetSheetIndex("目次"));
                int rowIdx = 5;
                int i = 1;
                foreach (string item in cmbTable.Items)
                {
                    if (!ExistsKey(item, keyDict)) { continue; }
                    excel.SetCell($"D{rowIdx}", item);  //TableName
                    rowIdx++;
                    i++;
                    if (i == 52) 
                    {
                        rowIdx += 5;
                        i = 1;
                    }
                }

                excel.Save();
                MessageBox.Show("OK");
            }
        }

        private Dictionary<string, int> GetTableKey()
        {
            Dictionary<string, int> keyDict = new Dictionary<string, int>();
            if (txtTableConditon.Text.Length > 0)
            {
                string[] condList = txtTableConditon.Text.Split(new char[] { ',' });
                foreach (string key in condList)
                {
                    keyDict.Add(key, 0);
                }
            }
            return keyDict;
        }


        private void button3_Click(object sender, EventArgs e)
        {
            if (cmbTable.Items.Count == 0)
            {
                MessageBox.Show("テーブル一覧がない。");
                cmbSheet.Focus();
            }

            Dictionary<string, int> keyDict = GetTableKey();

            using (MsSqlNet dataAccess = new MsSqlNet(new DBConnectItem() { HostName = txtHost.Text, UserID = txtUser.Text, Password = txtPassword.Text, DBName = txtDB.Text }))
            {
                using (NPOIExcel excel = new NPOIExcel(txtExcelPath.Text))
                {
                    int i = 0;
                    foreach (string tableName in cmbTable.Items)
                    {
                        if (!ExistsKey(tableName, keyDict)) { continue; }
                        using (DataTable data = dataAccess.GetFieldList(tableName))
                        {
                            SetTableToSheet(data, excel, tableName);
                        }
                        i++;
                        lblStatus.Text = i.ToString();
                        lblStatus.Refresh();
                    }
                    excel.Save();
                    MessageBox.Show("OK");
                }
            }
        }

        private void txtExcelPath_TextChanged(object sender, EventArgs e)
        {

        }

        private void cmbSheet_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}