tougao/cleanup.php

<?php
/**
 * 数据清理脚本
 * 用于定期清理过期的IP限制记录和旧的投稿数据
 * 建议通过定时任务每天运行一次
 */

require_once 'config/database.php';

class DataCleanup {
    private $db;
    
    public function __construct() {
        $database = new Database();
        $this->db = $database->getConnection();
    }
    
    /**
     * 清理过期的IP限制记录
     * 删除7天前的记录
     */
    public function cleanupIPLimits() {
        try {
            $sql = "DELETE FROM ip_limits WHERE DATE(last_submit) < DATE_SUB(NOW(), INTERVAL 7 DAY)";
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute();
            
            $deletedRows = $stmt->rowCount();
            $this->log("清理IP限制记录: 删除了 {$deletedRows} 条过期记录");
            
            return $deletedRows;
        } catch (Exception $e) {
            $this->log("清理IP限制记录失败: " . $e->getMessage(), 'ERROR');
            return false;
        }
    }
    
    /**
     * 清理旧的投稿记录
     * 删除6个月前已处理的记录
     */
    public function cleanupOldSubmissions() {
        $deletedWebsite = 0;
        $deletedApp = 0;
        
        try {
            // 清理网址投稿记录
            $sql = "DELETE FROM website_submissions WHERE status != 'pending' AND DATE(created_at) < DATE_SUB(NOW(), INTERVAL 6 MONTH)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $deletedWebsite = $stmt->rowCount();
            
            // 清理APP投稿记录
            $sql = "DELETE FROM app_submissions WHERE status != 'pending' AND DATE(created_at) < DATE_SUB(NOW(), INTERVAL 6 MONTH)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute();
            $deletedApp = $stmt->rowCount();
            
            $this->log("清理旧投稿记录: 网址投稿 {$deletedWebsite} 条, APP投稿 {$deletedApp} 条");
            
            return ['website' => $deletedWebsite, 'app' => $deletedApp];
        } catch (Exception $e) {
            $this->log("清理旧投稿记录失败: " . $e->getMessage(), 'ERROR');
            return false;
        }
    }
    
    /**
     * 优化数据库表
     */
    public function optimizeTables() {
        try {
            $tables = ['admins', 'website_submissions', 'app_submissions', 'ip_limits'];
            
            foreach ($tables as $table) {
                $sql = "OPTIMIZE TABLE {$table}";
                $stmt = $this->db->prepare($sql);
                $stmt->execute();
            }
            
            $this->log("数据库表优化完成");
            return true;
        } catch (Exception $e) {
            $this->log("数据库表优化失败: " . $e->getMessage(), 'ERROR');
            return false;
        }
    }
    
    /**
     * 获取数据库统计信息
     */
    public function getStatistics() {
        try {
            $stats = [];
            
            // 统计各表记录数
            $tables = [
                'website_submissions' => '网址投稿',
                'app_submissions' => 'APP投稿',
                'ip_limits' => 'IP限制记录',
                'admins' => '管理员账户'
            ];
            
            foreach ($tables as $table => $name) {
                $sql = "SELECT COUNT(*) as count FROM {$table}";
                $stmt = $this->db->prepare($sql);
                $stmt->execute();
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $stats[$name] = $result['count'];
            }
            
            // 统计各状态的投稿数量
            $statusStats = [];
            $statuses = ['pending' => '待处理', 'approved' => '已通过', 'rejected' => '已拒绝'];
            
            foreach ($statuses as $status => $name) {
                // 网址投稿
                $sql = "SELECT COUNT(*) as count FROM website_submissions WHERE status = ?";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([$status]);
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $statusStats["网址投稿-{$name}"] = $result['count'];
                
                // APP投稿
                $sql = "SELECT COUNT(*) as count FROM app_submissions WHERE status = ?";
                $stmt = $this->db->prepare($sql);
                $stmt->execute([$status]);
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $statusStats["APP投稿-{$name}"] = $result['count'];
            }
            
            return array_merge($stats, $statusStats);
        } catch (Exception $e) {
            $this->log("获取统计信息失败: " . $e->getMessage(), 'ERROR');
            return false;
        }
    }
    
    /**
     * 记录日志
     */
    private function log($message, $level = 'INFO') {
        $timestamp = date('Y-m-d H:i:s');
        $logMessage = "[{$timestamp}] [{$level}] {$message}" . PHP_EOL;
        
        // 输出到控制台
        echo $logMessage;
        
        // 写入日志文件
        $logFile = 'data/cleanup.log';
        if (!file_exists('data')) {
            mkdir('data', 0755, true);
        }
        file_put_contents($logFile, $logMessage, FILE_APPEND | LOCK_EX);
    }
    
    /**
     * 执行完整的清理流程
     */
    public function runFullCleanup() {
        $this->log("开始执行数据清理任务");
        
        // 显示清理前的统计信息
        $beforeStats = $this->getStatistics();
        if ($beforeStats) {
            $this->log("清理前统计信息:");
            foreach ($beforeStats as $key => $value) {
                $this->log("  {$key}: {$value}");
            }
        }
        
        // 执行清理任务
        $ipCleanup = $this->cleanupIPLimits();
        $submissionCleanup = $this->cleanupOldSubmissions();
        $optimize = $this->optimizeTables();
        
        // 显示清理后的统计信息
        $afterStats = $this->getStatistics();
        if ($afterStats) {
            $this->log("清理后统计信息:");
            foreach ($afterStats as $key => $value) {
                $this->log("  {$key}: {$value}");
            }
        }
        
        $this->log("数据清理任务完成");
        
        return [
            'ip_cleanup' => $ipCleanup,
            'submission_cleanup' => $submissionCleanup,
            'optimize' => $optimize,
            'before_stats' => $beforeStats,
            'after_stats' => $afterStats
        ];
    }
}

// 如果直接运行此脚本
if (php_sapi_name() === 'cli' || !isset($_SERVER['HTTP_HOST'])) {
    $cleanup = new DataCleanup();
    $result = $cleanup->runFullCleanup();
    
    echo "\n清理任务执行完成!\n";
    if ($result['ip_cleanup'] !== false) {
        echo "IP限制记录清理: {$result['ip_cleanup']} 条\n";
    }
    if ($result['submission_cleanup'] !== false) {
        echo "投稿记录清理: 网址 {$result['submission_cleanup']['website']} 条, APP {$result['submission_cleanup']['app']} 条\n";
    }
    echo "数据库优化: " . ($result['optimize'] ? '成功' : '失败') . "\n";
} else {
    // 如果通过Web访问，返回JSON格式结果
    header('Content-Type: application/json; charset=utf-8');
    
    // 简单的安全检查
    if (!isset($_GET['token']) || $_GET['token'] !== 'cleanup_token_2024') {
        http_response_code(403);
        echo json_encode(['error' => '访问被拒绝']);
        exit;
    }
    
    $cleanup = new DataCleanup();
    $result = $cleanup->runFullCleanup();
    
    echo json_encode([
        'success' => true,
        'message' => '清理任务执行完成',
        'data' => $result
    ], JSON_UNESCAPED_UNICODE);
}
?>