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

    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;

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

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

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

        return $data;
    }
}