using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Windows.Forms;

namespace DB_Tree
{
    public partial class TreeVisualization : Form
    {
        List<string> towns;

        public TreeVisualization()
        {
            towns = new List<string>();

            InitializeComponent();
            InitializeTVResources();
        }

        public void InitializeTVResources()
        {
            var sBuilder = new SQLiteConnectionStringBuilder();
            sBuilder.DataSource = @"C:\Users\thesk\OneDrive\Desktop\ПГНИУ\Практика\УП БД\Лаба 1\Workers.sqlite";
            sBuilder.ForeignKeys = true;

            string sConnStr = sBuilder.ConnectionString;

            using (SQLiteConnection sConn = new SQLiteConnection(sConnStr))
            {
                sConn.Open();
                SQLiteCommand sCommand = new SQLiteCommand();
                sCommand.Connection = sConn;
                sCommand.CommandText = @"
                    SELECT town.id as t_id,
                        town.name as t_name,
                        company.id as c_id,
                        company.name as c_name,
                        worker.name as w_name
                    FROM town
                        LEFT OUTER JOIN company
                            ON town.id = company.town_id
                        LEFT OUTER JOIN worker
                            ON company.id = worker.company_id
                    ORDER BY t_name,t_id,c_name ,c_id,w_name";

                using (SQLiteDataReader reader = sCommand.ExecuteReader())
                {
                    TreeNode CurrentTown = null;
                    TreeNode CurrentCompany = null;
                    long CurrentTownId = -1;
                    long CurrentCompanyId = -1;

                    while (reader.Read())
                    {
                        string TownName = (string)reader["t_name"];
                        long TownId = (long)reader["t_id"];

                        if (TownId != CurrentTownId)
                        {
                            towns.Add(TownName);
                            CurrentTown = DBVisualization.Nodes.Add(TownName);
                            CurrentTownId = TownId;
                        }

                        if (reader["c_id"] is DBNull)
                            continue;

                        string CompanyName = (string)reader["c_name"];
                        long CompanyId = (long)reader["c_id"];

                        if (CompanyId != CurrentCompanyId)
                        {
                            CurrentCompany = CurrentTown.Nodes.Add(CompanyName);
                            CurrentCompanyId = CompanyId;
                        }

                        if (reader["w_name"] is DBNull)
                            continue;

                        string WorkerName = (string)reader["w_name"];
                        CurrentCompany.Nodes.Add(WorkerName);

                    }
                }
            }
        }

        private void DBVisualization_AfterCollapse(object sender, TreeViewEventArgs e)
        {
            if (e.Node.Level != 0)
                return;
            e.Node.Text = towns[e.Node.Index];
        }

        private void DBVisualization_AfterExpand(object sender, TreeViewEventArgs e)
        {
            if (e.Node.Level != 0)
                return;

            int A = 0, B = 0;
            foreach (TreeNode node in e.Node.Nodes)
                if (node.Nodes.Count != 0)
                    A++;
                else
                    B++;

            e.Node.Text += $" {A}/{B}";
        }
    }
}