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

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

// --- DATABASE CONNECTION & CONFIGURATION ---
// Re-using the essential configuration from generate_report.php
define('DB_HOST', 'localhost');
define('DB_USER', 'cybsoul_loyality');
define('DB_PASS', 'Digital@1270');
define('DB_NAME', 'cybsoul_taskapp');

const ROLE_SMM = 5;
const ROLE_GD = 3;
const ROLE_VE = 4;

$roles_to_report = [
    'SMM'               => ROLE_SMM,
    'Production Team'   => [ROLE_GD, ROLE_VE],
];

// ----------------------------------------------------------------------
// 1. DATABASE CONNECTION FUNCTION
// (Same as in generate_report.php, but returns PDO object)
// ----------------------------------------------------------------------

function get_db_connection() {
    try {
        $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4", DB_USER, DB_PASS);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    } catch (PDOException $e) {
        error_log("Dashboard DB Connection Error: " . $e->getMessage());
        // Return error message in JSON
        echo json_encode(['error' => 'Database connection failed.', 'details' => $e->getMessage()]);
        exit();
    }
}

// ----------------------------------------------------------------------
// 2. DATA FETCHING LOGIC (Simplified from generate_report.php)
// ----------------------------------------------------------------------

function get_report_data_for_role(PDO $pdo, $role_id, $role_name) {
    // ... [Logic remains exactly same as in generate_report.php]
    
    // Convert role_id to string for SQL IN clause
    $role_filter = is_array($role_id) ? implode(',', $role_id) : $role_id;
    
    // Fetch distinct users based on the role filter
    $stmt_users = $pdo->prepare("
        SELECT DISTINCT u.id, u.name 
        FROM users u
        INNER JOIN user_roles ur ON u.id = ur.user_id
        WHERE ur.role_id IN ({$role_filter})
    ");
    $stmt_users->execute();
    $users_data = $stmt_users->fetchAll(PDO::FETCH_ASSOC);

    $report_data = [];

    // ----------------------------------------------------------------------
    // MAIN TASK DATA LOOP
    // ----------------------------------------------------------------------

    foreach ($users_data as $user) {
        $user_id = $user['id'];

        // --- PRODUCTION TEAM LOGIC (GD/VE) ---
        if ($role_name === 'Production Team') {
            
            // 1. TOTAL ASSIGNED (Today's assigned tasks - ALL types)
            $stmt_assigned = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE assigned_user_id = :user_id 
                AND DATE(assigned_date) = CURDATE()
            ");
            $stmt_assigned->execute([':user_id' => $user_id]);
            $assigned_today = $stmt_assigned->fetch(PDO::FETCH_ASSOC)['count'];
            
            // 2. DONE (Completed OR In Review - Today's time till NOW)
            $stmt_done = $pdo->prepare("
                SELECT 
                    COUNT(CASE WHEN (status = 'Completed' OR status = 'In Review') AND DATE(completed_at) = CURDATE() AND completed_at <= NOW() THEN 1 END) AS count_done_today,
                    COUNT(CASE WHEN status = 'Skipped' AND DATE(completed_at) = CURDATE() THEN 1 END) AS count_skipped
                FROM tasks
                WHERE assigned_user_id = :user_id
            ");
            $stmt_done->execute([':user_id' => $user_id]);
            $done_metrics = $stmt_done->fetch(PDO::FETCH_ASSOC);

            // 3. PENDING (Not Started OR In Process - Overall)
            $stmt_pending = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE assigned_user_id = :user_id 
                AND (status = 'Not Started' OR status = 'In Process')
            ");
            $stmt_pending->execute([':user_id' => $user_id]);
            $pending_overall = $stmt_pending->fetch(PDO::FETCH_ASSOC)['count'];
            
            // 4. OVERDUE (Overall - Assigned before today, not completed, rejected, or skipped)
            $stmt_overdue = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE assigned_user_id = :user_id 
                AND DATE(assigned_date) < CURDATE() 
                AND status != 'Completed' AND status != 'Rejected' AND status != 'Skipped'
            ");
            $stmt_overdue->execute([':user_id' => $user_id]);
            $overdue_overall = $stmt_overdue->fetch(PDO::FETCH_ASSOC)['count'];

            $report_data[] = [
                'user_name'         => $user['name'],
                'role_name'         => $role_name,
                'total_assigned'    => $assigned_today,
                'done_task'         => $done_metrics['count_done_today'],
                'pending_task'      => $pending_overall,
                'overdue_task'      => $overdue_overall,
                'count_skipped'     => $done_metrics['count_skipped'],
            ];


        // --- SMM LOGIC (Reviewer/Posting) ---
        } elseif ($role_name === 'SMM') {
            
            // 1. TOTAL ASSIGNED (SMM Assigned Today + SMM Created Today)
            $stmt_assigned_direct = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE assigned_user_id = :user_id AND DATE(assigned_date) = CURDATE()
            ");
            $stmt_assigned_direct->execute([':user_id' => $user_id]);
            $assigned_count = $stmt_assigned_direct->fetch(PDO::FETCH_ASSOC)['count'];

            $stmt_created = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE created_by = :user_id AND DATE(created_at) = CURDATE()
            ");
            $stmt_created->execute([':user_id' => $user_id]);
            $created_count = $stmt_created->fetch(PDO::FETCH_ASSOC)['count'];

            $total_assigned_combined = ($assigned_count ?? 0) + ($created_count ?? 0);
            
            // 2. DONE (Completed tasks - Today's time till NOW)
            $stmt_done = $pdo->prepare("
                SELECT COUNT(CASE WHEN status = 'Completed' AND DATE(completed_at) = CURDATE() AND completed_at <= NOW() THEN 1 END) AS count_done_today
                FROM tasks
                WHERE assigned_user_id = :user_id
            ");
            $stmt_done->execute([':user_id' => $user_id]);
            $done_metrics = $stmt_done->fetch(PDO::FETCH_ASSOC);

            // 3. PENDING (Overall pending tasks assigned to SMM - Not Started/In Process)
            $stmt_pending_direct = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE assigned_user_id = :user_id 
                AND (status = 'Not Started' OR status = 'In Process')
            ");
            $stmt_pending_direct->execute([':user_id' => $user_id]);
            $pending_overall_direct = $stmt_pending_direct->fetch(PDO::FETCH_ASSOC)['count'];


            // 4. OVERDUE (Overall)
            $stmt_overdue = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE assigned_user_id = :user_id 
                AND due_date < CURDATE() 
                AND status != 'Completed' AND status != 'Rejected' AND status != 'Skipped'
            ");
            $stmt_overdue->execute([':user_id' => $user_id]);
            $overdue_overall = $stmt_overdue->fetch(PDO::FETCH_ASSOC)['count'];

            // 5. PENDING APPROVAL (Overall - Where SMM is the Creator/Reviewer)
            $stmt_pending_approval = $pdo->prepare("
                SELECT COUNT(id) AS count
                FROM tasks
                WHERE created_by = :user_id 
                AND status = 'In Review'
            ");
            $stmt_pending_approval->execute([':user_id' => $user_id]);
            $pending_approval_count = $stmt_pending_approval->fetch(PDO::FETCH_ASSOC)['count'];

            $report_data[] = [
                'user_name'             => $user['name'],
                'role_name'             => $role_name,
                'total_assigned'        => $total_assigned_combined,
                'done_task'             => $done_metrics['count_done_today'],
                'pending_task'          => $pending_overall_direct,
                'overdue_task'          => $overdue_overall,
                'smm_pending_approval'  => $pending_approval_count, 
            ];
        }
    }
    
    // Safety check: ensure only unique users are reported (important for combined roles)
    $unique_users_data = [];
    $seen_users = [];

    foreach ($report_data as $data) {
        if (!in_array($data['user_name'], $seen_users)) {
            $unique_users_data[] = $data;
            $seen_users[] = $data['user_name'];
        }
    }
    
    return $unique_users_data;
}

// ----------------------------------------------------------------------
// 3. MAIN EXECUTION
// ----------------------------------------------------------------------

function main(array $roles_to_report) {
    $pdo = get_db_connection();
    if (!$pdo) {
        // Error already outputted in get_db_connection
        return; 
    }

    $all_report_data = [];

    // Loop through roles to fetch data
    foreach ($roles_to_report as $role_name => $role_id) {
        $data = get_report_data_for_role($pdo, $role_id, $role_name);
        $all_report_data = array_merge($all_report_data, $data);
    }

    if (empty($all_report_data)) {
        echo json_encode(['error' => 'No user data found.']);
        return;
    }

    // Output JSON data
    echo json_encode(['timestamp' => date('Y-m-d H:i:s'), 'data' => $all_report_data]);
}

// Execute the main function
main($roles_to_report);
?>
