<?php

namespace App\Http\Controllers;

use App\Models\AcademicSession;
use App\Models\Branch;
use App\Models\Family;
use App\Models\SchoolClass;
use App\Models\Section;
use App\Models\Student;
use App\Support\BranchContext;
use App\Support\SimpleXlsx;
use Illuminate\Http\Request;
use Illuminate\Routing\Controllers\HasMiddleware;
use Illuminate\Routing\Controllers\Middleware;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class StudentImportController extends Controller implements HasMiddleware
{
    /** Expected column headers (order-independent — matched by name). */
    private array $columns = [
        'name', 'father_name', 'father_cnic', 'father_mobile', 'mother_name',
        'gender', 'date_of_birth', 'b_form_number', 'class', 'section',
        'admission_date', 'roll_number', 'address',
    ];

    public static function middleware(): array
    {
        return [new Middleware('can:create,'.Student::class)];
    }

    public function form(): View
    {
        return view('students.import', ['columns' => $this->columns]);
    }

    /** Download a ready-to-fill CSV template (opens in Excel). */
    public function template()
    {
        $headers = ['Name', 'Father Name', 'Father CNIC', 'Father Mobile', 'Mother Name', 'Gender', 'Date of Birth', 'B-Form Number', 'Class', 'Section', 'Admission Date', 'Roll Number', 'Address'];
        $sample = [
            ['Ahmed Ali', 'Ali Khan', '12345-1234567-1', '0300-1234567', 'Sara Ali', 'male', '2018-05-10', '12345-1234567-2', 'Grade 1', 'A', '2026-04-01', '1', 'Shabqadar'],
            ['Fatima Noor', 'Noor Muhammad', '', '0311-7654321', '', 'female', '2017-09-22', '', 'Grade 2', 'B', '2026-04-01', '', ''],
        ];

        return response()->streamDownload(function () use ($headers, $sample) {
            $out = fopen('php://output', 'w');
            fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM for Excel
            fputcsv($out, $headers);
            foreach ($sample as $row) {
                fputcsv($out, $row);
            }
            fclose($out);
        }, 'student-import-template.csv', ['Content-Type' => 'text/csv; charset=UTF-8']);
    }

    public function import(Request $request, BranchContext $ctx)
    {
        $request->validate([
            'file' => ['required', 'file', 'mimes:xlsx,csv,txt', 'max:5120'],
        ]);

        $file = $request->file('file');
        $rows = SimpleXlsx::rows($file->getRealPath(), $file->getClientOriginalExtension());

        if (count($rows) < 2) {
            return back()->with('error', 'The file has no data rows.');
        }

        // Map header names → column index.
        $header = array_map(fn ($h) => strtolower(trim(preg_replace('/[^a-z0-9]+/i', '_', $h))), $rows[0]);
        $map = [];
        foreach ($header as $i => $h) {
            $map[$h] = $i;
        }

        $branchId = $ctx->currentId() ?? auth()->user()?->branch_id ?? Branch::min('id');
        $session = AcademicSession::current();
        $classes = SchoolClass::where('branch_id', $branchId)->get();
        $sections = Section::where('branch_id', $branchId)->get();

        $get = function (array $row, string $key) use ($map) {
            $i = $map[$key] ?? null;

            return $i !== null ? trim((string) ($row[$i] ?? '')) : '';
        };

        $created = 0;
        $skipped = 0;
        $errors = [];
        // Cache of families resolved this run so siblings group. Keyed by
        // father CNIC (primary) and father mobile (fallback).
        $familyCache = [];

        DB::transaction(function () use ($rows, $get, $classes, $sections, $branchId, $session, &$created, &$skipped, &$errors, &$familyCache) {
            foreach ($rows as $n => $row) {
                if ($n === 0) {
                    continue; // header
                }
                $name = $get($row, 'name') ?: $get($row, 'student_name');
                if ($name === '') {
                    continue; // blank line
                }

                $className = $get($row, 'class');
                $class = $classes->first(fn ($c) => strcasecmp(trim($c->name), $className) === 0);
                if ($className !== '' && ! $class) {
                    $errors[] = "Row ".($n + 1).": class “{$className}” not found — skipped.";
                    $skipped++;

                    continue;
                }

                $sectionName = $get($row, 'section');
                $section = $class ? $sections->first(fn ($s) => $s->class_id === $class->id && strcasecmp(trim($s->name), $sectionName) === 0) : null;

                // ── Family: match on father CNIC (primary), mobile (fallback) ──
                // Siblings = students sharing a family, so two students group
                // when their father's CNIC matches (ignoring dashes/spaces).
                $mobile = $get($row, 'father_mobile');
                $cnic = $get($row, 'father_cnic');
                $fatherName = $get($row, 'father_name');
                $cnicKey = preg_replace('/\D/', '', $cnic);   // digits only
                $mobileKey = preg_replace('/\D/', '', $mobile);

                $family = null;
                // 1) Already resolved this run (by CNIC, else mobile).
                if ($cnicKey !== '' && isset($familyCache['C:'.$cnicKey])) {
                    $family = $familyCache['C:'.$cnicKey];
                } elseif ($cnicKey === '' && $mobileKey !== '' && isset($familyCache['M:'.$mobileKey])) {
                    $family = $familyCache['M:'.$mobileKey];
                }
                // 2) Existing family in the DB with the same CNIC (links re-imports
                //    & siblings already on record). Dash/space-insensitive match.
                if (! $family && $cnicKey !== '') {
                    $family = Family::where('branch_id', $branchId)
                        ->whereRaw("REPLACE(REPLACE(REPLACE(father_cnic, '-', ''), ' ', ''), '.', '') = ?", [$cnicKey])
                        ->first();
                }
                // 3) Nothing matched → create a fresh family.
                if (! $family) {
                    $family = Family::create([
                        'branch_id' => $branchId,
                        'family_code' => Family::nextCode((int) $branchId),
                        'father_name' => $fatherName ?: null,
                        'father_cnic' => $cnic ?: null,
                        'father_mobile' => $mobile ?: null,
                        'mother_name' => $get($row, 'mother_name') ?: null,
                        'address' => $get($row, 'address') ?: null,
                    ]);
                }
                // Cache under whatever keys this row carries so later rows group.
                if ($cnicKey !== '') {
                    $familyCache['C:'.$cnicKey] = $family;
                }
                if ($mobileKey !== '') {
                    $familyCache['M:'.$mobileKey] = $family;
                }

                $gender = strtolower($get($row, 'gender'));
                $gender = in_array($gender, ['male', 'female', 'other'], true) ? $gender : 'male';

                Student::create([
                    'branch_id' => $branchId,
                    'family_id' => $family->id,
                    'academic_session_id' => $session?->id,
                    'class_id' => $class?->id,
                    'section_id' => $section?->id,
                    'name' => $name,
                    'gender' => $gender,
                    'date_of_birth' => SimpleXlsx::normalizeDate($get($row, 'date_of_birth')),
                    'b_form_number' => $get($row, 'b_form_number') ?: null,
                    'roll_number' => $get($row, 'roll_number') ?: null,
                    'admission_number' => Student::nextAdmissionNumber((int) $branchId),
                    'admission_date' => SimpleXlsx::normalizeDate($get($row, 'admission_date')) ?? now()->toDateString(),
                    'status' => 'active',
                ]);
                $created++;
            }
        });

        $msg = "Imported {$created} student(s).".($skipped ? " Skipped {$skipped}." : '');

        return back()->with('success', $msg)->with('import_errors', $errors);
    }
}
