<?php
namespace App\Service;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class U8ThirtyPartyDatabaseServerService extends Service
{
    protected $connectionName = 'u8_third_sqlserver';

    /**
     * 获取一个具备“自愈”功能的数据库连接
     * 解决 Windows 环境下远程主机强迫关闭连接 (08S02) 的问题
     */
    private function safeDb()
    {
        $conn = DB::connection($this->connectionName);

        try {
            // 心跳探测：执行一个极轻量的查询
            $conn->getPdo()->query('SELECT 1');
        } catch (\Throwable $e) {
            // 捕获到任何连接异常（如 08S02 / 08001）
            Log::channel('apiLog')->warning("U8连接丢失，正在尝试重连...", [
                'msg' => $e->getMessage()
            ]);

            // 彻底销毁旧连接句柄
            $conn->disconnect();
            DB::purge($this->connectionName);

            // 重新获取新连接
            $conn = DB::connection($this->connectionName);
        }

        return $conn;
    }

    // Service 内部查询
    public function getPendingBills($config, $lastId)
    {
        try {

            $db = $this->safeDb();

            $mainTable = $config['main'];
            $detailTable = $config['detail'];
            $mainKey = $config['main_key'];
            $foreignKey = $config['key'];
            $whereRaw = $config['whereRaw'];
            $main_field = $config['main_field'];
            $son_field = $config['son_field'];
            $limit = $config['limit'];

            // 1. 获取主表
            $mainRows = $db->table($mainTable)
                ->lock('WITH(NOLOCK)')
                ->where($mainKey, '>', $lastId) // 改用 ID
                ->whereRaw($whereRaw)
                ->select($main_field)
                ->orderBy($mainKey, 'ASC') // 按 ID 排序保证分页连续
                ->limit($limit)
                ->get();

            if ($mainRows->isEmpty()) return [true, []];
            if (empty($foreignKey) || empty($son_field)) {
                $array = $mainRows->map(function ($item) {
                    return (array) $item;
                })->toArray();
                return [true, $array];
            }

            // 2. 批量获取子表
            $mainIds = $mainRows->pluck('id')->toArray();
            $details = $db->table($detailTable. ' as detail')
                ->lock('WITH(NOLOCK)')
                ->whereIn($foreignKey, $mainIds)
                ->select($son_field)
                ->addSelect(DB::raw("ROW_NUMBER() OVER(PARTITION BY detail.{$foreignKey} ORDER BY detail.AutoID) as lineNum"))
                ->get()
                ->groupBy('id');

            // 3. 组合
            $result = $mainRows->map(function($main) use ($details, $foreignKey) {
                $res = (array)$main;
                $mainId = $res['id'];
                $res['details'] = isset($details[$mainId])
                    ? $details[$mainId]->map(function($item) {
                        return (array)$item; // 强制把子表对象转成数组
                    })->values()->toArray()
                    : [];
                return $res;
            })->toArray();

            return [true, $result];

        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getArrivalVouchById($orderId)
    {
        try {
            $db = $this->safeDb();

            // 1. 获取主表 (PU_ArrivalVouch) 并关联 采购类型表 (PurchaseType)
            $main = $db->table('PU_ArrivalVouch as main')
                ->leftJoin('PurchaseType as pt', 'main.cPTCode', '=', 'pt.cPTCode') // 关联采购类型表
                ->lock('WITH(NOLOCK)')
                ->where('main.ID', $orderId)
                ->select([
                    'main.ID as id',
                    'main.cCode as no',
                    'main.cVenCode as supply_code',
                    'main.cDepCode as depart_code',
                    'main.dDate as order_date',
                    'main.cBusType as b_type',
                    'main.cPTCode as cg_type',
                    'pt.cRdCode as rd_code'
                ])
                ->first();

            if (!$main) return [false, '采购退货单数据不存在'];

            $order = (array)$main;

            // 2. 获取子表 (PU_ArrivalVouchs)
            $details = $db->table('PU_ArrivalVouchs as detail')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $orderId) // 子表里的 ID 关联主表的 ID
                ->select([
                    'detail.cInvCode',
                    'detail.iQuantity',
                    'detail.iQuantity as iNNum',
                    'detail.iinvexchrate',
                    'detail.cBatch',
                    'detail.dPDate',
                    'detail.dVDate',
                    'detail.Autoid as iArrsId',
                    'detail.ivouchrowno as iRowNo',
                    'detail.iOriTaxCost',
                    'detail.iTaxRate',
//                    'detail.cordercode',
                    'detail.cWhCode',
                ])
                ->orderBy('detail.ivouchrowno', 'ASC') // 按 U8 原始行号排序
                ->get();

            // 3. 组合数据并返回
            $order['details'] = $details->map(function ($item) {
                return (array)$item;
            })->toArray();

            return [true, $order];
        }catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getInventoryData($config, $lastCode = null)
    {
        try {
            $db = $this->safeDb();
            $query = $db->table('Inventory as main')
                ->leftJoin('InventoryClass as sub', 'main.cInvCCode', '=', 'sub.cInvCCode')
                ->leftJoin('ComputationUnit as unit', 'main.cComUnitCode', '=', 'unit.cComUnitCode')
                ->select([
                    'main.cInvCode as product_code',
                    'main.cInvName as product_name',
                    'main.cInvStd as product_size',
                    'main.cInvCCode as product_category_code',
                    'sub.cInvCName as product_category_name', // 直接联查分类名
                    'main.cComUnitCode as product_unit',
                    'unit.cComUnitName as product_unit_title',
//                    'main.fOutExcess as out_limit',
//                    'main.cCurrencyName as common_name',
                    'main.fGrossW as grossWeight',
                    'main.bFree1 as param_one',
                    'main.bFree2 as param_two',
                    'main.bInvBatch as param_three',
                    'main.bInvQuality as param_four',
                    // 关键点：将二进制 ufts 转换为 0x... 字符串
                    DB::raw("master.sys.fn_varbintohexstr(main.pubufts) as ufts_str")
                ]);

            // 使用 product_code 做分页依据
            if ($lastCode) {
                $query->where('main.cInvCode', '>', $lastCode);
            }

            if (!empty($config['whereRaw'])) {
                $query->whereRaw($config['whereRaw']);
            }

            // 必须按编码排序，保证分页不重不漏
            $items = $query->orderBy('main.cInvCode', 'asc')
                ->limit($config['limit'])
                ->get();

            return [true, json_decode(json_encode($items), true)];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    //获取单个产品报检单的明细
    public function getBjOrder($id){
        try {
            $db = $this->safeDb();

            $array = $db->table('QMINSPECTVOUCHERS as detail')
                ->lock('WITH(NOLOCK)')
                ->where('detail.ID', $id)
                ->select('detail.*')
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                })
                ->keyBy('CINVCODE')
                ->toArray();
            return [true, $array];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getJyOrder($id)
    {
        try {
            $db = $this->safeDb();

            // 1. 获取表头信息 (主表)
            $main = $db->table('QMCHECKVOUCHER')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id)
                ->first();

            if (!$main) return [false, '检验单不存在'];

            $main = (array)$main;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getScDetails($id)
    {
        try {
            $db = $this->safeDb();

            // 1. 获取表头信息 (主表)
            $main = $db->table('mom_orderdetail')
                ->lock('WITH(NOLOCK)')
                ->where('MoDid', $id)
                ->first();

            if (!$main) {
                return [false, '生成订单明细不存在']; // 或者返回空数组 []
            }

            $main = (array)$main;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    //获取单个销售订单的信息
    public function getXsOrder($id)
    {
        try {
            $db = $this->safeDb();

        // 1. 获取销售订单主表信息 (SO_SOMain)
            $main = $db->table('SO_SOMain')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id)
                ->first();

            if (!$main) {
                return [false, '销售订单不存在'];
            }

            $main = (array)$main;

            // 2. 获取销售订单子表信息 (SO_SODetails)
            $details = $db->table('SO_SODetails')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id) // 子表通过 ID 关联主表
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                })
                ->toArray();

            // 3. 将子表数据放入 details 键中
            $main['details'] = $details;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getXsThOrder($id)
    {
        try {
            $db = $this->safeDb();

            // 1. 获取销售退货单主表信息 (DispatchList)
            $main = $db->table('DispatchList')
                ->lock('WITH(NOLOCK)')
                ->where('DLID', $id) // 注意：主键叫 DLID
                ->first();

            if (!$main) {
                return [false, '销售退货单不存在'];
            }

            $main = (array)$main;

            // 2. 获取销售退货单子表信息 (DispatchLists)
            $details =$db->table('DispatchLists')
                ->lock('WITH(NOLOCK)')
                ->where('DLID', $id) // 通过 DLID 关联
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                })
                ->toArray();

            // 3. 将子表数据放入 details 键中
            $main['details'] = $details;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getFhOrder($id)
    {
        try {
            $db = $this->safeDb();

            // 1. 获取销售发货单主表信息 (DispatchList)
            $main = $db->table('DispatchList')
                ->lock('WITH(NOLOCK)')
                ->where('DLID', $id) // U8发货单主键通常是 DLID
                ->first();

            if (!$main) {
                return [false, '销售发货单不存在'];
            }

            $main = (array)$main;

            // 2. 获取销售发货单子表信息 (DispatchLists)
            $details = $db->table('DispatchLists') // 注意这里是 DispatchLists
                ->lock('WITH(NOLOCK)')
                ->where('DLID', $id) // 子表通过 DLID 关联主表
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                })
                ->toArray();

            // 3. 将子表数据放入 details 键中
            $main['details'] = $details;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function getCGDHDetails($id)
    {
        try {
            $db = $this->safeDb();

            // 2. 获取采购到货单子表信息 (PU_ArrivalVouchs)
            $details = $db->table('PU_ArrivalVouchs')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id) // 通过 ID 关联主表
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                });

            // 3. 将子表数据以 cInvCode 为 Key 进行组织
            // 注意：如果一张单据里有重复的存货编码，后面的行会覆盖前面的行
            $details = $details->keyBy('cInvCode')->toArray();

            return [true, $details];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }

    }

    public function getLlSQDetails($id)
    {
        try {
            $db = $this->safeDb();

        // 1. 获取领料申请单子表信息 (MaterialAppVouchs)
            $details = $db->table('MaterialAppVouchs') // 领料申请单子表名
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id) // 通过 ID 关联主表
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                });

            // 2. 将子表数据以 cInvCode 为 Key 进行组织
            // 提示：领料申请单中同一存货可能出现在不同行（如：对应不同的生产订单行）
            // 如果确定 cInvCode 唯一，可用 keyBy；如果不唯一，建议直接返回数组或 keyBy('AutoID')
            $details = $details->keyBy('cInvCode')->toArray();

            return [true, $details];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function updateDhOrder($update, $insert)
    {
        try {
            // 1. 先通过心跳探测获取一个可靠的连接
            $db = $this->safeDb();

            return $db->transaction(function () use ($update, $insert,$db) {

                // 1. 处理更新部分 (子表: PU_ArrivalVouchs)
                if (!empty($update)) {
                    foreach ($update as $autoId => $fields) {
                        $db->table('PU_ArrivalVouchs') // 修正为子表名
                            ->where('Autoid', $autoId)  // U8 习惯大写 A 小写 utoid，但 SQL 不敏感
                            ->update($fields);
                    }
                }

                // 2. 处理插入部分 (拆分行)
                if (!empty($insert)) {
                    $mainId = $insert[0]['ID'] ?? null;

                    if ($mainId) {
                        // 获取全表最大 Autoid，确保全局唯一
                        $maxRowID = $db->table('PU_ArrivalVouchs') // 修正为子表名
                                ->max('Autoid') ?? 0;

                        foreach ($insert as &$newRow) {
                            $maxRowID++;
                            $newRow['Autoid'] = $maxRowID;
                        }

                        // 使用批量插入
                        $db->table('PU_ArrivalVouchs')
                            ->insert($insert);
                    }
                }

                return [true, '']; // 事务成功返回
            });
        } catch (\Throwable $e) {
            // 打印错误堆栈有助于调试
            return [false, "修改采购到货单失败: " . $e->getMessage()];
        }
    }

    public function getCgOrder($id)
    {
        try {
            $db = $this->safeDb();

// 1. 获取采购到货单主表信息 (PU_ArrivalVouch)
            // 关键：关联键是 ID
            $main = $db->table('PU_ArrivalVouch')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id)
                ->first();

            if (!$main) {
                return [false, '采购到货单不存在'];
            }

            $main = (array)$main;

            // 2. 获取采购到货单子表信息 (PU_ArrivalVouchs)
            $details = $db->table('PU_ArrivalVouchs')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id) // 子表通过 ID 关联主表
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                })
                ->toArray();

            // 3. 将子表数据放入 details 键中
            $main['details'] = $details;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }

    }

    public function getJyOrder2($id)
    {
        try {
            $db = $this->safeDb();

            // 关联采购到货单子表 (PU_ArrivalVouchs) 获取金额字段
            $order = $db->table('QMCHECKVOUCHER as qm')
                ->leftJoin('PU_ArrivalVouchs as arr', 'qm.SOURCEAUTOID', '=', 'arr.Autoid')
                ->select(
                    'qm.*',
                    'arr.iCost',        // 原币无税单价
                    'arr.iOriTaxCost',  // 原币含税单价
                    'arr.iTaxRate'      // 税率
                )
                ->where('qm.ID', $id) // 假设这是检验单主表ID
                ->first();

            return [true, $order ? (array)$order : []];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function updateLLOrder($update, $insert)
    {
        try {
            $db = $this->safeDb();
            return $db->transaction(function () use ($update, $insert, $db) {

                // 1. 处理更新部分 (子表: MaterialAppVouchs)
                if (!empty($update)) {
                    foreach ($update as $autoId => $fields) {
                        $db->table('MaterialAppVouchs') // 领料申请单子表
                            ->where('autoid', $autoId)    // 注意字段名在 U8 里可能是 autoid 或 AutoID
                            ->update($fields);
                    }
                }

                // 2. 处理插入部分 (拆分行/新增行)
                if (!empty($insert)) {
                    // 获取当前领料申请单子表的最大 Autoid，确保手动分配不冲突
                    $maxRowID = $db->table('MaterialAppVouchs')
                            ->max('autoid') ?? 0;

                    foreach ($insert as &$newRow) {
                        $maxRowID++;
                        // U8 的 autoid 通常必须手动指定
                        $newRow['autoid'] = $maxRowID;
                    }

                    // 使用批量插入
                    $db->table('MaterialAppVouchs')
                        ->insert($insert);
                }

                return [true, ''];
            });
        } catch (\Throwable $e) {
            // 建议记录详细日志
            return [false, "修改领料申请单失败: " . $e->getMessage()];
        }
    }

    public function getLLOrder($id)
    {
        try {
            $db = $this->safeDb();

            // 关键：关联键是 ID
            $main = $db->table('MaterialAppVouch')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id)
                ->first();

            if (!$main) {
                return [false, '领料单不存在'];
            }

            $main = (array)$main;

            // 2. 获取采购到货单子表信息 (PU_ArrivalVouchs)
            $details = $db->table('MaterialAppVouchs')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $id) // 子表通过 ID 关联主表
                ->get()
                ->map(function ($value) {
                    return (array)$value;
                })
                ->toArray();

            // 3. 将子表数据放入 details 键中
            $main['details'] = $details;

            return [true, $main];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function checkInventoryControl($invCodes)
    {
        try {
            $db = $this->safeDb();

            if (empty($invCodes)) {
                return [false, '存货编码为空'];
            }

            // 1. 查询存货档案
            $list = $db->table('Inventory')
                ->whereIn('cInvCode', $invCodes)
                ->select(['cInvCode', 'cInvName', 'bInvBatch', 'bInvQuality'])
                ->get();

            $noBatch = [];      // 未开启批次管理的
            $noExpiration = []; // 未开启保质期管理的
            $notFound = array_diff($invCodes, $list->pluck('cInvCode')->toArray()); // 数据库中不存在的

            foreach ($list as $item) {
                // 检查批次管理 (bInvBatch 为 0 表示未开启)
                if (empty($item->bInvBatch)) {
                    $noBatch[] = $item->cInvCode . '(' . $item->cInvName . ')';
                }

                // 检查保质期管理 (bInvQuality 为 0 表示未开启)
                if (empty($item->bInvQuality)) {
                    $noExpiration[] = $item->cInvCode . '(' . $item->cInvName . ')';
                }
            }

            // 2. 组织提示结果
            $messages = [];
            if (!empty($noBatch)) {
                $messages[] = "【未开启批次管理】: " . implode(', ', $noBatch);
            }
            if (!empty($noExpiration)) {
                $messages[] = "【未开启保质期管理】: " . implode(', ', $noExpiration);
            }
            if (!empty($notFound)) {
                $messages[] = "【档案不存在】: " . implode(', ', $notFound);
            }

            if (empty($messages)) {
                return [true, ''];
            }

            return [false, implode("\n", $messages)];
        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }

    public function rebuildDhDetails($mainId, $insertData)
    {
        try {
            $db = $this->safeDb();

            return $db->transaction(function () use ($mainId, $insertData,$db) {

                // 1. 删除旧明细
                $db->table('PU_ArrivalVouchs')
                    ->where('ID', $mainId)
                    ->delete();

                // 2. 获取新的起始 Autoid
                $maxAutoid = $db->table('PU_ArrivalVouchs')
                        ->max('Autoid') ?? 0;

                // 3. 分配新 Autoid 并插入
                foreach ($insertData as &$row) {
                    $maxAutoid++;
                    $row['Autoid'] = $maxAutoid;
                }

                $db->table('PU_ArrivalVouchs')
                    ->insert($insertData);

                return [true, ''];
            });
        } catch (\Throwable $e) {
            return [false, "重建明细失败: " . $e->getMessage()];
        }
    }

    public function rebuildLLDetails($mainId, $insertData)
    {
        try {
            $db = $this->safeDb();

            return $db->transaction(function () use ($mainId, $insertData,$db) {

                // 1. 删除旧明细 (领料申请单子表)
                $db->table('MaterialAppVouchs')
                    ->where('ID', $mainId)
                    ->delete();

                // 2. 获取新的起始 autoid
                $maxAutoid = $db->table('MaterialAppVouchs')
                        ->max('AutoID') ?? 0;

                // 3. 分配新 autoid 并插入
                foreach ($insertData as &$row) {
                    $maxAutoid++;
                    $row['AutoID'] = $maxAutoid;
                }

                $db->table('MaterialAppVouchs')
                    ->insert($insertData);

                return [true, ''];
            });
        } catch (\Throwable $e) {
            return [false, "重组领料申请单失败: " . $e->getMessage()];
        }
    }

    public function rebuildBjDetails($mainId, $insertData)
    {
        try {
            $db = $this->safeDb();

            return $db->transaction(function () use ($mainId, $insertData, $db) {

                // 1. 删除旧明细
                $db->table('QMINSPECTVOUCHERS')
                    ->where('ID', $mainId)
                    ->delete();

                $maxAutoid = $db->table('QMINSPECTVOUCHERS')
                        ->max('Autoid') ?? 0;

                // 过滤掉不可插入的字段，特别是 UFTS
                foreach ($insertData as $key => $row) {
                    $maxAutoid++;

                    // 彻底清理：移除 UFTS 字段，防止插入报错及 JSON 转换报错
                    unset($row['UFTS']);

                    $row['Autoid'] = $maxAutoid;
                    $insertData[$key] = $row;
                }

                // 4. 批量插入
                $db->table('QMINSPECTVOUCHERS')
                    ->insert($insertData);

                return [true, '操作成功'];
            });
        } catch (\Throwable $e) {
            // 这里返回错误信息时，确保不包含二进制内容
            return [false, "重组报检单失败: " . $e->getMessage()];
        }
    }
}