ServiceListingForm.cs

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 DB_Project
{
    public partial class ServiceListingForm : Form
    {
        private readonly string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";
        private int currentUserId; // ID of the current service provider
        private DataTable servicesTable; // Cache of services for filtering and sorting

        public ServiceListingForm(int userId = 0)
        {
            InitializeComponent();

            // If userId is provided, use it; otherwise try to get one from the database
            currentUserId = userId > 0 ? userId : GetCurrentServiceProviderId();

            // Setup event handlers
            this.Load += ServiceListingForm_Load;
            dgvServices.CellFormatting += DgvServices_CellFormatting;
        }

        private void ServiceListingForm_Load(object sender, EventArgs e)
        {
            SetupForm();
            LoadServices();
        }

        private void DgvServices_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            // Format the Status column - highlight unavailable services
            if (dgvServices.Columns[e.ColumnIndex].Name == "Status" && e.Value != null)
            {
                if (e.Value.ToString().ToLower() == "unavailable")
                {
                    e.CellStyle.ForeColor = Color.White;
                    e.CellStyle.BackColor = Color.FromArgb(192, 57, 43);
                    e.CellStyle.SelectionForeColor = Color.White;
                    e.CellStyle.SelectionBackColor = Color.FromArgb(231, 76, 60);
                }
                else
                {
                    e.CellStyle.ForeColor = Color.White;
                    e.CellStyle.BackColor = Color.FromArgb(39, 174, 96);
                    e.CellStyle.SelectionForeColor = Color.White;
                    e.CellStyle.SelectionBackColor = Color.FromArgb(46, 204, 113);
                }
            }
        }

        private int GetCurrentServiceProviderId()
        {
            // This is a placeholder. In a real application, this would come from your authentication system
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    // Try to get the first service provider from the database
                    string query = "SELECT TOP 1 user_id FROM ServiceProviderProfile";
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        var result = command.ExecuteScalar();
                        if (result != null)
                        {
                            return Convert.ToInt32(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error getting service provider ID: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            // Default value if we can't get a real ID
            return 1;
        }

        private void SetupForm()
        {
            // Set active button appearance
            HighlightActiveButton(button1); // Highlight the Service Listing button

            // Set up the DataGridView
            dgvServices.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvServices.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dgvServices.AllowUserToAddRows = false;
            dgvServices.AllowUserToDeleteRows = false;
            dgvServices.ReadOnly = true;
            dgvServices.RowHeadersVisible = false;
            dgvServices.MultiSelect = false;

            // Add a right-click context menu
            ContextMenuStrip contextMenu = new ContextMenuStrip();
            contextMenu.Items.Add("Edit Service", null, (s, e) => btnEdit_Click(s, e));
            contextMenu.Items.Add("Delete Service", null, (s, e) => btnDelete_Click(s, e));
            contextMenu.Items.Add(new ToolStripSeparator());
            contextMenu.Items.Add("Refresh List", null, (s, e) => LoadServices());
            dgvServices.ContextMenuStrip = contextMenu;

            // Enable/disable buttons based on selection
            dgvServices.SelectionChanged += (s, e) => {
                bool hasSelection = dgvServices.SelectedRows.Count > 0;
                btnEdit.Enabled = hasSelection;
                btnDelete.Enabled = hasSelection;
            };
        }

        // Helper method to highlight the active sidebar button
        private void HighlightActiveButton(Button activeButton)
        {
            // Reset all buttons
            foreach (Control c in panelSidebar.Controls)
            {
                if (c is Button btn)
                {
                    btn.BackColor = Color.FromArgb(52, 73, 94);
                    btn.ForeColor = Color.White;
                }
            }

            // Highlight active button
            activeButton.BackColor = Color.FromArgb(41, 128, 185);
            activeButton.ForeColor = Color.White;
        }

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

                    // Get provider name for the title
                    string providerQuery = "SELECT provider_name, type FROM ServiceProviderProfile WHERE user_id = @ProviderId";
                    using (SqlCommand providerCmd = new SqlCommand(providerQuery, connection))
                    {
                        providerCmd.Parameters.AddWithValue("@ProviderId", currentUserId);
                        using (SqlDataReader reader = providerCmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                string providerName = reader["provider_name"].ToString();
                                string providerType = reader["type"].ToString();
                                lblTitle.Text = $"Services - {providerName} ({providerType})";
                            }
                        }
                    }

                    // Query to get services for the current service provider
                    string query = @"SELECT s.service_id AS Id, 
                                           s.name AS Name, 
                                           s.type AS Type, 
                                           s.price_per_unit AS PricePerUnit, 
                                           s.total_units AS TotalUnits,
                                           s.location AS Location,
                                           s.description AS Description,
                                           s.status AS Status
                                    FROM Service s
                                    WHERE s.provider_id = @ProviderId
                                    ORDER BY s.name ASC";

                    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                    adapter.SelectCommand.Parameters.AddWithValue("@ProviderId", currentUserId);

                    servicesTable = new DataTable();
                    adapter.Fill(servicesTable);

                    dgvServices.DataSource = servicesTable;

                    // Format the DataGridView
                    if (dgvServices.Columns["Id"] != null)
                        dgvServices.Columns["Id"].Visible = false;

                    if (dgvServices.Columns["PricePerUnit"] != null)
                    {
                        dgvServices.Columns["PricePerUnit"].HeaderText = "Price";
                        dgvServices.Columns["PricePerUnit"].DefaultCellStyle.Format = "C";
                    }

                    if (dgvServices.Columns["TotalUnits"] != null)
                        dgvServices.Columns["TotalUnits"].HeaderText = "Available Units";

                    if (dgvServices.Columns["Description"] != null)
                        dgvServices.Columns["Description"].Visible = false;

                    // Update form title with count
                    if (servicesTable.Rows.Count > 0)
                        lblTitle.Text += $" - {servicesTable.Rows.Count} service(s)";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error loading services: {ex.Message}", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                // Fall back to dummy data if database connection fails
                LoadDummyData();
            }

            // Enable/disable buttons based on whether there are services
            bool hasServices = dgvServices.Rows.Count > 0;
            btnEdit.Enabled = hasServices && dgvServices.SelectedRows.Count > 0;
            btnDelete.Enabled = hasServices && dgvServices.SelectedRows.Count > 0;
        }

        private void LoadDummyData()
        {
            // Original dummy data as fallback
            var dummyData = new List<Service>
            {
                new Service { Id = 1, Name = "City Tour", Type = "Tour", PricePerUnit = 50, TotalUnits = 20, Location = "City Center", Status = "available" },
                new Service { Id = 2, Name = "Deluxe Room", Type = "Hotel", PricePerUnit = 150, TotalUnits = 10, Location = "Downtown", Status = "available" },
                new Service { Id = 3, Name = "Airport Transfer", Type = "Transportation", PricePerUnit = 30, TotalUnits = 15, Location = "Airport", Status = "available" }
            };

            servicesTable = new DataTable();
            servicesTable.Columns.Add("Id", typeof(int));
            servicesTable.Columns.Add("Name", typeof(string));
            servicesTable.Columns.Add("Type", typeof(string));
            servicesTable.Columns.Add("PricePerUnit", typeof(decimal));
            servicesTable.Columns.Add("TotalUnits", typeof(int));
            servicesTable.Columns.Add("Location", typeof(string));
            servicesTable.Columns.Add("Status", typeof(string));

            foreach (var service in dummyData)
            {
                servicesTable.Rows.Add(
                    service.Id,
                    service.Name,
                    service.Type,
                    service.PricePerUnit,
                    service.TotalUnits,
                    service.Location,
                    service.Status
                );
            }

            dgvServices.DataSource = servicesTable;
            dgvServices.Columns["Id"].Visible = false;
            dgvServices.Columns["PricePerUnit"].DefaultCellStyle.Format = "C";
            dgvServices.Columns["TotalUnits"].HeaderText = "Available Units";
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            if (dgvServices.SelectedRows.Count == 0) return;

            try
            {
                // Get the ID of the selected service
                int serviceId = Convert.ToInt32(dgvServices.SelectedRows[0].Cells["Id"].Value);

                // Fetch the full service details from the database
                Service serviceToEdit = GetServiceById(serviceId);

                if (serviceToEdit == null)
                {
                    MessageBox.Show("Could not find the selected service.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                // Create a form for editing the service
                using (Form editForm = CreateServiceEditForm(serviceToEdit))
                {
                    if (editForm.ShowDialog() == DialogResult.OK)
                    {
                        // Refresh the services list
                        LoadServices();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error editing service: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private Service GetServiceById(int serviceId)
        {
            Service service = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = @"SELECT service_id, name, type, price_per_unit, total_units, location, description, status 
                                     FROM Service 
                                     WHERE service_id = @ServiceId AND provider_id = @ProviderId";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@ServiceId", serviceId);
                        command.Parameters.AddWithValue("@ProviderId", currentUserId);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                service = new Service
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("service_id")),
                                    Name = reader.GetString(reader.GetOrdinal("name")),
                                    Type = reader.GetString(reader.GetOrdinal("type")),
                                    PricePerUnit = reader.GetDecimal(reader.GetOrdinal("price_per_unit")),
                                    TotalUnits = reader.GetInt32(reader.GetOrdinal("total_units")),
                                    Location = reader.IsDBNull(reader.GetOrdinal("location")) ? string.Empty : reader.GetString(reader.GetOrdinal("location")),
                                    Description = reader.IsDBNull(reader.GetOrdinal("description")) ? string.Empty : reader.GetString(reader.GetOrdinal("description")),
                                    Status = reader.GetString(reader.GetOrdinal("status"))
                                };
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error retrieving service: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return service;
        }

        private Form CreateServiceEditForm(Service service)
        {
            Form editForm = new Form
            {
                Text = "Edit Service",
                Size = new Size(450, 500),
                StartPosition = FormStartPosition.CenterParent,
                FormBorderStyle = FormBorderStyle.FixedDialog,
                MaximizeBox = false,
                MinimizeBox = false,
                BackColor = Color.FromArgb(236, 240, 241)
            };

            // Create the title label
            Label lblFormTitle = new Label
            {
                Text = "Edit Service",
                Font = new Font("Segoe UI", 12, FontStyle.Bold),
                Size = new Size(400, 30),
                TextAlign = ContentAlignment.MiddleCenter,
                Location = new Point(20, 20),
                ForeColor = Color.FromArgb(41, 128, 185)
            };

            // Create the form controls
            int yPos = 60;
            int spacing = 30;
            int labelWidth = 150;
            int controlWidth = 230;
            int controlHeight = 25;

            // Name
            Label lblName = new Label { Text = "Name:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            TextBox txtName = new TextBox
            {
                Text = service.Name,
                Width = controlWidth,
                Height = controlHeight,
                Location = new Point(180, yPos)
            };
            yPos += spacing;

            // Type
            Label lblType = new Label { Text = "Type:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            ComboBox cboType = new ComboBox
            {
                Width = controlWidth,
                Height = controlHeight,
                DropDownStyle = ComboBoxStyle.DropDownList,
                Location = new Point(180, yPos)
            };
            cboType.Items.AddRange(new string[] { "Tour", "Hotel", "Transport", "Guide" });
            cboType.SelectedItem = service.Type;
            yPos += spacing;

            // Price
            Label lblPrice = new Label { Text = "Price Per Unit:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            NumericUpDown numPrice = new NumericUpDown
            {
                Width = controlWidth,
                Height = controlHeight,
                Location = new Point(180, yPos),
                DecimalPlaces = 2,
                Minimum = 0,
                Maximum = 10000,
                Value = service.PricePerUnit,
                ThousandsSeparator = true
            };
            yPos += spacing;

            // Units
            Label lblUnits = new Label { Text = "Available Units:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            NumericUpDown numUnits = new NumericUpDown
            {
                Width = controlWidth,
                Height = controlHeight,
                Location = new Point(180, yPos),
                Minimum = 0,
                Maximum = 1000,
                Value = service.TotalUnits
            };
            yPos += spacing;

            // Location
            Label lblLocation = new Label { Text = "Location:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            TextBox txtLocation = new TextBox
            {
                Text = service.Location,
                Width = controlWidth,
                Height = controlHeight,
                Location = new Point(180, yPos)
            };
            yPos += spacing;

            // Description
            Label lblDescription = new Label { Text = "Description:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            TextBox txtDescription = new TextBox
            {
                Text = service.Description,
                Width = controlWidth,
                Height = 80,
                Location = new Point(180, yPos),
                Multiline = true,
                ScrollBars = ScrollBars.Vertical
            };
            yPos += 85;

            // Status
            Label lblStatus = new Label { Text = "Status:", Width = labelWidth, Location = new Point(20, yPos + 5) };
            ComboBox cboStatus = new ComboBox
            {
                Width = controlWidth,
                Height = controlHeight,
                DropDownStyle = ComboBoxStyle.DropDownList,
                Location = new Point(180, yPos)
            };
            cboStatus.Items.AddRange(new string[] { "available", "unavailable" });
            cboStatus.SelectedItem = service.Status;
            yPos += spacing * 2;

            // Buttons
            Button btnCancel = new Button
            {
                Text = "Cancel",
                DialogResult = DialogResult.Cancel,
                Size = new Size(100, 40),
                Location = new Point(180, yPos),
                BackColor = Color.FromArgb(189, 195, 199),
                FlatStyle = FlatStyle.Flat
            };
            btnCancel.FlatAppearance.BorderSize = 0;

            Button btnSave = new Button
            {
                Text = "Save",
                DialogResult = DialogResult.OK,
                Size = new Size(100, 40),
                Location = new Point(310, yPos),
                BackColor = Color.FromArgb(41, 128, 185),
                ForeColor = Color.White,
                FlatStyle = FlatStyle.Flat
            };
            btnSave.FlatAppearance.BorderSize = 0;

            // Handle save button click
            btnSave.Click += (s, e) =>
            {
                try
                {
                    // Validate inputs
                    if (string.IsNullOrWhiteSpace(txtName.Text))
                    {
                        MessageBox.Show("Please enter a service name.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                     //   e.Cancel = true;
                        return;
                    }

                    if (cboType.SelectedItem == null)
                    {
                        MessageBox.Show("Please select a service type.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                     //   e.Cancel = true;
                        return;
                    }

                    // Update the service in the database
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        string query = @"UPDATE Service 
                                        SET name = @Name, 
                                            type = @Type, 
                                            price_per_unit = @Price, 
                                            total_units = @Units,
                                            location = @Location,
                                            description = @Description,
                                            status = @Status
                                        WHERE service_id = @ServiceId AND provider_id = @ProviderId";

                        using (SqlCommand command = new SqlCommand(query, connection))
                        {
                            command.Parameters.AddWithValue("@Name", txtName.Text.Trim());
                            command.Parameters.AddWithValue("@Type", cboType.SelectedItem.ToString());
                            command.Parameters.AddWithValue("@Price", numPrice.Value);
                            command.Parameters.AddWithValue("@Units", (int)numUnits.Value);
                            command.Parameters.AddWithValue("@Location", txtLocation.Text.Trim());
                            command.Parameters.AddWithValue("@Description", txtDescription.Text.Trim());
                            command.Parameters.AddWithValue("@Status", cboStatus.SelectedItem.ToString());
                            command.Parameters.AddWithValue("@ServiceId", service.Id);
                            command.Parameters.AddWithValue("@ProviderId", currentUserId);

                            int rowsAffected = command.ExecuteNonQuery();

                            if (rowsAffected > 0)
                            {
                                MessageBox.Show("Service updated successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            else
                            {
                                MessageBox.Show("No changes were made.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Error updating service: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                 //   e.Cancel = true;
                }
            };

            // Add controls to the form
            editForm.Controls.AddRange(new Control[] {
                lblFormTitle,
                lblName, txtName,
                lblType, cboType,
                lblPrice, numPrice,
                lblUnits, numUnits,
                lblLocation, txtLocation,
                lblDescription, txtDescription,
                lblStatus, cboStatus,
                btnCancel, btnSave
            });

            return editForm;
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (dgvServices.SelectedRows.Count == 0) return;

            try
            {
                int serviceId = Convert.ToInt32(dgvServices.SelectedRows[0].Cells["Id"].Value);
                string serviceName = dgvServices.SelectedRows[0].Cells["Name"].Value.ToString();

                if (MessageBox.Show($"Are you sure you want to delete {serviceName}?",
                    "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();

                        // First check if this service has any assignments
                        string checkQuery = @"SELECT COUNT(*) FROM Assignment WHERE service_id = @ServiceId";
                        using (SqlCommand checkCommand = new SqlCommand(checkQuery, connection))
                        {
                            checkCommand.Parameters.AddWithValue("@ServiceId", serviceId);
                            int assignmentCount = (int)checkCommand.ExecuteScalar();

                            if (assignmentCount > 0)
                            {
                                // Service has assignments - perform a soft delete by updating status
                                string updateQuery = @"UPDATE Service SET status = 'unavailable' WHERE service_id = @ServiceId AND provider_id = @ProviderId";
                                using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
                                {
                                    updateCommand.Parameters.AddWithValue("@ServiceId", serviceId);
                                    updateCommand.Parameters.AddWithValue("@ProviderId", currentUserId);

                                    int rowsAffected = updateCommand.ExecuteNonQuery();

                                    if (rowsAffected > 0)
                                    {
                                        MessageBox.Show($"{serviceName} has been marked as unavailable since it has existing assignments.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                    else
                                    {
                                        MessageBox.Show("No changes were made.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                }
                            }
                            else
                            {
                                // No assignments - safe to delete the service
                                string deleteQuery = @"DELETE FROM Service WHERE service_id = @ServiceId AND provider_id = @ProviderId";
                                using (SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection))
                                {
                                    deleteCommand.Parameters.AddWithValue("@ServiceId", serviceId);
                                    deleteCommand.Parameters.AddWithValue("@ProviderId", currentUserId);

                                    int rowsAffected = deleteCommand.ExecuteNonQuery();

                                    if (rowsAffected > 0)
                                    {
                                        MessageBox.Show($"{serviceName} has been deleted successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                    else
                                    {
                                        MessageBox.Show("No changes were made.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                }
                            }
                        }
                    }

                    // Refresh the list
                    LoadServices();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error deleting service: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        // Navigation button handlers
        private void btnRegistration_Click_1(object sender, EventArgs e)
        {
            // Since we're not creating new forms, we'll just show a message
            // In a real application, you would implement navigation to ServiceIntegrationForm
            MessageBox.Show("This would navigate to the Service Integration form.", "Navigation", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // We're already on this form, so no need to open a new instance
            // Just refresh the data
            LoadServices();
            HighlightActiveButton(button1);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // Since we're not creating new forms, we'll just show a message
            // In a real application, you would implement navigation to BookingManagementForm
            MessageBox.Show("This would navigate to the Booking Management form.", "Navigation", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            // Since we're not creating new forms, we'll just show a message
            // In a real application, you would implement navigation to PerformanceReportForm
            MessageBox.Show("This would navigate to the Performance Reports form.", "Navigation", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    // Keep this class for backward compatibility or if database connection fails
    public class Service
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Type { get; set; }
        public decimal PricePerUnit { get; set; }
        public int TotalUnits { get; set; }
        public string Location { get; set; }
        public string Description { get; set; }
        public string Status { get; set; }
    }
}