<?php

namespace App\Imports;

use Carbon\Carbon;
use App\Models\Publisher;
use App\Models\Scientist;
use App\Models\InstProject;
use App\Models\ProjectType;
use App\Models\ScienceArea;
use App\Models\ResearchField;
use App\Models\ExpertActivity;
use App\Models\WorkExperience;
use App\Models\ResearcherGroup;
use App\Models\ScienceDirection;
use App\Models\StudyPublication;
use App\Models\IntScienceProject;
use App\Models\NatScienceProject;
use App\Models\ResearcherSubGroup;
use Illuminate\Support\Collection;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use Maatwebsite\Excel\Concerns\ToCollection;

class ScientistsSheetOneImport implements ToCollection
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function collection(Collection $rows)
    {
        // Optionally, skip the header row if needed.
        foreach ($rows->skip(3) as $row) {
            // Assume:
            // Scientist
            // $row[1] = name
            // $row[2] = ???
            // $row[3] = email

            // Research Fields /research-fields
            // $row[4] = science-areas /science-areas
            // $row[5] = science-directions /science-directions
            // $row[6] = title
            // $row[7] = input_time

            // Researcher Groups
            // $row[8] = title

            // Researcher Sub Groups
            // $row[9] = title

            // /work-experiences Work Experiences
            // $row[10] = period_from period_to
            // $row[11] = institution
            // $row[12] = position

            // $row[13] = ???

            // Expert activities /expert-activities
            // $row[14] = activity_type option from (App\Models\ExpertActivity::ACTIVITY_TYPE_SELECT )
            // $row[15] = ???
            // $row[16] = period_from period_to
            // $row[17] = institution
            // $row[18] = position

            // Int Science Projects /int-science-projects
            // $row[19] = period_from period_to
            // $row[20] = title
            // $row[21] = number
            // $row[22] = budget
            // $row[23] = leader
            // Truksta leaded_participated pasirinkimo

            // Nat Science Projects /nat-science-projects
            // $row[24] = period_from period_to
            // $row[25] = title
            // $row[26] = number
            // $row[27] = budget
            // $row[28] = leader
            // Truksta leaded_participated pasirinkimo

            // Inst Projects /inst-projects
            // $row[29] = period_from period_to
            // $row[30] = to project_type_id create from /project-types
            // $row[31] = title
            // $row[32] = number
            // $row[33] = budget
            // $row[34] = leader
            // add leaded_participated always value leaded

            // Study Publications /study-publications
            // $row[35] = year
            // $row[36] = /science-direction find or create
            // $row[37] = title
            // $row[38] = input_time
            // $row[39] = authorship_field
            // $row[40] = page_count
            // $row[41] = /publishers firstorcreate
            // $row[42] = isbn
            // $row[43] = doi

            
            // Use updateOrCreate to create or update scientist
            if (empty($row[1])) {
                continue;
            }

            $scientist = Scientist::updateOrCreate(
                [
                    'name' => trim($row[1]),
                ],
                [
                    'email' => $row[2] ?? null,
                    'phone' => $row[3] ?? null,
                ]
            );
            // dd($row);
            $this->syncResearchFields($scientist, $row);
            $this->syncResearcherGroups($scientist, $row);
            $this->syncResearcherSubGroups($scientist, $row);
            $this->syncWorkExperiences($scientist, $row);
            $this->syncExpertActivities($scientist, $row);
            $this->syncIntScienceProjects($scientist, $row); //leaded_participated ka naudoti?
            $this->syncNatScienceProjects($scientist, $row); //leaded_participated ka naudoti?
            $this->syncInstProjects($scientist, $row);
            $this->syncStudyPublications($scientist, $row);
        }
    }

    protected function syncResearchFields(Scientist $scientist, $row)
    {
        // dd($row[2], $row[6], $row[7]);

        $researchTitle = trim($row[6] ?? '');
        $inputTime = trim($row[7] ?? '');

        if (empty($researchTitle) || empty($inputTime)) {
            return;
        }
        
        $researchField = ResearchField::updateOrCreate(
            [
                'title' => $researchTitle,
            ],
            [
                'input_time' => $inputTime,
            ]
        );

        $areaNames = array_filter(array_map('trim', explode(',', $row[4] ?? '')));
        foreach ($areaNames as $areaName) {
            if (!empty($areaName)) {
                $scienceArea = ScienceArea::firstOrCreate(['name' => $areaName]);
                $researchField->science_areas()->syncWithoutDetaching($scienceArea->id);
            }
        }

        $directionNames = array_filter(array_map('trim', explode(',', $row[5] ?? '')));
        foreach ($directionNames as $directionName) {
            if (!empty($directionName)) {
                $scienceDirection = ScienceDirection::firstOrCreate(['name' => $directionName]);
                $researchField->science_directions()->syncWithoutDetaching($scienceDirection->id);
            }
        }

        $scientist->scientistResearchFields()->syncWithoutDetaching($researchField->id);
    }

    protected function syncResearcherGroups(Scientist $scientist, $row)
    {
        $groupTitles = array_filter(array_map('trim', explode(',', $row[8] ?? '')));

        if (empty($groupTitles)) {
            return;
        }

        foreach ($groupTitles as $groupTitle) {
            $researcherGroup = ResearcherGroup::firstOrCreate([
                'title' => $groupTitle,
            ]);

            $scientist->groups()->syncWithoutDetaching($researcherGroup->id);
        }
    }

    protected function syncResearcherSubGroups(Scientist $scientist, $row)
    {
        $subGroupTitles = array_filter(array_map('trim', explode(',', $row[9] ?? '')));

        if (empty($subGroupTitles)) {
            return;
        }

        foreach ($subGroupTitles as $subGroupTitle) {
            $researcherSubGroup = ResearcherSubGroup::firstOrCreate([
                'title' => $subGroupTitle,
            ]);

            $scientist->subgroups()->syncWithoutDetaching($researcherSubGroup->id);
        }
    }

    protected function syncWorkExperiences(Scientist $scientist, $row)
    {
        $periodCell  = trim($row[10] ?? '');
        $institution = trim($row[11] ?? '');
        $position    = trim($row[12] ?? '');
    
        if (empty($periodCell) || empty($institution)) {
            return;
        }
    
        $periodParts = preg_split('/\s+/', $periodCell);
        if (count($periodParts) < 1) {
            return;
        }
    
        $period_from = $periodParts[0];
        $period_to   = isset($periodParts[1]) ? $periodParts[1] : null;

        if (empty($period_from)) {
            return;
        } elseif (is_numeric($period_from)) {
            $period_from = Date::excelToDateTimeObject($period_from)->format('Y-m-d');
        }
    
        $workExperience = WorkExperience::updateOrCreate(
            [
                'institution' => $institution,
                'period_from' => $period_from,
            ],
            [
                'position' => $position,
                'period_to'   => $period_to,
            ]
        );
    
        $scientist->scientistWorkExperiences()->syncWithoutDetaching($workExperience->id);
    }
    
    protected function syncExpertActivities(Scientist $scientist, $row)
    {
        $activityType = null;
    
        $row14 = trim($row[14] ?? '');
        $row15 = trim($row[15] ?? '');
        
        $positiveValues = ['taip', '+'];
        
        if (!empty($row14) && in_array(strtolower($row14), array_map('strtolower', $positiveValues))) {
            $activityType = 'courses';
        } elseif (!empty($row15) && in_array(strtolower($row15), array_map('strtolower', $positiveValues))) {
            $activityType = 'memership';
        }  

        if (empty($activityType)) {
            return;
        }

        $period = trim($row[16] ?? '');
        $institution = trim($row[17] ?? '');
        $position = trim($row[18] ?? '');

        if (empty($period) || empty($institution) || empty($position)) {
            return;
        }

        $periodParts = preg_split('/\s+/', $period);
        if (count($periodParts) < 1) {
            return;
        }

        $period_from = trim($periodParts[0] ?? '');
        $period_to = trim(isset($periodParts[1]) ? $periodParts[1] : null ?? '');

        if (empty($period_from)) {
            return;
        } elseif (is_numeric($period_from)) {
            $period_from = Date::excelToDateTimeObject($period_from)->format('Y-m-d');
        }

        $expertActivity = ExpertActivity::updateOrCreate(
            [
                'activity_type' => $activityType,
                'institution'   => $institution,
                'position'      => $position,
            ],
            [
                'period_from'   => $period_from,
                'period_to'     => $period_to,
            ]
        );

        $scientist->scientistExpertActivities()->syncWithoutDetaching($expertActivity->id);
    }

    protected function syncIntScienceProjects(Scientist $scientist, $row)
    {
        // $row[19] = period, $row[20] = title, $row[21] = number, $row[22] = budget, $row[23] = leader
        $period = trim($row[19] ?? '');
        $title = trim($row[20] ?? '');
        $number = trim($row[21] ?? '');
        $budget = trim($row[22] ?? '');
        $leader = trim($row[23] ?? '');

        if (empty($period) || empty($title)) {
            return;
        }

        $periodParts = explode(' ', $period);
        $period_from = trim($periodParts[0] ?? '');
        $period_to = trim(isset($periodParts[1]) ? $periodParts[1] : null ?? '');

        if (empty($period_from)) {
            return;
        } elseif (is_numeric($period_from)) {
            $period_from = Date::excelToDateTimeObject($period_from)->format('Y-m-d');
        }
        $leadedParticipated = $scientist->name == $leader || empty($leader) ? 'leaded' : 'participated';

        $intScienceProject = IntScienceProject::updateOrCreate(
            [
                'number' => $number,
            ],
            [
                'period_from'         => $period_from,
                'period_to'           => $period_to,
                'budget'              => $budget,
                'leader'              => $leader,
                'title'               => $title,
                'leaded_participated' => $leadedParticipated
            ]
        );

        $scientist->scientistIntScienceProjects()->syncWithoutDetaching($intScienceProject->id);
    }

    protected function syncNatScienceProjects(Scientist $scientist, $row)
    {
        // $row[24] = period, $row[25] = title, $row[26] = number, $row[27] = budget, $row[28] = leader
        $period = trim($row[24] ?? '');
        $title = trim($row[25] ?? '');
        $number = trim($row[26] ?? '');
        $budget = trim($row[27] ?? '');
        $leader = trim($row[28] ?? '');

        if (empty($period) || empty($title)) {
            return;
        }

        $periodParts = explode(' ', $period);
        $period_from = trim($periodParts[0] ?? '');
        $period_to = trim($periodParts[1] ?? '');

        if (empty($period_from)) {
            return;
        } elseif (is_numeric($period_from)) {
            $period_from = Date::excelToDateTimeObject($period_from)->format('Y-m-d');
        }

        $leadedParticipated = $scientist->name == $leader || empty($leader) ? 'leaded' : 'participated';

        $natScienceProject = NatScienceProject::updateOrCreate(
            [
                'number'      => $number,
            ],
            [
                'title'        => $title,
                'period_from' => $period_from,
                'period_to'   => $period_to,
                'budget'      => $budget,
                'leader'      => $leader,
                'leaded_participated' => $leadedParticipated,
            ]
        );

        $scientist->scientistNatScienceProjects()->syncWithoutDetaching($natScienceProject->id);
    }

    protected function syncInstProjects(Scientist $scientist, $row)
    {
        // $row[29] = period, $row[30] = project type, $row[31] = title, $row[32] = number, $row[33] = budget, $row[34] = leader
        $period = trim($row[29] ?? '');
        $projectTypeName = trim($row[30] ?? '');
        $title = trim($row[31] ?? '');
        $number = trim($row[32] ?? '');
        $budget = trim($row[33] ?? '');
        $leader = trim($row[34] ?? '');

        if (empty($period) || empty($projectTypeName) || empty($title)) {
            return;
        }

        $periodParts = explode(' ', $period);
        $period_from = trim($periodParts[0] ?? '');
        $period_to = trim($periodParts[1] ?? '');

        if (empty($period_from)) {
            return;
        } elseif (is_numeric($period_from)) {
            $period_from = Date::excelToDateTimeObject($period_from)->format('Y-m-d');
        }

        // Create or find the project type
        $projectType = ProjectType::firstOrCreate([
            'name' => $projectTypeName,
        ]);

        $leadedParticipated = $scientist->name == $leader || empty($leader) ? 'leaded' : 'participated';

        $instProject = InstProject::updateOrCreate(
            [
                'number'       => $number,
            ],
            [
                'title'               => $title,
                'period_from'         => $period_from,
                'period_to'           => $period_to,
                'project_type_id'     => $projectType->id,
                'budget'              => $budget,
                'leader'              => $leader,
                'leaded_participated' => $leadedParticipated,          
            ]
        );

        $scientist->scientistInstProjects()->syncWithoutDetaching($instProject->id);
        
    }

    protected function syncStudyPublications(Scientist $scientist, $row)
    {
        // $row[35] = year, $row[36] = science-direction, $row[37] = title,
        // $row[38] = input_time, $row[39] = authorship_field, $row[40] = page_count,
        // $row[41] = publisher, $row[42] = isbn, $row[43] = doi
        $yearRaw = trim($row[35] ?? '');
        $scienceDirectionName = trim($row[36] ?? '');
        $title = trim($row[37] ?? '');
        $inputTime = trim($row[38] ?? '');
        $authorshipField = trim($row[39] ?? '');
        $pageCount = trim($row[40] ?? '');
        $publisherName = trim($row[41] ?? '');
        $isbn = trim($row[42] ?? '');
        $doi = trim($row[43] ?? '');

        if (empty($yearRaw) || empty($title) || empty($inputTime)) {
            return;
        }

        $scienceDirection = ScienceDirection::firstOrCreate([
            'name' => $scienceDirectionName,
        ]);

        $publisher = Publisher::firstOrCreate([
            'name' => $publisherName,
        ]);    


        if (is_numeric($yearRaw)) {
            $year = Date::excelToDateTimeObject($yearRaw)->format('Y-m-d');
        } else {
            $year = Carbon::parse($yearRaw)->toDateString();
        }

        $studyPublication = StudyPublication::updateOrCreate(
            [
                'isbn'                => $isbn,
            ],
            [
                'year'                => Carbon::parse($year)->toDateString(),
                'title'               => $title,
                'input_time'          => $inputTime,
                'authorship_field'    => $authorshipField,
                'page_count'          => $pageCount,
                'doi'                 => $doi,
                'publisher_id'        => $publisher->id,
            ]
        );

        $studyPublication->science_directions()->syncWithoutDetaching($scienceDirection->id);
        $scientist->scientistStudyPublications()->syncWithoutDetaching($studyPublication->id);   
    }

/**
 * Attempts to match the given input to a valid activity type key.
 *
 * @param string $input The raw activity type value from the Excel file.
 * @return string|null  Returns the matched key from ACTIVITY_TYPE_SELECT or null if none found.
 */
    // protected function matchActivityType(string $input, $validTypes)
    // {
    //     $inputLower = strtolower($input);

    //     $bestMatch   = null;
    //     $minDistance = PHP_INT_MAX;

    //     foreach ($validTypes as $key => $value) {
    //         // Calculate the Levenshtein distance between the input and the current key.
    //         $distance = levenshtein($inputLower, strtolower($key));

    //         // Check if this is an exact match.
    //         if ($distance === 0) {
    //             return $key;
    //         }

    //         // If this distance is smaller than any we've seen so far, record it.
    //         if ($distance < $minDistance) {
    //             $minDistance = $distance;
    //             $bestMatch = $key;
    //         }
    //     }

    //     // Optional: set a threshold to avoid matching completely off values.
    //     // For example, if the distance is too high, consider no match.
    //     $threshold = 3; // Adjust this threshold as needed.
    //     if ($minDistance > $threshold) {
    //         return null;
    //     }

    //     return $bestMatch;
    // }


}