using HotelPms.Data;
using HotelPms.Data.Common.Pagination;
using HotelPms.Data.UseInfo;
using HotelPms.GrpcService.Util;
using HotelPms.Share.Data;
using HotelPms.Share.IO;
using HotelPms.Share.Util;
using Google.Protobuf;
using Grpc.Core;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace HotelPms.GrpcService
{
    /// <summary>
    /// ■攻略
    /// 1.UseFree.proto(message：UseFree、UseFreeTable;Service：UseFreeCore) ⇒ projectファイルのInclude
    /// 2.UseFree.cs(両方)
    /// 3.UseFreeTable.cs(両方)
    /// 4.UseFreeService.cs(サーバー側) ⇒　StartupでMapGrpcServiceの処理が必要
    /// 5.UseFreeAccess.cs(クライアント側)
    /// </summary>
    public class UseFreeService : UseFreeCore.UseFreeCoreBase
    {
        #region  ★★★★★　Declartions　★★★★★

        private static string TableName = "D_UseFree";
        private readonly ILogger<UseFreeService> _logger;
        private IConfiguration m_Configuration;

        #endregion

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

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

        public UseFreeService(ILogger<UseFreeService> logger, IConfiguration configuration)
        {
            _logger = logger;
            m_Configuration = configuration;
        }

        #endregion

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

        private string GetSql(DataRequest request)
        {
            StringBuilder sql = new StringBuilder();
            if (request.ActionType == 0)
            {
                sql.Append($"SELECT * FROM {TableName} {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")}");
            }
            else if (request.ActionType == 1 || request.ActionType == 2)
            {                
                PagingRequest pagingRequest = JsonSerializer.Deserialize<PagingRequest>(request.Data);
                sql.Append($"EXECUTE Pagination {pagingRequest.PageNumber},{pagingRequest.PageSize},'{pagingRequest.Table}','{pagingRequest.Field}','{pagingRequest.OrderBy}','{CConvert.FromBase64(pagingRequest.Filter)}'");
            }
            return sql.ToString();
        }

        private DataTable GetDataTable(DataRequest request)
        {
            //ActionType、Dataの組み合わせでデータを取得する
            DataTable table = null;
            OperationLog.Instance.WriteLog($"データ請求：{request.ToString()}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                string sql = GetSql(request);
                if (request.ActionType == 2)
                {
                    using (DataSet set = msSqlNet.GetDataSet(sql))
                    {
                        if (set == null || set.Tables.Count == 0)
                        {
                            OperationLog.Instance.WriteLog("null");
                        }
                        else
                        {
                            if (set.Tables.Count == 2)
                            {
                                table = set.Tables[0];
                            }
                        }
                    }
                }
            }
            OperationLog.Instance.WriteLog("データ読込完了");
            return table;
        }


        private UseFreeTable Get(DataRequest request)
        {
            //ActionType、Dataの組み合わせでデータを取得する

            UseFreeTable table = new UseFreeTable() { ErrNo = 0, ErrData = string.Empty, Name = nameof(UseFreeTable) };
            OperationLog.Instance.WriteLog($"データ請求：{request.ToString()}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                string sql = GetSql(request);
                if (request.ActionType == 0)
                {
                    using (SqlConnection dbConnect = msSqlNet.Open())
                    {                       
                        using (SqlCommand command = new SqlCommand(sql, dbConnect))
                        {
                            OperationLog.Instance.WriteLog($"データ読込：{sql}");
                            command.CommandTimeout = 60000 * 100;
                            using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
                            {
                                //エラーまだ対応していない                                    
                                if (reader == null)
                                {
                                    OperationLog.Instance.WriteLog("null");
                                    table.ErrNo = msSqlNet.ErrNo;
                                    table.ErrData = msSqlNet.ErrInfo;
                                }
                                else
                                {
                                    while (reader.Read())
                                    {
                                        UseFree item = new UseFree();
                                        item.ConvertReader(reader);
                                        table.Rows.Add(item);
                                    }
                                }
                                reader.Close();
                            }
                        }
                    }
                }
                else if (request.ActionType == 1)
                {
                    using (DataSet set = msSqlNet.GetDataSet(sql))
                    {
                        if (set == null || set.Tables.Count == 0)
                        {
                            OperationLog.Instance.WriteLog("null");
                            table.ErrNo = msSqlNet.ErrNo;
                            table.ErrData = msSqlNet.ErrInfo;
                        } 
                        else
                        {
                            if (set.Tables.Count == 2)
                            {
                                foreach (DataRow row in set.Tables[0].Rows)
                                {
                                    UseFree item = new UseFree();
                                    item.ConvertDataRow(row);
                                    table.Rows.Add(item);
                                }

                                table.ErrData = JsonSerializer.Serialize(new PagingRespone()
                                {
                                    MaxPage = CConvert.ToInt(set.Tables[1].Rows[0]["MaxPage"]),
                                    TotalRow = CConvert.ToInt(set.Tables[1].Rows[0]["TotalRow"])
                                });
                            }
                            else
                            {
                                table.ErrNo = -1;
                                table.ErrData = set.Tables[0].Rows[0]["Descript"].ToString();
                            }
                        }
                    }
                }
            }
            OperationLog.Instance.WriteLog("データ読込完了");
            return table;
        }

        private DataResult Set(UseFreeTable table)
        {
            DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
            OperationLog.Instance.WriteLog($"データ請求：{table.Rows.Count}");

            StringBuilder sql = new StringBuilder();
            if (table.BeforeUpdate.Length > 0) { sql.AppendLine(table.BeforeUpdate.ToStringUtf8()); }

            foreach (UseFree item in table.Rows)
            {
                sql.AppendLine(table.UpdateType == 2 ? item.UpdateSql() : item.AddSql());
            }

            if (table.AfterUpdate.Length > 0) { sql.AppendLine(table.AfterUpdate.ToStringUtf8()); }

            string sqlCmd = sql.ToString();
            OperationLog.Instance.WriteLog($"Sql生成：{sqlCmd}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                if (!msSqlNet.ExecuteNonQueryWithTran(sqlCmd))
                {
                    result.ErrNo = msSqlNet.ErrNo;
                    result.ErrData = msSqlNet.ErrInfo;
                }
            }
            OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
            return result;
        }

        private DataResult AddOrUpdate(bool add, UseFree item)
        {
            DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
            OperationLog.Instance.WriteLog($"データ請求：{item.ToString()}");

            string sql = add ? item.AddSql() : item.UpdateSql();
            OperationLog.Instance.WriteLog($"Sql生成：{sql}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                if (msSqlNet.ExecuteNonQuery(sql) == -1)
                {
                    result.ErrNo = msSqlNet.ErrNo;
                    result.ErrData = msSqlNet.ErrInfo;
                }
            }
            OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
            return result;
        }

        private async Task AddOrUpdate(bool add, IAsyncStreamReader<UseFree> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            UseFree item = null;
            await foreach (var message in requestStream.ReadAllAsync())
            {
                item = message;
                break;
            }

            DataResult result = AddOrUpdate(add, item);
            await responseStream.WriteAsync(result);   //データ送信
        }

        private DataResult Delete(SqlWhere item)
        {
            DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
            OperationLog.Instance.WriteLog($"データ請求：{item.ToString()}");

            string sql = $"DELETE FROM {TableName} WHERE {item.Data.ToStringUtf8()}";
            OperationLog.Instance.WriteLog($"Sql生成：{sql}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                if (msSqlNet.ExecuteNonQuery(sql) == -1)
                {
                    result.ErrNo = msSqlNet.ErrNo;
                    result.ErrData = msSqlNet.ErrInfo;
                }
            }
            OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
            return result;
        }

        #endregion

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

        public async override Task OutputStream(DataRequest request, IServerStreamWriter<FileGrpcData> responseStream, ServerCallContext context)
        {
            try
            {
                using (DataTable data = GetDataTable(request))
                {
                    byte[] bytes = Excel.Export(data);
                    FileGrpcData fileGrpcData = new FileGrpcData
                    {
                        Block = 1,
                        FileName = "Data",
                        Content = ByteString.CopyFrom(bytes)
                    };
                    await responseStream.WriteAsync(fileGrpcData);
                }
            }
            catch { }
        }

        /// <summary>
        /// 同期実行
        /// </summary>
        /// <param name="request"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public override Task<UseFreeTable> GetData(DataRequest request, ServerCallContext context)
        {
            return Task.FromResult(Get(request));
        }

        public async override Task GetDataStream(IAsyncStreamReader<DataRequest> requestStream, IServerStreamWriter<UseFreeTable> responseStream, ServerCallContext context)
        {
            DataRequest request = null;
            await foreach (var message in requestStream.ReadAllAsync())
            {
                request = message;
                break;
            }
            UseFreeTable table = Get(request);
            await responseStream.WriteAsync(table);  //データ送信
        }

        public override Task<DataResult> SetData(UseFreeTable request, ServerCallContext context)
        {
            return Task.FromResult(Set(request));
        }

        public async override Task SetDataStream(IAsyncStreamReader<UseFreeTable> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            UseFreeTable table = null;
            await foreach (var message in requestStream.ReadAllAsync())
            {
                table = message;
                break;
            }

            DataResult result = Set(table);
            //データ送信
            await responseStream.WriteAsync(result);
        }

        public override Task<DataResult> Add(UseFree request, ServerCallContext context)
        {
            return Task.FromResult(AddOrUpdate(true, request));
        }

        public async override Task AddStream(IAsyncStreamReader<UseFree> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            await AddOrUpdate(true, requestStream, responseStream, context);
        }

        public override Task<DataResult> Update(UseFree request, ServerCallContext context)
        {
            return Task.FromResult(AddOrUpdate(false, request));
        }

        public async override Task UpdateStream(IAsyncStreamReader<UseFree> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            await AddOrUpdate(false, requestStream, responseStream, context);
        }

        public override Task<DataResult> Remove(SqlWhere request, ServerCallContext context)
        {
            return Task.FromResult(Delete(request));
        }

        public async override Task RemoveStream(IAsyncStreamReader<SqlWhere> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            SqlWhere item = null;
            await foreach (var message in requestStream.ReadAllAsync())
            {
                item = message;
                break;
            }

            DataResult result = Delete(item);
            await responseStream.WriteAsync(result);  //データ送信
        }

        #endregion
    }
}