#Plan 01 — callytics-common Schema Implementation Plan

// tests/schema/task-progress-events.test.ts
import { describe, expect, test } from 'bun:test';
import {
  taskProgressEvents,
  TASK_PROGRESS_TYPE,
  TASK_CHANNEL,
  TASK_ACTOR_TYPE,
} from '../../src/db/schema/task-progress-events';

describe('task-progress-events schema — unit', () => {
  test('U1: TASK_PROGRESS_TYPE matches spec §2.1 exact order', () => {
    expect(TASK_PROGRESS_TYPE).toEqual([
      'no_answer',
      'left_voicemail',
      'text_sent',
      'callback_requested',
      'follow_up_scheduled',
      'customer_considering',
    ]);
  });

  // U2-U14: implementer 按上方 case map 全 cover
  // 整 file 估计 ~150 行 test code
});

// tests/integration/task-progress-events.test.ts
import { describe, expect, test, beforeAll, afterAll } from 'bun:test';
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import { taskProgressEvents } from '../../src/db/schema/task-progress-events';
import { tasks } from '../../src/db/schema/tasks';

const sql = neon(process.env['DATABASE_URL_TEST']!);
const db = drizzle(sql);

describe('task-progress-events — DB integration', () => {
  let seedTaskId: string;
  beforeAll(async () => {
    // seed 1 个 task 给 FK reference 用
    const [task] = await db.insert(tasks).values({
      contactPhone: '+15555550001',
      franchiseId: 'TEST',
      accountId: 'TEST',
      storeId: 'STORE_TEST',
      taskType: 'follow_up',
      typeCategory: 'lead_follow_up',
      sourceType: 'manual',
      status: 'open',
    }).returning();
    seedTaskId = task!.taskId;
  });

  afterAll(async () => {
    // cleanup
    await db.delete(taskProgressEvents).where(/* by test storeId */);
    await db.delete(tasks).where(/* same */);
  });

  test('I1: INSERT 完整合法 row succeeds and is retrievable', async () => {
    const [row] = await db.insert(taskProgressEvents).values({
      taskId: seedTaskId,
      storeId: 'STORE_TEST',
      contactPhone: '+15555550001',
      progressType: 'no_answer',
      channel: 'phone',
      actorType: 'staff',
      callId: 'call_abc',
      occurredAt: new Date(),
      idempotencyKey: `progress:${seedTaskId}:call:call_abc:no_answer`,
    }).returning();
    expect(row).toBeDefined();
    expect(row?.taskId).toBe(seedTaskId);
  });

  // I2-I14: implementer 按 case map 全 cover
  // 整 file 估计 ~250 行 test code
});

import {
  pgTable,
  text,
  timestamp,
  uuid,
  bigint,
  index,
  uniqueIndex,
  check,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

// ─── Enum definitions (single source of truth) ──────────────────

export const TASK_PROGRESS_TYPE = [
  'no_answer',
  'left_voicemail',
  'text_sent',
  'callback_requested',
  'follow_up_scheduled',
  'customer_considering',
] as const;
export type TaskProgressType = (typeof TASK_PROGRESS_TYPE)[number];

export const TASK_CHANNEL = ['phone', 'sms', 'voicemail', 'email'] as const;
export type TaskChannel = (typeof TASK_CHANNEL)[number];

export const TASK_ACTOR_TYPE = ['staff', 'system', 'ai_agent', 'contact_analysis'] as const;
export type TaskActorType = (typeof TASK_ACTOR_TYPE)[number];

// ─── Table definition ───────────────────────────────────────────

/**
 * task_progress_events — Progress 事件 source of truth
 *
 * 每次员工 / 系统 / AI 对某个 open task 做了一次"进展"动作,就 INSERT 一行。
 * 不 close task。例如:打电话没接 → no_answer;留语音邮件 → left_voicemail;
 * 发短信 → text_sent。task 仍 open,attemptCount += 1。
 *
 * 替代旧设计:closeResult 里混合的 'no_answer' / 'left_voicemail' / 'callback_later'
 * / 'attempted' 这 4 个 "其实是 progress,不是 outcome" 的值。
 *
 * 多租户隔离:store_id 冗余存储,避免 join tasks 查 store guard。
 *
 * 唯一去重键:idempotency_key UNIQUE。不用 (task_id, call_id, progress_type)
 * 复合 unique 因为 PostgreSQL NULL ≠ NULL —— SMS progress (call_id IS NULL)
 * 会被重复插入。caller 生成 idempotency_key 见 normative-spec §3.2。
 *
 * 设计文档:docs/product-design/v2/unified-pipeline/implementation-plan/normative-spec.md §2.1
 */
export const taskProgressEvents = pgTable(
  'task_progress_events',
  {
    eventId: uuid('event_id').primaryKey().defaultRandom(),
    /**
     * FK → tasks.task_id(normative-spec §2.1 line 39 明文要求)。
     * 没 FK 会导致 orphan event row(task 被 hard-delete 时 event 残留)。
     * Phase 1 不做 cascade delete(spec 没要求),默认 NO ACTION 让 DB 阻止
     * task 删除直到 event 被 archive。
     */
    taskId: uuid('task_id').notNull().references(() => tasks.taskId),
    storeId: text('store_id').notNull(),
    contactPhone: text('contact_phone').notNull(),
    progressType: text('progress_type', { enum: TASK_PROGRESS_TYPE }).notNull(),
    channel: text('channel', { enum: TASK_CHANNEL }).notNull(),
    actorType: text('actor_type', { enum: TASK_ACTOR_TYPE }).notNull(),
    actorId: uuid('actor_id'),
    /**
     * evidence reference,no FK。
     * calls 表 PK 是 telephony_session_id(text)而非 uuid;且 call 可能在 progress
     * 写入之后才落库,加 FK 会引入时序耦合。Phase 1 不加 FK,只作 reference。
     */
    callId: text('call_id'),
    /**
     * evidence reference,no FK。
     * messages.id 是 bigint。同理不加 FK 避免时序耦合。
     */
    messageId: bigint('message_id', { mode: 'bigint' }),
    note: text('note'),
    nextDueAt: timestamp('next_due_at', { withTimezone: true }),
    occurredAt: timestamp('occurred_at', { withTimezone: true }).notNull(),
    /**
     * NOT NULL UNIQUE —— 全局唯一去重键。
     * 生成规则见 normative-spec §3.2:
     *   电话:`progress:{taskId}:call:{callId}:{progressType}`
     *   SMS:`progress:{taskId}:message:{messageId}:{progressType}`
     *   手动:`progress:{taskId}:manual:{actorId}:{occurredAt}:{progressType}`
     *   系统:`progress:{taskId}:system:{runId}:{progressType}`
     */
    idempotencyKey: text('idempotency_key').notNull().unique(),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    // task 维度查询(API 拉某 task 的全部进展)
    index('idx_tpe_task_occurred').on(table.taskId, table.occurredAt.desc()),
    // store 维度查询(dashboard "attempt workload")
    index('idx_tpe_store_occurred').on(table.storeId, table.occurredAt.desc()),
    // actor 维度查询(staff 个人产出统计)
    index('idx_tpe_actor').on(table.actorType, table.actorId, table.occurredAt.desc()),
    // progress 类型 + channel 统计
    index('idx_tpe_type_channel').on(table.progressType, table.channel, table.occurredAt.desc()),
    /*
     * DB-level CHECK constraints — normative-spec §2.1 line 42-47 明文要求。
     * Drizzle text+enum 只 TS 层验 — 任何 SQL 直插(staff ad-hoc / migration /
     * 旧 caller 没升级 client) 会绕过 TS 检查。CHECK 在 DB 层守 schema 不变量。
     */
    check('chk_tpe_progress_type', sql`progress_type IN (${sql.raw(TASK_PROGRESS_TYPE.map(v => `'${v}'`).join(', '))})`),
    check('chk_tpe_channel', sql`channel IN (${sql.raw(TASK_CHANNEL.map(v => `'${v}'`).join(', '))})`),
    check('chk_tpe_actor_type', sql`actor_type IN (${sql.raw(TASK_ACTOR_TYPE.map(v => `'${v}'`).join(', '))})`),
  ]
);

import { describe, expect, test } from 'bun:test';
import {
  TASK_STATUS,
  TASK_CLOSE_TYPE,
  TASK_CLOSE_RESULT,
  tasks,
} from '../../src/db/schema/tasks';

describe('tasks schema — Phase 1 transitional', () => {
  test('TASK_STATUS has 3 values during transitional window: pending, open, closed', () => {
    expect(TASK_STATUS).toEqual(['pending', 'open', 'closed']);
  });

  test('TASK_CLOSE_TYPE adds create_closed (3 values total)', () => {
    expect(TASK_CLOSE_TYPE).toEqual(['auto_closed', 'manual_closed', 'create_closed']);
  });

  test('TASK_CLOSE_RESULT adds unable_to_reach (19 values total during transitional)', () => {
    expect(TASK_CLOSE_RESULT).toContain('unable_to_reach');
    // transitional: old 18 + new unable_to_reach = 19; final cutover (Plan 07) shrinks to 15
    expect(TASK_CLOSE_RESULT.length).toBe(19);
  });

  test('tasks table exposes new columns: executorType, attemptCount, sourceCallId', () => {
    const columns = Object.keys(tasks);
    expect(columns).toContain('executorType');
    expect(columns).toContain('attemptCount');
    expect(columns).toContain('sourceCallId');
  });
});

// BEFORE:
export const TASK_STATUS = ['pending', 'closed'] as const;

// AFTER:
/**
 * Phase 1 transitional: 3 values during compatibility window.
 * Final cutover (Plan 07) shrinks back to ['open', 'closed'].
 *   - 'pending' — legacy value, kept readable during transition
 *   - 'open'    — new canonical value, all new writes use this
 *   - 'closed'  — terminal state (unchanged)
 * Read paths must use status IN ('pending', 'open') for "open work" queries
 * until cutover migration runs (UPDATE pending → open + DROP from enum).
 * Spec: normative-spec.md §6.2
 */
export const TASK_STATUS = ['pending', 'open', 'closed'] as const;

// BEFORE:
export const TASK_CLOSE_TYPE = ['auto_closed', 'manual_closed'] as const;

// AFTER:
/**
 * Phase 1: add 'create_closed' for tasks created already-closed
 * (e.g. inbound 当场成交 → 直接建 closed task,让 dashboard 能看到完成事项)。
 * Spec: normative-spec.md §2.2 + unified-pipeline-final.md Task Pipeline Deliverable
 */
export const TASK_CLOSE_TYPE = ['auto_closed', 'manual_closed', 'create_closed'] as const;

// BEFORE: 18 values
export const TASK_CLOSE_RESULT = [
  'attempted', 'converted', 'win_back', 'issue_resolved',
  'cancel_saved', 'renewed', 'upgraded', 'referral_obtained',
  'wrong_number', 'do_not_contact', 'other',
  'no_answer', 'left_voicemail', 'not_interested', 'callback_later', 'already_member',
  /* ... existing comment about booked/cancelled ... */
  'booked', 'cancelled',
] as const;

// AFTER: 19 values transitional (final cutover shrinks to 15 by removing 4 progress values)
export const TASK_CLOSE_RESULT = [
  'attempted', 'converted', 'win_back', 'issue_resolved',
  'cancel_saved', 'renewed', 'upgraded', 'referral_obtained',
  'wrong_number', 'do_not_contact', 'other',
  'no_answer', 'left_voicemail', 'not_interested', 'callback_later', 'already_member',
  /*
   * Added 2026-05-23 for OTF V1 prompt refinements (callytics-infrastructure
   * PR #980 series). Distinguish customer-facing outcomes the AI must record
   * on task close that the prior 16 values couldn't express cleanly:
   *   - `booked`: lead has a confirmed upcoming appointment/class/intro. Not
   *     the same as `converted` (paid membership) — booked may still no-show.
   *   - `cancelled`: cancellation request approved OR cancellation form sent
   *     to customer. Not the same as `cancel_saved` (customer was talked out
   *     of cancelling and is staying).
   */
  'booked', 'cancelled',
  /*
   * Added 2026-06 Phase 1 (normative-spec §2.2): represents "max attempts
   * reached, contact could not be reached" — the only legitimate "negative
   * outcome" after Phase 1 moves no_answer / left_voicemail / attempted into
   * task_progress_events. Final cutover (Plan 07) removes the 4 progress
   * values, leaving 15 outcome-only values.
   */
  'unable_to_reach',
] as const;

    /* existing columns up to note ... */
    note: text('note'),

    // ── Phase 1 新增字段(normative-spec §2.2)──

    /**
     * 执行 / 完成者类型,metrics 归因用。
     * 'human'     — staff 通过 UI 操作
     * 'ai_agent'  — future tool calling AI agent
     * 'system'    — pipeline 自动(e.g. DNC cascade)
     * Phase 1 大多数 task 是 human / system,ai_agent 留给 future。
     */
    executorType: text('executor_type', {
      enum: ['human', 'ai_agent', 'system'] as const,
    }),
    // CHECK 见 Step 7(connected to existing chk_tasks_* checks block)

    /**
     * 列表展示用 snapshot,真实 source of truth 是 task_progress_events 表。
     * Orchestrator 在 record_progress action 内用 CTE 保证只在真插入 progress event 时
     * 才 increment(normative-spec §3.2 B8 fix)。
     */
    attemptCount: integer('attempt_count').notNull().default(0),

    /**
     * create_closed task 的 evidence reference,对应触发"当场办成"的 call ID。
     * 用于 partial unique 防同一通电话重复建 closed task(normative-spec §3.1)。
     * 普通 create_open / staff manual close 时为 NULL。
     */
    sourceCallId: text('source_call_id'),

    // ── 系统时间戳 ──

    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    /* ... rest unchanged ... */

// BEFORE:
import {
  pgTable, text, timestamp, uuid, boolean, jsonb,
  index, uniqueIndex, check,
} from 'drizzle-orm/pg-core';

// AFTER:
import {
  pgTable, text, timestamp, uuid, boolean, jsonb, integer,
  index, uniqueIndex, check,
} from 'drizzle-orm/pg-core';

// BEFORE:
check('chk_tasks_closed_integrity', sql`
  (status = 'pending' AND close_type IS NULL AND closed_at IS NULL)
  OR
  (status = 'closed' AND close_type IS NOT NULL AND closed_at IS NOT NULL)
`),

// AFTER (transitional — accepts both 'pending' and 'open' as open-work state):
check('chk_tasks_closed_integrity', sql`
  (status IN ('pending', 'open') AND close_type IS NULL AND closed_at IS NULL)
  OR
  (status = 'closed' AND close_type IS NOT NULL AND closed_at IS NOT NULL)
`),

// BEFORE:
check('chk_tasks_close_result', sql`
  close_result IS NULL OR close_result = ANY(ARRAY[
    'attempted', 'converted', 'win_back', 'issue_resolved',
    'cancel_saved', 'renewed', 'upgraded', 'referral_obtained',
    'wrong_number', 'do_not_contact', 'other',
    'no_answer', 'left_voicemail', 'not_interested', 'callback_later', 'already_member',
    'booked', 'cancelled'
  ])
`),

// AFTER (adds 'unable_to_reach'):
check('chk_tasks_close_result', sql`
  close_result IS NULL OR close_result = ANY(ARRAY[
    'attempted', 'converted', 'win_back', 'issue_resolved',
    'cancel_saved', 'renewed', 'upgraded', 'referral_obtained',
    'wrong_number', 'do_not_contact', 'other',
    'no_answer', 'left_voicemail', 'not_interested', 'callback_later', 'already_member',
    'booked', 'cancelled',
    'unable_to_reach'
  ])
`),

// BEFORE:
uniqueIndex('uq_tasks_pending_contact_category')
  .on(table.contactPhone, table.storeId, table.typeCategory)
  .where(sql`status = 'pending' AND store_id IS NOT NULL`),

// AFTER (transitional — covers both 'pending' and 'open' open-state names):
uniqueIndex('uq_tasks_pending_contact_category')
  .on(table.contactPhone, table.storeId, table.typeCategory)
  .where(sql`status IN ('pending', 'open') AND store_id IS NOT NULL`),

// #8b create_closed 防重 — 同 (contactPhone, storeId, typeCategory, sourceCallId)
// 在 closed 状态下只允许 1 行。sourceCallId IS NOT NULL 排除普通 manual close。
// 防止同一通电话被同一个 caller 重复建 closed task。
uniqueIndex('uq_tasks_create_closed_evidence')
  .on(table.contactPhone, table.storeId, table.typeCategory, table.sourceCallId)
  .where(sql`status = 'closed' AND source_call_id IS NOT NULL`),

// Insert in the same constraints block(near chk_tasks_close_result):
check('chk_tasks_executor_type', sql`
  executor_type IS NULL OR executor_type = ANY(ARRAY['human', 'ai_agent', 'system'])
`),

import { describe, expect, test } from 'bun:test';
import {
  STATUS_OPTIONS,
  CLOSE_RESULT_OPTIONS,
  PROGRESS_OPTIONS,
} from '../../src/db/schema/task-ui';

describe('task-ui phase 1', () => {
  test('STATUS_OPTIONS includes open with label Open', () => {
    const openOpt = STATUS_OPTIONS.find((o) => o.value === 'open');
    expect(openOpt).toBeDefined();
    expect(openOpt?.label).toBe('Open');
  });

  test('CLOSE_RESULT_OPTIONS includes unable_to_reach', () => {
    const opt = CLOSE_RESULT_OPTIONS.find((o) => o.value === 'unable_to_reach');
    expect(opt).toBeDefined();
    expect(opt?.label).toBe('Unable to Reach');
  });

  test('PROGRESS_OPTIONS lists 6 values with UI labels', () => {
    expect(PROGRESS_OPTIONS.length).toBe(6);
    const values = PROGRESS_OPTIONS.map((o) => o.value);
    expect(values).toEqual([
      'no_answer',
      'left_voicemail',
      'text_sent',
      'callback_requested',
      'follow_up_scheduled',
      'customer_considering',
    ]);
    const noAnswer = PROGRESS_OPTIONS.find((o) => o.value === 'no_answer');
    expect(noAnswer?.label).toBe('No Answer');
  });
});

// BEFORE (assuming current shape):
export const STATUS_OPTIONS: readonly StatusOption[] = [
  { value: 'pending', label: 'Open' },
  { value: 'closed', label: 'Closed' },
] as const;

// AFTER (transitional — both 'pending' and 'open' map to UI label "Open"):
export const STATUS_OPTIONS: readonly StatusOption[] = [
  // Legacy value during Phase 1 transitional window; final cutover (Plan 07)
  // removes 'pending' entry. UI label stays "Open" for both during transition.
  { value: 'pending', label: 'Open' },
  { value: 'open', label: 'Open' },
  { value: 'closed', label: 'Closed' },
] as const;

// Add this entry to the array (preserve all existing entries):
{ value: 'unable_to_reach', label: 'Unable to Reach', category: 'negative', description: 'Max attempts reached, contact could not be reached' },

/**
 * Progress 类型 UI label 列表。
 * 对应 task_progress_events.progress_type enum(TASK_PROGRESS_TYPE in
 * task-progress-events.ts)。staff 用 progress endpoint 时前端 dropdown 用这个。
 *
 * 注意:no_answer / left_voicemail 现在是 progress(不再是 closeResult)。
 * 旧 UI 把这两个 + callback_later 放在 close dropdown 里,Phase 1 后必须移到 progress dropdown。
 */
export interface ProgressOption {
  value: TaskProgressType;
  label: string;
  description: string;
}

import type { TaskProgressType } from './task-progress-events';

export const PROGRESS_OPTIONS: readonly ProgressOption[] = [
  { value: 'no_answer', label: 'No Answer', description: 'Called but no one picked up' },
  { value: 'left_voicemail', label: 'Left Voicemail', description: 'Left a voicemail message' },
  { value: 'text_sent', label: 'Text Sent', description: 'Sent SMS, waiting for reply' },
  { value: 'callback_requested', label: 'Callback Requested', description: 'Customer asked to be called back' },
  { value: 'follow_up_scheduled', label: 'Follow-up Scheduled', description: 'Staff scheduled next contact attempt' },
  { value: 'customer_considering', label: 'Customer Considering', description: 'Customer needs time to think' },
] as const;

// BEFORE: 15 values(verbatim list per current schema)
export const TIMELINE_EVENT_TYPES = [
  'call.completed',
  'message.received',
  // ... 13 more
] as const;

// AFTER: 16 values(加 'task.progress_recorded')
export const TIMELINE_EVENT_TYPES = [
  'call.completed',
  'message.received',
  // ... 13 more
  'task.progress_recorded',  // NEW(Phase 1 Plan 06 record_progress endpoint)
] as const;

export * from './task-progress-events';