/**
 * Time management query helpers — time-management.
 *
 * All queries are tenant-scoped. Route files import from '@zync/time' which
 * re-exports these helpers. No raw Drizzle in route files.
 *
 * Column names verified against packages/db/src/schema/time.ts:
 *   time_entries: id, tenant_id, user_id, contractor_id, task_id, project_id,
 *     description, started_at, stopped_at, duration_seconds, source, billable,
 *     approval_status, locked_at, locked_reason, created_at, updated_at
 *   magic_link_tokens: id, tenant_id, user_id, task_id, email, token,
 *     token_hash, purpose, expires_at, used_at, created_at
 *
 * projects columns used: id, billing_type
 * retainer_months columns used: id, project_id, tenant_id, month, hours_used
 */
import { and, eq, isNull, isNotNull, lt, gte, lte, desc, asc, sql, count } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import {
  assertTenantOwnsOrThrow,
  assertTenantOwnsProject,
  assertTenantOwnsTask,
} from './tenant-guards'
import { auditLog } from './_audit-forward'
import { captureEntityChange } from './entity-history'
import { timeEntries, magicLinkTokens } from '../schema/time'

/** Optional actor context for operational-audit-trail diff capture. */
export interface TimeActorContext {
  actorName?: string | null
  actorEmail?: string | null
  ipAddress?: string | null
}
import type { TimeEntryRow, NewTimeEntry, MagicLinkTokenRow } from '../schema/time'
import { projects } from '../schema/projects'
import { retainerMonths } from '../schema/projects'
import { tenantSettings } from '../schema/tenants'
import { tasks } from '../schema/tasks'
import { users } from '../schema/users'
import { tenantMemberships } from '../schema/rbac'
import type {
  TimeEntry,
  TimeEntryObject,
  TimeEntrySource,
  TimeRoundingMode,
  WeekSummary,
  WeekDaySummary,
  ManualEntryInput,
  PaginatedResponse,
} from '@zync/types'

// ── Inlined rounding helpers (avoid circular: @zync/db <- @zync/time <- @zync/db) ───

function roundDuration(durationSeconds: number, mode: TimeRoundingMode): number {
  if (mode === 'none') return durationSeconds
  const minuteMap: Record<Exclude<TimeRoundingMode, 'none'>, number> = {
    nearest_5: 5, nearest_15: 15, nearest_30: 30, up_15: 15, up_30: 30,
  }
  const interval = minuteMap[mode] * 60
  if (mode.startsWith('nearest_')) return Math.round(durationSeconds / interval) * interval
  return Math.ceil(durationSeconds / interval) * interval
}

function computeRawSeconds(startedAt: Date | string, stoppedAt: Date | string): number {
  const a = new Date(startedAt).getTime()
  const b = new Date(stoppedAt).getTime()
  return Math.max(0, Math.floor((b - a) / 1000))
}

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

function encodeCursor(startedAt: Date, id: string): string {
  const payload = JSON.stringify({ startedAt: startedAt.toISOString(), id })
  return Buffer.from(payload).toString('base64url')
}

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

// ── Tenant rounding ────────────────────────────────────────────────────────────

export async function getTenantRounding(
  db: Db | DbTx,
  tenantId: string,
): Promise<TimeRoundingMode> {
  const [row] = await db
    .select({ timeRounding: tenantSettings.timeRounding })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)
  return (row?.timeRounding as TimeRoundingMode | undefined) ?? 'none'
}

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

export function serializeTimeEntry(
  row: TimeEntryRow,
  joined: { projectName: string; taskTitle: string | null; userName: string | null },
): TimeEntryObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    userId: row.userId ?? null,
    contractorId: row.contractorId ?? null,
    taskId: row.taskId ?? null,
    projectId: row.projectId,
    description: row.description ?? null,
    startedAt: row.startedAt.toISOString(),
    stoppedAt: row.stoppedAt?.toISOString() ?? null,
    durationSeconds: row.durationSeconds ?? null,
    source: row.source as TimeEntrySource,
    billable: row.billable,
    invoiceId: row.invoiceId ?? null,
    billedAt: row.billedAt?.toISOString() ?? null,
    approvalStatus: row.approvalStatus as TimeEntry['approvalStatus'],
    lockedAt: row.lockedAt?.toISOString() ?? null,
    lockedReason: row.lockedReason ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
    projectName: joined.projectName,
    taskTitle: joined.taskTitle,
    userName: joined.userName,
  }
}

export function serializeTimeEntryBase(row: TimeEntryRow): TimeEntry {
  return {
    id: row.id,
    tenantId: row.tenantId,
    userId: row.userId ?? null,
    contractorId: row.contractorId ?? null,
    taskId: row.taskId ?? null,
    projectId: row.projectId,
    description: row.description ?? null,
    startedAt: row.startedAt.toISOString(),
    stoppedAt: row.stoppedAt?.toISOString() ?? null,
    durationSeconds: row.durationSeconds ?? null,
    source: row.source as TimeEntrySource,
    billable: row.billable,
    invoiceId: row.invoiceId ?? null,
    billedAt: row.billedAt?.toISOString() ?? null,
    approvalStatus: row.approvalStatus as TimeEntry['approvalStatus'],
    lockedAt: row.lockedAt?.toISOString() ?? null,
    lockedReason: row.lockedReason ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  }
}

// ── Retainer hour-bank update ──────────────────────────────────────────────────

/**
 * When a billable entry on a retainer project is stopped, increment
 * retainer_months.hours_used for the entry's calendar month.
 * Creates the row if absent (idempotent on project+month unique constraint).
 */
async function maybeUpdateRetainerHourBank(
  tx: DbTx,
  entry: TimeEntryRow,
  addedSeconds: number,
): Promise<void> {
  if (!entry.billable || addedSeconds <= 0) return

  // Check if this project is a retainer
  const [project] = await tx
    .select({ billingType: projects.billingType })
    .from(projects)
    .where(eq(projects.id, entry.projectId))
    .limit(1)

  if (!project || project.billingType !== 'retainer') return

  // Derive month from startedAt in UTC (simplified; full impl uses tenant tz)
  const startedAt = entry.startedAt
  const month = `${startedAt.getFullYear()}-${String(startedAt.getMonth() + 1).padStart(2, '0')}`

  const addedHours = (addedSeconds / 3600).toString()

  // Upsert retainer month row, incrementing hours_used
  await tx
    .insert(retainerMonths)
    .values({
      projectId: entry.projectId,
      tenantId: entry.tenantId,
      month,
      hoursUsed: addedHours,
    })
    .onConflictDoUpdate({
      target: [retainerMonths.projectId, retainerMonths.month],
      set: {
        hoursUsed: sql`${retainerMonths.hoursUsed} + ${addedHours}::numeric`,
      },
    })
}

// ── Active entry ───────────────────────────────────────────────────────────────

export async function getActiveEntry(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<TimeEntry | null> {
  const [row] = await db
    .select()
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.userId, userId),
        isNull(timeEntries.stoppedAt),
      ),
    )
    .limit(1)
  return row ? serializeTimeEntryBase(row) : null
}

// ── Stop entry ─────────────────────────────────────────────────────────────────

/**
 * Stop a running entry: compute + round duration_seconds, set stopped_at.
 * Updates retainer hour-bank if applicable.
 * Returns the updated serialized entry.
 */
export async function stopEntry(
  db: Db,
  tenantId: string,
  entryId: string,
  options?: { stoppedAt?: Date; source?: TimeEntrySource },
): Promise<TimeEntry> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(timeEntries)
      .where(
        and(
          eq(timeEntries.id, entryId),
          eq(timeEntries.tenantId, tenantId),
          isNull(timeEntries.stoppedAt),
        ),
      )
      .limit(1)

    if (!existing) {
      throw Object.assign(new Error('Time entry not found or already stopped'), { status: 404 })
    }

    const stoppedAt = options?.stoppedAt ?? new Date()
    const rounding = await getTenantRounding(tx as unknown as Db, tenantId)
    const rawSeconds = computeRawSeconds(existing.startedAt, stoppedAt)
    const durationSeconds = roundDuration(rawSeconds, rounding)

    const updateValues: Partial<NewTimeEntry> = {
      stoppedAt,
      durationSeconds,
      updatedAt: new Date(),
    }
    if (options?.source) updateValues.source = options.source

    const [updated] = await tx
      .update(timeEntries)
      .set(updateValues)
      .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.id, entryId)))
      .returning()

    if (!updated) throw new Error('Failed to stop time entry')

    // Update retainer hour bank if applicable
    await maybeUpdateRetainerHourBank(tx, updated, durationSeconds)

    await tx.insert(auditLog).values({
      tenantId,
      actorId: updated.userId,
      actorType: 'user',
      entityType: 'time_entry',
      entityId: entryId,
      action: 'time_entry.stopped',
    })

    return serializeTimeEntryBase(updated)
  })
}

// ── Start entry ────────────────────────────────────────────────────────────────

/**
 * Start a new time entry. Atomically stops any currently running entry for
 * the same user first (there can only be one running entry per user).
 *
 * Returns { entry, stoppedEntry } where stoppedEntry is non-null if a prior
 * running entry was stopped.
 */
export async function startEntry(
  db: Db,
  tenantId: string,
  userId: string,
  input: {
    projectId: string
    taskId?: string | null
    description?: string | null
    source?: TimeEntrySource
    startedAt?: string
  },
): Promise<{ entry: TimeEntry; stoppedEntry: TimeEntry | null }> {
  return db.transaction(async (tx) => {
    assertTenantOwnsOrThrow(
      'projectId',
      await assertTenantOwnsProject(tx, tenantId, input.projectId),
    )
    assertTenantOwnsOrThrow(
      'taskId',
      await assertTenantOwnsTask(tx, tenantId, input.taskId),
    )

    let stoppedEntry: TimeEntry | null = null

    // Stop any currently running entry for this user
    const [running] = await tx
      .select()
      .from(timeEntries)
      .where(
        and(
          eq(timeEntries.tenantId, tenantId),
          eq(timeEntries.userId, userId),
          isNull(timeEntries.stoppedAt),
        ),
      )
      .limit(1)

    if (running) {
      const stoppedAt = new Date()
      const rounding = await getTenantRounding(tx as unknown as Db, tenantId)
      const rawSeconds = computeRawSeconds(running.startedAt, stoppedAt)
      const durationSeconds = roundDuration(rawSeconds, rounding)

      const [stopped] = await tx
        .update(timeEntries)
        .set({ stoppedAt, durationSeconds, updatedAt: new Date() })
        .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.id, running.id)))
        .returning()

      if (stopped) {
        await maybeUpdateRetainerHourBank(tx, stopped, durationSeconds)
        stoppedEntry = serializeTimeEntryBase(stopped)
      }
    }

    const startedAt = input.startedAt ? new Date(input.startedAt) : new Date()

    const [entry] = await tx
      .insert(timeEntries)
      .values({
        tenantId,
        userId,
        projectId: input.projectId,
        taskId: input.taskId ?? null,
        description: input.description ?? null,
        source: input.source ?? 'manual',
        startedAt,
        billable: true,
      })
      .returning()

    if (!entry) throw new Error('Failed to create time entry')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'time_entry',
      entityId: entry.id,
      action: 'time_entry.started',
    })

    return { entry: serializeTimeEntryBase(entry), stoppedEntry }
  })
}

// ── List entries ───────────────────────────────────────────────────────────────

export interface ListEntriesFilter {
  from?: string
  to?: string
  userId?: string
  projectId?: string
  taskId?: string
  cursor?: string
  limit?: number
}

export async function listEntries(
  db: Db,
  tenantId: string,
  filter: ListEntriesFilter,
): Promise<PaginatedResponse<TimeEntryObject>> {
  const limit = Math.min(filter.limit ?? 50, 100)
  const cursor = filter.cursor ? decodeCursor(filter.cursor) : null

  const buildConditions = () => {
    const conds = [eq(timeEntries.tenantId, tenantId)]
    if (filter.userId) conds.push(eq(timeEntries.userId, filter.userId))
    if (filter.projectId) conds.push(eq(timeEntries.projectId, filter.projectId))
    if (filter.taskId) conds.push(eq(timeEntries.taskId, filter.taskId))
    if (filter.from) conds.push(gte(timeEntries.startedAt, new Date(filter.from)))
    if (filter.to) conds.push(lte(timeEntries.startedAt, new Date(filter.to)))
    return conds
  }

  const baseConds = buildConditions()
  const countResult = await db
    .select({ value: count() })
    .from(timeEntries)
    .where(and(...baseConds))
  const total = countResult[0]?.value ?? 0

  const pageConds = [...baseConds]
  if (cursor) {
    pageConds.push(
      sql`(${timeEntries.startedAt}, ${timeEntries.id}) < (${new Date(cursor.startedAt)}, ${cursor.id})`,
    )
  }

  // Join projects for projectName, tasks for taskTitle, users for userName
  const rows = await db
    .select({
      entry: timeEntries,
      projectName: projects.name,
      taskTitle: tasks.title,
      userName: users.name,
    })
    .from(timeEntries)
    .leftJoin(projects, eq(timeEntries.projectId, projects.id))
    .leftJoin(tasks, eq(timeEntries.taskId, tasks.id))
    .leftJoin(users, eq(timeEntries.userId, users.id))
    .where(and(...pageConds))
    .orderBy(desc(timeEntries.startedAt), desc(timeEntries.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const pageRows = hasMore ? rows.slice(0, limit) : rows
  const lastRow = pageRows.at(-1)
  const nextCursor =
    hasMore && lastRow
      ? encodeCursor(lastRow.entry.startedAt, lastRow.entry.id)
      : null

  return {
    items: pageRows.map((r) =>
      serializeTimeEntry(r.entry, {
        projectName: r.projectName ?? 'Unknown',
        taskTitle: r.taskTitle ?? null,
        userName: r.userName ?? null,
      }),
    ),
    nextCursor,
    total,
  }
}

// ── Manual log entry ──────────────────────────────────────────────────────────

export async function logManualEntry(
  db: Db,
  tenantId: string,
  userId: string,
  input: ManualEntryInput,
): Promise<TimeEntry> {
  assertTenantOwnsOrThrow(
    'projectId',
    await assertTenantOwnsProject(db, tenantId, input.projectId),
  )
  assertTenantOwnsOrThrow(
    'taskId',
    await assertTenantOwnsTask(db, tenantId, input.taskId),
  )

  const rounding = await getTenantRounding(db, tenantId)
  const rawSeconds = computeRawSeconds(input.startedAt, input.stoppedAt)
  const durationSeconds = roundDuration(rawSeconds, rounding)

  const [row] = await db
    .insert(timeEntries)
    .values({
      tenantId,
      userId,
      projectId: input.projectId,
      taskId: input.taskId ?? null,
      description: input.description ?? null,
      startedAt: new Date(input.startedAt),
      stoppedAt: new Date(input.stoppedAt),
      durationSeconds,
      source: 'manual',
      billable: input.billable ?? true,
    })
    .returning()

  if (!row) throw new Error('Failed to create time entry')
  return serializeTimeEntryBase(row)
}

// ── Update entry ───────────────────────────────────────────────────────────────

export async function updateEntry(
  db: Db,
  tenantId: string,
  entryId: string,
  patch: Partial<ManualEntryInput> & { billable?: boolean },
  actorCtx?: TimeActorContext,
): Promise<TimeEntry> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(timeEntries)
      .where(and(eq(timeEntries.id, entryId), eq(timeEntries.tenantId, tenantId)))
      .limit(1)

    if (!existing) {
      throw Object.assign(new Error('Time entry not found'), { status: 404 })
    }

    if (existing.lockedAt !== null) {
      throw Object.assign(new Error('Time entry is locked and cannot be edited'), { status: 409 })
    }

    if (patch.projectId !== undefined) {
      assertTenantOwnsOrThrow(
        'projectId',
        await assertTenantOwnsProject(tx, tenantId, patch.projectId),
      )
    }
    if (patch.taskId !== undefined) {
      assertTenantOwnsOrThrow(
        'taskId',
        await assertTenantOwnsTask(tx, tenantId, patch.taskId),
      )
    }

    const updateValues: Partial<NewTimeEntry> = { updatedAt: new Date() }

    if (patch.projectId !== undefined) updateValues.projectId = patch.projectId
    if (patch.taskId !== undefined) updateValues.taskId = patch.taskId ?? null
    if (patch.description !== undefined) updateValues.description = patch.description ?? null
    if (patch.billable !== undefined) updateValues.billable = patch.billable

    // Recompute duration if times change
    const newStartedAt = patch.startedAt ? new Date(patch.startedAt) : existing.startedAt
    const newStoppedAt = patch.stoppedAt
      ? new Date(patch.stoppedAt)
      : existing.stoppedAt

    if (patch.startedAt !== undefined) updateValues.startedAt = newStartedAt
    if (patch.stoppedAt !== undefined) updateValues.stoppedAt = newStoppedAt

    if (
      (patch.startedAt !== undefined || patch.stoppedAt !== undefined) &&
      newStoppedAt !== null
    ) {
      const rounding = await getTenantRounding(tx as unknown as Db, tenantId)
      const rawSeconds = computeRawSeconds(newStartedAt, newStoppedAt)
      updateValues.durationSeconds = roundDuration(rawSeconds, rounding)
    }

    const [updated] = await tx
      .update(timeEntries)
      .set(updateValues)
      .where(and(eq(timeEntries.id, entryId), eq(timeEntries.tenantId, tenantId)))
      .returning()

    if (!updated) throw new Error('Failed to update time entry')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: updated.userId,
      actorType: 'user',
      entityType: 'time_entry',
      entityId: entryId,
      action: 'time_entry.updated',
    })

    // operational-audit-trail: capture changed fields
    const afterState: Record<string, unknown> = {}
    const beforeState: Record<string, unknown> = {}
    if (patch.projectId !== undefined && existing.projectId !== updated.projectId) {
      beforeState['projectId'] = existing.projectId; afterState['projectId'] = updated.projectId
    }
    if (patch.billable !== undefined && existing.billable !== updated.billable) {
      beforeState['billable'] = existing.billable; afterState['billable'] = updated.billable
    }
    if (patch.startedAt !== undefined || patch.stoppedAt !== undefined) {
      if (existing.durationSeconds !== updated.durationSeconds) {
        beforeState['durationSeconds'] = existing.durationSeconds
        afterState['durationSeconds'] = updated.durationSeconds
      }
    }
    if (Object.keys(afterState).length > 0) {
      await captureEntityChange({
        tx, tenantId, userId: updated.userId ?? null,
        actorName: actorCtx?.actorName ?? null,
        actorEmail: actorCtx?.actorEmail ?? null,
        eventType: 'time_entry.field_updated',
        entityType: 'time_entry', entityId: entryId,
        entityLabel: updated.description ?? entryId,
        beforeState,
        afterState,
        ipAddress: actorCtx?.ipAddress ?? null,
      })
    }

    return serializeTimeEntryBase(updated)
  })
}

// ── Delete entry ───────────────────────────────────────────────────────────────

export async function deleteEntry(db: Db, tenantId: string, entryId: string): Promise<void> {
  // Prevent deletion of locked entries
  const [existing] = await db
    .select({ id: timeEntries.id, lockedAt: timeEntries.lockedAt })
    .from(timeEntries)
    .where(and(eq(timeEntries.id, entryId), eq(timeEntries.tenantId, tenantId)))
    .limit(1)

  if (existing?.lockedAt !== null && existing?.lockedAt !== undefined) {
    throw Object.assign(new Error('Time entry is locked and cannot be deleted'), { status: 409 })
  }

  await db
    .delete(timeEntries)
    .where(and(eq(timeEntries.id, entryId), eq(timeEntries.tenantId, tenantId)))
}

// ── Week summary ───────────────────────────────────────────────────────────────

/**
 * Parse ISO week string 'YYYY-WNN' to Monday..Sunday date range (UTC).
 */
function parseIsoWeek(week: string): { monday: Date; sunday: Date } {
  const m = /^(\d{4})-W(\d{2})$/.exec(week)
  if (!m) throw new Error(`Invalid week format: ${week}`)

  const year = parseInt(m[1] ?? '0', 10)
  const weekNum = parseInt(m[2] ?? '0', 10)

  // Jan 4 of the year is always in week 1 (ISO 8601)
  const jan4 = new Date(Date.UTC(year, 0, 4))
  const dayOfWeek = jan4.getUTCDay() || 7 // 1=Mon..7=Sun
  const monday = new Date(jan4)
  monday.setUTCDate(jan4.getUTCDate() - (dayOfWeek - 1) + (weekNum - 1) * 7)

  const sunday = new Date(monday)
  sunday.setUTCDate(monday.getUTCDate() + 6)
  sunday.setUTCHours(23, 59, 59, 999)

  return { monday, sunday }
}

export async function getWeekSummary(
  db: Db,
  tenantId: string,
  week: string,
  userId?: string,
): Promise<WeekSummary> {
  const { monday, sunday } = parseIsoWeek(week)

  const conds = [
    eq(timeEntries.tenantId, tenantId),
    isNotNull(timeEntries.stoppedAt),
    gte(timeEntries.startedAt, monday),
    lte(timeEntries.startedAt, sunday),
  ]
  if (userId) conds.push(eq(timeEntries.userId, userId))

  const rows = await db
    .select({
      startedAt: timeEntries.startedAt,
      durationSeconds: timeEntries.durationSeconds,
    })
    .from(timeEntries)
    .where(and(...conds))
    .orderBy(asc(timeEntries.startedAt))

  // Aggregate by ISO date (UTC)
  const dailyMap = new Map<string, number>()

  // Initialize all 7 days of the week to 0
  for (let i = 0; i < 7; i++) {
    const d = new Date(monday)
    d.setUTCDate(monday.getUTCDate() + i)
    const dateStr = d.toISOString().slice(0, 10) // 'YYYY-MM-DD'
    dailyMap.set(dateStr, 0)
  }

  for (const row of rows) {
    const dateStr = row.startedAt.toISOString().slice(0, 10)
    const secs = row.durationSeconds ?? 0
    dailyMap.set(dateStr, (dailyMap.get(dateStr) ?? 0) + secs)
  }

  const days: WeekDaySummary[] = Array.from(dailyMap.entries()).map(([date, totalSeconds]) => ({
    date,
    totalSeconds,
  }))

  const totalSeconds = days.reduce((sum, d) => sum + d.totalSeconds, 0)

  return { week, days, totalSeconds }
}

// ── Stale-timer cleanup (system-scoped, used by cron) ─────────────────────────

/**
 * Find all entries (across all tenants) running for more than 2 hours.
 * Used by the stale-timer cleanup cron.
 */
export async function findStaleRunningEntries(db: Db): Promise<TimeEntryRow[]> {
  const twoHoursAgo = new Date(Date.now() - 2 * 60 * 60 * 1000)
  return db
    .select()
    .from(timeEntries)
    .where(and(isNull(timeEntries.stoppedAt), lt(timeEntries.startedAt, twoHoursAgo)))
}

/**
 * Stop a stale entry with source='auto'. Applies tenant rounding and updates
 * retainer hour-bank. Returns the stopped entry.
 */
export async function stopStaleEntry(db: Db, entry: TimeEntryRow): Promise<TimeEntry> {
  return db.transaction(async (tx) => {
    const stoppedAt = new Date()
    const rounding = await getTenantRounding(tx as unknown as Db, entry.tenantId)
    const rawSeconds = computeRawSeconds(entry.startedAt, stoppedAt)
    const durationSeconds = roundDuration(rawSeconds, rounding)

    const [updated] = await tx
      .update(timeEntries)
      .set({ stoppedAt, durationSeconds, source: 'auto', updatedAt: new Date() })
      .where(and(eq(timeEntries.tenantId, entry.tenantId), eq(timeEntries.id, entry.id)))
      .returning()

    if (!updated) throw new Error('Failed to stop stale entry')
    await maybeUpdateRetainerHourBank(tx, updated, durationSeconds)

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

    return serializeTimeEntryBase(updated)
  })
}

// ── Magic link token helpers ───────────────────────────────────────────────────

export async function insertTimerMagicLinkToken(
  db: Db,
  args: {
    tenantId: string
    userId: string
    taskId: string
    token: string
    tokenHash: string
    expiresAt: Date
  },
): Promise<void> {
  await db.insert(magicLinkTokens).values({
    tenantId: args.tenantId,
    userId: args.userId,
    taskId: args.taskId,
    token: args.token,
    tokenHash: args.tokenHash,
    purpose: 'timer',
    expiresAt: args.expiresAt,
  })
}

export async function findTimerMagicLinkByHash(
  db: Db,
  tokenHash: string,
): Promise<MagicLinkTokenRow | null> {
  const [row] = await db
    .select()
    .from(magicLinkTokens)
    .where(
      and(
        eq(magicLinkTokens.tokenHash, tokenHash),
        eq(magicLinkTokens.purpose, 'timer'),
        isNull(magicLinkTokens.usedAt),
      ),
    )
    .limit(1)
  return row ?? null
}

/**
 * Atomically mark the token as used — returns true if this call "won" the race
 * (0 rows updated → token already consumed).
 */
export async function consumeTimerMagicLink(db: Db, tokenHash: string): Promise<boolean> {
  const result = await db
    .update(magicLinkTokens)
    .set({ usedAt: new Date() })
    .where(
      and(eq(magicLinkTokens.tokenHash, tokenHash), isNull(magicLinkTokens.usedAt)),
    )
    .returning({ id: magicLinkTokens.id })
  return result.length > 0
}

// ── Time settings ──────────────────────────────────────────────────────────────

export interface TimeTrackingSettingsRow {
  time_rounding: string
  time_min_billable_minutes: number
  time_idle_threshold_minutes: number
  time_auto_pause_on_idle: boolean
  time_standard_hours_per_day: number
  time_flag_overtime: boolean
  time_require_overtime_approval: boolean
  contractor_time_enabled: boolean
  mileage_enabled: boolean
  time_magic_link_enabled: boolean
}

export async function getTimeTrackingSettings(
  db: Db,
  tenantId: string,
): Promise<TimeTrackingSettingsRow> {
  const [row] = await db
    .select({
      time_rounding: tenantSettings.timeRounding,
      time_min_billable_minutes: tenantSettings.timeMinBillableMinutes,
      time_idle_threshold_minutes: tenantSettings.timeIdleThresholdMinutes,
      time_auto_pause_on_idle: tenantSettings.timeAutoPauseOnIdle,
      time_standard_hours_per_day: tenantSettings.timeStandardHoursPerDay,
      time_flag_overtime: tenantSettings.timeFlagOvertime,
      time_require_overtime_approval: tenantSettings.timeRequireOvertimeApproval,
      contractor_time_enabled: tenantSettings.contractorTimeEnabled,
      mileage_enabled: tenantSettings.mileageEnabled,
      time_magic_link_enabled: tenantSettings.timeMagicLinkEnabled,
    })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  return {
    time_rounding: row?.time_rounding ?? 'none',
    time_min_billable_minutes: row?.time_min_billable_minutes ?? 0,
    time_idle_threshold_minutes: row?.time_idle_threshold_minutes ?? 10,
    time_auto_pause_on_idle: row?.time_auto_pause_on_idle ?? true,
    time_standard_hours_per_day: Number(row?.time_standard_hours_per_day ?? 8),
    time_flag_overtime: row?.time_flag_overtime ?? false,
    time_require_overtime_approval: row?.time_require_overtime_approval ?? false,
    contractor_time_enabled: row?.contractor_time_enabled ?? true,
    mileage_enabled: row?.mileage_enabled ?? false,
    time_magic_link_enabled: row?.time_magic_link_enabled ?? true,
  }
}

export interface UpdateTimeTrackingSettingsPatch {
  time_rounding?: string
  time_min_billable_minutes?: number
  time_idle_threshold_minutes?: number
  time_auto_pause_on_idle?: boolean
  time_standard_hours_per_day?: number
  time_flag_overtime?: boolean
  time_require_overtime_approval?: boolean
  contractor_time_enabled?: boolean
  mileage_enabled?: boolean
  time_magic_link_enabled?: boolean
}

export async function updateTimeTrackingSettings(
  db: Db,
  tenantId: string,
  patch: UpdateTimeTrackingSettingsPatch,
): Promise<TimeTrackingSettingsRow> {
  const updateValues: Record<string, unknown> = { updatedAt: new Date() }
  if (patch.time_rounding !== undefined) {
    updateValues['timeRounding'] = patch.time_rounding
  }
  if (patch.time_min_billable_minutes !== undefined) {
    updateValues['timeMinBillableMinutes'] = patch.time_min_billable_minutes
  }
  if (patch.time_idle_threshold_minutes !== undefined) {
    updateValues['timeIdleThresholdMinutes'] = patch.time_idle_threshold_minutes
  }
  if (patch.time_auto_pause_on_idle !== undefined) {
    updateValues['timeAutoPauseOnIdle'] = patch.time_auto_pause_on_idle
  }
  if (patch.time_standard_hours_per_day !== undefined) {
    updateValues['timeStandardHoursPerDay'] = String(patch.time_standard_hours_per_day)
  }
  if (patch.time_flag_overtime !== undefined) {
    updateValues['timeFlagOvertime'] = patch.time_flag_overtime
  }
  if (patch.time_require_overtime_approval !== undefined) {
    updateValues['timeRequireOvertimeApproval'] = patch.time_require_overtime_approval
  }
  if (patch.contractor_time_enabled !== undefined) {
    updateValues['contractorTimeEnabled'] = patch.contractor_time_enabled
  }
  if (patch.mileage_enabled !== undefined) {
    updateValues['mileageEnabled'] = patch.mileage_enabled
  }
  if (patch.time_magic_link_enabled !== undefined) {
    updateValues['timeMagicLinkEnabled'] = patch.time_magic_link_enabled
  }

  // Upsert — tenant_settings may not exist yet for a fresh tenant
  await db
    .insert(tenantSettings)
    .values({ tenantId, ...updateValues })
    .onConflictDoUpdate({
      target: tenantSettings.tenantId,
      set: updateValues,
    })

  return getTimeTrackingSettings(db, tenantId)
}

// ── Get entry by ID (tenant-scoped) ───────────────────────────────────────────

export async function getTimeEntryById(
  db: Db | DbTx,
  tenantId: string,
  entryId: string,
): Promise<TimeEntryRow | null> {
  const [row] = await db
    .select()
    .from(timeEntries)
    .where(and(eq(timeEntries.id, entryId), eq(timeEntries.tenantId, tenantId)))
    .limit(1)
  return row ?? null
}

// ── Task / user lookup helpers (for magic-link and create-link routes) ─────────

/**
 * Get projectId from a task. Used by magic-link start to derive project scope.
 */
export async function getTaskProjectId(
  db: Db,
  tenantId: string,
  taskId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ projectId: tasks.projectId })
    .from(tasks)
    .where(and(eq(tasks.id, taskId), eq(tasks.tenantId, tenantId)))
    .limit(1)
  return row?.projectId ?? null
}

/**
 * Get task title by id.
 */
export async function getTaskTitle(
  db: Db,
  tenantId: string,
  taskId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ title: tasks.title })
    .from(tasks)
    .where(and(eq(tasks.id, taskId), eq(tasks.tenantId, tenantId)))
    .limit(1)
  return row?.title ?? null
}

/**
 * Get user email and id by email (for magic-link recipient lookup).
 */
export async function getUserByEmail(
  db: Db,
  email: string,
): Promise<{ id: string; email: string } | null> {
  const [row] = await db
    .select({ id: users.id, email: users.email })
    .from(users)
    .where(eq(users.email, email))
    .limit(1)
  return row ?? null
}

/**
 * Resolve an active tenant member by email (timer magic-link recipient lookup).
 */
export async function getActiveTenantMemberByEmail(
  db: Db,
  tenantId: string,
  email: string,
): Promise<{ id: string; email: string } | null> {
  const [row] = await db
    .select({ id: users.id, email: users.email })
    .from(users)
    .innerJoin(tenantMemberships, eq(tenantMemberships.userId, users.id))
    .where(
      and(
        eq(users.email, email),
        eq(tenantMemberships.tenantId, tenantId),
        eq(tenantMemberships.status, 'active'),
      ),
    )
    .limit(1)
  return row ?? null
}

/**
 * Get user email by userId (for send-to-self magic link).
 */
export async function getUserEmailById(
  db: Db,
  userId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ email: users.email })
    .from(users)
    .where(eq(users.id, userId))
    .limit(1)
  return row?.email ?? null
}