using System;
using System.Data;
using System.Windows.Forms;
using Npgsql;

namespace CarApp
{
    public partial class Form1 : Form
    {
        private string connString = "Host=245e1-rw.db.pub.dbaas.postgrespro.ru;Database=dbimport;Username=nikolaeva_sa;Password=$SD$fF7U$z#;SSL Mode=Require;Trust Server Certificate=true;";

        public Form1()
        {
            InitializeComponent();
        }

        private bool IsValidInput(string input)
        {
            if (string.IsNullOrWhiteSpace(input))
                return false;

            string[] forbidden = { "--", ";", "DROP", "DELETE", "INSERT", "UPDATE", "ALTER" };
            foreach (string f in forbidden)
            {
                if (input.ToUpper().Contains(f))
                    return false;
            }
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                using (var cn = new NpgsqlConnection(connString))
                {
                    cn.Open();
                    string sql = "SELECT car_id, brand, model, year, price FROM car";
                    var da = new NpgsqlDataAdapter(sql, cn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
                MessageBox.Show("Данные загружены!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка: " + ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(textBox1.Text) && !IsValidInput(textBox1.Text))
            {
                MessageBox.Show("Некорректный поисковый запрос!", "Ошибка");
                return;
            }
            try
            {
                using (var cn = new NpgsqlConnection(connString))
                {
                    cn.Open();
                    string sql = "SELECT car_id, brand, model, year, price FROM car WHERE brand ILIKE @s";
                    var cmd = new NpgsqlCommand(sql, cn);
                    cmd.Parameters.AddWithValue("@s", "%" + textBox1.Text + "%");
                    var da = new NpgsqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка: " + ex.Message);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "CSV|*.csv";
            sfd.FileName = "cars.csv";

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                using (System.IO.StreamWriter sw = new System.IO.StreamWriter(sfd.FileName))
                {
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                        sw.Write(dataGridView1.Columns[i].HeaderText + (i == dataGridView1.Columns.Count - 1 ? "" : ";"));
                    sw.WriteLine();

                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                        if (row.IsNewRow) continue;
                        for (int i = 0; i < dataGridView1.Columns.Count; i++)
                            sw.Write(row.Cells[i].Value?.ToString() + (i == dataGridView1.Columns.Count - 1 ? "" : ";"));
                        sw.WriteLine();
                    }
                }
                MessageBox.Show("Экспорт завершён!");
            }
        }


        private void button4_Click(object sender, EventArgs e)
        {
            // ДИАЛОГ ДОБАВЛЕНИЯ
            Form addForm = new Form();
            addForm.Text = "Добавить авто";
            addForm.Size = new System.Drawing.Size(320, 280);
            addForm.StartPosition = FormStartPosition.CenterParent;

            TextBox txtReg = new TextBox() { Location = new System.Drawing.Point(120, 20), Size = new System.Drawing.Size(150, 23) };
            TextBox txtBrand = new TextBox() { Location = new System.Drawing.Point(120, 55), Size = new System.Drawing.Size(150, 23) };
            TextBox txtModel = new TextBox() { Location = new System.Drawing.Point(120, 90), Size = new System.Drawing.Size(150, 23) };
            TextBox txtYear = new TextBox() { Location = new System.Drawing.Point(120, 125), Size = new System.Drawing.Size(150, 23) };
            TextBox txtPrice = new TextBox() { Location = new System.Drawing.Point(120, 160), Size = new System.Drawing.Size(150, 23) };

            addForm.Controls.Add(new Label() { Text = "Рег. номер:", Location = new System.Drawing.Point(20, 23), Size = new System.Drawing.Size(80, 23) });
            addForm.Controls.Add(txtReg);
            addForm.Controls.Add(new Label() { Text = "Марка:", Location = new System.Drawing.Point(20, 58), Size = new System.Drawing.Size(80, 23) });
            addForm.Controls.Add(txtBrand);
            addForm.Controls.Add(new Label() { Text = "Модель:", Location = new System.Drawing.Point(20, 93), Size = new System.Drawing.Size(80, 23) });
            addForm.Controls.Add(txtModel);
            addForm.Controls.Add(new Label() { Text = "Год:", Location = new System.Drawing.Point(20, 128), Size = new System.Drawing.Size(80, 23) });
            addForm.Controls.Add(txtYear);
            addForm.Controls.Add(new Label() { Text = "Цена:", Location = new System.Drawing.Point(20, 163), Size = new System.Drawing.Size(80, 23) });
            addForm.Controls.Add(txtPrice);

            Button btnOk = new Button() { Text = "OK", Location = new System.Drawing.Point(70, 200), Size = new System.Drawing.Size(70, 30), DialogResult = DialogResult.OK };
            Button btnCancel = new Button() { Text = "Отмена", Location = new System.Drawing.Point(160, 200), Size = new System.Drawing.Size(70, 30), DialogResult = DialogResult.Cancel };
            addForm.Controls.Add(btnOk);
            addForm.Controls.Add(btnCancel);

            if (addForm.ShowDialog() == DialogResult.OK)
            {
               
                if (!IsValidInput(txtBrand.Text))
                {
                    MessageBox.Show("Некорректный ввод марки!", "Ошибка");
                    return;
                }
                if (!IsValidInput(txtModel.Text))
                {
                    MessageBox.Show("Некорректный ввод модели!", "Ошибка");
                    return;
                }
                if (!IsValidInput(txtReg.Text))
                {
                    MessageBox.Show("Некорректный ввод регистрационного номера!", "Ошибка");
                    return;
                }

                int year;
                if (!int.TryParse(txtYear.Text, out year) || year < 1900 || year > DateTime.Now.Year + 1)
                {
                    MessageBox.Show("Некорректный год!", "Ошибка");
                    return;
                }

                decimal price;
                if (!decimal.TryParse(txtPrice.Text, out price) || price <= 0)
                {
                    MessageBox.Show("Некорректная цена!", "Ошибка");
                    return;
                }
               

                try
                {
                    using (var cn = new NpgsqlConnection(connString))
                    {
                        cn.Open();
                        string sql = "INSERT INTO car (reg_number, brand, model, year, price) VALUES (@r, @b, @m, @y, @p)";
                        var cmd = new NpgsqlCommand(sql, cn);
                        cmd.Parameters.AddWithValue("@r", txtReg.Text);
                        cmd.Parameters.AddWithValue("@b", txtBrand.Text);
                        cmd.Parameters.AddWithValue("@m", txtModel.Text);
                        cmd.Parameters.AddWithValue("@y", year);
                        cmd.Parameters.AddWithValue("@p", price);
                        cmd.ExecuteNonQuery();
                    }
                    MessageBox.Show("Добавлено!");
                    button1_Click(null, null);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка: " + ex.Message);
                }
            }
        }
        // УДАЛИТЬ
        private void button5_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count == 0)
            {
                MessageBox.Show("Выберите строку для удаления");
                return;
            }

            int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["car_id"].Value);
            string name = dataGridView1.SelectedRows[0].Cells["brand"].Value.ToString();

            DialogResult res = MessageBox.Show($"Удалить {name}?", "Подтверждение", MessageBoxButtons.YesNo);

            if (res == DialogResult.Yes)
            {
                try
                {
                    using (var cn = new NpgsqlConnection(connString))
                    {
                        cn.Open();
                        string sql = "DELETE FROM car WHERE car_id=@id";
                        var cmd = new NpgsqlCommand(sql, cn);
                        cmd.Parameters.AddWithValue("@id", id);
                        cmd.ExecuteNonQuery();
                    }
                    MessageBox.Show("Удалено!");
                    button1_Click(null, null);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Ошибка: " + ex.Message);
                }
            }
        }
    }
}