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

    private static string TableName = "@1@";
    private readonly ILogger<@0@Service> _logger;
    private IConfiguration m_Configuration;

    #endregion

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

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

    public @0@Service(ILogger<@0@Service> 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 @0@Table Get(DataRequest request)
    {
        //ActionType、Dataの組み合わせでデータを取得する

        @0@Table table = new @0@Table() { ErrNo = 0, ErrData = string.Empty, Name = nameof(@0@Table) };
        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())
                                {
                                    @0@ item = new @0@();
                                    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)
                            {
                                @0@ item = new @0@();
                                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();
                        }
                    }
                }
            }
        }
        OperationLog.Instance.WriteLog("データ読込完了");
        return table;
    }

    private DataResult Set(@0@Table 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 (@0@ 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, @0@ 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<@0@> requestStream, IServerStreamWriter<DataResult> responseStream, ServerCallContext context)
    {
        @0@ 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<@0@Table> GetData(DataRequest request, ServerCallContext context)
    {
        return Task.FromResult(Get(request));
    }

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

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

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

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

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

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