<?php
// PHP Error reporting for debugging (comment out in production)
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Load PHPMailer classes (needed for environment setup in some servers)
require_once __DIR__ . '/PHPMailer-master/src/Exception.php';
require_once __DIR__ . '/PHPMailer-master/src/PHPMailer.php';
require_once __DIR__ . '/PHPMailer-master/src/SMTP.php';

// Database Credentials (Hardcoded for Cron Job/Standalone reliability)
define('DB_HOST', 'localhost');
define('DB_USER', 'cybsoul_loyality');
define('DB_PASS', 'Digital@1270');
define('DB_NAME', 'cybsoul_taskapp');

// ------------------- CORE FUNCTIONS -------------------

function get_db_connection() {
    try {
        $pdo = new PDO(
            "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
            DB_USER,
            DB_PASS,
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]
        );
        return $pdo;
    } catch (PDOException $e) {
        // Return error details instead of echoing (for JSON response)
        return ['error' => 'Database connection failed.', 'details' => $e->getMessage()];
    }
}

function get_live_pending_tasks(PDO $pdo) {
    // Current statuses that are considered "Pending" for the dashboard
    $pending_statuses = ['Not Started', 'In Process'];
    $pending_statuses_sql = "'" . implode("','", $pending_statuses) . "'";

    try {
        // Fetch all pending tasks with user and role details
        // Note: Tasks are NOT 'Completed', 'In Review', 'Rejected', or 'Skipped'
        $sql = "
            SELECT
                t.id AS task_id,
                t.title,
                t.assigned_date,
                t.status,
                u.name AS user_name,
                r.name AS role_name
            FROM tasks t
            INNER JOIN users u ON t.assigned_user_id = u.id
            INNER JOIN user_roles ur ON u.id = ur.user_id
            INNER JOIN roles r ON ur.role_id = r.id
            WHERE 
                t.status IN ({$pending_statuses_sql})
            ORDER BY u.name ASC, t.assigned_date ASC
        ";

        $stmt = $pdo->query($sql);
        $tasks = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Group tasks by user for organized display
        $grouped_tasks = [];
        foreach ($tasks as $task) {
            $user_key = $task['user_name'] . ' (' . $task['role_name'] . ')';
            if (!isset($grouped_tasks[$user_key])) {
                $grouped_tasks[$user_key] = [
                    'user_name' => $task['user_name'],
                    'role_name' => $task['role_name'],
                    'tasks' => []
                ];
            }
            $grouped_tasks[$user_key]['tasks'][] = [
                'task_id' => $task['task_id'],
                'title' => $task['title'],
                'assigned_date' => $task['assigned_date'],
                'status' => $task['status']
            ];
        }

        return ['status' => 'success', 'data' => array_values($grouped_tasks)];

    } catch (PDOException $e) {
        // Log the error (optional) and return a detailed error response
        return ['status' => 'error', 'error' => 'SQL Query Failed', 'details' => $e->getMessage()];
    }
}

// ------------------- MAIN EXECUTION -------------------

header('Content-Type: application/json');

$pdo = get_db_connection();

if (is_array($pdo) && isset($pdo['error'])) {
    echo json_encode(['status' => 'error', 'error' => $pdo['error'], 'details' => $pdo['details']]);
} else {
    $report = get_live_pending_tasks($pdo);
    echo json_encode($report);
}

exit;
