## Plan: Bulk Upload Codes Tied to Fee Sheets via CSV

### Overview
Codes are now **directly tied to fee sheets** via `fee_sheet_id` foreign key. Two workflows are supported:
1. **Standalone bulk upload**: `POST /api/v1/codes/bulk` - Upload codes to an existing fee sheet
2. **Fee sheet creation with bulk codes**: `POST /api/v1/setting/fee-sheets` - Create fee sheet and upload codes in one transaction

### Database Schema
- `codes` table now has `fee_sheet_id` (NOT NULL) - each code belongs to exactly one fee sheet
- Unique constraint: `['fee_sheet_id', 'code_type_id', 'code']` - same code can exist across different fee sheets
- Relationship: `FeeSheet hasMany Code`, `Code belongsTo FeeSheet`

### CSV Format
Expected columns:
- `code_type` (required) - Code type code (e.g., "CPT4", "ICD10") - must match existing `code_types.code`
- `code` (required) - Code value (max 50 chars)
- `name` (required) - Code name (max 255 chars)
- `description` (optional) - Code description
- `fee` (optional) - Fee amount
- `status` (optional) - 0 or 1, defaults to 1

### Key Behavior
- **Requires fee_sheet_id**: All bulk uploads must specify which fee sheet the codes belong to
- **Skip duplicates**: If `fee_sheet_id` + `code_type_id` + `code` combination already exists, skip and report
- **Non-breaking errors**: Failed rows collected with error messages, process continues
- **Code type lookup**: Map `code_type` to `code_types.id` via `code_types.code`

### Performance Optimizations for 3000+ rows
1. **Pre-fetch lookups**: Load all code types and existing codes once before processing
2. **Batch inserts**: Use chunked inserts (500 rows per batch) instead of individual creates
3. **Memory efficiency**: Process CSV in chunks using `fgetcsv()`
4. **Single transaction**: Wrap entire operation in one DB transaction
5. **Avoid N+1**: No queries inside loops - use pre-loaded lookup arrays

### Files Created/Modified

#### 1. Migration (Created)
**File:** `database/migrations/2025_11_25_204935_add_fee_sheet_id_to_codes_table.php`
- Add `fee_sheet_id` column to codes table (NOT NULL)
- Add foreign key constraint to `fee_sheets.id` with CASCADE on delete
- Drop old unique constraint: `unique_code_per_type`
- Add new unique constraint: `unique_code_per_sheet` (`fee_sheet_id`, `code_type_id`, `code`)

#### 2. Models (Updated)
**File:** `app/Models/Code.php`
- Added `fee_sheet_id` to `$fillable` and `$casts`
- Replaced `feeSheets()` belongsToMany with `feeSheet()` belongsTo

**File:** `app/Models/FeeSheet.php`
- Replaced `codes()` belongsToMany with `codes()` hasMany

#### 3. Form Request (Updated)
**File:** `app/Http/Requests/BulkUploadCodesRequest.php`
- Added validation: `'fee_sheet_id' => 'required|integer|exists:fee_sheets,id'`
- Validate CSV file (required, mimes: csv, max: 10MB)

**File:** `app/Http/Requests/StoreFeeSheetRequest.php`
- Added validation: `'codes_file' => 'nullable|file|mimes:csv|max:10240'`

#### 4. Service Methods (Updated)
**File:** `app/Services/CodeService.php`
- Updated `bulkCreateCodes(UploadedFile $file, int $feeSheetId, int $userId): array`
  - Now requires `fee_sheet_id` parameter
  - Validates fee sheet exists before processing
  - Sets `fee_sheet_id` on all code records
  - Pre-fetch existing codes filtered by fee_sheet_id
  - Updated duplicate check: `['fee_sheet_id', 'code_type_id', 'code']`
- Updated `createCode(array $data, int $userId): Code`
  - Requires `fee_sheet_id` in data array
  - Validates fee sheet exists

**File:** `app/Services/FeeSheetService.php`
- Updated `createFeeSheet(array $data, int $userId, $codesFile = null): FeeSheet`
  - Added optional `$codesFile` parameter
  - If file provided, calls `CodeService::bulkCreateCodes()` within transaction
  - Returns fee sheet with codes loaded

#### 5. Controllers (Updated)
**File:** `app/Http/Controllers/Api/V1/CodeController.php`
- Updated `bulkUpload(BulkUploadCodesRequest $request): JsonResponse`
  - Extracts `fee_sheet_id` from request
  - Passes `fee_sheet_id` to service method

**File:** `app/Http/Controllers/Api/V1/FeeSheetController.php`
- Updated `store(StoreFeeSheetRequest $request): JsonResponse`
  - Checks for `codes_file` in request
  - Passes file to service if present

#### 6. Routes
**File:** `routes/api.php`
- Existing route: `POST /api/v1/codes/bulk` - now requires `fee_sheet_id` in request body
- Existing route: `POST /api/v1/setting/fee-sheets` - now accepts optional `codes_file` multipart field

### Response Format
```json
{
    "message": "Bulk upload completed",
    "data": {
        "total": 3000,
        "successful": 2900,
        "skipped": 50,
        "failed": 50,
        "failed_rows": [
            {"row": 3, "data": {...}, "errors": ["Code type 'INVALID' does not exist"]},
            {"row": 7, "data": {...}, "errors": ["Fee must be a valid number"]}
        ],
        "skipped_rows": [
            {"row": 5, "data": {...}, "reason": "Duplicate code 'E0001' already exists for code type 'CPT4'"}
        ]
    }
}
```

*Note: Successfully created codes are not returned in response to avoid memory issues with large datasets. Count is provided instead.*

### Validation per Row
- `code_type`: required, must exist in `code_types.code`
- `code`: required, string, max 50, unique per fee_sheet_id + code_type_id combination
- `name`: required, string, max 255
- `description`: nullable, string
- `fee`: nullable, numeric, min 0
- `status`: nullable, integer, in 0,1

### Usage Examples

#### 1. Standalone Bulk Upload to Existing Fee Sheet
```bash
POST /api/v1/codes/bulk
Content-Type: multipart/form-data

fee_sheet_id: 5
file: codes.csv
```

#### 2. Create Fee Sheet with Bulk Codes Upload
```bash
POST /api/v1/setting/fee-sheets
Content-Type: multipart/form-data

name: "Standard Fee Sheet 2025"
description: "Annual fee sheet for 2025"
facility_codes[]: ["FAC001", "FAC002"]
codes_file: codes.csv
```

### Workflow Summary
1. **Fee sheets can be created without codes** - just omit `codes_file` parameter
2. **Codes can be bulk uploaded later** - use standalone bulk upload endpoint with `fee_sheet_id`
3. **Fee sheet creation with bulk upload** - provide `codes_file` during fee sheet creation (all in one transaction)

### Migration Notes
- Run migration: `php artisan migrate`
- Existing codes (if any) need `fee_sheet_id` assigned manually or via seeder before making column NOT NULL
- The migration handles constraint updates automatically