using HotelPms.Share.Data;
using HotelPms.Share.IO;
using HotelPms.Share.Util;
using HotelPms.VerUp.Util;
using System.Diagnostics;
using System.Reflection;
using System.Text;
using System.Text.Json;

namespace HotelPms.VerUp.DataBase
{
    public class DBCore : IDisposable
    {
        /// <summary>
        /// 通知イベント
        /// </summary>
        public event MessageEventHandler Output;

        /// <summary>
        /// 設定ファイル
        /// </summary>
        public string ConfigFile { get; set; } = AppDomain.CurrentDomain.BaseDirectory + @"Sql\Config.json";

        /// <summary>
        /// DB接続情報
        /// </summary>
        public DBConnectItem? DBConnectItem { get; set; }

        /// <summary>
        /// エラー発生時続くかどうか
        /// </summary>
        public bool IgnoreError { get; set; } = false;  

        /// <summary>
        /// 設定
        /// </summary>
        private Config? m_Config;
        private string? rootPath = string.Empty;

        public DBCore(DBConnectItem connectItem) :this(connectItem, string.Empty)
        {            
        }

        public DBCore(DBConnectItem connectItem, string config)
        {
            DBConnectItem = connectItem;
            if(config.Length > 0) { ConfigFile = config; }
        }

        public void Dispose()
        {
         
        }

        /// <summary>
        /// 実行
        /// </summary>
        /// <returns></returns>
        public bool Execute()
        {
            try
            {
                if(!ReadConfig()) { return false; }

                int appVer = GetAppVersion();
                rootPath = Path.GetDirectoryName(ConfigFile);

                using (MsSqlNet access = new MsSqlNet(DBConnectItem))
                {
                    foreach (ConfigData item in m_Config.Data)
                    {
                        RaiseEvent($"Name：{item.Name},Type：{item.Type}");

                        // Sql特定
                        string sql = GetSql(item);
                        if (sql.Length == 0) { if (IgnoreError) { continue; } else { return false; } }
                        if (item.Type == (int)ConfigData.DataType.ExeReaderResult)
                        {
                            string ret = CConvert.ToString(access.ExecuteScalar(sql));
                            if(ret.Length > 0) { sql = ret; } else { continue; }
                        }

                        // 実行
                        if (access.ExecuteNonQuery(sql) == -1)
                        {
                            RaiseEvent($"{access.ErrNo}.{access.ErrInfo}");
                            if (!IgnoreError) { return false; }
                        }
                    }
                }
                return true;
            }
            catch(Exception ex)
            {
                RaiseEvent(ex.Message);
                return false;
            }
        }

        private string GetSql(ConfigData item)
        {
            try
            {
                if (item.Type == (int)ConfigData.DataType.ExeCommand || item.Type == (int)ConfigData.DataType.ExeReaderResult)
                {
                    return item.Name;
                }
                else
                {
                    string file = Path.Combine(rootPath, item.Name);
                    if (!File.Exists(file)) { return string.Empty; }

                    StringBuilder sql = new StringBuilder();
                    string name = Path.GetFileNameWithoutExtension(item.Name);
                    if (item.Type == (int)ConfigData.DataType.View)
                    {
                        sql.AppendLine($"IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[{name}]')) DROP View [dbo].[{name}];");
                    }
                    else if (item.Type == (int)ConfigData.DataType.Fuction)
                    {
                        sql.AppendLine($"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{name}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[{name}];");
                    }
                    else if (item.Type == (int)ConfigData.DataType.Procedure)
                    {
                        sql.AppendLine($"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{name}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{name}];");
                    }
                    sql.Append(File.ReadAllText(file, Encoding.UTF8));
                    return sql.ToString();
                }
            }
            catch(Exception ex)
            {
                RaiseEvent(ex.Message);
                return string.Empty;
            }
        }

        /// <summary>
        /// イベント通知
        /// </summary>
        /// <param name="msg"></param>
        private void RaiseEvent(string msg)
        {
            if(Output != null) { Output(this, new MessageEventArgs(msg)); }
            OperationLog.Instance.WriteLog(msg);
        }

        /// <summary>
        /// ConfigFileを読込
        /// </summary>
        /// <returns></returns>
        private bool ReadConfig()
        {
            try
            {
                if (!File.Exists(ConfigFile)) 
                {
                    RaiseEvent($"設定ファイルが存在しません。{Environment.NewLine}{ConfigFile}");
                    return false; 
                }
                string json = File.ReadAllText(ConfigFile, Encoding.UTF8);
                m_Config = JsonSerializer.Deserialize<Config>(json);                
                return true;
            }
            catch (Exception ex)
            {
                RaiseEvent(ex.Message);
                return false;
            }
        }

        private int GetDbVersion(MsSqlNet access)
        {
            try
            {
                return CConvert.ToInt(access.ExecuteScalar("SELECT TOP 1 Version FROM S_Config"));
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// Exeのバージョン番号を取得する
        /// </summary>
        /// <returns></returns>
        private int GetAppVersion()
        {
            string verInfo = string.Empty;
            string[] ver = Assembly.GetEntryAssembly().GetName().Version.ToString().Split(new char[] { '.' });
            verInfo = ver[0] + ver[1] + ver[2].PadLeft(3, '0') + ver[3].PadLeft(3, '0');
            RaiseEvent(verInfo);
            return CConvert.ToInt(verInfo);
        }
    }
}