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;
using Microsoft.Data.SqlClient;


namespace WMS_project
{
    public partial class infoManager : Form
    {
        private string connectionString;
        private SqlConnection connection;
        private DataTable dataTable;
        public infoManager(string connectionString)
        {
            InitializeComponent();
            z.SelectedIndexChanged += categoryBox_SelectedIndexChanged; // Subscribe to the event
            this.connectionString = connectionString;
            LoadData();
        }

        private void LoadData()
        {
            try
            {

                connection = new SqlConnection(connectionString);
                connection.Open();
                string selectedTable = z.SelectedItem?.ToString();
                if (!string.IsNullOrEmpty(selectedTable))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + selectedTable, connection);
                    dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    dataGridView.DataSource = dataTable;
                }
                else
                {

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        private void categoryBox_SelectedIndexChanged(object sender, EventArgs e) //category box
        {
            {
                try
                {
                    LoadData();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message);
                }
            }
        }

        private void saveButton_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataTable != null)
                {
                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM " + z.SelectedItem.ToString(), connection);
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                    adapter.Update(dataTable);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private void deleteButton_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView.SelectedRows.Count > 0)
                {
                    connection.Open();
                    string selectedTable = z.SelectedItem?.ToString();

                    // Retrieve schema information including primary key column(s)
                    SqlCommand schemaCmd = new SqlCommand($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TableName", connection);
                    schemaCmd.Parameters.AddWithValue("@TableName", selectedTable);

                    List<string> primaryKeys = new List<string>();
                    using (SqlDataReader reader = schemaCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            primaryKeys.Add(reader["COLUMN_NAME"].ToString());
                        }
                    }

                    foreach (DataGridViewRow row in dataGridView.SelectedRows)
                    {
                        if (!row.IsNewRow)
                        {
                            // Build the WHERE clause based on primary key column(s)
                            StringBuilder whereClause = new StringBuilder();
                            foreach (string key in primaryKeys)
                            {
                                if (whereClause.Length > 0)
                                    whereClause.Append(" AND ");
                                whereClause.Append($"[{key}] = @{key}");
                            }

                            // Create a DELETE query and execute it for each selected row
                            SqlCommand cmd = new SqlCommand($"DELETE FROM {selectedTable} WHERE {whereClause}", connection);

                            // Set parameters for each primary key column
                            foreach (string key in primaryKeys)
                            {
                                cmd.Parameters.AddWithValue($"@{key}", row.Cells[key].Value);
                            }

                            cmd.ExecuteNonQuery();

                            // Remove the row from the DataGridView
                            dataGridView.Rows.Remove(row);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error: {ex.Message}");
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }

        private void dataGridView_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0 && e.ColumnIndex >= 0)
            {
                // Enable editing when a cell is double-clicked
                dataGridView.BeginEdit(true);
            }
        }

        private void buttonToLogOut_Click(object sender, EventArgs e)
        {
            mainMenu mainmenuForm = new mainMenu(connectionString);
            mainmenuForm.Show();
            this.Hide();
        }

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