using ChoETL;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using File = System.IO.File;
    
    namespace Middleware
    {
        internal class Program
        {
            static void Main(string[] args)
            {
                string fileName = ConfigurationManager.AppSettings["JsonInput"];
                string FileOut = ConfigurationManager.AppSettings["CsvOutput"];
                string HealthStreamOut = ConfigurationManager.AppSettings["ImporterOut"];
                string json = File.ReadAllText(fileName);
    
                using (var r = new ChoJSONReader(fileName))
                {
                    using (var w = new ChoCSVWriter(FileOut).WithFirstLineHeader())
                    {
                        #region Field Cleaner
                        w.IgnoreField("uid");
                        w.IgnoreField("role");
                        w.IgnoreField("confirmed_at");
                        w.IgnoreField("last_sign_in_at");
                        w.IgnoreField("sign_in_count");
                        w.IgnoreField("last_active_at");
                        w.IgnoreField("activated");
                        w.IgnoreField("training_completion_rate");
                        w.IgnoreField("assigned_lessons_count");
                        w.IgnoreField("assigned_completed_lessons_count");
                        w.IgnoreField("completed_lessons_count");
                        w.IgnoreField("primary_and_assigned_lessons_count");
                        w.IgnoreField("primary_and_assigned_completed_lessons_count");
                        w.IgnoreField("primary_course_name");
                        w.IgnoreField("primary_course_display_name");
                        w.IgnoreField("primary_course_started_at");
                        w.IgnoreField("primary_course_lessons_count");
                        w.IgnoreField("primary_course_completed_lessons_count");
                        w.IgnoreField("java.sql_injection.completed_at");
                        w.IgnoreField("java.xxe_processing.completed_at");
                        w.IgnoreField("java.command_injection.completed_at");
                        w.IgnoreField("java.session_fixation.completed_at");
                        w.IgnoreField("java.use_of_insufficiently_random_values.completed_at");
                        w.IgnoreField("java.reflected_xss.completed_at");
                        w.IgnoreField("java.stored_persistent_xss.completed_at");
                        w.IgnoreField("java.dom_xss.completed_at");
                        w.IgnoreField("java.directory_path_traversal.completed_at");
                        w.IgnoreField("java.privileged_interface_exposure.completed_at");
                        w.IgnoreField("java.leftover_debug_code.completed_at");
                        w.IgnoreField("java.authentication_credentials_in_url.completed_at");
                        w.IgnoreField("java.session_exposure_within_url.completed_at");
                        w.IgnoreField("java.user_enumeration.completed_at");
                        w.IgnoreField("java.horizontal_privilege_escalation.completed_at");
                        w.IgnoreField("java.vertical_privilege_escalation.completed_at");
                        w.IgnoreField("java.cross_site_request_forgery_post.completed_at");
                        w.IgnoreField("java.cross_site_request_forgery_get.completed_at");
                        w.IgnoreField("java.click_jacking.completed_at");
                        w.IgnoreField("java.insecure_url_redirect.completed_at");
                        w.IgnoreField("java.insecure_tls_validation.completed_at");
                        w.IgnoreField("java.insecure_object_deserialization.completed_at");
                        w.IgnoreField("java.components_with_known_vulnerabilities.completed_at");
                        w.IgnoreField("dotnet.xxe_processing.completed_at");
                        w.IgnoreField("dotnet.command_injection.completed_at");
                        w.IgnoreField("dotnet.session_fixation.completed_at");
                        w.IgnoreField("dotnet.use_of_insufficiently_random_values.completed_at");
                        w.IgnoreField("dotnet.sql_injection.completed_at");
                        w.IgnoreField("dotnet.reflected_xss.completed_at");
                        w.IgnoreField("dotnet.stored_persistent_xss.completed_at");
                        w.IgnoreField("dotnet.dom_xss.completed_at");
                        w.IgnoreField("dotnet.directory_path_traversal.completed_at");
                        w.IgnoreField("dotnet.privileged_interface_exposure.completed_at");
                        w.IgnoreField("dotnet.leftover_debug_code.completed_at");
                        w.IgnoreField("dotnet.components_with_known_vulnerabilities.completed_at");
                        w.IgnoreField("dotnet.authentication_credentials_in_url.completed_at");
                        w.IgnoreField("dotnet.session_exposure_within_url.completed_at");
                        w.IgnoreField("dotnet.user_enumeration.completed_at");
                        w.IgnoreField("dotnet.horizontal_privilege_escalation.completed_at");
                        w.IgnoreField("dotnet.vertical_privilege_escalation.completed_at");
                        w.IgnoreField("dotnet.cross_site_request_forgery_post.completed_at");
                        w.IgnoreField("dotnet.cross_site_request_forgery_get.completed_at");
                        w.IgnoreField("dotnet.click_jacking.completed_at");
                        w.IgnoreField("dotnet.insecure_url_redirect.completed_at");
                        w.IgnoreField("dotnet.insecure_tls_validation.completed_at");
                        w.IgnoreField("dotnet.insecure_object_deserialization.completed_at");
                        w.IgnoreField("http.get_post_overview.completed_at");
                        w.IgnoreField("http.http_security_headers.completed_at");
                        w.IgnoreField("http.get_post_headers.completed_at");
                        w.IgnoreField("http.http_misused_headers.completed_at");
                        w.IgnoreField("nodejs.sql_injection.completed_at");
                        w.IgnoreField("nodejs.xxe_processing.completed_at");
                        w.IgnoreField("nodejs.command_injection.completed_at");
                        w.IgnoreField("nodejs.session_fixation.completed_at");
                        w.IgnoreField("nodejs.use_of_insufficiently_random_values.completed_at");
                        w.IgnoreField("nodejs.reflected_xss.completed_at");
                        w.IgnoreField("nodejs.stored_persistent_xss.completed_at");
                        w.IgnoreField("nodejs.dom_xss.completed_at");
                        w.IgnoreField("nodejs.directory_path_traversal.completed_at");
                        w.IgnoreField("nodejs.privileged_interface_exposure.completed_at");
                        w.IgnoreField("nodejs.leftover_debug_code.completed_at");
                        w.IgnoreField("nodejs.authentication_credentials_in_url.completed_at");
                        w.IgnoreField("nodejs.session_exposure_within_url.completed_at");
                        w.IgnoreField("nodejs.user_enumeration.completed_at");
                        w.IgnoreField("nodejs.horizontal_privilege_escalation.completed_at");
                        w.IgnoreField("nodejs.vertical_privilege_escalation.completed_at");
                        w.IgnoreField("nodejs.cross_site_request_forgery_post.completed_at");
                        w.IgnoreField("nodejs.cross_site_request_forgery_get.completed_at");
                        w.IgnoreField("nodejs.click_jacking.completed_at");
                        w.IgnoreField("nodejs.insecure_url_redirect.completed_at");
                        w.IgnoreField("nodejs.insecure_tls_validation.completed_at");
                        w.IgnoreField("nodejs.insecure_object_deserialization.completed_at");
                        w.IgnoreField("nodejs.components_with_known_vulnerabilities.completed_at");
                        w.IgnoreField("php.sql_injection.completed_at");
                        w.IgnoreField("php.xxe_processing.completed_at");
                        w.IgnoreField("php.command_injection.completed_at");
                        w.IgnoreField("php.session_fixation.completed_at");
                        w.IgnoreField("php.use_of_insufficiently_random_values.completed_at");
                        w.IgnoreField("php.reflected_xss.completed_at");
                        w.IgnoreField("php.stored_persistent_xss.completed_at");
                        w.IgnoreField("php.dom_xss.completed_at");
                        w.IgnoreField("php.directory_path_traversal.completed_at");
                        w.IgnoreField("php.privileged_interface_exposure.completed_at");
                        w.IgnoreField("php.leftover_debug_code.completed_at");
                        w.IgnoreField("php.authentication_credentials_in_url.completed_at");
                        w.IgnoreField("php.session_exposure_within_url.completed_at");
                        w.IgnoreField("php.user_enumeration.completed_at");
                        w.IgnoreField("php.horizontal_privilege_escalation.completed_at");
                        w.IgnoreField("php.vertical_privilege_escalation.completed_at");
                        w.IgnoreField("php.cross_site_request_forgery_get.completed_at");
                        w.IgnoreField("php.click_jacking.completed_at");
                        w.IgnoreField("php.insecure_url_redirect.completed_at");
                        w.IgnoreField("php.insecure_tls_validation.completed_at");
                        w.IgnoreField("php.components_with_known_vulnerabilities.completed_at");
                        w.IgnoreField("php.cross_site_request_forgery_post.completed_at");
                        w.IgnoreField("php.insecure_object_deserialization.completed_at");
                        w.IgnoreField("frontend.reverse_tabnabbing.completed_at");
                        w.IgnoreField("cplus.c_orientation.completed_at");
                        w.IgnoreField("android.forceful_browsing.completed_at");
                        w.IgnoreField("ios.excessive_logging.completed_at");
                        w.IgnoreField("hacking_headlines.eslint_scope.completed_at");
                        w.IgnoreField("hacking_headlines.pippo_deserialization.completed_at");
                        w.IgnoreField("hacking_headlines.vertx_xxe.completed_at");
                        w.IgnoreField("hacking_headlines.flask_panel_xss.completed_at");
                        w.IgnoreField("hacking_headlines.cryptiles.completed_at");
                        w.IgnoreField("hacking_headlines.mozilla_bleach_mutation_cross_site_scripting_mxss.completed_at");
                        w.IgnoreField("hacking_headlines.apache_unomi.completed_at");
                        w.IgnoreField("go.sql_injection.completed_at");
                        w.IgnoreField("go.command_injection.completed_at");
                        w.IgnoreField("go.session_fixation.completed_at");
                        w.IgnoreField("python.sql_injection.completed_at");
                        w.IgnoreField("python.xxe_processing.completed_at");
                        w.IgnoreField("python.command_injection.completed_at");
                        w.IgnoreField("python.session_fixation.completed_at");
                        w.IgnoreField("python.use_of_insufficiently_random_values.completed_at");
                        w.IgnoreField("python.reflected_xss.completed_at");
                        w.IgnoreField("python.stored_persistent_xss.completed_at");
                        w.IgnoreField("python.dom_xss.completed_at");
                        w.IgnoreField("python.directory_path_traversal.completed_at");
                        w.IgnoreField("python.privileged_interface_exposure.completed_at");
                        w.IgnoreField("python.leftover_debug_code.completed_at");
                        w.IgnoreField("python.authentication_credentials_in_url.completed_at");
                        w.IgnoreField("python.session_exposure_within_url.completed_at");
                        w.IgnoreField("python.user_enumeration.completed_at");
                        w.IgnoreField("python.horizontal_privilege_escalation.completed_at");
                        w.IgnoreField("python.vertical_privilege_escalation.completed_at");
                        w.IgnoreField("python.cross_site_request_forgery_post.completed_at");
                        w.IgnoreField("python.cross_site_request_forgery_get.completed_at");
                        w.IgnoreField("python.click_jacking.completed_at");
                        w.IgnoreField("python.insecure_url_redirect.completed_at");
                        w.IgnoreField("python.insecure_tls_validation.completed_at");
                        w.IgnoreField("python.insecure_object_deserialization.completed_at");
                        w.IgnoreField("python.components_with_known_vulnerabilities.completed_at");
                        w.IgnoreField("source_codes.java.completed_at");
                        w.IgnoreField("backend_dotnet.password_storage.completed_at");
                        w.IgnoreField("backend_dotnet.encoding_hashing_encryption.completed_at");
                        w.IgnoreField("backend_dotnet.sql_injection.completed_at");
                        w.IgnoreField("dotnet_advanced_lessons.second_order_sql_injection.completed_at");
                        w.IgnoreField("dotnet_advanced_lessons.sql_injection.completed_at");
                        w.IgnoreField("dotnet_advanced_lessons.command_injection.completed_at");
                        w.IgnoreField("dotnet_advanced_lessons.session_fixation.completed_at");
                        w.IgnoreField("dotnet_advanced_lessons.cross_site_request_forgery.completed_at");
                        w.IgnoreField("dotnet_advanced_lessons.use_of_insufficiently_random_values.completed_at");
                        #endregion
    
                        w.Write(r);
                    }
                    var dt = CSVtoDataTable(FileOut.ToString());
                    List<Info> list = new List<Info>();
                    DataView view1 = new DataView(dt);
                    List<string> cols = new List<string>();
                    int i2 = 0;
                    foreach (DataColumn item in dt.Columns)
                    {
                        cols.Add(item.ColumnName);
                    }
                    foreach (DataRow row in dt.Rows)
                    {
                        int columncount = dt.Columns.Count / 2;
                        for (int i = 0; i < columncount; i++)
                        {
                            DataTable table1 = view1.ToTable("Table" + i, true, cols.Skip(1 + 2 * i).Take(2).ToArray());
                            Info info = new Info();
                            info.Email = dt.Rows[i2][0].ToString();
                            info.Course = table1.Columns[1].ColumnName.Replace(".completed_date", "");
                            info.Score = table1.Rows[i2][0].ToString();
                            info.completeddate = table1.Rows[i2][1].ToString();
                            list.Add(info);
                        }
                        i2++;
                        ExportData.ExportCsv(list, HealthStreamOut);
                        // Each row of view1 needs to be written out to CSV file                   
                    }
                }
            }
    
            public static DataTable CSVtoDataTable(string inputpath)
            {
    
                DataTable csvdt = new DataTable();
                string Fulltext;
                if (File.Exists(inputpath))
                {
                    using (StreamReader sr = new StreamReader(inputpath))
                    {
                        while (!sr.EndOfStream)
                        {
                            Fulltext = sr.ReadToEnd().ToString();//read full content
                            string[] rows = Fulltext.Split('\n');//split file content to get the rows
                            for (int i = 0; i < rows.Count() - 1; i++)
                            {
                                var regex = new Regex("\\\"(.*?)\\\"");
                                var output = regex.Replace(rows[i], m => m.Value.Replace(",", "\\c"));//replace commas inside quotes
                                string[] rowValues = output.Split(',');//split rows with comma',' to get the column values
                                {
                                    if (i == 0)
                                    {
                                        for (int j = 0; j < rowValues.Count(); j++)
                                        {
                                            csvdt.Columns.Add(rowValues[j].Replace("\\c", ","));//headers
                                        }
    
                                    }
                                    else
                                    {
                                        try
                                        {
                                            DataRow dr = csvdt.NewRow();
                                            for (int k = 0; k < rowValues.Count(); k++)
                                            {
                                                if (k >= dr.Table.Columns.Count)// more columns may exist
                                                {
                                                    csvdt.Columns.Add("clmn" + k);
                                                    dr = csvdt.NewRow();
                                                }
                                                dr[k] = rowValues[k].Replace("\\c", ",");
    
                                            }
                                            csvdt.Rows.Add(dr);//add other rows
                                        }
                                        catch
                                        {
                                            Console.WriteLine("error");
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                return csvdt;
            }
    
            public static class ExportData
            {
                public static void ExportCsv<T>(List<T> genericList, string fileName)
                {
                    var sb = new StringBuilder();
                    var basePath = AppDomain.CurrentDomain.BaseDirectory;
                    var finalPath = Path.Combine(basePath, fileName);
                    var header = "";
                    var info = typeof(T).GetProperties();
                    if (!File.Exists(finalPath))
                    {
                        var file = File.Create(finalPath);
                        file.Close();
                        foreach (var prop in typeof(T).GetProperties())
                        {
                            header += prop.Name + ",";
                        }
                        header = header.Substring(0, header.Length - 2);
                        sb.AppendLine(header);
                        TextWriter sw = new StreamWriter(finalPath, true);
                        sw.Write(sb.ToString());
                        sw.Close();
                    }
                    foreach (var obj in genericList)
                    {
                        sb = new StringBuilder();
                        var line = "";
                        foreach (var prop in info)
                        {
                            line += prop.GetValue(obj, null) + ",";
                        }
                        line = line.Substring(0, line.Length - 2);
                        sb.AppendLine(line);
                        TextWriter sw = new StreamWriter(finalPath, true);
                        sw.Write(sb.ToString());
                        sw.Close();
                    }
                }
            }
    
            public class Info
            {
                public string Email { get; set; }
                public string Course { get; set; }
                public string Score { get; set; }
                public string completeddate { get; set; }
            }
    
        }
    }