ProfileService.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 H1
{
    public partial class ProfileService : Form
    {
        public TravelerProfile GetTravelerProfile(int userId)
        {
            TravelerProfile profile = null;

            string query = @"SELECT tp.profile_id, tp.user_id, tp.first_name, tp.last_name, u.email, 
                           tp.phone, tp.profile_picture, tp.date_of_birth, tp.nationality, tp.created_at 
                           FROM TravelerProfile tp 
                           JOIN AppUser u ON tp.user_id = u.user_id 
                           WHERE tp.user_id = @UserId";

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserId", userId)
            };

            DataTable result = DatabaseUtil.ExecuteDataTable(query, CommandType.Text, parameters);

            if (result.Rows.Count > 0)
            {
                DataRow row = result.Rows[0];
                profile = new TravelerProfile
                {
                    ProfileId = Convert.ToInt32(row["profile_id"]),
                    UserId = Convert.ToInt32(row["user_id"]),
                    FirstName = row["first_name"].ToString(),
                    LastName = row["last_name"].ToString(),
                    Email = row["email"].ToString(),
                    Phone = row["phone"].ToString(),
                    ProfilePicture = row["profile_picture"] != DBNull.Value ? row["profile_picture"].ToString() : null,
                    DateOfBirth = row["date_of_birth"] != DBNull.Value ? Convert.ToDateTime(row["date_of_birth"]) : (DateTime?)null,
                    Nationality = row["nationality"] != DBNull.Value ? row["nationality"].ToString() : null,
                    CreatedAt = Convert.ToDateTime(row["created_at"]),
                    Preferences = GetTravelerPreferences(userId)
                };
            }

            return profile;
        }

        // Get traveler preferences by user ID
        public List<string> GetTravelerPreferences(int userId)
        {
            List<string> preferences = new List<string>();

            string query = @"SELECT preference_value 
                           FROM TravelerPreference 
                           WHERE user_id = @UserId AND preference_type = 'Category'";

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserId", userId)
            };

            DataTable result = DatabaseUtil.ExecuteDataTable(query, CommandType.Text, parameters);

            foreach (DataRow row in result.Rows)
            {
                preferences.Add(row["preference_value"].ToString());
            }

            return preferences;
        }

        // Update traveler profile
        public bool UpdateTravelerProfile(TravelerProfile profile)
        {
            string query = @"UPDATE TravelerProfile 
                           SET first_name = @FirstName, 
                               last_name = @LastName, 
                               phone = @Phone, 
                               date_of_birth = @DOB, 
                               nationality = @Nationality 
                           WHERE user_id = @UserId";

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@FirstName", profile.FirstName),
                new SqlParameter("@LastName", profile.LastName),
                new SqlParameter("@Phone", profile.Phone),
                new SqlParameter("@DOB", profile.DateOfBirth ?? (object)DBNull.Value),
                new SqlParameter("@Nationality", !string.IsNullOrEmpty(profile.Nationality) ? profile.Nationality : (object)DBNull.Value),
                new SqlParameter("@UserId", profile.UserId)
            };

            int result = DatabaseUtil.ExecuteNonQuery(query, CommandType.Text, parameters);

            if (result > 0)
            {
                // Update preferences
                return UpdateTravelerPreferences(profile.UserId, profile.Preferences);
            }

            return false;
        }

        // Update traveler preferences
        public bool UpdateTravelerPreferences(int userId, List<string> preferences)
        {
            // First, delete existing preferences
            string deleteQuery = "DELETE FROM TravelerPreference WHERE user_id = @UserId AND preference_type = 'Category'";

            SqlParameter[] deleteParams = new SqlParameter[]
            {
                new SqlParameter("@UserId", userId)
            };

            DatabaseUtil.ExecuteNonQuery(deleteQuery, CommandType.Text, deleteParams);

            // Then, insert new preferences
            foreach (string preference in preferences)
            {
                string insertQuery = @"INSERT INTO TravelerPreference (user_id, preference_type, preference_value) 
                                     VALUES (@UserId, 'Category', @Value)";

                SqlParameter[] insertParams = new SqlParameter[]
                {
                    new SqlParameter("@UserId", userId),
                    new SqlParameter("@Value", preference)
                };

                DatabaseUtil.ExecuteNonQuery(insertQuery, CommandType.Text, insertParams);
            }

            return true;
        }
    }
}