<?php

namespace App\Imports;

use App\Models\SalaryGrade;
use App\Models\SalaryStep;
use App\Models\SalaryStructure;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
use Throwable;

/**
 * Header-based salary-table import: structure -> grade -> step -> amount.
 * Upserts by (grade, step, effective_from); idempotent. Used to set up an
 * institution's salary scales or to load an annual review.
 */
class SalaryTableImport implements ToCollection, WithHeadingRow
{
    public int $created = 0;
    public int $updated = 0;
    public int $skipped = 0;

    /** @var array<int,array{row:int,messages:array<int,string>}> */
    public array $errors = [];

    public function __construct(private readonly string $defaultEffectiveFrom) {}

    public function collection(Collection $rows): void
    {
        SalaryStructure::disableAuditing();
        SalaryGrade::disableAuditing();
        SalaryStep::disableAuditing();

        $structureCache = [];
        $gradeCache = [];

        foreach ($rows as $i => $row) {
            $line = $i + 2;
            $structure = $this->upper($row['salary_structure'] ?? $row['structure'] ?? null);
            $grade = $this->upper($row['grade'] ?? null);
            $step = $this->intOrNull($row['step'] ?? null);
            $amount = $this->amount($row['consolidated_amount'] ?? $row['amount'] ?? null);
            $effective = $this->parseDate($row['effective_from'] ?? null) ?? $this->defaultEffectiveFrom;

            if ($this->isEmptyRow($structure, $grade, $step, $amount)) {
                continue;
            }

            $errs = [];
            if ($structure === null) {
                $errs[] = 'Salary Structure is required';
            }
            if ($grade === null) {
                $errs[] = 'Grade is required';
            }
            if ($step === null) {
                $errs[] = 'Step is required (a whole number)';
            }
            if ($amount === null) {
                $errs[] = 'Consolidated Amount is required (a positive number)';
            }
            if ($errs !== []) {
                $this->skipped++;
                $this->errors[] = ['row' => $line, 'messages' => $errs];

                continue;
            }

            try {
                $structureId = $structureCache[$structure]
                    ??= SalaryStructure::firstOrCreate(['code' => $structure], ['name' => $structure])->id;

                $gradeKey = "{$structure}|{$grade}";
                $gradeId = $gradeCache[$gradeKey]
                    ??= SalaryGrade::firstOrCreate(['salary_structure_id' => $structureId, 'code' => $grade])->id;

                $stepModel = SalaryStep::updateOrCreate(
                    ['salary_grade_id' => $gradeId, 'step' => $step, 'effective_from' => $effective],
                    ['consolidated_amount' => $amount]
                );

                $stepModel->wasRecentlyCreated ? $this->created++ : $this->updated++;
            } catch (Throwable) {
                $this->skipped++;
                $this->errors[] = ['row' => $line, 'messages' => ['Row could not be saved']];
            }
        }
    }

    private function isEmptyRow(?string $structure, ?string $grade, ?int $step, ?float $amount): bool
    {
        return $structure === null && $grade === null && $step === null && $amount === null;
    }

    private function upper(mixed $v): ?string
    {
        if ($v === null) {
            return null;
        }
        $s = strtoupper(trim(trim((string) $v), "\"'"));

        return $s === '' ? null : $s;
    }

    private function intOrNull(mixed $v): ?int
    {
        return is_numeric($v) ? (int) round((float) $v) : null;
    }

    private function amount(mixed $v): ?float
    {
        if ($v === null || $v === '') {
            return null;
        }
        $s = preg_replace('/[,\s₦]/', '', (string) $v);
        if (! is_numeric($s) || (float) $s <= 0) {
            return null;
        }

        return round((float) $s, 2);
    }

    private function parseDate(mixed $v): ?string
    {
        if ($v === null || $v === '') {
            return null;
        }
        if (is_numeric($v)) {
            try {
                return ExcelDate::excelToDateTimeObject((float) $v)->format('Y-m-d');
            } catch (Throwable) {
                return null;
            }
        }
        try {
            return Carbon::parse((string) $v)->format('Y-m-d');
        } catch (Throwable) {
            return null;
        }
    }
}
