using Microsoft.Data.SqlClient; //custom package
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using System.Windows.Forms;


namespace WMS_project
{
    public partial class invoiceManager : Form
    {
        private string connectionString;
        private SqlConnection connection;
        private DataTable dataTable;
        //------------------------------------------------------------------------------------------------------------------------

        public invoiceManager(string connectionString)
        {
            InitializeComponent();
            this.connectionString = connectionString;
            LoadData();
            textBoxSearch.TextChanged += textBoxSearch_TextChanged; // Bind the event

        }
        //------------------------------------------------------------------------------------------------------------------------

        private void LoadData()
        {
            try
            {
                connection = new SqlConnection(connectionString);
                connection.Open();

                // Fetch data from the 'Invoices' table directly
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Invoice", connection);
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                dataGridView.DataSource = dataTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        //------------------------------------------------------------------------------------------------------------------------

        private void addButton_Click(object sender, EventArgs e)
        {
            string companyName = companyNameBox.Text;
            string customerName = customerNameBox.Text;
            string customerAddress = customerAddressBox.Text;
            string country = countryBox.Text;
            string city = cityBox.Text;
            string state = stateBox.Text;
            string zip = zipBox.Text;
            string zone = zoneBox.Text;
            string phoneOne = phoneOneBox.Text;
            string phoneTwo = phoneTwoBox.Text;
            string email = emailBox.Text;
            string companyNameB = companyNameBoxB.Text;
            string customerNameB = customerNameBoxB.Text;
            string customerAddressB = customerAddressBoxB.Text;
            string countryB = countryBoxB.Text;
            string cityB = cityBoxB.Text;
            string stateB = stateBoxB.Text;
            string zipB = zipBoxB.Text;
            string zoneB = zoneBoxB.Text;
            string phoneOneB = phoneOneBoxB.Text;
            string phoneTwoB = phoneTwoBoxB.Text;
            string orderStatus = orderStatusBox.SelectedItem.ToString();
            string orderType = orderTypeBox.SelectedItem.ToString();
            string site = siteBox.SelectedItem.ToString();
            string warehouse = warehouseBox.SelectedItem.ToString();
            string calcMethod = calcMethodBox.SelectedItem.ToString();

            List<string> selectedServices = new List<string>();
            foreach (var item in servicesCheckedListBox.CheckedItems)
            {
                selectedServices.Add(item.ToString());
            }

            // Combine selected services into a single string separated by commas or any other delimiter
            string selectedServicesString = string.Join(",", selectedServices);

            //------------------------------------------------------------------------------------------------------------------------
            string insertQuery = @"
            INSERT INTO Invoice (
             Company, Customer, CustomerAddress, Country, City, State, Zip, Zone,
             PrimaryPhone, SecondaryPhone, Email, BillingCompany, BillingCustomer, BillingCustomerAddress, BillingCountry,
             BillingCity, BillingState, BillingZip, BillingZone, BillingPrimaryPhone, BillingSecondaryPhone,
             OrderStatus, OrderType, Site, Warehouse, Service, CalculationMethods
             ) VALUES (
             @CompanyName, @CustomerName, @CustomerAddress, @Country, @City, @State, @Zip, @Zone,
             @PhoneOne, @PhoneTwo, @Email, @CompanyNameB, @CustomerNameB, @CustomerAddressB, @CountryB,
             @CityB, @StateB, @ZipB, @ZoneB, @PhoneOneB, @PhoneTwoB,
             @OrderStatus, @OrderType, @Site, @Warehouse, @Service, @CalcMethod
             )";


            try
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(insertQuery, connection);

                // Add parameters to the SQL command
                cmd.Parameters.AddWithValue("@CompanyName", companyName);
                cmd.Parameters.AddWithValue("@CustomerName", customerName);
                cmd.Parameters.AddWithValue("@CustomerAddress", customerAddress);
                cmd.Parameters.AddWithValue("@Country", country);
                cmd.Parameters.AddWithValue("@City", city);
                cmd.Parameters.AddWithValue("@State", state);
                cmd.Parameters.AddWithValue("@Zip", zip);
                cmd.Parameters.AddWithValue("@Zone", zone);
                cmd.Parameters.AddWithValue("@PhoneOne", phoneOne);
                cmd.Parameters.AddWithValue("@PhoneTwo", phoneTwo);
                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters.AddWithValue("@CompanyNameB", companyNameB);
                cmd.Parameters.AddWithValue("@CustomerNameB", customerNameB);
                cmd.Parameters.AddWithValue("@CustomerAddressB", customerAddressB);
                cmd.Parameters.AddWithValue("@CountryB", countryB);
                cmd.Parameters.AddWithValue("@CityB", cityB);
                cmd.Parameters.AddWithValue("@StateB", stateB);
                cmd.Parameters.AddWithValue("@ZipB", zipB);
                cmd.Parameters.AddWithValue("@ZoneB", zoneB);
                cmd.Parameters.AddWithValue("@PhoneOneB", phoneOneB);
                cmd.Parameters.AddWithValue("@PhoneTwoB", phoneTwoB);
                cmd.Parameters.AddWithValue("@OrderStatus", orderStatus);
                cmd.Parameters.AddWithValue("@OrderType", orderType);
                cmd.Parameters.AddWithValue("@Site", site);
                cmd.Parameters.AddWithValue("@Warehouse", warehouse);
                cmd.Parameters.AddWithValue("@CalcMethod", calcMethod);
                cmd.Parameters.AddWithValue("@Service", selectedServicesString);

                int rowsAffected = cmd.ExecuteNonQuery();

                if (rowsAffected > 0)
                {

                    MessageBox.Show("Data inserted successfully.");
                }
                else
                {

                    MessageBox.Show("Data insertion failed.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        //------------------------------------------------------------------------------------------------------------------------
        private void ClearAllFields()
        {
            // Clear text boxes
            companyNameBox.Text = "";
            customerNameBox.Text = "";
            customerAddressBox.Text = "";
            countryBox.Text = "";
            cityBox.Text = "";
            stateBox.Text = "";
            zipBox.Text = "";
            zoneBox.Text = "";
            phoneOneBox.Text = "";
            phoneTwoBox.Text = "";
            emailBox.Text = "";
            companyNameBoxB.Text = "";
            customerNameBoxB.Text = "";
            customerAddressBoxB.Text = "";
            countryBoxB.Text = "";
            cityBoxB.Text = "";
            stateBoxB.Text = "";
            zipBoxB.Text = "";
            zoneBoxB.Text = "";
            phoneOneBoxB.Text = "";
            phoneTwoBoxB.Text = "";

            // Clear combo boxes
            orderStatusBox.SelectedIndex = -1;
            orderTypeBox.SelectedIndex = -1;
            siteBox.SelectedIndex = -1;
            warehouseBox.SelectedIndex = -1;
            calcMethodBox.SelectedIndex = -1;

            // Clear checked list box
            servicesCheckedListBox.Items.Clear();

            // Clear any other components or fields you want to reset
        }
        private void cancelButton_Click(object sender, EventArgs e)
        {
            companyNameBox.Text = string.Empty;
            customerNameBox.Text = string.Empty;
            customerAddressBox.Text = string.Empty;
            countryBox.Text = string.Empty;
            cityBox.Text = string.Empty;
            stateBox.Text = string.Empty;
            zipBox.Text = string.Empty;
            zoneBox.Text = string.Empty;
            phoneOneBox.Text = string.Empty;
            phoneTwoBox.Text = string.Empty;
            emailBox.Text = string.Empty;

            companyNameBoxB.Text = string.Empty;
            customerNameBoxB.Text = string.Empty;
            customerAddressBoxB.Text = string.Empty;
            countryBoxB.Text = string.Empty;
            cityBoxB.Text = string.Empty;
            stateBoxB.Text = string.Empty;
            zipBoxB.Text = string.Empty;
            zoneBoxB.Text = string.Empty;
            phoneOneBoxB.Text = string.Empty;
            phoneTwoBoxB.Text = string.Empty;

            orderStatusBox.SelectedItem = null;
            orderTypeBox.SelectedItem = null;
            siteBox.SelectedItem = null;
            warehouseBox.SelectedItem = null;
            calcMethodBox.SelectedItem = null;

            // Uncheck all items in servicesCheckedListBox
            for (int i = 0; i < servicesCheckedListBox.Items.Count; i++)
            {
                servicesCheckedListBox.SetItemChecked(i, false);
            }
        }
        //------------------------------------------------------------------------------------------------------------------------

        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 ButtonToExitPage_Click(object sender, EventArgs e)
        {
            mainMenu mainmenuForm = new mainMenu(connectionString);
            mainmenuForm.Show();
            this.Hide();
        }
        //------------------------------------------------------------------------------------------------------------------------
        private void textBoxSearch_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (dataTable != null)
                {
                    string searchTerm = textBoxSearch.Text.Trim();
                    if (!string.IsNullOrEmpty(searchTerm))
                    {
                        DataTable filteredTable = dataTable.Clone(); // Create a new table to hold the filtered data

                        foreach (DataRow row in dataTable.Rows)
                        {
                            foreach (DataColumn col in dataTable.Columns)
                            {
                                // Check each cell for the search term
                                if (row[col.ColumnName].ToString().IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0)
                                {
                                    filteredTable.ImportRow(row); // Add the entire row if a match is found
                                    break; // Once a match is found in this row, move to the next row
                                }
                            }
                        }

                        if (filteredTable.Rows.Count > 0)
                        {
                            dataGridView.DataSource = filteredTable; // Display the filtered data
                        }
                        else
                        {
                            dataGridView.DataSource = null; // No match found, clear the DataGridView
                        }
                    }
                    else
                    {
                        dataGridView.DataSource = dataTable; // Display all data when search term is empty
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
        }

        private void toggleSame_CheckedChanged(object sender, EventArgs e)
        {
            if (toggleSame.Checked)
            {

                companyNameBoxB.Text = companyNameBox.Text;
                customerNameBoxB.Text = customerNameBox.Text;
                customerAddressBoxB.Text = customerAddressBox.Text;
                countryBoxB.Text = countryBox.Text;
                cityBoxB.Text = cityBox.Text;
                stateBoxB.Text = stateBox.Text;
                zipBoxB.Text = zipBox.Text;
                zoneBoxB.Text = zoneBox.Text;
                phoneOneBoxB.Text = phoneOneBox.Text;
                phoneTwoBoxB.Text = phoneTwoBox.Text;

            }
            else
            {

            }
        }

        public void GenerateInvoiceButton_Click(object sender, EventArgs e)
        {
            string companyName = companyNameBox.Text;
            string customerName = customerNameBox.Text;
            string customerAddress = customerAddressBox.Text;
            string country = countryBox.Text;
            string city = cityBox.Text;
            string state = stateBox.Text;
            string zip = zipBox.Text;
            string zone = zoneBox.Text;
            string phoneOne = phoneOneBox.Text;
            string phoneTwo = phoneTwoBox.Text;
            string email = emailBox.Text;
            string companyNameB = companyNameBoxB.Text;
            string custNameB = customerNameBoxB.Text;
            string customerAddressB = customerAddressBoxB.Text;
            string countryB = countryBoxB.Text;
            string cityB = cityBoxB.Text;
            string stateB = stateBoxB.Text;
            string zipB = zipBoxB.Text;
            string zoneB = zoneBoxB.Text;
            string phoneOneB = phoneOneBoxB.Text;
            string phoneTwoB = phoneTwoBoxB.Text;

            QuoteInvoiceForms invoicePage = new QuoteInvoiceForms(
                companyName, customerName, customerAddress,
                country, city, state, zip, phoneOne, phoneTwo, email,
                companyNameB, custNameB, customerAddressB,
                countryB, cityB, stateB, zipB, phoneOneB, phoneTwoB);

            invoicePage.Show();


        }

        private void dataGrid_SelectionChanged(object sender, EventArgs e)
        {
            if (dataGridView.SelectedRows.Count > 0)
            {
                DataGridViewRow selectedRow = dataGridView.SelectedRows[0];

                companyNameBox.Text = selectedRow.Cells["Company"].Value.ToString();
                customerNameBox.Text = selectedRow.Cells["Customer"].Value.ToString();
                customerAddressBox.Text = selectedRow.Cells["CustomerAddress"].Value.ToString();
                countryBox.Text = selectedRow.Cells["Country"].Value.ToString();
                cityBox.Text = selectedRow.Cells["City"].Value.ToString();
                stateBox.Text = selectedRow.Cells["State"].Value.ToString();
                zipBox.Text = selectedRow.Cells["Zip"].Value.ToString();
                zoneBox.Text = selectedRow.Cells["Zone"].Value.ToString();
                phoneOneBox.Text = selectedRow.Cells["PrimaryPhone"].Value.ToString();
                phoneTwoBox.Text = selectedRow.Cells["SecondaryPhone"].Value.ToString();
                emailBox.Text = selectedRow.Cells["Email"].Value.ToString();

                companyNameBoxB.Text = selectedRow.Cells["BillingCompany"].Value.ToString();
                customerNameBoxB.Text = selectedRow.Cells["BillingCustomer"].Value.ToString();
                customerAddressBoxB.Text = selectedRow.Cells["BillingCustomerAddress"].Value.ToString();
                countryBoxB.Text = selectedRow.Cells["BillingCountry"].Value.ToString();
                cityBoxB.Text = selectedRow.Cells["BillingCity"].Value.ToString();
                stateBoxB.Text = selectedRow.Cells["BillingState"].Value.ToString();
                zipBoxB.Text = selectedRow.Cells["BillingZip"].Value.ToString();
                zoneBoxB.Text = selectedRow.Cells["BillingZone"].Value.ToString();
                phoneOneBoxB.Text = selectedRow.Cells["BillingPrimaryPhone"].Value.ToString();
                phoneTwoBoxB.Text = selectedRow.Cells["BillingSecondaryPhone"].Value.ToString();

                orderStatusBox.SelectedItem = selectedRow.Cells["OrderStatus"].Value.ToString();
                orderTypeBox.SelectedItem = selectedRow.Cells["OrderType"].Value.ToString();
                siteBox.SelectedItem = selectedRow.Cells["Site"].Value.ToString();
                warehouseBox.SelectedItem = selectedRow.Cells["Warehouse"].Value.ToString();
                calcMethodBox.SelectedItem = selectedRow.Cells["CalculationMethods"].Value.ToString();

                // Populate services from your DataGridView
                servicesCheckedListBox.Items.Clear(); // Clear previous items

                string servicesString = selectedRow.Cells["Service"].Value.ToString();
                string[] services = servicesString.Split(',');

                foreach (string service in services)
                {
                    if (!string.IsNullOrWhiteSpace(service))
                    {
                        servicesCheckedListBox.Items.Add(service.Trim(), true);
                    }
                }
            }
        }

        private void refresh_Click_1(object sender, EventArgs e)
        {
            LoadData(); //re-fetch data
        }
        //------------------------------------------------------------------------------------------------------------------------

        private void helpButton_Click(object sender, EventArgs e)
        {
            string instructions = "After adding all fields and clicking add, please refresh the table on the bottom right." +
                "Then, please click the left most cell to highlight a row and click Generate Invoice to be able to Print the invoice.";

            // Display instructions in a MessageBox
            MessageBox.Show(instructions, "Thank you.", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

    }
}

