2628 lines
143 KiB
Plaintext
2628 lines
143 KiB
Plaintext
"""
|
||
CRM Models - Owned and Managed by Our Service
|
||
|
||
These models are fully owned and managed by our service.
|
||
Schema changes should be consistent and controlled.
|
||
|
||
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
||
TIMESTAMP COLUMN STANDARDS (see backlog/create_update_time_consistency_fix.md)
|
||
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
||
|
||
Every new table MUST declare timestamps exactly as follows:
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
Rules:
|
||
|
||
1. COLUMN NAMES — always `created_at` and `updated_at`. No other names
|
||
(create_time, update_time, last_modified_time, etc.) are permitted on
|
||
new tables. Old tables that used create_time/update_time keep those
|
||
legacy columns for backward compatibility but also have the standard
|
||
columns added alongside them.
|
||
|
||
2. DB-LEVEL ENFORCEMENT — both columns use `server_default=text(...)` so
|
||
the value is set by the database engine itself. This guarantees correct
|
||
timestamps regardless of the write path: ORM session.add/update,
|
||
raw session.execute(text("UPDATE ...")), or any other SQL client.
|
||
|
||
3. NO ORM onupdate= — never use `onupdate=` on `updated_at`. The
|
||
`ON UPDATE CURRENT_TIMESTAMP` clause in the server_default already
|
||
handles updates at the DB level for ALL write paths. The ORM-level
|
||
onupdate hook is bypassed by raw SQL and therefore unreliable.
|
||
|
||
4. NOT NULL — both columns must be nullable=False. A missing timestamp is
|
||
always a data quality defect.
|
||
|
||
5. APPEND-ONLY / IMMUTABLE TABLES — tables that are never updated (audit
|
||
logs, event ledgers, snapshot rows) need only `created_at`. Omitting
|
||
`updated_at` is acceptable and correct in that case; document it with
|
||
a comment on the class.
|
||
|
||
COMMON MISTAKES TO AVOID:
|
||
|
||
❌ updated_at = Column(DateTime, server_default=text('CURRENT_TIMESTAMP'),
|
||
onupdate=text('CURRENT_TIMESTAMP'))
|
||
# Bad: onupdate= is ORM-only; raw SQL updates never trigger it.
|
||
|
||
❌ update_time = Column(DateTime, server_default=func.now(), onupdate=func.now())
|
||
# Bad: func.now() in onupdate is also ORM-only, and dialect-sensitive.
|
||
|
||
❌ updated_at = Column(DateTime)
|
||
# Bad: no default, always NULL after insert.
|
||
|
||
✅ updated_at = Column(DateTime, nullable=False,
|
||
server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
|
||
comment='更新时间')
|
||
|
||
MIGRATION WORKFLOW:
|
||
|
||
After adding or modifying columns in this file, run:
|
||
make create MSG="<description>"
|
||
to auto-generate the Alembic migration, then review and apply with:
|
||
make upgrade
|
||
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
||
"""
|
||
|
||
from sqlalchemy import Column, String, Integer, BigInteger, SmallInteger, Float, DateTime, Text, Date, Boolean, text, Numeric, Index, func, schema, Computed, JSON, UniqueConstraint
|
||
from sqlalchemy.sql import func
|
||
from sqlalchemy.dialects import mysql
|
||
from sqlalchemy.dialects.mysql import MEDIUMTEXT
|
||
from .crm_base import CRMBase
|
||
from engine.core.enums import RiskProgressType, RiskProgressStatus
|
||
|
||
|
||
class CRMTodo(CRMBase):
|
||
__tablename__ = 'crm_todos'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
title = Column(String(255), nullable=False, comment='待办事项标题')
|
||
description = Column(Text, comment='详细描述')
|
||
status = Column(String(50), comment='状态（待处理/进行中/已完成/已取消）系统状态')
|
||
priority = Column(String(50), comment='优先级（高/中/低）')
|
||
start_date = Column(Date, comment='开始日期')
|
||
due_date = Column(Date, comment='截止日期')
|
||
reminder_date = Column(Date, comment='提醒日期')
|
||
owner_status = Column(String(50), comment='负责人状态（待处理/进行中/已完成/已取消）')
|
||
ai_status = Column(String(50), comment='AI建议状态（待处理/进行中/已完成/已取消）')
|
||
ai_note = Column(Text, comment='AI建议备注')
|
||
eval_time = Column(DateTime, comment='评估时间')
|
||
|
||
# 关联字段
|
||
owner_id = Column(String(255), comment='负责人唯一性ID')
|
||
owner_name = Column(String(255), comment='负责人姓名')
|
||
opportunity_id = Column(String(255), comment='关联的商机唯一ID')
|
||
opportunity_name = Column(String(255), comment='关联的商机名称')
|
||
opportunity_stage = Column(String(255), comment='关联的商机阶段')
|
||
account_id = Column(String(255), comment='关联的客户唯一ID')
|
||
account_name = Column(String(255), comment='关联的客户名称')
|
||
department = Column(String(255), comment='部门')
|
||
department_id = Column(String(255), comment='部门唯一性ID')
|
||
|
||
# 参与者
|
||
internal_participants = Column(Text, comment='内部参与者（JSON格式）')
|
||
external_participants = Column(Text, comment='外部参与者即客户（JSON格式）')
|
||
linked_account_ids = Column(Text, comment='Mandatory: 关联的客户ID列表（JSON格式）')
|
||
linked_opportunity_ids = Column(Text, comment='Mandatory: 关联的商机ID列表（JSON格式）')
|
||
|
||
# 分类和标签
|
||
todo_type = Column(String(50), comment='待办类型（跟进/会议/电话/邮件/其他）')
|
||
category = Column(String(50), comment='分类（销售/客户服务/内部协调等）')
|
||
tags = Column(String(255), comment='标签（逗号分隔）')
|
||
todo_suggestion = Column(Text, comment='待办建议')
|
||
suggested_material = Column(Text, comment='建议材料')
|
||
playbook_item_ref = Column(String(255), comment='销售手册项目引用，格式：playbook_{stage}_{idx}，如playbook_Qualification_0，多个引用用逗号分隔')
|
||
|
||
# 完成相关
|
||
completion_date = Column(DateTime, comment='完成日期')
|
||
completion_note = Column(Text, comment='完成说明')
|
||
user_update_time = Column(DateTime, comment='用户更新时间（用户更新状态、完成说明或截止日期时的时间戳）')
|
||
|
||
# 元数据
|
||
data_source = Column(String(50), comment='数据来源（Review/Playbook/Meeting/Chatbot/CRM/等）')
|
||
creator = Column(String(255), comment='创建人')
|
||
creator_id = Column(String(255), comment='创建人唯一性ID')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
last_modifier = Column(String(255), comment='最后修改人')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='最后修改时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
is_deleted = Column(Boolean, default=False, server_default=text('0'), comment='是否删除')
|
||
delete_time = Column(DateTime, nullable=True, comment='删除时间')
|
||
correlation_id = Column(String(255), comment='关联ID')
|
||
|
||
# Indexes for permission-based queries and filtering
|
||
# See: docs/permission/tsd_entry_list_permission_indexes.md
|
||
__table_args__ = (
|
||
Index('idx_crm_todos_owner_id', 'owner_id', 'is_deleted'),
|
||
Index('idx_crm_todos_account_id', 'account_id', 'is_deleted'),
|
||
Index('idx_crm_todos_opportunity_id', 'opportunity_id', 'is_deleted'),
|
||
Index('idx_crm_todos_overdue', 'ai_status', 'due_date', 'is_deleted'),
|
||
{'comment': '待办事项表'},
|
||
)
|
||
|
||
|
||
class CRMTodoMergeEvent(CRMBase):
|
||
__tablename__ = 'crm_todo_merge_events'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID')
|
||
job_id = Column(String(255), nullable=True, comment='合并任务ID')
|
||
survivor_todo_id = Column(String(255), nullable=False, comment='保留的待办唯一ID')
|
||
merged_todo_id = Column(String(255), nullable=True, comment='被合并并软删除的待办唯一ID')
|
||
incoming_source = Column(String(50), nullable=True, comment='新待办来源')
|
||
survivor_source = Column(String(50), nullable=True, comment='保留待办来源')
|
||
decision = Column(String(100), nullable=False, comment='合并决策')
|
||
merge_action = Column(String(100), nullable=True, comment='LLM合并动作')
|
||
duplicate_reason = Column(Text, nullable=True, comment='重复判断原因')
|
||
llm_decision_json = Column(JSON, nullable=True, comment='LLM原始决策')
|
||
validated_decision_json = Column(JSON, nullable=True, comment='校验后的决策')
|
||
raw_candidate_json = Column(JSON, nullable=True, comment='新待办候选内容')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_todo_merge_events_job', 'job_id'),
|
||
Index('idx_todo_merge_events_survivor', 'survivor_todo_id'),
|
||
Index('idx_todo_merge_events_merged', 'merged_todo_id'),
|
||
Index('idx_todo_merge_events_decision', 'decision'),
|
||
{'comment': '待办合并审计表'},
|
||
)
|
||
|
||
|
||
class CRMTodoMergeJob(CRMBase):
|
||
__tablename__ = 'crm_todo_merge_jobs'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
job_id = Column(String(255), nullable=False, unique=True, comment='合并任务ID')
|
||
status = Column(String(50), nullable=False, server_default=text("'PENDING'"), comment='任务状态')
|
||
trigger_source = Column(String(100), nullable=True, comment='触发来源')
|
||
scope_key = Column(String(512), nullable=True, comment='合并范围键')
|
||
new_todo_ids_json = Column(JSON, nullable=False, comment='新建待办ID列表')
|
||
candidate_todo_ids_json = Column(JSON, nullable=True, comment='候选待办ID列表')
|
||
attempt_count = Column(Integer, nullable=False, server_default=text('0'), comment='尝试次数')
|
||
last_error = Column(Text, nullable=True, comment='最近错误')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
started_at = Column(DateTime, nullable=True, comment='开始时间')
|
||
completed_at = Column(DateTime, nullable=True, comment='完成时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_todo_merge_jobs_status', 'status'),
|
||
Index('idx_todo_merge_jobs_scope', 'scope_key'),
|
||
{'comment': '待办语义合并任务表'},
|
||
)
|
||
|
||
|
||
|
||
|
||
|
||
class CRMSalesActivity(CRMBase):
|
||
__tablename__ = 'crm_sales_activities'
|
||
__table_args__ = (
|
||
Index('idx_activity_type', 'communication_medium'),
|
||
Index('idx_record_date', 'record_date'),
|
||
Index('idx_correlation_id', 'correlation_id'),
|
||
Index('idx_account_id', 'account_id'),
|
||
Index('idx_opportunity_id', 'opportunity_id'),
|
||
{'comment': '销售活动记录表'},
|
||
)
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
|
||
# Activity Type/Category/source
|
||
category = Column(String(50), comment='活动类别（如update/callhigh/其他）')
|
||
data_source = Column(String(100), comment='数据来源（URL/飞书/CRM/Chatbot/等）')
|
||
|
||
# Time, Location and participants
|
||
record_date = Column(Date, nullable=False, comment='记录日期（YYYY-MM-DD）')
|
||
location = Column(String(255), comment='活动地点')
|
||
communication_medium = Column(String(255), comment='活动形式及沟通方式（如：线上会议/线下会议/电话/邮件/拜访/演示/提案等）')
|
||
|
||
# Participants
|
||
internal_participants = Column(Text, comment='内部参与者（JSON格式）')
|
||
external_participants = Column(Text, comment='外部参与者即客户（JSON格式）')
|
||
key_stakeholders = Column(Text, comment='关键干系人')
|
||
|
||
# Linked Entities
|
||
account_id = Column(String(255), comment='关联的客户ID')
|
||
account_name = Column(String(255), comment='关联的客户名称')
|
||
opportunity_id = Column(String(255), comment='关联的商机ID')
|
||
opportunity_name = Column(String(255), comment='关联的商机名称')
|
||
linked_account_ids = Column(Text, comment='Mandatory: 关联的客户ID列表（JSON格式）')
|
||
linked_opportunity_ids = Column(Text, comment='Mandatory: 关联的商机ID列表（JSON格式）')
|
||
owner_id = Column(String(255), comment='负责人ID')
|
||
owner_name = Column(String(255), comment='负责人姓名')
|
||
|
||
# Core Content
|
||
summary = Column(String(500), nullable=False, comment='活动摘要')
|
||
detailed_notes = Column(Text, comment='详细记录')
|
||
|
||
# Core Sales Opinions
|
||
next_steps = Column(Text, comment='下一步行动计划')
|
||
blockers = Column(Text, comment='当前障碍或挑战,即要解决的问题')
|
||
core_biz_info = Column(Text, comment='核心业务信息')
|
||
|
||
# Optional Options
|
||
deal_probability_change = Column(String(50), comment='成单概率变化（上升/下降/不变）')
|
||
customer_sentiment = Column(String(50), comment='客户态度（积极/中性/消极）')
|
||
|
||
# Metadata
|
||
correlation_id = Column(String(255), comment='关联ID')
|
||
creator = Column(String(255), comment='创建人')
|
||
creator_id = Column(String(255), comment='创建人唯一性ID')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
last_modifier = Column(String(255), comment='最后修改人')
|
||
last_modified_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='最后修改时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
|
||
class CRMLogProcessingStatus(CRMBase):
|
||
__tablename__ = 'crm_log_processing_status'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
|
||
# Source Identification
|
||
source_table = Column(String(255), comment='源数据表名')
|
||
source_field_name = Column(String(255), comment='源字段名称')
|
||
source_id = Column(Integer, comment='源数据ID')
|
||
source_unique_id = Column(String(255), comment='源数据唯一性ID')
|
||
|
||
# Processing Status
|
||
processing_status = Column(String(50), nullable=False, default='PENDING', server_default=text("'PENDING'"), comment='处理状态（PENDING/PROCESSING/PROCESSED/FAILED）')
|
||
processing_stage = Column(String(50), comment='处理阶段（如：INITIAL/EXTRACT/TRANSFORM/LOAD）')
|
||
processing_attempts = Column(Integer, nullable=False, default=0, server_default=text('0'), comment='处理尝试次数')
|
||
last_processing_time = Column(DateTime, comment='最后处理时间')
|
||
processing_error = Column(Text, comment='处理错误信息')
|
||
processing_mode = Column(String(50), comment='处理模式（APPEND/CREATE）')
|
||
flag = Column(String(255), comment='标志位,标记该数据被处理的任务')
|
||
|
||
# Source Data Hash
|
||
source_data_hash = Column(String(64), comment='源数据哈希值（用于检测变更）')
|
||
|
||
# Metadata
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Define indexes to match database schema
|
||
__table_args__ = (
|
||
Index('idx_log_processing_flag_status_source', 'flag', 'processing_status', 'source_unique_id'),
|
||
Index('idx_log_processing_source_unique_id', 'source_unique_id'),
|
||
Index('idx_processing_status', 'processing_status'),
|
||
Index('idx_source_id', 'source_id'),
|
||
Index('idx_source_table', 'source_table'),
|
||
{'comment': '日志处理状态跟踪表'},
|
||
)
|
||
|
||
|
||
class CRMMessageInbox(CRMBase):
|
||
__tablename__ = 'crm_message_inbox'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
message_id = Column(String(255), nullable=False, unique=True, comment='Message public ID')
|
||
message_type = Column(String(100), nullable=False, comment='Message type, e.g. crm.visit_record.saved')
|
||
source_system = Column(String(100), nullable=False, comment='Source system')
|
||
source_table = Column(String(255), comment='Source table')
|
||
source_unique_id = Column(String(255), comment='Source unique ID')
|
||
dedupe_key = Column(String(512), nullable=False, unique=True, comment='Idempotency key')
|
||
payload_hash = Column(String(64), nullable=False, comment='Canonical payload SHA-256 hash')
|
||
payload = Column(JSON, nullable=False, comment='Raw message payload')
|
||
status = Column(String(50), nullable=False, default='RECEIVED', server_default=text("'RECEIVED'"), comment='Message status')
|
||
priority = Column(Integer, nullable=False, default=0, server_default=text('0'), comment='Higher value is claimed first')
|
||
available_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Earliest dispatch time')
|
||
received_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Message receipt time')
|
||
orchestrated_at = Column(DateTime, comment='Work item orchestration time')
|
||
completed_at = Column(DateTime, comment='Terminal completion time')
|
||
attempt_count = Column(Integer, nullable=False, default=0, server_default=text('0'), comment='Orchestration attempts')
|
||
last_error = Column(Text, comment='Last orchestration error')
|
||
trace_id = Column(String(255), comment='Upstream trace ID')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Create time')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='Update time')
|
||
|
||
__table_args__ = (
|
||
Index('idx_message_inbox_status_available', 'status', 'available_at', 'priority'),
|
||
Index('idx_message_inbox_source', 'source_table', 'source_unique_id'),
|
||
{'comment': 'Internal message inbox'},
|
||
)
|
||
|
||
|
||
class CRMMessageWorkItem(CRMBase):
|
||
__tablename__ = 'crm_message_work_items'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
work_item_id = Column(String(255), nullable=False, unique=True, comment='Work item public ID')
|
||
message_id = Column(String(255), nullable=False, comment='Parent message ID')
|
||
work_type = Column(String(100), nullable=False, default='workflow_plan', server_default=text("'workflow_plan'"), comment='Work item type')
|
||
queue_name = Column(String(100), nullable=False, default='workflow_parallel', server_default=text("'workflow_parallel'"), comment='Logical queue name')
|
||
priority = Column(Integer, nullable=False, default=0, server_default=text('0'), comment='Higher value is claimed first')
|
||
plan_id = Column(String(255), comment='Workflow plan ID')
|
||
execution_id = Column(String(255), comment='Workflow execution ID')
|
||
report_id = Column(String(255), comment='Workflow report ID')
|
||
bootstrap_inputs = Column(JSON, comment='Workflow bootstrap inputs')
|
||
status = Column(String(50), nullable=False, default='PENDING', server_default=text("'PENDING'"), comment='Work item status')
|
||
attempt_count = Column(Integer, nullable=False, default=0, server_default=text('0'), comment='Dispatch attempts')
|
||
max_attempts = Column(Integer, nullable=False, default=3, server_default=text('3'), comment='Max dispatch attempts')
|
||
available_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Earliest dispatch time')
|
||
claimed_by = Column(String(255), comment='Dispatcher identity')
|
||
claimed_at = Column(DateTime, comment='Claim time')
|
||
started_at = Column(DateTime, comment='Start time')
|
||
submitted_at = Column(DateTime, comment='Celery submission time')
|
||
finished_at = Column(DateTime, comment='Terminal time')
|
||
last_error = Column(Text, comment='Last error')
|
||
output_ref = Column(JSON, comment='Output references or guard results')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Create time')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='Update time')
|
||
|
||
__table_args__ = (
|
||
Index('idx_message_work_items_message', 'message_id'),
|
||
Index('idx_message_work_items_ready', 'status', 'queue_name', 'available_at', 'priority'),
|
||
Index('idx_message_work_items_execution', 'execution_id'),
|
||
{'comment': 'Internal message work items'},
|
||
)
|
||
|
||
|
||
class CRMMessageWorkItemDependency(CRMBase):
|
||
__tablename__ = 'crm_message_work_item_deps'
|
||
|
||
work_item_id = Column(String(255), primary_key=True, comment='Dependent work item ID')
|
||
depends_on_work_item_id = Column(String(255), primary_key=True, comment='Prerequisite work item ID')
|
||
dependency_type = Column(String(50), nullable=False, default='SUCCESS', server_default=text("'SUCCESS'"), comment='SUCCESS/COMPLETION/OUTPUT_EXISTS')
|
||
output_condition = Column(JSON, comment='Optional output condition')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Create time')
|
||
|
||
__table_args__ = (
|
||
Index('idx_message_work_item_deps_depends_on', 'depends_on_work_item_id'),
|
||
{'comment': 'Internal message work item dependencies'},
|
||
)
|
||
|
||
|
||
class CRMMessageDispatchLog(CRMBase):
|
||
__tablename__ = 'crm_message_dispatch_logs'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
message_id = Column(String(255), nullable=False, comment='Message ID')
|
||
work_item_id = Column(String(255), comment='Work item ID')
|
||
event_type = Column(String(100), nullable=False, comment='Dispatch event type')
|
||
detail = Column(JSON, comment='Event detail')
|
||
error_message = Column(Text, comment='Error message')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Create time')
|
||
|
||
__table_args__ = (
|
||
Index('idx_message_dispatch_logs_message', 'message_id'),
|
||
Index('idx_message_dispatch_logs_work_item', 'work_item_id'),
|
||
{'comment': 'Internal message dispatch logs'},
|
||
)
|
||
|
||
|
||
class CRMEvalMetrics(CRMBase):
|
||
__tablename__ = 'crm_eval_metrics'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID')
|
||
metric_name = Column(String(255), nullable=False, comment='指标名称')
|
||
metric_type = Column(String(255), nullable=False, comment='指标类型')
|
||
account_id = Column(String(255), comment='客户ID')
|
||
opportunity_id = Column(String(255), comment='商机ID')
|
||
metric_value = Column(Float, comment='指标值')
|
||
metric_content = Column(Text, comment='指标内容')
|
||
metric_unit = Column(String(255), comment='指标单位')
|
||
metric_description = Column(Text, comment='指标描述')
|
||
eval_time = Column(DateTime, comment='评估时间')
|
||
is_active = Column(Boolean, default=True, server_default=text('1'), comment='是否为当前有效评估')
|
||
|
||
# KPI metrics fields (for owner/department-based KPIs)
|
||
owner = Column(String(255), comment='负责人姓名（用于owner级别KPI）')
|
||
owner_id = Column(String(255), comment='负责人唯一性ID（用于owner级别KPI）')
|
||
department = Column(String(255), comment='部门名称（用于department级别KPI）')
|
||
department_id = Column(String(255), comment='部门唯一性ID（用于department级别KPI）')
|
||
time_period = Column(String(50), comment='时间周期（如Q1+Q2+Q3+Q4，用于Review 1s）')
|
||
report_date = Column(Date, comment='报告日期（报告生成的日期）')
|
||
report_datetime = Column(DateTime, comment='报告精确时间')
|
||
report_week_of_year = Column(Integer, comment='年内周数(1-53)')
|
||
report_month_of_year = Column(Integer, comment='年内月份(1-12)')
|
||
report_quarter_of_year = Column(Integer, comment='年内季度(1-4)')
|
||
report_year = Column(Integer, comment='报告年份')
|
||
execution_id = Column(String(255), comment='工作流执行ID（关联到报告）')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Define indexes to match the SQL migration
|
||
__table_args__ = (
|
||
Index('idx_owner_dept_period', 'owner', 'department', 'time_period'),
|
||
Index('idx_owner_id_dept_id', 'owner_id', 'department_id'),
|
||
Index('idx_execution_id', 'execution_id'),
|
||
Index('idx_report_date', 'report_date'),
|
||
Index('idx_report_year_week', 'report_year', 'report_week_of_year'),
|
||
Index('idx_report_year_month', 'report_year', 'report_month_of_year'),
|
||
Index('idx_report_year_quarter', 'report_year', 'report_quarter_of_year'),
|
||
{'comment': '评估指标表'},
|
||
)
|
||
|
||
|
||
class CRMPushNotification(CRMBase):
|
||
"""Push notification configuration and tracking"""
|
||
__tablename__ = 'crm_push_notification'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
|
||
# Notification Basic Info
|
||
title = Column(String(255), nullable=False, comment='通知标题')
|
||
content = Column(Text, comment='通知内容')
|
||
notification_type = Column(String(50), nullable=False, comment='通知类型（workflow_complete/workflow_started/workflow_failed/custom）')
|
||
priority = Column(String(20), default='medium', comment='优先级（high/medium/low）')
|
||
|
||
# Workflow Linkage
|
||
plan_id = Column(String(255), comment='关联的工作流计划ID')
|
||
execution_id = Column(String(255), comment='关联的工作流执行ID')
|
||
report_id = Column(String(255), comment='关联的报告ID')
|
||
tenant_id = Column(String(255), nullable=False, comment='租户ID')
|
||
correlation_id = Column(String(255), comment='关联ID，用于追踪相关联的业务实体')
|
||
|
||
# Scheduling and Status
|
||
status = Column(String(50), nullable=False, default='pending', comment='通知状态（pending/scheduled/sent/failed/cancelled）')
|
||
scheduled_time = Column(DateTime, comment='计划发送时间')
|
||
trigger_condition = Column(String(100), comment='触发条件（immediate/workflow_complete/delay/custom）')
|
||
delay_minutes = Column(Integer, comment='延迟发送分钟数')
|
||
|
||
# Third-party Service Integration
|
||
target_service = Column(String(100), comment='目标第三方服务名称（webhook/email/sms/slack/dingtalk等）')
|
||
api_endpoint = Column(String(500), comment='第三方服务API端点')
|
||
http_method = Column(String(10), default='POST', comment='HTTP方法（GET/POST/PUT等）')
|
||
|
||
# Payload and Headers
|
||
request_payload = Column(Text, comment='发送给第三方服务的完整请求载荷（JSON格式）')
|
||
request_headers = Column(Text, comment='HTTP请求头（JSON格式）')
|
||
auth_config = Column(Text, comment='认证配置（JSON格式，支持Bearer Token/API Key等）')
|
||
|
||
# Retry Logic
|
||
max_retry_attempts = Column(Integer, default=3, comment='最大重试次数')
|
||
current_retry_count = Column(Integer, default=0, comment='当前重试次数')
|
||
retry_interval_minutes = Column(Integer, default=5, comment='重试间隔（分钟）')
|
||
next_retry_time = Column(DateTime, comment='下次重试时间')
|
||
|
||
# Response and Error Tracking
|
||
last_sent_time = Column(DateTime, comment='最后发送时间')
|
||
response_status_code = Column(Integer, comment='第三方服务响应状态码')
|
||
response_body = Column(Text, comment='第三方服务响应内容')
|
||
error_message = Column(Text, comment='错误信息')
|
||
|
||
# Business Context (for notification content generation)
|
||
linked_account_ids = Column(Text, comment='关联的客户ID列表（JSON格式）')
|
||
linked_opportunity_ids = Column(Text, comment='关联的商机ID列表（JSON格式）')
|
||
business_context = Column(Text, comment='业务上下文信息（JSON格式，用于动态生成通知内容）')
|
||
|
||
# Metadata
|
||
created_by = Column(String(255), comment='创建人')
|
||
created_by_id = Column(String(255), comment='创建人ID')
|
||
tags = Column(String(255), comment='标签（逗号分隔）')
|
||
notes = Column(Text, comment='备注信息')
|
||
|
||
# Timestamps
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
completed_time = Column(DateTime, comment='完成时间')
|
||
expired_time = Column(DateTime, comment='过期时间')
|
||
|
||
__table_args__ = (
|
||
{'comment': '推送通知配置表'},
|
||
)
|
||
|
||
|
||
class CRMAccountAssessment(CRMBase):
|
||
"""Account assessment records"""
|
||
__tablename__ = 'crm_account_assessment'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
assessment_date = Column(Date, nullable=False, comment='日期')
|
||
account_id = Column(String(255), comment='客户ID')
|
||
account_name = Column(String(255), comment='客户名称')
|
||
customer_type = Column(String(50), comment='客户类型(end_customer/partner)')
|
||
account_level = Column(String(255), comment='客户等级')
|
||
|
||
is_first_visit = Column(Boolean, comment='是否首次拜访')
|
||
assessment_flag = Column(Text, comment='评估结果(red/yellow/green)')
|
||
assessment_description = Column(Text, comment='评估描述')
|
||
assessment_description_en = Column(Text, comment='评估描述英文')
|
||
opportunity_ids = Column(Text, comment='商机UniqueID列表, json Array格式')
|
||
opportunity_names = Column(Text, comment='商机名称列表, json Array格式')
|
||
|
||
# 销售自行填写的信息
|
||
follow_up_note = Column(Text, comment='销售跟进记录')
|
||
follow_up_note_en = Column(Text, comment='销售跟进记录(英文)')
|
||
follow_up_next_step = Column(Text, comment='销售跟进下一步')
|
||
follow_up_next_step_en = Column(Text, comment='销售跟进下一步(英文)')
|
||
|
||
# Simple link to statistics
|
||
correlation_id = Column(String(255), comment='关联ID，用于链接到CRMDailyAccountStatistics')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
{'comment': '客户评估表'},
|
||
)
|
||
|
||
|
||
class CRMDailyAccountStatistics(CRMBase):
|
||
__tablename__ = 'crm_daily_account_statistics'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID')
|
||
report_date = Column(Date, nullable=False, comment='日期')
|
||
|
||
sales_id = Column(String(255), comment='销售ID')
|
||
sales_name = Column(String(255), comment='销售名字')
|
||
department_id = Column(String(255), comment='部门ID')
|
||
department_name = Column(String(255), comment='部门名字')
|
||
|
||
# Assessment statistics
|
||
assessment_red_count = Column(Integer, default=0, comment='评估为red的次数')
|
||
assessment_yellow_count = Column(Integer, default=0, comment='评估为yellow的次数')
|
||
assessment_green_count = Column(Integer, default=0, comment='评估为green的次数')
|
||
|
||
# 最终客户跟进统计
|
||
end_customer_total_follow_up = Column(Integer, default=0, comment='总跟进最终客户数')
|
||
end_customer_total_first_visit = Column(Integer, default=0, comment='总首次拜访最终客户数')
|
||
end_customer_total_multi_visit = Column(Integer, default=0, comment='总多次拜访最终客户数')
|
||
|
||
# 合作伙伴跟进统计
|
||
partner_total_follow_up = Column(Integer, default=0, comment='总跟进合作伙伴数')
|
||
partner_total_first_visit = Column(Integer, default=0, comment='总首次拜访合作伙伴数')
|
||
partner_total_multi_visit = Column(Integer, default=0, comment='总多次拜访合作伙伴数')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
{'comment': '客户日报统计表'},
|
||
)
|
||
|
||
|
||
class CRMReportIndex(CRMBase):
|
||
__tablename__ = 'crm_report_index'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
|
||
# Business identifiers
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID')
|
||
report_id = Column(Integer, nullable=False, comment='报告ID, 对应CRMReport表的id')
|
||
report_id_code = Column(String(255), nullable=False, comment='报告ID编码, 对应CRMReport表的report_id')
|
||
execution_id = Column(String(255), nullable=False, comment='执行ID')
|
||
plan_id = Column(String(255), nullable=False, comment='计划ID')
|
||
|
||
# Report classification
|
||
report_type = Column(String(64), nullable=False, comment='报告类型: review1/review1s/review2/review5/previsit/daily')
|
||
report_calendar_type = Column(String(64), nullable=False, comment='报告周期类型: daily/weekly/monthly/quarterly/yearly')
|
||
report_status = Column(String(32), nullable=False, default='published', comment='报告状态: draft/published/archived')
|
||
|
||
# Date fields for efficient period-based queries
|
||
report_date = Column(Date, nullable=False, comment='报告日期')
|
||
report_datetime = Column(DateTime, nullable=False, comment='报告精确时间')
|
||
report_week_of_year = Column(Integer, nullable=False, comment='年内周数(1-53)')
|
||
report_month_of_year = Column(Integer, nullable=False, comment='年内月份(1-12)')
|
||
report_quarter_of_year = Column(Integer, nullable=False, comment='年内季度(1-4)')
|
||
report_year = Column(Integer, nullable=False, comment='报告年份')
|
||
|
||
# Business context for efficient filtering
|
||
created_by = Column(String(255), comment='创建人')
|
||
department_id = Column(String(255), comment='部门ID')
|
||
department_name = Column(String(255), comment='部门名字')
|
||
|
||
# Metadata
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Essential indexes for performance
|
||
__table_args__ = (
|
||
# Primary lookup indexes - most common query patterns
|
||
Index('idx_report_type_date', 'report_type', 'report_date'),
|
||
Index('idx_execution_id', 'execution_id'),
|
||
Index('idx_report_id', 'report_id'),
|
||
Index('idx_unique_id', 'unique_id'),
|
||
{'comment': '报告索引表'},
|
||
)
|
||
|
||
|
||
class CRMChargeAudit(CRMBase):
|
||
__tablename__ = 'crm_charge_audit'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
|
||
# Workflow execution context
|
||
execution_id = Column(String(255), nullable=False, comment='工作流执行ID')
|
||
plan_id = Column(String(255), nullable=False, comment='执行计划ID')
|
||
report_id = Column(String(255), comment='报告ID')
|
||
version = Column(Integer, comment='执行版本')
|
||
step_id = Column(String(255), comment='产生计费项的步骤ID')
|
||
|
||
# Business object context
|
||
charge_item_type = Column(String(100), nullable=False, comment='计费项类型')
|
||
business_object_type = Column(String(100), nullable=False, comment='业务对象类型，如account/opportunity')
|
||
business_object_id = Column(String(255), nullable=False, comment='业务对象ID')
|
||
business_object_name = Column(String(255), comment='业务对象名称')
|
||
|
||
# External charging payload
|
||
trace_id = Column(String(100), nullable=False, comment='外部计费trace_id')
|
||
operator = Column(String(100), nullable=False, comment='操作人')
|
||
item_url = Column(String(500), comment='计费项结果URL')
|
||
review_detail = Column(String(500), nullable=False, comment='计费明细链接或引用')
|
||
ai_module_key = Column(String(100), nullable=False, comment='AI模块key')
|
||
|
||
# External charging lifecycle
|
||
status = Column(String(50), nullable=False, default='pending', server_default=text("'pending'"), comment='pending/sent/failed/skipped')
|
||
external_usage_record_id = Column(String(255), comment='外部用量记录ID')
|
||
external_sync_status = Column(String(100), comment='外部同步状态')
|
||
charge_request = Column(JSON, comment='发送给外部计费服务的请求')
|
||
charge_response = Column(JSON, comment='外部计费服务响应')
|
||
error_message = Column(Text, comment='错误信息')
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
charged_at = Column(DateTime, comment='发送计费时间')
|
||
|
||
__table_args__ = (
|
||
UniqueConstraint('trace_id', name='uq_crm_charge_audit_trace_id'),
|
||
UniqueConstraint(
|
||
'execution_id',
|
||
'ai_module_key',
|
||
'business_object_type',
|
||
'business_object_id',
|
||
name='uq_crm_charge_audit_business_item',
|
||
),
|
||
Index('idx_crm_charge_audit_execution', 'execution_id', 'plan_id'),
|
||
Index('idx_crm_charge_audit_business_object', 'business_object_type', 'business_object_id'),
|
||
Index('idx_crm_charge_audit_status', 'status'),
|
||
{'comment': 'AI计费审计表'},
|
||
)
|
||
|
||
|
||
class CRMSystemConfiguration(CRMBase):
|
||
__tablename__ = 'crm_system_configurations'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
config_type = Column(String(255), nullable=False, comment='配置类型（如CommunicationCategory/VisitStatus/UserRoles等）')
|
||
config_key = Column(String(255), nullable=False, comment='配置键')
|
||
config_value = Column(String(255), nullable=False, comment='配置值')
|
||
is_active = Column(Boolean, nullable=False, default=True, comment='是否启用')
|
||
description = Column(Text, comment='配置描述')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Define indexes to match the SQL table definition
|
||
__table_args__ = (
|
||
Index('idx_config_type', 'config_type'),
|
||
Index('idx_config_key', 'config_key'),
|
||
Index('idx_config_type_key', 'config_type', 'config_key'),
|
||
# Note: unique constraint is handled by the unique key in SQL
|
||
{'comment': '系统配置表'},
|
||
)
|
||
|
||
|
||
class CRMForecastTypeMapping(CRMBase):
|
||
"""Canonical forecast type mapping table.
|
||
|
||
Maps internal type keys (e.g. 'commit', 'closed_won') to the customer-specific
|
||
text values that appear in the CRM data. The stats endpoint always emits all
|
||
active rows in display_order, zero-filling types that have no data in the
|
||
current filter scope.
|
||
"""
|
||
__tablename__ = 'crm_forecast_type_mappings'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
internal_type = Column(String(50), nullable=False, comment='内部类型键 (e.g. commit, closed_won, upside, pipeline, lost_cancel)')
|
||
customer_values = Column(Text, nullable=False, comment='客户侧文本值列表（JSON数组，不区分大小写匹配）')
|
||
display_order = Column(Integer, nullable=False, default=0, comment='前端展示顺序（数字越小越靠前）')
|
||
is_active = Column(Boolean, nullable=False, default=True, server_default=text('1'), comment='是否启用')
|
||
description = Column(Text, comment='配置描述')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_crm_ftm_internal_type', 'internal_type', unique=True),
|
||
Index('idx_crm_ftm_display_order', 'display_order'),
|
||
Index('idx_crm_ftm_is_active', 'is_active'),
|
||
{'comment': 'Forecast type 标准映射表'},
|
||
)
|
||
|
||
|
||
class CRMPlaybook(CRMBase):
|
||
"""Playbook-level metadata for selectable playbooks."""
|
||
__tablename__ = 'crm_playbook'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
handbook_id = Column(String(64), nullable=False, comment='销售手册ID')
|
||
name = Column(String(255), nullable=False, comment='销售手册名称')
|
||
description = Column(Text, comment='描述')
|
||
is_active = Column(Boolean, default=True, server_default=text('1'), comment='是否启用')
|
||
is_default = Column(Boolean, default=False, server_default=text('0'), comment='是否默认销售手册')
|
||
created_by = Column(String(255), comment='创建人')
|
||
updated_by = Column(String(255), comment='更新人')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_crm_playbook_handbook_id', 'handbook_id', unique=True),
|
||
Index('idx_crm_playbook_active', 'is_active'),
|
||
Index('idx_crm_playbook_default', 'is_default'),
|
||
{'comment': 'Playbook元数据表'},
|
||
)
|
||
|
||
|
||
class CRMDataQualityWarning(CRMBase):
|
||
"""Persisted data-quality warnings emitted by scheduled/manual scanners."""
|
||
__tablename__ = 'crm_data_quality_warnings'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID')
|
||
scan_id = Column(String(255), nullable=False, comment='扫描ID')
|
||
rule_id = Column(String(255), nullable=False, comment='规则ID')
|
||
severity = Column(String(50), nullable=False, default='warning', server_default=text("'warning'"), comment='严重级别')
|
||
status = Column(String(50), nullable=False, default='active', server_default=text("'active'"), comment='状态(active/resolved)')
|
||
identity_hash = Column(String(64), nullable=False, comment='规则发现项身份哈希')
|
||
|
||
source_table = Column(String(255), nullable=False, comment='源表')
|
||
source_field = Column(String(255), comment='源字段')
|
||
source_value = Column(String(255), comment='源字段值')
|
||
handbook_id = Column(String(64), comment='Playbook handbook_id')
|
||
affected_count = Column(Integer, nullable=False, default=0, server_default=text('0'), comment='影响记录数')
|
||
sample_entity_ids = Column(Text, comment='样例实体ID(JSON)')
|
||
details = Column(Text, comment='规则上下文(JSON)')
|
||
|
||
first_seen_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='首次发现时间')
|
||
last_seen_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='最近发现时间')
|
||
resolved_at = Column(DateTime, comment='解决时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_dq_warning_rule_status', 'rule_id', 'status'),
|
||
Index('idx_dq_warning_identity_status', 'identity_hash', 'status', unique=True),
|
||
Index('idx_dq_warning_last_seen', 'last_seen_at'),
|
||
Index('idx_dq_warning_severity_status', 'severity', 'status'),
|
||
{'comment': '数据质量告警表'},
|
||
)
|
||
|
||
|
||
class CRMSentinelEvent(CRMBase):
|
||
"""Persisted sentinel health findings for dashboarding and alerting."""
|
||
__tablename__ = 'crm_sentinel_events'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
event_id = Column(String(255), nullable=False, comment='Public event ID')
|
||
check_name = Column(String(100), nullable=False, comment='Check name e.g. stale_task')
|
||
severity = Column(String(20), nullable=False, comment='INFO/WARNING/CRITICAL')
|
||
status = Column(String(20), nullable=False, default='OPEN', server_default=text("'OPEN'"), comment='OPEN/RESOLVED/SUPPRESSED')
|
||
subject_type = Column(String(100), nullable=False, comment='task_execution/work_item/celery_queue/db_session')
|
||
subject_id = Column(String(255), nullable=False, comment='Subject identifier')
|
||
message = Column(Text, nullable=False, comment='Human-readable description')
|
||
detail = Column(JSON, comment='Structured payload')
|
||
resolved_at = Column(DateTime, comment='When finding was resolved')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Create time')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='Update time')
|
||
|
||
__table_args__ = (
|
||
Index('idx_sentinel_event_id', 'event_id', unique=True),
|
||
Index('idx_sentinel_check_status', 'check_name', 'status'),
|
||
Index('idx_sentinel_subject', 'subject_type', 'subject_id'),
|
||
Index('idx_sentinel_severity_status_created', 'severity', 'status', 'created_at'),
|
||
Index('idx_sentinel_open_lookup', 'check_name', 'subject_id', 'status'),
|
||
{'comment': 'Sentinel health monitor events'},
|
||
)
|
||
|
||
|
||
class CRMSentinelScheduleLog(CRMBase):
|
||
"""Tracks scheduled workflow triggers for submission verification."""
|
||
__tablename__ = 'crm_sentinel_schedule_log'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
log_id = Column(String(255), nullable=False, comment='Public log ID')
|
||
plan_id = Column(String(255), nullable=False, comment='Workflow plan ID')
|
||
execution_id = Column(String(255), nullable=False, comment='Expected task execution ID')
|
||
triggered_at = Column(DateTime, nullable=False, comment='When trigger succeeded')
|
||
trigger_source = Column(String(50), nullable=False, default='manual', server_default=text("'manual'"), comment='cronicle/manual')
|
||
verified_at = Column(DateTime, comment='When submission check completed')
|
||
status = Column(String(20), nullable=False, default='PENDING', server_default=text("'PENDING'"), comment='PENDING/VERIFIED/MISSING')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='Create time')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='Update time')
|
||
|
||
__table_args__ = (
|
||
Index('idx_sentinel_schedule_log_id', 'log_id', unique=True),
|
||
Index('idx_sentinel_schedule_plan_status', 'plan_id', 'status'),
|
||
Index('idx_sentinel_schedule_execution', 'execution_id'),
|
||
{'comment': 'Sentinel scheduled trigger audit log'},
|
||
)
|
||
|
||
|
||
class CRMPlaybookMapping(CRMBase):
|
||
"""Field-based playbook mapping - any opportunity field can be mapped to a playbook"""
|
||
__tablename__ = 'crm_playbook_mapping'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
field_name = Column(String(100), nullable=False, comment='字段名 (e.g., business_type, opportunity_stage)')
|
||
field_value = Column(String(255), nullable=False, comment='字段值 (e.g., 新签, Discovery)')
|
||
handbook_id = Column(String(64), nullable=False, comment='关联的playbook handbook_id')
|
||
description = Column(Text, comment='描述')
|
||
is_active = Column(Boolean, default=True, comment='是否启用')
|
||
priority = Column(Integer, default=0, comment='优先级，高优先级优先匹配')
|
||
created_by = Column(String(255), comment='创建人')
|
||
updated_by = Column(String(255), comment='更新人')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_field_mapping', 'field_name', 'field_value', unique=True),
|
||
Index('idx_handbook_id', 'handbook_id'),
|
||
Index('idx_is_active_priority', 'is_active', 'priority'),
|
||
{'comment': 'Playbook字段映射表'},
|
||
)
|
||
|
||
|
||
class CRMDiagnosticPlaybook(CRMBase):
|
||
__tablename__ = 'diagnostic_playbook'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
|
||
# Execution tracking
|
||
plan_id = Column(String(64), nullable=False, comment='执行计划ID')
|
||
report_id = Column(String(64), comment='报告ID')
|
||
execution_id = Column(String(255), nullable=False, comment='执行ID, naming convention: plan_<plan_id>_<version>')
|
||
|
||
# General info
|
||
handbook_id = Column(String(64), nullable=False, comment='手册ID')
|
||
name = Column(String(255), nullable=False, comment='手册名称')
|
||
version = Column(String(255), nullable=False, comment='手册版本')
|
||
status = Column(String(255), nullable=False, comment='手册状态: active(生效中), draft(草稿), archived(已归档), deprecated(已废弃)')
|
||
type = Column(String(255), nullable=False, comment='手册类型')
|
||
sequence = Column(Integer, nullable=False, default=1, comment='销售阶段顺序，用于确定阶段的先后顺序，从1开始')
|
||
|
||
# Sales process definition
|
||
sales_stage = Column(String(255), comment='销售阶段')
|
||
stage_category = Column(String(64), comment='阶段语义分类: open/closed_won/closed_lost/cancelled')
|
||
stage_attributes = Column(JSON, comment='阶段行为属性覆盖(JSON)')
|
||
sales_key_activities = Column(Text, comment='销售关键动作')
|
||
sales_deliverables = Column(Text, comment='交付成果')
|
||
sales_exit_criteria = Column(Text, comment='阶段转化标准')
|
||
sales_key_info = Column(Text, comment='关键获取信息(CRM必填项)')
|
||
sales_outcomes = Column(Text, comment='销售阶段任务')
|
||
|
||
# Customer behavior and evaluation
|
||
customer_behavior_purchase = Column(Text, comment='客户采购行为')
|
||
customer_behavior_supporting = Column(Text, comment='客户支持行为')
|
||
sales_alerts = Column(Text, comment='预警')
|
||
fcst_eval_checklist = Column(Text, comment='FCST评估清单')
|
||
fcst_eval_guidance = Column(Text, comment='FCST评估指引')
|
||
|
||
# Timestamps
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_diagnostic_playbook_stage_lookup', 'handbook_id', 'status', 'sales_stage'),
|
||
Index('idx_diagnostic_playbook_stage_category', 'handbook_id', 'stage_category'),
|
||
{'comment': '诊断Playbook表'},
|
||
)
|
||
|
||
|
||
class CRMACVTargets(CRMBase):
|
||
__tablename__ = 'crm_acv_targets'
|
||
__table_args__ = {'comment': '按财年/季度保存ACV目标（quarter为NULL表示全年）'}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
fiscal_year = Column(String(9), nullable=False, comment='财年（如2026/2027）')
|
||
quarter = Column(String(8), comment='季度（如Q1、Q2、Q3、Q4，NULL表示全年）')
|
||
category = Column(String(255), comment='类别(FCST/FCST_NE)')
|
||
department = Column(String(255), comment='部门')
|
||
owner = Column(String(255), comment='负责人')
|
||
target_acv = Column(Numeric(18, 2), nullable=False, comment='ACV目标')
|
||
notes = Column(Text, comment='备注')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
# TODO: 新增一个字段is_active, 用于标识是否为当前有效目标
|
||
|
||
# Computed column for business key (FY + period suffix)
|
||
# Used by KPI calculations and query strategies
|
||
# Note: Using VIRTUAL (persisted=False) because STORED cannot be added via ALTER TABLE
|
||
biz_key = Column(
|
||
String(16),
|
||
Computed(
|
||
"CONCAT('FY', RIGHT(SUBSTRING_INDEX(fiscal_year, '/', 1), 2), IFNULL(quarter, ''))",
|
||
persisted=False # VIRTUAL computed column (not STORED)
|
||
),
|
||
comment='业务主键（如FY26Q1，全年为FY26）'
|
||
)
|
||
|
||
|
||
class CRMAchievementTimeseries(CRMBase):
|
||
"""Pre-computed cumulative achievement time-series for the line chart (业绩达成与预测折线图)."""
|
||
|
||
__tablename__ = "crm_achievement_timeseries"
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
# Dimension keys
|
||
period_type = Column(String(10), nullable=False, comment="yearly, quarterly, monthly")
|
||
period = Column(String(20), nullable=False, comment="FY26, FY26Q1, 2025-04")
|
||
scope_type = Column(String(20), nullable=False, comment="company, department, owner")
|
||
scope_id = Column(String(100), default=None, comment="department_id or owner_id; NULL for company")
|
||
scope_name = Column(String(200), default=None, comment="department or owner name; NULL for company")
|
||
granularity = Column(String(10), nullable=False, comment="daily or weekly")
|
||
point_date = Column(Date, nullable=False, comment="date this data point represents")
|
||
# Cumulative values at point_date
|
||
target = Column(Numeric(18, 2), default=0, comment="target line")
|
||
cum_closed = Column(Numeric(18, 2), default=0, comment="Closed Won by close_date ≤ point_date")
|
||
cum_commit_sales = Column(Numeric(18, 2), default=0, comment="cum_closed + sales Commit projected")
|
||
cum_commit_ai = Column(Numeric(18, 2), default=0, comment="cum_closed + AI Commit projected")
|
||
cum_upside_sales = Column(Numeric(18, 2), default=0, comment="cum_closed + Commit + Upside projected")
|
||
|
||
# Versioning & lifecycle
|
||
version = Column(Integer, nullable=False, default=1, server_default=text('1'))
|
||
is_active = Column(Boolean, nullable=False, default=True, server_default=text('1'), comment="1=active, 0=superseded")
|
||
superseded_at = Column(DateTime, default=None)
|
||
superseded_by = Column(String(100), default=None)
|
||
# Snapshot dimension
|
||
as_of_date = Column(Date, nullable=False, comment="run_date when this snapshot was computed")
|
||
data_cutoff_date = Column(Date, default=None, comment="Data cut-off (inclusive). When NULL, treat as_of_date as cut-off.")
|
||
# Computation metadata
|
||
computed_at = Column(DateTime, nullable=False)
|
||
execution_id = Column(String(100), default=None)
|
||
|
||
__table_args__ = (
|
||
Index(
|
||
"idx_ts_lookup",
|
||
"period_type",
|
||
"period",
|
||
"scope_type",
|
||
"scope_id",
|
||
"granularity",
|
||
"as_of_date",
|
||
"point_date",
|
||
"is_active",
|
||
),
|
||
Index("idx_ts_version", "period", "version", "is_active"),
|
||
Index("idx_ts_as_of", "as_of_date", "is_active"),
|
||
{'comment': 'Pre-computed cumulative achievement time-series for line chart'},
|
||
)
|
||
|
||
|
||
class CRMKAAccounts(CRMBase):
|
||
__tablename__ = 'crm_ka_accounts'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
account_id = Column(String(255), nullable=False, comment='客户唯一性ID')
|
||
account_name = Column(String(255), nullable=False, comment='客户名称')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
status = Column(String(20), default='active', comment='状态: active, inactive')
|
||
flag = Column(String(255), comment='标志位,区分不同类别的KA客户')
|
||
|
||
# Define indexes to match database schema
|
||
__table_args__ = (
|
||
Index('idx_account', 'account_id'),
|
||
{'comment': 'KA客户表'},
|
||
)
|
||
|
||
|
||
class CRMKeyOpportunities(CRMBase):
|
||
__tablename__ = 'crm_key_opportunities'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
opportunity_id = Column(String(255), nullable=False, comment='商机唯一性ID')
|
||
opportunity_name = Column(String(255), nullable=False, comment='商机名称')
|
||
account_id = Column(String(255), nullable=False, comment='客户唯一性ID')
|
||
account_name = Column(String(255), nullable=False, comment='客户名称')
|
||
opportunity_stage = Column(String(255), comment='商机阶段')
|
||
expected_closing_quarter = Column(String(50), comment='预期成交季度')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
status = Column(String(20), default='active', server_default=text("'active'"), comment='状态: active, inactive')
|
||
flag = Column(String(255), comment='标志位,区分不同用途的关键商机')
|
||
|
||
# Define indexes to match database schema
|
||
__table_args__ = (
|
||
Index('idx_account_id', 'account_id'),
|
||
Index('idx_expected_closing_quarter', 'expected_closing_quarter'),
|
||
Index('idx_flag', 'flag'),
|
||
Index('idx_opportunity_id', 'opportunity_id'),
|
||
Index('idx_status', 'status'),
|
||
{'comment': '关键商机表'},
|
||
)
|
||
|
||
|
||
# NOTE: These tables are commented out as they are not currently used
|
||
# If needed in the future, uncomment and create migration
|
||
# class CRMStageConfig(CRMBase):
|
||
# __tablename__ = 'crm_stage_config'
|
||
#
|
||
# id = Column(Integer, primary_key=True, autoincrement=True)
|
||
# tenant_id = Column(String(50), nullable=True, comment='租户ID, 为空表示通用配置')
|
||
#
|
||
# # 具体阶段定义
|
||
# stage_code = Column(String(100), nullable=False, comment='具体阶段代码（租户特定）')
|
||
# stage_name = Column(String(200), nullable=False, comment='具体阶段显示名称')
|
||
# stage_order = Column(Integer, nullable=False, comment='阶段顺序（1,2,3...）')
|
||
#
|
||
# # 高层分类映射（用于 Review/Report 流程）
|
||
# high_level_category = Column(String(50), nullable=False, comment='高层分类（INITIATION: 销售启动阶段, QUALIFICATION: 资格确认阶段, EVALUATION: 评估阶段, NEGOTIATION: 谈判阶段, CLOSURE: 结束阶段）')
|
||
# is_final_stage = Column(Boolean, default=False, comment='是否最终阶段')
|
||
#
|
||
# # 映射配置
|
||
# source_stage_names = Column(Text, comment='源系统阶段名称映射（JSON格式）')
|
||
#
|
||
# # 业务配置
|
||
# # probability_percentage = Column(Numeric(5, 2), default=0, comment='成单概率百分比')
|
||
# # requires_approval = Column(Boolean, default=False, comment='是否需要审批')
|
||
#
|
||
# # 元数据
|
||
# is_active = Column(Boolean, default=True, comment='是否启用')
|
||
# create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
# update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
#
|
||
#
|
||
# class CRMStageTransitionRules(CRMBase):
|
||
# __tablename__ = 'crm_stage_transition_rules'
|
||
#
|
||
# id = Column(Integer, primary_key=True, autoincrement=True)
|
||
# tenant_id = Column(String(50), nullable=True, comment='租户ID, 为空表示通用配置')
|
||
# from_stage_code = Column(String(100), nullable=False, comment='起始阶段代码')
|
||
# to_stage_code = Column(String(100), nullable=False, comment='目标阶段代码')
|
||
# is_allowed = Column(Boolean, default=True, comment='是否允许转换')
|
||
# create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
|
||
|
||
class CRMTrackedFieldSnapshots(CRMBase):
|
||
__tablename__ = 'crm_tracked_field_snapshots'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
source_table = Column(String(255), comment='源数据表名')
|
||
source_id = Column(Integer, comment='源数据记录ID')
|
||
source_unique_id = Column(String(255), comment='源数据记录唯一性ID')
|
||
source_field_name = Column(String(255), comment='源数据字段名称')
|
||
last_processed_content = Column(Text, comment='最后处理内容')
|
||
last_processed_hash = Column(String(255), comment='最后处理哈希值')
|
||
last_processed_time = Column(DateTime, comment='最后处理时间')
|
||
delta_content = Column(Text, comment='增量内容')
|
||
flag = Column(String(255), comment='标志位,标记该数据被处理的任务')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Define indexes to match database schema
|
||
__table_args__ = (
|
||
Index('idx_unique_id', 'unique_id'),
|
||
{'comment': '跟踪字段快照表'},
|
||
)
|
||
|
||
|
||
class CRMBIReports(CRMBase):
|
||
__tablename__ = 'bi_reports'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
report_id = Column(String(255), nullable=False, comment='报告ID')
|
||
plan_id = Column(String(255), comment='执行计划ID')
|
||
execution_id = Column(String(255), nullable=False, unique=True, comment='执行ID, naming convention: <plan_id>_<version>')
|
||
report_type = Column(String(64), nullable=False, comment='报告类型，例如: previsit, postvisit, quarterly, etc.')
|
||
account_id = Column(String(255), comment='关联客户唯一ID，用于避免报告查询时解析report_content')
|
||
|
||
# General Information
|
||
report_name = Column(String(255), comment='报告名称')
|
||
report_status = Column(String(32), default='published', server_default=text("'published'"), comment='报告状态: draft, published, archived')
|
||
|
||
# Report Content - Stored as JSON to maintain flexibility
|
||
report_content = Column(MEDIUMTEXT, comment='报告内容，包含所有分析结果')
|
||
report_content_en = Column(MEDIUMTEXT, comment='报告内容英文')
|
||
report_content_multi_lang = Column(MEDIUMTEXT, comment='报告内容(多语言)')
|
||
report_metadata = Column(Text, comment='报告元数据，例如生成参数、来源等')
|
||
|
||
# Tracking and Audit
|
||
version = Column(Integer, default=1, server_default=text('1'), comment='报告版本号')
|
||
created_by = Column(String(64), comment='创建人')
|
||
updated_by = Column(String(64), comment='更新人')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Define indexes to match database schema
|
||
__table_args__ = (
|
||
Index('idx_created_at', 'created_at'),
|
||
Index('idx_execution_id', 'execution_id'),
|
||
Index('idx_report_id', 'report_id'),
|
||
Index('idx_report_type', 'report_type'),
|
||
Index('idx_bi_reports_type_account_created', 'report_type', 'account_id', 'created_at'),
|
||
{'comment': '销售报告表'},
|
||
)
|
||
|
||
|
||
class CRMAccountReviewExecutionIndex(CRMBase):
|
||
__tablename__ = 'crm_account_review_execution_index'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
account_id = Column(String(255), nullable=False, unique=True, comment='客户唯一性ID')
|
||
execution_id = Column(String(255), nullable=False, comment='最新review2报告的execution_id')
|
||
report_id = Column(String(255), comment='报告ID')
|
||
plan_id = Column(String(255), comment='执行计划ID')
|
||
report_created_time = Column(DateTime, nullable=False, comment='报告创建时间')
|
||
last_updated_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='索引最后更新时间')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='索引创建时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_execution_id', 'execution_id'),
|
||
Index('idx_report_created_time', 'report_created_time'),
|
||
# Note: account_id has unique=True which automatically creates an index
|
||
{'comment': '客户复盘执行索引表'},
|
||
)
|
||
|
||
|
||
class CRMOpportunityReviewExecutionIndex(CRMBase):
|
||
__tablename__ = 'crm_opportunity_review_execution_index'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
opportunity_id = Column(String(255), nullable=False, unique=True, comment='商机唯一性ID')
|
||
execution_id = Column(String(255), nullable=False, comment='关联账户的最新review2报告的execution_id')
|
||
account_id = Column(String(255), comment='关联的账户ID')
|
||
report_id = Column(String(255), comment='报告ID')
|
||
plan_id = Column(String(255), comment='执行计划ID')
|
||
report_created_time = Column(DateTime, nullable=False, comment='报告创建时间')
|
||
last_updated_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='索引最后更新时间')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='索引创建时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_execution_id', 'execution_id'),
|
||
Index('idx_account_id', 'account_id'),
|
||
Index('idx_report_created_time', 'report_created_time'),
|
||
{'comment': '商机复盘执行索引表'},
|
||
)
|
||
|
||
|
||
class CRMAccountOpportunityAssessment(CRMBase):
|
||
__tablename__ = 'crm_account_opportunity_assessment'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
assessment_date = Column(Date, nullable=False, comment='评估日期')
|
||
account_id = Column(String(255), comment='客户ID')
|
||
account_name = Column(String(255), comment='客户名称')
|
||
opportunity_id = Column(String(255), comment='商机ID（NULL表示未关联商机）')
|
||
opportunity_name = Column(String(255), comment='商机名称（NULL表示未关联商机）')
|
||
assessment_flag = Column(Text, comment='评估结果(red/yellow/green)')
|
||
assessment_description = Column(Text, comment='评估描述')
|
||
assessment_description_en = Column(Text, comment='评估描述(英文)')
|
||
customer_type = Column(String(50), comment='客户类型(end_customer/partner)')
|
||
account_level = Column(String(255), comment='客户等级')
|
||
is_first_visit = Column(Boolean, comment='是否首次拜访')
|
||
follow_up_note = Column(Text, comment='销售跟进记录')
|
||
follow_up_note_en = Column(Text, comment='销售跟进记录(英文)')
|
||
follow_up_next_step = Column(Text, comment='销售跟进下一步')
|
||
follow_up_next_step_en = Column(Text, comment='销售跟进下一步(英文)')
|
||
correlation_id = Column(String(255), comment='关联ID，用于链接到部门/公司汇总')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_assessment_date', 'assessment_date'),
|
||
Index('idx_account_opportunity_date', 'account_id', 'opportunity_id', 'assessment_date'),
|
||
Index('idx_opportunity_id', 'opportunity_id'),
|
||
Index('idx_correlation_id', 'correlation_id'),
|
||
{'comment': '客户商机评估表'},
|
||
)
|
||
|
||
|
||
class CRMDepartmentDailySummary(CRMBase):
|
||
__tablename__ = 'crm_department_daily_summary'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
report_date = Column(Date, nullable=False, comment='日期')
|
||
summary_type = Column(String(50), nullable=False, comment='汇总类型(department/company)')
|
||
department_id = Column(String(255), comment='部门ID（仅当summary_type=department时）')
|
||
department_name = Column(String(255), comment='部门名称（仅当summary_type=department时）')
|
||
assessment_red_count = Column(Integer, default=0, server_default=text('0'), comment='评估为red的次数')
|
||
assessment_yellow_count = Column(Integer, default=0, server_default=text('0'), comment='评估为yellow的次数')
|
||
assessment_green_count = Column(Integer, default=0, server_default=text('0'), comment='评估为green的次数')
|
||
total_assessments = Column(Integer, default=0, server_default=text('0'), comment='总评估数')
|
||
total_first_visit = Column(Integer, default=0, server_default=text('0'), comment='总首次拜访数')
|
||
total_multi_visit = Column(Integer, default=0, server_default=text('0'), comment='总多次拜访数')
|
||
# End customer statistics - Total (跟进总计)
|
||
end_customer_total_red_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户总计评估为red的次数')
|
||
end_customer_total_yellow_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户总计评估为yellow的次数')
|
||
end_customer_total_green_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户总计评估为green的次数')
|
||
end_customer_total_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户总计数量')
|
||
# End customer statistics - First visit (首次跟进)
|
||
end_customer_first_visit_red_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户首次跟进评估为red的次数')
|
||
end_customer_first_visit_yellow_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户首次跟进评估为yellow的次数')
|
||
end_customer_first_visit_green_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户首次跟进评估为green的次数')
|
||
end_customer_first_visit_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户首次跟进数量')
|
||
# End customer statistics - Regular visit (多次跟进)
|
||
end_customer_regular_visit_red_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户多次跟进评估为red的次数')
|
||
end_customer_regular_visit_yellow_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户多次跟进评估为yellow的次数')
|
||
end_customer_regular_visit_green_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户多次跟进评估为green的次数')
|
||
end_customer_regular_visit_count = Column(Integer, default=0, server_default=text('0'), comment='最终客户多次跟进数量')
|
||
# Partner statistics - Total (跟进总计)
|
||
partner_total_count = Column(Integer, default=0, server_default=text('0'), comment='合作伙伴总计数量')
|
||
# Partner statistics - First visit (首次跟进)
|
||
partner_first_visit_count = Column(Integer, default=0, server_default=text('0'), comment='合作伙伴首次跟进数量')
|
||
# Partner statistics - Regular visit (多次跟进)
|
||
partner_regular_visit_count = Column(Integer, default=0, server_default=text('0'), comment='合作伙伴多次跟进数量')
|
||
# Partner statistics - Assessment counts (评估次数)
|
||
partner_red_count = Column(Integer, default=0, server_default=text('0'), comment='合作伙伴评估为red的次数')
|
||
partner_yellow_count = Column(Integer, default=0, server_default=text('0'), comment='合作伙伴评估为yellow的次数')
|
||
partner_green_count = Column(Integer, default=0, server_default=text('0'), comment='合作伙伴评估为green的次数')
|
||
# Summary content
|
||
summary_content = Column(Text, comment='汇总内容（中文）')
|
||
summary_content_en = Column(Text, comment='汇总内容（英文）')
|
||
summary_first_visit = Column(Text, comment='首次拜访汇总内容（中文）')
|
||
summary_regular_visit = Column(Text, comment='多次拜访汇总内容（中文）')
|
||
summary_red = Column(Text, comment='红灯评估汇总（中文；【首次跟进】与【多次跟进】分段）')
|
||
summary_yellow = Column(Text, comment='黄灯评估汇总（中文；【首次跟进】与【多次跟进】分段）')
|
||
summary_green = Column(Text, comment='绿灯评估汇总（中文；【首次跟进】与【多次跟进】分段）')
|
||
key_highlights = Column(Text, comment='关键亮点（JSON格式）')
|
||
key_concerns = Column(Text, comment='关键关注点（JSON格式）')
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_report_date_type', 'report_date', 'summary_type'),
|
||
Index('idx_department_date', 'department_id', 'report_date'),
|
||
Index('idx_unique_id', 'unique_id'),
|
||
{'comment': '部门/公司日报汇总表'},
|
||
)
|
||
|
||
|
||
class CRMOpportunityStageStayDaily(CRMBase):
|
||
"""FR-ST-1: Idempotent daily ledger — one row per (opportunity, stage, date). Source of truth for bucket."""
|
||
|
||
__tablename__ = "crm_opportunity_stage_stay_daily"
|
||
|
||
opportunity_id = Column(String(255), primary_key=True, comment="Opportunity id (crm_opportunities.unique_id)")
|
||
opportunity_stage = Column(String(255), primary_key=True, comment="Stage name (non-closed stages only)")
|
||
snapshot_date = Column(Date, primary_key=True, comment="Date this (opp, stage) was observed in snapshot")
|
||
|
||
__table_args__ = (
|
||
Index("idx_snapshot_date", "snapshot_date"),
|
||
{'comment': 'FR-ST-1: Idempotent daily ledger for stage stay; bucket is derived from this table'},
|
||
)
|
||
|
||
|
||
class CRMOpportunityStageStayBucket(CRMBase):
|
||
"""FR-ST-1: Per-opportunity per-stage stay day bucket for 商机停留时间变化. Recomputed from daily ledger."""
|
||
|
||
__tablename__ = "crm_opportunity_stage_stay_bucket"
|
||
|
||
opportunity_id = Column(String(255), primary_key=True, comment="Opportunity id (crm_opportunities.unique_id)")
|
||
opportunity_stage = Column(String(255), primary_key=True, comment="Stage name (non-closed stages only)")
|
||
stay_days = Column(Integer, nullable=False, default=0, server_default=text('0'), comment="Cumulative days in this stage (bucket count)")
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index("idx_updated_at", "updated_at"),
|
||
{'comment': '商机阶段停留时间汇总表'},
|
||
)
|
||
|
||
|
||
class CRMPlaybookStageMetrics(CRMBase):
|
||
"""
|
||
Stage metrics configuration for AI FCST evaluation.
|
||
|
||
Stores average stay days and other metrics per stage per playbook version.
|
||
Initially populated with fixed/default values, later updated from historical data.
|
||
"""
|
||
__tablename__ = 'crm_playbook_stage_metrics'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（UUID）')
|
||
|
||
# Playbook identification (supports multiple playbook versions)
|
||
handbook_id = Column(String(64), nullable=False, comment='销售手册ID，关联diagnostic_playbook')
|
||
|
||
# Stage identification
|
||
sales_stage = Column(String(255), nullable=False, comment='销售阶段名称')
|
||
stage_sequence = Column(Integer, comment='阶段顺序，用于计算剩余阶段所需时间')
|
||
|
||
# Metrics data
|
||
avg_stay_days = Column(Integer, nullable=False, default=0, server_default=text('0'),
|
||
comment='平均停留天数（初始为固定值，后续从历史数据计算）')
|
||
median_stay_days = Column(Integer, comment='中位数停留天数（可选）')
|
||
min_stay_days = Column(Integer, comment='最小停留天数（可选）')
|
||
max_stay_days = Column(Integer, comment='最大停留天数（可选）')
|
||
sample_size = Column(Integer, comment='计算平均值所用的样本数量')
|
||
|
||
# Data source tracking
|
||
data_source = Column(String(50), default='fixed', server_default=text("'fixed'"),
|
||
comment='数据来源：fixed(固定值)/calculated(历史计算)/manual(人工设置)')
|
||
calculation_period_start = Column(Date, comment='计算时间段起始（仅data_source=calculated时有效）')
|
||
calculation_period_end = Column(Date, comment='计算时间段结束')
|
||
|
||
# Status and lifecycle
|
||
is_active = Column(Boolean, default=True, server_default=text('1'), comment='是否启用')
|
||
version = Column(String(32), default='1.0', server_default=text("'1.0'"), comment='配置版本')
|
||
|
||
# Metadata
|
||
description = Column(Text, comment='备注说明')
|
||
created_by = Column(String(255), comment='创建人')
|
||
updated_by = Column(String(255), comment='更新人')
|
||
|
||
# Timestamps
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
# Indexes for efficient querying
|
||
__table_args__ = (
|
||
# Primary lookup: find metrics for a stage in a playbook
|
||
Index('idx_handbook_stage', 'handbook_id', 'sales_stage', unique=True),
|
||
# Get all stages for a playbook in order
|
||
Index('idx_handbook_sequence', 'handbook_id', 'stage_sequence'),
|
||
# Filter active records
|
||
Index('idx_handbook_active', 'handbook_id', 'is_active'),
|
||
# Lookup by unique_id
|
||
Index('idx_unique_id', 'unique_id'),
|
||
{'comment': '销售阶段平均停留时间表，用于AI FCST评估'},
|
||
)
|
||
|
||
# =============================================================================
|
||
# Weekly Leader Review Models
|
||
# =============================================================================
|
||
|
||
class CRMReviewDepartment(CRMBase):
|
||
"""Review department configuration"""
|
||
__tablename__ = 'crm_review_department'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
|
||
# Department reference
|
||
department_id = Column(String(255), nullable=False, comment='FK → crm_department.unique_id')
|
||
department_name = Column(String(255), comment='Department name (denormalized)')
|
||
parent_department_id = Column(String(255), comment='Parent department ID')
|
||
|
||
# Configuration
|
||
is_active = Column(Boolean, default=True, server_default=text('1'), comment='Whether review is enabled for this dept')
|
||
review_frequency = Column(String(32), default='weekly', comment='weekly/monthly/quarterly')
|
||
include_sub_departments = Column(Boolean, default=True, server_default=text('1'), comment='Whether to include sub-depts in review')
|
||
|
||
# Metadata
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_by = Column(String(255), comment='User who added this dept to review list')
|
||
|
||
__table_args__ = (
|
||
Index('idx_department_id', 'department_id'),
|
||
Index('idx_active', 'is_active'),
|
||
Index('idx_parent_id', 'parent_department_id'),
|
||
{'comment': '复盘部门配置表'},
|
||
)
|
||
|
||
|
||
class CRMReviewSession(CRMBase):
|
||
"""Review session registry with 4-phase lifecycle"""
|
||
__tablename__ = 'crm_review_session'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='Unique session identifier (UUID)')
|
||
session_name = Column(String(255), comment='Display name')
|
||
|
||
# Scope: Each session is bound to one department (with sub-depts)
|
||
department_id = Column(String(255), nullable=False, comment='FK → crm_department.unique_id')
|
||
department_name = Column(String(255), comment='Department name')
|
||
|
||
# Classification
|
||
review_type = Column(String(64), nullable=False, default='weekly_leader', comment='Review type')
|
||
period_type = Column(String(32), nullable=False, comment='weekly/monthly/quarterly')
|
||
period = Column(String(32), nullable=False, comment='Period identifier (e.g., 2026-W10)')
|
||
fiscal_year = Column(String(16), comment='Fiscal year')
|
||
|
||
# ═══════════════════════════════════════════════════════════════════════
|
||
# LIFECYCLE STATE (renamed from 'status' to 'stage')
|
||
# Stage represents the major milestone in the review lifecycle
|
||
# ═══════════════════════════════════════════════════════════════════════
|
||
stage = Column(String(32), nullable=False, default='initial_edit',
|
||
comment='initial_edit/first_calculating/first_calc_ready/lead_review/second_calculating/completed. '
|
||
'Note: FCST eval starts async at T1 during initial_edit (background optimization)')
|
||
|
||
# ═══════════════════════════════════════════════════════════════════════
|
||
# EDIT CONTROL (merged from is_editable + edit_phase)
|
||
# review_phase controls whether editing is currently allowed
|
||
# ═══════════════════════════════════════════════════════════════════════
|
||
review_phase = Column(String(32), default='not_started',
|
||
comment='not_started/edit/closed - controls if attendees can edit')
|
||
|
||
# T1-T4 configurable times
|
||
t1_time = Column(DateTime, nullable=False, comment='T1: Session launch')
|
||
t2_time = Column(DateTime, nullable=False, comment='T2: First calc')
|
||
t3_time = Column(DateTime, nullable=False, comment='T3: Open to lead')
|
||
t4_time = Column(DateTime, nullable=False, comment='T4: Second calc')
|
||
|
||
# Phase tracking
|
||
initial_window_open_time = Column(DateTime, comment='When initial edit window opened')
|
||
initial_window_close_time = Column(DateTime, comment='When initial edit window closed')
|
||
first_calc_start_time = Column(DateTime)
|
||
first_calc_end_time = Column(DateTime)
|
||
first_calc_execution_id = Column(String(255))
|
||
meeting_opened_by = Column(String(255))
|
||
meeting_opened_by_id = Column(String(255))
|
||
meeting_opened_at = Column(DateTime)
|
||
meeting_closed_at = Column(DateTime)
|
||
meeting_open_count = Column(Integer, default=0)
|
||
meeting_total_duration_minutes = Column(Integer, default=0)
|
||
second_calc_start_time = Column(DateTime)
|
||
second_calc_end_time = Column(DateTime)
|
||
second_calc_execution_id = Column(String(255))
|
||
|
||
# Launcher
|
||
launched_by = Column(String(255))
|
||
launched_by_id = Column(String(255))
|
||
plan_id = Column(String(255))
|
||
|
||
# Time dimensions
|
||
report_date = Column(Date, nullable=False)
|
||
report_week_of_year = Column(Integer)
|
||
report_month_of_year = Column(Integer)
|
||
report_quarter_of_year = Column(Integer)
|
||
report_year = Column(Integer, nullable=False)
|
||
|
||
# Period range for display
|
||
period_start = Column(Date, nullable=False, comment='Start date of the review period')
|
||
period_end = Column(Date, nullable=False, comment='End date of the review period')
|
||
|
||
# FY Quarter for efficient querying (e.g., FY26Q1)
|
||
report_fy_quarter = Column(String(10), comment='Report fiscal year quarter for performance queries')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_unique_id', 'unique_id'),
|
||
Index('idx_stage', 'stage'),
|
||
Index('idx_review_phase', 'review_phase'),
|
||
Index('idx_report_year_week', 'report_year', 'report_week_of_year'),
|
||
Index('idx_department_id', 'department_id'),
|
||
Index('idx_report_fy_quarter', 'report_fy_quarter'),
|
||
Index('idx_t1_time', 't1_time'),
|
||
Index('idx_t2_time', 't2_time'),
|
||
Index('idx_t3_time', 't3_time'),
|
||
Index('idx_t4_time', 't4_time'),
|
||
{'comment': '复盘会话表'},
|
||
)
|
||
|
||
|
||
class CRMReviewAttendee(CRMBase):
|
||
"""Attendees per review session with submission tracking"""
|
||
__tablename__ = 'crm_review_attendee'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='Unique identifier')
|
||
|
||
session_id = Column(String(255), nullable=False, comment='FK → crm_review_session.unique_id')
|
||
user_id = Column(String(255), nullable=False, comment='User ID')
|
||
crm_user_id = Column(String(255), nullable=False, comment='CRM user ID (matches opportunity.owner_id)')
|
||
|
||
user_name = Column(String(255), comment='User name')
|
||
department_id = Column(String(255), comment='Department ID')
|
||
department_name = Column(String(255), comment='Department name')
|
||
is_leader = Column(Boolean, default=False, comment='Is department lead')
|
||
is_primary_dept = Column(Boolean, default=False, comment='Is primary department')
|
||
|
||
# Submission tracking
|
||
has_submitted = Column(Boolean, default=False, server_default=text('0'))
|
||
submitted_at = Column(DateTime)
|
||
submission_count = Column(Integer, default=0, server_default=text('0'))
|
||
modification_count = Column(Integer, default=0, server_default=text('0'))
|
||
last_modified_at = Column(DateTime)
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_session_user', 'session_id', 'user_id', unique=True),
|
||
Index('idx_session_id', 'session_id'),
|
||
Index('idx_crm_user_id', 'crm_user_id'),
|
||
Index('idx_has_submitted', 'has_submitted'),
|
||
{'comment': '复盘参与者表'},
|
||
)
|
||
|
||
|
||
class CRMReviewOppBranchSnapshot(CRMBase):
|
||
"""Owner-based branch snapshot (shared across sessions via owner_id)"""
|
||
__tablename__ = 'crm_review_opp_branch_snapshot'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='Unique record ID')
|
||
|
||
# Owner-based identification (not session-bound)
|
||
opportunity_id = Column(String(255), nullable=False)
|
||
owner_id = Column(String(255), nullable=True, comment='Owner crm_user_id')
|
||
owner_name = Column(String(255))
|
||
owner_department_id = Column(String(255))
|
||
owner_department_name = Column(String(255))
|
||
|
||
snapshot_period = Column(String(32), nullable=False, comment='Period (e.g., 2026-W10)')
|
||
snapshot_date = Column(Date, nullable=False)
|
||
|
||
# Opportunity info
|
||
account_id = Column(String(255))
|
||
account_name = Column(String(255))
|
||
opportunity_name = Column(String(255))
|
||
|
||
# Current values (editable)
|
||
forecast_type = Column(String(255))
|
||
forecast_amount = Column(Numeric(18, 2))
|
||
forecast_amount_source = Column(String(64))
|
||
opportunity_stage = Column(String(255))
|
||
expected_closing_date = Column(String(255))
|
||
expected_closing_month = Column(String(50), comment='标准化预计成交月（如FY26M02）')
|
||
|
||
# Baseline (frozen at T2)
|
||
baseline_forecast_type = Column(String(255))
|
||
baseline_forecast_amount = Column(Numeric(18, 2))
|
||
baseline_forecast_amount_source = Column(String(64))
|
||
baseline_opportunity_stage = Column(String(255))
|
||
baseline_expected_closing_date = Column(String(255))
|
||
baseline_expected_closing_quarter = Column(String(50))
|
||
baseline_expected_closing_month = Column(String(50), comment='冻结基线预计成交月（如FY26M02）')
|
||
baseline_frozen_at = Column(DateTime)
|
||
|
||
# CRM originals (at T1)
|
||
crm_forecast_type = Column(String(255))
|
||
crm_forecast_amount = Column(Numeric(18, 2))
|
||
crm_forecast_amount_source = Column(String(64))
|
||
crm_opportunity_stage = Column(String(255))
|
||
crm_expected_closing_date = Column(String(255))
|
||
crm_expected_closing_quarter = Column(String(50))
|
||
crm_expected_closing_month = Column(String(50), comment='CRM原始预计成交月标准化值（如FY26M02）')
|
||
crm_opportunity_type = Column(String(255), comment='CRM original opportunity type')
|
||
|
||
# AI Evaluation
|
||
ai_commit_1st = Column(String(32))
|
||
ai_stage_1st = Column(String(255))
|
||
ai_expected_closing_date_1st = Column(String(255))
|
||
ai_evaluated_1st_at = Column(DateTime)
|
||
ai_commit_2nd = Column(String(32))
|
||
ai_stage_2nd = Column(String(255))
|
||
ai_expected_closing_date_2nd = Column(String(255))
|
||
ai_evaluated_2nd_at = Column(DateTime)
|
||
|
||
# AI Evaluation Summary (latest from 1st or 2nd)
|
||
ai_commit = Column(String(32), comment='Latest AI commit assessment (Commit/NotCommit)')
|
||
ai_stage = Column(String(255), comment='Latest AI stage assessment')
|
||
ai_expected_closing_date = Column(String(255), comment='Latest AI expected closing date')
|
||
ai_evaluated_at = Column(DateTime, comment='Timestamp of latest AI evaluation')
|
||
ai_eval_source = Column(String(10), comment='Source of AI eval: 1st or 2nd')
|
||
|
||
# Stage Stay
|
||
stage_stay = Column(Integer, comment='Days in current stage (from crm_opportunity_stage_stay_bucket)')
|
||
|
||
# Context
|
||
expected_closing_quarter = Column(String(50))
|
||
close_date = Column(Date)
|
||
is_closed = Column(Boolean, default=False)
|
||
customer_type = Column(String(255))
|
||
|
||
# Change tracking
|
||
was_changed_to_commit = Column(Boolean, default=False)
|
||
was_modified = Column(Boolean, default=False)
|
||
|
||
# Modification tracking
|
||
last_modified_by = Column(String(255))
|
||
last_modified_by_id = Column(String(255))
|
||
modification_count = Column(Integer, default=0, server_default=text('0'))
|
||
initial_edit_modification_count = Column(Integer, default=0)
|
||
meeting_edit_modification_count = Column(Integer, default=0)
|
||
|
||
# Original CRM opportunity create_time (copied from crm_opportunities at snapshot time)
|
||
opp_create_time = Column(DateTime, nullable=True, comment='商机创建时间（来源CRM）')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_opp_period', 'opportunity_id', 'snapshot_period', unique=True),
|
||
Index('idx_owner_period', 'owner_id', 'snapshot_period'),
|
||
Index('idx_snapshot_period', 'snapshot_period'),
|
||
Index('idx_was_changed', 'was_changed_to_commit'),
|
||
Index('idx_ai_commit', 'ai_commit'),
|
||
Index('idx_stage_stay', 'stage_stay'),
|
||
{'comment': '复盘商机快照表'},
|
||
)
|
||
|
||
|
||
class CRMReviewOppBranchSnapshotCache(CRMBase):
|
||
"""Cache copy of crm_review_opp_branch_snapshot.
|
||
|
||
Populated once per period at session init (T1) as a point-in-time copy of
|
||
crm_review_opp_branch_snapshot. A separate 3rd-party sync service is
|
||
responsible for keeping this cache in sync with the source table after
|
||
initialization. Read-only consumers (e.g. the /performance/query API) read
|
||
from this cache to isolate aggregation queries from concurrent workflow
|
||
writes on the source table.
|
||
"""
|
||
__tablename__ = 'crm_review_opp_branch_snapshot_cache'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='Unique record ID')
|
||
|
||
# Owner-based identification (not session-bound)
|
||
opportunity_id = Column(String(255), nullable=False)
|
||
owner_id = Column(String(255), nullable=True, comment='Owner crm_user_id')
|
||
owner_name = Column(String(255))
|
||
owner_department_id = Column(String(255))
|
||
owner_department_name = Column(String(255))
|
||
|
||
snapshot_period = Column(String(32), nullable=False, comment='Period (e.g., 2026-W10)')
|
||
snapshot_date = Column(Date, nullable=False)
|
||
|
||
# Opportunity info
|
||
account_id = Column(String(255))
|
||
account_name = Column(String(255))
|
||
opportunity_name = Column(String(255))
|
||
|
||
# Current values (editable)
|
||
forecast_type = Column(String(255))
|
||
forecast_amount = Column(Numeric(18, 2))
|
||
forecast_amount_source = Column(String(64))
|
||
opportunity_stage = Column(String(255))
|
||
expected_closing_date = Column(String(255))
|
||
expected_closing_month = Column(String(50), comment='标准化预计成交月（如FY26M02）')
|
||
|
||
# Baseline (frozen at T2)
|
||
baseline_forecast_type = Column(String(255))
|
||
baseline_forecast_amount = Column(Numeric(18, 2))
|
||
baseline_forecast_amount_source = Column(String(64))
|
||
baseline_opportunity_stage = Column(String(255))
|
||
baseline_expected_closing_date = Column(String(255))
|
||
baseline_expected_closing_quarter = Column(String(50))
|
||
baseline_expected_closing_month = Column(String(50), comment='冻结基线预计成交月（如FY26M02）')
|
||
baseline_frozen_at = Column(DateTime)
|
||
|
||
# CRM originals (at T1)
|
||
crm_forecast_type = Column(String(255))
|
||
crm_forecast_amount = Column(Numeric(18, 2))
|
||
crm_forecast_amount_source = Column(String(64))
|
||
crm_opportunity_stage = Column(String(255))
|
||
crm_expected_closing_date = Column(String(255))
|
||
crm_expected_closing_quarter = Column(String(50))
|
||
crm_expected_closing_month = Column(String(50), comment='CRM原始预计成交月标准化值（如FY26M02）')
|
||
crm_opportunity_type = Column(String(255), comment='CRM original opportunity type')
|
||
|
||
# AI Evaluation
|
||
ai_commit_1st = Column(String(32))
|
||
ai_stage_1st = Column(String(255))
|
||
ai_expected_closing_date_1st = Column(String(255))
|
||
ai_evaluated_1st_at = Column(DateTime)
|
||
ai_commit_2nd = Column(String(32))
|
||
ai_stage_2nd = Column(String(255))
|
||
ai_expected_closing_date_2nd = Column(String(255))
|
||
ai_evaluated_2nd_at = Column(DateTime)
|
||
|
||
# AI Evaluation Summary (latest from 1st or 2nd)
|
||
ai_commit = Column(String(32), comment='Latest AI commit assessment (Commit/NotCommit)')
|
||
ai_stage = Column(String(255), comment='Latest AI stage assessment')
|
||
ai_expected_closing_date = Column(String(255), comment='Latest AI expected closing date')
|
||
ai_evaluated_at = Column(DateTime, comment='Timestamp of latest AI evaluation')
|
||
ai_eval_source = Column(String(10), comment='Source of AI eval: 1st or 2nd')
|
||
|
||
# Stage Stay
|
||
stage_stay = Column(Integer, comment='Days in current stage (from crm_opportunity_stage_stay_bucket)')
|
||
|
||
# Context
|
||
expected_closing_quarter = Column(String(50))
|
||
close_date = Column(Date)
|
||
is_closed = Column(Boolean, default=False)
|
||
customer_type = Column(String(255))
|
||
|
||
# Change tracking
|
||
# NOTE: On this cache table, `was_changed_to_commit` is owned exclusively
|
||
# by the 3rd-party sync service. It is set TRUE when a commit-affecting
|
||
# user edit lands in the cache, and is NOT written by any engine workflow
|
||
# (init copy and post-eval backfill both skip this column). The engine's
|
||
# own commit-flip detection lives on the source table column of the same
|
||
# name; the two values may legitimately diverge.
|
||
was_changed_to_commit = Column(Boolean, default=False)
|
||
was_modified = Column(Boolean, default=False)
|
||
|
||
# Modification tracking
|
||
last_modified_by = Column(String(255))
|
||
last_modified_by_id = Column(String(255))
|
||
modification_count = Column(Integer, default=0, server_default=text('0'))
|
||
initial_edit_modification_count = Column(Integer, default=0)
|
||
meeting_edit_modification_count = Column(Integer, default=0)
|
||
|
||
# Original CRM opportunity create_time (copied from crm_opportunities at snapshot time)
|
||
opp_create_time = Column(DateTime, nullable=True, comment='商机创建时间（来源CRM）')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_opp_period_cache', 'opportunity_id', 'snapshot_period', unique=True),
|
||
Index('idx_owner_period_cache', 'owner_id', 'snapshot_period'),
|
||
Index('idx_snapshot_period_cache', 'snapshot_period'),
|
||
Index('idx_was_changed_cache', 'was_changed_to_commit'),
|
||
Index('idx_ai_commit_cache', 'ai_commit'),
|
||
Index('idx_stage_stay_cache', 'stage_stay'),
|
||
{'comment': '复盘商机快照缓存表（T1初始化时同步生成，由第三方服务负责后续同步）'},
|
||
)
|
||
|
||
|
||
class CRMReviewKpiMetrics(CRMBase):
|
||
"""Structured KPI metrics per scope per review session with delta/rate"""
|
||
__tablename__ = 'crm_review_kpi_metrics'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
session_id = Column(String(255), nullable=False)
|
||
|
||
scope_type = Column(String(32), nullable=False)
|
||
scope_id = Column(String(255))
|
||
scope_name = Column(String(255))
|
||
parent_scope_id = Column(String(255))
|
||
|
||
metric_category = Column(String(64), nullable=False)
|
||
metric_name = Column(String(255), nullable=False)
|
||
|
||
# Values
|
||
metric_value = Column(Numeric(18, 4), comment='Current period value')
|
||
metric_value_prev = Column(Numeric(18, 4), comment='Previous period value')
|
||
metric_delta = Column(Numeric(18, 4), comment='Change value')
|
||
metric_rate = Column(Numeric(8, 4), comment='Rate 0-1 (e.g., 0.288 = 28.8%)')
|
||
metric_unit = Column(String(32))
|
||
metric_content = Column(Text)
|
||
metric_content_en = Column(Text)
|
||
|
||
calc_phase = Column(String(32), default='second')
|
||
period_type = Column(String(32))
|
||
period = Column(String(32))
|
||
report_date = Column(Date)
|
||
report_year = Column(Integer)
|
||
report_week_of_year = Column(Integer)
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('idx_session_scope_cat', 'session_id', 'scope_type', 'metric_category'),
|
||
Index('idx_session_scope_metric', 'session_id', 'scope_id', 'metric_name'),
|
||
Index('idx_period_scope_metric', 'period', 'scope_type', 'metric_name'),
|
||
{'comment': '复盘KPI指标表'},
|
||
)
|
||
|
||
|
||
class CRMReviewKpiMetricOppLink(CRMBase):
|
||
"""
|
||
Materialized link between an aggregated KPI metric row in
|
||
crm_review_kpi_metrics and the opportunity-snapshot rows in
|
||
crm_review_opp_branch_snapshot that contributed to its value.
|
||
|
||
Populated by ReviewSaveKPIsStep at calc time so the UI can drill down
|
||
from a KPI to underlying opps without re-running the calc business logic.
|
||
|
||
Only opp-derived achievement metrics are linked:
|
||
closed, commit_sales, commit_ai, upside_sales.
|
||
"""
|
||
__tablename__ = 'crm_review_kpi_metric_opp_link'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
kpi_metric_unique_id = Column(String(255), nullable=False, comment='Logical FK -> crm_review_kpi_metrics.unique_id')
|
||
snapshot_unique_id = Column(String(255), nullable=False, comment='Logical FK -> crm_review_opp_branch_snapshot.unique_id')
|
||
|
||
session_id = Column(String(255), nullable=False, comment='Redundant for filter')
|
||
opportunity_id = Column(String(255), nullable=False, comment='Redundant for filter')
|
||
snapshot_period = Column(String(32), nullable=False, comment='Redundant for filter (e.g., 2026-W15)')
|
||
scope_type = Column(String(32), nullable=False, comment='owner | department | company')
|
||
scope_id = Column(String(255), comment='Owner/department id; null for company')
|
||
metric_name = Column(String(255), nullable=False, comment='closed | commit_sales | commit_ai | upside_sales')
|
||
calc_phase = Column(String(32), nullable=False, comment='first | second')
|
||
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
update_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), onupdate=text('CURRENT_TIMESTAMP'), comment='更新时间')
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
Index('uniq_kpi_snapshot', 'kpi_metric_unique_id', 'snapshot_unique_id', unique=True),
|
||
Index('idx_kpi_metric_unique_id', 'kpi_metric_unique_id'),
|
||
Index('idx_session_metric', 'session_id', 'metric_name'),
|
||
Index('idx_opportunity_period', 'opportunity_id', 'snapshot_period'),
|
||
{'comment': '复盘KPI指标与商机快照关联表（用于UI下钻）'},
|
||
)
|
||
|
||
|
||
class CRMReviewAchievementChangeAnalysis(CRMBase):
|
||
"""
|
||
Single-row JSON storage for weekly leader achievement change analysis.
|
||
|
||
analysis_content stores the weekly-leader Step 14 UI contract:
|
||
- kpi_data
|
||
- perf_forecast_change
|
||
"""
|
||
|
||
__tablename__ = "crm_review_achievement_change_analysis"
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
session_id = Column(String(255), nullable=False)
|
||
calc_phase = Column(String(16), nullable=False)
|
||
snapshot_period = Column(String(32), nullable=False)
|
||
report_date = Column(Date)
|
||
|
||
analysis_content = Column(MEDIUMTEXT, nullable=False)
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
|
||
__table_args__ = (
|
||
Index(
|
||
"idx_review_change_analysis_session_phase",
|
||
"session_id",
|
||
"calc_phase",
|
||
),
|
||
Index(
|
||
"idx_review_change_analysis_snapshot_period",
|
||
"snapshot_period",
|
||
"calc_phase",
|
||
),
|
||
{"comment": "复盘业绩变化分析（JSON单表存储）"},
|
||
)
|
||
|
||
|
||
|
||
# =============================================================================
|
||
# Risk and Progress Tracking Models (Weekly Review)
|
||
# =============================================================================
|
||
|
||
class CRMReviewOppRiskProgress(CRMBase):
|
||
__tablename__ = 'crm_review_opp_risk_progress'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
# Nesting support: NULL for root-level records; set for RISK_PART children.
|
||
parent_id = Column(String(255), nullable=True, comment='父风险记录的 unique_id，NULL 表示根级别风险')
|
||
part_key = Column(String(64), nullable=True, comment='在父卡片内的逻辑分区标识，如 commit_risk / upside_fill / pipeline')
|
||
display_order = Column(SmallInteger, nullable=True, default=0, comment='在父卡片内的渲染顺序')
|
||
|
||
session_id = Column(String(255), nullable=False)
|
||
scope_type = Column(String(32), nullable=False)
|
||
scope_id = Column(String(255))
|
||
department_id = Column(String(255))
|
||
|
||
snapshot_id = Column(String(255))
|
||
opportunity_id = Column(String(255))
|
||
owner_id = Column(String(255))
|
||
|
||
record_type = Column(String(32), nullable=False)
|
||
type_code = Column(String(64), nullable=False)
|
||
type_name = Column(String(128), nullable=False)
|
||
category = Column(String(64))
|
||
level = Column(String(32))
|
||
severity = Column(String(16))
|
||
|
||
source = Column(String(128))
|
||
metric_name = Column(String(64))
|
||
|
||
ai_assessment = Column(String(255))
|
||
sales_assessment = Column(String(255))
|
||
|
||
judgment_rule = Column(Text)
|
||
gap_description = Column(Text)
|
||
detail_description = Column(Text)
|
||
summary = Column(Text)
|
||
solution = Column(Text)
|
||
|
||
evidence = Column(JSON)
|
||
|
||
financial_impact = Column(Numeric(18, 2))
|
||
previous_value = Column(Numeric(18, 2))
|
||
current_value = Column(Numeric(18, 2))
|
||
rate_of_change = Column(Numeric(8, 4))
|
||
|
||
status = Column(String(32), default='ACTIVE')
|
||
detected_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
resolved_at = Column(DateTime)
|
||
resolved_by = Column(String(255))
|
||
resolution_type = Column(String(32))
|
||
resolution_note = Column(Text)
|
||
|
||
calc_phase = Column(String(16), nullable=False)
|
||
snapshot_period = Column(String(32), nullable=False)
|
||
|
||
metadata_ = Column(JSON, name='metadata')
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
created_by = Column(String(255), default='system')
|
||
updated_by = Column(String(255), default='system')
|
||
|
||
__table_args__ = (
|
||
Index('idx_session_scope', 'session_id', 'scope_type', 'scope_id'),
|
||
Index('idx_department', 'department_id'),
|
||
Index('idx_opportunity', 'opportunity_id'),
|
||
Index('idx_owner', 'owner_id'),
|
||
Index('idx_status', 'status'),
|
||
Index('idx_type_code', 'type_code'),
|
||
Index('idx_record_type', 'record_type'),
|
||
Index('idx_detected_at', 'detected_at'),
|
||
Index('idx_snapshot_period', 'snapshot_period'),
|
||
Index('idx_parent_id', 'parent_id'),
|
||
UniqueConstraint('session_id', 'scope_type', 'scope_id', 'type_code', 'snapshot_period', 'calc_phase', name='uk_session_scope_type_period'),
|
||
UniqueConstraint('parent_id', 'part_key', name='uk_risk_part'),
|
||
{'comment': '风险与进展追踪表'},
|
||
)
|
||
|
||
|
||
class CRMReviewRiskCategory(CRMBase):
|
||
__tablename__ = 'crm_review_risk_category'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
code = Column(String(64), nullable=False, unique=True)
|
||
name_zh = Column(String(128), nullable=False)
|
||
name_en = Column(String(128))
|
||
|
||
category_group = Column(String(64))
|
||
default_level = Column(String(32))
|
||
default_severity = Column(String(16))
|
||
|
||
detection_source = Column(String(64))
|
||
detection_rules = Column(Text)
|
||
solution = Column(Text)
|
||
|
||
auto_resolve = Column(Boolean, default=True)
|
||
requires_acknowledgment = Column(Boolean, default=False)
|
||
|
||
is_active = Column(Boolean, default=True)
|
||
sort_order = Column(Integer, default=0)
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
|
||
__table_args__ = (
|
||
Index('idx_category_group', 'category_group'),
|
||
Index('idx_is_active', 'is_active'),
|
||
{'comment': '风险类别配置表'},
|
||
)
|
||
|
||
|
||
class CRMReviewProgressCategory(CRMBase):
|
||
__tablename__ = 'crm_review_progress_category'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
code = Column(String(64), nullable=False, unique=True)
|
||
name_zh = Column(String(128), nullable=False)
|
||
name_en = Column(String(128))
|
||
|
||
category_group = Column(String(64))
|
||
default_level = Column(String(32))
|
||
|
||
detection_source = Column(String(64))
|
||
detection_rules = Column(JSON)
|
||
|
||
is_active = Column(Boolean, default=True)
|
||
sort_order = Column(Integer, default=0)
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
|
||
__table_args__ = (
|
||
Index('idx_is_active', 'is_active'),
|
||
{'comment': '进展类别配置表'},
|
||
)
|
||
|
||
|
||
class CRMReviewProgressThreshold(CRMBase):
|
||
"""商机进展阈值配置表。"""
|
||
__tablename__ = 'crm_review_progress_threshold'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
scope_type = Column(String(32), nullable=False, comment='范围类型：department/company')
|
||
scope_id = Column(String(255), nullable=False, comment='范围ID：部门ID或company')
|
||
progress_code = Column(String(64), nullable=False, comment='进展编码，例如 MAJOR_NEW_ORDER')
|
||
threshold_value = Column(Numeric(18, 2), nullable=False, comment='阈值金额')
|
||
description = Column(Text, comment='阈值说明')
|
||
is_active = Column(Boolean, default=True, server_default=text('1'), comment='是否启用')
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
|
||
__table_args__ = (
|
||
Index('idx_scope_type_scope_id', 'scope_type', 'scope_id'),
|
||
Index('idx_progress_code_active', 'progress_code', 'is_active'),
|
||
UniqueConstraint('scope_type', 'scope_id', 'progress_code', name='uk_scope_progress_code'),
|
||
{'comment': '商机进展阈值配置表'},
|
||
)
|
||
|
||
|
||
class CRMReviewRiskAggregation(CRMBase):
|
||
__tablename__ = 'crm_review_risk_aggregation'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
session_id = Column(String(255), nullable=False)
|
||
scope_type = Column(String(32), nullable=False)
|
||
scope_id = Column(String(255), nullable=False)
|
||
scope_name = Column(String(255))
|
||
|
||
snapshot_period = Column(String(32), nullable=False)
|
||
calc_phase = Column(String(16), nullable=False)
|
||
|
||
total_risks = Column(Integer, default=0)
|
||
total_progress = Column(Integer, default=0)
|
||
critical_risks = Column(Integer, default=0)
|
||
high_risks = Column(Integer, default=0)
|
||
medium_risks = Column(Integer, default=0)
|
||
low_risks = Column(Integer, default=0)
|
||
open_risks = Column(Integer, default=0)
|
||
resolved_risks = Column(Integer, default=0)
|
||
|
||
total_risk_amount = Column(Numeric(18, 2), default=0)
|
||
total_progress_amount = Column(Numeric(18, 2), default=0)
|
||
|
||
risk_breakdown = Column(JSON)
|
||
progress_breakdown = Column(JSON)
|
||
|
||
previous_period_risks = Column(Integer, default=0)
|
||
risk_trend = Column(String(16))
|
||
|
||
computed_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
|
||
__table_args__ = (
|
||
Index('idx_session_scope', 'session_id', 'scope_type', 'scope_id'),
|
||
Index('idx_period_phase', 'snapshot_period', 'calc_phase'),
|
||
Index('idx_computed_at', 'computed_at'),
|
||
UniqueConstraint('session_id', 'scope_type', 'scope_id', 'snapshot_period', 'calc_phase', name='uk_session_scope_period'),
|
||
{'comment': '风险聚合汇总表'},
|
||
)
|
||
|
||
|
||
class CRMReviewRiskOpportunityRelation(CRMBase):
|
||
"""非商机级风险到关联商机的关系表。"""
|
||
__tablename__ = 'crm_review_risk_opportunity_relation'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
risk_unique_id = Column(String(255), nullable=False, comment='关联 crm_review_opp_risk_progress.unique_id')
|
||
type_name = Column(String(128), comment='冗余的风险类型名称，用于加速查询')
|
||
session_id = Column(String(255), nullable=False)
|
||
snapshot_period = Column(String(32), nullable=False)
|
||
calc_phase = Column(String(16), nullable=False)
|
||
|
||
opportunity_id = Column(String(255), nullable=False)
|
||
owner_id = Column(String(255))
|
||
department_id = Column(String(255))
|
||
|
||
relation_reason = Column(Text)
|
||
relation_rank = Column(Integer)
|
||
relation_weight = Column(Numeric(8, 4))
|
||
metadata_ = Column(JSON, name='metadata')
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
|
||
|
||
__table_args__ = (
|
||
Index('idx_risk_unique_id', 'risk_unique_id'),
|
||
Index('idx_opportunity_id', 'opportunity_id'),
|
||
Index('idx_session_period_phase', 'session_id', 'snapshot_period', 'calc_phase'),
|
||
UniqueConstraint('risk_unique_id', 'opportunity_id', name='uk_risk_opp_relation'),
|
||
{'comment': '风险与商机关联关系表'},
|
||
)
|
||
|
||
|
||
class CRMReviewOppComment(CRMBase):
|
||
"""One row per comment on an opp review page.
|
||
|
||
Supports:
|
||
- Target anchoring: target_type + target_id point to a CRMReviewOppRiskProgress row
|
||
(record_type = RISK / PROGRESS / OPP_SUMMARY / OPP_REQS_INSIGHT), or NULL for a
|
||
general opp-level comment.
|
||
- Reply threading: parent_id references another CRMReviewOppComment.unique_id; NULL
|
||
means top-level. Max nesting depth is enforced at the service layer (MAX_REPLY_DEPTH=20).
|
||
- Per-comment ownership: only submitted_by may edit or delete the comment.
|
||
"""
|
||
|
||
__tablename__ = 'crm_review_opp_comment'
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='Stable comment UUID')
|
||
|
||
# ── Context ──────────────────────────────────────────────────────────────
|
||
session_id = Column(String(255), nullable=False, comment='FK → crm_review_session.unique_id')
|
||
opportunity_id = Column(String(255), nullable=False, comment='CRM opportunity ID')
|
||
|
||
# ── Target anchoring ─────────────────────────────────────────────────────
|
||
target_type = Column(
|
||
String(64), nullable=True,
|
||
comment='RiskProgressType value: RISK | PROGRESS | OPP_SUMMARY | OPP_REQS_INSIGHT; NULL = general opp comment',
|
||
)
|
||
target_id = Column(
|
||
String(255), nullable=True,
|
||
comment='CRMReviewOppRiskProgress.unique_id of the anchored entity',
|
||
)
|
||
|
||
# ── Threading ────────────────────────────────────────────────────────────
|
||
parent_id = Column(
|
||
String(255), nullable=True,
|
||
comment='CRMReviewOppComment.unique_id of parent comment; NULL = top-level',
|
||
)
|
||
|
||
# ── Content ──────────────────────────────────────────────────────────────
|
||
content = Column(Text, nullable=False, comment='Comment body text')
|
||
is_deleted = Column(
|
||
Boolean, nullable=False, default=False, server_default=text('0'),
|
||
comment='Soft-delete flag; content is blanked on delete',
|
||
)
|
||
is_edited = Column(
|
||
Boolean, nullable=False, default=False, server_default=text('0'),
|
||
comment='Set to True on first edit; UI shows "(edited)" badge',
|
||
)
|
||
|
||
# ── Authorship ───────────────────────────────────────────────────────────
|
||
submitted_by = Column(String(255), nullable=False, comment='CRM user ID of the submitter')
|
||
submitted_by_name = Column(String(255), nullable=False, comment='Display name at submit time')
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
UniqueConstraint('unique_id', name='uk_opp_comment_unique_id'),
|
||
Index('idx_opp_comment_session_opp', 'session_id', 'opportunity_id'),
|
||
Index('idx_opp_comment_target', 'target_type', 'target_id'),
|
||
Index('idx_opp_comment_parent', 'parent_id'),
|
||
Index('idx_opp_comment_submitter', 'submitted_by'),
|
||
{'comment': '复盘商机评论表（含回复树与目标锚定）'},
|
||
)
|
||
|
||
|
||
class CRMReviewAttendeeTodoStats(CRMBase):
|
||
"""
|
||
Per-attendee todo statistics for a review session, computed by the KPI calc workflow.
|
||
|
||
Stores 5 metrics per (session_id, owner_id, calc_phase):
|
||
- total_this_week: todos whose due_date falls in [period_start, period_end]
|
||
- completed_this_week: above with ai_status = COMPLETED
|
||
- incomplete_this_week: total_this_week - completed_this_week
|
||
- historical_overdue_completed_this_week: due_date < period_start AND completion_date in period AND ai_status = COMPLETED
|
||
- current_total_overdue: due_date < report_date AND ai_status NOT IN (COMPLETED, CANCELLED)
|
||
|
||
Append-only / replaced per calc: existing rows for (session_id, calc_phase) are
|
||
deleted and re-inserted on each workflow run, so updated_at is not needed.
|
||
"""
|
||
__tablename__ = 'crm_review_attendee_todo_stats'
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID')
|
||
|
||
# Session context
|
||
session_id = Column(String(255), nullable=False, comment='FK → crm_review_session.unique_id')
|
||
owner_id = Column(String(255), nullable=False, comment='Attendee CRM user ID (matches crm_review_attendee.crm_user_id)')
|
||
owner_name = Column(String(255), comment='Attendee display name')
|
||
department_id = Column(String(255), comment='Attendee department ID')
|
||
department_name = Column(String(255), comment='Attendee department name')
|
||
|
||
# Period context
|
||
period = Column(String(32), nullable=False, comment='Review period (e.g., 2026-W11)')
|
||
report_date = Column(Date, nullable=False, comment='Workflow report date (used as "today" for overdue calc)')
|
||
calc_phase = Column(String(32), nullable=False, default='first', comment='first | second')
|
||
|
||
# Statistics
|
||
total_this_week = Column(Integer, nullable=False, server_default=text('0'), comment='本周任务总数：due_date在本周区间的任务数')
|
||
completed_this_week = Column(Integer, nullable=False, server_default=text('0'), comment='本周任务已完成：本周任务中ai_status=COMPLETED的数量')
|
||
incomplete_this_week = Column(Integer, nullable=False, server_default=text('0'), comment='本周未完成：本周任务中未完成的数量')
|
||
historical_overdue_completed_this_week = Column(Integer, nullable=False, server_default=text('0'), comment='历史逾期本周完成：due_date早于本周但在本周完成的任务数')
|
||
current_total_overdue = Column(Integer, nullable=False, server_default=text('0'), comment='当前总逾期：due_date早于report_date且未完成/未取消的任务数')
|
||
|
||
created_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|
||
updated_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), comment='更新时间')
|
||
|
||
__table_args__ = (
|
||
UniqueConstraint('session_id', 'owner_id', 'calc_phase', name='uk_review_attendee_todo_stats'),
|
||
Index('idx_review_attendee_todo_stats_session', 'session_id'),
|
||
Index('idx_review_attendee_todo_stats_owner', 'owner_id'),
|
||
{'comment': '复盘参与者任务统计表'},
|
||
)
|
||
"""
|
||
CRM Models - External/Third-party Managed
|
||
|
||
These models are used by our service but managed by external systems.
|
||
We allow extra columns and schema flexibility for these models.
|
||
"""
|
||
|
||
from sqlalchemy import Column, String, Integer, BigInteger, Float, DateTime, Text, Date, Boolean, text, Numeric, Index
|
||
from sqlalchemy.types import JSON
|
||
from sqlalchemy.sql import func
|
||
from .crm_base import CRMBase
|
||
|
||
|
||
class CRMOpportunity(CRMBase):
|
||
__tablename__ = 'crm_opportunities'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), comment='唯一性ID（必填）')
|
||
opportunity_name = Column(String(255), comment='商机名称（必填）')
|
||
opportunity_type = Column(String(255), comment='商机类型（必填）')
|
||
# primary_database = Column(String(255), comment='主要数据库')
|
||
# primary_database_percentage = Column(Float, comment='主要数据库占比（%）')
|
||
# current_database_resource = Column(String(255), comment='主数据库当前所用资源')
|
||
# current_database_pain_points = Column(String(255), comment='现用数据库痛点')
|
||
# expected_launch_date = Column(String(255), comment='预计上线日期')
|
||
# expected_closing_month = Column(String(255), comment='预计成交月')
|
||
# service_amount_calculated = Column(Float, comment='Service金额-计算')
|
||
# current_year_subscription_forecast = Column(Float, comment='当财年 Subscription 收入预测金额（不含税）')
|
||
# competitor_name = Column(String(255), comment='友商名称')
|
||
# customer_journey_percentage = Column(Float, comment='客户旅程（%）')
|
||
# opportunity_level = Column(String(255), comment='商机2.0层级')
|
||
# price_list_id = Column(String(255), comment='价目表_唯一性ID')
|
||
# price_list = Column(String(255), comment='价目表')
|
||
# service_days_type = Column(String(255), comment='人天服务类型')
|
||
# cancellation_reason = Column(String(255), comment='取消原因')
|
||
# is_slip_deal = Column(String(255), comment='是否为 slip deal')
|
||
# forecast_amount = Column(Float, comment='预测金额')
|
||
# solution_owner_1 = Column(String(255), comment='联合解决方案负责人-1')
|
||
# unit = Column(String(255), comment='单位')
|
||
# lifecycle_status = Column(String(255), comment='生命状态')
|
||
# current_year_license_forecast = Column(Float, comment='当财年 License 收入预测金额（不含税）')
|
||
# former_name = Column(String(255), comment='曾用名')
|
||
|
||
# Customer information
|
||
customer_id = Column(String(255), comment='客户名称_唯一性ID（必填）')
|
||
customer_name = Column(String(255), comment='客户名称（必填）')
|
||
customer_type = Column(String(255), comment='客户属性')
|
||
owner = Column(String(255), comment='负责人（必填）')
|
||
owner_id = Column(String(255), comment='负责人ID')
|
||
customer_business_scenario = Column(String(255), comment='客户业务场景')
|
||
|
||
# Financial information
|
||
forecast_type = Column(String(255), comment='预测类型')
|
||
estimated_tcv = Column(Integer, comment='预估 TCV')
|
||
estimated_acv = Column(Integer, comment='预估 ACV')
|
||
expected_closing_date = Column(String(255), comment='预计成交日期')
|
||
expected_closing_quarter = Column(String(255), comment='预计成交季度')
|
||
expected_closing_year = Column(Integer, comment='业绩归属年度') # Shared column: expected closing year
|
||
expected_closing_month = Column(String(255), comment='业绩归属月份')
|
||
derived_close_date = Column(Date, comment='APTSell derived close date (when CRM close_date is missing)')
|
||
|
||
# Sales information
|
||
# sales_log_details = Column(Text, comment='当前详细状态及Close节奏(销售日志)')
|
||
# call_high_notes = Column(Text, comment='Call high 情况')
|
||
# customer_budget_status = Column(Text, comment='客户预算情况')
|
||
# todo_and_followup = Column(Text, comment='Todo & follow up')
|
||
|
||
# Stage and status
|
||
opportunity_stage = Column(String(255), comment='商机阶段（必填）')
|
||
stage_status = Column(String(255), comment='阶段状态')
|
||
stage_change_time = Column(String(255), comment='阶段变更时间')
|
||
business_type = Column(String(255), comment='业务类型（必填）')
|
||
|
||
# Audit fields
|
||
# creator = Column(String(255), comment='创建人')
|
||
create_time = Column(DateTime, comment='创建时间')
|
||
last_modifier = Column(String(255), comment='最后修改人')
|
||
last_modified_time = Column(DateTime, comment='最后修改时间')
|
||
last_followup_time = Column(String(255), comment='最后跟进时间')
|
||
owner_main_department = Column(String(255), comment='负责人主属部门')
|
||
delete_flag = Column(Boolean, nullable=True, comment='删除标识（0-正常，1-已删除）')
|
||
|
||
|
||
class CRMAccount(CRMBase):
|
||
__tablename__ = 'crm_accounts'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), comment='唯一性ID（必填）')
|
||
customer_name = Column(String(255), comment='客户名称（必填）')
|
||
customer_source = Column(String(255), comment='客户来源（必填）')
|
||
person_in_charge = Column(String(255), comment='负责人')
|
||
person_in_charge_id = Column(String(255), comment='负责人ID')
|
||
department = Column(String(255), comment='负责人主属部门')
|
||
# TODO: Add department_id field when available
|
||
# department_id = Column(String(255), comment='部门唯一性ID')
|
||
customer_level = Column(String(255), comment='客户等级')
|
||
industry = Column(String(255), comment='客户行业（必填）')
|
||
business_type = Column(String(255), comment='业务类型（必填）')
|
||
life_status = Column(String(255), comment='生命状态')
|
||
belonging_department = Column(String(255), comment='归属部门')
|
||
creator = Column(String(255), comment='创建人')
|
||
creation_time = Column(DateTime, comment='创建时间')
|
||
last_modifier = Column(String(255), comment='最后修改人')
|
||
last_modified_time = Column(DateTime, comment='最后修改时间')
|
||
customer_identifier = Column(String(255), comment='客户标识')
|
||
customer_code = Column(String(255), comment='客户编号')
|
||
customer_abbreviation = Column(String(255), comment='客户简称')
|
||
customer_attribute = Column(String(255), comment='客户属性')
|
||
partner = Column(String(255), comment='合作伙伴')
|
||
account_level = Column(String(255), comment='Name Account 分级')
|
||
status = Column(String(255), comment='Account状态')
|
||
delete_flag = Column(Boolean, nullable=True, comment='删除标识（0-正常，1-已删除）')
|
||
|
||
|
||
class CRMContact(CRMBase):
|
||
__tablename__ = 'crm_contacts'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
name = Column(String(255), nullable=False, comment='联系人姓名（必填）')
|
||
customer_id = Column(String(255), comment='客户名称_唯一性ID')
|
||
customer_name = Column(String(255), comment='客户名称')
|
||
position = Column(String(255), comment='职位')
|
||
key_decision_maker = Column(String(255), comment='关键决策人')
|
||
|
||
|
||
class LocalContact(CRMBase):
|
||
__tablename__ = 'local_contacts'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
name = Column(String(255), nullable=False, comment='联系人姓名（必填）')
|
||
customer_id = Column(String(255), comment='客户名称_唯一性ID')
|
||
customer_name = Column(String(255), comment='客户名称')
|
||
position = Column(String(255), comment='职位')
|
||
key_decision_maker = Column(Boolean, comment='关键决策人')
|
||
|
||
|
||
class CRMPOC(CRMBase):
|
||
__tablename__ = 'crm_poc'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False, comment='唯一性ID（必填）')
|
||
account_id = Column(String(255), nullable=True, comment='客户ID')
|
||
account_name = Column(String(255), nullable=True, comment='客户名称')
|
||
opportunity_name = Column(String(255), nullable=True, comment='商机名称')
|
||
opportunity_id = Column(String(255), nullable=True, comment='商机ID')
|
||
owner = Column(String(255), nullable=True, comment='负责人')
|
||
is_poc = Column(String(10), nullable=True, comment='是否POC（是/否）')
|
||
poc_status = Column(String(20), nullable=True, comment='POC状态（未开始/进行中/已完成）')
|
||
poc_result = Column(String(20), comment='POC结果（通过/赢；未通过/输；待定）')
|
||
poc_end_date = Column(Date, comment='POC完成日期')
|
||
created_by = Column(String(255), nullable=True, comment='创建人')
|
||
created_time = Column(DateTime, nullable=False, server_default=func.now(), comment='创建时间')
|
||
last_modified_by = Column(String(255), comment='最后修改人')
|
||
last_modified_time = Column(DateTime, nullable=False, server_default=func.now(), onupdate=func.now(), comment='最后修改时间')
|
||
|
||
# Define indexes for better query performance
|
||
__table_args__ = (
|
||
Index('idx_unique_id', 'unique_id'),
|
||
Index('idx_account_id', 'account_id'),
|
||
Index('idx_account_name', 'account_name'),
|
||
Index('idx_opportunity_id', 'opportunity_id'),
|
||
Index('idx_opportunity_name', 'opportunity_name'),
|
||
Index('idx_owner', 'owner'),
|
||
Index('idx_poc_status', 'poc_status'),
|
||
Index('idx_poc_end_date', 'poc_end_date'),
|
||
Index('idx_created_time', 'created_time'),
|
||
)
|
||
|
||
|
||
class CRMSalesVisitRecord(CRMBase):
|
||
__tablename__ = 'crm_sales_visit_records'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
|
||
# Account and Opportunity Information
|
||
account_name = Column(String(255), comment='客户名称')
|
||
account_id = Column(String(255), comment='客户ID')
|
||
opportunity_name = Column(String(255), comment='商机名称')
|
||
opportunity_id = Column(String(255), comment='商机ID')
|
||
partner_id = Column(String(255), comment='合作伙伴ID')
|
||
partner_name = Column(String(255), comment='合作伙伴名称')
|
||
customer_lead_source = Column(String(255), comment='客户线索来源')
|
||
visit_object_category = Column(String(255), comment='拜访对象类别')
|
||
|
||
# Contact Information
|
||
contact_position = Column(String(255), comment='联系人职位')
|
||
contact_name = Column(String(255), comment='联系人姓名')
|
||
|
||
# Recording Information
|
||
recorder = Column(String(255), comment='记录人')
|
||
recorder_id = Column(String(32), comment='记录人ID')
|
||
collaborative_participants = Column(String(255), comment='协作参与者')
|
||
|
||
# Visit Details
|
||
visit_communication_date = Column(Date, comment='拜访沟通日期')
|
||
counterpart_location = Column(String(255), comment='对方地点')
|
||
visit_communication_method = Column(String(255), comment='拜访沟通方式')
|
||
communication_duration = Column(String(255), comment='沟通时长')
|
||
visit_type = Column(String(20), comment='拜访类型')
|
||
visit_url = Column(Text, comment='拜访URL')
|
||
|
||
# Visit Assessment
|
||
expectation_achieved = Column(String(255), comment='期望达成情况')
|
||
is_first_visit = Column(Boolean, comment='是否首次拜访')
|
||
|
||
# Follow-up Information
|
||
followup_record = Column(Text, comment='跟进记录')
|
||
followup_quality_level_zh = Column(String(100), comment='跟进质量等级')
|
||
followup_quality_reason_zh = Column(Text, comment='跟进质量原因')
|
||
assessment_flag = Column(String(10), nullable=True, comment='评估结果(red/yellow/green)')
|
||
assessment_description = Column(Text, nullable=True, comment='评估描述')
|
||
|
||
# Next Steps
|
||
next_steps = Column(Text, comment='下一步计划')
|
||
next_steps_quality_level_zh = Column(String(100), comment='下一步计划质量等级')
|
||
next_steps_quality_reason_zh = Column(Text, comment='下一步计划质量原因')
|
||
|
||
# Additional Information
|
||
attachment = Column(String(255), comment='附件')
|
||
parent_record = Column(String(255), comment='父记录')
|
||
remarks = Column(Text, comment='备注')
|
||
|
||
# Metadata
|
||
record_id = Column(String(100), comment='记录ID')
|
||
last_modified_time = Column(DateTime, comment='最后修改时间')
|
||
|
||
|
||
class CRMUserProfile(CRMBase):
|
||
__tablename__ = 'user_profiles'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
user_id = Column(String(32), nullable=True, comment='用户ID')
|
||
oauth_user_id = Column(String(255), nullable=True, comment='OAuth用户ID')
|
||
crm_user_id = Column(String(100), nullable=True, comment='CRM用户ID，关联到crm_user.unique_id')
|
||
feishu_open_id = Column(String(255), nullable=True, comment='飞书Open ID')
|
||
name = Column(String(255), nullable=True, comment='姓名')
|
||
department = Column(String(255), nullable=True, comment='部门')
|
||
position = Column(String(255), nullable=True, comment='职位')
|
||
direct_manager_id = Column(String(255), nullable=True, comment='直属经理ID')
|
||
direct_manager_name = Column(String(255), nullable=True, comment='直属经理姓名')
|
||
is_active = Column(Boolean, nullable=False, comment='是否激活')
|
||
# created_at = Column(DateTime, nullable=True, server_default=func.now(), comment='创建时间')
|
||
# updated_at = Column(DateTime, nullable=True, server_default=func.now(), onupdate=func.now(), comment='更新时间')
|
||
platform = Column(String(50), nullable=True, comment='平台')
|
||
open_id = Column(String(255), nullable=True, comment='Open ID')
|
||
notification_tags = Column(String(1000), nullable=True, comment='通知标签')
|
||
|
||
|
||
class CRMUser(CRMBase):
|
||
__tablename__ = 'crm_user'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(64), nullable=True, comment='用户ID')
|
||
user_name = Column(String(255), nullable=True, comment='用户名称')
|
||
department_id = Column(String(255), nullable=True, comment='部门ID')
|
||
department = Column(String(255), nullable=True, comment='部门')
|
||
|
||
|
||
|
||
class CRMDepartment(CRMBase):
|
||
__tablename__ = 'crm_department'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=True, comment='唯一性ID')
|
||
department_name = Column(String(255), nullable=True, comment='部门名称')
|
||
parent_department_id = Column(String(255), nullable=True, comment='父部门ID')
|
||
is_active = Column(Boolean, nullable=True, comment='是否激活')
|
||
delete_flag = Column(Boolean, nullable=True, comment='删除标识')
|
||
|
||
|
||
class UserDepartmentRelation(CRMBase):
|
||
__tablename__ = 'user_department_relation'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True)
|
||
create_time = Column(DateTime, nullable=True, server_default=func.now())
|
||
update_time = Column(DateTime, nullable=True, server_default=func.now(), onupdate=func.now())
|
||
user_id = Column(String(36), nullable=True, comment='用户ID')
|
||
crm_user_id = Column(String(100), nullable=False, comment='CRM用户ID，关联到crm_user.unique_id')
|
||
department_id = Column(String(100), nullable=False, comment='部门ID')
|
||
is_primary = Column(Boolean, nullable=False, default=False, comment='是否主部门')
|
||
is_leader = Column(Boolean, nullable=False, default=False, comment='是否领导')
|
||
title = Column(String(100), nullable=True, comment='职位')
|
||
user_name = Column(String(100), nullable=True, comment='用户名称')
|
||
|
||
|
||
class CRMDataAuthority(CRMBase):
|
||
"""
|
||
CRM Data Authority Table - Externally Managed
|
||
|
||
This table stores permission mappings between users and CRM resources.
|
||
Since this table is externally managed, we only create the ORM model
|
||
to match the database schema. No custom indexes or logic should be added.
|
||
"""
|
||
__tablename__ = 'crm_data_authority'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(Integer, primary_key=True, autoincrement=True, comment='主键ID（自增序列）')
|
||
data_id = Column(String(255), nullable=True, comment='数据id')
|
||
user_id = Column(String(255), nullable=True, comment='用户id')
|
||
crm_id = Column(String(255), nullable=True, comment='CRM用户id')
|
||
type = Column(String(255), nullable=True, comment='数据类型')
|
||
create_time = Column(DateTime, nullable=True, comment='创建时间')
|
||
update_time = Column(DateTime, nullable=True, comment='最后更新时间')
|
||
delete_flag = Column(Boolean, nullable=True, comment='删除标识, True已删除')
|
||
|
||
|
||
class CRMReviewOppAuditLog(CRMBase):
|
||
"""Audit log for branch snapshot modifications"""
|
||
__tablename__ = 'crm_review_opp_audit_log'
|
||
__table_args__ = {'extend_existing': True}
|
||
|
||
id = Column(BigInteger, primary_key=True, autoincrement=True)
|
||
unique_id = Column(String(255), nullable=False)
|
||
|
||
snapshot_id = Column(String(255), nullable=False)
|
||
session_id = Column(String(255), nullable=False)
|
||
|
||
opportunity_id = Column(String(255), nullable=False)
|
||
opportunity_name = Column(String(255))
|
||
owner_id = Column(String(255))
|
||
owner_name = Column(String(255))
|
||
department_id = Column(String(255))
|
||
|
||
changed_field = Column(String(64), nullable=False)
|
||
old_value = Column(String(512))
|
||
new_value = Column(String(512))
|
||
|
||
change_type = Column(String(32), nullable=False, default='UPDATE')
|
||
change_reason = Column(String(255))
|
||
edit_phase = Column(String(32))
|
||
|
||
changed_by = Column(String(255), nullable=False)
|
||
changed_by_id = Column(String(255), nullable=False)
|
||
changed_by_role = Column(String(64))
|
||
|
||
client_ip = Column(String(64))
|
||
user_agent = Column(String(512))
|
||
request_id = Column(String(255))
|
||
|
||
value_changed = Column(Boolean, default=True, server_default=text('1'))
|
||
amount_delta = Column(Numeric(18, 2))
|
||
|
||
changed_at = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
|
||
create_time = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'), comment='创建时间')
|