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

namespace PMF
{
    public class FinanceReportData: Pmfbase
    {
   
        MySqlDataReader _reader;
      

        public APIGatewayProxyResponse GetFinanceReportData(APIGatewayProxyRequest request, ILambdaContext context)
        {
            APIGatewayProxyResponse response;
            string statement = "DB::GetFinanceReportData";
            string issuingAuthority = string.Empty;
            string startSettlementDate = string.Empty;
            string endSettlementDate = string.Empty;
            Dictionary<string, string> dict = new Dictionary<string, string>();

            try
            {

                string validationMessage = string.Empty;
                if (!Helper.IsRequestEmpty(request))
                {
                    var validRequest = Helper.ValidateRequestParameters(ref validationMessage, request.QueryStringParameters, "issuingAuthority", "startSettlementDate", "endSettlementDate");
                    if (validRequest)
                    {
                        if (_con.State == ConnectionState.Closed) _con.Open();
                        string result = string.Empty;
                        issuingAuthority = Helper.CleanInput(request.QueryStringParameters["issuingAuthority"]);
                        startSettlementDate = Helper.CleanInput(request.QueryStringParameters["startSettlementDate"]);
                        endSettlementDate = Helper.CleanInput(request.QueryStringParameters["endSettlementDate"]);

                        statement = @"SELECT t.dtIVeriSettlement as SettlementTime, 
                                             i.strIssuingAuthority as IssuingAuthority, 
                                             i.strInfringementSource as Channel, 
                                             t.strBankRef as PayId, 
                                             i.strInfringementNumber as NoticeNumber, 
                                             i.strAmount as Amount,
                                             t.strRecieptNumber as ReceiptNumber
                                        FROM infringement i
                                             left outer join
                                             transaction t on i.refTrasactionID = t.TransactionID
                                       where t.dtIVeriSettlement between @startSettlementDate and @endSettlementDate
                                         and t.intPaid = 'YES'
                                         and 1 = case when @issuingAuthority     = 'ALL'             then 1
                                                      when i.strIssuingAuthority = @issuingAuthority then 1
                                                 else 0 end; "; //adjusted as per statuses update - lloyd

                        var cmd = new MySqlCommand();

                        cmd.Parameters.AddWithValue("@issuingAuthority", issuingAuthority);
                        cmd.Parameters.AddWithValue("@startSettlementDate", startSettlementDate);
                        cmd.Parameters.AddWithValue("@endSettlementDate", endSettlementDate);

                        cmd.Connection = _con;
                        cmd.CommandText = statement;
                        cmd.ExecuteNonQuery();

                        _reader = cmd.ExecuteReader();
                        List<FinanceReportItemModel> settledPayments = new List<FinanceReportItemModel> { };
                        if (_reader.HasRows)
                        {
                            while (_reader.Read())
                            {
                                settledPayments.Add(new FinanceReportItemModel
                                {
                                    SettlementDateTime = _reader.GetValue(0).ToString(),
                                    IssuingAuthority = _reader.GetValue(1).ToString(),
                                    Channel = _reader.GetValue(2).ToString(),
                                    PayId = _reader.GetValue(3).ToString(),
                                    NoticeNumber = _reader.GetValue(4).ToString(),
                                    Amount = _reader.GetValue(5).ToString(),
                                    ReceiptNumber = _reader.GetValue(6).ToString()
                                });
                            }

                            _reader.Close();
                            response = HttpService.CreateResponse(200, payload: settledPayments);
                        }
                        else
                        {
                            response = HttpService.CreateResponse(200, payload: "No settings found");
                        }
                    }
                    else
                    {
                        response = HttpService.CreateResponse(400, payload: validationMessage);
                    }
                }
                else
                {
                    response = HttpService.CreateResponse(400, payload: "No parameters provided");
                }
                //_logger.WriteLog(userId, statement, response.Body, "DB");
                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();
            }
        }

    }

}