<?php

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\console\input\Option;
use think\Db;

/**
 * 旧版 Java 存客宝 → cunkebao_v3 数据补全迁移
 *
 * php think migrate:legacyCkb --step=report
 * php think migrate:legacyCkb --step=fixCompany
 * php think migrate:legacyCkb --step=fixTasks
 * php think migrate:legacyCkb --step=customers --batch=3000
 * php think migrate:legacyCkb --step=content
 * php think migrate:legacyCkb --step=tags
 */
class MigrateLegacyCkbCommand extends Command
{
    private array $cfg = [];
    private ?\PDO $src = null;
    private array $companyMap = [];
    private array $taskIdMap = []; // legacyTaskId => new task id

    protected function configure()
    {
        $this->setName('migrate:legacyCkb')
            ->setDescription('旧版存客宝云端数据迁移/补全到 cunkebao_v3')
            ->addOption('step', 's', Option::VALUE_REQUIRED, 'report|fixCompany|fixTasks|customers|content|tags|all', 'report')
            ->addOption('batch', 'b', Option::VALUE_OPTIONAL, '客资每批条数', 5000)
            ->addOption('legacy-task', null, Option::VALUE_OPTIONAL, '仅处理指定旧任务 lId', null)
            ->addOption('dry-run', null, Option::VALUE_NONE, '只统计不写库')
            ->addOption('material-limit', null, Option::VALUE_OPTIONAL, '每个内容库最多迁移素材条数', 2000);
    }

    protected function execute(Input $input, Output $output)
    {
        $this->cfg = include dirname(__DIR__, 2) . '/config/legacy_source.php';
        $step = $input->getOption('step') ?: 'report';
        $dry = (bool)$input->getOption('dry-run');

        $this->src = $this->connectSource();
        $this->companyMap = $this->buildCompanyMap();
        $this->loadTaskIdMap();

        $steps = $step === 'all'
            ? ['report', 'fixCompany', 'fixTasks', 'customers', 'content', 'tags']
            : [$step];

        foreach ($steps as $s) {
            $output->writeln("<info>=== step: {$s} ===</info>");
            switch ($s) {
                case 'report':
                    $this->stepReport($output);
                    break;
                case 'fixCompany':
                    $this->stepFixCompany($output, $dry);
                    break;
                case 'fixTasks':
                    $this->stepFixTasks($output, $dry);
                    break;
                case 'customers':
                    $this->stepCustomers($output, $dry, (int)$input->getOption('batch'), $input->getOption('legacy-task'));
                    break;
                case 'content':
                    $this->stepContent($output, $dry, (int)$input->getOption('material-limit'));
                    break;
                case 'tags':
                    $this->stepTags($output, $dry);
                    break;
                default:
                    $output->writeln("<error>未知 step: {$s}</error>");
                    return 1;
            }
        }

        return 0;
    }

    private function connectSource(): \PDO
    {
        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=%s',
            $this->cfg['host'],
            $this->cfg['port'],
            $this->cfg['database'],
            $this->cfg['charset']
        );
        return new \PDO($dsn, $this->cfg['username'], $this->cfg['password'], [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        ]);
    }

    private function buildCompanyMap(): array
    {
        $map = $this->cfg['company_map'] ?? [];
        $accounts = $this->src->query('SELECT lId, sName FROM Account')->fetchAll();
        $local = Db::name('company')->where('deleteTime', 0)->column('id', 'name');
        foreach ($accounts as $row) {
            $lid = (int)$row['lId'];
            if (isset($map[$lid])) {
                continue;
            }
            $name = trim((string)$row['sName']);
            if ($name !== '' && isset($local[$name])) {
                $map[$lid] = (int)$local[$name];
            }
        }
        return $map;
    }

    private function resolveCompanyId(int $legacyAccountId): int
    {
        if (isset($this->companyMap[$legacyAccountId])) {
            return (int)$this->companyMap[$legacyAccountId];
        }
        $stmt = $this->src->prepare('SELECT sName FROM Account WHERE lId = ?');
        $stmt->execute([$legacyAccountId]);
        $name = trim((string)($stmt->fetchColumn() ?: ''));
        if ($name !== '') {
            $id = Db::name('company')->where(['name' => $name, 'deleteTime' => 0])->value('id');
            if ($id) {
                $this->companyMap[$legacyAccountId] = (int)$id;
                return (int)$id;
            }
        }
        return 0;
    }

    private function loadTaskIdMap(): void
    {
        $rows = Db::name('customer_acquisition_task')
            ->where('isLegacy', 1)
            ->whereNotNull('legacyId')
            ->column('id', 'legacyId');
        foreach ($rows as $legacyId => $id) {
            $this->taskIdMap[(int)$legacyId] = (int)$id;
        }
    }

    private function mapSceneId(?string $typeId): int
    {
        $key = strtolower(trim((string)$typeId));
        return (int)($this->cfg['scene_map'][$key] ?? 9);
    }

    private function stepReport(Output $output): void
    {
        $remoteTasks = (int)$this->src->query('SELECT COUNT(*) FROM FriendRequestTask')->fetchColumn();
        $remoteDetails = (int)$this->src->query('SELECT COUNT(*) FROM FriendRequestTaskDetail')->fetchColumn();
        $remoteLibs = (int)$this->src->query('SELECT COUNT(*) FROM MaterialLib')->fetchColumn();
        $remoteMaterials = (int)$this->src->query('SELECT COUNT(*) FROM Material')->fetchColumn();
        $remoteLabels = (int)$this->src->query('SELECT COUNT(*) FROM WeChatFriendLabelLib')->fetchColumn();

        $localTasks = Db::name('customer_acquisition_task')->count();
        $localLegacy = Db::name('customer_acquisition_task')->where('isLegacy', 1)->count();
        $localCustomers = Db::name('task_customer')->count();
        $localLibs = Db::name('content_library')->where('isDel', 0)->count();
        $localBadCo = Db::name('customer_acquisition_task')->where('isLegacy', 1)->where('companyId', 0)->count();

        $output->writeln("云端 FriendRequestTask: {$remoteTasks}");
        $output->writeln("云端 FriendRequestTaskDetail: {$remoteDetails}");
        $output->writeln("云端 MaterialLib / Material: {$remoteLibs} / {$remoteMaterials}");
        $output->writeln("云端 WeChatFriendLabelLib: {$remoteLabels}");
        $output->writeln("本地 获客计划 / legacy: {$localTasks} / {$localLegacy}");
        $output->writeln("本地 客资: {$localCustomers}");
        $output->writeln("本地 内容库: {$localLibs}");
        $output->writeln("本地 legacy 且 companyId=0: {$localBadCo}");
        $output->writeln('公司映射: ' . json_encode($this->companyMap, JSON_UNESCAPED_UNICODE));
    }

    private function stepFixCompany(Output $output, bool $dry): void
    {
        $fixed = 0;
        $tasks = Db::name('customer_acquisition_task')->where('isLegacy', 1)->select();
        foreach ($tasks as $t) {
            $legacyId = (int)$t['legacyId'];
            $stmt = $this->src->prepare('SELECT AccountId FROM FriendRequestTask WHERE lId = ?');
            $stmt->execute([$legacyId]);
            $accId = (int)$stmt->fetchColumn();
            $companyId = $this->resolveCompanyId($accId);
            if ($companyId <= 0) {
                $output->writeln("<comment>跳过 task legacy={$legacyId} 无公司映射 AccountId={$accId}</comment>");
                continue;
            }
            if ((int)$t['companyId'] === $companyId) {
                continue;
            }
            if (!$dry) {
                Db::name('customer_acquisition_task')->where('id', $t['id'])->update([
                    'companyId' => $companyId,
                    'updateTime' => time(),
                ]);
            }
            $fixed++;
        }
        $output->writeln($dry ? "将修复 companyId: {$fixed} 条" : "已修复 companyId: {$fixed} 条");
    }

    private function stepFixTasks(Output $output, bool $dry): void
    {
        $fixed = 0;
        foreach ($this->taskIdMap as $legacyId => $newId) {
            $stmt = $this->src->prepare(
                'SELECT lId,sName,TypeId,sKey,sHello,sTheme,sTip,PosterId,AccountId,bEnable FROM FriendRequestTask WHERE lId = ?'
            );
            $stmt->execute([$legacyId]);
            $old = $stmt->fetch();
            if (!$old) {
                continue;
            }
            $sceneConf = json_decode(
                (string)Db::name('customer_acquisition_task')->where('id', $newId)->value('sceneConf'),
                true
            ) ?: [];
            $sceneConf['tips'] = $old['sTip'] ?? ($sceneConf['tips'] ?? '');
            $sceneConf['greeting'] = $old['sHello'] ?? ($sceneConf['greeting'] ?? '');
            $sceneConf['theme'] = $old['sTheme'] ?? ($sceneConf['theme'] ?? '');
            $sceneConf['legacyTypeId'] = $old['TypeId'] ?? '';

            $posterUrl = $this->resolvePosterUrl((int)($old['PosterId'] ?? 0));
            if ($posterUrl) {
                $sceneConf['posters'] = [['url' => $posterUrl, 'name' => $old['sName'] ?? '']];
            }

            $mini = $this->resolveMiniProgramForTask($legacyId);
            if ($mini) {
                $sceneConf['miniprogram'] = $mini;
            }

            $update = [
                'sceneId' => $this->mapSceneId($old['TypeId'] ?? ''),
                'sceneConf' => json_encode($sceneConf, JSON_UNESCAPED_UNICODE),
                'companyId' => $this->resolveCompanyId((int)$old['AccountId']),
                'status' => ((int)($old['bEnable'] ?? 0)) === 1 ? 1 : 0,
                'updateTime' => time(),
            ];
            if (!empty($old['sKey'])) {
                $update['apiKey'] = $old['sKey'];
            }

            if (!$dry) {
                Db::name('customer_acquisition_task')->where('id', $newId)->update($update);
            }
            $fixed++;
        }
        $output->writeln($dry ? "将修复计划配置: {$fixed} 条" : "已修复计划配置: {$fixed} 条");
    }

    private function resolvePosterUrl(int $posterId): ?string
    {
        if ($posterId <= 0) {
            return null;
        }
        $stmt = $this->src->prepare(
            'SELECT a.sCdnUrl FROM Poster p LEFT JOIN SysAttach a ON p.sThumb = a.lId WHERE p.lId = ? LIMIT 1'
        );
        $stmt->execute([$posterId]);
        $url = $stmt->fetchColumn();
        return $url ? (string)$url : null;
    }

    private function resolveMiniProgramForTask(int $legacyTaskId): ?array
    {
        $stmt = $this->src->prepare(
            'SELECT m.sName, m.sContent FROM Material m
             INNER JOIN FriendRequestTask t ON t.lId = ?
             WHERE m.TypeId = 5 LIMIT 1'
        );
        $stmt->execute([$legacyTaskId]);
        $row = $stmt->fetch();
        if (!$row || empty($row['sContent'])) {
            return null;
        }
        $content = json_decode($row['sContent'], true);
        if (!is_array($content)) {
            return null;
        }
        return [
            'title' => $content['title'] ?? $row['sName'],
            'appId' => $content['miniprogramId'] ?? ($content['weappinfo']['appid'] ?? ''),
            'pagePath' => $content['pagepath'] ?? ($content['weappinfo']['pagepath'] ?? ''),
            'raw' => $content,
        ];
    }

    private function mapDetailStatus(array $row): int
    {
        if (!empty($row['dPassedTime']) && $row['dPassedTime'] !== '0000-00-00 00:00:00') {
            return 4;
        }
        if (!empty($row['dFailedTime']) && $row['dFailedTime'] !== '0000-00-00 00:00:00') {
            return 3;
        }
        if (!empty($row['dSendTime'])) {
            return 1;
        }
        return 0;
    }

    private function stepCustomers(Output $output, bool $dry, int $batch, ?string $onlyLegacyTask): void
    {
        $legacyFilter = $onlyLegacyTask !== null ? [(int)$onlyLegacyTask] : array_keys($this->taskIdMap);
        $inserted = 0;
        $skipped = 0;

        foreach ($legacyFilter as $legacyTaskId) {
            $newTaskId = $this->taskIdMap[$legacyTaskId] ?? null;
            if (!$newTaskId) {
                continue;
            }
            $existingPhones = Db::name('task_customer')
                ->where('task_id', $newTaskId)
                ->column('phone');
            $existingSet = array_flip($existingPhones);

            $offset = 0;
            while (true) {
                $stmt = $this->src->prepare(
                    'SELECT lId,sPhoneOrWechatId,StatusId,dSendTime,dPassedTime,dFailedTime,sFailMessage,dNewTime
                     FROM FriendRequestTaskDetail
                     WHERE FriendRequestTaskId = :tid
                     ORDER BY lId ASC LIMIT :lim OFFSET :off'
                );
                $stmt->bindValue(':tid', $legacyTaskId, \PDO::PARAM_INT);
                $stmt->bindValue(':lim', $batch, \PDO::PARAM_INT);
                $stmt->bindValue(':off', $offset, \PDO::PARAM_INT);
                $stmt->execute();
                $rows = $stmt->fetchAll();
                if (!$rows) {
                    break;
                }

                $batchInsert = [];
                foreach ($rows as $row) {
                    $rawPhone = trim((string)($row['sPhoneOrWechatId'] ?? ''));
                    if ($rawPhone === '') {
                        $skipped++;
                        continue;
                    }
                    $phone = mb_substr($rawPhone, 0, 50);
                    if (isset($existingSet[$phone])) {
                        $skipped++;
                        continue;
                    }
                    $remark = strlen($rawPhone) > 50 ? $rawPhone : '';
                    $createTs = $row['dNewTime'] ? strtotime($row['dNewTime']) : time();
                    $addTs = $row['dSendTime'] ? strtotime($row['dSendTime']) : 0;
                    $passTs = $row['dPassedTime'] ? strtotime($row['dPassedTime']) : 0;
                    $batchInsert[] = [
                        'task_id' => $newTaskId,
                        'channelId' => 0,
                        'name' => '',
                        'source' => 'legacy_import',
                        'phone' => $phone,
                        'remark' => mb_substr($remark, 0, 100),
                        'tags' => null,
                        'siteTags' => null,
                        'processed_wechat_ids' => '',
                        'status' => $this->mapDetailStatus($row),
                        'fail_reason' => (string)($row['sFailMessage'] ?? ''),
                        'addTime' => $addTs ?: 0,
                        'passTime' => $passTs ?: 0,
                        'createTime' => $createTs,
                        'updateTime' => $createTs,
                    ];
                    $existingSet[$phone] = true;
                }

                if (!$dry && $batchInsert) {
                    Db::name('task_customer')->insertAll($batchInsert);
                }
                $inserted += count($batchInsert);
                $offset += $batch;
                $output->writeln("  legacy task {$legacyTaskId} -> {$newTaskId}: +{$inserted} (skip {$skipped}) offset {$offset}");
            }
        }
        $output->writeln($dry ? "将新增客资约 {$inserted} 条" : "已新增客资 {$inserted} 条，跳过 {$skipped}");
    }

    private function stepContent(Output $output, bool $dry, int $materialLimit = 2000): void
    {
        $libs = $this->src->query(
            'SELECT lib.lId, lib.sName, lib.sKeyWord, lib.sExclude, lib.sConfigJson, lib.sCollectObjectJson,
                    lib.bAIEnable, lib.sAIRequire, lib.NewUserId, u.AccountId
             FROM MaterialLib lib
             LEFT JOIN SysUser u ON lib.NewUserId = u.lID'
        )->fetchAll();

        $libCreated = 0;
        $itemCreated = 0;

        foreach ($libs as $lib) {
            $legacyLibId = (int)$lib['lId'];
            $exists = Db::name('content_library')->where('legacyLibId', $legacyLibId)->find();
            if ($exists) {
                $libraryId = (int)$exists['id'];
            } else {
                $companyId = $this->resolveCompanyId((int)($lib['AccountId'] ?? 0));
                if ($companyId <= 0) {
                    $companyId = 914;
                }
                $row = [
                    'formType' => 0,
                    'sourceType' => 1,
                    'name' => $lib['sName'] ?: ('旧库' . $legacyLibId),
                    'keywordInclude' => $lib['sKeyWord'] ? json_encode([$lib['sKeyWord']]) : null,
                    'keywordExclude' => $lib['sExclude'] ? json_encode([$lib['sExclude']]) : null,
                    'aiEnabled' => (int)($lib['bAIEnable'] ?? 0),
                    'aiPrompt' => $lib['sAIRequire'] ?? '',
                    'status' => 1,
                    'userId' => (int)($lib['NewUserId'] ?? 0),
                    'companyId' => $companyId,
                    'legacyLibId' => $legacyLibId,
                    'createTime' => time(),
                    'updateTime' => time(),
                    'isDel' => 0,
                ];
                if ($dry) {
                    $libCreated++;
                    continue;
                }
                $libraryId = (int)Db::name('content_library')->insertGetId($row);
                $libCreated++;
            }

            $mStmt = $this->src->prepare(
                'SELECT lId,TypeId,sContent,sContentAI,sPic,sLink,sTitle,sThumb,MaterialLibId,dNewTime,lWechatTime
                 FROM Material WHERE MaterialLibId = ? ORDER BY lId ASC LIMIT ' . max(1, $materialLimit)
            );
            $mStmt->execute([$legacyLibId]);
            while ($m = $mStmt->fetch()) {
                $legacyMatId = (int)$m['lId'];
                if (Db::name('content_item')->where('legacyMaterialId', $legacyMatId)->find()) {
                    continue;
                }
                $contentType = $this->mapMaterialType((int)($m['TypeId'] ?? 0));
                $resUrls = [];
                if (!empty($m['sPic'])) {
                    $resUrls[] = $m['sPic'];
                }
                if (!empty($m['sThumb']) && is_numeric($m['sThumb'])) {
                    $url = $this->resolveAttachUrl((int)$m['sThumb']);
                    if ($url) {
                        $resUrls[] = $url;
                    }
                }
                $item = [
                    'libraryId' => $libraryId,
                    'type' => 'moment',
                    'contentType' => $contentType,
                    'title' => $m['sTitle'] ?? '',
                    'content' => $m['sContent'] ?? '',
                    'contentAi' => $m['sContentAI'] ?? '',
                    'contentData' => json_encode(['link' => $m['sLink'] ?? '', 'legacyTypeId' => $m['TypeId']], JSON_UNESCAPED_UNICODE),
                    'resUrls' => $resUrls ? json_encode($resUrls, JSON_UNESCAPED_UNICODE) : null,
                    'status' => 1,
                    'legacyMaterialId' => $legacyMatId,
                    'createTime' => $m['dNewTime'] ? strtotime($m['dNewTime']) : time(),
                    'updateTime' => time(),
                    'isDel' => 0,
                ];
                if (!$dry) {
                    Db::name('content_item')->insert($item);
                }
                $itemCreated++;
            }
        }
        $output->writeln($dry ? "将新增内容库 {$libCreated} / 素材 {$itemCreated}" : "已新增内容库 {$libCreated} / 素材 {$itemCreated}");
    }

    private function mapMaterialType(int $typeId): int
    {
        $map = [1 => 4, 2 => 1, 3 => 3, 5 => 5, 43 => 3];
        return $map[$typeId] ?? 0;
    }

    private function resolveAttachUrl(int $attachId): ?string
    {
        $stmt = $this->src->prepare('SELECT sCdnUrl FROM SysAttach WHERE lId = ?');
        $stmt->execute([$attachId]);
        $url = $stmt->fetchColumn();
        return $url ? (string)$url : null;
    }

    private function stepTags(Output $output, bool $dry): void
    {
        $labels = $this->src->query(
            'SELECT lId,sName,FriendRequestTaskId,UpId,ValueTypeId,jListValue,bSys FROM WeChatFriendLabelLib ORDER BY lId'
        )->fetchAll();

        $catCreated = 0;
        $defCreated = 0;

        foreach ($labels as $lab) {
            $legacyTaskId = (int)($lab['FriendRequestTaskId'] ?? 0);
            $companyId = 914;
            if ($legacyTaskId > 0) {
                $stmt = $this->src->prepare('SELECT AccountId FROM FriendRequestTask WHERE lId = ?');
                $stmt->execute([$legacyTaskId]);
                $companyId = $this->resolveCompanyId((int)$stmt->fetchColumn()) ?: 914;
            }

            $catName = '旧版_' . ($lab['sName'] ?: ('标签' . $lab['lId']));
            $catCode = 'legacy_cat_' . (int)$lab['lId'];
            $cat = Db::name('traffic_pool_tag_category')
                ->where(['companyId' => $companyId, 'categoryCode' => $catCode])
                ->find();
            if (!$cat) {
                if (!$dry) {
                    $catId = Db::name('traffic_pool_tag_category')->insertGetId([
                        'companyId' => $companyId,
                        'parentId' => 0,
                        'tagType' => 2,
                        'categoryCode' => $catCode,
                        'categoryName' => $catName,
                        'sort' => 0,
                        'status' => 1,
                        'createTime' => time(),
                        'updateTime' => time(),
                        'isDel' => 0,
                    ]);
                } else {
                    $catId = 0;
                }
                $catCreated++;
            } else {
                $catId = (int)$cat['id'];
            }

            $values = [];
            if (!empty($lab['jListValue'])) {
                $list = json_decode($lab['jListValue'], true);
                if (is_array($list)) {
                    foreach ($list as $v) {
                        $values[] = is_array($v) ? ($v['name'] ?? $v['value'] ?? '') : (string)$v;
                    }
                }
            }
            if (!$values) {
                $values = [$lab['sName']];
            }

            foreach ($values as $val) {
                $val = trim((string)$val);
                if ($val === '') {
                    continue;
                }
                $exists = Db::name('traffic_pool_tag_define')
                    ->where(['companyId' => $companyId, 'tagName' => $val, 'categoryId' => $catId ?? 0])
                    ->find();
                if ($exists) {
                    continue;
                }
                $tagCode = 'legacy_' . (int)$lab['lId'] . '_' . md5($val);
                if (!$dry) {
                    Db::name('traffic_pool_tag_define')->insert([
                        'companyId' => $companyId,
                        'categoryId' => $catId ?? 0,
                        'tagType' => 2,
                        'tagCode' => substr($tagCode, 0, 50),
                        'tagName' => mb_substr($val, 0, 50),
                        'status' => 1,
                        'createTime' => time(),
                        'updateTime' => time(),
                        'isDel' => 0,
                    ]);
                }
                $defCreated++;
            }

            if ($legacyTaskId > 0 && isset($this->taskIdMap[$legacyTaskId]) && !$dry) {
                $newId = $this->taskIdMap[$legacyTaskId];
                $task = Db::name('customer_acquisition_task')->where('id', $newId)->find();
                $tagConf = json_decode($task['tagConf'] ?? '{}', true) ?: [];
                $tagConf['legacyLabels'] = $tagConf['legacyLabels'] ?? [];
                $tagConf['legacyLabels'][] = [
                    'legacyLibId' => (int)$lab['lId'],
                    'name' => $lab['sName'],
                    'values' => $values,
                ];
                Db::name('customer_acquisition_task')->where('id', $newId)->update([
                    'tagConf' => json_encode($tagConf, JSON_UNESCAPED_UNICODE),
                    'updateTime' => time(),
                ]);
            }
        }
        $output->writeln($dry ? "将新增标签类目 {$catCreated} / 定义 {$defCreated}" : "已新增标签类目 {$catCreated} / 定义 {$defCreated}");
    }
}