<?php

namespace App\Http\Controllers\Superadmin;

use App\Http\Controllers\Controller;
use App\Models\School;
use App\Models\Kcd;
use App\Models\KabKota;
use App\Models\User;
use App\Models\Role;
use App\Models\Employee;
use App\Models\EmployeeAssignment;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\Log;

class SchoolController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index()
    {
        $schools = School::with('kcd', 'kabKota')->orderBy('name')->get();
        return view('superadmin.schools.index', compact('schools'));
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        $kcds = Kcd::where('is_active', true)->orderBy('name')->get();
        $kabKotas = KabKota::where('is_active', true)->orderBy('name')->get();
        return view('superadmin.schools.create', compact('kcds', 'kabKotas'));
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        $request->validate([
            'kcd_id' => 'required|exists:kcds,id',
            'kab_kota_id' => 'nullable|exists:kab_kota,id',
            'npsn' => 'required|string|max:20|unique:schools,npsn',
            'name' => 'required|string|max:200',
            'level' => 'required|in:PAUD,SD,SMP,SMA,SMK,SLB',
            'status' => 'required|in:Negeri,Swasta',
            'address' => 'nullable|string',
            'phone' => 'nullable|string|max:20',
            'email' => 'nullable|email|max:100',
            'principal_name' => 'nullable|string|max:100',
            'principal_nip' => 'nullable|string|max:20',
            'principal_phone' => 'nullable|string|max:20',
            'has_uld' => 'nullable|boolean',
            'uld_count' => 'nullable|integer|min:0|required_if:has_uld,1',
        ], [
            'kcd_id.required' => 'KCD harus dipilih',
            'kcd_id.exists' => 'KCD tidak valid',
            'kab_kota_id.exists' => 'Kab/Kota tidak valid',
            'npsn.required' => 'NPSN harus diisi',
            'npsn.unique' => 'NPSN sudah terdaftar',
            'name.required' => 'Nama sekolah harus diisi',
            'level.required' => 'Jenjang harus dipilih',
            'status.required' => 'Status harus dipilih',
        ]);

        DB::beginTransaction();
        try {
            // Create school
            $school = School::create([
                'kcd_id' => $request->kcd_id,
                'kab_kota_id' => $request->kab_kota_id,
                'npsn' => $request->npsn,
                'name' => $request->name,
                'level' => $request->level,
                'status' => $request->status,
                'address' => $request->address,
                'phone' => $request->phone,
                'email' => $request->email,
                'principal_name' => $request->principal_name,
                'principal_nip' => $request->principal_nip,
                'principal_phone' => $request->principal_phone,
                'has_uld' => $request->has_uld ? true : false,
                'uld_count' => $request->has_uld ? $request->uld_count : null,
                'is_active' => true,
            ]);

            // Auto-create user admin_sekolah
            $this->createAdminSekolahUser($school);

            DB::commit();

            return redirect()->route('superadmin.schools.index')
                ->with('success', "Sekolah \"{$school->name}\" berhasil ditambahkan dan akun admin sekolah telah dibuat (Username: {$school->npsn}, Password: {$school->npsn})");
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Error creating school', ['error' => $e->getMessage()]);
            return redirect()->route('superadmin.schools.index')
                ->with('error', 'Gagal menambahkan sekolah: ' . $e->getMessage());
        }
    }

    /**
     * Create admin sekolah user automatically
     */
    private function createAdminSekolahUser(School $school)
    {
        try {
            // Get admin_sekolah role
            $role = Role::where('slug', 'admin_sekolah')->first();
            
            if (!$role) {
                throw new \Exception('Role admin_sekolah tidak ditemukan');
            }

            // Prepare email (use school email or generate default)
            $email = $school->email ?: $school->npsn . '@sekolah.local';

            // Check if user already exists with this email
            $existingUser = User::where('email', $email)->first();
            
            if ($existingUser) {
                Log::warning('User already exists for this email', ['email' => $email]);
                return;
            }

            // Create user
            $user = User::create([
                'role_id' => $role->id,
                'school_id' => $school->id,
                'npsn' => $school->npsn,
                'name' => 'Admin ' . $school->name,
                'email' => $email,
                'password' => Hash::make($school->npsn), // Password = NPSN
                'nip' => $school->principal_nip ?: null,
                'phone' => $school->phone,
                'is_active' => true,
            ]);

            // Create employee record
            $employee = Employee::create([
                'name' => 'Admin ' . $school->name,
                'nip' => $school->principal_nip ?: null,
                'phone' => $school->phone,
                'email' => $email,
                'is_active' => true,
            ]);

            // Link user to employee
            $user->update(['employee_id' => $employee->id]);

            // Create assignment
            EmployeeAssignment::create([
                'employee_id' => $employee->id,
                'assignment_type' => EmployeeAssignment::TYPE_SCHOOL,
                'school_id' => $school->id,
                'start_date' => now(),
                'notes' => 'Admin Sekolah',
                'is_active' => true,
            ]);

            Log::info('Admin sekolah user created successfully', [
                'school_id' => $school->id,
                'user_id' => $user->id,
                'email' => $email
            ]);

        } catch (\Exception $e) {
            Log::error('Error creating admin sekolah user', [
                'school_id' => $school->id,
                'error' => $e->getMessage()
            ]);
            throw $e;
        }
    }

    /**
     * Display the specified resource.
     */
    public function show($id)
    {
        $school = School::with('kcd', 'supervisorAssignments')->findOrFail($id);
        return view('superadmin.schools.show', compact('school'));
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function edit($id)
    {
        $school = School::findOrFail($id);
        $kcds = Kcd::where('is_active', true)->orderBy('name')->get();
        $kabKotas = KabKota::where('is_active', true)->orderBy('name')->get();
        return view('superadmin.schools.edit', compact('school', 'kcds', 'kabKotas'));
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, $id)
    {
        $school = School::findOrFail($id);

        $request->validate([
            'kcd_id' => 'required|exists:kcds,id',
            'kab_kota_id' => 'nullable|exists:kab_kota,id',
            'npsn' => 'required|string|max:20|unique:schools,npsn,' . $school->id,
            'name' => 'required|string|max:200',
            'level' => 'required|in:PAUD,SD,SMP,SMA,SMK,SLB',
            'status' => 'required|in:Negeri,Swasta',
            'address' => 'nullable|string',
            'phone' => 'nullable|string|max:20',
            'email' => 'nullable|email|max:100',
            'principal_name' => 'nullable|string|max:100',
            'principal_nip' => 'nullable|string|max:20',
            'principal_phone' => 'nullable|string|max:20',
            'has_uld' => 'nullable|boolean',
            'uld_count' => 'nullable|integer|min:0|required_if:has_uld,1',
            'is_active' => 'required|boolean',
        ], [
            'kcd_id.required' => 'KCD harus dipilih',
            'kcd_id.exists' => 'KCD tidak valid',
            'npsn.required' => 'NPSN harus diisi',
            'npsn.unique' => 'NPSN sudah terdaftar',
            'name.required' => 'Nama sekolah harus diisi',
            'level.required' => 'Jenjang harus dipilih',
            'status.required' => 'Status harus dipilih',
        ]);

        DB::beginTransaction();
        try {
            $oldNpsn = $school->npsn;
            
            $school->update([
                'kcd_id' => $request->kcd_id,
                'kab_kota_id' => $request->kab_kota_id,
                'npsn' => $request->npsn,
                'name' => $request->name,
                'level' => $request->level,
                'status' => $request->status,
                'address' => $request->address,
                'phone' => $request->phone,
                'email' => $request->email,
                'principal_name' => $request->principal_name,
                'principal_nip' => $request->principal_nip,
                'principal_phone' => $request->principal_phone,
                'has_uld' => $request->has_uld ? true : false,
                'uld_count' => $request->has_uld ? $request->uld_count : null,
                'is_active' => $request->is_active,
            ]);

            // Update user admin sekolah if NPSN changed
            if ($oldNpsn !== $request->npsn) {
                $this->updateAdminSekolahPassword($school, $oldNpsn);
            }

            DB::commit();

            return redirect()->route('superadmin.schools.index')
                ->with('success', "Sekolah \"{$school->name}\" berhasil diperbarui");
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Error updating school', ['error' => $e->getMessage()]);
            return redirect()->route('superadmin.schools.index')
                ->with('error', 'Gagal memperbarui sekolah: ' . $e->getMessage());
        }
    }

    /**
     * Update admin sekolah password when NPSN changes
     */
    private function updateAdminSekolahPassword(School $school, $oldNpsn)
    {
        try {
            // Find user by old email (oldNPSN@sekolah.local)
            $oldEmail = $school->email ?: $oldNpsn . '@sekolah.local';
            
            $user = User::where('email', $oldEmail)
                ->whereHas('role', function($q) {
                    $q->where('slug', 'admin_sekolah');
                })
                ->whereHas('employee.activeAssignment', function($q) use ($school) {
                    $q->where('school_id', $school->id);
                })
                ->first();

            if ($user) {
                // Update password to new NPSN
                $user->update([
                    'password' => Hash::make($school->npsn)
                ]);

                Log::info('Admin sekolah password updated', [
                    'school_id' => $school->id,
                    'user_id' => $user->id,
                    'new_npsn' => $school->npsn
                ]);
            }
        } catch (\Exception $e) {
            Log::error('Error updating admin sekolah password', [
                'school_id' => $school->id,
                'error' => $e->getMessage()
            ]);
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy($id)
    {
        try {
            $school = School::findOrFail($id);
            $schoolName = $school->name;
            $school->delete();
            
            return redirect()->route('superadmin.schools.index')
                ->with('success', "Sekolah \"{$schoolName}\" berhasil dihapus");
        } catch (\Exception $e) {
            return redirect()->route('superadmin.schools.index')
                ->with('error', 'Gagal menghapus sekolah: ' . $e->getMessage());
        }
    }

    /**
     * Toggle active status
     */
    public function toggleStatus($id)
    {
        try {
            $school = School::findOrFail($id);
            $school->is_active = !$school->is_active;
            $school->save();
            
            $status = $school->is_active ? 'diaktifkan' : 'dinonaktifkan';
            return redirect()->route('superadmin.schools.index')
                ->with('success', "Sekolah \"{$school->name}\" berhasil {$status}");
        } catch (\Exception $e) {
            return redirect()->route('superadmin.schools.index')
                ->with('error', 'Gagal mengubah status sekolah: ' . $e->getMessage());
        }
    }

    /**
     * Export schools to Excel
     */
    public function export()
    {
        try {
            $schools = School::with('kcd', 'kabKota')->orderBy('name')->get();
            
            $filename = 'data_sekolah_' . date('Y-m-d_His') . '.xls';
            
            $headers = [
                'Content-Type' => 'application/vnd.ms-excel; charset=UTF-8',
                'Content-Disposition' => "attachment; filename=\"{$filename}\"",
                'Pragma' => 'no-cache',
                'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
                'Expires' => '0'
            ];

            $callback = function() use ($schools) {
                echo "\xEF\xBB\xBF"; // UTF-8 BOM
                
                // Start Excel XML
                echo '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
                echo '<head>';
                echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">';
                echo '<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
                echo '<x:Name>Data Sekolah</x:Name>';
                echo '<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>';
                echo '</x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->';
                echo '</head>';
                echo '<body>';
                echo '<table border="1">';
                
                // Header
                echo '<thead>';
                echo '<tr style="background-color: #4CAF50; color: white; font-weight: bold;">';
                echo '<th>NPSN</th>';
                echo '<th>Nama Sekolah</th>';
                echo '<th>Jenjang</th>';
                echo '<th>Status</th>';
                echo '<th>Alamat</th>';
                echo '<th>Telepon</th>';
                echo '<th>Email</th>';
                echo '<th>KCD</th>';
                echo '<th>Kab/Kota</th>';
                echo '<th>Kepala Sekolah</th>';
                echo '<th>NIP Kepala Sekolah</th>';
                echo '<th>Telp Kepala Sekolah</th>';
                echo '<th>Memiliki ULD</th>';
                echo '<th>Jumlah ULD</th>';
                echo '<th>Status Aktif</th>';
                echo '</tr>';
                echo '</thead>';
                
                // Data
                echo '<tbody>';
                foreach ($schools as $school) {
                    echo '<tr>';
                    echo '<td>\'' . htmlspecialchars($school->npsn ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->name ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->level ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->status ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->address ?? '') . '</td>';
                    echo '<td>\'' . htmlspecialchars($school->phone ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->email ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->kcd->name ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->kabKota->name ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($school->principal_name ?? '') . '</td>';
                    echo '<td>\'' . htmlspecialchars($school->principal_nip ?? '') . '</td>';
                    echo '<td>\'' . htmlspecialchars($school->principal_phone ?? '') . '</td>';
                    echo '<td>' . ($school->has_uld ? 'Ya' : 'Tidak') . '</td>';
                    echo '<td>' . ($school->uld_count ?? '') . '</td>';
                    echo '<td>' . ($school->is_active ? 'Aktif' : 'Nonaktif') . '</td>';
                    echo '</tr>';
                }
                echo '</tbody>';
                echo '</table>';
                echo '</body>';
                echo '</html>';
            };

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

        } catch (\Exception $e) {
            Log::error('Error exporting schools', ['error' => $e->getMessage()]);
            return redirect()->route('superadmin.schools.index')
                ->with('error', 'Gagal mengekspor data: ' . $e->getMessage());
        }
    }

    /**
     * Download import template
     */
    public function downloadTemplate()
    {
        $filename = 'template_import_sekolah.xls';
        
        $headers = [
            'Content-Type' => 'application/vnd.ms-excel; charset=UTF-8',
            'Content-Disposition' => "attachment; filename=\"{$filename}\"",
            'Pragma' => 'no-cache',
            'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
            'Expires' => '0'
        ];

        $callback = function() {
            echo "\xEF\xBB\xBF"; // UTF-8 BOM
            
            // Start Excel XML
            echo '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
            echo '<head>';
            echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">';
            echo '<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
            echo '<x:Name>Template Import</x:Name>';
            echo '<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>';
            echo '</x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->';
            echo '</head>';
            echo '<body>';
            echo '<table border="1">';
            
            // Header
            echo '<thead>';
            echo '<tr style="background-color: #4CAF50; color: white; font-weight: bold;">';
            echo '<th>NPSN</th>';
            echo '<th>Nama Sekolah</th>';
            echo '<th>Jenjang</th>';
            echo '<th>Status</th>';
            echo '<th>Alamat</th>';
            echo '<th>Telepon</th>';
            echo '<th>Email</th>';
            echo '<th>Kode KCD</th>';
            echo '<th>Nama Kepala Sekolah</th>';
            echo '<th>NIP Kepala Sekolah</th>';
            echo '<th>Telp Kepala Sekolah</th>';
            echo '<th>Memiliki ULD</th>';
            echo '<th>Jumlah ULD</th>';
            echo '</tr>';
            echo '</thead>';
            
            // Sample data rows
            echo '<tbody>';
            echo '<tr>';
            echo '<td>\'12345678</td>';
            echo '<td>SD Negeri 1 Contoh</td>';
            echo '<td>SD</td>';
            echo '<td>Negeri</td>';
            echo '<td>Jl. Contoh No. 123</td>';
            echo '<td>\'021-12345678</td>';
            echo '<td>sdncontoh@example.com</td>';
            echo '<td>KCD01</td>';
            echo '<td>Drs. Contoh, M.Pd</td>';
            echo '<td>\'196501011990031001</td>';
            echo '<td>\'08123456789</td>';
            echo '<td>Ya</td>';
            echo '<td>5</td>';
            echo '</tr>';
            echo '<tr>';
            echo '<td>\'87654321</td>';
            echo '<td>SMP Negeri 2 Contoh</td>';
            echo '<td>SMP</td>';
            echo '<td>Swasta</td>';
            echo '<td>Jl. Contoh No. 456</td>';
            echo '<td>\'021-87654321</td>';
            echo '<td>smpcontoh@example.com</td>';
            echo '<td>KCD02</td>';
            echo '<td>Dr. Contoh, S.Pd</td>';
            echo '<td>\'197001011995031002</td>';
            echo '<td>\'08198765432</td>';
            echo '<td>Tidak</td>';
            echo '<td></td>';
            echo '</tr>';
            echo '</tbody>';
            echo '</table>';
            echo '</body>';
            echo '</html>';
        };

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

    /**
     * Import schools from Excel/CSV
     */
    public function import(Request $request)
    {
        $request->validate([
            'file' => 'required|file|mimes:csv,xlsx,xls|max:2048'
        ]);

        try {
            $file = $request->file('file');
            $extension = $file->getClientOriginalExtension();
            
            DB::beginTransaction();

            $imported = 0;
            $errors = [];
            
            // Read file content
            $content = file_get_contents($file->getRealPath());
            
            // Check if it's an HTML/XML Excel file
            if (strpos($content, '<table') !== false || $extension === 'xls') {
                // Parse HTML table
                $dom = new \DOMDocument();
                @$dom->loadHTML($content);
                $rows = $dom->getElementsByTagName('tr');
                
                $rowNum = 0;
                foreach ($rows as $row) {
                    $rowNum++;
                    
                    // Skip header row
                    if ($rowNum === 1) continue;
                    
                    $cells = $row->getElementsByTagName('td');
                    if ($cells->length === 0) {
                        $cells = $row->getElementsByTagName('th');
                        if ($cells->length === 0) continue;
                        continue; // Skip header
                    }
                    
                    $data = [];
                    foreach ($cells as $cell) {
                        $data[] = trim($cell->nodeValue);
                    }
                    
                    try {
                        $this->importRow($data, $rowNum, $imported, $errors);
                    } catch (\Exception $e) {
                        $errors[] = "Baris {$rowNum}: " . $e->getMessage();
                    }
                }
            } else {
                // Parse CSV
                $handle = fopen($file->getRealPath(), 'r');
                
                // Skip BOM if exists
                $bom = fread($handle, 3);
                if ($bom !== chr(0xEF).chr(0xBB).chr(0xBF)) {
                    rewind($handle);
                }
                
                // Skip header
                fgetcsv($handle);
                
                $row = 1;
                while (($data = fgetcsv($handle)) !== false) {
                    $row++;
                    
                    try {
                        $this->importRow($data, $row, $imported, $errors);
                    } catch (\Exception $e) {
                        $errors[] = "Baris {$row}: " . $e->getMessage();
                    }
                }
                
                fclose($handle);
            }

            DB::commit();

            $message = "Import selesai! {$imported} sekolah berhasil diimpor.";
            if (count($errors) > 0) {
                $message .= " " . count($errors) . " baris gagal.";
                session()->flash('import_errors', $errors);
            }

            return redirect()->route('superadmin.schools.index')
                ->with('success', $message);

        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Import schools error', ['error' => $e->getMessage()]);
            return redirect()->route('superadmin.schools.index')
                ->with('error', 'Gagal mengimpor data: ' . $e->getMessage());
        }
    }

    /**
     * Import single row
     */
    private function importRow($data, $row, &$imported, &$errors)
    {
        // Validate required fields
        if (empty($data[0]) || empty($data[1]) || empty($data[2]) || empty($data[3])) {
            $errors[] = "Baris {$row}: Data tidak lengkap (NPSN, Nama, Jenjang, Status wajib diisi)";
            return;
        }

        // Find KCD
        $kcd = null;
        if (!empty($data[7])) {
            $kcd = Kcd::where('code', $data[7])->orWhere('name', 'like', '%' . $data[7] . '%')->first();
        }

        if (!$kcd) {
            $kcd = Kcd::first(); // Default to first KCD if not found
        }

        // Check if school exists
        $school = School::where('npsn', $data[0])->first();

        $schoolData = [
            'kcd_id' => $kcd->id,
            'npsn' => $data[0],
            'name' => $data[1],
            'level' => strtoupper($data[2]),
            'status' => ucfirst($data[3]),
            'address' => $data[4] ?? null,
            'phone' => $data[5] ?? null,
            'email' => $data[6] ?? null,
            'principal_name' => $data[8] ?? null,
            'principal_nip' => $data[9] ?? null,
            'principal_phone' => $data[10] ?? null,
            'has_uld' => isset($data[11]) && strtolower($data[11]) === 'ya',
            'uld_count' => isset($data[12]) && is_numeric($data[12]) ? (int)$data[12] : null,
            'is_active' => true,
        ];

        if ($school) {
            // Update existing
            $school->update($schoolData);
        } else {
            // Create new
            $school = School::create($schoolData);
            
            // Auto-create admin sekolah user
            $this->createAdminSekolahUser($school);
        }

        $imported++;
    }
}

