<?php

namespace App\Imports;

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

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

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

    public function __construct($defaultMonth = null)
    {
        if ($defaultMonth) {
            // Convert Y-m format to Y-m-01 format
            $this->defaultMonth = Carbon::createFromFormat('Y-m', $defaultMonth)->format('Y-m-01');
        } else {
            $this->defaultMonth = Carbon::now()->format('Y-m-01');
        }
    }

    public function collection(Collection $rows)
    {
        foreach ($rows as $row) {
            try {
                // Get member name and group name
                $memberName = $this->getValue($row, ['name_of_vsla_member', 'name']);
                $groupName = $this->getValue($row, ['group_name']);
                
                if (empty($memberName) || empty($groupName)) {
                    $this->skippedCount++;
                    continue;
                }

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

                // Get Individual ID
                $individualId = intval($this->getValue($row, ['individual_id', 'individual_id_1']) ?? 0);
                if ($individualId === 0) {
                    $individualId = $this->importedCount + $this->updatedCount + 1;
                }

                // Determine gender
                $genderRaw = strtolower($this->getValue($row, ['gender']) ?? 'female');
                $gender = 'female';
                if ($genderRaw === 'male') {
                    $gender = 'male';
                }

                // Get age
                $age = intval($this->getValue($row, ['age']) ?? 0);

                // Map training completions from Excel columns
                $completedCore2 = $this->mapTrainingStatus($this->getValue($row, ['vsla_operations_management_and_leadership']));
                $completedCore1 = $this->mapTrainingStatus($this->getValue($row, ['functional_literacy_and_financial_literacy_management']));
                $completedCore3 = $this->mapTrainingStatus($this->getValue($row, ['business_development_and_entrepreneurship']));
                $completedCore4 = $this->mapTrainingStatus($this->getValue($row, ['digital_and_information_literacy']));

                // Map certificate (Received Certificate column)
                $receivedCertificate = $this->mapYesNo($this->getValue($row, ['received_certificate']));

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

                // Get existing income activities
                $existingIncomeActivities = $this->getValue($row, ['existing_main_income_generation_activities_activities']);

                // Determine category from Excel (if exists)
                $category = $this->getValue($row, ['category']);
                $memberCategory = 'new';
                if (!empty($category)) {
                    $catLower = strtolower(trim($category));
                    if (in_array($catLower, ['mature', 'progressive', 'new'])) {
                        $memberCategory = $catLower;
                    }
                }

                // Check if member already exists
                $existingMember = C1Member::where('group_id', $group->id)
                    ->where('serial_no', $individualId)
                    ->first();

                $memberData = [
                    'group_id' => $group->id,
                    'serial_no' => $individualId,
                    'name' => trim($memberName),
                    'category' => $memberCategory,
                    'gender' => $gender,
                    'age' => $age,
                    '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' => $existingIncomeActivities,
                    '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);
                    $this->updatedCount++;
                } 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' || $value === 'n/a') ? 1 : 0;
    }

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

    protected function getValue($row, $keys)
    {
        foreach ($keys as $key) {
            // Handle the special column name with spaces from Excel
            $normalizedKey = str_replace('_', ' ', $key);
            foreach ($row as $rowKey => $rowValue) {
                $normalizedRowKey = strtolower(str_replace('_', ' ', $rowKey));
                if ($normalizedRowKey === $normalizedKey || $rowKey === $key) {
                    if (!empty($rowValue)) {
                        return $rowValue;
                    }
                }
            }
        }
        return null;
    }

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

    public function getUpdatedCount()
    {
        return $this->updatedCount;
    }

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

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