<?php
include('connectioni.php'); // Must define $dbinv as a mysqli connection

// Date ranges: start of this month to today
$currentMonthStart = date('Y-m-01');
$currentDate = date('Y-m-d');

// Date ranges: same range last month
$previousMonthStart = date('Y-m-01', strtotime('first day of last month'));
$previousSameDate = date('Y-m-d', strtotime('last month'));

// ===== CURRENT MONTH SALES & COMPLETED ORDERS =====
$sqlCurrent = "
    SELECT 
        SUM(GRAND_TOT) AS total_current, 
        COUNT(*) AS orders_current 
    FROM s_salma 
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$currentMonthStart' AND '$currentDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
";
$resultCurrent = mysqli_query($dbinv, $sqlCurrent);
if (!$resultCurrent) {
    die("Current query failed: " . mysqli_error($dbinv));
}
$rowCurrent = mysqli_fetch_assoc($resultCurrent);
$totalCurrent = (isset($rowCurrent['total_current']) && is_numeric($rowCurrent['total_current'])) ? floatval($rowCurrent['total_current']) : 0;
$ordersCurrent = (isset($rowCurrent['orders_current']) && is_numeric($rowCurrent['orders_current'])) ? intval($rowCurrent['orders_current']) : 0;

// ===== PREVIOUS MONTH SALES & COMPLETED ORDERS =====
$sqlPrevious = "
    SELECT 
        SUM(GRAND_TOT) AS total_previous, 
        COUNT(*) AS orders_previous 
    FROM s_salma 
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$previousMonthStart' AND '$previousSameDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
";
$resultPrevious = mysqli_query($dbinv, $sqlPrevious);
if (!$resultPrevious) {
    die("Previous query failed: " . mysqli_error($dbinv));
}
$rowPrevious = mysqli_fetch_assoc($resultPrevious);
$totalPrevious = (isset($rowPrevious['total_previous']) && is_numeric($rowPrevious['total_previous'])) ? floatval($rowPrevious['total_previous']) : 0;
$ordersPrevious = (isset($rowPrevious['orders_previous']) && is_numeric($rowPrevious['orders_previous'])) ? intval($rowPrevious['orders_previous']) : 0;

// ===== CALCULATE PERCENTAGE CHANGES =====
if ($totalPrevious > 0) {
    $changePercentTotal = (($totalCurrent - $totalPrevious) / $totalPrevious) * 100;
} elseif ($totalCurrent > 0) {
    $changePercentTotal = 100;
} else {
    $changePercentTotal = 0;
}

if ($ordersPrevious > 0) {
    $changePercentOrders = (($ordersCurrent - $ordersPrevious) / $ordersPrevious) * 100;
} elseif ($ordersCurrent > 0) {
    $changePercentOrders = 100;
} else {
    $changePercentOrders = 0;
}

// ===== FORMATTING =====
$totalCurrentFormatted = 'Rs.' . number_format($totalCurrent, 2);
$ordersCurrentFormatted = number_format($ordersCurrent);
$changeSignTotal = ($changePercentTotal >= 0) ? '+' : '-';
$changeClassTotal = ($changePercentTotal >= 0) ? 'positive' : 'negative';
$changeTotalFormatted = $changeSignTotal . number_format(abs($changePercentTotal), 1) . '%';

$changeSignOrders = ($changePercentOrders >= 0) ? '+' : '-';
$changeClassOrders = ($changePercentOrders >= 0) ? 'positive' : 'negative';
$changeOrdersFormatted = $changeSignOrders . number_format(abs($changePercentOrders), 1) . '%';


// Handle selected year (fallback to current year)
$selectedYear = isset($_GET['year']) ? intval($_GET['year']) : date('Y');

// Generate monthly sales data for the selected year
$sqlMonthlySales = "
    SELECT 
        MONTH(STR_TO_DATE(SDATE, '%Y-%m-%d')) AS month,
        SUM(GRAND_TOT) AS total
    FROM s_salma
    WHERE 
        YEAR(STR_TO_DATE(SDATE, '%Y-%m-%d')) = $selectedYear
        AND (CANCELL != '1' OR CANCELL IS NULL)
    GROUP BY month
    ORDER BY month
";
$resultMonthlySales = mysqli_query($dbinv, $sqlMonthlySales);

// Initialize an array with 12 months = 0
$monthlySales = array_fill(1, 12, 0.0);

while ($row = mysqli_fetch_assoc($resultMonthlySales)) {
    $monthNum = intval($row['month']);
    $monthlySales[$monthNum] = floatval($row['total']);
}

// Encode for JS
$monthlySalesJson = json_encode(array_values($monthlySales));

// ===== DAILY SALES COMPARISON (Current vs Last Month) =====

$previousMonthEnd = date('Y-m-t', strtotime($previousMonthStart)); // gives last date of previous month


// Get current month's daily sales
$sqlDailyCurrent = "
    SELECT 
        DAY(STR_TO_DATE(SDATE, '%Y-%m-%d')) AS day,
        SUM(GRAND_TOT) AS total
    FROM s_salma
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$currentMonthStart' AND '$currentDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
    GROUP BY day
    ORDER BY day
";
$resultDailyCurrent = mysqli_query($dbinv, $sqlDailyCurrent);
$dailyCurrent = array_fill(1, 31, 0.0); // assume up to 31 days
while ($row = mysqli_fetch_assoc($resultDailyCurrent)) {
    $day = intval($row['day']);
    $dailyCurrent[$day] = floatval($row['total']);
}

// Get last month's same day range sales
$sqlDailyPrevious = "
    SELECT 
        DAY(STR_TO_DATE(SDATE, '%Y-%m-%d')) AS day,
        SUM(GRAND_TOT) AS total
    FROM s_salma
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$previousMonthStart' AND '$previousMonthEnd'
        AND (CANCELL != '1' OR CANCELL IS NULL)
    GROUP BY day
    ORDER BY day
";

$resultDailyPrevious = mysqli_query($dbinv, $sqlDailyPrevious);
$dailyPrevious = array_fill(1, 31, 0.0);
while ($row = mysqli_fetch_assoc($resultDailyPrevious)) {
    $day = intval($row['day']);
    $dailyPrevious[$day] = floatval($row['total']);
}

// Encode daily sales for chart
$dailyCurrentJson = json_encode(array_values($dailyCurrent));
$dailyPreviousJson = json_encode(array_values($dailyPrevious));


// ===== CURRENT MONTH OUTSTANDING =====
$sqlOutstandingCurrent = "
    SELECT SUM(GRAND_TOT - TOTPAY) AS outstanding_current
    FROM s_salma 
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$currentMonthStart' AND '$currentDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
";
$resultOutstandingCurrent = mysqli_query($dbinv, $sqlOutstandingCurrent);
$rowOutstandingCurrent = mysqli_fetch_assoc($resultOutstandingCurrent);
$outstandingCurrent = (isset($rowOutstandingCurrent['outstanding_current']) && is_numeric($rowOutstandingCurrent['outstanding_current'])) ? floatval($rowOutstandingCurrent['outstanding_current']) : 0;

// ===== PREVIOUS MONTH OUTSTANDING =====
$sqlOutstandingPrevious = "
    SELECT SUM(GRAND_TOT - TOTPAY) AS outstanding_previous
    FROM s_salma 
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$previousMonthStart' AND '$previousSameDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
";
$resultOutstandingPrevious = mysqli_query($dbinv, $sqlOutstandingPrevious);
$rowOutstandingPrevious = mysqli_fetch_assoc($resultOutstandingPrevious);
$outstandingPrevious = (isset($rowOutstandingPrevious['outstanding_previous']) && is_numeric($rowOutstandingPrevious['outstanding_previous'])) ? floatval($rowOutstandingPrevious['outstanding_previous']) : 0;

// ===== CURRENT MONTH RETURN =====
$sqlReturnCurrent = "
    SELECT SUM(RET_AMO) AS return_current
    FROM s_salma 
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$currentMonthStart' AND '$currentDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
";
$resultReturnCurrent = mysqli_query($dbinv, $sqlReturnCurrent);
$rowReturnCurrent = mysqli_fetch_assoc($resultReturnCurrent);
$returnCurrent = (isset($rowReturnCurrent['return_current']) && is_numeric($rowReturnCurrent['return_current'])) ? floatval($rowReturnCurrent['return_current']) : 0;

// ===== PREVIOUS MONTH RETURN =====
$sqlReturnPrevious = "
    SELECT SUM(RET_AMO) AS return_previous
    FROM s_salma 
    WHERE 
        STR_TO_DATE(SDATE, '%Y-%m-%d') BETWEEN '$previousMonthStart' AND '$previousSameDate'
        AND (CANCELL != '1' OR CANCELL IS NULL)
";
$resultReturnPrevious = mysqli_query($dbinv, $sqlReturnPrevious);
$rowReturnPrevious = mysqli_fetch_assoc($resultReturnPrevious);
$returnPrevious = (isset($rowReturnPrevious['return_previous']) && is_numeric($rowReturnPrevious['return_previous'])) ? floatval($rowReturnPrevious['return_previous']) : 0;

// ===== PERCENTAGE CHANGES =====
$changePercentOutstanding = ($outstandingPrevious > 0) ? (($outstandingCurrent - $outstandingPrevious) / $outstandingPrevious) * 100 : ($outstandingCurrent > 0 ? 100 : 0);
$changePercentReturn = ($returnPrevious > 0) ? (($returnCurrent - $returnPrevious) / $returnPrevious) * 100 : ($returnCurrent > 0 ? 100 : 0);

// ===== FORMATTING =====
$outstandingCurrentFormatted = 'Rs.' . number_format($outstandingCurrent, 2);
$returnCurrentFormatted = 'Rs.' . number_format($returnCurrent, 2);

$changeSignOutstanding = ($changePercentOutstanding >= 0) ? '+' : '-';
$changeClassOutstanding = ($changePercentOutstanding >= 0) ? 'positive' : 'negative';
$changeOutstandingFormatted = $changeSignOutstanding . number_format(abs($changePercentOutstanding), 1) . '%';

$changeSignReturn = ($changePercentReturn >= 0) ? '+' : '-';
$changeClassReturn = ($changePercentReturn >= 0) ? 'positive' : 'negative';
$changeReturnFormatted = $changeSignReturn . number_format(abs($changePercentReturn), 1) . '%';


?>
