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

namespace PayrollSystem
{
    public partial class form_employees : Form
    {
        public form_employees(string departmentName = "")
        {
            InitializeComponent();
            combo_date_type.SelectedIndex = 0;
            combo_department.Items.Clear();
            combo_department.Items.Add("Any");
            using (SqlConnection connection = new SqlConnection(Helpers.conString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT Name FROM Departments", connection))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            combo_department.Items.Add(reader["Name"].ToString());
                        }
                    }
                }
                connection.Close();
            }

            if (departmentName != "")
            {
                for (int i = 0; i < combo_department.Items.Count; i++)
                {
                    if (departmentName == combo_department.Items[i].ToString())
                    {
                        combo_department.SelectedIndex = i;
                        break;
                    }
                }
            }
            else
            {
                combo_department.SelectedIndex = 0;
            }
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {

        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            joined_date_picker.Visible = !combo_date_type.SelectedItem.ToString().Contains("Anytime");
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            txtID.Clear();
            txtName.Clear();
            combo_date_type.SelectedItem = "Anytime";
            combo_department.SelectedItem = "Any";

            Search();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            Search();
        }
        private void form_employees_Load(object sender, EventArgs e)
        {
            Search();
        }

        void Search()
        {
            using(SqlConnection con = new SqlConnection(Helpers.conString))
            {
                con.Open();
                //Prepare the WHERE Clauses
                List<string> clauses = new List<string>();
                if(txtID.Text.Length > 0) { clauses.Add($"Employees.ID={txtID.Text}"); }
                if (txtName.Text.Length > 0) { clauses.Add($"Employees.Name LIKE '%{txtName.Text}%'"); }

                if(combo_date_type.SelectedItem.ToString() != "Anytime") {
                    string condition = "=";
                    if (combo_date_type.SelectedItem.ToString() == "After")
                    {
                        condition = ">";
                    }else if(combo_date_type.SelectedItem.ToString() == "Before")
                    {
                        condition = "<";
                    }
                    clauses.Add($"Employees.Joined_Date{condition}'{joined_date_picker.Value}'"); 
                }
                if(combo_department.SelectedItem.ToString() != "Any")
                {
                    clauses.Add($"Departments.Name='{combo_department.SelectedItem.ToString()}'");
                }
                
                //Calculate the final WHERE statement
                string querySuffix = clauses.Count >0 ? $" WHERE {clauses[0]} " : "";
                for(int i=1; i < clauses.Count; i++)
                {
                    querySuffix += " AND "+clauses[i];
                }

                string baseQuery = "SELECT Employees.ID, Employees.Name, Departments.Name as DepartmentName, Departments.Basic, Employees.Allowance, Employees.Joined_Date FROM Employees JOIN Departments ON Employees.Department_ID=Departments.ID";
               // MessageBox.Show(baseQuery + querySuffix);
                using (SqlCommand cmd = new SqlCommand(baseQuery + querySuffix, con))
                {
                    
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        list_employees.Items.Clear();
                        while (reader.Read())
                        {
                            ListViewItem newItem = new ListViewItem(reader["ID"].ToString());
                            newItem.SubItems.Add(reader["Name"].ToString());
                            newItem.SubItems.Add(reader["DepartmentName"].ToString());
                            newItem.SubItems.Add(reader["Basic"].ToString());
                            newItem.SubItems.Add(reader["Allowance"].ToString());
                            newItem.SubItems.Add(reader["Joined_Date"].ToString());
                            list_employees.Items.Add(newItem);
                        }
                    }
                }

                con.Close();
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            form_modify_employee modifyForm = new form_modify_employee();
            modifyForm.ShowDialog();

            Search();
        }

        private void list_employees_SelectedIndexChanged(object sender, EventArgs e)
        {
            RefreshUI();
        }

        void RefreshUI()
        {
            btnEdit.Enabled = list_employees.SelectedItems.Count > 0;
            btnRemove.Enabled = list_employees.SelectedItems.Count > 0;
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            RefreshUI();
            if (!btnEdit.Enabled) { return; }

            form_modify_employee modifyForm = new form_modify_employee(int.Parse(list_employees.SelectedItems[0].Text));
            modifyForm.ShowDialog();

            Search();
        }

        private void btnRemove_Click(object sender, EventArgs e)
        {
            RefreshUI();
            if (!btnRemove.Enabled) { return; }

            DialogResult confirmation = MessageBox.Show($"Are you sure to remove this Employee?\n\n{list_employees.SelectedItems[0].SubItems[1].Text}", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (confirmation == DialogResult.Yes)
            {
                using (SqlConnection conn = new SqlConnection(Helpers.conString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand("DELETE FROM Employees WHERE ID=@id", conn))
                    {
                        cmd.Parameters.AddWithValue("@id", list_employees.SelectedItems[0].Text);

                        int result = cmd.ExecuteNonQuery();

                        if (result < 0)
                        {
                            MessageBox.Show($"There was an error removing employee", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                    conn.Close();
                }
            }

            Search();
        }
    }
}
