# Bulk Import External Jobs - Feature Guide

## Overview
Super admins can now bulk import external job postings (jobs that redirect to external application URLs) using Excel or CSV templates.

## Feature Location
**Admin Panel → Jobs → Bulk Import**
- Route: `/admin/jobs/import`
- Controller: `App\Http\Controllers\Admin\JobImportController`
- View: `resources/views/admin/jobs/import.blade.php`

## Access Control
- **Middleware**: `auth:admin`, `admin`
- **Role Required**: Super Admin or Operations Admin
- **Routes Protected**:
  - `admin.jobs.import.create` (GET /admin/jobs/import)
  - `admin.jobs.import.store` (POST /admin/jobs/import)
  - `admin.jobs.import.template` (GET /admin/jobs/import/template)

## Features

### 1. Template Downloads
Two template formats available:

#### Excel Template (.xlsx) - Recommended
- **Features**:
  - Professional styling with indigo headers
  - Column descriptions in row 2
  - Separate "Instructions" sheet with complete guide
  - Two sample rows with realistic data
  - Frozen header rows for easy navigation
  - 26 pre-formatted columns

- **Download URL**: `/admin/jobs/import/template?format=xlsx`

#### CSV Template (.csv) - Basic
- **Features**:
  - Simple text format
  - Header row with column names
  - Two sample rows
  - Compatible with all spreadsheet software

- **Download URL**: `/admin/jobs/import/template?format=csv`

### 2. Template Fields (26 Columns)

| Column | Type | Required | Description |
|--------|------|----------|-------------|
| `employer_email` | Email | Yes | Employer contact email (auto-creates employer if not exists) |
| `employer_name` | String | No | Employer contact name |
| `company_name` | String | No | Organization/company name |
| `job_title` | String | Yes | Position title |
| `location_city` | String | No | City location |
| `location_state` | String | No | State/Province (if applicable) |
| `location_country` | String | Yes | Country location |
| `employment_type` | Enum | No | Options: full-time, part-time, contract, per-diem, temporary |
| `work_mode` | Enum | No | Options: onsite, hybrid, remote |
| `visa_sponsorship` | Boolean | No | "true" or "false" |
| `relocation_package` | Boolean | No | "true" or "false" |
| `housing_provided` | Boolean | No | "true" or "false" |
| `required_license` | String | No | License/certification required (e.g., "NY RN (NCLEX)") |
| `required_experience_years` | Integer | No | Years of experience required |
| `required_specialty` | String | No | Medical specialty (e.g., "ICU", "Public Health") |
| `required_language` | String | No | Language requirements |
| `salary_min` | Numeric | No | Minimum salary (no commas or symbols) |
| `salary_max` | Numeric | No | Maximum salary (no commas or symbols) |
| `currency` | String | No | 3-letter ISO code (USD, EUR, GBP, etc.) |
| `contract_length_months` | Integer | No | Contract duration in months |
| `application_deadline` | Date | No | Format: YYYY-MM-DD |
| `summary` | Text | No | Brief job summary |
| `description` | Text | No | Detailed job description |
| `external_application_url` | URL | **Yes** | Full URL where candidates apply (required for external jobs) |
| `status` | Enum | No | Options: open, closed, paused, draft (default: open) |
| `published_at` | Date | No | Format: YYYY-MM-DD (defaults to today) |

### 3. Sample Data Included

The templates include two realistic samples:

**Sample 1**: US Public Health Nurse
- Location: New York, NY, United States
- Visa sponsorship: Yes
- Salary: $82,000 - $98,000 USD
- License: NY RN (NCLEX)

**Sample 2**: UK ICU Nurse
- Location: London, United Kingdom
- Visa sponsorship: Yes
- Housing provided: Yes
- Salary: £50,000 - £65,000 GBP
- License: NMC Registration

### 4. Import Process

#### Step 1: Download Template
1. Navigate to `/admin/jobs/import`
2. Click "Excel Template (.xlsx)" or "CSV Template (.csv)"
3. Template downloads with sample data

#### Step 2: Fill Template
1. Open downloaded template in Excel, Google Sheets, or any spreadsheet software
2. Review the Instructions sheet (Excel only)
3. Delete sample rows (rows 3-4)
4. Add your job data (one job per row)
5. Ensure `external_application_url` is filled for each job
6. Save the file

#### Step 3: Upload File
1. Return to `/admin/jobs/import`
2. Click "Upload a file" or drag and drop
3. Select your filled template (.xlsx, .xls, or .csv)
4. Click "Import Jobs"

#### Step 4: Review Results
- Success message shows: `{count} jobs imported successfully`
- Errors displayed with specific issues (invalid format, missing fields, etc.)

### 5. What Happens During Import

1. **File Validation**
   - Checks file format (CSV, XLS, XLSX accepted)
   - Validates column headers match template
   - Ensures file is not empty

2. **Employer Processing**
   - Checks if employer exists by email
   - Creates new employer user if not found:
     - Username: `employer_{unique_id}`
     - Email: from template
     - Name: from template
     - Role: `employer`
     - Password: randomly generated
     - Email verified: Yes

3. **Job Creation**
   - Each row creates a new `JobPosting` record
   - `external_application_url` field populated
   - `employer_id` linked to employer
   - Status defaults to "open" if not specified
   - `published_at` defaults to current date if not specified

4. **Data Transformations**
   - Boolean fields: converts "true"/"false" strings to actual booleans
   - Dates: parses YYYY-MM-DD format
   - Empty values: stored as NULL
   - Salary: stored as integers (no formatting)

### 6. Statistics Dashboard

The import page displays:
- **Total Jobs**: Count of all jobs in system
- **External Jobs**: Count of jobs with `external_application_url` set
- **Last Import**: Human-readable time since last external job import (e.g., "2 hours ago")

## Technical Implementation

### Controller Methods

```php
// Show import form with statistics
JobImportController::create()

// Download template (format: xlsx or csv)
JobImportController::template(Request $request)

// Process uploaded file and create jobs
JobImportController::store(Request $request)

// Generate Excel template with styling
JobImportController::generateExcelTemplate($columns, $descriptions, $samples)

// Generate CSV template
JobImportController::generateCsvTemplate($columns, $samples)

// Read uploaded Excel file
JobImportController::readExcel($path)

// Read uploaded CSV file
JobImportController::readCsv($path)

// Get column names array
JobImportController::columns()

// Get column descriptions array
JobImportController::columnDescriptions()
```

### Dependencies

**PhpSpreadsheet** (already included in Laravel)
```php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
```

### File Validation Rules

```php
$request->validate([
    'file' => ['required', 'file', 'mimes:csv,txt,xlsx,xls'],
]);
```

### Database Transaction

The import uses a database transaction to ensure all jobs are imported successfully or none at all (rollback on error).

## Error Handling

### Common Errors

1. **"The uploaded file is empty"**
   - Solution: Ensure file has at least one data row

2. **"Invalid columns. Please use the provided template"**
   - Solution: Column headers must match exactly (download fresh template)

3. **"The file field is required"**
   - Solution: Select a file before clicking Import

4. **"The file must be a file of type: csv, txt, xlsx, xls"**
   - Solution: Only CSV and Excel formats accepted

### Validation During Import

- Email format validation for `employer_email`
- URL format validation for `external_application_url`
- Date format validation (YYYY-MM-DD)
- Enum validation for `employment_type`, `work_mode`, `status`

## Best Practices

### For Admins

1. **Always download a fresh template** before starting a new import
2. **Keep the header row** (row 1) - do not modify column names
3. **Use the Excel template** for better guidance and formatting
4. **Test with 1-2 jobs first** before bulk importing hundreds
5. **Check the statistics** after import to verify count
6. **Backup data** before large imports

### For Data Entry

1. **External URL is mandatory** - verify each URL is valid and active
2. **Use consistent formats**:
   - Dates: 2025-12-31 (not 12/31/2025)
   - Booleans: "true" or "false" (not "yes"/"no" or "1"/"0")
   - Currency: "USD", "EUR", "GBP" (3 letters)
   
3. **Salary values**:
   - Enter as numbers only: 50000 (not $50,000 or 50,000 USD)
   - Min should be less than Max

4. **Employer information**:
   - Use same email for jobs from same employer (prevents duplicates)
   - Provide full organization name in `company_name`

## Future Enhancements

Potential improvements:
- [ ] Bulk update existing jobs (not just create)
- [ ] Import preview before committing
- [ ] Downloadable error log for failed rows
- [ ] Support for images/logos import
- [ ] Scheduled imports from external APIs
- [ ] Email notifications on import completion
- [ ] Import history tracking

## Testing

### Manual Test Steps

1. **Download Template Test**
   ```
   GET /admin/jobs/import/template?format=xlsx
   GET /admin/jobs/import/template?format=csv
   ```
   - Verify file downloads
   - Check sample data is present
   - Verify formatting (Excel only)

2. **Import Valid File Test**
   - Upload template with sample data
   - Verify success message
   - Check database for new jobs
   - Verify external_application_url is set

3. **Import Invalid File Test**
   - Upload wrong format (e.g., .txt, .pdf)
   - Upload file with wrong columns
   - Upload empty file
   - Verify error messages

4. **Employer Auto-Creation Test**
   - Import jobs with new employer email
   - Verify new employer user created
   - Import more jobs with same email
   - Verify no duplicate employers

## Support & Troubleshooting

### File Not Importing

1. Check file format is .xlsx, .xls, or .csv
2. Verify column headers exactly match template
3. Ensure external_application_url column has valid URLs
4. Check Laravel logs: `storage/logs/laravel.log`

### Employer Not Created

- Verify employer email is valid format
- Check `users` table for existing user with role `employer`
- Review `employer_email` column in template

### Jobs Not Appearing

1. Check job status (might be "draft" or "closed")
2. Verify `published_at` date is not in future
3. Check if jobs were actually created: `SELECT * FROM job_postings ORDER BY created_at DESC LIMIT 10`

## Related Files

```
cura-app/
├── app/
│   └── Http/
│       └── Controllers/
│           └── Admin/
│               └── JobImportController.php (475 lines)
├── resources/
│   └── views/
│       └── admin/
│           └── jobs/
│               └── import.blade.php (171 lines)
├── routes/
│   └── web.php (lines 264-266)
└── database/
    └── seeders/
        └── InternationalHealthcareJobsSeeder.php (for reference)
```

## Changelog

### Version 1.0 (January 2025)
- ✅ Excel and CSV template generation
- ✅ Bulk import with employer auto-creation
- ✅ External application URL support
- ✅ Statistics dashboard
- ✅ Comprehensive field validation
- ✅ Instructions sheet in Excel template
- ✅ Sample data in templates
- ✅ Error handling and user feedback

---

**Last Updated**: January 2025
**Feature Status**: ✅ Production Ready
**Maintained By**: K&A Development Team