<?php

namespace App\Models;

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

class Student extends Model
{
    use HasFactory;


    /**
     * The attributes that are mass assignable.
     *
     * @var string[]
     */
    protected $fillable = [
        'first_name',
        'last_name',
        'transportation_id',
        'birth_date',
        'birth_place',
        'gender',
        'city_id',
        'street',
        'area',
        'phone',
        'total_installment',
        'telephone',
        'emergency_phone',
        'emergency_kinship',
        'religion_id',
        'mother_id',
        'father_id',
        'division_id',
        'created_at',
        'updated_at',
    ];


    public static function createAndGetId(array $data)
    {
        return DB::table('students')->insertGetId([
            'first_name' => $data['first_name'],
            'last_name' => $data['last_name'],
            'transportation_id' => $data['transportation_id'],
            'birth_date' => \Carbon\Carbon::create($data['birth_date']),
            'birth_place' => $data['birth_place'],
            'gender' => $data['gender'],
            'city_id' => $data['city_id'],
            'street' => $data['street'],
            'area' => $data['area'],
            'phone' => $data['phone'],
            'total_installment' => $data['total_installment'],
            'telephone' => $data['telephone'],
            'emergency_phone' => $data['emergency_phone'],
            'emergency_kinship' => $data['emergency_kinship'],
            'religion_id' => $data['religion_id'],
            'mother_id' => $data['mother_id'],
            'father_id' => $data['father_id'],
            'division_id' => $data['division_id'],
            'created_at' => \Carbon\Carbon::now(),
            'updated_at' => \Carbon\Carbon::now(),
        ]);
    }


    public static function getParentStudents($parent_id)
    {
        return Student::select(
            'students.id',
            DB::raw("CONCAT(students.first_name, ' ',students.last_name) AS students_name"),
            'divisions.title as division',
            'levels.title as level',
            'stages.title as stage',
        )
            ->join('divisions', 'divisions.id', '=', 'students.division_id')
            ->join('levels', 'levels.id', '=', 'divisions.level_id')
            ->join('stages', 'stages.id', '=', 'levels.stage_id')
            ->where('students.mother_id', $parent_id)
            ->orWhere('students.father_id', $parent_id)
            ->get();
    }



    public static function getStudentExams($student_id)
    {
        return DB::table('student_exam_grades')->select(
           'student_exam_grades.created_at',
            'student_exam_grades.grade',
            'semester_exams.title as exam_title',
            'study_materials.title as study_material',
            'semester_exams.quiz_mark as max_grade',
            'study_materials.min_grade',
            //'semester_exams.quiz_mark as exam_grade'
        )
             
            ->join('semester_exams', 'semester_exams.id', '=', 'student_exam_grades.semester_exam_id')
            ->join('study_materials', 'study_materials.id', '=', 'semester_exams.study_material_id')
            ->where('student_exam_grades.student_id', $student_id)
            ->orderBy('created_at', 'desc')->get();
    }


    public static function getStudentNotes($student_id)
    {
        return DB::table('student_notes')->select(
            'student_notes.id',
            'student_notes.text',
            'student_notes.created_at',
            'student_notes_types.title as types',
        )
            ->join('student_notes_types', 'student_notes_types.id', '=', 'student_notes.type_id')
            ->where('student_notes.student_id', $student_id)
            ->where('student_notes_types.type', 'general')
            ->orderBy('created_at', 'desc')->get();
    }


    public static function getStudentAbsence($student_id)
    {
        return DB::table('attendees')->select(
            'attendees.id',
            'attendees.date',
            'attendees.status',
            'attendees.details',
        )
            ->where('attendees.student_id', $student_id)
            ->orderBy('created_at', 'desc')->get();
    }

    public static function getStudentPunishments($student_id)
    {
        return DB::table('student_notes')->select(
            'student_notes.id',
            'student_notes.text',
            'student_notes.created_at',
            'student_notes_types.title as types',
        )
            ->join('student_notes_types', 'student_notes_types.id', '=', 'student_notes.type_id')
            ->where('student_notes.student_id', $student_id)
            ->where('student_notes_types.type', 'punishment')
            ->orderBy('created_at', 'desc')->get();
    }

    public static function getStudentWeeklyProgram($student_id)
    {
        $student  = Student::find($student_id);
        if ($student) {
            return DB::table('weekly_programs')->select(
                'weekly_programs.id',
                'weekly_programs.title',
                'weekly_programs.description',
                'weekly_programs.attachments',
                'weekly_programs.created_at',
                'divisions.title as division',
                'levels.title as level',
                'stages.title as stage',
            )
                ->join('divisions', 'divisions.id', '=', 'weekly_programs.division_id')
                ->join('levels', 'levels.id', '=', 'divisions.level_id')
                ->join('stages', 'stages.id', '=', 'levels.stage_id')
                ->where('weekly_programs.division_id', $student->division_id)
                ->orderBy('created_at', 'desc')->get();
        } else {
            return [];
        }
    }

    public static function getStudentWorksheets($student_id)
    {
        $student  = Student::find($student_id);
        if ($student) {
            return DB::table('worksheets')->select(
                'worksheets.id',
                'worksheets.title',
                'worksheets.description',
                'worksheets.attachments',
                'worksheets.created_at',
            )
                ->where('worksheets.division_id', $student->division_id)
                ->orderBy('created_at', 'desc')->get();
        } else {
            return [];
        }
    }


    public static function getStudentExamsPrograms($student_id)
    {
        $student  = Student::find($student_id);
        if ($student) {
            return DB::table('exam_programs')->select(
                'exam_programs.id',
                'exam_programs.title',
                'exam_programs.description',
                'exam_programs.attachments',
                'exam_programs.created_at',
            )
                ->where('exam_programs.division_id', $student->division_id)
                ->orderBy('created_at', 'desc')->get();
        } else {
            return [];
        }
    }

    public  static function calStudentGrad($student_id,$quiz_type)
    {
        $result = DB::table('student_exam_grades')
        ->join('semester_exams','semester_exams.id','=','student_exam_grades.semester_exam_id')
        ->sum('student_exam_grades.grad')
        ->where('student_exam_grades.student_id',$student_id)
        ->where('semester_exams.quiz_type',$quiz_type);
        $data ={'id':2};
        return $result;
    }
}
