import { randomUUID } from 'node:crypto'

/**
 * In-memory stand-in for the subset of the supabase-js query builder the
 * purchasing code uses (tests/purchases-*.test.ts).
 *
 *   db.from(t).select(cols?, { count, head }?) .eq/.neq/.in/.gte/.lt/.order/.limit
 *   db.from(t).insert(row | rows).select().single() / .maybeSingle()
 *   db.from(t).upsert(row, { onConflict })
 *   db.from(t).update(patch).eq(...).select().maybeSingle()
 *   db.from(t).delete().eq(...).select().maybeSingle()
 *
 * Every builder is lazy: nothing runs until `.then` (await), `.single()` or
 * `.maybeSingle()`. Tables are plain arrays keyed by name (`db.tables`), so a
 * test can seed or inspect rows directly. Uuids and `created_at`/`updated_at`
 * are generated; `created_at` is strictly increasing so `.order('created_at')`
 * is deterministic even inside one millisecond.
 *
 * Only the unique constraints that the code under test depends on are
 * simulated (see UNIQUE below); a violation returns `{ error: { code: '23505' } }`
 * exactly like Postgres via PostgREST.
 */

export type Row = Record<string, unknown>
type Result = { data: unknown; error: { code: string; message: string } | null; count?: number | null }

interface Unique {
  table: string
  columns: string[]
  /** Partial index predicate; rows for which it is false are not indexed. */
  where?: (row: Row) => boolean
}

const UNIQUE: Unique[] = [
  { table: 'purchases', columns: ['idempotency_key'] },
  { table: 'trip_carts', columns: ['trip_id', 'user_id'], where: (r) => r.status === 'open' },
  { table: 'purchase_events', columns: ['external_event_id'], where: (r) => r.external_event_id != null },
  { table: 'trip_cart_items', columns: ['cart_id', 'permit_id'] },
  { table: 'permit_request_drafts', columns: ['trip_id', 'user_id'], where: (r) => r.status === 'pending' },
  // Auto-Participants (migration 0049)
  { table: 'trip_participants', columns: ['trip_id', 'email', 'role'] },
  { table: 'auto_participant_rules', columns: ['owner_user_id', 'participant_email_normalized', 'scope_type'], where: (r) => r.rule_type === 'personal' && LIVE_RULE.has(r.status as string) },
  { table: 'auto_participant_rules', columns: ['owner_company_id', 'participant_email_normalized', 'scope_type'], where: (r) => r.rule_type === 'company' && LIVE_RULE.has(r.status as string) },
  { table: 'auto_participant_rules', columns: ['accept_token'], where: (r) => r.accept_token != null },
  { table: 'trip_participant_sources', columns: ['participant_id', 'rule_id'] },
  { table: 'membership_roles', columns: ['membership_id', 'role_type'] },
]
const LIVE_RULE = new Set(['pending', 'active', 'disabled', 'suspended'])

/** Column defaults applied on insert when the caller omitted them. */
const DEFAULTS: Record<string, () => Row> = {
  trip_carts: () => ({ status: 'open', purchase_id: null }),
  purchases: () => ({ needs_reconciliation: false }),
  purchase_items: () => ({ synchron_permit_token: null, hha_route_request_token: null, service_request_id: null }),
  payment_references: () => ({ stripe_payment_intent_id: null, stripe_charge_id: null, payment_confirmed_at: null }),
  trip_cart_items: () => ({ quantity: 1 }),
  routing_products: () => ({ active: true, currency: 'USD', product_version: 1, description: null }),
  service_requests: () => ({ status: 'requested' }),
  permit_request_drafts: () => ({ setup_reference: null }),
  trip_participants: () => ({
    user_id: null, phone: null, phone_ext: null, status: 'invited', invited_by: null, completed_at: null,
    completion_prompt_dismissed_at: null, share_chat: true, context_company_id: null, context_role_type: null,
    addition_method: null, triggering_user_id: null, triggering_company_id: null, auto_participant_rule_id: null, rules_evaluated_at: null,
  }),
  auto_participant_rules: () => ({
    owner_user_id: null, owner_company_id: null, participant_user_id: null, participant_first_name: null, participant_last_name: null,
    participant_role_type: null, status: 'pending', suspended_reason: null, requires_acceptance: true, accepted_at: null, accepted_by: null,
    accept_token: randomUUID().replace(/-/g, ''), token_expires_at: null, effective_from: null, disabled_at: null, disabled_by: null, conditions: {},
  }),
  trip_participant_sources: () => ({ triggering_user_id: null, triggering_company_id: null }),
  auto_participant_rule_events: () => ({ actor_user_id: null, actor_label: 'system', trip_id: null, participant_id: null, detail: {} }),
  company_memberships: () => ({ status: 'approved', permission_level: 'member', role: 'carrier_dispatcher' }),
  membership_roles: () => ({ status: 'active', added_at: null, removed_at: null, removed_by: null }),
  profiles: () => ({ claim_status: null }),
  auth_accounts: () => ({ blocked_at: null, email_verified_at: null }),
  trips: () => ({ imported_at: null }),
}

export class FakeSupabase {
  tables: Record<string, Row[]> = {}
  private seq = 0

  /** Rows of a table (created on first touch). */
  table(name: string): Row[] {
    return (this.tables[name] ??= [])
  }

  reset() {
    this.tables = {}
    this.seq = 0
  }

  /** Seed rows directly, filling ids/timestamps like an insert would. */
  seed(name: string, rows: Row[]): Row[] {
    return rows.map((r) => this.insertRow(name, r))
  }

  private stamp(): string {
    // Monotonic: base time + sequence ms.
    return new Date(Date.UTC(2026, 8, 29, 12, 0, 0) + this.seq++).toISOString()
  }

  private violates(name: string, row: Row, ignoreId?: unknown): boolean {
    return UNIQUE.filter((u) => u.table === name && (!u.where || u.where(row))).some((u) =>
      this.table(name).some((existing) =>
        existing.id !== ignoreId && (!u.where || u.where(existing)) && u.columns.every((c) => existing[c] === row[c]),
      ),
    )
  }

  private insertRow(name: string, input: Row): Row {
    const now = this.stamp()
    const row: Row = { id: randomUUID(), created_at: now, updated_at: now, added_at: now, ...(DEFAULTS[name]?.() ?? {}), ...input }
    this.table(name).push(row)
    return row
  }

  from(name: string) {
    return new Builder(this, name)
  }

  /** @internal executed by Builder */
  run(name: string, q: Query): Result {
    const rows = this.table(name)
    const matches = () => rows.filter((r) => q.filters.every((f) => f(r)))
    const dup = (): Result => ({ data: null, error: { code: '23505', message: `duplicate key value violates unique constraint on ${name}` } })

    if (q.op === 'insert') {
      const inputs = Array.isArray(q.payload) ? q.payload : [q.payload as Row]
      for (const input of inputs) if (this.violates(name, input)) return dup()
      const inserted = inputs.map((r) => this.insertRow(name, r))
      return { data: Array.isArray(q.payload) ? inserted : inserted[0], error: null }
    }
    if (q.op === 'upsert') {
      const input = q.payload as Row
      const cols = q.onConflict ? q.onConflict.split(',').map((c) => c.trim()) : ['id']
      const existing = cols.every((c) => input[c] !== undefined) ? rows.find((r) => cols.every((c) => r[c] === input[c])) : undefined
      if (existing) {
        const next = { ...existing, ...input, updated_at: this.stamp() }
        if (this.violates(name, next, existing.id)) return dup()
        Object.assign(existing, next)
        return { data: existing, error: null }
      }
      if (this.violates(name, input)) return dup()
      return { data: this.insertRow(name, input), error: null }
    }
    if (q.op === 'update') {
      const hit = matches()
      for (const r of hit) {
        const next = { ...r, ...(q.payload as Row), updated_at: this.stamp() }
        if (this.violates(name, next, r.id)) return dup()
        Object.assign(r, next)
      }
      return { data: hit, error: null }
    }
    if (q.op === 'delete') {
      const hit = matches()
      this.tables[name] = rows.filter((r) => !hit.includes(r))
      return { data: hit, error: null }
    }
    // select
    let hit = matches()
    for (const o of [...q.orders].reverse()) {
      hit = [...hit].sort((a, b) => {
        const av = a[o.column] as string | number, bv = b[o.column] as string | number
        return (av < bv ? -1 : av > bv ? 1 : 0) * (o.ascending ? 1 : -1)
      })
    }
    if (q.limit != null) hit = hit.slice(0, q.limit)
    return { data: q.head ? null : hit, error: null, count: q.count ? hit.length : null }
  }
}

interface Query {
  op: 'select' | 'insert' | 'upsert' | 'update' | 'delete'
  payload?: unknown
  onConflict?: string
  filters: Array<(r: Row) => boolean>
  orders: Array<{ column: string; ascending: boolean }>
  limit?: number
  count?: boolean
  head?: boolean
  /** true after `.select()` on a write: return rows instead of null. */
  returning: boolean
}

/** Chainable, thenable query. Mirrors PostgrestFilterBuilder loosely. */
class Builder implements PromiseLike<Result> {
  private q: Query = { op: 'select', filters: [], orders: [], returning: false }
  constructor(private db: FakeSupabase, private name: string) {}

  select(_cols?: string, opts?: { count?: string; head?: boolean }) {
    if (this.q.op === 'select') { this.q.count = !!opts?.count; this.q.head = !!opts?.head }
    else this.q.returning = true
    return this
  }
  insert(payload: Row | Row[]) { this.q.op = 'insert'; this.q.payload = payload; return this }
  upsert(payload: Row, opts?: { onConflict?: string }) { this.q.op = 'upsert'; this.q.payload = payload; this.q.onConflict = opts?.onConflict; return this }
  update(patch: Row) { this.q.op = 'update'; this.q.payload = patch; return this }
  delete() { this.q.op = 'delete'; return this }

  eq(c: string, v: unknown) { this.q.filters.push((r) => r[c] === v); return this }
  neq(c: string, v: unknown) { this.q.filters.push((r) => r[c] !== v); return this }
  in(c: string, vs: unknown[]) { this.q.filters.push((r) => vs.includes(r[c])); return this }
  is(c: string, v: unknown) { this.q.filters.push((r) => r[c] === v); return this }
  gte(c: string, v: string | number) { this.q.filters.push((r) => (r[c] as string | number) >= v); return this }
  lte(c: string, v: string | number) { this.q.filters.push((r) => (r[c] as string | number) <= v); return this }
  /** Case-insensitive match; `%` / `_` wildcards as in Postgres, backslash escapes them. */
  ilike(c: string, pattern: string) {
    let re = '^'
    for (let i = 0; i < pattern.length; i++) {
      const ch = pattern[i]
      if (ch === '\\' && i + 1 < pattern.length) { re += pattern[++i].replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); continue }
      if (ch === '%') { re += '.*'; continue }
      if (ch === '_') { re += '.'; continue }
      re += ch.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')
    }
    const rx = new RegExp(re + '$', 'i')
    this.q.filters.push((r) => typeof r[c] === 'string' && rx.test(r[c] as string))
    return this
  }
  not(c: string, op: string, v: unknown) {
    if (op === 'is') this.q.filters.push((r) => r[c] !== v)
    else if (op === 'eq') this.q.filters.push((r) => r[c] !== v)
    else if (op === 'in') this.q.filters.push((r) => !(v as unknown[]).includes(r[c]))
    return this
  }
  lt(c: string, v: string | number) { this.q.filters.push((r) => (r[c] as string | number) < v); return this }
  order(c: string, opts?: { ascending?: boolean }) { this.q.orders.push({ column: c, ascending: opts?.ascending ?? true }); return this }
  limit(n: number) { this.q.limit = n; return this }

  private exec(): Result {
    const res = this.db.run(this.name, this.q)
    if (res.error) return res
    // Writes without `.select()` return no rows, like PostgREST with `Prefer: return=minimal`.
    if (this.q.op !== 'select' && !this.q.returning) return { data: null, error: null }
    return res
  }
  private rows(res: Result): Row[] { return Array.isArray(res.data) ? res.data : res.data ? [res.data as Row] : [] }

  async single(): Promise<Result> {
    const res = this.exec(); if (res.error) return res
    const rows = this.rows(res)
    if (rows.length !== 1) return { data: null, error: { code: 'PGRST116', message: `expected exactly one row, got ${rows.length}` } }
    return { data: rows[0], error: null }
  }
  async maybeSingle(): Promise<Result> {
    const res = this.exec(); if (res.error) return res
    const rows = this.rows(res)
    if (rows.length > 1) return { data: null, error: { code: 'PGRST116', message: `expected at most one row, got ${rows.length}` } }
    return { data: rows[0] ?? null, error: null }
  }
  then<A = Result, B = never>(onfulfilled?: ((v: Result) => A | PromiseLike<A>) | null, onrejected?: ((e: unknown) => B | PromiseLike<B>) | null): Promise<A | B> {
    return Promise.resolve(this.exec()).then(onfulfilled, onrejected)
  }
}

export function createFakeSupabase(): FakeSupabase {
  return new FakeSupabase()
}