<?php
define('_FEXEC', 1);
require('includes/application_top.php');

define('HEADING_TITLE', 'Section ID Checker');
define('HEADING_NOTE', 'Check for Section ID discrepancies');

// Handle fixing discrepancies
if (isset($_GET['fix'])) {
    $product_id = (int)$_GET['product_id'];
    $section_id = (int)$_GET['section_id'];

    if ($product_id > 0 && $section_id > 0) {
        // Update products_description.section_id to match products.section_id
        tep_db_query("
            UPDATE products_description
            SET section_id = $section_id
            WHERE products_id = $product_id
        ");

        // Redirect to avoid form resubmission
        tep_redirect(tep_href_link('section_check.php'));
    }
}

tep_get_last_access_file();
?>
<!doctype html>
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link href="assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
<link href="assets/vendor/bootstrap-icons/bootstrap-icons.css" rel="stylesheet">

<script language="javascript" src="includes/tweak/js/payment_general_templates.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script language="javascript" src="includes/http.js"></script>
<script type="text/javascript" src="includes/aim.js"></script>
<script type="text/javascript" src="includes/tweak/js/ajax.js"></script>

<script src="includes/javascript/jquery-3.6.0.min.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<section id="sections" class="sections">
<div class="container">
    <h1><?php echo HEADING_TITLE; ?></h1>
    <p><?php echo HEADING_NOTE; ?></p>
    <?php
    // Fetch all categories and their section_id
    $categories_query = tep_db_query("SELECT categories_id, section_id FROM categories");
    $categories = [];
    while ($category = tep_db_fetch_array($categories_query)) {
        $categories[$category['categories_id']] = $category['section_id'];
    }

    // Fetch all products and their section_id from products_to_categories and products_description
    $products_query = tep_db_query("
        SELECT 
            p.products_id, 
            p.section_id AS product_section_id, 
            ptc.categories_id, 
            ptc.section_id AS ptc_section_id, 
            c.section_id AS category_section_id,
            pd.section_id AS pd_section_id
        FROM products p
        JOIN products_to_categories ptc ON p.products_id = ptc.products_id
        JOIN categories c ON ptc.categories_id = c.categories_id
        JOIN products_description pd ON p.products_id = pd.products_id
    ");

    $discrepancies = [];
    while ($product = tep_db_fetch_array($products_query)) {
        // Check if products_description.section_id matches products.section_id
        if ($product['pd_section_id'] != $product['product_section_id']) {
            $discrepancies[] = [
                'type' => 'PD vs Product',
                'product_id' => $product['products_id'],
                'product_section_id' => $product['product_section_id'],
                'pd_section_id' => $product['pd_section_id'],
            ];
        }
    }

    if (count($discrepancies) > 0) {
        echo '<div class="alert alert-danger">';
        echo '<h4>Discrepancies found:</h4>';
        echo '<table class="table table-bordered">';
        echo '<thead><tr><th>Type</th><th>Product ID</th><th>Product Section ID</th><th>PD Section ID</th><th>Action</th></tr></thead>';
        echo '<tbody>';
        foreach ($discrepancies as $discrepancy) {
            echo '<tr>';
            echo '<td>' . $discrepancy['type'] . '</td>';
            echo '<td>' . $discrepancy['product_id'] . '</td>';
            echo '<td>' . $discrepancy['product_section_id'] . '</td>';
            echo '<td>' . $discrepancy['pd_section_id'] . '</td>';
            echo '<td>';
            echo '<a href="' . tep_href_link('section_check.php', 'fix=1&product_id=' . $discrepancy['product_id'] . '&section_id=' . $discrepancy['product_section_id']) . '" class="btn btn-warning btn-sm">Fix</a>';
            echo '</td>';
            echo '</tr>';
        }
        echo '</tbody>';
        echo '</table>';
        echo '</div>';
    } else {
        echo '<div class="alert alert-success">No discrepancies found.</div>';
    }
    ?>
</div>
</section>

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<script src="assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
</body>
</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>