"""
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='创建时间')
