Collections and mapping from database results

Hi. I've been trying to get through this one on my own to try and get to know it a bit more, but Im at a stage where I thought I had it, then, well, ye didn't! :)

Anyway, what I am trying to achieve is to grab all records from the database from a given date range, group by a client_id and then build an array so that I can try and do some logic with the data. Currently I have a working controller that does the job, but is very ugly code and thought this may be a good use for collections and mapping.

Heres what I cave as an example data set from the database:

- id
- uuid
- name (category)
- client_id
- type (the type of event)
- timestamp (Y-m-d)
- user

This data originally comes from an Api call from an external source and then stores into the database columns above.

What I am trying to achieve is to create an array where it groups the 'client_ids', then adds the different 'type' events to that 'client_id' along with the timestamp. So hopefully ending up with an array like below:

[
	'client_id' => 1234,
	'created' => '2023-01-20',
	'incomplete' => '2023-01-24',
	'approved' => '2023-02-01',
etc...
]

Once I have these, I then need to try and iterate through all the client_ids and work out the time difference between the 'created' and 'approved'.

This is my testing trying to play with the mapping:

$start = Carbon::parse(Carbon::parse('2021-01-01')->format('Y-m-d'));
        $end = Carbon::parse(Carbon::parse('2021-12-31')->format('Y-m-d'));

        $reports = ReportImport::groupBy('client_id')->whereBetween('timestamp', [$start, $end])
            ->get()->take(100);

        $data = collect($reports)->map(function ($item) {
            $filtered = [];
            $filtered['client_id'] = $item->client_id;
            ($item->type == 'Date Created' ? $filtered['Created'] = $item->timestamp : $filtered['Created'] = null);
            ($item->type == 'Set Status To Approved' ? $filtered['Approved'] = $item->timestamp :
                $filtered['Approved'] =
                null);
            ($item->type == 'Set Status To Verified Complete' ? $filtered['VerifiedComplete'] = $item->timestamp :
                $filtered['VerifiedComplete'] = null);
            ($item->type == 'Document Issue Date' ? $filtered['DocumentIssued'] = $item->timestamp : $filtered['DocumentIssued'] =
                null);

            return $filtered;
        });

        dd($data->take(100));

Any pointers on this would be massively grateful.