# Notice Attachment Issue - Complete Guide

## Problem Summary

The KKCP system has **543 notices with broken attachment URLs**. The files these notices reference no longer exist on the server, causing errors when users try to access them.

### Key Facts
- **Broken Notices**: 543 total
- **Missing Files**: All files in `/img/attachments/` directory
- **Current Directory**: `/php/uploads/notices/` exists but is empty
- **Data Issue**: Multiple notices reference the same file (should be 1:1)
- **Error Type**: 500 Internal Server Error when file exists, 404 when missing

## Root Cause Analysis

### Why Files Are Missing
1. **Wrong Directory**: Files were stored in `/img/attachments/` instead of `/php/uploads/notices/`
2. **No Backup**: Original files were not backed up
3. **Directory Cleanup**: `/img/attachments/` directory may have been deleted or cleaned up
4. **Migration Issue**: System may have been migrated without preserving attachment files

### Why Multiple Notices Share Files
This is a **data integrity issue** that needs investigation:
- Some notices may intentionally share documents (e.g., policy files)
- Or it could be a bug in the upload process
- Or database corruption from a failed migration

## Solution Overview

### Step 1: Clear Broken URLs (Immediate)
**What**: Remove attachment URLs from database for missing files
**Why**: Prevents 500 errors and allows notices to display normally
**How**: Run `/php/admin/cleanup_notices_simple.php`
**Time**: < 1 minute
**Reversible**: Yes (database backup exists)

### Step 2: Investigate Data Integrity (This Week)
**What**: Understand why multiple notices share files
**Why**: Prevent this issue from recurring
**How**: Run `/php/admin/investigate_duplicate_attachments.php`
**Time**: 5-10 minutes
**Reversible**: Read-only investigation

### Step 3: Implement Prevention (Next Week)
**What**: Ensure proper file upload handling for future notices
**Why**: Prevent broken attachments in the future
**How**: Use `/php/includes/FileUpload.php` for all uploads
**Time**: 1-2 hours
**Reversible**: Yes

## How to Execute

### Cleanup Broken URLs

1. **Access the cleanup tool**
   ```
   URL: http://your-domain/php/admin/cleanup_notices_simple.php
   ```

2. **Review the warning**
   - The tool will show a warning about missing files
   - This is expected and correct

3. **Click "Clear Missing Attachments"**
   - The tool will process all 543 notices
   - It will remove URLs for files that don't exist
   - It will keep URLs for files that do exist (if any)

4. **Review the results**
   - Summary shows: Total, Cleared, Exists
   - Detailed table shows each notice processed
   - Expected result: ~543 cleared, 0 exists

5. **Verify the fix**
   - Access a notice that previously had an attachment
   - It should now display without attachment link
   - No 500 errors should occur

### Investigate Duplicate Attachments

1. **Access the investigation tool**
   ```
   URL: http://your-domain/php/admin/investigate_duplicate_attachments.php
   ```

2. **Review the statistics**
   - Shows how many notices share attachments
   - Shows which attachments are shared
   - Shows which notices share each file

3. **Analyze the results**
   - If no shared attachments: Data is clean
   - If shared attachments: Determine if intentional or bug

4. **Document findings**
   - Screenshot the results
   - Note any patterns
   - Determine next steps

## Technical Details

### Cleanup Script
**File**: `/php/admin/cleanup_notices_simple.php`
**Type**: Web interface (no authentication required)
**Database**: Reads from `notices` table, updates `AttachmentUrl` column
**Safety**: Only removes URLs for files that don't exist

### Investigation Script
**File**: `/php/admin/investigate_duplicate_attachments.php`
**Type**: Web interface (read-only)
**Database**: Queries `notices` table, no modifications
**Safety**: No database changes

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

-- Expected result: 543 rows updated
```

## Expected Outcomes

### After Cleanup
✅ No more 500 errors when accessing notices
✅ Notices display normally without attachment links
✅ Database is clean and consistent
✅ Ready for new attachment uploads

### After Investigation
✅ Understand why multiple notices share files
✅ Determine if issue is intentional or bug
✅ Document findings for future reference
✅ Plan prevention measures

### After Prevention Implementation
✅ New uploads use proper file handling
✅ Each notice has unique attachment (1:1 relationship)
✅ Broken links detected automatically
✅ System prevents duplicate references

## Troubleshooting

### Cleanup Script Shows 500 Error
**Cause**: Database connection issue
**Solution**: 
1. Check database credentials in `/php/includes/db.php`
2. Verify database is accessible
3. Check database user permissions

### Cleanup Script Shows 0 Cleared
**Cause**: All files exist or no notices have attachments
**Solution**:
1. This is actually good - means no broken links
2. Verify by checking a notice with attachment
3. If attachment works, no cleanup needed

### Investigation Script Shows Many Shared Attachments
**Cause**: Multiple notices reference same file
**Solution**:
1. Determine if intentional (shared documents)
2. If bug, need to fix data model
3. Implement validation to prevent future sharing

## Prevention Measures

### Use FileUpload Class
```php
require_once 'includes/FileUpload.php';

$uploader = new FileUpload('notices/');
$result = $uploader->upload($_FILES['attachment']);

if ($result['success']) {
    $attachmentPath = $result['path'];
    // Save to database
} else {
    $error = $result['error'];
}
```

### Validation Rules
1. Each notice can have only one attachment
2. Each attachment file must be unique
3. File must exist before saving URL to database
4. File permissions must be correct (0644)

### Monitoring
1. Regular checks for broken links
2. Alerts when attachment URLs don't match files
3. Automatic cleanup of orphaned files
4. Logging of all upload operations

## Files Involved

### Cleanup Tools
- `/php/admin/cleanup_notices_simple.php` - Main cleanup tool
- `/php/admin/investigate_duplicate_attachments.php` - Investigation tool

### Upload Handling
- `/php/includes/FileUpload.php` - Secure file upload class
- `/php/uploads/notices/` - Directory for notice attachments
- `/php/uploads/.htaccess` - Prevents PHP execution in uploads

### Configuration
- `/php/includes/db.php` - Database connection
- `/php/includes/table_helper.php` - Table name detection

## Timeline

### Today (Immediate)
- [ ] Run cleanup script
- [ ] Verify no 500 errors
- [ ] Document results

### This Week
- [ ] Run investigation script
- [ ] Analyze shared attachments
- [ ] Document findings

### Next Week
- [ ] Implement prevention measures
- [ ] Update upload process
- [ ] Train users on proper procedures

## Support & Questions

### Common Questions

**Q: Will this delete my files?**
A: No, this only removes URLs from the database. Files are already missing.

**Q: Can I undo the cleanup?**
A: Yes, if you have a database backup. But since files are missing anyway, there's nothing to restore.

**Q: Why do multiple notices share files?**
A: This is what we're investigating. Could be intentional or a bug.

**Q: What happens to notices after cleanup?**
A: They display normally without attachment links. Users can still read the notice content.

**Q: How do I prevent this in the future?**
A: Use the FileUpload class and implement validation rules.

### Getting Help

1. Check the detailed results in the cleanup tool
2. Review the investigation tool findings
3. Check database logs for errors
4. Contact system administrator if issues persist

## Next Steps

1. **Execute cleanup** - Remove broken URLs
2. **Investigate** - Understand data integrity issue
3. **Implement prevention** - Ensure it doesn't happen again
4. **Monitor** - Watch for broken links in future

---

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

## Quick Reference

| Task | Tool | URL | Time |
|------|------|-----|------|
| Cleanup | cleanup_notices_simple.php | /php/admin/cleanup_notices_simple.php | < 1 min |
| Investigate | investigate_duplicate_attachments.php | /php/admin/investigate_duplicate_attachments.php | 5-10 min |
| Prevent | FileUpload.php | /php/includes/FileUpload.php | 1-2 hours |