<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
require_once('config.php');
require_once('connection.php');
require_once('connectionUI.php');
require 'vendor/vendor/autoload.php'; // Include PhpSpreadsheet library

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

$db = new Connection();
$dbui = new ConnectionUI();
$dbconn = new DBConnection();
session_start();
if (isset($_POST['submit'])) {
    $_SESSION['logged_in'] = true;
    $username = $_POST['username'];
    $password = $_POST['password'];
    $_SESSION['username'] = $username;

    if ($username === "bkpmysamm" && $password === "12345678") {
        header("Location: filer_status.php");
        exit();
    } elseif ($username !== "bkpmysamm" && $password === "12345678") {
        header("Location: index.php?error=username");
        exit();
    } elseif ($username === "bkpmysamm" && $password !== "12345678") {
        header("Location: index.php?error=password");
        exit();
    } else {
        header("Location: index.php?error=both");
        exit();
    }
}
// if (isset($_GET['statusId']) && isset($_GET['name']) && isset($_GET['web']) /*&& isset($_GET['date'])*/) {
//     $statusId = isset($_GET['statusId']) ? $_GET['statusId'] : null;
//     $name = isset($_GET['name']) ? $_GET['name'] : null;
//     $web = isset($_GET['web']) ? $_GET['web'] : null;
//     // $created_date = isset($_GET['date']) ? $_GET['date'] : null;
//     // print_r($created_date); 
//     $whereClause = '';
//     if ($statusId === 'all' && $name === 'all' && $web === 'all' /*&& $created_date === ''*/) {
//         $query = "SELECT users.title as username, crawlers.title, instances_running.*
//             FROM instances_running
//             JOIN users ON users.id = instances_running.user_id
//             JOIN crawlers ON crawlers.id = instances_running.crawler_id
//             AND users.username NOT LIKE '%DEMO%' AND users.username NOT LIKE '%TEST%'
//             ";
//     } else {
//         $whereClause = "WHERE 1 ";

//         if ($statusId !== 'all') {
//             $whereClause .= "AND is_running = '$statusId' ";
//         }

//         if ($name !== 'all') {
//             $whereClause .= "AND user_id = '$name' ";
//         }

//         if ($web !== 'all') {
//             $whereClause .= "AND crawler_id = '$web' ";
//         }
//         // if ($created_date !== '') {
//         //     $whereClause .= "AND DATE(instances_running.created) = '$created_date' ";
//         // }
//         $query = "SELECT users.title as username, crawlers.title, instances_running.*
//                       FROM instances_running
//                       JOIN users ON users.id = instances_running.user_id
//                       JOIN crawlers ON crawlers.id = instances_running.crawler_id
//                       $whereClause";
//         //   print_r($query);die;
//     }
//     $result = $db->Query($query);
//     $data = [];
//     $current_day = date('d-m-Y');
//     foreach ($result as $row) {
//         $formattedDate = date('Y-m-d', strtotime($row['created']));

//         $data[] = [
//             $row['username'],
//             $row['title'],
//             $row['products_done'],
//             $row['row_limit'],
//             $row['products_done'],
//             $formattedDate, //$row['created'],// $current_day,
//             $row['status']
//         ];
//     }
//     header('Content-Type: application/json');
//     echo json_encode(['data' => $data]);
// }

if (isset($_GET['statusId']) && isset($_GET['name']) && isset($_GET['web'])) {
    $statusId = $_GET['statusId'];
    $name = $_GET['name'];
    $web = $_GET['web'];

    $whereClause = '';

    if ($statusId === 'all' && $name === 'all' && $web === 'all') {
        $whereClause = "WHERE 1";
        $query = "SELECT
        users.username AS username,
        crawlers.title AS crawler_title,
        user_crawlers.*
        FROM
        user_crawlers
        JOIN
        users ON users.id = user_crawlers.user_id
        JOIN
        crawlers ON crawlers.id = user_crawlers.crawler_id
        WHERE
        user_crawlers.crawler_id = 4
        AND users.id IN (
            SELECT id
            FROM users
            WHERE pid = 0 AND disabled = 0 AND id NOT IN (6, 118, 371, 150)
            AND users.username NOT LIKE '%DEMO%' AND users.username NOT LIKE '%TEST%'
        )";
        $instance_query = "SELECT
        users.id AS user_id,
        users.username AS username,
        crawlers.id AS crawler_id,
        crawlers.title AS crawler_title,
        amazon_mysamm_8_instances.*,
        SUM(amazon_mysamm_8_instances.instance_limit)  AS total_urls,
        SUM(amazon_mysamm_8_instances.product_done) AS total_products,
        SUM(amazon_mysamm_8_instances.product_done) AS total_urls_crawled,
        SUM(amazon_mysamm_8_instances.instances) AS total_instances,
        MAX(amazon_mysamm_8_instances.instances) AS t_instance,
        SUM(CASE WHEN amazon_mysamm_8_instances.status = 'completed' THEN 1 ELSE 0 END) AS total_completed_instances,
        SUM(CASE WHEN amazon_mysamm_8_instances.status = 'running' THEN 1 ELSE 0 END) AS total_running_instances,
        SUM(CASE WHEN amazon_mysamm_8_instances.status = 'break' THEN 1 ELSE 0 END) AS total_break_instances,
        MAX(amazon_mysamm_8_instances.crawling_date) AS latest_crawl_start_date
        FROM
        amazon_mysamm_8_instances
        JOIN users ON users.id = amazon_mysamm_8_instances.user_id
        JOIN crawlers ON crawlers.id = amazon_mysamm_8_instances.crawler_id
        WHERE
        users.username NOT LIKE '%DEMO%'
        AND users.username NOT LIKE '%TEST%'
        GROUP BY
        users.id,
        users.username,
        crawlers.id,
        crawlers.title";
        $instance_query_result = $db->Query($instance_query);

        $instance_total_urls_data = array();
        $instance_instance_limit_data = array();
        $instance_total_urls_crawled_data = array();
        $instance_t_instance_data = array();
        $instance_total_break_instances_data = array();
        $instance_total_completed = array();
        $crawler_date = array();
        foreach ($instance_query_result as $status) {
            $key = $status['user_id'] . "**" . $status['crawler_id'];
            $instance_total_urls_data[$key] = $status['total_urls'];
            $instance_instance_limit_data[$key] = $status['instance_limit'];
            $instance_total_urls_crawled_data[$key] = $status['total_urls_crawled'];
            $instance_t_instance_data[$key] = $status['t_instance'];
            $instance_total_break_instances_data[$key] = $status['total_break_instances'];
            $instance_total_completed[$key] = $status['total_completed_instances'];
            $instance_total_running[$key] = $status['total_running_instances'];
            $crawler_date[$key] = $status['latest_crawl_start_date'];
        }
        $result = $db->Query($query);


        foreach ($result as $row) {
            $status_key = $row['user_id'] . "**" . $row['crawler_id'];
            $total_url__value = isset($instance_total_urls_data[$status_key]) ? $instance_total_urls_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
            $instance_limit__value = isset($instance_instance_limit_data[$status_key]) ? $instance_instance_limit_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
            $total_urls_crawled__value = isset($instance_total_urls_crawled_data[$status_key]) ? $instance_total_urls_crawled_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
            $t_instance_value = isset($instance_t_instance_data[$status_key]) ? $instance_t_instance_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
            // $action_button = ($row['total_break_instances'] > 0) ?
            //                 "<button class='mehroon-button' style='font-size: 12px; padding: 8px 7px 9px;white-space: nowrap;overflow: hidden;text-overflow: ellipsis;' type='button' onclick='buttonbreakClicked(\"{$row['crawler_id']}\", \"{$row['user_id']}\")'>Resume Crawler for broken instances</button>" :
            //                 "<button class='Teal-button' style='font-size: 12px; padding: 8px 7px 9px;white-space: nowrap;overflow: hidden;text-overflow: ellipsis;' type='button' '>Active</button>";
            $total_break_values = isset($instance_total_break_instances_data[$status_key]) ?
                $instance_total_break_instances_data[$status_key] :
                '0';
            $total_break_values = isset($instance_total_break_instances_data[$status_key]) ?
                $instance_total_break_instances_data[$status_key] : '0';

            $total_completed_values = isset($instance_total_completed[$status_key]) ?
                $instance_total_completed[$status_key] : '0';

            $total_running_values = isset($instance_total_running[$status_key]) ?
                $instance_total_running[$status_key] : '0';
            $crawling_date = isset($crawler_date[$status_key]) ?
                $crawler_date[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
            if ($t_instance_value === '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>') {
                $action_button = '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $step1Value_1 = '';
            } else {
                $action_button = ($total_break_values > 0) ?
                    "<button class='mehroon-button' style='font-size: 12px; padding: 8px 7px 9px;white-space: nowrap;overflow: hidden;text-overflow: ellipsis;' type='button' onclick='buttonbreakClicked(\"{$row['crawler_id']}\", \"{$row['user_id']}\")'>Resume Crawler for broken instances</button>" :
                    "<button class='Teal-button' style='font-size: 12px; padding: 8px 7px 9px;white-space: nowrap;overflow: hidden;text-overflow: ellipsis;' type='button'>Active</button>";
            }
            if ($total_completed_values > 0) {
                $row['status'] = 'completed';
                $step1Value_1 = $row['status'];
                $completed_button =  "<button class='modal-button transparent-button' onclick='openModal(\"{$row['crawler_id']}\", \"{$row['user_id']}\", \"{$row['status']}\")'>" . $instance_total_completed[$status_key] . "</button>";
            } else {
                $completed_button = "0";
            }
            if ($total_running_values > 0) {
                $row['status'] = 'running';
                $step1Value_1 = $row['status'];
                $running_button =  "<button class='modal-button transparent-button' onclick='openModal(\"{$row['crawler_id']}\", \"{$row['user_id']}\", \"{$row['status']}\")'>"  . $instance_total_running[$status_key] . "</button>";
            } else {
                $running_button = "0";
            }
            if ($total_break_values > 0) {
                $row['status'] = 'break';
                $step1Value_1 = $row['status'];
                $break_button =  "<button class='modal-button transparent-button' onclick='openModal(\"{$row['crawler_id']}\", \"{$row['user_id']}\", \"{$row['status']}\")'>"  . $total_break_values . "</button>";
            } else {
                $break_button = "0";
            }
            //   $status = $row['status']; === 'completed' ? 'completed' : 'running';

            $data[] = [

                $row['crawler_id'],
                $row['username'],
                $row['crawler_title'],
                $total_url__value,
                $instance_limit__value,
                $total_urls_crawled__value,
                $t_instance_value,
                $completed_button,
                $running_button,
                $break_button,
                $crawling_date,
                $action_button,
                $step1Value_1,
                'completed'
            ];
        }
    } else {
        $whereClause = "WHERE 1 ";

        if ($statusId !== 'all') {
            $whereClause .= "AND status = '$statusId' ";
        }

        if ($name !== 'all') {
            $whereClause .= "AND user_id = '$name' ";
        }

        if ($web !== 'all') {
            $whereClause .= "AND crawler_id = '$web' ";
        }
        $query = "SELECT
                    users.id AS user_id,
                    users.username AS username,
                    crawlers.id AS crawler_id,
                    crawlers.title AS crawler_title,
                    amazon_mysamm_8_instances.*,
                    SUM(amazon_mysamm_8_instances.instance_limit) AS total_urls,
                    SUM(amazon_mysamm_8_instances.product_done) AS total_products,
                    SUM(amazon_mysamm_8_instances.product_done) AS total_urls_crawled,
                    SUM(amazon_mysamm_8_instances.instances) AS total_instances,
                    MAX(amazon_mysamm_8_instances.instances) AS t_instance,
                    SUM(CASE WHEN amazon_mysamm_8_instances.status = 'completed' THEN 1 ELSE 0 END) AS total_completed_instances,
                    SUM(CASE WHEN amazon_mysamm_8_instances.status = 'running' THEN 1 ELSE 0 END) AS total_running_instances,
                    SUM(CASE WHEN amazon_mysamm_8_instances.status = 'break' THEN 1 ELSE 0 END) AS total_break_instances,
                    MAX(amazon_mysamm_8_instances.crawling_date) AS latest_crawl_start_date
                
                FROM
                    amazon_mysamm_8_instances
                JOIN users ON users.id = amazon_mysamm_8_instances.user_id
                JOIN crawlers ON crawlers.id = amazon_mysamm_8_instances.crawler_id
                
                AND users.username NOT LIKE '%DEMO%'
                AND users.username NOT LIKE '%TEST%'
                GROUP BY
                    users.id,
                    users.username,
                    crawlers.id,
                    crawlers.title;";
        $result = $dbconn->Query($query);
        print_r($query);
        echo "<br>==================================================<br>";
        print_r($result);
        $result = $db->Query($query);
        $data = [];

        foreach ($result as $row) {
            $action_button = ($row['total_break_instances'] > 0) ?
                "<button class='mehroon-button' style='font-size: 12px; padding: 8px 7px 9px;white-space: nowrap;overflow: hidden;text-overflow: ellipsis;' type='button' onclick='buttonbreakClicked(\"{$row['crawler_id']}\", \"{$row['user_id']}\")'>Resume Crawler for broken instances</button>" :
                "<button class='Teal-button' style='font-size: 12px; padding: 8px 7px 9px;white-space: nowrap;overflow: hidden;text-overflow: ellipsis;' type='button' '>Active</button>";
            if ($row['total_completed_instances'] > 0) {
                $row['status'] = 'completed';
                $status = $row['status'];
                $completed_button =  "<button class='modal-button transparent-button' onclick='openModal(\"{$row['crawler_id']}\", \"{$row['user_id']}\", \"{$row['status']}\")'>" . $row['total_completed_instances'] . "</button>";
            } else {
                $completed_button = "0";
            }
            if ($row['total_running_instances'] > 0) {
                $row['status'] = 'running';
                $status = $row['status'];
                $running_button =  "<button class='modal-button transparent-button' onclick='openModal(\"{$row['crawler_id']}\", \"{$row['user_id']}\", \"{$row['status']}\")'>"  . $row['total_running_instances'] . "</button>";
            } else {
                $running_button = "0";
            }
            if ($row['total_break_instances'] > 0) {
                $row['status'] = 'break';
                $status = $row['status'];
                $break_button =  "<button class='modal-button transparent-button' onclick='openModal(\"{$row['crawler_id']}\", \"{$row['user_id']}\", \"{$row['status']}\")'>"  . $row['total_break_instances'] . "</button>";
            } else {
                $break_button = "0";
            }
            $status = '';

            // if ($row['total_completed_instances'] > 0 && $row['total_running_instances'] === 0 && $row['total_break_instances'] === 0) {
            //     $status = 'completed';
            // } elseif ($row['total_running_instances'] > 0 && $row['total_break_instances'] === 0) {
            //     $status = 'running';
            // } elseif ($row['total_break_instances'] > 0) {
            //     $status = 'break';
            // }
            //   $status = $row['status']; === 'completed' ? 'completed' : 'running';

            $data[] = [

                $row['crawler_id'],
                $row['username'],
                $row['crawler_title'],
                $row['total_urls'],
                $row['instance_limit'],
                $row['total_urls_crawled'],
                $row['t_instance'],
                $completed_button,
                $running_button,
                $break_button,
                $row['latest_crawl_start_date'],
                $action_button,
                $row['status'],
                'completed'
            ];
        }
    }

    header('Content-Type: application/json');
    echo json_encode(['data' => $data]);
}


if (isset($_POST['value']) && isset($_POST['id']) && isset($_POST['client_id'])) {
    $value = $_POST['value'];
    $id = $_POST['id'];
    $clientID = $_POST['client_id'];
    $query = "UPDATE manage_data_status SET `status` = 'Ready to manage' WHERE `user_id` = '$clientID' AND website_id='$id' ";
    $result = $db->execute($query);
    if ($result) {
        echo "Update successful";
        $client_query = "   UPDATE client_website_filter_status 
                                SET step_1 = '', step_2 = '', step_3 = '', step_4 = '', 
                                    step_5 = '', step_6 = '', step_7 = '', step_8 = ''
                                WHERE user_id = '$clientID' AND website_id = '$id'";
        $result = $dbui->execute($client_query);
        if ($result) {
            echo "Row with ID $id has been updated with NULL values for steps 1 to 8.";
        }
    } else {
        echo "Update failed";
    }
}

function generateStepButton($value)
{
    if ($value === 'Completed') {
        return "<button class='green-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'Started') {
        return "<button class='primary-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'break' || $value === 'Break') {
        return "<button class='break-button' style='font-size: 12px; background-color: red;color: #fff;padding: 5px 10px;border: none;border-radius: 4px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords($value) . "</button>";
    } elseif ($value === '2') {
        return "<button class='green-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords('completed') . "</button>";
    } elseif ($value === '1') {
        return "<button class='primary-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords('Started') . "</button>";
    } elseif ($value === '0') {
        return "<button class='mehroon-button' style='font-size: 12px; background-color: mehroon;color: #fff;padding: 5px 10px;border: none;border-radius: 4px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords('Not Started') . "</button>";
    } elseif (empty($value) || is_null($value)) {
        return "<button class='mehroon-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"-\")'>-</button>";
    } else {
        return ucwords($value);
    }
}

function generateqcButton($value)
{
    if ($value === '1') {
        return "<button class='green-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords('Completed') . "</button>";
    } elseif ($value === '0') {
        return "<button class='mehroon-button' style='font-size: 12px; background-color: mehroon;color: #fff;padding: 5px 10px;border: none;border-radius: 4px;' type='submit' onclick='buttonClicked(\"$value\")'>" . ucwords('Running') . "</button>";
    } elseif (empty($value) || is_null($value)) {
        return "<button class='mehroon-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"-\")'>-</button>";
    } else {
        return ucwords($value);
    }
}

function generateManageButton($value, $websiteId, $client_id)
{
    if ($value === 'Waiting') {
        return "<button class='yellow-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\", \"$websiteId\", \"$client_id\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'Manage Completed') {
        return "<button id='manageButton-$websiteId-$client_id' class='Teal-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\", \"$websiteId\", \"$client_id\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'Filter Completed') {
        return "<button id='manageButton-$websiteId-$client_id' class='green-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\", \"$websiteId\", \"$client_id\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'Filter Running') {
        return "<button id='manageButton-$websiteId-$client_id' class='on-process-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\", \"$websiteId\", \"$client_id\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'Ready to manage') {
        return "<button id='manageButton-$websiteId' class='ready-button' style='font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\", \"$websiteId\", \"$client_id\")'>" . ucwords($value) . "</button>";
    } elseif ($value === 'Manage Started') {
        return "<button id='manageButton-$websiteId-$client_id' class='manage_started-button' style='background-color: #B39DDB;color: white;padding: 5px 10px;border: none;border-radius: 4px;font-size: 12px;' type='submit' onclick='buttonClicked(\"$value\", \"$websiteId\", \"$client_id\")'>" . ucwords($value) . "</button>";
    } else {
        return ucwords($value);
    }
}

if (isset($_GET['stepId']) && isset($_GET['name']) && isset($_GET['web']) && isset($_GET['qc'])) {
    $m_status = isset($_GET['stepId']) ? $_GET['stepId'] : null;
    $name = isset($_GET['name']) ? $_GET['name'] : null;
    $web = isset($_GET['web']) ? $_GET['web'] : null;
    $qc = isset($_GET['qc']) ? $_GET['qc'] : null;
    $whereClause = '';
    $stepClause = '';
    $manage_Clause = '';
    $date = date("d-m-Y");
    if ($m_status === 'all' && $name === 'all' && $web === 'all' && $qc === 'all') {
        $query = "SELECT users.title as username, crawlers.title as web, user_crawlers.*
                      FROM user_crawlers
                      JOIN users ON users.id = user_crawlers.user_id
                      LEFT JOIN crawlers ON crawlers.id = user_crawlers.crawler_id
                      WHERE `user_id` IN (
                    SELECT id FROM users WHERE pid = 0 AND disabled = 0 AND id NOT IN (6, 118, 371, 150)
                ) AND users.username NOT LIKE '%DEMO%' AND users.username NOT LIKE '%TEST%'";
        $Client_web_addition = "SELECT id,user_id,website_id,step_1,step_2,step_3,step_4,step_5,step_6,step_7,step_8,created,fix_mycontent_report,cm_qc FROM client_website_filter_status";
        $manage_status = "SELECT id,status,user_id,website_id FROM manage_data_status";
    } else {
        $stepClause = 'WHERE 1=1';
        $manage_Clause = 'WHERE 1=1';
        if ($m_status == '4') {
            $manage_Clause .= " AND (`status` = 'Manage Completed')";
        } elseif ($m_status == '3') {
            $manage_Clause .= " AND (`status` = 'Filter Completed')";
        } elseif ($m_status == '2') {
            $manage_Clause .= " AND (`status` = 'Filter Running')";
        } elseif ($m_status == '1') {
            $manage_Clause .= " AND (`status` = 'Ready to manage')";
        } elseif ($m_status == '0') {
            $manage_Clause .= " AND (`status` = 'Manage Started')";
        } elseif ($m_status == '5') {
            $manage_Clause .= " AND (`status` = 'Waiting')";
        }

        if ($qc !== 'all') {
            $stepClause .= " AND cm_qc = '$qc'";
        }
        if ($name !== 'all') {
            $whereClause .= " AND user_id = '$name'";
            $stepClause .= " AND user_id = '$name' ";
            $manage_Clause .= " AND user_id = '$name'";
        }
        if ($web !== 'all') {
            $whereClause .= " AND crawlers.id = '$web'";
            $stepClause .= " AND website_id = '$web' ";
            $manage_Clause .= " AND website_id = '$web'";
        }
        $query = "SELECT users.username as username, crawlers.title as web, user_crawlers.*
                  FROM user_crawlers
                  JOIN users ON users.id = user_crawlers.user_id
                  LEFT JOIN crawlers ON crawlers.id = user_crawlers.crawler_id
                  WHERE user_id IN (
                      SELECT id FROM users WHERE pid = 0 AND disabled = 0 AND id NOT IN (6, 118, 371, 150)
                  ) AND users.username NOT LIKE '%DEMO%' AND users.username NOT LIKE '%TEST%'
                  $whereClause";
        $Client_web_addition = "SELECT id,user_id,website_id, step_1, step_2, step_3, step_4, step_5, step_6, step_7, step_8, `fix_mycontent_report`, `cm_qc` FROM client_website_filter_status $stepClause";
        $manage_status = "SELECT id,`status`,user_id,website_id FROM manage_data_status $manage_Clause";
    }
    $client_result = $dbui->Query($Client_web_addition);
    $manage_result = $db->Query($manage_status);
    $data = [];
    $stepButtons = [];
    $date = date("d-m-Y");
    if ($client_result || $manage_result) {
        $manage_status_data = array();
        foreach ($manage_result as $status) {
            $key = $status['user_id'] . "**" . $status['website_id'];
            $manage_status_data[$key] = $status['status'];
        }
        $cm_qc_data = array();
        $client_website_filter_status_steps = array();
        $client_website_filter_fix_mycontent_report = array();
        if (is_array($client_result)) {
            foreach ($client_result as $newweb) {
                $key = $newweb['user_id'] . "**" . $newweb['website_id'];
                $client_website_filter_status_steps[$key] = $newweb;
                $client_website_filter_fix_mycontent_report[$key] = $newweb['fix_mycontent_report'];
                $cm_qc_data[$key] = $newweb['cm_qc'];
            }
        }
        // foreach ($client_result as $newweb) {
        //     $key = $newweb['user_id'] . "**" . $newweb['website_id'];
        //     $client_website_filter_status_steps[$key] = $newweb;
        //     $client_website_filter_fix_mycontent_report[$key] = $newweb['fix_mycontent_report'];
        //     $cm_qc_data[$key] = $newweb['cm_qc'];
        // }
        if ($m_status === 'all' && $qc === 'all') {
            $step = ['step_1', 'step_2', 'step_3', 'step_4', 'step_5', 'step_6', 'step_7', 'step_8'];
            $main_result = $dbui->Query($query);
            foreach ($main_result as $row) {
                $status_key = $row['user_id'] . "**" . $row['crawler_id'];
                $manage_status_value = isset($manage_status_data[$status_key]) ? $manage_status_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $manageButton = generateManageButton($manage_status_value, $row['crawler_id'], $row['user_id']);
                $fix_content =  isset($client_website_filter_fix_mycontent_report[$status_key]) ? $client_website_filter_fix_mycontent_report[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $fix_content_button = generateStepButton($fix_content);
                $cm_qc_values = isset($cm_qc_data[$status_key]) ? $cm_qc_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $cm_qc_button = generateqcButton($cm_qc_values);
                if (is_array($step)) {
                    foreach ($step as $step_heading) {
                        $stepValues = isset($client_website_filter_status_steps[$status_key][$step_heading]) ? $client_website_filter_status_steps[$status_key][$step_heading] : '';
                        $stepButtons[] = generateStepButton($stepValues);
                    }
                }
                $data[] = [
                    isset($row['username']) ? $row['username'] : '',
                    isset($row['web']) ? $row['web'] : '',
                    $manageButton,
                    $stepButtons[0],
                    $stepButtons[1],
                    $stepButtons[2],
                    $stepButtons[3],
                    $stepButtons[4],
                    $stepButtons[5],
                    $stepButtons[6],
                    $stepButtons[7],
                    $fix_content_button,
                    $cm_qc_button,
                    $date
                ];
                $stepButtons = [];
            }
        } else {
            $manage_status_data = array();
            foreach ($manage_result as $status) {
                $key = $status['user_id'] . "**" . $status['website_id'];
                $manage_status_data[$key] = $status['status'];
            }
            $cm_qc_data = array();
            $client_website_filter_status_steps = array();
            $client_website_filter_fix_mycontent_report = array();
            if (is_array($client_result)) {
                foreach ($client_result as $newweb) {
                    $key = $newweb['user_id'] . "**" . $newweb['website_id'];
                    $client_website_filter_status_steps[$key] = $newweb;
                    $client_website_filter_fix_mycontent_report[$key] = $newweb['fix_mycontent_report'];
                    $cm_qc_data[$key] = $newweb['cm_qc'];
                }
            }
            // foreach ($client_result as $newweb) {
            //     $key = $newweb['user_id'] . "**" . $newweb['website_id'];
            //     $client_website_filter_status_steps[$key] = $newweb;
            //     $client_website_filter_fix_mycontent_report[$key] = $newweb['fix_mycontent_report'];
            //     $cm_qc_data[$key] = $newweb['cm_qc'];
            // }
            $step = ['step_1', 'step_2', 'step_3', 'step_4', 'step_5', 'step_6', 'step_7', 'step_8'];
            $main_result = $dbui->Query($query);
            foreach ($main_result as $row) {
                $status_key = $row['user_id'] . "**" . $row['crawler_id'];
                if ($m_status !== 'all' && !isset($manage_status_data[$status_key]) ||  $qc !== 'all' && !isset($cm_qc_data[$status_key])) {
                    continue; // Skip to the next iteration
                }
                $manage_status_value = isset($manage_status_data[$status_key]) ? $manage_status_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $manageButton = generateManageButton($manage_status_value, $row['crawler_id'], $row['user_id']);
                $fix_content =  isset($client_website_filter_fix_mycontent_report[$status_key]) ? $client_website_filter_fix_mycontent_report[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $fix_content_button = generateStepButton($fix_content);
                $cm_qc_values = isset($cm_qc_data[$status_key]) ? $cm_qc_data[$status_key] : '<button class="mehroon-button" style="font-size: 12px;" type="submit">-</button>';
                $cm_qc_button = generateqcButton($cm_qc_values);
                if (is_array($step)) {
                    foreach ($step as $step_heading) {
                        $stepValues = isset($client_website_filter_status_steps[$status_key][$step_heading]) ? $client_website_filter_status_steps[$status_key][$step_heading] : '';
                        $stepButtons[] = generateStepButton($stepValues);
                    }
                }
                $data[] = [
                    isset($row['username']) ? $row['username'] : '',
                    isset($row['web']) ? $row['web'] : '',
                    $manageButton,
                    $stepButtons[0],
                    $stepButtons[1],
                    $stepButtons[2],
                    $stepButtons[3],
                    $stepButtons[4],
                    $stepButtons[5],
                    $stepButtons[6],
                    $stepButtons[7],
                    $fix_content_button,
                    $cm_qc_button,
                    $date
                ];
                $stepButtons = [];
            }
        }
    }

    header('Content-Type: application/json');
    echo json_encode(['data' => $data]);
}

if (isset($_POST['tableData'])) {
    $tableData = json_decode($_POST['tableData'], true);
    $spreadsheet = new Spreadsheet();
    $min = 1; // Minimum value
    $max = 100; // Maximum value
    $rand = rand($min, $max);
    $sheet = $spreadsheet->getActiveSheet();
    $headerRow = array_shift($tableData);
    $sheet->fromArray([$headerRow], NULL, 'A1');
    $headerStyle = [
        'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '343a40']],
        'borders' => ['bottom' => ['style' => Border::BORDER_THIN, 'color' => ['rgb' => '000000']]]
    ];
    // $sheet->getStyle('A1:' . $sheet->getHighestColumn() . '1')->applyFromArray($headerStyle);
    $headerRange = 'A1:' . $sheet->getHighestColumn() . '1';
    $sheet->getStyle($headerRange)->applyFromArray($headerStyle);
    $sheet->fromArray($tableData, NULL, 'A2');
    $writer = new Xlsx($spreadsheet);
    $filename = 'Filter_Status_' . date('Y-m-d') . $rand . '.xlsx';
    $writer->save($filename);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');

    exit;
}


if (isset($_GET['crawlerId']) && isset($_GET['userId']) && isset($_GET['crawler_status'])) {
    $crawlerId = $_GET['crawlerId'];
    $userId = $_GET['userId'];
    $crawler_status = $_GET['crawler_status'];
    // $query = "SELECT * FROM instances_running WHERE crawler_id = $crawlerId AND user_id = $userId AND `status` = '$crawler_status';
    // ";
    $query =  "SELECT amazon_mysamm_8_instances.*, users.*, crawlers.*
    
    FROM amazon_mysamm_8_instances
 
    JOIN users ON users.id = amazon_mysamm_8_instances.user_id
 
    JOIN crawlers ON crawlers.id = amazon_mysamm_8_instances.crawler_id
 
    WHERE amazon_mysamm_8_instances.crawler_id = $crawlerId
 
    AND amazon_mysamm_8_instances.user_id = $userId
 
    AND amazon_mysamm_8_instances.status = '$crawler_status'
 
    AND users.username NOT LIKE '%DEMO%'
 
    AND users.username NOT LIKE '%TEST%';
 
  ";


    // "SELECT instances_running.*, users.*, crawlers.*
    //       FROM instances_running
    //       JOIN users ON users.id = instances_running.user_id
    //       JOIN crawlers ON crawlers.id = instances_running.crawler_id
    //       WHERE instances_running.crawler_id = $crawlerId
    //       AND instances_running.user_id = $userId
    //       AND instances_running.status = '$crawler_status'
    //       AND users.username NOT LIKE '%DEMO%'
    //       AND users.username NOT LIKE '%TEST%';
    //     ";
    $result = $db->Query($query);
    $data = [];
    foreach ($result as $row) {
        $formattedDate = date('Y-m-d', strtotime($row['created']));

        $data[] = [
            $row['username'],
            $row['title'],
            $row['instances'],
            // $row['instances_limit'],
            // $row['products_done'],
            // $row['crawling_date'], //$row['created'],// $current_day,
            $row['status']
        ];
    }
    echo json_encode($data);
}
if (isset($_GET['breakcrawlerId']) && isset($_GET['breakuserId'])) {
    $userId = $_GET['breakuserId'];
    $crawler_id = $_GET['breakcrawlerId'];
    $query = "UPDATE amazon_mysamm_8_instances SET `status` = 'running' WHERE `user_id` = '$userId' AND crawler_id='$crawler_id' AND `status` = 'break'";
    $result = $db->execute($query);

    if ($result) {
        $successMessage = "Update successful";
        $response = array(
            'success' => true,
            'message' => $successMessage,
            'interval' => 2000  // Set your desired interval time in milliseconds
        );
        echo json_encode($response);
    }
}
