# Universal Data Importer, Exporter, Multi-Project Sync & cPanel Package Plan

> **For Claude / Antigravity:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

**Goal:** Upgrade the Universal Data Import Hub to support seamless round-trip Bulk Export/Update, eliminate the 50-row preview bottleneck with full pagination and error-filtering, intelligently handle Excel files with active filters/hidden rows, fix pharmacy/doctor area matching with an advanced Arabic orthographic normalizer, synchronize all changes across both `hilal-medical-visits` and `creative-medvisits`, push to GitHub on both repositories, and generate a deployable cPanel update zip package.

**Architecture:** 
1. Build a dedicated `ArabicNormalizer` utility to strip noise prefixes, non-breaking spaces, and diacritics, and normalize Arabic glyphs (`ة/ه`, `أ/إ/آ`, `ى/ي`) and area codes (`ALP-01`).
2. Upgrade `DataImportService` to detect row visibility (`$sheet->getRowDimension()->getVisible()`) and safely handle formula calculation when Excel AutoFilters are active.
3. Implement `exportEntity` in `DataImportService` to generate pre-filled Excel workbooks matching the import template structure for Doctors, Pharmacies, Hospitals, Products, and Areas.
4. Expand `preview()` to return comprehensive rows and upgrade `Admin/Import/Index.vue` with client-side pagination, status filter tabs (All, Valid, Duplicates, Errors Only), search filter, and instant Export triggers.
5. Add direct "تصدير Excel" action buttons on directory pages (`Doctors`, `Pharmacies`, `Hospitals`, `Products`, `Areas`).
6. Port all updates to the parent repository `creative-medvisits` preserving its whitelabel defaults.
7. Run production builds, commit and push both repositories to GitHub, and package an automated `cpanel_update.zip` containing all updated backend files, Vue sources, and compiled production assets.

**Tech Stack:** Laravel 13, PHP 8.3, PhpSpreadsheet, Inertia.js v2, Vue 3 (Composition API & TypeScript), Tailwind CSS v4, Git, PowerShell Zip.

---

### Task 1: Arabic Orthographic & Area/Governorate Matching Engine

**Files:**
- Create: `app/Support/ArabicNormalizer.php`
- Modify: `app/Services/DataImportService.php:750-950`
- Test: `tests/Unit/ArabicNormalizerTest.php`

**Step 1: Write the failing unit test**
Create `tests/Unit/ArabicNormalizerTest.php`:
```php
<?php

namespace Tests\Unit;

use App\Support\ArabicNormalizer;
use PHPUnit\Framework\TestCase;

class ArabicNormalizerTest extends TestCase
{
    public function test_normalizes_alef_variations(): void
    {
        $this->assertEquals('احمد', ArabicNormalizer::normalize('أحمد'));
        $this->assertEquals('اسماعيل', ArabicNormalizer::normalize('إسماعيل'));
        $this->assertEquals('ادونيس', ArabicNormalizer::normalize('أدونيس'));
        $this->assertEquals('اكرام', ArabicNormalizer::normalize('آكرام'));
    }

    public function test_normalizes_taa_marbuta_and_haa(): void
    {
        $this->assertEquals('الجابريه', ArabicNormalizer::normalize('الجابرية'));
        $this->assertEquals('حماه', ArabicNormalizer::normalize('حماة'));
        $this->assertEquals('الشريعه', ArabicNormalizer::normalize('الشريعة'));
    }

    public function test_strips_tashkeel_and_diacritics(): void
    {
        $this->assertEquals('الحاضر', ArabicNormalizer::normalize('الحَاضِرُ'));
    }

    public function test_cleans_non_breaking_spaces_and_unicode_whitespace(): void
    {
        $this->assertEquals('الفرقان', ArabicNormalizer::normalize("الفرقان\u{00A0}"));
        $this->assertEquals('باب الحديد', ArabicNormalizer::normalize("باب\u{00A0}الحديد"));
    }

    public function test_strips_common_administrative_prefixes(): void
    {
        $this->assertEquals('الفرقان', ArabicNormalizer::normalizeArea('حي الفرقان'));
        $this->assertEquals('الحاضر', ArabicNormalizer::normalizeArea('منطقة الحاضر'));
        $this->assertEquals('الزهراء', ArabicNormalizer::normalizeArea('بلدة الزهراء'));
    }
}
```

**Step 2: Run test to verify it fails**
Run: `php artisan test tests/Unit/ArabicNormalizerTest.php`
Expected: FAIL with "Class App\Support\ArabicNormalizer not found"

**Step 3: Implement `ArabicNormalizer`**
Create `app/Support/ArabicNormalizer.php`:
```php
<?php

namespace App\Support;

class ArabicNormalizer
{
    public static function normalize(?string $text): string
    {
        if ($text === null || $text === '') {
            return '';
        }

        // Replace non-breaking spaces and unicode whitespace
        $text = preg_replace('/[\x{00A0}\x{2000}-\x{200B}\x{202F}\x{205F}\x{3000}]/u', ' ', $text);
        $text = trim(preg_replace('/\s+/u', ' ', $text));
        $norm = mb_strtolower($text, 'UTF-8');

        // Strip diacritics / tashkeel
        $norm = preg_replace('/[\x{064B}-\x{065F}\x{0670}]/u', '', $norm);

        // Normalize Alefs
        $norm = preg_replace('/[أإآٱ]/u', 'ا', $norm);

        // Normalize Taa Marbuta to Haa
        $norm = preg_replace('/ة/u', 'ه', $norm);

        // Normalize Alef Maqsura to Yaa
        $norm = preg_replace('/ى/u', 'ي', $norm);

        return trim($norm);
    }

    public static function normalizeArea(?string $area): string
    {
        $norm = self::normalize($area);
        if ($norm === '') {
            return '';
        }

        // Strip common prefixes: حي, منطقة, بلدة, قرية, مدينة
        $norm = preg_replace('/^(حي|منطقه|بلده|قريه|مدينه)\s+/u', '', $norm);

        return trim($norm);
    }
}
```

**Step 4: Integrate `ArabicNormalizer` into `DataImportService.php`**
In `DataImportService.php`:
- Update `parseRow` for `ENTITY_DOCTORS`, `ENTITY_PHARMACIES`, and `ENTITY_HOSPITALS`:
  - Normalize governorates using `ArabicNormalizer::normalize`.
  - Normalize areas using `ArabicNormalizer::normalizeArea`.
  - Support area code matching (`$a->code`).
  - Add fallback cross-governorate match: if area matches uniquely across all areas in Syria, automatically assign the correct governorate and area.
  - In Pharmacies: replace `$dict['governorates']->firstWhere(...)` with normalized matching and avoid fatal errors when area can be resolved with warnings.

**Step 5: Run tests and verify they pass**
Run: `php artisan test tests/Unit/ArabicNormalizerTest.php`
Expected: PASS

---

### Task 2: Excel AutoFilter, Hidden Rows & Safe Formula Ingestion

**Files:**
- Modify: `app/Services/DataImportService.php:470-540`
- Test: `tests/Feature/ExcelFilterImportTest.php`

**Step 1: Write the failing feature test**
Create `tests/Feature/ExcelFilterImportTest.php` verifying that files with hidden rows (AutoFilter) only import visible rows and report filtered rows accurately without throwing CalculationExceptions.

**Step 2: Run test to verify it fails**
Run: `php artisan test tests/Feature/ExcelFilterImportTest.php`

**Step 3: Implement safe reading and filter handling in `DataImportService.php`**
1. Read rows with safe formula fallback:
   ```php
   try {
       $rows = $sheet->toArray(null, true, true, false);
   } catch (\Throwable $e) {
       $rows = $sheet->toArray(null, false, true, false);
   }
   ```
2. Detect hidden rows:
   ```php
   $hiddenRowCount = 0;
   $rowNumber = $index + 2;
   $isVisible = $sheet->getRowDimension($rowNumber)->getVisible();
   if (! $isVisible) {
       $hiddenRowCount++;
       continue; // Automatically skip rows hidden by active Excel filters
   }
   ```
3. Return `hidden_rows_skipped => $hiddenRowCount` in the preview response so the user is informed with a friendly badge.

**Step 4: Run test to verify it passes**
Run: `php artisan test tests/Feature/ExcelFilterImportTest.php`
Expected: PASS

---

### Task 3: Universal Entity Export for Bulk Round-Trip Updates

**Files:**
- Modify: `app/Services/DataImportService.php`
- Modify: `app/Http/Controllers/Admin/DataImportController.php`
- Modify: `routes/web.php`
- Test: `tests/Feature/DataExportRoundTripTest.php`

**Step 1: Write the failing feature test**
Create `tests/Feature/DataExportRoundTripTest.php` to verify:
- `GET /admin/import/export/doctors` downloads a valid `.xlsx` containing existing doctors in the template schema.
- `GET /admin/import/export/pharmacies` downloads a valid `.xlsx` containing existing pharmacies.
- Re-importing the exported file with `duplicate_strategy = 'update'` updates the existing records without duplicates.

**Step 2: Run test to verify it fails**
Run: `php artisan test tests/Feature/DataExportRoundTripTest.php`

**Step 3: Implement `exportEntity` in `DataImportService.php`**
Add:
```php
public function exportEntity(string $entity, User $user): BinaryFileResponse
```
- For Doctors: exports Name, Specialty, Classification, Governorate, Area, Clinic Name, Phone, Cooperation, Notes.
- For Pharmacies: exports Name, Pharmacist Name, Governorate, Area, Address, Phone, Is Hospital Pharmacy, Hospital Name, Notes.
- For Hospitals: exports Arabic Name, English Name, Governorate, Area, Hospital Type, Phone, Address.
- For Products: exports Arabic Name, English Name, Category, Dosage Form, Active Status.
- For Areas: exports Arabic Name, English Name, Governorate, Area Code, Active Status.
- Applies the official styling, auto-column sizing, dropdown validation, and reference guide sheets identical to `generateTemplate()`.

**Step 4: Register route and controller action**
In `routes/web.php`:
```php
Route::get('import/export/{entity}', [DataImportController::class, 'exportData'])->name('import.export');
```
In `DataImportController.php`:
```php
public function exportData(string $entity, Request $request): BinaryFileResponse
{
    return $this->importService->exportEntity($entity, $request->user());
}
```

**Step 5: Run tests to verify they pass**
Run: `php artisan test tests/Feature/DataExportRoundTripTest.php`
Expected: PASS

---

### Task 4: High-Capacity Preview with Pagination, Error Filtering & UI Enhancements

**Files:**
- Modify: `app/Services/DataImportService.php:520-545`
- Modify: `resources/js/pages/Admin/Import/Index.vue`

**Step 1: Update `DataImportService.php` preview limits**
Remove the hardcoded `count($previewRows) < 50` restriction. Return all parsed rows (up to 1,500 rows) so that no errors remain hidden.

**Step 2: Enhance `Admin/Import/Index.vue` with:**
1. **Interactive Status Filter Tabs**:
   - `الكل (All Rows)`
   - `جاهز فقط (Valid Rows)`
   - `مكرر فقط (Duplicates Only)`
   - `أخطاء تحتاج تصحيح (Errors Only)` — with badge count
2. **Client-Side Pagination**:
   - Rows per page selector: `25`, `50`, `100`, `عرض الكل (All)`
   - Page navigation (`الصفحة السابقة`, `الصفحة التالية`, page number indicator)
3. **Search / Filter Input**:
   - Instant search across name, phone, area, and error messages
4. **Excel Filter Alert Banner**:
   - When `hidden_rows_skipped > 0`, display a prominent amber banner:
     `تم اكتشاف X صفاً مخفياً في الملف (بسبب فلتر مفعّل في إكسل) وتم تجاهلها تلقائياً لضمان دقة الاستيراد.`
5. **Direct "تصدير البيانات الحالية" Button**:
   - Add a primary action button on each entity card:
     `تصدير البيانات الحالية (Excel)` linking to `/admin/import/export/${currentEntity}`.

---

### Task 5: Directory Pages One-Click Export Integration

**Files:**
- Modify: `resources/js/pages/Doctors/Index.vue`
- Modify: `resources/js/pages/Pharmacies/Index.vue`
- Modify: `resources/js/pages/Hospitals/Index.vue`
- Modify: `resources/js/pages/Products/Index.vue`
- Modify: `resources/js/pages/Admin/Areas/Index.vue`

**Step 1:**
Add an "تصدير Excel" button in the header action bar of each directory:
- Links directly to `/admin/import/export/doctors`, `/admin/import/export/pharmacies`, etc.
- Styled consistently with the Emerald design system with `<Download class="w-4 h-4 me-1.5" />`.

---

### Task 6: Multi-Project Parity & Synchronization with `creative-medvisits`

**Files:**
- Sibling repository: `../creative-medvisits/`
- Target files to synchronize:
  - `app/Support/ArabicNormalizer.php`
  - `app/Services/DataImportService.php`
  - `app/Http/Controllers/Admin/DataImportController.php`
  - `routes/web.php`
  - `resources/js/pages/Admin/Import/Index.vue`
  - `resources/js/pages/Doctors/Index.vue`
  - `resources/js/pages/Pharmacies/Index.vue`
  - `resources/js/pages/Hospitals/Index.vue`
  - `resources/js/pages/Products/Index.vue`
  - `resources/js/pages/Admin/Areas/Index.vue`
  - Relevant tests

**Step 1:**
Port all newly created and updated backend & frontend files to `../creative-medvisits/`.
Ensure brand-specific references remain cleanly decoupled via `config('whitelabel')` and `useBranding()`.

**Step 2:**
Run tests in `creative-medvisits`:
Run: `powershell -Command "cd ../creative-medvisits; php artisan test"`
Expected: ALL PASS

---

### Task 7: Production Build & cPanel Update Package Generation

**Files:**
- Target archive: `cpanel_update_import_export.zip`
- Source directories: `app/`, `resources/js/`, `routes/`, `public/build/`

**Step 1: Compile production assets in both projects**
- Run `npm run build` in `hilal-medical-visits`
- Run `npm run build` in `creative-medvisits`

**Step 2: Generate cPanel deployable update package**
Create an automated packaging PowerShell script to bundle:
- All modified PHP backend files (`app/Support/ArabicNormalizer.php`, `app/Services/DataImportService.php`, `app/Http/Controllers/Admin/DataImportController.php`, `routes/web.php`)
- All modified Vue frontend components (`resources/js/pages/Admin/Import/Index.vue`, directories index pages)
- Fresh compiled production assets (`public/build/`)
- Save as `cpanel_update_import_export.zip` in the project root so the user can directly extract it on cPanel without needing Node.js/Composer on the server.

---

### Task 8: Git Commit & Push to GitHub on Both Repositories

**Step 1: Commit and push `hilal-medical-visits`**
```bash
git add app/ resources/ routes/ tests/ docs/
git commit -m "feat(import-export): add universal entity exports, smart area normalizer, and paginated error preview"
git push origin main
```

**Step 2: Commit and push `creative-medvisits`**
```bash
cd ../creative-medvisits
git add app/ resources/ routes/ tests/
git commit -m "feat(import-export): sync universal entity exports, smart area normalizer, and paginated error preview"
git push origin main
```

---

---

### Task 9: Dynamic Entity-Aware Template & Preview Field Synchronization (Audio Feedback) - [COMPLETED]

**User Clarification**:
Ensure that the dynamic templates (Excel template download and export) and the pre-flight preview table dynamically adapt to the exact schema of each entity (Doctors, Pharmacies, Hospitals, Products, Areas). Specifically:
- **Columns Alignment**: Every entity's preview table displays the precise fields relevant to that entity:
  - **Doctors**: Name (`col1`), Specialty (`col2`), Tier Badge (`col3`), Gov/Area (`col4`), Phone (`col5`), Clinic (`col6`).
  - **Pharmacies**: Name (`col1`), Pharmacist (`col2`), Gov/Area (`col3`), Phone (`col4`), Pharmacy Type Badge (`col5`), Address (`col6`).
  - **Hospitals**: Name Ar (`col1`), Name En (`col2`), Gov/Area (`col3`), Hospital Type Badge (`col4`), Phone (`col5`), Address (`col6`).
  - **Products**: Trade Name (`col1`), Scientific Name (`col2`), Category (`col3`), Form (`col4`), Status Badge (`col5`).
  - **Areas**: Area Ar (`col1`), Area En (`col2`), Gov (`col3`), Area Code Badge (`col4`), Status Badge (`col5`).
- **Template & Export Uniformity**: Downloaded blank templates and exported existing data share the identical column headers and order, allowing seamless round-trip editing and re-import.
- **Dynamic Table Renderer in Vue**: Rather than hardcoded ternary expressions on 5 columns, implemented a reactive `tableColumns` schema definition per entity that automatically generates the appropriate `<th>` headers and formatted `<td>` cells.

---

### Implementation Status Summary

| Task | Scope | Status | Verification |
|------|-------|--------|--------------|
| Task 1 | Arabic Orthographic & Area/Governorate Normalizer (`ArabicNormalizer.php`) | Completed | 6 unit tests passing (25 assertions) |
| Task 2 | Excel AutoFilter, Hidden Rows & Safe Formula Ingestion (`DataImportService.php`) | Completed | Feature test passing (5 assertions) |
| Task 3 | Universal Entity Export (`DataImportService.php`, `DataImportController.php`, `routes/web.php`) | Completed | 2 round-trip tests passing (4 assertions) |
| Task 4 | High-Capacity Preview (1,500 rows), Pagination, Filter Tabs, Search & Dynamic UI (`Admin/Import/Index.vue`) | Completed | Vite compilation clean (0 errors) |
| Task 5 | Directory Pages Direct "تصدير Excel" Integration (5 directories) | Completed | Vite compilation clean (0 errors) |
| Task 6 | Multi-Project Parity & Synchronization (`creative-medvisits`) | Completed | 9 backend tests passing + Vite build 0 errors |
| Task 7 | Production Build & cPanel Update Package (`cpanel_update_import_export.zip`) | Completed | 0.62 MB deployable bundle verified |
| Task 8 | Git Commit & Push to GitHub on Both Repositories | Ready | Performing push to `origin main` |
| Task 9 | Dynamic Template & Preview Synchronization (Audio Feedback) | Completed | Exact schema parity verified across all 5 entities |