import { CriteriaParser } from '../builders/CriteriaParser'
import { AudienceCriteria } from '../types'
import { ClickHouseEventQueryExecutor } from '../executors/ClickHouseEventQueryExecutor'

type Logger = {
  log: (...args: any[]) => void
  warn: (...args: any[]) => void
  error: (...args: any[]) => void
}

const getByPath = (obj: any, dottedPath: string): any => {
  if (!obj || !dottedPath) return undefined
  const parts = dottedPath.split('.').filter(Boolean)
  let cur: any = obj
  for (const p of parts) {
    if (cur == null) return undefined
    cur = cur[p]
  }
  return cur
}

const getEventPropertyValue = (eventData: any, keyRaw: any): any => {
  const key = String(keyRaw || '').trim()
  if (!key) return undefined
  const ed = eventData || {}

  if (key.startsWith('event_data.')) return getByPath(ed, key.replace(/^event_data\./, ''))
  if (key.startsWith('custom_data.')) return getByPath(ed?.custom_data, key.replace(/^custom_data\./, ''))
  if (key.startsWith('utm_data.')) return getByPath(ed?.utm_data, key.replace(/^utm_data\./, ''))
  if (key.startsWith('url_data.')) return getByPath(ed?.url_data, key.replace(/^url_data\./, ''))
  if (key.startsWith('session_data.')) return getByPath(ed?.session_data, key.replace(/^session_data\./, ''))

  // fallback: tentar direto e depois dentro de custom_data
  return getByPath(ed, key) ?? getByPath(ed?.custom_data, key)
}

const isValidIanaTimezone = (tzRaw: any): boolean => {
  const tz = String(tzRaw || '').trim()
  if (!tz) return false
  try {
    // eslint-disable-next-line no-new
    new Intl.DateTimeFormat('en-US', { timeZone: tz })
    return true
  } catch {
    return false
  }
}

const parseTimeToMinutes = (raw: any): number | null => {
  const s = String(raw || '').trim()
  if (!s) return null
  const m = s.match(/^(\d{1,2}):(\d{2})\s*(AM|PM)?$/i)
  if (!m) return null
  let hh = Number(m[1])
  const mm = Number(m[2])
  const ampm = m[3] ? String(m[3]).toUpperCase() : null
  if (!Number.isFinite(hh) || !Number.isFinite(mm)) return null
  if (mm < 0 || mm > 59) return null

  if (ampm) {
    if (hh < 1 || hh > 12) return null
    if (ampm === 'AM') hh = hh === 12 ? 0 : hh
    if (ampm === 'PM') hh = hh === 12 ? 12 : hh + 12
  } else {
    if (hh < 0 || hh > 23) return null
  }
  return hh * 60 + mm
}

const getLocalTimeParts = (iso: any, tz: string): { minutes: number; weekday: number; dayOfMonth: number } | null => {
  const d = new Date(String(iso || ''))
  if (Number.isNaN(d.getTime())) return null

  const dtf = new Intl.DateTimeFormat('en-US', {
    timeZone: tz,
    hour: '2-digit',
    minute: '2-digit',
    hour12: false,
    weekday: 'short',
    day: '2-digit',
  })

  const parts = dtf.formatToParts(d)
  let hourStr = ''
  let minStr = ''
  let weekdayStr = ''
  let dayStr = ''
  for (const p of parts) {
    if (p.type === 'hour') hourStr = p.value
    if (p.type === 'minute') minStr = p.value
    if (p.type === 'weekday') weekdayStr = p.value
    if (p.type === 'day') dayStr = p.value
  }

  const hour = Number(hourStr)
  const minute = Number(minStr)
  const dayOfMonth = Number(dayStr)
  if (!Number.isFinite(hour) || !Number.isFinite(minute) || !Number.isFinite(dayOfMonth)) return null
  const minutes = hour * 60 + minute

  // 0=Sunday ... 6=Saturday
  const weekdayMap: Record<string, number> = { Sun: 0, Mon: 1, Tue: 2, Wed: 3, Thu: 4, Fri: 5, Sat: 6 }
  const weekday = weekdayMap[weekdayStr] ?? -1
  if (weekday < 0) return null

  return { minutes, weekday, dayOfMonth }
}

const parseWeekdayToIndex = (raw: any): number | null => {
  const s = String(raw || '').trim().toLowerCase()
  if (!s) return null
  const map: Record<string, number> = {
    sunday: 0, sun: 0,
    monday: 1, mon: 1,
    tuesday: 2, tue: 2, tues: 2,
    wednesday: 3, wed: 3,
    thursday: 4, thu: 4, thurs: 4,
    friday: 5, fri: 5,
    saturday: 6, sat: 6,
  }
  return map[s] ?? null
}

const matchesInterest = (raw: any, op: string, expected: any): boolean => {
  if (raw === null || raw === undefined) return false
  const a = String(raw).trim()
  const b = String(expected ?? '').trim()
  if (!b) return false
  const nOp = String(op || 'equals').trim()
  if (nOp === 'equals') return a === b
  if (nOp === 'contains') return a.toLowerCase().includes(b.toLowerCase())
  if (nOp === 'starts_with') return a.toLowerCase().startsWith(b.toLowerCase())
  if (nOp === 'ends_with') return a.toLowerCase().endsWith(b.toLowerCase())
  if (nOp === 'not_equals') return a !== b
  return a.toLowerCase().includes(b.toLowerCase())
}

const normalizeEventPropertyOp = (opRaw: any): string => {
  const op = String(opRaw ?? '').trim()
  if (op === '=' || op === '==' || op === 'eq' || op === 'equals') return 'equals'
  if (op === '!=' || op === '<>' || op === 'neq' || op === 'not_equals') return 'not_equals'
  if (op === 'contains') return 'contains'
  if (op === 'not_contains') return 'not_contains'
  if (op === 'starts_with') return 'starts_with'
  if (op === 'ends_with') return 'ends_with'
  if (op === 'is_empty') return 'is_empty'
  if (op === 'is_not_empty') return 'is_not_empty'
  if (op === '>' || op === 'gt') return 'gt'
  if (op === '>=' || op === 'gte') return 'gte'
  if (op === '<' || op === 'lt') return 'lt'
  if (op === '<=' || op === 'lte') return 'lte'
  return op
}

const normalizePropertyOp = (opRaw: any): string => {
  const op = String(opRaw ?? '').trim()
  if (op === 'on' || op === 'was_exactly' || op === '=' || op === '==' || op === 'eq' || op === 'equals') return 'equals'
  if (op === '!=' || op === '<>' || op === 'neq' || op === 'not_equals') return 'not_equals'
  if (op === '>' || op === 'gt' || op === 'greater' || op === 'greater_than') return '>'
  if (op === '>=' || op === 'gte' || op === 'greater_or_equal' || op === 'greater_or_equals' || op === 'greater_than_or_equal') return '>='
  if (op === '<' || op === 'lt' || op === 'less' || op === 'less_than') return '<'
  if (op === '<=' || op === 'lte' || op === 'less_or_equal' || op === 'less_or_equals' || op === 'less_than_or_equal') return '<='
  return op
}

const normalizeTagValues = (value: any): string[] => {
  if (value === null || value === undefined) return []
  const raw = Array.isArray(value) ? value : String(value).split(',')
  const out: string[] = []
  for (const item of raw) {
    const v = String(item ?? '').trim()
    if (v.length > 0) out.push(v)
  }
  return out
}

const normalizeTagOp = (opRaw: any): string => {
  const op = String(opRaw ?? '').trim().toLowerCase()
  // UI principal: espelha os operadores dos campos texto.
  if (op === 'equals' || op === 'eq' || op === '=' || op === 'has_all' || op === 'has') return 'equals'
  if (op === 'not_equals' || op === 'neq' || op === '!=' || op === '<>') return 'not_equals'
  if (op === 'contains') return 'contains'
  if (op === 'not_contains') return 'not_contains'
  // Array-specific (forward-compat / API direta)
  if (op === 'has_any' || op === 'in') return 'has_any'
  if (op === 'is_empty') return 'is_empty'
  if (op === 'is_not_empty') return 'is_not_empty'
  return op
}

const isNumericLike = (value: any): boolean => {
  if (typeof value === 'number') return Number.isFinite(value)
  if (typeof value !== 'string') return false
  const trimmed = value.trim()
  if (!trimmed) return false
  return /^-?\d+(\.\d+)?$/.test(trimmed)
}

const isNumericOperator = (op: string): boolean => {
  return op === '>' || op === '>=' || op === '<' || op === '<=' || op === 'between'
}

const matchesEventPropertyFilter = (raw: any, opRaw: any, expected: any): boolean => {
  const op = normalizeEventPropertyOp(opRaw)

  if (op === 'is_empty') {
    return raw === null || raw === undefined || String(raw).trim() === ''
  }
  if (op === 'is_not_empty') {
    return raw !== null && raw !== undefined && String(raw).trim() !== ''
  }

  if (raw === null || raw === undefined) return false
  const a = String(raw)
  const b = String(expected ?? '')

  if (op === 'equals') return a === b
  if (op === 'not_equals') return a !== b
  if (op === 'contains') return a.toLowerCase().includes(b.toLowerCase())
  if (op === 'not_contains') return !a.toLowerCase().includes(b.toLowerCase())
  if (op === 'starts_with') return a.toLowerCase().startsWith(b.toLowerCase())
  if (op === 'ends_with') return a.toLowerCase().endsWith(b.toLowerCase())

  if (op === 'gt' || op === 'gte' || op === 'lt' || op === 'lte') {
    const aNum = Number(raw)
    const bNum = Number(expected)
    if (!Number.isFinite(aNum) || !Number.isFinite(bNum)) return false
    if (op === 'gt') return aNum > bNum
    if (op === 'gte') return aNum >= bNum
    if (op === 'lt') return aNum < bNum
    return aNum <= bNum
  }

  return false
}

const applyEventRuleFilters = (
  rows: any[],
  filters: any[],
  opts?: { timezone?: string }
): any[] => {
  let out = rows
  const tz = String(opts?.timezone || '').trim()
  for (const f of filters) {
    const type = String(f?.type || '').trim()
    if (type === 'event_property') {
      const key = String(f?.key || '').trim()
      if (!key) continue
      const op = f?.op
      const value = f?.value
      out = out.filter((row) => matchesEventPropertyFilter(getEventPropertyValue(row?.event_data, key), op, value))
    } else if (type === 'time_of_day') {
      if (!tz) continue
      const startMin = parseTimeToMinutes(f?.timeStart)
      const endMin = parseTimeToMinutes(f?.timeEnd)
      if (startMin == null || endMin == null) continue
      // compatível com between: se start > end, não casa
      if (startMin > endMin) {
        out = []
        continue
      }
      out = out.filter((row) => {
        const parts = getLocalTimeParts(row?.event_timestamp, tz)
        if (!parts) return false
        return parts.minutes >= startMin && parts.minutes <= endMin
      })
    } else if (type === 'day_of_week') {
      if (!tz) continue
      const days = Array.isArray(f?.days) ? f.days : []
      const allowed = new Set<number>()
      for (const d of days) {
        const idx = parseWeekdayToIndex(d)
        if (idx != null) allowed.add(idx)
      }
      if (allowed.size === 0) continue
      out = out.filter((row) => {
        const parts = getLocalTimeParts(row?.event_timestamp, tz)
        if (!parts) return false
        return allowed.has(parts.weekday)
      })
    } else if (type === 'day_of_month') {
      if (!tz) continue
      const days = Array.isArray(f?.days) ? f.days : []
      const allowed = new Set<number>()
      for (const d of days) {
        const n = Number(d)
        if (Number.isFinite(n) && n >= 1 && n <= 31) allowed.add(Math.floor(n))
      }
      if (allowed.size === 0) continue
      out = out.filter((row) => {
        const parts = getLocalTimeParts(row?.event_timestamp, tz)
        if (!parts) return false
        return allowed.has(parts.dayOfMonth)
      })
    }
  }
  return out
}

/**
 * Engine V2 (groups/rules) portado do reachy-api para o audience-module.
 *
 * Objetivo: permitir que o módulo execute a filtragem sozinho (sem depender do ContactRepository do reachy-api),
 * precisando apenas de um client Supabase compatível (com .from/.select/.eq/.gte/.lte/.lt/.gt/.in/.or/.filter).
 */
export class V2AudienceEngine {
  private supabase: any
  private debug: boolean
  private logger: Logger
  private chExecutor: ClickHouseEventQueryExecutor | null = null

  private static _tzCache = new Map<string, { tz: string; fetchedAt: number }>()
  private static TZ_CACHE_TTL = 5 * 60 * 1000 // 5 minutes

  constructor(params: { supabaseClient: any; clickhouseClient?: any; debug?: boolean; logger?: Logger }) {
    this.supabase = params.supabaseClient
    this.debug = !!params.debug
    this.logger = params.logger || console
    if (params.clickhouseClient) {
      this.chExecutor = new ClickHouseEventQueryExecutor(params.clickhouseClient, params.logger || console)
      if (this.debug) this.logger.log('[V2AudienceEngine] ClickHouse executor initialized')
    }
  }

  private async getProjectTimezone(organizationId: string, projectId: string): Promise<string> {
    const cacheKey = `${organizationId}:${projectId}`
    const cached = V2AudienceEngine._tzCache.get(cacheKey)
    if (cached && Date.now() - cached.fetchedAt < V2AudienceEngine.TZ_CACHE_TTL) {
      return cached.tz
    }
    try {
      const { data } = await this.supabase
        .from('projects')
        .select('settings')
        .eq('organization_id', organizationId)
        .eq('id', projectId)
        .single()
      const tzCandidate = (data as any)?.settings?.timezone
      const tz = (typeof tzCandidate === 'string' && isValidIanaTimezone(tzCandidate))
        ? tzCandidate.trim()
        : 'UTC'
      V2AudienceEngine._tzCache.set(cacheKey, { tz, fetchedAt: Date.now() })
      return tz
    } catch {
      V2AudienceEngine._tzCache.set(cacheKey, { tz: 'UTC', fetchedAt: Date.now() })
      return 'UTC'
    }
  }

  async getContactIdsByAudienceCriteriaV2(
    organizationId: string,
    projectId: string,
    criteriaRaw: any
  ): Promise<Set<string>> {
    let criteria: AudienceCriteria = CriteriaParser.parse(criteriaRaw as any) as any

    const projectTimezone = await this.getProjectTimezone(organizationId, projectId)

    // Compat: permitir critérios legados (filters/conditions) sem groups,
    // convertendo para groups V2 para que o engine avalie sozinho.
    if (
      (!Array.isArray((criteria as any).groups) || (criteria as any).groups.length === 0) &&
      (Array.isArray((criteria as any).filters) || Array.isArray((criteria as any).conditions))
    ) {
      criteria = coerceCriteriaToGroups(criteria)
    }

    this.dlog('start', {
      organizationId,
      projectId,
      type: String((criteria as any)?.type || ''),
      groups: Array.isArray((criteria as any)?.groups) ? (criteria as any).groups.length : 0
    })

    if (!criteria || !Array.isArray((criteria as any).groups) || (criteria as any).groups.length === 0) {
      this.dlog('V2 Engine: no groups; returning empty set')
      return new Set<string>()
    }

    const typeId = String((criteria as any)?.type || '')

    const MAX_FETCH_ROWS = 500_000
    const fetchAll = async (baseQuery: any, pageSize: number, maxRows: number = MAX_FETCH_ROWS): Promise<any[]> => {
      let offset = 0
      let out: any[] = []
      while (true) {
        const { data, error } = await baseQuery.range(offset, offset + pageSize - 1)
        if (error) throw error
        if (!data || data.length === 0) break
        out = out.concat(data)
        if (out.length >= maxRows) {
          this.dlog('fetchAll: max rows reached', { maxRows, fetched: out.length })
          break
        }
        if (data.length < pageSize) break
        offset += pageSize
      }
      return out
    }

    // Lazy loading: só carrega contatos quando necessário (negate ou event rules que precisam de identity maps)
    const groups = (criteria as any).groups as any[]
    const hasNegate = groups.some((g: any) => (g.rules || []).some((r: any) => r.negate))
    const hasEventRules = groups.some((g: any) => (g.rules || []).some((r: any) => r.kind === 'event'))

    let allContactIds = new Set<string>()
    let reachyIdToContactId = new Map<string, string>()
    let contactIdToReachyId = new Map<string, string>()
    let emailToContactId = new Map<string, string>()
    let _contactsLoaded = false

    const ensureContactsLoaded = async () => {
      if (_contactsLoaded) return
      _contactsLoaded = true
      const allContacts = await fetchAll(
        this.supabase
          .from('contacts')
          .select('id, reachy_id, email')
          .eq('organization_id', organizationId)
          .eq('project_id', projectId),
        1000
      )
      allContactIds = new Set<string>((allContacts || []).map((c: any) => c.id as string))
      for (const c of allContacts || []) {
        const rid = c?.reachy_id ? String(c.reachy_id).trim() : ''
        const cid = c?.id ? String(c.id).trim() : ''
        if (rid && cid) {
          reachyIdToContactId.set(rid, cid)
          contactIdToReachyId.set(cid, rid)
        }
        const email = c?.email ? String(c.email).trim().toLowerCase() : ''
        if (email && cid) emailToContactId.set(email, cid)
      }
    }

    // Preload contatos se sabemos que será necessário
    if (hasNegate || hasEventRules) {
      await ensureContactsLoaded()
    }

    const union = (a: Set<string>, b: Set<string>): Set<string> => {
      const result = new Set(a)
      for (const x of b) result.add(x)
      return result
    }
    const intersect = (a: Set<string>, b: Set<string>): Set<string> => {
      const [smaller, larger] = a.size <= b.size ? [a, b] : [b, a]
      const result = new Set<string>()
      for (const x of smaller) { if (larger.has(x)) result.add(x) }
      return result
    }
    const diff = (a: Set<string>, b: Set<string>): Set<string> => {
      const result = new Set<string>()
      for (const x of a) { if (!b.has(x)) result.add(x) }
      return result
    }

    const resolveEventContactId = (row: any): string | null => {
      const rawReachyId = row?.reachy_id ? String(row.reachy_id).trim() : ''
      if (rawReachyId) {
        const mapped = reachyIdToContactId.get(rawReachyId)
        if (mapped) return mapped
      }

      const rawContactId = row?.contact_id ? String(row.contact_id).trim() : ''
      if (rawContactId && allContactIds.has(rawContactId)) return rawContactId

      const ed = row?.event_data || {}
      const emailCandidates = [ed.email, ed.user_email, ed.userEmail, ed.contact_email, ed.contactEmail]
      for (const e of emailCandidates) {
        if (typeof e === 'string') {
          const normalized = e.trim().toLowerCase()
          if (!normalized) continue
          const mapped = emailToContactId.get(normalized)
          if (mapped) return mapped
        }
      }
      return null
    }

    const evalEventRule = async (rule: any): Promise<Set<string>> => {
      const cfg = (criteria as any)?.config || {}

      // Route to ClickHouse when available (replaces N paginated Supabase queries + JS aggregation)
      if (this.chExecutor) {
        try {
          return await this.chExecutor.execute(
            rule, criteria, organizationId, projectId, projectTimezone,
            reachyIdToContactId, contactIdToReachyId, allContactIds
          )
        } catch (chErr) {
          this.logger.warn('[V2AudienceEngine] ClickHouse evalEventRule failed, falling back to Supabase:', chErr)
          // Fall through to Supabase path
        }
      }

      const effectiveEventName =
        cfg.eventType && String(cfg.eventType).trim() !== ''
          ? String(cfg.eventType)
          : String(rule.eventName)

      // Determine minimal SELECT fields based on what's needed
      const ruleFiltersPrecheck = Array.isArray((rule as any).filters) ? (rule as any).filters : []
      const needsEventData = !!(
        (rule.interest && rule.interest.key) ||
        ruleFiltersPrecheck.some((f: any) => ['event_property', 'time_of_day', 'day_of_week', 'day_of_month'].includes(String(f?.type || '').trim())) ||
        (rule.frequency && rule.frequency.type && rule.frequency.type !== 'count') ||
        typeId === 'live-page-count'
      )
      const needsTimestamp = !!(
        ruleFiltersPrecheck.some((f: any) => ['first_time', 'last_time', 'time_of_day', 'day_of_week', 'day_of_month'].includes(String(f?.type || '').trim()))
      )

      let selectFields = 'contact_id, reachy_id'
      if (needsEventData) selectFields += ', event_data'
      if (needsTimestamp) selectFields += ', event_timestamp'
      if (needsEventData || needsTimestamp) selectFields += ', event_name'

      let query = this.supabase
        .from('contact_events')
        .select(selectFields)
        .eq('organization_id', organizationId)
        .eq('project_id', projectId)
        .eq('event_name', effectiveEventName)

      // live presets helpers (mantido por paridade com reachy-api)
      if (typeId === 'live-page-visit') {
        const v = cfg.pageUrl
        const d = cfg.domain
        const ors: string[] = []
        if (v && String(v).trim() !== '') {
          const like = `%${String(v)}%`
          ors.push(`path.ilike.${like}`)
          ors.push(`current_url.ilike.${like}`)
        }
        if (d && String(d).trim() !== '') {
          const dlike = `%${String(d)}%`
          ors.push(`domain.ilike.${dlike}`)
          ors.push(`event_data->>domain.ilike.${dlike}`)
        }
        if (ors.length > 0) query = query.or(ors.join(','))
      }

      if (typeId === 'live-referrer') {
        query = query.eq('session_is_new', true)
        const ors: string[] = []
        const v = cfg.referrerUrl
        if (v && String(v).trim() !== '') ors.push(`referrer.ilike.%${String(v)}%`)
        if (cfg.utm_source && String(cfg.utm_source).trim() !== '') ors.push(`event_data->>utm_source.ilike.%${String(cfg.utm_source)}%`)
        if (cfg.utm_medium && String(cfg.utm_medium).trim() !== '') ors.push(`event_data->>utm_medium.ilike.%${String(cfg.utm_medium)}%`)
        if (cfg.utm_campaign && String(cfg.utm_campaign).trim() !== '') ors.push(`event_data->>utm_campaign.ilike.%${String(cfg.utm_campaign)}%`)
        if (cfg.utm_term && String(cfg.utm_term).trim() !== '') ors.push(`event_data->>utm_term.ilike.%${String(cfg.utm_term)}%`)
        if (cfg.utm_content && String(cfg.utm_content).trim() !== '') ors.push(`event_data->>utm_content.ilike.%${String(cfg.utm_content)}%`)
        if (ors.length > 0) query = query.or(ors.join(','))
      }

      // TimeFrame via config (fallback)
      if (!rule.time && cfg && cfg.timeFrame) {
        const tf = String(cfg.timeFrame).trim()
        const now = new Date()
        let unit: 'minutes' | 'hours' | 'days' | 'weeks' | 'months' = 'days'
        let value = 7
        if (/^\d+$/.test(tf)) { value = Number(tf); unit = 'days' }
        else if (/^\d+\s*m$/.test(tf)) { value = Number(tf.replace(/m$/, '')); unit = 'minutes' }
        else if (/^\d+\s*h$/.test(tf)) { value = Number(tf.replace(/h$/, '')); unit = 'hours' }
        else if (/^\d+\s*d$/.test(tf)) { value = Number(tf.replace(/d$/, '')); unit = 'days' }
        else if (/^\d+\s*w$/.test(tf)) { value = Number(tf.replace(/w$/, '')); unit = 'weeks' }
        else if (/^\d+\s*mo$/.test(tf)) { value = Number(tf.replace(/mo$/, '')); unit = 'months' }
        const units: Record<typeof unit, number> = {
          minutes: 60 * 1000,
          hours: 60 * 60 * 1000,
          days: 24 * 60 * 60 * 1000,
          weeks: 7 * 24 * 60 * 60 * 1000,
          months: 30 * 24 * 60 * 60 * 1000
        }
        const from = new Date(now.getTime() - value * units[unit])
        query = query.gte('event_timestamp', from.toISOString())
      }

      // Janela temporal explícita (relativa ou absoluta)
      if (rule.time) {
        if (rule.time.unit && rule.time.value) {
          type TimeUnit = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
          const unit = String(rule.time.unit) as TimeUnit
          const value = Number(rule.time.value)
          const now = new Date()
          const units: Record<TimeUnit, number> = {
            minutes: 60 * 1000,
            hours: 60 * 60 * 1000,
            days: 24 * 60 * 60 * 1000,
            weeks: 7 * 24 * 60 * 60 * 1000,
            months: 30 * 24 * 60 * 60 * 1000
          }
          const ms = units[unit] ?? units['days']
          const from = new Date(now.getTime() - value * ms)
          query = query.gte('event_timestamp', from.toISOString())
        } else {
          if (rule.time.from) {
            const rawFrom = rule.time.from
            const fromStr = typeof rawFrom === 'string' ? rawFrom : String(rawFrom)
            const isDateOnly = typeof rawFrom === 'string' && !fromStr.includes('T')
            const startIso = isDateOnly ? normalizeDateBoundary(fromStr, 'start') : undefined
            const finalFrom = startIso || fromStr
            query = query.gte('event_timestamp', finalFrom)
          }
          if (rule.time.to) {
            const rawTo = rule.time.to
            const toStr = typeof rawTo === 'string' ? rawTo : String(rawTo)
            const isDateOnly = typeof rawTo === 'string' && !toStr.includes('T')
            const endIsoExclusive = isDateOnly ? normalizeDateBoundary(toStr, 'end') : undefined
            const finalTo = endIsoExclusive || toStr
            query = isDateOnly ? query.lt('event_timestamp', finalTo) : query.lte('event_timestamp', finalTo)
          }
        }
      }

      // Event attributes: attributes: [{ key, op, value }]
      const interest = (rule as any)?.interest
      try {
        let attributes = Array.isArray(rule.attributes) ? rule.attributes : (rule.attributes ? [rule.attributes] : [])
        // Se houver "interest", não podemos pré-filtrar o dataset pelo atributo do interesse,
        // senão o cálculo de % sempre vira 100% (porque só sobram eventos que já casam).
        const interestKey = interest?.key ? String(interest.key).trim() : ''
        if (interestKey) {
          attributes = attributes.filter((a: any) => String(a?.key || '').trim() !== interestKey)
        }

        const trackerEvents = new Set([
          'click',
          'page_view',
          'form_submit',
          'time_on_page',
          'heartbeat',
          'scroll_depth',
          'scroll_depth_snapshot'
        ])

        const resolveDbFieldForAttrKey = (keyRaw: string) => {
          const key = String(keyRaw || '').trim()
          if (!key) return null

          const columnKeys = new Set([
            'current_url',
            'domain',
            'path',
            'referrer',
            'page_title',
            'utm_source',
            'utm_medium',
            'utm_campaign',
            'utm_term',
            'utm_content',
            'session_id',
            'session_is_new'
          ])
          if (columnKeys.has(key)) return { kind: 'column', field: key }

          const buildNested = (root: string, dottedPath: string) => jsonNestedTextAccessor(root, dottedPath)

          if (key.startsWith('event_data.')) return { kind: 'json', field: buildNested('event_data', key.replace(/^event_data\./, '')) }
          if (key.startsWith('custom_data.')) return { kind: 'json', field: buildNested('event_data->custom_data', key.replace(/^custom_data\./, '')) }
          if (key.startsWith('url_data.')) return { kind: 'json', field: buildNested('event_data->url_data', key.replace(/^url_data\./, '')) }
          if (key.startsWith('utm_data.')) return { kind: 'json', field: buildNested('event_data->utm_data', key.replace(/^utm_data\./, '')) }
          if (key.startsWith('session_data.')) return { kind: 'json', field: buildNested('event_data->session_data', key.replace(/^session_data\./, '')) }

          if (key.includes('.')) return { kind: 'json', field: buildNested('event_data->custom_data', key) }

          if (trackerEvents.has(effectiveEventName)) return { kind: 'json', field: jsonTextAccessor('event_data->custom_data', key) }
          return { kind: 'json', field: jsonTextAccessor('event_data', key) }
        }

        for (const attr of attributes) {
          const key = String(attr.key || '').trim()
          const op = String(attr.op || 'contains')
          const value = attr.value
          if (!key || value == null) continue

          const resolved = resolveDbFieldForAttrKey(key)
          if (!resolved) continue
          const dbField = resolved.field

          switch (op) {
            case 'equals':
              query = query.filter(dbField, 'eq', value)
              break
            case 'not_equals':
              query = query.filter(dbField, 'neq', value)
              break
            case 'starts_with':
              query = query.filter(dbField, 'ilike', `${value}%`)
              break
            case 'ends_with':
              query = query.filter(dbField, 'ilike', `%${value}`)
              break
            case 'contains':
            default:
              query = query.filter(dbField, 'ilike', `%${value}%`)
              break
          }
        }
      } catch (e) {
        // Evitar logar objetos/valores (podem conter PII). Se debug estiver ligado, logar apenas um aviso genérico.
        if (this.debug) this.logger.warn('[AUDIENCE_MODULE_ENGINE] event attributes filter error')
      }

      const MAX_EVENT_ROWS = 200_000
      const data = await fetchAll(query, 1000, MAX_EVENT_ROWS)
      if (!data || data.length === 0) return new Set<string>()

      // Event advanced filters (ex.: event_property dentro do DID)
      let rows = data as any[]
      const ruleFiltersAll = Array.isArray((rule as any).filters) ? (rule as any).filters : []
      const hasFirstTime = ruleFiltersAll.some((f: any) => String(f?.type || '').trim() === 'first_time')
      const hasLastTime = ruleFiltersAll.some((f: any) => String(f?.type || '').trim() === 'last_time')
      const ruleFilters = ruleFiltersAll.filter((f: any) => {
        const t = String(f?.type || '').trim()
        return t && t !== 'first_time' && t !== 'last_time'
      })

      if (ruleFilters.length > 0) {
        rows = applyEventRuleFilters(rows, ruleFilters, { timezone: projectTimezone })
      }

      // First Time / Last Time (CleverTap-like): avaliar pelo histórico do evento (vida inteira),
      // e só depois aplicar a janela temporal (rule.time).
      // Implementação eficiente:
      // - candidatos = contatos que têm pelo menos 1 evento na janela atual
      // - first_time: excluir candidatos que já tinham o evento ANTES do início da janela
      // - last_time: excluir candidatos que têm o evento DEPOIS do fim da janela
      if ((hasFirstTime || hasLastTime) && rows.length > 0) {
        const now = new Date()
        let windowStartIso: string | undefined
        let windowEndIso: string | undefined

        // Preferir rule.time; fallback em cfg.timeFrame (paridade)
        if (rule.time) {
          if (rule.time.unit && rule.time.value != null) {
            type TimeUnit = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
            const unit = String(rule.time.unit) as TimeUnit
            const valueNum = Number(rule.time.value)
            const units: Record<TimeUnit, number> = {
              minutes: 60 * 1000,
              hours: 60 * 60 * 1000,
              days: 24 * 60 * 60 * 1000,
              weeks: 7 * 24 * 60 * 60 * 1000,
              months: 30 * 24 * 60 * 60 * 1000,
            }
            const ms = units[unit] ?? units['days']
            if (Number.isFinite(valueNum) && valueNum >= 0) {
              windowStartIso = new Date(now.getTime() - valueNum * ms).toISOString()
              windowEndIso = now.toISOString()
            }
          } else {
            if (rule.time.from) windowStartIso = String(rule.time.from)
            if (rule.time.to) windowEndIso = String(rule.time.to)
          }
        } else if (cfg && cfg.timeFrame) {
          const tf = String(cfg.timeFrame).trim()
          let unit: 'minutes' | 'hours' | 'days' | 'weeks' | 'months' = 'days'
          let value = 7
          if (/^\d+$/.test(tf)) { value = Number(tf); unit = 'days' }
          else if (/^\d+\s*m$/.test(tf)) { value = Number(tf.replace(/m$/, '')); unit = 'minutes' }
          else if (/^\d+\s*h$/.test(tf)) { value = Number(tf.replace(/h$/, '')); unit = 'hours' }
          else if (/^\d+\s*d$/.test(tf)) { value = Number(tf.replace(/d$/, '')); unit = 'days' }
          else if (/^\d+\s*w$/.test(tf)) { value = Number(tf.replace(/w$/, '')); unit = 'weeks' }
          else if (/^\d+\s*mo$/.test(tf)) { value = Number(tf.replace(/mo$/, '')); unit = 'months' }
          const units: Record<typeof unit, number> = {
            minutes: 60 * 1000,
            hours: 60 * 60 * 1000,
            days: 24 * 60 * 60 * 1000,
            weeks: 7 * 24 * 60 * 60 * 1000,
            months: 30 * 24 * 60 * 60 * 1000,
          }
          if (Number.isFinite(value) && value >= 0) {
            const from = new Date(now.getTime() - value * units[unit])
            windowStartIso = from.toISOString()
            windowEndIso = now.toISOString()
          }
        }

        if (!windowEndIso) windowEndIso = now.toISOString()

        const candidateIds = new Set<string>()
        for (const row of rows) {
          const id = resolveEventContactId(row)
          if (id) candidateIds.add(id)
        }

        if (candidateIds.size === 0) {
          return new Set<string>()
        }

        const candidateArr = Array.from(candidateIds)
        const CHUNK = 500
        const PAGE = 1000

        const fetchOutside = async (dir: 'before' | 'after', boundaryIso: string): Promise<Set<string>> => {
          const outIds = new Set<string>()
          for (let i = 0; i < candidateArr.length; i += CHUNK) {
            const chunkIds = candidateArr.slice(i, i + CHUNK)
            const chunkReachyIds = chunkIds.map((cid) => contactIdToReachyId.get(cid)).filter(Boolean) as string[]

            const runPaged = async (queryBuilderFactory: (rangeFrom: number, rangeTo: number) => any) => {
              let offset = 0
              while (true) {
                const query = queryBuilderFactory(offset, offset + PAGE - 1)
                const { data: rowsPage, error: pageError } = await query
                if (pageError || !rowsPage || rowsPage.length === 0) break
                for (const r of rowsPage as any[]) {
                  const cid = resolveEventContactId(r)
                  if (cid) outIds.add(cid)
                }
                if (outIds.size >= candidateIds.size) return
                if (rowsPage.length < PAGE) break
                offset += PAGE
                if (offset > 50_000) break
              }
            }

            // Query por contact_id
            await runPaged((from: number, to: number) => {
              let q = this.supabase
                .from('contact_events')
                .select('contact_id, reachy_id, event_timestamp')
                .eq('organization_id', organizationId)
                .eq('project_id', projectId)
                .eq('event_name', effectiveEventName)
              q = dir === 'before' ? q.lt('event_timestamp', boundaryIso) : q.gt('event_timestamp', boundaryIso)
              q = q.in('contact_id', chunkIds)
              return q.range(from, to)
            })

            if (outIds.size >= candidateIds.size) break

            // Query por reachy_id (para casos em que contact_id é null)
            if (chunkReachyIds.length > 0) {
              await runPaged((from: number, to: number) => {
                let q = this.supabase
                  .from('contact_events')
                  .select('contact_id, reachy_id, event_timestamp')
                  .eq('organization_id', organizationId)
                  .eq('project_id', projectId)
                  .eq('event_name', effectiveEventName)
                q = dir === 'before' ? q.lt('event_timestamp', boundaryIso) : q.gt('event_timestamp', boundaryIso)
                q = q.in('reachy_id', chunkReachyIds)
                return q.range(from, to)
              })
            }

            if (outIds.size >= candidateIds.size) break
          }
          return outIds
        }

        let allowed = new Set<string>(candidateIds)
        if (hasFirstTime && windowStartIso) {
          const hadBefore = await fetchOutside('before', windowStartIso)
          allowed = diff(allowed, hadBefore)
        }
        if (hasLastTime && windowEndIso) {
          const hadAfter = await fetchOutside('after', windowEndIso)
          allowed = diff(allowed, hadAfter)
        }

        if (allowed.size === 0) {
          return new Set<string>()
        }

        rows = rows.filter((row) => {
          const id = resolveEventContactId(row)
          return id ? allowed.has(id) : false
        })
      }

      // User Interests: predominantly / at least X% of the time
      // rule.interest?: { key, op, value, occurrenceType, occurrencePercentage }
      if (interest && interest.key) {
        const key = String(interest.key || '').trim()
        const op = String(interest.op || 'equals').trim()
        const expected = interest.value
        const occType = String(interest.occurrenceType || 'predominantly').trim()
        const pct = Number(interest.occurrencePercentage ?? 0)
        const threshold = Number.isFinite(pct) ? Math.max(0, Math.min(100, pct)) / 100 : 0

        // total events por contato dentro da janela (já filtrada por time/event_name)
        const totals = new Map<string, number>()
        // matches (para at_least) e counts por valor (para predominantly)
        const matchCounts = new Map<string, number>()
        const valueCounts = new Map<string, Map<string, number>>()

        for (const row of rows) {
          const id = resolveEventContactId(row)
          if (!id) continue
          totals.set(id, (totals.get(id) || 0) + 1)

          const rawVal = getEventPropertyValue(row?.event_data, key)
          const strVal = rawVal === null || rawVal === undefined ? '' : String(rawVal)

          // para predominantemente: contar por valor bruto
          if (!valueCounts.has(id)) valueCounts.set(id, new Map<string, number>())
          const per = valueCounts.get(id)!
          per.set(strVal, (per.get(strVal) || 0) + 1)

          if (matchesInterest(rawVal, op === 'contains' ? 'contains' : op, expected)) {
            matchCounts.set(id, (matchCounts.get(id) || 0) + 1)
          }
        }

        const res = new Set<string>()
        totals.forEach((total, id) => {
          if (total <= 0) return

          const match = matchCounts.get(id) || 0
          if (occType === 'at_least') {
            if (match > 0 && match / total >= threshold) res.add(id)
            return
          }

          // predominantly
          const per = valueCounts.get(id)
          if (!per) return
          let maxOther = 0
          // "predominantly": o grupo que casa o operador/valor deve ser >= qualquer outro valor individual.
          per.forEach((cnt, valStr) => {
            if (!matchesInterest(valStr, op, expected)) {
              if (cnt > maxOther) maxOther = cnt
            }
          })

          if (match > 0 && match >= maxOther) res.add(id)
        })

        return res
      }

      // Frequência / agregação
      if (rule.frequency && rule.frequency.value != null) {
        const { op, value, value2, type = 'count', field = 'value' } = rule.frequency
        const counts = new Map<string, number>()
        const denoms = new Map<string, number>() // usado para avg (ignora eventos sem número)

        for (const row of rows) {
          const id = resolveEventContactId(row)
          if (!id) continue

          if (type === 'count') {
            counts.set(id, (counts.get(id) || 0) + 1)
          } else if (type === 'sum' || type === 'avg') {
            const eventData = row.event_data || {}
            const raw =
              getEventPropertyValue(eventData, field) ??
              (eventData?.[field] !== undefined ? eventData[field] : undefined) ??
              eventData?.value ??
              eventData?.amount
            const numVal = Number(raw)

            if (Number.isFinite(numVal)) {
              counts.set(id, (counts.get(id) || 0) + numVal)
              if (type === 'avg') denoms.set(id, (denoms.get(id) || 0) + 1)
            }
          }
        }

        const res = new Set<string>()
        counts.forEach((aggregatedValue, id) => {
          let finalValue = aggregatedValue
          if (type === 'avg') {
            const denom = denoms.get(id) || 0
            finalValue = denom > 0 ? aggregatedValue / denom : 0
          }

          const betweenOk = (() => {
            if (op !== 'between') return false
            const a = Number(value)
            const b = Number(value2)
            if (!Number.isFinite(a) || !Number.isFinite(b)) return false
            // Compatível com CleverTap: se o usuário inverter (min > max), não deve casar.
            if (a > b) return false
            return finalValue >= a && finalValue <= b
          })()

          if (
            (op === '>=' && finalValue >= value) ||
            (op === '>' && finalValue > value) ||
            (op === '=' && finalValue === value) ||
            (op === '!=' && finalValue !== value) ||
            (op === '<=' && finalValue <= value) ||
            (op === '<' && finalValue < value) ||
            betweenOk
          ) {
            res.add(id)
          }
        })
        return res
      }

      // live-page-count: threshold
      if (typeId === 'live-page-count' && cfg && cfg.pageCount != null) {
        const threshold = Number(cfg.pageCount)
        const counts = new Map<string, number>()
        for (const row of rows) {
          const id = resolveEventContactId(row)
          if (!id) continue
          counts.set(id, (counts.get(id) || 0) + 1)
        }
        const res = new Set<string>()
        counts.forEach((cnt, id) => { if (cnt >= threshold) res.add(id) })
        return res
      }

      const res = new Set<string>()
      for (const row of rows) {
        const id = resolveEventContactId(row)
        if (id) res.add(id)
      }
      return res
    }

    const evalPropertyRule = async (rule: any): Promise<Set<string>> => {
      const field = rule.field as string
      const opRaw = String(rule.op || '').trim()
      const value = rule.value
      const value2 = rule.value2
      const isDateField = field === 'created_at' || field === 'updated_at'
      const timeFrom = rule?.time?.from
      const timeTo = rule?.time?.to
      const hasTimeRange = isDateField && (typeof timeFrom === 'string' || typeof timeTo === 'string')
      const op = normalizePropertyOp(opRaw)

      if (
        op === 'between' &&
        !isDateField &&
        (value2 === null || value2 === undefined || (typeof value2 === 'string' && value2.trim() === ''))
      ) {
        return new Set<string>()
      }

      const computeRelativeRange = (direction: 'past' | 'future' = 'past'): { start: string; end: string } | null => {
        const unitSource = rule.timeUnit ?? rule.unit ?? 'days'
        const rawUnit = String(unitSource).toLowerCase()
        const numericValue = Number(rule.timeValue ?? rule.value ?? NaN)
        if (!Number.isFinite(numericValue) || numericValue < 0) return null

        const units: Record<string, number> = {
          minutes: 60 * 1000,
          hours: 60 * 60 * 1000,
          days: 24 * 60 * 60 * 1000,
          weeks: 7 * 24 * 60 * 60 * 1000,
          months: 30 * 24 * 60 * 60 * 1000,
          years: 365 * 24 * 60 * 60 * 1000
        }

        const now = new Date()
        const baseUnitMs: number = units[rawUnit] ?? units['days'] ?? 24 * 60 * 60 * 1000
        const delta = baseUnitMs * numericValue
        const start = direction === 'past' ? new Date(now.getTime() - delta) : now
        const end = direction === 'past' ? now : new Date(now.getTime() + delta)
        return { start: start.toISOString(), end: end.toISOString() }
      }

      const mappedField = mapAudienceFieldToContactField(field)

      if (field === 'tags' || mappedField === 'tags') {
        const tagOp = normalizeTagOp(opRaw)

        if (tagOp === 'contains' || tagOp === 'not_contains') {
          const expected = String(value ?? '').trim().toLowerCase()
          if (!expected) return new Set<string>()
          const fetchQuery = this.supabase
            .from('contacts')
            .select('id, tags')
            .eq('organization_id', organizationId)
            .eq('project_id', projectId)
          const data = await fetchAll(fetchQuery, 1000)
          if (!data || data.length === 0) return new Set<string>()
          const result = new Set<string>()
          for (const row of data as any[]) {
            const id = row?.id as string | undefined
            if (!id) continue
            const tags = Array.isArray(row.tags) ? row.tags : []
            const hasMatch = tags.some((t: any) => String(t ?? '').toLowerCase().includes(expected))
            if (tagOp === 'contains' && hasMatch) result.add(id)
            else if (tagOp === 'not_contains' && !hasMatch) result.add(id)
          }
          return result
        }

        const tagValues = normalizeTagValues(value)
        let tagQuery = this.supabase
          .from('contacts')
          .select('id')
          .eq('organization_id', organizationId)
          .eq('project_id', projectId)

        if (tagOp === 'equals') {
          if (tagValues.length === 0) return new Set<string>()
          tagQuery = tagQuery.contains('tags', tagValues)
        } else if (tagOp === 'not_equals') {
          if (tagValues.length === 0) return new Set<string>()
          const arrLiteral = `{${tagValues.join(',')}}`
          tagQuery = tagQuery.or(`tags.is.null,tags.not.cs.${arrLiteral}`)
        } else if (tagOp === 'has_any') {
          if (tagValues.length === 0) return new Set<string>()
          tagQuery = tagQuery.overlaps('tags', tagValues)
        } else if (tagOp === 'is_empty') {
          tagQuery = tagQuery.or('tags.is.null,tags.eq.{}')
        } else if (tagOp === 'is_not_empty') {
          tagQuery = tagQuery.not('tags', 'is', null).neq('tags', '{}')
        } else {
          this.dlog('tags rule with unsupported op, returning empty', { opRaw, tagOp })
          return new Set<string>()
        }

        const tagData = await fetchAll(tagQuery, 1000)
        if (!tagData || tagData.length === 0) return new Set<string>()
        return new Set<string>(tagData.map((r: any) => r.id as string))
      }

      const known = ['email', 'first_name', 'last_name', 'phone', 'is_subscribed', 'created_at', 'updated_at', 'name']
      const isJsonb = !(known.includes(field) || known.includes(mappedField))
      const dbField = isJsonb ? `properties->>${field}` : mappedField
      const shouldNumericJsonb =
        isJsonb && isNumericOperator(op) && isNumericLike(value) && (op !== 'between' || isNumericLike(value2))
      const shouldManualJsonbNegation = isJsonb && (op === 'not_equals' || op === 'not_contains')

      let query = this.supabase
        .from('contacts')
        .select(shouldNumericJsonb || shouldManualJsonbNegation ? 'id, properties' : 'id')
        .eq('organization_id', organizationId)
        .eq('project_id', projectId)

      if (shouldNumericJsonb) {
        const a = Number(value)
        const b = op === 'between' ? Number(value2) : undefined
        if (!Number.isFinite(a) || (op === 'between' && !Number.isFinite(b))) return new Set<string>()
        if (op === 'between' && a > (b as number)) return new Set<string>()

        query = query.not(dbField, 'is', null).neq(dbField, '')
        const data = await fetchAll(query, 1000)
        if (!data || data.length === 0) return new Set<string>()

        const res = new Set<string>()
        for (const row of data as any[]) {
          const raw = row?.properties?.[field]
          const n = Number(raw)
          if (!Number.isFinite(n)) continue
          if (op === '>' && n > a) res.add(row.id as string)
          else if (op === '>=' && n >= a) res.add(row.id as string)
          else if (op === '<' && n < a) res.add(row.id as string)
          else if (op === '<=' && n <= a) res.add(row.id as string)
          else if (op === 'between' && n >= a && n <= (b as number)) res.add(row.id as string)
        }
        return res
      }

      if (shouldManualJsonbNegation) {
        const data = await fetchAll(query, 1000)
        if (!data || data.length === 0) return new Set<string>()

        const expected = value == null ? '' : String(value)
        const expectedLower = expected.toLowerCase()
        const res = new Set<string>()

        for (const row of data as any[]) {
          const raw = row?.properties?.[field]
          const id = row?.id as string | undefined
          if (!id) continue

          if (op === 'not_equals') {
            if (raw === null || raw === undefined) {
              res.add(id)
              continue
            }
            const rawStr = String(raw)
            if (expected === '') {
              if (rawStr !== '') res.add(id)
            } else if (rawStr !== expected) {
              res.add(id)
            }
            continue
          }

          if (op === 'not_contains') {
            if (raw === null || raw === undefined) {
              res.add(id)
              continue
            }
            if (expected === '') continue
            const rawStr = String(raw).toLowerCase()
            if (!rawStr.includes(expectedLower)) res.add(id)
          }
        }

        return res
      }

      const apply = (dbField: string, isJsonb: boolean = false) => {
        switch (op) {
          case 'equals':
            if (isDateField) {
              if (hasTimeRange) {
                if (typeof timeFrom === 'string' && timeFrom.trim()) query = query.gte(dbField, timeFrom.trim())
                if (typeof timeTo === 'string' && timeTo.trim()) query = query.lte(dbField, timeTo.trim())
              } else {
                const startIso = normalizeDateBoundary(value, 'start')
                const endIsoExclusive = normalizeDateBoundary(value, 'end')
                if (startIso) query = query.gte(dbField, startIso)
                if (endIsoExclusive) query = query.lt(dbField, endIsoExclusive)
              }
            } else {
              query = query.eq(dbField, value)
            }
            break
          case 'not_equals':
            if (isJsonb) {
              if (value === '' || value === null || value === undefined) {
                query = query.or(`${dbField}.is.null,${dbField}.neq.`)
              } else {
                query = query.or(`${dbField}.is.null,${dbField}.neq.${value}`)
              }
            } else {
              query = query.neq(dbField, value)
            }
            break
          case 'contains':
            query = query.ilike(dbField, `%${value}%`)
            break
          case 'not_contains':
            if (isJsonb) {
              query = query.or(`${dbField}.is.null,${dbField}.not.ilike.%${value}%`)
            } else {
            query = query.not(dbField, 'ilike', `%${value}%`)
            }
            break
          case 'starts_with':
            query = query.ilike(dbField, `${value}%`)
            break
          case 'ends_with':
            query = query.ilike(dbField, `%${value}`)
            break
          case '>':
            query = query.gt(dbField, value)
            break
          case '>=':
            query = query.gte(dbField, value)
            break
          case '<':
            query = query.lt(dbField, value)
            break
          case '<=':
            query = query.lte(dbField, value)
            break
          case 'between':
            if (isDateField) {
              if (hasTimeRange) {
                if (typeof timeFrom === 'string' && timeFrom.trim()) query = query.gte(dbField, timeFrom.trim())
                if (typeof timeTo === 'string' && timeTo.trim()) query = query.lte(dbField, timeTo.trim())
              } else {
                const startIso = normalizeDateBoundary(value, 'start')
                const endIsoExclusive = normalizeDateBoundary(value2 ?? value, 'end')
                if (startIso) query = query.gte(dbField, startIso)
                if (endIsoExclusive) query = query.lt(dbField, endIsoExclusive)
              }
            } else {
              if (value != null) query = query.gte(dbField, value)
              if (value2 != null) query = query.lte(dbField, value2)
            }
            break
          case 'after':
            if (isDateField) {
              if (hasTimeRange && typeof timeFrom === 'string' && timeFrom.trim()) {
                query = query.gte(dbField, timeFrom.trim())
              } else {
                const startIso = normalizeDateBoundary(value, 'start')
                if (startIso) query = query.gte(dbField, startIso)
              }
            } else {
              query = query.gt(dbField, value)
            }
            break
          case 'before':
            if (isDateField) {
              if (hasTimeRange && typeof timeTo === 'string' && timeTo.trim()) {
                query = query.lte(dbField, timeTo.trim())
              } else {
                const endIsoExclusive = normalizeDateBoundary(value, 'end')
                if (endIsoExclusive) query = query.lt(dbField, endIsoExclusive)
              }
            } else {
              query = query.lt(dbField, value)
            }
            break
          case 'is_empty':
            if (isJsonb) query = query.or(`${dbField}.is.null,${dbField}.eq.`)
            else query = query.or(`${dbField}.is.null,${dbField}.eq.`)
            break
          case 'is_not_empty':
            if (isJsonb) {
              const parts = dbField.split('->>')
              const propertyObj = (parts[0] || '').trim()
              const keyName = field
              if (propertyObj) {
                query = query
                  .filter(`${propertyObj}`, 'cs', `{"${keyName}":`)
                  .not(dbField, 'is', null)
                  .neq(dbField, '')
              } else {
                query = query.not(dbField, 'is', null).neq(dbField, '')
              }
            } else {
              query = query.not(dbField, 'is', null).neq(dbField, '')
            }
            break
          case 'is_true':
            query = query.eq(dbField, true)
            break
          case 'is_false':
            query = query.eq(dbField, false)
            break
          case 'in_the_last': {
            const range = computeRelativeRange('past')
            if (range) query = query.gte(dbField, range.start).lte(dbField, range.end)
            break
          }
          case 'in_the_next': {
            const range = computeRelativeRange('future')
            if (range) query = query.gte(dbField, range.start).lte(dbField, range.end)
            break
          }
        }
      }

      apply(dbField, isJsonb)

      const data = await fetchAll(query, 1000)
      if (!data || data.length === 0) return new Set<string>()
      return new Set<string>(data.map((r: any) => r.id as string))
    }

    const evalRule = (rule: any) => rule.kind === 'event' ? evalEventRule(rule) : evalPropertyRule(rule)

    const evalGroup = async (group: any): Promise<Set<string>> => {
      const rules = group.rules || []
      if (rules.length === 0) return new Set<string>()

      const op = group.operator

      // OR: run all rules in parallel then union
      if (op === 'OR') {
        const results = await Promise.all(rules.map(async (rule: any) => {
          const set = await evalRule(rule)
          return rule.negate ? diff(allContactIds, set) : set
        }))
        let acc = results[0]!
        for (let i = 1; i < results.length; i++) {
          acc = union(acc, results[i]!)
        }
        return acc
      }

      // AND / NOT: sequential with early exit on empty set
      let acc: Set<string> | null = null
      for (const rule of rules) {
        const set = await evalRule(rule)
        const s = rule.negate ? diff(allContactIds, set) : set

        if (acc == null) {
          acc = s
        } else {
          if (op === 'AND') acc = intersect(acc, s)
          else if (op === 'NOT') acc = diff(acc, s)
        }

        // Early exit: AND/NOT with empty set can't recover
        if (acc.size === 0) return acc
      }
      return acc || new Set<string>()
    }

    // Evaluate groups — detect if we can parallelize
    const allGroups = (criteria as any).groups as any[]
    let result: Set<string> | null = null

    // Check if all groups combine with OR → can run in parallel
    const allCombineWithOr = allGroups.length > 1 && allGroups.every((g: any, i: number) => {
      if (i === 0) return true
      return String((g as any).combineOperator || g.operator || 'AND').toUpperCase() === 'OR'
    })

    if (allCombineWithOr) {
      const groupResults = await Promise.all(allGroups.map((g: any) => evalGroup(g)))
      result = groupResults[0]!
      for (let i = 1; i < groupResults.length; i++) {
        result = union(result, groupResults[i]!)
      }
    } else {
      for (let i = 0; i < allGroups.length; i++) {
        const group = allGroups[i]
        const gset = await evalGroup(group)
        if (result == null) {
          result = gset
        } else {
          const betweenOp = String((group as any).combineOperator || group.operator || 'AND').toUpperCase()
          if (betweenOp === 'AND') result = intersect(result, gset)
          else if (betweenOp === 'OR') result = union(result, gset)
          else if (betweenOp === 'NOT') result = diff(result, gset)
        }
        // Early exit for AND between groups
        if (result.size === 0) {
          const nextOp = i + 1 < allGroups.length
            ? String(((allGroups[i + 1] as any).combineOperator || (allGroups[i + 1] as any).operator || 'AND')).toUpperCase()
            : ''
          if (nextOp === 'AND' || nextOp === 'NOT') break
        }
      }
    }

    const finalSet = result || new Set<string>()
    this.dlog('done', { total: finalSet.size })
    return finalSet
  }

  /**
   * Variante otimizada para avaliar apenas UM contato (useful para Journeys/Live).
   * Retorna true se o contact_id atende ao critério.
   *
   * @param triggerEvent (opcional) Restringe a avaliação de regras de evento
   *   ao evento específico que disparou o fluxo (ex.: split de Journey).
   *   Quando informado, a query em `contact_events` é limitada a este registro
   *   pelo `event_id` (preferencial) OU pelo par (event_name, event_timestamp).
   *   Sem isso, o engine percorre TODO o histórico do contato — esse é o
   *   comportamento correto para audiências/segmentos, mas é um BUG quando o
   *   uso é "avaliar a propriedade do evento de gatilho atual" em journeys.
   */
  async matchesContactByAudienceCriteriaV2(
    organizationId: string,
    projectId: string,
    criteriaRaw: any,
    contactId: string,
    triggerEvent?: {
      event_id?: string
      event_name?: string
      event_timestamp?: string
    }
  ): Promise<boolean> {
    let criteria: AudienceCriteria = CriteriaParser.parse(criteriaRaw as any) as any

    if (
      (!Array.isArray((criteria as any).groups) || (criteria as any).groups.length === 0) &&
      (Array.isArray((criteria as any).filters) || Array.isArray((criteria as any).conditions))
    ) {
      criteria = coerceCriteriaToGroups(criteria)
    }

    if (!criteria || !Array.isArray((criteria as any).groups) || (criteria as any).groups.length === 0) {
      return false
    }

    const typeId = String((criteria as any)?.type || '')

    const projectTimezone = await this.getProjectTimezone(organizationId, projectId)

    // Carregar identidade do contato (para reachy_id)
    const { data: contactRow } = await this.supabase
      .from('contacts')
      .select('id, reachy_id, email')
      .eq('organization_id', organizationId)
      .eq('project_id', projectId)
      .eq('id', contactId)
      .maybeSingle()

    const reachyId = contactRow?.reachy_id ? String(contactRow.reachy_id).trim() : ''

    const evalEventRuleForContact = async (rule: any): Promise<boolean> => {
      const cfg = (criteria as any)?.config || {}

      // Route to ClickHouse when available — use batch engine scoped to 1 contact
      if (this.chExecutor) {
        try {
          // Build single-contact identity maps for the executor
          const singleContactIdToReachyId = new Map<string, string>()
          const singleReachyIdToContactId = new Map<string, string>()
          const singleAllContactIds = new Set<string>([contactId])
          if (reachyId) {
            singleReachyIdToContactId.set(reachyId, contactId)
            singleContactIdToReachyId.set(contactId, reachyId)
          }

          const matchSet = await this.chExecutor.execute(
            rule, criteria, organizationId, projectId, projectTimezone,
            singleReachyIdToContactId, singleContactIdToReachyId, singleAllContactIds,
            triggerEvent // narrow ao evento de gatilho (split de Journey c/ event_property)
          )
          if (matchSet.has(contactId)) return true
          // Sem narrow de gatilho, o CH e autoritativo -> retorna o resultado direto.
          // Com narrow ativo, um miss pode ser apenas lag de sync Supabase->CH; nesse
          // caso caimos no path Supabase abaixo (mesmo narrow + retry) para decidir.
          if (!triggerEvent) return false
        } catch (chErr) {
          this.logger.warn('[V2AudienceEngine] ClickHouse evalEventRuleForContact failed, falling back to Supabase:', chErr)
        }
      }

      const effectiveEventName =
        cfg.eventType && String(cfg.eventType).trim() !== ''
          ? String(cfg.eventType)
          : String(rule.eventName)

      let query = this.supabase
        .from('contact_events')
        .select('contact_id, reachy_id, event_data, event_timestamp, event_name')
        .eq('organization_id', organizationId)
        .eq('project_id', projectId)
        .eq('event_name', effectiveEventName)

      // NARROW para 1 contato (sempre que possível)
      const ors: string[] = []
      if (contactId) ors.push(`contact_id.eq.${contactId}`)
      if (reachyId) ors.push(`reachy_id.eq.${reachyId}`)
      if (ors.length > 0) query = query.or(ors.join(','))

      // 🎯 NARROW para o evento que disparou a journey (split com event_property).
      // Sem isso, a avaliação considera TODO o histórico do contato, fazendo a
      // condição cair sempre em "yes" se algum evento histórico bater.
      // Só restringe quando o nome do evento de gatilho casa com a regra avaliada
      // (caso contrário, manteria o comportamento correto p/ outras regras).
      const triggerEventNameMatches =
        !!triggerEvent &&
        typeof triggerEvent.event_name === 'string' &&
        String(triggerEvent.event_name).trim() === String(effectiveEventName).trim()

      if (triggerEvent && triggerEventNameMatches) {
        if (triggerEvent.event_id) {
          query = query.eq('id', triggerEvent.event_id)
        } else if (triggerEvent.event_timestamp) {
          query = query.eq('event_timestamp', triggerEvent.event_timestamp)
        }
      }

      // live presets helpers (paridade)
      if (typeId === 'live-page-visit') {
        const v = cfg.pageUrl
        const d = cfg.domain
        const ors2: string[] = []
        if (v && String(v).trim() !== '') {
          const like = `%${String(v)}%`
          ors2.push(`path.ilike.${like}`)
          ors2.push(`current_url.ilike.${like}`)
        }
        if (d && String(d).trim() !== '') {
          const dlike = `%${String(d)}%`
          ors2.push(`domain.ilike.${dlike}`)
          ors2.push(`event_data->>domain.ilike.${dlike}`)
        }
        if (ors2.length > 0) query = query.or(ors2.join(','))
      }

      if (typeId === 'live-referrer') {
        query = query.eq('session_is_new', true)
        const ors2: string[] = []
        const v = cfg.referrerUrl
        if (v && String(v).trim() !== '') ors2.push(`referrer.ilike.%${String(v)}%`)
        if (cfg.utm_source && String(cfg.utm_source).trim() !== '') ors2.push(`event_data->>utm_source.ilike.%${String(cfg.utm_source)}%`)
        if (cfg.utm_medium && String(cfg.utm_medium).trim() !== '') ors2.push(`event_data->>utm_medium.ilike.%${String(cfg.utm_medium)}%`)
        if (cfg.utm_campaign && String(cfg.utm_campaign).trim() !== '') ors2.push(`event_data->>utm_campaign.ilike.%${String(cfg.utm_campaign)}%`)
        if (cfg.utm_term && String(cfg.utm_term).trim() !== '') ors2.push(`event_data->>utm_term.ilike.%${String(cfg.utm_term)}%`)
        if (cfg.utm_content && String(cfg.utm_content).trim() !== '') ors2.push(`event_data->>utm_content.ilike.%${String(cfg.utm_content)}%`)
        if (ors2.length > 0) query = query.or(ors2.join(','))
      }

      // TimeFrame via config (fallback)
      if (!rule.time && cfg && cfg.timeFrame) {
        const tf = String(cfg.timeFrame).trim()
        const now = new Date()
        let unit: 'minutes' | 'hours' | 'days' | 'weeks' | 'months' = 'days'
        let value = 7
        if (/^\d+$/.test(tf)) { value = Number(tf); unit = 'days' }
        else if (/^\d+\s*m$/.test(tf)) { value = Number(tf.replace(/m$/, '')); unit = 'minutes' }
        else if (/^\d+\s*h$/.test(tf)) { value = Number(tf.replace(/h$/, '')); unit = 'hours' }
        else if (/^\d+\s*d$/.test(tf)) { value = Number(tf.replace(/d$/, '')); unit = 'days' }
        else if (/^\d+\s*w$/.test(tf)) { value = Number(tf.replace(/w$/, '')); unit = 'weeks' }
        else if (/^\d+\s*mo$/.test(tf)) { value = Number(tf.replace(/mo$/, '')); unit = 'months' }
        const units: Record<typeof unit, number> = {
          minutes: 60 * 1000,
          hours: 60 * 60 * 1000,
          days: 24 * 60 * 60 * 1000,
          weeks: 7 * 24 * 60 * 60 * 1000,
          months: 30 * 24 * 60 * 60 * 1000
        }
        const from = new Date(now.getTime() - value * units[unit])
        query = query.gte('event_timestamp', from.toISOString())
      }

      // Janela temporal explícita (relativa ou absoluta)
      if (rule.time) {
        if (rule.time.unit && rule.time.value) {
          type TimeUnit = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
          const unit = String(rule.time.unit) as TimeUnit
          const value = Number(rule.time.value)
          const now = new Date()
          const units: Record<TimeUnit, number> = {
            minutes: 60 * 1000,
            hours: 60 * 60 * 1000,
            days: 24 * 60 * 60 * 1000,
            weeks: 7 * 24 * 60 * 60 * 1000,
            months: 30 * 24 * 60 * 60 * 1000
          }
          const ms = units[unit] ?? units['days']
          const from = new Date(now.getTime() - value * ms)
          query = query.gte('event_timestamp', from.toISOString())
        } else {
          if (rule.time.from) {
            const rawFrom = rule.time.from
            const fromStr = typeof rawFrom === 'string' ? rawFrom : String(rawFrom)
            const isDateOnly = typeof rawFrom === 'string' && !fromStr.includes('T')
            const startIso = isDateOnly ? normalizeDateBoundary(fromStr, 'start') : undefined
            const finalFrom = startIso || fromStr
            query = query.gte('event_timestamp', finalFrom)
          }
          if (rule.time.to) {
            const rawTo = rule.time.to
            const toStr = typeof rawTo === 'string' ? rawTo : String(rawTo)
            const isDateOnly = typeof rawTo === 'string' && !toStr.includes('T')
            const endIsoExclusive = isDateOnly ? normalizeDateBoundary(toStr, 'end') : undefined
            const finalTo = endIsoExclusive || toStr
            query = isDateOnly ? query.lt('event_timestamp', finalTo) : query.lte('event_timestamp', finalTo)
          }
        }
      }

      // Event attributes
      const interest = (rule as any)?.interest
      try {
        let attributes = Array.isArray(rule.attributes) ? rule.attributes : (rule.attributes ? [rule.attributes] : [])
        const interestKey = interest?.key ? String(interest.key).trim() : ''
        if (interestKey) {
          attributes = attributes.filter((a: any) => String(a?.key || '').trim() !== interestKey)
        }
        const resolveDbFieldForAttrKey = (keyRaw: string) => {
          const key = String(keyRaw || '').trim()
          if (!key) return null
          const columnKeys = new Set([
            'current_url',
            'domain',
            'path',
            'referrer',
            'page_title',
            'utm_source',
            'utm_medium',
            'utm_campaign',
            'utm_term',
            'utm_content',
            'session_id',
            'session_is_new'
          ])
          if (columnKeys.has(key)) return { kind: 'column', field: key }
          const buildNested = (root: string, dottedPath: string) => jsonNestedTextAccessor(root, dottedPath)
          if (key.startsWith('event_data.')) return { kind: 'json', field: buildNested('event_data', key.replace(/^event_data\./, '')) }
          if (key.startsWith('custom_data.')) return { kind: 'json', field: buildNested('event_data->custom_data', key.replace(/^custom_data\./, '')) }
          if (key.startsWith('url_data.')) return { kind: 'json', field: buildNested('event_data->url_data', key.replace(/^url_data\./, '')) }
          if (key.startsWith('utm_data.')) return { kind: 'json', field: buildNested('event_data->utm_data', key.replace(/^utm_data\./, '')) }
          if (key.startsWith('session_data.')) return { kind: 'json', field: buildNested('event_data->session_data', key.replace(/^session_data\./, '')) }
          if (key.includes('.')) return { kind: 'json', field: buildNested('event_data->custom_data', key) }
          return { kind: 'json', field: jsonTextAccessor('event_data', key) }
        }

        for (const attr of attributes) {
          const key = String(attr.key || '').trim()
          const op = String(attr.op || 'contains')
          const value = attr.value
          if (!key || value == null) continue
          const resolved = resolveDbFieldForAttrKey(key)
          if (!resolved) continue
          const dbField = resolved.field

          switch (op) {
            case 'equals':
              query = query.filter(dbField, 'eq', value)
              break
            case 'not_equals':
              query = query.filter(dbField, 'neq', value)
              break
            case 'starts_with':
              query = query.filter(dbField, 'ilike', `${value}%`)
              break
            case 'ends_with':
              query = query.filter(dbField, 'ilike', `%${value}`)
              break
            case 'contains':
            default:
              query = query.filter(dbField, 'ilike', `%${value}%`)
              break
          }
        }
      } catch {
      }

      const { data, error } = await query
      if (error) return false

      // Event advanced filters (ex.: event_property dentro do DID)
      let rows = (data || []) as any[]
      const ruleFiltersAll = Array.isArray((rule as any).filters) ? (rule as any).filters : []
      const hasFirstTime = ruleFiltersAll.some((f: any) => String(f?.type || '').trim() === 'first_time')
      const hasLastTime = ruleFiltersAll.some((f: any) => String(f?.type || '').trim() === 'last_time')
      const ruleFilters = ruleFiltersAll.filter((f: any) => {
        const t = String(f?.type || '').trim()
        return t && t !== 'first_time' && t !== 'last_time'
      })

      if (ruleFilters.length > 0) {
        rows = applyEventRuleFilters(rows, ruleFilters, { timezone: projectTimezone })
      }

      if ((hasFirstTime || hasLastTime) && rows.length > 0) {
        const now = new Date()
        let windowStartIso: string | undefined
        let windowEndIso: string | undefined

        if (rule.time) {
          if (rule.time.unit && rule.time.value != null) {
            type TimeUnit = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
            const unit = String(rule.time.unit) as TimeUnit
            const valueNum = Number(rule.time.value)
            const units: Record<TimeUnit, number> = {
              minutes: 60 * 1000,
              hours: 60 * 60 * 1000,
              days: 24 * 60 * 60 * 1000,
              weeks: 7 * 24 * 60 * 60 * 1000,
              months: 30 * 24 * 60 * 60 * 1000,
            }
            const ms = units[unit] ?? units['days']
            if (Number.isFinite(valueNum) && valueNum >= 0) {
              windowStartIso = new Date(now.getTime() - valueNum * ms).toISOString()
              windowEndIso = now.toISOString()
            }
          } else {
            if (rule.time.from) windowStartIso = String(rule.time.from)
            if (rule.time.to) windowEndIso = String(rule.time.to)
          }
        } else if (cfg && cfg.timeFrame) {
          const tf = String(cfg.timeFrame).trim()
          let unit: 'minutes' | 'hours' | 'days' | 'weeks' | 'months' = 'days'
          let value = 7
          if (/^\d+$/.test(tf)) { value = Number(tf); unit = 'days' }
          else if (/^\d+\s*m$/.test(tf)) { value = Number(tf.replace(/m$/, '')); unit = 'minutes' }
          else if (/^\d+\s*h$/.test(tf)) { value = Number(tf.replace(/h$/, '')); unit = 'hours' }
          else if (/^\d+\s*d$/.test(tf)) { value = Number(tf.replace(/d$/, '')); unit = 'days' }
          else if (/^\d+\s*w$/.test(tf)) { value = Number(tf.replace(/w$/, '')); unit = 'weeks' }
          else if (/^\d+\s*mo$/.test(tf)) { value = Number(tf.replace(/mo$/, '')); unit = 'months' }
          const units: Record<typeof unit, number> = {
            minutes: 60 * 1000,
            hours: 60 * 60 * 1000,
            days: 24 * 60 * 60 * 1000,
            weeks: 7 * 24 * 60 * 60 * 1000,
            months: 30 * 24 * 60 * 60 * 1000,
          }
          if (Number.isFinite(value) && value >= 0) {
            const from = new Date(now.getTime() - value * units[unit])
            windowStartIso = from.toISOString()
            windowEndIso = now.toISOString()
          }
        }

        if (!windowEndIso) windowEndIso = now.toISOString()

        if (hasFirstTime && windowStartIso) {
          let q = this.supabase
            .from('contact_events')
            .select('id')
            .eq('organization_id', organizationId)
            .eq('project_id', projectId)
            .eq('event_name', effectiveEventName)
            .lt('event_timestamp', windowStartIso)
            .limit(1)

          // mesmo narrow de identidade que usamos acima
          const ors2: string[] = []
          if (contactId) ors2.push(`contact_id.eq.${contactId}`)
          if (reachyId) ors2.push(`reachy_id.eq.${reachyId}`)
          if (ors2.length > 0) q = q.or(ors2.join(','))

          const { data: beforeRows } = await q
          if (Array.isArray(beforeRows) && beforeRows.length > 0) return false
        }

        if (hasLastTime && windowEndIso) {
          let q = this.supabase
            .from('contact_events')
            .select('id')
            .eq('organization_id', organizationId)
            .eq('project_id', projectId)
            .eq('event_name', effectiveEventName)
            .gt('event_timestamp', windowEndIso)
            .limit(1)

          const ors2: string[] = []
          if (contactId) ors2.push(`contact_id.eq.${contactId}`)
          if (reachyId) ors2.push(`reachy_id.eq.${reachyId}`)
          if (ors2.length > 0) q = q.or(ors2.join(','))

          const { data: afterRows } = await q
          if (Array.isArray(afterRows) && afterRows.length > 0) return false
        }
      }

      // User Interests: predominantly / at least X% of the time (para 1 contato)
      if (interest && interest.key) {
        const key = String(interest.key || '').trim()
        const op = String(interest.op || 'equals').trim()
        const expected = interest.value
        const occType = String(interest.occurrenceType || 'predominantly').trim()
        const pct = Number(interest.occurrencePercentage ?? 0)
        const threshold = Number.isFinite(pct) ? Math.max(0, Math.min(100, pct)) / 100 : 0

        if (rows.length === 0) return false

        let total = 0
        let match = 0
        const counts = new Map<string, number>()

        for (const row of rows) {
          total++
          const rawVal = getEventPropertyValue(row?.event_data, key)
          const strVal = rawVal === null || rawVal === undefined ? '' : String(rawVal)
          counts.set(strVal, (counts.get(strVal) || 0) + 1)
          if (matchesInterest(rawVal, op === 'contains' ? 'contains' : op, expected)) match++
        }

        if (occType === 'at_least') {
          return match > 0 && match / total >= threshold
        }

        let maxOther = 0
        counts.forEach((cnt, valStr) => {
          if (!matchesInterest(valStr, op, expected)) {
            if (cnt > maxOther) maxOther = cnt
          }
        })

        return match > 0 && match >= maxOther
      }

      // Frequência / agregação (para 1 contato)
      if (rule.frequency && rule.frequency.value != null) {
        const { op, value, value2, type = 'count', field = 'value' } = rule.frequency
        let aggregatedValue = 0
        let denom = 0
        if (type === 'count') {
          aggregatedValue = rows.length
        } else {
          for (const row of rows) {
            const eventData = row.event_data || {}
            const raw =
              getEventPropertyValue(eventData, field) ??
              (eventData?.[field] !== undefined ? eventData[field] : undefined) ??
              eventData?.value ??
              eventData?.amount
            const numVal = Number(raw)
            if (Number.isFinite(numVal)) {
              aggregatedValue += numVal
              denom += 1
            }
          }
          if (type === 'avg') aggregatedValue = denom > 0 ? aggregatedValue / denom : 0
        }

        if (op === 'between') {
          const a = Number(value)
          const b = Number(value2)
          if (!Number.isFinite(a) || !Number.isFinite(b)) return false
          // Compatível com CleverTap: se o usuário inverter (min > max), não deve casar.
          if (a > b) return false
          return aggregatedValue >= a && aggregatedValue <= b
        }

        return (
          (op === '>=' && aggregatedValue >= value) ||
          (op === '>' && aggregatedValue > value) ||
          (op === '=' && aggregatedValue === value) ||
          (op === '!=' && aggregatedValue !== value) ||
          (op === '<=' && aggregatedValue <= value) ||
          (op === '<' && aggregatedValue < value)
        )
      }

      if (typeId === 'live-page-count' && cfg && cfg.pageCount != null) {
        const threshold = Number(cfg.pageCount)
        return rows.length >= threshold
      }

      return rows.length > 0
    }

    const evalPropertyRuleForContact = async (rule: any): Promise<boolean> => {
      const field = rule.field as string
      const opRaw = String(rule.op || '').trim()
      const value = rule.value
      const value2 = rule.value2
      const isDateField = field === 'created_at' || field === 'updated_at'
      const timeFrom = rule?.time?.from
      const timeTo = rule?.time?.to
      const hasTimeRange = isDateField && (typeof timeFrom === 'string' || typeof timeTo === 'string')
      const op = normalizePropertyOp(opRaw)

      // Mesma proteção do modo bulk: between sem value2 em campos não-data é inválido.
      if (
        op === 'between' &&
        !isDateField &&
        (value2 === null || value2 === undefined || (typeof value2 === 'string' && value2.trim() === ''))
      ) {
        return false
      }

      const computeRelativeRange = (direction: 'past' | 'future' = 'past'): { start: string; end: string } | null => {
        const unitSource = rule.timeUnit ?? rule.unit ?? 'days'
        const rawUnit = String(unitSource).toLowerCase()
        const numericValue = Number(rule.timeValue ?? rule.value ?? NaN)
        if (!Number.isFinite(numericValue) || numericValue < 0) return null
        const units: Record<string, number> = {
          minutes: 60 * 1000,
          hours: 60 * 60 * 1000,
          days: 24 * 60 * 60 * 1000,
          weeks: 7 * 24 * 60 * 60 * 1000,
          months: 30 * 24 * 60 * 60 * 1000,
          years: 365 * 24 * 60 * 60 * 1000
        }
        const now = new Date()
        const baseUnitMs: number = units[rawUnit] ?? units['days'] ?? 24 * 60 * 60 * 1000
        const delta = baseUnitMs * numericValue
        const start = direction === 'past' ? new Date(now.getTime() - delta) : now
        const end = direction === 'past' ? now : new Date(now.getTime() + delta)
        return { start: start.toISOString(), end: end.toISOString() }
      }

      const mappedField = mapAudienceFieldToContactField(field)

      // Coluna `tags` é text[] — desvia para operadores de array antes do path scalar/JSONB.
      if (field === 'tags' || mappedField === 'tags') {
        const tagOp = normalizeTagOp(opRaw)

        const { data: tagsRow, error: tagsErr } = await this.supabase
          .from('contacts')
          .select('id, tags')
          .eq('organization_id', organizationId)
          .eq('project_id', projectId)
          .eq('id', contactId)
          .maybeSingle()

        if (tagsErr) {
          this.logger.warn('[V2AudienceEngine] tags lookup failed in matchesContact', {
            organizationId,
            projectId,
            contactId,
            opRaw,
            tagOp,
            error: tagsErr
          })
          return false
        }

        if (!tagsRow || !tagsRow.id) return false
        const tags = Array.isArray((tagsRow as any)?.tags)
          ? (tagsRow as any).tags.map((t: any) => String(t ?? '').trim()).filter((t: string) => t.length > 0)
          : []

        if (tagOp === 'contains' || tagOp === 'not_contains') {
          const expected = String(value ?? '').trim().toLowerCase()
          if (!expected) return false
          const hasMatch = tags.some((t: string) => t.toLowerCase().includes(expected))
          return tagOp === 'contains' ? hasMatch : !hasMatch
        }

        const tagValues = normalizeTagValues(value)
        if (tagOp === 'equals') {
          if (tagValues.length === 0) return false
          return tagValues.every((v) => tags.includes(v))
        }
        if (tagOp === 'not_equals') {
          if (tagValues.length === 0) return false
          return !tagValues.every((v) => tags.includes(v))
        }
        if (tagOp === 'has_any') {
          if (tagValues.length === 0) return false
          return tagValues.some((v) => tags.includes(v))
        }
        if (tagOp === 'is_empty') {
          return tags.length === 0
        }
        if (tagOp === 'is_not_empty') {
          return tags.length > 0
        }

        this.dlog('tags rule with unsupported op in matchesContact, returning false', { opRaw, tagOp })
        return false
      }

      const known = ['email', 'first_name', 'last_name', 'phone', 'is_subscribed', 'created_at', 'updated_at', 'name']
      const isJsonb = !(known.includes(field) || known.includes(mappedField))
      const dbField = isJsonb ? `properties->>${field}` : mappedField
      const shouldNumericJsonb =
        isJsonb && isNumericOperator(op) && isNumericLike(value) && (op !== 'between' || isNumericLike(value2))
      const shouldManualJsonbNegation = isJsonb && (op === 'not_equals' || op === 'not_contains')

      let query = this.supabase
        .from('contacts')
        .select(shouldNumericJsonb || shouldManualJsonbNegation ? 'id, properties' : 'id')
        .eq('organization_id', organizationId)
        .eq('project_id', projectId)
        .eq('id', contactId)

      if (shouldNumericJsonb) {
        const a = Number(value)
        const b = op === 'between' ? Number(value2) : undefined
        if (!Number.isFinite(a) || (op === 'between' && !Number.isFinite(b))) return false
        if (op === 'between' && a > (b as number)) return false

        query = query.not(dbField, 'is', null).neq(dbField, '')
        const { data, error } = await query
        if (error) return false
        const row = Array.isArray(data) ? data[0] : undefined
        const raw = row?.properties?.[field]
        const n = Number(raw)
        if (!Number.isFinite(n)) return false
        if (op === '>') return n > a
        if (op === '>=') return n >= a
        if (op === '<') return n < a
        if (op === '<=') return n <= a
        if (op === 'between') return n >= a && n <= (b as number)
        return false
      }

      if (shouldManualJsonbNegation) {
        const { data, error } = await query
        if (error) return false
        const row = Array.isArray(data) ? data[0] : undefined
        const raw = row?.properties?.[field]

        const expected = value == null ? '' : String(value)
        const expectedLower = expected.toLowerCase()

        if (op === 'not_equals') {
          if (raw === null || raw === undefined) return true
          const rawStr = String(raw)
          if (expected === '') return rawStr !== ''
          return rawStr !== expected
        }

        if (op === 'not_contains') {
          if (raw === null || raw === undefined) return true
          if (expected === '') return false
          const rawStr = String(raw).toLowerCase()
          return !rawStr.includes(expectedLower)
        }
        return false
      }

      const apply = (dbField: string, isJsonb: boolean = false) => {
        switch (op) {
          case 'equals':
            if (isDateField) {
              if (hasTimeRange) {
                if (typeof timeFrom === 'string' && timeFrom.trim()) query = query.gte(dbField, timeFrom.trim())
                if (typeof timeTo === 'string' && timeTo.trim()) query = query.lte(dbField, timeTo.trim())
              } else {
                const startIso = normalizeDateBoundary(value, 'start')
                const endIsoExclusive = normalizeDateBoundary(value, 'end')
                if (startIso) query = query.gte(dbField, startIso)
                if (endIsoExclusive) query = query.lt(dbField, endIsoExclusive)
              }
            } else {
              query = query.eq(dbField, value)
            }
            break
          case 'not_equals':
            if (isJsonb) {
              if (value === '' || value === null || value === undefined) {
                query = query.or(`${dbField}.is.null,${dbField}.neq.`)
              } else {
                query = query.or(`${dbField}.is.null,${dbField}.neq.${value}`)
              }
            } else {
              query = query.neq(dbField, value)
            }
            break
          case 'contains':
            query = query.ilike(dbField, `%${value}%`)
            break
          case 'not_contains':
            if (isJsonb) {
              query = query.or(`${dbField}.is.null,${dbField}.not.ilike.%${value}%`)
            } else {
            query = query.not(dbField, 'ilike', `%${value}%`)
            }
            break
          case 'starts_with':
            query = query.ilike(dbField, `${value}%`)
            break
          case 'ends_with':
            query = query.ilike(dbField, `%${value}`)
            break
          case '>':
            query = query.gt(dbField, value)
            break
          case '>=':
            query = query.gte(dbField, value)
            break
          case '<':
            query = query.lt(dbField, value)
            break
          case '<=':
            query = query.lte(dbField, value)
            break
          case 'between':
            if (isDateField) {
              if (hasTimeRange) {
                if (typeof timeFrom === 'string' && timeFrom.trim()) query = query.gte(dbField, timeFrom.trim())
                if (typeof timeTo === 'string' && timeTo.trim()) query = query.lte(dbField, timeTo.trim())
              } else {
                const startIso = normalizeDateBoundary(value, 'start')
                const endIsoExclusive = normalizeDateBoundary(value2 ?? value, 'end')
                if (startIso) query = query.gte(dbField, startIso)
                if (endIsoExclusive) query = query.lt(dbField, endIsoExclusive)
              }
            } else {
              if (value != null) query = query.gte(dbField, value)
              if (value2 != null) query = query.lte(dbField, value2)
            }
            break
          case 'after':
            if (isDateField) {
              if (hasTimeRange && typeof timeFrom === 'string' && timeFrom.trim()) query = query.gte(dbField, timeFrom.trim())
              else {
                const startIso = normalizeDateBoundary(value, 'start')
                if (startIso) query = query.gte(dbField, startIso)
              }
            } else query = query.gt(dbField, value)
            break
          case 'before':
            if (isDateField) {
              if (hasTimeRange && typeof timeTo === 'string' && timeTo.trim()) query = query.lte(dbField, timeTo.trim())
              else {
                const endIsoExclusive = normalizeDateBoundary(value, 'end')
                if (endIsoExclusive) query = query.lt(dbField, endIsoExclusive)
              }
            } else query = query.lt(dbField, value)
            break
          case 'is_empty':
            if (isJsonb) query = query.or(`${dbField}.is.null,${dbField}.eq.`)
            else query = query.or(`${dbField}.is.null,${dbField}.eq.`)
            break
          case 'is_not_empty':
            if (isJsonb) query = query.not(dbField, 'is', null).neq(dbField, '')
            else query = query.not(dbField, 'is', null).neq(dbField, '')
            break
          case 'is_true':
            query = query.eq(dbField, true)
            break
          case 'is_false':
            query = query.eq(dbField, false)
            break
          case 'in_the_last': {
            const range = computeRelativeRange('past')
            if (range) query = query.gte(dbField, range.start).lte(dbField, range.end)
            break
          }
          case 'in_the_next': {
            const range = computeRelativeRange('future')
            if (range) query = query.gte(dbField, range.start).lte(dbField, range.end)
            break
          }
        }
      }

      apply(dbField, isJsonb)

      const { data, error } = await query
      if (error) return false
      return (data || []).length > 0
    }

    const evalRuleForContact = async (rule: any): Promise<boolean> => {
      const base = rule.kind === 'event' ? await evalEventRuleForContact(rule) : await evalPropertyRuleForContact(rule)
      const res = rule.negate ? !base : base
      return res
    }

    const evalGroupForContact = async (group: any): Promise<boolean> => {
      const rules = group.rules || []
      if (rules.length === 0) return false
      const op = group.operator

      let acc: boolean | null = null
      for (const rule of rules) {
        const r = await evalRuleForContact(rule)
        if (acc == null) {
          acc = r
        } else {
          if (op === 'AND') acc = acc && r
          else if (op === 'OR') acc = acc || r
          else if (op === 'NOT') acc = acc && !r
        }
        // Early exit: AND false can't become true; OR true can't become false
        if (op === 'AND' && acc === false) return false
        if (op === 'OR' && acc === true) return true
      }
      return !!acc
    }

    let result: boolean | null = null
    for (const group of (criteria as any).groups) {
      const g = await evalGroupForContact(group)
      if (result == null) {
        result = g
      } else {
        const betweenOp = String((group as any).combineOperator || group.operator || 'AND').toUpperCase()
        if (betweenOp === 'AND') result = result && g
        else if (betweenOp === 'OR') result = result || g
        else if (betweenOp === 'NOT') result = result && !g
      }
      // Early exit between groups
      if (result === false) {
        // If all remaining groups are AND/NOT, result stays false
        const remaining = (criteria as any).groups.slice((criteria as any).groups.indexOf(group) + 1)
        const allAnd = remaining.every((g2: any) => {
          const op2 = String(g2.combineOperator || g2.operator || 'AND').toUpperCase()
          return op2 === 'AND' || op2 === 'NOT'
        })
        if (allAnd) return false
      }
    }

    return !!result
  }

  private dlog(...args: any[]) {
    if (!this.debug) return
    this.logger.log('[AUDIENCE_MODULE_ENGINE]', ...args)
  }
}

function jsonTextAccessor(base: string, key: string): string {
  const safeKey = String(key || '').trim()
  return `${base}->>${safeKey}`
}

function jsonNestedTextAccessor(base: string, path: string): string {
  const parts = String(path || '')
    .split('.')
    .map((p) => p.trim())
    .filter(Boolean)
  if (parts.length === 0) return jsonTextAccessor(base, 'value')
  if (parts.length === 1) return jsonTextAccessor(base, parts[0]!)
  const chain = parts
    .slice(0, -1)
    .map((p) => `->${String(p).trim()}`)
    .join('')
  const last = String(parts[parts.length - 1]!).trim()
  return `${base}${chain}->>${last}`
}

function mapAudienceFieldToContactField(audienceField: string): string {
  const fieldMapping: { [key: string]: string } = {
    is_subscribed: 'is_subscribed',
    email: 'email',
    name: 'first_name',
    first_name: 'first_name',
    last_name: 'last_name',
    phone: 'phone',
    created_at: 'created_at',
    updated_at: 'updated_at',
    city: 'city',
    country: 'country',
    tags: 'tags',
    status: 'status'
  }
  return fieldMapping[audienceField] || audienceField
}

function normalizeDateBoundary(value: any, boundary: 'start' | 'end'): string | undefined {
  if (!value || typeof value !== 'string') return undefined

  const hasTimeComponent = value.includes('T')

  const toUtcStartOfDay = (y: number, mZeroBased: number, d: number): Date => {
    return new Date(Date.UTC(y, mZeroBased, d, 0, 0, 0, 0))
  }

  let baseDate: Date | null = null

  if (hasTimeComponent) {
    const dt = new Date(value)
    baseDate = Number.isNaN(dt.getTime()) ? null : dt
  } else if (/^\d{4}-\d{2}-\d{2}$/.test(value)) {
    const parts = value.split('-')
    if (parts.length === 3) {
      const y = parseInt(parts[0]!, 10)
      const mm = parseInt(parts[1]!, 10)
      const d = parseInt(parts[2]!, 10)
      if (Number.isFinite(y) && Number.isFinite(mm) && Number.isFinite(d)) {
        baseDate = toUtcStartOfDay(y, mm - 1, d)
      }
    }
  } else if (/^\d{2}\/\d{2}\/\d{4}$/.test(value)) {
    const parts = value.split('/')
    if (parts.length === 3) {
      const d = parseInt(parts[0]!, 10)
      const mm = parseInt(parts[1]!, 10)
      const y = parseInt(parts[2]!, 10)
      if (Number.isFinite(y) && Number.isFinite(mm) && Number.isFinite(d)) {
        baseDate = toUtcStartOfDay(y, mm - 1, d)
      }
    }
  } else {
    const dt = new Date(`${value}T00:00:00Z`)
    baseDate = Number.isNaN(dt.getTime()) ? null : dt
  }

  if (!baseDate) return undefined

  const utcYear = baseDate.getUTCFullYear()
  const utcMonth = baseDate.getUTCMonth()
  const utcDay = baseDate.getUTCDate()

  if (boundary === 'start') {
    const startOfDay = new Date(Date.UTC(utcYear, utcMonth, utcDay, 0, 0, 0, 0))
    return startOfDay.toISOString()
  }

  const nextDay = new Date(Date.UTC(utcYear, utcMonth, utcDay + 1, 0, 0, 0, 0))
  return nextDay.toISOString()
}

function coerceCriteriaToGroups(criteria: AudienceCriteria): AudienceCriteria {
  const groups: any[] = []

  const pushGroup = (operator: any, rules: any[]) => {
    if (!rules || rules.length === 0) return
    const op = operator === 'OR' || operator === 'NOT' ? operator : 'AND'
    groups.push({ operator: op, rules })
  }

  // From `filters` (legado)
  if (Array.isArray((criteria as any).filters)) {
    for (const fg of (criteria as any).filters) {
      const rules: any[] = []
      for (const c of fg?.conditions || []) {
        const fieldRaw = String(c?.field || '')
        const operator = c?.operator
        const value = c?.value
        const value2 = c?.value2

        if (fieldRaw === 'event') {
          rules.push({
            kind: 'event',
            eventName: value,
            negate: operator === 'not_equals',
            ...(c?.dateFrom || c?.dateTo ? { time: { from: c?.dateFrom, to: c?.dateTo } } : {}),
            ...(c?.timeValue != null ? { time: { unit: c?.timeUnit || 'days', value: Number(c?.timeValue) } } : {})
          })
          continue
        }

        // Custom field: usar a chave real para cair em properties->>key
        const field = fieldRaw === 'custom_field' && c?.customFieldKey ? String(c.customFieldKey) : fieldRaw
        const rule: any = {
          kind: 'property',
          field,
          op: operator
        }

        if (operator === 'in_the_last' || operator === 'in_the_next') {
          rule.timeValue = c?.timeValue ?? value
          rule.timeUnit = c?.timeUnit || 'days'
        } else if (operator === 'between') {
          rule.value = value
          rule.value2 = value2
        } else if (operator === 'is_empty' || operator === 'is_not_empty') {
          // sem value
        } else {
          rule.value = value
          if (value2 != null) rule.value2 = value2
        }

        rules.push(rule)
      }
      pushGroup(fg?.operator, rules)
    }
  }

  // From `conditions` (normalizado/legado)
  if (Array.isArray((criteria as any).conditions)) {
    for (const cg of (criteria as any).conditions) {
      const rules: any[] = []
      for (const c of cg?.conditions || []) {
        const fieldRaw = String(c?.field || '')
        const operator = c?.operator
        const value = c?.value
        const value2 = c?.value2
        const field = fieldRaw === 'custom_field' && c?.customFieldKey ? String(c.customFieldKey) : fieldRaw

        const rule: any = { kind: 'property', field, op: operator }
        if (operator === 'between') {
          rule.value = value
          rule.value2 = value2
        } else if (operator === 'in_the_last' || operator === 'in_the_next') {
          rule.timeValue = (c as any)?.timeValue ?? value
          rule.timeUnit = (c as any)?.timeUnit || 'days'
        } else if (operator === 'is_empty' || operator === 'is_not_empty') {
        } else {
          rule.value = value
          if (value2 != null) rule.value2 = value2
        }
        rules.push(rule)
      }
      pushGroup(cg?.operator, rules)
    }
  }

  return { ...(criteria as any), groups }
}