<?php

namespace App\Services\Asteria;

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

class Magento1OrderReader
{
    private const ORDER_COLUMNS = [
        'entity_id',
        'increment_id',
        'customer_id',
        'customer_email',
        'customer_firstname',
        'customer_lastname',
        'customer_is_guest',
        'status',
        'state',
        'store_id',
        'base_currency_code',
        'order_currency_code',
        'store_currency_code',
        'grand_total',
        'base_grand_total',
        'subtotal',
        'base_subtotal',
        'subtotal_incl_tax',
        'base_subtotal_incl_tax',
        'tax_amount',
        'base_tax_amount',
        'discount_amount',
        'base_discount_amount',
        'shipping_amount',
        'base_shipping_amount',
        'shipping_incl_tax',
        'base_shipping_incl_tax',
        'shipping_tax_amount',
        'base_shipping_tax_amount',
        'shipping_method',
        'shipping_description',
        'shipping_discount_amount',
        'base_shipping_discount_amount',
        'remote_ip',
        'x_forwarded_for',
        'coupon_code',
        'giftcard_id',
        'giftcard_amount',
        'base_giftcard_amount',
        'member_amount',
        'base_member_amount',
        'member_free_amount',
        'base_member_free_amount',
        'mw_rewardpoint',
        'mw_rewardpoint_discount',
        'mw_rewardpoint_discount_show',
        'amcheckoutfees_amount',
        'base_amcheckoutfees_amount',
        'total_item_count',
        'total_qty_ordered',
        'total_invoiced',
        'base_total_invoiced',
        'subtotal_invoiced',
        'base_subtotal_invoiced',
        'tax_invoiced',
        'base_tax_invoiced',
        'shipping_invoiced',
        'base_shipping_invoiced',
        'discount_invoiced',
        'base_discount_invoiced',
        'total_refunded',
        'base_total_refunded',
        'subtotal_refunded',
        'base_subtotal_refunded',
        'tax_refunded',
        'base_tax_refunded',
        'shipping_refunded',
        'base_shipping_refunded',
        'discount_refunded',
        'base_discount_refunded',
        'created_at',
        'updated_at',
    ];

    private const ITEM_COLUMNS = [
        'item_id',
        'order_id',
        'parent_item_id',
        'product_id',
        'product_type',
        'sku',
        'name',
        'qty_ordered',
        'qty_shipped',
        'qty_invoiced',
        'qty_canceled',
        'qty_refunded',
        'price',
        'base_price',
        'price_incl_tax',
        'base_price_incl_tax',
        'row_total',
        'base_row_total',
        'row_total_incl_tax',
        'base_row_total_incl_tax',
        'tax_amount',
        'base_tax_amount',
        'tax_percent',
        'discount_amount',
        'base_discount_amount',
        'discount_percent',
        'weight',
        'row_weight',
        'product_options',
        'created_at',
    ];

    private const SHIPMENT_COLUMNS = [
        'entity_id',
        'order_id',
        'increment_id',
        'total_qty',
        'total_weight',
        'email_sent',
        'customer_id',
        'created_at',
        'updated_at',
    ];

    private const SHIPMENT_ITEM_COLUMNS = [
        'entity_id',
        'parent_id',
        'order_item_id',
        'product_id',
        'sku',
        'name',
        'description',
        'qty',
        'weight',
        'price',
        'row_total',
    ];

    private const SHIPMENT_TRACK_COLUMNS = [
        'entity_id',
        'parent_id',
        'order_id',
        'track_number',
        'title',
        'carrier_code',
        'created_at',
    ];

    private const ADDRESS_COLUMNS = [
        'entity_id',
        'parent_id',
        'address_type',
        'firstname',
        'lastname',
        'company',
        'street',
        'city',
        'region',
        'postcode',
        'country_id',
        'telephone',
        'email',
    ];

    private const PAYMENT_COLUMNS = [
        'entity_id',
        'parent_id',
        'method',
        'last_trans_id',
        'cc_type',
        'cc_last4',
        'amount_ordered',
        'base_amount_ordered',
        'additional_information',
        'protection_eligibility',
        'account_status',
        'address_status',
    ];

    private Magento1Schema $schema;

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

    /**
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchOrders(int $afterEntityId, int $limit): Collection
    {
        $columns = $this->schema->existingColumns('sales_flat_order', self::ORDER_COLUMNS);

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

        $rows = DB::connection($this->connection)
            ->table('sales_flat_order')
            ->where('entity_id', '>', $afterEntityId)
            ->orderBy('entity_id')
            ->limit($limit)
            ->get($columns);

        return $rows->map(fn ($row) => $this->toArray($row, 'entity_id'))->values();
    }

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

        $columns = $this->schema->existingColumns('sales_flat_order_item', self::ITEM_COLUMNS);

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

        $rows = DB::connection($this->connection)
            ->table('sales_flat_order_item')
            ->whereIn('order_id', $orderEntityIds)
            ->orderBy('item_id')
            ->get($columns);

        return $rows->map(function ($row) {
            $item = $this->toArray($row, 'item_id');
            $item['order_id'] = (int) ($row->order_id ?? 0);
            $item['parent_item_id'] = isset($row->parent_item_id) && $row->parent_item_id !== null && $row->parent_item_id !== ''
                ? (int) $row->parent_item_id
                : null;
            $item['product_id'] = isset($row->product_id) && $row->product_id !== null && $row->product_id !== ''
                ? (int) $row->product_id
                : null;

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

    /**
     * @param  array<int, int>  $itemIds
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchItemsByItemIds(array $itemIds): Collection
    {
        $itemIds = array_values(array_unique(array_filter(array_map('intval', $itemIds))));

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

        $columns = $this->schema->existingColumns('sales_flat_order_item', self::ITEM_COLUMNS);

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

        $rows = collect();

        foreach (array_chunk($itemIds, 500) as $chunk) {
            $rows = $rows->concat(
                DB::connection($this->connection)
                    ->table('sales_flat_order_item')
                    ->whereIn('item_id', $chunk)
                    ->orderBy('item_id')
                    ->get($columns)
            );
        }

        return $rows->map(function ($row) {
            $item = $this->toArray($row, 'item_id');
            $item['order_id'] = (int) ($row->order_id ?? 0);
            $item['parent_item_id'] = isset($row->parent_item_id) && $row->parent_item_id !== null && $row->parent_item_id !== ''
                ? (int) $row->parent_item_id
                : null;
            $item['product_id'] = isset($row->product_id) && $row->product_id !== null && $row->product_id !== ''
                ? (int) $row->product_id
                : null;

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

    /**
     * Magento catalog SKUs keyed by catalog_product_entity.entity_id.
     *
     * @param  array<int, int>  $productIds
     * @return array<int, string>
     */
    public function fetchCatalogSkus(array $productIds): array
    {
        $productIds = array_values(array_unique(array_filter(array_map('intval', $productIds))));

        if ($productIds === [] || ! $this->schema->hasTable('catalog_product_entity')) {
            return [];
        }

        $columns = $this->schema->existingColumns('catalog_product_entity', ['entity_id', 'sku']);

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

        $map = [];

        foreach (array_chunk($productIds, 500) as $chunk) {
            foreach (
                DB::connection($this->connection)
                    ->table('catalog_product_entity')
                    ->whereIn('entity_id', $chunk)
                    ->get($columns) as $row
            ) {
                $sku = trim((string) ($row->sku ?? ''));

                if ($sku === '') {
                    continue;
                }

                $map[(int) $row->entity_id] = $sku;
            }
        }

        return $map;
    }

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

        $columns = $this->schema->existingColumns('sales_flat_order_address', self::ADDRESS_COLUMNS);

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

        $rows = DB::connection($this->connection)
            ->table('sales_flat_order_address')
            ->whereIn('parent_id', $orderEntityIds)
            ->orderBy('entity_id')
            ->get($columns);

        return $rows->map(function ($row) {
            $address = $this->toArray($row, 'entity_id');
            $address['parent_id'] = (int) ($row->parent_id ?? 0);

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

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

        $columns = $this->schema->existingColumns('sales_flat_order_payment', self::PAYMENT_COLUMNS);

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

        $rows = DB::connection($this->connection)
            ->table('sales_flat_order_payment')
            ->whereIn('parent_id', $orderEntityIds)
            ->orderBy('entity_id')
            ->get($columns);

        return $rows->map(function ($row) {
            $payment = $this->toArray($row, 'entity_id');
            $payment['parent_id'] = (int) ($row->parent_id ?? 0);

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

    /**
     * MW Reward Points rows keyed later by Magento order entity_id.
     *
     * @param  array<int, int>  $orderEntityIds
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchRewardPoints(array $orderEntityIds): Collection
    {
        if ($orderEntityIds === [] || ! $this->schema->hasTable('mw_reward_point_order')) {
            return collect();
        }

        $columns = $this->schema->existingColumns('mw_reward_point_order', [
            'order_id',
            'reward_point',
            'earn_rewardpoint',
            'money',
            'reward_point_money_rate',
        ]);

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

        $rows = DB::connection($this->connection)
            ->table('mw_reward_point_order')
            ->whereIn('order_id', $orderEntityIds)
            ->get($columns);

        return $rows->map(function ($row) {
            $data = $this->toArray($row, 'order_id');
            $data['order_id'] = (int) ($row->order_id ?? 0);

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

    /**
     * @param  array<int, int>  $orderEntityIds
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchShipments(array $orderEntityIds): Collection
    {
        if ($orderEntityIds === [] || ! $this->schema->hasTable('sales_flat_shipment')) {
            return collect();
        }

        $columns = $this->schema->existingColumns('sales_flat_shipment', self::SHIPMENT_COLUMNS);

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

        $rows = DB::connection($this->connection)
            ->table('sales_flat_shipment')
            ->whereIn('order_id', $orderEntityIds)
            ->orderBy('entity_id')
            ->get($columns);

        return $rows->map(function ($row) {
            $shipment = $this->toArray($row, 'entity_id');
            $shipment['order_id'] = (int) ($row->order_id ?? 0);

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

    /**
     * @param  array<int, int>  $shipmentIds
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchShipmentItems(array $shipmentIds): Collection
    {
        if ($shipmentIds === [] || ! $this->schema->hasTable('sales_flat_shipment_item')) {
            return collect();
        }

        $columns = $this->schema->existingColumns('sales_flat_shipment_item', self::SHIPMENT_ITEM_COLUMNS);

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

        $rows = collect();

        foreach (array_chunk($shipmentIds, 500) as $chunk) {
            $rows = $rows->concat(
                DB::connection($this->connection)
                    ->table('sales_flat_shipment_item')
                    ->whereIn('parent_id', $chunk)
                    ->orderBy('entity_id')
                    ->get($columns)
            );
        }

        return $rows->map(function ($row) {
            $item = $this->toArray($row, 'entity_id');
            $item['parent_id'] = (int) ($row->parent_id ?? 0);
            $item['order_item_id'] = isset($row->order_item_id) && $row->order_item_id !== null && $row->order_item_id !== ''
                ? (int) $row->order_item_id
                : null;

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

    /**
     * @param  array<int, int>  $shipmentIds
     * @return Collection<int, array<string, mixed>>
     */
    public function fetchShipmentTracks(array $shipmentIds): Collection
    {
        if ($shipmentIds === [] || ! $this->schema->hasTable('sales_flat_shipment_track')) {
            return collect();
        }

        $columns = $this->schema->existingColumns('sales_flat_shipment_track', self::SHIPMENT_TRACK_COLUMNS);

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

        $rows = collect();

        foreach (array_chunk($shipmentIds, 500) as $chunk) {
            $rows = $rows->concat(
                DB::connection($this->connection)
                    ->table('sales_flat_shipment_track')
                    ->whereIn('parent_id', $chunk)
                    ->orderBy('entity_id')
                    ->get($columns)
            );
        }

        return $rows->map(function ($row) {
            $track = $this->toArray($row, 'entity_id');
            $track['parent_id'] = (int) ($row->parent_id ?? 0);

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

    /**
     * @return array<string, mixed>
     */
    private function toArray(object $row, string $idColumn): array
    {
        $data = (array) $row;
        $data[$idColumn] = (int) ($row->{$idColumn} ?? 0);

        return $data;
    }
}