/**
 * Project query helpers — projects module.
 *
 * All helpers are tenant-filtered: every statement carries a tenant_id WHERE
 * clause bound at call-site. Route files MUST NOT import raw Drizzle tables —
 * they call these helpers via the tenantQuery factory.
 *
 * Cursor encoding: base64url of JSON `{ field: string, id: string }`.
 * Default sort: updated_at DESC, id DESC.
 */
import {
  and,
  eq,
  or,
  inArray,
  lt,
  asc,
  desc,
  count,
  sql,
  type SQL,
} from 'drizzle-orm'
import type { Db } from '../client'
import { projects, projectMembers, retainerMonths } from '../schema/projects'
import { customers } from '../schema/customers'
import { users } from '../schema/users'
import { tasks, taskLabels, taskStatuses } from '../schema/tasks'
import { timeEntries } from '../schema/time'
import { invoices } from '../schema/invoices'
import type { NewProject, NewProjectMember, NewRetainerMonth } from '../schema/projects'
import { auditLog } from './_audit-forward'
import { captureEntityChange } from './entity-history'
import {
  assertActiveTenantAssignee,
  assertTenantOwnsCustomer,
  assertTenantOwnsOrThrow,
} from './tenant-guards'
import {
  serializeProject,
  serializeProjectMember,
  serializeRetainerMonth,
} from '../serialize/projects'
import { createDepositMilestone } from './project-milestones'

/** Optional actor context for operational-audit-trail diff capture. */
export interface ProjectActorContext {
  actorName?: string | null
  actorEmail?: string | null
  ipAddress?: string | null
}
import type {
  ProjectObject,
  ProjectMemberObject,
  RetainerMonthObject,
  ProjectStats,
  ListProjectsParams,
  CreateProjectInput,
  UpdateProjectInput,
  AddMemberInput,
  UpdateMemberInput,
  RetainerBillingConfig,
  TaskObject,
} from '@zync/types'
import { getTasksActualHoursMap } from './tasks'

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

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

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

// ── List projects ──────────────────────────────────────────────────────────────

export async function listProjects(
  db: Db,
  tenantId: string,
  params: ListProjectsParams,
  opts: { fullVisibility: boolean; userId: string },
): Promise<{ items: ProjectObject[]; nextCursor: string | null; total: number }> {
  const limit = Math.min(params.limit ?? 50, 100)
  const sort = params.sort ?? 'updated_at'

  const buildFilters = (withCursor?: { field: string; id: string }) => {
    const statuses = Array.isArray(params.status) ? params.status : params.status ? [params.status] : ['active', 'completed']
    const conditions = [
      eq(projects.tenantId, tenantId),
      ...(statuses.length === 1 ? [eq(projects.status, statuses[0]!)] : [inArray(projects.status, statuses)]),
      ...(params.billing_type ? [eq(projects.billingType, params.billing_type)] : []),
      ...(params.customer_id ? [eq(projects.customerId, params.customer_id)] : []),
    ]

    if (!opts.fullVisibility) {
      // restrict to projects where the user is a member
      conditions.push(sql`EXISTS (
          SELECT 1 FROM project_members pm
          WHERE pm.project_id = ${projects.id}
            AND pm.tenant_id = ${tenantId}
            AND pm.user_id = ${opts.userId}
        )` as SQL<boolean>)
    }

    if (withCursor) {
      const cursorDate = new Date(withCursor.field)
      if (sort === 'updated_at') {
        const cond = or(
          lt(projects.updatedAt, cursorDate),
          and(eq(projects.updatedAt, cursorDate), lt(projects.id, withCursor.id)),
        )
        if (cond) conditions.push(cond as SQL<boolean>)
      } else if (sort === 'start_date') {
        const cond = or(
          sql`${projects.startDate} < ${withCursor.field}`,
          and(
            sql`${projects.startDate} = ${withCursor.field}`,
            lt(projects.id, withCursor.id),
          ),
        )
        if (cond) conditions.push(cond as SQL<boolean>)
      } else if (sort === 'name') {
        const cond = or(
          sql`${projects.name} > ${withCursor.field}`,
          and(sql`${projects.name} = ${withCursor.field}`, lt(projects.id, withCursor.id)),
        )
        if (cond) conditions.push(cond as SQL<boolean>)
      }
    }

    return and(...(conditions as SQL<boolean>[]))
  }

  // total count (no cursor)
  const [totalRow] = await db
    .select({ count: count() })
    .from(projects)
    .where(buildFilters())

  const total = Number(totalRow?.count ?? 0)

  // cursor condition
  let cursorData: { field: string; id: string } | undefined
  if (params.cursor) {
    const decoded = decodeCursor(params.cursor)
    if (decoded) cursorData = decoded
  }

  const orderBy =
    sort === 'name'
      ? [asc(projects.name), desc(projects.id)]
      : sort === 'start_date'
        ? [desc(projects.startDate), desc(projects.id)]
        : [desc(projects.updatedAt), desc(projects.id)]

  const rows = await db
    .select({
      id: projects.id,
      tenantId: projects.tenantId,
      customerId: projects.customerId,
      customerName: customers.name,
      name: projects.name,
      description: projects.description,
      status: projects.status,
      billingType: projects.billingType,
      billingConfig: projects.billingConfig,
      currency: projects.currency,
      startDate: projects.startDate,
      endDate: projects.endDate,
      createdBy: projects.createdBy,
      createdAt: projects.createdAt,
      updatedAt: projects.updatedAt,
      completedAt: projects.completedAt,
      archivedAt: projects.archivedAt,
    })
    .from(projects)
    .leftJoin(customers, eq(projects.customerId, customers.id))
    .where(buildFilters(cursorData))
    .orderBy(...orderBy)
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows
  const lastItem = items[items.length - 1]

  let nextCursor: string | null = null
  if (hasMore && lastItem) {
    const cursorField =
      sort === 'name'
        ? lastItem.name
        : sort === 'start_date'
          ? (lastItem.startDate ?? '')
          : lastItem.updatedAt
    nextCursor = encodeCursor(cursorField, lastItem.id)
  }

  return { items: items.map(serializeProject), nextCursor, total }
}

// ── Access control ──────────────────────────────────────────────────────────────

/**
 * Per-request project access. `fullVisibility` is true when the caller's role
 * carries tenant-wide projects:read; otherwise the caller may only reach
 * projects they are a member of. Mirrors the gate listProjects applies, so the
 * by-id and member helpers enforce the same visibility rule (no IDOR).
 */
export type ProjectAccess = { fullVisibility: boolean; userId: string }

// Correlated EXISTS: the caller is a member of the project (tenant-scoped).
// Usable in both SELECT and UPDATE WHERE clauses (projects.id correlates to the row).
const callerIsMember = (tenantId: string, userId: string) => sql`EXISTS (
    SELECT 1 FROM project_members pm
    WHERE pm.project_id = ${projects.id}
      AND pm.tenant_id = ${tenantId}
      AND pm.user_id = ${userId}
  )`

export interface ProjectEstimateSummary {
  taskCount: number
  totalEstimated: number
  totalLogged: number
  remaining: number
  budgetConsumedPct: number
  paceStatus: 'on_track' | 'at_risk' | null
}

function serializeProjectTask(row: typeof tasks.$inferSelect, labels: string[], actualHours: number): 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,
    actual_hours: actualHours,
    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(),
  }
}

async function getProjectTaskLabels(
  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(sql`${taskLabels.taskId} = ANY(ARRAY[${sql.join(taskIds.map((taskId) => sql`${taskId}`), sql`, `)}]::uuid[])`)

  const labelsByTaskId: Record<string, string[]> = {}
  for (const row of rows) {
    if (!labelsByTaskId[row.taskId]) labelsByTaskId[row.taskId] = []
    labelsByTaskId[row.taskId]!.push(row.label)
  }
  return labelsByTaskId
}

function computePaceStatus(
  startDate: string | null,
  endDate: string | null,
  totalEstimated: number,
  totalLogged: number,
): 'on_track' | 'at_risk' | null {
  if (!startDate || !endDate) return null

  const start = new Date(`${startDate}T00:00:00.000Z`)
  const end = new Date(`${endDate}T23:59:59.999Z`)
  const startMs = start.getTime()
  const endMs = end.getTime()
  if (!Number.isFinite(startMs) || !Number.isFinite(endMs) || endMs <= startMs) return null

  const elapsedPct = Math.min(1, Math.max(0, (Date.now() - startMs) / (endMs - startMs)))
  const expectedLogged = elapsedPct * totalEstimated

  if (totalLogged > expectedLogged * 1.2) return 'at_risk'
  return 'on_track'
}

// ── Get project by ID ──────────────────────────────────────────────────────────

export async function getProjectById(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<ProjectObject | null> {
  const conditions = [eq(projects.tenantId, tenantId), eq(projects.id, projectId)]
  if (access && !access.fullVisibility) {
    conditions.push(callerIsMember(tenantId, access.userId))
  }

  const [row] = await db
    .select({
      id: projects.id,
      tenantId: projects.tenantId,
      customerId: projects.customerId,
      customerName: customers.name,
      name: projects.name,
      description: projects.description,
      status: projects.status,
      billingType: projects.billingType,
      billingConfig: projects.billingConfig,
      currency: projects.currency,
      startDate: projects.startDate,
      endDate: projects.endDate,
      createdBy: projects.createdBy,
      createdAt: projects.createdAt,
      updatedAt: projects.updatedAt,
      completedAt: projects.completedAt,
      archivedAt: projects.archivedAt,
    })
    .from(projects)
    .leftJoin(customers, eq(projects.customerId, customers.id))
    .where(and(...conditions))
    .limit(1)

  return row ? serializeProject(row) : null
}

// ── Get project with stats ─────────────────────────────────────────────────────

export function getUtcMonthStartIso(date: Date): string {
  const monthStart = new Date(date)
  monthStart.setUTCDate(1)
  monthStart.setUTCHours(0, 0, 0, 0)
  return monthStart.toISOString()
}

export async function getProjectWithStats(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<(ProjectObject & { stats: ProjectStats }) | null> {
  const project = await getProjectById(db, tenantId, projectId, access)
  if (!project) return null

  const monthStart = getUtcMonthStartIso(new Date())

  const [taskStatsRow] = await db
    .select({
      totalTasks: count(tasks.id),
      openTasks: sql<number>`COUNT(CASE WHEN ${taskStatuses.isTerminal} = false THEN 1 END)`,
    })
    .from(tasks)
    .innerJoin(taskStatuses, eq(tasks.statusId, taskStatuses.id))
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.projectId, projectId)))

  const [timeStatsRow] = await db
    .select({
      hoursAllTime:
        sql<string | null>`COALESCE(SUM(${timeEntries.durationSeconds}) / 3600.0, 0)`,
      hoursThisMonth:
        sql<string | null>`COALESCE(SUM(CASE WHEN ${timeEntries.startedAt} >= ${monthStart} THEN ${timeEntries.durationSeconds} ELSE 0 END) / 3600.0, 0)`,
      unbilledHours:
        sql<string | null>`COALESCE(SUM(CASE WHEN ${timeEntries.billable} = true AND ${timeEntries.invoiceId} IS NULL THEN ${timeEntries.durationSeconds} ELSE 0 END) / 3600.0, 0)`,
    })
    .from(timeEntries)
    .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.projectId, projectId)))

  const [invoiceStatsRow] = await db
    .select({
      invoicesPaid: sql<number>`COUNT(CASE WHEN ${invoices.status} = 'PAID' THEN 1 END)`,
      invoicesOutstanding:
        sql<number>`COUNT(CASE WHEN ${invoices.status} IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID') THEN 1 END)`,
    })
    .from(invoices)
    .where(and(eq(invoices.tenantId, tenantId), eq(invoices.projectId, projectId)))

  const stats: ProjectStats = {
    total_tasks: Number(taskStatsRow?.totalTasks ?? 0),
    open_tasks: Number(taskStatsRow?.openTasks ?? 0),
    hours_this_month: Number.parseFloat(String(timeStatsRow?.hoursThisMonth ?? '0')),
    hours_all_time: Number.parseFloat(String(timeStatsRow?.hoursAllTime ?? '0')),
    unbilled_hours: Number.parseFloat(String(timeStatsRow?.unbilledHours ?? '0')),
    invoices_paid: Number(invoiceStatsRow?.invoicesPaid ?? 0),
    invoices_outstanding: Number(invoiceStatsRow?.invoicesOutstanding ?? 0),
  }

  return { ...project, stats }
}

export async function getProjectTasksWithActualHours(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<Array<TaskObject & { actual_hours: number }>> {
  const project = await getProjectById(db, tenantId, projectId, access)
  if (!project) throw new Error('Project not found')

  const rows = await db
    .select()
    .from(tasks)
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.projectId, projectId)))
    .orderBy(asc(tasks.statusId), asc(tasks.position), asc(tasks.id))

  const taskIds = rows.map((row) => row.id)
  const [actualHoursByTaskId, labelsByTaskId] = await Promise.all([
    getTasksActualHoursMap(db, tenantId, taskIds),
    getProjectTaskLabels(db, taskIds),
  ])

  return rows.map((row) =>
    serializeProjectTask(row, labelsByTaskId[row.id] ?? [], actualHoursByTaskId.get(row.id) ?? 0) as TaskObject & {
      actual_hours: number
    })
}

export async function getProjectEstimateSummary(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<ProjectEstimateSummary> {
  const project = await getProjectById(db, tenantId, projectId, access)
  if (!project) throw new Error('Project not found')

  const [estimateRow] = await db
    .select({
      taskCount: count(tasks.id),
      totalEstimated: sql<string | number>`COALESCE(SUM(COALESCE(${tasks.estimatedHours}, 0)), 0)`,
    })
    .from(tasks)
    .where(and(eq(tasks.tenantId, tenantId), eq(tasks.projectId, projectId)))

  const [loggedRow] = await db
    .select({
      totalLogged: sql<string | number>`COALESCE(SUM(${timeEntries.durationSeconds}) / 3600.0, 0)`,
    })
    .from(timeEntries)
    .innerJoin(tasks, eq(timeEntries.taskId, tasks.id))
    .where(and(eq(timeEntries.tenantId, tenantId), eq(tasks.projectId, projectId)))

  const taskCount = Number(estimateRow?.taskCount ?? 0)
  const totalEstimated = Number(estimateRow?.totalEstimated ?? 0)
  const totalLogged = Number(loggedRow?.totalLogged ?? 0)
  const remaining = Math.max(0, Math.round((totalEstimated - totalLogged) * 100) / 100)
  const budgetConsumedPct =
    totalEstimated > 0 ? Math.round((totalLogged / totalEstimated) * 100) : 0

  return {
    taskCount,
    totalEstimated,
    totalLogged,
    remaining,
    budgetConsumedPct,
    paceStatus: computePaceStatus(project.start_date, project.end_date, totalEstimated, totalLogged),
  }
}

// ── Create project ─────────────────────────────────────────────────────────────

export async function createProject(
  db: Db,
  tenantId: string,
  input: CreateProjectInput,
  createdBy: string,
  _queue?: { send: (msg: unknown) => Promise<unknown> },
): Promise<ProjectObject> {
  const project = await db.transaction(async (tx) => {
    assertTenantOwnsOrThrow(
      'customer_id',
      await assertTenantOwnsCustomer(tx, tenantId, input.customer_id),
    )

    const values: NewProject = {
      tenantId,
      customerId: input.customer_id ?? null,
      name: input.name,
      description: input.description ?? null,
      billingType: input.billing_type,
      billingConfig: input.billing_config ?? null,
      currency: input.currency ?? 'ILS',
      startDate: input.start_date ?? null,
      endDate: input.end_date ?? null,
      createdBy,
      status: 'active',
    }

    const [row] = await tx.insert(projects).values(values).returning()
    if (!row) throw new Error('Project not found after insert')

    const requestedMembers = input.members ?? []
    if (requestedMembers.length > 0) {
      for (const m of requestedMembers) {
        assertTenantOwnsOrThrow(
          'members',
          await assertActiveTenantAssignee(tx, tenantId, m.user_id),
        )
      }
    }

    const memberMap = new Map<string, NewProjectMember>()
    memberMap.set(createdBy, {
      projectId: row.id,
      userId: createdBy,
      tenantId,
      role: 'owner',
      hourlyRate: null,
    })

    for (const m of requestedMembers) {
      memberMap.set(m.user_id, {
        projectId: row.id,
        userId: m.user_id,
        tenantId,
        role: m.user_id === createdBy ? 'owner' : (m.role ?? 'member'),
        hourlyRate: m.hourly_rate != null ? String(m.hourly_rate) : null,
      })
    }

    await tx.insert(projectMembers).values([...memberMap.values()])

    await tx.insert(auditLog).values({
      tenantId,
      actorId: createdBy,
      actorType: 'user',
      entityType: 'project',
      entityId: row.id,
      action: 'project.created',
      changes: null,
    })

    if (input.billing_type === 'fixed') {
      await createDepositMilestone(tx, {
        tenantId,
        projectId: row.id,
        billingConfig: input.billing_config ?? null,
        createdBy,
      })
    }

    return serializeProject(row)
  })

  return project
}

// ── Update project ─────────────────────────────────────────────────────────────

export async function updateProject(
  db: Db,
  tenantId: string,
  projectId: string,
  patch: UpdateProjectInput,
  actorId?: string,
  access?: ProjectAccess,
  actorCtx?: ProjectActorContext,
): Promise<ProjectObject> {
  return db.transaction(async (tx) => {
    if (patch.customer_id !== undefined) {
      assertTenantOwnsOrThrow(
        'customer_id',
        await assertTenantOwnsCustomer(tx, tenantId, patch.customer_id),
      )
    }

    const setValues: Partial<NewProject> = { updatedAt: new Date() }

    if (patch.name !== undefined) setValues.name = patch.name
    if (patch.customer_id !== undefined) setValues.customerId = patch.customer_id
    if (patch.description !== undefined) setValues.description = patch.description
    if (patch.status !== undefined) setValues.status = patch.status
    if (patch.billing_type !== undefined) setValues.billingType = patch.billing_type
    if (patch.billing_config !== undefined) setValues.billingConfig = patch.billing_config
    if (patch.currency !== undefined) setValues.currency = patch.currency
    if (patch.start_date !== undefined) setValues.startDate = patch.start_date
    if (patch.end_date !== undefined) setValues.endDate = patch.end_date

    const conditions = [eq(projects.tenantId, tenantId), eq(projects.id, projectId)]
    if (access && !access.fullVisibility) {
      conditions.push(callerIsMember(tenantId, access.userId))
    }

    const [row] = await tx
      .update(projects)
      .set(setValues)
      .where(and(...conditions))
      .returning()

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

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'project',
      entityId: projectId,
      action: 'project.updated',
      changes: null,
    })

    // operational-audit-trail: capture field diff for status changes and field updates
    const isStatusChange = patch.status !== undefined
    const eventType = isStatusChange ? 'project.status_changed' : 'project.field_updated'
    const afterState: Record<string, unknown> = {}
    if (patch.name !== undefined) afterState['name'] = row.name
    if (patch.status !== undefined) afterState['status'] = row.status
    if (patch.customer_id !== undefined) afterState['customerId'] = row.customerId
    if (patch.description !== undefined) afterState['description'] = row.description
    if (Object.keys(afterState).length > 0) {
      await captureEntityChange({
        tx, tenantId, userId: actorId ?? null,
        actorName: actorCtx?.actorName ?? null,
        actorEmail: actorCtx?.actorEmail ?? null,
        eventType,
        entityType: 'project', entityId: projectId,
        entityLabel: row.name,
        beforeState: null,
        afterState,
        ipAddress: actorCtx?.ipAddress ?? null,
      })
    }

    return serializeProject(row)
  })
}

// ── Archive project (soft delete) ─────────────────────────────────────────────

export async function archiveProject(
  db: Db,
  tenantId: string,
  projectId: string,
  actorId?: string,
  access?: ProjectAccess,
  actorCtx?: ProjectActorContext,
): Promise<void> {
  return db.transaction(async (tx) => {
    const conditions = [eq(projects.tenantId, tenantId), eq(projects.id, projectId)]
    if (access && !access.fullVisibility) {
      conditions.push(callerIsMember(tenantId, access.userId))
    }

    const [row] = await tx
      .update(projects)
      .set({ status: 'archived', updatedAt: new Date(), archivedAt: new Date() })
      .where(and(...conditions))
      .returning()

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

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'project',
      entityId: projectId,
      action: 'project.archived',
      changes: null,
    })

    // operational-audit-trail: status change
    await captureEntityChange({
      tx, tenantId, userId: actorId ?? null,
      actorName: actorCtx?.actorName ?? null,
      actorEmail: actorCtx?.actorEmail ?? null,
      eventType: 'project.status_changed',
      entityType: 'project', entityId: projectId,
      entityLabel: row.name,
      beforeState: { status: row.status === 'archived' ? 'active' : row.status },
      afterState: { status: 'archived' },
      ipAddress: actorCtx?.ipAddress ?? null,
    })
  })
}

// ── Member helpers ─────────────────────────────────────────────────────────────

export async function listProjectMembers(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<ProjectMemberObject[]> {
  if (access && !access.fullVisibility) {
    // caller must have access to the project itself before listing its members
    const proj = await getProjectById(db, tenantId, projectId, access)
    if (!proj) throw new Error('Project not found')
  }

  const rows = await db
    .select({
      projectId: projectMembers.projectId,
      userId: projectMembers.userId,
      tenantId: projectMembers.tenantId,
      role: projectMembers.role,
      hourlyRate: projectMembers.hourlyRate,
      createdAt: projectMembers.createdAt,
      userName: users.name,
      userEmail: users.email,
    })
    .from(projectMembers)
    .leftJoin(users, eq(projectMembers.userId, users.id))
    .where(
      and(
        eq(projectMembers.tenantId, tenantId),
        eq(projectMembers.projectId, projectId),
      ),
    )
    .orderBy(asc(projectMembers.createdAt))

  return rows.map(serializeProjectMember)
}

/**
 * addProjectMember — upserts on PK (project_id, user_id) so re-adding an
 * existing member updates their role/rate instead of throwing a PK violation.
 */
export async function addProjectMember(
  db: Db,
  tenantId: string,
  projectId: string,
  input: AddMemberInput,
  actorId?: string,
  access?: ProjectAccess,
): Promise<ProjectMemberObject> {
  return db.transaction(async (tx) => {
    if (access && !access.fullVisibility) {
      const [actorRow] = await tx
        .select({ role: projectMembers.role })
        .from(projectMembers)
        .where(
          and(
            eq(projectMembers.tenantId, tenantId),
            eq(projectMembers.projectId, projectId),
            eq(projectMembers.userId, access.userId),
          ),
        )
        .limit(1)
      if (!actorRow) throw new Error('Project not found')
      // only owners (or full-visibility admins) may grant/modify the owner role
      if ((input.role ?? 'member') === 'owner' && actorRow.role !== 'owner') {
        throw new Error('Forbidden: only project owners can assign the owner role')
      }
    }

    assertTenantOwnsOrThrow(
      'members',
      await assertActiveTenantAssignee(tx, tenantId, input.user_id),
    )

    const values: NewProjectMember = {
      projectId,
      userId: input.user_id,
      tenantId,
      role: input.role ?? 'member',
      hourlyRate: input.hourly_rate != null ? String(input.hourly_rate) : null,
    }

    const [row] = await tx
      .insert(projectMembers)
      .values(values)
      .onConflictDoUpdate({
        target: [projectMembers.projectId, projectMembers.userId],
        set: {
          role: values.role,
          hourlyRate: values.hourlyRate,
        },
      })
      .returning()

    if (!row) throw new Error('Member not found after upsert')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'project_member',
      entityId: projectId,
      action: 'project_member.upserted',
      changes: null,
    })

    return serializeProjectMember(row)
  })
}

export async function updateProjectMember(
  db: Db,
  tenantId: string,
  projectId: string,
  userId: string,
  patch: UpdateMemberInput,
  actorId?: string,
): Promise<ProjectMemberObject> {
  return db.transaction(async (tx) => {
    const setValues: Partial<NewProjectMember> = {}
    if (patch.role !== undefined) setValues.role = patch.role
    if (patch.hourly_rate !== undefined)
      setValues.hourlyRate = patch.hourly_rate != null ? String(patch.hourly_rate) : null

    const [row] = await tx
      .update(projectMembers)
      .set(setValues)
      .where(
        and(
          eq(projectMembers.tenantId, tenantId),
          eq(projectMembers.projectId, projectId),
          eq(projectMembers.userId, userId),
        ),
      )
      .returning()

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

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'project_member',
      entityId: projectId,
      action: 'project_member.updated',
      changes: null,
    })

    return serializeProjectMember(row)
  })
}

export async function removeProjectMember(
  db: Db,
  tenantId: string,
  projectId: string,
  userId: string,
  actorId?: string,
  access?: ProjectAccess,
): Promise<void> {
  return db.transaction(async (tx) => {
    if (access && !access.fullVisibility) {
      const [actorRow] = await tx
        .select({ role: projectMembers.role })
        .from(projectMembers)
        .where(
          and(
            eq(projectMembers.tenantId, tenantId),
            eq(projectMembers.projectId, projectId),
            eq(projectMembers.userId, access.userId),
          ),
        )
        .limit(1)
      if (!actorRow) throw new Error('Project not found')
      const [targetRow] = await tx
        .select({ role: projectMembers.role })
        .from(projectMembers)
        .where(
          and(
            eq(projectMembers.tenantId, tenantId),
            eq(projectMembers.projectId, projectId),
            eq(projectMembers.userId, userId),
          ),
        )
        .limit(1)
      // only owners (or full-visibility admins) may remove an owner
      if (targetRow?.role === 'owner' && actorRow.role !== 'owner') {
        throw new Error('Forbidden: only project owners can remove an owner')
      }
    }

    await tx
      .delete(projectMembers)
      .where(
        and(
          eq(projectMembers.tenantId, tenantId),
          eq(projectMembers.projectId, projectId),
          eq(projectMembers.userId, userId),
        ),
      )

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'project_member',
      entityId: projectId,
      action: 'project_member.removed',
      changes: null,
    })
  })
}

// ── Hours summary ──────────────────────────────────────────────────────────────

/**
 * getProjectHours — returns { this_month, all_time } hour totals.
 * When time-management entries table is present, this should JOIN to it.
 * Until then returns zeroed totals so this module builds standalone.
 */
export async function getProjectHours(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<{ this_month: number; all_time: number }> {
  const project = await getProjectById(db, tenantId, projectId, access)
  if (!project) throw new Error('Project not found')

  const monthStart = getUtcMonthStartIso(new Date())

  const [row] = await db
    .select({
      thisMonth:
        sql<string | null>`COALESCE(SUM(CASE WHEN ${timeEntries.startedAt} >= ${monthStart} THEN ${timeEntries.durationSeconds} ELSE 0 END) / 3600.0, 0)`,
      allTime: sql<string | null>`COALESCE(SUM(${timeEntries.durationSeconds}) / 3600.0, 0)`,
    })
    .from(timeEntries)
    .where(and(eq(timeEntries.tenantId, tenantId), eq(timeEntries.projectId, projectId)))

  return {
    this_month: Number.parseFloat(String(row?.thisMonth ?? '0')),
    all_time: Number.parseFloat(String(row?.allTime ?? '0')),
  }
}

// ── Retainer months ────────────────────────────────────────────────────────────

export async function listRetainerMonths(
  db: Db,
  tenantId: string,
  projectId: string,
  access?: ProjectAccess,
): Promise<RetainerMonthObject[]> {
  const project = await getProjectById(db, tenantId, projectId, access)
  if (!project) throw new Error('Project not found')

  const rows = await db
    .select()
    .from(retainerMonths)
    .where(
      and(
        eq(retainerMonths.tenantId, tenantId),
        eq(retainerMonths.projectId, projectId),
      ),
    )
    .orderBy(desc(retainerMonths.month))

  return rows.map(serializeRetainerMonth)
}

// ── Retainer hour-bank engine ──────────────────────────────────────────────────

/**
 * incrementRetainerHours — atomically upserts the retainer_months row for
 * (project_id, month), seeds hours_included from billing_config (+ rolled over
 * from prior month), and adds `hours` to hours_used.
 *
 * Depletion check: when hours_used >= hours_included AND invoice_triggered_at IS NULL:
 *  - sets invoice_triggered_at = now()
 *  - enqueues QUEUE message with type 'retainer.invoice' (when auto_invoice + overflow=invoice)
 *
 * The entire read-modify-write runs in a transaction to prevent concurrent
 * double-triggers (satisfies require-audit-in-transaction).
 */
export async function incrementRetainerHours(
  db: Db,
  tenantId: string,
  projectId: string,
  month: string,
  hours: number,
  // The QUEUE binding is passed in so the engine can enqueue from the route context
  queue?: { send: (msg: unknown) => Promise<void> },
): Promise<RetainerMonthObject> {
  return db.transaction(async (tx) => {
    // Load the project to read billing_config
    const [projectRow] = await tx
      .select({
        id: projects.id,
        billingType: projects.billingType,
        billingConfig: projects.billingConfig,
      })
      .from(projects)
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .limit(1)

    if (!projectRow) throw new Error('Project not found')
    if (projectRow.billingType !== 'retainer') throw new Error('Project is not a retainer')

    const config = projectRow.billingConfig as RetainerBillingConfig | null

    // Determine hours_included: from billing_config + any rolled-over hours from prior month
    const baseIncluded = config?.monthly_hours_included ?? 0

    // Compute prior month key (e.g. '2024-03' → '2024-02')
    const [yearStr, monthStr] = month.split('-')
    const year = parseInt(yearStr ?? '0', 10)
    const mo = parseInt(monthStr ?? '0', 10)
    const priorDate = new Date(year, mo - 2, 1) // mo-2 because months are 0-indexed
    const priorMonth = `${priorDate.getFullYear()}-${String(priorDate.getMonth() + 1).padStart(2, '0')}`

    const [priorRow] = await tx
      .select({ hoursRolledOver: retainerMonths.hoursRolledOver })
      .from(retainerMonths)
      .where(
        and(
          eq(retainerMonths.tenantId, tenantId),
          eq(retainerMonths.projectId, projectId),
          eq(retainerMonths.month, priorMonth),
        ),
      )
      .limit(1)

    const rolledOver = priorRow
      ? parseFloat(String(priorRow.hoursRolledOver ?? '0'))
      : 0
    const hoursIncluded = baseIncluded + rolledOver

    // Upsert retainer_months row — increment hours_used atomically
    const [upserted] = await tx
      .insert(retainerMonths)
      .values({
        projectId,
        tenantId,
        month,
        hoursIncluded: String(hoursIncluded),
        hoursUsed: String(hours),
        hoursRolledOver: '0',
        invoiceTriggeredAt: null,
      } as NewRetainerMonth)
      .onConflictDoUpdate({
        target: [retainerMonths.projectId, retainerMonths.month],
        set: {
          hoursUsed: sql`retainer_months.hours_used + ${hours}`,
          hoursIncluded: sql`COALESCE(retainer_months.hours_included, ${hoursIncluded})`,
        },
      })
      .returning()

    if (!upserted) throw new Error('Retainer month not found after upsert')

    const usedNow = parseFloat(String(upserted.hoursUsed ?? '0'))
    const includedNow = parseFloat(String(upserted.hoursIncluded ?? '0'))

    // Depletion trigger — idempotent on invoice_triggered_at
    if (usedNow >= includedNow && upserted.invoiceTriggeredAt === null && includedNow > 0) {
      const [triggered] = await tx
        .update(retainerMonths)
        .set({ invoiceTriggeredAt: new Date() })
        .where(
          and(
            eq(retainerMonths.tenantId, tenantId),
            eq(retainerMonths.projectId, projectId),
            eq(retainerMonths.month, month),
            sql`retainer_months.invoice_triggered_at IS NULL`,
          ),
        )
        .returning()

      if (triggered) {
        const excessHours = usedNow - includedNow

        // Enqueue 'retainer.depleted' webhook notification via QUEUE
        if (queue) {
          await queue.send({
            type: 'retainer.depleted',
            tenant_id: tenantId,
            project_id: projectId,
            month,
            excess_hours: excessHours,
          })
        }

        // When auto_invoice=true AND overflow_action='invoice' → enqueue invoice generation
        if (
          queue &&
          config?.auto_invoice === true &&
          config?.hour_bank_overflow_action === 'invoice'
        ) {
          await queue.send({
            type: 'retainer.invoice',
            tenant_id: tenantId,
            project_id: projectId,
            month,
            excess_hours: excessHours,
          })
        }

        await tx.insert(auditLog).values({
          tenantId,
          actorId: null,
          actorType: 'system',
          entityType: 'retainer_month',
          entityId: upserted.id,
          action: 'retainer.depleted',
          changes: null,
        })

        // Return the triggered row
        const [finalRow] = await tx
          .select()
          .from(retainerMonths)
          .where(and(eq(retainerMonths.tenantId, tenantId), eq(retainerMonths.id, upserted.id)))
          .limit(1)

        if (finalRow) return serializeRetainerMonth(finalRow)
      }
    }

    await tx.insert(auditLog).values({
      tenantId,
      actorId: null,
      actorType: 'system',
      entityType: 'retainer_month',
      entityId: upserted.id,
      action: 'retainer.hours_incremented',
      changes: null,
    })

    return serializeRetainerMonth(upserted)
  })
}

/**
 * rollOverRetainerHours — computes unused hours from `fromMonth`
 * (hours_included - hours_used, floored at 0) and writes hours_rolled_over
 * onto the `toMonth` row. Intended to be invoked by a monthly cron.
 */
export async function rollOverRetainerHours(
  db: Db,
  tenantId: string,
  projectId: string,
  fromMonth: string,
  toMonth: string,
): Promise<RetainerMonthObject> {
  return db.transaction(async (tx) => {
    const [projectRow] = await tx
      .select({
        id: projects.id,
        billingType: projects.billingType,
      })
      .from(projects)
      .where(and(eq(projects.tenantId, tenantId), eq(projects.id, projectId)))
      .limit(1)

    if (!projectRow) throw new Error('Project not found')
    if (projectRow.billingType !== 'retainer') throw new Error('Project is not a retainer')

    const [fromRow] = await tx
      .select()
      .from(retainerMonths)
      .where(
        and(
          eq(retainerMonths.tenantId, tenantId),
          eq(retainerMonths.projectId, projectId),
          eq(retainerMonths.month, fromMonth),
        ),
      )
      .limit(1)

    const included = fromRow ? parseFloat(String(fromRow.hoursIncluded ?? '0')) : 0
    const used = fromRow ? parseFloat(String(fromRow.hoursUsed ?? '0')) : 0
    const unused = Math.max(0, included - used)

    // Upsert the toMonth row with rolled-over hours
    const [toRow] = await tx
      .insert(retainerMonths)
      .values({
        projectId,
        tenantId,
        month: toMonth,
        hoursIncluded: null,
        hoursUsed: '0',
        hoursRolledOver: String(unused),
        invoiceTriggeredAt: null,
      } as NewRetainerMonth)
      .onConflictDoUpdate({
        target: [retainerMonths.projectId, retainerMonths.month],
        set: { hoursRolledOver: String(unused) },
      })
      .returning()

    if (!toRow) throw new Error('Retainer month not found after rollover')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: null,
      actorType: 'system',
      entityType: 'retainer_month',
      entityId: toRow.id,
      action: 'retainer.rolled_over',
      changes: null,
    })

    return serializeRetainerMonth(toRow)
  })
}