using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PhoneBook
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        string connStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;


        public new DataTable Select()
        {
            SqlConnection Conn = new SqlConnection(connStr);
            DataTable dt = new DataTable();
            try
            {

                string sql = "SELECT * FROM MyPhoneBook";
                SqlCommand cmd = new SqlCommand(sql, Conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                Conn.Open();
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
            }
            return dt;
        }



        // Inserting Data into DataBase

        public bool Insert()
        {
            bool Issuccess = false;
            SqlConnection Conn = new SqlConnection(connStr);

            try
            {
                //string picpath = PictureBox.ImageLocation;
           
                string sql = "INSERT INTO MyPhoneBook (FirstName, LastName, ContactNo, Address, Gender,  Email, Picture) VALUES (@FirstName, @LastName, @ContactNo, @Address, @Gender, @Email, @Picture)";
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.Parameters.AddWithValue("@FirstName", FirstName.Text);
                cmd.Parameters.AddWithValue("@LastName", LastName.Text);
                cmd.Parameters.AddWithValue("@Email", Email.Text);
                cmd.Parameters.AddWithValue("@ContactNo", ContactNo.Text);
                cmd.Parameters.AddWithValue("@Address", Address.Text);
                cmd.Parameters.AddWithValue("@Gender", cmbGender.Text);

                if (picpath != "")
                {
                    byte[] img = null;
                    FileStream fs = new FileStream(picpath, FileMode.Open, FileAccess.Read);
                    BinaryReader br = new BinaryReader(fs);
                    img = br.ReadBytes((int)fs.Length);
                    cmd.Parameters.AddWithValue("@Picture", img);
                }
                else
                {
                    byte[] img = null;
                    cmd.Parameters.AddWithValue("@Picture", img);
                }
                
                

                Conn.Open();
                int rows = cmd.ExecuteNonQuery();
                if (rows > 0)
                {
                    Issuccess = true;
                }
                else
                {
                    Issuccess = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("You should upload a picture!");
            }
            finally
            {
                Conn.Close();
            }

            return Issuccess;
        }


        // Updating Data of DataBase
        public bool UpdateTb()
        {
            bool Issuccess = false;
            SqlConnection Conn = new SqlConnection(connStr);

            try
            {
                //string picpath = PictureBox.ImageLocation;
                
                string sql = "UPDATE MyPhoneBook SET FirstName=@FirstName, LastName=@LastName, Email=@Email, ContactNo=@ContactNo, Address=@Address, Gender=@Gender, Picture=@Picture WHERE ContactID=@ContactID";
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.Parameters.AddWithValue("@ContactID", System.Convert.ToInt32(ContactID.Text));

                cmd.Parameters.AddWithValue("@FirstName", FirstName.Text);
                cmd.Parameters.AddWithValue("@LastName", LastName.Text);
                cmd.Parameters.AddWithValue("@Email", Email.Text);
                cmd.Parameters.AddWithValue("@ContactNo", ContactNo.Text);
                cmd.Parameters.AddWithValue("@Address", Address.Text);
                cmd.Parameters.AddWithValue("@Gender", cmbGender.Text);

                if (picpath != "")
                {
                    byte[] img = null;
                    FileStream fs = new FileStream(picpath, FileMode.Open, FileAccess.Read);
                    BinaryReader br = new BinaryReader(fs);
                    img = br.ReadBytes((int)fs.Length);
                    cmd.Parameters.AddWithValue("@Picture", img);

                }
                else
                {
                    

                }


                Conn.Open();
                int rows = cmd.ExecuteNonQuery();
                if (rows > 0)
                {
                    Issuccess = true;
                }
                else
                {
                    Issuccess = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("You must upload a new picture or re-upload the last picture");
            }
            finally
            {
                Conn.Close();
            }
            return Issuccess;
        }


        // Deleting Data from DataBase
        public bool Delete()
        {
            bool Issuccess = false;
            SqlConnection Conn = new SqlConnection(connStr);

            try
            {
                string sql = "DELETE FROM MyPhoneBook WHERE ContactID=@ContactID";
                SqlCommand cmd = new SqlCommand(sql, Conn);
                cmd.Parameters.AddWithValue("@ContactID", System.Convert.ToInt32(ContactID.Text));

                Conn.Open();
                int rows = cmd.ExecuteNonQuery();
                if (rows > 0)
                {
                    Issuccess = true;
                }
                else
                {
                    Issuccess = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
            }
            return Issuccess;
        }



        public void clear()
        {
            FirstName.Text = "";
            LastName.Text = "";
            ContactNo.Text = "";
            Email.Text = "";
            Address.Text = "";
            cmbGender.Text = "";
            ContactID.Text = "";
            PictureBox.ImageLocation = "";
            PictureBox.Image = null;
            picpath = "";
        }





        private void button6_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            bool success = Insert();
            if (success == true)
            {
                MessageBox.Show("The contact has been added to Database successfully");
                clear();
            }
            else
            {
                MessageBox.Show("Failed to add contact to Database");
            }
            DataTable dt = Select();
            dgvContactList.DataSource = dt;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt = Select();
            dgvContactList.DataSource = dt;
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            clear();
        }

    
        private void btnEdit_Click(object sender, EventArgs e)
        {
            bool success = UpdateTb();
            if (success == true)
            {
                MessageBox.Show("Contact has successfully updated.");
                DataTable dt = Select();
                dgvContactList.DataSource = dt;
                clear();

            }
            else
            {
                MessageBox.Show("Updating failed. try again.");
            }
            
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            bool success = Delete();
            if (success == true)
            {
                MessageBox.Show("Contact successfully deleted.");
                DataTable dt = Select();
                dgvContactList.DataSource = dt;
                clear();
            }
            else
            {
                MessageBox.Show("Failed to delete contact. Try again");
            }
        }

        private void Search_TextChanged(object sender, EventArgs e)
        {
            string keyword = Search.Text;
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM MyPhoneBook WHERE FirstName LIKE '%" + keyword + "%' OR LastName LIKE '%" + keyword + "%' OR ContactNo LIKE '%" + keyword + "%' OR Address LIKE '%" + keyword + "%' OR Gender LIKE '%" + keyword + "%' OR Email LIKE '%" + keyword + "%'", conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dgvContactList.DataSource = dt;
        }

        string picpath = "";

        private void button1_Click(object sender, EventArgs e)
        {

            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "All Files (*.*)|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {

                picpath = dlg.FileName.ToString();
                PictureBox.ImageLocation = picpath;
            }
            else
            {
                MessageBox.Show("Image Upload Failed.");
            }

        }

        private void dgvContactList_RowHeaderMouseClick_1(object sender, DataGridViewCellMouseEventArgs e)
        {
            int rowIndex = e.RowIndex;
            ContactID.Text = dgvContactList.Rows[rowIndex].Cells[0].Value.ToString();
            FirstName.Text = dgvContactList.Rows[rowIndex].Cells[1].Value.ToString();
            LastName.Text = dgvContactList.Rows[rowIndex].Cells[2].Value.ToString();
            ContactNo.Text = dgvContactList.Rows[rowIndex].Cells[3].Value.ToString();
            Address.Text = dgvContactList.Rows[rowIndex].Cells[5].Value.ToString();
            cmbGender.Text = dgvContactList.Rows[rowIndex].Cells[6].Value.ToString();
            Email.Text = dgvContactList.Rows[rowIndex].Cells[4].Value.ToString();


            if (dgvContactList.Rows[rowIndex].Cells[7].Value.ToString() == "")
            {
                PictureBox.Image = null;

            }
            else
            {
                byte[] img = (byte[])(dgvContactList.Rows[rowIndex].Cells[7].Value);
                MemoryStream ms = new MemoryStream(img);
                PictureBox.Image = Image.FromStream(ms);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("https://www.instagram.com/abolfazl_m1363/");

        }

        private void button3_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("https://www.linkedin.com/in/abolfazl-mohammadijoo-ab573b6/");

        }

        private void button2_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("https://github.com/mohammadijoo");

        }

        private void button5_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("https://www.facebook.com/abolfazl.mohammadijoo.18");

        }

        private void button10_Click(object sender, EventArgs e)
        {
            MessageBox.Show(picpath);
        }

        private void button8_Click(object sender, EventArgs e)
        {
            Form2 openform = new Form2();
            openform.Show();
        }
    }
}
