# UAE Healthcare Facility Directory — Full Project Specification > **Database**: `ehealth_dmr` · **Primary Market**: Dubai (Phase 1) → All UAE Emirates (Phase 2) > **Stack Recommendation**: Next.js 14 (App Router) · MySQL/MariaDB · Tailwind CSS · Resend (email) · Supabase or PlanetScale --- ## Table of Contents 1. [Database Analysis & Schema Map](#1-database-analysis--schema-map) 2. [Project Architecture](#2-project-architecture) 3. [URL & Slug Structure](#3-url--slug-structure) 4. [Directory Homepage — Dubai Healthcare Facilities](#4-directory-homepage--dubai-healthcare-facilities) 5. [Single Facility Page](#5-single-facility-page) 6. [Booking Form — Save to DB + Email](#6-booking-form--save-to-db--email) 7. [SEO / AEO / GEO Strategy](#7-seo--aeo--geo-strategy) 8. [Database Tables Used (Annotated)](#8-database-tables-used-annotated) 9. [Extended UAE Coverage (All Emirates)](#9-extended-uae-coverage-all-emirates) 10. [Implementation Checklist](#10-implementation-checklist) --- ## 1. Database Analysis & Schema Map ### Core Entity Relationships ``` emirates (7 UAE emirates) └── uae_zones (free zones, districts, healthcare clusters per emirate) └── facilities (main facility record) ├── facility_categories (Hospital, Clinic, Pharmacy…) │ └── facility_subcategories (General Hospital, Specialty Clinic…) ├── facility_details (hours, beds, accreditation, ratings — 1:1) ├── facility_social_links (Facebook, Instagram, LinkedIn…) ├── facility_emails (GENERAL, APPOINTMENTS, HR…) ├── facility_services (list of services offered) ├── facility_specialties → specialties (Cardiology, Orthopedics…) ├── facility_departments → clinical_departments ├── facility_staff_specialists (doctor profiles) ├── facility_licenses → health_authorities (DHA, DOH, MOHAP…) ├── facility_ratings_reviews (user reviews) └── healthcare_organizations (parent group / chain) ``` ### Key Tables for the Public Directory | Table | Purpose | Public-Facing? | |-------|---------|---------------| | `emirates` | 7 emirates with slug, description, coordinates | ✅ Yes | | `uae_zones` | DHCC, DIFC, Downtown, JLT etc. | ✅ Yes (area filter) | | `facilities` | Core listing: name, slug, address, phone, logo, cover image | ✅ Yes | | `facility_categories` | Hospital / Clinic / Pharmacy / Lab / Dental etc. | ✅ Yes (filter) | | `facility_subcategories` | General Hospital / Day Surgery / IVF Clinic etc. | ✅ Yes (filter) | | `facility_details` | Hours, beds, accreditations, ratings, amenities | ✅ Yes | | `facility_social_links` | FB / IG / LinkedIn / WhatsApp URLs | ✅ Yes | | `facility_emails` | Public appointment email | ✅ Yes (APPOINTMENTS type) | | `facility_services` | Services list | ✅ Yes | | `facility_specialties` | Medical specialties | ✅ Yes | | `facility_staff_specialists` | Doctor profiles | ✅ Yes | | `health_authorities` | DHA / DOH / MOHAP badges | ✅ Yes | | `healthcare_organizations` | Parent chain (Aster, NMC, Mediclinic…) | ✅ Yes | | `facility_ratings_reviews` | Star ratings + reviews | ✅ Yes | | `specialties` | Master specialty catalog | ✅ Yes | --- ## 2. Project Architecture ``` /app / → UAE-wide landing (emirate selector) /dubai → Dubai Healthcare Directory Homepage /dubai/[category-slug] → Category listing page (e.g. /dubai/hospitals) /dubai/[category]/[sub] → Sub-category page (e.g. /dubai/hospitals/general) /dubai/[category]/[area] → Area-filtered page (e.g. /dubai/clinics/downtown-dubai) /dubai/facility/[slug] → Single facility page ⭐ /[emirate-slug] → Per-emirate directory (abu-dhabi, sharjah, ajman…) /[emirate]/facility/[slug] → Emirate-specific facility page /api /bookings → POST: save booking + send email /facilities/[slug] → GET: facility JSON for SSR/ISR /reviews → POST: submit review /sitemap.xml → Dynamic sitemap (all facility slugs) /robots.txt ``` ### Slug Generation Rules ```sql -- facilities.facility_slug is already in the DB -- Format: {facility-name-kebab-case}-{emirate-code}-{category-code} -- Example: "aster-hospital-dubai-hospital-dxb" -- Guarantee uniqueness: append facility_uid suffix if collision SELECT facility_slug FROM facilities WHERE facility_slug = 'american-hospital-dubai' -- → /dubai/facility/american-hospital-dubai ``` --- ## 3. URL & Slug Structure ### Canonical URL Pattern ``` https://yourdomain.com/{emirate-slug}/facility/{facility-slug} Examples: https://yourdomain.com/dubai/facility/american-hospital-dubai https://yourdomain.com/dubai/facility/aster-clinic-karama https://yourdomain.com/abu-dhabi/facility/mediclinic-al-ain ``` ### Filter URLs (SEO-friendly) ``` /dubai/hospitals → All Dubai hospitals /dubai/hospitals/general → General hospitals in Dubai /dubai/clinics/dermatology → Dermatology clinics in Dubai /dubai/clinics/area/downtown-dubai → Clinics in Downtown Dubai /dubai/pharmacies/area/jumeirah → Pharmacies in Jumeirah /dubai/dental-centers → All dental centers in Dubai /dubai/diagnostic-labs/area/deira → Labs in Deira ``` ### Slug Sources from DB ```sql -- Emirates slugs (from emirates.slug) SELECT slug FROM emirates; -- → dubai, abu-dhabi, sharjah, ajman, ras-al-khaimah, fujairah, umm-al-quwain -- Category slugs (from facility_categories.slug) SELECT slug, name FROM facility_categories WHERE is_active = 1; -- Sub-category slugs (from facility_subcategories.slug) SELECT fs.slug, fs.name, fc.slug AS cat_slug FROM facility_subcategories fs JOIN facility_categories fc ON fc.id = fs.category_id; -- Area slugs (from uae_zones.slug) SELECT slug, zone_name FROM uae_zones WHERE emirate_id = 1; -- Dubai = 1 ``` --- ## 4. Directory Homepage — Dubai Healthcare Facilities ### Page Goal > Serve as the main entry point for Dubai healthcare search. > URL: `/dubai` | Title: `Dubai Healthcare Facilities Directory 2025 | Find Hospitals, Clinics & More` --- ### 4.1 Hero Section ```html

Dubai Healthcare Facilities Directory

Find verified hospitals, clinics, pharmacies and specialist centres across Dubai — licensed by DHA, accredited and reviewed.

1,200+ Facilities 45+ Specialties 30+ Areas DHA Licensed
``` **DB Query for stats:** ```sql SELECT COUNT(*) AS total_facilities, COUNT(DISTINCT area_name) AS total_areas, COUNT(DISTINCT category_id) AS total_categories FROM facilities WHERE emirate_id = 1 -- Dubai AND facility_status = 'ACTIVE' AND deleted_at IS NULL; ``` --- ### 4.2 Category Filter Tabs ```html ``` **DB Query:** ```sql SELECT fc.id, fc.name, fc.slug, fc.icon_url, fc.color_code, COUNT(f.id) AS facility_count FROM facility_categories fc LEFT JOIN facilities f ON f.category_id = fc.id AND f.emirate_id = 1 AND f.facility_status = 'ACTIVE' AND f.deleted_at IS NULL WHERE fc.is_active = 1 GROUP BY fc.id ORDER BY fc.display_order ASC; ``` --- ### 4.3 Sub-Category + Area Filter Panel ```html ``` **Area query:** ```sql SELECT uz.id, uz.zone_name, uz.slug, COUNT(f.id) AS facility_count FROM uae_zones uz LEFT JOIN facilities f ON f.zone_id = uz.id AND f.facility_status = 'ACTIVE' AND f.deleted_at IS NULL WHERE uz.emirate_id = 1 AND uz.is_active = 1 GROUP BY uz.id ORDER BY facility_count DESC; ``` --- ### 4.4 Facility Cards Grid Each card renders from a combined query of `facilities` + `facility_details` + `facility_categories`. **Card HTML:** ```html
{facility_name} — {category_name} in {area_name}, Dubai {category_name} ✓ Verified Open Now

{facility_name}

{sub_category_name}

📍 {area_name}, Dubai 📞 {primary_phone}
★ {avg_rating} ({review_count} reviews)
{#if jci_accredited}JCI{/if} {#if dha_accredited}DHA{/if}
``` **Listing Query (Dubai, paginated):** ```sql SELECT f.id, f.facility_name, f.facility_slug, f.area_name, f.primary_phone, f.logo_url, f.cover_image_url, f.description, f.google_place_id, f.latitude, f.longitude, fc.name AS category_name, fc.slug AS category_slug, fc.color_code, fsc.name AS sub_category_name, fd.avg_rating, fd.review_count, fd.google_rating, fd.is_open_24_7, fd.jci_accredited, fd.dha_accredited, fd.is_featured, fd.is_verified, fd.accepts_insurance, fd.has_telemedicine, fd.parking_available, ha.short_name AS health_authority_badge, ha.primary_color AS authority_color FROM facilities f JOIN facility_categories fc ON fc.id = f.category_id LEFT JOIN facility_subcategories fsc ON fsc.id = f.sub_category_id LEFT JOIN facility_details fd ON fd.facility_id = f.id LEFT JOIN health_authorities ha ON ha.id = f.health_authority_id WHERE f.emirate_id = 1 -- Dubai AND f.facility_status = 'ACTIVE' AND f.deleted_at IS NULL -- Dynamic filters: -- AND f.category_id = ? -- category filter -- AND f.sub_category_id = ? -- sub-category filter -- AND f.zone_id = ? -- area filter -- AND fd.is_open_24_7 = 1 -- 24/7 filter -- AND fd.jci_accredited = 1 -- JCI filter -- AND fd.accepts_insurance = 1 -- Insurance filter ORDER BY fd.is_featured DESC, fd.listing_priority ASC, fd.avg_rating DESC, f.facility_name ASC LIMIT 20 OFFSET ?; ``` --- ## 5. Single Facility Page > URL: `/dubai/facility/{facility-slug}` > Example: `/dubai/facility/american-hospital-dubai` ### Design Direction **Vibrant healthcare colors**: primary teal `#0D9488`, accent coral `#F97316`, clean white backgrounds, deep navy `#0F172A` for text. Clean but energetic — trustworthy medical + approachable wellness. --- ### 5.1 Page Header / Hero ```html {facility_name} | {category_name} in {area_name}, Dubai | {domain}
{facility_name} — {sub_category_name} in {area_name} Dubai
{facility_name} logo
{health_authority_short_name} Licensed

{facility_name}

{sub_category_name} · {area_name}, Dubai

★ {avg_rating} ({review_count} reviews) {#if google_rating} Google {google_rating} ({google_review_count}) {/if}
{#if jci_accredited}JCI Accredited{/if} {#if dha_accredited}DHA Accredited{/if} {#if haad_accredited}DOH Accredited{/if} {#if is_open_24_7}Open 24/7{/if} {#if accepts_insurance}Insurance Accepted{/if} {#if has_telemedicine}Telemedicine{/if}
📞 Call Now 📅 Book Appointment 🌐 Visit Website
``` --- ### 5.2 Main Content Layout (2-Column) ```html

About {facility_name}

{description}

{#if description_ar}
عربي

{description_ar}

{/if}

Services Offered

    {#each services as service}
  • {service.service_name} {#if service.is_available_24_7}24/7{/if}
    • {/each}

Medical Specialties

{#each specialties as spec} {spec.name} {/each}
{#if staff_specialists.length > 0}

Our Specialists

{#each staff_specialists as doctor}
Dr. {doctor.specialist_name}

Dr. {doctor.specialist_name}

{doctor.specialty}

{#if doctor.experience_years}

{doctor.experience_years} years experience

{/if} {#if doctor.consultation_fee}

Consultation: {doctor.consultation_fee}

{/if}
{/each}
{/if}

Opening Hours

{#if is_open_24_7}

Open 24 Hours, 7 Days a Week

{:else} {#if is_open_weekends}{/if}
Days{operating_days_from} – {operating_days_to}
Hours{operating_hours_from} – {operating_hours_to}
✅ Open Weekends
{#if hours_notes}

{hours_notes}

{/if} {/if}

Facilities & Amenities

    {#if bed_capacity}
  • 🛏️ {bed_capacity} Beds
    • {/if} {#if icu_bed_count}
  • 🏥 {icu_bed_count} ICU Beds
    • {/if} {#if operating_theatre_count}
  • 🔪 {operating_theatre_count} Operating Theatres
    • {/if} {#if has_pharmacy}
  • 💊 In-House Pharmacy
    • {/if} {#if has_lab}
  • 🔬 Diagnostic Laboratory
    • {/if} {#if has_radiology}
  • 🩻 Radiology / Imaging
    • {/if} {#if has_ambulance}
  • 🚑 Ambulance Service
    • {/if} {#if wheelchair_accessible}
  • ♿ Wheelchair Accessible
    • {/if} {#if parking_available}
  • 🅿️ Parking Available
    • {/if} {#if valet_parking}
  • 🚗 Valet Parking
    • {/if}
{#if languages_spoken}

Languages Spoken

{languages_spoken}

{/if} {#if insurance_networks}

Accepted Insurance

{insurance_networks}

{/if}

Patient Reviews

{#each reviews.filter(r => r.review_status === 'APPROVED').slice(0,5) as review}
{review.rating}/5

{review.review_title}

{review.review_text}

{#if review.admin_response} {/if}
{/each}

Location

{address_line_1}

{#if address_line_2}

{address_line_2}

{/if}

{area_name}, Dubai, UAE

``` --- ## 6. Booking Form — Save to DB + Email ### 6.1 Database Table (Add to Schema) ```sql CREATE TABLE `appointment_bookings` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `facility_id` BIGINT UNSIGNED NOT NULL, `booking_ref` VARCHAR(20) NOT NULL COMMENT 'e.g. BK-20250601-XXXX', `patient_name` VARCHAR(150) NOT NULL, `patient_email` VARCHAR(255) NOT NULL, `patient_phone` VARCHAR(20) NOT NULL, `nationality` VARCHAR(80) DEFAULT NULL, `insurance_provider` VARCHAR(150) DEFAULT NULL, `preferred_date` DATE NOT NULL, `preferred_time` ENUM('MORNING','AFTERNOON','EVENING') NOT NULL DEFAULT 'MORNING', `specialty_requested` VARCHAR(150) DEFAULT NULL, `doctor_requested` VARCHAR(150) DEFAULT NULL, `reason_for_visit` TEXT DEFAULT NULL, `notes` TEXT DEFAULT NULL, `status` ENUM('PENDING','CONFIRMED','CANCELLED','COMPLETED') NOT NULL DEFAULT 'PENDING', `email_sent_facility` TINYINT(1) NOT NULL DEFAULT 0, `email_sent_patient` TINYINT(1) NOT NULL DEFAULT 0, `ip_address` VARCHAR(45) DEFAULT NULL, `source_url` VARCHAR(500) DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `booking_ref` (`booking_ref`), KEY `facility_id` (`facility_id`), KEY `preferred_date` (`preferred_date`), FOREIGN KEY (`facility_id`) REFERENCES `facilities`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Patient appointment booking requests from the public directory'; ``` --- ### 6.2 Booking Form HTML ```html

📅 Book an Appointment

Optional. Do not include sensitive medical details.
``` --- ### 6.3 API Route — `/api/bookings` (Next.js) ```typescript // app/api/bookings/route.ts import { NextRequest, NextResponse } from 'next/server'; import { db } from '@/lib/db'; // your DB connection (mysql2 / PlanetScale) import { sendBookingEmails } from '@/lib/email'; import { nanoid } from 'nanoid'; import { z } from 'zod'; const BookingSchema = z.object({ facility_id: z.coerce.number().int().positive(), facility_name: z.string().min(1), facility_email: z.string().email(), patient_name: z.string().min(2).max(150), patient_email: z.string().email().max(255), patient_phone: z.string().min(7).max(20), preferred_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/), preferred_time: z.enum(['MORNING', 'AFTERNOON', 'EVENING']).default('MORNING'), specialty_requested: z.string().max(150).optional(), doctor_requested: z.string().max(150).optional(), reason_for_visit: z.string().max(500).optional(), insurance_provider: z.string().max(150).optional(), source_url: z.string().url().optional(), }); export async function POST(req: NextRequest) { try { const body = await req.json(); const data = BookingSchema.parse(body); // Validate preferred_date is not in the past if (new Date(data.preferred_date) < new Date()) { return NextResponse.json( { success: false, message: 'Preferred date must be in the future.' }, { status: 400 } ); } // Generate unique booking reference const booking_ref = `BK-${new Date().toISOString().slice(0,10).replace(/-/g,'')}-${nanoid(6).toUpperCase()}`; // Save to DB await db.query( `INSERT INTO appointment_bookings (facility_id, booking_ref, patient_name, patient_email, patient_phone, preferred_date, preferred_time, specialty_requested, doctor_requested, reason_for_visit, insurance_provider, source_url, ip_address) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ data.facility_id, booking_ref, data.patient_name, data.patient_email, data.patient_phone, data.preferred_date, data.preferred_time, data.specialty_requested || null, data.doctor_requested || null, data.reason_for_visit || null, data.insurance_provider || null, data.source_url || null, req.headers.get('x-forwarded-for') || req.ip || null, ] ); // Send emails (non-blocking) sendBookingEmails({ booking_ref, facility_name: data.facility_name, facility_email: data.facility_email, patient_name: data.patient_name, patient_email: data.patient_email, patient_phone: data.patient_phone, preferred_date: data.preferred_date, preferred_time: data.preferred_time, specialty: data.specialty_requested, reason: data.reason_for_visit, }).catch(console.error); // don't fail if email fails return NextResponse.json({ success: true, booking_ref, message: `Appointment request submitted! Reference: ${booking_ref}. The facility will contact you within 24 hours.`, }); } catch (err) { if (err instanceof z.ZodError) { return NextResponse.json( { success: false, errors: err.flatten().fieldErrors }, { status: 422 } ); } console.error('Booking error:', err); return NextResponse.json( { success: false, message: 'An error occurred. Please try again.' }, { status: 500 } ); } } ``` --- ### 6.4 Email Templates (Resend / Nodemailer) ```typescript // lib/email.ts import { Resend } from 'resend'; const resend = new Resend(process.env.RESEND_API_KEY); interface BookingEmailData { booking_ref: string; facility_name: string; facility_email: string; patient_name: string; patient_email: string; patient_phone: string; preferred_date: string; preferred_time: string; specialty?: string; reason?: string; } export async function sendBookingEmails(data: BookingEmailData) { const dateFormatted = new Date(data.preferred_date).toLocaleDateString('en-AE', { weekday: 'long', year: 'numeric', month: 'long', day: 'numeric' }); const timeMap = { MORNING: 'Morning (8am–12pm)', AFTERNOON: 'Afternoon (12pm–5pm)', EVENING: 'Evening (5pm–9pm)' }; // Email to FACILITY await resend.emails.send({ from: 'noreply@yourdomain.com', to: data.facility_email, subject: `New Appointment Request — ${data.booking_ref} | ${data.patient_name}`, html: `

New Appointment Request

Ref: ${data.booking_ref}

Patient Details

${data.specialty ? `` : ''} ${data.reason ? `` : ''}
Name${data.patient_name}
Email${data.patient_email}
Phone${data.patient_phone}
Preferred Date${dateFormatted}
Preferred Time${timeMap[data.preferred_time]}
Specialty${data.specialty}
Reason${data.reason}

Please contact the patient within 24 hours to confirm their appointment.

`, }); // Confirmation email to PATIENT await resend.emails.send({ from: `${data.facility_name} via YourDirectory `, to: data.patient_email, subject: `Appointment Request Confirmed — ${data.booking_ref} | ${data.facility_name}`, html: `

Appointment Request Received ✓

${data.facility_name}

Hi ${data.patient_name},

Your appointment request at ${data.facility_name} has been received. Here are your details:

Reference: ${data.booking_ref}

Preferred Date: ${dateFormatted}

Preferred Time: ${timeMap[data.preferred_time]}

${data.specialty ? `

Specialty: ${data.specialty}

` : ''}

The facility will contact you within 24 hours to confirm.

This is an automated confirmation. Please contact the facility directly for urgent enquiries.

`, }); } ``` --- ## 7. SEO / AEO / GEO Strategy ### 7.1 Page-Level SEO #### Title Tag Pattern ``` {facility_name} — {sub_category_name} in {area_name} Dubai | Book Now American Hospital Dubai — General Hospital in Oud Metha, Dubai | Book Now ``` #### Meta Description Pattern (160 chars) ``` {facility_name} is a {sub_category_name} in {area_name}, Dubai. {#if jci}JCI Accredited. {/if} {#if is_open_24_7}Open 24/7. {/if} Call {primary_phone} or book an appointment online. Services: {top_3_specialties}. ``` **DB Query for meta_description:** ```sql SELECT CONCAT( f.facility_name, ' is a ', fsc.name, ' in ', f.area_name, ', Dubai. ', IF(fd.jci_accredited, 'JCI Accredited. ', ''), IF(fd.is_open_24_7, 'Open 24/7. ', ''), 'Call ', f.primary_phone, ' or book online.' ) AS auto_meta_description FROM facilities f LEFT JOIN facility_subcategories fsc ON fsc.id = f.sub_category_id LEFT JOIN facility_details fd ON fd.facility_id = f.id WHERE f.id = ?; -- Use facilities.meta_description if already populated, else use auto-generated ``` --- ### 7.2 Schema.org Structured Data (JSON-LD) Each facility page must include a complete `MedicalOrganization` schema block: ```json { "@context": "https://schema.org", "@type": "MedicalOrganization", "@id": "https://yourdomain.com/dubai/facility/{facility_slug}#organization", "name": "{facility_name}", "alternateName": "{facility_name_ar}", "description": "{meta_description}", "url": "https://yourdomain.com/dubai/facility/{facility_slug}", "logo": "{logo_url}", "image": "{cover_image_url}", "telephone": "{primary_phone}", "email": "{appointment_email}", "foundingDate": "{establishment_year}", "medicalSpecialty": ["{specialty_1}", "{specialty_2}"], "address": { "@type": "PostalAddress", "streetAddress": "{address_line_1}", "addressLocality": "{area_name}", "addressRegion": "Dubai", "addressCountry": "AE", "postalCode": "{postal_code}" }, "geo": { "@type": "GeoCoordinates", "latitude": "{latitude}", "longitude": "{longitude}" }, "openingHoursSpecification": [ { "@type": "OpeningHoursSpecification", "dayOfWeek": ["Monday","Tuesday","Wednesday","Thursday"], "opens": "{operating_hours_from}", "closes": "{operating_hours_to}" } ], "aggregateRating": { "@type": "AggregateRating", "ratingValue": "{avg_rating}", "reviewCount": "{review_count}", "bestRating": "5", "worstRating": "1" }, "hasMap": "https://www.google.com/maps/place/?q=place_id:{google_place_id}", "sameAs": [ "{facebook_url}", "{instagram_url}", "{linkedin_url}" ], "isAccreditedBy": [ { "@type": "Organization", "name": "Dubai Health Authority", "url": "https://www.dha.gov.ae" } ] } ``` **Add `BreadcrumbList` on every page:** ```json { "@context": "https://schema.org", "@type": "BreadcrumbList", "itemListElement": [ {"@type":"ListItem","position":1,"name":"UAE Healthcare","item":"https://yourdomain.com"}, {"@type":"ListItem","position":2,"name":"Dubai","item":"https://yourdomain.com/dubai"}, {"@type":"ListItem","position":3,"name":"{category_name}","item":"https://yourdomain.com/dubai/{category_slug}"}, {"@type":"ListItem","position":4,"name":"{facility_name}","item":"https://yourdomain.com/dubai/facility/{facility_slug}"} ] } ``` **Add `FAQPage` schema (AI-generated from facility data):** ```json { "@context": "https://schema.org", "@type": "FAQPage", "mainEntity": [ { "@type": "Question", "name": "What are {facility_name} opening hours?", "acceptedAnswer": { "@type": "Answer", "text": "{is_open_24_7 ? 'Open 24 hours, 7 days a week.' : operating_days_from + ' to ' + operating_days_to + ', ' + operating_hours_from + ' to ' + operating_hours_to}" } }, { "@type": "Question", "name": "Does {facility_name} accept insurance?", "acceptedAnswer": { "@type": "Answer", "text": "{accepts_insurance ? 'Yes, ' + facility_name + ' accepts insurance including: ' + insurance_networks : 'Please contact the facility for insurance information.'}" } }, { "@type": "Question", "name": "Where is {facility_name} located?", "acceptedAnswer": { "@type": "Answer", "text": "{facility_name} is located at {address_line_1}, {area_name}, Dubai, UAE." } }, { "@type": "Question", "name": "Is {facility_name} JCI accredited?", "acceptedAnswer": { "@type": "Answer", "text": "{jci_accredited ? 'Yes, ' + facility_name + ' is JCI accredited.' : 'Please check with the facility for current accreditation status.'}" } } ] } ``` --- ### 7.3 AEO — Answer Engine Optimization **Target AI-cited queries (ChatGPT, Perplexity, Gemini, Claude):** | Query Pattern | Page to Optimize | |--------------|-----------------| | "Best hospitals in Dubai for cardiology" | `/dubai/hospitals` + Cardiology specialty pages | | "24-hour clinics near Downtown Dubai" | Filter page + facility detail pages | | "JCI accredited hospitals Dubai" | Filtered listing `/dubai/hospitals?accreditation=jci` | | "Nearest dentist in Jumeirah Dubai" | Area + category filter page | | "What is American Hospital Dubai phone number" | Facility detail page | | "Does City Hospital Dubai accept Daman insurance" | Facility detail page | **AEO Implementation:** 1. Add `` with direct answers to likely questions 2. Use `FAQ schema` with conversational Q&A pairs drawn from facility data 3. Ensure H2/H3 headings match natural language questions 4. Keep first paragraph of each section a direct, dense-fact summary 5. Maintain `HowTo` schema for the booking process: ```json { "@type": "HowTo", "name": "How to book an appointment at {facility_name}", "step": [ {"@type":"HowToStep","text":"Fill in your name, email, and phone number"}, {"@type":"HowToStep","text":"Select your preferred date and time"}, {"@type":"HowToStep","text":"Choose your specialty or department"}, {"@type":"HowToStep","text":"Submit the form and receive a confirmation email with your booking reference"} ] } ``` --- ### 7.4 GEO — Generative Engine Optimization Optimize for LLM citation by making facts machine-readable and citation-friendly: ```html ``` **Content Freshness (GEO ranking factor):** ```sql -- Surface "last verified" timestamp prominently SELECT verified_at, updated_at FROM facilities WHERE id = ?; -- Add to page: ``` --- ### 7.5 Dynamic Sitemap ```typescript // app/sitemap.ts export default async function sitemap() { const facilities = await db.query(` SELECT facility_slug, emirate_id, updated_at, (SELECT slug FROM emirates WHERE id = facilities.emirate_id) AS emirate_slug FROM facilities WHERE facility_status = 'ACTIVE' AND deleted_at IS NULL ORDER BY updated_at DESC `); return [ { url: 'https://yourdomain.com', lastModified: new Date(), changeFrequency: 'daily', priority: 1 }, { url: 'https://yourdomain.com/dubai', lastModified: new Date(), changeFrequency: 'daily', priority: 0.9 }, ...facilities.map(f => ({ url: `https://yourdomain.com/${f.emirate_slug}/facility/${f.facility_slug}`, lastModified: new Date(f.updated_at), changeFrequency: 'weekly', priority: 0.8, })), ]; } ``` --- ## 8. Database Tables Used (Annotated) ### Full JOIN Query for Single Facility Page ```sql SELECT -- Core facility f.id, f.facility_uid, f.facility_name, f.facility_name_ar, f.facility_slug, f.address_line_1, f.address_line_2, f.area_name, f.area_name_ar, f.city, f.postal_code, f.latitude, f.longitude, f.google_place_id, f.primary_phone, f.secondary_phone, f.website_url, f.logo_url, f.featured_image_url, f.cover_image_url, f.description, f.description_ar, f.meta_keywords, f.meta_description, f.bed_capacity, f.staff_count_total, f.establishment_year, f.facility_status, -- Emirate e.name AS emirate_name, e.slug AS emirate_slug, -- Category & Sub-category fc.name AS category_name, fc.slug AS category_slug, fc.color_code AS cat_color, fsc.name AS sub_category_name, fsc.slug AS sub_category_slug, -- Details (1:1) fd.staff_count_physicians, fd.staff_count_nurses, fd.icu_bed_count, fd.er_bed_count, fd.operating_theatre_count, fd.operating_days_from, fd.operating_days_to, fd.operating_hours_from, fd.operating_hours_to, fd.is_open_24_7, fd.is_open_weekends, fd.hours_notes, fd.jci_accredited, fd.jci_accreditation_date, fd.dha_accredited, fd.haad_accredited, fd.iso_certifications, fd.other_certifications, fd.avg_rating, fd.review_count, fd.google_rating, fd.google_review_count, fd.is_featured, fd.is_verified, fd.languages_spoken, fd.wheelchair_accessible, fd.parking_available, fd.valet_parking, fd.has_pharmacy, fd.has_lab, fd.has_radiology, fd.has_ambulance, fd.has_telemedicine, fd.accepts_insurance, fd.insurance_networks, fd.profile_completion_pct, -- Health Authority ha.short_name AS authority_short, ha.name AS authority_name, ha.primary_color AS authority_color, -- Healthcare Organization (parent chain) ho.org_name AS parent_org_name, ho.org_slug AS parent_org_slug, ho.logo_url AS parent_logo_url, -- Zone uz.zone_name, uz.zone_type FROM facilities f JOIN emirates e ON e.id = f.emirate_id JOIN facility_categories fc ON fc.id = f.category_id LEFT JOIN facility_subcategories fsc ON fsc.id = f.sub_category_id LEFT JOIN facility_details fd ON fd.facility_id = f.id LEFT JOIN health_authorities ha ON ha.id = f.health_authority_id LEFT JOIN healthcare_organizations ho ON ho.id = f.healthcare_org_id LEFT JOIN uae_zones uz ON uz.id = f.zone_id WHERE f.facility_slug = ? AND f.facility_status = 'ACTIVE' AND f.deleted_at IS NULL LIMIT 1; ``` --- ## 9. Extended UAE Coverage (All Emirates) ```sql -- All 7 emirates from DB SELECT id, name, name_ar, slug, code, capital, population, description, description_ar, logo_url, banner_url FROM emirates WHERE is_active = 1 ORDER BY id; -- Expected results: -- 1 = Dubai (slug: dubai) -- 2 = Abu Dhabi (slug: abu-dhabi) -- 3 = Sharjah (slug: sharjah) -- 4 = Ajman (slug: ajman) -- 5 = Ras Al Khaimah (slug: ras-al-khaimah) -- 6 = Fujairah (slug: fujairah) -- 7 = Umm Al Quwain (slug: umm-al-quwain) ``` ### Per-Emirate Health Authority Mapping ```sql SELECT ha.code, ha.short_name, ha.name, e.name AS emirate, ha.website_url FROM health_authorities ha LEFT JOIN emirates e ON e.id = ha.emirate_id ORDER BY ha.display_order; -- DHA = Dubai Health Authority (Dubai) -- DOH = Department of Health (Abu Dhabi) -- MOHAP = Ministry of Health & Prevention (Federal / Northern Emirates) -- SHC = Sharjah Health Council (Sharjah) -- RAKHC = Ras Al Khaimah Health Cluster -- FHC = Fujairah Health Cluster -- AJMHD = Ajman Healthcare ``` ### UAE Homepage Structure ``` yourdomain.com/ ├── Emirate cards with count of facilities ├── /dubai → Dubai Directory (Phase 1) ├── /abu-dhabi → Abu Dhabi Directory ├── /sharjah → Sharjah Directory ├── /ajman → Ajman Directory ├── /ras-al-khaimah ├── /fujairah └── /umm-al-quwain ``` --- ## 10. Implementation Checklist ### Phase 1 — Dubai Directory (MVP) - [ ] Set up Next.js 14 project with App Router + TypeScript - [ ] Connect to `ehealth_dmr` database (PlanetScale / Supabase / direct MySQL) - [ ] Build `emirates` + `facility_categories` seed data pages - [ ] Implement `/dubai` homepage with category tabs + area filter - [ ] Implement `/dubai/facility/[slug]` dynamic route with ISR (revalidate: 3600) - [ ] Build `appointment_bookings` table migration - [ ] Implement `/api/bookings` POST endpoint with Zod validation - [ ] Integrate Resend for dual email (facility + patient) - [ ] Add Schema.org JSON-LD on all facility pages - [ ] Generate `sitemap.xml` dynamically from all active facilities - [ ] Add `robots.txt` and canonical tags - [ ] Implement Arabic (`/ar`) language routes with RTL layout - [ ] Add Open Graph images (auto-generate with Satori or Cloudinary) - [ ] Set up Google Search Console + Bing Webmaster ### Phase 2 — Full UAE Coverage - [ ] Extend to all 7 emirates - [ ] Add specialty sub-directory pages (`/dubai/specialties/cardiology`) - [ ] Implement reviews/ratings system (POST to `facility_ratings_reviews`) - [ ] Add doctor profile pages from `facility_staff_specialists` - [ ] Implement organization group pages from `healthcare_organizations` - [ ] Build comparison feature (compare 2-3 facilities side by side) - [ ] Add map view using Google Maps API + `latitude/longitude` from `facilities` - [ ] Implement WhatsApp booking button (from `facility_social_links` where `platform = 'WHATSAPP'`) - [ ] Add insurance filter using `fd.accepts_insurance` + `fd.insurance_networks` ### SEO / AEO / GEO Final Checks - [ ] All pages have unique `` and `<meta description>` - [ ] `MedicalOrganization` + `BreadcrumbList` JSON-LD on every facility page - [ ] `FAQPage` schema with 4+ Q&A pairs per facility - [ ] `hreflang` en/ar on all pages - [ ] Core Web Vitals: LCP < 2.5s (optimize cover images with Next.js `Image`) - [ ] Dynamic sitemap covers all active facilities across all emirates - [ ] `geo.region`, `geo.placename`, `geo.position` meta tags - [ ] Verified by Google Search Console + submitted sitemap - [ ] Page titles include area names for local SEO (e.g. "Clinics in Jumeirah Dubai") - [ ] Internal linking: each facility page links to related specialties, areas, and category pages --- *Generated from `ehealth_dmr` database schema analysis — June 2026* *Covers: 35+ tables · 7 emirates · all facility categories & subcategories*