<?php
// ─── GET /api/revenue-report.php ────────────────────────────────────────
define('ROOT', dirname(__DIR__));
require_once ROOT . '/core/DB.php';
require_once ROOT . '/core/Response.php';
require_once ROOT . '/core/Auth.php';

Auth::require();
$db = DB::get();

$dateFrom = trim($_GET['date_from'] ?? '');
$dateTo   = trim($_GET['date_to']   ?? '');
if (!$dateTo) $dateTo = date('Y-m-d');

$dWhere = ['jp.jobcard_id NOT LIKE \'PI_%\'', 'jp.jobcard_id NOT LIKE \'PR_%\''];
$dParams = [];
if ($dateFrom) { $dWhere[] = 'DATE(jp.date_time) >= ?'; $dParams[] = $dateFrom; }
if ($dateTo)   { $dWhere[] = 'DATE(jp.date_time) <= ?'; $dParams[] = $dateTo; }

$drilling = $db->rows(
    "SELECT jp.jobcard_id, jp.amount, jp.payment_method, jp.date_time,
            j.jc_no, j.client_name, j.address, t.name AS team_name
     FROM   jobcard_payments jp
     LEFT JOIN jobcards j ON CAST(j.record_id AS CHAR) = jp.jobcard_id
     LEFT JOIN teams t ON t.record_id = j.team_assigned_id
     WHERE  " . implode(' AND ', $dWhere) . "
     ORDER BY jp.date_time DESC LIMIT 500",
    $dParams
);

$pWhere = ['(jp.jobcard_id LIKE \'PI_%\' OR jp.jobcard_id LIKE \'PR_%\')'];
$pParams = [];
if ($dateFrom) { $pWhere[] = 'DATE(jp.date_time) >= ?'; $pParams[] = $dateFrom; }
if ($dateTo)   { $pWhere[] = 'DATE(jp.date_time) <= ?'; $pParams[] = $dateTo; }

$pumpRaw = $db->rows(
    "SELECT jp.jobcard_id, jp.amount, jp.payment_method, jp.date_time
     FROM   jobcard_payments jp
     WHERE  " . implode(' AND ', $pWhere) . "
     ORDER BY jp.date_time DESC LIMIT 500",
    $pParams
);

// Enrich pump payments with client/team info
$pump = [];
foreach ($pumpRaw as $p) {
    $jid = $p['jobcard_id'];
    if (strpos($jid, 'PI_') === 0) {
        $info = $db->row(
            "SELECT pi.client_name, pi.area, t.name AS team_name
             FROM pump_installation pi
             LEFT JOIN teams t ON t.record_id = pi.drilling_team_id
             WHERE pi.jobcard_no = ?", [$jid]
        );
        $p['client_name'] = $info['client_name'] ?? '';
        $p['area']        = $info['area'] ?? '';
        $p['team_name']   = $info['team_name'] ?? '';
        $p['job_type']    = 'Install';
    } else {
        $info = $db->row(
            "SELECT COALESCE(pi.client_name, pr.address) AS client_name,
                    COALESCE(pi.area, pr.address) AS area,
                    t.name AS team_name
             FROM pump_repair pr
             LEFT JOIN pump_installation pi ON pi.record_id = pr.installation_id
             LEFT JOIN teams t ON t.record_id = pr.drilling_team_id
             WHERE pr.jobcard_no = ?", [$jid]
        );
        $p['client_name'] = $info['client_name'] ?? '';
        $p['area']        = $info['area'] ?? '';
        $p['team_name']   = $info['team_name'] ?? '';
        $p['job_type']    = 'Repair';
    }
    $pump[] = $p;
}

$dTotal     = array_sum(array_column($drilling, 'amount'));
$pTotal     = array_sum(array_column($pump, 'amount'));
$grandTotal = $dTotal + $pTotal;

$byMethod = [];
foreach (array_merge($drilling, $pump) as $p) {
    $m = $p['payment_method'] ?: 'UNKNOWN';
    $byMethod[$m] = ($byMethod[$m] ?? 0) + (float)$p['amount'];
}

Response::ok(compact('drilling','pump','dTotal','pTotal','grandTotal','byMethod'));