ylrz_saas_his_scrm/sql/lobster_tables_ddl.sql

-- ═══════════════════════════════════════════════════════
--  龙虾引擎统一建表脚本（从 Java 代码散落 CREATE TABLE 迁移至此）
--  用途: 新租户初始化 / DBA 审核 / 22 库批量执行
--  日期: 2026-06-04
-- ═══════════════════════════════════════════════════════

-- 1. 合规规则表（原 ComplianceServiceImpl.ensureTable）
CREATE TABLE IF NOT EXISTS lobster_compliance_rule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT DEFAULT NULL,
    rule_name VARCHAR(200) NOT NULL,
    rule_type VARCHAR(50) DEFAULT 'keyword',
    pattern TEXT,
    description VARCHAR(500) DEFAULT NULL,
    action VARCHAR(50) DEFAULT 'warn',
    severity INT DEFAULT 1,
    enabled INT DEFAULT 1,
    del_flag INT DEFAULT 0,
    create_by VARCHAR(64) DEFAULT NULL,
    create_time DATETIME DEFAULT NOW(),
    update_by VARCHAR(64) DEFAULT NULL,
    update_time DATETIME DEFAULT NOW(),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 合规审计轨迹（原 ComplianceService.recordComplianceAudit / LobsterComplianceAudit）
CREATE TABLE IF NOT EXISTS lobster_compliance_audit (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    rule_name VARCHAR(200) DEFAULT NULL,
    severity INT DEFAULT 1,
    matched_keyword VARCHAR(200) DEFAULT NULL,
    content_snippet VARCHAR(500) DEFAULT NULL,
    create_time DATETIME DEFAULT NOW(),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 工作流变量（原 VariableStoreImpl / LobsterWorkflowVariableMapper）
CREATE TABLE IF NOT EXISTS lobster_workflow_variable (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    instance_id BIGINT NOT NULL,
    company_id BIGINT DEFAULT NULL,
    var_key VARCHAR(200) NOT NULL,
    var_value TEXT,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_inst_key (instance_id, var_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 待审知识库（原 PendingAuditKnowledgeServiceImpl）
CREATE TABLE IF NOT EXISTS lobster_pending_knowledge (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    external_user_id VARCHAR(128) DEFAULT NULL,
    knowledge_type VARCHAR(50) DEFAULT 'auto',
    content TEXT,
    context_snapshot TEXT COMMENT '评分维度/上下文快照JSON',
    source_node_code VARCHAR(100) DEFAULT NULL,
    status VARCHAR(30) DEFAULT 'pending' COMMENT 'pending/APPROVED/REJECTED',
    auditor_id BIGINT DEFAULT NULL,
    audit_comment VARCHAR(500) DEFAULT NULL,
    audit_time DATETIME DEFAULT NULL,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    INDEX idx_company_status (company_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 敏感词表（原 SensitiveWordServiceImpl / LobsterSensitiveWordMapper）
CREATE TABLE IF NOT EXISTS lobster_sensitive_word (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    word VARCHAR(200) NOT NULL,
    category VARCHAR(50) DEFAULT '通用',
    level VARCHAR(20) DEFAULT 'warn',
    replacement VARCHAR(200) DEFAULT '***',
    enabled INT DEFAULT 1,
    create_time DATETIME DEFAULT NOW(),
    create_by VARCHAR(64) DEFAULT 'SYSTEM',
    INDEX idx_company (company_id),
    UNIQUE KEY uk_word (company_id, word)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. 用户画像（原 ContextAssemblerImpl / ProfileEnrichmentService）
CREATE TABLE IF NOT EXISTS lobster_user_profile (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    external_user_id VARCHAR(128) NOT NULL,
    nickname VARCHAR(128) DEFAULT NULL,
    persona VARCHAR(500) DEFAULT NULL,
    current_state VARCHAR(100) DEFAULT NULL,
    lifecycle_stage VARCHAR(30) DEFAULT 'NEW' COMMENT 'NEW/ACTIVE/DORMANT/SLEEP/CHURN',
    value_score INT DEFAULT 10,
    total_purchase DECIMAL(12,2) DEFAULT 0,
    interaction_count INT DEFAULT 0,
    last_active_time DATETIME DEFAULT NULL,
    internal_tags VARCHAR(1000) DEFAULT NULL,
    variable_snapshot VARCHAR(2000) DEFAULT NULL,
    deleted INT DEFAULT 0,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_user (company_id, external_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. 多轮对话状态（原 MultiTurnDialogueManagerImpl / LobsterMultiTurnDialogueMapper）
CREATE TABLE IF NOT EXISTS lobster_multi_turn_dialogue (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT DEFAULT NULL,
    instance_id BIGINT NOT NULL,
    node_code VARCHAR(100) NOT NULL,
    state_json TEXT,
    turn_index INT DEFAULT 0,
    direction INT COMMENT '1=客户 2=AI',
    content TEXT,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_instance_node (instance_id, node_code),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. 客户沟通习惯（原 LobsterEvolutionEngineImpl / CustomerHabitMapper）
CREATE TABLE IF NOT EXISTS customer_habit (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    external_user_id VARCHAR(128) NOT NULL,
    habit_key VARCHAR(64) NOT NULL,
    habit_value VARCHAR(512),
    confidence DOUBLE DEFAULT 0.5,
    source VARCHAR(32) DEFAULT 'AI',
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_habit (company_id, external_user_id, habit_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. 客户事实记忆（原 SummaryGeneratorImpl / CustomerFactMapper）
CREATE TABLE IF NOT EXISTS customer_fact (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    external_user_id VARCHAR(128) NOT NULL,
    instance_id BIGINT DEFAULT NULL,
    fact_key VARCHAR(200) NOT NULL,
    fact_value VARCHAR(2000),
    fact_type VARCHAR(30) DEFAULT 'EXTRACTED' COMMENT 'EXTRACTED/INFERRED/MANUAL',
    confidence DOUBLE DEFAULT 1.0 COMMENT '1.0=新插入, 衰减至<0.3将被清理',
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_fact (company_id, external_user_id, instance_id, fact_key),
    INDEX idx_company_user (company_id, external_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. 转人工事件记录（原 LobsterEvolutionEngineImpl / LobsterHandoffEventMapper）
CREATE TABLE IF NOT EXISTS lobster_handoff_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    instance_id BIGINT DEFAULT NULL,
    external_user_id VARCHAR(128) DEFAULT NULL,
    trigger_type VARCHAR(50) COMMENT 'sensitive_word/sensitive_word_high_risk/semantic_takeover',
    trigger_detail VARCHAR(500) DEFAULT NULL,
    create_time DATETIME DEFAULT NOW(),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. 知识使用反馈日志（原 ContextAssemblerImpl / LobsterKnowledgeUsageLogMapper）
CREATE TABLE IF NOT EXISTS lobster_knowledge_usage_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    instance_id BIGINT DEFAULT NULL,
    knowledge_title VARCHAR(200) DEFAULT NULL,
    knowledge_text VARCHAR(500) DEFAULT NULL,
    retrieval_method VARCHAR(30) COMMENT 'vector/keyword',
    source VARCHAR(50) DEFAULT 'context_assembler',
    create_time DATETIME DEFAULT NOW(),
    INDEX idx_company (company_id),
    INDEX idx_company_title (company_id, knowledge_title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. 智能 API 配置表（原 DynamicNodeExecutorImpl / LobsterSmartApiMapper）
CREATE TABLE IF NOT EXISTS lobster_smart_api (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT DEFAULT NULL,
    api_code VARCHAR(100) NOT NULL COMMENT 'API唯一标识',
    api_name VARCHAR(200) DEFAULT NULL,
    api_url VARCHAR(500) NOT NULL,
    api_method VARCHAR(10) DEFAULT 'POST',
    headers_json VARCHAR(1000) DEFAULT '{}',
    body_template TEXT,
    description VARCHAR(500) DEFAULT NULL,
    enabled INT DEFAULT 1,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_code (company_id, api_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 13. 对话状态追踪（原 LobsterDialogueStateMapper / LobsterEvolutionEngineImpl）
CREATE TABLE IF NOT EXISTS lobster_dialogue_state (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    instance_id BIGINT NOT NULL,
    external_user_id VARCHAR(128) NOT NULL,
    node_code VARCHAR(100) DEFAULT NULL,
    detected_intent VARCHAR(50) DEFAULT NULL,
    sentiment VARCHAR(20) DEFAULT NULL,
    last_reply VARCHAR(500) DEFAULT NULL,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_session (company_id, external_user_id, instance_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 14. 支付订单表（PayService）
CREATE TABLE IF NOT EXISTS lobster_pay_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    contact_id BIGINT DEFAULT NULL,
    instance_id BIGINT DEFAULT NULL,
    order_no VARCHAR(64) NOT NULL,
    product_name VARCHAR(256),
    amount BIGINT DEFAULT 0 COMMENT '金额(分)',
    status VARCHAR(20) DEFAULT 'CREATED',
    gateway VARCHAR(20) DEFAULT 'wechat',
    transaction_id VARCHAR(128) DEFAULT NULL,
    extra TEXT,
    create_time DATETIME DEFAULT NOW(),
    paid_time DATETIME DEFAULT NULL,
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_order (order_no),
    INDEX idx_company_status (company_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 15. 用户标签表
CREATE TABLE IF NOT EXISTS customer_tag (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    external_user_id VARCHAR(128) NOT NULL,
    tag_key VARCHAR(100) NOT NULL,
    tag_value VARCHAR(500),
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_tag (company_id, external_user_id, tag_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 16. 渠道注册表
CREATE TABLE IF NOT EXISTS lobster_channel_type_registry (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    channel_type VARCHAR(30) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    source_table VARCHAR(100),
    user_id_column VARCHAR(100),
    enabled TINYINT DEFAULT 1,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 17. 统一触点映射
CREATE TABLE IF NOT EXISTS lobster_unified_contact (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    contact_id BIGINT NOT NULL,
    channel_type VARCHAR(30) NOT NULL,
    channel_user_id VARCHAR(128) NOT NULL,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW(),
    UNIQUE KEY uk_contact (company_id, contact_id, channel_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;