<?php 
function connectd()
{


    include("asl.php");
    $conn = new PDO("mysql:host=$servername;dbname=$mydb", $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"));
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $conn;
}
$conn = connectd();

include('functions.php');
include('class/bot.php');


// $allowedChatIds = [7444950700, 6316143685, 7173186129, 7064234824, 123579145];


//     $time = time() - (24 * 60 * 60);


// $sql = "SELECT 
// products2.chatid,
// user.name,
// user.username,
// products2.subject,
// SUM(CASE 
//     WHEN products2.problem = 'L' THEN products2.c - products2.l_problem 
//     WHEN products2.problem = 'J' THEN products2.c - products2.j_problem 
//     WHEN products2.problem = 'K' THEN products2.c - products2.k_problem
//     ELSE 0
// END) AS total_problem
// FROM 
// products2
// JOIN 
// user ON products2.chatid = user.chatid
// WHERE 
// products2.status = 'problem'
// AND products2.problem_time > $time
// GROUP BY 
// products2.chatid, products2.subject, user.name, user.username
// HAVING 
// products2.subject BETWEEN 1 AND 10;
// ";

//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();



//     foreach($results as $result){

//         $telname = $result['name'];
//         $username = $result['username'];
//         $subject = $result['subject'];
//         $total_problem = $result['total_problem'];

// $text = "
// نام کاربر :‌ $telname
// یوزرنیم کاربر :‌ $usernamed
// دسته : $subject
// جمع :‌ $total_problem
// ";
    
//         // sendmessage(urlencode($text), $chatid, '');
//         foreach ($allowedChatIds as $chatid) {
//         sendmessage(urlencode($text), $chatid, '');
//                                             }

//     }
    
    
    
    
    
//     // Group A 
//     $allowedChatIds = [7444950700, 6316143685, 7173186129, 7064234824, 123579145, 5803067539];
    
//     foreach ($allowedChatIds as $chatid) {
    




//     $now = time() - (24 * 60 * 60);

//     // $sql = "SELECT SUM(c - e_problem) AS total_difference
//     // FROM products
//     // WHERE problem = 'e' AND status = 'problem' AND subject = 1 and problem_time >= $now;    
//     // ";

//     $sql = "SELECT SUM(c - 
//     CASE problem
//         WHEN 'e' THEN e_problem
//         WHEN 'f' THEN f_problem
//         WHEN 'g' THEN g_problem
//         WHEN 'h' THEN h_problem
//         WHEN 'i' THEN i_problem
//         WHEN 'p' THEN p_problem
//     END) AS total_difference
// FROM products
// WHERE status = 'problem' 
//     AND subject = 1 and problem_time >= $now;
// ";

// // sendmessage(urlencode('here : ' . $sql), $chatid, '');


//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();


//     $totalOne = $results[0]['total_difference'];
//     $textOne = "آمار 1 : $totalOne";
//     sendmessage(urlencode($textOne), $chatid, '');


//     // $sql = "SELECT SUM(c - e_problem) AS total_difference
//     // FROM products
//     // WHERE problem = 'e' AND status = 'problem' AND subject = 2 and problem_time >= $now;    
//     // ";


// $sql = "SELECT SUM(c - 
//     CASE problem
//         WHEN 'e' THEN e_problem
//         WHEN 'f' THEN f_problem
//         WHEN 'g' THEN g_problem
//         WHEN 'h' THEN h_problem
//         WHEN 'i' THEN i_problem
//         WHEN 'p' THEN p_problem
//     END) AS total_difference
// FROM products
// WHERE status = 'problem' 
//     AND subject = 2 and problem_time >= $now;
// ";

//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();


//     $totalTwo = $results[0]['total_difference'];
//     $textTwo = "آمار 2 : $totalTwo";
//     sendmessage(urlencode($textTwo), $chatid, '');


//     // $sql = "SELECT SUM(c - e_problem) AS total_difference
//     // FROM products
//     // WHERE problem = 'e' AND status = 'problem' AND subject = 3 and problem_time >= $now;    
//     // ";

// $sql = "SELECT SUM(c - 
//     CASE problem
//         WHEN 'e' THEN e_problem
//         WHEN 'f' THEN f_problem
//         WHEN 'g' THEN g_problem
//         WHEN 'h' THEN h_problem
//         WHEN 'i' THEN i_problem
//         WHEN 'p' THEN p_problem
//     END) AS total_difference
// FROM products
// WHERE status = 'problem' 
//     AND subject = 3 and problem_time >= $now;
// ";

//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();


//     $totalThree = $results[0]['total_difference'];
//     $textThree = "آمار 3 : $totalThree";
//     sendmessage(urlencode($textThree), $chatid, '');


//     // $sql = "SELECT SUM(c - e_problem) AS total_difference
//     // FROM products
//     // WHERE problem = 'e' AND status = 'problem' AND subject = 4 and problem_time >= $now;    
//     // ";

//     $sql = "SELECT SUM(c - 
//     CASE problem
//         WHEN 'e' THEN e_problem
//         WHEN 'f' THEN f_problem
//         WHEN 'g' THEN g_problem
//         WHEN 'h' THEN h_problem
//         WHEN 'i' THEN i_problem
//         WHEN 'p' THEN p_problem
//     END) AS total_difference
// FROM products
// WHERE status = 'problem' 
//     AND subject = 4 and problem_time >= $now;
// ";

//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();


//     $totalFour = $results[0]['total_difference'];
//     $textFour = "آمار 4 : $totalFour";
//     sendmessage(urlencode($textFour), $chatid, '');



//     // $sql = "SELECT SUM(c - e_problem) AS total_difference
//     // FROM products
//     // WHERE problem = 'e' AND status = 'problem' AND subject = 5 and problem_time >= $now;    
//     // ";

//     $sql = "SELECT SUM(c - 
//     CASE problem
//         WHEN 'e' THEN e_problem
//         WHEN 'f' THEN f_problem
//         WHEN 'g' THEN g_problem
//         WHEN 'h' THEN h_problem
//         WHEN 'i' THEN i_problem
//         WHEN 'p' THEN p_problem
//     END) AS total_difference
// FROM products
// WHERE status = 'problem' 
//     AND subject = 5 and problem_time >= $now;
// ";

//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();


//     $totalFive = $results[0]['total_difference'];
//     $textFive = "آمار 5 : $totalFive";
//     sendmessage(urlencode($textFive), $chatid, '');


//     // $sql = "SELECT SUM(c - e_problem) AS total_difference
//     // FROM products
//     // WHERE problem = 'e' AND status = 'problem' AND subject = 6 and problem_time >= $now;    
//     // ";


//     $sql = "SELECT SUM(c - 
//     CASE problem
//         WHEN 'e' THEN e_problem
//         WHEN 'f' THEN f_problem
//         WHEN 'g' THEN g_problem
//         WHEN 'h' THEN h_problem
//         WHEN 'i' THEN i_problem
//         WHEN 'p' THEN p_problem
//     END) AS total_difference
// FROM products
// WHERE status = 'problem' 
//     AND subject = 6 and problem_time >= $now;
// ";
//     $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();


//     $totalSix = $results[0]['total_difference'];
//     $textSix = "آمار 6 : $totalSix";
//     sendmessage(urlencode($textSix), $chatid, '');


    // $sql = "SELECT SUM(c - e_problem) AS total_difference
    // FROM products
    // WHERE problem = 'e' AND status = 'problem' AND subject = 7 and problem_time >= $now;    
    // ";

    // $stmt = $conn->prepare($sql);
    // $stmt->execute();
    // $stmt->setFetchMode(PDO::FETCH_ASSOC);
    // $results = $stmt->fetchAll();


    // $totalEight = $results[0]['total_difference'];
    // $textEight = "آمار 8 : $totalEight";
    // sendmessage(urlencode($textEight), $chatid, '');

    // $sql = "SELECT SUM(c - e_problem) AS total_difference
    // FROM products
    // WHERE problem = 'e' AND status = 'problem' AND subject = 9 and problem_time >= $now;    
    // ";

    // $stmt = $conn->prepare($sql);
    // $stmt->execute();
    // $stmt->setFetchMode(PDO::FETCH_ASSOC);
    // $results = $stmt->fetchAll();


    // $totalNine = $results[0]['total_difference'];
    // $textNine = "آمار 9 : $totalNine";
    // sendmessage(urlencode($textNine), $chatid, '');


    // $sql = "SELECT SUM(c - e_problem) AS total_difference
    // FROM products
    // WHERE problem = 'e' AND status = 'problem' AND subject = 10 and problem_time >= $now;    
    // ";

    // $stmt = $conn->prepare($sql);
    // $stmt->execute();
    // $stmt->setFetchMode(PDO::FETCH_ASSOC);
    // $results = $stmt->fetchAll();


    // $totalTen = $results[0]['total_difference'];
    // $textTen = "آمار 10 : $totalTen";
    // sendmessage(urlencode($textTen), $chatid, '');

// }



// New code

// $allowedChatIds = [7444950700, 6316143685, 7173186129, 7064234824, 123579145, 5803067539];
// $sql = "SELECT *
// FROM `b_problems_history`
// WHERE `problem_time` <= NOW() - INTERVAL 24 HOUR;";
// $stmt = $conn->prepare($sql);
//     $stmt->execute();
//     $stmt->setFetchMode(PDO::FETCH_ASSOC);
//     $results = $stmt->fetchAll();



// foreach ($results as $result) {
//     $difference = floatval($result['c']) - floatval($result['problem_value']);
// $text = "
// Group B
// Chatid : $result[chatid]
// Telegram name : $result[telname]
// Username : $result[username]
// Problem : $result[problem]
// Subject : $result[subject]
// Account : $result[a]
// User input :$result[problem_value]
// Value : $result[c]
// Difference : $difference";

// print($text);


//     foreach($allowedChatIds as $chatid){
//         sendmessage(urlencode($text), $chatid, '');
//     }
// }




$allowedChatIds = [5803067539];
// $allowedChatIds = [7444950700, 6316143685, 7173186129, 7064234824, 123579145, 5803067539];


// $sql = "SELECT *
// FROM `a_problems_history`
// WHERE `problem_time` <= NOW() - INTERVAL 24 HOUR;";
// $stmt = $conn->prepare($sql);
// $stmt->execute();
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
// $results = $stmt->fetchAll();

// // آرایه برای نگه‌داری اطلاعات بر اساس chatid و subject
// $groupedData = [];

// foreach ($results as $result) {
//     $difference = floatval($result['c']) - floatval($result['problem_value']);
//     $chatid = $result['chatid'];
//     $subject = $result['subject'];
//     $telname = $result['telname'];
//     $c = floatval($result['c']);
//     $problem_value = floatval($result['problem_value']);

//     // اطمینان از مقداردهی اولیه آرایه‌های چندبعدی
//     if (!isset($groupedData[$chatid])) {
//         $groupedData[$chatid] = [];
//     }
//     if (!isset($groupedData[$chatid][$subject])) {
//         $groupedData[$chatid][$subject] = [];
//     }

//     // ذخیره اطلاعات مربوط به هر subject
//     $groupedData[$chatid][$subject][] = [
//         'telname' => $telname,
//         'c' => $c,
//         'problem_value' => $problem_value,
//         'difference' => $difference
//     ];
// }

// // ارسال پیام جداگانه برای هر subject و هر chatid
// foreach ($groupedData as $chatid => $subjects) {
//     foreach ($subjects as $subject => $dataList) {
//         $totalDifference = 0;
//         $text = "Group A\n";
//         $text .= "📌 **Summary for Subject: $subject**\n";

//         foreach ($dataList as $data) {
//             $totalDifference += $data['difference'];
//             $text .= "👤 Name: {$data['telname']}\n";
//             $text .= "🔹 Value: {$data['c']}\n";
//             $text .= "🔸 User Input: {$data['problem_value']}\n";
//             $text .= "⚖️ Difference: " . number_format($data['difference'], 2) . "\n\n";
//         }

//         // افزودن مجموع difference برای این subject
//         $text .= "📊 **Total Difference for $subject:** " . number_format($totalDifference, 2) . "\n";

//         // ارسال پیام فقط به chatid مرتبط
//         if (in_array($chatid, $allowedChatIds)) {
//             sendmessage(urlencode($text), $chatid, '');
//         }
//     }
// }


$sql = "SELECT *
FROM `a_problems_history`
WHERE `problem_time` <= NOW() - INTERVAL 24 HOUR;";
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$results = $stmt->fetchAll();

// آرایه برای نگه‌داری مجموع c و problem_value بر اساس chatid و subject
$groupedData = [];

foreach ($results as $result) {
    $chatid = $result['chatid'];
    $subject = $result['subject'];
    $telname = $result['telname'];
    $c = floatval($result['c']);
    
    // اطمینان از اینکه problem_value مقدار عددی دارد
    $problem_value = is_numeric($result['problem_value']) ? floatval($result['problem_value']) : 0;

    // مقداردهی اولیه آرایه در صورت عدم وجود
    if (!isset($groupedData[$chatid])) {
        $groupedData[$chatid] = [];
    }
    if (!isset($groupedData[$chatid][$subject])) {
        $groupedData[$chatid][$subject] = [
            'total_c' => 0,
            'total_problem_value' => 0,
            'users' => []
        ];
    }

    // جمع مقادیر c و problem_value برای هر subject
    $groupedData[$chatid][$subject]['total_c'] += $c;
    $groupedData[$chatid][$subject]['total_problem_value'] += $problem_value;

    // ذخیره اطلاعات مربوط به کاربران
    $groupedData[$chatid][$subject]['users'][] = [
        'telname' => $telname,
        'c' => $c,
        'problem_value' => $problem_value
    ];
}

// ارسال پیام جداگانه برای هر subject و هر chatid
foreach ($groupedData as $chatid => $subjects) {
    foreach ($subjects as $subject => $data) {
        $totalDifference = $data['total_c'] - $data['total_problem_value'];

        // متن گزارش
        $text = "Group A\n";
        $text .= "📌 **Summary for Subject: $subject**\n";

        foreach ($data['users'] as $user) {
            $text .= "👤 Name: {$user['telname']}\n";
            $text .= "🔹 Value: {$user['c']}\n";
            $text .= "🔸 User Input: {$user['problem_value']}\n\n";
        }

        // افزودن مجموع c و problem_value و اختلاف کلی
        $text .= "📊 **Total c for $subject:** " . number_format($data['total_c'], 2) . "\n";
        $text .= "📊 **Total User Input for $subject:** " . number_format($data['total_problem_value'], 2) . "\n";
        $text .= "⚖️ **Total Difference for $subject:** " . number_format($totalDifference, 2) . "\n";

        // ارسال پیام فقط به chatid مرتبط
        if (in_array($chatid, $allowedChatIds)) {
            sendmessage(urlencode($text), $chatid, '');
        }
    }
}



exit();


$sql = "SELECT *
FROM `b_problems_history`
WHERE `problem_time` <= NOW() - INTERVAL 24 HOUR;";
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$results = $stmt->fetchAll();

// آرایه برای نگه‌داری اطلاعات بر اساس chatid و subject
$groupedData = [];

foreach ($results as $result) {
    $difference = floatval($result['c']) - floatval($result['problem_value']);
    $chatid = $result['chatid'];
    $subject = $result['subject'];
    $telname = $result['telname'];
    $c = floatval($result['c']);
    $problem_value = floatval($result['problem_value']);

    // اطمینان از مقداردهی اولیه آرایه‌های چندبعدی
    if (!isset($groupedData[$chatid])) {
        $groupedData[$chatid] = [];
    }
    if (!isset($groupedData[$chatid][$subject])) {
        $groupedData[$chatid][$subject] = [];
    }

    // ذخیره اطلاعات مربوط به هر subject
    $groupedData[$chatid][$subject][] = [
        'telname' => $telname,
        'c' => $c,
        'problem_value' => $problem_value,
        'difference' => $difference
    ];
}

// ارسال پیام جداگانه برای هر subject و هر chatid
foreach ($groupedData as $chatid => $subjects) {
    foreach ($subjects as $subject => $dataList) {
        $totalDifference = 0;
        $text = "Group B\n";
        $text .= "📌 **Summary for Subject: $subject**\n";

        foreach ($dataList as $data) {
            $totalDifference += $data['difference'];
            $text .= "👤 Name: {$data['telname']}\n";
            $text .= "🔹 Value: {$data['c']}\n";
            $text .= "🔸 User Input: {$data['problem_value']}\n";
            $text .= "⚖️ Difference: " . number_format($data['difference'], 2) . "\n\n";
        }

        // افزودن مجموع difference برای این subject
        $text .= "📊 **Total Difference for $subject:** " . number_format($totalDifference, 2) . "\n";

        // ارسال پیام فقط به chatid مرتبط
        if (in_array($chatid, $allowedChatIds)) {
            sendmessage(urlencode($text), $chatid, '');
        }
    }
}


