<?php

namespace App\Exports;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class MemberUsageExport implements FromCollection, ShouldAutoSize, WithHeadings, WithMapping
{
    private $from;
    private $to;

    public function __construct(string $from, string $to)
    {
        $this->from = $from;
        $this->to = $to;
    }

    public function collection(): Collection
    {
        $users = DB::table('users')
            ->leftJoin('members', 'users.member_id', '=', 'members.id')
            ->select([
                'users.id',
                'users.name',
                'users.last_name',
                'users.email',
                'members.name as member_name',
            ])
            ->orderBy('users.last_name')
            ->orderBy('users.name')
            ->get();

        $usageByUserAndMonth = DB::table('member_usage_sessions')
            ->whereBetween('started_at', [
                $this->from . ' 00:00:00',
                $this->to . ' 23:59:59',
            ])
            ->select('user_id')
            ->selectRaw("DATE_FORMAT(started_at, '%Y-%m') as month")
            ->selectRaw('COUNT(id) as login_count')
            ->selectRaw('AVG(active_seconds) as average_session_seconds')
            ->selectRaw('MAX(started_at) as last_login_at')
            ->groupBy('user_id', 'month')
            ->get()
            ->keyBy(function ($usage) {
                return $usage->user_id . ':' . $usage->month;
            });

        $mostRecentLoginByUser = DB::table('member_usage_sessions')
            ->select('user_id')
            ->selectRaw('MAX(started_at) as most_recent_login_at')
            ->groupBy('user_id')
            ->pluck('most_recent_login_at', 'user_id');

        $rows = collect();
        $month = Carbon::parse($this->to)->startOfMonth();
        $firstMonth = Carbon::parse($this->from)->startOfMonth();

        while ($month->gte($firstMonth)) {
            $monthValue = $month->format('Y-m');

            foreach ($users as $user) {
                $usage = $usageByUserAndMonth->get($user->id . ':' . $monthValue);

                $rows->push((object) [
                    'month' => $monthValue,
                    'name' => $user->name,
                    'last_name' => $user->last_name,
                    'email' => $user->email,
                    'member_name' => $user->member_name,
                    'last_login_at' => $usage->last_login_at ?? null,
                    'most_recent_login_at' => $mostRecentLoginByUser->get($user->id),
                    'login_count' => (int) ($usage->login_count ?? 0),
                    'average_session_seconds' => $usage->average_session_seconds ?? null,
                ]);
            }

            $month->subMonth();
        }

        return $rows;
    }

    public function headings(): array
    {
        return [
            'Month',
            'First name',
            'Last name',
            'Email',
            'Member company',
            'Most recent login',
            'Last login in month',
            'Logins in month',
            'Average session duration (m:ss)',
        ];
    }

    public function map($user): array
    {
        return [
            $user->month,
            $user->name,
            $user->last_name,
            $user->email,
            $user->member_name,
            $user->most_recent_login_at,
            $user->last_login_at,
            (int) $user->login_count,
            $user->average_session_seconds === null
                ? null
                : sprintf(
                    '%d:%02d',
                    intdiv((int) round((float) $user->average_session_seconds), 60),
                    (int) round((float) $user->average_session_seconds) % 60
                ),
        ];
    }
}