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 Npgsql;

using System.Threading;
using System.Collections;


public struct things
{
    public int id, cost;
    public int[] cat, tp, nm;
    public string category, type, name;
}

namespace DGV
{
    public partial class Form1 : Form
    {

        private static string connectionString = "Server=localhost;Port=5432;User=postgres;Password=12345;Database=data";
        private static string insertCategoryString = "INSERT INTO categories(id, name, id_parent) VALUES (@id, @name, @id_parent)";
        private static string insertProductString = "INSERT INTO products(id, name, cost, image, description, id_category) VALUES (@id, @name, @cost, @image, @description, @id_category)";
        private static string selectString = "SELECT p.id, p.name, p.cost, p.image, p.description, c.name FROM products p LEFT JOIN categories c on p.id_category = c.id ORDER BY p.id";
        private static string deleteString = "DELETE FROM products WHERE id = ";

        private int currentPage = 0;

        private string currentRow;
        List<Row> rowList = new List<Row>();

        NpgsqlConnection mainConnection = new NpgsqlConnection(connectionString);


        public Form1()
        {
            InitializeComponent();
            mainConnection.Open();
            dataGridView1.SuspendLayout();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        }


        private void button1_Click(object sender, EventArgs e)
        {
            createProducts();
        }


        private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
        {
         //   DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            currentRow = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            textBox1.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
            textBox2.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
            textBox3.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
            textBox4.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
        }

        private void button2_Click(object sender, EventArgs e) //редактирование
        {
            if (dataGridView1.SelectedRows.Count == 1)
            {
                try
                {
                    string str = "UPDATE products SET " + "name= '" + textBox3.Text + "', cost= " + textBox4.Text + ", description= '" + textBox2.Text + "', id_category= " + textBox1.Text + " WHERE id = " + currentRow;
                    Console.WriteLine(str);
                    NpgsqlCommand command = new NpgsqlCommand(str, mainConnection);
                    command.ExecuteReader();

                    int n = dataGridView1.SelectedRows[0].Index;
                    dataGridView1.Rows[n].Cells[1].Value = textBox1.Text;
                    dataGridView1.Rows[n].Cells[2].Value = textBox2.Text;
                    dataGridView1.Rows[n].Cells[3].Value = textBox3.Text;
                    dataGridView1.Rows[n].Cells[4].Value = textBox4.Text;
                }
                catch (Exception exc)
                {
                    MessageBox.Show("Data is incorrect");
                }
            }
            else
            {
                MessageBox.Show("Выберите строку для редактирования.", "Ошибка.");
            }
        }

        private void button3_Click(object sender, EventArgs e) //удалить выбранную строку
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                Console.WriteLine(deleteString + currentRow);
                NpgsqlCommand command = new NpgsqlCommand(deleteString + currentRow, mainConnection);
                command.ExecuteReader();
                dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
            }
            else
            {
                MessageBox.Show("Выберите строку для удаления.", "Ошибка.");
            }
        }

        private void button4_Click(object sender, EventArgs e) //очистить таблицу
        {
            if (dataGridView1.Rows.Count > 0)
            {
                dataGridView1.Rows.Clear();
            }
            else
            {
                MessageBox.Show("Таблица пустая.", "Ошибка.");
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            addRow();
        }

        private void createCategories()
        {
            NpgsqlConnection connection = new NpgsqlConnection(connectionString);
            connection.Open();
            int mainCategoriesNumber = 40;
            int subcategoriesNumber = 5;
            int id = 0;
            for (int i = 1; i < mainCategoriesNumber; i++) {
                NpgsqlCommand command = new NpgsqlCommand(insertCategoryString, connection);
                id++;

                var idParameter = command.CreateParameter();
                idParameter.ParameterName = "id";
                idParameter.Value = id;
                command.Parameters.Add(idParameter);

                var nameParameter = command.CreateParameter();
                nameParameter.ParameterName = "name";
                nameParameter.Value = "Категория " + id;
                command.Parameters.Add(nameParameter);

                var idParParameter = command.CreateParameter();
                idParParameter.ParameterName = "id_parent";
                idParParameter.Value = 0;
                command.Parameters.Add(idParParameter);

                NpgsqlDataReader dr = command.ExecuteReader();
            }

            for (int i = 1; i < mainCategoriesNumber; i++)
            {
                int parent_id = i;
                for (int j = 1; j < subcategoriesNumber; j++)
                {
                    NpgsqlCommand command = new NpgsqlCommand(insertCategoryString, connection);
                    id++;
                    var idParameter = command.CreateParameter();
                    idParameter.ParameterName = "id";
                    idParameter.Value = id;
                    command.Parameters.Add(idParameter);

                    var nameParameter = command.CreateParameter();
                    nameParameter.ParameterName = "name";
                    nameParameter.Value = "Категория " + parent_id + "." + j;
                    command.Parameters.Add(nameParameter);

                    var idParParameter = command.CreateParameter();
                    idParParameter.ParameterName = "id_parent";
                    idParParameter.Value = parent_id;
                    command.Parameters.Add(idParParameter);

                    NpgsqlDataReader dr = command.ExecuteReader();
                }
            }

            connection.Close();
        }

        private void createProducts()
        {
            NpgsqlConnection connection = new NpgsqlConnection(connectionString);
            connection.Open();
            int productsNumber = 50000;
            Random rnd = new Random();
            int id = 0;
            for (int i = 1; i < productsNumber; i++)
            {
                NpgsqlCommand command = new NpgsqlCommand(insertProductString, connection);
                id++;

                var idParameter = command.CreateParameter();
                idParameter.ParameterName = "id";
                idParameter.Value = id;
                command.Parameters.Add(idParameter);

                var nameParameter = command.CreateParameter();
                nameParameter.ParameterName = "name";
                nameParameter.Value = "Имя " + id;
                command.Parameters.Add(nameParameter);

                var costParameter = command.CreateParameter();
                costParameter.ParameterName = "cost";
                costParameter.Value = rnd.NextDouble()*200000;
                command.Parameters.Add(costParameter);

                var ImageParameter = command.CreateParameter();
                ImageParameter.ParameterName = "image";
                ImageParameter.Value = "C:\\LAB1\\Folder\\" + id +".jpg";
                command.Parameters.Add(ImageParameter);

                var descriptionParameter = command.CreateParameter();
                descriptionParameter.ParameterName = "description";
                descriptionParameter.Value = "Описание " + i;
                command.Parameters.Add(descriptionParameter);

                var idCatParameter = command.CreateParameter();
                idCatParameter.ParameterName = "id_category";
                idCatParameter.Value = rnd.Next (1, 195);
                command.Parameters.Add(idCatParameter);

                NpgsqlDataReader dr = command.ExecuteReader();
            }
            Console.WriteLine("Data Inserted");
            connection.Close();
        }

        private void addRow() {
            try
            {
                NpgsqlCommand maxCommand = new NpgsqlCommand("SELECT MAX (id) as max_id FROM products", mainConnection);
                NpgsqlDataReader maxDr = maxCommand.ExecuteReader();
                maxDr.Read();
                int id = Convert.ToInt32(maxDr[0].ToString());
                id++;

                maxDr.Close();
                string str;
                str = "INSERT INTO products(id, name, cost, image, description, id_category)" +
                                                            " VALUES (" + id.ToString() + ", '" + textBox3.Text + "', " + textBox4.Text + ", '" + textBox5.Text + "', '" + textBox2.Text + "', " + textBox1.Text + " )";
                Console.WriteLine(str);
                NpgsqlCommand command = new NpgsqlCommand(str, mainConnection);
                command.ExecuteReader();

                int n = dataGridView1.Rows.Add();
                dataGridView1.Rows[n].Cells[0].Value = id;
                dataGridView1.Rows[n].Cells[1].Value = textBox1.Text;
                dataGridView1.Rows[n].Cells[2].Value = textBox2.Text;
                dataGridView1.Rows[n].Cells[3].Value = textBox3.Text;
                dataGridView1.Rows[n].Cells[4].Value = textBox4.Text;
            }
            catch (Exception e) {
                MessageBox.Show("Data is incorrect");
            }

        }

        private void select() {
            try
            {
                NpgsqlConnection connection = new NpgsqlConnection(connectionString);
                connection.Open();
                NpgsqlCommand command = new NpgsqlCommand(selectString, connection);

                NpgsqlDataReader dr = command.ExecuteReader();
                Row row;
                rowList = new List<Row>();
                while (dr.Read())
                {
                    row = new Row(dr[0].ToString(), dr[5].ToString(), dr[4].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString());
                    rowList.Add(row);
                    //dataGridView1.Rows.Add(dr[0], dr[5], dr[4], dr[1], dr[2], Image.FromFile(dr[3].ToString()));
                }
                select(0);
                Console.WriteLine(rowList.Count);
                dr.Close();
                connection.Close();
            }
            catch (Exception exception) {
                MessageBox.Show("Error while loading data");
                //Можно заблокировать все кнопки
                //textBox1.Enabled = false;
            }

        }

        private void select(int i)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                dataGridView1.Rows.Clear();
            }
            Row row;
            for (int p = i*500; p < i*500 + 500; p++) {
                row = rowList.ElementAt(p);
                dataGridView1.Rows.Add(row.id, row.category, row.description, row.name, row.cost, Image.FromFile(row.image));
            }

        }

        private void button6_Click(object sender, EventArgs e)
        {
            select();
        }

        public class Row {
            public Row(string id, string category, string description, string name, string cost, string image) {
                this.id = id;
                this.category = category;
                this.description = description;
                this.name = name;
                this.cost = cost;
                this.image = image;
            }
            public string id; //dr[0]
            public string category; //dr[1]
            public string description; //dr[2]
            public string name; //dr[3]
            public string cost; //dr[4]
            public string image; //dr[5]
        }

        private void button8_Click(object sender, EventArgs e) //next page
        {
            currentPage++;
            select(currentPage);
        }

        private void button7_Click(object sender, EventArgs e) //previous page
        {
            currentPage--;
            select(currentPage);
        }
    }
}