.-.-.-.-.-

<?php

namespace App\Http\Controllers;

use App\Helpers\Analytics;
use App\Helpers\ExcelHelper;
use App\Helpers\SurveyJSHelper;
use App\Livewire\Filter\DepartmentFilter;
use App\Livewire\TopicDetail;
use App\Models\Interview;
use App\Models\Invitation;
use App\Models\Topic;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Symfony\Component\HttpFoundation\StreamedResponse;

class DashboardController extends Controller
{
    public function index()
    {
        $invitationCount = Invitation::count();
        $answerCount = Interview::where(['is_test' => 0])->count();
        $answerRate = ($invitationCount > 0) ? number_format($answerCount / $invitationCount * 100, 1) : 0;

$invitationQuarterCount = $this->getInvitationsInQuarter();
        $quarterCount = $this->getCurrentQuarterInterviews()->count();
        $quarterRate = ($invitationQuarterCount > 0) ? number_format($quarterCount / $invitationQuarterCount * 100, 1) : 0;

return view('dashboard.index', [
            'answerCount' => $answerCount,
            'answerRate' => $answerRate,
            'quarterCount' => $quarterCount,
            'quarterRate' => $quarterRate,
        ]);
    }

private function getLineChartData(): string
    {
        $top2 = Interview::where(['topic_id' => 1, 'is_test' => 0])
            ->whereQuestion('f1', range(1, 2))
            ->resultsByQuarter()->get()->toArray();
        $total = Interview::where(['topic_id' => 1, 'is_test' => 0])
            ->whereQuestion('f1', range(1, 5))
            ->resultsByQuarter()->get()->toArray();

foreach ($top2 as $key => $value) {
            $top2[$key]['count'] = $value['count'] / $total[$key]['count'] * 100;
        }

$results = array_map(function ($item) {
            return [
                'quarter' => 'Q'.$item['quarter'].' '.$item['year'],
                'value' => round($item['count']),
            ];
        }, $top2);

return json_encode($results);
    }

private function getPieChartData(): string
    {

$interviews = Interview::where(['topic_id' => 1, 'is_test' => 0])->questionResults('f1')->get()->toArray();

$results = [];
        array_map(function ($item) use (&$results) {
            $results[$item['answer']] = $item['count'];
        }, $interviews);

return '['.implode(',', array_reverse($results)).']';
    }

public function getInvitationsInQuarter()
    {
        $currentQuarter = Carbon::now()->quarter;
        $currentYear = Carbon::now()->year;

$sql = "SELECT * FROM invitations WHERE QUARTER(created_at) = $currentQuarter AND YEAR(created_at) = $currentYear";

return count(DB::select(DB::raw($sql)->getValue(DB::connection()->getQueryGrammar())));
    }

public function getCurrentQuarterInterviews()
    {
        return $this->getInterviewsInQuarter(Carbon::now()->quarter, Carbon::now()->year);
    }

/**
     * @return \Illuminate\Database\Query\Builder
     */
    private function getInterviewsInQuarter(int $quarter, int $year, int $topicId = 0)
    {
        $topicFilter = $topicId === 0 ? ['is_test' => 0] : ['topic_id' => $topicId, 'is_test' => 0];

return DB::table('interviews')->where($topicFilter)->whereRaw("QUARTER(created_at) = $quarter AND YEAR(created_at) = $year");
    }

public function comments()
    {
        return view('dashboard.comments');
    }

public function detail(int $id)
    {
        $topic = Topic::where('id', $id)->first();

// add visit Log
        Analytics::addLog('PageVisited', $topic->id, '');

return view('dashboard.detail', [
            'topic' => $topic,
        ]);

return view('dashboard.detail', [
            'topic' => $topic,
        ]);
    }

private function agesToFilter($ages): array
    {
        $agesFilter = [];
        foreach ($ages as $ageString) {
            $split = explode('_', $ageString);
            $agesFilter = [...$agesFilter, ...range($split[0], $split[1])];
        }

return $agesFilter;
    }

public function topics()
    {
        return view('dashboard.topics');
    }

public function topicsOld()
    {
        return view('dashboard.topics-old');
    }

public function topicsSpecial()
    {
        return view('dashboard.topics-special');
    }

/**
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function downloadChartData(Request $request): StreamedResponse|JsonResponse
    {

if (! isset($request->topicId)) {
            return response()->json(['message' => 'topic id not provided'], 400);
        }

$topic = Topic::where('id', $request->topicId)->first();

if (empty($topic)) {
            return response()->json(['message' => 'Topic not found'], 404);
        }

$excel = new ExcelHelper;

if (! empty($request->filters)) {

$excel->addSheet('Angewandte Filter', $this->prepareFiltersForExcel($request->filters));
        }

$departments = DepartmentFilter::getFilters();

if (! empty($request->filters['ages']) || ! empty($request->filters['seniority']) || ! empty($request->filters['gender'])) {

$departments = array_filter($departments, static fn ($dep) => count($dep['filters']) > 1);
        }

$surveyJs = new SurveyJSHelper;

$interviews = $this->getFilteredInterviews($topic->id, $request->filters ?? [], $departments);

$surveyJs->setResults($topic, $interviews)
            ->addDepartments($departments)
            ->addAverage();

$excel->addSheets($surveyJs->getResultsForExcel());

return $excel->download($topic->name.' '.date('Y-m-d'));
    }

private function getFilteredInterviews(string|int $topicId, array $filters, array $departments): Collection|array
    {
        $query = Interview::query();
        $query->where(TopicDetail::baseFilter($topicId));
        $query = TopicDetail::filterByAges($query, $filters['ages'] ?? []);
        $query = TopicDetail::filterByDepartments($query, $departments, $filters['departments'] ?? []);
        $query = TopicDetail::filterByPosition($query, $filters['leadingPosition'] ?? null);
        $query = TopicDetail::filterByGender($query, $filters['gender'] ?? null);
        $query = TopicDetail::filterByQuarters($query, $filters['quarter'] ?? []);

return $query->get();
    }

private function prepareFiltersForExcel(array $filters): array
    {
        $results = [];

$w = 0;

foreach ($filters as $filter) {

if (is_array($filter) && count($filter) >= $w) {

$w = count($filter);
            }
        }

foreach ($filters as $key => $filter) {

$row = [$this->filterTitle($key).':'];

foreach (range(1, $w) as $index) {

$row[$index] = 0;

if (is_array($filter)) {

$row[$index] = isset($filter[$index - 1]) ? $this->filterValueTitle($key, $filter[$index - 1]) : null;
                } else {

$row[1] = $filter;
                }
            }

$results[] = $row;
        }

return array_map(null, ...$results);
    }

private function filterTitle(string $key): string
    {
        // TODO: replace with dynamic labels
        return match ($key) {
            'departments' => 'Beschäftigungsbereich',
            'ages' => 'Alter',
            'regions' => 'Regionen wähle',
            'location' => 'Standorte',
            'seniority' => 'Betriebszugehörigkeit',
            'quarter' => 'Zeitraum',
            'gender' => 'Geschlecht',
            'company' => 'Unternehmen',
            'leadingPosition' => 'Führungsposition',
            default => $key,
        };
    }

private function filterValueTitle(string $key, string $index): string
    {
        // TODO: replace with dynamic labels
        return match ($key) {
            'departments' => match ($index) {
                '16' => 'Admiral: Personal/LoBu/Ausbildung',
                '17' => 'Admiral: Finanzen/Controlling/FiBu',
                '18' => 'Admiral: Einkauf/Fuhrpark',
                '19' => 'Admiral: Standortmanagement',
                '20' => 'Admiral: Verwaltung',
                '21' => 'Admiral: Baubüro/Logistik',
                '22' => 'Admiral: Kassierer',
                '23' => 'Admiral: Technik',
                '24' => 'Admiral: Gastro/Gerätepark',
                '25' => 'Admiral: Oper. Außendienst',
                '26' => 'Admiral: Oper. Innendienst',
                '1' => 'Löwen: Vertrieb',
                '2' => 'Löwen: Produktion & Werkerhaltung/Reinigung',
                '3' => 'Löwen: F&E / CashManagement',
                '4' => 'Löwen: Produktmanagement, Marketing',
                '5' => 'Löwen: Service',
                '6' => 'Löwen: Personal & GF & Politik',
                '9' => 'Löwen: Logistik',
                '10' => 'Löwen: Einkauf /FM / Compactsport',
                '11' => 'Löwen: IT & Organisation',
                '12' => 'Löwen: Finanzen & Controlling',
                '13' => 'Löwen: Compliance /QM /Recht',
                '14' => 'Löwen: Berufsausbildung',
                '15' => 'Löwen: Novo Interactive',
                default => $index
            },
            'ages' => match ($index) {
                '18_25' => '18 - 25 Jahre',
                '26_35' => '26 - 35 Jahre',
                '36_45' => '36 - 45 Jahre',
                '46_55' => '46 - 55 Jahre',
                '56_99' => '56 Jahre oder älter',
                default => $index
            },
            'location' => match ($index) {
                '1' => 'Zentrale Bingen',
                '2' => 'Rellingen',
                '3' => 'Niederlassung',
                '4' => 'Innendienst',
                '5' => 'Außendienst',
                default => $index
            },
            'regions' => match ($index) {
                '1' => 'Nord/Ost',
                '2' => 'Mitte',
                '3' => 'West',
                '4' => 'Süd',
                default => $index
            },
            'seniority' => match ($index) {
                '0_5' => 'Bis zu 5 Jahre',
                '6_15' => '6 - 15 Jahre',
                '16_25' => '16 - 25 Jahre',
                '26_99' => '26 Jahre oder älter',
                default => $index
            },
            'quarter' => match ($index) {
                '2-2022' => 'Q2 2022',
                '3-2022' => 'Q3 2022',
                '4-2022' => 'Q4 2022',
                default => $index
            },
            'gender' => match ($index) {
                '1' => 'männlich',
                '2' => 'weiblich'
            },
            'company' => match ($index) {
                '1' => 'Löwen',
                '2' => 'Admiral',
                default => $index
            },
            'leadingPosition' => match ($index) {
                '1' => 'Führungsposition',
                '2' => 'Keine Führungsposition',
                default => $index
            },
            default => $index,
        };
    }
}

/home/forge/stage.sksb.smartcon-survey.com/app/Console/../Http/Controllers/DashboardController.php