<?php
/**
 * AIMS Profile Management Class
 * Complete Facebook-like profile functionality
 * File: classes/Profile.php
 * Student: Musumba Jonathan (21/BCS/010/UMC)
 */

namespace App;

use PDO;
use PDOException;

class Profile {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    /**
     * Get complete user profile with privacy filters
     */
    public function getProfile($userId, $viewerId = null) {
        try {
            // Set session variables for stored procedure
            if ($viewerId) {
                $this->db->exec("SET @current_ip_address = '" . ($_SERVER['REMOTE_ADDR'] ?? '0.0.0.0') . "'");
                $this->db->exec("SET @current_user_agent = '" . ($_SERVER['HTTP_USER_AGENT'] ?? 'Unknown') . "'");
            }
            
            $sql = "CALL GetUserProfile(?, ?)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $viewerId]);
            
            $profile = $stmt->fetch(PDO::FETCH_ASSOC);
            $stmt->closeCursor(); // Important: close cursor after stored procedure
            
            if ($profile) {
                // Add default values for optional fields to prevent undefined key warnings
                $profile['whatsapp_number'] = $profile['whatsapp_number'] ?? '';
                $profile['website'] = $profile['website'] ?? '';
                $profile['secondary_phone'] = $profile['secondary_phone'] ?? '';
                $profile['facebook_url'] = $profile['facebook_url'] ?? '';
                $profile['twitter_url'] = $profile['twitter_url'] ?? '';
                $profile['instagram_url'] = $profile['instagram_url'] ?? '';
                $profile['linkedin_url'] = $profile['linkedin_url'] ?? '';
                $profile['youtube_url'] = $profile['youtube_url'] ?? '';
                $profile['tagline'] = $profile['tagline'] ?? '';
                $profile['bio'] = $profile['bio'] ?? '';
                $profile['about_me'] = $profile['about_me'] ?? '';
                
                // Ensure photos are properly loaded from profile_photos table
                if (!isset($profile['profile_picture']) || empty($profile['profile_picture'])) {
                    $sql = "SELECT photo_url FROM profile_photos 
                            WHERE user_id = ? AND photo_type = 'profile' AND is_current = 1 
                            LIMIT 1";
                    $stmt = $this->db->prepare($sql);
                    $stmt->execute([$userId]);
                    $currentProfile = $stmt->fetch(PDO::FETCH_ASSOC);
                    if ($currentProfile) {
                        $profile['profile_picture'] = $currentProfile['photo_url'];
                    }
                }
                
                if (!isset($profile['cover_photo']) || empty($profile['cover_photo'])) {
                    $sql = "SELECT photo_url FROM profile_photos 
                            WHERE user_id = ? AND photo_type = 'cover' AND is_current = 1 
                            LIMIT 1";
                    $stmt = $this->db->prepare($sql);
                    $stmt->execute([$userId]);
                    $currentCover = $stmt->fetch(PDO::FETCH_ASSOC);
                    if ($currentCover) {
                        $profile['cover_photo'] = $currentCover['photo_url'];
                    }
                }
                
                // Get additional profile data
                $profile['photos'] = $this->getProfilePhotos($userId);
                $profile['achievements'] = $this->getUserAchievements($userId);
                $profile['experience'] = $this->getUserExperience($userId);
                $profile['education'] = $this->getUserEducation($userId);
                $profile['certifications'] = $this->getUserCertifications($userId);
                $profile['recommendations'] = $this->getUserRecommendations($userId);
                $profile['life_events'] = $this->getUserLifeEvents($userId);
                
                // Parse JSON fields
                if (isset($profile['interests'])) {
                    $profile['interests'] = json_decode($profile['interests'], true) ?? [];
                }
                if (isset($profile['skills'])) {
                    $profile['skills'] = json_decode($profile['skills'], true) ?? [];
                }
                if (isset($profile['languages'])) {
                    $profile['languages'] = json_decode($profile['languages'], true) ?? [];
                }
                if (isset($profile['crops_grown'])) {
                    $profile['crops_grown'] = json_decode($profile['crops_grown'], true) ?? [];
                }
                if (isset($profile['livestock_kept'])) {
                    $profile['livestock_kept'] = json_decode($profile['livestock_kept'], true) ?? [];
                }
            }
            
            return $profile;
            
        } catch (PDOException $e) {
            error_log("Get profile error: " . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Update user profile
     */
    public function updateProfile($userId, $data) {
        try {
            $this->db->beginTransaction();
            
            // Update basic user info
            $sql = "UPDATE users SET 
                    full_name = ?,
                    email = ?,
                    region_id = ?,
                    district_id = ?,
                    subcounty_id = ?,
                    parish_id = ?,
                    village_id = ?,
                    updated_at = CURRENT_TIMESTAMP
                    WHERE id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['full_name'] ?? '',
                $data['email'] ?? null,
                $data['region_id'] ?? null,
                $data['district_id'] ?? null,
                $data['subcounty_id'] ?? null,
                $data['parish_id'] ?? null,
                $data['village_id'] ?? null,
                $userId
            ]);
            
            // Update extended profile
            $sql = "UPDATE user_profiles SET 
                    bio = ?,
                    tagline = ?,
                    about_me = ?,
                    date_of_birth = ?,
                    gender = ?,
                    secondary_phone = ?,
                    whatsapp_number = ?,
                    website = ?,
                    occupation = ?,
                    education_level = ?,
                    years_in_farming = ?,
                    farm_size_acres = ?,
                    farming_type = ?,
                    facebook_url = ?,
                    twitter_url = ?,
                    instagram_url = ?,
                    linkedin_url = ?,
                    youtube_url = ?,
                    interests = ?,
                    skills = ?,
                    languages = ?,
                    crops_grown = ?,
                    livestock_kept = ?
                    WHERE user_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['bio'] ?? null,
                $data['tagline'] ?? null,
                $data['about_me'] ?? null,
                $data['date_of_birth'] ?? null,
                $data['gender'] ?? null,
                $data['secondary_phone'] ?? null,
                $data['whatsapp_number'] ?? null,
                $data['website'] ?? null,
                $data['occupation'] ?? null,
                $data['education_level'] ?? null,
                $data['years_in_farming'] ?? 0,
                $data['farm_size_acres'] ?? 0,
                $data['farming_type'] ?? 'mixed',
                $data['facebook_url'] ?? null,
                $data['twitter_url'] ?? null,
                $data['instagram_url'] ?? null,
                $data['linkedin_url'] ?? null,
                $data['youtube_url'] ?? null,
                isset($data['interests']) ? json_encode($data['interests']) : null,
                isset($data['skills']) ? json_encode($data['skills']) : null,
                isset($data['languages']) ? json_encode($data['languages']) : null,
                isset($data['crops_grown']) ? json_encode($data['crops_grown']) : null,
                isset($data['livestock_kept']) ? json_encode($data['livestock_kept']) : null,
                $userId
            ]);
            
            $this->db->commit();
            
            return ['success' => true, 'message' => 'Profile updated successfully'];
            
        } catch (PDOException $e) {
            $this->db->rollback();
            error_log("Update profile error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to update profile'];
        }
    }
    
    /**
     * Update privacy settings
     */
    public function updatePrivacySettings($userId, $settings) {
        try {
            $sql = "UPDATE user_profiles SET 
                    show_phone = ?,
                    show_location = ?,
                    show_email = ?,
                    show_birthday = ?,
                    allow_messages = ?,
                    allow_friend_requests = ?,
                    profile_visibility = ?
                    WHERE user_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $settings['show_phone'] ?? 1,
                $settings['show_location'] ?? 1,
                $settings['show_email'] ?? 1,
                $settings['show_birthday'] ?? 0,
                $settings['allow_messages'] ?? 1,
                $settings['allow_friend_requests'] ?? 1,
                $settings['profile_visibility'] ?? 'public',
                $userId
            ]);
            
            return ['success' => true, 'message' => 'Privacy settings updated'];
            
        } catch (PDOException $e) {
            error_log("Update privacy error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to update privacy settings'];
        }
    }
    
    /**
     * Update notification preferences
     */
    public function updateNotificationPreferences($userId, $prefs) {
        try {
            $sql = "UPDATE user_preferences SET 
                    email_notifications = ?,
                    sms_notifications = ?,
                    push_notifications = ?,
                    friend_request_notifications = ?,
                    message_notifications = ?,
                    post_notifications = ?
                    WHERE user_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $prefs['email_notifications'] ?? 1,
                $prefs['sms_notifications'] ?? 1,
                $prefs['push_notifications'] ?? 1,
                $prefs['friend_request_notifications'] ?? 1,
                $prefs['message_notifications'] ?? 1,
                $prefs['post_notifications'] ?? 1,
                $userId
            ]);
            
            return ['success' => true, 'message' => 'Notification preferences updated'];
            
        } catch (PDOException $e) {
            error_log("Update preferences error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to update preferences'];
        }
    }
    
    /**
     * Upload profile picture
     */
    public function uploadProfilePicture($userId, $file) {
        try {
            $upload = $this->handleImageUpload($file, 'profile');
            
            if (!$upload['success']) {
                return $upload;
            }
            
            $this->db->beginTransaction();
            
            // Mark previous profile photos as not current
            $sql = "UPDATE profile_photos SET is_current = 0 
                    WHERE user_id = ? AND photo_type = 'profile'";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            // Insert new profile photo
            $sql = "INSERT INTO profile_photos (user_id, photo_url, photo_type, is_current) 
                    VALUES (?, ?, 'profile', 1)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $upload['file_path']]);
            
            // Update users table
            $sql = "UPDATE users SET profile_picture = ? WHERE id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$upload['file_path'], $userId]);
            
            $this->db->commit();
            
            return [
                'success' => true, 
                'message' => 'Profile picture updated',
                'photo_url' => $upload['file_path']
            ];
            
        } catch (PDOException $e) {
            $this->db->rollback();
            error_log("Upload profile picture error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to upload profile picture'];
        }
    }
    
    /**
     * Upload cover photo
     */
    public function uploadCoverPhoto($userId, $file) {
        try {
            $upload = $this->handleImageUpload($file, 'cover');
            
            if (!$upload['success']) {
                return $upload;
            }
            
            $this->db->beginTransaction();
            
            // Mark previous cover photos as not current
            $sql = "UPDATE profile_photos SET is_current = 0 
                    WHERE user_id = ? AND photo_type = 'cover'";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            // Insert new cover photo
            $sql = "INSERT INTO profile_photos (user_id, photo_url, photo_type, is_current) 
                    VALUES (?, ?, 'cover', 1)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $upload['file_path']]);
            
            // Update user_profiles table
            $sql = "UPDATE user_profiles SET cover_photo = ? WHERE user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$upload['file_path'], $userId]);
            
            $this->db->commit();
            
            return [
                'success' => true, 
                'message' => 'Cover photo updated',
                'photo_url' => $upload['file_path']
            ];
            
        } catch (PDOException $e) {
            $this->db->rollback();
            error_log("Upload cover photo error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to upload cover photo'];
        }
    }
    
    /**
     * Add photo to gallery
     */
    public function addGalleryPhoto($userId, $file, $caption = null) {
        try {
            $upload = $this->handleImageUpload($file, 'gallery');
            
            if (!$upload['success']) {
                return $upload;
            }
            
            $sql = "INSERT INTO profile_photos (user_id, photo_url, photo_type, caption) 
                    VALUES (?, ?, 'gallery', ?)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $upload['file_path'], $caption]);
            
            return [
                'success' => true, 
                'message' => 'Photo added to gallery',
                'photo_id' => $this->db->lastInsertId(),
                'photo_url' => $upload['file_path']
            ];
            
        } catch (PDOException $e) {
            error_log("Add gallery photo error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to add photo'];
        }
    }
    
    /**
     * Get profile photos
     */
    public function getProfilePhotos($userId, $type = null, $limit = null) {
        try {
            if ($type) {
                $sql = "SELECT * FROM profile_photos 
                        WHERE user_id = ? AND photo_type = ? 
                        ORDER BY upload_date DESC";
                if ($limit) {
                    $sql .= " LIMIT " . (int)$limit;
                }
                $stmt = $this->db->prepare($sql);
                $stmt->execute([$userId, $type]);
            } else {
                $sql = "SELECT * FROM profile_photos 
                        WHERE user_id = ? 
                        ORDER BY photo_type, upload_date DESC";
                if ($limit) {
                    $sql .= " LIMIT " . (int)$limit;
                }
                $stmt = $this->db->prepare($sql);
                $stmt->execute([$userId]);
            }
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get profile photos error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Delete photo
     */
    public function deletePhoto($userId, $photoId) {
        try {
            // Get photo details
            $sql = "SELECT * FROM profile_photos WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$photoId, $userId]);
            $photo = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$photo) {
                return ['success' => false, 'message' => 'Photo not found'];
            }
            
            // Delete file from server
            $filePath = __DIR__ . '/../' . $photo['photo_url'];
            if (file_exists($filePath)) {
                unlink($filePath);
            }
            
            // Delete from database
            $sql = "DELETE FROM profile_photos WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$photoId, $userId]);
            
            return ['success' => true, 'message' => 'Photo deleted'];
            
        } catch (PDOException $e) {
            error_log("Delete photo error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to delete photo'];
        }
    }
    
    /**
     * Like/Unlike photo
     */
    public function likePhoto($photoId, $userId) {
        try {
            // Check if already liked
            $sql = "SELECT id FROM photo_likes WHERE photo_id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$photoId, $userId]);
            
            if ($stmt->fetch()) {
                // Unlike
                $sql = "DELETE FROM photo_likes WHERE photo_id = ? AND user_id = ?";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([$photoId, $userId]);
                
                return ['success' => true, 'liked' => false, 'message' => 'Photo unliked'];
            } else {
                // Like
                $sql = "INSERT INTO photo_likes (photo_id, user_id) VALUES (?, ?)";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([$photoId, $userId]);
                
                return ['success' => true, 'liked' => true, 'message' => 'Photo liked'];
            }
            
        } catch (PDOException $e) {
            error_log("Like photo error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to like photo'];
        }
    }
    
    /**
     * Add work experience
     */
    public function addExperience($userId, $data) {
        try {
            $sql = "INSERT INTO user_experience 
                    (user_id, title, organization, location, start_date, end_date, is_current, description, employment_type) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $userId,
                $data['title'],
                $data['organization'] ?? null,
                $data['location'] ?? null,
                $data['start_date'] ?? null,
                $data['end_date'] ?? null,
                $data['is_current'] ?? 0,
                $data['description'] ?? null,
                $data['employment_type'] ?? 'full_time'
            ]);
            
            return [
                'success' => true, 
                'message' => 'Experience added',
                'id' => $this->db->lastInsertId()
            ];
            
        } catch (PDOException $e) {
            error_log("Add experience error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to add experience'];
        }
    }
    
    /**
     * Get user experience
     */
    public function getUserExperience($userId) {
        try {
            $sql = "SELECT * FROM user_experience 
                    WHERE user_id = ? 
                    ORDER BY is_current DESC, start_date DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get experience error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Update experience
     */
    public function updateExperience($userId, $expId, $data) {
        try {
            $sql = "UPDATE user_experience SET 
                    title = ?,
                    organization = ?,
                    location = ?,
                    start_date = ?,
                    end_date = ?,
                    is_current = ?,
                    description = ?,
                    employment_type = ?
                    WHERE id = ? AND user_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $data['title'],
                $data['organization'] ?? null,
                $data['location'] ?? null,
                $data['start_date'] ?? null,
                $data['end_date'] ?? null,
                $data['is_current'] ?? 0,
                $data['description'] ?? null,
                $data['employment_type'] ?? 'full_time',
                $expId,
                $userId
            ]);
            
            return ['success' => true, 'message' => 'Experience updated'];
            
        } catch (PDOException $e) {
            error_log("Update experience error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to update experience'];
        }
    }
    
    /**
     * Delete experience
     */
    public function deleteExperience($userId, $expId) {
        try {
            $sql = "DELETE FROM user_experience WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$expId, $userId]);
            
            return ['success' => true, 'message' => 'Experience deleted'];
            
        } catch (PDOException $e) {
            error_log("Delete experience error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to delete experience'];
        }
    }
    
    /**
     * Add education
     */
    public function addEducation($userId, $data) {
        try {
            $sql = "INSERT INTO user_education 
                    (user_id, institution, degree, field_of_study, start_year, end_year, grade, description) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $userId,
                $data['institution'],
                $data['degree'] ?? null,
                $data['field_of_study'] ?? null,
                $data['start_year'] ?? null,
                $data['end_year'] ?? null,
                $data['grade'] ?? null,
                $data['description'] ?? null
            ]);
            
            return [
                'success' => true, 
                'message' => 'Education added',
                'id' => $this->db->lastInsertId()
            ];
            
        } catch (PDOException $e) {
            error_log("Add education error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to add education'];
        }
    }
    
    /**
     * Get user education
     */
    public function getUserEducation($userId) {
        try {
            $sql = "SELECT * FROM user_education 
                    WHERE user_id = ? 
                    ORDER BY end_year DESC, start_year DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get education error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Delete education
     */
    public function deleteEducation($userId, $eduId) {
        try {
            $sql = "DELETE FROM user_education WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$eduId, $userId]);
            
            return ['success' => true, 'message' => 'Education deleted'];
            
        } catch (PDOException $e) {
            error_log("Delete education error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to delete education'];
        }
    }
    
    /**
     * Add certification
     */
    public function addCertification($userId, $data) {
        try {
            $sql = "INSERT INTO user_certifications 
                    (user_id, certification_name, issuing_organization, issue_date, expiry_date, credential_id, credential_url) 
                    VALUES (?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $userId,
                $data['certification_name'],
                $data['issuing_organization'] ?? null,
                $data['issue_date'] ?? null,
                $data['expiry_date'] ?? null,
                $data['credential_id'] ?? null,
                $data['credential_url'] ?? null
            ]);
            
            return [
                'success' => true, 
                'message' => 'Certification added',
                'id' => $this->db->lastInsertId()
            ];
            
        } catch (PDOException $e) {
            error_log("Add certification error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to add certification'];
        }
    }
    
    /**
     * Get user certifications
     */
    public function getUserCertifications($userId) {
        try {
            $sql = "SELECT * FROM user_certifications 
                    WHERE user_id = ? 
                    ORDER BY issue_date DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get certifications error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Delete certification
     */
    public function deleteCertification($userId, $certId) {
        try {
            $sql = "DELETE FROM user_certifications WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$certId, $userId]);
            
            return ['success' => true, 'message' => 'Certification deleted'];
            
        } catch (PDOException $e) {
            error_log("Delete certification error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to delete certification'];
        }
    }
    
    /**
     * Add recommendation
     */
    public function addRecommendation($userId, $recommenderId, $text, $relationship) {
        try {
            $sql = "INSERT INTO user_recommendations 
                    (user_id, recommender_id, recommendation_text, relationship, status) 
                    VALUES (?, ?, ?, ?, 'pending')";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $recommenderId, $text, $relationship]);
            
            return [
                'success' => true, 
                'message' => 'Recommendation sent for approval',
                'id' => $this->db->lastInsertId()
            ];
            
        } catch (PDOException $e) {
            error_log("Add recommendation error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to add recommendation'];
        }
    }
    
    /**
     * Approve recommendation
     */
    public function approveRecommendation($userId, $recId) {
        try {
            $sql = "UPDATE user_recommendations 
                    SET status = 'approved' 
                    WHERE id = ? AND user_id = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$recId, $userId]);
            
            return ['success' => true, 'message' => 'Recommendation approved'];
            
        } catch (PDOException $e) {
            error_log("Approve recommendation error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to approve recommendation'];
        }
    }
    
    /**
     * Get user recommendations
     */
    public function getUserRecommendations($userId, $status = 'approved') {
        try {
            $sql = "SELECT r.*, u.full_name as recommender_name, u.profile_picture as recommender_photo
                    FROM user_recommendations r
                    JOIN users u ON r.recommender_id = u.id
                    WHERE r.user_id = ? AND r.status = ?
                    ORDER BY r.created_at DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $status]);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get recommendations error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Add life event
     */
    public function addLifeEvent($userId, $data) {
        try {
            $sql = "INSERT INTO user_life_events 
                    (user_id, event_type, event_title, event_description, event_date, visibility) 
                    VALUES (?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $userId,
                $data['event_type'],
                $data['event_title'],
                $data['event_description'] ?? null,
                $data['event_date'] ?? date('Y-m-d'),
                $data['visibility'] ?? 'public'
            ]);
            
            return [
                'success' => true, 
                'message' => 'Life event added',
                'id' => $this->db->lastInsertId()
            ];
            
        } catch (PDOException $e) {
            error_log("Add life event error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to add life event'];
        }
    }
    
    /**
     * Get user life events
     */
    public function getUserLifeEvents($userId) {
        try {
            $sql = "SELECT * FROM user_life_events 
                    WHERE user_id = ? 
                    ORDER BY event_date DESC, created_at DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get life events error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get user achievements
     */
    public function getUserAchievements($userId) {
        try {
            $sql = "SELECT * FROM user_achievements 
                    WHERE user_id = ? 
                    ORDER BY earned_at DESC";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get achievements error: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Award achievement to user
     */
    public function awardAchievement($userId) {
        try {
            $sql = "CALL AwardAchievements(?)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId]);
            $stmt->closeCursor();
            
            return ['success' => true];
            
        } catch (PDOException $e) {
            error_log("Award achievement error: " . $e->getMessage());
            return ['success' => false];
        }
    }
    
    /**
     * Get profile statistics
     */
    public function getProfileStats($userId) {
        try {
            $sql = "SELECT 
                        COALESCE(profile_views, 0) as profile_views,
                        COALESCE(profile_completeness, 0) as profile_completeness,
                        (SELECT COUNT(*) FROM friendships 
                         WHERE (user_id = ? OR friend_id = ?) 
                         AND status = 'accepted') as friends_count,
                        (SELECT COUNT(*) FROM products 
                         WHERE seller_id = ? AND status = 'available') as products_count,
                        (SELECT COUNT(*) FROM posts 
                         WHERE user_id = ?) as posts_count,
                        (SELECT COALESCE(AVG(rating), 0) 
                         FROM reviews WHERE reviewed_user_id = ?) as avg_rating,
                        (SELECT COUNT(*) FROM reviews 
                         WHERE reviewed_user_id = ?) as reviews_count,
                        (SELECT COUNT(*) FROM profile_photos 
                         WHERE user_id = ? AND photo_type = 'gallery') as photos_count
                    FROM user_profiles WHERE user_id = ?";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $userId, $userId, $userId, $userId, $userId, $userId, $userId]);
            
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // Return default values if no stats found
            if (!$stats) {
                return [
                    'profile_views' => 0,
                    'profile_completeness' => 0,
                    'friends_count' => 0,
                    'products_count' => 0,
                    'posts_count' => 0,
                    'avg_rating' => 0,
                    'reviews_count' => 0,
                    'photos_count' => 0
                ];
            }
            
            return $stats;
            
        } catch (PDOException $e) {
            error_log("Get profile stats error: " . $e->getMessage());
            // Return default values on error
            return [
                'profile_views' => 0,
                'profile_completeness' => 0,
                'friends_count' => 0,
                'products_count' => 0,
                'posts_count' => 0,
                'avg_rating' => 0,
                'reviews_count' => 0,
                'photos_count' => 0
            ];
        }
    }
    
    /**
     * Handle image upload
     */
    private function handleImageUpload($file, $type) {
        $allowedTypes = ['image/jpeg', 'image/jpg', 'image/png', 'image/gif'];
        $maxSize = 5 * 1024 * 1024; // 5MB
        
        if (!in_array($file['type'], $allowedTypes)) {
            return ['success' => false, 'message' => 'Invalid file type. Only JPG, PNG, and GIF allowed'];
        }
        
        if ($file['size'] > $maxSize) {
            return ['success' => false, 'message' => 'File too large. Maximum 5MB allowed'];
        }
        
        // Get image dimensions
        $imageInfo = getimagesize($file['tmp_name']);
        if (!$imageInfo) {
            return ['success' => false, 'message' => 'Invalid image file'];
        }
        
        list($width, $height) = $imageInfo;
        
        // Facebook-like minimum dimensions
        if ($type === 'cover') {
            // Cover photo minimum: 820px wide x 312px tall
            if ($width < 820 || $height < 312) {
                return ['success' => false, 'message' => 'Cover photo must be at least 820x312 pixels. Current size: ' . $width . 'x' . $height];
            }
        } elseif ($type === 'profile') {
            // Profile photo minimum: 180px x 180px
            if ($width < 180 || $height < 180) {
                return ['success' => false, 'message' => 'Profile photo must be at least 180x180 pixels. Current size: ' . $width . 'x' . $height];
            }
        }
        
        $uploadDir = defined('UPLOAD_PATH') ? UPLOAD_PATH . 'profiles/' : __DIR__ . '/../assets/uploads/profiles/';
        if (!is_dir($uploadDir)) {
            mkdir($uploadDir, 0755, true);
        }
        
        $extension = pathinfo($file['name'], PATHINFO_EXTENSION);
        $filename = uniqid($type . '_') . '.' . $extension;
        $filepath = $uploadDir . $filename;
        
        if (move_uploaded_file($file['tmp_name'], $filepath)) {
            return [
                'success' => true, 
                'file_path' => 'assets/uploads/profiles/' . $filename
            ];
        }
        
        return ['success' => false, 'message' => 'Failed to upload file'];
    }
    
    /**
     * Save item to collection
     */
    public function saveItem($userId, $itemType, $itemId, $collectionName = 'Saved Items', $notes = null) {
        try {
            $sql = "INSERT INTO user_saved_items (user_id, item_type, item_id, collection_name, notes) 
                    VALUES (?, ?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE collection_name = ?, notes = ?";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([$userId, $itemType, $itemId, $collectionName, $notes, $collectionName, $notes]);
            
            return ['success' => true, 'message' => 'Item saved'];
            
        } catch (PDOException $e) {
            error_log("Save item error: " . $e->getMessage());
            return ['success' => false, 'message' => 'Failed to save item'];
        }
    }
    
    /**
     * Get saved items
     */
    public function getSavedItems($userId, $itemType = null, $collectionName = null) {
        try {
            $sql = "SELECT * FROM user_saved_items WHERE user_id = ?";
            $params = [$userId];
            
            if ($itemType) {
                $sql .= " AND item_type = ?";
                $params[] = $itemType;
            }
            
            if ($collectionName) {
                $sql .= " AND collection_name = ?";
                $params[] = $collectionName;
            }
            
            $sql .= " ORDER BY saved_at DESC";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);
            
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
            
        } catch (PDOException $e) {
            error_log("Get saved items error: " . $e->getMessage());
            return [];
        }
    }
}