<?php

namespace App\Exports;

use App\Models\ParkingAccessRecord;
use App\Models\ParkingLicensePlate;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;

class ParkingBehaviorExport implements FromArray, WithHeadings
{
    public function array(): array
    {
        $data = [];
        $index = 1;
        $select = " SELECT DATE_FORMAT(FROM_UNIXTIME(enter_time), "
            . "'%Y-%m-%d') AS time_period, `license_plate_id`, "
            . "COUNT(license_plate_id) AS parking_frequency, SUM(COALESCE("
            . "leave_time, UNIX_TIMESTAMP()) - enter_time) AS parking_duration";
        $from = " FROM `parking_access_record` ";
        $group = " GROUP BY DATE_FORMAT(FROM_UNIXTIME(enter_time),"
            . " '%Y-%m-%d'), `license_plate_id`";

        $items = DB::select($select . $from . $group);
        foreach ($items as &$item) {
            $license_plate = ParkingLicensePlate::getNumber(
                $item->license_plate_id
            );
            $data[] = [
                'id'                => $index,
                'time_period'       => $item->time_period,
                'license_plate'     => $license_plate,
                'parking_frequency' => $item->parking_frequency,
                'parking_duration'  => $item->parking_duration
            ];
            $index += 1;
        }
        return $data;
    }

    /**
     * @return array
     */
    public function headings(): array
    {
        return [
            __('exports.global.index'),
            __('exports.parking_behavior.time_period'),
            __('exports.parking_behavior.license_plate'),
            __('exports.parking_behavior.parking_frequency'),
            __('exports.parking_behavior.parking_duration')
        ];
    }
}