Form9.cs

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace H1
{

    public partial class Form9 : Form
    {
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";

        // Colors based on the provided specs
        private Color navbarColor = Color.FromArgb(44, 62, 80);
        private Color headerColor = Color.FromArgb(52, 152, 219);
        private Color whiteColor = Color.White;
        private Color lightGrayColor = Color.FromArgb(240, 240, 240);

        // Current selected trip for booking
        private DataRow selectedTrip = null;

        public Form9()
        {
            InitializeComponent();
            LoadInitialData();
        }

        private void LoadInitialData()
        {
            // Load destinations from database
            LoadDestinationsFromDB();

            // Load categories from database
            LoadCategoriesFromDB();

            // Load trips from database
            LoadTripsFromDB();

            // Payment methods remain hardcoded
            string[] paymentMethods = { "Credit Card", "Debit Card", "PayPal", "Bank Transfer" };
            cmbPaymentMethod.Items.AddRange(paymentMethods);
        }
        private void LoadDestinationsFromDB()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    string query = "SELECT DISTINCT destination FROM Trip WHERE is_active = 1";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        cmbDestination.Items.Add(reader["destination"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading destinations: " + ex.Message);
                // Fallback to hardcoded values
                string[] destinations = { "Paris", "London", "Tokyo", "New York" };
                cmbDestination.Items.AddRange(destinations);
            }
        }

        private void LoadCategoriesFromDB()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    string query = "SELECT name FROM TourCategory";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        cmbCategory.Items.Add(reader["name"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading categories: " + ex.Message);
                // Fallback to hardcoded values
                string[] categories = { "Adventure", "Cultural", "Beach" };
                cmbCategory.Items.AddRange(categories);
            }
        }

        private void LoadTripsFromDB()
        {
            try
            {
                DataTable trips = new DataTable();

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    string query = @"
                SELECT 
                    t.trip_id AS ID,
                    t.title AS Title,
                    t.destination AS Destination,
                    FORMAT(t.start_date, 'dd-MMM-yyyy') AS StartDate,
                    FORMAT(t.end_date, 'dd-MMM-yyyy') AS EndDate,
                    '$' + CONVERT(VARCHAR, t.price) AS Price,
                    t.max_capacity AS Available,
                    cat.name AS Category
                FROM Trip t
                LEFT JOIN TripCategory tc ON t.trip_id = tc.trip_id
                LEFT JOIN TourCategory cat ON tc.category_id = cat.category_id
                WHERE t.is_active = 1";

                    SqlCommand cmd = new SqlCommand(query, conn);
                    conn.Open();

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(trips);
                }

                dgvSearchResults.DataSource = trips;
                FormatSearchResultsGrid();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading trips: " + ex.Message);
                LoadSampleTrips(); // Fallback to your original sample data method
            }
        }

        private void LoadSampleTrips()
        {
            DataTable trips = new DataTable();
            trips.Columns.Add("ID", typeof(int));
            trips.Columns.Add("Title", typeof(string));
            trips.Columns.Add("Destination", typeof(string));
            trips.Columns.Add("StartDate", typeof(string));
            trips.Columns.Add("EndDate", typeof(string));
            trips.Columns.Add("Price", typeof(string));
            trips.Columns.Add("Available", typeof(int));
            trips.Columns.Add("Category", typeof(string));

            // Add sample trips
            trips.Rows.Add(1, "Paris City Tour", "Paris", "15-May-2025", "20-May-2025", "$1200", 25, "City Tour");
            trips.Rows.Add(2, "London Explorer", "London", "01-Jun-2025", "07-Jun-2025", "$1500", 15, "Cultural");
            trips.Rows.Add(3, "Tokyo Adventure", "Tokyo", "10-Jul-2025", "20-Jul-2025", "$2500", 10, "Adventure");
            trips.Rows.Add(4, "Rome History Tour", "Rome", "05-Aug-2025", "12-Aug-2025", "$1800", 20, "Historical");

            dgvSearchResults.DataSource = trips;
            FormatSearchResultsGrid();
        }

        private void FormatSearchResultsGrid()
        {
            dgvSearchResults.Columns["ID"].Visible = false;
            dgvSearchResults.Columns["Category"].Visible = false;
            dgvSearchResults.Columns["Price"].HeaderText = "Price (per person)";
            dgvSearchResults.Columns["Available"].HeaderText = "Available Places";
            dgvSearchResults.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgvSearchResults.RowHeadersVisible = false;
            dgvSearchResults.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable trips = new DataTable();

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    string query = @"
                SELECT 
                    t.trip_id AS ID,
                    t.title AS Title,
                    t.destination AS Destination,
                    FORMAT(t.start_date, 'dd-MMM-yyyy') AS StartDate,
                    FORMAT(t.end_date, 'dd-MMM-yyyy') AS EndDate,
                    '$' + CONVERT(VARCHAR, t.price) AS Price,
                    t.max_capacity AS Available,
                    cat.name AS Category
                FROM Trip t
                LEFT JOIN TripCategory tc ON t.trip_id = tc.trip_id
                LEFT JOIN TourCategory cat ON tc.category_id = cat.category_id
                WHERE t.is_active = 1";

                    // Dynamic WHERE clause
                    List<string> conditions = new List<string>();
                    List<SqlParameter> parameters = new List<SqlParameter>();

                    if (cmbDestination.SelectedItem != null)
                    {
                        conditions.Add("t.destination = @destination");
                        parameters.Add(new SqlParameter("@destination", cmbDestination.SelectedItem.ToString()));
                    }

                    if (cmbCategory.SelectedItem != null)
                    {
                        conditions.Add("cat.name = @category");
                        parameters.Add(new SqlParameter("@category", cmbCategory.SelectedItem.ToString()));
                    }

                    if (dtpStartDate.Checked)
                    {
                        conditions.Add("t.start_date >= @startDate");
                        parameters.Add(new SqlParameter("@startDate", dtpStartDate.Value));
                    }

                    if (dtpEndDate.Checked)
                    {
                        conditions.Add("t.end_date <= @endDate");
                        parameters.Add(new SqlParameter("@endDate", dtpEndDate.Value));
                    }

                    if (conditions.Count > 0)
                    {
                        query += " AND " + string.Join(" AND ", conditions);
                    }

                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.Parameters.AddRange(parameters.ToArray());

                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(trips);
                }

                dgvSearchResults.DataSource = trips;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error searching trips: " + ex.Message);
            }
        }

        private void dgvSearchResults_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                // Get selected trip data
                DataGridViewRow row = dgvSearchResults.Rows[e.RowIndex];
                selectedTrip = ((DataRowView)row.DataBoundItem).Row;

                // Update booking tab with selected trip
                lblSelectedTrip.Text = selectedTrip["Title"].ToString();
                lblTripDetails.Text = $"Destination: {selectedTrip["Destination"]}\n" +
                                    $"Dates: {selectedTrip["StartDate"]} to {selectedTrip["EndDate"]}\n" +
                                    $"Price per person: {selectedTrip["Price"]}\n" +
                                    $"Category: {selectedTrip["Category"]}";

                // Calculate initial price
                UpdateTotalPrice();

                // Enable booking controls
                nudBookingParticipants.Enabled = true;
                cmbPaymentMethod.Enabled = true;
                btnConfirmBooking.Enabled = true;

                // Switch to booking tab
                tabControl.SelectedTab = tabBooking;
            }
        }

        private void UpdateTotalPrice()
        {
            if (selectedTrip != null)
            {
                string priceStr = selectedTrip["Price"].ToString().Replace("$", "");
                if (decimal.TryParse(priceStr, out decimal pricePerPerson))
                {
                    decimal totalPrice = pricePerPerson * nudBookingParticipants.Value;
                    txtTotalPrice.Text = $"${totalPrice}";
                }
            }
        }

        private void nudBookingParticipants_ValueChanged(object sender, EventArgs e)
        {
            UpdateTotalPrice();
        }

        private void btnConfirmBooking_Click(object sender, EventArgs e)
        {
            if (cmbPaymentMethod.SelectedItem == null)
            {
                MessageBox.Show("Please select a payment method", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    // 1. Create booking
                    string bookingQuery = @"
                INSERT INTO Booking 
                (traveler_id, trip_id, participants, total_amount, status)
                VALUES (@travelerId, @tripId, @participants, @totalAmount, 'confirmed');
                SELECT SCOPE_IDENTITY();";

                    decimal totalPrice = decimal.Parse(txtTotalPrice.Text.Replace("$", ""));

                    SqlCommand bookingCmd = new SqlCommand(bookingQuery, conn);
                    bookingCmd.Parameters.AddWithValue("@travelerId", 1); // Replace with actual user ID
                    bookingCmd.Parameters.AddWithValue("@tripId", selectedTrip["ID"]);
                    bookingCmd.Parameters.AddWithValue("@participants", nudBookingParticipants.Value);
                    bookingCmd.Parameters.AddWithValue("@totalAmount", totalPrice);

                    int bookingId = Convert.ToInt32(bookingCmd.ExecuteScalar());

                    // 2. Create payment record
                    string paymentQuery = @"
                INSERT INTO Payment 
                (booking_id, amount, payment_method, transaction_id, status)
                VALUES (@bookingId, @amount, @method, NEWID(), 'completed')";

                    string paymentMethod = cmbPaymentMethod.SelectedItem.ToString()
                        .ToLower()
                        .Replace(" ", "_");

                    SqlCommand paymentCmd = new SqlCommand(paymentQuery, conn);
                    paymentCmd.Parameters.AddWithValue("@bookingId", bookingId);
                    paymentCmd.Parameters.AddWithValue("@amount", totalPrice);
                    paymentCmd.Parameters.AddWithValue("@method", paymentMethod);
                    paymentCmd.ExecuteNonQuery();

                    MessageBox.Show($"Booking #{bookingId} confirmed!\nTotal: {txtTotalPrice.Text}",
                                  "Success",
                                  MessageBoxButtons.OK,
                                  MessageBoxIcon.Information);

                    ResetBookingForm();
                    tabControl.SelectedTab = tabSearch;
                    LoadTripsFromDB(); // Refresh the trip list
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error saving booking: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void ResetBookingForm()
        {
            lblSelectedTrip.Text = "(No trip selected)";
            lblTripDetails.Text = "Please select a trip from the Search tab first";
            nudBookingParticipants.Value = 1;
            txtTotalPrice.Text = "";
            cmbPaymentMethod.SelectedIndex = -1;
            nudBookingParticipants.Enabled = false;
            cmbPaymentMethod.Enabled = false;
            btnConfirmBooking.Enabled = false;
            selectedTrip = null;
        }
    }
}