/**
 * Shared activity feed helper — activity-timeline (wave-12).
 *
 * Generic feed loader for any of the four per-entity activity tables.
 * Returns newest-first, 20 per page, with cursor-based pagination via
 * ISO8601 `before` timestamp (rows strictly older than the cursor).
 *
 * LEFT JOINs users to resolve actorName; NULL for system events.
 */
import { sql } from '@zync/db'
import type { Db } from '@zync/db/queries'
import type { ActivityEvent, ActivityFeedResponse } from '@zync/types'

const MAX_LIMIT = 20

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

type ActivityTableName =
  | 'invoice_activities'
  | 'project_activities'
  | 'customer_activities'
  | 'vendor_activities'

type ScopeColumn =
  | 'invoice_id'
  | 'project_id'
  | 'customer_id'
  | 'vendor_id'

// Raw shape returned by the SQL query
type ActivityRow = {
  id: string
  actor_id: string | null
  actor_name: string | null
  actor_type: string
  event_type: string
  metadata: Record<string, unknown>
  note: string | null
  created_at: Date
} & Record<string, unknown>

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

export function serializeActivity(row: ActivityRow): ActivityEvent {
  return {
    id: row.id,
    actorId: row.actor_id,
    actorName: row.actor_name,
    actorType: row.actor_type as ActivityEvent['actorType'],
    eventType: row.event_type,
    metadata: row.metadata ?? {},
    note: row.note,
    createdAt: row.created_at.toISOString(),
  }
}

// ── Feed loader ───────────────────────────────────────────────────────────────

export async function loadActivityFeed(
  db: Db,
  opts: {
    table: ActivityTableName
    scopeColumn: ScopeColumn
    entityId: string
    tenantId: string
    before?: string   // ISO8601 cursor; rows strictly older than this
    limit?: number    // default & max 20
  },
): Promise<ActivityFeedResponse> {
  const limit = Math.min(opts.limit ?? MAX_LIMIT, MAX_LIMIT)

  // Build the WHERE clause: tenant scope + entity scope + not deleted + optional before cursor
  const beforeClause =
    opts.before
      ? sql`AND t.created_at < ${new Date(opts.before)}`
      : sql``

  // Use raw SQL for the query since we need a dynamic table name + LEFT JOIN on users.
  // sql.raw is safe here: opts.table and opts.scopeColumn are constrained by the
  // ActivityTableName and ScopeColumn union types — only hardcoded string literals
  // are accepted at compile time; no user-supplied values can reach these interpolations.
  const rows = await db.execute<ActivityRow>(
    sql`
      SELECT
        t.id,
        t.actor_id,
        u.name AS actor_name,
        t.actor_type,
        t.event_type,
        t.metadata,
        t.note,
        t.created_at
      FROM ${sql.raw(`"${opts.table}"`)} t
      LEFT JOIN users u ON u.id = t.actor_id
      WHERE t.tenant_id = ${opts.tenantId}
        AND t.${sql.raw(`"${opts.scopeColumn}"`)} = ${opts.entityId}
        AND t.deleted_at IS NULL
        ${beforeClause}
      ORDER BY t.created_at DESC
      LIMIT ${limit + 1}
    `,
  )

  const data = (rows as unknown as { rows: ActivityRow[] }).rows ?? (rows as unknown as ActivityRow[])
  const hasMore = data.length > limit
  const events = (hasMore ? data.slice(0, limit) : data).map(serializeActivity)

  return { events, hasMore }
}