<?php
// 设置错误报告
ini_set('display_errors', 1);
error_reporting(E_ALL);

// 数据库连接信息
$db_host = '192.168.2.4';
$db_port = 3307;
$db_name = 'task_reporter';
$db_user = 'task_reporter';
$db_pass = 'Pass12349ers!';

// 创建数据库连接
try {
    $pdo = new PDO("mysql:host=$db_host;port=$db_port;dbname=$db_name", $db_user, $db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("SET NAMES utf8");
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

// 创建任务表（如果不存在）
try {
    $sql = "CREATE TABLE IF NOT EXISTS task (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100) NOT NULL,
        tool_name VARCHAR(100) NOT NULL,
        task_name VARCHAR(100) NOT NULL,
        time_saved VARCHAR(100) NOT NULL,
        time_cost VARCHAR(100) NOT NULL,
        timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($sql);
} catch (PDOException $e) {
    die("创建表失败: " . $e->getMessage());
}

// 处理添加任务的POST请求
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_GET['action']) && $_GET['action'] === 'add_task') {
    try {
        $data = json_decode(file_get_contents('php://input'), true);
        
        if (!$data) {
            throw new Exception("无效的JSON数据");
        }
        
        $stmt = $pdo->prepare("INSERT INTO task (username, tool_name, task_name, time_saved, time_cost, timestamp) 
                              VALUES (:username, :tool_name, :task_name, :time_saved, :time_cost, :timestamp)");
        
        $stmt->bindParam(':username', $data['username']);
        $stmt->bindParam(':tool_name', $data['tool_name']);
        $stmt->bindParam(':task_name', $data['task_name']);
        $stmt->bindParam(':time_saved', $data['time_saved']);
        $stmt->bindParam(':time_cost', $data['time_cost']);
        $stmt->bindParam(':timestamp', $data['timestamp']);
        
        $stmt->execute();
        
        $time = explode(".", $data['timestamp'])[0];
        $time = str_replace("T", " ", $time);
        
        header('Content-Type: application/json');
        echo json_encode([
            "message" => "$time {$data['tool_name']}-{$data['task_name']} reported successfully!"
        ]);
        exit;
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(400);
        echo json_encode(["error" => $e->getMessage()]);
        exit;
    }
}

// 处理查询任务数据的GET请求
if (isset($_GET['action']) && $_GET['action'] === 'query_task_data') {
    try {
        // 获取筛选参数
        $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
        $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
        $task_name = isset($_GET['task_name']) ? $_GET['task_name'] : null;
        $tool_name = isset($_GET['tool_name']) ? $_GET['tool_name'] : null;
        $time_saved = isset($_GET['time_saved']) ? $_GET['time_saved'] : null;
        $time_cost = isset($_GET['time_cost']) ? $_GET['time_cost'] : null;
        $show_debug = isset($_GET['show_debug']) && $_GET['show_debug'] === 'true';
        
        // 获取排序参数
        $sort_by = isset($_GET['sort_by']) ? $_GET['sort_by'] : 'timestamp';
        $sort_direction = isset($_GET['sort_direction']) ? $_GET['sort_direction'] : 'desc';
        $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 50;
        
        // 构建查询
        $query = "SELECT * FROM task WHERE 1=1";
        $params = [];
        
        // 应用日期筛选
        if ($start_date) {
            $query .= " AND timestamp >= :start_date";
            $params[':start_date'] = $start_date . " 00:00:00";
        }
        
        if ($end_date) {
            $query .= " AND timestamp <= :end_date";
            $params[':end_date'] = $end_date . " 23:59:59";
        }
        
        // 应用任务名称筛选
        if ($task_name) {
            $query .= " AND task_name LIKE :task_name";
            $params[':task_name'] = "%$task_name%";
        }
        
        // 应用工具名称筛选
        if ($tool_name) {
            $query .= " AND tool_name LIKE :tool_name";
            $params[':tool_name'] = "%$tool_name%";
        }
        
        // 排除调试工具（如果不显示调试）
        if (!$show_debug) {
            $query .= " AND tool_name NOT LIKE '%(Debug)%'";
        }
        
        // 应用排序
        $valid_sort_fields = ['timestamp', 'time_saved', 'time_cost'];
        if (!in_array($sort_by, $valid_sort_fields)) {
            $sort_by = 'timestamp';
        }
        
        $query .= " ORDER BY $sort_by " . ($sort_direction === 'asc' ? 'ASC' : 'DESC');
        
        // 应用分页
        $offset = ($page - 1) * $per_page;
        $query .= " LIMIT :offset, :limit";
        $params[':offset'] = $offset;
        $params[':limit'] = $per_page;
        
        // 执行查询
        $stmt = $pdo->prepare($query);
        foreach ($params as $key => $value) {
            if ($key === ':offset' || $key === ':limit') {
                $stmt->bindValue($key, $value, PDO::PARAM_INT);
            } else {
                $stmt->bindValue($key, $value);
            }
        }
        $stmt->execute();
        
        $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 获取总记录数
        $count_query = "SELECT COUNT(*) FROM task WHERE 1=1";
        $count_params = [];
        
        if ($start_date) {
            $count_query .= " AND timestamp >= :start_date";
            $count_params[':start_date'] = $start_date . " 00:00:00";
        }
        
        if ($end_date) {
            $count_query .= " AND timestamp <= :end_date";
            $count_params[':end_date'] = $end_date . " 23:59:59";
        }
        
        if ($task_name) {
            $count_query .= " AND task_name LIKE :task_name";
            $count_params[':task_name'] = "%$task_name%";
        }
        
        if ($tool_name) {
            $count_query .= " AND tool_name LIKE :tool_name";
            $count_params[':tool_name'] = "%$tool_name%";
        }
        
        if (!$show_debug) {
            $count_query .= " AND tool_name NOT LIKE '%(Debug)%'";
        }
        
        $count_stmt = $pdo->prepare($count_query);
        foreach ($count_params as $key => $value) {
            $count_stmt->bindValue($key, $value);
        }
        $count_stmt->execute();
        $total_count = $count_stmt->fetchColumn();
        
        // 获取所有工具名称和任务名称（用于筛选）
        $tools_stmt = $pdo->query("SELECT DISTINCT tool_name FROM task ORDER BY tool_name");
        $tools = $tools_stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $tasks_stmt = $pdo->query("SELECT DISTINCT task_name FROM task ORDER BY task_name");
        $task_names = $tasks_stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // 返回数据
        $result = [
            'tasks' => $tasks,
            'total' => $total_count,
            'page' => $page,
            'per_page' => $per_page,
            'total_pages' => ceil($total_count / $per_page),
            'tools' => $tools,
            'task_names' => $task_names
        ];
        
        header('Content-Type: application/json');
        echo json_encode($result);
        exit;
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(["error" => $e->getMessage()]);
        exit;
    }
}

// 处理获取工具统计数据的GET请求
if (isset($_GET['action']) && $_GET['action'] === 'get_tool_statistics') {
    try {
        // 获取日期范围
        $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
        $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
        
        // 构建查询
        $query = "SELECT tool_name, COUNT(*) as request_count, SUM(time_saved) as time_saved 
                  FROM task 
                  WHERE tool_name NOT LIKE '%(Debug)%'";
        $params = [];
        
        if ($start_date) {
            $query .= " AND timestamp >= :start_date";
            $params[':start_date'] = $start_date . " 00:00:00";
        }
        
        if ($end_date) {
            $query .= " AND timestamp <= :end_date";
            $params[':end_date'] = $end_date . " 23:59:59";
        }
        
        $query .= " GROUP BY tool_name ORDER BY request_count DESC";
        
        // 执行查询
        $stmt = $pdo->prepare($query);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        
        $tools_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 计算总请求数和总节省时间
        $total_requests = 0;
        $total_time_saved = 0;
        
        foreach ($tools_stats as $stat) {
            $total_requests += $stat['request_count'];
            $total_time_saved += $stat['time_saved'];
        }
        
        // 返回数据
        $result = [
            'tools' => $tools_stats,
            'total_requests' => $total_requests,
            'total_time_saved' => $total_time_saved
        ];
        
        header('Content-Type: application/json');
        echo json_encode($result);
        exit;
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(["error" => $e->getMessage()]);
        exit;
    }
}

// 处理导出CSV的GET请求
if (isset($_GET['action']) && $_GET['action'] === 'export_csv') {
    try {
        // 获取筛选参数
        $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
        $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
        $task_name = isset($_GET['task_name']) ? $_GET['task_name'] : null;
        $tool_name = isset($_GET['tool_name']) ? $_GET['tool_name'] : null;
        $show_debug = isset($_GET['show_debug']) && $_GET['show_debug'] === 'true';
        
        // 构建查询
        $query = "SELECT id, username, tool_name, task_name, time_saved, time_cost, timestamp 
                  FROM task WHERE 1=1";
        $params = [];
        
        if ($start_date) {
            $query .= " AND timestamp >= :start_date";
            $params[':start_date'] = $start_date . " 00:00:00";
        }
        
        if ($end_date) {
            $query .= " AND timestamp <= :end_date";
            $params[':end_date'] = $end_date . " 23:59:59";
        }
        
        if ($task_name) {
            $query .= " AND task_name LIKE :task_name";
            $params[':task_name'] = "%$task_name%";
        }
        
        if ($tool_name) {
            $query .= " AND tool_name LIKE :tool_name";
            $params[':tool_name'] = "%$tool_name%";
        }
        
        if (!$show_debug) {
            $query .= " AND tool_name NOT LIKE '%(Debug)%'";
        }
        
        $query .= " ORDER BY timestamp DESC";
        
        // 执行查询
        $stmt = $pdo->prepare($query);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();
        
        $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 设置CSV文件头
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=task_data.csv');
        
        // 创建CSV输出流
        $output = fopen('php://output', 'w');
        
        // 添加CSV头行
        fputcsv($output, ['ID', 'Username', 'Tool Name', 'Task Name', 'Time Saved', 'Time Cost', 'Timestamp']);
        
        // 添加数据行
        foreach ($tasks as $task) {
            fputcsv($output, [
                $task['id'],
                $task['username'],
                $task['tool_name'],
                $task['task_name'],
                $task['time_saved'],
                $task['time_cost'],
                $task['timestamp']
            ]);
        }
        
        fclose($output);
        exit;
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(["error" => $e->getMessage()]);
        exit;
    }
}

// 处理获取特定工具任务的GET请求
if (isset($_GET['action']) && $_GET['action'] === 'get_tool_tasks') {
    try {
        $tool_name = isset($_GET['tool_name']) ? $_GET['tool_name'] : null;
        
        if (!$tool_name) {
            throw new Exception("Tool name is required");
        }
        
        // 查询该工具的所有任务
        $stmt = $pdo->prepare("SELECT task_name, COUNT(*) as total_requests, SUM(time_saved) as time_saved 
                               FROM task 
                               WHERE tool_name = :tool_name AND tool_name NOT LIKE '%(Debug)%' 
                               GROUP BY task_name 
                               ORDER BY total_requests DESC");
        $stmt->bindParam(':tool_name', $tool_name);
        $stmt->execute();
        
        $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        header('Content-Type: application/json');
        echo json_encode(['tasks' => $tasks]);
        exit;
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(["error" => $e->getMessage()]);
        exit;
    }
}

// 处理获取趋势数据的GET请求
if (isset($_GET['action']) && $_GET['action'] === 'get_trend_data') {
    try {
        // 获取查询参数中的日期范围
        $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : null;
        $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : null;
        
        // 如果没有指定日期范围，默认显示最近30天
        if (!$start_date || !$end_date) {
            $end_date = date('Y-m-d');
            $start_date = date('Y-m-d', strtotime('-30 days'));
        }
        
        // 将字符串日期转换为日期对象
        $start_datetime = date_create($start_date);
        $end_datetime = date_create($end_date);
        
        // 设置结束日期为当天的最后一秒
        $end_datetime->setTime(23, 59, 59);
        
        // 使用PDO查询
        $stmt = $pdo->prepare("SELECT DATE(timestamp) as date, COUNT(*) as total_requests, SUM(time_saved) as time_saved 
                               FROM task 
                               WHERE timestamp BETWEEN :start_date AND :end_date 
                               GROUP BY DATE(timestamp) 
                               ORDER BY date");
        $stmt->bindValue(':start_date', $start_datetime->format('Y-m-d 00:00:00'));
        $stmt->bindValue(':end_date', $end_datetime->format('Y-m-d H:i:s'));
        $stmt->execute();
        
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // 填充所有日期（包括没有数据的日期）
        $dates = [];
        $requests = [];
        $time_saved = [];
        
        // 创建日期范围内的所有日期
        $date_range = [];
        $current = clone $start_datetime;
        while ($current <= $end_datetime) {
            $date_range[$current->format('Y-m-d')] = [
                'total_requests' => 0,
                'time_saved' => 0
            ];
            $current->modify('+1 day');
        }
        
        // 填充实际数据
        foreach ($results as $row) {
            $date_range[$row['date']] = [
                'total_requests' => (int)$row['total_requests'],
                'time_saved' => (float)$row['time_saved']
            ];
        }
        
        // 转换为数组格式
        foreach ($date_range as $date => $stats) {
            $dates[] = $date;
            $requests[] = $stats['total_requests'];
            $time_saved[] = $stats['time_saved'];
        }
        
        $response_data = [
            'dates' => $dates,
            'requests' => $requests,
            'timeSaved' => $time_saved
        ];
        
        header('Content-Type: application/json');
        echo json_encode($response_data);
        exit;
    } catch (Exception $e) {
        header('Content-Type: application/json');
        http_response_code(500);
        echo json_encode(["error" => $e->getMessage()]);
        exit;
    }
}

// 如果没有特定操作，显示主页
?>
<!DOCTYPE html>
<html>
<head>
    <title>Task Report System</title>
    <style>
        body {
            margin: 0;
            padding: 0;
            height: 100vh;
            display: flex;
            flex-direction: column;
            justify-content: center;
            align-items: center;
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-image: url('static/background.jpg');
            background-size: cover;
            background-position: center;
            background-repeat: no-repeat;
            position: relative;
        }

        body::before {
            content: '';
            position: absolute;
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            background: rgba(0, 0, 0, 0.2);
            z-index: 1;
        }

        .content {
            position: relative;
            z-index: 2;
            text-align: center;
        }

        h1 {
            color: rgb(255, 166, 0);
            font-size: 4em;
            margin-bottom: 30px;
            text-shadow: 2px 2px 4px rgba(114, 77, 10, 0.5);
            animation: fadeIn 1.5s ease-out;
        }

        .view-btn {
            background: transparent;
            border: 2px solid white;
            color: white;
            padding: 15px 40px;
            font-size: 1.2em;
            cursor: pointer;
            transition: all 0.3s ease;
            border-radius: 30px;
            text-decoration: none;
            animation: slideUp 1s ease-out 0.5s both;
        }

        .view-btn:hover {
            background: rgba(255, 255, 255, 0.1);
            transform: translateY(-3px);
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.3);
        }

        /* 淡入动画 */
        @keyframes fadeIn {
            from {
                opacity: 0;
                transform: translateY(-20px);
            }
            to {
                opacity: 1;
                transform: translateY(0);
            }
        }

        @keyframes slideUp {
            from {
                opacity: 0;
                transform: translateY(20px);
            }
            to {
                opacity: 1;
                transform: translateY(0);
            }
        }

        @media (max-width: 768px) {
            h1 {
                font-size: 2.5em;
                padding: 0 20px;
            }

            .view-btn {
                padding: 12px 30px;
                font-size: 1em;
            }
        }
    </style>
</head>
<body>
    <div class="content">
        <h1>CGNICO Task Report System</h1>
        <button class="view-btn" onclick="window.location.href='query_task_data.php'" style="margin-right: 15px;">
            查看任务数据
        </button>
        <button class="view-btn" onclick="window.location.href='generate_report.php'" style="margin-left: 15px;">
            查看报告
        </button>
    </div>
</body>
</html>