using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.IO;
using System.Data.SqlClient;



namespace JsonToDb
{
    #region RootObjects
    public class ClicksUserAgent
    {
        public string deviceType { get; set; }
        public string operatingSystem { get; set; }
        public string browser { get; set; }
        public string rawUserAgent { get; set; }
    }

    public class ClicksUserInfo
    {
        public string userId { get; set; }
        public string ip { get; set; }
        public ClicksUserAgent userAgent { get; set; }
    }

    public class ClicksSiteDomain
    {
        public string countryCode { get; set; }
        public string domainName { get; set; }
    }

    public class ClicksRootObject
    {
        public ClicksUserInfo userInfo { get; set; }
        public ClicksSiteDomain siteDomain { get; set; }
        public string dataCenter { get; set; }
        public string utcDate { get; set; }
        public string timeKey { get; set; }
        public string offerTitle { get; set; }
        public List<string> category { get; set; }
        public double price { get; set; }
        public string merchant { get; set; }
        public string source { get; set; }
        public List<string> keywords { get; set; }
        public string offerViewId { get; set; }
        public string clickId { get; set; }
        public double earning { get; set; }
        public string searchId { get; set; }
    }
    #endregion

    class Clicks
    {

        SqlConnection con = new SqlConnection(GlobalVar.databaseConnection);

        #region FieldsForClicks
        public string userInfo_userID { get; set; }
        public string userInfo_ip { get; set; }
        public string userInfo_userAgent_deviceType { get; set; }
        public string userInfo_userAgent_operatingSystem { get; set; }
        public string userInfo_userAgent_browser { get; set; }
        public string userInfo_userAgent_rawUserAgent { get; set; }
        public string siteDomain_countryCode { get; set; }
        public string siteDomain_domainName { get; set; }
        public string dataCenter { get; set; }
        public string utcDate { get; set; }
        public string timeKey { get; set; }
        public string offerTitle { get; set; }
        public string category { get; set; }
        public string price { get; set; }
        public string merchant { get; set; }
        public string source { get; set; }
        public string keywords { get; set; }
        public string offerViewId { get; set; }
        public string clickId { get; set; }
        public string earning { get; set; }
        public string searchId { get; set; }
        #endregion

        List<ClicksRootObject> rootObjectList = new List<ClicksRootObject>();
        List<string> fileList = new List<string>();

        public void start()
        {
            con.Open();

            readAllFiles();
            foreach (string s in fileList)
            {
                fileToJson(s);
            }
            jsonToRow();

            con.Close();
        }

        private void readAllFiles()
        {
            string folderPath = @"C:\Users\venskusm\Desktop\DMP\Click";
            string[] filePaths = Directory.GetFiles(folderPath, "*.json", SearchOption.TopDirectoryOnly);

            foreach (string s in filePaths)
            {
                fileList.Add(s);
            }
        }

        private void fileToJson(string path)
        {
            string line;
            int index = 0;
            // Read the file and display it line by line.
            System.IO.StreamReader file = new System.IO.StreamReader(path);
            while ((line = file.ReadLine()) != null)
            {
                var jsonObject = JsonConvert.DeserializeObject<ClicksRootObject>(line);
                rootObjectList.Add(jsonObject);
                index++;
            }

            file.Close();
        }

        private void jsonToRow()
        {

            foreach (ClicksRootObject r in rootObjectList)//0
            {
                #region defining the values of RootObjects
                try
                {
                    if (r.userInfo == null)
                    {
                        //do nothing
                    }
                    else
                    {
                        ClicksUserInfo clicksUserInfo = r.userInfo;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.userInfo.userId == null)
                    {
                        string userId = "";
                    }
                    else
                    {
                        string userId = r.userInfo.userId;//2
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.userInfo.ip == null)
                    {
                        string ip = "";
                    }
                    else
                    {
                        string ip = r.userInfo.ip;//2
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if(r.userInfo.userAgent != null)
                    {
                        ClicksUserAgent userAgent = r.userInfo.userAgent;//2
                    }
                    else
                    {
                        Console.WriteLine("nulis");
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }


                try
                {
                    if (r.userInfo.userAgent.deviceType == null)
                    {
                        string deviceType = "";
                    }
                    else
                    {
                        string deviceType = r.userInfo.userAgent.deviceType;//3
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }


                try
                {
                    if (r.userInfo.userAgent.operatingSystem == null)
                    {
                        string operatingSystem = "";
                    }
                    else
                    {
                        string operatingSystem = r.userInfo.userAgent.operatingSystem;//3
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.userInfo.userAgent.browser == null)
                    {
                        string browser = "";
                    }
                    else
                    {
                        string browser = r.userInfo.userAgent.browser;//3
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.userInfo.userAgent.rawUserAgent == null)
                    {
                        string rawUserAgent = "";
                    }
                    else
                    {
                        string rawUserAgent = r.userInfo.userAgent.rawUserAgent;//3
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }


                try
                {
                    if (r.siteDomain == null)
                    {
                        //do nothing
                    }
                    else
                    {
                        ClicksSiteDomain siteDomain = r.siteDomain;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.siteDomain.countryCode == null)
                    {
                        string countryCode = "";
                    }
                    else
                    {
                        string countryCode = r.siteDomain.countryCode;//2
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.siteDomain.domainName == null)
                    {
                        string domainName = "";
                    }
                    else
                    {
                        string domainName = r.siteDomain.domainName;//2
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.dataCenter == null)
                    {
                        string dataCenter = "";
                    }
                    else
                    {
                        string dataCenter = r.dataCenter;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.utcDate == null)
                    {
                        string utcDate = "";
                    }
                    else
                    {
                        string utcDate = r.utcDate;//1
                    }
                }

                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                string date = utcDate;


                try
                {
                    if (r.offerTitle == null)
                    {
                        string offerTitle = "";
                    }
                    else
                    {
                        string offerTitle = r.offerTitle;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                //category listas


                double price = r.price;//1


                try
                {
                    if (r.merchant == null)
                    {
                        string merchant = "";
                    }
                    else
                    {
                        string merchant = r.merchant;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.source == null)
                    {
                        string source = "";
                    }
                    else
                    {
                        string source = r.source;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                //keywords listas

                try
                {
                    if (r.offerViewId == null)
                    {
                        string offerViewId = "";
                    }
                    else
                    {
                        string offerViewId = r.offerViewId;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                try
                {
                    if (r.clickId == null)
                    {
                        string clickId = "";
                    }
                    else
                    {
                        string clickId = r.clickId;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }



                double earning = r.earning;//1

                try
                {
                    if (r.searchId == null)
                    {
                        string searchId = "";
                    }
                    else
                    {
                        string searchId = r.searchId;//1
                    }
                }
                catch (NullReferenceException ex)
                {
                    Console.WriteLine("Processor Usage" + ex.Message);
                }

                #endregion

                if (r.category.Count == 0 && r.keywords.Count == 0)
                {
                    #region testavimui
                    /*
                    Console.WriteLine(r.userInfo);//1

                    Console.WriteLine(r.userInfo.userId);//2

                    Console.WriteLine(r.userInfo.ip);//2

                    Console.WriteLine(r.userInfo.userAgent);//2

                    Console.WriteLine(r.userInfo.userAgent.deviceType);//3

                    Console.WriteLine(r.userInfo.userAgent.operatingSystem);//3

                    Console.WriteLine(r.userInfo.userAgent.browser);//3

                    Console.WriteLine(r.userInfo.userAgent.rawUserAgent);//3

                    Console.WriteLine(r.siteDomain);//1

                    Console.WriteLine(r.siteDomain.countryCode);//2

                    Console.WriteLine(r.siteDomain.domainName);//2

                    Console.WriteLine(r.dataCenter);//1

                    Console.WriteLine(r.utcDate);//1

                    Console.WriteLine(r.offerTitle);//1

                    //tuscias category listas

                    Console.WriteLine(r.price);//1

                    Console.WriteLine(r.merchant);//1

                    Console.WriteLine(r.source);//1

                    //tuscias keywords listas

                    Console.WriteLine(r.offerViewId);//1

                    Console.WriteLine(r.clickId);//1

                    Console.WriteLine(r.earning);//1

                    Console.WriteLine(r.searchId);//1*/
                    #endregion

                    CallStoredProcedure
                        (
                            r.userInfo.userId,
                            r.userInfo.ip,
                            r.userInfo.userAgent.deviceType,
                            r.userInfo.userAgent.operatingSystem,
                            r.userInfo.userAgent.browser,
                            r.userInfo.userAgent.rawUserAgent,
                            r.siteDomain.countryCode,
                            r.siteDomain.domainName,
                            r.dataCenter,
                            r.utcDate,
                            r.timeKey,
                            r.offerTitle,
                            "",
                            r.price,
                            r.merchant,
                            r.source,
                            "",
                            r.offerTitle,
                            r.clickId,
                            r.earning,
                            r.searchId
                        );

                }
                else if (r.category.Count != 0 && r.keywords.Count == 0)
                {

                    foreach (string s in r.category)
                    {
                        #region testavimui
                        Console.WriteLine(r.userInfo.userId);//2

                        Console.WriteLine(r.userInfo.ip);//2

                        Console.WriteLine(r.userInfo.userAgent.deviceType);//3

                        Console.WriteLine(r.userInfo.userAgent.operatingSystem);//3

                        Console.WriteLine(r.userInfo.userAgent.browser);//3

                        Console.WriteLine(r.userInfo.userAgent.rawUserAgent);//3

                        Console.WriteLine(r.siteDomain.countryCode);//2

                        Console.WriteLine(r.siteDomain.domainName);//2

                        Console.WriteLine(r.dataCenter);//1

                        Console.WriteLine(r.utcDate);//1

                        Console.WriteLine(r.offerTitle);//1

                        Console.WriteLine(s);//category listas

                        Console.WriteLine(r.price);//1

                        Console.WriteLine(r.merchant);//1

                        Console.WriteLine(r.source);//1

                        //tuscias keywords listas

                        Console.WriteLine(r.offerViewId);//1

                        Console.WriteLine(r.clickId);//1

                        Console.WriteLine(r.earning);//1

                        Console.WriteLine(r.searchId);//1
#endregion
                        CallStoredProcedure
                        (
                            r.userInfo.userId,
                            r.userInfo.ip,
                            r.userInfo.userAgent.deviceType,
                            r.userInfo.userAgent.operatingSystem,
                            r.userInfo.userAgent.browser,
                            r.userInfo.userAgent.rawUserAgent,
                            r.siteDomain.countryCode,
                            r.siteDomain.domainName,
                            r.dataCenter,
                            r.utcDate,
                            r.timeKey,
                            r.offerTitle,
                            s,
                            r.price,
                            r.merchant,
                            r.source,
                            "",
                            r.offerTitle,
                            r.clickId,
                            r.earning,
                            r.searchId
                        );

                    }

                }
                else if (r.category.Count == 0 && r.keywords.Count != 0)
                {

                    foreach (string s in r.keywords)
                    {
                        #region testavimui
                        /*Console.WriteLine(r.userInfo.userId);//2

                        Console.WriteLine(r.userInfo.ip);//2

                        Console.WriteLine(r.userInfo.userAgent.deviceType);//3

                        Console.WriteLine(r.userInfo.userAgent.operatingSystem);//3

                        Console.WriteLine(r.userInfo.userAgent.browser);//3

                        Console.WriteLine(r.userInfo.userAgent.rawUserAgent);//3

                        Console.WriteLine(r.siteDomain.countryCode);//2

                        Console.WriteLine(r.siteDomain.domainName);//2

                        Console.WriteLine(r.dataCenter);//1

                        Console.WriteLine(r.utcDate);//1

                        Console.WriteLine(r.offerTitle);//1

                        //tuscias category listas

                        Console.WriteLine(r.price);//1

                        Console.WriteLine(r.merchant);//1

                        Console.WriteLine(r.source);//1

                        Console.WriteLine(s);//tuscias keywords listas

                        Console.WriteLine(r.offerViewId);//1

                        Console.WriteLine(r.clickId);//1

                        Console.WriteLine(r.earning);//1

                        Console.WriteLine(r.searchId);//1*/
#endregion
                        CallStoredProcedure
                        (
                            r.userInfo.userId,
                            r.userInfo.ip,
                            r.userInfo.userAgent.deviceType,
                            r.userInfo.userAgent.operatingSystem,
                            r.userInfo.userAgent.browser,
                            r.userInfo.userAgent.rawUserAgent,
                            r.siteDomain.countryCode,
                            r.siteDomain.domainName,
                            r.dataCenter,
                            r.utcDate,
                            r.timeKey,
                            r.offerTitle,
                            "",
                            r.price,
                            r.merchant,
                            r.source,
                            s,
                            r.offerTitle,
                            r.clickId,
                            r.earning,
                            r.searchId
                        );

                    }

                }
                else if (r.category.Count != 0 && r.keywords.Count != 0)//3 ir 2
                {
                    int maxIndex = 0;
                    int index = 0;
                    if (r.category.Count > r.keywords.Count)
                    {
                        maxIndex = r.category.Count;
                    }
                    else
                    {
                        maxIndex = r.keywords.Count;
                    }


                    while (index < maxIndex)//0<3
                    {
                        if (r.category.Count > index && r.keywords.Count > index)//2>0 && 3>0, 2>1 && 3>1
                        {
                            #region testavimui
                            /*Console.WriteLine(r.userInfo.userId);//2

                            Console.WriteLine(r.userInfo.ip);//2

                            Console.WriteLine(r.userInfo.userAgent.deviceType);//3

                            Console.WriteLine(r.userInfo.userAgent.operatingSystem);//3

                            Console.WriteLine(r.userInfo.userAgent.browser);//3

                            Console.WriteLine(r.userInfo.userAgent.rawUserAgent);//3

                            Console.WriteLine(r.siteDomain.countryCode);//2

                            Console.WriteLine(r.siteDomain.domainName);//2

                            Console.WriteLine(r.dataCenter);//1

                            Console.WriteLine(r.utcDate);//1

                            Console.WriteLine(r.offerTitle);//1

                            Console.WriteLine(r.category[index]);//:)

                            Console.WriteLine(r.price);//1

                            Console.WriteLine(r.merchant);//1

                            Console.WriteLine(r.source);//1

                            Console.WriteLine(r.keywords[index]);//:)

                            Console.WriteLine(r.offerViewId);//1

                            Console.WriteLine(r.clickId);//1

                            Console.WriteLine(r.earning);//1

                            Console.WriteLine(r.searchId);//1*/
#endregion
                            CallStoredProcedure
                            (
                                r.userInfo.userId,
                                r.userInfo.ip,
                                r.userInfo.userAgent.deviceType,
                                r.userInfo.userAgent.operatingSystem,
                                r.userInfo.userAgent.browser,
                                r.userInfo.userAgent.rawUserAgent,
                                r.siteDomain.countryCode,
                                r.siteDomain.domainName,
                                r.dataCenter,
                                r.utcDate,
                                r.timeKey,
                                r.offerTitle,
                                r.category[index],
                                r.price,
                                r.merchant,
                                r.source,
                                Convert.ToString(r.keywords[index]),
                                r.offerTitle,
                                r.clickId,
                                r.earning,
                                r.searchId
                            );
                        }
                        else if (r.category.Count > index)
                        {
                            #region testavimui
                            /*Console.WriteLine(r.userInfo.userId);//2

                            Console.WriteLine(r.userInfo.ip);//2

                            Console.WriteLine(r.userInfo.userAgent.deviceType);//3

                            Console.WriteLine(r.userInfo.userAgent.operatingSystem);//3

                            Console.WriteLine(r.userInfo.userAgent.browser);//3

                            Console.WriteLine(r.userInfo.userAgent.rawUserAgent);//3

                            Console.WriteLine(r.siteDomain.countryCode);//2

                            Console.WriteLine(r.siteDomain.domainName);//2

                            Console.WriteLine(r.dataCenter);//1

                            Console.WriteLine(r.utcDate);//1

                            Console.WriteLine(r.offerTitle);//1

                            Console.WriteLine(r.category[index]);//:)

                            Console.WriteLine(r.price);//1

                            Console.WriteLine(r.merchant);//1

                            Console.WriteLine(r.source);//1

                            //nebeliko keywordu

                            Console.WriteLine(r.offerViewId);//1

                            Console.WriteLine(r.clickId);//1

                            Console.WriteLine(r.earning);//1

                            Console.WriteLine(r.searchId);//1*/
#endregion
                            CallStoredProcedure
                            (
                                r.userInfo.userId,
                                r.userInfo.ip,
                                r.userInfo.userAgent.deviceType,
                                r.userInfo.userAgent.operatingSystem,
                                r.userInfo.userAgent.browser,
                                r.userInfo.userAgent.rawUserAgent,
                                r.siteDomain.countryCode,
                                r.siteDomain.domainName,
                                r.dataCenter,
                                r.utcDate,
                                r.timeKey,
                                r.offerTitle,
                                r.category[index],
                                r.price,
                                r.merchant,
                                r.source,
                                "",
                                r.offerTitle,
                                r.clickId,
                                r.earning,
                                r.searchId
                            );
                        }
                        else if (r.keywords.Count > index)
                        {
                            #region testavimui
                            /*Console.WriteLine(r.userInfo.userId);//2

                            Console.WriteLine(r.userInfo.ip);//2

                            Console.WriteLine(r.userInfo.userAgent.deviceType);//3

                            Console.WriteLine(r.userInfo.userAgent.operatingSystem);//3

                            Console.WriteLine(r.userInfo.userAgent.browser);//3

                            Console.WriteLine(r.userInfo.userAgent.rawUserAgent);//3

                            Console.WriteLine(r.siteDomain.countryCode);//2

                            Console.WriteLine(r.siteDomain.domainName);//2

                            Console.WriteLine(r.dataCenter);//1

                            Console.WriteLine(r.utcDate);//1

                            Console.WriteLine(r.offerTitle);//1

                            //nebeliko categoriju

                            Console.WriteLine(r.price);//1

                            Console.WriteLine(r.merchant);//1

                            Console.WriteLine(r.source);//1

                            Console.WriteLine(r.keywords[index]);//:)

                            Console.WriteLine(r.offerViewId);//1

                            Console.WriteLine(r.clickId);//1

                            Console.WriteLine(r.earning);//1

                            Console.WriteLine(r.searchId);//1*/
#endregion
                            CallStoredProcedure
                            (
                                r.userInfo.userId,
                                r.userInfo.ip,
                                r.userInfo.userAgent.deviceType,
                                r.userInfo.userAgent.operatingSystem,
                                r.userInfo.userAgent.browser,
                                r.userInfo.userAgent.rawUserAgent,
                                r.siteDomain.countryCode,
                                r.siteDomain.domainName,
                                r.dataCenter,
                                r.utcDate,
                                r.timeKey,
                                r.offerTitle,
                                "",
                                r.price,
                                r.merchant,
                                r.source,
                                Convert.ToString(r.keywords[index]),
                                r.offerTitle,
                                r.clickId,
                                r.earning,
                                r.searchId
                            );

                        }
                        index++;
                    }
                }
            }
        }

        private void CallStoredProcedure
        (
            string userInfo_userID,
            string userInfo_ip,
            string userInfo_userAgent_deviceType,
            string userInfo_userAgent_operatingSystem,
            string userInfo_userAgent_browser,
            string userInfo_userAgent_rawUserAgent,
            string siteDomain_countryCode,
            string siteDomain_domainName,
            string dataCenter,
            string utcDate,
            string date,
            string offerTitle,
            string category,
            double price,
            string merchant,
            string source,
            string keywords,
            string offerViewId,
            string clickId,
            double earning,
            string searchId
        )
        {
            SqlCommand command = new SqlCommand("StoredProcedureClicks", con);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@userInfo_userID", userInfo_userID ?? "");
            command.Parameters.AddWithValue("@userInfo_ip", @userInfo_ip ?? "");
            command.Parameters.AddWithValue("@userInfo_userAgent_deviceType", userInfo_userAgent_deviceType ?? "");
            command.Parameters.AddWithValue("@userInfo_userAgent_operatingSystem", userInfo_userAgent_operatingSystem ?? "");
            command.Parameters.AddWithValue("@userInfo_userAgent_browser", userInfo_userAgent_browser ?? "");
            command.Parameters.AddWithValue("@userInfo_userAgent_rawUserAgent", userInfo_userAgent_rawUserAgent ?? "");
            command.Parameters.AddWithValue("@siteDomain_countryCode", siteDomain_countryCode ?? "");
            command.Parameters.AddWithValue("@siteDomain_domainName", siteDomain_domainName ?? "");
            command.Parameters.AddWithValue("@dataCenter", dataCenter ?? "");
            command.Parameters.AddWithValue("@utcDate", utcDate ?? "");
            command.Parameters.AddWithValue("@timeKey", timeKey ?? "");
            command.Parameters.AddWithValue("@offerTitle", offerTitle ?? "");
            command.Parameters.AddWithValue("@category", category ?? "");
            command.Parameters.AddWithValue("@price", price);
            command.Parameters.AddWithValue("@merchant", merchant ?? "");
            command.Parameters.AddWithValue("@source", source ?? "");
            command.Parameters.AddWithValue("@keywords", keywords ?? "");
            command.Parameters.AddWithValue("@offerViewId", offerViewId ?? "");
            command.Parameters.AddWithValue("@clickId", clickId ?? "");
            command.Parameters.AddWithValue("@earning", earning);
            command.Parameters.AddWithValue("@searchId", searchId ?? "");
            command.ExecuteNonQuery();
        }
    }
}