using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Google.Protobuf;
using Grpc.Core;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using HotelPms.Data;
using HotelPms.GrpcService.Util;
using HotelPms.Share.Data;
using HotelPms.Share.IO;
using HotelPms.Share.Util;
using HotelPms.Data.Common;
using Org.BouncyCastle.Asn1.Ocsp;

namespace HotelPms.GrpcService
{
    public class GrpcTableService : GrpcTableCore.GrpcTableCoreBase
    {
        private readonly ILogger<GrpcTableService> _logger;
        private IConfiguration m_Configuration;

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

        private static byte[] GetFieldValue(SqlDataReader reader, int index, Type type, bool isNull)
        {
            if (type == typeof(System.Int32)) { return CConvert.GetBytes((int)(isNull ? 0 : reader.GetInt32(index))); }
            else if (type == typeof(bool)) { return CConvert.GetBytes((bool)(isNull ? false : reader.GetBoolean(index))); }
            else if (type == typeof(DateTime)) { return CConvert.GetBytes((DateTime)(isNull ? DateTime.MinValue : reader.GetDateTime(index))); }
            else if (type == typeof(float)) { return CConvert.GetBytes((float)(isNull ? 0F : reader.GetFloat(index))); }
            else if (type == typeof(double)) { return CConvert.GetBytes((double)(isNull ? 0D : reader.GetDouble(index))); }
            else if (type == typeof(decimal)) { return CConvert.GetBytes((decimal)(isNull ? 0M : reader.GetDecimal(index))); }
            else if (type == typeof(System.Byte)) { return CConvert.GetBytes((byte)(isNull ? 0x00 : reader.GetByte(index))); }
            else if (type == typeof(System.Int16)) { return CConvert.GetBytes((short)(isNull ? 0 : reader.GetInt16(index))); }
            else
            {
                byte[] data = Encoding.UTF8.GetBytes(isNull ? string.Empty : reader.GetString(index));
                byte[] endFlg = new byte[data.Length + 1];
                Array.Copy(data, 0, endFlg, 0, data.Length);
                return endFlg;
            }

            //object value = reader.GetValue(index);
            //return GetValue(type, value);
        }

        private string GetSql(DataRequest request)
        {
            string[] fields;
            StringBuilder sql = new StringBuilder();
            if (request.ActionType == (int)ETableActionType.CustomSql)
            {
                sql.Append(request.Data);
            }
            else if (request.ActionType == (int)ETableActionType.RoomTypeMasterGrid)
            {
                sql.Append($"SELECT A.*,dbo.FN_GetRoomKind(A.Kind) KindName FROM M_RoomType A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
            }
            else if (request.ActionType == (int)ETableActionType.BuildingMasterGrid)
            {
                sql.Append($"SELECT A.* FROM M_Building A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
            }
            else if (request.ActionType == (int)ETableActionType.RoomStatusMasterGrid)
            {
                sql.Append($"SELECT A.* FROM M_RoomStatus A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.SortID");
            }
            else if (request.ActionType == (int)ETableActionType.HotelMasterGrid)
            {
                sql.Append($"SELECT A.* FROM S_Hotel A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
            }            
            else if (request.ActionType == (int)ETableActionType.UpdateRoomStatus)
            {
                fields = request.Data.Split(new char[] { ',' });
                sql.Append($"EXECUTE SetRoomStatus '{fields[0]}',{fields[1]},{fields[2]}");
            }           
            else if (request.ActionType == (int)ETableActionType.DemoMasterGrid)
            {
                sql.Append($"SELECT A.* FROM M_Demo A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
            }
            else if (request.ActionType == (int)ETableActionType.RoomViewTabMasterGrid)
            {
                sql.Append($"SELECT A.* FROM M_RoomViewTab A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.ID");
            }
            else if (request.ActionType == (int)ETableActionType.RoomViewLayoutGrid)
            {
                sql.Append($"SELECT A.* FROM M_RoomViewLayout A WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY A.TabID,A.Row,A.Col");
            }
            else if (request.ActionType == (int)ETableActionType.RoomViewState)
            {
                fields = request.Data.Split(new char[] { ',' });
                sql.Append($"EXECUTE GetRoomState '{fields[0]}',{fields[1]}");
            }
            else if (request.ActionType == (int)ETableActionType.ReportMaster)
            {
                sql.Append($"SELECT Name FROM S_Report WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE {request.Data}")} ORDER BY ID");
            }
            else if (request.ActionType == (int)ETableActionType.PostNoSearch)
            {
                sql.Append($"SELECT ZipCode,Address FROM M_Address WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE ZipCode LIKE '{request.Data}%'")} ORDER BY ZipCode");
            }
            else if (request.ActionType == (int)ETableActionType.AddressSearch)
            {
                sql.Append($"SELECT ZipCode,Address FROM M_Address WITH(NOLOCK) {(string.IsNullOrEmpty(request.Data) ? string.Empty : $"WHERE Address LIKE '%{request.Data}%'")} ORDER BY ZipCode");
            }
            else if (request.ActionType == (int)ETableActionType.HotelDate)
            {
                sql.Append($"SELECT TOP 1 HotelDate FROM S_Hotel WITH(NOLOCK)");
            }
            else if (request.ActionType == (int)ETableActionType.Sequence)
            {
                sql.Append($"SELECT NEXT VALUE FOR dbo.{GetSequence(CConvert.ToInt(request.Data))}");
            }
            else if (request.ActionType == (int)ETableActionType.TaxRate)
            {
                fields = request.Data.Split(new char[] { ',' });
                sql.Append($"SELECT dbo.GetTax('{fields[0]}',{fields[1]})");
            }
            else if (request.ActionType == (int)ETableActionType.MasterName)
            {
                fields = request.Data.Split(new char[] { ',' });
                sql.Append($"SELECT Name FROM M_{fields[0]} WITH(NOLOCK) WHERE ID = {fields[1]}");
            }
            else if (request.ActionType == (int)ETableActionType.RoomTypeBase)
            {
                sql.Append($"SELECT Name,Kind FROM M_RoomType WITH(NOLOCK) WHERE ID = {CConvert.ToInt(request.Data)}");
            }
            else if (request.ActionType == (int)ETableActionType.RoomTypeList)
            {
                sql.Append($"SELECT ID コード,Name 名称,ShortName 略称 FROM M_RoomType WITH(NOLOCK)");
            }
            else if (request.ActionType == (int)ETableActionType.SalesLoginList)
            {
                sql.Append($"SELECT ID コード,Name 名称 FROM M_Login WITH(NOLOCK) WHERE DataType = 1");
            }
            else if (request.ActionType == (int)ETableActionType.SalesLoginName)
            {
                sql.Append($"SELECT Name FROM M_Login WITH(NOLOCK) WHERE ID = {request.Data} AND DataType = 1");
            }
            return sql.ToString();
        }

        private string GetSequence(int type)
        {
            return $"Seq{((ESeqType)type).ToString()}";
        }

        private string GetDB(DataRequest request)
{
            if (request.ActionType == (int)ETableActionType.PostNoSearch
                || request.ActionType == (int)ETableActionType.AddressSearch)
            {
                return Setting.SystemCenterDB;
            }
            else if (request.ActionType == (int)ETableActionType.ReportMaster)
            {
                return Setting.HotelCommonDB;
            }
            else
            {
                return Setting.HotelPmsDB;
            }
        }

        public static void SetTable(GrpcTable table, SqlDataReader reader)
        {
            OperationLog.Instance.WriteLog($"SetTable Begin：{DateTime.Now.ToString("HH:mm:ss fff")}");
            List<Type> fieldTypes = new List<Type>();
            //List<string> sqlTypes = new List<string>();

            #region Col

            OperationLog.Instance.WriteLog("Col Begin");
            for (int colIdx = 0; colIdx < reader.FieldCount; colIdx++)
            {
                //sqlTypes.Add(reader.GetDataTypeName(colIdx));
                fieldTypes.Add(reader.GetFieldType(colIdx));
                int fieldTypeInt = CConvert.ToTypeInt(fieldTypes[colIdx]);
                string name = reader.GetName(colIdx);
                //_logger.LogInformation($"{name}：{fieldTypes[colIdx].Name} = {fieldTypeInt}");
                table.Cols.Add(new GrpcCol() { Name = name, FieldType = fieldTypeInt, PrimaryKey = false });
            }

            #endregion

            #region Row

            OperationLog.Instance.WriteLog("Row Begin");
            while (reader.Read())
            {
                int fieldCount = reader.FieldCount;
                bool[] isNullCache = new bool[fieldCount];
                for (int i = 0; i < fieldCount; i++)
                {
                    isNullCache[i] = reader.IsDBNull(i);
                }

                using (MemoryStream stream = new MemoryStream())
                {
                    for (int i = 0; i < fieldCount; i++)
                    {
                        stream.Write(GetFieldValue(reader, i, fieldTypes[i], isNullCache[i]));
                    }

                    stream.Seek(0, SeekOrigin.Begin);
                    table.Rows.Add(new GrpcRow() { Data = ByteString.FromStream(stream) });
                }
            }
            OperationLog.Instance.WriteLog($"Row End⇒{table.Rows.Count}件");

            #endregion

            OperationLog.Instance.WriteLog($"SetTable End：{DateTime.Now.ToString("HH:mm:ss fff")}");
        }


        private GrpcTable Get(DataRequest request)
        {
            GrpcTable table = new GrpcTable() { ErrNo = 0, ErrData = string.Empty };
            OperationLog.Instance.WriteLog(request.ToString());

            using (MsSqlNet msSqlNet = new MsSqlNet(GetDB(request)))
            {
                using (SqlConnection dbConnect = msSqlNet.Open())
                {
                    string sql = GetSql(request);
                    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 (true)
                                {
                                    SetTable(table, reader);
                                    if (!reader.NextResult()) { break; }
                                }
                            }
                            reader.Close();
                        }
                    }
                }
            }

            if(table.ErrNo == 0)
            {
                table.ErrData = "";   //総行数を返す
            }
            return table;
        }

        public override Task<GrpcTable> GetData(DataRequest request, ServerCallContext context)
        {
            OperationLog.Instance.WriteLog("GetData Start");
            return Task.FromResult(Get(request));
        }

        /// <summary>
        /// データ取得
        /// </summary>
        /// <param name="requestStream"></param>
        /// <param name="responseStream"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public async override Task GetDataStream(IAsyncStreamReader<DataRequest> requestStream, IServerStreamWriter<GrpcTable> responseStream, ServerCallContext context)
        {
            DataRequest request = null;
            await foreach (var message in requestStream.ReadAllAsync())
            {
                request = message;
                break;
            }

            //データ送信
            GrpcTable table = Get(request);
            await responseStream.WriteAsync(table);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="requestStream"></param>
        /// <param name="responseStream"></param>
        /// <param name="context"></param>
        /// <returns></returns>
        public override Task SetData(IAsyncStreamReader<GrpcTable> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
        {
            return base.SetData(requestStream, responseStream, context);
        }
    }
}