# Auto-Cleanup Feature for Outages

## Overview

The auto-cleanup feature automatically removes resolved outages after 12 hours to keep the outage management system clean and efficient. This prevents the database from filling up with old resolved outages while maintaining recent history for reference.

## How It Works

### 1. Automatic Cleanup
- **Trigger**: Runs automatically when `getActiveOutages()` or `getAllOutages()` methods are called
- **Frequency**: Every time the mobile app or admin interface loads outages
- **Target**: Outages with status `resolved` and `resolved_at` timestamp older than 12 hours
- **Action**: Permanently deletes matching outages from the database

### 2. Manual Cleanup
- **Trigger**: Via admin interface "Cleanup Old Outages" button or API call
- **Endpoint**: `POST /api/v1/outages/cleanup`
- **Target**: Same as automatic cleanup
- **Response**: Returns count of deleted outages and timestamp information

### 3. Database Changes
- **New Column**: `resolved_at DATETIME` in the `outages` table
- **Index**: Added for efficient cleanup queries
- **Auto-Update**: `resolved_at` is automatically set when outage status changes to `resolved`

## Implementation Details

### Database Schema Changes
```sql
-- Add the resolved_at column
ALTER TABLE outages ADD COLUMN resolved_at DATETIME NULL AFTER estimated_resolution;
ALTER TABLE outages ADD INDEX idx_resolved_at (resolved_at);

-- Update existing resolved outages
UPDATE outages 
SET resolved_at = updated_at 
WHERE status = 'resolved' AND resolved_at IS NULL;
```

### API Endpoints

#### Get Cleanup Statistics
```
GET /api/v1/outages/cleanup/stats
```
Returns:
```json
{
  "success": true,
  "data": {
    "total_resolved": 5,
    "cleanup_eligible": 2,
    "recent_resolved": 3,
    "oldest_resolved": "2025-07-05 14:30:00",
    "newest_resolved": "2025-07-06 08:15:00"
  }
}
```

#### Manual Cleanup
```
POST /api/v1/outages/cleanup
```
Returns:
```json
{
  "success": true,
  "message": "Successfully cleaned up 2 resolved outages",
  "data": {
    "deleted_count": 2,
    "oldest_resolved": "2025-07-05 14:30:00",
    "newest_resolved": "2025-07-05 18:45:00"
  }
}
```

### Admin Interface
- **Cleanup Button**: Added to the main outages management page
- **Confirmation**: Requires user confirmation before deletion
- **Feedback**: Shows success/failure messages with deletion count

## Configuration

### Time Limit
The 12-hour cleanup period is defined in the `cleanupOldResolvedOutages()` method:
```php
AND resolved_at < DATE_SUB(NOW(), INTERVAL 12 HOUR)
```

To change the cleanup period, modify the `INTERVAL` value:
- 6 hours: `INTERVAL 6 HOUR`
- 24 hours: `INTERVAL 24 HOUR`
- 7 days: `INTERVAL 7 DAY`

## Automation Options

### 1. Cron Job (Recommended)
Use the provided cron script for scheduled cleanup:
```bash
# Run every hour
0 * * * * /usr/bin/php /path/to/your/app/cron/cleanup-outages.php

# Run every 6 hours
0 */6 * * * /usr/bin/php /path/to/your/app/cron/cleanup-outages.php

# Run daily at 2 AM
0 2 * * * /usr/bin/php /path/to/your/app/cron/cleanup-outages.php
```

### 2. Automatic Cleanup
Already implemented and runs when:
- Mobile app loads outages
- Admin interface loads outages
- Any API call to outage endpoints

## Testing

### Manual Testing
1. Open admin interface
2. Create a test outage
3. Mark it as resolved
4. Wait 12+ hours OR manually update `resolved_at` in database
5. Click "Cleanup Old Outages" button
6. Verify outage is deleted

### Automated Testing
Run the test script:
```bash
php tests/test-auto-cleanup.php
```

## Safety Features

### Data Integrity
- Only deletes outages with status `resolved`
- Only deletes outages with `resolved_at` timestamp
- Cascade deletes related records (outage_updates, notifications)

### Error Handling
- Cleanup failures don't affect normal operations
- Errors are logged but don't break the application
- Database transactions ensure consistency

### Logging
- Successful cleanups are logged with deletion count
- Failed cleanups are logged with error details
- Cron job logs start/end times

## Monitoring

### Check Cleanup Status
```bash
# Check recent cleanup logs
tail -f /var/log/php_errors.log | grep "cleanup"

# Check cleanup statistics via API
curl -X GET http://yoursite.com/api/v1/outages/cleanup/stats
```

### Database Monitoring
```sql
-- Check resolved outages older than 12 hours
SELECT COUNT(*) as cleanup_eligible
FROM outages 
WHERE status = 'resolved' 
  AND resolved_at IS NOT NULL 
  AND resolved_at < DATE_SUB(NOW(), INTERVAL 12 HOUR);

-- Check recent resolved outages
SELECT COUNT(*) as recent_resolved
FROM outages 
WHERE status = 'resolved' 
  AND resolved_at IS NOT NULL 
  AND resolved_at >= DATE_SUB(NOW(), INTERVAL 12 HOUR);
```

## Migration Guide

### For Existing Installations
1. Run the database migration:
   ```bash
   mysql -u username -p database_name < database/migration_add_resolved_at.sql
   ```

2. Update your application code (already done if using latest version)

3. Set up cron job (optional but recommended):
   ```bash
   crontab -e
   # Add: 0 */6 * * * /usr/bin/php /path/to/your/app/cron/cleanup-outages.php
   ```

### Rollback (if needed)
To disable auto-cleanup:
1. Remove the `cleanupOldResolvedOutages()` calls from `getActiveOutages()` and `getAllOutages()`
2. Remove the cleanup button from admin interface
3. Keep the `resolved_at` column for future use

## Best Practices

1. **Monitor cleanup logs** regularly to ensure it's working
2. **Set up appropriate cron schedule** based on your outage frequency
3. **Test cleanup in staging environment** before production
4. **Keep database backups** before major cleanup operations
5. **Consider notification thresholds** for large-scale cleanups

## Troubleshooting

### Cleanup Not Working
1. Check error logs for PHP/database errors
2. Verify `resolved_at` column exists and has correct data
3. Check that outages have correct status and timestamps
4. Test manual cleanup via admin interface

### Performance Issues
1. Ensure database indexes are in place
2. Consider running cleanup during low-traffic hours
3. Monitor database query performance
4. Adjust cleanup frequency if needed

### Data Loss Concerns
1. Verify cleanup targets only resolved outages
2. Check cascade delete relationships
3. Test with non-production data first
4. Keep database backups

## Code Examples

### PHP Implementation
```php
/**
 * Clean up resolved outages that are older than 12 hours
 */
public function cleanupOldResolvedOutages() {
    try {
        $sql = "DELETE FROM outages 
                WHERE status = 'resolved' 
                AND resolved_at IS NOT NULL 
                AND resolved_at < DATE_SUB(NOW(), INTERVAL 12 HOUR)";
        
        $stmt = $this->db->query($sql);
        
        if ($stmt && $stmt->rowCount() > 0) {
            error_log("Auto-cleanup: Deleted " . $stmt->rowCount() . " resolved outages older than 12 hours");
        }
        
        return true;
    } catch (Exception $e) {
        error_log("Auto-cleanup failed: " . $e->getMessage());
        return false;
    }
}
```

### JavaScript Admin Interface
```javascript
async function performCleanup() {
    if (confirm('This will permanently delete all resolved outages older than 12 hours. Are you sure?')) {
        try {
            const response = await fetch(`${API_BASE_URL}/outages/cleanup`, {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({})
            });
            
            const data = await response.json();
            
            if (data.success) {
                const deletedCount = data.data.deleted_count;
                if (deletedCount > 0) {
                    showAlert(`Cleanup completed! Deleted ${deletedCount} resolved outages older than 12 hours.`, 'success');
                } else {
                    showAlert('No outages needed cleanup.', 'info');
                }
                loadOutages();
                loadStats();
            } else {
                showAlert('Error performing cleanup: ' + data.message, 'danger');
            }
        } catch (error) {
            showAlert('Error performing cleanup: ' + error.message, 'danger');
        }
    }
}
```

### Cron Job Script
```php
#!/usr/bin/env php
<?php
/**
 * Cron job script for automatic cleanup of old resolved outages
 */

chdir(dirname(__DIR__));

require_once 'config/config.php';
require_once 'classes/Database.php';
require_once 'classes/OutageService.php';

error_log("Starting outage cleanup job at " . date('Y-m-d H:i:s'));

try {
    $outageService = new OutageService();
    $cleanupResult = $outageService->performManualCleanup();
    
    if ($cleanupResult['success']) {
        $deletedCount = $cleanupResult['data']['deleted_count'];
        
        if ($deletedCount > 0) {
            error_log("Cleanup completed: Deleted {$deletedCount} resolved outages older than 12 hours");
        } else {
            error_log("Cleanup completed: No outages needed cleanup");
        }
    } else {
        error_log("Cleanup failed: " . $cleanupResult['message']);
    }
    
} catch (Exception $e) {
    error_log("Cleanup job failed with exception: " . $e->getMessage());
}

error_log("Outage cleanup job completed at " . date('Y-m-d H:i:s'));
?>
```

## File Structure

```
project/
├── classes/
│   └── OutageService.php          # Contains cleanup methods
├── database/
│   ├── schema.sql                 # Updated with resolved_at column
│   └── migration_add_resolved_at.sql  # Migration script
├── admin/
│   └── outages.html              # Admin interface with cleanup button
├── api/v1/
│   └── index.php                 # API endpoints for cleanup
├── cron/
│   └── cleanup-outages.php       # Cron job script
├── tests/
│   ├── test-auto-cleanup.php     # Comprehensive testing
│   └── test-cleanup-simple.php   # Quick verification
└── docs/
    └── AUTO_CLEANUP_FEATURE.md   # This documentation
```

## Summary

The auto-cleanup feature provides:

1. **Automatic maintenance** of the outages database
2. **Configurable retention period** (default 12 hours)
3. **Multiple trigger methods** (automatic, manual, cron)
4. **Comprehensive monitoring** and statistics
5. **Safe operation** with proper error handling
6. **Easy configuration** and customization

This feature ensures that your outage management system remains efficient and doesn't accumulate unnecessary historical data while preserving recent outage information for operational needs.