<?php

namespace Yuvayana\Acadlix\Common\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Capsule\Manager as DB;

defined('ABSPATH') || exit();

if (!class_exists('Statistic')) {
  class Statistic extends Model
  {
    protected $table;

    protected $fillable = [
      "statistic_ref_id",
      "question_id",
      "correct_count",
      "partial_correct_count",
      "incorrect_count",
      "hint_count",
      "solved_count",
      "points",
      "negative_points",
      "question_time",
      "answer_data",
      "attempted_at",
      "is_evaluated",
      "evaluated_by",
      "evaluated_id",
      "evaluation_remark"
    ];

    protected $casts = [
      "statistic_ref_id" => "integer",
      "question_id" => "integer",
      "correct_count" => "integer", // ✅ Ensures integer type
      "partial_correct_count" => "integer",
      "incorrect_count" => "integer",
      "hint_count" => "integer",
      "solved_count" => "integer",
      "points" => "double",
      "negative_points" => "double",
      "question_time" => "integer",
      "attempted_at" => "string",
      "is_evaluated" => "integer",
      "evaluated_by" => "string",
      "evaluated_id" => "integer",
      "evaluation_remark" => "string",
    ];

    public function __construct(array $attributes = [])
    {
      parent::__construct($attributes);

      $this->table = acadlix()->helper()->acadlix_table_prefix('statistic');
    }

    public function statistic_ref()
    {
      return $this->belongsTo(acadlix()->model()->statisticRef(), 'statistic_ref_id', 'id');
    }

    public function question()
    {
      return $this->belongsTo(acadlix()->model()->question(), 'question_id', 'id');
    }

    public function setAnswerDataAttribute($value)
    {
      $this->attributes['answer_data'] = maybe_serialize($value);
    }

    public function getAnswerDataAttribute($value)
    {
      return maybe_unserialize($value);
    }

    public function getSubjectPerformance($user_id, $page = 0, $pageSize = 10, $search = null)
    {
      $statisticTable = $this->table;
      $statisticRefTable = acadlix()->model()->statisticRef()->getTable();
      $questionTable = acadlix()->model()->question()->getTable();
      $subjectTable = acadlix()->model()->subject()->getTable();

      // 1. Create a base query for common joins and conditions
      $baseQuery = DB::table($statisticTable)
        ->join($statisticRefTable, "$statisticRefTable.id", '=', "$statisticTable.statistic_ref_id")
        ->join($questionTable, "$questionTable.id", '=', "$statisticTable.question_id")
        ->join($subjectTable, "$subjectTable.id", '=', "$questionTable.subject_id")
        ->where("$statisticRefTable.user_id", $user_id)
        // 2. Add dynamic search if a search term is provided
        ->when($search, function ($q) use ($subjectTable, $search) {
          return $q->where("$subjectTable.subject_name", 'like', "%{$search}%");
        });

      // 3. Optimize the count logic by simply counting distinct subject IDs 
      $total = (clone $baseQuery)->count(DB::raw("DISTINCT $subjectTable.id"));

      // 4. Apply groupings, optimized math, and safety checks to the main query
      $query = $baseQuery
        ->groupBy("$subjectTable.id", "$subjectTable.subject_name")
        ->selectRaw("
            $subjectTable.id as subject_id,
            $subjectTable.subject_name as subject_name,
            COUNT(*) as total_question,
            SUM($statisticTable.solved_count) as solved_question,
            SUM($statisticTable.correct_count) as correct_question,
            SUM($statisticTable.incorrect_count) as incorrect_question,
            SUM($statisticTable.partial_correct_count) as partially_correct_question,
            ROUND(AVG($statisticTable.question_time), 2) as average_time_per_question,
            ROUND((SUM($statisticTable.correct_count) / NULLIF(SUM($statisticTable.solved_count), 0)) * 100, 2) as accuracy
        ")
        ->orderByDesc('total_question');

      // 5. Fixed the pagination syntax ($page * $pageSize)
      if ($pageSize > 0) {
        $query->skip($page * $pageSize)->take($pageSize);
      }

      return [
        'data' => $query->get(),
        'total' => $total,
      ];
    }

  }
}