<?php
namespace App\Exports;

use App\Models\Visitor;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class VisitorsExport implements FromCollection, WithHeadings, WithMapping
{
    public function collection()
    {
        $query = Visitor::query();
        $query->select(DB::raw('first_name, last_name, email, company, country, created_at, (SELECT count(*) FROM sessions WHERE visitor_id = visitors.id) as sessions, '
            .' (SELECT max(s.created_at) FROM sessions s WHERE s.visitor_id = visitors.id) as last_session, '
            .' (SELECT count(*) FROM visitor_page_views WHERE visitor_id = visitors.id) as page_views'));
        $query->where('website_id', 1);
        $query->orderBy('created_at', 'desc');
        return $query->get();
    }
    public function headings(): array
    {
        return [
            'First Name',
            'Last Name',
            'Email',
            'Company',
            'Country',
            'Create Date',
            'Sessions #',
            'Last Session',
            'Page views #'
        ];
    }
    public function map($visitorDownload): array
    {
        return [
            $visitorDownload->first_name,
            $visitorDownload->last_name,
            $visitorDownload->email,
            $visitorDownload->company,
            $visitorDownload->country,
            $visitorDownload->created_at->toDateTimeString(),
            $visitorDownload->sessions,
            $visitorDownload->last_session,
            $visitorDownload->page_views
        ];
    }
}