-- ============================================================
-- 售后工单模块 DDL
-- ============================================================

-- 1. 售后工单主表
DROP TABLE IF EXISTS `after_sale_ticket`;
CREATE TABLE `after_sale_ticket` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '工单编号',
  `no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '工单单号',
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '工单主题/标题',
  `customer_id` bigint NOT NULL COMMENT '客户编号，关联 crm_customer.id',
  `contact_id` bigint DEFAULT NULL COMMENT '联系人编号，关联 crm_contact.id',
  `sale_order_id` bigint DEFAULT NULL COMMENT '销售订单编号，关联 erp_sale_order.id',
  `sale_order_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '销售订单号(冗余)',
  `contract_id` bigint DEFAULT NULL COMMENT '合同编号，关联 crm_contract.id',
  `issue_type` tinyint NOT NULL DEFAULT 0 COMMENT '问题类型: 0-未判定 1-一般问题 2-维修问题 3-退货问题',
  `issue_category` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '问题分类',
  `issue_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '问题描述',
  `severity_level` tinyint DEFAULT 0 COMMENT '严重程度: 0-轻微 1-一般 2-严重 3-致命',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态: 0-草稿 10-处理中 30-待退货 40-待退款/换货 50-已完结 60-已关闭 99-已取消',
  `owner_user_id` bigint NOT NULL COMMENT '负责人用户编号',
  `handler_user_id` bigint DEFAULT NULL COMMENT '处理人用户编号',
  `process_instance_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'BPM工作流实例编号',
  `return_id` bigint DEFAULT NULL COMMENT '关联退货申请编号',
  `repair_id` bigint DEFAULT NULL COMMENT '关联维修记录编号',
  `resolved_time` datetime DEFAULT NULL COMMENT '解决时间',
  `closed_time` datetime DEFAULT NULL COMMENT '关闭时间',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_no` (`no`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_sale_order_id` (`sale_order_id`),
  KEY `idx_owner_user_id` (`owner_user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后工单主表';

-- 2. 售后工单明细行表
DROP TABLE IF EXISTS `after_sale_ticket_item`;
CREATE TABLE `after_sale_ticket_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `ticket_id` bigint NOT NULL COMMENT '工单编号，关联 after_sale_ticket.id',
  `sale_order_item_id` bigint DEFAULT NULL COMMENT '销售订单行编号',
  `item_id` bigint NOT NULL COMMENT 'MDM物料编号',
  `item_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料编码(冗余)',
  `item_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料名称(冗余)',
  `batch_id` bigint DEFAULT NULL COMMENT '生产批次编号',
  `batch_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '生产批次号',
  `sale_quantity` decimal(10,3) NOT NULL DEFAULT 0.000 COMMENT '销售数量',
  `return_quantity` decimal(10,3) DEFAULT 0.000 COMMENT '退货数量',
  `need_repair` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否需要维修',
  `need_return` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否需要退货',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_ticket_id` (`ticket_id`),
  KEY `idx_item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后工单明细行表';

-- 3. 退货申请表
DROP TABLE IF EXISTS `after_sale_return`;
CREATE TABLE `after_sale_return` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '退货申请编号',
  `no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '退货单号',
  `ticket_id` bigint NOT NULL COMMENT '关联工单编号，关联 after_sale_ticket.id',
  `customer_id` bigint NOT NULL COMMENT '客户编号',
  `sale_order_id` bigint DEFAULT NULL COMMENT '关联销售订单编号',
  `return_type` tinyint NOT NULL COMMENT '退货类型: 1-退款退货 2-换货 3-仅退款',
  `return_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '退货原因',
  `defect_category` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '缺陷分类',
  `total_return_price` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '应退总金额',
  `actual_refund_price` decimal(12,2) DEFAULT 0.00 COMMENT '实退金额',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态: 0-草稿 10-销售审核中 20-审核通过已同步MES 30-检验中 40-检验完成 50-已入库 60-退款/换货完成 70-已关闭 99-已取消',
  `audit_user_id` bigint DEFAULT NULL COMMENT '审核人',
  `audit_time` datetime DEFAULT NULL COMMENT '审核时间',
  `mes_return_id` bigint DEFAULT NULL COMMENT 'MES退货单编号，关联 mes_wm_return_sales.id',
  `mes_return_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'MES退货单号(冗余)',
  `quality_result` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '质检结论: 合格/特采/退货/报废',
  `quality_report_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '质检报告附件URL',
  `return_inbound_time` datetime DEFAULT NULL COMMENT '退货入库完成时间',
  `refund_time` datetime DEFAULT NULL COMMENT '退款/换货完成时间',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_no` (`no`),
  KEY `idx_ticket_id` (`ticket_id`),
  KEY `idx_mes_return_id` (`mes_return_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后退货申请表';

-- 4. 退货明细行表
DROP TABLE IF EXISTS `after_sale_return_item`;
CREATE TABLE `after_sale_return_item` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `return_id` bigint NOT NULL COMMENT '退货申请编号，关联 after_sale_return.id',
  `ticket_item_id` bigint NOT NULL COMMENT '工单明细行编号，关联 after_sale_ticket_item.id',
  `item_id` bigint NOT NULL COMMENT 'MDM物料编号',
  `item_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料编码(冗余)',
  `item_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '物料名称(冗余)',
  `batch_id` bigint DEFAULT NULL COMMENT '批次编号',
  `batch_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '批次号',
  `return_quantity` decimal(10,3) NOT NULL COMMENT '退货数量',
  `unit_price` decimal(12,2) NOT NULL COMMENT '销售单价',
  `return_price` decimal(12,2) NOT NULL COMMENT '退货金额',
  `mes_return_line_id` bigint DEFAULT NULL COMMENT 'MES退货单行编号',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_return_id` (`return_id`),
  KEY `idx_ticket_item_id` (`ticket_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后退货明细行表';

-- 5. 维修记录表
DROP TABLE IF EXISTS `after_sale_repair`;
CREATE TABLE `after_sale_repair` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '维修记录编号',
  `ticket_id` bigint NOT NULL COMMENT '关联工单编号，关联 after_sale_ticket.id',
  `repair_type` tinyint NOT NULL COMMENT '维修类型: 1-现场维修 2-返厂维修 3-远程指导',
  `fault_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '故障描述',
  `repair_action` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '维修措施',
  `repair_result` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '维修结果: 已修复/无法修复/部分修复',
  `repair_cost` decimal(10,2) DEFAULT 0.00 COMMENT '维修费用',
  `repair_user_id` bigint DEFAULT NULL COMMENT '维修人',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态: 0-待处理 10-维修中 20-已完成 30-已关闭',
  `start_time` datetime DEFAULT NULL COMMENT '开始维修时间',
  `finish_time` datetime DEFAULT NULL COMMENT '完成维修时间',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_ticket_id` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后维修记录表';

-- 6. 售后数据权限表
DROP TABLE IF EXISTS `after_sale_permission`;
CREATE TABLE `after_sale_permission` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `biz_type` tinyint NOT NULL COMMENT '数据类型: 1-售后工单 2-退货申请 3-维修记录',
  `biz_id` bigint NOT NULL COMMENT '数据编号',
  `user_id` bigint NOT NULL COMMENT '用户编号',
  `level` tinyint NOT NULL COMMENT '权限级别: 1-负责人 2-只读 3-读写',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_biz` (`biz_type`, `biz_id`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='售后数据权限表';