<?php

namespace App\Http\Controllers;

use App\Imports\ResultsImport;
use App\Imports\ResultPrepareImport;
use App\Result;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Validator;
use Illuminate\Http\Request;
use Redirect;
use Response;

class ResultController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
        ini_set('max_execution_time', 1200);
        ini_set('memory_limit', '2048M');
        ini_set('max_input_time', 1200);
        ini_set('post_max_size', '500M');
        ini_set('upload_max_filesize', '500M');
    }

    public function admin_dashboard()
    {
        if (!Auth::user()->hasAnyRole(['super-admin', 'admin', 'Super Admin', 'Admin'])) {
            return redirect()->route('student.dashboard');
        }

        $userCount = \Illuminate\Support\Facades\Cache::remember('admin_user_count', 86400, function () {
            return \App\User::count();
        });

        $resultCount = \Illuminate\Support\Facades\Cache::remember('admin_result_count', 86400, function () {
            try {
                $res = \Illuminate\Support\Facades\DB::select("SELECT table_rows FROM information_schema.tables WHERE table_name = 'results'");
                if (!empty($res) && isset($res[0]->table_rows) && $res[0]->table_rows > 0) {
                    return (int) $res[0]->table_rows;
                }
            } catch (\Exception $e) {}
            return \Illuminate\Support\Facades\DB::table('results')->count();
        });

        $roleCount = \Illuminate\Support\Facades\Cache::remember('admin_role_count', 86400, function () {
            return \Spatie\Permission\Models\Role::count();
        });

        $unreadMessagesCount = function_exists('getAdminUnreadMessages') ? count(getAdminUnreadMessages()) : 0;

        return view('admin.pages.dashboard', compact('userCount', 'resultCount', 'roleCount', 'unreadMessagesCount'));
    }

    public function student_dashboard()
    {
        $matric = Auth::user()->username;

        $sessions = DB::table('results')
            ->where([
                ['matric', $matric],
                ['is_show', 1]
            ])
             ->select('session')
            ->distinct()->get();

        $results = DB::table('results')
            ->select('matric', 'session', 'semester')
            ->groupBy('session', 'semester')
            ->where([
                ['matric', $matric],
                ['is_show', 1]
            ])
            ->get();

        $session_count = $sessions->count();

        $intDiv = intdiv($session_count, 4);

        $last_row_columns = fmod($session_count, 4);

        $i = 0;
        $vect_sessions = array();
        foreach ($sessions as $session) {
            $vect_sessions[$i++] = $session->session;
        }

        rsort($vect_sessions);

        $count = 0;
        $rows = 0;
        $mat_sessions = array();
        for ($i = 1; $i <= $intDiv; $i++) {
            for ($j = 0; $j < 4; $j++) {
                $mat_sessions[$rows][$j] = $vect_sessions[$count++];
            }
            $rows++;
        }

        if ($last_row_columns > 0) {
            for ($j = 0; $j < $last_row_columns; $j++) {
                $mat_sessions[$rows][$j] = $vect_sessions[$count++];
            }
        }

        return view('student.dashboard', compact('results', 'sessions', 'session_count', 'mat_sessions'));

    }

    /**
     * Display the specified resource.
     *
     * @param  int $id
     * @return \Illuminate\Http\Response
     */
    public function show1()
    {

        //$payslip = Payslip::find($id);

        return view('student.result1');
    }

    public function show2()
    {

        //$payslip = Payslip::find($id);

        return view('student.result2');
    }

    public function show($session, $semester)
    {
        $matric = Auth::user()->username;
        $grades = Result::where([
            ['matric', $matric],
            ['session', $session],
            ['semester', $semester],
            ['is_show', 1]
        ])->orderBy('course_code', 'asc')
            ->get();

        $record = Result::where([
            ['matric', $matric],
            ['session', $session],
            ['semester', $semester],
            ['is_show', 1]
        ])->first();

        return view('student.result', compact('grades', 'record'));
    }

    public function uploadindex()
    {
        if (!Auth::user()->hasAnyRole(['super-admin', 'admin', 'Super Admin', 'Admin'])) {
            return redirect()->route('student.dashboard');
        }

        return view('admin.pages.uploadresults');
    }

    public function import()
    {

        $validator = Validator::make(request()->all(), [
            'file' => 'required'
        ]);

        if ($validator->fails()) {
            return response()->json(['type' => 'error', 'errors'=>$validator->errors()->all()]);
        }else{
            Excel::import(new ResultPrepareImport,request()->file('file'));
            Excel::import(new ResultsImport,request()->file('file'));
            return response()->json(['type' => 'success', 'message' => 'Users uploaded successfully.']);
        }
    }

    public function manage_results(Request $request){
        if (!Auth::user()->hasAnyRole(['super-admin', 'admin', 'Super Admin', 'Admin'])) {
            if ($request->ajax()) {
                return response()->json(['type' => 'error', 'message' => 'Unauthorized action.'], 403);
            }
            return redirect()->route('student.dashboard');
        }

        if ($request->ajax()) {
            $dept = $request->department_name;
            $degree = $request->degree_name;
            $session = $request->resultsession;
            $semester = $request->semester;

            $query = DB::table('results')
                ->where('session', $session)
                ->where('semester', $semester);

            if ($dept && $dept !== '%') {
                $query->where('department_name', $dept);
            }
            if ($degree && $degree !== '%') {
                $query->where('degree_name', $degree);
            }

            $students = $query->select(
                    'matric', 
                    'name', 
                    'degree_name', 
                    'department_name', 
                    DB::raw('MAX(is_show) as is_show'),
                    DB::raw('COUNT(*) as total_courses'),
                    DB::raw('SUM(CASE WHEN is_show = 1 THEN 1 ELSE 0 END) as published_courses')
                )
                ->groupBy('matric', 'name', 'degree_name', 'department_name')
                ->get();

            $totalStudents = $students->count();
            $publishedStudents = $students->where('is_show', 1)->count();
            $unpublishedStudents = $totalStudents - $publishedStudents;

            $view = view('admin.pages.load_result_data', compact(
                'students', 'totalStudents', 'publishedStudents', 'unpublishedStudents', 'session', 'semester', 'dept', 'degree'
            ))->render();

            return Response::json([
                'html' => $view,
                'total' => $totalStudents,
                'published' => $publishedStudents,
                'unpublished' => $unpublishedStudents
            ]);
        }

        return view('admin.pages.manageresults');
    }

    public function loadFaculties()
    {

        $faculties = DB::table('faculties')->select('id', 'name')
            ->distinct()
            ->where('IsOtherInstitutes', 2)->get();
        $result = "";

        if ($faculties->count() > 1)
            $result .= "<option value='%'>All Faculties</option>";

        foreach($faculties as $faculty) {
            $result .= "<option value='" . $faculty->id . "'>$faculty->name</option>";
        }
        return $result;
    }

    public function loadDepartments(Request $request) {
        $departments = DB::table('departments')
            ->join('faculties', 'departments.facultyid', '=', 'faculties.id')
            ->where([
                ['departments.facultyid', 'like' ,$request->facultyid],
                ['IsOtherInstitutes', '=', 2],
                ['depttype', '=', 2]
            ])
            ->select('departments.id', 'departments.name')
            ->distinct()
            ->orderBy('departments.name', 'asc')
            ->get();

        $result = "";

        if ($departments->count() > 1)
            $result .= "<option value='%'>All Departments</option>";

        foreach($departments as $department) {
            $result .= "<option value='" . $department->id . "'>$department->name</option>";
        }
        return $result;

    }

    public function loadProgrammeTypes(Request $request) {

        $programme_types = DB::table('programme_types')
       ->join('programmes', 'programme_types.id', '=', 'programmes.programmetypeid')
       ->select('programme_types.id', 'programme_types.progtypename')
            ->distinct()
            ->where('programmes.departmentid', 'like', $request->departmentid)
            ->orderBy('progtypename', 'asc')
            ->get();

        $result = "";

        if ($programme_types->count() > 1)
            $result .= "<option value='%'>All Programme Types</option>";

        foreach($programme_types as $programme_type) {
            $result .= "<option value='" . $programme_type->id . "'>$programme_type->progtypename</option>";
        }
        return $result;
    }

   public function loadProgrammes(Request $request) {

        $programmes = DB::table('programmes')
           ->join('departments', 'programmes.departmentid', '=', 'departments.id')
           ->where([
               ['departments.facultyid', 'like' ,$request->facultyid],
               ['programmes.departmentid', 'like', $request->departmentid],
               ['programmes.programmetypeid', 'like', $request->programmetypeid]
           ])
           ->select('programmes.id', 'programmes.name')
            ->distinct()
            ->orderBy('name', 'asc')
           ->get();

       $result = "";

       if ($programmes->count() > 1)
           $result .= "<option value='%'>All Programme Types</option>";

       foreach($programmes as $programme) {
           $result .= "<option value='" . $programme->id . "'>$programme->name</option>";
       }
       return $result;

    }

    public function filter_sessions(){
        return \Illuminate\Support\Facades\Cache::remember('results_filter_sessions', 600, function () {
            $sessions = DB::table('results')
                ->select('session')
                ->distinct()
                ->orderBy('session', 'asc')
                ->get();

            $result = "";
            foreach ($sessions as $session) {
                $result .= "<option value='" . $session->session . "'>".$session->session."/".($session->session+1)."</option>";
            }

            return $result;
        });
    }

    public function change_result_status(Request $request){
        $dept = $request->department_name;
        $degree = $request->degree_name;
        $session = $request->resultsession;
        $semester = $request->semester;
        $action = $request->action;

        $query = Result::where('session', $session)->where('semester', $semester);

        if ($dept && $dept !== '%') {
            $query->where('department_name', $dept);
        }
        if ($degree && $degree !== '%') {
            $query->where('degree_name', $degree);
        }

        if ($action === 'publish_all') {
            $query->update(['is_show' => 1]);
            return Response::json(['type' => 'success', 'message' => 'All student results in this cohort have been successfully published.']);
        } elseif ($action === 'unpublish_all') {
            $query->update(['is_show' => 0]);
            return Response::json(['type' => 'success', 'message' => 'All student results in this cohort have been unpublished.']);
        }

        // Standard checkbox selection update scoped to selected department/degree
        $query->update(['is_show' => 0]);

        if (is_array($request->arrstudentid) && count($request->arrstudentid)) {
            $pubQuery = Result::whereIn('matric', $request->arrstudentid)
                ->where('session', $session)
                ->where('semester', $semester);

            if ($dept && $dept !== '%') {
                $pubQuery->where('department_name', $dept);
            }
            if ($degree && $degree !== '%') {
                $pubQuery->where('degree_name', $degree);
            }

            $pubQuery->update(['is_show' => 1]);
        }

        return Response::json(['type' => 'success', 'message' => 'Publication status updated successfully.']);
    }

    public function get_departments(){
        return \Illuminate\Support\Facades\Cache::remember('results_filter_departments', 600, function () {
            $departments = DB::table('results')->select('department_name')->distinct()->get();
            $result = "";

            if ($departments->count() > 1)
                $result .= "<option value='%'>All Departments</option>";

            foreach ($departments as $department) {
                $result .= "<option value='" . $department->department_name . "'>".$department->department_name."</option>";
            }

            return $result;
        });
    }

    public function get_programmes(Request $request){
        $cacheKey = 'results_filter_prog_' . md5($request->department_name ?? '');
        return \Illuminate\Support\Facades\Cache::remember($cacheKey, 600, function () use ($request) {
            $query = DB::table('results')->select('degree_name')->distinct();
            if ($request->department_name && $request->department_name !== '%') {
                $query->where('department_name', $request->department_name);
            }
            $programmes = $query->get();

            $result = "";

            if ($programmes->count() > 1)
                $result .= "<option value='%'>All Programmes</option>";

            foreach ($programmes as $programme) {
                $result .= "<option value='" . $programme->degree_name . "'>".$programme->degree_name."</option>";
            }

            return $result;
        });
    }

    /**
     * Display a student's full Statement of Result across all semesters for Administrative audit.
     *
     * @param Request $request
     * @return \Illuminate\View\View
     */
    public function admin_student_statement(Request $request)
    {
        if (!Auth::user()->hasAnyRole(['super-admin', 'admin', 'Super Admin', 'Admin'])) {
            return redirect()->route('student.dashboard');
        }

        $matric = trim($request->input('matric', ''));
        $student = null;
        $semesterBlocks = [];
        $unpublishedCount = 0;
        $publishedCount = 0;

        if ($matric !== '') {
            // Retrieve student details from results table
            $student = DB::table('results')
                ->where('matric', $matric)
                ->select('matric', 'name', 'degree_name', 'department_name', 'level', 'session')
                ->first();

            if (!$student) {
                // Fallback to users table if student exists but has no result entries
                $userObj = DB::table('users')->where('username', $matric)->first();
                if ($userObj) {
                    $student = (object)[
                        'matric' => $userObj->username,
                        'name' => trim(($userObj->last_name ?? '') . ' ' . ($userObj->middle_name ?? '') . ' ' . ($userObj->first_name ?? '')),
                        'degree_name' => 'N/A',
                        'department_name' => 'N/A',
                        'level' => 'N/A',
                        'session' => 'N/A'
                    ];
                }
            }

            if ($student) {
                // Calculate unpublished vs published counts
                $unpublishedCount = DB::table('results')
                    ->where([['matric', $matric], ['is_show', 0]])
                    ->count();

                $publishedCount = DB::table('results')
                    ->where([['matric', $matric], ['is_show', 1]])
                    ->count();

                // Retrieve all distinct registered sessions and semesters for this student
                $distinctSemesters = DB::table('results')
                    ->where('matric', $matric)
                    ->select('session', 'semester')
                    ->groupBy('session', 'semester')
                    ->orderBy('session', 'asc')
                    ->orderBy('semester', 'asc')
                    ->get();

                foreach ($distinctSemesters as $sem) {
                    $grades = DB::table('results')
                        ->where([
                            ['matric', $matric],
                            ['session', $sem->session],
                            ['semester', $sem->semester]
                        ])
                        ->orderBy('course_code', 'asc')
                        ->get();

                    $record = DB::table('results')
                        ->where([
                            ['matric', $matric],
                            ['session', $sem->session],
                            ['semester', $sem->semester]
                        ])
                        ->first();

                    $semesterBlocks[] = [
                        'session' => $sem->session,
                        'semester' => $sem->semester,
                        'grades' => $grades,
                        'record' => $record
                    ];
                }
            }
        }

        if ($request->has('print')) {
            return view('admin.pages.student_statement_print', compact('matric', 'student', 'semesterBlocks', 'unpublishedCount', 'publishedCount'));
        }

        return view('admin.pages.student_statement', compact('matric', 'student', 'semesterBlocks', 'unpublishedCount', 'publishedCount'));
    }

    /**
     * Publish a single result item by ID.
     */
    public function publish_single_result(Request $request)
    {
        $id = $request->input('id');
        if ($id) {
            DB::table('results')->where('id', $id)->update(['is_show' => 1]);
            \Illuminate\Support\Facades\Cache::forget('admin_result_count');
            return response()->json(['success' => true, 'message' => 'Result published successfully.']);
        }
        return response()->json(['success' => false, 'message' => 'Invalid result ID.'], 400);
    }

    /**
     * Publish all unpublished results for a student.
     */
    public function publish_student_results(Request $request)
    {
        $matric = trim($request->input('matric', ''));
        if ($matric !== '') {
            $updated = DB::table('results')->where('matric', $matric)->where('is_show', 0)->update(['is_show' => 1]);
            \Illuminate\Support\Facades\Cache::forget('admin_result_count');
            return response()->json(['success' => true, 'message' => "Published {$updated} result record(s) for {$matric}."]);
        }
        return response()->json(['success' => false, 'message' => 'Invalid registration number.'], 400);
    }

}
