<?php

namespace App\Imports;

use App\Models\C1Group;
use App\Models\C1Member;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\SkipsFailures;
use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Support\Collection;
use Carbon\Carbon;

class MembersImport implements ToCollection, WithHeadingRow, SkipsOnFailure
{
    use SkipsFailures;

    protected $importedCount = 0;
    protected $skippedCount = 0;
    protected $errors = [];
    protected $defaultMonth;

    public function __construct($defaultMonth = null)
    {
        $this->defaultMonth = $defaultMonth ?? Carbon::now()->format('Y-m-01');
    }

    public function collection(Collection $rows)
    {
        foreach ($rows as $row) {
            try {
                // Skip if essential data is missing
                if (empty($row['name_of_vsla_member']) || empty($row['group_name'])) {
                    $this->skippedCount++;
                    continue;
                }

                // Find the group
                $group = C1Group::where('name', $row['group_name'])->first();
                
                if (!$group) {
                    $this->errors[] = "Group '{$row['group_name']}' not found for member '{$row['name_of_vsla_member']}'";
                    $this->skippedCount++;
                    continue;
                }

                // Determine gender from the Gender column
                $gender = 'female';
                $genderRaw = strtolower($row['gender'] ?? '');
                if ($genderRaw === 'male') {
                    $gender = 'male';
                } elseif ($genderRaw === 'adol.girl' || $genderRaw === 'adolescent_girl') {
                    $gender = 'female';
                }

                // Map training completions
                $completedCore1 = $this->mapTrainingStatus($row['functional_literacy_and_financial_literacy_management'] ?? null);
                $completedCore2 = $this->mapTrainingStatus($row['vsla_operations_management_and_leadership'] ?? null);
                $completedCore3 = $this->mapTrainingStatus($row['business_development_and_entrepreneurship'] ?? null);
                $completedCore4 = $this->mapTrainingStatus($row['digital_and_information_literacy'] ?? null);

                // Map certificate
                $receivedCertificate = $this->mapYesNo($row['received_certificate'] ?? null);

                // Map started new business
                $startedNewBusiness = $this->mapYesNo($row['new_business_created_and_operational_as_a_result_of_sswseep'] ?? null);

                // Check if member already exists
                $existingMember = C1Member::where('group_id', $group->id)
                    ->where('serial_no', $row['individual_id'] ?? 0)
                    ->first();

                $memberData = [
                    'group_id' => $group->id,
                    'serial_no' => $row['individual_id'] ?? 0,
                    'name' => trim($row['name_of_vsla_member']),
                    'category' => $this->determineCategory($row),
                    'gender' => $gender,
                    'age' => $row['age'] ?? 0,
                    'active' => 1,
                    'exec_committee' => 0,
                    'disability_status' => 0,
                    'head_household' => 0,
                    'completed_core_1' => $completedCore1,
                    'completed_core_2' => $completedCore2,
                    'completed_core_3' => $completedCore3,
                    'completed_core_4' => $completedCore4,
                    'received_specialized_1' => 0,
                    'existing_income_activities' => $row['existing_main_income_generation_activities_activities'] ?? null,
                    'received_certificate' => $receivedCertificate,
                    'started_new_business' => $startedNewBusiness,
                    'actively_saving_monthly' => 1,
                    'total_savings' => 0,
                    'outstanding_loan' => 0,
                    'month' => $this->defaultMonth,
                    'updated_at' => now(),
                ];

                if ($existingMember) {
                    $existingMember->update($memberData);
                } else {
                    $memberData['created_at'] = now();
                    C1Member::create($memberData);
                }
                
                $this->importedCount++;
                
            } catch (\Exception $e) {
                $this->errors[] = "Error importing member: " . $e->getMessage();
                $this->skippedCount++;
            }
        }
    }

    protected function mapTrainingStatus($value)
    {
        if (empty($value)) return 0;
        $value = strtolower(trim($value));
        return ($value === 'completed' || $value === 'yes') ? 1 : 0;
    }

    protected function mapYesNo($value)
    {
        if (empty($value)) return 0;
        $value = strtolower(trim($value));
        return ($value === 'yes' || $value === 'completed') ? 1 : 0;
    }

    protected function determineCategory($row)
    {
        if (!empty($row['category'])) {
            $category = strtolower($row['category']);
            if (in_array($category, ['mature', 'progressive', 'new'])) {
                return $category;
            }
        }
        return 'new';
    }

    public function getImportedCount()
    {
        return $this->importedCount;
    }

    public function getSkippedCount()
    {
        return $this->skippedCount;
    }

    public function getErrors()
    {
        return $this->errors;
    }
}