<?php
session_start();

include('config.php');

try {
    $connection = new mysqli($hostname, $username, $password, $database);

    if ($connection->connect_error) {
        throw new mysqli_sql_exception("Error: " . $connection->connect_error);
    }
} catch (mysqli_sql_exception $e) {
    exit($e->getMessage());
}

$Username = $_SESSION['Username'];

// Fetch user records based on their Username
function fetchUserRecords($connection, $Username) {
    $stmt = $connection->prepare('SELECT * FROM business_records WHERE Username = ?');
    $stmt->bind_param('s', $Username);
    $stmt->execute();
    return $stmt->get_result();
}

$result = fetchUserRecords($connection, $Username);

// Fetch user records based on their Username and group by category
function fetchUserRecordsByCategory($connection, $Username) {
    $stmt = $connection->prepare('SELECT category.categoryname AS categoryname, 
        GROUP_CONCAT(inventory.Product) AS ProductNames, 
        SUM(inventory.InventoryValue) AS InventoryValue, 
        SUM(inventory.InventoryCost) AS TotalInventoryCost, 
        SUM(inventory.SupplyValue) AS TotalSupplyValue, 
        SUM(inventory.SupplyQty) AS TotalSupplyQty, 
        SUM(inventory.InventoryQty) AS TotalInventoryQty, 
        SUM(inventory.StockQty) AS TotalStockQty, 
        SUM(inventory.StockValue) AS TotalStockValue,
        SUM(inventory.YearlySalesQty) AS TotalYearlySalesQty,
        (SUM(InventoryQty) - SUM(YearlysalesQty)) AS AvailableStock
        FROM inventory
        INNER JOIN category ON inventory.category_id = category.id_category
        WHERE inventory.Username = ?
        GROUP BY categoryname');

    $stmt->bind_param('s', $Username);
    $stmt->execute();
    return $stmt->get_result();
}

$resultCategories = fetchUserRecordsByCategory($connection, $Username);

// Fetch data from inventory table
function fetchSalesData($connection, $Username, $field, $alias) {
    $stmt = $connection->prepare("SELECT $field, $alias FROM inventory WHERE Username = ?");
    $stmt->bind_param('s', $Username);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $data[$field][] = $row[$field];
            $data[$alias][] = $row[$alias];
        }
    } else {
        throw new mysqli_sql_exception("Error fetching $field data: " . $connection->error);
    }
}

// Fetch monthly profit data
fetchSalesData($connection, $Username, 'Month', 'InventoryQty');

// Fetch monthly revenue data
fetchSalesData($connection, $Username, 'Month', 'SupplyQty');

// Fetch monthly expenses data
fetchSalesData($connection, $Username, 'Month', 'StockQty');

// Fetch data for inventory metrics with category information
function fetchMetricsData($connection, $Username) {
    $stmt = $connection->prepare('
        SELECT inventory.*, category.categoryname
        FROM inventory
        JOIN category ON inventory.category_id = category.id_category
        WHERE inventory.Username = ?
    ');
    $stmt->bind_param('s', $Username);
    $stmt->execute();
    return $stmt->get_result();
}

$resultMetrics = fetchMetricsData($connection, $Username);

// Fetch category names and IDs
function fetchCategoryNames($connection) {
    $stmt = $connection->prepare('SELECT id_category, categoryname FROM category');
    $stmt->execute();
    $result = $stmt->get_result();

    $categoryNames = array();
    while ($row = $result->fetch_assoc()) {
        $categoryNames[$row['id_category']] = $row['categoryname'];
    }

    return $categoryNames;
}

$categoryNames = fetchCategoryNames($connection);

// Fetch and calculate totals
function fetchAndCalculateTotals($connection, $Username) {
    $stmt = $connection->prepare('SELECT COUNT(*) AS TotalProduct, 
                                          SUM(InventoryCost) AS TotalInventoryCost,
                                          SUM(InventoryValue) AS InventoryValue,
                                          SUM(SupplyValue) AS TotalSupplyValue,
                                          SUM(StockValue) AS TotalStockValue,
                                          (SUM(InventoryQty) - SUM(YearlySalesQty)) AS AvailableStock,
                                          SUM(YearlySalesQty) AS TotalYearlySalesQty
                                   FROM inventory 
                                   WHERE Username = ?');
    $stmt->bind_param('s', $Username);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        return [
            'TotalProduct' => $row['TotalProduct'],
            'TotalSupplyValue' => $row['TotalSupplyValue'],
            'InventoryValue' => $row['InventoryValue'],
            'TotalInventoryCost' => $row['TotalInventoryCost'],
            'TotalStockvalue' => $row['TotalStockValue'],
            'TotalYearlySalesQty' => $row['TotalYearlySalesQty']
        ];
    } else {
        return [
            'TotalProduct' => 0,
            'TotalSupplyValue' => 0,
            'InventoryValue' => 0,
            'TotalStockValue' => 0,
            'TotalYearlySalesQty' => 0,
            'TotalInventoryCost' => 0
        ];
    }
}

$totalData = fetchAndCalculateTotals($connection, $Username);

// Fetch data for total inventory value
function fetchInventoryData($connection, $interval) {
    // Implement logic to fetch inventory data based on the provided interval (day, week, month, year)
}

// Fetch data for revenue, cost, profit, and loss
function fetchFinancialData($connection, $interval) {
    // Implement logic to fetch financial data based on the provided interval (day, week, month, year)
}

// Display chart data
function displayChartData($title, $data) {
    // Display chart using the provided title and data
}

// Fetch and display data for total inventory value
$inventoryDayData = fetchInventoryData($connection, 'day');
displayChartData('Total Inventory Value (Day)', $inventoryDayData);

$inventoryWeekData = fetchInventoryData($connection, 'week');
displayChartData('Total Inventory Value (Week)', $inventoryWeekData);

$inventoryMonthData = fetchInventoryData($connection, 'month');
displayChartData('Total Inventory Value (Month)', $inventoryMonthData);

$inventoryYearData = fetchInventoryData($connection, 'year');
displayChartData('Total Inventory Value (Year)', $inventoryYearData);

// Fetch and display data for revenue, cost, profit, and loss
$financialDayData = fetchFinancialData($connection, 'day');
displayChartData('Financial Data (Day)', $financialDayData);

$financialWeekData = fetchFinancialData($connection, 'week');
displayChartData('Financial Data (Week)', $financialWeekData);

$financialMonthData = fetchFinancialData($connection, 'month');
displayChartData('Financial Data (Month)', $financialMonthData);

$financialYearData = fetchFinancialData($connection, 'year');
displayChartData('Financial Data (Year)', $financialYearData);

?>