# Bulk Excel/photo member import and live camera capture

## Objective
Extend the member modal-first workflow with two production-grade capabilities:
1. Bulk member import from Excel/CSV with many profile pictures in one operation.
2. A switch beneath Profile photograph between normal upload and live device-camera capture with preview.

## Environment
- PHP 8 / PDO MySQL.
- Available extensions include `zip`, `dom`, `SimpleXML`, `xmlreader`, `xmlwriter`, `fileinfo`; no Composer/PhpSpreadsheet.
- Existing shared admin shell/modal controller and secure single-member processor.

## Scope
Modify/create as needed:
- `admin/member_view.php`
- `admin/member_add_modal.php`
- `admin/member_add.php`
- new bulk import modal/partial such as `admin/member_import_modal.php`
- new `admin/process_member_import.php`
- new `admin/member_import_template.php`
- reusable parser/helper such as `app/member_import.php` or `admin/includes/member_import.php`
- `assets/portal/css/admin.css`
- `assets/portal/js/admin.js`

Do not modify schema. Do not add external dependencies or make network calls. Do not insert test members.

## Permissions and entry points
- Only users with `members.create` can see or use import/camera creation capabilities.
- In Member View intro, keep primary “Add new member” and add a clearly visible secondary glass “Import members” action. Both open accessible modals.
- Direct POST endpoints independently enforce staff authentication, `members.create`, POST, and CSRF.

## Import UX
Create a polished Import Members modal that explains a simple three-step process:
1. Download template.
2. Fill Excel/CSV and name photos in the `image_filename` column.
3. Upload spreadsheet plus photo ZIP, or upload one complete bundle ZIP.

Provide:
- “Download import template” real endpoint producing a UTF-8 BOM CSV that Excel opens correctly.
- Mode switch/tabs:
  - Spreadsheet + photo ZIP: `.xlsx` or `.csv` spreadsheet, optional/expected `.zip` photos archive.
  - Complete bundle ZIP: one `.zip` containing a single `members.xlsx` or `members.csv` (case-insensitive or clearly documented) plus photos in root or `photos/`.
- Clear accepted formats/limits and `image_filename` matching example.
- One submit action “Validate and import members.”
- No fake drag/drop unless fully functional.
- After processing, redirect to `member_view` with an accessible toast summary. For errors, store a bounded row-level summary in session and render a polished dismissible import-result panel/modal on the member list; do not expose stack traces.

## Spreadsheet columns
Required headers:
`first_name`, `last_name`, `gender`, `date_of_birth`, `residence`, `phone_number`, `email`, `ministry`, `member_type`, `image_filename`

- Email and image_filename may be blank if supported; all other profile fields follow existing single-member requirements.
- `ministry` accepts an exact case-insensitive ministry name or a valid ministry ID.
- Member type allowlist currently `Active Member`.
- Gender allowlist `Male`, `Female`.
- Dates accept `YYYY-MM-DD`; for XLSX also convert numeric Excel dates correctly.
- Normalize headers (trim/lowercase/spaces to underscores) but require all named columns so mapping is never ambiguous.

## XLSX/CSV parsing
- Implement focused first-sheet XLSX reader using `ZipArchive` plus XMLReader/SimpleXML: workbook relationships, shared strings, inline strings, booleans/numbers, cell references, missing-cell gaps, and date value handling needed for this template.
- Never evaluate formulas, macros, hyperlinks, external relationships, or embedded content.
- `.xls` is not supported; say so clearly.
- CSV delimiter detection for comma/semicolon/tab; UTF-8/BOM handling; reject invalid encoding safely.
- Cap rows at 1,000 and reject empty/duplicate headers.

## ZIP/photo safety
- Max spreadsheet around 10 MB; ZIP around 50 MB; per-photo 5 MB; bounded total uncompressed photo bytes (e.g. 100 MB) and entry count.
- Never extract archive paths directly. Read matching entries safely; reject traversal, absolute paths, encrypted/unsupported files, duplicate basenames, and zip bombs by size/ratio limits.
- Match `image_filename` by safe basename, case-insensitively, whether in archive root or `photos/`.
- MIME-inspect JPEG/PNG/WebP content with `fileinfo`; generate unique random stored filenames/extensions.
- Empty image_filename is allowed and stores NULL, with existing church-logo fallback in UI. If a filename is supplied it must exist and validate.

## Batch validation and database safety
- Validate every row before any DB insert: lengths, names, phone, email, DOB, gender/type, ministry, photo, duplicates within file and existing DB.
- All-or-nothing: if any row fails, import zero members and return bounded errors (for example first 50 plus total count). State this in UI.
- If clean, use one transaction. Lock/generate sequential `JSOW###` usernames safely for the batch and generate secure random initial passwords.
- Move prepared image files safely; on any DB/filesystem failure roll back DB and delete every image written by this import.
- Do not send welcome SMS during bulk imports; state that bulk records are imported silently and follow-up can happen later.
- Audit/log a safe import summary if existing audit helper permits.

## Live camera in member form
Under Profile photograph add a segmented switch:
- “Upload photo” (default)
- “Take live photo”

Upload mode uses the existing secure file input.

Camera mode:
- Use `navigator.mediaDevices.getUserMedia({video:{facingMode:{ideal:'environment'}},audio:false})` on HTTPS/localhost.
- Show live `<video>` preview, “Capture photo,” “Retake,” and “Use this photo.”
- Capture to canvas/Blob as JPEG, convert to a `File`, and set the existing `image` input via `DataTransfer`, so backend validation remains unchanged.
- Show captured preview and clear state. Stop every media stream track when captured/retaken as appropriate, modal closes, form submits, mode switches, or page unloads.
- If camera permission/API is unavailable, show an inline accessible error/toast and switch back to upload without breaking the form.
- Never auto-start camera just by opening Add Member; only after explicit “Take live photo.”
- Standalone fallback page receives the same form/camera capability.

## Visual direction
Keep the forest/photo/lemon/glass admin system. Import modal should feel like a calm data intake assistant; camera should feel like a compact identity booth, not a raw browser widget. Large readable controls, clear progress/instructions, no excessive pills.

## Responsive/accessibility
- Modals fit 1440, 1024, 768, 430, 375; internal scroll; actions reachable above bottom nav.
- Keyboard operable switches/buttons, labeled video/status, focus management via existing modal system.
- Touch targets >=44px, reduced motion respected, no native alert.

## Verification
- PHP lint all changed/new PHP.
- Unit-like parser tests using generated tiny CSV and XLSX fixtures without importing to DB; verify shared strings, missing cells, Excel date conversion, invalid headers.
- ZIP safety tests for traversal/duplicate basenames/missing photo without writing outside temp.
- Confirm no DB rows are inserted during tests.
- Browser/static test camera state logic; camera hardware permission may be unavailable in automation, so verify graceful fallback.
- Authenticated Member View HTTP 200, both actions/modal markup present for creator.
- Update CSS/JS cache keys.
