<?php

declare(strict_types=1);

namespace App\Jobs;

use App\Enums\ImportStatus;
use App\Models\Branch;
use App\Models\ImportJob;
use App\Models\Member;
use App\Services\DashboardService;
use App\Services\ImportRowValidator;
use App\Services\MemberCsvColumns;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use League\Csv\Reader;
use League\Csv\Statement;
use League\Csv\Writer;

class ProcessImportChunkJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    private const CHUNK_SIZE = 500;

    public function __construct(
        private readonly int $importJobId,
        private readonly int $startRow,
    ) {}

    public function handle(ImportRowValidator $validator, DashboardService $dashboard): void
    {
        $job = ImportJob::find($this->importJobId);

        if ($job === null || $job->status !== ImportStatus::Processing) {
            return;
        }

        try {
            $path = Storage::disk('local')->path($job->source_path);
            $csv = Reader::createFromPath($path, 'r');
            $csv->setHeaderOffset(0);

            $records = Statement::create()->offset($this->startRow)->limit(self::CHUNK_SIZE)->process($csv);

            $branchCodeMap = $job->branch_id === null ? Branch::query()->pluck('id', 'code')->all() : [];
            $defaultStatus = $job->options['default_status'] ?? null;
            $dedupeEnabled = ($job->options['dedupe'] ?? null) === 'phone_or_email';

            $valid = [];
            $invalid = [];
            $rowCount = 0;

            foreach ($records as $offset => $record) {
                $rowCount++;
                $rowNumber = $offset + 1; // league/csv keys records by their 0-based file row (header = row 0)

                $result = $validator->validate($record, $job->branch_id, $branchCodeMap, $defaultStatus);

                if (! $result['ok']) {
                    $invalid[] = ['row_number' => $rowNumber, 'record' => $record, 'error' => $result['error']];

                    continue;
                }

                $valid[] = [
                    'row_number' => $rowNumber,
                    'record' => $record,
                    'branch_id' => $result['branch_id'],
                    'attributes' => $result['attributes'],
                ];
            }

            if ($dedupeEnabled) {
                [$valid, $dedupeRejects] = $this->applyDedupe($valid);
                $invalid = [...$invalid, ...$dedupeRejects];
            }

            $createdCount = DB::transaction(fn () => $this->insertValidRows($job, $valid));

            foreach (array_unique(array_column($valid, 'branch_id')) as $branchId) {
                $dashboard->bustForBranch($branchId);
            }

            if ($invalid !== []) {
                $this->appendErrorReport($job, $invalid);
            }

            $job->update([
                'processed_rows' => $job->processed_rows + $rowCount,
                'created_rows' => $job->created_rows + $createdCount,
                'failed_rows' => $job->failed_rows + count($invalid),
            ]);

            if ($rowCount === self::CHUNK_SIZE) {
                self::dispatch($job->id, $this->startRow + self::CHUNK_SIZE);

                return;
            }

            $job->update([
                'status' => $job->failed_rows > 0 ? ImportStatus::CompletedWithErrors : ImportStatus::Completed,
            ]);
        } catch (\Throwable $e) {
            $job->update(['status' => ImportStatus::Failed, 'error_message' => $e->getMessage()]);
        }
    }

    /**
     * @param  array<int, array{row_number: int, record: array, branch_id: int, attributes: array}>  $valid
     * @return array{0: array, 1: array<int, array{row_number: int, record: array, error: string}>}
     */
    private function applyDedupe(array $valid): array
    {
        $byBranch = [];

        foreach ($valid as $row) {
            $byBranch[$row['branch_id']][] = $row;
        }

        $kept = [];
        $rejected = [];

        foreach ($byBranch as $branchId => $rows) {
            $phones = array_values(array_filter(array_map(
                fn ($r) => $this->normalizePhone($r['attributes']['phone_primary'] ?? null),
                $rows,
            )));
            $emails = array_values(array_filter(array_map(fn ($r) => $r['attributes']['email'] ?? null, $rows)));

            $existingPhones = $phones === [] ? [] : Member::query()
                ->where('branch_id', $branchId)
                ->whereIn('phone_primary', $phones)
                ->pluck('phone_primary')
                ->all();

            $existingEmails = $emails === [] ? [] : Member::query()
                ->where('branch_id', $branchId)
                ->whereIn('email', $emails)
                ->pluck('email')
                ->all();

            foreach ($rows as $row) {
                $phone = $this->normalizePhone($row['attributes']['phone_primary'] ?? null);
                $email = $row['attributes']['email'] ?? null;

                $isDuplicate = ($phone !== null && in_array($phone, $existingPhones, true))
                    || ($email !== null && in_array($email, $existingEmails, true));

                if ($isDuplicate) {
                    $rejected[] = [
                        'row_number' => $row['row_number'],
                        'record' => $row['record'],
                        'error' => 'Duplicate: a member with this phone or email already exists in this branch.',
                    ];
                } else {
                    $kept[] = $row;
                }
            }
        }

        return [$kept, $rejected];
    }

    /**
     * @param  array<int, array{row_number: int, record: array, branch_id: int, attributes: array}>  $valid
     */
    private function insertValidRows(ImportJob $job, array $valid): int
    {
        if ($valid === []) {
            return 0;
        }

        $now = now();
        $nowStr = $now->toDateTimeString();
        $rows = [];

        foreach ($valid as $row) {
            $attributes = $row['attributes'];

            $rows[] = [
                'branch_id' => $row['branch_id'],
                'household_id' => null,
                'household_role' => null,
                'title' => $attributes['title'] ?? null,
                'first_name' => $attributes['first_name'],
                'middle_name' => $attributes['middle_name'] ?? null,
                'last_name' => $attributes['last_name'],
                'preferred_name' => null,
                'gender' => $attributes['gender'],
                'date_of_birth' => $attributes['date_of_birth'] ?? null,
                'marital_status' => $attributes['marital_status'] ?? null,
                'email' => $attributes['email'] ?? null,
                'phone_primary' => $this->normalizePhone($attributes['phone_primary'] ?? null),
                'phone_secondary' => $this->normalizePhone($attributes['phone_secondary'] ?? null),
                'address_line1' => $attributes['address_line1'] ?? null,
                'address_line2' => $attributes['address_line2'] ?? null,
                'city' => $attributes['city'] ?? null,
                'state_province' => $attributes['state_province'] ?? null,
                'postal_code' => $attributes['postal_code'] ?? null,
                'country' => $attributes['country'] ?? null,
                'photo_path' => null,
                'membership_status' => $attributes['membership_status'],
                'date_joined' => $attributes['date_joined'] ?? null,
                'date_baptized' => $attributes['date_baptized'] ?? null,
                'baptism_type' => null,
                'occupation' => $attributes['occupation'] ?? null,
                'employer' => null,
                'emergency_contact_name' => null,
                'emergency_contact_phone' => null,
                'emergency_contact_relationship' => null,
                'custom_fields' => null,
                'created_by' => $job->user_id,
                'updated_by' => $job->user_id,
                'member_number' => null,
                'created_at' => $nowStr,
                'updated_at' => $nowStr,
            ];
        }

        DB::table('members')->insert($rows);

        // member_number is only ever NULL transiently, within this same
        // transaction, between insert and backfill — docs guarantee no
        // committed rows are ever left without one. That makes it a safe way
        // to recover the exact IDs just inserted: MySQL's auto_increment
        // counter is not gap-free after a rolled-back transaction, so a
        // naive before/after MAX(id) range can miscount.
        $insertedIds = DB::table('members')->whereNull('member_number')->orderBy('id')->pluck('id')->all();

        DB::table('members')
            ->whereIn('id', $insertedIds)
            ->update(['member_number' => DB::raw("CONCAT('M', LPAD(id, 8, '0'))")]);

        $statusHistoryRows = [];
        $auditRows = [];

        foreach ($valid as $index => $row) {
            $id = $insertedIds[$index];

            $statusHistoryRows[] = [
                'member_id' => $id,
                'from_status' => null,
                'to_status' => $row['attributes']['membership_status'],
                'reason' => null,
                'effective_date' => $now->toDateString(),
                'changed_by' => null,
                'created_at' => $nowStr,
            ];

            $auditRows[] = [
                'user_id' => $job->user_id,
                'action' => 'imported',
                'auditable_type' => Member::class,
                'auditable_id' => $id,
                'old_values' => null,
                'new_values' => json_encode($rows[$index]),
                'ip_address' => null,
                'created_at' => $nowStr,
            ];
        }

        DB::table('member_status_histories')->insert($statusHistoryRows);
        DB::table('audit_logs')->insert($auditRows);

        return count($valid);
    }

    /**
     * @param  array<int, array{row_number: int, record: array, error: string}>  $invalid
     */
    private function appendErrorReport(ImportJob $job, array $invalid): void
    {
        $errorPath = $job->error_report_path ?? dirname($job->source_path).'/errors.csv';
        $absolutePath = Storage::disk('local')->path($errorPath);

        $isNew = ! is_file($absolutePath);

        if ($isNew) {
            Storage::disk('local')->makeDirectory(dirname($errorPath));
        }

        $writer = Writer::createFromPath($absolutePath, $isNew ? 'w' : 'a');

        if ($isNew) {
            $writer->insertOne([...MemberCsvColumns::templateColumns(), 'row_number', 'error']);
        }

        foreach ($invalid as $row) {
            $line = [];
            foreach (MemberCsvColumns::templateColumns() as $column) {
                $line[] = $row['record'][$column] ?? '';
            }
            $line[] = $row['row_number'];
            $line[] = $row['error'];
            $writer->insertOne($line);
        }

        if ($job->error_report_path === null) {
            $job->update(['error_report_path' => $errorPath]);
        }
    }

    private function normalizePhone(?string $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $digits = preg_replace('/\D+/', '', $value);

        return $digits === '' ? null : $digits;
    }
}
