<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class StudentsController extends Controller
{
    public function index(Request $request)
    {
        $q = trim((string) $request->query('q', ''));
        $gradeId = $request->query('grade_id');
        $perPage = max(5, min(100, (int) $request->query('per_page', 10)));
        $sort = (string) $request->query('sort', 'last_name');
        $dir = strtolower((string) $request->query('dir', 'asc')) === 'desc' ? 'desc' : 'asc';
        $allowedSorts = [
            'last_name' => 'students.last_name',
            'first_name' => 'students.first_name',
            'gender' => 'students.gender',
            'status' => 'students.status',
            'grade' => 'grades.name',
        ];
        $orderBy = $allowedSorts[$sort] ?? 'students.last_name';

        $query = DB::table('students')
            ->leftJoin('grades', 'students.grade_id', '=', 'grades.id')
            ->select(
                'students.id', 'students.first_name', 'students.last_name',
                'students.gender', 'students.status', 'students.qr_uid',
                'grades.name as grade_name', 'students.grade_id'
            );

        if ($q !== '') {
            $query->where(function ($w) use ($q) {
                $w->where('students.first_name', 'like', "%$q%")
                  ->orWhere('students.last_name', 'like', "%$q%")
                  ->orWhere('students.qr_uid', 'like', "%$q%");
            });
        }
        if ($gradeId) {
            $query->where('students.grade_id', (int)$gradeId);
        }

        $students = $query->orderBy($orderBy, $dir)
            ->paginate($perPage)
            ->appends(['q' => $q, 'grade_id' => $gradeId, 'per_page' => $perPage, 'sort' => $sort, 'dir' => $dir]);

        $grades = DB::table('grades')->orderBy('display_order')->get();

        return view('students.index', [
            'students' => $students,
            'grades' => $grades,
            'selectedGradeId' => $gradeId,
            'q' => $q,
            'perPage' => $perPage,
            'sort' => $sort,
            'dir' => $dir,
        ]);
    }

    public function create()
    {
        $grades = DB::table('grades')->orderBy('display_order')->get();
        return view('students.create', [ 'grades' => $grades ]);
    }

    public function store(Request $request)
    {
        $data = $request->validate([
            'first_name' => 'required|string|max:100',
            'last_name' => 'required|string|max:100',
            'gender' => 'required|in:Male,Female',
            'grade_id' => 'required|integer|exists:grades,id',
            'parent_full_name' => 'required|string|max:150',
            'parent_whatsapp' => 'required|string|max:30',
            'parent_email' => 'nullable|email|max:150',
            'status' => 'nullable|string|max:50',
        ]);

        $status = $data['status'] ?? 'active';

        return DB::transaction(function () use ($data, $status) {
            $parentId = DB::table('parent_contacts')->insertGetId([
                'full_name' => $data['parent_full_name'],
                'whatsapp_number' => $data['parent_whatsapp'],
                'email' => $data['parent_email'] ?? null,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            $uid = (string) Str::uuid();
            $qr = (string) Str::uuid();

            DB::table('students')->insert([
                'uid' => $uid,
                'first_name' => $data['first_name'],
                'last_name' => $data['last_name'],
                'gender' => $data['gender'],
                'grade_id' => $data['grade_id'],
                'parent_contact_id' => $parentId,
                'qr_uid' => $qr,
                'status' => $status,
                'created_at' => now(),
                'updated_at' => now(),
            ]);

            return redirect()->route('students.index')->with('ok', 'Student created');
        });
    }

    public function edit(int $id)
    {
        $grades = DB::table('grades')->orderBy('display_order')->get();
        $student = DB::table('students')->where('id', $id)->first();
        if (!$student) { abort(404); }
        $parent = DB::table('parent_contacts')->where('id', $student->parent_contact_id)->first();
        return view('students.edit', [ 'grades' => $grades, 'student' => $student, 'parent' => $parent ]);
    }

    public function update(Request $request, int $id)
    {
        $data = $request->validate([
            'first_name' => 'required|string|max:100',
            'last_name' => 'required|string|max:100',
            'gender' => 'required|in:Male,Female',
            'grade_id' => 'required|integer|exists:grades,id',
            'parent_full_name' => 'required|string|max:150',
            'parent_whatsapp' => 'required|string|max:30',
            'parent_email' => 'nullable|email|max:150',
            'status' => 'nullable|string|max:50',
        ]);
        $status = $data['status'] ?? 'active';

        $student = DB::table('students')->where('id', $id)->first();
        if (!$student) { abort(404); }

        return DB::transaction(function () use ($data, $status, $student) {
            DB::table('parent_contacts')->where('id', $student->parent_contact_id)->update([
                'full_name' => $data['parent_full_name'],
                'whatsapp_number' => $data['parent_whatsapp'],
                'email' => $data['parent_email'] ?? null,
                'updated_at' => now(),
            ]);

            DB::table('students')->where('id', $student->id)->update([
                'first_name' => $data['first_name'],
                'last_name' => $data['last_name'],
                'gender' => $data['gender'],
                'grade_id' => $data['grade_id'],
                'status' => $status,
                'updated_at' => now(),
            ]);

            return redirect()->route('students.index')->with('ok', 'Student updated');
        });
    }

    public function exportCsv()
    {
        $filename = 'students_' . now()->format('Ymd_His') . '.csv';
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];

        $callback = function () {
            $out = fopen('php://output', 'w');
            // header row
            fputcsv($out, ['first_name','last_name','gender','grade','parent_full_name','parent_whatsapp','parent_email','status','qr_uid']);

            $rows = DB::table('students')
                ->leftJoin('grades','students.grade_id','=','grades.id')
                ->leftJoin('parent_contacts','students.parent_contact_id','=','parent_contacts.id')
                ->select('students.first_name','students.last_name','students.gender','grades.name as grade','parent_contacts.full_name as parent_full_name','parent_contacts.whatsapp_number as parent_whatsapp','parent_contacts.email as parent_email','students.status','students.qr_uid')
                ->orderBy('students.last_name')
                ->cursor();

            foreach ($rows as $r) {
                fputcsv($out, [
                    $r->first_name,
                    $r->last_name,
                    $r->gender,
                    $r->grade,
                    $r->parent_full_name,
                    $r->parent_whatsapp,
                    $r->parent_email,
                    $r->status,
                    $r->qr_uid,
                ]);
            }
            fclose($out);
        };

        return response()->stream($callback, 200, $headers);
    }

    public function importCsv(Request $request)
    {
        $request->validate([
            'csv_file' => 'required|file|mimes:csv,txt|max:2048',
        ]);

        $file = $request->file('csv_file');
        $handle = fopen($file->getRealPath(), 'r');
        if (!$handle) {
            return back()->withErrors(['csv_file' => 'Unable to open uploaded file.']);
        }

        // Expect header
        $header = fgetcsv($handle);
        $expected = ['first_name','last_name','gender','grade','parent_full_name','parent_whatsapp','parent_email','status'];
        if (!$header) { fclose($handle); return back()->withErrors(['csv_file' => 'Empty CSV']); }

        // Normalize header
        $map = [];
        foreach ($header as $i => $col) {
            $key = strtolower(trim($col));
            $map[$key] = $i;
        }
        foreach ($expected as $col) {
            if (!array_key_exists($col, $map)) {
                fclose($handle);
                return back()->withErrors(['csv_file' => "Missing column: $col"]);
            }
        }

        $count = 0;
        DB::beginTransaction();
        try {
            while (($row = fgetcsv($handle)) !== false) {
                if (count($row) == 1 && trim($row[0]) === '') { continue; }
                $first = trim($row[$map['first_name']] ?? '');
                $last = trim($row[$map['last_name']] ?? '');
                $gender = trim($row[$map['gender']] ?? '');
                $gradeName = trim($row[$map['grade']] ?? '');
                $pname = trim($row[$map['parent_full_name']] ?? '');
                $pwa = trim($row[$map['parent_whatsapp']] ?? '');
                $pemail = trim($row[$map['parent_email']] ?? '') ?: null;
                $status = trim($row[$map['status']] ?? 'active') ?: 'active';

                if ($first === '' || $last === '' || !in_array($gender, ['Male','Female']) || $gradeName === '' || $pname === '' || $pwa === '') {
                    continue; // skip invalid rows
                }

                // find or create grade
                $grade = DB::table('grades')->where('name', $gradeName)->first();
                if (!$grade) {
                    $display = (int) (DB::table('grades')->max('display_order') ?? 0) + 1;
                    $gradeId = DB::table('grades')->insertGetId([
                        'name' => $gradeName,
                        'display_order' => $display,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ]);
                } else { $gradeId = $grade->id; }

                $parentId = DB::table('parent_contacts')->insertGetId([
                    'full_name' => $pname,
                    'whatsapp_number' => $pwa,
                    'email' => $pemail,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                DB::table('students')->insert([
                    'uid' => (string) Str::uuid(),
                    'first_name' => $first,
                    'last_name' => $last,
                    'gender' => $gender,
                    'grade_id' => $gradeId,
                    'parent_contact_id' => $parentId,
                    'qr_uid' => (string) Str::uuid(),
                    'status' => $status,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
                $count++;
            }
            fclose($handle);
            DB::commit();
        } catch (\Throwable $e) {
            if (is_resource($handle)) { fclose($handle); }
            DB::rollBack();
            return back()->withErrors(['csv_file' => 'Import failed: '.$e->getMessage()]);
        }

        return redirect()->route('students.index')->with('ok', "Imported $count students.");
    }

    public function qrPrint(Request $request)
    {
        $grades = DB::table('grades')->orderBy('display_order')->get();
        $gradeId = $request->query('grade_id');
        $group = (bool)$request->query('group', false);

        $q = DB::table('students')
            ->leftJoin('grades','students.grade_id','=','grades.id')
            ->select('students.first_name','students.last_name','students.qr_uid','grades.name as grade_name');
        if ($gradeId) {
            $q->where('students.grade_id', (int)$gradeId);
        }
        $students = $q->orderBy('students.last_name')->get();

        return view('students.qr', [
            'grades' => $grades,
            'students' => $students,
            'selectedGradeId' => $gradeId,
            'group' => $group,
        ]);
    }

    public function destroy(int $id)
    {
        $student = DB::table('students')->where('id', $id)->first();
        if (!$student) { abort(404); }

        return DB::transaction(function () use ($student) {
            // Capture parent id before deletion
            $parentId = $student->parent_contact_id;

            // Delete student
            DB::table('students')->where('id', $student->id)->delete();

            // If parent has no other students, delete parent contact
            $hasOther = DB::table('students')->where('parent_contact_id', $parentId)->exists();
            if (!$hasOther) {
                DB::table('parent_contacts')->where('id', $parentId)->delete();
            }

            return redirect()->route('students.index')->with('ok', 'Student deleted');
        });
    }

    public function toggleStatus(int $id)
    {
        $student = DB::table('students')->where('id', $id)->first(['id','status']);
        if (!$student) { abort(404); }
        $new = $student->status === 'active' ? 'inactive' : 'active';
        DB::table('students')->where('id', $id)->update(['status' => $new, 'updated_at' => now()]);
        return redirect()->back()->with('ok', 'Status updated');
    }

    public function csvTemplate()
    {
        // samples folder lives one level up from backend_app
        $path = base_path('../samples/student_import_template.csv');
        if (!file_exists($path)) {
            abort(404, 'Template not found');
        }
        return response()->download($path, 'student_import_template.csv', [
            'Content-Type' => 'text/csv',
        ]);
    }

    public function bulkDestroy(Request $request)
    {
        $data = $request->validate([
            'ids' => 'required|array',
            'ids.*' => 'integer'
        ]);

        $ids = $data['ids'];
        if (count($ids) === 0) {
            return redirect()->back()->withErrors(['ids' => 'No students selected.']);
        }

        $backup = DB::transaction(function () use ($ids) {
            // Backup students and potentially affected parents
            $studentsRows = DB::table('students')->whereIn('id', $ids)->get()->map(function($r){ return (array)$r; })->all();
            $parentIds = collect($studentsRows)->pluck('parent_contact_id')->filter()->unique()->values();
            $parentsRows = $parentIds->count() ? DB::table('parent_contacts')->whereIn('id', $parentIds)->get()->map(fn($r)=>(array)$r)->all() : [];

            // Perform delete
            DB::table('students')->whereIn('id', $ids)->delete();
            // Delete orphan parents
            if ($parentIds->count() > 0) {
                $orphans = DB::table('parent_contacts')
                    ->whereIn('id', $parentIds)
                    ->whereNotIn('id', DB::table('students')->select('parent_contact_id'))
                    ->pluck('id');
                if ($orphans->count() > 0) {
                    DB::table('parent_contacts')->whereIn('id', $orphans)->delete();
                }
            }

            return [ 'students' => $studentsRows, 'parents' => $parentsRows ];
        });

        // Store backup in session for undo
        session(['students_last_deleted' => $backup]);
        $undoUrl = route('students.undo_delete');
        return redirect()->route('students.index')->with('ok', "Selected students deleted. <a href='$undoUrl'>Undo</a>");
    }

    public function seedDemo()
    {
        // Seed ~20 demo students across existing grades
        $grades = DB::table('grades')->orderBy('display_order')->pluck('id');
        if ($grades->count() === 0) {
            return redirect()->route('students.index')->withErrors(['seed' => 'Please create grades first.']);
        }

        $firstNames = ['Ada','Chinedu','Ifeoma','Bola','Tunde','Ngozi','Halima','Femi','Aisha','Uche','Yemi','Amaka','Kunle','Sade','Chika','Ibrahim','Taiwo','Kehinde','Zainab','Emeka'];
        $lastNames = ['Okafor','Balogun','Adewale','Olawale','Okeke','Mohammed','Ogunleye','Oshodi','Ogunbiyi','Abiola','Okon','Eze','Abubakar','Afolayan','Adeyemi'];
        $genders = ['Male','Female'];

        DB::transaction(function () use ($grades, $firstNames, $lastNames, $genders) {
            for ($i=0; $i<20; $i++) {
                $fn = $firstNames[array_rand($firstNames)];
                $ln = $lastNames[array_rand($lastNames)];
                $gender = $genders[array_rand($genders)];
                $gradeId = $grades[random_int(0, $grades->count()-1)];

                $parentId = DB::table('parent_contacts')->insertGetId([
                    'full_name' => $ln.' Parent',
                    'whatsapp_number' => '+23480'.random_int(10000000, 99999999),
                    'email' => null,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                DB::table('students')->insert([
                    'first_name' => $fn,
                    'last_name' => $ln,
                    'gender' => strtolower($gender),
                    'grade_id' => $gradeId,
                    'status' => 'active',
                    'qr_uid' => (string) Str::uuid(),
                    'parent_contact_id' => $parentId,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
        });

        return redirect()->route('students.index')->with('ok', 'Demo students added');
    }

    public function undoDelete()
    {
        $backup = session('students_last_deleted');
        if (!$backup || empty($backup['students'])) {
            return redirect()->route('students.index')->withErrors(['undo' => 'Nothing to undo.']);
        }

        DB::transaction(function () use ($backup) {
            // Restore parents first
            if (!empty($backup['parents'])) {
                foreach ($backup['parents'] as $p) {
                    // Upsert parent_contacts by id
                    $id = $p['id'];
                    unset($p['id']);
                    $exists = DB::table('parent_contacts')->where('id', $id)->exists();
                    if (!$exists) {
                        DB::table('parent_contacts')->insert(array_merge(['id' => $id], $p));
                    }
                }
            }
            // Restore students
            foreach ($backup['students'] as $s) {
                $id = $s['id'];
                unset($s['id']);
                $exists = DB::table('students')->where('id', $id)->exists();
                if (!$exists) {
                    DB::table('students')->insert(array_merge(['id' => $id], $s));
                }
            }
        });

        session()->forget('students_last_deleted');
        return redirect()->route('students.index')->with('ok', 'Deletion undone.');
    }

    public function exportAdvanced(Request $request)
    {
        $cols = $request->query('cols', ['last_name','first_name','gender','grade','status','qr_uid']);
        $allowed = [
            'first_name' => 'students.first_name',
            'last_name' => 'students.last_name',
            'gender' => 'students.gender',
            'status' => 'students.status',
            'qr_uid' => 'students.qr_uid',
            'grade' => 'grades.name as grade',
        ];
        $selects = [];
        foreach ($cols as $c) { if (isset($allowed[$c])) $selects[] = $allowed[$c]; }
        if (empty($selects)) { $selects = [$allowed['last_name'], $allowed['first_name']]; }

        $q = trim((string) $request->query('q', ''));
        $gradeId = $request->query('grade_id');

        $query = DB::table('students')
            ->leftJoin('grades','students.grade_id','=','grades.id')
            ->selectRaw(implode(',', $selects));
        if ($q !== '') {
            $query->where(function ($w) use ($q) {
                $w->where('students.first_name', 'like', "%$q%")
                  ->orWhere('students.last_name', 'like', "%$q%")
                  ->orWhere('students.qr_uid', 'like', "%$q%");
            });
        }
        if ($gradeId) { $query->where('students.grade_id', (int)$gradeId); }

        $rows = $query->orderBy('students.last_name')->get();

        $filename = 'students_export_'.date('Ymd_His').'.csv';
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"$filename\"",
        ];
        $callback = function () use ($rows, $cols) {
            $out = fopen('php://output','w');
            fputcsv($out, $cols);
            foreach ($rows as $r) {
                $line = [];
                foreach ($cols as $c) { $line[] = $r->$c ?? ''; }
                fputcsv($out, $line);
            }
            fclose($out);
        };
        return response()->stream($callback, 200, $headers);
    }
}
