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

namespace CarApp
{
    class Program
    {
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.Run(new MainForm());
        }
    }

    class MainForm : Form
    {
        // строка подключения
        string conn = "Host=edu-pg.itiscaf.ru;Port=5432;Database=dbwork;Username=valko_da;Password=vn$Y$5dr#$6;";

        DataGridView grid = new DataGridView();
        TextBox txtAdres = new TextBox();
        TextBox txtPhone = new TextBox();
        TextBox txtGorod = new TextBox();
        Button btnAdd    = new Button();
        Button btnUpdate = new Button();
        Button btnDelete = new Button();

        // для процедуры
        ComboBox cmbMarka  = new ComboBox();
        DateTimePicker dtpOt  = new DateTimePicker();
        DateTimePicker dtpDo  = new DateTimePicker();
        Button btnProc     = new Button();
        DataGridView gridProc = new DataGridView();

        int selectedId = -1;

        public MainForm()
        {
            Text = "Продажа автомобилей";
            Size = new Size(950, 600);
            StartPosition = FormStartPosition.CenterScreen;

            var tabs = new TabControl { Dock = DockStyle.Fill };

            tabs.TabPages.Add(MakeClientTab());
            tabs.TabPages.Add(MakeProcTab());

            Controls.Add(tabs);

            LoadClients();
            LoadMarki();
        }

        // ВКЛАДКА КЛИЕНТЫ 
        TabPage MakeClientTab()
        {
            var tab = new TabPage("Клиенты");

            // таблица
            grid.Location = new Point(10, 10);
            grid.Size = new Size(550, 500);
            grid.ReadOnly = true;
            grid.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            grid.AllowUserToAddRows = false;
            grid.SelectionChanged += (s, e) =>
            {
                if (grid.CurrentRow == null) return;
                selectedId     = Convert.ToInt32(grid.CurrentRow.Cells[0].Value);
                txtAdres.Text  = grid.CurrentRow.Cells[1].Value?.ToString();
                txtPhone.Text  = grid.CurrentRow.Cells[2].Value?.ToString();
                txtGorod.Text  = grid.CurrentRow.Cells[3].Value?.ToString();
            };

            // поля
            var lAdres = new Label { Text = "Адрес:",   Left = 580, Top = 20,  Width = 80 };
            var lPhone = new Label { Text = "Телефон:", Left = 580, Top = 65,  Width = 80 };
            var lGorod = new Label { Text = "Город:",   Left = 580, Top = 110, Width = 80 };

            txtAdres.Location = new Point(580, 40);  txtAdres.Width = 300;
            txtPhone.Location = new Point(580, 85);  txtPhone.Width = 300;
            txtGorod.Location = new Point(580, 130); txtGorod.Width = 300;

            btnAdd.Text    = "Добавить";  btnAdd.Location    = new Point(580, 180); btnAdd.Width = 140;
            btnUpdate.Text = "Изменить";  btnUpdate.Location = new Point(740, 180); btnUpdate.Width = 140;
            btnDelete.Text = "Удалить";   btnDelete.Location = new Point(580, 220); btnDelete.Width = 300;

            btnAdd.Click    += (s, e) => AddClient();
            btnUpdate.Click += (s, e) => UpdateClient();
            btnDelete.Click += (s, e) => DeleteClient();

            tab.Controls.AddRange(new Control[] {
                grid, lAdres, txtAdres, lPhone, txtPhone, lGorod, txtGorod,
                btnAdd, btnUpdate, btnDelete
            });

            return tab;
        }

        void LoadClients()
        {
            try
            {
                using var c = new NpgsqlConnection(conn);
                c.Open();
                var da = new NpgsqlDataAdapter("SELECT id_klienta, adres, nomer_telefona, gorod FROM klient", c);
                var dt = new DataTable();
                da.Fill(dt);
                grid.DataSource = dt;
                grid.Columns[0].HeaderText = "ID";
                grid.Columns[1].HeaderText = "Адрес";
                grid.Columns[2].HeaderText = "Телефон";
                grid.Columns[3].HeaderText = "Город";
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        void AddClient()
        {
            try
            {
                using var c = new NpgsqlConnection(conn);
                c.Open();
                var cmd = new NpgsqlCommand(
                    "INSERT INTO klient(adres, nomer_telefona, gorod) VALUES(@a,@p,@g)", c);
                cmd.Parameters.AddWithValue("@a", txtAdres.Text);
                cmd.Parameters.AddWithValue("@p", txtPhone.Text);
                cmd.Parameters.AddWithValue("@g", txtGorod.Text);
                cmd.ExecuteNonQuery();
                LoadClients();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        void UpdateClient()
        {
            if (selectedId == -1) { MessageBox.Show("Выберите строку"); return; }
            try
            {
                using var c = new NpgsqlConnection(conn);
                c.Open();
                var cmd = new NpgsqlCommand(
                    "UPDATE klient SET adres=@a, nomer_telefona=@p, gorod=@g WHERE id_klienta=@id", c);
                cmd.Parameters.AddWithValue("@a",  txtAdres.Text);
                cmd.Parameters.AddWithValue("@p",  txtPhone.Text);
                cmd.Parameters.AddWithValue("@g",  txtGorod.Text);
                cmd.Parameters.AddWithValue("@id", selectedId);
                cmd.ExecuteNonQuery();
                LoadClients();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        void DeleteClient()
        {
            if (selectedId == -1) { MessageBox.Show("Выберите строку"); return; }
            if (MessageBox.Show("Удалить?", "", MessageBoxButtons.YesNo) != DialogResult.Yes) return;
            try
            {
                using var c = new NpgsqlConnection(conn);
                c.Open();
                var cmd = new NpgsqlCommand("DELETE FROM klient WHERE id_klienta=@id", c);
                cmd.Parameters.AddWithValue("@id", selectedId);
                cmd.ExecuteNonQuery();
                LoadClients();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        //ВКЛАДКА ПРОЦЕДУРА 
        TabPage MakeProcTab()
        {
            var tab = new TabPage("Хранимая процедура");

            var lMarka = new Label { Text = "Марка:",  Left = 10, Top = 15, Width = 60 };
            cmbMarka.Location = new Point(75, 12);
            cmbMarka.Width = 150;
            cmbMarka.DropDownStyle = ComboBoxStyle.DropDownList;

            var lOt = new Label { Text = "С:",  Left = 240, Top = 15, Width = 20 };
            dtpOt.Location = new Point(263, 12); dtpOt.Width = 130;
            dtpOt.Format = DateTimePickerFormat.Short;
            dtpOt.Value = new DateTime(2023, 1, 1);

            var lDo = new Label { Text = "По:", Left = 405, Top = 15, Width = 25 };
            dtpDo.Location = new Point(433, 12); dtpDo.Width = 130;
            dtpDo.Format = DateTimePickerFormat.Short;
            dtpDo.Value = DateTime.Today;

            btnProc.Text = "Выполнить";
            btnProc.Location = new Point(580, 10);
            btnProc.Width = 120;
            btnProc.Click += (s, e) => RunProc();

            gridProc.Location = new Point(10, 50);
            gridProc.Size = new Size(900, 480);
            gridProc.ReadOnly = true;
            gridProc.AllowUserToAddRows = false;
            gridProc.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

            tab.Controls.AddRange(new Control[] {
                lMarka, cmbMarka, lOt, dtpOt, lDo, dtpDo, btnProc, gridProc
            });

            return tab;
        }

        void LoadMarki()
        {
            try
            {
                using var c = new NpgsqlConnection(conn);
                c.Open();
                var cmd = new NpgsqlCommand("SELECT nazvanie_marki FROM marka ORDER BY nazvanie_marki", c);
                var r = cmd.ExecuteReader();
                while (r.Read()) cmbMarka.Items.Add(r.GetString(0));
                if (cmbMarka.Items.Count > 0) cmbMarka.SelectedIndex = 0;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }

        void RunProc()
        {
            if (cmbMarka.SelectedItem == null) return;

            MessageBox.Show(
     $"Марка: {cmbMarka.SelectedItem}\n" +
     $"Дата от: {dtpOt.Value.Date:d}\n" +
     $"Дата до: {dtpDo.Value.Date:d}"
 );
            try
            {
                using var c = new NpgsqlConnection(conn);
                c.Open();
                var cmd = new NpgsqlCommand(
                    "SELECT * FROM GetCitiesByMarkAndPeriod(@m, @d1, @d2)", c);
                cmd.Parameters.AddWithValue("@m",  cmbMarka.SelectedItem.ToString());
                cmd.Parameters.AddWithValue("@d1", dtpOt.Value.Date);
                cmd.Parameters.AddWithValue("@d2", dtpDo.Value.Date);

                var da = new NpgsqlDataAdapter(cmd);
                var dt = new DataTable();
                da.Fill(dt);
                gridProc.DataSource = dt;
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
    }
}