<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\ActivityLog;
use App\Models\SchoolClass;
use App\Models\Staff;
use App\Models\Student;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/**
 * Data backup (JSON export of the core tables) and data import (bulk-create
 * students / staff from an uploaded sheet parsed on the client).
 */
class BackupController extends Controller
{
    /** Tables included in a full JSON backup. */
    private array $tables = [
        'branches', 'school_classes', 'subjects', 'periods',
        'students', 'guardians', 'staff',
        'fee_heads', 'student_fees', 'fee_payments',
        'evaluations', 'evaluation_results', 'evaluation_student_notes',
        'attendances', 'timetable_slots', 'class_subjects',
        'notices', 'events', 'invitations', 'visitors', 'health_visits',
        'incidents', 'inventory_items', 'stock_movements',
        'books', 'transactions', 'settings',
    ];

    /** GET /backup/stats — row counts per table for the Backup tab. */
    public function stats()
    {
        $out = [];
        foreach ($this->tables as $t) {
            try {
                $out[$t] = DB::table($t)->count();
            } catch (\Throwable $e) {
                $out[$t] = null; // table not present
            }
        }

        return response()->json([
            'tables' => $out,
            'total' => array_sum(array_map(fn ($v) => (int) $v, $out)),
            'generated_at' => now()->toIso8601String(),
        ]);
    }

    /** GET /backup/export — download a JSON snapshot of the core tables. */
    public function export()
    {
        $data = [];
        foreach ($this->tables as $t) {
            try {
                $data[$t] = DB::table($t)->get();
            } catch (\Throwable $e) {
                $data[$t] = [];
            }
        }
        $payload = [
            'app' => 'school-quasar',
            'version' => 1,
            'exported_at' => now()->toIso8601String(),
            'tables' => $data,
        ];
        $json = json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
        $name = 'school-backup-' . now()->format('Ymd-His') . '.json';

        ActivityLog::record('export', 'backup', 'Downloaded full data backup');

        return response($json, 200, [
            'Content-Type' => 'application/json',
            'Content-Disposition' => 'attachment; filename="' . $name . '"',
        ]);
    }

    /**
     * POST /backup/import — bulk import rows for a given type.
     * Body: { type: 'students'|'staff', rows: [ {header: value, ...}, ... ] }
     * Headers are matched case-insensitively against known aliases. Existing
     * records are updated (matched by admission_number / emp_no) rather than
     * duplicated.
     */
    public function import(Request $request)
    {
        $data = $request->validate([
            'type' => ['required', 'in:students,staff'],
            'rows' => ['required', 'array', 'min:1'],
        ]);
        $branchId = $request->header('X-Branch-Id') ?: $request->query('branch_id')
            ?: \App\Models\Branch::value('id');

        return $data['type'] === 'staff'
            ? $this->importStaff($data['rows'], $branchId)
            : $this->importStudents($data['rows'], $branchId);
    }

    private function importStudents(array $rows, $branchId)
    {
        $classes = SchoolClass::when($branchId, fn ($q) => $q->where('branch_id', $branchId))
            ->get()->keyBy(fn ($c) => mb_strtolower(trim($c->name)));

        $created = $updated = $skipped = 0;
        $errors = [];

        foreach ($rows as $i => $raw) {
            $row = $this->normalize($raw);
            $first = $this->pick($row, ['first_name', 'firstname', 'first', 'name', 'student_name']);
            $last = $this->pick($row, ['last_name', 'lastname', 'last', 'surname']);
            if (! $first && ! $last) {
                $skipped++;
                continue;
            }
            // "Full Name" in a single column → split.
            if ($first && ! $last && str_contains($first, ' ')) {
                [$first, $last] = array_pad(explode(' ', $first, 2), 2, '');
            }

            $className = $this->pick($row, ['class', 'class_name', 'grade', 'section_class']);
            $classId = $className ? optional($classes->get(mb_strtolower(trim($className))))->id : null;

            $attrs = array_filter([
                'branch_id' => $branchId,
                'class_id' => $classId,
                'first_name' => $first ?: null,
                'last_name' => $last ?: null,
                'father_name' => $this->pick($row, ['father_name', 'father', 'father name']),
                'gender' => $this->gender($this->pick($row, ['gender', 'sex'])),
                'phone' => $this->pick($row, ['phone', 'mobile', 'contact']),
                'father_mobile' => $this->pick($row, ['father_mobile', 'father_phone', 'father mobile']),
                'roll_number' => $this->pick($row, ['roll_number', 'roll', 'roll no', 'roll_no']),
                'blood_group' => $this->pick($row, ['blood_group', 'blood', 'blood group']),
                'date_of_birth' => $this->pick($row, ['date_of_birth', 'dob', 'birth_date']),
                'province' => $this->pick($row, ['province', 'state']),
                'address' => $this->pick($row, ['address']),
                'status' => $this->pick($row, ['status']) ?: 'active',
            ], fn ($v) => $v !== null && $v !== '');

            $admission = $this->pick($row, ['admission_number', 'admission', 'admission_no', 'admno', 'adm']);

            try {
                if ($admission) {
                    $existing = Student::where('admission_number', $admission)
                        ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))->first();
                    if ($existing) {
                        $existing->update($attrs);
                        $updated++;
                        continue;
                    }
                    $attrs['admission_number'] = $admission;
                }
                Student::create($attrs);
                $created++;
            } catch (\Throwable $e) {
                $errors[] = ['row' => $i + 1, 'message' => $e->getMessage()];
            }
        }

        ActivityLog::record('import', 'students', "Imported students (+{$created}, ~{$updated})");

        return response()->json(compact('created', 'updated', 'skipped') + [
            'errors' => array_slice($errors, 0, 20),
            'error_count' => count($errors),
        ]);
    }

    private function importStaff(array $rows, $branchId)
    {
        $created = $updated = $skipped = 0;
        $errors = [];

        foreach ($rows as $i => $raw) {
            $row = $this->normalize($raw);
            $name = $this->pick($row, ['name', 'full_name', 'staff_name']);
            if (! $name) {
                $skipped++;
                continue;
            }
            $attrs = array_filter([
                'branch_id' => $branchId,
                'type' => $this->staffType($this->pick($row, ['type', 'staff_type'])),
                'name' => $name,
                'father_name' => $this->pick($row, ['father_name', 'father']),
                'gender' => $this->gender($this->pick($row, ['gender', 'sex'])),
                'phone' => $this->pick($row, ['phone', 'mobile', 'contact']),
                'email' => $this->pick($row, ['email']),
                'designation' => $this->pick($row, ['designation', 'title', 'position']),
                'department' => $this->pick($row, ['department', 'dept']),
                'subjects' => $this->pick($row, ['subjects', 'subject']),
                'qualification' => $this->pick($row, ['qualification', 'degree']),
                'basic_salary' => $this->pick($row, ['basic_salary', 'salary']),
                'employment_type' => $this->pick($row, ['employment_type', 'contract']),
                'status' => $this->pick($row, ['status']) ?: 'active',
            ], fn ($v) => $v !== null && $v !== '');

            $empNo = $this->pick($row, ['emp_no', 'employee_no', 'code', 'empno']);

            try {
                if ($empNo) {
                    $existing = Staff::where('emp_no', $empNo)
                        ->when($branchId, fn ($q) => $q->where('branch_id', $branchId))->first();
                    if ($existing) {
                        $existing->update($attrs);
                        $updated++;
                        continue;
                    }
                    $attrs['emp_no'] = $empNo;
                }
                Staff::create($attrs);
                $created++;
            } catch (\Throwable $e) {
                $errors[] = ['row' => $i + 1, 'message' => $e->getMessage()];
            }
        }

        ActivityLog::record('import', 'staff', "Imported staff (+{$created}, ~{$updated})");

        return response()->json(compact('created', 'updated', 'skipped') + [
            'errors' => array_slice($errors, 0, 20),
            'error_count' => count($errors),
        ]);
    }

    /**
     * Canonicalise header keys (lower-case, strip spaces/underscores/punctuation)
     * so "First Name", "first_name" and "firstname" all match the same alias.
     */
    private function normalize(array $row): array
    {
        $out = [];
        foreach ($row as $k => $v) {
            $out[$this->canon($k)] = is_string($v) ? trim($v) : $v;
        }

        return $out;
    }

    private function canon($k): string
    {
        return preg_replace('/[^a-z0-9]/', '', mb_strtolower(trim((string) $k)));
    }

    private function pick(array $row, array $aliases)
    {
        foreach ($aliases as $a) {
            $a = $this->canon($a);
            if (array_key_exists($a, $row) && $row[$a] !== '' && $row[$a] !== null) {
                return $row[$a];
            }
        }

        return null;
    }

    private function gender($v): ?string
    {
        if (! $v) {
            return null;
        }
        $v = mb_strtolower((string) $v);

        return in_array($v, ['f', 'female', 'girl', 'دختر', 'اناث'], true) ? 'female'
            : (in_array($v, ['m', 'male', 'boy', 'پسر', 'ذکور'], true) ? 'male' : null);
    }

    private function staffType($v): string
    {
        $v = mb_strtolower((string) $v);

        return str_contains($v, 'teach') || $v === '' ? 'teacher' : 'employee';
    }
}
