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

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private string connStr;
        private string currentUser;

        public Form1(string login, string password)
        {
            InitializeComponent();

            currentUser = login;

            connStr =
                $"Host=80.250.189.52;" +
                $"Port=5432;" +
                $"Database=dbwork;" +
                $"Username={login};" +
                $"Password={password}";
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cmbTables.Items.Clear();

            cmbTables.Items.Add("position_ref");
            cmbTables.Items.Add("employee");
            cmbTables.Items.Add("passenger");
            cmbTables.Items.Add("airline");
            cmbTables.Items.Add("airplane");
            cmbTables.Items.Add("flight");
            cmbTables.Items.Add("ticket");
            cmbTables.Items.Add("baggage");
            cmbTables.Items.Add("passenger_baggage");

            cmbTables.SelectedIndex = 0;

            // просмотрщик может только смотреть
            if (currentUser == "viewer_user")
            {
                btnAdd.Enabled = false;
                btnEdit.Enabled = false;
                btnDelete.Enabled = false;
            }
        }

        private void LoadData()
        {
            try
            {
                using (NpgsqlConnection conn =
                    new NpgsqlConnection(connStr))
                {
                    conn.Open();

                    string tableName = cmbTables.Text;

                    string sql;

                    if (tableName == "employee")
                    {
                        sql = "select * from simonova_da.employee_info";
                    }
                    else if (tableName == "ticket")
                    {
                        sql = "select * from simonova_da.ticket_info";
                    }
                    else
                    {
                        sql = $"select * from simonova_da.{tableName}";
                    } 

                    NpgsqlDataAdapter da =
                        new NpgsqlDataAdapter(sql, conn);

                    DataTable dt =
                        new DataTable();

                    da.Fill(dt);

                    dataGridView1.DataSource = dt;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void cmbTables_SelectedIndexChanged(
            object sender,
            EventArgs e)
        {
            LoadData();
        }

        private void btnSearch_Click(
            object sender,
            EventArgs e)
        {
            try
            {
                using (NpgsqlConnection conn =
                    new NpgsqlConnection(connStr))
                {
                    conn.Open();

                    string sql =
                    @"select *
                      from simonova_da.passenger
                      where lower(last_name)
                      like lower(@search)";

                    NpgsqlDataAdapter da =
                        new NpgsqlDataAdapter(sql, conn);

                    da.SelectCommand.Parameters.AddWithValue(
                        "search",
                        "%" + txtSearch.Text + "%");

                    DataTable dt =
                        new DataTable();

                    da.Fill(dt);

                    dataGridView1.DataSource = dt;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnAdd_Click(
            object sender,
            EventArgs e)
        {
            try
            {
                using (NpgsqlConnection conn =
                    new NpgsqlConnection(connStr))
                {
                    conn.Open();

                    string sql =
                    @"insert into simonova_da.passenger
                    (
                        passenger_code,
                        last_name,
                        first_name,
                        phone_number
                    )
                    values
                    (
                        @code,
                        @last,
                        @first,
                        @phone
                    )";

                    NpgsqlCommand cmd =
                        new NpgsqlCommand(sql, conn);

                    cmd.Parameters.AddWithValue(
                        "code",
                        txtCode.Text);

                    cmd.Parameters.AddWithValue(
                        "last",
                        txtLastName.Text);

                    cmd.Parameters.AddWithValue(
                        "first",
                        txtFirstName.Text);

                    cmd.Parameters.AddWithValue(
                        "phone",
                        txtPhone.Text);

                    cmd.ExecuteNonQuery();

                    MessageBox.Show(
                        "Пассажир добавлен");
                }

                LoadData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnEdit_Click(
            object sender,
            EventArgs e)
        {
            try
            {
                using (NpgsqlConnection conn =
                    new NpgsqlConnection(connStr))
                {
                    conn.Open();

                    string sql =
                    @"update simonova_da.passenger
                      set last_name=@last,
                          first_name=@first,
                          phone_number=@phone
                      where passenger_code=@code";

                    NpgsqlCommand cmd =
                        new NpgsqlCommand(sql, conn);

                    cmd.Parameters.AddWithValue(
                        "code",
                        txtCode.Text);

                    cmd.Parameters.AddWithValue(
                        "last",
                        txtLastName.Text);

                    cmd.Parameters.AddWithValue(
                        "first",
                        txtFirstName.Text);

                    cmd.Parameters.AddWithValue(
                        "phone",
                        txtPhone.Text);

                    cmd.ExecuteNonQuery();

                    MessageBox.Show(
                        "Запись изменена");
                }

                LoadData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnDelete_Click(
            object sender,
            EventArgs e)
        {
            try
            {
                using (NpgsqlConnection conn =
                    new NpgsqlConnection(connStr))
                {
                    conn.Open();

                    string sql =
                    @"delete from simonova_da.passenger
                      where passenger_code=@code";

                    NpgsqlCommand cmd =
                        new NpgsqlCommand(sql, conn);

                    cmd.Parameters.AddWithValue(
                        "code",
                        txtCode.Text);

                    cmd.ExecuteNonQuery();

                    MessageBox.Show(
                        "Запись удалена");
                }

                LoadData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnExport_Click(
            object sender,
            EventArgs e)
        {
            try
            {
                SaveFileDialog save =
                    new SaveFileDialog();

                save.Filter =
                    "CSV files (*.csv)|*.csv";

                if (save.ShowDialog() ==
                    DialogResult.OK)
                {
                    using (StreamWriter sw =
                        new StreamWriter(
                            save.FileName))
                    {
                        for (int i = 0;
                             i < dataGridView1.Columns.Count;
                             i++)
                        {
                            sw.Write(
                                dataGridView1.Columns[i]
                                .HeaderText);

                            if (i <
                                dataGridView1.Columns.Count - 1)
                                sw.Write(";");
                        }

                        sw.WriteLine();

                        foreach (DataGridViewRow row
                                 in dataGridView1.Rows)
                        {
                            if (!row.IsNewRow)
                            {
                                for (int i = 0;
                                     i < dataGridView1.Columns.Count;
                                     i++)
                                {
                                    sw.Write(
                                        row.Cells[i].Value);

                                    if (i <
                                        dataGridView1.Columns.Count - 1)
                                        sw.Write(";");
                                }

                                sw.WriteLine();
                            }
                        }
                    }

                    MessageBox.Show(
                        "Экспорт выполнен");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView1_CellClick(
            object sender,
            DataGridViewCellEventArgs e)
        {
            if (cmbTables.Text != "passenger")
                return;

            if (e.RowIndex >= 0)
            {
                DataGridViewRow row =
                    dataGridView1.Rows[e.RowIndex];

                txtCode.Text =
                    row.Cells["passenger_code"]
                    .Value.ToString();

                txtLastName.Text =
                    row.Cells["last_name"]
                    .Value.ToString();

                txtFirstName.Text =
                    row.Cells["first_name"]
                    .Value.ToString();

                txtPhone.Text =
                    row.Cells["phone_number"]
                    .Value.ToString();
            }
        }

        private void txtFirstName_TextChanged(object sender, EventArgs e)
        {
        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {
        }

        private void btnExport_Click_1(object sender, EventArgs e)
        {
            btnExport_Click(sender, e);
        }
    }
}