using System;
using System.Collections.Generic;
using Amazon.Lambda.Core;
using Amazon.Lambda.APIGatewayEvents;
using MySqlConnector;
using System.Data;
using classes;
using Model;

namespace PMF
{
    public class TransactionList: Pmfbase
    {
     
        MySqlDataReader _reader;
      
        public APIGatewayProxyResponse GetTransactions(APIGatewayProxyRequest request, ILambdaContext context)
        {
            APIGatewayProxyResponse response;
            List<IdTransactionRecord> transactions = new List<IdTransactionRecord>();
            string statement = "DB::GetTransactions";
            string userId = "";
            string idNumber = "";
            string strIdType = "";
            bool idSupplied = false;
            bool idOrUuidSupplied = false;
            try
            {

                if (!Helper.IsRequestEmpty(request))
                {
                    if (_con.State == ConnectionState.Closed) _con.Open();
                    string validationMessage = string.Empty;

                    if (request.QueryStringParameters.ContainsKey("userId"))
                    {
                        var validCognitoUuidRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "userId");

                        if (validCognitoUuidRequest)
                        {
                            userId = Helper.CleanInput(request.QueryStringParameters["userId"]);
                            idOrUuidSupplied = true;
                        }
                        if (!validCognitoUuidRequest && validationMessage.Length > 0) return HttpService.CreateResponse(400, payload: validationMessage);

                    }
                    if (request.QueryStringParameters.ContainsKey("strIdNumber"))
                    {
                        if (request.QueryStringParameters.ContainsKey("strIdType"))
                        {
                            strIdType = Helper.CleanInput(request.QueryStringParameters["strIdType"]);
                            if (string.Equals(strIdType, "2"))
                            {
                                string validationError = string.Empty;
                                var valid = Helper.ValidateRequestParameters(ref validationError, request.QueryStringParameters, "strIdNumber");
                                if (!valid)
                                {
                                    return HttpService.CreateResponse(400, payload: validationError);
                                }
                            }
                            idNumber = Helper.CleanInput(request.QueryStringParameters["strIdNumber"]);
                            idOrUuidSupplied = true;
                        }
                        else
                        {
                            return HttpService.CreateResponse(400, payload: "Missing strIdType");
                        }

                    }
                    if (idOrUuidSupplied)
                    {
                        if (!string.IsNullOrEmpty(idNumber))
                        {
                            statement = $@"SELECT  user.strName, user.strSurname,
                                        transaction.intPaid, transaction.strIVeriNumber, transaction.dtPaid, transaction.dtPayment, transaction.strStatus, transaction.strAmount, transaction.strRecieptNumber, transaction.strVeriResponse,
                                        infringement.dtInfringementDate, infringement.strInfringementNumber, infringement.strIssuingAuthority, infringement.strVechicleRegistration, infringement.intDemeritPoints,
                                        transaction.dtIVeriSettlement, transaction.strBankRef
                                        FROM user
                                        INNER JOIN transaction ON transaction.refClientID = user.UserID
                                        INNER JOIN infringement ON infringement.refTrasactionID = transaction.TransactionID                                         
                                        WHERE user.strIDNumber = '{idNumber}' GROUP BY  strIVeriNumber";

                            idSupplied = true;
                        }
                        else
                        {
                            statement = $@"SELECT  user.strName, user.strSurname,
                                        transaction.intPaid, transaction.strIVeriNumber, transaction.dtPaid, transaction.dtPayment, transaction.strStatus, transaction.strAmount, transaction.strRecieptNumber, transaction.strVeriResponse,
                                        infringement.strIdNumber, infringement.dtInfringementDate, infringement.strInfringementNumber, infringement.strIssuingAuthority, infringement.strVechicleRegistration, infringement.intDemeritPoints, 
                                        transaction.dtIVeriSettlement, transaction.strBankRef
                                        FROM user
                                        INNER JOIN transaction ON transaction.refClientID = user.UserID
                                        INNER JOIN infringement ON infringement.refTrasactionID = transaction.TransactionID                                            
                                        WHERE user.UserID = {userId} GROUP BY  strIVeriNumber";
                        }

                        var cmd = new MySqlCommand(statement, _con);
                        _reader = cmd.ExecuteReader();

                        if (_reader.HasRows)
                        {
                            while (_reader.Read())
                            {
                                if (idSupplied)
                                {
                                    transactions.Add(new IdTransactionRecord
                                    {
                                        ClientId = idNumber,
                                        TransactionId = _reader.GetValue(16).ToString(),
                                        strInfringementNumber = _reader.GetValue(11).ToString(),
                                        intPaid = _reader.GetValue(2).ToString(),
                                        strIVeriNumber = _reader.GetValue(3).ToString(),
                                        dtPaid = _reader.GetValue(4).ToString(),
                                        dtPayment = _reader.GetValue(5).ToString(),
                                        strIDNumber = idNumber,
                                        strStatus = _reader.GetValue(6).ToString(),
                                        strReceiptNumber = _reader.GetValue(8).ToString(),
                                        strVeriResponse = _reader.GetValue(9).ToString(),
                                        dtInfringementDate = _reader.GetValue(10).ToString(),
                                        strAmount = _reader.GetValue(7).ToString(),
                                        strIssuingAuthority = _reader.GetValue(12).ToString(),
                                        strVechicleRegistration = _reader.GetValue(13).ToString(),
                                        intDemeritPoints = Convert.ToInt32(_reader["intDemeritPoints"]),                                      
                                        dtIVeriSettlement = _reader.GetValue(15).ToString(),
                                    });
                                }
                                else
                                {
                                    transactions.Add(new IdTransactionRecord
                                    {
                                        ClientId = _reader.GetValue(0).ToString(),
                                        TransactionId = _reader.GetValue(17).ToString(),
                                        strInfringementNumber = _reader.GetValue(12).ToString(),
                                        intPaid = _reader.GetValue(2).ToString(),
                                        strIVeriNumber = _reader.GetValue(3).ToString(),
                                        dtPaid = _reader.GetValue(4).ToString(),
                                        dtPayment = _reader.GetValue(5).ToString(),
                                        strStatus = _reader.GetValue(6).ToString(),
                                        strReceiptNumber = _reader.GetValue(8).ToString(),
                                        strVeriResponse = _reader.GetValue(9).ToString(),
                                        strIDNumber = _reader.GetValue(10).ToString(),
                                        dtInfringementDate = _reader.GetValue(11).ToString(),
                                        strIssuingAuthority = _reader.GetValue(13).ToString(),
                                        strAmount = _reader.GetValue(7).ToString(),
                                        strVechicleRegistration = _reader.GetValue(14).ToString(),
                                        intDemeritPoints = Convert.ToInt32(_reader.GetValue(15)),                                     
                                        dtIVeriSettlement = _reader.GetValue(16).ToString(),

                                    });

                                }

                            }
                            _reader.Close();
                        }
                        if (transactions.Count > 0)
                        {
                            response = HttpService.CreateResponse(200, payload: transactions, serializeToJson: true);
                            _logger.WriteLog(userId, statement, response.Body.ToString(), "DB");
                            return response;
                        }
                        else
                        {
                            response = HttpService.CreateResponse(200, payload: "No Transactions Available");
                            _logger.WriteLog(userId, statement, response.Body.ToString(), "DB");
                            return response;
                        }
                    }
                    else
                    {
                        _logger.LogMessage(context, "Processing request failed - Missing Parameters");
                        response = HttpService.CreateResponse(400, payload: "Please add parameter(s): user or IdNumber");
                        _logger.WriteLog(userId, statement, response.Body.ToString(), "DB");
                        return response;
                    }
                }
                else
                {
                    _logger.LogMessage(context, $"Processing request failed invalid request");
                    response = HttpService.CreateResponse(400, payload: "No parameters provided");
                    return response;
                }
            }
            catch (Exception ex)
            {
                _logger.LogMessage(context, "Processing request failed: " + ex.Message);
                _logger.WriteLog(userId, statement, ex.Message, "DB");
                return HttpService.CreateResponse(500, payload: ex.Message);
            }
            finally
            {
                if (_reader != null) _reader.Close();
                if (_con != null || _con.State == ConnectionState.Open) _con.Close();
            }
        }

    }

}