<?php

namespace app\api\controller;

use app\common\controller\Api;
use think\Db;
use think\Exception;
use think\Log;

/**
 * 设备生产信息统计
 */
class ProductionInformationStatistics extends Api
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];

    /** @var string 主表 */
    protected $tableName = '设备_生产信息统计表';

    /** @var string 日志表 */
    protected $logTableName = '设备_生产信息统计操作日志';

    /** @var array 固定班组 */
    protected $defaultShifts = ['A班', 'B班'];

    /** @var string 设备基本资料表 */
    protected $equipmentTable = '设备_基本资料';

    /** @var array 可编辑业务字段 */
    protected $dataFields = [
        'sczl_jtbh',
        'sczl_bzdh',
        'sczl_rq',
        'theoretical_speed',
        'scheduled_production',
        'scheduled_maintenance',
        'scheduled_handover',
        'fault',
        'changeover',
        'proofing',
        'abnormal',
        'planned_output',
        'actual_output',
        'defective_product',
    ];

    /** @var array 字段中文名 */
    protected $fieldLabels = [
        'sczl_jtbh'            => '设备编号',
        'sczl_bzdh'            => '班组代号',
        'sczl_rq'              => '日期',
        'theoretical_speed'    => '理论速度',
        'scheduled_production' => '计划生产时间',
        'scheduled_maintenance'=> '计划保养时间',
        'scheduled_handover'   => '计划早会交接班时间',
        'fault'                => '设备故障时间',
        'changeover'           => '换型时间',
        'proofing'             => '打样时间',
        'abnormal'             => '异常时间',
        'planned_output'       => '计划完成产量',
        'actual_output'        => '实际产量',
        'defective_product'    => '不良品数量',
    ];

    /** @var array 按月汇总时求和的字段 */
    protected $sumFields = [
        'scheduled_production',
        'scheduled_maintenance',
        'scheduled_handover',
        'fault',
        'changeover',
        'proofing',
        'abnormal',
        'planned_output',
        'actual_output',
        'defective_product',
    ];

    /**
     * 1. 左侧菜单：年 -> 月 -> 日
     * @ApiMethod (GET)
     */
    public function getMenu()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $dateList = Db::name($this->tableName)
            ->where('sczl_rq', 'not null')
            ->field("DISTINCT DATE_FORMAT(sczl_rq, '%Y-%m-%d') AS rq")
            ->order('rq DESC')
            ->select();

        $tree = [];
        foreach ($dateList as $item) {
            $rq = $item['rq'];
            if (empty($rq)) {
                continue;
            }
            $year = date('Y', strtotime($rq));
            $month = date('Y-m', strtotime($rq));

            if (!isset($tree[$year])) {
                $tree[$year] = [
                    'year'     => $year,
                    'children' => [],
                ];
            }
            if (!isset($tree[$year]['children'][$month])) {
                $tree[$year]['children'][$month] = [
                    'month'    => $month,
                    'children' => [],
                ];
            }
            if (!in_array($rq, $tree[$year]['children'][$month]['children'], true)) {
                $tree[$year]['children'][$month]['children'][] = $rq;
            }
        }

        $result = array_values($tree);
        foreach ($result as &$yearItem) {
            $yearItem['children'] = array_values($yearItem['children']);
        }
        unset($yearItem);

        $this->success('成功', $result);
    }

    /**
     * 2. 表格数据查询（联表设备基本资料）
     * @ApiMethod (GET)
     * @param string date 时间参数：Y-m-d 查日明细，Y-m 查月汇总
     */
    public function getList()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $time = trim((string)$this->request->param('date', ''));
        if ($time === '') {
            $this->error('请传入 date 参数，格式 Y-m-d 或 Y-m');
        }

        $query = $this->parseQueryTime($time);
        if (!$this->hasStatsInPeriod($query)) {
            if ($query['type'] === 'day') {
                $this->error(date('Y-m-d', strtotime($query['start'])) . '没有数据，请添加后查询');
            }
            $this->error($query['month'] . '没有数据，请添加后查询');
        }

        $list = $this->buildDisplayList($query);

        $this->success('成功', $list);
    }

    /**
     * 判断指定时间段内是否存在统计数据
     */
    protected function hasStatsInPeriod(array $query)
    {
        $count = Db::name($this->tableName)
            ->where('sczl_rq', '>=', $query['start'])
            ->where('sczl_rq', '<=', $query['end'])
            ->count();

        return $count > 0;
    }

    /**
     * 解析查询时间：Y-m-d 为日，Y-m 为月
     */
    protected function parseQueryTime($time)
    {
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $time)) {
            $timestamp = strtotime($time);
            if ($timestamp === false || date('Y-m-d', $timestamp) !== $time) {
                $this->error('日期格式错误，请传 Y-m-d，如 2026-07-23');
            }
            return [
                'type'  => 'day',
                'start' => date('Y-m-d 00:00:00', $timestamp),
                'end'   => date('Y-m-d 23:59:59', $timestamp),
            ];
        }

        if (preg_match('/^\d{4}-\d{2}$/', $time)) {
            $timestamp = strtotime($time . '-01');
            if ($timestamp === false || date('Y-m', $timestamp) !== $time) {
                $this->error('月份格式错误，请传 Y-m，如 2026-07');
            }
            return [
                'type'  => 'month',
                'month' => $time,
                'start' => date('Y-m-01 00:00:00', $timestamp),
                'end'   => date('Y-m-t 23:59:59', $timestamp),
            ];
        }

        $this->error('时间格式错误，请传 Y-m-d（具体日期）或 Y-m（月份）');
    }

    /**
     * 供 Excel 导出使用的表格数据
     * @param string $time Y-m-d 或 Y-m
     * @return array
     */
    public function getExportData($time)
    {
        $time = trim((string)$time);
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $time)) {
            $timestamp = strtotime($time);
            if ($timestamp === false) {
                return [];
            }
            $query = [
                'type'  => 'day',
                'start' => date('Y-m-d 00:00:00', $timestamp),
                'end'   => date('Y-m-d 23:59:59', $timestamp),
            ];
        } elseif (preg_match('/^\d{4}-\d{2}$/', $time)) {
            $timestamp = strtotime($time . '-01');
            if ($timestamp === false) {
                return [];
            }
            $query = [
                'type'  => 'month',
                'month' => $time,
                'start' => date('Y-m-01 00:00:00', $timestamp),
                'end'   => date('Y-m-t 23:59:59', $timestamp),
            ];
        } else {
            return [];
        }

        return $this->buildDisplayList($query);
    }

    /**
     * 3. 获取车间、机组、机台编号、机台名称、班组基础数据
     * @ApiMethod (GET)
     */
    public function getBaseData()
    {
        if (!$this->request->isGet()) {
            $this->error('请求错误');
        }

        $equipmentMap = $this->fetchEquipmentMap(true);
        if (empty($equipmentMap)) {
            $this->success('未获取到机台数据', []);
        }

        $list = [];
        foreach ($equipmentMap as $code => $equipment) {
            $list[] = [
                '车间'       => $equipment['车间'],
                '机组'       => $equipment['机组'],
                '编组'       => $equipment['编组'],
                '机台编号'   => $code,
                '机台名称'   => $equipment['机台名称'],
                '设备编号'   => $code,
                '理论速度'   => $equipment['理论速度'],
                '班组列表'   => $this->defaultShifts,
            ];
        }

        $this->success('成功', $this->sortEquipmentList($list));
    }

    /**
     * 4. 批量新增（每个机台每个班组一条）
     * @ApiMethod (POST)
     * @param array  list   数据列表
     * @param string sys_id 操作人
     */
    public function batchAdd()
    {
        if (!$this->request->isPost()) {
            $this->error('请求错误');
        }

        $params = $this->request->post();
        $list = isset($params['list']) ? $params['list'] : [];
        $operator = isset($params['sys_id']) ? trim($params['sys_id']) : '';

        if (empty($list) || !is_array($list)) {
            $this->error('参数 list 不能为空');
        }
        if ($operator === '') {
            $this->error('参数 sys_id 不能为空');
        }

        $now = date('Y-m-d H:i:s');
        $insertRows = [];
        $logs = [];

        Db::startTrans();
        try {
            foreach ($list as $index => $row) {
                if (!is_array($row)) {
                    throw new Exception('第 ' . ($index + 1) . ' 条数据格式错误');
                }

                $data = $this->normalizeRow($row);
                $this->validateRow($data, $index + 1);

                $exists = Db::name($this->tableName)
                    ->where('sczl_jtbh', $data['sczl_jtbh'])
                    ->where('sczl_bzdh', $data['sczl_bzdh'])
                    ->where('sczl_rq', $data['sczl_rq'])
                    ->find();
                if ($exists) {
                    throw new Exception(sprintf(
                        '第 %d 条记录已存在：设备[%s] 班组[%s] 日期[%s]',
                        $index + 1,
                        $data['sczl_jtbh'],
                        $data['sczl_bzdh'],
                        date('Y-m-d', strtotime($data['sczl_rq']))
                    ));
                }

                $data['sys_id'] = $operator;
                $data['sys_rq'] = $now;
                $insertRows[] = $data;
            }

            foreach ($insertRows as $data) {
                $recordId = Db::name($this->tableName)->insertGetId($data);
                if (!$recordId) {
                    throw new Exception('新增失败');
                }
                $logs = array_merge($logs, $this->buildAddLogs($recordId, $data, $operator, $now));
            }

            if (!empty($logs)) {
                Db::name($this->logTableName)->insertAll($logs);
            }

            Db::commit();
            $this->success('批量新增成功', ['count' => count($insertRows)]);
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
    }

    /**
     * 5. 批量修改
     * @ApiMethod (POST)
     * @param array  list   含 id 的数据列表
     * @param string sys_id 操作人
     */
    public function batchUpdate()
    {
        if (!$this->request->isPost()) {
            $this->error('请求错误');
        }

        $params = $this->request->post();
        $list = isset($params['list']) ? $params['list'] : [];
        $operator = isset($params['sys_id']) ? trim($params['sys_id']) : '';

        if (empty($list) || !is_array($list)) {
            $this->error('参数 list 不能为空');
        }
        if ($operator === '') {
            $this->error('参数 sys_id 不能为空');
        }

        $now = date('Y-m-d H:i:s');
        $logs = [];
        $updateCount = 0;

        Db::startTrans();
        try {
            foreach ($list as $index => $row) {
                if (!is_array($row) || empty($row['id'])) {
                    throw new Exception('第 ' . ($index + 1) . ' 条数据缺少 id');
                }

                $id = (int)$row['id'];
                $old = Db::name($this->tableName)->where('id', $id)->find();
                if (empty($old)) {
                    throw new Exception('第 ' . ($index + 1) . ' 条记录不存在');
                }

                $data = $this->normalizeRow($row, false);
                unset($data['id']);

                if (array_key_exists('sczl_bzdh', $row)) {
                    if ($data['sczl_bzdh'] === '' || !in_array($data['sczl_bzdh'], $this->defaultShifts, true)) {
                        throw new Exception('第 ' . ($index + 1) . ' 条班组代号只能为 A班 或 B班');
                    }
                }

                $checkJtbh = isset($data['sczl_jtbh']) ? $data['sczl_jtbh'] : rtrim($old['sczl_jtbh']);
                $checkBzdh = isset($data['sczl_bzdh']) ? $data['sczl_bzdh'] : rtrim($old['sczl_bzdh']);
                $checkRq = isset($data['sczl_rq']) ? $data['sczl_rq'] : $old['sczl_rq'];

                $duplicate = Db::name($this->tableName)
                    ->where('sczl_jtbh', $checkJtbh)
                    ->where('sczl_bzdh', $checkBzdh)
                    ->where('sczl_rq', $checkRq)
                    ->where('id', '<>', $id)
                    ->find();
                if ($duplicate) {
                    throw new Exception(sprintf(
                        '第 %d 条修改后与已有记录冲突：设备[%s] 班组[%s] 日期[%s]',
                        $index + 1,
                        $checkJtbh,
                        $checkBzdh,
                        date('Y-m-d', strtotime($checkRq))
                    ));
                }

                $data['mod_id'] = $operator;
                $data['mod_rq'] = $now;

                $changed = [];
                foreach ($data as $field => $value) {
                    if (!array_key_exists($field, $old)) {
                        continue;
                    }
                    $oldValue = $old[$field];
                    if ((string)$oldValue !== (string)$value) {
                        $changed[$field] = $value;
                    }
                }

                if (empty($changed)) {
                    continue;
                }

                $result = Db::name($this->tableName)->where('id', $id)->update($changed + [
                    'mod_id' => $operator,
                    'mod_rq' => $now,
                ]);
                if ($result === false) {
                    throw new Exception('第 ' . ($index + 1) . ' 条修改失败');
                }

                $logs = array_merge($logs, $this->buildUpdateLogs($id, $old, $changed, $operator, $now));
                $updateCount++;
            }

            if (!empty($logs)) {
                Db::name($this->logTableName)->insertAll($logs);
            }

            Db::commit();
            $this->success('批量修改成功', ['count' => $updateCount]);
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
    }

    /**
     * 6. 批量删除
     * @ApiMethod (POST)
     * @param string ids    逗号分隔 id
     * @param string sys_id 操作人
     */
    public function batchDelete()
    {
        if (!$this->request->isPost()) {
            $this->error('请求错误');
        }

        $params = $this->request->post();
        $idsParam = isset($params['ids']) ? trim($params['ids']) : '';
        $operator = isset($params['sys_id']) ? trim($params['sys_id']) : '';

        if ($idsParam === '') {
            $this->error('参数 ids 不能为空');
        }
        if ($operator === '') {
            $this->error('参数 sys_id 不能为空');
        }

        $ids = array_values(array_filter(array_map('intval', explode(',', $idsParam))));
        if (empty($ids)) {
            $this->error('ids 格式错误');
        }

        $records = Db::name($this->tableName)->where('id', 'in', $ids)->select();
        if (count($records) !== count($ids)) {
            $this->error('部分记录不存在，请刷新后重试');
        }

        $now = date('Y-m-d H:i:s');
        $logs = [];

        Db::startTrans();
        try {
            foreach ($records as $record) {
                $logs = array_merge($logs, $this->buildDeleteLogs((int)$record['id'], $record, $operator, $now));
            }

            $deleteResult = Db::name($this->tableName)->where('id', 'in', $ids)->delete();
            if ($deleteResult === false) {
                throw new Exception('删除失败');
            }

            if (!empty($logs)) {
                Db::name($this->logTableName)->insertAll($logs);
            }

            Db::commit();
            $this->success('批量删除成功', ['count' => $deleteResult]);
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
    }

    /**
     * 构建带计算字段和汇总行的表格数据
     */
    protected function buildDisplayList(array $query)
    {
        if ($query['type'] === 'day') {
            $statsRows = $this->fetchStatsRows($query['start'], $query['end']);
            $dateMeta = [
                'query_type'  => 'day',
                'sczl_rq'     => date('Y-m-d 00:00:00', strtotime($query['start'])),
                'query_month' => '',
            ];
        } else {
            $statsRows = $this->fetchMonthlyStatsRows($query['start'], $query['end']);
            $dateMeta = [
                'query_type'  => 'month',
                'sczl_rq'     => $query['month'] . '-01 00:00:00',
                'query_month' => $query['month'],
            ];
        }

        $equipmentMap = $this->fetchEquipmentMap(true);
        $statsRows = $this->enrichStatsWithEquipment($statsRows, $equipmentMap);

        $statsMap = [];
        foreach ($statsRows as $row) {
            $key = $this->trimText($row['sczl_jtbh']) . '|' . $this->normalizeShift($row['sczl_bzdh']);
            $statsMap[$key] = $row;
        }

        $detailRows = [];
        $processedKeys = [];
        foreach ($equipmentMap as $code => $equipment) {
            foreach ($this->defaultShifts as $shift) {
                $key = $code . '|' . $shift;
                $processedKeys[$key] = true;
                $stat = isset($statsMap[$key]) ? $statsMap[$key] : [];
                $detailRows[] = $this->buildDetailRow($equipment, $shift, $stat, $dateMeta);
            }
        }

        // 补充统计表中有数据但未纳入骨架的行（按设备基本资料补全车间编组）
        foreach ($statsMap as $key => $stat) {
            if (isset($processedKeys[$key])) {
                continue;
            }
            $code = $this->trimText($stat['sczl_jtbh']);
            $shift = $this->normalizeShift($stat['sczl_bzdh']);
            if ($code === '' || $shift === '' || !isset($equipmentMap[$code])) {
                continue;
            }
            $detailRows[] = $this->buildDetailRow($equipmentMap[$code], $shift, $stat, $dateMeta);
        }

        return $this->assembleListWithSummaries($detailRows);
    }

    /**
     * 获取设备基本资料映射（key=机台编号）
     */
    protected function fetchEquipmentMap($displayOnly = true)
    {
        $query = Db::name($this->equipmentTable)
            ->where('使用部门', '<>', '研发中心');

        if ($displayOnly) {
            $query->where('设备编组', '<>', '')->where('sys_sbID', '<>', '');
        }

        $equipments = $query
            ->field([
                'rtrim(使用部门) as 车间',
                'rtrim(设备编组) as 机组',
                'rtrim(设备编号) as 机台编号',
                'rtrim(设备名称) as 机台名称',
                '平均车速',
            ])
            ->order('设备编组,设备编号')
            ->select();

        $map = [];
        foreach ($equipments as $item) {
            $code = $this->trimText($item['机台编号']);
            if ($code === '') {
                continue;
            }
            $map[$code] = $this->formatEquipmentItem($item, $code);
        }

        return $map;
    }

    /**
     * 格式化设备基本资料项
     */
    protected function formatEquipmentItem(array $item, $code = null)
    {
        $code = $code ?: $this->trimText($item['机台编号']);
        $group = $this->trimText($item['机组']);

        return [
            '车间'     => $this->trimText($item['车间']),
            '机组'     => $group,
            '编组'     => $group,
            '机台编号' => $code,
            '机台名称' => $this->trimText($item['机台名称']),
            '理论速度' => (int)$this->formatNumber($item['平均车速']),
        ];
    }

    /**
     * 按机台编号用设备基本资料补全统计数据
     */
    protected function enrichStatsWithEquipment(array $statsRows, array $equipmentMap)
    {
        foreach ($statsRows as &$row) {
            $code = $this->trimText($row['sczl_jtbh']);
            if ($code === '' || !isset($equipmentMap[$code])) {
                continue;
            }
            $equipment = $equipmentMap[$code];
            $row['车间'] = $equipment['车间'];
            $row['机组'] = $equipment['机组'];
            $row['编组'] = $equipment['编组'];
            $row['机台名称'] = $equipment['机台名称'];
        }
        unset($row);

        return $statsRows;
    }

    /**
     * 查询原始统计数据（按日）
     */
    protected function fetchStatsRows($start, $end)
    {
        return Db::name($this->tableName)
            ->alias('a')
            ->where('a.sczl_rq', '>=', $start)
            ->where('a.sczl_rq', '<=', $end)
            ->field([
                'a.id',
                'a.sczl_jtbh',
                'a.sczl_bzdh',
                'a.sczl_rq',
                'a.theoretical_speed',
                'a.scheduled_production',
                'a.scheduled_maintenance',
                'a.scheduled_handover',
                'a.fault',
                'a.changeover',
                'a.proofing',
                'a.abnormal',
                'a.planned_output',
                'a.actual_output',
                'a.defective_product',
                'a.sys_id',
                'a.sys_rq',
                'a.mod_id',
                'a.mod_rq',
            ])
            ->select();
    }

    /**
     * 查询原始统计数据（按月汇总）
     */
    protected function fetchMonthlyStatsRows($start, $end)
    {
        $sumExpr = [];
        foreach ($this->sumFields as $field) {
            $sumExpr[] = "SUM(a.{$field}) as {$field}";
        }

        return Db::name($this->tableName)
            ->alias('a')
            ->where('a.sczl_rq', '>=', $start)
            ->where('a.sczl_rq', '<=', $end)
            ->field(array_merge([
                'MIN(a.id) as id',
                'a.sczl_jtbh',
                'a.sczl_bzdh',
                'MAX(a.theoretical_speed) as theoretical_speed',
            ], $sumExpr))
            ->group('a.sczl_jtbh,a.sczl_bzdh')
            ->select();
    }

    /**
     * 构建明细行
     */
    protected function buildDetailRow(array $equipment, $shift, array $stat, array $dateMeta)
    {
        $row = [
            'row_type'             => 'detail',
            'id'                   => isset($stat['id']) ? (int)$stat['id'] : 0,
            '车间'                 => $equipment['车间'],
            '机组'                 => $equipment['机组'],
            '编组'                 => $equipment['编组'],
            '机台编号'             => $equipment['机台编号'],
            '设备编号'             => $equipment['机台编号'],
            '机台名称'             => $equipment['机台名称'],
            '班组'                 => $shift,
            'sczl_jtbh'            => $equipment['机台编号'],
            'sczl_bzdh'            => $shift,
            'sczl_rq'              => $dateMeta['sczl_rq'],
            'query_type'           => $dateMeta['query_type'],
            'query_month'          => $dateMeta['query_month'],
            'theoretical_speed'    => !empty($stat['id'])
                ? (int)$this->formatNumber($stat['theoretical_speed'])
                : $equipment['理论速度'],
            'scheduled_production' => $this->formatNumber(isset($stat['scheduled_production']) ? $stat['scheduled_production'] : 0),
            'scheduled_maintenance'=> $this->formatNumber(isset($stat['scheduled_maintenance']) ? $stat['scheduled_maintenance'] : 0),
            'scheduled_handover'   => $this->formatNumber(isset($stat['scheduled_handover']) ? $stat['scheduled_handover'] : 0),
            'fault'                => $this->formatNumber(isset($stat['fault']) ? $stat['fault'] : 0),
            'changeover'           => $this->formatNumber(isset($stat['changeover']) ? $stat['changeover'] : 0),
            'proofing'             => $this->formatNumber(isset($stat['proofing']) ? $stat['proofing'] : 0),
            'abnormal'             => $this->formatNumber(isset($stat['abnormal']) ? $stat['abnormal'] : 0),
            'planned_output'       => $this->formatNumber(isset($stat['planned_output']) ? $stat['planned_output'] : 0),
            'actual_output'        => $this->formatNumber(isset($stat['actual_output']) ? $stat['actual_output'] : 0),
            'defective_product'    => $this->formatNumber(isset($stat['defective_product']) ? $stat['defective_product'] : 0),
            'sys_id'               => isset($stat['sys_id']) ? $this->trimText($stat['sys_id']) : '',
            'sys_rq'               => isset($stat['sys_rq']) ? $stat['sys_rq'] : '',
            'mod_id'               => isset($stat['mod_id']) ? $this->trimText($stat['mod_id']) : '',
            'mod_rq'               => isset($stat['mod_rq']) ? $stat['mod_rq'] : '',
        ];

        return $this->appendCalculatedFields($row);
    }

    /**
     * 组装明细 + 汇总行
     */
    protected function assembleListWithSummaries(array $detailRows)
    {
        $result = [];
        $workshopGroups = [];

        foreach ($detailRows as $row) {
            $workshop = $row['车间'];
            $group = $row['机组'];
            if (!isset($workshopGroups[$workshop])) {
                $workshopGroups[$workshop] = [];
            }
            if (!isset($workshopGroups[$workshop][$group])) {
                $workshopGroups[$workshop][$group] = [];
            }
            $workshopGroups[$workshop][$group][] = $row;
        }

        uksort($workshopGroups, function ($a, $b) {
            return strcmp($a, $b);
        });

        foreach ($workshopGroups as $workshop => $groups) {
            uksort($groups, function ($a, $b) {
                $orderA = $this->extractGroupOrder($a);
                $orderB = $this->extractGroupOrder($b);
                if ($orderA !== $orderB) {
                    return $orderA <=> $orderB;
                }
                return strcmp($a, $b);
            });

            $workshopRows = [];
            $workshopBlock = [];
            foreach ($groups as $group => $groupRows) {
                $groupRows = $this->sortEquipmentList($groupRows);
                $workshopRows = array_merge($workshopRows, $groupRows);

                $workshopBlock[] = $this->buildSummaryRow(
                    'group',
                    $workshop,
                    $group,
                    $this->buildGroupShortName($group) . '汇总',
                    '/',
                    $groupRows
                );

                foreach ($this->defaultShifts as $shift) {
                    $shiftRows = array_values(array_filter($groupRows, function ($item) use ($shift) {
                        return $item['班组'] === $shift;
                    }));
                    if (!empty($shiftRows)) {
                        $workshopBlock[] = $this->buildSummaryRow(
                            'group_shift',
                            $workshop,
                            $group,
                            $this->buildGroupShortName($group) . $shift,
                            $shift,
                            $shiftRows
                        );
                    }
                }

                $workshopBlock = array_merge($workshopBlock, $groupRows);
            }

            if (!empty($workshopRows)) {
                array_unshift(
                    $workshopBlock,
                    $this->buildSummaryRow(
                        'workshop',
                        $workshop,
                        '全部机组',
                        $workshop . '全部工序合计',
                        '/',
                        $workshopRows
                    )
                );
            }

            $result = array_merge($result, $workshopBlock);
        }

        return $result;
    }

    /**
     * 提取机组简称（03、卷凹机组 -> 卷凹）
     */
    protected function buildGroupShortName($group)
    {
        $group = $this->trimText($group);
        if (preg_match('/、(.+?)机组/u', $group, $matches)) {
            return $matches[1];
        }
        if (preg_match('/、(.+)/u', $group, $matches)) {
            return $matches[1];
        }
        return $group;
    }

    /**
     * 构建汇总行
     */
    protected function buildSummaryRow($rowType, $workshop, $group, $machineName, $shift, array $sourceRows)
    {
        $aggregated = $this->aggregateBaseFields($sourceRows);
        $row = [
            'row_type'             => $rowType,
            'id'                   => 0,
            '车间'                 => $workshop,
            '机组'                 => $group,
            '编组'                 => $group,
            '机台编号'             => '',
            '设备编号'             => '',
            '机台名称'             => $machineName,
            '班组'                 => $shift,
            'sczl_jtbh'            => '',
            'sczl_bzdh'            => $shift === '/' ? '' : $shift,
            'sczl_rq'              => $sourceRows[0]['sczl_rq'],
            'query_type'           => $sourceRows[0]['query_type'],
            'query_month'          => $sourceRows[0]['query_month'],
            'theoretical_speed'    => 0,
            'scheduled_production' => $aggregated['scheduled_production'],
            'scheduled_maintenance'=> $aggregated['scheduled_maintenance'],
            'scheduled_handover'   => $aggregated['scheduled_handover'],
            'fault'                => $aggregated['fault'],
            'changeover'           => $aggregated['changeover'],
            'proofing'             => $aggregated['proofing'],
            'abnormal'             => $aggregated['abnormal'],
            'planned_output'       => $aggregated['planned_output'],
            'actual_output'        => $aggregated['actual_output'],
            'defective_product'    => $aggregated['defective_product'],
            'sys_id'               => '',
            'sys_rq'               => '',
            'mod_id'               => '',
            'mod_rq'               => '',
        ];

        return $this->appendCalculatedFields($row, $aggregated['standard_output']);
    }

    /**
     * 汇总基础字段
     */
    protected function aggregateBaseFields(array $rows)
    {
        $totals = [
            'scheduled_production' => 0,
            'scheduled_maintenance'=> 0,
            'scheduled_handover'   => 0,
            'fault'                => 0,
            'changeover'           => 0,
            'proofing'             => 0,
            'abnormal'             => 0,
            'planned_output'       => 0,
            'actual_output'        => 0,
            'defective_product'    => 0,
            'standard_output'      => 0,
        ];

        foreach ($rows as $row) {
            foreach ($this->sumFields as $field) {
                $totals[$field] += $this->formatNumber($row[$field]);
            }
            $totals['standard_output'] += $this->formatNumber($row['标准产量']);
        }

        return $totals;
    }

    /**
     * 计算并追加衍生指标
     */
    protected function appendCalculatedFields(array $row, $standardOutputOverride = null)
    {
        $scheduledProduction = $this->formatNumber($row['scheduled_production']);
        $scheduledMaintenance = $this->formatNumber($row['scheduled_maintenance']);
        $scheduledHandover = $this->formatNumber($row['scheduled_handover']);
        $fault = $this->formatNumber($row['fault']);
        $changeover = $this->formatNumber($row['changeover']);
        $proofing = $this->formatNumber($row['proofing']);
        $abnormal = $this->formatNumber($row['abnormal']);
        $plannedOutput = $this->formatNumber($row['planned_output']);
        $actualOutput = $this->formatNumber($row['actual_output']);
        $defectiveProduct = $this->formatNumber($row['defective_product']);
        $theoreticalSpeed = $this->formatNumber($row['theoretical_speed']);

        $loadTime = $scheduledProduction - ($scheduledMaintenance + $scheduledHandover);
        if ($loadTime < 0) {
            $loadTime = 0;
        }

        $actualRunTime = $loadTime - ($fault + $changeover + $proofing + $abnormal);
        if ($actualRunTime < 0) {
            $actualRunTime = 0;
        }

        $standardOutput = $standardOutputOverride !== null
            ? $this->formatNumber($standardOutputOverride)
            : ($theoreticalSpeed * $actualRunTime);

        $planAchievementRate = $plannedOutput > 0
            ? (($actualOutput - $defectiveProduct) / $plannedOutput)
            : 0;
        $timeUtilization = $loadTime > 0 ? ($actualRunTime / $loadTime) : 0;
        $speedUtilization = $standardOutput > 0 ? ($actualOutput / $standardOutput) : 0;
        $qualityRate = $actualOutput > 0
            ? (($actualOutput - $defectiveProduct) / $actualOutput)
            : 0;
        $oee = $timeUtilization * $speedUtilization * $qualityRate;

        $row['负荷时间'] = round($loadTime, 2);
        $row['实际运行时间'] = round($actualRunTime, 2);
        $row['生产计划达成率'] = $this->formatRate($planAchievementRate);
        $row['标准产量'] = round($standardOutput, 2);
        $row['时间利用率'] = $this->formatRate($timeUtilization);
        $row['速度利用率'] = $this->formatRate($speedUtilization);
        $row['质量合格率'] = $this->formatRate($qualityRate);
        $row['设备综合效率'] = $this->formatRate($oee);
        $row['load_time'] = $row['负荷时间'];
        $row['actual_run_time'] = $row['实际运行时间'];
        $row['plan_achievement_rate'] = $row['生产计划达成率'];
        $row['standard_output'] = $row['标准产量'];
        $row['time_utilization'] = $row['时间利用率'];
        $row['speed_utilization'] = $row['速度利用率'];
        $row['quality_rate'] = $row['质量合格率'];
        $row['oee'] = $row['设备综合效率'];

        $row['理论速度'] = (int)$theoreticalSpeed;
        $row['计划生产时间'] = round($scheduledProduction, 2);
        $row['计划保养时间'] = round($scheduledMaintenance, 2);
        $row['计划早会交接班时间'] = round($scheduledHandover, 2);
        $row['设备故障时间'] = round($fault, 2);
        $row['换型时间'] = round($changeover, 2);
        $row['打样时间'] = round($proofing, 2);
        $row['异常时间'] = round($abnormal, 2);
        $row['计划完成产量'] = round($plannedOutput, 2);
        $row['实际产量'] = round($actualOutput, 2);
        $row['不良品数量'] = round($defectiveProduct, 2);

        return $row;
    }

    /**
     * 格式化为百分比（0-100，保留2位）
     */
    protected function formatRate($value)
    {
        return round((float)$value * 100, 2);
    }

    /**
     * 查询字段
     */
    protected function buildSelectFields($isMonth = false)
    {
        $fields = [
            'a.id',
            'a.sczl_jtbh',
            'a.sczl_bzdh',
            'a.sczl_rq',
            'a.theoretical_speed',
            'a.scheduled_production',
            'a.scheduled_maintenance',
            'a.scheduled_handover',
            'a.fault',
            'a.changeover',
            'a.proofing',
            'a.abnormal',
            'a.planned_output',
            'a.actual_output',
            'a.defective_product',
            'a.sys_id',
            'a.sys_rq',
            'a.mod_id',
            'a.mod_rq',
            'rtrim(b.使用部门) as 车间',
            'rtrim(b.设备编组) as 机组',
            'rtrim(b.设备名称) as 机台名称',
        ];

        return $fields;
    }

    /**
     * 格式化返回列表
     */
    protected function formatList($list)
    {
        $result = [];
        foreach ($list as $row) {
            $result[] = [
                'id'                   => (int)$row['id'],
                '车间'                 => isset($row['车间']) ? rtrim($row['车间']) : '',
                '机组'                 => isset($row['机组']) ? rtrim($row['机组']) : '',
                '机台编号'             => rtrim($row['sczl_jtbh']),
                '设备编号'             => rtrim($row['sczl_jtbh']),
                '机台名称'             => isset($row['机台名称']) ? rtrim($row['机台名称']) : '',
                '班组'                 => $this->normalizeShift($row['sczl_bzdh']),
                'sczl_jtbh'            => rtrim($row['sczl_jtbh']),
                'sczl_bzdh'            => $this->normalizeShift($row['sczl_bzdh']),
                'sczl_rq'              => $row['sczl_rq'],
                'theoretical_speed'    => $this->formatNumber($row['theoretical_speed']),
                'scheduled_production' => $this->formatNumber($row['scheduled_production']),
                'scheduled_maintenance'=> $this->formatNumber($row['scheduled_maintenance']),
                'scheduled_handover'   => $this->formatNumber($row['scheduled_handover']),
                'fault'                => $this->formatNumber($row['fault']),
                'changeover'           => $this->formatNumber($row['changeover']),
                'proofing'             => $this->formatNumber($row['proofing']),
                'abnormal'             => $this->formatNumber($row['abnormal']),
                'planned_output'       => $this->formatNumber($row['planned_output']),
                'actual_output'        => $this->formatNumber($row['actual_output']),
                'defective_product'    => $this->formatNumber($row['defective_product']),
                'sys_id'               => isset($row['sys_id']) ? rtrim($row['sys_id']) : '',
                'sys_rq'               => isset($row['sys_rq']) ? $row['sys_rq'] : '',
                'mod_id'               => isset($row['mod_id']) ? rtrim($row['mod_id']) : '',
                'mod_rq'               => isset($row['mod_rq']) ? $row['mod_rq'] : '',
                'query_type'           => isset($row['query_type']) ? $row['query_type'] : 'day',
                'query_month'          => isset($row['query_month']) ? $row['query_month'] : '',
            ];
        }
        return $this->sortEquipmentList($result);
    }

    /**
     * 按设备编组、机台编号数字排序
     */
    protected function sortEquipmentList(array $list)
    {
        usort($list, function ($a, $b) {
            return $this->compareEquipmentRow($a, $b);
        });
        return $list;
    }

    /**
     * 设备行排序比较
     */
    protected function compareEquipmentRow($a, $b)
    {
        $groupA = $this->trimText(isset($a['机组']) ? $a['机组'] : '');
        $groupB = $this->trimText(isset($b['机组']) ? $b['机组'] : '');

        $groupOrderA = $this->extractGroupOrder($groupA);
        $groupOrderB = $this->extractGroupOrder($groupB);
        if ($groupOrderA !== $groupOrderB) {
            return $groupOrderA <=> $groupOrderB;
        }
        if ($groupA !== $groupB) {
            return strcmp($groupA, $groupB);
        }

        $codeA = $this->getMachineCodeFromRow($a);
        $codeB = $this->getMachineCodeFromRow($b);
        $numA = $this->extractMachineNumber($codeA);
        $numB = $this->extractMachineNumber($codeB);
        if ($numA !== $numB) {
            return $numA <=> $numB;
        }
        if ($codeA !== $codeB) {
            return strcmp($codeA, $codeB);
        }

        $shiftA = isset($a['班组']) ? $a['班组'] : (isset($a['sczl_bzdh']) ? $a['sczl_bzdh'] : '');
        $shiftB = isset($b['班组']) ? $b['班组'] : (isset($b['sczl_bzdh']) ? $b['sczl_bzdh'] : '');
        return strcmp($shiftA, $shiftB);
    }

    /**
     * 提取设备编组前缀序号（如 03、卷凹机组 -> 3）
     */
    protected function extractGroupOrder($group)
    {
        $group = $this->trimText($group);
        if (preg_match('/^(\d+)/', $group, $matches)) {
            return (int)$matches[1];
        }
        return PHP_INT_MAX;
    }

    /**
     * 提取机台编号中的数字（如 YWY01# -> 1）
     */
    protected function extractMachineNumber($code)
    {
        $code = $this->trimText($code);
        if (preg_match('/(\d+)/', $code, $matches)) {
            return (int)$matches[1];
        }
        return PHP_INT_MAX;
    }

    /**
     * 从返回行中获取机台编号
     */
    protected function getMachineCodeFromRow($row)
    {
        if (!empty($row['机台编号'])) {
            return $this->trimText($row['机台编号']);
        }
        if (!empty($row['sczl_jtbh'])) {
            return $this->trimText($row['sczl_jtbh']);
        }
        if (!empty($row['设备编号'])) {
            return $this->trimText($row['设备编号']);
        }
        return '';
    }

    /**
     * 去除字符串首尾空白（含全角空格）
     */
    protected function trimText($value)
    {
        return preg_replace('/^[\s\x{3000}]+|[\s\x{3000}]+$/u', '', (string)$value);
    }

    /**
     * 班组名称规范化：仅支持 A班、B班
     */
    protected function normalizeShift($shift)
    {
        $shift = $this->trimText($shift);
        $shift = preg_replace('/\s+/u', '', $shift);
        if ($shift === '') {
            return '';
        }
        if (preg_match('/^A(?:班)?$/iu', $shift)) {
            return 'A班';
        }
        if (preg_match('/^B(?:班)?$/iu', $shift)) {
            return 'B班';
        }
        return '';
    }

    /**
     * 标准化单行数据
     */
    protected function normalizeRow(array $row, $requireAll = true)
    {
        $data = [];
        foreach ($this->dataFields as $field) {
            if (!array_key_exists($field, $row)) {
                if (!$requireAll) {
                    continue;
                }
                $data[$field] = in_array($field, $this->sumFields, true) || $field === 'theoretical_speed' ? 0 : '';
                continue;
            }

            if ($field === 'sczl_rq') {
                $data[$field] = date('Y-m-d 00:00:00', strtotime($row[$field]));
                continue;
            }

            if (in_array($field, ['sczl_jtbh', 'sczl_bzdh'], true)) {
                $value = trim((string)$row[$field]);
                $data[$field] = $field === 'sczl_bzdh' ? $this->normalizeShift($value) : $value;
                continue;
            }

            if ($field === 'theoretical_speed') {
                $data[$field] = (int)$row[$field];
                continue;
            }

            $data[$field] = $row[$field] === '' || $row[$field] === null ? 0 : $row[$field];
        }

        return $data;
    }

    /**
     * 校验单行数据
     */
    protected function validateRow(array $data, $lineNo)
    {
        if ($data['sczl_jtbh'] === '') {
            throw new Exception('第 ' . $lineNo . ' 条设备编号不能为空');
        }
        if ($data['sczl_bzdh'] === '') {
            throw new Exception('第 ' . $lineNo . ' 条班组代号不能为空，且只能为 A班 或 B班');
        }
        if (!in_array($data['sczl_bzdh'], $this->defaultShifts, true)) {
            throw new Exception('第 ' . $lineNo . ' 条班组代号只能为 A班 或 B班');
        }
        if (empty($data['sczl_rq'])) {
            throw new Exception('第 ' . $lineNo . ' 条日期不能为空');
        }

        $equipment = Db::name($this->equipmentTable)
            ->where('设备编号', $data['sczl_jtbh'])
            ->find();
        if (empty($equipment)) {
            throw new Exception('第 ' . $lineNo . ' 条设备编号不存在：' . $data['sczl_jtbh']);
        }
    }

    /**
     * 构建新增日志
     */
    protected function buildAddLogs($recordId, array $data, $operator, $time)
    {
        $logs = [];
        foreach ($this->dataFields as $field) {
            if (!array_key_exists($field, $data)) {
                continue;
            }
            $logs[] = [
                'record_id'      => $recordId,
                'operation_type' => 'add',
                'field_name'     => isset($this->fieldLabels[$field]) ? $this->fieldLabels[$field] : $field,
                'old_value'      => null,
                'new_value'      => (string)$data[$field],
                'operator_id'    => $operator,
                'operation_time' => $time,
            ];
        }
        return $logs;
    }

    /**
     * 构建修改日志
     */
    protected function buildUpdateLogs($recordId, array $old, array $changed, $operator, $time)
    {
        $logs = [];
        foreach ($changed as $field => $newValue) {
            if ($field === 'mod_id' || $field === 'mod_rq') {
                continue;
            }
            $logs[] = [
                'record_id'      => $recordId,
                'operation_type' => 'update',
                'field_name'     => isset($this->fieldLabels[$field]) ? $this->fieldLabels[$field] : $field,
                'old_value'      => isset($old[$field]) ? (string)$old[$field] : '',
                'new_value'      => (string)$newValue,
                'operator_id'    => $operator,
                'operation_time' => $time,
            ];
        }
        return $logs;
    }

    /**
     * 构建删除日志
     */
    protected function buildDeleteLogs($recordId, array $record, $operator, $time)
    {
        $logs = [];
        foreach ($this->dataFields as $field) {
            if (!array_key_exists($field, $record)) {
                continue;
            }
            $logs[] = [
                'record_id'      => $recordId,
                'operation_type' => 'delete',
                'field_name'     => isset($this->fieldLabels[$field]) ? $this->fieldLabels[$field] : $field,
                'old_value'      => (string)$record[$field],
                'new_value'      => null,
                'operator_id'    => $operator,
                'operation_time' => $time,
            ];
        }
        return $logs;
    }

    /**
     * 数值格式化
     */
    protected function formatNumber($value)
    {
        if ($value === null || $value === '') {
            return 0;
        }
        return is_numeric($value) ? (float)$value : $value;
    }
}