<?php

namespace App\Console\Commands;

use App\Models\AnalyticsEvent;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class SessionsMaintaining extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'sessions:maintain';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Sessions Maintaining Command';

    /**
     * Execute the console command.
     *
     * @return void
     */
    public function handle()
    {
        $eventModels = DB::table('analytics_events')
            ->leftJoin('analytics_sessions', 'analytics_events.session_id', '=', 'analytics_sessions.id')
            ->selectRaw("analytics_sessions.start_time,analytics_events.id, analytics_events.session_id, analytics_events.created_at, analytics_events.data->>'$.utm_source' utm_source, analytics_events.data->>'$.utm_medium' utm_medium, analytics_events.data->>'$.utm_campaign' utm_campaign, analytics_events.application_id, analytics_events.platform_id")
            ->where('analytics_events.is_session_processed', '=', 0)
            ->whereNotNull('analytics_events.session_id')->limit(1000)->orderBy('id', 'ASC')
            ->get();
        $eventsId = $eventModels->pluck('id')->toArray();
        $sessionsEvents = $eventModels->groupBy('session_id');
        $newSessions = [];
        $oldSessionIDs = [];
        $oldSessionLastActivityCases = '';
        $oldSessionLifetimeCases = '';
        foreach ($sessionsEvents as $key => $sessionEvents) {
            $firstEvent = $sessionEvents->first();
            if (is_null($firstEvent->start_time)) {
                $analyticsSession = [];
                $analyticsSession['id'] = $key;
                $analyticsSession['start_time'] = $firstEvent->created_at;
                $utms = [];
                if ($firstEvent->utm_source)
                    $utms['utm_source'] = $firstEvent->utm_source;
                if ($firstEvent->utm_campaign)
                    $utms['utm_campaign'] = $firstEvent->utm_campaign;
                if ($firstEvent->utm_medium)
                    $utms['utm_medium'] = $firstEvent->utm_medium;
                $analyticsSession['utms'] = json_encode($utms);
                $analyticsSession['application_id'] = $firstEvent->application_id;
                $analyticsSession['platform_id'] = $firstEvent->platform_id;
                $analyticsSession['last_activity_time'] = $sessionEvents->last()->created_at;
                $analyticsSession['lifetime'] = Carbon::parse($analyticsSession['last_activity_time'])->diffInSeconds($analyticsSession['start_time']);
                $newSessions[] = $analyticsSession;
            } else {
                $oldSessionIDs[] = $key;
                $oldSessionLastActivityCases .= "WHEN '{$key}' then '{$sessionEvents->last()->created_at}' ";
                $oldSessionLifetimeCases .= "WHEN '{$key}' then " . Carbon::parse($sessionEvents->last()->created_at)->diffInSeconds($firstEvent->start_time) . " ";
            }
        }

        DB::beginTransaction();
        if (sizeof($oldSessionIDs)) {
            $oldSessionIDsStr = implode("','", $oldSessionIDs);
            DB::update("UPDATE analytics_sessions SET
                            `last_activity_time` = CASE `id` {$oldSessionLastActivityCases} END,
                            `lifetime` = CASE `id` {$oldSessionLifetimeCases} END
                            WHERE `id` in ('{$oldSessionIDsStr}')");
        }
        if (sizeof($newSessions)) {
            DB::table('analytics_sessions')->insert($newSessions);
        }
        AnalyticsEvent::whereIn('id', $eventsId)->update(['is_session_processed' => 1]);
        DB::commit();
    }
}