using HotelPms.Share.Data;
using HotelPms.Share.Data.Script;
using HotelPms.Share.IO;
using HotelPms.Share.Windows.Util;
using System.Reflection;
using System.Text;

namespace HotelPms.SourceFactory.Table
{
    /// <summary>
    /// Excelのテーブル定義書より、
    /// SqlScriptを作成する
    /// </summary>
    public class SqlFactory : IDisposable
    {
        public DBConnectItem? DBSetting { get; set; }
        public string ExcelFile { get; set; } = string.Empty;

        public void Dispose()
        {

        }

        public bool CreateByExcel(string file, DBConnectItem dbSetting)
        {
            try
            {
                ExcelFile = file;
                DBSetting = dbSetting;
                using (NPOIExcel excel = new NPOIExcel(file))
                {
                    for (int i = 0; i < excel.Workbook.NumberOfSheets; i++)
                    {
                        excel.SetCurrentSheet(i);
                        if (!IsTableSheet(excel)) { continue; }
                        CreateBySheet(excel);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 対象シートはテーブル定義のシートかどうか
        /// B4 = "テーブル名" ⇒　TABLE = G4
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private bool IsTableSheet(NPOIExcel excel)
        {
            bool result = false;
            try
            {
                if("テンプレート" == excel.Sheet.SheetName
                    || excel.GetCellForceString("B4") != "テーブル名"
                    || excel.GetCellForceString("B6") != "No"
                    || excel.GetCellForceString("B7") != "1"
                    || excel.GetCellForceString("D6") != "項目名"
                    || excel.GetCellForceString("K6") != "説明"
                    || excel.GetCellForceString("S6") != "型"
                    || excel.GetCellForceString("X6") != "PK") 
                { 
                    result = false;
                    return result;
                }

                result = true;
                return result;
            }
            catch (Exception ex)
            {
                OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{ex.Message}");
                result = false;
                return result;
            }
            finally
            {
                OperationLog.Instance.WriteLog($"{excel.Sheet.SheetName} ⇒ テーブル定義シート：{ (result ? "Yes" : "No") }");
            }
        }

        /// <summary>
        /// シート情報よりテーブル作成する
        /// </summary>
        /// <param name="sheet"></param>
        public bool CreateBySheet(NPOIExcel excel)
        {
            try
            {
                OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{excel.Sheet.SheetName}");
                string[] tables = excel.GetCellString("G4").Split(new char[] { ',' });

                using (TableCreator creator = new TableCreator())
                using (DataAccess dataAccess = new MsSqlNet(DBSetting))
                {

                    //DB中のデータ取得
                    var dbFields = dataAccess.GetDataReader<Dictionary<string, Field>>($"EXECUTE [dbo].[GetTableDefine] '{tables[0]}'", (reader, data) =>
                    {
                        while (reader.Read())
                        {
                            var item = new Field(reader);
                            data.Add(item.Name, item);
                        }
                    });

                    bool isNew = dbFields == null || dbFields.Count == 0;
                    StringBuilder sqlEdit = new StringBuilder();

                    //Excelを読込む
                    List<Field> curFields = new List<Field>();
                    int r = 0;
                    for (int i = 6; i < excel.Sheet.LastRowNum; i++)
                    {
                        int no = excel.GetCellInt($"B{i + 1}");
                        if (no != (r + 1)) { continue; }   //必ず連続
                        r++;
                        string colName = excel.GetCellString($"D{i + 1}");
                        if (colName.Length == 0) { break; }

                        string pk = excel.GetCellString($"X{i + 1}").ToUpper();
                        var curItem = new Field
                        {
                            Name = colName,
                            Description = excel.GetCellString($"K{i + 1}"),
                            DataType = excel.GetCellString($"S{i + 1}"),
                            IsPrimaryKey = pk.Length > 0,
                            IsIdentity = pk == "AUTO"
                        };

                        creator.Fields.Add(curItem);  //無条件追加、データ型変更した場合、テーブル再作成になる

                        Field dbItem;
                        if (dbFields != null && dbFields.TryGetValue(colName, out dbItem))
                        {
                            //突合(追加・変更分のみ)
                            int ret = dbItem.CompareTo(curItem);
                            if (ret == 1 || ret == 12)
                            {
                                //削除
                                sqlEdit.AppendLine($@"IF EXISTS(SELECT * FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('*TableName*') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = '{curItem.Name}' AND [object_id] = OBJECT_ID('*TableName*'))) 
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'*TableName*', @level2type=N'COLUMN',@level2name=N'{curItem.Name}';");


                                if (curItem.Description.Length > 0)
                                {
                                    sqlEdit.AppendLine($"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{curItem.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'*TableName*', @level2type=N'COLUMN',@level2name=N'{curItem.Name}';");
                                }
                            }

                            if (ret == 2 || ret == 12)
                            {
                                //タイプ変更があったら、削除してから、再作成のほうが増し
                                isNew = true;
                                //例：DateTime⇒intではダメ
                                //sqlEdit.AppendLine($"ALTER TABLE [*TableName*] ALTER COLUMN {curItem.Name} {curItem.DataType} NULL;");
                                //sqlEdit.AppendLine($"ALTER TABLE [*TableName*] DROP COLUMN {curItem.Name};");
                                //sqlEdit.AppendLine($"ALTER TABLE [*TableName*] ADD {curItem.Name} {curItem.DataType} NULL;");
                            }
                        }
                        else
                        {
                            //新規追加
                            sqlEdit.AppendLine($"ALTER TABLE [*TableName*] ADD {curItem.Name} {curItem.DataType} NULL;");
                        }
                    }
                    
                    if(!isNew && sqlEdit.Length == 0) { return true; }

                    //作成
                    string sql = String.Empty;
                    foreach (string tableName in tables)
                    {
                        if (isNew)
                        {
                            creator.Name = tableName;
                            creator.Description = excel.GetCellString("G5");
                            creator.PartitionSchema = excel.GetCellString("X4");
                            creator.PartitionKey = excel.GetCellString("AH4");
                            sql = creator.ToString();
                        }
                        else
                        {           
                            sql = sqlEdit.ToString().Replace("*TableName*", tableName);
                        }

                        int ret = dataAccess.ExecuteNonQuery(sql);
                        OperationLog.Instance.WriteLog(sql);
                        if (ret == -1) { return false; }
                    }
                }               
                return true;
            }
            catch (Exception ex)
            {
                OperationLog.Instance.WriteLog($"{MethodInfo.GetCurrentMethod()}：{ex.Message}");
                return false;
            }
        }

    }
}