import { getDb } from '../client'
import { taskExecutions, cronTasks } from '../schema'
import { eq, and, or, desc, sql } from 'drizzle-orm'

/**
 * TaskExecution Repository。封装 task_executions 表的所有查询。
 *
 * 设计约定：
 * - 返回类型由 drizzle 推断，消费方不再写 `as Record<string, unknown>`
 * - task_executions 随 cron_task 删除由 cronTaskRepo.deleteByIdAndOwner 事务级联
 * - 统计用 sql<number>`count(*)`
 */

/** 执行状态枚举（与 schema 一致） */
type ExecutionStatus = 'ok' | 'alert' | 'error'

/** 任务类型过滤（对应 GET /task-executions?type=） */
type TaskTypeFilter = 'heartbeat' | 'cron' | 'webhook'

export const taskExecutionRepo = {
  /**
   * 按 task_id 分页查询执行记录，executed_at 倒序。
   * 对应 GET /task-executions?task_id=...
   */
  listByTask(taskId: string, limit: number, offset: number) {
    return getDb()
      .select()
      .from(taskExecutions)
      .where(eq(taskExecutions.taskId, taskId))
      .orderBy(desc(taskExecutions.executedAt))
      .limit(limit)
      .offset(offset)
      .all()
  },

  /**
   * 联表查询执行记录（te.*），支持 workspace / type / status 过滤。
   * 对应 GET /task-executions（无 task_id 分支）。
   *
   * 过滤语义：
   * - workspaceId：ct.workspace_id = ? OR ct.is_heartbeat = 1
   * - type=heartbeat：ct.is_heartbeat = 1
   * - type=cron：ct.is_heartbeat = 0 AND ct.webhook_enabled = 0
   * - type=webhook：ct.is_heartbeat = 0 AND ct.webhook_enabled = 1
   * - status：te.status = ?
   */
  listWithJoin(opts: {
    workspaceId?: string
    typeFilter?: TaskTypeFilter
    statusFilter?: ExecutionStatus
    limit: number
    offset: number
  }) {
    const conds = []

    if (opts.workspaceId) {
      conds.push(
        or(
          eq(cronTasks.workspaceId, opts.workspaceId),
          eq(cronTasks.isHeartbeat, 1),
        ),
      )
    }

    if (opts.typeFilter === 'heartbeat') {
      conds.push(eq(cronTasks.isHeartbeat, 1))
    } else if (opts.typeFilter === 'cron') {
      conds.push(
        and(eq(cronTasks.isHeartbeat, 0), eq(cronTasks.webhookEnabled, 0)),
      )
    } else if (opts.typeFilter === 'webhook') {
      conds.push(
        and(eq(cronTasks.isHeartbeat, 0), eq(cronTasks.webhookEnabled, 1)),
      )
    }

    if (opts.statusFilter) {
      conds.push(eq(taskExecutions.status, opts.statusFilter))
    }

    return getDb()
      .select({
        id: taskExecutions.id,
        taskId: taskExecutions.taskId,
        taskName: taskExecutions.taskName,
        result: taskExecutions.result,
        status: taskExecutions.status,
        sessionId: taskExecutions.sessionId,
        executedAt: taskExecutions.executedAt,
      })
      .from(taskExecutions)
      .innerJoin(cronTasks, eq(taskExecutions.taskId, cronTasks.id))
      .where(conds.length ? and(...conds) : undefined)
      .orderBy(desc(taskExecutions.executedAt))
      .limit(opts.limit)
      .offset(opts.offset)
      .all()
  },

  /** 按 id 查单条执行记录 */
  findById(id: string) {
    return getDb()
      .select()
      .from(taskExecutions)
      .where(eq(taskExecutions.id, id))
      .get()
  },

  /**
   * 创建执行记录。
   * - executedAt 省略时由 schema default datetime('now') 填充
   * 返回创建后的完整行。
   */
  create(data: {
    id: string
    taskId: string
    taskName?: string
    result?: string
    status?: ExecutionStatus
    sessionId?: string
    executedAt?: string
  }) {
    getDb()
      .insert(taskExecutions)
      .values({
        id: data.id,
        taskId: data.taskId,
        taskName: data.taskName ?? '',
        result: data.result ?? '',
        status: data.status ?? 'ok',
        sessionId: data.sessionId ?? '',
        executedAt: data.executedAt,
      })
      .run()
    return this.findById(data.id)
  },

  /** 统计某任务的执行记录总数 */
  countByTask(taskId: string): number {
    const row = getDb()
      .select({ c: sql<number>`count(*)` })
      .from(taskExecutions)
      .where(eq(taskExecutions.taskId, taskId))
      .get()
    return row?.c ?? 0
  },

  /**
   * 幂等检查：按 taskId + result 子串匹配查找执行记录 id。
   * 用于 webhook handler 的 X-Request-Id 去重（result 字段中带 `[meta: requestId:...]`）。
   * 返回 id（字符串）或 undefined。
   */
  findIdByTaskIdAndResultLike(taskId: string, pattern: string): string | undefined {
    const row = getDb()
      .select({ id: taskExecutions.id })
      .from(taskExecutions)
      .where(
        and(
          eq(taskExecutions.taskId, taskId),
          sql`${taskExecutions.result} LIKE ${pattern}`,
        ),
      )
      .limit(1)
      .get()
    return row?.id
  },

}