/**
 * Task query helpers — tasks-board-engine.
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * Fractional-index helpers (positionBetween / needsRebalance / rebalanceColumn) live in
 * packages/db/src/lib/fractional-index.ts.
 */
import { and, eq, inArray, gte, lte, ilike, desc, asc, sql, type SQL } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { tasks, taskLabels } from '../schema/tasks'
import { auditLog } from './_audit-forward'
import {
  assertActiveTenantAssignee,
  assertTenantOwnsOrThrow,
  assertTenantOwnsProject,
  assertTenantOwnsTaskStatus,
} from './tenant-guards'
import { positionBetween, needsRebalance, rebalanceColumn } from '../lib/fractional-index'
import type {
  TaskObject,
  TaskFilters,
  CreateTaskInput,
  UpdateTaskInput,
} from '@zync/types'

// ── Cursor helpers ────────────────────────────────────────────────────────────

export function encodeCursor(statusId: string, position: number, id: string): string {
  return Buffer.from(JSON.stringify({ statusId, position, id })).toString('base64url')
}

export function decodeCursor(cursor: string): { statusId: string; position: number; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { statusId: string; position: number; id: string }
    if (
      typeof parsed.statusId !== 'string' ||
      typeof parsed.position !== 'number' ||
      typeof parsed.id !== 'string'
    )
      return null
    return parsed
  } catch {
    return null
  }
}

// ── Serializer ────────────────────────────────────────────────────────────────

function serializeTask(row: typeof tasks.$inferSelect, labels: string[]): TaskObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    project_id: row.projectId ?? null,
    status_id: row.statusId,
    title: row.title,
    description: row.description ?? null,
    priority: row.priority as TaskObject['priority'],
    assignee_id: row.assigneeId ?? null,
    reporter_id: row.reporterId,
    due_date: row.dueDate ?? null,
    estimated_hours:
      row.estimatedHours !== null && row.estimatedHours !== undefined
        ? parseFloat(String(row.estimatedHours))
        : null,
    source: row.source as TaskObject['source'],
    external_id: row.externalId ?? null,
    position: parseFloat(String(row.position)),
    labels,
    created_at: row.createdAt.toISOString(),
    updated_at: row.updatedAt.toISOString(),
  }
}

// ── Labels helper ─────────────────────────────────────────────────────────────

async function getLabelsForTask(db: Db | DbTx, taskId: string): Promise<string[]> {
  const rows = await db
    .select({ label: taskLabels.label })
    .from(taskLabels)
    .where(eq(taskLabels.taskId, taskId))
  return rows.map((r) => r.label)
}

async function getLabelsForTasks(db: Db, taskIds: string[]): Promise<Record<string, string[]>> {
  if (taskIds.length === 0) return {}
  const rows = await db
    .select({ taskId: taskLabels.taskId, label: taskLabels.label })
    .from(taskLabels)
    .where(inArray(taskLabels.taskId, taskIds))
  const map: Record<string, string[]> = {}
  for (const row of rows) {
    if (!map[row.taskId]) map[row.taskId] = []
    map[row.taskId]!.push(row.label)
  }
  return map
}

export async function getTaskActualHours(
  db: Db,
  tenantId: string,
  taskId: string,
): Promise<number> {
  const rows = await db.execute(
    sql`
      SELECT COALESCE(SUM(duration_seconds), 0) / 3600.0 AS actual_hours
      FROM time_entries
      WHERE tenant_id = ${tenantId}
        AND task_id = ${taskId}
    `,
  )
  const row = (rows as unknown as Array<{ actual_hours: string | number | null }>)[0]
  return row ? Number(row.actual_hours ?? 0) : 0
}

export async function getTasksActualHoursMap(
  db: Db,
  tenantId: string,
  taskIds: string[],
): Promise<Map<string, number>> {
  if (taskIds.length === 0) return new Map()

  const rows = await db.execute(
    sql`
      SELECT task_id, COALESCE(SUM(duration_seconds), 0) / 3600.0 AS actual_hours
      FROM time_entries
      WHERE tenant_id = ${tenantId}
        AND task_id = ANY(ARRAY[${sql.join(taskIds.map((taskId) => sql`${taskId}`), sql`, `)}]::uuid[])
      GROUP BY task_id
    `,
  )

  const map = new Map<string, number>()
  for (const row of rows as unknown as Array<{ task_id: string; actual_hours: string | number | null }>) {
    map.set(row.task_id, Number(row.actual_hours ?? 0))
  }
  return map
}

// ── setTaskLabels ─────────────────────────────────────────────────────────────

/**
 * Replace all labels for a task (delete + insert in one transaction step).
 * Must be called inside a transaction.
 */
export async function setTaskLabels(
  tx: Db | DbTx,
  taskId: string,
  labels: string[],
): Promise<void> {
  await tx.delete(taskLabels).where(eq(taskLabels.taskId, taskId))
  if (labels.length > 0) {
    await tx
      .insert(taskLabels)
      .values(labels.map((label) => ({ taskId, label })))
      .onConflictDoNothing()
  }
}

// ── listTasks ─────────────────────────────────────────────────────────────────

export async function listTasks(
  db: Db,
  tenantId: string,
  filters: TaskFilters,
  cursor?: string,
  limit = 50,
): Promise<{ rows: TaskObject[]; nextCursor: string | null }> {
  const clampedLimit = Math.min(limit, 100)

  const conditions: SQL[] = [
    eq(tasks.tenantId, tenantId),
  ]

  if (filters.project) {
    conditions.push(eq(tasks.projectId, filters.project))
  }
  if (filters.status && filters.status.length > 0) {
    conditions.push(inArray(tasks.statusId, filters.status) as SQL<boolean>)
  }
  if (filters.priority && filters.priority.length > 0) {
    conditions.push(inArray(tasks.priority, filters.priority) as SQL<boolean>)
  }
  if (filters.assignee) {
    conditions.push(eq(tasks.assigneeId, filters.assignee))
  }
  if (filters.source && filters.source.length > 0) {
    conditions.push(inArray(tasks.source, filters.source) as SQL<boolean>)
  }
  if (filters.dueFrom) {
    conditions.push(gte(tasks.dueDate, filters.dueFrom) as SQL<boolean>)
  }
  if (filters.dueTo) {
    conditions.push(lte(tasks.dueDate, filters.dueTo) as SQL<boolean>)
  }
  if (filters.q) {
    conditions.push(ilike(tasks.title, `%${filters.q}%`) as SQL<boolean>)
  }
  if (filters.labels && filters.labels.length > 0) {
    // Subquery: task has at least one of the requested labels
    conditions.push(sql`EXISTS (
      SELECT 1 FROM task_labels tl
      WHERE tl.task_id = ${tasks.id}
        AND tl.label = ANY(ARRAY[${sql.join(filters.labels.map((l) => sql`${l}`), sql`, `)}]::text[])
    )` as SQL<boolean>)
  }

  // Cursor: (status_id, position, id) composite keyset
  if (cursor) {
    const decoded = decodeCursor(cursor)
    if (decoded) {
      conditions.push(sql`(
        ${tasks.statusId} > ${decoded.statusId}
        OR (${tasks.statusId} = ${decoded.statusId} AND ${tasks.position} > ${decoded.position})
        OR (${tasks.statusId} = ${decoded.statusId} AND ${tasks.position} = ${decoded.position} AND ${tasks.id} > ${decoded.id})
      )` as SQL<boolean>)
    }
  }

  const rows = await db
    .select()
    .from(tasks)
    .where(and(...conditions))
    .orderBy(asc(tasks.statusId), asc(tasks.position), asc(tasks.id))
    .limit(clampedLimit + 1)

  const hasMore = rows.length > clampedLimit
  const pageRows = hasMore ? rows.slice(0, clampedLimit) : rows

  const taskIds = pageRows.map((r) => r.id)
  const labelsMap = await getLabelsForTasks(db, taskIds)

  let nextCursor: string | null = null
  if (hasMore) {
    const last = pageRows[pageRows.length - 1]
    if (last) {
      nextCursor = encodeCursor(
        last.statusId,
        parseFloat(String(last.position)),
        last.id,
      )
    }
  }

  return {
    rows: pageRows.map((r) => serializeTask(r, labelsMap[r.id] ?? [])),
    nextCursor,
  }
}

// ── getTask ───────────────────────────────────────────────────────────────────

export async function getTask(
  db: Db,
  tenantId: string,
  id: string,
): Promise<TaskObject | null> {
  const [row] = await db
    .select()
    .from(tasks)
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.id, id)))
    .limit(1)

  if (!row) return null
  const labels = await getLabelsForTask(db, id)
  return serializeTask(row, labels)
}

// ── createTask ────────────────────────────────────────────────────────────────

export async function createTask(
  db: Db,
  tenantId: string,
  input: CreateTaskInput,
): Promise<TaskObject> {
  return db.transaction(async (tx) => {
    assertTenantOwnsOrThrow(
      'project_id',
      await assertTenantOwnsProject(tx, tenantId, input.project_id),
    )
    assertTenantOwnsOrThrow(
      'status_id',
      await assertTenantOwnsTaskStatus(tx, tenantId, input.status_id),
    )
    assertTenantOwnsOrThrow(
      'assignee_id',
      await assertActiveTenantAssignee(tx, tenantId, input.assignee_id),
    )
    assertTenantOwnsOrThrow(
      'reporter_id',
      await assertActiveTenantAssignee(tx, tenantId, input.reporter_id),
    )

    // Determine position: if not provided, append to end of column
    let position = input.position
    if (position === undefined) {
      // Get the max position in the target column
      const [maxRow] = await tx
        .select({ pos: tasks.position })
        .from(tasks)
        .where(and(eq(tasks.tenantId, tenantId), eq(tasks.statusId, input.status_id)))
        .orderBy(desc(tasks.position))
        .limit(1)

      position = positionBetween(
        maxRow ? parseFloat(String(maxRow.pos)) : null,
        null,
      )
    }

    const [row] = await tx
      .insert(tasks)
      .values({
        tenantId,
        projectId: input.project_id ?? null,
        statusId: input.status_id,
        title: input.title,
        description: input.description ?? null,
        priority: input.priority ?? 'medium',
        assigneeId: input.assignee_id ?? null,
        reporterId: input.reporter_id,
        dueDate: input.due_date ?? null,
        estimatedHours: input.estimated_hours != null ? String(input.estimated_hours) : null,
        source: input.source ?? 'manual',
        externalId: input.external_id ?? null,
        position: String(position),
      })
      .returning()

    if (!row) throw new Error('Task not found after insert')

    const labels = input.labels ?? []
    await setTaskLabels(tx, row.id, labels)

    await tx.insert(auditLog).values({
      tenantId,
      actorId: input.reporter_id ?? null,
      actorType: input.reporter_id ? 'user' : 'system',
      entityType: 'task',
      entityId: row.id,
      action: 'task.created',
    })

    return serializeTask(row, labels)
  })
}

// ── updateTask ────────────────────────────────────────────────────────────────

export async function updateTask(
  db: Db,
  tenantId: string,
  id: string,
  patch: UpdateTaskInput,
): Promise<TaskObject> {
  return db.transaction(async (tx) => {
    // Load current row for position rebalance check
    const [current] = await tx
      .select({ statusId: tasks.statusId, position: tasks.position })
      .from(tasks)
      .where(and(eq(tasks.tenantId, tenantId), eq(tasks.id, id)))
      .limit(1)

    if (!current) throw new Error('Task not found')

    if (patch.project_id !== undefined) {
      assertTenantOwnsOrThrow(
        'project_id',
        await assertTenantOwnsProject(tx, tenantId, patch.project_id),
      )
    }
    if (patch.status_id !== undefined) {
      assertTenantOwnsOrThrow(
        'status_id',
        await assertTenantOwnsTaskStatus(tx, tenantId, patch.status_id),
      )
    }
    if (patch.assignee_id !== undefined) {
      assertTenantOwnsOrThrow(
        'assignee_id',
        await assertActiveTenantAssignee(tx, tenantId, patch.assignee_id),
      )
    }

    const setValues: Partial<typeof tasks.$inferInsert> = {
      updatedAt: new Date(),
    }

    if (patch.title !== undefined) setValues.title = patch.title
    if (patch.project_id !== undefined) setValues.projectId = patch.project_id
    if (patch.status_id !== undefined) setValues.statusId = patch.status_id
    if (patch.priority !== undefined) setValues.priority = patch.priority
    if (patch.assignee_id !== undefined) setValues.assigneeId = patch.assignee_id
    if (patch.due_date !== undefined) setValues.dueDate = patch.due_date
    if (patch.estimated_hours !== undefined)
      setValues.estimatedHours =
        patch.estimated_hours != null ? String(patch.estimated_hours) : null
    if (patch.position !== undefined) setValues.position = String(patch.position)
    if (patch.description !== undefined) setValues.description = patch.description

    const [row] = await tx
      .update(tasks)
      .set(setValues)
      .where(and(eq(tasks.tenantId, tenantId), eq(tasks.id, id)))
      .returning()

    if (!row) throw new Error('Task not found')

    // If position was updated, check whether the column needs a rebalance.
    // We do a lightweight probe: fetch the two neighbors.
    if (patch.position !== undefined) {
      const newStatusId = patch.status_id ?? current.statusId
      const newPos = patch.position

      // Fetch nearest neighbor below and above in same column
      const [prev] = await tx
        .select({ pos: tasks.position })
        .from(tasks)
        .where(
          and(
            eq(tasks.tenantId, tenantId),
            eq(tasks.statusId, newStatusId),
            sql`${tasks.position} < ${String(newPos)}`,
            sql`${tasks.id} != ${id}`,
          ),
        )
        .orderBy(desc(tasks.position))
        .limit(1)

      const [next] = await tx
        .select({ pos: tasks.position })
        .from(tasks)
        .where(
          and(
            eq(tasks.tenantId, tenantId),
            eq(tasks.statusId, newStatusId),
            sql`${tasks.position} > ${String(newPos)}`,
            sql`${tasks.id} != ${id}`,
          ),
        )
        .orderBy(asc(tasks.position))
        .limit(1)

      const before = prev ? parseFloat(String(prev.pos)) : null
      const after = next ? parseFloat(String(next.pos)) : null

      if (needsRebalance(before, after)) {
        await rebalanceColumn(tx, tenantId, newStatusId)
      }
    }

    if (patch.labels !== undefined) {
      await setTaskLabels(tx, id, patch.labels)
    }

    const finalLabels = patch.labels ?? (await getLabelsForTask(tx, id))

    await tx.insert(auditLog).values({
      tenantId,
      actorId: null,
      actorType: 'system',
      entityType: 'task',
      entityId: id,
      action: 'task.updated',
    })

    return serializeTask(row, finalLabels)
  })
}

// ── deleteTask ────────────────────────────────────────────────────────────────

export async function deleteTask(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  const [row] = await db
    .delete(tasks)
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.id, id)))
    .returning({ id: tasks.id })

  if (!row) throw new Error('Task not found')
}

// ── bulkUpdateStatus ──────────────────────────────────────────────────────────

/**
 * Move multiple tasks to a new status column atomically.
 * Position is not changed — tasks retain their original column-local position.
 * Returns the count of updated rows.
 */
export async function bulkUpdateStatus(
  db: Db,
  tenantId: string,
  taskIds: string[],
  statusId: string,
): Promise<number> {
  if (taskIds.length === 0) return 0

  assertTenantOwnsOrThrow(
    'status_id',
    await assertTenantOwnsTaskStatus(db, tenantId, statusId),
  )

  const rows = await db
    .update(tasks)
    .set({ statusId, updatedAt: new Date() })
    .where(
      and(
        eq(tasks.tenantId, tenantId),
        inArray(tasks.id, taskIds),
      ),
    )
    .returning({ id: tasks.id })

  return rows.length
}

// Re-export fractional helpers for route-layer convenience
export { positionBetween, needsRebalance, rebalanceColumn } from '../lib/fractional-index'
// Re-export TaskObject so queries/index.ts can re-alias it
export type { TaskObject } from '@zync/types'