<?php

namespace App\Services;

use App\Enums\MemberStatusEnum;
use App\Enums\MemberTypeEnum;
use App\Enums\RelationEnum;
use App\Models\Academy;
use App\Models\Dependent;
use App\Models\Member;
use Carbon\Carbon;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;
use Symfony\Component\HttpFoundation\StreamedResponse;

class AcademyMemberDependentsBundleImportService
{
    public const DEPENDENT_SLOTS = 5;

    /** Columns per dependent slot: name, relation, dob, phone (status defaults to Active). */
    public const DEPENDENT_COLUMNS_PER_SLOT = 4;

    /**
     * One spreadsheet row = one primary member (by phone) + up to 5 dependents.
     * Creates missing members/dependents, updates existing when matched, attaches all to the academy.
     *
     * @param  array<int, array<string, string|null>>  $rows  Normalized associative rows (header keys).
     * @return array<string, mixed>
     */
    public function importBundles(Academy $academy, array $rows): array
    {
        $membersCreated = 0;
        $membersUpdated = 0;
        $dependentsCreated = 0;
        $dependentsUpdated = 0;
        $skipped = 0;
        $errors = [];

        foreach ($rows as $index => $row) {
            $line = $index + 2;

            if ($this->isBlankBundleRow($row)) {
                continue;
            }

            $memberName = trim((string) ($row['member_name'] ?? $row['name'] ?? ''));
            $memberPhone = $this->normalizePhone($row['member_phone'] ?? $row['phone'] ?? null);
            $hasDependentNames = $this->rowHasAnyDependentName($row);

            if (($memberName === '' || $memberPhone === null || $memberPhone === '') && ! $hasDependentNames) {
                continue;
            }

            if ($memberName === '' || $memberPhone === null || $memberPhone === '') {
                $skipped++;
                if (count($errors) < 150) {
                    $errors[] = "Row {$line}: member name and phone are required when dependent data is present.";
                }
                continue;
            }

            $memberPayload = $this->extractMemberPayload($row);
            $member = Member::query()->where('phone', $memberPhone)->first();

            if (! $member) {
                $create = array_merge([
                    'name' => $memberName,
                    'phone' => $memberPhone,
                    'password' => Hash::make('12345678'),
                    'member_type' => MemberTypeEnum::ACADEMIC->value,
                    'status' => MemberStatusEnum::ACTIVE->value,
                ], $this->onlyFillableMember(array_merge($memberPayload, [
                    'name' => $memberName,
                    'phone' => $memberPhone,
                ])));

                $create['member_type'] = MemberTypeEnum::ACADEMIC->value;
                $create['status'] = MemberStatusEnum::ACTIVE->value;

                foreach (['email', 'dob', 'address', 'start_date_subscription', 'end_date_subscription'] as $k) {
                    if (array_key_exists($k, $create) && ($create[$k] === null || $create[$k] === '')) {
                        unset($create[$k]);
                    }
                }

                $member = Member::query()->create($create);
                $membersCreated++;
            } else {
                $update = $this->onlyFillableMember(array_merge($memberPayload, [
                    'name' => $memberName,
                ]));
                unset($update['phone']);
                $update = array_filter(
                    $update,
                    static fn ($v) => $v !== null && $v !== ''
                );
                if ($update !== []) {
                    $member->fill($update);
                    $member->save();
                    $membersUpdated++;
                }
            }

            $dependentIds = [];
            for ($i = 1; $i <= self::DEPENDENT_SLOTS; $i++) {
                $depName = trim((string) ($row["dep{$i}_name"] ?? ''));
                if ($depName === '') {
                    continue;
                }

                $depDob = $this->parseDate($row["dep{$i}_dob"] ?? null);
                $depPhone = $this->normalizePhone($row["dep{$i}_phone"] ?? null);
                $depRelation = $this->normalizeRelation($row["dep{$i}_relation"] ?? null) ?? RelationEnum::SON->value;
                $depStatus = MemberStatusEnum::ACTIVE->value;

                $dep = $this->findDependent((int) $member->id, $depName, $depPhone, $depDob);

                $depAttrs = [
                    'name' => $depName,
                    'relation' => $depRelation,
                    'dob' => $depDob,
                    'phone' => $depPhone,
                    'status' => $depStatus,
                    'assigned_member_id' => (int) $member->id,
                ];

                if (! $dep) {
                    $dep = Dependent::query()->create($depAttrs);
                    $dependentsCreated++;
                } else {
                    $dep->fill([
                        'relation' => $depRelation,
                        'dob' => $depDob,
                        'phone' => $depPhone,
                        'status' => $depStatus,
                    ]);
                    $dep->save();
                    $dependentsUpdated++;
                }

                if ($dep) {
                    $dependentIds[] = (int) $dep->id;
                }
            }

            $syncData = [(int) $member->id => ['assignment_type' => 1]];
            $academy->members()->syncWithoutDetaching($syncData);

            $dependentIds = array_values(array_unique(array_filter($dependentIds)));
            if ($dependentIds !== []) {
                $academy->enrolledDependents()->syncWithoutDetaching($dependentIds);
            }
        }

        return [
            'members_created' => $membersCreated,
            'members_updated' => $membersUpdated,
            'dependents_created' => $dependentsCreated,
            'dependents_updated' => $dependentsUpdated,
            'added' => $membersCreated + $dependentsCreated,
            'skipped' => $skipped,
            'errors' => $errors,
        ];
    }

    public function streamBundleTemplate(?string $downloadFilename = null): StreamedResponse
    {
        return app(BundleImportExcelTemplateService::class)->streamAcademyBundleTemplate(
            $downloadFilename ?? 'academy_member_dependents_import_template.xlsx'
        );
    }

    /**
     * @return list<string>
     */
    public function templateHeaderRow(): array
    {
        $h = [
            'member_name',
            'member_phone',
            'member_email',
            'member_dob',
            'member_address',
            'member_start_date_subscription',
            'member_end_date_subscription',
        ];
        for ($i = 1; $i <= self::DEPENDENT_SLOTS; $i++) {
            $h[] = "dep{$i}_name";
            $h[] = "dep{$i}_relation";
            $h[] = "dep{$i}_dob";
            $h[] = "dep{$i}_phone";
        }

        return $h;
    }

    /**
     * @param  array<string, string|null>  $row
     * @return array<string, mixed>
     */
    private function extractMemberPayload(array $row): array
    {
        $out = [];

        $email = trim((string) ($row['member_email'] ?? $row['email'] ?? ''));
        if ($email !== '') {
            $out['email'] = $email;
        }

        $address = trim((string) ($row['member_address'] ?? $row['address'] ?? ''));
        if ($address !== '') {
            $out['address'] = $address;
        }

        $dob = $this->parseDate($row['member_dob'] ?? null);
        if ($dob !== null) {
            $out['dob'] = $dob;
        }

        $sd = $this->parseDate($row['member_start_date_subscription'] ?? $row['start_date_subscription'] ?? null);
        if ($sd !== null) {
            $out['start_date_subscription'] = $sd;
        }

        $ed = $this->parseDate($row['member_end_date_subscription'] ?? $row['end_date_subscription'] ?? null);
        if ($ed !== null) {
            $out['end_date_subscription'] = $ed;
        }

        return $out;
    }

    /**
     * @param  array<string, mixed>  $data
     * @return array<string, mixed>
     */
    private function onlyFillableMember(array $data): array
    {
        $allowed = array_flip([
            'name', 'phone', 'dob', 'address', 'email', 'status',
            'start_date_subscription', 'end_date_subscription', 'password',
        ]);

        return array_intersect_key($data, $allowed);
    }

    private function findDependent(int $memberId, string $name, ?string $phone, ?string $dob): ?Dependent
    {
        $q = Dependent::query()
            ->where('assigned_member_id', $memberId)
            ->whereRaw('LOWER(TRIM(name)) = ?', [Str::lower(trim($name))]);

        if ($phone !== null && $phone !== '') {
            $q->where('phone', $phone);
        } else {
            $q->where(function ($q2) {
                $q2->whereNull('phone')->orWhere('phone', '');
            });
        }

        if ($dob !== null && $dob !== '') {
            $q->whereDate('dob', $dob);
        }

        return $q->first();
    }

    /**
     * Rows where every cell is empty (or whitespace / numeric zero) are ignored —
     * e.g. trailing rows from Excel validation ranges.
     */
    private function isBlankBundleRow(array $row): bool
    {
        foreach ($row as $value) {
            if ($this->importCellHasMeaningfulContent($value)) {
                return false;
            }
        }

        return true;
    }

    private function rowHasAnyDependentName(array $row): bool
    {
        for ($i = 1; $i <= self::DEPENDENT_SLOTS; $i++) {
            if (trim((string) ($row["dep{$i}_name"] ?? '')) !== '') {
                return true;
            }
        }

        return false;
    }

    private function importCellHasMeaningfulContent(mixed $value): bool
    {
        if ($value === null) {
            return false;
        }
        if (is_bool($value)) {
            return $value;
        }
        if (is_numeric($value)) {
            return ((float) $value) != 0.0;
        }
        $s = trim((string) $value);
        if ($s === '') {
            return false;
        }
        if (preg_match('/^="(.*)"$/', $s, $m)) {
            $s = trim((string) $m[1]);
        }

        return $s !== '';
    }

    /**
     * Accepts ISO dates (2026-02-17), other parseable strings, and Excel serial numbers.
     */
    private function parseDate(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }
        if ($value instanceof \DateTimeInterface) {
            return $value->format('Y-m-d');
        }
        if (is_numeric($value) && ! is_string($value)) {
            try {
                $dt = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject((float) $value);
                $y = (int) $dt->format('Y');
                if ($y >= 1950 && $y <= 2100) {
                    return $dt->format('Y-m-d');
                }
            } catch (\Throwable) {
                return null;
            }

            return null;
        }

        $v = trim((string) $value);
        if ($v === '') {
            return null;
        }
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $v, $m)) {
            $y = (int) $m[1];
            $mo = (int) $m[2];
            $d = (int) $m[3];
            if (checkdate($mo, $d, $y)) {
                return sprintf('%04d-%02d-%02d', $y, $mo, $d);
            }

            return null;
        }
        if (is_numeric($v) && ! str_contains(strtolower($v), 'e')) {
            try {
                $dt = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject((float) $v);
                $y = (int) $dt->format('Y');
                if ($y >= 1950 && $y <= 2100) {
                    return $dt->format('Y-m-d');
                }
            } catch (\Throwable) {
                // fall through to Carbon
            }
        }
        try {
            return Carbon::parse($v)->toDateString();
        } catch (\Throwable) {
            return null;
        }
    }

    private function normalizePhone(?string $value): ?string
    {
        if ($value === null) {
            return null;
        }
        $v = trim($value);
        if ($v === '') {
            return null;
        }
        if (preg_match('/^="(.*)"$/', $v, $m)) {
            return trim((string) $m[1]);
        }

        return $v;
    }

    private function normalizeStatus(?string $value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }
        if (is_numeric($value)) {
            $i = (int) $value;
            if (in_array($i, MemberStatusEnum::values(), true)) {
                return $i;
            }

            return null;
        }
        $normalized = strtolower(trim($value));

        return match ($normalized) {
            'pending' => MemberStatusEnum::PENDING->value,
            'active' => MemberStatusEnum::ACTIVE->value,
            'suspended' => MemberStatusEnum::SUSPENDED->value,
            'expired' => MemberStatusEnum::EXPIRED->value,
            default => null,
        };
    }

    private function normalizeRelation(?string $value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }
        if (is_numeric($value)) {
            $i = (int) $value;
            if (in_array($i, RelationEnum::values(), true)) {
                return $i;
            }

            return null;
        }
        $normalized = strtolower(trim($value));

        return match ($normalized) {
            'husband' => RelationEnum::HUSBAND->value,
            'wife' => RelationEnum::WIFE->value,
            'son' => RelationEnum::SON->value,
            'daughter' => RelationEnum::DAUGHTER->value,
            'other' => RelationEnum::OTHER->value,
            default => null,
        };
    }
}
