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

namespace demo26052026
{
    public partial class Form1 : Form
    {
        static string ConnString = "Server=localhost;Port=5432;Database=newdemo262;Username=postgres;Password=Swa2sWA13;";
        public Form1()
        {
            InitializeComponent();
            NpgsqlConnection npgsqlConnection = new NpgsqlConnection(ConnString);
            npgsqlConnection.Open();
            NpgsqlCommand npgsqlCommand = new NpgsqlCommand(@$"SELECT ""Articul"", (SELECT ""Imia"" FROM public.""Naimenovanie"" WHERE ""ID_Naim"" = ""Naim_ID""), ""Ed_izm"", ""Cena"", (SELECT ""Imia"" FROM public.""Postavchik"" WHERE ""ID_Postavchik"" = ""Postav_ID""), (SELECT ""Imia"" FROM public.""Proizvoditel"" WHERE ""ID_Proizvoditel"" = ""Proizvod_ID"") ,  (SELECT ""Imia"" FROM public.""Kategory"" WHERE ""ID_Kategory"" = ""Kategory_ID""), ""Skidka"", ""Kolichestvo"", ""Opisanie"", ""Photo"", ""ID_Tovar"" FROM public.""Tovar"";", npgsqlConnection);
            DataTable dataTable = new DataTable();
            dataTable.Load(npgsqlCommand.ExecuteReader());
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                UserControl1 us = new UserControl1(this);
                us.Name = dataTable.Rows[i].ItemArray[11].ToString();
                us.textBox1.Text = dataTable.Rows[i].ItemArray[1].ToString() + " | " + dataTable.Rows[i].ItemArray[6].ToString();
                us.textBox2.Text = dataTable.Rows[i].ItemArray[9].ToString();
                us.textBox3.Text = dataTable.Rows[i].ItemArray[5].ToString();
                us.textBox4.Text = dataTable.Rows[i].ItemArray[4].ToString();
                us.textBox5.Text = dataTable.Rows[i].ItemArray[3].ToString();
                us.textBox6.Text = dataTable.Rows[i].ItemArray[2].ToString();
                us.textBox7.Text = dataTable.Rows[i].ItemArray[8].ToString();
                us.textBox8.Text = dataTable.Rows[i].ItemArray[7].ToString();

                if(dataTable.Rows[i].ItemArray[10].ToString() != "")
                {
                    us.pictureBox1.Image = Image.FromFile(dataTable.Rows[i].ItemArray[10].ToString());
                }
                else
                {
                    us.pictureBox1.Image = Image.FromFile("picture.png");
                }
                flowLayoutPanel1.Controls.Add(us);
            }
            npgsqlConnection.Close();

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            SearchAll();
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            SearchAll();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            SearchAll();
        }

        public void SearchAll()
        {
            flowLayoutPanel1.Controls.Clear();
            NpgsqlConnection npgsqlConnection = new NpgsqlConnection(ConnString);
            npgsqlConnection.Open();

            string dob = "";
            if(comboBox1.SelectedIndex == 0)
            {
                dob = $@"ORDER BY ""Kolichestvo"" ASC";
            }
            else if(comboBox1.SelectedIndex == 1)
            {
                dob = $@"ORDER BY ""Kolichestvo"" DESC";
            }


            string dob2 = "";

            if (comboBox2.SelectedIndex == 1)
            {
                dob2 = $@" AND ""Postav_ID"" = 1 ";
            }
            else if (comboBox2.SelectedIndex == 2)
            {
                dob2 = $@" AND ""Postav_ID"" = 2 ";
            }
            

            NpgsqlCommand npgsqlCommand = new NpgsqlCommand(@$"SELECT ""Articul"", (SELECT ""Imia"" FROM public.""Naimenovanie"" WHERE ""ID_Naim"" = ""Naim_ID""), ""Ed_izm"", ""Cena"", (SELECT ""Imia"" FROM public.""Postavchik"" WHERE ""ID_Postavchik"" = ""Postav_ID""), (SELECT ""Imia"" FROM public.""Proizvoditel"" WHERE ""ID_Proizvoditel"" = ""Proizvod_ID"") ,  (SELECT ""Imia"" FROM public.""Kategory"" WHERE ""ID_Kategory"" = ""Kategory_ID""), ""Skidka"", ""Kolichestvo"", ""Opisanie"", ""Photo"", ""ID_Tovar"" FROM public.""Tovar"" WHERE (SELECT ""Imia"" FROM public.""Naimenovanie"" WHERE ""ID_Naim"" = ""Naim_ID"") || ""Ed_izm"" || ""Cena"" || (SELECT ""Imia"" FROM public.""Postavchik"" WHERE ""ID_Postavchik"" = ""Postav_ID"") || (SELECT ""Imia"" FROM public.""Proizvoditel"" WHERE ""ID_Proizvoditel"" = ""Proizvod_ID"") ||  (SELECT ""Imia"" FROM public.""Kategory"" WHERE ""ID_Kategory"" = ""Kategory_ID"") || ""Skidka"" || ""Kolichestvo"" || ""Opisanie"" || ""Photo"" ILIKE '%{textBox1.Text}%'" + dob2 + dob, npgsqlConnection);
            
            DataTable dataTable = new DataTable();
            dataTable.Load(npgsqlCommand.ExecuteReader());
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                UserControl1 us = new UserControl1(this);
                us.Name = dataTable.Rows[i].ItemArray[11].ToString();
                us.textBox1.Text = dataTable.Rows[i].ItemArray[1].ToString() + " | " + dataTable.Rows[i].ItemArray[6].ToString();
                us.textBox2.Text = dataTable.Rows[i].ItemArray[9].ToString();
                us.textBox3.Text = dataTable.Rows[i].ItemArray[5].ToString();
                us.textBox4.Text = dataTable.Rows[i].ItemArray[4].ToString();
                us.textBox5.Text = dataTable.Rows[i].ItemArray[3].ToString();
                us.textBox6.Text = dataTable.Rows[i].ItemArray[2].ToString();
                us.textBox7.Text = dataTable.Rows[i].ItemArray[8].ToString();
                us.textBox8.Text = dataTable.Rows[i].ItemArray[7].ToString();

                if (dataTable.Rows[i].ItemArray[10].ToString() != "")
                {
                    us.pictureBox1.Image = Image.FromFile(dataTable.Rows[i].ItemArray[10].ToString());
                }
                else
                {
                    us.pictureBox1.Image = Image.FromFile("picture.png");
                }
                flowLayoutPanel1.Controls.Add(us);
            }
            npgsqlConnection.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Hide();
            Form2 fr = new Form2(0);
            fr.ShowDialog();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Hide();
            Form3 form3 = new Form3();
            form3.ShowDialog();
        }
    }
}