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;
using System.Windows.Forms.DataVisualization.Charting;

namespace WindowsFormsApp4
{
    public partial class Form1 : Form
    {
        

        private string connectionString = "Host=80.250.189.52;Port=5432;Database=dbcourse;Username=druzhinin_ia;Password=q$J37q6#6f%";
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            NpgsqlConnection connection = null;
            connection = new NpgsqlConnection(connectionString);

            connection.Open();

            string query = "SELECT * FROM \"auto\"";

            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, connection);
            DataTable dataTable = new DataTable();

            adapter.Fill(dataTable);

            dataGridView1.DataSource = dataTable;

        }

        private void button5_Click(object sender, EventArgs e)
        {
            NpgsqlConnection connection = null;
            connection = new NpgsqlConnection(connectionString);

            connection.Open();

            string query = "SELECT * FROM \"sell_auto\"";

            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, connection);
            DataTable dataTable = new DataTable();

            adapter.Fill(dataTable);

            dataGridView1.DataSource = dataTable;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            NpgsqlConnection connection = null;
            connection = new NpgsqlConnection(connectionString);

            connection.Open();

            string query = "SELECT * FROM \"users\"";

            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, connection);
            DataTable dataTable = new DataTable();

            adapter.Fill(dataTable);

            dataGridView1.DataSource = dataTable;
        }

        private void chart1_Click(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            Series series = new Series("Автомобили по брендам");
            series.ChartType = SeriesChartType.Column;
            series.Color = Color.DodgerBlue;
            series.BorderColor = Color.DarkBlue;
            series.BorderWidth = 2;
            series.IsValueShownAsLabel = true;
            series.LabelForeColor = Color.White;
            series.Font = new Font("Arial", 9, FontStyle.Bold);

            using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
            {
                connection.Open();

                string query = @"
                        SELECT 
                            b.name as brand_name, 
                            COUNT(*) as car_count
                        FROM auto a
                        JOIN brand b ON a.brand_id = b.id
                        GROUP BY b.id, b.name
                        ORDER BY car_count DESC";

                using (NpgsqlCommand cmd = new NpgsqlCommand(query, connection))
                using (NpgsqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string brandName = reader.GetString(0);
                        int carCount = reader.GetInt32(1);

                        DataPoint point = new DataPoint();
                        point.SetValueXY(brandName, carCount);
                        point.Label = carCount.ToString();

                        series.Points.Add(point);
                    }
                }
            }

            chart1.Series.Add(series);

            chart1.Titles.Add("Распределение автомобилей по брендам");
            chart1.Titles[0].Font = new Font("Arial", 14, FontStyle.Bold);
            chart1.Titles[0].ForeColor = Color.DarkBlue;

            chart1.Update();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            var row = dataGridView1.Rows[dataGridView1.Rows.Count - 2];

            string sql = $@"
        INSERT INTO auto 
        (
            ID, model_id, year_release, VIN_num, color, 
            retail_price, purchase_price, provider_id
        ) 
        VALUES 
        (
            {row.Cells["brand_id"].Value ?? 1},
            {row.Cells["model_id"].Value ?? 1},
            '{(row.Cells["year_release"].Value ?? DateTime.Today):yyyy-MM-dd}',
            '{row.Cells["VIN_num"].Value ?? "NEWVIN"}',
            '{row.Cells["color"].Value ?? "Черный"}',
            {row.Cells["retail_price"].Value ?? 1000000},
            {row.Cells["purchase_price"].Value ?? 800000},
            {row.Cells["provider_id"].Value ?? 1}
        )";

            using (var conn = new NpgsqlConnection(connectionString))
            {
                conn.Open();
                new NpgsqlCommand(sql, conn).ExecuteNonQuery();
            }

            MessageBox.Show("Добавлено!");
            button1_Click(sender, e);

        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (dataGridView1.CurrentRow == null || dataGridView1.CurrentRow.IsNewRow)
            {
                MessageBox.Show("Выберите автомобиль для удаления!");
                return;
            }

            int autoId = Convert.ToInt32(dataGridView1.CurrentRow.Cells["num_auto"].Value);

            DialogResult result = MessageBox.Show(
                $"Вы действительно хотите удалить автомобиль с ID {autoId}?",
                "Подтверждение удаления",
                MessageBoxButtons.YesNo,
                MessageBoxIcon.Question);

            if (result == DialogResult.Yes)
            {
                string sql = $"DELETE FROM auto WHERE num_auto = {autoId}";

                using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
                {
                    conn.Open();
                    int rowsAffected = new NpgsqlCommand(sql, conn).ExecuteNonQuery();

                    if (rowsAffected > 0)
                    {
                        MessageBox.Show($"Автомобиль с ID {autoId} успешно удален!");

                        button1_Click(sender, e);
                    }
                    else
                    {
                        MessageBox.Show("Автомобиль не найден!");
                    }
                }
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {

        }

        private void button7_Click_1(object sender, EventArgs e)
        {
            var row = dataGridView1.Rows[dataGridView1.Rows.Count - 2];

            string sql = $@"
            INSERT INTO sell_auto 
            (
                staff_id, client_id, date_sell, num_auto, type_pay
            ) 
            VALUES 
            (
                {row.Cells["staff_id"].Value ?? 1},
                {row.Cells["client_id"].Value ?? 1},
                '{(row.Cells["date_sell"].Value ?? DateTime.Today):yyyy-MM-dd}',
                {row.Cells["num_auto"].Value ?? 1},
                '{row.Cells["type_pay"].Value ?? "Наличные"}'
            )";

            using (var conn = new NpgsqlConnection(connectionString))
            {
                conn.Open();
                new NpgsqlCommand(sql, conn).ExecuteNonQuery();
            }

            MessageBox.Show("Продажа добавлена в таблицу sell_auto!");

            button5_Click(sender, e);
        }

        private void button7_Click_2(object sender, EventArgs e)
        {
            if (dataGridView1.CurrentRow == null || dataGridView1.CurrentRow.IsNewRow)
            {
                MessageBox.Show("Выберите автомобиль для изменения!");
                return;
            }

            int autoId = Convert.ToInt32(dataGridView1.CurrentRow.Cells["num_auto"].Value);

            DataGridViewRow row = dataGridView1.CurrentRow;

            string sql = $@"
            UPDATE auto 
            SET 
                brand_id = {row.Cells["brand_id"].Value ?? 1},
                model_id = {row.Cells["model_id"].Value ?? 1},
                year_release = '{(row.Cells["year_release"].Value ?? DateTime.Today):yyyy-MM-dd}',
                VIN_num = '{row.Cells["VIN_num"].Value ?? ""}',
                color = '{row.Cells["color"].Value ?? ""}',
                retail_price = {row.Cells["retail_price"].Value ?? 0},
                purchase_price = {row.Cells["purchase_price"].Value ?? 0},
                provider_id = {row.Cells["provider_id"].Value ?? 1},
                WHERE num_auto = {autoId}";

            using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
            {
                conn.Open();
                int rowsAffected = new NpgsqlCommand(sql, conn).ExecuteNonQuery();

                if (rowsAffected > 0)
                {
                    MessageBox.Show($"Данные автомобиля с ID {autoId} обновлены!");

                    button1_Click(sender, e);
                }
                else
                {
                    MessageBox.Show("Автомобиль не найден!");
                }
            }
        }
    }
}