using System;
using System.Collections.Generic;
using Amazon.Lambda.Core;
using Amazon.Lambda.APIGatewayEvents;
using MySqlConnector;
using System.Data;
using classes;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using AwsDotnetCsharp.Model;

namespace PMF
{
    public class RedirectIveriPayment: Pmfbase
    {
        MySqlDataReader _reader = null;

        public async Task<APIGatewayProxyResponse> IVeriPayment(APIGatewayProxyRequest request, ILambdaContext context)
        {
            string statement = "iVeri-Lite::IVeriPayment";
            string econConsumerId = "Not returned by iVeri", merchantReference = "Not returned by iVeri", liteBankReference = "Not returned by iVeri";

            //MySqlTransaction transaction = null;
            

            try
            {
                if (request != null)
                {
                    var iVeriLocation = request.QueryStringParameters["veriLocation"];
                    var status = request.QueryStringParameters["status"];
                    string transactionID = request.QueryStringParameters["transactionID"];

                    var iveriResult = request.Body;
                    string iveriCleaned = iveriResult.Substring(iveriResult.IndexOf("__EVENTVALIDATION"));

                    string[] iveriResultArr = iveriCleaned.Split("&");
                    string liteResultDescription = iveriResultArr.Where(x => x.StartsWith("LITE_RESULT_DESCRIPTION")).FirstOrDefault();
                    liteResultDescription = liteResultDescription.Substring(liteResultDescription.IndexOf("=") + 1);

                    if (_con.State == ConnectionState.Closed) _con.Open();
                    //transaction = this._con.BeginTransaction();

                    using var cmd = new MySqlCommand();
                    cmd.Connection = _con;

                    if (string.IsNullOrEmpty(liteResultDescription)) //No result in here payment went through.-->check with iveri
                    {
                        econConsumerId = iveriResultArr.Where(x => x.StartsWith("ECOM_CONSUMERORDERID")).FirstOrDefault();
                        merchantReference = iveriResultArr.Where(x => x.StartsWith("MERCHANTREFERENCE")).FirstOrDefault();
                        liteBankReference = iveriResultArr.Where(x => x.StartsWith("LITE_BANKREFERENCE")).FirstOrDefault();
                        liteBankReference = Uri.UnescapeDataString(liteBankReference);

                        statement = $"Update transaction SET intPaid = 'YES', dtPaid = NOW(), strStatus = '{status}', strVeriResponse = 'No Result', dtLastEdit = NOW(), strRecieptNumber = '{econConsumerId.Substring(econConsumerId.IndexOf("=") + 1)}', strBankRef = '{liteBankReference.Substring(liteBankReference.IndexOf("=") + 1)}', dtIVeriSettlement = NOW(), strMerchantReference = '{merchantReference.Substring(merchantReference.IndexOf("=") + 1)}'  WHERE strIVeriNUmber = '{transactionID}'";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        statement = $"SELECT TransactionID FROM transaction WHERE strIVeriNumber = '{transactionID}'";
                        cmd.CommandText = statement;
                        string transactionDbId = Convert.ToInt32(cmd.ExecuteScalar()).ToString();

                        statement = $"Update infringement SET intPaid = 'YES', dtPaid = NOW(), strStatus = '{status}', dtLastEdit = NOW() WHERE refTrasactionID = '{transactionDbId}'";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        //transaction.Commit();

                        statement = "SELECT strValue FROM setting WHERE strKey = 'convenience'";
                        cmd.CommandText = statement;
                        double convenience = Convert.ToDouble(cmd.ExecuteScalar());

                        statement = $"SELECT refClientID FROM transaction WHERE transactionId =  {transactionDbId}";
                        cmd.CommandText = statement;
                        double payeeId = Convert.ToDouble(cmd.ExecuteScalar());

                        // in infringement table get number,vehicle reg, amount, id number
                        statement = $"SELECT * FROM infringement WHERE refTrasactionID = {transactionDbId}";
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        List<InfringementReceiptModel> paidInfringements = new List<InfringementReceiptModel>();

                        _reader = cmd.ExecuteReader();

                        if (_reader.HasRows)
                        {
                            while (_reader.Read())
                            {
                                paidInfringements.Add(new InfringementReceiptModel
                                {
                                    Infringement = _reader["strInfringementNumber"].ToString(),
                                    VechicleRegistration = _reader["strVechicleRegistration"].ToString(),
                                    IdNumber = _reader["strIdNumber"].ToString(),
                                    Amount = Convert.ToDouble(_reader["strAmount"]),
                                });
                            }
                            _reader.Close();

                            statement = $"SELECT strEmail FROM user WHERE UserID = {payeeId}";
                            cmd.CommandText = statement;
                            string payeeEmail = cmd.ExecuteScalar().ToString();
                            var paymentNotification = new PaymentReceiptService(_logger, context);
                            var receiptGenerated = await paymentNotification.GenerateReceipt(paidInfringements, convenience, payeeEmail, transactionDbId, econConsumerId.Substring(econConsumerId.IndexOf("=") + 1));
                            if (!receiptGenerated) _logger.WriteLog(transactionDbId, payeeEmail, "mail not sent", "TMT");
                        }

                        string url = $"{iVeriLocation}?PaymentNotification?transactionID={transactionID}&status={status}";

                        var response = new APIGatewayProxyResponse
                        {
                            StatusCode = 302,
                            Headers = new Dictionary<string, string>
                        {
                            { "location", url}
                        }
                        };

                        _logger.WriteLog("iVeriPayment", $"Location: {iVeriLocation}", "iveri transaction", "DB");
                        return response;
                    }
                    else //iveri has an error in result
                    {

                        if (string.Equals(liteResultDescription, "Transaction cancelled by customer"))
                        {
                            statement = $"Update transaction SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', strVeriResponse = '{liteResultDescription}', dtLastEdit = NOW(), dtIVeriSettlement = NOW()  WHERE strIVeriNUmber = '{transactionID}'";
                            cmd.CommandText = statement;
                            cmd.ExecuteNonQuery();

                            statement = $"SELECT TransactionID FROM transaction WHERE strIVeriNumber = '{transactionID}'";
                            cmd.CommandText = statement;
                            string transactionDbId = Convert.ToInt32(cmd.ExecuteScalar()).ToString();

                            statement = $"Update infringement SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', dtLastEdit = NOW() WHERE refTrasactionID = {transactionDbId}";
                            cmd.CommandText = statement;
                            cmd.ExecuteNonQuery();

                            //transaction.Commit();
                        }
                        else
                        {
                            statement = $"Update transaction SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', strVeriResponse = '{liteResultDescription}', dtLastEdit = NOW(), strRecieptNumber = '{econConsumerId.Substring(econConsumerId.IndexOf("=") + 1)}', strBankRef = '{liteBankReference.Substring(liteBankReference.IndexOf("=") + 1)}', dtIVeriSettlement = NOW(), strMerchantReference = '{merchantReference.Substring(merchantReference.IndexOf("=") + 1)}'  WHERE strIVeriNUmber = '{transactionID}'";
                            cmd.CommandText = statement;
                            cmd.ExecuteNonQuery();

                            statement = $"SELECT TransactionID FROM transaction WHERE strIVeriNumber = '{transactionID}'";
                            cmd.CommandText = statement;
                            string transactionDbId = Convert.ToInt32(cmd.ExecuteScalar()).ToString();

                            statement = $"Update infringement SET intPaid = 'NO', dtPaid = NOW(), strStatus = '{status}', dtLastEdit = NOW() WHERE refTrasactionID = {transactionDbId}";
                            cmd.CommandText = statement;
                            cmd.ExecuteNonQuery();

                            //transaction.Commit();

                        }

                        string url = $"{iVeriLocation}?PaymentNotification?transactionID={transactionID}&status={status}&liteResultDescription={liteResultDescription}";

                        var response = new APIGatewayProxyResponse
                        {
                            StatusCode = 302,
                            Headers = new Dictionary<string, string>
                        {
                            { "location", url }
                        }
                        };

                        _logger.WriteLog("iVeriPayment", $"Location: {url}", "iveri transaction", "DB");
                        return response;
                    }

                }
                else
                {
                    _logger.WriteLog("iVeriPayment", "iVeri returned an empty request", "Exception", "DB");
                }

            }
            catch (Exception ex)
            {
                //if(transaction !=null) transaction.Rollback();
                string lineNumber = (new StackTrace(ex, true)).GetFrame(0).GetFileLineNumber().ToString();
                _logger.WriteLog("iVeriPayment", ex.Message.ToString(), $"Exception line:  {lineNumber}", "DB");
                return HttpService.CreateResponse(500, payload: ex.Message);
            }
            finally
            {
                if (_reader != null) _reader.Close();
                if (_con != null || _con.State == ConnectionState.Open) _con.Close();
            }
            return HttpService.CreateResponse(500, payload: "Error"); //should never get here
        }

        public async Task<APIGatewayProxyResponse> GenerateReceipt(APIGatewayProxyRequest request, ILambdaContext context)
        {
            //MySqlTransaction transaction = null;

            try
            {
                if (request != null)
                {
                    string strIVeriNumber = request.QueryStringParameters["strIVeriNumber"];
                    InfringementReceiptTranstactionModel infringementReceiptTranstaction = null; 

                    if (_con.State == ConnectionState.Closed) _con.Open();
     
                    using var cmd = new MySqlCommand();
                    cmd.Connection = _con;

                    string statement = $"SELECT TransactionID, strIVeriNumber, refClientID, strRecieptNumber FROM transaction WHERE strIVeriNumber = '{strIVeriNumber}'";
                    //cmd.CommandText = statement;
                    cmd.ExecuteNonQuery();

                    _reader = cmd.ExecuteReader();

                    if (_reader.HasRows)
                    {
                        while (_reader.Read())
                        {
                            infringementReceiptTranstaction = new InfringementReceiptTranstactionModel
                            {
                                TransactionId = _reader["TransactionID"]?.ToString(),
                                IVeriNumber = _reader["strIVeriNumber"]?.ToString(),
                                ClientId = _reader["refClientID"]?.ToString(),
                                RecieptNumber = _reader["strRecieptNumber"]?.ToString()
                            };
                        }
                        _reader.Close();
                    }

                    statement = "SELECT strValue FROM setting WHERE strKey = 'convenience'";
                    cmd.CommandText = statement;
                    double convenience = Convert.ToDouble(cmd.ExecuteScalar());

                    statement = $"SELECT strInfringementNumber, strVechicleRegistration, strIdNumber, strAmount FROM infringement WHERE refTrasactionID = {infringementReceiptTranstaction.TransactionId}";
                    cmd.CommandText = statement;
                    cmd.ExecuteNonQuery();

                    List<InfringementReceiptModel> paidInfringements = new List<InfringementReceiptModel>();

                    _reader = cmd.ExecuteReader();

                    if (_reader.HasRows)
                    {
                        while (_reader.Read())
                        {
                            paidInfringements.Add(new InfringementReceiptModel
                            {
                                Infringement = _reader["strInfringementNumber"]?.ToString(),
                                VechicleRegistration = _reader["strVechicleRegistration"]?.ToString(),
                                IdNumber = _reader["strIdNumber"]?.ToString(),
                                Amount = Convert.ToDouble(_reader["strAmount"]),
                            });
                        }
                        _reader.Close();

                        statement = $"SELECT strEmail FROM user WHERE UserID = {infringementReceiptTranstaction.ClientId}";
                        cmd.CommandText = statement;
                        string payeeEmail = cmd.ExecuteScalar().ToString();
                        var paymentNotification = new PaymentReceiptService(_logger, context);
                        bool receiptGenerated = await paymentNotification.GenerateReceipt(paidInfringements, convenience, payeeEmail, infringementReceiptTranstaction.TransactionId, infringementReceiptTranstaction.RecieptNumber);
                        
                        if (!receiptGenerated)
                        {
                            _logger.WriteLog(infringementReceiptTranstaction.TransactionId, payeeEmail, "mail not sent", "TMT");
                        }
                        else
                        {
                            return new APIGatewayProxyResponse
                            {
                                StatusCode = 200,
                            };
                        }
                    }

                    return new APIGatewayProxyResponse
                    {
                        StatusCode = 500,
                    };
                }
                else
                {
                    _logger.WriteLog("iVeriPayment", "iVeri returned an empty request", "Exception", "DB");
                }

            }
            catch (Exception ex)
            {
                //if (transaction != null) transaction.Rollback();
                string lineNumber = (new StackTrace(ex, true)).GetFrame(0).GetFileLineNumber().ToString();
                _logger.WriteLog("iVeriPayment", ex.Message.ToString(), $"Exception line:  {lineNumber}", "DB");
                return HttpService.CreateResponse(500, payload: ex.Message);
            }
            finally
            {
                if (_reader != null) _reader.Close();
                if (_con != null || _con.State == ConnectionState.Open) _con.Close();
            }
            return HttpService.CreateResponse(500, payload: "Error"); //should never get here
        }
    }

}