.-.-.-.-.-

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class DataAggregation extends Model
{
    public $timestamps = false;

use HasFactory;

protected $guarded = [];

/**
     * Aggregate the interviews data.
     * The method aggregates the interview data and stores it in the data_aggregations table.
     * to improve the performance of the application.
     * this should be run after the interviews data is imported, or after the cache is cleared.
     */
    public static function aggregateInterviews(): void
    {
        DataAggregation::truncate();
        $interviews = Interview::query()
            ->select('country_id', 'wave', 'key', 'value', DB::raw('YEAR(created_at) as year'), DB::raw('QUARTER(created_at) as quarter'), DB::raw('COUNT(*) as cnt'))
            ->join('answers', 'interviews.id', '=', 'answers.interview_id')
            ->where('key', 'like', 'Q%')
            ->where('key', 'not like', '%order%')
            ->where('key', 'not like', '%color%')
            ->where('key', 'not like', '%shown%')
            ->whereNotNull('value')
            ->whereNot('value', '')
            ->groupBy('country_id', 'year', 'quarter', 'wave', 'key', 'value')
            ->get()->toArray();

$interviews = array_map(function (array $aggregate) {

$data = explode('_', strtoupper($aggregate['key']));
            $questionName = $data[0];

if (in_array($questionName, ['Q19', 'Q9'], true)) {
                $statementId = $data[1];
                if (! isset($data[2])) {
                    $modelId = $aggregate['value'];
                    $aggregate['value'] = $aggregate['value'] == 99 ? 0 : 1;
                } else {
                    $modelId = $data[2];
                }
            } else {
                $modelId = $data[1] ?? $aggregate['value'];
                $statementId = null;
            }
            if (strtoupper($modelId) === 'BRAND') {
                $questionName = $data[0].'_'.$data[1];
                $modelId = $data[2] ?? $aggregate['value'];
            }
            $modelId = in_array($modelId, ['NONOPTION', 'NOANSWER']) ? 99 : $modelId;

// Default model type is Brand and the questions Q2a to Q8a are related to products
            // so we need to change the model type to Product
            $modelType = 'Brand';
            if (! in_array($questionName, ['Q1A', 'Q1B'], true) && str_contains($aggregate['key'], 'a')) {
                $modelType = 'Product';
            }

return [
                'country_id' => $aggregate['country_id'],
                'year' => $aggregate['year'],
                'quarter' => $aggregate['quarter'],
                'wave' => $aggregate['wave'],
                'key' => $aggregate['key'],
                'question_name' => $questionName,
                'model_id' => $modelId,
                'model_type' => $modelType,
                'statement_id' => $statementId,
                'value' => $aggregate['value'],
                'aggregate' => $aggregate['cnt'],
            ];
        }, $interviews);

foreach (array_chunk($interviews, 1000) as $interviews) {
            static::insert($interviews, ['country_id', 'key', 'wave'], ['value', 'aggregate']);
        }
    }
}

/home/forge/stage.herta-bht.smartcon-survey.com/app/Models/DataAggregation.php