using System;
using System.Data;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace mySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // SQL Connection Data
                MySqlConnection connection = new MySqlConnection();
                MySqlDataAdapter data = new MySqlDataAdapter();
                connection.ConnectionString = "server=194.78.56.157; database=tiwat; " +
                    "port=40009; user=tiwatadmin; password=watti;";
                connection.Open();

                // Specify which node to get neighbordata from
                string Nodeid = "FD01";
                Console.WriteLine("Data from neighbors of " + Nodeid + "\n");

                // Get the different neighbors from the specified node
                string sql = "SELECT DISTINCT Neighbors.Neighborid FROM tiwat.Neighbors WHERE Neighbors.Nodeid = (SELECT Node.idNode FROM tiwat.Node WHERE Node.Name = '"
                    + Nodeid + "') ORDER BY Neighbors.Neighborid ASC";
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                MySqlDataReader rdr = cmd.ExecuteReader();

                List<int> Neighborsid = new List<int>();

                while (rdr.Read())
                {
                    Neighborsid.Add((int)rdr[0]);
                }
                rdr.Close();

                // get the names of the different neighbors from the specified node
                List<string> Neighbors = new List<string>();
                for (int i = 0; i < Neighborsid.Count; i++)
                {
                    sql = "SELECT Node.Name FROM tiwat.Node WHERE Node.idNode = "
                        + Neighborsid[i];
                    cmd = new MySqlCommand(sql, connection);
                    rdr = cmd.ExecuteReader();
                    rdr.Read();
                    Neighbors.Add((string)rdr[0]);
                    rdr.Close();
                }

                // get the RSL and successrate of the ALL SAMPLES of ALL NEIGHBOURS of the specified node
                List<List<int>> NeighborData = new List<List<int>>();
                for (int i = 0; i < Neighbors.Count; i++)
                {
                    sql = "SELECT DISTINCT Neighbors.RSL, Neighbors.Sendsuccess, Neighbors.Sendunsuccess FROM tiwat.Neighbors, tiwat.Node WHERE Neighbors.Nodeid = (SELECT idNode FROM tiwat.Node WHERE Name = '" + Nodeid +
                        "') AND Neighbors.Neighborid = " + Neighborsid[i];
                    cmd = new MySqlCommand(sql, connection);
                    rdr = cmd.ExecuteReader();
                    List<int> Data = new List<int>();
                    // get the rsl and successrate info from ALL SAMPLES from ONE NEIGHBOUR
                    while (rdr.Read())
                    {
                        Data.Add(Convert.ToInt32(rdr[0]));
                        Data.Add(Convert.ToInt32(rdr[1]));
                        Data.Add(Convert.ToInt32(rdr[2]));
                    }
                    NeighborData.Add(Data);
                    rdr.Close();
                }

                // output Names of Neighbour (using the list Neighbors) and corresponding data (using NeighborData)
                int p = 0;
                foreach (var sublist in NeighborData)
                {
                    Console.WriteLine("Data from " + Neighbors[p]);
                    Console.WriteLine("Sample 1 - RSL: " + sublist[0] + ", SuccessRate = " + String.Format("{0:0.00}", (double)sublist[1] / ((double)sublist[1] + (double)sublist[2])));
                    Console.WriteLine("Sample 2 - RSL: " + sublist[3] + ", SuccessRate = " + String.Format("{0:0.00}", (double)sublist[4] / ((double)sublist[4] + (double)sublist[5])));
                    Console.WriteLine("Sample 3 - RSL: " + sublist[6] + ", SuccessRate = " + String.Format("{0:0.00}", (double)sublist[7] / ((double)sublist[7] + (double)sublist[8])));
                    Console.WriteLine("Sample 4 - RSL: " + sublist[9] + ", SuccessRate = " + String.Format("{0:0.00}", (double)sublist[10] / ((double)sublist[10] + (double)sublist[11])));
                    Console.WriteLine("Sample 5 - RSL: " + sublist[12] + ", SuccessRate = " + String.Format("{0:0.00}", (double)sublist[13] / ((double)sublist[13] + (double)sublist[14])));
                    Console.WriteLine();
                    p++;
                }

                connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            Console.ReadLine();
        }
    }
}