/**
 * Time-to-invoice query helpers — time-to-invoice (P064).
 *
 * These functions implement the bridge that turns billable, unbilled time
 * entries into invoice lines. All writes are tenant-scoped and transactional.
 *
 * Key contract:
 *  - listUnbilledTimeEntries: returns entries where invoiceId IS NULL AND
 *    billable = true AND stoppedAt IS NOT NULL (running timers not billable),
 *    optionally filtered by projectId / customerId / date range / userId.
 *  - markTimeEntriesBilled: sets invoiceId + billedAt atomically within the
 *    caller's transaction (called from POST /api/invoices handler).
 *  - unbillTimeEntries: clears invoiceId + billedAt (used by invoice delete/void).
 */
import { and, eq, gt, isNull, inArray, gte, lte, or, isNotNull } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { assertTenantOwnsOrThrow, assertTenantOwnsInvoice } from './tenant-guards'
import { timeEntries } from '../schema/time'
import { projects } from '../schema/projects'
import type { TimeEntryRow } from '../schema/time'

// ── Types ──────────────────────────────────────────────────────────────────────

export interface UnbilledEntryFilter {
  tenantId: string
  projectId?: string
  customerId?: string
  /** Restrict to entries belonging to this user. */
  userId?: string
  dateFrom?: string // ISO date string YYYY-MM-DD
  dateTo?: string   // ISO date string YYYY-MM-DD
  cursor?: string   // base64url-encoded {startedAt, id}
  limit?: number
}

export interface UnbilledTimeEntry {
  id: string
  tenantId: string
  userId: string | null
  contractorId: string | null
  projectId: string
  taskId: string | null
  description: string | null
  startedAt: string    // ISO 8601
  stoppedAt: string    // ISO 8601 (always set — running entries excluded)
  durationSeconds: number
  billable: true
  approvalStatus: string
}

export interface UnbilledEntriesPage {
  entries: UnbilledTimeEntry[]
  nextCursor: string | null
}

export interface MarkBilledInput {
  entryIds: string[]
  invoiceId: string
  billedAt: Date
}

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

function encodeCursor(startedAt: Date, id: string): string {
  return Buffer.from(JSON.stringify({ startedAt: startedAt.toISOString(), id })).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
  }
}

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

function serializeEntry(row: TimeEntryRow): UnbilledTimeEntry {
  return {
    id: row.id,
    tenantId: row.tenantId,
    userId: row.userId ?? null,
    contractorId: row.contractorId ?? null,
    projectId: row.projectId,
    taskId: row.taskId ?? null,
    description: row.description ?? null,
    startedAt: row.startedAt.toISOString(),
    stoppedAt: row.stoppedAt!.toISOString(),
    durationSeconds: row.durationSeconds ?? 0,
    billable: true,
    approvalStatus: row.approvalStatus,
  }
}

// ── Query: list unbilled time entries ─────────────────────────────────────────

export async function listUnbilledTimeEntries(
  db: Db,
  filter: UnbilledEntryFilter,
): Promise<UnbilledEntriesPage> {
  const limit = Math.min(filter.limit ?? 50, 200)

  const conditions = [
    eq(timeEntries.tenantId, filter.tenantId),
    eq(timeEntries.billable, true),
    isNull(timeEntries.invoiceId),
    isNotNull(timeEntries.stoppedAt),
  ]

  if (filter.projectId) {
    conditions.push(eq(timeEntries.projectId, filter.projectId))
  }

  if (filter.customerId) {
    conditions.push(eq(projects.customerId, filter.customerId))
  }

  if (filter.userId) {
    conditions.push(eq(timeEntries.userId, filter.userId))
  }

  if (filter.dateFrom) {
    conditions.push(gte(timeEntries.startedAt, new Date(filter.dateFrom)))
  }

  if (filter.dateTo) {
    // Include the entire dateTo day by advancing to midnight of next day
    const end = new Date(filter.dateTo)
    end.setUTCDate(end.getUTCDate() + 1)
    conditions.push(lte(timeEntries.startedAt, end))
  }

  if (filter.cursor) {
    const decoded = decodeCursor(filter.cursor)
    if (decoded) {
      // Keyset cursor for ASC (startedAt, id): rows after the last page item
      const cursorStartedAt = new Date(decoded.startedAt)
      conditions.push(
        or(
          gt(timeEntries.startedAt, cursorStartedAt),
          and(eq(timeEntries.startedAt, cursorStartedAt), gt(timeEntries.id, decoded.id)),
        )!,
      )
    }
  }

  const rows = await db
    .select({ entry: timeEntries })
    .from(timeEntries)
    .leftJoin(projects, eq(timeEntries.projectId, projects.id))
    .where(and(...conditions))
    .orderBy(timeEntries.startedAt, timeEntries.id)
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const page = hasMore ? rows.slice(0, limit) : rows

  const nextCursor =
    hasMore && page.length > 0
      ? encodeCursor(page[page.length - 1]!.entry.startedAt, page[page.length - 1]!.entry.id)
      : null

  return {
    entries: page.map((row) => serializeEntry(row.entry)),
    nextCursor,
  }
}

// ── Mutation: mark entries as billed ─────────────────────────────────────────

/**
 * Mark a set of time entries as billed. Must be called within the same
 * transaction as the invoice insert (atomicity requirement from spec P064).
 *
 * Uses a transaction parameter (DbTx) to enforce caller-controlled atomicity.
 * The caller (POST /api/invoices) begins the transaction, creates the invoice,
 * then calls this function before committing.
 */
export async function markTimeEntriesBilled(
  tx: DbTx,
  tenantId: string,
  input: MarkBilledInput,
): Promise<void> {
  if (input.entryIds.length === 0) return

  assertTenantOwnsOrThrow(
    'invoice_id',
    await assertTenantOwnsInvoice(tx, tenantId, input.invoiceId),
  )

  await tx
    .update(timeEntries)
    .set({
      invoiceId: input.invoiceId,
      billedAt: input.billedAt,
      lockedAt: input.billedAt,
      lockedReason: 'invoiced',
      approvalStatus: 'locked',
      updatedAt: input.billedAt,
    })
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        inArray(timeEntries.id, input.entryIds),
        isNull(timeEntries.invoiceId), // idempotency guard — don't double-bill
        eq(timeEntries.billable, true),
      ),
    )
}

/**
 * Clear billing linkage from time entries. Called when invoice is voided or
 * deleted. In practice, the ON DELETE SET NULL FK on invoice_id handles the
 * invoice_id column automatically; this function also clears the lock state so
 * entries become editable/re-billable again.
 */
export async function unbillTimeEntries(
  tx: DbTx,
  tenantId: string,
  invoiceId: string,
): Promise<void> {
  await tx
    .update(timeEntries)
    .set({
      invoiceId: null,
      billedAt: null,
      lockedAt: null,
      lockedReason: null,
      approvalStatus: 'auto_approved',
      updatedAt: new Date(),
    })
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        eq(timeEntries.invoiceId, invoiceId),
      ),
    )
}

/**
 * Check whether all supplied entry IDs are unbilled and belong to the tenant.
 * Returns the IDs that are already billed (non-empty → conflict).
 */
export async function findAlreadyBilledEntries(
  db: Db | DbTx,
  tenantId: string,
  entryIds: string[],
): Promise<string[]> {
  if (entryIds.length === 0) return []

  const rows = await db
    .select({ id: timeEntries.id })
    .from(timeEntries)
    .where(
      and(
        eq(timeEntries.tenantId, tenantId),
        inArray(timeEntries.id, entryIds),
        isNotNull(timeEntries.invoiceId),
      ),
    )

  return rows.map((r) => r.id)
}