//-----------------------------------------------------------------------
// <copyright>
// Copyright (C) Sergey Solyanik. All rights reserved.
// </copyright>
//----------------------------------------------------------------------- 
namespace GunResearch
{
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    using Excel = Microsoft.Office.Interop.Excel;

    class Program
    {
        static void Main(string[] args)
        {
            Dictionary<string, CountryData> countries = new Dictionary<string, CountryData>(StringComparer.OrdinalIgnoreCase);
            var excel = new Excel.Application();
            excel.Visible = true;

            Excel.Workbook wb = excel.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, "GINI.xlsx"), ReadOnly: true);
            Excel.Worksheet ws = wb.Worksheets["Sheet1"];
            int row = 4;
            for (;;)
            {
                string country = ws.Cells[row, "A"].Value;
                if (string.IsNullOrWhiteSpace(country) || "World".Equals(country, StringComparison.OrdinalIgnoreCase))
                {
                    break;
                }

                country = country.Trim();
                CountryData cd = null;
                if (!countries.TryGetValue(country, out cd))
                {
                    cd = new CountryData();
                    cd.Name = country;
                    countries[country] = cd;
                }

                cd.GINI = ws.Cells[row, "D"].Value is double ? (double)ws.Cells[row, "D"].Value : 0;
                ++row;
            }

            wb.Close();

            wb = excel.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, "GunOwnership.xlsx"), ReadOnly: true);
            ws = wb.Worksheets["Sheet1"];

            row = 3;
            for (;;)
            {
                string country = ws.Cells[row, "A"].Value;
                if (string.IsNullOrWhiteSpace(country))
                {
                    break;
                }

                country = country.Trim();
                CountryData cd = null;
                if (!countries.TryGetValue(country, out cd))
                {
                    cd = new CountryData();
                    cd.Name = country;
                    countries[country] = cd;
                }

                cd.GunOwnershipRate = ws.Cells[row, "B"].Value;
                ++row;
            }

            wb.Close();

            wb = excel.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, "HomicideRates.xlsx"), ReadOnly: true);
            ws = wb.Worksheets["Sheet1"];

            row = 5;
            for (;;)
            {
                string country = ws.Cells[row, "A"].Value;
                if (string.IsNullOrWhiteSpace(country) || "World".Equals(country, StringComparison.OrdinalIgnoreCase))
                {
                    break;
                }

                country = country.Trim();
                CountryData cd = null;
                if (!countries.TryGetValue(country, out cd))
                {
                    cd = new CountryData();
                    cd.Name = country;
                    countries[country] = cd;
                }

                cd.HomicideRate = ws.Cells[row, "B"].Value;
                ++row;
            }

            wb.Close();

            wb = excel.Workbooks.Add();
            ws = wb.Worksheets["Sheet1"];

            row = 1;
            ws.Cells[row, "A"].Value = "Country";
            ws.Cells[row, "B"].Value = "GINI";
            ws.Cells[row, "C"].Value = "Gun Ownership Rate";
            ws.Cells[row, "D"].Value = "Homicide Rate";
            ++row;

            string[] countriesArray = countries.Keys.ToArray();
            Array.Sort(countriesArray, (a, b) => string.Compare(a, b));

            foreach(string s in countriesArray)
            {
                CountryData cd = countries[s];

                if (cd.GINI == 0 || cd.GunOwnershipRate == 0 || cd.HomicideRate == 0)
                {
                    continue;
                }

                ws.Cells[row, "A"].Value = cd.Name;
                ws.Cells[row, "B"].Value = cd.GINI;
                ws.Cells[row, "C"].Value = cd.GunOwnershipRate;
                ws.Cells[row, "D"].Value = cd.HomicideRate;
                ++row;
            }
        }
    }

    class CountryData
    {
        public string Name;
        public double GINI;
        public double HomicideRate;
        public double GunOwnershipRate;
    }
}