# Notice Management Tools - Complete Reference

## Overview

This document provides a complete reference for all tools available to manage notices and their attachments in the KKCP system.

## Available Tools

### 1. Cleanup Broken Attachments
**Purpose**: Remove attachment URLs from database when files don't exist
**Location**: `/php/admin/cleanup_notices_simple.php`
**Type**: Web interface (no authentication required)
**Status**: ✅ Ready to use

**What it does**:
- Scans all notices with attachment URLs
- Checks if each file exists on the server
- Removes URLs for missing files
- Keeps URLs for existing files
- Shows detailed results

**When to use**:
- After discovering broken attachment links
- When 500 errors occur on notice pages
- During system maintenance
- After file cleanup or migration

**Expected result**:
- 543 notices will have attachment URLs cleared
- No more 500 errors
- Notices display normally without attachments

**How to run**:
1. Open browser: `http://your-domain/php/admin/cleanup_notices_simple.php`
2. Click "Clear Missing Attachments" button
3. Review results
4. Confirm operation

---

### 2. Investigate Duplicate Attachments
**Purpose**: Find notices that share the same attachment file
**Location**: `/php/admin/investigate_duplicate_attachments.php`
**Type**: Web interface (read-only)
**Status**: ✅ Ready to use

**What it does**:
- Queries all notices with attachments
- Groups by attachment URL
- Identifies which notices share files
- Shows statistics and details
- No database modifications

**When to use**:
- To understand data integrity issues
- To determine if sharing is intentional
- To identify duplicate notices
- During data audit

**Expected result**:
- Shows how many notices share attachments
- Lists which notices share each file
- Helps determine if issue is bug or feature

**How to run**:
1. Open browser: `http://your-domain/php/admin/investigate_duplicate_attachments.php`
2. Review statistics
3. Analyze shared attachments
4. Document findings

---

### 3. Fix Notice Paths (Auto-Fixer v1)
**Purpose**: Automatically fix notice attachment paths using timestamp/filename matching
**Location**: `/php/admin/fix_notice_paths.php` (web) or `/php/scripts/fix_notice_paths.php` (API)
**Type**: Web interface or API
**Status**: ⚠️ Not applicable (files don't exist)

**What it does**:
- Scans notices with broken paths
- Looks for matching files in `/php/uploads/notices/`
- Uses timestamp and filename matching
- Updates database with correct paths
- Shows match scores

**When to use**:
- When files exist but paths are wrong
- After moving files to correct directory
- When filenames have changed

**Limitations**:
- Requires files to exist in `/php/uploads/notices/`
- Currently not applicable (directory is empty)

---

### 4. Fix Notice Paths by Name (Auto-Fixer v2)
**Purpose**: Fix notice paths using fuzzy string matching on notice NAME field
**Location**: `/php/admin/fix_notice_paths_by_name.php` (web) or `/php/scripts/fix_notice_paths_by_name.php` (API)
**Type**: Web interface or API
**Status**: ⚠️ Not applicable (files don't exist)

**What it does**:
- Scans notices with broken paths
- Matches by notice NAME field using Levenshtein distance
- Looks for files in `/php/uploads/notices/`
- Uses 60%+ similarity threshold
- Updates database with correct paths

**When to use**:
- When filenames don't match timestamps
- When notice names are similar to filenames
- For intelligent matching

**Limitations**:
- Requires files to exist in `/php/uploads/notices/`
- Currently not applicable (directory is empty)

---

### 5. File Upload Handler
**Purpose**: Securely handle file uploads for notices
**Location**: `/php/includes/FileUpload.php`
**Type**: PHP class
**Status**: ✅ Ready to use

**What it does**:
- Validates file MIME types
- Enforces file size limits
- Sets secure file permissions (0644)
- Prevents PHP execution in upload directory
- Generates unique filenames
- Returns upload status and path

**When to use**:
- When implementing notice attachment upload
- For any file upload in the system
- To ensure secure file handling

**How to use**:
```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'];
}
```

---

### 6. Database Connection
**Purpose**: Establish secure database connection
**Location**: `/php/includes/db.php`
**Type**: PHP functions
**Status**: ✅ Ready to use

**What it does**:
- Connects to MySQL database
- Sets UTF-8 character encoding
- Handles connection errors
- Returns PDO connection object

**When to use**:
- In all PHP scripts that need database access
- For queries and updates

**How to use**:
```php
require_once 'includes/db.php';
$pdo = getDBConnection();
```

---

### 7. Table Helper Functions
**Purpose**: Detect correct table names (handles numbered versions)
**Location**: `/php/includes/table_helper.php`
**Type**: PHP functions
**Status**: ✅ Ready to use

**What it does**:
- Finds correct table name in database
- Handles both lowercase and mixed case
- Supports numbered table versions
- Caches results for performance

**When to use**:
- When querying tables with variable names
- To handle database schema variations

**How to use**:
```php
require_once 'includes/table_helper.php';
$noticesTable = getTableNameCached($pdo, 'notices');
```

---

## Tool Comparison

| Tool | Purpose | Status | Files Needed | Time |
|------|---------|--------|--------------|------|
| Cleanup | Remove broken URLs | ✅ Ready | None | < 1 min |
| Investigate | Find shared attachments | ✅ Ready | None | 5-10 min |
| Auto-Fixer v1 | Fix paths by timestamp | ⚠️ N/A | Yes | N/A |
| Auto-Fixer v2 | Fix paths by name | ⚠️ N/A | Yes | N/A |
| FileUpload | Secure uploads | ✅ Ready | N/A | N/A |
| DB Connection | Database access | ✅ Ready | N/A | N/A |
| Table Helper | Find table names | ✅ Ready | N/A | N/A |

## Recommended Workflow

### Step 1: Cleanup (Today)
1. Run cleanup tool
2. Verify no 500 errors
3. Document results

### Step 2: Investigate (This Week)
1. Run investigation tool
2. Analyze shared attachments
3. Determine if intentional or bug

### Step 3: Prevent (Next Week)
1. Implement FileUpload class
2. Add validation rules
3. Train users

### Step 4: Monitor (Ongoing)
1. Watch for broken links
2. Regular audits
3. Automatic cleanup

## File Locations

### Admin Tools
```
/php/admin/
├── cleanup_notices_simple.php
├── investigate_duplicate_attachments.php
├── fix_notice_paths.php
├── fix_notice_paths_v2.php
└── fix_notice_paths_by_name.php
```

### API Scripts
```
/php/scripts/
├── fix_notice_paths.php
├── fix_notice_paths_v2.php
└── fix_notice_paths_by_name.php
```

### Includes
```
/php/includes/
├── db.php
├── table_helper.php
├── FileUpload.php
├── auth.php
└── config.php
```

### Upload Directories
```
/php/uploads/
├── notices/          (for notice attachments)
├── members/          (for member uploads)
└── .htaccess         (prevents PHP execution)
```

## Database Tables

### Notices Table
```sql
CREATE TABLE notices (
    Id INT PRIMARY KEY,
    Name VARCHAR(255),
    AttachmentUrl VARCHAR(255),
    -- other columns...
);
```

**Key columns**:
- `Id` - Notice ID
- `Name` - Notice name/title
- `AttachmentUrl` - Path to attachment file

## Security Considerations

### File Upload Security
✅ MIME type validation
✅ File size limits
✅ Secure permissions (0644)
✅ PHP execution disabled in upload directory
✅ Unique filenames to prevent overwrites

### Database Security
✅ Prepared statements (PDO)
✅ UTF-8 encoding
✅ Error handling
✅ Connection pooling

### Access Control
✅ Authentication required for admin tools
✅ Read-only investigation tool
✅ No auth required for cleanup (files already missing)

## Troubleshooting

### Cleanup Tool Shows 500 Error
**Cause**: Database connection issue
**Solution**: Check `/php/includes/db.php` credentials

### Investigation Tool Shows No Results
**Cause**: No shared attachments (good!)
**Solution**: This is expected if data is clean

### Auto-Fixer Shows No Matches
**Cause**: Files don't exist in `/php/uploads/notices/`
**Solution**: Use cleanup tool instead

### FileUpload Class Not Working
**Cause**: Directory permissions or missing directory
**Solution**: Ensure `/php/uploads/notices/` exists and is writable

## Support

For questions or issues:
1. Check tool documentation above
2. Review error messages in tool output
3. Check database logs
4. Contact system administrator

---

**Document Created**: March 15, 2026
**Last Updated**: Initial Creation
**Status**: Complete Reference