<?php

namespace App\Services\Asteria;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class Magento1CustomerReader
{
    private const CUSTOMER_CODES = [
        'firstname',
        'lastname',
        'dob',
        'gender',
        'telephone',
        'password_hash',
        'default_billing',
        'default_shipping',
    ];

    private const ADDRESS_CODES = [
        'firstname',
        'lastname',
        'company',
        'street',
        'city',
        'region',
        'postcode',
        'country_id',
        'telephone',
    ];

    private Magento1Schema $schema;

    public function __construct(private string $connection = 'asteria')
    {
        $this->schema = new Magento1Schema($connection);
    }

    /**
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchCustomers(int $afterEntityId, int $limit): Collection
    {
        $query = DB::connection($this->connection)
            ->table('customer_entity')
            ->where('entity_id', '>', $afterEntityId)
            ->orderBy('entity_id')
            ->limit($limit);

        $staticColumns = $this->existingColumns('customer_entity', array_merge(
            ['entity_id', 'email', 'is_active', 'created_at', 'updated_at', 'group_id'],
            self::CUSTOMER_CODES
        ));

        $rows = $query->get($staticColumns);

        if ($rows->isEmpty()) {
            return collect();
        }

        $eav = $this->loadEavValues(
            'customer',
            'customer_entity',
            $rows->pluck('entity_id')->all(),
            self::CUSTOMER_CODES
        );

        return $rows->map(function ($row) use ($eav) {
            $id = (int) $row->entity_id;
            $merged = array_merge($eav[$id] ?? [], (array) $row);
            $merged['entity_id'] = $id;

            return $merged;
        })->values();
    }

    /**
     * @param  array<int, int>  $customerEntityIds
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchAddresses(array $customerEntityIds): Collection
    {
        if ($customerEntityIds === []) {
            return collect();
        }

        $staticColumns = $this->existingColumns('customer_address_entity', array_merge(
            ['entity_id', 'parent_id', 'created_at', 'is_active'],
            self::ADDRESS_CODES
        ));

        $rows = DB::connection($this->connection)
            ->table('customer_address_entity')
            ->whereIn('parent_id', $customerEntityIds)
            ->orderBy('entity_id')
            ->get($staticColumns);

        if ($rows->isEmpty()) {
            return collect();
        }

        $eav = $this->loadEavValues(
            'customer_address',
            'customer_address_entity',
            $rows->pluck('entity_id')->all(),
            self::ADDRESS_CODES
        );

        return $rows->map(function ($row) use ($eav) {
            $id = (int) $row->entity_id;
            $merged = array_merge($eav[$id] ?? [], (array) $row);
            $merged['entity_id'] = $id;
            $merged['parent_id'] = (int) $row->parent_id;

            return $merged;
        })->values();
    }

    /**
     * @param  array<int, string>  $codes
     * @return array<int, array<string, mixed>>
     */
    private function loadEavValues(
        string $entityTypeCode,
        string $valueTablePrefix,
        array $entityIds,
        array $codes
    ): array {
        if ($entityIds === []) {
            return [];
        }

        $attributes = $this->attributeMap($entityTypeCode, $codes);

        if ($attributes === []) {
            return [];
        }

        $byType = [];

        foreach ($attributes as $code => $attribute) {
            $type = $attribute->backend_type ?? 'varchar';

            if ($type === 'static' || $type === '') {
                continue;
            }

            $byType[$type][(int) $attribute->attribute_id] = $code;
        }

        $values = [];

        foreach ($byType as $type => $idToCode) {
            $table = $valueTablePrefix.'_'.$type;

            if (! $this->schema->hasTable($table)) {
                continue;
            }

            $rows = DB::connection($this->connection)
                ->table($table)
                ->select('entity_id', 'attribute_id', 'value')
                ->whereIn('entity_id', $entityIds)
                ->whereIn('attribute_id', array_keys($idToCode))
                ->get();

            foreach ($rows as $row) {
                $code = $idToCode[(int) $row->attribute_id] ?? null;

                if ($code === null || $row->value === null || $row->value === '') {
                    continue;
                }

                $values[(int) $row->entity_id][$code] = $row->value;
            }
        }

        return $values;
    }

    /**
     * @param  array<int, string>  $codes
     * @return array<string, object>
     */
    private function attributeMap(string $entityTypeCode, array $codes): array
    {
        $query = DB::connection($this->connection)
            ->table('eav_attribute')
            ->select('attribute_id', 'attribute_code', 'backend_type')
            ->whereIn('attribute_code', $codes);

        if ($this->schema->hasTable('eav_entity_type')) {
            $typeId = DB::connection($this->connection)
                ->table('eav_entity_type')
                ->where('entity_type_code', $entityTypeCode)
                ->value('entity_type_id');

            if ($typeId) {
                $query->where('entity_type_id', $typeId);
            }
        }

        return $query->get()->keyBy('attribute_code')->all();
    }

    /**
     * @param  array<int, string>  $candidates
     * @return array<int, string>
     */
    private function existingColumns(string $table, array $candidates): array
    {
        return $this->schema->existingColumns($table, $candidates);
    }
}