# Implementation Plan: EPR Data Processor

## Overview

This implementation plan breaks down the EPR data processor into discrete coding tasks. The system will run as a persistent Laravel command that polls a SQL Server database every 8 seconds, processes EPR records through data transformation, document generation, and file upload, then saves results to a target database.

The implementation follows Laravel's MVC + Service/Repository architecture with comprehensive error handling, retry mechanisms, and logging.

## Tasks

- [ ] 1. Set up project configuration and database connections
  - Create database connection configurations for source and target SQL Server databases in `config/database.php`
  - Add environment variables to `.env.example` for database credentials, DeepL API, and Tencent COS
  - Create custom log channel 'epr' in `config/logging.php` for EPR-specific logs
  - Ensure directory structure exists: `storage/app/temp`, `storage/app/translations`
  - _Requirements: US-1, US-10, NFR-3_

- [ ] 2. Implement data transformation service
  - [ ] 2.1 Create DataTransformService class with phone formatting logic
    - Implement `formatPhoneNumber()` method to convert phone numbers to "00+country_code+number" format
    - Handle various input formats: with/without country code, with spaces/dashes
    - Default to China country code (86) if not detected
    - _Requirements: US-2_

  - [ ]* 2.2 Write unit tests for phone number formatting
    - Test cases: "86-13488979214" → "008613488979214", "13488979214" → "008613488979214"
    - Test edge cases: empty input, special characters, international numbers
    - _Requirements: US-2_

  - [ ] 2.3 Implement legal name splitting logic
    - Implement `splitLegalName()` method to split pinyin full name into first and last names
    - Last name: everything after last space, converted to uppercase
    - First name: everything before last space with spaces removed, first letter capitalized
    - Handle edge cases: no spaces, empty input
    - _Requirements: US-4_

  - [ ]* 2.4 Write unit tests for name splitting
    - Test cases from requirements: "Shaohua SHI", "Xiao Ming WANG", "Li", etc.
    - Test various case combinations and spacing
    - _Requirements: US-4_

  - [ ] 2.5 Implement birth date splitting and formatting
    - Implement `splitBirthDate()` method to extract day, month, year as integers
    - Implement `formatDateForWord()` method to format dates as DD/MM/YYYY
    - Support multiple input formats: YYYY-MM-DD, YYYY/MM/DD, DateTime objects
    - Handle invalid dates by returning null
    - _Requirements: US-5_

  - [ ]* 2.6 Write unit tests for date processing
    - Test date splitting: "1982-02-28" → day=28, month=2, year=1982
    - Test Word formatting: "1982-02-28" → "28/02/1982"
    - Test edge cases: invalid dates, null input, leap years
    - _Requirements: US-5_

- [ ] 3. Implement translation service with caching and retry logic
  - [ ] 3.1 Create TranslationService class with DeepL API integration
    - Implement cache loading/saving from `storage/app/translations/country_names_it.json`
    - Implement `translateCountryName()` method with cache-first lookup
    - Implement `callDeepLApi()` with HTTP client for DeepL API calls
    - _Requirements: US-3_

  - [ ] 3.2 Implement retry mechanism for DeepL API failures
    - Add retry logic: 3 attempts with delays of 2s, 5s, 10s
    - Log each retry attempt with detailed information
    - Throw TranslationException after 3 failed attempts to stop program
    - _Requirements: US-3, P-6_

  - [ ]* 3.3 Write unit tests for translation service
    - Test cache hit scenario (no API call)
    - Test cache miss scenario (API call + cache update)
    - Mock DeepL API responses
    - _Requirements: US-3, P-5_

  - [ ]* 3.4 Write integration tests for retry mechanism
    - Mock API failures to test retry behavior
    - Verify retry delays and attempt counts
    - Verify program stops after 3 failures
    - _Requirements: P-6_

- [ ] 4. Checkpoint - Ensure all tests pass
  - Ensure all tests pass, ask the user if questions arise.

- [ ] 5. Implement document generation service
  - [ ] 5.1 Create DocumentGeneratorService class
    - Implement `generateEprDocument()` method using WordTemplateProcessor
    - Implement `prepareTemplateData()` to map data to template placeholders
    - Implement `generateTempFilePath()` to create unique temp file paths with code and timestamp
    - Template path: `1.docx` in project root
    - Output path: `storage/app/temp/epr_desc_{code}_{timestamp}.docx`
    - _Requirements: US-6_

  - [ ] 5.2 Handle Word template placeholder replacement
    - Map all required placeholders: company_name, company_address, vat_number, etc.
    - Format legal_rep_name as "FirstName LastName"
    - Format dates using DD/MM/YYYY format
    - Handle missing data gracefully (use empty string or null)
    - _Requirements: US-6_

  - [ ]* 5.3 Write unit tests for document generation
    - Test template data preparation with complete data
    - Test handling of missing/null fields
    - Test file path generation uniqueness
    - _Requirements: US-6_

- [ ] 6. Implement file upload service for Tencent COS
  - [ ] 6.1 Create FileUploadService class with Tencent COS SDK
    - Initialize COS client with credentials from environment variables
    - Implement `uploadToTencentCos()` method
    - Implement `generateCosKey()` to create path: `epr/italy/desc/{year}/{month}/{filename}`
    - Return full URL after successful upload
    - Delete local temp file after successful upload
    - _Requirements: US-7_

  - [ ] 6.2 Add error handling and retry for upload failures
    - Implement retry logic: 3 attempts for upload failures
    - Throw FileUploadException after failures
    - Log upload attempts and results
    - _Requirements: US-7, BC-4_

  - [ ]* 6.3 Write unit tests for file upload service
    - Mock COS client to test upload logic
    - Test COS key generation with various dates
    - Test error handling and exceptions
    - _Requirements: US-7_

- [ ] 7. Implement repository layer for database access
  - [ ] 7.1 Create SourceDbRepository class
    - Implement `getNextPendingRecord()` to fetch TOP 1 record with Country='IT' and PushTaxBureauStatus=5
    - Implement `updateRecordStatusToFailed()` to set PushTaxBureauStatus=-1 with error message
    - Implement `updateRecordStatusToSuccess()` to update status after processing
    - Use SQL Server connection named 'source'
    - _Requirements: US-1, US-9_

  - [ ] 7.2 Implement database connection retry mechanism in SourceDbRepository
    - Implement `executeWithRetry()` wrapper method
    - Retry logic: 10 attempts with 5-second intervals
    - Log each retry attempt with warning level
    - Exit program after 10 failed attempts
    - Log successful reconnection
    - _Requirements: US-12, P-7_

  - [ ] 7.3 Create TargetDbRepository class
    - Implement `saveProcessedRecord()` to insert into `italia_epr_file` table
    - Set desc_status=2, desc_file_url, desc_process_time, doc_day/month/year
    - Use SQL Server connection named 'target'
    - Use database transactions for data consistency
    - _Requirements: US-8_

  - [ ] 7.4 Implement database connection retry mechanism in TargetDbRepository
    - Implement `executeWithRetry()` wrapper method with same retry logic as source
    - Ensure transaction rollback on failure
    - _Requirements: US-12, P-7_

  - [ ]* 7.5 Write unit tests for repository layer
    - Test query building and parameter binding
    - Test status update methods
    - Mock database connections
    - _Requirements: US-1, US-8, US-9_

- [ ] 8. Checkpoint - Ensure all tests pass
  - Ensure all tests pass, ask the user if questions arise.

- [ ] 9. Implement core processor service
  - [ ] 9.1 Create EprProcessorService class with dependency injection
    - Inject all required services: SourceDbRepository, TargetDbRepository, DataTransformService, TranslationService, DocumentGeneratorService, FileUploadService
    - Implement `processNextRecord()` method to check for pending records
    - Return early if no records found (for polling loop)
    - _Requirements: US-1, US-11_

  - [ ] 9.2 Implement main processing workflow in processRecord()
    - Read record from source database
    - Transform data: format phone, split name, split birth date
    - Translate country name to Italian
    - Generate Word document from template
    - Upload document to Tencent COS
    - Save result to target database
    - Update source record status to success
    - _Requirements: US-1 through US-8_

  - [ ] 9.3 Implement comprehensive error handling
    - Wrap entire process in try-catch block
    - Implement `handleProcessingError()` method
    - Update source database with PushTaxBureauStatus=-1 on any failure
    - Truncate error messages to 100 characters
    - Format error messages: "[{step}] {error_type}: {description} ({timestamp})"
    - Log all errors with full stack traces
    - _Requirements: US-9, BC-2_

  - [ ]* 9.4 Write integration tests for processor service
    - Test complete workflow with mocked dependencies
    - Test error handling for each step failure
    - Verify source status updates on success and failure
    - _Requirements: P-1, P-4_

- [ ] 10. Implement persistent command with polling
  - [ ] 10.1 Create EprProcessItalyCommand Artisan command
    - Set signature: `epr:process-italy`
    - Set description for command help
    - Inject EprProcessorService via constructor
    - _Requirements: US-11_

  - [ ] 10.2 Implement infinite polling loop in handle() method
    - Log startup message on command start
    - Implement while(true) loop for persistent execution
    - Call `processNextRecord()` every iteration
    - Sleep for 8 seconds between iterations
    - Catch and log all exceptions without stopping loop
    - Support graceful shutdown on Ctrl+C (Laravel handles this automatically)
    - _Requirements: US-11, P-8_

  - [ ]* 10.3 Write tests for command execution
    - Test command registration and signature
    - Test polling behavior (mock sleep)
    - Test exception handling in loop
    - _Requirements: US-11_

- [ ] 11. Configure logging and environment
  - [ ] 11.1 Create custom log channel configuration
    - Add 'epr' channel to `config/logging.php`
    - Configure daily log rotation
    - Set log path: `storage/logs/epr-processor.log`
    - Set appropriate log levels (INFO, WARNING, ERROR)
    - _Requirements: US-10_

  - [ ] 11.2 Add comprehensive logging throughout application
    - Log processing start with saas_id and code
    - Log each major step completion (transform, translate, generate, upload, save)
    - Log all retry attempts with attempt number and delay
    - Log processing success with execution time
    - Log all errors with full context
    - _Requirements: US-10_

  - [ ] 11.3 Create .env.example with all required configuration
    - Document all environment variables with descriptions
    - Include source/target database credentials
    - Include DeepL API configuration
    - Include Tencent COS configuration
    - Include EPR processor settings (poll interval, retry counts)
    - _Requirements: NFR-3_

- [ ] 12. Windows environment compatibility
  - [ ] 12.1 Ensure Windows path compatibility
    - Use Laravel's `storage_path()` and `base_path()` helpers for all file paths
    - Test path generation on Windows with backslashes
    - Verify temp file creation and deletion
    - _Requirements: US-13_

  - [ ] 12.2 Create PowerShell startup script
    - Create `start-epr-processor.ps1` in project root
    - Script content: `php artisan epr:process-italy`
    - Add instructions for running as Windows service with NSSM
    - _Requirements: US-13_

  - [ ] 12.3 Add Windows service documentation
    - Document NSSM installation commands in README or docs
    - Include service configuration: auto-restart on failure, 5-second delay
    - Document log file locations for service output/error
    - _Requirements: US-13_

- [ ] 13. Final integration and testing
  - [ ] 13.1 Create service provider for dependency injection
    - Register all services in `AppServiceProvider` or create dedicated `EprServiceProvider`
    - Bind TranslationService with configuration from environment
    - Bind FileUploadService with Tencent COS credentials
    - Ensure singleton pattern for services that maintain state (cache)
    - _Requirements: NFR-3_

  - [ ] 13.2 Wire all components together
    - Verify all dependencies are properly injected
    - Test database connections (source and target)
    - Test DeepL API connection
    - Test Tencent COS connection
    - Verify Word template file exists at project root
    - _Requirements: US-1, US-3, US-7_

  - [ ]* 13.3 Write end-to-end integration tests
    - Test complete flow with test database
    - Verify data integrity from source to target
    - Test error scenarios and status updates
    - Verify file upload and cleanup
    - _Requirements: P-1, P-2, P-3, P-4_

  - [ ]* 13.4 Write property test for data integrity
    - **Property P-1: Data completeness**
    - **Validates: Requirements P-1**
    - Verify every processed record exists in target or marked as failed in source
    - _Requirements: P-1_

  - [ ]* 13.5 Write property test for phone number format consistency
    - **Property P-2: Phone format consistency**
    - **Validates: Requirements P-2**
    - Generate random phone inputs and verify all outputs match pattern ^00\d{10,15}$
    - _Requirements: P-2_

- [ ] 14. Final checkpoint - Ensure all tests pass
  - Ensure all tests pass, ask the user if questions arise.

## Notes

- Tasks marked with `*` are optional and can be skipped for faster MVP
- Each task references specific requirements for traceability
- The implementation uses Laravel 11.51.0 with PHP 8.3.25
- All code follows PSR-12 coding standards
- Database retry mechanism: 10 attempts with 5-second intervals
- DeepL API retry mechanism: 3 attempts with 2s, 5s, 10s intervals
- Program runs as persistent process with 8-second polling interval
- Designed for Windows environment with PowerShell and NSSM service support