<?php
/**
 * Debug script to check weekly sales data
 */

require_once 'system/autoload/PEAR2/Autoload.php';
require_once 'init.php';

echo "<h2>Weekly Sales Debug</h2>";
echo "<p>Today: " . date('Y-m-d') . "</p>";

// Check if there are any transactions
$totalTransactions = ORM::for_table('tbl_transactions')->count();
echo "<p>Total transactions in database: $totalTransactions</p>";

// Check recent transactions
$recentTransactions = ORM::for_table('tbl_transactions')
    ->order_by_desc('recharged_on')
    ->limit(10)
    ->find_many();

echo "<h3>Last 10 Transactions:</h3>";
echo "<table border='1' cellpadding='5'>";
echo "<tr><th>Date</th><th>Method</th><th>Price</th><th>Username</th></tr>";
foreach ($recentTransactions as $trans) {
    echo "<tr>";
    echo "<td>{$trans->recharged_on}</td>";
    echo "<td>{$trans->method}</td>";
    echo "<td>{$trans->price}</td>";
    echo "<td>{$trans->username}</td>";
    echo "</tr>";
}
echo "</table>";

// Generate weekly sales data
echo "<h3>Weekly Sales (Last 8 Weeks):</h3>";
echo "<table border='1' cellpadding='5'>";
echo "<tr><th>Week</th><th>Start Date</th><th>End Date</th><th>Total Sales</th></tr>";

for ($i = 7; $i >= 0; $i--) {
    if ($i == 0) {
        $weekStart = date('Y-m-d', strtotime('monday this week'));
        $weekEnd = date('Y-m-d');
    } else {
        $weekStart = date('Y-m-d', strtotime("-$i week monday"));
        $weekEnd = date('Y-m-d', strtotime("-$i week sunday"));
    }
    
    $results = ORM::for_table('tbl_transactions')
        ->where_gte('recharged_on', $weekStart)
        ->where_lte('recharged_on', $weekEnd)
        ->where_not_equal('method', 'Customer - Balance')
        ->where_not_equal('method', 'Recharge Balance - Administrator')
        ->select_expr('SUM(price)', 'total')
        ->find_one();
    
    $totalSales = $results ? $results->total : 0;
    
    echo "<tr>";
    echo "<td>Week " . (8 - $i) . "</td>";
    echo "<td>$weekStart</td>";
    echo "<td>$weekEnd</td>";
    echo "<td>" . number_format($totalSales, 2) . "</td>";
    echo "</tr>";
}
echo "</table>";

// Check the SQL being generated
echo "<h3>Sample SQL Query:</h3>";
$weekStart = date('Y-m-d', strtotime("-1 week monday"));
$weekEnd = date('Y-m-d', strtotime("-1 week sunday"));
$query = ORM::for_table('tbl_transactions')
    ->where_gte('recharged_on', $weekStart)
    ->where_lte('recharged_on', $weekEnd)
    ->where_not_equal('method', 'Customer - Balance')
    ->where_not_equal('method', 'Recharge Balance - Administrator')
    ->select_expr('SUM(price)', 'total');

echo "<pre>";
echo "For week: $weekStart to $weekEnd\n";
echo "Query: " . $query->find_one();
echo "</pre>";
