using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using System.Collections;

namespace ProgramaGE
{
    class Clientes
    {
        static string serverName = "localhost";
        static string port = "5432";
        static string userName = "postgres";
        static string password = "adm";
        static string databaseName = "GE";
        NpgsqlConnection conn = null;
        string ConnString = null;

        public Clientes()
        {
            ConnString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                                       serverName, port, userName, password, databaseName);

        }

        public bool InserirCliente(ArrayList p_arr)
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdInserir = String.Format("INSERT INTO CLIENTES (NOME, ENDERECO, CIDADE, EMAIL, TELEFONE) VALUES (@NOME, @ENDERECO, @CIDADE, @EMAIL, @TELEFONE)");
                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdInserir, conn))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("@NOME", p_arr[0]));
                    cmd.Parameters.Add(new NpgsqlParameter("@ENDERECO", p_arr[1]));
                    cmd.Parameters.Add(new NpgsqlParameter("@CIDADE", p_arr[2]));
                    cmd.Parameters.Add(new NpgsqlParameter("@EMAIL", p_arr[3]));
                    cmd.Parameters.Add(new NpgsqlParameter("@TELEFONE", p_arr[4]));

                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
        }

        public int UltimoRegistroCliente()
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdLast = String.Format("SELECT * FROM CLIENTES ORDER BY ID_CLIENTE DESC LIMIT 1;");

                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdLast, conn))
                {
                    return Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
        }

        public NpgsqlDataReader BuscarCliente(ArrayList p_arr)
        {
            using (conn = new NpgsqlConnection(ConnString))
            {
                conn.Open();
                string cmdBuscar = String.Format("SELECT * FROM CLIENTES WHERE id_cliente = @ID");
                using (NpgsqlCommand cmd = new NpgsqlCommand(cmdBuscar, conn))
                {
                    cmd.Parameters.Add(new NpgsqlParameter("@ID", Convert.ToInt32(p_arr[0])));

                    NpgsqlDataReader reader = cmd.ExecuteReader();


                    if (reader.Read())
                    {
                        p_arr[1] = reader[0].ToString(); //txt.Text que vou passar e coluna que vou trazer dao db
                        p_arr[2] = reader[1].ToString();
                        p_arr[3] = reader[2].ToString();
                        p_arr[4] = reader[3].ToString();
                        p_arr[5] = reader[4].ToString();
                        p_arr[6] = reader[5].ToString();

                    }
                    else
                    {
                        conn.Close();
                    }
                }
            }
        }
    }
}

private void btnBuscarCliente_Click(object sender, EventArgs e)
        {
            Clientes buscar = new Clientes();
            ArrayList arr = new ArrayList();

            arr.Add(Convert.ToInt32(txtBuscarCliente.Text));
            arr.Add(txtId.Text);
            arr.Add(txtNomeCliente.Text);
            arr.Add(txtEndereco.Text);
            arr.Add(txtCidade.Text);
            arr.Add(txtEmail.Text);
            arr.Add(txtTelefone.Text);

            buscar.BuscarCliente(arr)
        }