fdesk/app/command/InquiryBulkAssignCommand.php

<?php

namespace app\command;

use app\common\utils\email\InquiryDigestMail;
use app\common\utils\email\MailSend;
use app\crm\model\CustomerHis;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use support\Db;

class InquiryBulkAssignCommand extends Command
{
    protected static $defaultName = 'inquiry:bulk-assign';
    protected static $defaultDescription = '按 Excel 行序轮询分配询盘给业务员，更新 crm_customer.user_id 并发汇总邮件';

    // Excel 列映射（A=编号/code）
    private const COL_CODE = 'A';
    private const COL_CHANNEL = 'D';
    private const COL_EMAIL = 'E';
    private const COL_PHONE = 'F';
    private const COL_WHATSAPP = 'G';
    private const COL_CONTACT = 'H';
    private const COL_REMARK = 'I';
    private const COL_COUNTRY_CN = 'J';

    protected function configure()
    {
        $this->addOption('file', null, InputOption::VALUE_REQUIRED, 'Excel 文件路径', 'docs/崔琪2.xlsx')
            ->addOption('users', null, InputOption::VALUE_REQUIRED, '目标 user_id 列表，逗号分隔', '34,73,72,155,64,211')
            ->addOption('dry-run', null, InputOption::VALUE_NONE, '只解析+匹配+分配，打印报告，不写库不发邮件')
            ->addOption('only-user', null, InputOption::VALUE_REQUIRED, '仅给指定 user_id 发邮件（试发）', '0')
            ->addOption('notify-only', null, InputOption::VALUE_NONE, '跳过写库，按 manifest 补发未成功的邮件')
            ->addOption('no-notify', null, InputOption::VALUE_NONE, '只写库不发邮件（先核对库再用 --notify-only 补发）')
            ->addOption('self-test', null, InputOption::VALUE_NONE, '运行纯逻辑自测');
    }

    /**
     * 内存轮询分配（确定性 i % n）
     * 给 $matched 每项追加 new_user_id，返回 [user_id => [rows...]]
     */
    public static function assign(array &$matched, array $targetUserIds): array
    {
        $groups = [];
        foreach ($targetUserIds as $uid) {
            $groups[$uid] = [];
        }
        $n = count($targetUserIds);
        foreach ($matched as $i => &$row) {
            $uid = $targetUserIds[$i % $n];
            $row['new_user_id'] = $uid;
            $groups[$uid][] = $row;
        }
        unset($row);
        return $groups;
    }

    /**
     * 解析 Excel，按行顺序返回询盘数组
     */
    private function parseExcel(string $filePath): array
    {
        $sheet = IOFactory::load($filePath)->getActiveSheet();
        $highestRow = $sheet->getHighestRow();
        $rows = [];
        $get = function (string $col, int $r) use ($sheet): string {
            return trim((string)($sheet->getCell($col . $r)->getValue() ?? ''));
        };
        for ($r = 2; $r <= $highestRow; $r++) {
            $code = $get(self::COL_CODE, $r);
            if ($code === '') {
                continue;
            }
            $rows[] = [
                'code' => $code,
                'channel' => $get(self::COL_CHANNEL, $r),
                'email' => $get(self::COL_EMAIL, $r),
                'phone' => $get(self::COL_PHONE, $r),
                'whatsapp' => $get(self::COL_WHATSAPP, $r),
                'contact' => $get(self::COL_CONTACT, $r),
                'country_cn' => $get(self::COL_COUNTRY_CN, $r),
                'remark' => $get(self::COL_REMARK, $r),
                'excel_row' => $r,
            ];
        }
        return $rows;
    }

    /**
     * 批量匹配 crm_customer（code 唯一取 MIN(id)，排除软删除）
     * 返回 ['matched' => [...], 'unmatched' => [...]]；matched 每项追加 customer_id/old_user_id
     */
    private function matchCustomers(array $rows): array
    {
        $codes = array_values(array_unique(array_column($rows, 'code')));
        $map = []; // code => ['id','old_user_id']
        foreach (array_chunk($codes, 500) as $chunk) {
            $recs = Db::table('crm_customer')
                ->whereIn('code', $chunk)
                ->whereNull('deleted_at')
                ->orderBy('id', 'asc')
                ->get(['id', 'code', 'user_id']);
            foreach ($recs as $rec) {
                if (!isset($map[$rec->code])) {
                    $map[$rec->code] = ['id' => (int)$rec->id, 'old_user_id' => $rec->user_id];
                }
            }
        }
        $matched = [];
        $unmatched = [];
        foreach ($rows as $row) {
            if (isset($map[$row['code']])) {
                $row['customer_id'] = $map[$row['code']]['id'];
                $row['old_user_id'] = $map[$row['code']]['old_user_id'];
                $matched[] = $row;
            } else {
                $unmatched[] = $row;
            }
        }
        return ['matched' => $matched, 'unmatched' => $unmatched];
    }

    /**
     * 单事务：更新 user_id + 写历史。用 Db::table() 不触发 model 事件。
     */
    private function applyAssignments(array $matched): void
    {
        Db::connection()->transaction(function () use ($matched) {
            foreach ($matched as $row) {
                Db::table('crm_customer')
                    ->where('id', $row['customer_id'])
                    ->update(['user_id' => $row['new_user_id']]);
                CustomerHis::create([
                    'customer_id' => $row['customer_id'],
                    'user_his' => $row['old_user_id'],
                    'user_id' => $row['new_user_id'],
                ]);
            }
        });
    }

    private function manifestPath(string $date): string
    {
        return runtime_path() . '/inquiry_bulk_assign_' . $date . '.json';
    }

    private function initManifest(string $path, array $targetUserIds, array $matched, array $groups): void
    {
        if (is_file($path)) {
            return; // 已存在则保留（含 emailed 标记），供补发使用
        }
        $perUser = [];
        $emailed = [];
        foreach ($targetUserIds as $uid) {
            $perUser[(string)$uid] = count($groups[$uid] ?? []);
            $emailed[(string)$uid] = false;
        }
        $data = [
            'date' => date('Y-m-d'),
            'assigned_at' => time(),
            'target_users' => $targetUserIds,
            'total_matched' => count($matched),
            'per_user_counts' => $perUser,
            'emailed' => $emailed,
        ];
        file_put_contents($path, json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
    }

    private function loadManifest(string $path): array
    {
        if (!is_file($path)) {
            return ['emailed' => []];
        }
        return json_decode((string)file_get_contents($path), true) ?: ['emailed' => []];
    }

    private function saveManifest(string $path, array $data): void
    {
        file_put_contents($path, json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
    }

    /**
     * 按 targetUserIds 顺序发汇总邮件；跳过已标记成功与 --only-user 之外的人。
     */
    private function sendDigests(array $groups, array $targetUserIds, string $manifestPath, int $onlyUser, OutputInterface $output): void
    {
        $users = Db::table('user')->whereIn('id', $targetUserIds)->get(['id', 'name', 'email']);
        $userMap = [];
        foreach ($users as $u) {
            $userMap[(int)$u->id] = $u;
        }
        $manifest = $this->loadManifest($manifestPath);
        $date = date('Y-m-d');

        foreach ($targetUserIds as $uid) {
            if ($onlyUser > 0 && $uid !== $onlyUser) {
                continue;
            }
            if (!empty($manifest['emailed'][(string)$uid])) {
                $output->writeln('<comment>跳过已发送: user ' . $uid . '</comment>');
                continue;
            }
            $u = $userMap[$uid] ?? null;
            if (!$u || empty($u->email)) {
                $output->writeln('<error>user ' . $uid . ' 无邮箱，跳过</error>');
                continue;
            }
            $inquiries = $groups[$uid] ?? [];
            $html = InquiryDigestMail::build((string)$u->name, $inquiries, $date);
            $subject = '询盘分配汇总（共 ' . count($inquiries) . ' 条）- ' . $u->name . ' - ' . $date;

            $ok = MailSend::send($u->email, $subject, $html);
            if (!$ok) {
                $ok = MailSend::send($u->email, $subject, $html); // 重试 1 次
            }
            $manifest['emailed'][(string)$uid] = (bool)$ok;
            $this->saveManifest($manifestPath, $manifest);

            $tag = $ok ? 'info' : 'error';
            $output->writeln('<' . $tag . '>user ' . $uid . ' (' . $u->email . '): '
                . ($ok ? '发送成功' : '发送失败') . '，共 ' . count($inquiries) . ' 条</' . $tag . '>');
        }
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        if ($input->getOption('self-test')) {
            return $this->runSelfTest($output);
        }

        $file = $input->getOption('file');
        $targetUserIds = array_values(array_filter(array_map('intval', explode(',', (string)$input->getOption('users')))));
        $dryRun = (bool)$input->getOption('dry-run');
        $notifyOnly = (bool)$input->getOption('notify-only');
        $noNotify = (bool)$input->getOption('no-notify');
        $onlyUser = (int)$input->getOption('only-user');
        $date = date('Y-m-d');

        if (!$targetUserIds) {
            $output->writeln('<error>--users 不能为空</error>');
            return self::FAILURE;
        }
        if (!is_file($file)) {
            $output->writeln('<error>文件不存在: ' . $file . '</error>');
            return self::FAILURE;
        }
        if ($notifyOnly && $noNotify) {
            $output->writeln('<error>--notify-only 与 --no-notify 不能同时使用</error>');
            return self::FAILURE;
        }

        $output->writeln('<info>解析 Excel: ' . $file . '</info>');
        $rows = $this->parseExcel($file);
        $output->writeln('<info>Excel 询盘数: ' . count($rows) . '</info>');

        $res = $this->matchCustomers($rows);
        $matched = $res['matched'];
        $unmatched = $res['unmatched'];
        $output->writeln('<info>匹配成功: ' . count($matched) . '，未匹配: ' . count($unmatched) . '</info>');
        if ($unmatched) {
            $sample = implode(', ', array_slice(array_column($unmatched, 'code'), 0, 10));
            $output->writeln('<comment>未匹配 code 样例: ' . $sample . '</comment>');
        }

        $groups = self::assign($matched, $targetUserIds);
        foreach ($targetUserIds as $uid) {
            $output->writeln('<info>user ' . $uid . ' 分配: ' . count($groups[$uid]) . ' 条</info>');
        }
        foreach ($matched as $i => $row) {
            if ($i < 3) {
                $output->writeln('<comment>样例[' . $i . '] code=' . $row['code']
                    . ' -> user ' . $row['new_user_id'] . ' (原 ' . $row['old_user_id'] . ')</comment>');
            }
        }

        if ($dryRun) {
            $output->writeln('<info>[dry-run] 不写库、不发邮件。确认无误后去掉 --dry-run 执行。</info>');
            return self::SUCCESS;
        }

        if (!$notifyOnly) {
            $this->applyAssignments($matched);
            $output->writeln('<info>已写库（user_id 更新 + crm_customer_his），共 ' . count($matched) . ' 条。</info>');
        } else {
            $output->writeln('<comment>[notify-only] 跳过写库。</comment>');
        }

        if ($noNotify) {
            $output->writeln('<comment>[no-notify] 已写库，跳过发邮件。核对库后用 --notify-only 补发。</comment>');
            return self::SUCCESS;
        }

        $manifestPath = $this->manifestPath($date);
        $this->initManifest($manifestPath, $targetUserIds, $matched, $groups);
        $this->sendDigests($groups, $targetUserIds, $manifestPath, $onlyUser, $output);
        return self::SUCCESS;
    }

    private function runSelfTest(OutputInterface $output): int
    {
        $fail = 0;

        // 1) assign: 7 条 / 3 人 -> 计数 [3,2,2]
        $items = array_fill(0, 7, []);
        $groups = self::assign($items, [10, 20, 30]);
        $counts = [count($groups[10]), count($groups[20]), count($groups[30])];
        if ($counts !== [3, 2, 2]) {
            $output->writeln('<error>assign 计数错误: ' . json_encode($counts) . '</error>');
            $fail++;
        }

        // 2) InquiryDigestMail::build 内容校验
        $html = InquiryDigestMail::build('张三', [[
            'code' => 'CM1', 'contact' => 'A', 'email' => 'a@x.com',
            'phone' => '1', 'country_cn' => '中国', 'channel' => 'fb', 'remark' => '意向车型：X',
        ]], '2026-07-17');
        if (strpos($html, '张三') === false || strpos($html, 'CM1') === false
            || strpos($html, '共 <strong>1</strong> 条') === false) {
            $output->writeln('<error>InquiryDigestMail::build 内容校验失败</error>');
            $fail++;
        }

        if ($fail === 0) {
            $output->writeln('<info>self-test 通过</info>');
            return self::SUCCESS;
        }
        return self::FAILURE;
    }
}