using HotelPms.Share.Data.Script;
using HotelPms.Share.Util;
using System.Data;
using System.Data.SqlClient;

namespace HotelPms.Share.Data;

/// <summary>										
/// ****************************** Description *******************************										
/// ◇システム名称										
/// 　@CoreCom										
/// ◇概要										
/// 　施設設定画面										
/// ◇履歴										
/// 　2007/11/02　小木　勝龍　　新規作成	
/// ****************************** Declarations ******************************										
/// </summary>										
public class SqlBrokerListener : IDisposable
{
    #region  ★★★★★　Declartions　★★★★★

#if false
CREATE TABLE [dbo].[S_BrokerMsg](
	[ID] [int] NOT NULL,
	[SeqNo]   int null,
	[ActionID]  TINYINT NULL,
	[ActionData] [nvarchar](500) NULL,
	[PcName]   [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	UpdateDate DATETIME,
CONSTRAINT [PK_S_BrokerMsg] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))
GO

CREATE TABLE [dbo].[H_BrokerMsg](
[ID] [int] IDENTITY(1,1) NOT NULL,
	[BrokerID] [int] NULL,
	[SeqNo]   int null,
	[ActionID]  TINYINT NULL,
	[ActionData] [nvarchar](500) NULL,
	[PcName]   [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	UpdateDate DATETIME,
CONSTRAINT [PK_H_BrokerMsg] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))
GO

CREATE TRIGGER [dbo].[TR_AddBrokerLog] 
ON [dbo].[S_BrokerMsg] 
FOR INSERT, UPDATE
AS 
BEGIN
	IF @@ROWCOUNT = 0 RETURN 
	SET NOCOUNT ON 
	DELETE FROM [H_BrokerMsg] WHERE UpdateDate < DATEADD(DD, -7, GETDATE());
	INSERT INTO [H_BrokerMsg]([PcName],[UserName],[SeqNo],[ActionID],[ActionData],[UpdateDate]) SELECT [PcName],[UserName],[SeqNo],[ActionID],[ActionData],[UpdateDate] FROM inserted; 
END
GO

ALTER TABLE [dbo].[S_BrokerMsg] ENABLE TRIGGER [TR_AddBrokerLog]
GO
#endif


    /// <summary>
    /// 非同期のため、使う時Invoke必要
    /// </summary>
    public event DataChangeEventHandler? DataChange;
    private bool m_Disposed = false;
    private string m_ConnectString = string.Empty;

    #endregion

    #region  ★★★★★　Property　★★★★★

    /// <summary>
    /// メッセージデータのID
    /// </summary>
    public int ID { get; set; } = 1;

    #endregion

    #region  ★★★★★　Class Event　★★★★★

    public SqlBrokerListener(string hostName, string useID, string password, string dbName, int id)
    {
        ID = id;
        m_ConnectString = string.Format("Data Source={0};User ID={1};Password={2};Initial Catalog={3};Application Name=SqlBrokerListener;", hostName, useID, password, dbName);
    }

    ~SqlBrokerListener()
    {
        Dispose(false);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (!m_Disposed)   //一回だけ
        {
            if (disposing)
            {
                //Managed Resources
                SqlDependency.Stop(m_ConnectString);
            }

            //Unmanaged resources
            m_Disposed = true;
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    #endregion

    #region  ★★★★★　Private Function　★★★★★

    private void Update(bool isFirst)
    {
        using (SqlConnection connection = new SqlConnection(m_ConnectString))
        {
            //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]  
            //using (SqlCommand command = new SqlCommand("select [Message] From [dbo].[Messages] WHERE ID = 1", connection))
            using (SqlCommand command = new SqlCommand(string.Format("select [SeqNo] From [dbo].[S_BrokerMsg] WHERE ID = {0}", ID), connection))
            {
                //command.NotificationAutoEnlist = true;
                command.Notification = null;
                command.CommandType = CommandType.Text;
                connection.Open();
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += dependency_OnChange;

                using (DataTable dataTable = new DataTable())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = command;
                        adapter.Fill(dataTable);
                        adapter.Dispose();
                    }

                    if (isFirst) { return; }
                    if (DataChange != null) { DataChange(this, new SqlDataChangeEventArgs(isFirst ? 0 : 1, false, dataTable)); }
                }
            }
        }
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        SqlDependency dependency = (SqlDependency)sender;
        dependency.OnChange -= dependency_OnChange;

        //if (m_OwnerForm.InvokeRequired)
        //{
        //    m_OwnerForm.Invoke(new OnChangeEventHandler(dependency_OnChange), new object[] { sender, e });
        //}
        //else
        //{
            Update(false);
        //}            
    }

    #endregion

    #region  ★★★★★　Public  Function　★★★★★

    public void Execute()
    {
        SqlDependency.Start(m_ConnectString);
        Update(true);
    }

    public static bool BrokerEnabled(string hostName, string useID, string password, string dbName)
    {
        try
        {
            using (MsSqlNet msSqlNet = new MsSqlNet(hostName, useID, password, "master"))
            {
                object ret = msSqlNet.ExecuteScalar(string.Format("SELECT is_broker_enabled FROM sys.databases WHERE name ='{0}'", dbName));
                return CConvert.ToBool(ret);
            }
        }
        catch
        {
            return false;
        }
    }

    /// <summary>
    /// インストール時、is_broker_enabledよりfalseの場合、初期化必要
    /// </summary>
    /// <param name="hostName"></param>
    /// <param name="useID"></param>
    /// <param name="password"></param>
    /// <param name="dbName"></param>
    /// <returns></returns>
    public static bool InitBroker(string hostName, string useID, string password, string dbName)
    {
        try
        {
            string sql = string.Empty;

            //MASTER登録
            using (MsSqlNet msSqlNet = new MsSqlNet(hostName, useID, password, "master"))
            {
                if (!msSqlNet.ExistsProctrue("SP_CloseDBConnect"))
                {
                    sql = ResourceFile.GetScript(ResourceFile.ResType.SP_CloseDBConnect);
                    if (msSqlNet.ExecuteNonQuery(sql) == -1) { return false; }
                }

                //ＤＢをクロス
                //if (msSqlNet.ExecuteNonQuery(string.Format("EXECUTE SP_CloseDBConnect '{0}'", dbName)) == -1) { return false; }
                msSqlNet.ExecuteNonQuery(string.Format("EXECUTE SP_CloseDBConnect '{0}'", dbName));

                //Broker Enable
                sql = string.Format(@"ALTER DATABASE {0} SET NEW_BROKER WITH ROLLBACK IMMEDIATE;  
ALTER DATABASE {0} SET ENABLE_BROKER; ", dbName);
                if (msSqlNet.ExecuteNonQuery(sql) == -1) { return false; }

                //sa権限⇒ＤＢへ
                if (msSqlNet.ExecuteNonQuery(string.Format("ALTER AUTHORIZATION ON DATABASE::{0} TO [sa];", dbName)) == -1) { return false; }

                //CLR??

                //成功チェック
                return CConvert.ToBool(msSqlNet.ExecuteScalar(string.Format("SELECT is_broker_enabled FROM sys.databases WHERE name ='{0}'", dbName)));
            }
        }
        catch
        {
            return false;
        }
    }

    #endregion
}