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 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<object> 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_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 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\Clicks";
            string folderPath = @"C:\Users\romasda\Desktop\JsonToDb\clicks";
            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
                ClicksUserInfo clicksUserInfo = r.userInfo;//1

                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);
                }

                ClicksUserAgent userAgent = r.userInfo.userAgent;//2

                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);
                }

                ClicksSiteDomain siteDomain = r.siteDomain;//1

                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);
                }


                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)
                {/*
                    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

                    //category listas

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

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

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

                    //keywords listas

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

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

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

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


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

                    foreach (string s in r.category)
                    {
                        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

                        Console.WriteLine(r.utcDate);
                        Console.WriteLine("stop");

                        callSP(r.userInfo.userId, r.userInfo.ip, r.utcDate, r.earning);

                    }

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

                    foreach (string s in r.keywords)
                    {

                        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

                    }

                }
                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
                        {
                            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
                        }
                        else if (r.category.Count > index)
                        {
                            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
                        }
                        else if (r.keywords.Count > index)
                        {

                            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
                        }
                        index++;
                    }
                }
            }
        }

        private void callSP(string userInfo_userID, string userInfo_ip , string utcDate, double earning)
        {
            SqlCommand command = new SqlCommand("sp_insertClick",con);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@userInfoUserId", userInfo_userID);
            command.Parameters.AddWithValue("@userInfoUserIP", userInfo_ip);
            command.Parameters.AddWithValue("@utcDate", utcDate);
            command.Parameters.AddWithValue("@earning", earning);

            command.ExecuteNonQuery();

        }
    }
}