<?php

namespace App\Http\Controllers\SchoolAdmin\Staff;

use App\Http\Controllers\Controller;
use App\Models\SchoolClass;
use App\Models\StaffProfile;
use App\Models\Subject;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class WorkloadController extends Controller
{
    /** Overview of all teacher workloads */
    public function index()
    {
        $schoolId = Auth::user()->school_id;

        // Get each teacher's number of class-subject assignments
        $staffList = StaffProfile::with('user')
            ->forSchool($schoolId)
            ->active()
            ->withCount(['user as periods_per_week' => function ($query) {
                $query->select(DB::raw('count(*)'))
                    ->from('school_class_subject_teacher as scst')
                    ->whereColumn('scst.teacher_id', 'users.id');
            }])
            ->get();

        return view('schooladmin.staff.workload.index', compact('staffList'));
    }

    /** Individual teacher workload detail */
    public function show(StaffProfile $staff)
    {
        $staff->load('user');
        $schoolId = Auth::user()->school_id;

        // All class-subject-teacher assignments for this teacher
        $assignments = DB::table('school_class_subject_teacher as scst')
            ->join('school_classes as sc', 'sc.id', '=', 'scst.school_class_id')
            ->join('subjects as s', 's.id', '=', 'scst.subject_id')
            ->where('scst.teacher_id', $staff->user_id)
            ->select('sc.name as class_name', 's.name as subject_name', 'scst.id as pivot_id')
            ->get();

        // Available classes and subjects for assignment
        $classes  = SchoolClass::where('school_id', $schoolId)->orderBy('name')->get();
        $subjects = Subject::where('school_id', $schoolId)->orderBy('name')->get();

        return view('schooladmin.staff.workload.show', compact('staff', 'assignments', 'classes', 'subjects'));
    }

    /** Assign a class+subject to a teacher */
    public function assign(Request $request)
    {
        $request->validate([
            'staff_id'        => 'required|exists:staff_profiles,id',
            'school_class_id' => 'required|exists:school_classes,id',
            'subject_id'      => 'required|exists:subjects,id',
        ]);

        $staff = StaffProfile::findOrFail($request->staff_id);

        // Prevent duplicates
        $exists = DB::table('school_class_subject_teacher')
            ->where('teacher_id', $staff->user_id)
            ->where('school_class_id', $request->school_class_id)
            ->where('subject_id', $request->subject_id)
            ->exists();

        if ($exists) {
            return back()->with('error', 'This assignment already exists.');
        }

        DB::table('school_class_subject_teacher')->insert([
            'teacher_id'      => $staff->user_id,
            'school_class_id' => $request->school_class_id,
            'subject_id'      => $request->subject_id,
        ]);

        return redirect()->route('schooladmin.staff.workload.show', $staff)
            ->with('success', 'Class/subject assigned successfully.');
    }

    /** Remove a class-subject-teacher assignment */
    public function unassign(Request $request)
    {
        $request->validate(['pivot_id' => 'required|integer']);

        DB::table('school_class_subject_teacher')->where('id', $request->pivot_id)->delete();

        return back()->with('success', 'Assignment removed.');
    }
}
