urbanLifeline/urbanLifelineServ/.bin/database/postgres/sql/createTableWorkcase.sql

-- =============================
-- 智能客服系统业务模块（工单系统）
-- 支持：微信小程序客户咨询、智能问答、工单管理、CRM集成
-- =============================
CREATE SCHEMA IF NOT EXISTS workcase;

-- 客户信息表
DROP TABLE IF EXISTS workcase.tb_customer CASCADE;
CREATE TABLE workcase.tb_customer (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  customer_id        VARCHAR(50)   NOT NULL,               -- 客户ID
  customer_no        VARCHAR(100),                         -- 客户编号
  customer_name      VARCHAR(255),                         -- 客户姓名
  customer_type      VARCHAR(30)   DEFAULT 'individual',   -- 客户类型：individual-个人/enterprise-企业
  company_name       VARCHAR(255),                         -- 公司名称
  phone              VARCHAR(20),                          -- 电话
  email              VARCHAR(100),                         -- 邮箱
  wechat_openid      VARCHAR(100),                         -- 微信OpenID
  wechat_unionid     VARCHAR(100),                         -- 微信UnionID
  avatar             VARCHAR(500),                         -- 头像URL
  gender             INTEGER       DEFAULT 0,              -- 性别：0-未知/1-男/2-女
  address            VARCHAR(500),                         -- 地址
  customer_level     VARCHAR(20)   DEFAULT 'normal',       -- 客户等级：vip/important/normal/potential
  customer_source    VARCHAR(50),                          -- 客户来源：wechat-微信/web-网站/phone-电话/referral-推荐
  tags               TEXT[],                               -- 客户标签数组
  notes              TEXT,                                 -- 备注
  crm_customer_id    VARCHAR(50),                          -- CRM系统客户ID（外部系统）
  last_contact_time  TIMESTAMPTZ,                          -- 最后联系时间
  total_consultations INTEGER     DEFAULT 0,              -- 咨询总次数
  total_orders       INTEGER       DEFAULT 0,              -- 订单总数
  total_amount       DECIMAL(18,2) DEFAULT 0,              -- 总消费金额
  satisfaction_score DECIMAL(3,2),                         -- 满意度评分（1-5）
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  status             VARCHAR(20)   DEFAULT 'active',       -- 状态：active-活跃/inactive-非活跃/blacklist-黑名单
  creator            VARCHAR(50)   DEFAULT NULL,           -- 创建者
  updater            VARCHAR(50)   DEFAULT NULL,           -- 更新者
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  update_time        TIMESTAMPTZ   DEFAULT NULL,           -- 更新时间
  delete_time        TIMESTAMPTZ   DEFAULT NULL,           -- 删除时间
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (customer_id),
  UNIQUE (optsn),
  UNIQUE (wechat_openid),
  UNIQUE (phone),
  UNIQUE (email)
);

CREATE INDEX idx_customer_type ON workcase.tb_customer(customer_type) WHERE deleted = false;
CREATE INDEX idx_customer_level ON workcase.tb_customer(customer_level) WHERE deleted = false;
CREATE INDEX idx_customer_wechat ON workcase.tb_customer(wechat_openid) WHERE deleted = false;

COMMENT ON TABLE workcase.tb_customer IS '客户信息表';
COMMENT ON COLUMN workcase.tb_customer.customer_level IS '客户等级：vip/important/normal/potential';

-- 会话表
DROP TABLE IF EXISTS workcase.tb_conversation CASCADE;
CREATE TABLE workcase.tb_conversation (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  conversation_id    VARCHAR(50)   NOT NULL,               -- 会话ID
  customer_id        VARCHAR(50)   NOT NULL,               -- 客户ID
  conversation_type  VARCHAR(30)   DEFAULT 'ai',           -- 会话类型：ai-AI客服/human-人工客服/transfer-转接
  channel            VARCHAR(20)   DEFAULT 'wechat',       -- 渠道：wechat-微信/web-网页/app-应用/phone-电话
  agent_id           VARCHAR(50),                          -- 智能体ID或客服人员ID
  agent_type         VARCHAR(20)   DEFAULT 'ai',           -- 座席类型：ai-AI/human-人工
  session_start_time TIMESTAMPTZ   DEFAULT now(),          -- 会话开始时间
  session_end_time   TIMESTAMPTZ,                          -- 会话结束时间
  duration_seconds   INTEGER,                              -- 会话时长（秒）
  message_count      INTEGER       DEFAULT 0,              -- 消息数量
  conversation_status VARCHAR(20)  DEFAULT 'active',       -- 会话状态：active-进行中/closed-已结束/transferred-已转接/timeout-超时
  satisfaction_rating INTEGER,                             -- 满意度评分（1-5星）
  satisfaction_feedback TEXT,                              -- 满意度反馈
  summary            TEXT,                                 -- 会话摘要（AI生成）
  tags               TEXT[],                               -- 会话标签
  metadata           JSONB,                                -- 会话元数据
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  creator            VARCHAR(50)   DEFAULT NULL,           -- 创建者
  updater            VARCHAR(50)   DEFAULT NULL,           -- 更新者
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  update_time        TIMESTAMPTZ   DEFAULT NULL,           -- 更新时间
  delete_time        TIMESTAMPTZ   DEFAULT NULL,           -- 删除时间
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (conversation_id),
  UNIQUE (optsn),
  FOREIGN KEY (customer_id) REFERENCES workcase.tb_customer(customer_id)
);

CREATE INDEX idx_conv_customer ON workcase.tb_conversation(customer_id, session_start_time DESC) WHERE deleted = false;
CREATE INDEX idx_conv_status ON workcase.tb_conversation(conversation_status) WHERE deleted = false;
CREATE INDEX idx_conv_agent ON workcase.tb_conversation(agent_id) WHERE deleted = false;

COMMENT ON TABLE workcase.tb_conversation IS '会话表';
COMMENT ON COLUMN workcase.tb_conversation.conversation_type IS '会话类型：ai/human/transfer';

-- 会话消息表
DROP TABLE IF EXISTS workcase.tb_conversation_message CASCADE;
CREATE TABLE workcase.tb_conversation_message (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  message_id         VARCHAR(50)   NOT NULL,               -- 消息ID
  conversation_id    VARCHAR(50)   NOT NULL,               -- 所属会话ID
  sender_type        VARCHAR(20)   NOT NULL,               -- 发送者类型：customer-客户/agent-座席/system-系统
  sender_id          VARCHAR(50),                          -- 发送者ID
  message_type       VARCHAR(30)   NOT NULL DEFAULT 'text',-- 消息类型：text-文本/image-图片/voice-语音/video-视频/file-文件/card-卡片
  content            TEXT,                                 -- 消息内容
  content_url        VARCHAR(500),                         -- 内容URL（图片、文件等）
  is_ai_generated    BOOLEAN       DEFAULT false,          -- 是否AI生成
  ai_model           VARCHAR(100),                         -- 使用的AI模型
  kb_references      VARCHAR(50)[],                        -- 引用的知识库文档ID数组
  confidence_score   DECIMAL(5,4),                         -- AI回答置信度
  sentiment          VARCHAR(20),                          -- 情感分析：positive-正面/neutral-中性/negative-负面
  intent             VARCHAR(100),                         -- 意图识别结果
  is_sensitive       BOOLEAN       DEFAULT false,          -- 是否敏感信息
  read_status        BOOLEAN       DEFAULT false,          -- 已读状态
  read_time          TIMESTAMPTZ,                          -- 阅读时间
  metadata           JSONB,                                -- 消息元数据
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间（发送时间）
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (message_id),
  UNIQUE (optsn),
  FOREIGN KEY (conversation_id) REFERENCES workcase.tb_conversation(conversation_id)
);

CREATE INDEX idx_msg_conversation ON workcase.tb_conversation_message(conversation_id, create_time) WHERE deleted = false;
CREATE INDEX idx_msg_sender ON workcase.tb_conversation_message(sender_id) WHERE deleted = false;

COMMENT ON TABLE workcase.tb_conversation_message IS '会话消息表';
COMMENT ON COLUMN workcase.tb_conversation_message.sentiment IS '情感分析：positive/neutral/negative';

-- 工单表
DROP TABLE IF EXISTS workcase.tb_ticket CASCADE;
CREATE TABLE workcase.tb_ticket (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  ticket_id          VARCHAR(50)   NOT NULL,               -- 工单ID
  ticket_no          VARCHAR(100)  NOT NULL,               -- 工单编号
  customer_id        VARCHAR(50)   NOT NULL,               -- 客户ID
  conversation_id    VARCHAR(50),                          -- 关联会话ID
  ticket_type        VARCHAR(50)   NOT NULL,               -- 工单类型：consultation-咨询/complaint-投诉/suggestion-建议/repair-维修/installation-安装/other-其他
  ticket_category    VARCHAR(100),                         -- 工单分类（具体业务分类）
  priority           VARCHAR(20)   DEFAULT 'normal',       -- 优先级：urgent-紧急/high-高/normal-普通/low-低
  title              VARCHAR(500)  NOT NULL,               -- 工单标题
  description        TEXT          NOT NULL,               -- 问题描述
  attachments        VARCHAR(50)[],                        -- 附件ID数组
  ticket_source      VARCHAR(30)   DEFAULT 'ai',           -- 工单来源：ai-AI生成/manual-人工创建/system-系统自动
  assigned_to        VARCHAR(50),                          -- 分配给（处理人）
  assigned_dept      VARCHAR(50),                          -- 分配部门
  ticket_status      VARCHAR(30)   DEFAULT 'pending',      -- 工单状态：pending-待处理/processing-处理中/resolved-已解决/closed-已关闭/cancelled-已取消
  resolution         TEXT,                                 -- 解决方案
  resolution_time    TIMESTAMPTZ,                          -- 解决时间
  close_time         TIMESTAMPTZ,                          -- 关闭时间
  response_time      TIMESTAMPTZ,                          -- 首次响应时间
  sla_deadline       TIMESTAMPTZ,                          -- SLA截止时间
  is_overdue         BOOLEAN       DEFAULT false,          -- 是否逾期
  customer_rating    INTEGER,                              -- 客户评分（1-5星）
  customer_feedback  TEXT,                                 -- 客户反馈
  crm_ticket_id      VARCHAR(50),                          -- CRM系统工单ID（外部系统）
  sync_status        VARCHAR(20)   DEFAULT 'pending',      -- 同步状态：pending-待同步/synced-已同步/failed-失败
  tags               TEXT[],                               -- 工单标签
  metadata           JSONB,                                -- 工单元数据
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  creator            VARCHAR(50)   DEFAULT NULL,           -- 创建者
  updater            VARCHAR(50)   DEFAULT NULL,           -- 更新者
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  update_time        TIMESTAMPTZ   DEFAULT NULL,           -- 更新时间
  delete_time        TIMESTAMPTZ   DEFAULT NULL,           -- 删除时间
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (ticket_id),
  UNIQUE (optsn),
  UNIQUE (ticket_no),
  FOREIGN KEY (customer_id) REFERENCES workcase.tb_customer(customer_id)
);

CREATE INDEX idx_ticket_customer ON workcase.tb_ticket(customer_id) WHERE deleted = false;
CREATE INDEX idx_ticket_status ON workcase.tb_ticket(ticket_status) WHERE deleted = false;
CREATE INDEX idx_ticket_assigned ON workcase.tb_ticket(assigned_to) WHERE deleted = false;
CREATE INDEX idx_ticket_priority ON workcase.tb_ticket(priority) WHERE deleted = false;
CREATE INDEX idx_ticket_sla ON workcase.tb_ticket(sla_deadline) WHERE deleted = false AND is_overdue = false;

COMMENT ON TABLE workcase.tb_ticket IS '工单表';
COMMENT ON COLUMN workcase.tb_ticket.ticket_type IS '工单类型：consultation/complaint/suggestion/repair/installation/other';

-- 工单处理记录表
DROP TABLE IF EXISTS workcase.tb_ticket_log CASCADE;
CREATE TABLE workcase.tb_ticket_log (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  log_id             VARCHAR(50)   NOT NULL,               -- 日志ID
  ticket_id          VARCHAR(50)   NOT NULL,               -- 工单ID
  action_type        VARCHAR(50)   NOT NULL,               -- 操作类型：create-创建/assign-分配/update-更新/comment-评论/resolve-解决/close-关闭/reopen-重开
  action_content     TEXT,                                 -- 操作内容
  old_value          TEXT,                                 -- 旧值
  new_value          TEXT,                                 -- 新值
  operator_id        VARCHAR(50),                          -- 操作人ID
  operator_name      VARCHAR(100),                         -- 操作人姓名
  attachments        VARCHAR(50)[],                        -- 附件ID数组
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  PRIMARY KEY (log_id),
  UNIQUE (optsn),
  FOREIGN KEY (ticket_id) REFERENCES workcase.tb_ticket(ticket_id)
);

CREATE INDEX idx_ticket_log_ticket ON workcase.tb_ticket_log(ticket_id, create_time DESC);

COMMENT ON TABLE workcase.tb_ticket_log IS '工单处理记录表';

-- FAQ表（常见问题）
DROP TABLE IF EXISTS workcase.tb_faq CASCADE;
CREATE TABLE workcase.tb_faq (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  faq_id             VARCHAR(50)   NOT NULL,               -- FAQ ID
  knowledge_id              VARCHAR(50),                          -- 关联知识库ID
  category           VARCHAR(100)  NOT NULL,               -- 分类
  question           TEXT          NOT NULL,               -- 问题
  answer             TEXT          NOT NULL,               -- 答案
  similar_questions  TEXT[],                               -- 相似问题数组
  keywords           TEXT[],                               -- 关键词数组
  hit_count          INTEGER       DEFAULT 0,              -- 命中次数
  helpful_count      INTEGER       DEFAULT 0,              -- 有帮助次数
  unhelpful_count    INTEGER       DEFAULT 0,              -- 无帮助次数
  is_published       BOOLEAN       DEFAULT false,          -- 是否发布
  priority           INTEGER       DEFAULT 0,              -- 优先级
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  creator            VARCHAR(50)   DEFAULT NULL,           -- 创建者
  updater            VARCHAR(50)   DEFAULT NULL,           -- 更新者
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  update_time        TIMESTAMPTZ   DEFAULT NULL,           -- 更新时间
  delete_time        TIMESTAMPTZ   DEFAULT NULL,           -- 删除时间
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (faq_id),
  UNIQUE (optsn)
);

CREATE INDEX idx_faq_category ON workcase.tb_faq(category) WHERE deleted = false;
CREATE INDEX idx_faq_published ON workcase.tb_faq(is_published) WHERE deleted = false AND is_published = true;

COMMENT ON TABLE workcase.tb_faq IS 'FAQ常见问题表';

-- 客服评价表
DROP TABLE IF EXISTS workcase.tb_service_evaluation CASCADE;
CREATE TABLE workcase.tb_service_evaluation (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  evaluation_id      VARCHAR(50)   NOT NULL,               -- 评价ID
  customer_id        VARCHAR(50)   NOT NULL,               -- 客户ID
  conversation_id    VARCHAR(50),                          -- 会话ID
  ticket_id          VARCHAR(50),                          -- 工单ID
  evaluation_type    VARCHAR(30)   NOT NULL,               -- 评价类型：conversation-会话/ticket-工单/overall-整体服务
  rating             INTEGER       NOT NULL,               -- 评分（1-5星）
  dimensions         JSONB,                                -- 分维度评分（JSON格式：响应速度、专业性、态度等）
  feedback           TEXT,                                 -- 评价反馈
  tags               TEXT[],                               -- 评价标签
  is_anonymous       BOOLEAN       DEFAULT false,          -- 是否匿名
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (evaluation_id),
  UNIQUE (optsn),
  FOREIGN KEY (customer_id) REFERENCES workcase.tb_customer(customer_id)
);

CREATE INDEX idx_eval_customer ON workcase.tb_service_evaluation(customer_id) WHERE deleted = false;
CREATE INDEX idx_eval_rating ON workcase.tb_service_evaluation(rating) WHERE deleted = false;

COMMENT ON TABLE workcase.tb_service_evaluation IS '客服评价表';

-- CRM集成配置表
DROP TABLE IF EXISTS workcase.tb_crm_config CASCADE;
CREATE TABLE workcase.tb_crm_config (
  optsn              VARCHAR(50)   NOT NULL,               -- 流水号
  config_id          VARCHAR(50)   NOT NULL,               -- 配置ID
  crm_system         VARCHAR(50)   NOT NULL,               -- CRM系统名称
  api_endpoint       VARCHAR(500)  NOT NULL,               -- API端点
  api_key            VARCHAR(500),                         -- API密钥（加密存储）
  auth_type          VARCHAR(30)   DEFAULT 'api_key',      -- 认证类型：api_key/oauth2/basic_auth
  sync_interval      INTEGER       DEFAULT 3600,           -- 同步间隔（秒）
  sync_direction     VARCHAR(30)   DEFAULT 'bidirectional',-- 同步方向：to_crm-单向到CRM/from_crm-单向从CRM/bidirectional-双向
  field_mapping      JSONB,                                -- 字段映射配置
  sync_enabled       BOOLEAN       DEFAULT false,          -- 是否启用同步
  last_sync_time     TIMESTAMPTZ,                          -- 最后同步时间
  dept_path          VARCHAR(255)  DEFAULT NULL,           -- 部门全路径
  creator            VARCHAR(50)   DEFAULT NULL,           -- 创建者
  updater            VARCHAR(50)   DEFAULT NULL,           -- 更新者
  create_time        TIMESTAMPTZ   NOT NULL DEFAULT now(), -- 创建时间
  update_time        TIMESTAMPTZ   DEFAULT NULL,           -- 更新时间
  delete_time        TIMESTAMPTZ   DEFAULT NULL,           -- 删除时间
  deleted            BOOLEAN       NOT NULL DEFAULT false, -- 是否删除
  PRIMARY KEY (config_id),
  UNIQUE (optsn)
);

COMMENT ON TABLE workcase.tb_crm_config IS 'CRM集成配置表';