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

namespace PMF
{
    public class GraphSettingsPost: Pmfbase
    {
       
        MySqlDataReader _reader;
     
        public APIGatewayProxyResponse PostGraphSettings(APIGatewayProxyRequest request, ILambdaContext context)
        {
            APIGatewayProxyResponse response;
            string statement = "DB::PostGraphSettings";
            string userId = "";
            List<GraphSettings> updatedSettings = new List<GraphSettings> { };

            try
            {
                string validationMessage = string.Empty;
                if (request != null && request.Body != null)
                {
                    var data = JsonConvert.DeserializeObject<JObject>(request.Body);
                    var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "userId");
                    if (validRequest)
                    {
                        if (_con.State == ConnectionState.Closed) _con.Open();
                        int temp = 0; //zero if its a new entry

                        var cmd = new MySqlCommand();
                        cmd.Connection = _con;
                        foreach (var record in data["graphSettings"])
                        {
                            if (record["GraphID"] != null)
                            {
                                if (!string.IsNullOrEmpty(record["GraphID"].ToString()))
                                {
                                    temp = Convert.ToInt32(Helper.CleanInput(record["GraphID"].ToString()));

                                    if (record["Delete"] != null)
                                    {
                                        statement = $"DELETE FROM graph WHERE GraphID = {temp}";
                                        cmd.CommandText = statement;
                                        cmd.ExecuteNonQuery();

                                        return HttpService.CreateResponse(200, payload: "Graph settings successfully updated");
                                    }
                                }

                            }
                            updatedSettings.Add(
                                new GraphSettings
                                {
                                    GraphID = temp,
                                    GraphTitle = Helper.CleanInput(record["GraphTitle"].ToString()),
                                    GraphType = Helper.CleanInput(record["GraphType"].ToString()),
                                    GraphQuery = Helper.CleanInput(record["GraphQuery"].ToString()),
                                    GraphDescription = Helper.CleanInput(record["GraphDescription"].ToString()),
                                    GraphX = Helper.CleanInput(record["GraphX"].ToString()),
                                    GraphY = Helper.CleanInput(record["GraphY"].ToString()),
                                    Active = Convert.ToInt32(record["Active"])
                                }
                            );
                        }

                        foreach (var setting in updatedSettings)
                        {
                            if (setting.GraphID == 0)
                            {
                                if (!String.Equals(setting.GraphTitle, "NedBankTransactions"))
                                {
                                    try
                                    {
                                        string tempStatement = $"{setting.GraphQuery}";
                                        tempStatement = tempStatement.Replace("{startDate}", "2012-02-02"); //demo date
                                        tempStatement = tempStatement.Replace("{endDate}", "2012-02-02");
                                        tempStatement = tempStatement.Replace("''", "'");
                                        cmd.CommandText = tempStatement;
                                        cmd.ExecuteNonQuery();
                                    }
                                    catch (Exception)
                                    {
                                        throw new Exception("The sql statement entered is invalid");
                                    }

                                }
                                statement = $"INSERT INTO graph (strGraphType, strGraphTitle, strGraphData, strDescription,strXColumn, strYColumn, blnActive) VALUES('{setting.GraphType}','{setting.GraphTitle}','{setting.GraphQuery}','{setting.GraphDescription}', '{setting.GraphX}', '{setting.GraphY}',{setting.Active})";

                            }
                            else
                            {
                                if (!String.Equals(setting.GraphTitle, "NedBankTransactions"))
                                {
                                    try
                                    {
                                        string tempStatement = $"{setting.GraphQuery}";
                                        tempStatement = tempStatement.Replace("{startDate}", "2012-02-02"); //demo date
                                        tempStatement = tempStatement.Replace("{endDate}", "2012-02-02");
                                        tempStatement = tempStatement.Replace("''", "'");
                                        cmd.CommandText = tempStatement;
                                        cmd.ExecuteNonQuery();
                                    }
                                    catch (Exception)
                                    {
                                        throw new Exception("The sql statement entered is invalid");
                                    }

                                }
                                statement = $"UPDATE graph set strGraphType = '{setting.GraphType}', strGraphTitle = '{setting.GraphTitle}', strGraphData = '{setting.GraphQuery}', strDescription = '{setting.GraphDescription}', strXColumn = '{setting.GraphX}',strYColumn = '{setting.GraphY}',blnActive = {setting.Active} WHERE GraphID = {setting.GraphID}";
                            }
                            cmd.CommandText = statement;
                            cmd.ExecuteNonQuery();
                        }

                        foreach (var result in updatedSettings)
                        {
                            if (result.GraphID == 0)
                            {
                                cmd.CommandText = "SELECT MAX(GraphID) FROM graph";
                                cmd.ExecuteNonQuery();
                                int insertedId = 0; ;
                                _reader = cmd.ExecuteReader();
                                while (_reader.Read())
                                {
                                    insertedId = _reader.GetInt32(0);
                                }
                                _reader.Close();

                                result.GraphID = insertedId;
                            }
                        }

                        response = HttpService.CreateResponse(200, payload: updatedSettings.FirstOrDefault());
                    }
                    else
                    {
                        response = HttpService.CreateResponse(400, payload: validationMessage);
                    }
                }
                else
                {
                    response = HttpService.CreateResponse(400, payload: "Body can not be empty");
                }
            }
            catch (Exception ex)
            {
                _logger.WriteLog(userId, statement, ex.Message, "DB");
                return HttpService.CreateResponse(500, payload: ex.Message);
            }
            finally
            {
                if (_con != null || _con.State == ConnectionState.Open) _con.Close();
            }
            return response;
        }

    }

}