using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
using System;


namespace WindowsFormsApp2
{
    public partial class Client : Form
    {
        string connectionString = "Server=85.239.57.125; Database=Gavrilov_Aksenov_exam; Uid=ISP41_Gavrilov; Pwd=ISP41_Gavrilov;";

        public Client()
        {
            InitializeComponent();
            LoadOrders();
            textBoxSearch.TextChanged += TextBoxSearch_TextChanged;
            comboBoxFilter.SelectedIndexChanged += ComboBoxFilter_SelectedIndexChanged;
            comboBoxSort.SelectedIndexChanged += ComboBoxSort_SelectedIndexChanged;
        }

        void LoadOrders()
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT o.order_number as 'Номер', s.name as 'Статус', o.pickup_address as 'Адрес', o.order_date as 'Дата заказа', o.delivery_date as 'Дата доставки' FROM orders o JOIN users u ON o.user_id = u.id JOIN status s ON o.status = s.idstatus";
                MySqlDataAdapter adapter = new MySqlDataAdapter(query, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }

        void TextBoxSearch_TextChanged(object sender, EventArgs e)
        {
            FilterAndSort();
        }

        void ComboBoxFilter_SelectedIndexChanged(object sender, EventArgs e)
        {
            FilterAndSort();
        }

        void ComboBoxSort_SelectedIndexChanged(object sender, EventArgs e)
        {
            FilterAndSort();
        }

        void FilterAndSort()
        {
            string search = textBoxSearch.Text;
            string filter = comboBoxFilter.Text;
            string sort = comboBoxSort.Text;

            string query = "SELECT o.order_number as 'Номер', s.name as 'Статус', o.pickup_address as 'Адрес', o.order_date as 'Дата заказа', o.delivery_date as 'Дата доставки' FROM orders o JOIN users u ON o.user_id = u.id JOIN status s ON o.status = s.idstatus WHERE 1=1";

            if (!string.IsNullOrEmpty(search))
            {
                if (filter == "По номеру")
                {
                    query += " AND o.order_number LIKE '%" + search + "%'";
                }
                else if (filter == "По статусу")
                {
                    query += " AND s.name LIKE '%" + search + "%'";
                }
                else if (filter == "По дате")
                {
                    query += " AND (o.order_date LIKE '%" + search + "%' OR o.delivery_date LIKE '%" + search + "%')";
                }
                else
                {
                    query += " AND (o.order_number LIKE '%" + search + "%' OR s.name LIKE '%" + search + "%' OR o.pickup_address LIKE '%" + search + "%')";
                }
            }

            if (sort == "По дате новые")
            {
                query += " ORDER BY o.order_date DESC";
            }
            else if (sort == "По дате старые")
            {
                query += " ORDER BY o.order_date ASC";
            }
            else if (sort == "По статусу")
            {
                query += " ORDER BY s.name ASC";
            }

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Author form1 = new Author();
            form1.Show();
            this.Hide();
        }
    }
}