Form12.cs


using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using System.Configuration;

namespace H1
{
    public partial class Form12 : Form
    {
        // Colors
        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);

        // Database connection - made configurable
        private string connectionString = @"Data Source=ABDULSABOOR190\SQLEXPRESS;Initial Catalog=sabbbb;Integrated Security=True;Encrypt=False";

        // Current operator ID (replace with actual operator ID from login)
        private int currentOperatorId = 3;

        // Selected trip ID for updates/deletes
        private int selectedTripId = -1;
        private int defaultPolicyId = 1; // Default cancellation policy ID

        public Form12()
        {
            InitializeComponent();

            // Try to load connection string from config if available
            try
            {
                if (ConfigurationManager.ConnectionStrings["DefaultConnection"] != null)
                {
                    connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                }
            }
            catch (Exception)
            {
                // Use the default connection string
            }

            // Set default dates
            dtpStartDate.Value = DateTime.Now.AddDays(1);
            dtpEndDate.Value = DateTime.Now.AddDays(8);

            LoadData();
        }

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

                    // Check if cancellation policy exists and create a default one if not
                    EnsureDefaultCancellationPolicy(conn);

                    // Load categories
                    LoadCategories(conn);

                    // Load trips
                    LoadTrips(conn);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading data: " + ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                LoadSampleData(); // Fallback to sample data
            }
        }

        private void EnsureDefaultCancellationPolicy(SqlConnection conn)
        {
            try
            {
                // Check if table exists
                string checkTableQuery = @"
                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CancellationPolicy')
                    SELECT 1 ELSE SELECT 0";
                SqlCommand checkTableCmd = new SqlCommand(checkTableQuery, conn);
                int tableExists = (int)checkTableCmd.ExecuteScalar();

                if (tableExists == 0)
                {
                    // Create the table if it doesn't exist
                    string createTableQuery = @"
                        CREATE TABLE CancellationPolicy (
                            policy_id INT PRIMARY KEY IDENTITY(1,1),
                            refund_percent DECIMAL(5,2) NOT NULL,
                            deadline_days INT NOT NULL,
                            terms TEXT NOT NULL
                        )";
                    SqlCommand createTableCmd = new SqlCommand(createTableQuery, conn);
                    createTableCmd.ExecuteNonQuery();
                }

                // Check if any policy exists
                string checkQuery = "SELECT COUNT(*) FROM CancellationPolicy";
                SqlCommand checkCmd = new SqlCommand(checkQuery, conn);
                int policyCount = (int)checkCmd.ExecuteScalar();

                if (policyCount == 0)
                {
                    // Create a default policy
                    string createQuery = @"
                        INSERT INTO CancellationPolicy (refund_percent, deadline_days, terms)
                        VALUES (100, 7, 'Full refund if canceled 7 days before the trip.');
                        SELECT SCOPE_IDENTITY();";

                    SqlCommand createCmd = new SqlCommand(createQuery, conn);
                    decimal result = (decimal)createCmd.ExecuteScalar();
                    defaultPolicyId = (int)result;
                }
                else
                {
                    // Get the ID of an existing policy to use as default
                    string getIdQuery = "SELECT TOP 1 policy_id FROM CancellationPolicy";
                    SqlCommand getIdCmd = new SqlCommand(getIdQuery, conn);
                    defaultPolicyId = (int)getIdCmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error setting up cancellation policy: " + ex.Message, "Warning",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void LoadCategories(SqlConnection conn)
        {
            try
            {
                // Check if table exists
                string checkTableQuery = @"
                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TourCategory')
                    SELECT 1 ELSE SELECT 0";
                SqlCommand checkTableCmd = new SqlCommand(checkTableQuery, conn);
                int tableExists = (int)checkTableCmd.ExecuteScalar();

                if (tableExists == 0)
                {
                    // Create the table if it doesn't exist
                    string createTableQuery = @"
                        CREATE TABLE TourCategory (
                            category_id INT PRIMARY KEY IDENTITY(1,1),
                            name VARCHAR(50) UNIQUE NOT NULL,
                            description TEXT
                        )";
                    SqlCommand createTableCmd = new SqlCommand(createTableQuery, conn);
                    createTableCmd.ExecuteNonQuery();
                }

                string query = "SELECT category_id AS CategoryID, name AS Name, description AS Description FROM TourCategory";
                SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
                DataTable categories = new DataTable();
                adapter.Fill(categories);

                if (categories.Rows.Count > 0)
                {
                    cmbCategory.DataSource = categories;
                    cmbCategory.DisplayMember = "Name";
                    cmbCategory.ValueMember = "CategoryID";
                }
                else
                {
                    // If no categories exist, try to create some default ones
                    CreateDefaultCategories(conn);

                    // Try loading again
                    adapter.Fill(categories);
                    cmbCategory.DataSource = categories;
                    cmbCategory.DisplayMember = "Name";
                    cmbCategory.ValueMember = "CategoryID";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading categories: " + ex.Message, "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void CreateDefaultCategories(SqlConnection conn)
        {
            try
            {
                string[] categoryNames = { "Adventure", "Cultural", "Beach", "Mountain", "City Tour", "Wildlife", "Historical" };
                string[] descriptions = {
                    "High-energy outdoor activities",
                    "Immersive cultural experiences",
                    "Relaxing beach vacations",
                    "Mountain hiking and climbing",
                    "Urban exploration tours",
                    "Wildlife safaris and encounters",
                    "Historical sites and museums"
                };

                for (int i = 0; i < categoryNames.Length; i++)
                {
                    string query = @"
                        INSERT INTO TourCategory (name, description)
                        VALUES (@name, @description)";

                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.Parameters.AddWithValue("@name", categoryNames[i]);
                    cmd.Parameters.AddWithValue("@description", descriptions[i]);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error creating default categories: " + ex.Message, "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void LoadTrips(SqlConnection conn)
        {
            try
            {
                // Check if tables exist
                EnsureTripTablesExist(conn);

                // Check if operator profile exists for the current operator ID
                EnsureOperatorProfileExists(conn);

                string query = @"
                    SELECT 
                        t.trip_id AS TripID,
                        t.title AS Title,
                        t.description AS Description,
                        t.destination AS Destination,
                        tc.category_id AS CategoryID,
                        cat.name AS Category,
                        t.start_date AS StartDate,
                        t.end_date AS EndDate,
                        t.price AS Price,
                        t.max_capacity AS MaxCapacity,
                        t.difficulty_level AS Difficulty,
                        CASE WHEN t.is_active = 1 THEN 'Active' ELSE 'Inactive' END AS Status,
                        t.policy_id AS PolicyID
                    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.operator_id = @operatorId";

                SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
                adapter.SelectCommand.Parameters.AddWithValue("@operatorId", currentOperatorId);
                DataTable trips = new DataTable();
                adapter.Fill(trips);

                dgvTrips.DataSource = trips;
                FormatTripsGrid();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading trips: " + ex.Message, "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void EnsureTripTablesExist(SqlConnection conn)
        {
            try
            {
                // Check if Trip table exists
                string checkTripTableQuery = @"
                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Trip')
                    SELECT 1 ELSE SELECT 0";
                SqlCommand checkTripTableCmd = new SqlCommand(checkTripTableQuery, conn);
                int tripTableExists = (int)checkTripTableCmd.ExecuteScalar();

                if (tripTableExists == 0)
                {
                    // Create the Trip table if it doesn't exist
                    string createTripTableQuery = @"
                        CREATE TABLE Trip (
                            trip_id INT PRIMARY KEY IDENTITY(1,1),
                            operator_id INT NOT NULL,
                            policy_id INT NOT NULL,
                            title VARCHAR(255) NOT NULL,
                            description TEXT NOT NULL,
                            destination VARCHAR(255) NOT NULL,
                            start_date DATE NOT NULL,
                            end_date DATE NOT NULL,
                            max_capacity INT NOT NULL,
                            price DECIMAL(10,2) NOT NULL,
                            difficulty_level VARCHAR(10) NOT NULL CHECK (difficulty_level IN ('easy', 'medium', 'hard')),
                            sustainability_score DECIMAL(3,1),
                            is_active BIT DEFAULT 1
                        )";
                    SqlCommand createTripTableCmd = new SqlCommand(createTripTableQuery, conn);
                    createTripTableCmd.ExecuteNonQuery();
                }

                // Check if TripCategory table exists
                string checkTripCategoryTableQuery = @"
                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TripCategory')
                    SELECT 1 ELSE SELECT 0";
                SqlCommand checkTripCategoryTableCmd = new SqlCommand(checkTripCategoryTableQuery, conn);
                int tripCategoryTableExists = (int)checkTripCategoryTableCmd.ExecuteScalar();

                if (tripCategoryTableExists == 0)
                {
                    // Create the TripCategory table if it doesn't exist
                    string createTripCategoryTableQuery = @"
                        CREATE TABLE TripCategory (
                            trip_id INT NOT NULL,
                            category_id INT NOT NULL,
                            PRIMARY KEY (trip_id, category_id)
                        )";
                    SqlCommand createTripCategoryTableCmd = new SqlCommand(createTripCategoryTableQuery, conn);
                    createTripCategoryTableCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error ensuring trip tables exist: " + ex.Message, "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void EnsureOperatorProfileExists(SqlConnection conn)
        {
            try
            {
                // Check if AppUser table exists
                string checkAppUserTableQuery = @"
                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppUser')
                    SELECT 1 ELSE SELECT 0";
                SqlCommand checkAppUserTableCmd = new SqlCommand(checkAppUserTableQuery, conn);
                int appUserTableExists = (int)checkAppUserTableCmd.ExecuteScalar();

                if (appUserTableExists == 0)
                {
                    // Create the AppUser table if it doesn't exist
                    string createAppUserTableQuery = @"
                        CREATE TABLE AppUser (
                            user_id INT IDENTITY(1,1) PRIMARY KEY,
                            email VARCHAR(255) UNIQUE NOT NULL,
                            password_hash VARCHAR(255) NOT NULL,
                            registration_status VARCHAR(50) NOT NULL,
                            role VARCHAR(50) NOT NULL,
                            is_active BIT DEFAULT 1 NOT NULL,
                            created_at DATETIME DEFAULT GETDATE() NOT NULL
                        )";
                    SqlCommand createAppUserTableCmd = new SqlCommand(createAppUserTableQuery, conn);
                    createAppUserTableCmd.ExecuteNonQuery();
                }

                // Check if TourOperatorProfile table exists
                string checkOperatorTableQuery = @"
                    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TourOperatorProfile')
                    SELECT 1 ELSE SELECT 0";
                SqlCommand checkOperatorTableCmd = new SqlCommand(checkOperatorTableQuery, conn);
                int operatorTableExists = (int)checkOperatorTableCmd.ExecuteScalar();

                if (operatorTableExists == 0)
                {
                    // Create the TourOperatorProfile table if it doesn't exist
                    string createOperatorTableQuery = @"
                        CREATE TABLE TourOperatorProfile (
                            user_id INT PRIMARY KEY,
                            company_name VARCHAR(100) NOT NULL,
                            contact_no VARCHAR(20) NOT NULL,
                            license_no VARCHAR(50) UNIQUE NOT NULL,
                            description TEXT
                        )";
                    SqlCommand createOperatorTableCmd = new SqlCommand(createOperatorTableQuery, conn);
                    createOperatorTableCmd.ExecuteNonQuery();
                }

                // Check if the current operator exists
                string checkOperatorQuery = @"
                    SELECT COUNT(*) FROM TourOperatorProfile WHERE user_id = @operatorId";
                SqlCommand checkOperatorCmd = new SqlCommand(checkOperatorQuery, conn);
                checkOperatorCmd.Parameters.AddWithValue("@operatorId", currentOperatorId);
                int operatorExists = (int)checkOperatorCmd.ExecuteScalar();

                if (operatorExists == 0)
                {
                    // Check if user exists in AppUser table
                    string checkUserQuery = @"
                        SELECT COUNT(*) FROM AppUser WHERE user_id = @userId";
                    SqlCommand checkUserCmd = new SqlCommand(checkUserQuery, conn);
                    checkUserCmd.Parameters.AddWithValue("@userId", currentOperatorId);
                    int userExists = (int)checkUserCmd.ExecuteScalar();

                    if (userExists == 0)
                    {
                        // Create a user entry
                        string createUserQuery = @"
                            INSERT INTO AppUser (user_id, email, password_hash, registration_status, role, is_active)
                            VALUES (@userId, @email, @passwordHash, 'approved', 'operator', 1)";
                        SqlCommand createUserCmd = new SqlCommand(createUserQuery, conn);
                        createUserCmd.Parameters.AddWithValue("@userId", currentOperatorId);
                        createUserCmd.Parameters.AddWithValue("@email", "operator" + currentOperatorId + "@example.com");
                        createUserCmd.Parameters.AddWithValue("@passwordHash", "dummypasswordhash");
                        createUserCmd.ExecuteNonQuery();
                    }

                    // Create an operator profile
                    string createOperatorQuery = @"
                        INSERT INTO TourOperatorProfile (user_id, company_name, contact_no, license_no, description)
                        VALUES (@operatorId, @companyName, @contactNo, @licenseNo, @description)";
                    SqlCommand createOperatorCmd = new SqlCommand(createOperatorQuery, conn);
                    createOperatorCmd.Parameters.AddWithValue("@operatorId", currentOperatorId);
                    createOperatorCmd.Parameters.AddWithValue("@companyName", "Default Tour Operator");
                    createOperatorCmd.Parameters.AddWithValue("@contactNo", "123-456-7890");
                    createOperatorCmd.Parameters.AddWithValue("@licenseNo", "LIC-" + currentOperatorId);
                    createOperatorCmd.Parameters.AddWithValue("@description", "Default tour operator description");
                    createOperatorCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error ensuring operator profile exists: " + ex.Message, "Database Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void FormatTripsGrid()
        {
            if (dgvTrips.Columns.Count > 0)
            {
                dgvTrips.Columns["TripID"].Visible = false;
                dgvTrips.Columns["CategoryID"].Visible = false;
                dgvTrips.Columns["Description"].Visible = false;

                if (dgvTrips.Columns.Contains("PolicyID"))
                    dgvTrips.Columns["PolicyID"].Visible = false;

                dgvTrips.Columns["Title"].HeaderText = "Trip Title";
                dgvTrips.Columns["Destination"].HeaderText = "Destination";
                dgvTrips.Columns["StartDate"].HeaderText = "Start Date";
                dgvTrips.Columns["EndDate"].HeaderText = "End Date";
                dgvTrips.Columns["Price"].HeaderText = "Price (USD)";
                dgvTrips.Columns["MaxCapacity"].HeaderText = "Capacity";

                dgvTrips.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                dgvTrips.RowHeadersVisible = false;
                dgvTrips.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                dgvTrips.AllowUserToAddRows = false;
            }
        }

        private void btnCreateTrip_Click(object sender, EventArgs e)
        {
            if (ValidateTripForm())
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();

                        // Ensure all required tables exist
                        EnsureTripTablesExist(conn);
                        EnsureOperatorProfileExists(conn);
                        EnsureDefaultCancellationPolicy(conn);

                        SqlTransaction transaction = conn.BeginTransaction();

                        try
                        {
                            // 1. Insert trip
                            string tripQuery = @"
                                INSERT INTO Trip (
                                    operator_id, title, description, destination, 
                                    start_date, end_date, max_capacity, price, 
                                    difficulty_level, is_active, policy_id
                                ) 
                                VALUES (
                                    @operatorId, @title, @description, @destination, 
                                    @startDate, @endDate, @maxCapacity, @price, 
                                    @difficulty, 1, @policyId
                                );
                                SELECT SCOPE_IDENTITY();";

                            SqlCommand tripCmd = new SqlCommand(tripQuery, conn, transaction);
                            tripCmd.Parameters.AddWithValue("@operatorId", currentOperatorId);
                            tripCmd.Parameters.AddWithValue("@title", txtTitle.Text);
                            tripCmd.Parameters.AddWithValue("@description", txtDescription.Text);
                            tripCmd.Parameters.AddWithValue("@destination", txtDestination.Text);
                            tripCmd.Parameters.AddWithValue("@startDate", dtpStartDate.Value);
                            tripCmd.Parameters.AddWithValue("@endDate", dtpEndDate.Value);
                            tripCmd.Parameters.AddWithValue("@maxCapacity", int.Parse(txtCapacity.Text));
                            tripCmd.Parameters.AddWithValue("@price", decimal.Parse(txtPrice.Text));
                            tripCmd.Parameters.AddWithValue("@difficulty", cmbDifficulty.SelectedItem.ToString().ToLower());
                            tripCmd.Parameters.AddWithValue("@policyId", defaultPolicyId);

                            int tripId = Convert.ToInt32(tripCmd.ExecuteScalar());

                            // 2. Insert trip category
                            string categoryQuery = @"
                                INSERT INTO TripCategory (trip_id, category_id)
                                VALUES (@tripId, @categoryId)";

                            SqlCommand categoryCmd = new SqlCommand(categoryQuery, conn, transaction);
                            categoryCmd.Parameters.AddWithValue("@tripId", tripId);
                            categoryCmd.Parameters.AddWithValue("@categoryId", cmbCategory.SelectedValue);
                            categoryCmd.ExecuteNonQuery();

                            transaction.Commit();
                            MessageBox.Show("Trip created successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            ClearForm();
                            LoadTrips(conn); // Refresh the trips list
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw new Exception("Database error during trip creation: " + ex.Message);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error creating trip: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private bool ValidateTripForm()
        {
            if (string.IsNullOrWhiteSpace(txtTitle.Text))
            {
                MessageBox.Show("Please enter a trip title", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (string.IsNullOrWhiteSpace(txtDestination.Text))
            {
                MessageBox.Show("Please enter a destination", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (dtpStartDate.Value.Date < DateTime.Now.Date)
            {
                MessageBox.Show("Start date cannot be in the past", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (dtpStartDate.Value >= dtpEndDate.Value)
            {
                MessageBox.Show("End date must be after start date", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (!decimal.TryParse(txtPrice.Text, out decimal price) || price <= 0)
            {
                MessageBox.Show("Please enter a valid price", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (!int.TryParse(txtCapacity.Text, out int capacity) || capacity <= 0)
            {
                MessageBox.Show("Please enter a valid capacity", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (cmbCategory.SelectedValue == null)
            {
                MessageBox.Show("Please select a category", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            return true;
        }

        private void ClearForm()
        {
            txtTitle.Clear();
            txtDescription.Clear();
            txtDestination.Clear();
            if (cmbCategory.Items.Count > 0) cmbCategory.SelectedIndex = 0;
            dtpStartDate.Value = DateTime.Now.AddDays(1);
            dtpEndDate.Value = DateTime.Now.AddDays(8);
            txtPrice.Clear();
            txtCapacity.Clear();
            if (cmbDifficulty.Items.Count > 0) cmbDifficulty.SelectedIndex = 0;

            btnCreateTrip.Enabled = true;
            btnUpdateTrip.Enabled = false;
            btnDeleteTrip.Enabled = false;

            selectedTripId = -1;
        }

        private void dgvTrips_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvTrips.SelectedRows.Count > 0)
            {
                DataRowView row = (DataRowView)dgvTrips.SelectedRows[0].DataBoundItem;
                LoadTripDetails(row.Row);
            }
        }

        private void LoadTripDetails(DataRow trip)
        {
            try
            {
                selectedTripId = Convert.ToInt32(trip["TripID"]);
                txtTitle.Text = trip["Title"].ToString();
                txtDescription.Text = trip["Description"].ToString();
                txtDestination.Text = trip["Destination"].ToString();

                // Handle potential null or missing CategoryID
                if (trip["CategoryID"] != DBNull.Value && cmbCategory.Items.Count > 0)
                {
                    cmbCategory.SelectedValue = trip["CategoryID"];
                }

                dtpStartDate.Value = (trip["StartDate"] != DBNull.Value) ?
                    (DateTime)trip["StartDate"] : DateTime.Now;

                dtpEndDate.Value = (trip["EndDate"] != DBNull.Value) ?
                    (DateTime)trip["EndDate"] : DateTime.Now.AddDays(1);

                txtPrice.Text = trip["Price"].ToString();
                txtCapacity.Text = trip["MaxCapacity"].ToString();

                // Set difficulty (adapt for case sensitivity)
                string difficulty = trip["Difficulty"].ToString();
                for (int i = 0; i < cmbDifficulty.Items.Count; i++)
                {
                    if (cmbDifficulty.Items[i].ToString().Equals(difficulty, StringComparison.OrdinalIgnoreCase))
                    {
                        cmbDifficulty.SelectedIndex = i;
                        break;
                    }
                }

                btnUpdateTrip.Enabled = true;
                btnDeleteTrip.Enabled = true;
                btnCreateTrip.Enabled = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading trip details: " + ex.Message, "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnUpdateTrip_Click(object sender, EventArgs e)
        {
            if (selectedTripId <= 0)
            {
                MessageBox.Show("Please select a trip to update", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (ValidateTripForm())
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        SqlTransaction transaction = conn.BeginTransaction();

                        try
                        {
                            // 1. Update trip
                            string tripQuery = @"
                                UPDATE Trip SET
                                    title = @title,
                                    description = @description,
                                    destination = @destination,
                                    start_date = @startDate,
                                    end_date = @endDate,
                                    max_capacity = @maxCapacity,
                                    price = @price,
                                    difficulty_level = @difficulty
                                WHERE trip_id = @tripId";

                            SqlCommand tripCmd = new SqlCommand(tripQuery, conn, transaction);
                            tripCmd.Parameters.AddWithValue("@title", txtTitle.Text);
                            tripCmd.Parameters.AddWithValue("@description", txtDescription.Text);
                            tripCmd.Parameters.AddWithValue("@destination", txtDestination.Text);
                            tripCmd.Parameters.AddWithValue("@startDate", dtpStartDate.Value);
                            tripCmd.Parameters.AddWithValue("@endDate", dtpEndDate.Value);
                            tripCmd.Parameters.AddWithValue("@maxCapacity", int.Parse(txtCapacity.Text));
                            tripCmd.Parameters.AddWithValue("@price", decimal.Parse(txtPrice.Text));
                            tripCmd.Parameters.AddWithValue("@difficulty", cmbDifficulty.SelectedItem.ToString().ToLower());
                            tripCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                            tripCmd.ExecuteNonQuery();

                            // 2. Update trip category (delete old and insert new)
                            string deleteCategoryQuery = "DELETE FROM TripCategory WHERE trip_id = @tripId";
                            SqlCommand deleteCmd = new SqlCommand(deleteCategoryQuery, conn, transaction);
                            deleteCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                            deleteCmd.ExecuteNonQuery();

                            string insertCategoryQuery = @"
                                INSERT INTO TripCategory (trip_id, category_id)
                                VALUES (@tripId, @categoryId)";
                            SqlCommand insertCmd = new SqlCommand(insertCategoryQuery, conn, transaction);
                            insertCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                            insertCmd.Parameters.AddWithValue("@categoryId", cmbCategory.SelectedValue);
                            insertCmd.ExecuteNonQuery();

                            transaction.Commit();
                            MessageBox.Show("Trip updated successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            ClearForm();
                            LoadTrips(conn); // Refresh the trips list
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw new Exception("Database error during trip update: " + ex.Message);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error updating trip: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void btnDeleteTrip_Click(object sender, EventArgs e)
        {
            if (selectedTripId <= 0)
            {
                MessageBox.Show("Please select a trip to delete", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            var confirm = MessageBox.Show("Are you sure you want to delete this trip?", "Confirm Delete",
                                         MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (confirm == DialogResult.Yes)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        SqlTransaction transaction = conn.BeginTransaction();

                        try
                        {
                            // Check if this trip has any bookings
                            string checkBookingsQuery = @"
                                SELECT COUNT(*) FROM Booking WHERE trip_id = @tripId";
                            SqlCommand checkCmd = new SqlCommand(checkBookingsQuery, conn, transaction);
                            checkCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                            int bookingCount = (int)checkCmd.ExecuteScalar();

                            if (bookingCount > 0)
                            {
                                // If there are bookings, just set the trip to inactive instead of deleting
                                string deactivateQuery = @"
                                    UPDATE Trip SET is_active = 0 WHERE trip_id = @tripId";
                                SqlCommand deactivateCmd = new SqlCommand(deactivateQuery, conn, transaction);
                                deactivateCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                                deactivateCmd.ExecuteNonQuery();

                                transaction.Commit();
                                MessageBox.Show("Trip has existing bookings and has been deactivated instead of deleted.",
                                    "Trip Deactivated", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            else
                            {
                                // First delete from TripCategory (foreign key constraint)
                                string deleteCategoryQuery = "DELETE FROM TripCategory WHERE trip_id = @tripId";
                                SqlCommand deleteCategoryCmd = new SqlCommand(deleteCategoryQuery, conn, transaction);
                                deleteCategoryCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                                deleteCategoryCmd.ExecuteNonQuery();

                                // Check and delete any itinerary items
                                string deleteItineraryQuery = "DELETE FROM ItineraryItem WHERE trip_id = @tripId";
                                SqlCommand deleteItineraryCmd = new SqlCommand(deleteItineraryQuery, conn, transaction);
                                deleteItineraryCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                                deleteItineraryCmd.ExecuteNonQuery();

                                // Then delete from Trip
                                string deleteTripQuery = "DELETE FROM Trip WHERE trip_id = @tripId";
                                SqlCommand deleteTripCmd = new SqlCommand(deleteTripQuery, conn, transaction);
                                deleteTripCmd.Parameters.AddWithValue("@tripId", selectedTripId);
                                deleteTripCmd.ExecuteNonQuery();

                                transaction.Commit();
                                MessageBox.Show("Trip deleted successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }

                            ClearForm();
                            LoadTrips(conn); // Refresh the trips list
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error deleting trip: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void btnClearForm_Click(object sender, EventArgs e)
        {
            ClearForm();
        }

        private void LoadSampleData()
        {
            // Fallback sample data for categories
            DataTable categories = new DataTable();
            categories.Columns.Add("CategoryID", typeof(int));
            categories.Columns.Add("Name", typeof(string));
            categories.Columns.Add("Description", typeof(string));

            categories.Rows.Add(1, "Adventure", "High-energy outdoor activities");
            categories.Rows.Add(2, "Cultural", "Immersive cultural experiences");
            categories.Rows.Add(3, "Beach", "Relaxing beach vacations");
            categories.Rows.Add(4, "Mountain", "Mountain hiking and climbing");
            categories.Rows.Add(5, "City Tour", "Urban exploration tours");
            categories.Rows.Add(6, "Wildlife", "Wildlife safaris and encounters");
            categories.Rows.Add(7, "Historical", "Historical sites and museums");

            cmbCategory.DataSource = categories;
            cmbCategory.DisplayMember = "Name";
            cmbCategory.ValueMember = "CategoryID";

            // Fallback sample data for trips
            DataTable trips = new DataTable();
            trips.Columns.Add("TripID", typeof(int));
            trips.Columns.Add("Title", typeof(string));
            trips.Columns.Add("Description", typeof(string));
            trips.Columns.Add("Destination", typeof(string));
            trips.Columns.Add("CategoryID", typeof(int));
            trips.Columns.Add("Category", typeof(string));
            trips.Columns.Add("StartDate", typeof(DateTime));
            trips.Columns.Add("EndDate", typeof(DateTime));
            trips.Columns.Add("Price", typeof(decimal));
            trips.Columns.Add("MaxCapacity", typeof(int));
            trips.Columns.Add("Difficulty", typeof(string));
            trips.Columns.Add("Status", typeof(string));
            trips.Columns.Add("PolicyID", typeof(int));

            trips.Rows.Add(1, "Paris City Tour", "Explore the beautiful city of Paris", "Paris", 5, "City Tour",
                          new DateTime(2025, 5, 15), new DateTime(2025, 5, 20), 1200m, 25, "Easy", "Active", 1);
            trips.Rows.Add(2, "Alpine Adventure", "Challenging mountain hikes", "Swiss Alps", 4, "Mountain",
                          new DateTime(2025, 6, 10), new DateTime(2025, 6, 17), 1800m, 15, "Hard", "Active", 1);
            trips.Rows.Add(3, "Safari Experience", "African wildlife safari", "Serengeti", 6, "Wildlife",
                          new DateTime(2025, 7, 5), new DateTime(2025, 7, 12), 2500m, 12, "Medium", "Active", 1);

            dgvTrips.DataSource = trips;
            FormatTripsGrid();
        }
    }
}