Reporting engine

namespace App\Services;

use App\Models\SavedReport;
use Illuminate\Support\Facades\DB;

class ReportBuilder
{
    /**
     * Run a SavedReport (parameters stored on the row) and return the
     * standardized data shape used by every downstream consumer.
     */
    public function run(SavedReport $report): array
    {
        $params     = $report->parameters ?? [];
        $facilityId = $report->facility_id;

        $data = match ($report->report_type) {
            'occupancy'   => $this->buildOccupancyReport($params, $facilityId),
            'revenue'     => $this->buildRevenueReport($params, $facilityId),
            'delinquency' => $this->buildDelinquencyReport($params, $facilityId),
            'movement'    => $this->buildMovementReport($params, $facilityId),
            'aging'       => $this->buildAgingReport($params, $facilityId),
            'autopay'     => $this->buildAutopayReport($params, $facilityId),
            // Unknown report type → empty shape, not an exception.
            // Forward-compatible if a SavedReport references a removed type.
            default       => ['headers' => [], 'rows' => [], 'totals' => []],
        };

        // Stamp metadata that every consumer expects.
        $data['generated_at'] = now()->toIso8601String();
        $data['row_count']    = count($data['rows'] ?? []);

        return $data;
    }
}

public function buildOccupancyReport(array $params, int $facilityId): array
{
    // Aggregate in the database. One query, indexed scan on units.status.
    // CASE WHEN ... THEN 1 ELSE 0 / SUM is a portable conditional count —
    // works on MySQL, SQLite, Postgres without a CASE-by-driver.
    $rows = Unit::selectRaw("
            ui.unit_size,
            COUNT(*) as total,
            SUM(CASE WHEN units.status = 'occupied'    THEN 1 ELSE 0 END) as occupied,
            SUM(CASE WHEN units.status = 'available'   THEN 1 ELSE 0 END) as available,
            SUM(CASE WHEN units.status = 'reserved'    THEN 1 ELSE 0 END) as reserved,
            SUM(CASE WHEN units.status = 'maintenance' THEN 1 ELSE 0 END) as maintenance
        ")
        ->join('unit_inventories as ui', 'units.unit_inventory_id', '=', 'ui.id')
        ->groupBy('ui.unit_size')
        ->orderBy('ui.unit_size')
        ->get()
        // PHP only does the percentage formatting — heavy lifting is done.
        ->map(fn ($row) => [
            $row->unit_size,
            $row->total,
            (int) $row->occupied,
            (int) $row->available,
            (int) $row->reserved,
            (int) $row->maintenance,
            $row->total > 0
                ? round(($row->occupied / $row->total) * 100, 1) . '%'
                : '0%',
        ])
        ->toArray();

    // Roll-up totals — second small query, also aggregated.
    $totals = Unit::selectRaw("
            COUNT(*) as total,
            SUM(CASE WHEN units.status = 'occupied' THEN 1 ELSE 0 END) as occupied
        ")
        ->join('unit_inventories as ui', 'units.unit_inventory_id', '=', 'ui.id')
        ->first();

    return [
        'headers' => ['Unit Size', 'Total', 'Occupied', 'Available', 'Reserved', 'Maintenance', 'Occupancy %'],
        'rows'    => $rows,
        'totals'  => [
            'Total Units'    => $totals->total ?? 0,
            'Total Occupied' => (int) ($totals->occupied ?? 0),
            'Overall Rate'   => ($totals->total ?? 0) > 0
                ? round(((int) $totals->occupied / $totals->total) * 100, 1) . '%'
                : '0%',
        ],
    ];
}

public function buildAgingReport(array $params, int $facilityId): array
{
    $rows = Payment::where('type', 'charge')
        ->where('status', 'pending')
        ->where('due_date', '<', now())
        ->with([
            // Column projection on relations — avoid SELECT *.
            'user:id,name,email',
            'rental:id,unit_number',
        ])
        ->select('id', 'user_id', 'rental_id', 'amount', 'late_fee', 'due_date')
        ->limit(10000)  // hard cap — backstop, not a feature
        ->get()
        ->map(function ($p) {
            $days   = (int) Carbon::parse($p->due_date)->diffInDays(now());
            $bucket = match (true) {
                $days <= 30 => '1-30',
                $days <= 60 => '31-60',
                $days <= 90 => '61-90',
                default     => '90+',
            };

            return [
                $p->user->name  ?? 'Unknown',
                $p->user->email ?? '',
                $p->rental->unit_number ?? '',
                '$' . number_format((float) $p->amount, 2),
                '$' . number_format((float) $p->late_fee, 2),
                '$' . number_format((float) $p->amount + (float) $p->late_fee, 2),
                $p->due_date?->format('Y-m-d'),
                $days,
                $bucket,
            ];
        });

    return [
        'headers' => ['Customer', 'Email', 'Unit', 'Amount', 'Late Fee', 'Total Owed', 'Due Date', 'Days Overdue', 'Bucket'],
        'rows'    => $rows->toArray(),
        'totals'  => [
            'Total Records' => $rows->count(),

            // Total computed in SQL — independent of the 10K cap above.
            // If 10,001 records exist, the displayed rows are capped, but the
            // grand total still represents the true universe of overdue debt.
            'Total Owed' => '$' . number_format(
                Payment::where('type', 'charge')
                    ->where('status', 'pending')
                    ->where('due_date', '<', now())
                    ->sum(DB::raw('amount + late_fee')),
                2
            ),
        ],
    ];
}