<?php

namespace App\Http\Controllers;

use App\Exports\StudentsExport;
use App\Exports\TeachersExport;
use App\Exports\UsersExport;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use Inertia\Response;
use Maatwebsite\Excel\Facades\Excel;
use Symfony\Component\HttpFoundation\BinaryFileResponse;
use Symfony\Component\HttpFoundation\StreamedResponse;

class ExportController extends Controller
{
    public function index(): Response
    {
        return Inertia::render('Exports/ExportCenter', [
            'exports' => [
                [
                    'title' => 'دانش‌آموزان',
                    'description' => 'پرونده، پایه و شماره‌های تماس',
                    'csvHref' => route('school.exports.students'),
                    'xlsxHref' => route('school.exports.students.xlsx'),
                ],
                [
                    'title' => 'معلمان',
                    'description' => 'اطلاعات معلم‌ها و تخصص‌ها',
                    'csvHref' => route('school.exports.teachers'),
                    'xlsxHref' => route('school.exports.teachers.xlsx'),
                ],
                [
                    'title' => 'کاربران',
                    'description' => 'لیست کاربران مدرسه با نقش‌ها',
                    'csvHref' => route('school.exports.users'),
                    'xlsxHref' => route('school.exports.users.xlsx'),
                ],
                [
                    'title' => 'حضور و غیاب CSV',
                    'description' => 'رکوردهای حضور/غیاب کل مدرسه',
                    'csvHref' => route('school.exports.attendance'),
                    'xlsxHref' => null,
                ],
                [
                    'title' => 'مصرف SMS',
                    'description' => 'کیف پول و تراکنش‌های پیامک',
                    'csvHref' => route('school.exports.sms'),
                    'xlsxHref' => null,
                ],
                [
                    'title' => 'نتایج رأی‌گیری',
                    'description' => 'کاندیداها، تعداد رأی و درصد',
                    'csvHref' => route('school.exports.voting'),
                    'xlsxHref' => null,
                ],
            ],
        ]);
    }

    public function usersCsv(Request $request): StreamedResponse
    {
        $schoolId = $this->currentSchoolId($request);

        $rows = DB::table('users')
            ->where('school_id', $schoolId)
            ->orderBy('last_name')
            ->get(['username', 'first_name', 'last_name', 'mobile', 'email', 'role_key', 'status']);

        return $this->csv('users.csv', ['username', 'first_name', 'last_name', 'mobile', 'email', 'role_key', 'status'], $rows);
    }

    public function studentsCsv(Request $request): StreamedResponse
    {
        $schoolId = $this->currentSchoolId($request);

        $rows = DB::table('students')
            ->leftJoin('grade_levels', 'grade_levels.id', '=', 'students.grade_level_id')
            ->where('students.school_id', $schoolId)
            ->orderBy('students.last_name')
            ->get([
                'students.student_code',
                'students.first_name',
                'students.last_name',
                'students.mobile',
                'students.father_mobile',
                'students.mother_mobile',
                'students.home_phone',
                'grade_levels.name_fa as grade',
                'students.status',
            ]);

        return $this->csv('students.csv', ['student_code', 'first_name', 'last_name', 'mobile', 'father_mobile', 'mother_mobile', 'home_phone', 'grade', 'status'], $rows);
    }

    public function teachersCsv(Request $request): StreamedResponse
    {
        $schoolId = $this->currentSchoolId($request);

        $rows = DB::table('teachers')
            ->where('school_id', $schoolId)
            ->orderBy('last_name')
            ->get(['teacher_code', 'first_name', 'last_name', 'mobile', 'email', 'specialty', 'status']);

        return $this->csv('teachers.csv', ['teacher_code', 'first_name', 'last_name', 'mobile', 'email', 'specialty', 'status'], $rows);
    }

    public function smsUsageCsv(Request $request): StreamedResponse
    {
        $schoolId = $this->currentSchoolId($request);

        $rows = DB::table('sms_wallet_transactions')
            ->where('school_id', $schoolId)
            ->latest('created_at')
            ->get(['type', 'amount', 'balance_before', 'balance_after', 'description', 'created_at']);

        return $this->csv('sms-usage.csv', ['type', 'amount', 'balance_before', 'balance_after', 'description', 'created_at'], $rows);
    }

    public function votingResultsCsv(Request $request): StreamedResponse
    {
        $schoolId = $this->currentSchoolId($request);

        $rows = DB::table('voting_candidates')
            ->leftJoin('voting_votes', 'voting_votes.candidate_id', '=', 'voting_candidates.id')
            ->join('votings', 'votings.id', '=', 'voting_candidates.voting_id')
            ->where('voting_candidates.school_id', $schoolId)
            ->groupBy('votings.title', 'voting_candidates.display_name')
            ->select(['votings.title as voting_title', 'voting_candidates.display_name', DB::raw('COUNT(voting_votes.id) as votes_count')])
            ->orderBy('votings.title')
            ->orderByDesc('votes_count')
            ->get();

        return $this->csv('voting-results.csv', ['voting_title', 'candidate', 'votes_count'], $rows);
    }

    public function attendanceCsv(Request $request): StreamedResponse
    {
        $schoolId = $this->currentSchoolId($request);

        $rows = DB::table('attendance_records')
            ->join('attendance_sessions', 'attendance_sessions.id', '=', 'attendance_records.attendance_session_id')
            ->join('students', 'students.id', '=', 'attendance_records.student_id')
            ->leftJoin('classrooms', 'classrooms.id', '=', 'attendance_sessions.classroom_id')
            ->leftJoin('subjects', 'subjects.id', '=', 'attendance_sessions.subject_id')
            ->where('attendance_records.school_id', $schoolId)
            ->orderByDesc('attendance_sessions.date')
            ->limit(10000)
            ->get([
                'attendance_sessions.date',
                'attendance_sessions.date_jalali',
                'classrooms.name as classroom_name',
                'subjects.name_fa as subject_name',
                'students.first_name',
                'students.last_name',
                'students.student_code',
                'attendance_records.status',
                'attendance_records.delay_minutes',
                'attendance_records.note',
            ]);

        return $this->csv('attendance.csv', ['date', 'date_jalali', 'classroom_name', 'subject_name', 'first_name', 'last_name', 'student_code', 'status', 'delay_minutes', 'note'], $rows);
    }

    public function studentsXlsx(Request $request): BinaryFileResponse
    {
        $schoolId = $this->currentSchoolId($request);
        $rows = collect(DB::table('students')
            ->leftJoin('grade_levels', 'grade_levels.id', '=', 'students.grade_level_id')
            ->where('students.school_id', $schoolId)
            ->orderBy('students.last_name')
            ->get(['students.student_code', 'students.first_name', 'students.last_name', 'students.mobile', 'students.father_mobile', 'students.mother_mobile', 'students.home_phone', 'grade_levels.name_fa as grade', 'students.status']));

        return Excel::download(new StudentsExport($rows), 'students.xlsx');
    }

    public function teachersXlsx(Request $request): BinaryFileResponse
    {
        $schoolId = $this->currentSchoolId($request);
        $rows = collect(DB::table('teachers')
            ->where('school_id', $schoolId)
            ->orderBy('last_name')
            ->get(['teacher_code', 'first_name', 'last_name', 'mobile', 'email', 'specialty', 'status']));

        return Excel::download(new TeachersExport($rows), 'teachers.xlsx');
    }

    public function usersXlsx(Request $request): BinaryFileResponse
    {
        $schoolId = $this->currentSchoolId($request);
        $rows = collect(DB::table('users')
            ->where('school_id', $schoolId)
            ->orderBy('last_name')
            ->get(['username', 'first_name', 'last_name', 'mobile', 'email', 'role_key', 'status']));

        return Excel::download(new UsersExport($rows), 'users.xlsx');
    }

    public function schoolZip(Request $request): \Symfony\Component\HttpFoundation\Response
    {
        $schoolId = $this->currentSchoolId($request);
        abort_unless($schoolId, 422);

        $school = DB::table('schools')->where('id', $schoolId)->first(['name_fa', 'school_code']);
        $slug = $school ? preg_replace('/[^a-zA-Z0-9_\-]/', '', $school->school_code ?? 'school') : 'school';
        if (! $slug) {
            $slug = 'school';
        }

        $tmpDir = sys_get_temp_dir().'/daneshyar_export_'.$schoolId.'_'.time();
        mkdir($tmpDir, 0700, true);

        try {
            $this->writeCsvFile($tmpDir.'/students.csv',
                ['student_code', 'first_name', 'last_name', 'mobile', 'father_mobile', 'mother_mobile', 'home_phone', 'grade', 'status'],
                DB::table('students')
                    ->leftJoin('grade_levels', 'grade_levels.id', '=', 'students.grade_level_id')
                    ->where('students.school_id', $schoolId)
                    ->get(['students.student_code', 'students.first_name', 'students.last_name', 'students.mobile', 'students.father_mobile', 'students.mother_mobile', 'students.home_phone', 'grade_levels.name_fa as grade', 'students.status'])
            );

            $this->writeCsvFile($tmpDir.'/teachers.csv',
                ['teacher_code', 'first_name', 'last_name', 'mobile', 'email', 'specialty', 'status'],
                DB::table('teachers')->where('school_id', $schoolId)->get(['teacher_code', 'first_name', 'last_name', 'mobile', 'email', 'specialty', 'status'])
            );

            $this->writeCsvFile($tmpDir.'/users.csv',
                ['username', 'first_name', 'last_name', 'mobile', 'email', 'role_key', 'status'],
                DB::table('users')->where('school_id', $schoolId)->get(['username', 'first_name', 'last_name', 'mobile', 'email', 'role_key', 'status'])
            );

            $this->writeCsvFile($tmpDir.'/attendance.csv',
                ['date', 'date_jalali', 'classroom', 'subject', 'student_code', 'first_name', 'last_name', 'status', 'delay_minutes'],
                DB::table('attendance_records')
                    ->join('attendance_sessions', 'attendance_sessions.id', '=', 'attendance_records.attendance_session_id')
                    ->join('students', 'students.id', '=', 'attendance_records.student_id')
                    ->leftJoin('classrooms', 'classrooms.id', '=', 'attendance_sessions.classroom_id')
                    ->leftJoin('subjects', 'subjects.id', '=', 'attendance_sessions.subject_id')
                    ->where('attendance_records.school_id', $schoolId)
                    ->orderByDesc('attendance_sessions.date')
                    ->limit(50000)
                    ->get(['attendance_sessions.date', 'attendance_sessions.date_jalali', 'classrooms.name as classroom', 'subjects.name_fa as subject', 'students.student_code', 'students.first_name', 'students.last_name', 'attendance_records.status', 'attendance_records.delay_minutes'])
            );

            $this->writeCsvFile($tmpDir.'/grades.csv',
                ['student_code', 'first_name', 'last_name', 'subject', 'classroom', 'score', 'max_score', 'status'],
                DB::table('grades')
                    ->join('students', 'students.id', '=', 'grades.student_id')
                    ->leftJoin('subjects', 'subjects.id', '=', 'grades.subject_id')
                    ->leftJoin('classrooms', 'classrooms.id', '=', 'grades.classroom_id')
                    ->where('grades.school_id', $schoolId)
                    ->get(['students.student_code', 'students.first_name', 'students.last_name', 'subjects.name_fa as subject', 'classrooms.name as classroom', 'grades.score', 'grades.max_score', 'grades.status'])
            );

            $zipFile = sys_get_temp_dir().'/daneshyar_'.$slug.'_'.date('Ymd').'.zip';
            $zip = new \ZipArchive();
            if ($zip->open($zipFile, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
                abort(500, 'خطا در ساخت فایل ZIP.');
            }

            foreach (glob($tmpDir.'/*.csv') as $csvFile) {
                $zip->addFile($csvFile, basename($csvFile));
            }
            $zip->close();

            $response = response()->download($zipFile, 'export_school_'.date('Ymd').'.zip', [
                'Content-Type' => 'application/zip',
            ])->deleteFileAfterSend(true);

            return $response;
        } finally {
            foreach (glob($tmpDir.'/*.csv') as $f) {
                @unlink($f);
            }
            @rmdir($tmpDir);
        }
    }

    private function writeCsvFile(string $path, array $headers, iterable $rows): void
    {
        $handle = fopen($path, 'w');
        fwrite($handle, "\xEF\xBB\xBF");
        fputcsv($handle, $headers);
        foreach ($rows as $row) {
            fputcsv($handle, array_map(fn ($h) => $this->csvValue($row, $h), $headers));
        }
        fclose($handle);
    }

    private function csv(string $filename, array $headers, iterable $rows): StreamedResponse
    {
        return response()->streamDownload(function () use ($headers, $rows): void {
            $handle = fopen('php://output', 'w');
            fwrite($handle, "\xEF\xBB\xBF");
            fputcsv($handle, $headers);

            foreach ($rows as $row) {
                fputcsv($handle, array_map(fn ($header) => $this->csvValue($row, $header), $headers));
            }

            fclose($handle);
        }, $filename, ['Content-Type' => 'text/csv; charset=UTF-8']);
    }

    private function csvValue(object $row, string $header): mixed
    {
        $field = match ($header) {
            'candidate' => 'display_name',
            default => $header,
        };

        return $row->{$field} ?? '';
    }
}