<?php
// ─── GET /api/invoices/stats.php ─────────────────────────────────────────
define('ROOT', dirname(__DIR__, 2));
require_once ROOT . '/core/DB.php';
require_once ROOT . '/core/Response.php';
require_once ROOT . '/core/Auth.php';

Auth::require();
$db = DB::get();

$now       = new DateTime();
$thisMonth = $now->format('Y-m');
$lastMonth = (clone $now)->modify('-1 month')->format('Y-m');

// Invoice counts
$inv_this  = (int) $db->scalar("SELECT COUNT(*) FROM invoices WHERE DATE_FORMAT(date_time,'%Y-%m')=?", [$thisMonth]);
$inv_last  = (int) $db->scalar("SELECT COUNT(*) FROM invoices WHERE DATE_FORMAT(date_time,'%Y-%m')=?", [$lastMonth]);

// Payment totals — this month & last month
$pay_this  = (float) $db->scalar(
    "SELECT COALESCE(SUM(amount),0) FROM jobcard_payments WHERE DATE_FORMAT(date_time,'%Y-%m')=?", [$thisMonth]);
$pay_last  = (float) $db->scalar(
    "SELECT COALESCE(SUM(amount),0) FROM jobcard_payments WHERE DATE_FORMAT(date_time,'%Y-%m')=?", [$lastMonth]);

// Invoice value totals (items + drilling + casing) — this and last month
$val_this = (float) $db->scalar(
    "SELECT COALESCE(SUM(
        (SELECT COALESCE(SUM(ii.total),0) FROM invoice_items ii WHERE ii.invoice_no=i.invoice_no)
        + (i.drilling_meters * i.drilling_price)
        + (i.casing_meters   * i.casing_price)
     ),0) FROM invoices i WHERE DATE_FORMAT(i.date_time,'%Y-%m')=?", [$thisMonth]);
$val_last = (float) $db->scalar(
    "SELECT COALESCE(SUM(
        (SELECT COALESCE(SUM(ii.total),0) FROM invoice_items ii WHERE ii.invoice_no=i.invoice_no)
        + (i.drilling_meters * i.drilling_price)
        + (i.casing_meters   * i.casing_price)
     ),0) FROM invoices i WHERE DATE_FORMAT(i.date_time,'%Y-%m')=?", [$lastMonth]);

Response::ok([
    'invoices_this_month'  => $inv_this,
    'invoices_last_month'  => $inv_last,
    'payments_this_month'  => round($pay_this, 2),
    'payments_last_month'  => round($pay_last, 2),
    'inv_value_this_month' => round($val_this * 1.15, 2), // incl VAT
    'inv_value_last_month' => round($val_last * 1.15, 2),
    'this_month_label'     => $now->format('M Y'),
    'last_month_label'     => (clone $now)->modify('-1 month')->format('M Y'),
]);