# FR EPR Registration Processor

Laravel service that processes French Extended Producer Responsibility (EPR) registration records: queries source DB, enriches with INSEE API data, generates DOCX/XLSX files, uploads to Tencent Cloud OSS, and tracks results in a target DB.

## Commands

```bash
# Continuous EPR processor daemon (8s poll interval; exits with code 1 after 10 consecutive DB failures)
php artisan epr:process

# Dry run (one-shot query only, no file generation)
php artisan epr:process --dry-run

# Combined mail monitor daemon (recommended: single IMAP connection, Léko + CITEO, 8s interval)
php artisan epr:mail-monitor

# Standalone Léko mail monitor daemon (IMAP polling for Stripe invoices, 8s interval)
php artisan epr:leko-monitor

# Standalone CITEO invoice monitor daemon (IMAP polling for CITEO invoices, 8s interval)
php artisan epr:citeo-monitor

# Run target DB migration
php artisan migrate --database=sqlsrv_target
```

## Architecture

```
while(true) {
  Source DB (sqlsrv_source)          ← SELECT TOP 1
    → SourceRepository.fetchPendingRecords()
    → validateRequiredFields()       ← check BusinessSerialNumber, NameEng, EprRegInfoId, LegalPersonFullNamePinYin; RegisteredCapital + RegisteredCapitalCurrency (FR only)
    → [empty field] → markAsFailed(id, error) → WeChat notification → next cycle
    → InseeApiClient.fetchCompanyData(siren)  [3 extra fields, FR only]
    → [INSEE failure] → markAsFailed(id, error) → WeChat notification → next cycle
    → DocxGenerator.generate(record)          [power of attorney]
    → XlsxGenerator.generate(records)         [LEKO registration sheet]
    → OssUploader.upload(path, dir, fileName)  [Tencent Cloud OSS, path: epr_factory/fr/reg]
    → TargetRepository.getLatestAttachmentIds(eprRegInfoId)  [fetch prev F_Ids]
    → TargetRepository.createRecord(...)      [sqlsrv_target, returns new row id]
    → SourceStatusUpdater.markAsProcessed(id) [sqlsrv_source, PushTaxBureauStatus → 6]
    → SourceAttachmentRepository.insertAttachmentRecords(...) [sqlsrv_source, returns F_Ids]
    → TargetRepository.updateAttachmentIds(...)  [write F_Ids back to target row]
    → SourceAttachmentRepository.deleteByFIds(...) [delete prev Base_AnnexesFile rows if existed]
    → WeChatNotificationService.sendNotification(bsn, nameEng, "成功生成文件", mobile)
    → [failure] → markAsFailed(id, error) → WeChat notification → next cycle
}
```

## Services

| Service | File | Purpose |
|---------|------|---------|
| SourceRepository | `app/Services/EprReg/SourceRepository.php` | Query 1 pending FR record per cycle (SELECT TOP 1) |
| InseeApiClient | `app/Services/EprReg/InseeApiClient.php` | Fetch legal_form, naf_code, siret_siege from INSEE SIRENE API |
| SignatureGenerator | `app/Services/EprReg/SignatureGenerator.php` | Generate PNG signature from TTF fonts (random from 7 fonts in `storage/fonts/`) |
| WordTemplateProcessor | `app/Services/EprReg/WordTemplateProcessor.php` | ZipArchive-based DOCX template placeholder replacement + inline image embedding |
| DocxGenerator | `app/Services/EprReg/DocxGenerator.php` | Generate power-of-attorney DOCX from template (includes inline signature image) |
| XlsxGenerator | `app/Services/EprReg/XlsxGenerator.php` | Generate LEKO registration XLSX from template (memory_limit raised to 512M during generation) |
| EprReadFilter | `app/Services/EprReg/EprReadFilter.php` | PhpSpreadsheet read filter (row/column range) used by XlsxMergeService to read specific XLSX rows |
| PdfConverter | `app/Services/EprReg/PdfConverter.php` | DOCX→PDF orchestrator: LibreOffice convert then PdfNormalizer with firstPageOnly=true |
| LibreOfficeConverter | `app/Services/EprReg/LibreOfficeConverter.php` | Wrap LibreOffice headless PDF conversion with flock concurrency control |
| PdfNormalizer | `app/Services/EprReg/PdfNormalizer.php` | Resize PDF to A4 portrait via Ghostscript (primary) or PyMuPDF fallback |
| SoftwarePathResolver | `app/Services/EprReg/SoftwarePathResolver.php` | Auto-detect LibreOffice/Ghostscript paths, cache to `storage/cache/software_paths.json` |
| OssUploader | `app/Services/EprReg/OssUploader.php` | Upload files to Tencent Cloud OSS (S3 protocol); decodes `%XX` in the returned URL path so filenames are readable (query string untouched) |
| FileDownloader | `app/Services/EprReg/FileDownloader.php` | Stream-download a file from URL to disk via Http::sink() (used by merge-xlsx / send-registration-mail APIs) |
| TargetRepository | `app/Services/EprReg/TargetRepository.php` | Insert/update records in target DB |
| SourceStatusUpdater | `app/Services/EprReg/SourceStatusUpdater.php` | Update PushTaxBureauStatus in source DB (6=files generated, 3=推送成功, 7=failure with Remarks) |
| SourceAttachmentRepository | `app/Services/EprReg/SourceAttachmentRepository.php` | Insert DOCX/XLSX attachment records into source DB |
| FrenchFileGenerationService | `app/Services/EprReg/French/FrenchFileGenerationService.php` | French unified LEKO/CITEO file-generation API orchestration (API_Flow: zero source-DB reads/writes; LEKO France queries INSEE via LekoInseeResolver) |
| LekoInseeResolver | `app/Services/EprReg/LekoInseeResolver.php` | LEKO API_Flow INSEE decision + retry (FR only, SIREN from RegNumber, 5x/6s) |
| LekoApiFileGenerator | `app/Services/EprReg/LekoApiFileGenerator.php` | LEKO XLSX + POA PDF generation for API_Flow (reuses XlsxGenerator/DocxGenerator/PdfConverter/OssUploader) |
| CiteoPoaGenerator | `app/Services/EprReg/CiteoPoaGenerator.php` | Pure CITEO POA generator (delegated by legacy CiteoPoaService) |
| FrenchMergeService | `app/Services/EprReg/French/FrenchMergeService.php` | French LEKO merge API orchestration (API_Flow: OSS key in/out on the API bucket, zero DB access; delegates row merging to XlsxRowMerger) |
| FrenchRegistrationMailService | `app/Services/EprReg/French/FrenchRegistrationMailService.php` | French LEKO registration-mail API orchestration (API_Flow: builds POA.zip from OSS keys, checks row/PDF count, sends the Léko mail; zero DB access) |
| FrenchApiOssKeyValidator | `app/Services/EprReg/French/FrenchApiOssKeyValidator.php` | Validates API-flow OSS keys (relative path, allowed `{OSS_API_PREFIX}{year}/{OSS_API_MODULE_DIR}/` scope, no traversal) and streams objects to temp files |
| XlsxRowMerger | `app/Services/EprReg/XlsxRowMerger.php` | Shared XLSX row merger (row 14+, cols A–AJ, FORMAT_TEXT) used by both `/api/epr/merge-xlsx` and `/api/epr/fr/merge-xlsx` |
| LekoRegistrationMailer | `app/Services/EprReg/LekoRegistrationMailer.php` | Shared Léko registration mail sender + XLSX data-row counter (used by `send-registration-mail` and `/api/epr/fr/registration-mail`) |
| LekoRpa (task repo / claim / result / notifier) | `app/Services/EprReg/LekoRpa/` | API-flow UIN task store on `fr_epr_reg` (`data_source='api'`), atomic claim with lease, result write + SaaS delivery callback (§13) with retry/logging |
| WeChatNotificationService | `app/Services/EprReg/WeChatNotificationService.php` | Send enterprise WeChat webhook notifications (success/failure) |
| BaseMailMonitor | `app/Services/EprReg/BaseMailMonitor.php` | Abstract base class: shared IMAP connection (TCP→SSL→banner pre-check), folder query, main loop, env restoration |
| CombinedMailMonitor | `app/Services/EprReg/CombinedMailMonitor.php` | Orchestrator: single IMAP connection per cycle, processes Léko + CITEO sequentially (independent try/catch per folder) |
| MailInvoiceMonitor | `app/Services/EprReg/MailInvoiceMonitor.php` | Léko mail handler (extends BaseMailMonitor): Stripe email → parse → OCR+AI → COS upload → source lookup → AgencyBills → EPRRegInfo update → save |
| MailInvoiceParser | `app/Services/EprReg/MailInvoiceParser.php` | Parse email subject (EN/FR) and HTML body for invoice fields |
| CiteoMailMonitor | `app/Services/EprReg/CiteoMailMonitor.php` | CITEO mail handler (extends BaseMailMonitor): CITEO email → parse → OCR+AI → source lookup → AgencyBills → save |
| CiteoInvoiceParser | `app/Services/EprReg/CiteoInvoiceParser.php` | Parse CITEO email body (N° Client, N° FACTURE, company name) |
| BaiduOcrClient | `app/Services/EprReg/BaiduOcrClient.php` | OCR PDF attachments via Baidu general_basic API |
| ArkModelClient | `app/Services/EprReg/ArkModelClient.php` | Extract invoice fields from OCR text via Volcano Doubao model |
| XlsxMergeService | `app/Services/EprReg/XlsxMergeService.php` | Merge multiple XLSX attachments into one file |
| RegistrationMailService | `app/Services/EprReg/RegistrationMailService.php` | Send registration XLSX + PDF ZIP to Léko via SMTP, update source DB status |
| CiteoPoaService | `app/Services/EprReg/CiteoPoaService.php` | Generate CITEO POA PDF from an EPRRegInfo ID (query source DB -> DOCX template -> PDF -> COS upload) |
| RefashionPoaService | `app/Services/EprReg/RefashionPoaService.php` | Generate Refashion (textile law / ECO TLC) POA PDF from an EPRRegInfo ID (ECO TLC scoped, text-only template fill -> PDF -> COS upload) |
| RefashionPoaNotifier | `app/Services/EprReg/RefashionPoaNotifier.php` | Post-generation customer-manager notification (email PDF attachment / WeChat @18381322048 fallback, best-effort) - **currently disabled**: `RefashionPoaService` no longer calls it; class retained for re-enablement |
| RefashionUinCertificateService | `app/Services/EprReg/RefashionUinCertificateService.php` | Generate Refashion (textile law) UIN certificate PDF from API-passed company data (template fill -> PDF -> COS `refashion_uin/` upload) |
| SafeImapClient | `app/Services/EprReg/Imap/SafeImapClient.php` | Extends webklex Client to use SafeLegacyProtocol (null-safe IMAP STATUS) |
| SafeLegacyProtocol | `app/Services/EprReg/Imap/SafeLegacyProtocol.php` | Extends webklex LegacyProtocol: null-safe examineFolder() tolerating partial IMAP STATUS responses |
| EprRegProcessor | `app/Services/EprReg/EprRegProcessor.php` | Orchestrate the full pipeline (infinite polling loop) |
| FileNameSanitizer | `app/Services/EprReg/FileNameSanitizer.php` | Strip Windows-invalid + URL-breaking chars (`\ / : * ? " ' < > | # %`), control chars -> space, trim; used for all POA/UIN certificate filenames (LEKO, CITEO, Refashion) |

## Environment Variables

| Variable | Purpose | Required |
|----------|---------|----------|
| `DB_SOURCE_*` | Source SQL Server connection (host, port, database, username, password, charset) | Yes |
| `DB_TARGET_*` | Target SQL Server connection | Yes |
| `OSS_ACCESS_KEY_ID` | Tencent Cloud COS access key | Yes |
| `OSS_SECRET_ACCESS_KEY` | Tencent Cloud COS secret key | Yes |
| `OSS_BUCKET` | COS bucket name | Yes |
| `INSEE_API_KEY` | INSEE SIRENE API key (register at portail-api.insee.fr) | Yes |
| `WECHAT_WEBHOOK_URL` | Enterprise WeChat robot webhook URL | Yes |
| `SMTP_MAIL_HOST` | SMTP host for registration mail (Aliyun enterprise email) | Yes (for send-registration-mail) |
| `SMTP_MAIL_PORT` | SMTP SSL port (465) | Yes |
| `SMTP_MAIL_USERNAME` | SMTP auth username (also used for IMAP) | Yes |
| `SMTP_MAIL_PASSWORD` | SMTP auth password (also used for IMAP) | Yes |
| `SMTP_MAIL_TO` | Registration mail recipient (Léko contact email) | Yes |
| `REFASHION_SMTP_HOST` | Refashion SMTP host (Aliyun enterprise email, separate account from Léko `SMTP_MAIL_*`) | Yes (for send-refashion-mail) |
| `REFASHION_SMTP_PORT` | Refashion SMTP SSL port (465) | Yes (for send-refashion-mail) |
| `REFASHION_SMTP_USERNAME` | Refashion registration mail sender (Aliyun enterprise email, `fr-epr@seamew.de`, separate from Léko `SMTP_MAIL_*`) | Yes (for send-refashion-mail) |
| `REFASHION_SMTP_PASSWORD` | Refashion SMTP auth password | Yes (for send-refashion-mail) |
| `REFASHION_REG_MAIL_TO` | Refashion registration mail recipient (default: `hotline@refashion.fr`) | Yes (for send-refashion-mail) |
| `IMAP_HOST` | IMAP host for mail monitor (default: imap.qiye.aliyun.com) | Yes (for epr:leko-monitor) |
| `IMAP_PORT` | IMAP port (default: 993) | Yes |
| `IMAP_ENCRYPTION` | IMAP encryption (default: ssl) | Yes |
| `BAIDU_OCR_API_KEY` | Baidu OCR API key for PDF invoice recognition | Yes (for epr:leko-monitor) |
| `BAIDU_OCR_SECRET_KEY` | Baidu OCR API secret key | Yes |
| `VOLCANO_API_KEY` | Volcano Ark model API key for invoice field extraction | Yes |
| `VOLCANO_MODEL` | Volcano Doubao model name (default: doubao-seed-1-6-251015) | No |
| `LIBREOFFICE_PATH` | LibreOffice executable path (optional, auto-detected if empty) | No |
| `LIBREOFFICE_MAX_CONCURRENT` | Max concurrent LibreOffice instances (default: 2) | No |
| `GHOSTSCRIPT_PATH` | Ghostscript executable path (optional, auto-detected if empty) | No |
| `LOG_EPR_DAYS` | EPR daily log retention days (default: 30) | No |
| `LOG_MAIL_DAYS` | Mail invoice log retention days (default: 30) | No |
| `LOG_CITEO_DAYS` | CITEO invoice log retention days (default: 30) | No |
| `LOG_API_DAYS` | API log retention days for all `api_*` channels (default: 30) | No |
| `INTERNAL_NETWORK_ENFORCE` | Enforce `internal.network` (172.16.0.0/16) IP check on internal APIs (default `true`; set `false` for local dev off-subnet) | No |
| `FR_FILE_API_AUTH_ENABLED` | Enable optional Bearer auth for the French unified API (default `false`) | No |
| `FR_FILE_API_AUTH_TOKEN` | Bearer token checked when `FR_FILE_API_AUTH_ENABLED=true` | No (required if auth enabled) |
| `FR_FILE_API_MAX_BODY_BYTES` | Max request body size for the French unified API (default 1MB) | No |
| `FR_FILE_API_RATE_LIMIT_PER_MINUTE` | Per-IP rate limit for the French unified API (default 60; `0` disables) | No |

## Testing

```bash
vendor/bin/phpunit
# Template-dependent tests auto-skip if templates missing from storage/
```

## API Endpoints

| Endpoint | Method | Auth | Description |
|----------|--------|------|-------------|
| `/fr_epr_reg/api/epr/merge-xlsx` | POST | `internal.network` (172.16.x.x) | Merge multiple XLSX attachments into one file. See `docs/merge-xlsx-api.md`. |
| `/fr_epr_reg/api/epr/send-registration-mail` | POST | `internal.network` + `parse.large.json` | Send merged XLSX + PDF ZIP to Léko, update source status. See `docs/send-registration-mail-api.md`. |
| `/fr_epr_reg/api/epr/send-refashion-mail` | POST | `internal.network` + `parse.large.json` | Send POA ZIP to Refashion (ECO TLC), update source status. See `docs/refashion-mail-api.md`. |
| `/fr_epr_reg/api/epr/generate-citeo-poa` | POST | `internal.network` (172.16.x.x) | Generate CITEO POA PDF from an EPRRegInfo ID. See `docs/citeo-poa-api.md`. |
| `/fr_epr_reg/api/epr/generate-refashion-poa` | POST | `internal.network` (172.16.x.x) | Generate Refashion (textile / ECO TLC) POA PDF from an EPRRegInfo ID. See `docs/refashion-poa-api.md`. |
| `/fr_epr_reg/api/epr/generate-refashion-uin-certificate` | POST | `internal.network` (172.16.x.x) | Generate Refashion (textile law) UIN certificate PDF from passed company data (no source DB query). See `docs/refashion-uin-certificate-api.md`. |
| `/fr_epr_reg/api/epr/fr/file-generation` | POST | `fr.api.transport` + optional Bearer (`FR_FILE_API_AUTH_ENABLED`) + per-IP rate limit | Unified LEKO/CITEO synchronous file generation for the SaaS relay: `FR_EPR_REGISTER_LEKO_FILE` (XLSX + POA) / `FR_EPR_REGISTER_CITEO_FILE` (POA); OSS keys land under `{OSS_API_PREFIX}{year}/{OSS_API_MODULE_DIR}/{leko|citeo}/…` in the private API bucket (objects stay private; consumers sign); zero source-DB reads/writes, LEKO France queries INSEE server-side. See `docs/french-file-generation-api.md`; full contract in `E:\ou\meiou-app\app_withdrawn\docs\UNIFIED_API_DESIGN.md` §4.4.21（LEKO）/ §4.4.7（CITEO）. |
| `/fr_epr_reg/api/epr/fr/merge-xlsx` | POST | same three guards (shared per-IP quota) | Merge 2–50 generated LEKO XLSX (API-bucket OSS relative paths passed back in `Data.files[]`) into one company list and write it to `{OSS_API_PREFIX}{year}/{OSS_API_MODULE_DIR}/merged/`; zero DB access. See `docs/french-merge-xlsx-api.md`; contract §4.4.22. |
| `/fr_epr_reg/api/epr/fr/registration-mail` | POST | same three guards (shared per-IP quota) | Take the merged XLSX + 1–100 POA PDFs by OSS key, build `POA.zip`, require XLSX data-row count == POA count, then email the registration dossier to Léko; zero source-DB access — on success persists one LEKO UIN task row per company (`fr_epr_reg`, `data_source='api'`). See `docs/french-registration-mail-api.md`; contract §4.4.23. |
| `/fr_epr_reg/api/epr/leko-rpa/claim` | POST | `internal.network` | Claim one API-flow UIN task (only `data_source='api'` rows whose registration succeeded); returns source-aligned fields + registration-file keys with server-signed download URLs (10 min) + echoed `bizParam`. See `docs/french-rpa-leko-api.md`. |
| `/fr_epr_reg/api/epr/leko-rpa/result` | POST | `internal.network` | Submit the UIN (`reg_code`) and certificate OSS key for a claimed task; the service stores them and calls the SaaS delivery async callback (§13). Idempotent for completed tasks. See `docs/french-rpa-leko-api.md`. |

## RPA Scripts (`python/`)

Léko post-registration RPA helpers (UIN / certificate), called by the RPA flow rather than by this service:

| Script | Purpose |
|--------|---------|
| `rpa_leko_step1_get.py` | Claim one pending task (`PushTaxBureauStatus=3` + LEKO 包装法注册 filters, oldest `ModificationDate` first) and bump `ModificationDate` as a lease |
| `rpa_leko_step1_search_error_sendmsg.py` | Enterprise WeChat alert when a company matches multiple records |
| `rpa_leko_step1_search_error_update_reginfo.py` | Write error status/remarks back to `EPRRegInfo` |
| `rpa_leko_step2_upload_oss.py` | Upload the certificate PDF from the portal to Tencent COS (credentials passed in by the caller) |
| `rpa_leko_step3_save.py` | Persist success: insert the `Base_AnnexesFile` certificate row, set `EPRRegInfo.RegBackNumber` and `PushTaxBureauStatus=4` |
| `rpa_leko_step3_save_sucess_sendmsg.py` | Success notification (currently empty) |

- Dependencies: `pymssql`, `cos-python-sdk-v5`, `requests`. DB connection arguments are function parameters (in-script defaults are placeholders only).
- `PushTaxBureauStatus=4` means **UIN issued** (`RegBackNumber` written by the RPA).
- The `/api/epr/fr/*` endpoints never write `PushTaxBureauStatus` (zero DB access, status kept by the SaaS side), so API-flow registrations are not picked up by these scripts unless the new system sets status 3 itself.

## Template Files

- `storage/Leko_Template.docx` — Power of attorney template ({{NameEng}}, {{RegAddressEng}}, {{CityEngName}}, {{CurrentDay}}, {{CurrentDay2}}, {{LegalPersonFullNamePinYin}})
- `storage/Leko_Template.xlsx` — LEKO registration template (data starts at row 14)
- `storage/Citeo_Template.docx` — CITEO POA template (7 placeholders / 9 usages; used by the generate-citeo-poa API)
- `storage/Refashion_Template.docx` — Refashion (textile law / ECO TLC) POA template (8 placeholders / 13 usages, text-only; used by the generate-refashion-poa API)
- `storage/Refashion_UIN_Template.docx` — Refashion UIN certificate template (5 placeholders / 6 usages, text-only; used by the generate-refashion-uin-certificate API)
- `storage/fonts/` — 7 TTF signature fonts (deterministically selected by first letter via `LETTER_FONT_MAP`; GD extension required)
- `storage/python/normalize_pdf.py` — PyMuPDF fallback for PdfNormalizer (Ghostscript is primary)