using HotelPms.Data;
using HotelPms.Data.Common.Pagination;
using HotelPms.Data.Master;
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.Output.proto(message：Output、OutputTable;Service：OutputCore) ⇒ projectファイルのInclude
    /// 2.Output.cs(両方)
    /// 3.OutputTable.cs(両方)
    /// 4.OutputService.cs(サーバー側) ⇒　StartupでMapGrpcServiceの処理が必要
    /// 5.OutputAccess.cs(クライアント側)
    /// </summary>
    public class OutputService : OutputCore.OutputCoreBase
    {
        #region  ★★★★★　Declartions　★★★★★

        public enum EActionType : int
        {
            Master = 0,
            Page = 1,
            Xlsx = 2,
            Xls = 3,
            Pdf = 4,
            Csv = 5,
            Json = 6,
            Xml = 7,
            DefOutput = 8,
        }

        private static string TableName = "M_Output";
        private readonly ILogger<OutputService> _logger;
        private IConfiguration m_Configuration;

        #endregion

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

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

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

        #endregion

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

        private string GetSql(DataRequest request)
        {
            StringBuilder sql = new StringBuilder();
            if (request.ActionType == (int)EActionType.Master)
            {
                sql.Append($"SELECT * FROM {TableName} {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")}");
            }
            else if (request.ActionType >= (int)EActionType.Page && request.ActionType <= (int)EActionType.Xml)
            {
                PagingRequest pagingRequest = JsonSerializer.Deserialize<PagingRequest>(request.Data);
                sql.Append($"EXEC Pagination {pagingRequest.PageNumber},{pagingRequest.PageSize},'{pagingRequest.Table}','{pagingRequest.Field}','{pagingRequest.OrderBy}','{CConvert.FromBase64(pagingRequest.Filter)}'");
            }
            else if (request.ActionType == (int)EActionType.DefOutput)
            {
                sql.Append($"EXEC GetDefaultOuput {request.Data}");
            }
            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 >= (int)EActionType.Xlsx && request.ActionType <= (int)EActionType.Xml)
                {
                    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;
        }

        /// <summary>
        /// マスタデータのセット
        /// </summary>
        /// <param name="msSqlNet"></param>
        /// <param name="sql"></param>
        /// <param name="table"></param>
        private void SetMaster(MsSqlNet msSqlNet, string sql, OutputTable table)
        {
            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())
                            {
                                Output item = new Output();
                                item.ConvertReader(reader);
                                table.Rows.Add(item);
                            }
                        }
                        reader.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 頁毎データのセット
        /// </summary>
        /// <param name="msSqlNet"></param>
        /// <param name="sql"></param>
        /// <param name="table"></param>
        private void SetPage(MsSqlNet msSqlNet, string sql, OutputTable table)
        {
            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)
                        {
                            Output item = new Output();
                            item.ConvertDataRow(row);
                            table.Rows.Add(item);
                        }

                        table.Tag = 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();
                    }
                }
            }
        }

        /// <summary>
        /// ディフォルト列設定をセット
        /// </summary>
        /// <param name="msSqlNet"></param>
        /// <param name="sql"></param>
        /// <param name="table"></param>
        private void SetDefOutput(MsSqlNet msSqlNet, string sql, OutputTable table)
        {
            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
                {
                    Output output = new Output();
                    output.ConvertDataRow(set.Tables[0].Rows[0]);
                    table.Rows.Add(output);

                    foreach (DataRow row in set.Tables[1].Rows)
                    {
                        OutputItem item = new OutputItem();
                        item.ConvertDataRow(row);
                        output.Items.Add(item);
                    }

                    // 表示名称 ⇒ フィールド名のマッピング
                    // ※存在しなかったら、表示名称 = フィールド名ということ
                    foreach (DataRow row in set.Tables[2].Rows)
                    {
                        output.FieldMap.Add(row["Name"].ToString(), row["Field"].ToString());
                    }

                    foreach (DataRow row in set.Tables[3].Rows)
                    {
                        output.SelectList.Add(CConvert.ToInt(row["ID"]), row["Name"].ToString());
                    }
                }
            }
        }

        /// <summary>
        /// ActionTypeによるデータ取得
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        private OutputTable Get(DataRequest request)
        {
            //ActionType、Dataの組み合わせでデータを取得する

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

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                string sql = GetSql(request);
                if (request.ActionType == (int)EActionType.Master) { SetMaster(msSqlNet, sql, table); }
                else if (request.ActionType == (int)EActionType.Page) { SetPage(msSqlNet, sql, table); }
                else if (request.ActionType == (int)EActionType.DefOutput) { SetDefOutput(msSqlNet, sql, table); }
            }
            OperationLog.Instance.WriteLog("データ読込完了");
            return table;
        }

        private DataResult Set(OutputTable 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 (Output 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, Output item)
        {
            DataResult result = new DataResult() { ErrNo = 0, ErrData = string.Empty };
            OperationLog.Instance.WriteLog($"データ請求：{item.ToString()}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                StringBuilder sql = new StringBuilder();
                if (add)
                {
                    int id = CConvert.ToInt(msSqlNet.ExecuteScalar(item.MaxIDSql())) + 1;
                    item.ID = id;
                }
                else
                {
                    sql.AppendLine(item.ClearDetailSql());
                }
                sql.AppendLine(add ? item.AddSql() : item.UpdateSql());
                int i = 1;
                foreach (OutputItem detail in item.Items)
                {
                    detail.MachineName = item.MachineName;
                    detail.UserName = item.UserName;
                    detail.ReportID = item.ReportID;
                    detail.OutputID = item.ID;
                    detail.ID = i;
                    sql.AppendLine(detail.AddSql());
                    i++;
                }

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

                if (!msSqlNet.ExecuteNonQueryWithTran(sql.ToString()))
                {
                    result.ErrNo = msSqlNet.ErrNo;
                    result.ErrData = msSqlNet.ErrInfo;
                }
            }
            OperationLog.Instance.WriteLog($"更新：{result.ToString()}");
            return result;
        }

        private async Task AddOrUpdate(bool add, IAsyncStreamReader<Output> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            Output 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 M_OutputItem WHERE OutputID IN (SELECT ID FROM {TableName} WHERE {item.Data.ToStringUtf8()});
DELETE FROM {TableName} WHERE {item.Data.ToStringUtf8()};";
            OperationLog.Instance.WriteLog($"Sql生成：{sql}");

            using (MsSqlNet msSqlNet = new MsSqlNet(Setting.HotelPmsDB))
            {
                if (!msSqlNet.ExecuteNonQueryWithTran(sql))
                {
                    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<OutputTable> GetData(DataRequest request, ServerCallContext context)
        {
            return Task.FromResult(Get(request));
        }

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

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

        public async override Task SetDataStream(IAsyncStreamReader<OutputTable> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            OutputTable 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(Output request, ServerCallContext context)
        {
            return Task.FromResult(AddOrUpdate(true, request));
        }

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

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

        public async override Task UpdateStream(IAsyncStreamReader<Output> 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
    }
}