<?php
declare(strict_types=1);
namespace Esun\LegacyOrder\Model;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Serialize\Serializer\Json;
use Magento\Store\Model\StoreManagerInterface;

class LegacyOrderImporter
{
    private const REQUIRED = ['订单号','邮箱','产品总价','订单总额','产品单价','产品数量','产品SKU','创建时间'];
    public function __construct(private readonly ResourceConnection $resource, private readonly Json $json, private readonly StoreManagerInterface $stores) {}

    /** @return array{rows:int,orders:int,items:int,linked:int,unlinked:int,created:int,updated:int,errors:array<int,string>,warnings:array<int,string>} */
    public function process(string $path, string $sourceCode, int $websiteId, string $currency, bool $commit): array
    {
        $sourceCode = trim($sourceCode); $currency = strtoupper(trim($currency));
        if ($sourceCode === '' || !preg_match('/^[a-z0-9_-]{2,64}$/i', $sourceCode)) throw new LocalizedException(__('Source code must contain only letters, numbers, underscore or hyphen.'));
        if (!preg_match('/^[A-Z]{3}$/', $currency)) throw new LocalizedException(__('Currency must be a 3-letter ISO code.'));
        if ((int)$this->stores->getWebsite($websiteId)->getId() <= 0) throw new LocalizedException(__('Select a valid website.'));
        if (!is_file($path) || !is_readable($path)) throw new LocalizedException(__('CSV file is not readable.'));
        [$headers,$rows] = $this->read($path);
        $missing = array_diff(self::REQUIRED, $headers);
        if ($missing) throw new LocalizedException(__('Missing required columns: %1', implode(', ', $missing)));
        $groups=[]; $errors=[]; $warnings=[];
        foreach ($rows as $line=>$values) {
            if (count($values)!==count($headers)) { $errors[]="Line $line: column count mismatch"; continue; }
            $row=[]; foreach($headers as $i=>$header) $row[$header]=$values[$i];
            $number=$this->clean($row['订单号']??'');
            if($number===''){ $errors[]="Line $line: missing order number"; continue; }
            $email=strtolower($this->clean($row['邮箱']??'')); $sku=$this->clean($row['产品SKU']??'');
            if(!filter_var($email,FILTER_VALIDATE_EMAIL)){ $errors[]="Line $line: invalid email"; continue; }
            if($sku===''){ $errors[]="Line $line: missing SKU"; continue; }
            $row['_line']=$line; $row['_email']=$email; $row['_sku']=$sku;
            $groups[$number][]=$row;
        }
        $stats=['rows'=>count($rows),'orders'=>count($groups),'items'=>0,'linked'=>0,'unlinked'=>0,'created'=>0,'updated'=>0,'errors'=>$errors,'warnings'=>[]];
        foreach($groups as $number=>$items){
            $stats['items']+=count($items); $head=$this->findHeaderRow($items);
            $emails=array_unique(array_column($items,'_email'));
            if(count($emails)!==1){$stats['errors'][]="Order $number: mixed customer emails";continue;}
            try{$normalized=$this->normalizeOrder($number,$head,$items,$sourceCode,$websiteId,$currency);}catch(\Throwable $e){$stats['errors'][]="Order $number: ".$e->getMessage();continue;}
            if($normalized['order']['customer_id'])$stats['linked']++;else{$stats['unlinked']++;$warnings[]="Order $number: customer not linked";}
            if($commit){$exists=$this->save($normalized['order'],$normalized['items']);$stats[$exists?'updated':'created']++;}
        }
        $stats['warnings']=$warnings; return $stats;
    }

    public function relink(?int $websiteId = null): array
    {
        $db=$this->resource->getConnection();$table=$this->resource->getTableName('esun_legacy_order');
        $select=$db->select()->from($table,['legacy_order_id','customer_email','website_id'])->where('customer_id IS NULL');
        if($websiteId!==null)$select->where('website_id=?',$websiteId);
        $linked=0;$unlinked=0;foreach($db->fetchAll($select) as$row){$customers=$this->findCustomers(strtolower(trim((string)$row['customer_email'])),(int)$row['website_id']);if(count($customers)===1){$db->update($table,['customer_id'=>(int)$customers[0]['entity_id'],'link_status'=>'linked'],['legacy_order_id=?'=>(int)$row['legacy_order_id']]);$linked++;}else{$db->update($table,['link_status'=>count($customers)>1?'conflict':'unlinked'],['legacy_order_id=?'=>(int)$row['legacy_order_id']]);$unlinked++;}}return['linked'=>$linked,'unlinked'=>$unlinked];
    }

    private function read(string $path): array
    {
        $h=fopen($path,'rb'); if(!$h)throw new LocalizedException(__('Unable to open CSV.'));
        try{$headers=fgetcsv($h,null,',','"','\\');if($headers===false)throw new LocalizedException(__('CSV is empty.'));
            $seen=[];foreach($headers as $i=>$name){$name=trim((string)$name,"\xEF\xBB\xBF \t\r\n");$seen[$name]=($seen[$name]??0)+1;$headers[$i]=$seen[$name]>1?$name.'_'.$seen[$name]:$name;}
            $rows=[];$line=1;while(($row=fgetcsv($h,null,',','"','\\'))!==false){$line++;if(count($row)===1&&trim((string)$row[0])==='')continue;$rows[$line]=$row;}return[$headers,$rows];
        }finally{fclose($h);}
    }
    private function findHeaderRow(array $items): array { foreach($items as $r)if($this->clean($r['产品总价']??'')!=='')return$r; throw new LocalizedException(__('missing order summary row')); }
    private function normalizeOrder(string $number,array $h,array $items,string $source,int $website,string $currency): array
    {
        $email=$h['_email'];$customers=$this->findCustomers($email,$website);$customerId=count($customers)===1?(int)$customers[0]['entity_id']:null;
        $ship=$this->address($h,'收货');$bill=$this->address($h,'账单');$batch=gmdate('YmdHis');
        $order=['source_code'=>$source,'source_order_number'=>$number,'source_order_id'=>$this->clean($h['订单 ID']??''),'customer_id'=>$customerId,'website_id'=>$website,'customer_email'=>$email,'link_status'=>$customerId?'linked':(count($customers)>1?'conflict':'unlinked'),'currency'=>$currency,'product_total'=>$this->money($h['产品总价']??''),'discount_amount'=>$this->money($h['折扣']??''),'coupon'=>$this->nullable($h['优惠券']??''),'shipping_amount'=>$this->money($h['运费']??''),'tax_amount'=>$this->money($h['税费']??''),'grand_total'=>$this->money($h['订单总额']??''),'fee_amount'=>$this->money($h['手续费']??''),'payment_status'=>$this->nullable($h['付款状态']??''),'order_status'=>$this->nullable($h['订单状态']??''),'shipping_status'=>$this->nullable($h['发货状态']??''),'payment_method'=>$this->nullable($h['付款方式']??''),'payment_transaction_id'=>$this->nullable($h['支付流水号']??''),'shipping_method'=>$this->nullable($h['配送信息']??''),'carrier'=>$this->nullable($h['物流商']??''),'tracking_number'=>$this->nullable($h['运单号']??''),'weight'=>$this->number($h['重量']??''),'ordered_at'=>$this->date($h['创建时间']??''),'paid_at'=>$this->date($h['付款时间']??''),'shipping_address'=>$this->json->serialize($ship),'billing_address'=>$this->json->serialize($bill),'customer_note'=>$this->nullable($h['顾客备注']??''),'refund_reason'=>$this->nullable($h['退款原因']??''),'cancel_reason'=>$this->nullable($h['取消原因']??''),'source_data'=>$this->json->serialize($h),'import_batch'=>$batch];
        $out=[];foreach($items as $r){$qty=$this->number($r['产品数量']??'');$price=$this->money($r['产品单价']??'');$out[]=['line_number'=>(int)$r['_line'],'sku'=>$r['_sku'],'product_name'=>$this->productName($r['_sku']),'qty'=>$qty,'price'=>$price,'row_total'=>$qty*$price,'source_data'=>$this->json->serialize($r)];}
        return['order'=>$order,'items'=>$out];
    }
    private function save(array $order,array $items): bool
    {
        $db=$this->resource->getConnection();$ot=$this->resource->getTableName('esun_legacy_order');$it=$this->resource->getTableName('esun_legacy_order_item');
        $id=$db->fetchOne($db->select()->from($ot,['legacy_order_id'])->where('source_code=?',$order['source_code'])->where('source_order_number=?',$order['source_order_number']));$exists=(bool)$id;
        $db->beginTransaction();try{if($id){$db->update($ot,$order,['legacy_order_id=?'=>(int)$id]);}else{$db->insert($ot,$order);$id=$db->lastInsertId($ot);}$db->delete($it,['legacy_order_id=?'=>(int)$id]);foreach($items as $item){$item['legacy_order_id']=(int)$id;$db->insert($it,$item);}$db->commit();return$exists;}catch(\Throwable$e){if($db->getTransactionLevel()>0)$db->rollBack();throw$e;}
    }
    private function findCustomers(string $email,int $website): array{$db=$this->resource->getConnection();return$db->fetchAll($db->select()->from($this->resource->getTableName('customer_entity'),['entity_id'])->where('website_id=?',$website)->where('LOWER(TRIM(email))=?',$email));}
    private function productName(string $sku): ?string{$db=$this->resource->getConnection();$id=$db->fetchOne($db->select()->from($this->resource->getTableName('catalog_product_entity'),['entity_id'])->where('sku=?',$sku));return$id?'':null;}
    private function address(array $r,string $p):array{return['name'=>$this->clean($r[$p.'姓名']??''),'street'=>$this->clean($r[$p.'街道']??''),'unit'=>$this->clean($r[$p.'寓所']??''),'country'=>$this->clean($r[$p.'国家']??''),'region'=>$this->clean($r[$p.'省份']??''),'city'=>$this->clean($r[$p.'城市']??''),'postcode'=>$this->clean($r[$p.'邮编']??''),'telephone'=>$this->clean($r[$p.'电话']??'')];}
    private function clean(mixed $v):string{return trim(str_replace(["\xEF\xBB\xBF","\t"],'',(string)$v));}
    private function nullable(mixed $v):?string{$v=$this->clean($v);return$v===''||strtoupper($v)==='N/A'?null:$v;}
    private function money(mixed $v):float{$v=preg_replace('/[^0-9.\-]/','',$this->clean($v));return$v===''?0.0:(float)$v;}
    private function number(mixed $v):float{$v=str_replace(',','',$this->clean($v));return is_numeric($v)?(float)$v:0.0;}
    private function date(mixed $v):?string{$v=$this->nullable($v);if(!$v)return null;$t=strtotime($v);return$t?date('Y-m-d H:i:s',$t):null;}
}