using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SQLite;
using System.Diagnostics;
using System.Numerics;

namespace WindowsFormsApp4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            treeView1.BeforeExpand += treeView1_BeforeExpand;
            treeView1.BeforeCollapse += treeView1_BeforeCollapse;
            LoadData();
        }

        public void LoadData()
        {
            using (var sqlcon = new SQLiteConnection(@"DataSource=A:\sqlite\321.db"))
            {
                sqlcon.Open();
                using (var sqlcommand = new SQLiteCommand(sqlcon))
                {
                    sqlcommand.CommandText = @"SELECT students.name_students wName
     ,students.id_students idW
     ,department.name_department cName
     ,department.id_department idC
     ,faculty.name_faculty coName
FROM faculty LEFT OUTER
                 JOIN department ON faculty.id_faculty = department.id_faculty LEFT OUTER
                 JOIN students ON department.id_department = students.id_department

ORDER BY coName, idC, cName, idW, wName";
                    using (var reader = sqlcommand.ExecuteReader())
                    {
                        var lastAddedCountryNode = new TreeNode();
                        var lastAddedCompanyNode = new TreeNode();
                        var  lastIdCompany  = -1;
                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(reader.GetOrdinal("coName")))
                            {
                                var countryName = (string) reader["coName"];
                                if (countryName != lastAddedCountryNode.Text)
                                {
                                    lastAddedCountryNode = treeView1.Nodes.Add(countryName);
                                    lastAddedCountryNode.Tag = countryName;
                                }
                            }

                            if (!reader.IsDBNull(reader.GetOrdinal("cName")))
                            {
                                var idCompany = Convert.ToInt32(reader["idC"]);

                                if (idCompany != lastIdCompany)
                                {
                                    var companyName = (string) reader["cName"];
                                    lastAddedCompanyNode = lastAddedCountryNode.Nodes.Add(companyName);
                                    lastAddedCompanyNode.Tag = false;
                                    lastIdCompany = idCompany;
                                }
                            }

                            if (reader.IsDBNull(reader.GetOrdinal("wName")))
                            {
                                continue;
                            }

                            lastAddedCompanyNode.Tag = true;
                            var workerName = (string) reader["wName"];
                            lastAddedCompanyNode.Nodes.Add(workerName);
                        }
                    }
                }
            }

        }

        private void treeView1_BeforeExpand(object sender, TreeViewCancelEventArgs e)
        {
            if (e.Node.Level != 0)
            {
                return;
            }

            int sumA = 0, sumB = 0;
            foreach (TreeNode lastAddedCompanyNode in e.Node.Nodes)
            {
                if ((bool)lastAddedCompanyNode.Tag)
                {
                    sumA += 1;
                }
                else
                {
                    sumB += 1;
                }
            }

            e.Node.Text += @" " + sumA + @" / " + sumB;
        }

        private void treeView1_BeforeCollapse(object sender, TreeViewCancelEventArgs e)
        {
            if (e.Node.Level == 0)
            {
                e.Node.Text = (string)e.Node.Tag;
            }
        }


        private void Form1_Load(object sender, EventArgs e)
        {
            throw new System.NotImplementedException();
        }
    }
}