<?php

namespace App\Console\Commands;

use App\Models\Subject;
use App\Models\Question;
use App\Models\Option;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportQuestionsFromExcel extends Command
{
    protected $signature = 'tasheeh:import-questions
                            {path : Path to the .xlsx file}
                            {--lang=ar : Question language to save (ar/en)}';

    protected $description = 'Import questions (Arabic headers) into subjects, questions, options';

    public function handle(): int
    {
        $path = $this->argument('path');
        $lang = $this->option('lang') ?: 'ar';

        if (!file_exists($path)) {
            $this->error("File not found: $path");
            return self::FAILURE;
        }

        $this->info("Loading: $path");
        $spreadsheet = IOFactory::load($path);
        $sheets = $spreadsheet->getAllSheets();

        $totalRows = 0; $createdQ = 0; $updatedQ = 0; $createdSubj = 0;

        foreach ($sheets as $sheet) {
            $title = $sheet->getTitle();
            $rows = $sheet->toArray(null, true, true, true);
            if (empty($rows)) continue;

            $headerRow = array_shift($rows);
            $map = $this->buildHeaderMap($headerRow);

            foreach (['subject','stem','grade','level','opt1','opt1ok','opt2','opt2ok','opt3','opt3ok','opt4','opt4ok'] as $req) {
                if (!isset($map[$req])) {
                    $this->warn("Sheet [$title]: missing required column mapping for [$req]. Skipping sheet.");
                    continue 2;
                }
            }

            $this->info("Importing sheet: {$title} (".count($rows)." rows)");

            // NEW: forward-fill trackers
            $lastSubject = null;
            $lastGrade   = null;

            foreach ($rows as $row) {
                $totalRows++;

                // Raw values
                $rawSubject = $this->val($row, $map['subject']);
                $rawGrade   = $this->val($row, $map['grade']);

                // Forward-fill logic
                if ($rawSubject !== null && $rawSubject !== '') { $lastSubject = $rawSubject; }
                if ($rawGrade   !== null && $rawGrade   !== '') { $lastGrade   = $rawGrade;   }

                $subjectName = $rawSubject ?: $lastSubject;
                $gradeRaw    = $rawGrade   ?: $lastGrade;

                $stem = $this->valLong($row, $map['stem']);
                if (!$subjectName || !$stem) continue; // still skip if both are missing

                $gradeStr = $gradeRaw ? ('Grade ' . trim((string)$gradeRaw)) : null;

                $level = (int) ($this->val($row, $map['level']) ?? 5);
                if ($level < 1 || $level > 9) $level = 5;
                $b = ($level - 5) / 2.0;

                $opt = [
                    ['label'=>'A', 'text'=>$this->valLong($row, $map['opt1']), 'w'=>$this->floatOrZero($this->val($row, $map['opt1ok']))],
                    ['label'=>'B', 'text'=>$this->valLong($row, $map['opt2']), 'w'=>$this->floatOrZero($this->val($row, $map['opt2ok']))],
                    ['label'=>'C', 'text'=>$this->valLong($row, $map['opt3']), 'w'=>$this->floatOrZero($this->val($row, $map['opt3ok']))],
                    ['label'=>'D', 'text'=>$this->valLong($row, $map['opt4']), 'w'=>$this->floatOrZero($this->val($row, $map['opt4ok']))],
                ];

                $skills  = isset($map['skills'])  ? $this->valLong($row, $map['skills'])  : null;
                $outputs = isset($map['outputs']) ? $this->valLong($row, $map['outputs']) : null;

                // Subject upsert
                $subjectCode = strtoupper(Str::limit(Str::slug($subjectName, '_'), 16));
                $subject = Subject::firstOrCreate(
                    ['code' => $subjectCode],
                    ['name' => $subjectName, 'is_active' => true]
                );
                if ($subject->wasRecentlyCreated) $createdSubj++;

                DB::beginTransaction();
                try {
                    $question = Question::where([
                        'subject_id' => $subject->id,
                        'question_language' => $lang,
                    ])->where('stem_html', $stem)->first();

                    $payload = [
                        'subject_id'        => $subject->id,
                        'question_language' => $lang,
                        'grade'             => $gradeStr,
                        'level'             => $level,
                        'b'                 => $b,
                        'stem_html'         => $stem,
                        'skills_text'       => $skills,
                        'outputs_text'      => $outputs,
                        'is_active'         => true,
                    ];

                    if ($question) {
                        $question->fill($payload)->save();
                        $updatedQ++;
                    } else {
                        $question = Question::create($payload);
                        $createdQ++;
                    }

                    Option::where('question_id', $question->id)->delete();
                    foreach ($opt as $o) {
                        if (!($o['text'] ?? null)) continue;
                        Option::create([
                            'question_id' => $question->id,
                            'label'       => $o['label'],
                            'text_html'   => $o['text'],
                            'weight'      => max(0.0, min(1.0, (float)$o['w'])),
                            'is_correct'  => ((float)$o['w']) >= 0.999,
                        ]);
                    }

                    DB::commit();
                } catch (\Throwable $e) {
                    DB::rollBack();
                    $this->warn("Row import failed: ".$e->getMessage());
                }
            }
        }

        $this->line('');
        $this->info("Done.");
        $this->info("Sheets processed: ".count($sheets));
        $this->info("Rows seen:       {$totalRows}");
        $this->info("Subjects created: {$createdSubj}");
        $this->info("Questions created: {$createdQ}, updated: {$updatedQ}");

        return self::SUCCESS;
    }

    private function buildHeaderMap(array $headerRow): array
    {
        $map = [];
        $norm = function($v){
            $v = trim((string)$v);
            $v = preg_replace('/\s+/u', ' ', $v);
            return $v;
        };
        $dict = [
            'المادة' => 'subject',
            'السؤال' => 'stem',
            'الخيار الأول' => 'opt1',
            'هل الخيار الأول صحيح ؟' => 'opt1ok',
            'الخيار الثاني' => 'opt2',
            'هل الخيار الثاني صحيح ؟' => 'opt2ok',
            'الخيار الثالث' => 'opt3',
            'هل الخيار الثالث صحيح ؟' => 'opt3ok',
            'الخيار الرابع' => 'opt4',
            'هل الخيار الرابع صحيح ؟' => 'opt4ok',
            'الصف' => 'grade',
            'المستوى' => 'level',
            'المهارة' => 'skills',
            'المخرجات' => 'outputs',
        ];
        foreach ($headerRow as $col => $name) {
            $name = $norm($name);
            if (isset($dict[$name])) {
                $map[$dict[$name]] = $col;
            }
        }
        return $map;
    }

    private function val(array $row, ?string $col)
    {
        if (!$col || !array_key_exists($col, $row)) return null;
        $v = $row[$col];
        return is_string($v) ? trim($v) : $v;
    }

    private function valLong(array $row, ?string $col)
    {
        $v = $this->val($row, $col);
        if (is_null($v)) return null;
        if (is_string($v)) {
            $v = preg_replace("/\r\n|\r|\n/u", "\n", $v);
            $v = trim($v);
        }
        return $v;
    }

    private function floatOrZero($v): float
    {
        if ($v === null || $v === '') return 0.0;
        if (is_string($v)) $v = str_replace(',', '.', $v);
        return (float)$v;
    }
}
