<?php
require_once __DIR__ . '/../../../../helpers/response.php';
require_once __DIR__ . '/../../../Models/Database.php';

class MarketplaceController {
    
    public function search() {
        $db = Database::getInstance();
        
        $searchTerm = $_GET['searchTerm'] ?? '';
        $type = $_GET['type'] ?? 'all'; // all, maids, agencies
        
        // Pagination
        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;
        
        // Sorting logic
        $sortBy = $_GET['sortBy'] ?? 'best_match';
        
        // Parse filter lists helper
        $parseIds = function($input) {
            if (empty($input)) return [];
            if (is_array($input)) return array_map('intval', $input);
            return array_filter(array_map('intval', explode(',', $input)));
        };
        
        $nationalityIds = $parseIds($_GET['nationality_ids'] ?? $_GET['nationality_id'] ?? null);
        $specialtyIds = $parseIds($_GET['specialty_ids'] ?? $_GET['specialty_id'] ?? null);
        $skillIds = $parseIds($_GET['skill_ids'] ?? $_GET['skill_id'] ?? null);
        $languageIds = $parseIds($_GET['language_ids'] ?? $_GET['language_id'] ?? null);
        $cityIds = $parseIds($_GET['city_ids'] ?? $_GET['city_id'] ?? null);
        
        $salaryMin = (isset($_GET['salary_min']) && $_GET['salary_min'] !== '') ? $_GET['salary_min'] : null;
        $salaryMax = (isset($_GET['salary_max']) && $_GET['salary_max'] !== '') ? $_GET['salary_max'] : null;
        $experienceMin = (isset($_GET['experience_min']) && $_GET['experience_min'] !== '') ? $_GET['experience_min'] : null;
        $experienceMax = (isset($_GET['experience_max']) && $_GET['experience_max'] !== '') ? $_GET['experience_max'] : null;
        $contractType = (isset($_GET['contract_type']) && $_GET['contract_type'] !== '') ? $_GET['contract_type'] : null;
        
        // Features & Package Includes
        $visaIncluded = (isset($_GET['visa_included']) && $_GET['visa_included'] !== '') ? intval($_GET['visa_included']) : null;
        $referenceChecked = (isset($_GET['reference_checked']) && $_GET['reference_checked'] !== '') ? intval($_GET['reference_checked']) : null;
        $availableNow = (isset($_GET['available_now']) && $_GET['available_now'] !== '') ? intval($_GET['available_now']) : 1; // default to showing active available by default
        $availableSoon = (isset($_GET['available_soon']) && $_GET['available_soon'] !== '') ? intval($_GET['available_soon']) : null;
        
        $packageIncludesInput = $_GET['package_includes'] ?? '';
        $packageIncludes = is_array($packageIncludesInput) ? $packageIncludesInput : array_filter(explode(',', $packageIncludesInput));
        if (isset($_GET['visa_processing']) && $_GET['visa_processing'] == 1) $packageIncludes[] = 'visa_processing';
        if (isset($_GET['full_insurance']) && $_GET['full_insurance'] == 1) $packageIncludes[] = 'full_insurance';
        if (isset($_GET['accommodation']) && $_GET['accommodation'] == 1) $packageIncludes[] = 'accommodation';
        if (isset($_GET['annual_leave']) && $_GET['annual_leave'] == 1) $packageIncludes[] = 'annual_leave';
        if (isset($_GET['mohre_registration']) && $_GET['mohre_registration'] == 1) $packageIncludes[] = 'mohre_registration';
        $packageIncludes = array_unique($packageIncludes);
        
        $data = [];
        $pagination = null;
        
        // Search categories/specialties if type is 'all' or 'categories'
        if ($type === 'all' || $type === 'categories') {
            if (!empty($searchTerm)) {
                $catStmt = $db->prepare("
                    SELECT id, name_en, name_ar, icon
                    FROM specialties
                    WHERE status = 'active' AND (name_en LIKE ? OR name_ar LIKE ?)
                    LIMIT 5
                ");
                $catStmt->execute(["%$searchTerm%", "%$searchTerm%"]);
                $data['categories'] = $catStmt->fetchAll();
            } else {
                $data['categories'] = $db->query("
                    SELECT id, name_en, name_ar, icon
                    FROM specialties
                    WHERE status = 'active'
                    LIMIT 10
                ")->fetchAll();
            }
        }
        
        // 1. HELPERS / MAIDS SEARCH
        if ($type === 'all' || $type === 'maids') {
            $where = ["m.status = 'published'", "v.status = 'approved'"];
            $params = [];
            $joinSql = "";
            
            // Text Search matching name, bio, nationality, or agency name
            if (!empty($searchTerm)) {
                $words = array_filter(explode(' ', $searchTerm));
                $termConditions = [];
                foreach ($words as $word) {
                    $termConditions[] = "(m.full_name LIKE ? OR m.bio LIKE ? OR n.name_en LIKE ? OR n.name_ar LIKE ? OR v.agency_name LIKE ?)";
                    $params[] = "%$word%";
                    $params[] = "%$word%";
                    $params[] = "%$word%";
                    $params[] = "%$word%";
                    $params[] = "%$word%";
                }
                if (!empty($termConditions)) {
                    $where[] = "(" . implode(" AND ", $termConditions) . ")";
                }
            }
            
            // Availability filter
            if ($availableNow) {
                $where[] = "m.availability_status = 'available'";
            } elseif ($availableSoon) {
                $where[] = "m.availability_status != 'booked'";
            }
            
            // Filters
            if (!empty($nationalityIds)) {
                $where[] = "m.nationality_id IN (" . implode(',', $nationalityIds) . ")";
            }
            if (!empty($cityIds)) {
                $where[] = "v.city_id IN (" . implode(',', $cityIds) . ")";
            }
            
            if ($salaryMin !== null) {
                $where[] = "m.monthly_salary >= ?";
                $params[] = floatval($salaryMin);
            }
            if ($salaryMax !== null) {
                $where[] = "m.monthly_salary <= ?";
                $params[] = floatval($salaryMax);
            }
            
            if ($experienceMin !== null) {
                $where[] = "m.years_experience >= ?";
                $params[] = intval($experienceMin);
            }
            if ($experienceMax !== null) {
                $where[] = "m.years_experience <= ?";
                $params[] = intval($experienceMax);
            }
            
            if ($contractType) {
                $where[] = "EXISTS (SELECT 1 FROM service_models sm WHERE sm.id = m.service_model_id AND (sm.name_en LIKE ? OR sm.name_ar LIKE ?))";
                $params[] = "%$contractType%";
                $params[] = "%$contractType%";
            }
            
            // Feature / Package filters
            if ($visaIncluded || in_array('visa_processing', $packageIncludes)) {
                $where[] = "m.visa_status = 'sponsored'";
            }
            if ($referenceChecked) {
                $where[] = "m.emirates_id_verified = 1";
            }
            if (in_array('full_insurance', $packageIncludes) || in_array('mohre_registration', $packageIncludes)) {
                $where[] = "v.is_verified = 1";
            }
            if (in_array('accommodation', $packageIncludes)) {
                $where[] = "m.accommodation_type_id IS NOT NULL";
            }
            
            // Joins for relations
            if (!empty($specialtyIds)) {
                $joinSql .= " JOIN maid_specialties msp ON msp.maid_id = m.id AND msp.specialty_id IN (" . implode(',', $specialtyIds) . ")";
            }
            if (!empty($skillIds)) {
                $joinSql .= " JOIN maid_skills msk ON msk.maid_id = m.id AND msk.skill_id IN (" . implode(',', $skillIds) . ")";
            }
            if (!empty($languageIds)) {
                $joinSql .= " JOIN maid_languages mla ON mla.maid_id = m.id AND mla.language_id IN (" . implode(',', $languageIds) . ")";
            }
            
            // Count query
            $countSql = "
                SELECT COUNT(DISTINCT m.id)
                FROM maids m
                JOIN vendors v ON m.vendor_id = v.id
                LEFT JOIN nationalities n ON m.nationality_id = n.id
                {$joinSql}
                WHERE " . implode(" AND ", $where) . "
            ";
            $countStmt = $db->prepare($countSql);
            $countStmt->execute($params);
            $totalMaids = (int) $countStmt->fetchColumn();
            
            // Sorting
            $orderSql = "ORDER BY m.id DESC";
            if ($sortBy === 'lowest_price') {
                $orderSql = "ORDER BY m.monthly_salary ASC";
            } elseif ($sortBy === 'top_rated') {
                $orderSql = "ORDER BY rating DESC, reviews_count DESC";
            } elseif ($sortBy === 'best_match') {
                $orderSql = "ORDER BY rating DESC, m.views DESC";
            } elseif ($sortBy === 'newest') {
                $orderSql = "ORDER BY m.created_at DESC";
            }
            
            // Execute results query
            $limitSql = " LIMIT " . intval($limit) . " OFFSET " . intval($offset);
            $sql = "
                SELECT m.id, m.full_name, m.nationality_id, m.monthly_salary, m.years_experience, 
                       m.profile_image, m.views, m.bio, m.reliability_score, m.is_recommended,
                       COALESCE(AVG(r.rating), 5.0) as rating, COUNT(r.id) as reviews_count,
                       n.name_en as nationality_name, n.name_ar as nationality_name_ar,
                       v.agency_name
                FROM maids m
                JOIN vendors v ON m.vendor_id = v.id
                LEFT JOIN nationalities n ON m.nationality_id = n.id
                LEFT JOIN reviews r ON r.maid_id = m.id
                {$joinSql}
                WHERE " . implode(" AND ", $where) . "
                GROUP BY m.id
                {$orderSql}
                {$limitSql}
            ";
            
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            $maids = $stmt->fetchAll();
            
            // Load tags for each maid
            foreach ($maids as &$maid) {
                // Languages
                $langStmt = $db->prepare("
                    SELECT l.name_en, l.name_ar 
                    FROM maid_languages ml
                    JOIN languages l ON ml.language_id = l.id
                    WHERE ml.maid_id = ?
                ");
                $langStmt->execute([$maid['id']]);
                $langs = $langStmt->fetchAll();
                $maid['languages'] = array_column($langs, 'name_en');
                $maid['languages_ar'] = array_column($langs, 'name_ar');
                
                // Specialties
                $specStmt = $db->prepare("
                    SELECT s.name_en, s.name_ar 
                    FROM maid_specialties ms
                    JOIN specialties s ON ms.specialty_id = s.id
                    WHERE ms.maid_id = ?
                ");
                $specStmt->execute([$maid['id']]);
                $specs = $specStmt->fetchAll();
                $maid['specialties'] = array_column($specs, 'name_en');
                $maid['specialties_ar'] = array_column($specs, 'name_ar');
                
                // Formatting
                $maid['rating'] = floatval($maid['rating']);
                $maid['reviews_count'] = intval($maid['reviews_count']);
                $maid['monthly_salary'] = floatval($maid['monthly_salary']);
                $maid['years_experience'] = intval($maid['years_experience']);
                $maid['is_recommended'] = intval($maid['is_recommended']) === 1;
            }
            
            $data['maids'] = $maids;
            
            if ($type === 'maids') {
                $pagination = [
                    'total' => $totalMaids,
                    'page' => $page,
                    'limit' => $limit,
                    'total_pages' => ceil($totalMaids / $limit)
                ];
            }
        }
        
        // 2. AGENCIES / VENDORS SEARCH
        if ($type === 'all' || $type === 'agencies') {
            $vendorWhere = ["v.status = 'approved'"];
            $vendorParams = [];
            
            if (!empty($searchTerm)) {
                $words = array_filter(explode(' ', $searchTerm));
                $termConditions = [];
                foreach ($words as $word) {
                    $termConditions[] = "(v.agency_name LIKE ? OR v.description LIKE ?)";
                    $vendorParams[] = "%$word%";
                    $vendorParams[] = "%$word%";
                }
                if (!empty($termConditions)) {
                    $vendorWhere[] = "(" . implode(" AND ", $termConditions) . ")";
                }
            }
            
            if (!empty($cityIds)) {
                $vendorWhere[] = "v.city_id IN (" . implode(',', $cityIds) . ")";
            }
            
            if (!empty($specialtyIds)) {
                $vendorWhere[] = "EXISTS (
                    SELECT 1 FROM maid_specialties ms 
                    JOIN maids m ON ms.maid_id = m.id 
                    WHERE m.vendor_id = v.id AND m.status = 'published' AND ms.specialty_id IN (" . implode(',', $specialtyIds) . ")
                )";
            }
            
            if (in_array('full_insurance', $packageIncludes) || in_array('mohre_registration', $packageIncludes)) {
                $vendorWhere[] = "v.is_verified = 1";
            }
            if (in_array('visa_processing', $packageIncludes)) {
                $vendorWhere[] = "EXISTS (SELECT 1 FROM maids m WHERE m.vendor_id = v.id AND m.status = 'published' AND m.visa_status = 'sponsored')";
            }
            if (in_array('accommodation', $packageIncludes)) {
                $vendorWhere[] = "EXISTS (SELECT 1 FROM maids m WHERE m.vendor_id = v.id AND m.status = 'published' AND m.accommodation_type_id IS NOT NULL)";
            }
            
            // Count query
            $countVendorsSql = "
                SELECT COUNT(DISTINCT v.id)
                FROM vendors v
                WHERE " . implode(" AND ", $vendorWhere) . "
            ";
            $countVendorsStmt = $db->prepare($countVendorsSql);
            $countVendorsStmt->execute($vendorParams);
            $totalVendors = (int) $countVendorsStmt->fetchColumn();
            
            // Sorting
            $vendorOrder = "ORDER BY rating DESC, reviews_count DESC";
            if ($sortBy === 'lowest_price') {
                $vendorOrder = "ORDER BY starting_price ASC";
            }
            
            // Execute results query
            $limitSql = " LIMIT " . intval($limit) . " OFFSET " . intval($offset);
            $sql = "
                SELECT v.id, v.agency_name, v.tagline, v.logo, v.description, v.cover_image, v.is_verified,
                       COALESCE(AVG(r.rating), 5.0) as rating, COUNT(r.id) as reviews_count,
                       COALESCE((
                           SELECT MIN(m.monthly_salary)
                           FROM maids m
                           WHERE m.vendor_id = v.id AND m.status = 'published' AND m.availability_status = 'available'
                       ), 2500.00) as starting_price,
                       COALESCE((
                           SELECT COUNT(*) 
                           FROM maids m 
                           WHERE m.vendor_id = v.id AND m.status = 'published'
                       ), 0) as staff_count,
                       COALESCE((
                           SELECT ROUND(AVG(m.years_experience)) 
                           FROM maids m 
                           WHERE m.vendor_id = v.id AND m.status = 'published'
                       ), 5) as experience_years
                FROM vendors v
                LEFT JOIN reviews r ON r.vendor_id = v.id
                WHERE " . implode(" AND ", $vendorWhere) . "
                GROUP BY v.id
                {$vendorOrder}
                {$limitSql}
            ";
            
            $stmt = $db->prepare($sql);
            $stmt->execute($vendorParams);
            $vendors = $stmt->fetchAll();
            
            foreach ($vendors as &$vendor) {
                $vendor['rating'] = floatval($vendor['rating']);
                $vendor['reviews_count'] = intval($vendor['reviews_count']);
                $vendor['starting_price'] = floatval($vendor['starting_price']);
                $vendor['staff_count'] = intval($vendor['staff_count']);
                $vendor['experience_years'] = intval($vendor['experience_years']);
                $vendor['is_verified'] = intval($vendor['is_verified']) === 1;
                $vendor['insurance_coverage'] = $vendor['is_verified'];
                
                // Get dynamic tags (specialties of maids in this agency)
                $tagStmt = $db->prepare("
                    SELECT DISTINCT s.name_en, s.name_ar 
                    FROM maid_specialties ms
                    JOIN specialties s ON ms.specialty_id = s.id
                    JOIN maids m ON ms.maid_id = m.id
                    WHERE m.vendor_id = ? AND m.status = 'published'
                    LIMIT 2
                ");
                $tagStmt->execute([$vendor['id']]);
                $tags = $tagStmt->fetchAll();
                $vendor['tags'] = array_column($tags, 'name_en');
                $vendor['tags_ar'] = array_column($tags, 'name_ar');
            }
            
            $data['vendors'] = $vendors;
            
            if ($type === 'agencies') {
                $pagination = [
                    'total' => $totalVendors,
                    'page' => $page,
                    'limit' => $limit,
                    'total_pages' => ceil($totalVendors / $limit)
                ];
            }
        }
        
        jsonResponse(true, 'Search results loaded successfully', $data, $pagination);
    }
    
    public function listVendors() {
        $db = Database::getInstance();
        $searchTerm = $_GET['searchTerm'] ?? '';
        
        $packageIncludesInput = $_GET['package_includes'] ?? '';
        $packageIncludes = is_array($packageIncludesInput) ? $packageIncludesInput : array_filter(explode(',', $packageIncludesInput));
        if (isset($_GET['visa_processing']) && $_GET['visa_processing'] == 1) $packageIncludes[] = 'visa_processing';
        if (isset($_GET['full_insurance']) && $_GET['full_insurance'] == 1) $packageIncludes[] = 'full_insurance';
        if (isset($_GET['accommodation']) && $_GET['accommodation'] == 1) $packageIncludes[] = 'accommodation';
        if (isset($_GET['annual_leave']) && $_GET['annual_leave'] == 1) $packageIncludes[] = 'annual_leave';
        if (isset($_GET['mohre_registration']) && $_GET['mohre_registration'] == 1) $packageIncludes[] = 'mohre_registration';
        $packageIncludes = array_unique($packageIncludes);

        $sql = "
            SELECT v.id, v.agency_name, v.tagline, v.logo, v.description, v.cover_image, v.is_verified,
                   COALESCE(AVG(r.rating), 5.0) as rating, COUNT(r.id) as reviews_count,
                   COALESCE((
                       SELECT MIN(m.monthly_salary)
                       FROM maids m
                       WHERE m.vendor_id = v.id AND m.status = 'published' AND m.availability_status = 'available'
                   ), 2500.00) as starting_price,
                   COALESCE((
                       SELECT COUNT(*) 
                       FROM maids m 
                       WHERE m.vendor_id = v.id AND m.status = 'published'
                   ), 0) as staff_count,
                   COALESCE((
                       SELECT ROUND(AVG(m.years_experience)) 
                       FROM maids m 
                       WHERE m.vendor_id = v.id AND m.status = 'published'
                   ), 5) as experience_years
            FROM vendors v
            LEFT JOIN reviews r ON r.vendor_id = v.id
            WHERE v.status = 'approved'
        ";
        
        $params = [];
        if (!empty($searchTerm)) {
            $sql .= " AND v.agency_name LIKE ?";
            $params[] = "%$searchTerm%";
        }
        
        if (in_array('full_insurance', $packageIncludes) || in_array('mohre_registration', $packageIncludes)) {
            $sql .= " AND v.is_verified = 1";
        }
        if (in_array('visa_processing', $packageIncludes)) {
            $sql .= " AND EXISTS (SELECT 1 FROM maids m WHERE m.vendor_id = v.id AND m.status = 'published' AND m.visa_status = 'sponsored')";
        }
        if (in_array('accommodation', $packageIncludes)) {
            $sql .= " AND EXISTS (SELECT 1 FROM maids m WHERE m.vendor_id = v.id AND m.status = 'published' AND m.accommodation_type_id IS NOT NULL)";
        }
        
        $sql .= " GROUP BY v.id ORDER BY rating DESC";
        
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $vendors = $stmt->fetchAll();
        
        foreach ($vendors as &$vendor) {
            $vendor['rating'] = floatval($vendor['rating']);
            $vendor['reviews_count'] = intval($vendor['reviews_count']);
            $vendor['starting_price'] = floatval($vendor['starting_price']);
            $vendor['staff_count'] = intval($vendor['staff_count']);
            $vendor['experience_years'] = intval($vendor['experience_years']);
            $vendor['is_verified'] = intval($vendor['is_verified']) === 1;
            $vendor['insurance_coverage'] = $vendor['is_verified'];
            
            // Get dynamic tags (specialties of maids in this agency)
            $tagStmt = $db->prepare("
                SELECT DISTINCT s.name_en, s.name_ar 
                FROM maid_specialties ms
                JOIN specialties s ON ms.specialty_id = s.id
                JOIN maids m ON ms.maid_id = m.id
                WHERE m.vendor_id = ? AND m.status = 'published'
                LIMIT 2
            ");
            $tagStmt->execute([$vendor['id']]);
            $tags = $tagStmt->fetchAll();
            $vendor['tags'] = array_column($tags, 'name_en');
            $vendor['tags_ar'] = array_column($tags, 'name_ar');
        }
        
        sendResponse(['vendors' => $vendors], 'Vendors retrieved');
    }
    
    public function getVendor($id) {
        $db = Database::getInstance();
        
        // Vendor details
        $stmt = $db->prepare("
            SELECT v.id, v.agency_name, v.tagline, v.logo, v.description, v.cover_image, v.is_verified,
                   v.email, v.phone_code, v.phone, v.address, v.latitude, v.longitude,
                   COALESCE(AVG(r.rating), 5.0) as rating, COUNT(r.id) as reviews_count,
                   COALESCE((
                       SELECT COUNT(*) 
                       FROM maids m 
                       WHERE m.vendor_id = v.id AND m.status = 'published'
                   ), 0) as staff_count,
                   COALESCE((
                       SELECT MIN(m.monthly_salary)
                       FROM maids m
                       WHERE m.vendor_id = v.id AND m.status = 'published' AND m.availability_status = 'available'
                   ), 2500.00) as starting_price
            FROM vendors v
            LEFT JOIN reviews r ON r.vendor_id = v.id
            WHERE v.id = ? AND v.status = 'approved'
            GROUP BY v.id
        ");
        $stmt->execute([$id]);
        $vendor = $stmt->fetch();
        
        if (!$vendor) {
            sendError('Vendor not found', [], 404);
        }
        
        $vendor['rating'] = floatval($vendor['rating']);
        $vendor['reviews_count'] = intval($vendor['reviews_count']);
        $vendor['staff_count'] = intval($vendor['staff_count']);
        $vendor['starting_price'] = floatval($vendor['starting_price']);
        $vendor['is_verified'] = intval($vendor['is_verified']) === 1;
        $vendor['insurance_coverage'] = $vendor['is_verified'];
        
        // Format contact links
        $fullPhone = '+' . ($vendor['phone_code'] ?? '971') . ($vendor['phone'] ?? '');
        $vendor['contact_actions'] = [
            'call' => 'tel:' . $fullPhone,
            'whatsapp' => 'https://wa.me/' . ltrim($fullPhone, '+'),
            'email' => 'mailto:' . ($vendor['email'] ?? ''),
            'directions' => ($vendor['latitude'] && $vendor['longitude']) 
                ? 'https://www.google.com/maps/search/?api=1&query=' . $vendor['latitude'] . ',' . $vendor['longitude']
                : 'https://maps.google.com/?q=' . urlencode($vendor['address'] ?? '')
        ];
        
        // Package Inclusions
        $vendor['package_inclusions'] = [
            [
                'key' => 'visa_processing',
                'title' => 'Visa Processing',
                'title_ar' => 'معالجة التأشيرة',
                'description' => 'Full sponsorship & renewal included.',
                'description_ar' => 'رعاية كاملة وتجديد مشمول.',
                'icon' => 'passport'
            ],
            [
                'key' => 'full_insurance',
                'title' => 'Full Insurance',
                'title_ar' => 'تأمين كامل',
                'description' => 'Medical & liability coverage.',
                'description_ar' => 'تغطية طبية وتأمين ضد المسؤولية.',
                'icon' => 'shield'
            ],
            [
                'key' => 'accommodation',
                'title' => 'Accommodation',
                'title_ar' => 'السكن',
                'description' => 'Vendor-managed staff housing.',
                'description_ar' => 'سكن موظفين مدار من قبل المورد.',
                'icon' => 'home'
            ],
            [
                'key' => 'annual_leave',
                'title' => 'Annual Leave',
                'title_ar' => 'الإجازة السنوية',
                'description' => 'Paid leave & flight allowance.',
                'description_ar' => 'إجازة مدفوعة وتذكرة طيران.',
                'icon' => 'plane'
            ]
        ];
        
        // Service Models Supported
        $smStmt = $db->prepare("
            SELECT DISTINCT sm.name_en, sm.name_ar 
            FROM maids m 
            JOIN service_models sm ON m.service_model_id = sm.id 
            WHERE m.vendor_id = ? AND m.status = 'published'
        ");
        $smStmt->execute([$id]);
        $vendor['service_models'] = $smStmt->fetchAll();
        
        // Vendor maids
        $stmt = $db->prepare("
            SELECT m.id, m.full_name, m.monthly_salary, m.years_experience, m.profile_image, m.bio, m.reliability_score, m.is_recommended, m.availability_status,
                   n.name_en as nationality_name, n.name_ar as nationality_name_ar,
                   sm.name_en as service_model_name, sm.name_ar as service_model_name_ar,
                   COALESCE(AVG(r.rating), 5.0) as rating, COUNT(r.id) as reviews_count
            FROM maids m
            LEFT JOIN nationalities n ON m.nationality_id = n.id
            LEFT JOIN service_models sm ON m.service_model_id = sm.id
            LEFT JOIN reviews r ON r.maid_id = m.id AND r.status = 'approved'
            WHERE m.vendor_id = ? AND m.status = 'published' AND m.availability_status = 'available'
            GROUP BY m.id
            ORDER BY m.id DESC
        ");
        $stmt->execute([$id]);
        $maids = $stmt->fetchAll();
        
        foreach ($maids as &$m) {
            $m['rating'] = floatval($m['rating']);
            $m['reviews_count'] = intval($m['reviews_count']);
            $m['monthly_salary'] = floatval($m['monthly_salary']);
            $m['years_experience'] = intval($m['years_experience']);
            $m['reliability_score'] = intval($m['reliability_score']);
            $m['is_recommended'] = intval($m['is_recommended']) === 1;
            
            $m['service_model_formatted'] = isRTL() ? ($m['service_model_name_ar'] ?? $m['service_model_name']) : $m['service_model_name'];
            
            // Languages
            $langStmt = $db->prepare("
                SELECT l.name_en 
                FROM maid_languages ml 
                JOIN languages l ON ml.language_id = l.id 
                WHERE ml.maid_id = ?
            ");
            $langStmt->execute([$m['id']]);
            $m['languages'] = $langStmt->fetchAll(PDO::FETCH_COLUMN);
            
            // Specialties
            $specStmt = $db->prepare("
                SELECT s.name_en 
                FROM maid_specialties ms 
                JOIN specialties s ON ms.specialty_id = s.id 
                WHERE ms.maid_id = ?
            ");
            $specStmt->execute([$m['id']]);
            $m['specialties'] = $specStmt->fetchAll(PDO::FETCH_COLUMN);
        }
        $vendor['maids'] = $maids;
        
        // Vendor reviews
        $stmt = $db->prepare("
            SELECT r.*, c.full_name as customer_name
            FROM reviews r
            JOIN customers c ON r.customer_id = c.id
            WHERE r.vendor_id = ? AND r.status = 'approved'
            ORDER BY r.created_at DESC
        ");
        $stmt->execute([$id]);
        $reviews = $stmt->fetchAll();
        $vendor['reviews'] = $reviews;
        
        sendResponse($vendor, 'Vendor details loaded successfully');
    }
    
    public function getMaid($id) {
        $db = Database::getInstance();
        
        // Increment view count
        $stmt = $db->prepare("UPDATE maids SET views = views + 1 WHERE id = ?");
        $stmt->execute([$id]);
        
        // Maid details
        $stmt = $db->prepare("
            SELECT m.*, 
                   n.name_en as nationality_name,
                   c.name_en as country_name,
                   ci.name_en as city_name,
                   sm.name_en as service_model_name,
                   at.name_en as accommodation_type_name,
                   v.agency_name, v.logo as vendor_logo, v.is_verified as vendor_verified
            FROM maids m
            LEFT JOIN nationalities n ON m.nationality_id = n.id
            LEFT JOIN countries c ON m.country_id = c.id
            LEFT JOIN cities ci ON m.city_id = ci.id
            LEFT JOIN service_models sm ON m.service_model_id = sm.id
            LEFT JOIN accommodation_types at ON m.accommodation_type_id = at.id
            JOIN vendors v ON m.vendor_id = v.id
            WHERE m.id = ? AND m.status = 'published' AND v.status = 'approved'
        ");
        $stmt->execute([$id]);
        $maid = $stmt->fetch();
        
        if (!$maid) {
            sendError('Maid profile not found', [], 404);
        }
        
        // Dynamic rating calculation
        $stmt = $db->prepare("
            SELECT COALESCE(AVG(rating), 5.0) as rating, COUNT(id) as reviews_count 
            FROM reviews 
            WHERE maid_id = ? AND status = 'approved'
        ");
        $stmt->execute([$id]);
        $ratingDetails = $stmt->fetch();
        $maid['rating'] = floatval($ratingDetails['rating']);
        $maid['reviews_count'] = intval($ratingDetails['reviews_count']);
        
        // Formatting UI Badges & Statistics
        $maid['experience_formatted'] = intval($maid['years_experience']) . '+ Years Exp.';
        $maid['reliability_formatted'] = intval($maid['reliability_score']) . '% Reliable';
        
        $visaMap = [
            'sponsored' => 'Sponsored Visa',
            'own_visa' => 'Own Visa',
            'visit_visa' => 'Visit Visa',
            'none' => 'No Visa'
        ];
        $maid['visa_status_formatted'] = $visaMap[$maid['visa_status']] ?? 'None';
        
        $availMap = [
            'available' => 'AVAILABLE MONTHLY',
            'booked' => 'FULLY BOOKED',
            'unavailable' => 'UNAVAILABLE'
        ];
        $maid['availability_formatted'] = $availMap[$maid['availability_status']] ?? 'UNAVAILABLE';
        $maid['is_verified_pro'] = intval($maid['emirates_id_verified']) === 1;
        $maid['location_formatted'] = ($maid['city_name'] && $maid['country_name']) ? ($maid['city_name'] . ', ' . $maid['country_name']) : 'Dubai, UAE';
        $maid['service_model_formatted'] = $maid['service_model_name'] ? $maid['service_model_name'] : 'Full Time';
        
        // Gallery
        $stmt = $db->prepare("SELECT image FROM maid_gallery WHERE maid_id = ? ORDER BY sort_order ASC");
        $stmt->execute([$id]);
        $maid['gallery'] = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // Languages
        $stmt = $db->prepare("
            SELECT l.id, l.name_en, l.name_ar, ml.proficiency 
            FROM maid_languages ml
            JOIN languages l ON ml.language_id = l.id
            WHERE ml.maid_id = ?
        ");
        $stmt->execute([$id]);
        $langs = $stmt->fetchAll();
        
        foreach ($langs as &$lang) {
            $lang['proficiency'] = !empty($lang['proficiency']) ? ucfirst($lang['proficiency']) : 'Conversational';
        }
        $maid['languages'] = $langs;
        
        // Skills
        $stmt = $db->prepare("
            SELECT s.id, s.name_en, s.name_ar 
            FROM maid_skills ms
            JOIN skills s ON ms.skill_id = s.id
            WHERE ms.maid_id = ?
        ");
        $stmt->execute([$id]);
        $maid['skills'] = $stmt->fetchAll();
        
        // Specialties
        $stmt = $db->prepare("
            SELECT sp.id, sp.name_en, sp.name_ar 
            FROM maid_specialties msp
            JOIN specialties sp ON msp.specialty_id = sp.id
            WHERE msp.maid_id = ?
        ");
        $stmt->execute([$id]);
        $maid['specialties'] = $stmt->fetchAll();
        
        // Experience Timeline History
        $stmt = $db->prepare("
            SELECT job_title, employer, location, description, date_from, date_to, is_current 
            FROM maid_work_experience 
            WHERE maid_id = ? 
            ORDER BY sort_order ASC, date_from DESC
        ");
        $stmt->execute([$id]);
        $realExperience = $stmt->fetchAll();
        
        $expList = [];
        if (!empty($realExperience)) {
            foreach ($realExperience as $exp) {
                $yearFrom = $exp['date_from'] ? date('Y', strtotime($exp['date_from'])) : '';
                $yearTo = $exp['is_current'] ? 'Present' : ($exp['date_to'] ? date('Y', strtotime($exp['date_to'])) : '');
                $period = $yearFrom . ($yearTo ? ' - ' . $yearTo : '');
                
                $expList[] = [
                    'role' => $exp['job_title'],
                    'company' => $exp['employer'] ?? 'Private Family',
                    'period' => $period,
                    'description' => $exp['description'] ?? ''
                ];
            }
        } else {
            $currentYear = intval(date('Y'));
            $yearsExp = intval($maid['years_experience']);
            if ($yearsExp > 0) {
                $startYear1 = $currentYear - max(1, min(3, $yearsExp - 1));
                $expList[] = [
                    'role' => 'Full-time Housemaid',
                    'company' => $maid['agency_name'] ?? 'Private Family',
                    'period' => $startYear1 . ' - Present',
                    'description' => 'Managing household cleaning, deep organizing, laundry, and assisting with daily chores.'
                ];
                
                if ($yearsExp > 3) {
                    $startYear2 = $startYear1 - ($yearsExp - 3);
                    $expList[] = [
                        'role' => 'Domestic Helper',
                        'company' => 'Elite Home Services',
                        'period' => $startYear2 . ' - ' . $startYear1,
                        'description' => 'Senior cleaner for luxury apartments and villas. Specialized in deep move-in/move-out services.'
                    ];
                }
            } else {
                $expList[] = [
                    'role' => 'Housemaid Trainee',
                    'company' => $maid['agency_name'] ?? 'Training Center',
                    'period' => ($currentYear - 1) . ' - Present',
                    'description' => 'Completed intensive training in housekeeping standards, sanitation, and child care safety.'
                ];
            }
        }
        $maid['experience_history'] = $expList;
        
        // Reviews
        $stmt = $db->prepare("
            SELECT r.*, c.full_name as customer_name, c.profile_image as customer_image
            FROM reviews r
            JOIN customers c ON r.customer_id = c.id
            WHERE r.maid_id = ? AND r.status = 'approved'
            ORDER BY r.created_at DESC
        ");
        $stmt->execute([$id]);
        $reviews = $stmt->fetchAll();
        foreach ($reviews as &$r) {
            $r['rating'] = floatval($r['rating']);
            // Format time ago
            $time = strtotime($r['created_at']);
            $diff = time() - $time;
            if ($diff < 60) {
                $r['time_ago'] = 'Just now';
            } elseif ($diff < 3600) {
                $r['time_ago'] = round($diff / 60) . 'm ago';
            } elseif ($diff < 86400) {
                $r['time_ago'] = round($diff / 3600) . 'h ago';
            } elseif ($diff < 172800) {
                $r['time_ago'] = 'Yesterday';
            } else {
                $r['time_ago'] = round($diff / 86400) . ' days ago';
            }
        }
        $maid['reviews'] = $reviews;
        
        sendResponse($maid, 'Maid details loaded successfully');
    }

    public function listMaids() {
        $_GET['type'] = 'maids';
        $this->search();
    }
}
