# Fitter Hire Linking & PDI Upload Implementation Summary

## Overview
Added functionality to link fitter callouts to specific hires, mark them as chargeable, and upload PDI (Pre-Delivery Inspection) documents.

## Changes Made

### 1. Database Schema
**File:** `migrations/2026-06-15_add_fitter_hire_link.sql`

Added three new columns to the `fitter` table:
- `id_hire_list INT NULL` — Foreign key link to hire_list table (optional)
- `is_chargeable ENUM('Y', 'N') DEFAULT 'N'` — Mark fitter job as chargeable
- `pdi_filename VARCHAR(255) NULL` — Store uploaded PDI file reference

Also created indexes for performance:
- `idx_fitter_hire` on `id_hire_list`
- `idx_fitter_chargeable` on `is_chargeable`

**Status:** ✅ Ready to apply (run on live server via phpMyAdmin)

### 2. File Upload System
**Files Created:**
- `CRUD/fitter/pdi_upload_handler.php` — Handles PDI file uploads via AJAX
- `CRUD/fitter/pdi_download.php` — Securely serves PDI files with access control

**Features:**
- File type validation (PDF, JPG, PNG, DOC, DOCX)
- File size limit (10MB max)
- Secure filename generation with timestamp + random hex
- Directory traversal protection in download handler
- Database verification before serving files

### 3. Frontend - Create Form
**File:** `CRUD/fitter/fitter_create.php`

**Changes:**
- Added "Link to Hire" dropdown (optional) — populated from active hires
- Added "Chargeable?" select (Y/N, default N)
- Added "Upload PDI Document" file input with AJAX upload button
- JavaScript handles:
  - File upload via fetch API
  - Status updates (uploading, success, error)
  - File input disable after successful upload
  - Enter key support

**Form Flow:**
1. User selects hire (optional) from active hires dropdown
2. User marks as chargeable if applicable
3. User optionally selects and uploads PDI file
4. On successful upload, filename is stored in hidden input
5. Form submission includes all new fields

### 4. Frontend - Update Form
**File:** `CRUD/fitter/fitter_update.php`

**Changes:**
- Same hire selector and chargeable checkbox as create form
- PDI section shows:
  - Current uploaded file (if any) with download link
  - File upload input for replacement
  - Clear indication of how to update
- Same AJAX upload functionality as create form

**Additional Features:**
- Displays current PDI file with download link
- Allows replacing PDI with new upload
- Stores updated filename on save

### 5. Display & Reporting
**File:** `CRUD/fitter/fitter_index.php`

**Changes:**
- Added three new columns to the fitter list table:
  - **Hire Link** — Shows fleet number as clickable link to hire_details.php
  - **Chargeable** — Shows "Yes" (green) or "No" (gray)
  - **PDI** — Shows PDF icon link to download PDI file
- Links to `hire_details.php?hire_id=` for hire details
- Links to `pdi_download.php?f=` for secure file download

### 6. Supporting Download Handler
**File:** `CRUD/fitter/pdi_download.php`

**Security Features:**
- Validates filename format (prevents directory traversal)
- Verifies file exists in database before serving
- Sets proper Content-Type and download headers
- Prevents caching with appropriate headers

## Implementation Details

### Upload Directory Structure
```
CRUD/forms/pdi/pdi/
  PDI_2026-06-15_120000_a1b2c3d4.pdf
  PDI_2026-06-15_120500_e5f6g7h8.jpg
  SF.058 Pre-Delivery Inspection Form PDI.pdf
  ... (other PDI documents)
```

Files are named with:
- Timestamp (YYYY-MM-DD_HHMMSS)
- 8-character random hex
- Original file extension

All PDI uploads (fitter, forms, etc.) are centralized in this single location.

### Database Operations
All database operations use prepared statements with parameterized queries for SQL injection protection.

**Insert:** Includes all 11 columns (date, job, customer, location, hours, priority, comments, completed, hire_id, chargeable, pdi_filename)

**Update:** Modifies all editable fields including the new ones

**Select:** JOIN with hire_list to display hire information in fitter_index.php

### API Response Format
**PDI Upload Success:**
```json
{
  "success": true,
  "filename": "PDI_2026-06-15_120000_a1b2c3d4.pdf",
  "message": "PDI file uploaded successfully"
}
```

**PDI Upload Error:**
```json
{
  "success": false,
  "error": "File type not allowed. Accepted: PDF, JPG, PNG, DOC, DOCX"
}
```

## User Experience Flow

### Creating a Fitter Job
1. Open fitter_create.php
2. Fill in standard fields (date, job, customer, location, hours, priority, comments)
3. **Optionally:**
   - Select a hire from the dropdown
   - Mark as chargeable
   - Select and upload a PDI document
4. Submit form
5. Redirects to fitter_index.php

### Updating a Fitter Job
1. Open fitter_index.php
2. Click edit icon for desired fitter job
3. Update any fields
4. **If PDI changes needed:**
   - Current PDI file shown with download link
   - Select new file and click Upload
   - Previous file replaced in database
5. Save changes
6. Redirects to fitter_index.php

### Viewing Fitter Details
1. In fitter_index.php, see new columns:
   - Hire Link: Click to view hire details
   - Chargeable: Green "Yes" or gray "No"
   - PDI: PDF icon to download (if uploaded)

## Testing Checklist

- [ ] Run migration on live server
- [ ] Create new fitter with hire link but no PDI
- [ ] Create new fitter with PDI but no hire link
- [ ] Create new fitter with both hire and PDI
- [ ] Verify PDI file uploads to correct directory
- [ ] Verify PDI download works from fitter_index.php
- [ ] Update existing fitter to add hire/chargeable/PDI
- [ ] Update existing fitter to replace PDI file
- [ ] Verify hire link works in fitter_index.php
- [ ] Test file size limit (>10MB should reject)
- [ ] Test file type validation (non-approved types should reject)

## Next Steps for Invoice Integration

To integrate with invoice reporting:

1. **Create chargeable fitter query** — View to show all chargeable fitter jobs
2. **Create invoice line items** — Allow mapping chargeable fitter jobs to invoice line items
3. **Update invoice report** — Include fitter charges in customer invoicing
4. **Add hire lookup** — Allow selecting fitter job from hire ID when creating invoice

This would require:
- New view/endpoint to list chargeable fitters by hire
- Updates to invoice creation to support fitter job line items
- Updates to invoice reporting to show fitter charges
- Optional: UI to link fitter directly from invoice creation

## File Cleanup Notes

- Upload directory `/CRUD/fitter/uploads/pdi/` will grow over time
- Consider implementing:
  - Archive/delete old PDI files after X months
  - Disk space monitoring
  - Cleanup script for orphaned files (in DB but not on disk, or vice versa)

## Security Considerations

✅ **Implemented:**
- SQL injection prevention (prepared statements)
- Directory traversal prevention (filename validation in download)
- File type validation (MIME type + extension check)
- File size limits (10MB max)
- Authentication required (session.php required)
- Secure file naming (timestamp + random hex)
- Database verification before serving files

⚠️ **Recommended Future:**
- Regular backup of uploaded PDI files
- Virus scanning for uploaded files
- Access logging for PDI downloads
- Audit trail of who uploaded/downloaded what

## Performance Notes

- New indexes added for hire_list and is_chargeable lookups
- Foreign key constraint on hire_list for referential integrity
- File operations in uploads directory (outside web root recommended)
- Download handler uses readfile() for efficient streaming