-- 多轮询价对话状态表
-- 每条记录代表一个 "商品-商家" 的询价对话，跟踪 4 轮漏斗过滤状态

CREATE TABLE IF NOT EXISTS customer_service_chats (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  intent_id       UUID REFERENCES procurement_materials(id),
  search_result_id UUID,
  batch_id        TEXT NOT NULL,
  merchant_id     TEXT NOT NULL DEFAULT '',
  merchant_name   TEXT NOT NULL DEFAULT '',
  merchant_url    TEXT NOT NULL DEFAULT '',
  contact_url     TEXT NOT NULL DEFAULT '',
  contact_handle  TEXT NOT NULL DEFAULT '',
  contact_site    TEXT NOT NULL DEFAULT '',
  offer_url       TEXT NOT NULL DEFAULT '',
  title           TEXT NOT NULL DEFAULT '',

  -- 状态机
  current_round   INTEGER NOT NULL DEFAULT 1,
  status          TEXT NOT NULL DEFAULT 'PENDING_SEND'
    CHECK (status IN (
      'PENDING_SEND',
      'WAITING_REPLY',
      'R1_PASSED', 'R2_PASSED', 'R3_PASSED', 'R4_PASSED',
      'REJECTED',
      'TIMEOUT_REJECTED',
      'COMPLETED',
      'HUMAN_TAKEOVER'
    )),

  -- 巡检调度
  next_check_time  TIMESTAMPTZ,
  retry_count      INTEGER NOT NULL DEFAULT 0,
  last_message_hash TEXT,

  -- LLM 提取的结构化数据
  extracted_data   JSONB DEFAULT '{}'::jsonb,

  -- 话术上下文 (材料规格/数量/品牌等)
  ask_context      JSONB DEFAULT '{}'::jsonb,

  -- 审计
  last_interacted_at TIMESTAMPTZ DEFAULT now(),
  created_at       TIMESTAMPTZ DEFAULT now(),
  updated_at       TIMESTAMPTZ DEFAULT now()
);

-- 巡检 Worker 的核心查询索引
CREATE INDEX IF NOT EXISTS idx_csc_worker_poll
  ON customer_service_chats (status, next_check_time)
  WHERE status = 'WAITING_REPLY';

CREATE INDEX IF NOT EXISTS idx_csc_batch
  ON customer_service_chats (batch_id);

CREATE INDEX IF NOT EXISTS idx_csc_intent
  ON customer_service_chats (intent_id);
