<?php
namespace EasyChat\Library\Commerce;

final class WooCommerceContextProvider extends AbstractCommerceContextProvider
{
    private $hpos;

    public function capabilities(): array
    {
        return array('customer' => true, 'orders' => true, 'order_items' => true, 'read_only' => true, 'schema' => $this->isHpos() ? 'hpos' : 'legacy');
    }

    public function resolveCustomer(array $lookup): array
    {
        $emails = $this->emails($lookup);
        $phones = $this->phones($lookup);
        if (!$emails && !$phones) return $this->unresolved('not_found');
        try {
            return $this->isHpos() ? $this->resolveHpos($emails, $phones) : $this->resolveLegacy($emails, $phones);
        } catch (\Throwable $e) {
            return $this->unresolved('unavailable');
        }
    }

    public function findOrders(array $reference, int $limit = 3): array
    {
        try {
            return $this->isHpos() ? $this->findHposOrders($reference, $limit) : $this->findLegacyOrders($reference, $limit);
        } catch (\Throwable $e) {
            return array();
        }
    }

    public function health(): array
    {
        return array(
            'ready' => true,
            'schema' => $this->isHpos() ? 'woocommerce_hpos' : 'woocommerce_legacy',
            'index_policy' => 'native_or_diagnostic',
            'recommended' => $this->isHpos()
                ? array('wc_orders.customer_id', 'wc_orders.billing_email', 'wc_order_addresses.order_id', 'woocommerce_order_items.order_id')
                : array('posts.type_status_date', 'postmeta.post_id/meta_key', 'woocommerce_order_items.order_id'),
        );
    }

    private function isHpos(): bool
    {
        if ($this->hpos !== null) return $this->hpos;
        try {
            $row = $this->row($this->db->query("SHOW TABLES LIKE '" . $this->db->escape($this->prefix . 'wc_orders') . "'"));
            $enabled = $this->row($this->db->query("SELECT `option_value` FROM `{$this->prefix}options` WHERE `option_name`='woocommerce_custom_orders_table_enabled' LIMIT 1"));
            $this->hpos = !empty($row) && strtolower((string)($enabled['option_value'] ?? 'no')) === 'yes';
        } catch (\Throwable $e) {
            $this->hpos = false;
        }
        return $this->hpos;
    }

    private function resolveHpos(array $emails, array $phones): array
    {
        $conditions = array();
        if ($emails) $conditions[] = "o.`billing_email` IN (" . $this->quoted($emails) . ")";
        if ($phones) $conditions[] = "a.`phone` IN (" . $this->quoted($phones) . ")";
        $rows = $this->rows($this->db->query(
            "SELECT DISTINCT o.`customer_id`,o.`billing_email`,a.`phone`
             FROM `{$this->prefix}wc_orders` o
             LEFT JOIN `{$this->prefix}wc_order_addresses` a ON a.`order_id`=o.`id` AND a.`address_type`='billing'
             WHERE o.`type`='shop_order' AND (" . implode(' OR ', $conditions) . ")
             ORDER BY o.`customer_id` DESC LIMIT 4"
        ));
        return $this->referenceFromOrderContacts($rows, $emails, $phones);
    }

    private function resolveLegacy(array $emails, array $phones): array
    {
        $conditions = array();
        if ($emails) $conditions[] = "(em.`meta_key`='_billing_email' AND em.`meta_value` IN (" . $this->quoted($emails) . "))";
        if ($phones) $conditions[] = "(pm.`meta_key`='_billing_phone' AND pm.`meta_value` IN (" . $this->quoted($phones) . "))";
        $rows = $this->rows($this->db->query(
            "SELECT DISTINCT COALESCE(NULLIF(cm.`meta_value`,''),'0') AS `customer_id`,em.`meta_value` AS `billing_email`,pm.`meta_value` AS `phone`
             FROM `{$this->prefix}posts` p
             LEFT JOIN `{$this->prefix}postmeta` cm ON cm.`post_id`=p.`ID` AND cm.`meta_key`='_customer_user'
             LEFT JOIN `{$this->prefix}postmeta` em ON em.`post_id`=p.`ID` AND em.`meta_key`='_billing_email'
             LEFT JOIN `{$this->prefix}postmeta` pm ON pm.`post_id`=p.`ID` AND pm.`meta_key`='_billing_phone'
             WHERE p.`post_type`='shop_order' AND (" . implode(' OR ', $conditions) . ") LIMIT 4"
        ));
        return $this->referenceFromOrderContacts($rows, $emails, $phones);
    }

    private function referenceFromOrderContacts(array $rows, array $emails, array $phones): array
    {
        if (!$rows) return $this->unresolved('not_found');
        $ids = array_values(array_unique(array_filter(array_map(function ($row) { return (int)($row['customer_id'] ?? 0); }, $rows))));
        if (count($ids) > 1) return $this->unresolved('ambiguous');
        $first = $rows[0];
        $email = mb_strtolower(trim((string)($first['billing_email'] ?? '')), 'UTF-8');
        $evidence = in_array($email, $emails, true) ? 'email' : 'phone';
        if (count($ids) === 1) return $this->matched((string)$ids[0], 'customer_id', $evidence);
        $value = $evidence === 'email' ? $email : (string)($first['phone'] ?? '');
        return $this->matched(hash('sha256', $evidence . ':' . $value), 'guest_contact', $evidence, array('contact_type' => $evidence, 'contact_value' => $value));
    }

    private function findHposOrders(array $ref, int $limit): array
    {
        $limit = $this->limit($limit);
        $where = $this->hposOwnership($ref);
        if ($where === '') return array();
        if (!empty($ref['order_id'])) $where .= ' AND o.`id`=' . (int)$ref['order_id'];
        $orders = $this->rows($this->db->query(
            "SELECT o.`id`,o.`date_created_gmt`,o.`date_updated_gmt`,o.`status`,o.`total_amount`,o.`currency`,o.`payment_method_title`
             FROM `{$this->prefix}wc_orders` o
             LEFT JOIN `{$this->prefix}wc_order_addresses` a ON a.`order_id`=o.`id` AND a.`address_type`='billing'
             WHERE o.`type`='shop_order' AND {$where} ORDER BY o.`date_created_gmt` DESC,o.`id` DESC LIMIT {$limit}"
        ));
        return $this->decorateWooOrders($orders, 'id', 'date_created_gmt', 'total_amount', 'currency', 'payment_method_title');
    }

    private function findLegacyOrders(array $ref, int $limit): array
    {
        $limit = $this->limit($limit);
        $where = $this->legacyOwnership($ref);
        if ($where === '') return array();
        if (!empty($ref['order_id'])) $where .= ' AND p.`ID`=' . (int)$ref['order_id'];
        $orders = $this->rows($this->db->query(
            "SELECT p.`ID`,p.`post_date_gmt`,p.`post_modified_gmt`,p.`post_status`,tot.`meta_value` AS `total_amount`,cur.`meta_value` AS `currency`,pay.`meta_value` AS `payment_method_title`
             FROM `{$this->prefix}posts` p
             LEFT JOIN `{$this->prefix}postmeta` cm ON cm.`post_id`=p.`ID` AND cm.`meta_key`='_customer_user'
             LEFT JOIN `{$this->prefix}postmeta` em ON em.`post_id`=p.`ID` AND em.`meta_key`='_billing_email'
             LEFT JOIN `{$this->prefix}postmeta` pm ON pm.`post_id`=p.`ID` AND pm.`meta_key`='_billing_phone'
             LEFT JOIN `{$this->prefix}postmeta` tot ON tot.`post_id`=p.`ID` AND tot.`meta_key`='_order_total'
             LEFT JOIN `{$this->prefix}postmeta` cur ON cur.`post_id`=p.`ID` AND cur.`meta_key`='_order_currency'
             LEFT JOIN `{$this->prefix}postmeta` pay ON pay.`post_id`=p.`ID` AND pay.`meta_key`='_payment_method_title'
             WHERE p.`post_type`='shop_order' AND {$where} ORDER BY p.`post_date_gmt` DESC,p.`ID` DESC LIMIT {$limit}"
        ));
        return $this->decorateWooOrders($orders, 'ID', 'post_date_gmt', 'total_amount', 'currency', 'payment_method_title');
    }

    private function decorateWooOrders(array $orders, string $id_key, string $date_key, string $total_key, string $currency_key, string $payment_key): array
    {
        if (!$orders) return array();
        $ids = array_map(function ($row) use ($id_key) { return (int)$row[$id_key]; }, $orders);
        $items = $this->groupItems($this->rows($this->db->query(
            "SELECT oi.`order_id`,oi.`order_item_name` AS `name`,COALESCE(q.`meta_value`,1) AS `quantity`,COALESCE(pid.`meta_value`,'') AS `product_id`
             FROM `{$this->prefix}woocommerce_order_items` oi
             LEFT JOIN `{$this->prefix}woocommerce_order_itemmeta` q ON q.`order_item_id`=oi.`order_item_id` AND q.`meta_key`='_qty'
             LEFT JOIN `{$this->prefix}woocommerce_order_itemmeta` pid ON pid.`order_item_id`=oi.`order_item_id` AND pid.`meta_key`='_product_id'
             WHERE oi.`order_item_type`='line_item' AND oi.`order_id` IN (" . implode(',', $ids) . ") ORDER BY oi.`order_item_id`"
        )));
        $tracking = $this->trackingForOrders($ids);
        $api_ship = $this->apiShipShipmentsForOrders($ids);
        return array_map(function ($row) use ($id_key, $date_key, $total_key, $currency_key, $payment_key, $items, $tracking, $api_ship) {
            $id = (string)$row[$id_key];
            $status = (string)($row['status'] ?? $row['post_status'] ?? '');
            return array('id'=>$id,'number'=>$id,'date'=>(string)$row[$date_key],'status'=>$this->wooStatus($status),
                'updated_at'=>(string)($row['date_updated_gmt'] ?? $row['post_modified_gmt'] ?? $row[$date_key]),
                'total'=>$this->money($row[$total_key] ?? 0, (string)($row[$currency_key] ?? '')),
                'payment'=>$this->boundedText($row[$payment_key] ?? '',100),'delivery'=>'',
                'payment_status'=>$this->wooPaymentStatus($status),'refund_status'=>$this->wooRefundStatus($status),
                'fulfillment_status'=>$this->wooFulfillmentStatus($status),'shipments'=>array_merge($api_ship[$id]??array(), $tracking[$id]??array()),
                'items'=>array_slice($items[$id] ?? array(),0,20));
        }, $orders);
    }

    private function hposOwnership(array $ref): string
    {
        if (($ref['kind'] ?? '') === 'customer_id' && (int)($ref['id'] ?? 0)>0) return 'o.`customer_id`='.(int)$ref['id'];
        if (($ref['kind'] ?? '') !== 'guest_contact') return '';
        $raw = (string)($ref['contact_value'] ?? '');
        $v = $this->db->escape($raw);
        return ($ref['contact_type'] ?? '') === 'email' ? "o.`billing_email`='".$this->db->escape(mb_strtolower($raw,'UTF-8'))."'" : "a.`phone`='{$v}'";
    }

    private function legacyOwnership(array $ref): string
    {
        if (($ref['kind'] ?? '') === 'customer_id' && (int)($ref['id'] ?? 0)>0) return "cm.`meta_value`='".(int)$ref['id']."'";
        if (($ref['kind'] ?? '') !== 'guest_contact') return '';
        $raw = (string)($ref['contact_value'] ?? '');
        $v = $this->db->escape($raw);
        return ($ref['contact_type'] ?? '') === 'email' ? "em.`meta_value`='".$this->db->escape(mb_strtolower($raw,'UTF-8'))."'" : "pm.`meta_value`='{$v}'";
    }

    private function wooStatus(string $status): string
    {
        $key = preg_replace('/^(wc-|wc_)/', '', strtolower($status));
        $map = array('pending'=>'Ожидает оплаты','processing'=>'В обработке','on-hold'=>'На удержании','completed'=>'Выполнен','cancelled'=>'Отменён','refunded'=>'Возвращён','failed'=>'Ошибка','checkout-draft'=>'Черновик');
        return $map[$key] ?? $this->boundedText(str_replace(array('-', '_'), ' ', $key), 80);
    }

    private function trackingForOrders(array $ids): array
    {
        if (!$ids) return array();
        try {
            $rows = $this->rows($this->db->query(
                "SELECT `post_id` AS `order_id`,`meta_key`,`meta_value` FROM `{$this->prefix}postmeta`
                 WHERE `post_id` IN (" . implode(',', array_map('intval', $ids)) . ")
                   AND `meta_key` IN ('_tracking_number','_tracking_provider','_aftership_tracking_number','_aftership_tracking_provider_name')"
            ));
        } catch (\Throwable $e) { return array(); }
        $meta=array();foreach($rows as$row){$meta[(string)($row['order_id']??'')][(string)($row['meta_key']??'')]=(string)($row['meta_value']??'');}
        $result=array();foreach($meta as$id=>$values){$number=trim((string)($values['_tracking_number']??$values['_aftership_tracking_number']??''));if(!preg_match('/^[A-Za-z0-9-]{5,40}$/',$number))continue;$carrier=$this->boundedText($values['_tracking_provider']??$values['_aftership_tracking_provider_name']??'',80);$result[$id][]=array('carrier'=>$carrier,'tracking_number'=>$number,'status'=>'unknown','status_label'=>'','updated_at'=>'');}
        return $result;
    }

    




    private function apiShipShipmentsForOrders(array $ids): array
    {
        if (!$ids) return array();
        try {
            $rows = $this->rows($this->db->query(
                "SELECT oi.`order_id`,im.`meta_key`,im.`meta_value` FROM `{$this->prefix}woocommerce_order_items` oi
                 INNER JOIN `{$this->prefix}woocommerce_order_itemmeta` im ON im.`order_item_id`=oi.`order_item_id`
                 WHERE oi.`order_id` IN (" . implode(',', array_map('intval', $ids)) . ")
                   AND oi.`order_item_type`='shipping' AND im.`meta_key` IN ('integratorOrder','providerNumber')"
            ));
            $meta = array();
            foreach ($rows as $row) $meta[(string)($row['order_id'] ?? '')][(string)($row['meta_key'] ?? '')] = (string)($row['meta_value'] ?? '');
            $result = array();
            foreach ($meta as $order_id => $values) {
                $shipment = ApiShipShipment::fromOfficialModule($order_id, (string)($values['integratorOrder'] ?? ''), (string)($values['providerNumber'] ?? ''), 'apiship_woocommerce');
                if ($shipment) $result[$order_id][] = $shipment;
            }
            return $result;
        } catch (\Throwable $e) {
            return array();
        }
    }

    private function wooPaymentStatus(string $status): string
    {
        $key=preg_replace('/^(wc-|wc_)/','',strtolower($status));
        if(in_array($key,array('processing','completed'),true))return'paid';
        if($key==='refunded')return'refunded';
        if($key==='failed')return'failed';
        return'pending';
    }
    private function wooRefundStatus(string $status): string{return strpos(strtolower($status),'refund')!==false?'full':'none';}
    private function wooFulfillmentStatus(string $status): string
    {
        $key=preg_replace('/^(wc-|wc_)/','',strtolower($status));
        return array('pending'=>'new','processing'=>'processing','on-hold'=>'processing','completed'=>'delivered','cancelled'=>'cancelled','refunded'=>'returned','failed'=>'cancelled')[$key]??'unknown';
    }
}
