<?php
require_once('includes/load.php'); // Load your database connection and session management

// Function to clear old data from the chart_data table
function clearOldData($db) {
    $db->query("DELETE FROM chart_data WHERE created_at < NOW() - INTERVAL 1 HOUR");
}

// Function to populate chart_data table
function populateChartData($db) {
    // Clear old data
    clearOldData($db);

    // Get current date and time
    $currentDate = date('Y-m-d H:i:s');

    // Insert top 5 sales
    $queryTop5Sales = "SELECT sales.product_id, products.name AS product_name, SUM(qty) AS total_qty
                       FROM sales
                       JOIN products ON sales.product_id = products.id
                       WHERE sales.date >= NOW() - INTERVAL 1 HOUR
                       GROUP BY sales.product_id
                       ORDER BY total_qty DESC
                       LIMIT 5";

    $resultTop5Sales = $db->query($queryTop5Sales);
    while ($row = $resultTop5Sales->fetch_assoc()) {
        $db->query("INSERT INTO chart_data (product_id, product_name, total_qty, category_id, date, type) 
                    VALUES ({$row['product_id']}, '{$row['product_name']}', {$row['total_qty']}, 7, '{$currentDate}', 'sales')");
    }

    // Insert top 5 incoming goods
    $queryTop5Incoming = "SELECT ig.product_id, p.name AS product_name, SUM(ig.quantity) AS total_qty
                          FROM incoming_goods ig
                          JOIN products p ON ig.product_id = p.id
                          WHERE ig.arrival_date >= NOW() - INTERVAL 1 HOUR
                          GROUP BY ig.product_id
                          ORDER BY total_qty DESC
                          LIMIT 5";

    $resultTop5Incoming = $db->query($queryTop5Incoming);
    while ($row = $resultTop5Incoming->fetch_assoc()) {
        $db->query("INSERT INTO chart_data (product_id, product_name, total_qty, category_id, date, type) 
                    VALUES ({$row['product_id']}, '{$row['product_name']}', {$row['total_qty']}, NULL, '{$currentDate}', 'incoming')");
    }

    // Insert daily sales for the current date
    $queryDailySales = "SELECT sales.product_id, products.name AS product_name, SUM(qty) AS total_qty
                        FROM sales
                        JOIN products ON sales.product_id = products.id
                        WHERE DATE(sales.date) = CURDATE()
                        GROUP BY sales.product_id
                        ORDER BY total_qty DESC";

    $resultDailySales = $db->query($queryDailySales);
    while ($row = $resultDailySales->fetch_assoc()) {
        $db->query("INSERT INTO chart_data (product_id, product_name, total_qty, category_id, date, type) 
                    VALUES ({$row['product_id']}, '{$row['product_name']}', {$row['total_qty']}, NULL, '{$currentDate}', 'daily')");
    }

    // Insert top 5 stock
    $queryTop5Stock = "SELECT id AS product_id, name AS product_name, quantity AS total_qty 
                       FROM products 
                       ORDER BY quantity DESC 
                       LIMIT 5";

    $resultTop5Stock = $db->query($queryTop5Stock);
    while ($row = $resultTop5Stock->fetch_assoc()) {
        $db->query("INSERT INTO chart_data (product_id, product_name, total_qty, category_id, date, type) 
                    VALUES ({$row['product_id']}, '{$row['product_name']}', {$row['total_qty']}, NULL, '{$currentDate}', 'stock')");
    }

    // Insert top 5 outbound goods
    $queryTop5Outbound = "SELECT part_number AS product_id, SUM(quantity) AS total_qty
                          FROM awb
                          WHERE DATE(scan_status) = CURDATE()
                          GROUP BY part_number
                          ORDER BY total_qty DESC
                          LIMIT 5";

    $resultTop5Outbound = $db->query($queryTop5Outbound);
    while ($row = $resultTop5Outbound->fetch_assoc()) {
        $db->query("INSERT INTO chart_data (product_id, product_name, total_qty, category_id, date, type) 
                    VALUES ('{$row['product_id']}', '{$row['product_id']}', {$row['total_qty']}, NULL, '{$currentDate}', 'outbound')");
    }
}

// Call the function to populate the chart data
populateChartData($db);
?>
