<?php

namespace App\Services;

use App\Models\Athlete;
use App\Models\Club;
use App\Models\Country;
use App\Models\AgeCategory;
use App\Models\Level;
use App\Models\Category;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;
use Carbon\Carbon;

class AthleteImportService
{
    public function import($file): array
    {
        $data = $this->parseFile($file);

        $successCount = 0;
        $failedRows   = [];

        foreach ($data as $index => $row) {
            // If your file has a header row, “real” Excel row = index + 2
            $rowNumber = $index + 2;

            $rowErrors = [];

            // Basic required fields
            if (empty($row['first_name']) || empty($row['last_name'])) {
                $rowErrors[] = 'First name and last name are required.';
            }

            // Date of birth: normalize / validate
            $dob = null;
            if (!empty($row['dob'])) {
                try {
                    // Adjust format if needed (e.g. d/m/Y)
                    $dob = Carbon::parse($row['dob'])->format('Y-m-d');
                } catch (\Throwable $e) {
                    $rowErrors[] = 'Invalid date of birth format.';
                }
            } else {
                $rowErrors[] = 'Date of birth is required.';
            }

            // Resolve FK names -> IDs, collecting errors instead of just logging
            $club_id         = $this->resolveIdByName(Club::class,        $row['club_id']        ?? null, 'Club',         $rowErrors);
            $country_id      = $this->resolveIdByName(Country::class,     $row['country_id']     ?? null, 'Country',      $rowErrors);
            $age_category_id = $this->resolveIdByName(AgeCategory::class, $row['age_category_id']?? null, 'Age category', $rowErrors);
            $level_id        = $this->resolveIdByName(Level::class,       $row['level_id']       ?? null, 'Level',        $rowErrors);
            $category_id     = $this->resolveIdByName(Category::class,    $row['category_id']    ?? null, 'Category',     $rowErrors);

            // If we already found errors, skip DB and collect this row as failed
            if (!empty($rowErrors)) {
                $failedRows[] = [
                    'row'    => $rowNumber,
                    'data'   => $row,
                    'errors' => $rowErrors,
                ];
                continue;
            }

            // Try to insert/update this row
            try {
                DB::beginTransaction();

                Athlete::updateOrCreate(
                    [
                        'first_name' => $row['first_name'],
                        'last_name'  => $row['last_name'],
                    ],
                    [
                        'gender'          => $row['gender'] ?? null,
                        'dob'             => $dob,
                        'club_id'         => $club_id,
                        'country_id'      => $country_id,
                        'age_category_id' => $age_category_id,
                        'level_id'        => $level_id,
                        'category_id'     => $category_id,
                        'type'            => $row['type'] ?? null,
                    ]
                );

                DB::commit();
                $successCount++;
            } catch (\Throwable $e) {
                DB::rollBack();

                Log::error('Athlete import failed.', [
                    'row'   => $rowNumber,
                    'data'  => $row,
                    'error' => $e->getMessage(),
                ]);

                $failedRows[] = [
                    'row'    => $rowNumber,
                    'data'   => $row,
                    'errors' => ['Unexpected error: ' . $e->getMessage()],
                ];
            }
        }

        return [
            'success_count' => $successCount,
            'failed_rows'   => $failedRows,
        ];
    }

    /**
     * Resolve ID from model based on name and push error if not found.
     */
    private function resolveIdByName($modelClass, $name, string $label, array &$errors)
    {
        if ($name === null || $name === '') {
            // If you want some of these to be mandatory, treat empty as error too.
            return null;
        }

        $id = $modelClass::where('name', $name)->value('id');

        if (!$id) {
            $message = "$label '{$name}' does not exist.";
            $errors[] = $message;
            Log::warning($message);
        }

        return $id;
    }

    private function parseFile($file)
    {
        $extension = $file->getClientOriginalExtension();

        if (in_array($extension, ['csv', 'txt'])) {
            return $this->parseCsv($file);
        }

        if ($extension === 'xlsx') {
            return $this->parseExcel($file);
        }

        throw new \Exception("Unsupported file type.");
    }

    private function parseCsv($file)
    {
        $data = [];
        if (($handle = fopen($file->getRealPath(), 'r')) !== false) {
            $header = fgetcsv($handle, 1000, ',');
            while (($row = fgetcsv($handle, 1000, ',')) !== false) {
                $data[] = array_combine($header, $row);
            }
            fclose($handle);
        }
        return $data;
    }

    private function parseExcel($file)
    {
        $array  = Excel::toArray([], $file);
        $header = array_shift($array[0]);
        $data   = [];

        foreach ($array[0] as $row) {
            $data[] = array_combine($header, $row);
        }

        return $data;
    }
}
