<?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',
        'source',
        'customer_source',
    ];

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

    private Magento1Schema $schema;

    /** @var array<string, array<string, object>> */
    private array $attributeMaps = [];

    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();
    }

    /**
     * Magento 1 newsletter_subscriber rows for the given customers (by entity_id or email).
     *
     * @param  array<int, int>  $customerEntityIds
     * @param  array<int, string>  $emails
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchSubscribers(array $customerEntityIds, array $emails): Collection
    {
        $columns = $this->subscriberColumns();

        if ($columns === []) {
            return collect();
        }

        $customerEntityIds = array_values(array_unique(array_filter(array_map('intval', $customerEntityIds))));
        $emails = array_values(array_unique(array_filter(array_map(
            fn ($email) => strtolower(trim((string) $email)),
            $emails
        ))));

        if ($customerEntityIds === [] && $emails === []) {
            return collect();
        }

        $query = DB::connection($this->connection)->table('newsletter_subscriber');

        $query->where(function ($inner) use ($customerEntityIds, $emails, $columns) {
            if ($customerEntityIds !== [] && in_array('customer_id', $columns, true)) {
                $inner->whereIn('customer_id', $customerEntityIds);
            }

            if ($emails !== []) {
                $method = $customerEntityIds !== [] && in_array('customer_id', $columns, true)
                    ? 'orWhereIn'
                    : 'whereIn';

                $inner->{$method}(DB::raw('LOWER(subscriber_email)'), $emails);
            }
        });

        return $this->mapSubscriberRows($query->get($columns));
    }

    /**
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchSubscribersAfter(int $afterSubscriberId, int $limit): Collection
    {
        $columns = $this->subscriberColumns();

        if ($columns === [] || ! in_array('subscriber_id', $columns, true)) {
            return collect();
        }

        $rows = DB::connection($this->connection)
            ->table('newsletter_subscriber')
            ->where('subscriber_id', '>', $afterSubscriberId)
            ->orderBy('subscriber_id')
            ->limit($limit)
            ->get($columns);

        return $this->mapSubscriberRows($rows);
    }

    /**
     * @return array<int, string>
     */
    private function subscriberColumns(): array
    {
        if (! $this->schema->hasTable('newsletter_subscriber')) {
            return [];
        }

        $columns = $this->schema->existingColumns('newsletter_subscriber', [
            'subscriber_id',
            'customer_id',
            'subscriber_email',
            'subscriber_status',
            'subscriber_confirm_code',
            'change_status_at',
        ]);

        if (! in_array('subscriber_email', $columns, true) || ! in_array('subscriber_status', $columns, true)) {
            return [];
        }

        return $columns;
    }

    /**
     * @param  \Illuminate\Support\Collection<int, object>|iterable<int, object>  $rows
     * @return Collection<int, array<string, mixed>>
     */
    private function mapSubscriberRows($rows): Collection
    {
        return collect($rows)->map(function ($row) {
            $data = (array) $row;
            $data['subscriber_id'] = (int) ($row->subscriber_id ?? 0);
            $data['customer_id'] = (int) ($row->customer_id ?? 0);
            $data['subscriber_status'] = (int) ($row->subscriber_status ?? 0);

            return $data;
        })->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
    {
        $cacheKey = $entityTypeCode.':'.implode(',', $codes);

        if (isset($this->attributeMaps[$cacheKey])) {
            return $this->attributeMaps[$cacheKey];
        }

        $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 $this->attributeMaps[$cacheKey] = $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);
    }
}