-- 审核管理模块迁移 SQL
-- 适用：MySQL 8 / InnoDB / utf8mb4
-- 说明：当前项目生产环境 typeorm.synchronize=false，请先在目标库执行本文件。
-- 注意：本文件用于新库初始化；若目标库已存在旧版审核表，请按实际表结构另行补充 ALTER TABLE 升级语句。

CREATE TABLE IF NOT EXISTS `audit_order` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `createTime` varchar(255) NOT NULL COMMENT '创建时间',
  `updateTime` varchar(255) NOT NULL COMMENT '更新时间',
  `tenantId` int NULL COMMENT '租户ID',
  `orderNo` varchar(64) NOT NULL COMMENT 'TYCM订单号',
  `orderType` varchar(20) NOT NULL COMMENT '订单类型 insure=投保 claim=理赔',
  `oemId` int NULL COMMENT '主机厂ID',
  `agentId` int NULL COMMENT '使用的Agent ID（关联netaclaw_agent）',
  `sessionId` varchar(64) NULL COMMENT 'Agent会话ID（关联netaclaw会话）',
  `requestId` varchar(64) NOT NULL COMMENT '请求唯一标识',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '审核状态 pending=待处理 processing=审核中 approved=通过 rejected=驳回 manual_review=转人工 failed=失败',
  `inputData` json NULL COMMENT 'TYCM传入的原始订单数据（证件URL、录入信息等）',
  `auditResult` json NULL COMMENT '最终审核结果（通过/驳回+原因+评分）',
  `skillResults` json NULL COMMENT '各Skill执行结果汇总',
  `oemName` varchar(50) NULL COMMENT '主机厂名称',
  `productName` varchar(100) NULL COMMENT '产品名称',
  `productNo` varchar(64) NULL COMMENT '产品编号',
  `salesPrice` varchar(20) NULL COMMENT '销售价格',
  `policyFee` varchar(20) NULL COMMENT '保费',
  `insuranceCompany` varchar(50) NULL COMMENT '保险公司',
  `dealerCode` varchar(64) NULL COMMENT '经销商代码',
  `factoryName` varchar(100) NULL COMMENT '经销商名称',
  `creator` varchar(50) NULL COMMENT '录单人',
  `createPhone` varchar(20) NULL COMMENT '录单人手机号',
  `orderSource` varchar(20) NULL COMMENT '订单来源',
  `cardName` varchar(50) NULL COMMENT '车主姓名',
  `cardNumber` varchar(30) NULL COMMENT '身份证号',
  `phone` varchar(20) NULL COMMENT '手机号',
  `carHost` varchar(50) NULL COMMENT '行驶证车主',
  `carFrame` varchar(30) NULL COMMENT '车架号',
  `carPlate` varchar(20) NULL COMMENT '车牌号',
  `brandName` varchar(50) NULL COMMENT '品牌',
  `modelName` varchar(100) NULL COMMENT '车型',
  `vehiclePrice` varchar(20) NULL COMMENT '车辆价格',
  `policyNo` varchar(64) NULL COMMENT '保单号',
  `carPurchaseTime` varchar(30) NULL COMMENT '购车时间',
  `serviceStart` varchar(30) NULL COMMENT '服务开始时间',
  `auditScore` decimal(5,2) NULL COMMENT '审核评分',
  `auditDecision` varchar(20) NULL COMMENT '审核结论',
  `packageType` varchar(50) NULL COMMENT '套餐类型',
  `serviceDuration` varchar(20) NULL COMMENT '服务期限(年)',
  `serviceEnd` varchar(30) NULL COMMENT '服务结束时间',
  `carPower` varchar(20) NULL COMMENT '动力类型',
  `carType` varchar(10) NULL COMMENT '车辆类型(新车/在用车)',
  `fee` varchar(20) NULL COMMENT '产品售价',
  `claimAmount` decimal(12,2) NULL COMMENT '理赔金额',
  `repairType` varchar(30) NULL COMMENT '理赔类型(维修/代步/置换)',
  `relatedPolicyNo` varchar(64) NULL COMMENT '关联投保保单号',
  `repairFactoryName` varchar(100) NULL COMMENT '报修机构',
  `repairItems` varchar(200) NULL COMMENT '报修类型/项目',
  `repairStatus` varchar(30) NULL COMMENT '报修状态',
  `reportNo` varchar(64) NULL COMMENT '报案号',
  `callbackUrl` varchar(500) NULL COMMENT 'TYCM回调地址',
  `callbackStatus` varchar(20) NULL COMMENT '回调状态 pending/success/failed',
  `callbackRetryCount` int NOT NULL DEFAULT 0 COMMENT '回调重试次数',
  `duration` int NULL COMMENT '审核耗时(ms)',
  `remark` text NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_audit_order_orderNo` (`orderNo`),
  UNIQUE KEY `IDX_audit_order_requestId` (`requestId`),
  KEY `IDX_audit_order_createTime` (`createTime`),
  KEY `IDX_audit_order_updateTime` (`updateTime`),
  KEY `IDX_audit_order_tenantId` (`tenantId`),
  KEY `IDX_audit_order_status` (`status`),
  KEY `IDX_audit_order_type_status_time` (`orderType`, `status`, `createTime`),
  KEY `IDX_audit_order_oem_time` (`oemId`, `createTime`),
  KEY `IDX_audit_order_agent_time` (`agentId`, `createTime`),
  KEY `IDX_audit_order_decision_time` (`auditDecision`, `createTime`),
  KEY `IDX_audit_order_policyNo` (`policyNo`),
  KEY `IDX_audit_order_dealer_time` (`factoryName`, `createTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='审核订单主表';

CREATE TABLE IF NOT EXISTS `audit_order_detail` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `createTime` varchar(255) NOT NULL COMMENT '创建时间',
  `updateTime` varchar(255) NOT NULL COMMENT '更新时间',
  `tenantId` int NULL COMMENT '租户ID',
  `orderId` int NOT NULL COMMENT '关联审核订单ID（audit_order.id）',
  `stepType` varchar(30) NOT NULL COMMENT '步骤类型 id_card_ocr/vehicle_invoice_ocr/driving_license_ocr/data_compare/rule_check/insure_audit/claim_audit/damage_detect/audit_report/oem_audit_router/llm_judge',
  `stepName` varchar(100) NOT NULL COMMENT '步骤名称（中文展示用）',
  `skillName` varchar(50) NULL COMMENT '调用的Skill标识',
  `inputData` json NULL COMMENT 'Skill输入数据',
  `outputData` json NULL COMMENT 'Skill输出结果（OCR字段、比对结果等）',
  `score` decimal(5,2) NULL COMMENT '该步骤评分（0-100）',
  `passed` tinyint NULL COMMENT '该步骤是否通过 0=否 1=是 2=警告',
  `remark` text NULL COMMENT '备注/原因',
  `duration` int NULL COMMENT '该步骤耗时(ms)',
  PRIMARY KEY (`id`),
  KEY `IDX_audit_order_detail_createTime` (`createTime`),
  KEY `IDX_audit_order_detail_updateTime` (`updateTime`),
  KEY `IDX_audit_order_detail_tenantId` (`tenantId`),
  KEY `IDX_audit_order_detail_orderId` (`orderId`),
  KEY `IDX_audit_order_detail_order_step` (`orderId`, `stepType`),
  KEY `IDX_audit_order_detail_skill` (`skillName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='审核步骤明细表';

CREATE TABLE IF NOT EXISTS `audit_config` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `createTime` varchar(255) NOT NULL COMMENT '创建时间',
  `updateTime` varchar(255) NOT NULL COMMENT '更新时间',
  `tenantId` int NULL COMMENT '租户ID',
  `name` varchar(100) NOT NULL COMMENT '规则名称',
  `orderType` varchar(20) NULL COMMENT '订单类型 insure=投保 claim=理赔, 为空表示匹配所有',
  `oemId` int NULL COMMENT '主机厂ID, 为空表示匹配所有',
  `oemName` varchar(100) NULL COMMENT '主机厂名称(展示用)',
  `productKeyword` varchar(200) NULL COMMENT '产品名称关键词, 为空表示匹配所有, 支持模糊匹配',
  `productNo` varchar(100) NULL COMMENT '产品编号, 为空表示匹配所有',
  `productName` varchar(200) NULL COMMENT '产品名称(展示用)',
  `carType` varchar(10) NULL COMMENT '车辆类型 0=新车 1=在用车, 为空表示匹配所有',
  `carPower` varchar(20) NULL COMMENT '动力类型 1=燃油 2=新能源, 长安专用, 为空表示匹配所有',
  `agentId` int NOT NULL COMMENT '关联的Agent ID',
  `agentIds` json NULL COMMENT '关联的Agent ID列表',
  `agentName` varchar(200) NULL COMMENT 'Agent名称(展示用)',
  `priority` int NOT NULL DEFAULT 0 COMMENT '优先级, 数字越大越优先',
  `status` int NOT NULL DEFAULT 1 COMMENT '状态 0=禁用 1=启用',
  `remark` text NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `IDX_audit_config_createTime` (`createTime`),
  KEY `IDX_audit_config_updateTime` (`updateTime`),
  KEY `IDX_audit_config_tenantId` (`tenantId`),
  KEY `IDX_audit_config_agentId` (`agentId`),
  KEY `IDX_audit_config_priority` (`priority`),
  KEY `IDX_audit_config_status` (`status`),
  KEY `IDX_audit_config_match` (`status`, `orderType`, `oemId`, `carType`, `carPower`, `priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='审核配置规则表';

-- 已存在 audit_config 表的升级 SQL：
-- ALTER TABLE `audit_config` ADD COLUMN `carType` varchar(10) NULL COMMENT '车辆类型 0=新车 1=在用车, 为空表示匹配所有' AFTER `productKeyword`;
-- ALTER TABLE `audit_config` ADD COLUMN `productNo` varchar(100) NULL COMMENT '产品编号, 为空表示匹配所有' AFTER `productKeyword`;
-- ALTER TABLE `audit_config` ADD COLUMN `productName` varchar(200) NULL COMMENT '产品名称(展示用)' AFTER `productNo`;
-- ALTER TABLE `audit_config` ADD COLUMN `carPower` varchar(20) NULL COMMENT '动力类型 1=燃油 2=新能源, 长安专用, 为空表示匹配所有' AFTER `carType`;
-- ALTER TABLE `audit_config` ADD COLUMN `agentIds` json NULL COMMENT '关联的Agent ID列表' AFTER `agentId`;
-- ALTER TABLE `audit_config` DROP INDEX `IDX_audit_config_match`;
-- ALTER TABLE `audit_config` ADD INDEX `IDX_audit_config_match` (`status`, `orderType`, `oemId`, `carType`, `carPower`, `priority`);

-- 菜单权限：审核管理 / 审核订单 / 审核统计 / 审核配置 / 审核详情(隐藏)
-- 使用 router 做幂等判断；若已有同 router 菜单，本段不会重复插入。
SET @now = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT NULL, '审核管理', NULL, NULL, 0, 'icon-dict', 95, NULL, 1, 1, @now, @now, NULL
WHERE NOT EXISTS (
  SELECT 1 FROM `base_sys_menu` WHERE `name` = '审核管理' AND `parentId` IS NULL
);

SET @audit_parent_id = (
  SELECT `id` FROM `base_sys_menu`
  WHERE `name` = '审核管理' AND `parentId` IS NULL
  ORDER BY `id` DESC LIMIT 1
);

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_parent_id, '审核订单', '/audit/orders', NULL, 1, 'icon-menu', 0, 'modules/audit/views/order-list.vue', 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `router` = '/audit/orders');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_parent_id, '审核统计', '/audit/dashboard', NULL, 1, 'icon-count', 1, 'modules/audit/views/dashboard.vue', 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `router` = '/audit/dashboard');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_parent_id, '审核配置', '/audit/config', NULL, 1, 'icon-params', 2, 'modules/audit/views/config.vue', 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `router` = '/audit/config');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_parent_id, '审核详情', '/audit/detail', NULL, 1, 'icon-menu', 3, 'modules/audit/views/order-detail.vue', 0, 0, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `router` = '/audit/detail');

SET @audit_order_menu_id = (SELECT `id` FROM `base_sys_menu` WHERE `router` = '/audit/orders' ORDER BY `id` DESC LIMIT 1);
SET @audit_dashboard_menu_id = (SELECT `id` FROM `base_sys_menu` WHERE `router` = '/audit/dashboard' ORDER BY `id` DESC LIMIT 1);
SET @audit_config_menu_id = (SELECT `id` FROM `base_sys_menu` WHERE `router` = '/audit/config' ORDER BY `id` DESC LIMIT 1);

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_order_menu_id, '查询', NULL, 'audit:audit:page,audit:audit:list,audit:audit:info,audit:audit:detail', 2, NULL, 0, NULL, 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `parentId` = @audit_order_menu_id AND `name` = '查询');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_order_menu_id, '操作', NULL, 'audit:audit:retry,audit:audit:manual,audit:audit:remove,audit:audit:stats', 2, NULL, 1, NULL, 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `parentId` = @audit_order_menu_id AND `name` = '操作');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_dashboard_menu_id, '查询', NULL, 'audit:audit:statsOverview,audit:audit:statsByProduct,audit:audit:statsByDealer,audit:audit:statsByOem,audit:audit:statsByInsurance,audit:audit:filterOptions,audit:audit:searchDealers,audit:audit:searchProducts,audit:audit:exportData', 2, NULL, 0, NULL, 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `parentId` = @audit_dashboard_menu_id AND `name` = '查询');

INSERT INTO `base_sys_menu`
(`parentId`, `name`, `router`, `perms`, `type`, `icon`, `orderNum`, `viewPath`, `keepAlive`, `isShow`, `createTime`, `updateTime`, `tenantId`)
SELECT @audit_config_menu_id, '管理', NULL, 'audit:config:add,audit:config:delete,audit:config:update,audit:config:info,audit:config:list,audit:config:page,audit:config:agents,audit:config:testMatch', 2, NULL, 0, NULL, 1, 1, @now, @now, NULL
WHERE NOT EXISTS (SELECT 1 FROM `base_sys_menu` WHERE `parentId` = @audit_config_menu_id AND `name` = '管理');