<?php

namespace App\Imports;

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\Importable;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\Kelas;
use App\Models\DidikPokok;
use App\Models\MataPelajaran;

class IntrakulikulerImport implements ToCollection
{
    use Importable;

    public function collection(Collection $rows)
    {
        // Expect header row; skip it
        if ($rows->count() <= 1) return;

        $header = $rows->first()->map(function($v){ return strtolower(trim($v)); })->toArray();

        $expected = ['kelas_id','peserta_didik_id','peserta_nama','mata_pelajaran_id','mata_pelajaran_nama','materi1','materi2','materi3','materi4','materi5','materi6','nilai1','nilai2','nilai3','nilai4','nilai5','nilai6','n_sumatif','n_akhir_semester','nilai_akhir','deskripsi'];

        foreach ($rows->slice(1) as $row) {
            $data = [];
            foreach ($header as $i => $col) {
                $data[$col] = $row[$i] ?? null;
            }

            // Basic mapping and sanitization
            $rowToInsert = [
                'kelas_id' => $data['kelas_id'] ?? null,
                'peserta_didik_id' => $data['peserta_didik_id'] ?? null,
                'mata_pelajaran_id' => $data['mata_pelajaran_id'] ?? null,
                'nilai1' => isset($data['nilai1']) && $data['nilai1'] !== '' ? (is_numeric($data['nilai1']) ? (int) round($data['nilai1']) : null) : null,
                'nilai2' => isset($data['nilai2']) && $data['nilai2'] !== '' ? (is_numeric($data['nilai2']) ? (int) round($data['nilai2']) : null) : null,
                'nilai3' => isset($data['nilai3']) && $data['nilai3'] !== '' ? (is_numeric($data['nilai3']) ? (int) round($data['nilai3']) : null) : null,
                'nilai4' => isset($data['nilai4']) && $data['nilai4'] !== '' ? (is_numeric($data['nilai4']) ? (int) round($data['nilai4']) : null) : null,
                'nilai5' => isset($data['nilai5']) && $data['nilai5'] !== '' ? (is_numeric($data['nilai5']) ? (int) round($data['nilai5']) : null) : null,
                'nilai6' => isset($data['nilai6']) && $data['nilai6'] !== '' ? (is_numeric($data['nilai6']) ? (int) round($data['nilai6']) : null) : null,
                'n_sumatif' => isset($data['n_sumatif']) && $data['n_sumatif'] !== '' ? (is_numeric($data['n_sumatif']) ? (int) round($data['n_sumatif']) : null) : null,
                'n_akhir_semester' => isset($data['n_akhir_semester']) && $data['n_akhir_semester'] !== '' ? (is_numeric($data['n_akhir_semester']) ? (int) round($data['n_akhir_semester']) : null) : null,
                'nilai_akhir' => isset($data['nilai_akhir']) && $data['nilai_akhir'] !== '' ? (is_numeric($data['nilai_akhir']) ? (int) round($data['nilai_akhir']) : null) : null,
                'deskripsi' => $data['deskripsi'] ?? null,
                'created_at' => now(), 'updated_at' => now(),
            ];

            // If peserta_didik_id not provided but peserta_nama is in the template, try to resolve it
            if (empty($rowToInsert['peserta_didik_id']) && !empty($data['peserta_nama'])) {
                try {
                    $pd = DidikPokok::where('nama', trim($data['peserta_nama']))->orWhere('nis', trim($data['peserta_nama']))->first();
                    if ($pd) $rowToInsert['peserta_didik_id'] = $pd->id;
                } catch (\Throwable $e) {
                    Log::warning('Failed to resolve peserta_nama to peserta_didik_id', ['peserta_nama' => $data['peserta_nama'], 'exception' => $e->getMessage()]);
                }
            }

            // If mata_pelajaran_id not provided but mata_pelajaran_nama present, try to resolve
            if (empty($rowToInsert['mata_pelajaran_id']) && !empty($data['mata_pelajaran_nama'])) {
                try {
                    $mp = MataPelajaran::where('nama', trim($data['mata_pelajaran_nama']))->first();
                    if ($mp) $rowToInsert['mata_pelajaran_id'] = $mp->id;
                } catch (\Throwable $e) {
                    Log::warning('Failed to resolve mata_pelajaran_nama', ['mata_pelajaran_nama' => $data['mata_pelajaran_nama'], 'exception' => $e->getMessage()]);
                }
            }

            // Compute n_sumatif if missing: average of nilai1..nilai6 (ignore nulls)
            if (empty($rowToInsert['n_sumatif'])) {
                $vals = [];
                for ($i = 1; $i <= 6; $i++) {
                    $k = 'nilai' . $i;
                    if (isset($rowToInsert[$k]) && $rowToInsert[$k] !== null) $vals[] = $rowToInsert[$k];
                }
                if (count($vals) > 0) {
                    $rowToInsert['n_sumatif'] = (int) round(array_sum($vals) / count($vals));
                }
            }

            // Compute nilai_akhir if missing: average of n_sumatif and n_akhir_semester (if present)
            if (empty($rowToInsert['nilai_akhir']) && !empty($rowToInsert['n_sumatif'])) {
                if (!empty($rowToInsert['n_akhir_semester'])) {
                    $rowToInsert['nilai_akhir'] = (int) round((($rowToInsert['n_sumatif'] + (int) $rowToInsert['n_akhir_semester']) / 2));
                } else {
                    $rowToInsert['nilai_akhir'] = $rowToInsert['n_sumatif'];
                }
            }

            try {
                if (isset($rowToInsert['kelas_id']) && $rowToInsert['kelas_id'] === '') $rowToInsert['kelas_id'] = null;

                // If current user is a guru, enforce kelas ownership (skip or set to first owned)
                if (auth()->check() && optional(auth()->user())->role === 'guru') {
                    try { $owned = Kelas::where('walikelas_id', auth()->id())->pluck('id')->toArray(); } catch (\Throwable $e) { $owned = []; }
                    if (empty($owned)) {
                        // no owned kelas - skip this row
                        continue;
                    }
                    if (empty($rowToInsert['kelas_id']) || !in_array($rowToInsert['kelas_id'], $owned)) {
                        $rowToInsert['kelas_id'] = $owned[0];
                    }
                }

                // Build a query to find existing record for same class + student + subject
                $query = DB::table('penilaian_intrakulikuler')->where('kelas_id', $rowToInsert['kelas_id']);

                if (!empty($rowToInsert['peserta_didik_id'])) {
                    $query->where('peserta_didik_id', $rowToInsert['peserta_didik_id']);
                } elseif (!empty($data['peserta_nama'])) {
                    $query->where('peserta_nama', trim($data['peserta_nama']));
                }

                if (!empty($rowToInsert['mata_pelajaran_id'])) {
                    $query->where('mata_pelajaran_id', $rowToInsert['mata_pelajaran_id']);
                } elseif (!empty($data['mata_pelajaran_nama'])) {
                    $query->where('mata_pelajaran_nama', trim($data['mata_pelajaran_nama']));
                }

                $existing = $query->first();

                if ($existing) {
                    // Update existing record instead of inserting duplicate
                    DB::table('penilaian_intrakulikuler')->where('id', $existing->id)->update(array_merge($rowToInsert, ['updated_at' => now()]));
                } else {
                    DB::table('penilaian_intrakulikuler')->insert($rowToInsert);
                }
            } catch (\Throwable $e) {
                Log::error('Intrakulikuler import failed', [
                    'row' => $rowToInsert,
                    'exception' => $e->getMessage(),
                    'trace' => $e->getTraceAsString(),
                ]);
                // continue processing remaining rows
            }
        }
    }
}
