using Npgsql;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Office.Word;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using KssSmaPlaLib.Commons;
using Serilog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using static KssSmaPlaLib.IO.Database.DbUxer;
using Path = System.IO.Path;

namespace KssSmaPlaLib.IO.Database
{
    /// <summary>
    /// DBアクセスアシスタント
    /// </summary>
    public class DbUxer : IDisposable
    {
        public enum DbProvider
        {
            PostgreSQL,
            SQLServer,
            MySQL,
            Oracle,
            SQLite,
        }

        /// <summary>
        /// プロバイダ名ディクショナリ
        /// </summary>
        private static readonly Dictionary<DbProvider, string> _providerNameDic = new Dictionary<DbProvider, string>()
        {
            { DbProvider.PostgreSQL, "Npgsql" },
            { DbProvider.SQLServer, "System.Data.SqlClient" },
            { DbProvider.MySQL, "MySql.Data.MySqlClient" },
            { DbProvider.Oracle, "Oracle.ManagedDataAccess.Client" },
            { DbProvider.SQLite, "System.Data.SQLite" },
        };

        /// <summary>
        /// プロバイダ名（文字列）からenum値に変更
        /// </summary>
        /// <param name="dbProviderName">プロバイダ名（文字列）</param>
        /// <returns>enum値（該当なし時はnull）</returns>
        public static DbProvider? GetDbProviderEnumFromString(string dbProviderName = "PostgreSQL")
        {
            switch(dbProviderName.ToLower().Replace(" ",""))
            {
                case "postgresql":
                case "ポスグレ":
                    return DbProvider.PostgreSQL;
                case "sqlserver":
                    return DbProvider.SQLServer;
                case "mysql":
                    return DbProvider.MySQL;
                case "oracle":
                case "oracledatabase":
                    return DbProvider.Oracle;
                case "sqlite":
                case "sqlite3":
                    return DbProvider.SQLite;
                default:
                    return null;
            }
        }

        /// <summary>
        /// コミット済みフラグ
        /// </summary>
        private bool _isCommitted = false;

        /// <summary>
        /// SQLディクショナリ
        /// </summary>
        private static readonly Dictionary<string, string> _sqlDic = new Dictionary<string, string>();

        /// <summary>
        /// 指定フォルダからSQLファイルを読み込む（ディクショナリ保存）
        /// </summary>
        /// <param name="folderPath">フォルダパス</param>
        /// <returns>成否</returns>
        public static bool TryLoadSqlFromText(string folderPath, out string resultMessage)
        {
            resultMessage = string.Empty;
            var fileCount = 0;
            List<string> files = new List<string>();

            try
            {
                if (string.IsNullOrEmpty(folderPath))
                {
                    resultMessage = "フォルダパスが指定されていません。";
                    return false;
                }
                if (!Directory.Exists(folderPath))
                {
                    resultMessage = $"フォルダが存在しません。[フォルダパス:{folderPath}]";
                    return false;
                }
                _sqlDic.Clear();
                foreach (var file in Directory.GetFiles(folderPath, "*.sql.txt"))
                {
                    _sqlDic.Add(Path.GetFileNameWithoutExtension(file).ToLower().Replace(".sql", ""), System.IO.File.ReadAllText(file));
                    files.Add(Path.GetFileNameWithoutExtension(file).Replace(".sql", ""));
                    fileCount++;
                }
                resultMessage = $"SQLファイルをロードしました。[ファイル数:{fileCount},ファイル:{string.Join(",", files)}]";
                return true;
            }
            catch (Exception ex)
            {
                resultMessage = $"SQLファイル読み込み時にエラーが発生しました。[エラー内容:{ex.Message}]";
                return false;
            }
        }

        public static bool TestConnectByDic(Dictionary<string,string> iniDic, out string resultMessage)
        {
            const string KEY_DB_PROVIDER_NAME = "DbProviderName";
            const string KEY_DB_CONNECTION_STRING = "DbConnectionString";

            var dbProviderName = DictionaryUxer.GetValueOrDefault(iniDic, KEY_DB_PROVIDER_NAME, string.Empty);
            var connectionString = DictionaryUxer.GetValueOrDefault(iniDic, KEY_DB_CONNECTION_STRING, string.Empty);
            return TestConnect(dbProviderName, connectionString, out resultMessage);
        }

        /// <summary>
        /// テスト接続
        /// </summary>
        /// <returns>成否</returns>
        public static bool TestConnect(string dbPrividerName, string dbConnectionString, out string resultMessage)
        {
            // パラメータチェック＆DBプロバイダ取得
            if (!TryConnectParameter(dbPrividerName, dbConnectionString, out DbProvider? _, out resultMessage))
            {
                return false;
            }

            if (!TryCreate(dbPrividerName, dbConnectionString, out var dbUxer, out resultMessage, false))
            {
                return false;
            }
            dbUxer.Dispose();
            return true;
        }

        /// <summary>
        /// DB接続
        /// </summary>
        private DbConnection _con = null;

        /// <summary>
        /// トランザクション
        /// </summary>
        private DbTransaction _tran = null;

        public static bool TryCreateByDic(Dictionary<string, string> iniDic, out DbUxer dbUxer, out string resultMessage, bool useTransaction = false)
        {
            const string KEY_DB_PROVIDER_NAME = "DbProviderName";
            const string KEY_DB_CONNECTION_STRING = "DbConnectionString";

            var dbProviderName = DictionaryUxer.GetValueOrDefault(iniDic, KEY_DB_PROVIDER_NAME, string.Empty);
            var connectionString = DictionaryUxer.GetValueOrDefault(iniDic, KEY_DB_CONNECTION_STRING, string.Empty);

            return TryCreate(dbProviderName, connectionString, out dbUxer, out resultMessage, useTransaction);
        }

        /// <summary>
        /// 生成挑戦
        /// </summary>
        /// <param name="dbPrividerName"></param>
        /// <param name="dbProvider"></param>
        /// <param name="dbConnectionString"></param>
        /// <param name="dbUxer"></param>
        /// <param name="resultMessage"></param>
        /// <returns></returns>
        public static bool TryCreate(string dbPrividerName, string dbConnectionString, out DbUxer dbUxer, out string resultMessage, bool useTransaction = false)
        {
            dbUxer = null;

            // パラメータチェック＆DBプロバイダ取得
            if (!TryConnectParameter(dbPrividerName, dbConnectionString, out DbProvider? dbp, out resultMessage))
            {
                return false;
            }

            DbProvider dbpp = (DbProvider)dbp;

            try
            {
                // 生成
                dbUxer = new DbUxer(dbpp, dbConnectionString, useTransaction);
                return true;
            }
            catch (Exception ex)
            {
                // 接続NG回答
                resultMessage = $"DB接続に失敗しました。[エラー内容:{ex.Message}]";
                return false;
            }
        }

        private static bool TryConnectParameter(string dbPrividerName, string dbConnectionString, out DbProvider? dbp, out string resultMessage)
        {
            dbp = DbProvider.PostgreSQL;
            resultMessage = string.Empty;

            if (string.IsNullOrEmpty(dbPrividerName))
            {
                // DBプロバイダ指定なしの場合、デフォルトはポスグレとする
                // Infoメッセージ
                resultMessage = "DBプロバイダ名が未指定のため、デフォルトのPostgreSQLを使用します。";
            }

            if (string.IsNullOrEmpty(dbConnectionString))
            {
                resultMessage = "接続文字列が設定されていません。";
                return false;
            }

            if (!string.IsNullOrEmpty(dbPrividerName))
            {
                dbp = GetDbProviderEnumFromString(dbPrividerName);
            }

            if (dbp == null)
            {
                resultMessage = $"DBプロバイダ名が不正です。[DBプロバイダ名:{dbPrividerName}]";
                return false;
            }

            return true;
        }

        /// <summary>
        /// コンストラクタ（DB接続）
        /// </summary>
        /// <param name="dbProvider">DBプロバイダ（enum値）</param>
        /// <param name="connectionString">接続文字列</param>
        /// <param name="useTransaction">トランザクション有無</param>
        private DbUxer(DbProvider dbProvider, string connectionString, bool useTransaction = false)
        {
            try
            {
                // DBプロバイダファクトリー作成
                DbProviderFactory factory;
                if (dbProvider == DbProvider.PostgreSQL)
                {
                    // ポスグレの場合、Npgsqlのインスタンをを直接取得
                    factory = Npgsql.NpgsqlFactory.Instance;
                }
                else
                {
                    factory = DbProviderFactories.GetFactory(_providerNameDic[dbProvider]);
                }

                // DB接続作成
                _con = factory.CreateConnection();

                // 接続文字列設定
                _con.ConnectionString = connectionString;

                // 接続
                _con.Open();

                // トランザクション
                if (useTransaction) _tran = _con.BeginTransaction();
            }
            catch (Exception ex)
            {
                LoggerUxer.Error($"DB接続に失敗しました。[プロバイダ:{dbProvider},接続文字列:{connectionString},エラー内容:{ex.Message}]");
                throw ex;
            }
        }

        public bool ExecuteNonQuery(string sqlOrName, Dictionary<string, object> parameters, out int resultCount, out string resultMessage)
        {
            resultCount = 0;
            resultMessage = string.Empty;

            if (_con == null || _con.State != ConnectionState.Open)
            {
                resultMessage = "DBと接続されていません。";
                return false;
            }

            try
            {
                using (var cmd = _con.CreateCommand())
                {
                    // コパンドプロパティを注入
                    if (!TryInjectSoulToCmd(cmd, sqlOrName, parameters, out var messageForMe))
                    {
                        resultMessage = messageForMe;
                        return false;
                    }

                    resultCount = cmd.ExecuteNonQuery();
                    LoggerUxer.Info($"[Rec Count:{resultCount}]");
                    return true;
                }
            }
            catch (Exception ex)
            {
                resultMessage = $"SQL実行でエラーが発生しました。[SQL名:{sqlOrName},エラー内容:{ex.Message}]";
                return false;
            }
        }

        public bool BeginTextImport(
            string sqlOrName,
            string csvFilePath,
            out int resultCount,
            out string resultMessage)
        {
            resultCount = 0;
            resultMessage = string.Empty;

            if (_con == null || _con.State != ConnectionState.Open)
            {
                resultMessage = "DBと接続されていません。";
                return false;
            }

            try
            {
                // SQLの取得
                sqlOrName = sqlOrName.ToLower();
                var sql = _sqlDic.ContainsKey(sqlOrName) ? _sqlDic[sqlOrName] : sqlOrName;

                // CSV有無
                if (!System.IO.File.Exists(csvFilePath))
                {
                    resultMessage = $"CSVファイルが存在しません。[ファイルパス:{csvFilePath}]";
                    return false;
                }

                // バルクインサート用のメソッドはポスグレの接続しか持たない
                var pgConn = (NpgsqlConnection)_con;

                var sqls = sql.Split(new[] { "-- SPLIT --" }, StringSplitOptions.RemoveEmptyEntries);
                if (sqls.Length != 3)
                {
                    resultMessage = $"バルク挿入SQLで分割数が正しくありません。[SQLファイル名:{sqlOrName},分割数:{sqls.Length}]";
                    return false;
                }

                // 1個目のSQL（一時テーブル作成）
                if (!ExecuteNonQuery(sqls[0], new Dictionary<string, object>(), out var _, out var messageForMe))
                {
                    resultMessage = $"バルク挿入SQL(1)が失敗しました。[詳細:{messageForMe}]";
                    return false;
                }

                // 2個目のSQL（一時テーブルにバルク挿入）
                using (var writer = pgConn.BeginTextImport(sqls[1]))
                {
                    using (var reader = new StreamReader(csvFilePath))
                    {
                        string line;
                        while ((line = reader.ReadLine()) != null)
                        {
                            writer.WriteLine(line);
                            //resultCount++; // ここで「1件、2件…」と数える！♨️
                        }
                    }
                } // これで完了！1000件なら「シュンッ！」で終わります

                // 3個目のSQL（一時テーブルから対象テーブルへコピー）
                if (!ExecuteNonQuery(sqls[2], new Dictionary<string, object>(), out resultCount, out messageForMe))
                {
                    resultMessage = $"バルク挿入SQL(2)が失敗しました。[詳細:{messageForMe}]";
                    return false;
                }

                LoggerUxer.Info($"[Rec Count:{resultCount}]");
                return true;
                
            }
            catch (Exception ex)
            {
                resultMessage = $"SQL実行でエラーが発生しました。[SQL名:{sqlOrName},エラー内容:{ex.Message}]";
                return false;
            }
        }

        public bool ExecuteQuery(string sqlOrName, Dictionary<string, object> parameters, out List<Dictionary<string, object>> recordSet, out string resultMessage)
        {
            recordSet = new List<Dictionary<string, object>>();
            resultMessage = string.Empty;

            if (_con == null || _con.State != ConnectionState.Open)
            {
                resultMessage = "DBと接続されていません。";
                return false;
            }

            try
            {
                using (var cmd = _con.CreateCommand())
                {
                    // コパンドプロパティを注入
                    if (!TryInjectSoulToCmd(cmd, sqlOrName, parameters, out var messageForMe))
                    {
                        resultMessage = messageForMe;
                        return false;
                    }
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var row = new Dictionary<string, object>();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                row[reader.GetName(i)] = reader.GetValue(i);
                            }
                            recordSet.Add(row);
                        }
                        LoggerUxer.Info($"[Rec Count:{recordSet.Count}]");
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                resultMessage = $"DBからデータ取得時にエラーが発生しました。[SQL名:{sqlOrName},エラー内容:{ex.Message}]";
                return false;
            }
        }

        private bool TryInjectSoulToCmd(DbCommand cmd, string sqlOrName, Dictionary<string,object> parameters, out string resultMessage)
        {
            resultMessage = string.Empty;

            // SQLの取得
            sqlOrName = sqlOrName.ToLower();
            var sql = _sqlDic.ContainsKey(sqlOrName) ? _sqlDic[sqlOrName] : sqlOrName;
            //LoggerUxer.Info($"[SQL:{sql}]");

            // SQL中のコマンドパラメータ名取得
            var paraList = ExtractSqlParams(sql);
            //oggerUxer.Info($"[SQL Para names:{string.Join(",", paraList)}]");

            // コマンドパラメータ値の型変換
            if (!TryChangeParamTypeFromSqlParams(ref parameters, ref paraList, out var messageForMe))
            {
                resultMessage = $"コマンドパラメータ値の型変換に失敗しました。[エラー内容:{messageForMe}]";
                return false;
            }

            // SQL文中のコマンドパラメータ型定義をカット
            cmd.CommandText = sql = CutParamTypeOfSQL(sql);
            //LoggerUxer.Info($"[SQL(Param Type Cut):{sql}]");

            // 再度、SQL中のコマンドパラメータ名取得
            paraList = ExtractSqlParams(sql);
            //LoggerUxer.Info($"[SQL Para names(Type cut):{string.Join(",", paraList)}]");

            // SQLに合わせたコマンドパラメータ登録
            foreach (var p in parameters)
            {
                if (!paraList.Contains(p.Key)) continue;
                var param = cmd.CreateParameter();
                param.ParameterName = p.Key;
                param.Value = p.Value;
                cmd.Parameters.Add(param);
            }

            //LoggerUxer.Info($"[SQL Params:{GetCommandParametersCsv(cmd)}]");

            if (cmd.Parameters.Count != paraList.Count)
            {
                resultMessage = $"必要なパラメータが不足しています。[SQL名:{sqlOrName},SQL項目:{string.Join(",", paraList)}, CSV項目:{string.Join(",", parameters.Keys)}]";
                return false;
            }
            return true;
        }

        public void Commit()
        {
            _tran?.Commit();
            _isCommitted = true;
        }

        /// <summary>
        /// 終了処理
        /// </summary>
        /// <exception cref="NotImplementedException"></exception>
        public void Dispose()
        {
            if (_tran != null && !_isCommitted)
            {
                Log.Warning("トランザクション未コミット → 自動ロールバックされました");
                _tran.Rollback(); // 明示的にロールバック
            }
            _con?.Dispose();
            _con = null;
            _tran = null;
        }

        public static List<string> ExtractSqlParams(string sql)
        {
            //var matches = System.Text.RegularExpressions.Regex.Matches(sql, @":([a-zA-Z_][a-zA-Z0-9_]*)");
            //return matches.Cast<System.Text.RegularExpressions.Match>()
            //              .Select(m => m.Groups[1].Value)
            //              .Distinct()
            //              .ToList();

            //var result = new List<string>();
            //var lines = sql.Split(new[] { "\r\n", "\n" }, StringSplitOptions.None);

            //foreach (var line in lines)
            //{
            //    // コメント部分を除去
            //    var codePart = line.Contains("--") ? line.Substring(0, line.IndexOf("--")) : line;

            //    // パラメータ抽出
            //    var matches = System.Text.RegularExpressions.Regex.Matches(
            //        codePart,
            //        @":(\[[a-zA-Z]\][a-zA-Z_][a-zA-Z0-9_]*)|:([a-zA-Z_][a-zA-Z0-9_]*)"
            //    );
            //    foreach (System.Text.RegularExpressions.Match match in matches)
            //    {
            //        var paramName = match.Groups[1].Success ? match.Groups[1].Value : match.Groups[2].Value;
            //        if (!result.Contains(paramName))
            //            result.Add(paramName);
            //    }
            //}
            //return result;

            var result = new List<string>();
            var lines = sql.Split(new[] { "\r\n", "\n" }, StringSplitOptions.None);

            foreach (var line in lines)
            {
                // コメント部分を除去
                var codePart = line.Contains("--") ? line.Substring(0, line.IndexOf("--")) : line;

                // パラメータ抽出（::で始まるものは除外）
                var matches = System.Text.RegularExpressions.Regex.Matches(
                    codePart,
                    @"(?<!:):(\[[a-zA-Z]\][a-zA-Z_][a-zA-Z0-9_]*)|(?<!:):([a-zA-Z_][a-zA-Z0-9_]*)"
                );

                foreach (System.Text.RegularExpressions.Match match in matches)
                {
                    var paramName = match.Groups[1].Success ? match.Groups[1].Value : match.Groups[2].Value;
                    if (!result.Contains(paramName))
                        result.Add(paramName);
                }
            }
            return result;
        }
        public static string GetCommandParametersCsv(DbCommand command)
        {
            if (command == null || command.Parameters.Count == 0)
                return string.Empty;

            var list = new List<string>();

            foreach (DbParameter param in command.Parameters)
            {
                string name = param.ParameterName?.TrimStart('@'); // @を除去（任意）
                object value = param.Value;

                string formattedValue;

                if (value == null || value == DBNull.Value)
                {
                    formattedValue = "NULL";
                }
                else if (value is string || value is char)
                {
                    formattedValue = $"'{value}'"; // シングルクォートで括る
                }
                else if (value is DateTime dt)
                {
                    formattedValue = $"'{dt:yyyy-MM-dd HH:mm:ss}'"; // 日付も文字列扱い
                }
                else
                {
                    formattedValue = value.ToString();
                }

                list.Add($"{name}={formattedValue}");
            }

            return string.Join(",", list);
        }
        private static bool TryChangeParamTypeFromSqlParams(ref Dictionary<string, object> parameters, ref List<string> paraNameList, out string resultMessage)
        {
            resultMessage = string.Empty;

            foreach (var key in parameters.Keys.ToList())
            {
                var name = paraNameList
                    .FirstOrDefault(m => m == key || m.Substring(0, 1) == "[" && m.Substring(3) == key);
                if (name != null && name.Substring(0, 1) == "[")
                {
                    switch (name.Substring(0, 3).ToUpper())
                    {
                        case "[I]":
                            if (string.IsNullOrEmpty(parameters[key].ToString()))
                            {
                                parameters[key] = DBNull.Value;
                            }
                            else if (int.TryParse(parameters[key].ToString(), out int intValue))
                            {
                                parameters[key] = intValue;
                            }
                            else
                            {
                                resultMessage = $"パラメータ値[I]型変換に失敗しました。[キー:{key}値:{parameters[key]}]";
                                return false;
                            }
                            break;
                        //case "[IN":
                        //    if (string.IsNullOrEmpty(parameters[key].ToString()))
                        //    {
                        //        parameters[key] = DBNull.Value;
                        //    }
                        //    else if (int.TryParse(parameters[key].ToString(), out intValue))
                        //    {
                        //        parameters[key] = intValue;
                        //    }
                        //    else
                        //    {
                        //        resultMessage = $"パラメータ値[IN]型変換に失敗しました。[キー:{key}値:{parameters[key]}]";
                        //        return false;
                        //    }
                        //    break;
                        case "[F]":
                            if (double.TryParse(parameters[key].ToString(), out double doubleValue))
                            {
                                parameters[key] = doubleValue;
                            }
                            else
                            {
                                resultMessage = $"パラメータ値[F]型変換に失敗しました。[キー:{key}値:{parameters[key]}]";
                                return false;
                            }
                            break;
                        case "[D]":
                            if (DateTime.TryParse(parameters[key].ToString(), out DateTime dtValue))
                            {
                                parameters[key] = dtValue;
                            }
                            else
                            {
                                resultMessage = $"パラメータ値[D]型変換に失敗しました。[キー:{key}値:{parameters[key]}]";
                                return false;
                            }
                            break;
                        case "[S]":
                            parameters[key] = parameters[key].ToString();
                            break;
                    }
                }
            }
            return true;
        }

        // SQL文中のコマンドパラメータ型定義をカット
        private static string CutParamTypeOfSQL(string sql)
        {
            string[] cutItems = new string[] { "[I]", "[F]", "[D]", "[S]" };
            foreach (var cutItem in cutItems)
            {
                sql = sql.Replace(cutItem.ToUpper(), "");
                sql = sql.Replace(cutItem.ToLower(), "");
            }
            return sql;
        }
    }
}