using Npgsql;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace Mirle.Component.Database
{
    /// <summary>
    /// 關聯式資料庫類別
    /// </summary>
    public class RelationDatabaseFactory : IDisposable
    {
        /// <summary>
        /// 建構式
        /// </summary>
        protected RelationDatabaseFactory()
        {
            Opne();
        }
        /// <summary>
        /// 建構式
        /// </summary>
        /// <param name="databaseType">資料庫類別</param>
        protected RelationDatabaseFactory(RelationDatabaseType databaseType)
        {
            _databaseType = databaseType;
            Opne();
        }
        /// <summary>
        /// 關聯式資料庫物件
        /// </summary>
        private static volatile RelationDatabaseFactory _relationDB;
        /// <summary>
        /// 關聯式資料庫物件鎖定
        /// </summary>
        private static readonly object _syncRoot = new object();
        /// <summary>
        /// 關聯式資料庫類別
        /// </summary>
        private RelationDatabaseType _databaseType = RelationDatabaseType.Oracle;
        /// <summary>
        /// 關聯式資料庫連線
        /// </summary>
        public IDbConnection Connection
        {
            get
            {
                return Opne();
            }
        }
        /// <summary>
        /// 關聯式資料庫類別實例
        /// </summary>
        /// <param name="databaseType">資料庫類別</param>
        /// <returns>關聯式資料庫類別</returns>
        /// <remarks>預設為 Oracle Database</remarks>
        public static RelationDatabaseFactory Instance(RelationDatabaseType databaseType = RelationDatabaseType.Oracle)
        {
            if (_relationDB == null)
            {
                lock (_syncRoot) // 鎖定避免多執行緒重覆呼叫建立物件
                {
                    _relationDB = new RelationDatabaseFactory(databaseType);
                }
            }
            return _relationDB;
        }
        /// <summary>
        /// 開啟資料庫連線
        /// </summary>
        /// <returns>資料庫連線</returns>
        /// <exception cref="ArgumentException">資料庫類別未定義</exception>
        private IDbConnection Opne()
        {
            IDbConnection conn;
            switch (_databaseType)
            {
                case RelationDatabaseType.Oracle:
                    {
                        conn = new OracleConnection(ConfigurationManager.ConnectionStrings["Oracle"].ConnectionString);
                        break;
                    }
                case RelationDatabaseType.Mssql:
                    {
                        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssql"].ConnectionString);
                        break;
                    }
                case RelationDatabaseType.PostgreSQL:
                    {
                        conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["PostgreSQL"].ConnectionString);
                        break;
                    }
                default:
                    {
                        throw new ArgumentException("Undefined database or not support.");
                    }
            }

            if (conn.State != ConnectionState.Open)
                conn.Open();
            return conn;
        }
        /// <summary>
        /// 設定資料庫類別
        /// </summary>
        /// <param name="relationDatabaseType">資料庫類別列舉項目</param>
        public void SetRelationDatabaseType(RelationDatabaseType relationDatabaseType)
        {
            _databaseType = relationDatabaseType;
        }
        /// <summary>
        /// 確認交易
        /// </summary>
        /// <param name="trans">資料庫交易</param>
        /// <param name="result">交易結果</param>
        /// <returns>True/False</returns>
        public bool Commit(IDbTransaction trans, out string result)
        {
            try
            {
                trans.Commit();
                result = "Success";
                return true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                result = ex.Message;
                return false;
            }
            finally
            {
                trans.Dispose();
            }
        }
        /// <summary>
        /// 取得資料庫語法
        /// </summary>
        /// <param name="filePath">完整檔案路徑</param>
        /// <param name="nodeName">完整子節點名稱</param>
        /// <returns>資料庫語法</returns>
        public string GetScriptContent(string filePath, string nodeName)
        {
            XmlDocument xmlDocument = new XmlDocument();
            xmlDocument.Load(filePath);
            return xmlDocument.SelectSingleNode(nodeName).InnerText.Trim();
        }
        /// <summary>
        /// 取得資料庫語法
        /// </summary>
        /// <param name="filePath">檔案路徑</param>
        /// <param name="fileName">檔案名稱</param>
        /// <param name="nodeName">完整子節點名稱</param>
        /// <returns>資料庫語法</returns>
        public string GetScriptContent(string filePath, string fileName, string nodeName)
        {
            XmlDocument xmlDocument = new XmlDocument();
            xmlDocument.Load($"{filePath}\\{fileName}");
            return xmlDocument.SelectSingleNode(nodeName).InnerText.Trim();
        }

        #region Destructure

        /// <summary>
        /// 釋放旗標
        /// </summary>
        private bool disposedValue;
        /// <summary>
        /// 釋放物件
        /// </summary>
        public void Dispose()
        {
            // 請勿變更此程式碼。請將清除程式碼放入 'Dispose(bool disposing)' 方法
            Dispose(disposing: true);
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// 解構式
        /// </summary>
        ~RelationDatabaseFactory() => Dispose(false); // 僅有當 'Dispose(bool disposing)' 具有會釋出非受控資源的程式碼時，才覆寫完成項
        /// <summary>
        /// 釋放物件
        /// </summary>
        /// <param name="disposing">受控物件釋放旗標</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    // 處置受控狀態 (受控物件)
                }
                // 釋出非受控資源 (非受控物件) 並覆寫完成項
                // 將大型欄位設為 Null
                disposedValue = true;
            }
        }

        #endregion
    }
    /// <summary>
    /// 關聯式資料庫類別
    /// </summary>
    public enum RelationDatabaseType
    {
        /// <summary>
        /// Oracle Database
        /// </summary>
        Oracle,
        /// <summary>
        /// Microsoft SQL Server Database
        /// </summary>
        Mssql,
        /// <summary>
        /// PostgreSQL Database
        /// </summary>
        PostgreSQL
    }
}