using HotelPms.Share.IO;
using Npgsql;
using System.Data;
using System.Data.Common;

namespace HotelPms.Share.Data;

public class PostgreSqlNet : DataAccess
{
    #region  ★★★★★　Declartions　★★★★★

    public string ConnectionString { get; set; } = string.Empty;

    /// <summary>ユーザー</summary>
    public override string UseID { get; set; } = string.Empty;

    /// <summary>パスワード</summary>
    public override string PassWord { get; set; } = string.Empty;

    /// <summary>データベース所在のパソコン名</summary>
    public override string DataSource { get; set; } = string.Empty;

    /// <summary>データベース名</summary>
    public override string Catalog { get; set; } = string.Empty;

    public override string Schema { get; set; } = "public";

    public override int Port { get; set; } = 5432;

    /// <summary>
    /// デッドロック関連： 1205,3635,5231,5252,17888,22840,23424
    /// </summary>
    public int Message_id { get; set; } = 0;

    public string SqlState { get; set; } = string.Empty;

    /// <summary>エラー情報</summary>
    public override string ErrInfo { get; set; } = string.Empty;

    /// <summary>エラーコード</summary>
    public override int ErrNo { get; set; } = 0;

    public bool PoolEnabled { get; set; } = false;

    public int MaxPoolSize { get; set; } = 1000;

    public int RetryCount { get; set; } = 12;

    public int RetryInterval { get; set; } = 5;

    /// <summary>
    /// タイマー単位のSQL
    /// </summary>
    public bool IsTimerAccess { get; set; } = false;

    public bool FireInfoMessageEventOnUserErrors { get; set; } = false;

    public bool MessageEventEnabled { get; set; } = false;

    #endregion


    public PostgreSqlNet(string connectionString)
    {
        ConnectionString = connectionString;
        NpgsqlConnectionStringBuilder ssb = new NpgsqlConnectionStringBuilder(connectionString);
        DataSource = ssb.Host;
        Port = ssb.Port;
        UseID = ssb.Username;
        PassWord = ssb.Password;
        Catalog = ssb.Database;
        MaxPoolSize = ssb.MaxPoolSize;
    }

    public PostgreSqlNet(DBConnectItem connecitem) : this(connecitem.HostName, connecitem.UserID, connecitem.Password, connecitem.Port, connecitem.DBName)
    { }

    public PostgreSqlNet(string dataSource, string userID, string passWord) : this(dataSource, userID, passWord, 5432, "postgres")
    {
    }

    public PostgreSqlNet(string dataSource, string userID, string passWord, int port) : this(dataSource, userID, passWord, port, "postgres")
    { }

    public PostgreSqlNet(string dataSource, string userID, string passWord, int port,  string catalog)
    {
        DataSource = dataSource;
        Port = port;
        UseID = userID;
        PassWord = passWord;
        Catalog = catalog;
        ResetConnectionString();
    }

    public override void Dispose()
    {

    }

    public void ResetConnectionString()
    {
        NpgsqlConnectionStringBuilder ssb = new NpgsqlConnectionStringBuilder();
        ssb.Host = DataSource;
        ssb.Port = Port;
        ssb.Username = UseID;
        ssb.Password = PassWord;
        ssb.Database = Catalog;
        ssb.Enlist = true;
        //ssb.MaxPoolSize = MaxPoolSize;
        ssb.ApplicationName = System.IO.Path.GetFileNameWithoutExtension(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
        ssb.Encoding = "UTF8";
        //ssb.ApplicationName = System.IO.Path.GetFileNameWithoutExtension(Application.ExecutablePath);
        ConnectionString = ssb.ToString();
    }

    public static bool CreateDataBase(DBConnectItem item)
    {
        using (PostgreSqlNet dbAccess = new PostgreSqlNet(item.HostName, item.UserID, item.Password, item.Port))
        {
            if (dbAccess.ErrNo != 0) { return false; }
            return (dbAccess.ExecuteNonQuery($"CREATE DATABASE \"{item.DBName}\" ENCODING 'UTF8'") != -1);
        }
    }

    /// 可変戻り値
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="fillReaderHandler"></param>
    /// <returns></returns>
    public override T GetDataReader<T>(string sql, Action<DbDataReader, T> Fetch)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataReader({0})", sql), GetLogLevel());
        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.GetDataReader({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                T t = new T();
                using (NpgsqlConnection dbConnect = Open())
                {
                    using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 6000;
                        using (NpgsqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
                        {
                            
                            Fetch(reader, t);
                        }  //自動クロス
                    }
                }
                return t;
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return default; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return default;
    }

    public override DataSet GetDataSet(string sql)
    {
        throw new NotImplementedException();
    }

    public override DataSet GetDataSetWithTran(string sql)
    {
        throw new NotImplementedException();
    }

    public override DataTable GetDataTable(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("GetDataTable({0})", sql), GetLogLevel());
        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒GetDataTable({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (NpgsqlConnection dbConnect = Open())
                {
                    using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 60000;
                        DataTable dataTable = new DataTable();
                        using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter())
                        {
                            adapter.SelectCommand = command;
                            adapter.Fill(dataTable);
                        }
                        return dataTable;
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return null; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return null;
    }

    public override object ExecuteScalar(string sql)
    {
        OperationLog.Instance.WriteLog($"ExecuteScalar({sql})", GetLogLevel());

        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog($"{i}⇒ExecuteScalar({sql})", OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (NpgsqlConnection dbConnect = Open())
                {
                    using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
                    {
                        return command.ExecuteReader();
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return null; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return null;
    }

    public override int ExecuteGetID(string sql)
    {
        throw new NotImplementedException();
    }

    public override int ExecuteNonQuery(string sql)
    {
        OperationLog.Instance.WriteLog($"ExecuteNonQuery({sql})", GetLogLevel());

        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog($"{i}⇒ExecuteNonQuery({sql})", OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (NpgsqlConnection dbConnect = Open())
                {
                    using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
                    {
                        int row = command.ExecuteNonQuery();
                        return (row == -1 ? 0 : row);
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return -1; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return -1;
    }

    public override bool ExecuteNonQueryWithTran(string sql)
    {
        OperationLog.Instance.WriteLog($"ExecuteNonQuery({sql})", GetLogLevel());

        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog($"{i}⇒ExecuteNonQuery({sql})", OperationLog.LogLevelType.Normal); }
            NpgsqlTransaction tran = null;
            try
            {
                ErrClear();
                using (NpgsqlConnection dbConnect = Open())
                {
                    tran = dbConnect.BeginTransaction();
                    using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
                    {
                        int row = command.ExecuteNonQuery();
                        if (row == -1)
                        {
                            tran.Rollback();
                            return false;
                        }
                        else
                        {
                            tran.Commit();
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                tran?.Rollback(); 
                if (!IsTransient(ex, sql)) { return false; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return false;
    }

    public NpgsqlConnection Open()
    {
        NpgsqlConnection dbConnect = new NpgsqlConnection();
        dbConnect.ConnectionString = ConnectionString;
        dbConnect.Open();
        return dbConnect;
    }

    /// <summary>
    /// 一時的なエラー
    /// </summary>
    /// <param name="ex"></param>
    /// <returns></returns>
    private bool IsTransient(Exception ex, string sql)
    {
        NpgsqlException exSql = null;
        if (ex is NpgsqlException)
        {
            exSql = ex as NpgsqlException;
            Message_id = exSql.HResult;
            ErrNo = exSql.ErrorCode;
            SqlState = exSql.SqlState;
        }
        else
        {
            ErrNo = -1;
        }
        ErrInfo = ex.Message;
        OperationLog.Instance.WriteLog(string.Format("IsTransient({0},{1},{2},{3})", Message_id, ErrNo, ErrInfo, sql), OperationLog.LogLevelType.Normal);

        if (exSql != null)
        {
            //Specifies whether the exception is considered transient, that is, whether retrying the operation could succeed (e.g. a network error or a timeout).
            if (exSql.IsTransient) { return true; }
            if (exSql.SqlState == "40P01") { return true; }  //デッドロックの検出
        }
        else if (ex is TimeoutException)
        {
            return true;
        }
        return false;
    }

    private void ErrClear()
    {
        ErrNo = 0;
        ErrInfo = string.Empty;
        SqlState = string.Empty;
        Message_id = 0;
    }

    private OperationLog.LogLevelType GetLogLevel()
    {
        return IsTimerAccess ? OperationLog.LogLevelType.Level8 : OperationLog.LogLevelType.Level7;
    }

    /// <summary>
    /// テーブル一覧を取得
    /// ※一般的にschema = publicですが
    /// </summary>
    /// <returns></returns>
    public override DataTable GetTableList()
    {
        return GetDataTable($"SELECT \"table_name\" AS \"Name\" FROM \"information_schema\".\"tables\" WHERE \"table_schema\"='{Schema}';");
    }

    /// <summary>
    /// テーブル一覧を取得
    /// </summary>
    /// <returns></returns>
    public override DataTable GetTableList(string whereAdding)
    {
        return GetDataTable($"SELECT \"table_name\" AS \"Name\" FROM \"information_schema\".\"tables\" WHERE \"table_schema\"='{Schema}' AND {whereAdding};");
    }
}