# Notice Attachment Recovery Plan

## Executive Summary

The KKCP system has **543 notices with broken attachment URLs** that point to non-existent files. The files are completely missing from the server, and many notices incorrectly reference the same file (data integrity issue).

## Current Status

### Problem Analysis
- **Total Notices**: 543 with broken attachment URLs
- **File Location**: Originally pointed to `/img/attachments/Attachment_XXX.pdf`
- **Current State**: Files don't exist in `/img/attachments/` or anywhere else
- **Directory Status**: `/php/uploads/notices/` exists but is empty
- **Data Issue**: Multiple notices share the same attachment file (should be 1:1 relationship)

### Root Causes
1. **Missing Files**: PDFs were never uploaded to the correct location or were deleted
2. **Wrong Directory**: Files should be in `/php/uploads/notices/` but were in `/img/attachments/`
3. **Data Model Issue**: Multiple notices incorrectly reference the same file
4. **No Backup**: No backup of original PDF files available

## Recovery Options

### Option 1: Clear Broken URLs (Recommended - Immediate)
**Status**: Ready to execute
**Impact**: Notices will display without attachments
**Reversibility**: Can be reversed by restoring database backup

**Steps**:
1. Access `/php/admin/cleanup_notices_simple.php` via web browser
2. Click "Clear Missing Attachments" button
3. Review results showing which notices were cleared
4. Confirm the operation

**Expected Result**:
- All 543 broken attachment URLs removed from database
- Notices will display normally without attachment links
- No 500 errors when accessing notices

### Option 2: Investigate Data Integrity Issue
**Status**: Requires investigation
**Impact**: Understand why multiple notices share files
**Reversibility**: Read-only investigation

**Questions to Answer**:
1. Why do multiple notices reference the same file?
2. Was this intentional (shared documents) or a bug?
3. Are there duplicate notices in the database?
4. Should each notice have a unique attachment?

**Investigation Script** (to be created):
```php
// Find notices sharing the same attachment
SELECT AttachmentUrl, COUNT(*) as count, GROUP_CONCAT(Id) as notice_ids
FROM notices
WHERE AttachmentUrl IS NOT NULL AND AttachmentUrl != ''
GROUP BY AttachmentUrl
HAVING count > 1
ORDER BY count DESC;
```

### Option 3: Recover from Backup
**Status**: Requires backup availability
**Impact**: Restore original PDF files if backup exists
**Reversibility**: Yes, can revert to current state

**Prerequisites**:
1. Database backup with original attachment URLs
2. File backup with original PDF files
3. Backup restoration procedure

**Steps**:
1. Restore database backup to temporary database
2. Extract original attachment URLs
3. Restore PDF files from backup
4. Update production database with correct paths

## Recommended Action Plan

### Phase 1: Immediate (Today)
1. **Run cleanup script** to clear broken URLs
   - Access: `/php/admin/cleanup_notices_simple.php`
   - Action: Click "Clear Missing Attachments"
   - Result: 543 notices will have empty attachment URLs

2. **Document the cleanup**
   - Screenshot results
   - Note timestamp of cleanup
   - Record number of notices affected

### Phase 2: Investigation (This Week)
1. **Investigate data integrity issue**
   - Query database for notices sharing attachments
   - Determine if this is intentional or a bug
   - Document findings

2. **Check for backups**
   - Look for database backups
   - Look for file backups
   - Determine if recovery is possible

### Phase 3: Prevention (Next Week)
1. **Implement proper file upload handling**
   - Use `/php/includes/FileUpload.php` for all uploads
   - Ensure 1:1 relationship between notices and attachments
   - Add validation to prevent duplicate references

2. **Create upload guidelines**
   - Document proper attachment upload process
   - Train users on correct procedures
   - Set up monitoring for broken links

## Technical Details

### Cleanup Script Location
```
/php/admin/cleanup_notices_simple.php
```

### What It Does
1. Queries all notices with attachment URLs
2. Checks if each file exists on the server
3. For missing files: removes the URL from database
4. For existing files: leaves them unchanged
5. Displays detailed results

### Database Changes
```sql
-- Before cleanup
UPDATE notices SET AttachmentUrl = NULL 
WHERE AttachmentUrl IS NOT NULL 
AND AttachmentUrl != '' 
AND file_does_not_exist;

-- Result: 543 notices will have AttachmentUrl = NULL
```

### File Structure After Cleanup
```
/php/uploads/
├── notices/          (empty - ready for new uploads)
├── members/          (existing member uploads)
└── .htaccess         (prevents PHP execution)
```

## Risks and Mitigation

### Risk 1: Data Loss
- **Risk**: Clearing URLs loses reference to files
- **Mitigation**: Files are already missing, so no additional loss
- **Backup**: Database backup exists before cleanup

### Risk 2: User Confusion
- **Risk**: Users expect attachments that are now gone
- **Mitigation**: Communicate that files were lost, not deleted
- **Action**: Send notification to users about missing attachments

### Risk 3: Duplicate Issue Recurrence
- **Risk**: Multiple notices sharing files could happen again
- **Mitigation**: Implement validation in upload process
- **Action**: Update `FileUpload.php` to prevent duplicates

## Success Criteria

✅ **Cleanup Complete**
- All 543 broken URLs removed from database
- No 500 errors when accessing notices
- Notices display normally without attachment links

✅ **Data Integrity Verified**
- Understand why multiple notices shared files
- Document findings
- Implement prevention measures

✅ **Prevention Implemented**
- New upload process prevents duplicate references
- Validation ensures 1:1 notice-to-attachment relationship
- Monitoring detects broken links automatically

## Next Steps

1. **Execute Phase 1** (Immediate)
   - Run cleanup script
   - Document results
   - Verify no errors

2. **Execute Phase 2** (This Week)
   - Investigate data integrity
   - Check for backups
   - Document findings

3. **Execute Phase 3** (Next Week)
   - Implement prevention
   - Train users
   - Set up monitoring

## Support

For questions or issues:
1. Check `/php/admin/cleanup_notices_simple.php` for detailed results
2. Review database logs for any errors
3. Contact system administrator if cleanup fails

---

**Document Created**: March 15, 2026
**Status**: Ready for Phase 1 Execution
**Last Updated**: Initial Creation