<?php

namespace App\Http\Utils;

use Exception;
use SelfPhp\Auth;
use SelfPhp\DB\Serve;
use SelfPhp\SPException;
use App\Models\Category;
use App\Models\WordType;
use App\Models\Word;
use App\Models\Translation;
use App\Models\Language;
use App\Models\ApiSettings;
use \PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Class DictionaryUtil
 * Utility class for authentication-related operations, extending the base Utils class.
 */
class DictionaryUtil extends Utils
{
    /**
     * DictionaryUtil constructor.
     * Constructor method (currently empty).
     */
    public function __construct()
    {
        // Constructor logic (if any).
    }

    public function createCategory($input)
    {
        try {
            Serve::beginTransaction();
            $input = [
                'name' => ucwords(trim($input['name'])),
                'slug' => trim($input['slug']),
                'status' => isset($input['status']) ? trim($input['status']) : 'active',
                'description' => isset($input['description']) ? ucfirst(trim($input['description'])) : null,
                'parent_id' => isset($input['parent_id']) && !empty($input['parent_id']) ? trim($input['parent_id']) : null,
                'created_by' => Auth::User('user_id'),
            ];

            $serve = Category::query()
                ->insert($input);

            Serve::commit();
            return $serve;
        } catch (Exception $e) {
            Serve::rollback();
            throw new Exception($e->getMessage());
        }
    }

    public function updateCategoryUtil($input)
    {
        try {
            Serve::beginTransaction();

            $categoryId = trim($input['categoryId']);

            $updateData = [
                'name' => ucwords(trim($input['name'])),
                'slug' => trim($input['slug']),
                'status' => isset($input['status']) ? trim($input['status']) : 'active',
                'description' => isset($input['description']) ? ucfirst(trim($input['description'])) : null,
                'parent_id' => isset($input['parent_id']) && !empty($input['parent_id']) ? trim($input['parent_id']) : null,
                'updated_by' => Auth::User('user_id'),
            ];

            $serve = Category::query()
                ->where('id', $categoryId)
                ->update($updateData);

            Serve::commit();
            return $serve;
        } catch (Exception $e) {
            Serve::rollback();
            throw new Exception($e->getMessage());
        }
    }

    public function deleteCategoryUtil($input)
    {
        try {
            Serve::beginTransaction();

            $categoryId = trim($input['categoryId']);

            $serve = Category::query()
                ->where('id', $categoryId)
                ->delete();

            Serve::commit();
            return $serve;
        } catch (Exception $e) {
            Serve::rollback();
            throw new Exception($e->getMessage());
        }
    }

    public function getAllCategories($scopes, $toArray = false)
    {
        $query = Category::query()
            ->when(!empty($scopes['name']), function ($q) use ($scopes) {
                $q->where('name', 'like', '%' . $scopes['name'] . '%');
            })
            ->when(!empty($scopes['status']), function ($q) use ($scopes) {
                $q->where('status', $scopes['status']);
            })
            ->when(isset($scopes['parent_id']), function ($q) use ($scopes) {
                if (is_null($scopes['parent_id'])) {
                    $q->whereNull('parent_id');
                } else {
                    $q->where('parent_id', $scopes['parent_id']);
                }
            })
            ->orderBy('created_at', 'asc');

        return $toArray ? $query->get()->toArray() : $query->get();
    }

    public function getAllWordTypes($scopes, $toArray = false)
    {
        $query = WordType::query()
            ->when(!empty($scopes['name']), function ($q) use ($scopes) {
                $q->where('name', 'like', '%' . $scopes['name'] . '%');
            })
            ->orderBy('created_at', 'asc');

        return $toArray ? $query->get()->toArray() : $query->get();
    }

    public function getCategoryById($categoryId, $toArray = false)
    {
        $query = Category::query()
            ->where('id', $categoryId)
            ->first();

        return $toArray ? $query->toArray() : $query;
    }

    /**
     * Ensure language exists in the languages table
     * 
     * @param string $languageCode
     * @return bool
     */
    private function ensureLanguageExists($languageCode)
    {
        return Language::query()
            ->where('language_code', $languageCode)
            ->exists(); 
    }

    /**
     * Create a new word along with its translation if provided.
     * 
     * @param array $input The input data for creating the word.
     * @return bool True on success.
     * @throws SPException If an error occurs during the creation process.
     */
    public function createWord($input)
    {  
        if (isset($input['ipaWord']) && empty($input['ipaWord'])) {
            $input['ipaWord'] = $this->toIPA($input['original_word']);
        }
        
        $originalWord = [
            'original_language' => $input['original_language'] ?? 'ki',
            'category_id' => trim($input['category_id']),
            'word_type_id' => trim($input['word_type_id']),
            'original_word' => ucwords(trim($input['original_word'])),
            'original_meaning' => ucfirst(trim($input['original_meaning'])),
            'original_example' => isset($input['original_example']) ? ucfirst(trim($input['original_example'])) : null,
            'status' => isset($input['status']) ? trim($input['status']) : 'draft',
            'word_image' => isset($input['word_image']) ? trim($input['word_image']) : null,
            'pronunciation' => isset($input['pronunciation']) ? trim($input['pronunciation']) : null,
            'ipaWord' => isset($input['ipaWord']) ? trim($input['ipaWord']) : null,
            'created_by' => Auth::User('user_id'),
        ];

        $wordId = Word::query()
            ->insertGetId($originalWord);
        
        if (!empty($wordId) && isset($input['translation_language']) && 
            !empty($input['translation_language']) && !empty($input['translated_word'])) { 
            
            $trans_word = [
                'word_id' => $wordId,
                'language_code' => !empty($input['translation_language']) ? trim($input['translation_language']) : 'en',
                'translated_word' => !empty($input['translated_word']) ? ucfirst(trim($input['translated_word'])) : null,
                'translated_meaning' => !empty($input['translated_meaning']) ? ucfirst(trim($input['translated_meaning'])) : null,
                'translated_example' => !empty($input['translated_example']) ? ucfirst(trim($input['translated_example'])) : null,
                'trans_word_image' => !empty($input['trans_word_image']) ? trim($input['trans_word_image']) : null,
                'trans_pronunciation' => !empty($input['trans_pronunciation']) && isset($input['trans_pronunciation']) ? trim($input['trans_pronunciation']) : null,
                'status' => isset($input['trans_status']) && !empty($input['trans_status']) ? trim($input['trans_status']) : 'draft',
                'created_by' => Auth::User('user_id'), 
            ]; 
            
            $save = Translation::query()
                ->insert($trans_word);
            
            if (!$save) {
                throw new SPException("Failed to create word translation.");
            }
        }
        
        return true; 
    }

    /**
     * Update an existing word and its translation.
     * 
     * @param array $input The input data for updating the word.
     * @return mixed The result of the update operation.
     * @throws Exception If an error occurs during the update process.
     */
    public function updateWordUtil($input)
    { 
        $wordId = $input['wordId'];

        $updateData = [
            'original_language' => $input['original_language'] ?? 'ki',
            'category_id' => trim($input['category_id']),
            'word_type_id' => trim($input['word_type_id']),
            'original_word' => ucfirst(trim($input['original_word'])),
            'original_meaning' => ucfirst(trim($input['original_meaning'])),
            'original_example' => isset($input['original_example']) ? ucfirst(trim($input['original_example'])) : null,
            'status' => isset($input['status']) ? trim($input['status']) : 'draft',
            'word_image' => isset($input['word_image']) ? trim($input['word_image']) : null,
            'pronunciation' => isset($input['pronunciation']) ? trim($input['pronunciation']) : null,
            'ipaWord' => isset($input['ipaWord']) ? trim($input['ipaWord']) : null,
            'updated_by' => Auth::User('user_id'),
        ];

        $serve = Word::query()
            ->where('id', $wordId)
            ->update($updateData);

        if (!empty($wordId) && isset($input['translation_language']) && 
            !empty($input['translation_language']) && !empty($input['translated_word'])) {  

            $trans = $this->isTranslationExists($wordId, $input['translation_language']);   
            $trans_word = [
                'word_id' => trim($wordId),
                'language_code' => !empty($input['translation_language']) ? trim($input['translation_language']) : 'en',
                'translated_word' => !empty($input['translated_word']) ? ucfirst(trim($input['translated_word'])) : null,
                'translated_meaning' => !empty($input['translated_meaning']) ? ucfirst(trim($input['translated_meaning'])) : null,
                'translated_example' => !empty($input['translated_example']) ? ucfirst(trim($input['translated_example'])) : null,
                'trans_word_image' => !empty($input['trans_word_image']) ? trim($input['trans_word_image']) : null,
                'trans_pronunciation' => !empty($input['trans_pronunciation']) && isset($input['trans_pronunciation']) ? trim($input['trans_pronunciation']) : null,
                'status' => isset($input['trans_status']) && !empty($input['trans_status']) ? trim($input['trans_status']) : 'draft',
                'updated_by' => Auth::User('user_id'), 
            ];

            if (!empty($trans)) {
                $save = Translation::query()
                    ->where('word_id', $wordId)
                    ->update($trans_word);
                
                if (!$save) {
                    throw new SPException("Failed to update word translation.");
                } 
            }
            else {
                $save = Translation::query()
                    ->insert($trans_word);

                if (!$save) {
                    throw new SPException("Failed to create word translation.");
                }
            } 
        }

        return $serve; 
    }

    /**
     * Create a new word translation.
     * 
     * @param array $input The input data for creating the word translation.
     * @return bool True on success.
     * @throws SPException If an error occurs during the creation process.
     */
    public function createWordTranslation($input)
    { 
        $translation_language = $input['translation_language'];
        $translated_word = $input['translation_language'];
        $translated_meaning = $input['translation_language'];
        $translated_example = $input['translation_language'];
        $trans_status = $input['translation_language'];
        $trans_word_image = $input['translation_language'];
        $trans_pronunciation = $input['translation_language'];

        $input = [
            'word_id' => trim($input['word_id']),
            'language_code' => trim($translation_language),
            'translated_word' => ucfirst(trim($translated_word)),
            'translated_meaning' => ucfirst(trim($translated_meaning)),
            'translated_example' => isset($translated_example) ? ucfirst(trim($translated_example)) : null,
            'trans_word_image' => isset($trans_word_image) ? trim($trans_word_image) : null,
            'trans_pronunciation' => isset($trans_pronunciation) ? trim($trans_pronunciation) : null,
            'status' => isset($trans_status) ? trim($trans_status) : 'draft',
            'created_by' => Auth::User('user_id'),  
        ];

        $serve = Translation::query()
            ->insert($input);

        return true; 
    }

    /**
     * Get word translation by word ID and language code.
     * @param int    $wordId       The ID of the word.
     * @param string $languageCode The language code for the translation.
     * @param bool   $toArray      Whether to return the result as an array.
     * @return mixed The word translation record or null if not found.
     */
    public function getWordTranslationByWordIdAndLanguage($wordId, $languageCode, $toArray = false)
    {
        $query = Translation::query()
            ->where('word_id', $wordId)
            ->where('language_code', $languageCode)
            ->first();

        return !empty($query) && $toArray ? $query->toArray() : $query;
    }

    /**
     * Check if a translation exists for a given word ID and language code.
     * 
     * @param int    $wordId       The ID of the word.
     * @param string $languageCode The language code for the translation.
     * @return bool True if the translation exists, false otherwise.
     */
    public function isTranslationExists($wordId, $languageCode)
    {
        return Translation::query()
            ->where('word_id', $wordId)
            ->where('language_code', $languageCode)
            ->exists(); 
    }

    /**
     * Fetches all dictionary words joined with their English translations, categories, and word types.
     * This ensures the JSON output matches the expected keys in the Flutter Master class:
     * (original_word, translated_word, category_name, word_type_name, level_id).
     */
    public function getAllWords($scopes, $toArray = false)
    {
        $query = Word::query()
            // Join Categories and Word Types
            ->join('categories', 'dictionary_words.category_id', '=', 'categories.id')
            ->join('word_types', 'dictionary_words.word_type_id', '=', 'word_types.id')
            
            // Join Translations (Filtering for English 'en' by default)
            ->leftJoin('translations', function ($join) {
                $join->on('dictionary_words.id', '=', 'translations.word_id')
                    ->where('translations.language_code', '=', 'en');
            })

            // Search Scopes
            ->when(!empty($scopes['original_word']), function ($q) use ($scopes) {
                $q->where('dictionary_words.original_word', 'like', '%' . $scopes['original_word'] . '%');
            })
            ->when(!empty($scopes['status']), function ($q) use ($scopes) {
                $q->where('dictionary_words.status', $scopes['status']);
            })
            ->when(!empty($scopes['category_id']), function ($q) use ($scopes) {
                $q->where('dictionary_words.category_id', $scopes['category_id']);
            })
            ->when(!empty($scopes['word_type_id']), function ($q) use ($scopes) {
                $q->where('dictionary_words.word_type_id', $scopes['word_type_id']);
            }) 
            ->when(!empty($scopes['wordTypeName']), function ($q) use ($scopes) {
                $q->where('word_types.name', $scopes['wordTypeName']);
            }) 

            // Selection Aliasing for Flutter Compatibility
            ->select(
                'dictionary_words.id',
                'dictionary_words.original_word',
                'dictionary_words.original_meaning',
                'dictionary_words.original_example',
                'dictionary_words.level_id',
                'dictionary_words.status',
                'translations.translated_word as translated_word',
                'translations.translated_meaning as translated_meaning',
                'translations.translated_example as translated_example',
                'categories.name as category_name',
                'word_types.name as word_type_name'
            )
            ->orderBy('dictionary_words.id', 'asc');

        return $toArray ? $query->get()->toArray() : $query->get();
    }
    
    public function getWordTypes(array $scopes = [], bool $toArray = false)
    {
        return WordType::query()
            ->leftJoin('dictionary_words', 'word_types.id', '=', 'dictionary_words.word_type_id')
            ->select(
                'word_types.id',
                'word_types.name',
                'word_types.description',
                'word_types.created_at',
                'word_types.updated_at',
                Serve::raw('COUNT(dictionary_words.id) as word_count')
            )
            ->when(!empty($scopes['status']), function ($q) use ($scopes) {
                $q->where('dictionary_words.status', $scopes['status']);
            })
            ->groupBy(
                'word_types.id', 
                'word_types.name', 
                'word_types.description', 
                'word_types.created_at', 
                'word_types.updated_at'
            )
            ->orderBy('word_types.name', 'asc')
            ->get();
    }
    
    public function getDictionaryStats($scopes)
    {
        // Total Words (scoped)
        $totalWords = Word::query()
            ->when(!empty($scopes['status']), fn($q) => $q->where('status', $scopes['status']))
            ->count();
    
        // New This Month
        $newThisMonth = Word::query()
            ->whereMonth('created_at', now()->month)
            ->whereYear('created_at', now()->year)
            ->count();
    
        // Audio Pronunciations
        $audioCount = Word::query()
            ->whereNotNull('pronunciation')
            ->count();
    
        // Total Categories
        $categoryCount = Category::where('status', 'active')->count();
    
        // Words with Examples
        $withExamples = Word::query()
            ->whereNotNull('original_example')
            ->where('original_example', '!=', '')
            ->count();
    
        // Availability Status (Defaulting to 24/7)
        $updateStatus = "24/7"; 
    
        return [
            ['label' => 'Total Words', 'count' => $totalWords],
            ['label' => 'New This Month', 'count' => $newThisMonth],
            ['label' => 'Audio Pronunciations', 'count' => $audioCount],
            ['label' => 'Categories', 'count' => $categoryCount],
            ['label' => 'With Examples', 'count' => $withExamples],
            ['label' => 'Update Status', 'count' => $updateStatus],
        ];
    }
    
    /**
     * Get a word by its ID.
     * 
     * @param int  $wordId   The ID of the word.
     * @param bool $toArray  Whether to return the result as an array.
     * @return mixed The word record or null if not found.
     */
    public function getWordById($wordId, $toArray = false)
    {
        $query = Word::query()
            ->where('id', $wordId)
            ->first();

        return $toArray ? $query->toArray() : $query;
    }

    /**
     * Get a detailed word by its ID.
     * 
     * @param int  $wordId   The ID of the word.
     * @param bool $toArray  Whether to return the result as an array.
     * @return mixed The word record or null if not found.
     */
    public function getDetailedWordById(int $wordId, $toArray = false)
    {
        $word = Word::query()
            ->leftJoin('categories', 'categories.id', '=', 'dictionary_words.category_id')
            ->leftJoin('word_types', 'word_types.id', '=', 'dictionary_words.word_type_id')

            ->leftJoin('users as creator', 'creator.id', '=', 'dictionary_words.created_by')
            ->leftJoin('users as updater', 'updater.id', '=', 'dictionary_words.updated_by')

            ->where('dictionary_words.id', $wordId)
            ->select(
                'dictionary_words.*',

                // category + type
                'categories.name as category_name',
                'word_types.name as word_type_name',

                // creator
                'creator.id as creator_id',
                'creator.name as creator_name',
                'creator.email as creator_email',

                // updater
                'updater.id as updater_id',
                'updater.name as updater_name',
                'updater.email as updater_email'
            )
            ->first();

        if (!$word) return null;

        /**
         * Translations
         */
        $translations = Translation::query()
            ->leftJoin('users as t_creator', 't_creator.id', '=', 'translations.created_by')
            ->leftJoin('users as t_updater', 't_updater.id', '=', 'translations.updated_by')
            ->where('translations.word_id', $wordId)
            ->select(
                'translations.*',
                't_creator.name as created_by_name',
                't_updater.name as updated_by_name'
            )
            ->get();

        $word->translations = $toArray ? $translations->toArray() : $translations;

        return $toArray ? $word->toArray() : $word;
    }

    /**
     * Get word translation by word ID.
     * 
     * @param int  $wordId   The ID of the word.
     * @param bool $toArray  Whether to return the result as an array.
     * @return mixed The word translation record or null if not found.
     */
    public function getWordTranslationByWordId($wordId, $toArray = false)
    {
        $query = Translation::query()
            ->where('word_id', $wordId)
            ->first();

        return !empty($query) && $toArray ? $query->toArray() : $query;
    }

    /**
     * Import words from a CSV/XLSX file.
     *
     * @param array $input
     * @return bool
     * @throws SPException
     */
    public function importWordsUtil(array $input = []): bool
    {
        Serve::beginTransaction();
    
        try {
            if (empty($input['import_file_path'])) {
                throw new SPException("Import file path missing.");
            }
    
            $filePath  = $input['import_file_path'];
            $extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
    
            /**
             * Header Mapping
             */
            $headerMap = [
                'Original Language (Gĩkũyũ)' => 'original_language',
                'Category' => 'category_name',
                'Expressions' => 'expressions',
                'Word Type' => 'word_type_name',
                'Phrase' => 'phrase',
                'Word' => 'original_word',
                'Meaning' => 'original_meaning',
                'Example Sentence' => 'original_example',
                'Status' => 'status',
                'Word Image' => 'word_image',
    
                'Target Language' => 'translation_language',
                'Translated Word' => 'translated_word',
                'Translated Meaning' => 'translated_meaning',
                'Translated Example' => 'translated_example',
            ];
    
            /**
             * Parse file
             */
            $rows = [];
    
            if ($extension === 'csv') {
    
                if (($handle = fopen($filePath, 'r')) === false) {
                    throw new SPException("Unable to read CSV file.");
                }
    
                $headers = fgetcsv($handle);
                if (empty($headers)) {
                    throw new SPException("CSV file is empty or invalid.");
                }
                
                while (($data = fgetcsv($handle)) !== false) { 
                    if (empty(array_filter($data))) continue;
                    if (count($headers) !== count($data)) continue;
    
                    $rows[] = array_combine($headers, $data);
                }
    
                fclose($handle);
    
            } elseif (in_array($extension, ['xlsx', 'xls'])) {
    
                $spreadsheet = IOFactory::load($filePath);
                $sheet = $spreadsheet->getActiveSheet()->toArray();
    
                if (empty($sheet) || count($sheet) < 2) {
                    throw new SPException("XLSX file is empty.");
                }
    
                $headers = $sheet[0];
    
                for ($i = 1; $i < count($sheet); $i++) {
                    $data = $sheet[$i];
    
                    if (empty(array_filter($data))) continue;
                    if (count($headers) !== count($data)) continue;
    
                    $rows[] = array_combine($headers, $data);
                }
    
            } else {
                throw new SPException("Unsupported file format.");
            }
    
            if (empty($rows)) {
                throw new SPException("No valid data found in import file.");
            }

            /** * 
             * Normalize rows 
             */ 
            $normalizedRows = []; 
            foreach ($rows as $row) 
            { 
                $normalized = []; 
                foreach ($headerMap as $fileHeader => $dbField) { 
                    $normalized[$dbField] = $row[$fileHeader] ?? null; 
                } 
                
                $normalizedRows[] = $normalized; 
            } 
            
            $rows = $normalizedRows;
    
            /**
             * Defaults
             */
            $defaultCategory = $input['default_category'] ?? null;
            $defaultStatus   = $input['default_status'] ?? 'draft';
            $skipDuplicates  = !empty($input['skip_duplicates']);
            $validateOnly    = !empty($input['validate_only']);
    
            /**
             * Tracking
             */
            $success = 0;
            $failed  = 0;
            $skipped = 0;
            $errors  = [];
    
            // services
            $wordService = new Word();
            $translationService = new Translation();
    
            foreach ($rows as $index => $row) {
    
                $rowNumber = $index + 2;
                
                try {
    
                    /**
                     * Basic validation
                     */
                    if (empty($row['original_word']) || empty($row['original_meaning'])) {
                        throw new SPException("Missing required fields.");
                    }

                    /**
                     * Resolve Category (lookup or create)
                     */
                    $categoryId = null;
    
                    if (!empty($row['category_name'])) {
                        $categoryName = trim(ucwords($row['category_name']));
    
                        $category = Category::query()
                            ->where('name', $categoryName)
                            ->first();
    
                        if (!$category) {
                            $categoryId = Category::insertGetId([
                                'name' => $categoryName,
                                'slug' => createSlug($categoryName),
                                'status' => 'active',
                                'created_at' => now(),
                                'updated_at' => now(),
                            ]);
                        } else {
                            $categoryId = $category->id;
                        }
                    }
    
                    /**
                     * Resolve Word Type (lookup or create)
                     */
                    $wordTypeId = 1;
    
                    if (!empty($row['word_type_name'])) {
                        $wordTypeName = trim(ucwords($row['word_type_name']));
    
                        $wordType = WordType::query()
                            ->where('name', $wordTypeName)
                            ->first();
    
                        if (!$wordType) {
                            $wordTypeId = WordType::insertGetId([
                                'name' => $wordTypeName,
                                'created_at' => now(),
                                'updated_at' => now(),
                            ]);
                        } else {
                            $wordTypeId = $wordType->id;
                        }
                    }
    
                    /**
                     * Duplicate check
                     */
                    if ($skipDuplicates) {
                        $exists = Word::query()
                            ->where('original_word', trim($row['original_word']))
                            ->exists();
    
                        if ($exists) {
                            $skipped++;
                            continue;
                        }
                    }

                    /**
                     * Language resolution
                     */
                    $originalLangCode = Language::query()
                        ->where('language_name', $row['original_language'] ?? 'Kikuyu')
                        ->value('language_code') ?? 'ki';
    
                    $translationLangCode = Language::query()
                        ->where('language_name', $row['translation_language'] ?? 'English')
                        ->value('language_code') ?? 'en';
    
                    /**
                     * WORD (delegated)
                     */
                    $row['ipaWord'] = null;
                    if (isset($input['row']) && empty($row['ipaWord'])) {
                        $row['ipaWord'] = $this->toIPA($row['original_word']);
                    }
                    
                    $word = $wordService->createOrUpdateWord([
                        'original_language' => $originalLangCode,
                        'category_id'       => $categoryId ?? $defaultCategory,
                        'word_type_id'      => $wordTypeId,
                        'ipaWord'           => $row['ipaWord'],
                        'original_word'     => $row['original_word'],
                        'original_meaning'  => $row['original_meaning'],
                        'original_example'  => $row['original_example'] ?? null,
                        'status'            => strtolower($row['status'] ?? $defaultStatus),
                        'word_image'        => $row['word_image'] ?? null,
                    ]);
                    
                    /**
                     * TRANSLATION (delegated)
                     */
                    if (!empty($row['translated_word'])) {
    
                        $translationService->createOrUpdateTranslation([
                            'word_id'            => $word->id,
                            'language_code'      => $translationLangCode,
                            'translated_word'    => $row['translated_word'],
                            'translated_meaning' => $row['translated_meaning'],
                            'translated_example' => $row['translated_example'],
                            'status'             => strtolower($row['status'] ?? $defaultStatus),
                        ]);
                    }
    
                    $success++;
    
                } catch (SPException $e) {
    
                    $failed++;
                    $errors[] = [
                        'row'   => $rowNumber,
                        'word'  => $row['original_word'] ?? null,
                        'error' => $e->getMessage()
                    ];
    
                } catch (\Throwable $e) {
                    $failed++;
                    $errors[] = [
                        'row'   => $rowNumber,
                        'word'  => $row['original_word'] ?? null,
                        'error' => $e->getMessage()
                    ];
                }
            }
    
            /**
             * Final validation mode
             */
            if ($validateOnly) {
                if ($failed > 0) { 
                    DEBUG($errors, true);
                    throw new SPException("Validation failed with {$failed} errors.");
                }
                return true;
            }
    
            if ($success === 0) {
                DEBUG($errors, true);
                throw new SPException("Import failed. No records created.");
            }
    
            Serve::commit();
            return true;
    
        } catch (SPException $e) {
            Serve::rollback();
            throw $e;
    
        } catch (\Throwable $e) {
            Serve::rollback();
            throw new SPException("Import process failed: " . $e->getMessage());
        }
    }
}
