<?php
require('config.php');

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Initialize the response structure with default values
$response = [
    'stock' => [
        'totalCount' => '0',
        'totalValue' => '0'
    ],
    'transactions' => [
        'Today' => ['cash' => ['count' => '0', 'amount' => '0'], 'credit' => ['count' => '0', 'amount' => '0']],
        'This Week' => ['cash' => ['count' => '0', 'amount' => '0'], 'credit' => ['count' => '0', 'amount' => '0']],
        'This Month' => ['cash' => ['count' => '0', 'amount' => '0'], 'credit' => ['count' => '0', 'amount' => '0']],
    ],
    'credits' => ['Fully Paid' => ['count' => '0', 'amount' => '0'], 'Unpaid' => ['count' => '0', 'amount' => '0']],
    'general cash transactions' => ['count' => '0', 'amount' => '0'],
    'error' => 'false',
    'message' => ''
];

try {
    // 1. Count all available stock
    $stockQuery = "SELECT COUNT(*) as totalStockCount, COALESCE(SUM(quantity * productPrice), 0) as totalStockValue FROM stock";
    $stockResult = $connect->query($stockQuery);

    // Check if the query was successful
    if ($stockResult === false) {
        throw new Exception("Error executing stock query: " . $connect->error);
    }

    $stockData = $stockResult->fetch_assoc();
    $response['stock'] = [
        'totalCount' => isset($stockData['totalStockCount']) ? (string)$stockData['totalStockCount'] : '0',
        'totalValue' => isset($stockData['totalStockValue']) ? (string)$stockData['totalStockValue'] : '0'
    ];

    // 2. Transaction counts and sums grouped by releaseDate categories
    $transactionsQuery = "
        SELECT 
            releaseType,
            COUNT(id) as transactionCount,
            COALESCE(SUM(amount), 0) as totalAmount,
            CASE 
                WHEN DATE(CONVERT_TZ(releaseDate, '+00:00', '+03:00')) = DATE(CURDATE()) THEN 'Today'
                WHEN WEEK(CONVERT_TZ(releaseDate, '+00:00', '+03:00')) = WEEK(CURDATE()) THEN 'This Week'
                WHEN MONTH(CONVERT_TZ(releaseDate, '+00:00', '+03:00')) = MONTH(CURDATE()) THEN 'This Month'
                ELSE 'Other'
            END as timeCategory
        FROM transactions
        GROUP BY timeCategory, releaseType
    ";
    $transactionsResult = $connect->query($transactionsQuery);

    // Check if the query was successful
    if ($transactionsResult === false) {
        throw new Exception("Error executing transactions query: " . $connect->error);
    }

    while ($row = $transactionsResult->fetch_assoc()) {
        $category = $row['timeCategory'];
        $type = strtolower($row['releaseType']); // cash or credit
        $response['transactions'][$category][$type] = [
            'count' => isset($row['transactionCount']) ? (string)$row['transactionCount'] : '0',
            'amount' => isset($row['totalAmount']) ? (string)$row['totalAmount'] : '0'
        ];
    }

    // 3. Calculate all cash transactions and credits grouped by credit status
    $creditsQuery = "
       SELECT 
            CASE 
                WHEN credits.status = 'Fully Paid' THEN 'Fully Paid'
                ELSE 'Unpaid'
            END AS creditStatus,
            COUNT(credits.id) AS totalCount,
            COALESCE(SUM(credits.quantity * stock.productPrice), 0) AS totalAmount
       FROM 
            credits
       JOIN 
            stock ON credits.product = stock.productID
       GROUP BY 
            creditStatus
    ";
    $creditsResult = $connect->query($creditsQuery);

    // Check if the query was successful
    if ($creditsResult === false) {
        throw new Exception("Error executing credits query: " . $connect->error);
    }

    while ($row = $creditsResult->fetch_assoc()) {
        $status = $row['creditStatus']; // Fully Paid or Unpaid
        $response['credits'][$status] = [
            'count' => isset($row['totalCount']) ? (string)$row['totalCount'] : '0',
            'amount' => isset($row['totalAmount']) ? (string)$row['totalAmount'] : '0'
        ];
    }

    // 4. General cash transactions (releaseType = 'Cash')
    $cashTransactionsQuery = "
        SELECT 
            COUNT(id) as transactionCount,
            COALESCE(SUM(amount), 0) as totalAmount
        FROM transactions
        WHERE releaseType = 'Cash'
    ";
    $cashTransactionsResult = $connect->query($cashTransactionsQuery);

    // Check if the query was successful
    if ($cashTransactionsResult === false) {
        throw new Exception("Error executing cash transactions query: " . $connect->error);
    }

    $cashTransactionsData = $cashTransactionsResult->fetch_assoc();
    $response['general cash transactions'] = [
        'count' => isset($cashTransactionsData['transactionCount']) ? (string)$cashTransactionsData['transactionCount'] : '0',
        'amount' => isset($cashTransactionsData['totalAmount']) ? (string)$cashTransactionsData['totalAmount'] : '0'
    ];

} catch (Exception $e) {
    $response['error'] = 'true';
    $response['message'] = $e->getMessage();
}

// Output the dashboard data as JSON
echo json_encode($response);
?>
