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;


namespace PMF
{
    public class PostUserDetail: Pmfbase
    {
        MySqlDataReader _reader = null;
        public APIGatewayProxyResponse PostUserDetails(APIGatewayProxyRequest request, ILambdaContext context)
        {
            APIGatewayProxyResponse response;
            Dictionary<string, string> dict = new Dictionary<string, string>();
            Object resultResponse = null;
            
            string insertUpdateStatementId = "";
            string userId = "";
            string statement = "";

            try
            {
                string validationMessage = string.Empty;
                if (request != null && request.Body != null)
                {
                    if (_con.State == ConnectionState.Closed) _con.Open();
                    var data = JsonConvert.DeserializeObject<JObject>(request.Body);
                    var validRequest = Helper.ValidateRequestParameters(ref validationMessage, data, "userId", "strName", "strSurname", "strIdType", "strMobileNumber", "txtBillAddress", "additionalIds");
                    if (validRequest)
                    {
                        //CHECK IF sa id IS VALID ANY OTHER id does not have validation
                        if (String.Equals(data["strIdType"].ToString(), "2"))
                        {
                            string idErrors = string.Empty;
                            var validSaId = Helper.ValidateRequestParameters(ref idErrors, data, "strIdNumber");
                            if (!validSaId)
                            {
                                response = HttpService.CreateResponse(400, payload: idErrors);
                            }
                        }
                        userId = Helper.CleanInput(data["userId"].ToString());
                        if (!String.IsNullOrEmpty(userId))
                        {
                            statement = $"SELECT * FROM `user` WHERE UserID = '{userId}'";

                            var cmd = new MySqlCommand();
                            cmd.Connection = _con;
                            cmd.CommandText = statement;
                            cmd.ExecuteNonQuery();

                            _reader = cmd.ExecuteReader();

                            //user exists
                            if (_reader.HasRows)
                            {
                                _reader.Close();
                                //DO update user
                                string updateStatementUser = $"Update user SET strName = '{Helper.CleanInput(data["strName"].ToString())}', strSurname = '{Helper.CleanInput(data["strSurname"].ToString())}', strIdType = '{Helper.CleanInput(data["strIdType"].ToString())}', strIdNumber = '{Helper.CleanInput(data["strIdNumber"].ToString())}', strMobileNumber = '{Helper.CleanInput(data["strMobileNumber"].ToString())}' , strLastUser = '{Helper.CleanInput(data["strName"].ToString())}', txtBillToAddress = '{Helper.CleanInput(data["txtBillAddress"].ToString())}', dtLastEdit = NOW() WHERE UserID = '{userId}'";
                                cmd.CommandText = updateStatementUser;
                                cmd.ExecuteNonQuery();

                                //Insert update additional IDs
                                List<string> newIds = new List<string> { };
                                foreach (var idRecord in data["additionalIds"])
                                {
                                    string uniqueId = Helper.CleanInput(idRecord["strIDNumber"].ToString());
                                    newIds.Add(uniqueId);

                                    string sqlId = $"SELECT * FROM userIdNumber WHERE strIDNumber = '{uniqueId}' and refUserID = '{userId}'";
                                    cmd.CommandText = sqlId;
                                    cmd.ExecuteNonQuery();
                                    MySqlDataReader readInsertUpdateIds = cmd.ExecuteReader();
                                    //if its in data not in db add
                                    if (!readInsertUpdateIds.HasRows)
                                    {
                                        insertUpdateStatementId = $"INSERT INTO userIdNumber (refUserID, strName, strSurname, strIdType, strIDNumber,strFirstUser, strLastUser, dtLastEdit, dtFirstEdit) VALUES ('{userId}', '{Helper.CleanInput(idRecord["strName"].ToString())}', '{idRecord["strSurname"]}' ,'{Helper.CleanInput(idRecord["strIdType"].ToString())}', '{Helper.CleanInput(idRecord["strIDNumber"].ToString())}','{Helper.CleanInput(data["strName"].ToString())}', '{Helper.CleanInput(data["strName"].ToString())}',NOW(), NOW())";
                                    }
                                    //if its data and db up
                                    else
                                    {
                                        insertUpdateStatementId = $"UPDATE userIdNumber SET strName = '{Helper.CleanInput(idRecord["strName"].ToString())}', strSurname = '{Helper.CleanInput(idRecord["strSurname"].ToString())}', strIdType = '{Helper.CleanInput(idRecord["strIdType"].ToString())}',strLastUser = '{Helper.CleanInput(data["strName"].ToString())}', dtLastEdit = NOW() WHERE strIDNumber = '{uniqueId}'";
                                    }

                                    readInsertUpdateIds.Close();
                                    cmd.CommandText = insertUpdateStatementId;
                                    cmd.ExecuteNonQuery();
                                }

                                //Delete Ids that are in the db but not in the body
                                string sqlDeleteIDs = $"SELECT * FROM userIdNumber WHERE refUserID = '{userId}'";

                                cmd.CommandText = sqlDeleteIDs;
                                cmd.ExecuteNonQuery();

                                MySqlDataReader deleteIds = cmd.ExecuteReader();

                                List<string> unMatchedIDs = new List<string> { };

                                while (deleteIds.Read())
                                {
                                    if (!newIds.Contains(deleteIds.GetValue(5).ToString()))
                                    {
                                        unMatchedIDs.Add(deleteIds.GetValue(5).ToString()); //Watch out when ERD Changes
                                    }

                                }
                                deleteIds.Close();

                                foreach (var id in unMatchedIDs)
                                {
                                    string DeleteIds = $"DELETE FROM userIdNumber WHERE strIDNumber = '{id}' and refUserID = '{userId}'";
                                    cmd.CommandText = DeleteIds;
                                    cmd.ExecuteNonQuery();
                                }

                                //populate Ids dictionary from updated details
                                string getAdditionalIds = $"SELECT * FROM userIdNumber WHERE RefUserID = '{userId}'";
                                cmd.CommandText = getAdditionalIds;
                                cmd.ExecuteNonQuery();
                                MySqlDataReader readIds = cmd.ExecuteReader();
                                List<Object> ids = new List<object> { };
                                while (readIds.Read())
                                {
                                    ids.Add(new
                                    {
                                        strName = readIds.GetValue(2).ToString(),
                                        strSurname = readIds.GetValue(3).ToString(),
                                        dtDateCaptured = readIds.GetValue(9).ToString(),
                                        strIDNumber = readIds.GetValue(5).ToString()

                                    });
                                }
                                readIds.Close();

                                //populate data dictionary from updated DB
                                cmd.CommandText = statement;
                                cmd.ExecuteNonQuery();
                                MySqlDataReader readUpdate = cmd.ExecuteReader();

                                while (readUpdate.Read())
                                {
                                    //More details??
                                    dict.Add(readUpdate.GetName(2), readUpdate.GetValue(2).ToString()); //strName
                                    dict.Add(readUpdate.GetName(3), readUpdate.GetValue(3).ToString()); //strSurname
                                    dict.Add(readUpdate.GetName(4), readUpdate.GetValue(4).ToString()); //strIdType
                                    dict.Add(readUpdate.GetName(5), readUpdate.GetValue(5).ToString()); //strEmail
                                    dict.Add(readUpdate.GetName(6), readUpdate.GetValue(6).ToString()); //Mobile
                                    dict.Add("dtDateCaptured", readUpdate.GetValue(13).ToString()); //strIdNumber
                                }

                                resultResponse = new
                                {
                                    data = dict,
                                    additionalIds = ids
                                };
                            }
                            else
                            {
                                response = HttpService.CreateResponse(404, payload: "User does not exist");
                                return response;
                            }

                        }
                        else
                        {
                            response = HttpService.CreateResponse(400, payload: "UserId was not supplied");
                            _logger.WriteLog(userId, statement, "CognitoUuid was not supplied", "DB");
                            return response;
                        }
                    }
                    else
                    {
                        response = HttpService.CreateResponse(400, payload: $"{validationMessage}");
                        _logger.WriteLog(userId, statement, $"Please add parameter(s) : {validationMessage}", "DB");
                        return response;
                    }

                }
                else
                {
                    response = HttpService.CreateResponse(400, payload: "Please provide parameters");
                    _logger.WriteLog(userId, statement, "Please provide parameters", "DB");
                    return response;
                }

            }
            catch (Exception ex)
            {
                response = HttpService.CreateResponse(500, payload: ex.Message);
                _logger.WriteLog(userId, statement, ex.Message, "DB");
                return response;
            }
            finally
            {
                if (_reader != null) _reader.Close();
                if (_con != null || _con.State == ConnectionState.Open) _con.Close();
            }

            response = HttpService.CreateResponse(200, resultResponse);
            _logger.WriteLog(userId, statement, response.Body.ToString(), "DB");
            return response;
        }
    }

}