using IoTHubTrigger = Microsoft.Azure.WebJobs.EventHubTriggerAttribute;

using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Azure.EventHubs;
using System.Text;
using System.Net.Http;
using Microsoft.Extensions.Logging;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System;
using NewInlaminingsuppgift.Models;

namespace NewInlaminingsuppgift
{
    public static class SaveToSql
    {
        private static HttpClient client = new HttpClient();

        [FunctionName("SaveToSql")]
        public static void Run([IoTHubTrigger("messages/events", Connection = "IotHubConnection", ConsumerGroup = "sql")]EventData message, ILogger log)
        {
            var obj = JsonConvert.DeserializeObject<DeviceMessage>(Encoding.UTF8.GetString(message.Body.Array));

            var sqlConn = Environment.GetEnvironmentVariable("sqlConnection");
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {

                string _deviceid = "";
                int _locationid = -1;
                int _ownerid = -1;

                var locationQuery = "IF NOT EXISTS (SELECT 1 FROM Location WHERE Latitude = @cLatitude AND Longitud = @cLongitude) " +
                    "INSERT INTO Location (Latitude, Longitud) OUTPUT INSERTED.Id " +
                    "VALUES(@cLatitude, @cLongitude) ELSE SELECT Id FROM Location WHERE Latitude = @cLatitude AND Longitud = @cLongitude";


                var deviceQuery = "IF NOT EXISTS (SELECT 1 FROM Devices WHERE DeviceId = @cDeviceId) INSERT INTO Devices (DeviceId, LocationId, OwnerId) " +
                    "OUTPUT INSERTED.DeviceId VALUES(@cDeviceId, @cLocationId, @cOwnerId) ELSE SELECT DeviceId FROM Devices WHERE DeviceId = @cDeviceId";


                var ownerQuery = "IF NOT EXISTS (SELECT 1 FROM Owner WHERE MyName = @cMyName) INSERT INTO Owner (MyName, ClassName, SchoolName) OUTPUT INSERTED.Id" +
                    " VALUES(@cMyName, @cClassName, @cSchoolName) ELSE SELECT Id FROM Owner WHERE MyName = @cMyName";


                var messageQuery = "INSERT INTO Messages (MessageCounter, DeviceId, Lumen, Temperature, Humidity, Timestamp, TemperatureAlert) " +
                   "VALUES(@cMessageId, @cDeviceId, @cLuminosity, @cTemperature, @cHumidity, @cEpochTime, @cTemperatureAlert)";

                conn.Open();
                using (SqlCommand cmd = new SqlCommand(locationQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@cLatitude", message.Properties["latitude"]);
                    cmd.Parameters.AddWithValue("@cLongitude", message.Properties["longitude"]);

                    _locationid = int.Parse(cmd.ExecuteScalar().ToString());
                }

                using (SqlCommand cmd = new SqlCommand(ownerQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@cSchoolName", message.Properties["schoolName"]);
                    cmd.Parameters.AddWithValue("@cMyName", message.Properties["myName"]);
                    cmd.Parameters.AddWithValue("@cClassName", message.Properties["className"]);

                    _ownerid = int.Parse(cmd.ExecuteScalar().ToString());
                }

                using (SqlCommand cmd = new SqlCommand(deviceQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@cDeviceId", message.Properties["device"]);
                    cmd.Parameters.AddWithValue("@cLocationId", _locationid);
                    cmd.Parameters.AddWithValue("@cOwnerId", _ownerid);

                    _deviceid = cmd.ExecuteScalar().ToString();
                }

                using (SqlCommand cmd = new SqlCommand(messageQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@cMessageId", obj.messageCount);
                    cmd.Parameters.AddWithValue("@cDeviceId", _deviceid);
                    cmd.Parameters.AddWithValue("@cTemperatureAlert", message.Properties["temperatureAlert"]);
                    cmd.Parameters.AddWithValue("@cTemperature", obj.Temperature);
                    cmd.Parameters.AddWithValue("@cHumidity", obj.Humidity);
                    cmd.Parameters.AddWithValue("@cLuminosity", obj.Luminosity);
                    cmd.Parameters.AddWithValue("@cEpochTime", obj.EpochTime);

                    cmd.ExecuteNonQuery();
                }
            }
            }
        }
}