/**
 * ClickHouseEventQueryExecutor
 *
 * Evaluates audience rules of kind='event' using ClickHouse instead of
 * Supabase + JavaScript aggregation.  This replaces the pattern of:
 *   - fetchAll() in pages of 500 rows
 *   - JS reduce for frequency/sum/avg/dedup
 *   - extra round-trips for first_time / last_time
 *
 * Property rules (kind='property') still run against Supabase contacts table
 * and are NOT handled here.
 *
 * Security: All user-controlled values (orgId, projId, eventName, datetime bounds,
 * attribute values) are passed via ClickHouse query_params to prevent SQL injection.
 * Only field expressions (JSON paths derived from known key formats) are inlined.
 */

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

// Maximum rows returned by fetch queries to prevent OOM
const MAX_FETCH_ROWS = 200_000

// ---------------------------------------------------------------------------
// Internal helpers
// ---------------------------------------------------------------------------

function safeStr(v: any): string {
  return v === null || v === undefined ? '' : String(v).trim()
}

/**
 * Resolves a ClickHouse result row (with `contact_id` + `reachy_id` fields) to
 * a single contact UUID, using the identity maps from V2AudienceEngine.
 */
function resolveContactId(
  row: { contact_id?: string | null; reachy_id?: string | null },
  reachyIdToContactId: Map<string, string>,
  allContactIds: Set<string>
): string | null {
  const cid = safeStr(row.contact_id)
  if (cid && allContactIds.has(cid)) return cid

  const rid = safeStr(row.reachy_id)
  if (rid) {
    const mapped = reachyIdToContactId.get(rid)
    if (mapped) return mapped
  }
  return null
}

/** Build a ClickHouse DateTime64-compatible string from a JS ISO date string */
function chDateTime(iso: string): string {
  return iso.replace('T', ' ').replace('Z', '').split('.')[0] ?? iso.replace('T', ' ').replace('Z', '')
}

// ---------------------------------------------------------------------------
// JSON field accessor helpers
// ---------------------------------------------------------------------------

type JsonFieldRef = { expr: string; kind: 'string' | 'float' }

const COLUMN_FIELDS = new Set([
  'current_url', 'domain', 'path', 'referrer', 'page_title',
  'utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content',
  'session_id', 'session_is_new',
])

// Allowlist of safe identifier characters for field names in JSON path expressions
const SAFE_FIELD_RE = /^[a-zA-Z0-9_]+$/

function isSafeFieldName(name: string): boolean {
  return SAFE_FIELD_RE.test(name)
}

function resolveEventFieldExpr(keyRaw: string): JsonFieldRef {
  const key = safeStr(keyRaw)
  if (!key) return { expr: "''", kind: 'string' }

  if (COLUMN_FIELDS.has(key)) return { expr: key, kind: 'string' }

  const extractStr = (base: string, path: string): string => {
    const parts = path.split('.').filter(Boolean)
    // Validate all parts are safe identifiers
    if (!parts.every(isSafeFieldName)) return "''"
    if (parts.length === 0) return `JSONExtractString(${base}, 'value')`
    if (parts.length === 1) return `JSONExtractString(${base}, '${parts[0]}')`
    let expr = base
    for (let i = 0; i < parts.length - 1; i++) {
      expr = `JSONExtractRaw(${expr}, '${parts[i]}')`
    }
    return `JSONExtractString(${expr}, '${parts[parts.length - 1]}')`
  }

  if (key.startsWith('event_data.')) return { expr: extractStr('event_data', key.replace(/^event_data\./, '')), kind: 'string' }
  if (key.startsWith('custom_data.')) return { expr: extractStr("JSONExtractRaw(event_data, 'custom_data')", key.replace(/^custom_data\./, '')), kind: 'string' }
  if (key.startsWith('url_data.')) return { expr: extractStr("JSONExtractRaw(event_data, 'url_data')", key.replace(/^url_data\./, '')), kind: 'string' }
  if (key.startsWith('utm_data.')) return { expr: extractStr("JSONExtractRaw(event_data, 'utm_data')", key.replace(/^utm_data\./, '')), kind: 'string' }
  if (key.startsWith('session_data.')) return { expr: extractStr("JSONExtractRaw(event_data, 'session_data')", key.replace(/^session_data\./, '')), kind: 'string' }
  if (key.includes('.')) return { expr: extractStr("JSONExtractRaw(event_data, 'custom_data')", key), kind: 'string' }

  if (!isSafeFieldName(key)) return { expr: "''", kind: 'string' }
  return { expr: `JSONExtractString(event_data, '${key}')`, kind: 'string' }
}

// ---------------------------------------------------------------------------
// Attribute WHERE clause builder (parameterized)
// ---------------------------------------------------------------------------

type ParamBag = Record<string, string | number>

export type TriggerEventRef = {
  event_id?: string
  event_name?: string
  event_timestamp?: string
}

function buildAttributeWhereClause(attributes: any[], params: ParamBag, paramIdx: { n: number }): string {
  if (!Array.isArray(attributes) || attributes.length === 0) return ''
  const parts: string[] = []
  for (const attr of attributes) {
    const key = safeStr(attr.key)
    if (!key) continue
    const op = safeStr(attr.op) || 'contains'
    const value = attr.value
    if (value === null || value === undefined) continue
    const { expr } = resolveEventFieldExpr(key)
    if (expr === "''") continue

    const pName = `attr_v_${paramIdx.n++}`
    const strValue = String(value)

    switch (op) {
      case 'equals':
        params[pName] = strValue
        parts.push(`${expr} = {${pName}:String}`)
        break
      case 'not_equals':
        params[pName] = strValue
        parts.push(`${expr} != {${pName}:String}`)
        break
      case 'starts_with':
        params[pName] = `${strValue}%`
        parts.push(`${expr} LIKE {${pName}:String}`)
        break
      case 'ends_with':
        params[pName] = `%${strValue}`
        parts.push(`${expr} LIKE {${pName}:String}`)
        break
      case 'contains':
      default:
        params[pName] = `%${strValue}%`
        parts.push(`${expr} LIKE {${pName}:String}`)
        break
    }
  }
  return parts.length > 0 ? parts.join(' AND ') : ''
}

// ---------------------------------------------------------------------------
// Time window builder (parameterized)
// ---------------------------------------------------------------------------

function buildTimeWhereClause(rule: any, cfg: any, params: ParamBag, paramIdx: { n: number }): string {
  const parts: string[] = []

  if (rule.time) {
    if (rule.time.unit && rule.time.value != null) {
      type TU = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
      const unit = String(rule.time.unit) as TU
      const value = Number(rule.time.value)
      const unitMs: Record<TU, number> = {
        minutes: 60000, hours: 3600000, days: 86400000, weeks: 604800000, months: 2592000000
      }
      const from = new Date(Date.now() - value * (unitMs[unit] ?? unitMs.days))
      const pName = `time_from_${paramIdx.n++}`
      params[pName] = chDateTime(from.toISOString())
      parts.push(`event_timestamp >= {${pName}:DateTime64(3)}`)
    } else {
      if (rule.time.from) {
        const pName = `time_from_${paramIdx.n++}`
        params[pName] = chDateTime(String(rule.time.from))
        parts.push(`event_timestamp >= {${pName}:DateTime64(3)}`)
      }
      if (rule.time.to) {
        const pName = `time_to_${paramIdx.n++}`
        params[pName] = chDateTime(String(rule.time.to))
        parts.push(`event_timestamp <= {${pName}:DateTime64(3)}`)
      }
    }
  } else if (cfg && cfg.timeFrame) {
    const tf = String(cfg.timeFrame).trim()
    type TU = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
    let unit: TU = '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 unitMs: Record<TU, number> = {
      minutes: 60000, hours: 3600000, days: 86400000, weeks: 604800000, months: 2592000000
    }
    const from = new Date(Date.now() - value * (unitMs[unit] ?? unitMs.days))
    const pName = `time_from_${paramIdx.n++}`
    params[pName] = chDateTime(from.toISOString())
    parts.push(`event_timestamp >= {${pName}:DateTime64(3)}`)
  }

  return parts.join(' AND ')
}

// ---------------------------------------------------------------------------
// Executor
// ---------------------------------------------------------------------------

export class ClickHouseEventQueryExecutor {
  constructor(
    private client: any,
    private logger: Logger = console
  ) {}

  /**
   * Main entry point — mirrors the return type of V2AudienceEngine.evalEventRule.
   * Returns the Set of contact UUIDs that match the rule.
   */
  async execute(
    rule: any,
    criteria: any,
    organizationId: string,
    projectId: string,
    projectTimezone: string,
    reachyIdToContactId: Map<string, string>,
    _contactIdToReachyId: Map<string, string>,
    allContactIds: Set<string>,
    // Quando informado (ex.: split de Journey com event_property), restringe a
    // avaliacao da regra de evento ao registro especifico que disparou o fluxo,
    // em vez de varrer TODO o historico do contato. E opcional: consumidores de
    // audiencia/segmento (RFM, live audiences) nao passam e mantem o comportamento
    // de varredura de historico, que e o correto para esses casos.
    triggerEvent?: TriggerEventRef
  ): Promise<Set<string>> {
    try {
      const cfg = criteria?.config || {}
      const typeId = String(criteria?.type || '')
      const effectiveEventName =
        cfg.eventType && String(cfg.eventType).trim() !== ''
          ? String(cfg.eventType)
          : String(rule.eventName)

      const ruleFiltersAll: any[] = Array.isArray(rule.filters) ? rule.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'
      })

      // Detect if in-memory post-processing is needed
      const needsInMemory =
        ruleFilters.some((f: any) => ['time_of_day', 'day_of_week', 'day_of_month', 'event_property'].includes(String(f?.type || '').trim())) ||
        !!(rule.interest && rule.interest.key)

      const hasPureFrequency = rule.frequency && rule.frequency.value != null
      const hasFirstOrLast = hasFirstTime || hasLastTime

      // So restringe ao evento de gatilho quando o nome do evento de gatilho casa
      // com a regra avaliada. Caso contrario (regra de outro evento, ou consumidor
      // de audiencia sem triggerEvent), mantem a varredura de historico - correto.
      const triggerNarrow: TriggerEventRef | undefined =
        triggerEvent &&
        typeof triggerEvent.event_name === 'string' &&
        triggerEvent.event_name.trim() === effectiveEventName.trim()
          ? triggerEvent
          : undefined

      // --- first_time / last_time: use GROUP BY + HAVING min/max ---
      if (hasFirstOrLast && !hasFirstTime && hasLastTime) {
        return this._executeLastTime(rule, cfg, organizationId, projectId, effectiveEventName, reachyIdToContactId, allContactIds, triggerNarrow)
      }
      if (hasFirstTime && !hasLastTime) {
        return this._executeFirstTime(rule, cfg, organizationId, projectId, effectiveEventName, reachyIdToContactId, allContactIds, triggerNarrow)
      }
      if (hasFirstTime && hasLastTime) {
        return this._executeFirstAndLastTime(rule, cfg, organizationId, projectId, effectiveEventName, reachyIdToContactId, allContactIds, triggerNarrow)
      }

      // --- Pure frequency with GROUP BY + HAVING (no in-memory post-processing) ---
      if (hasPureFrequency && !needsInMemory) {
        return this._executeFrequency(rule, cfg, organizationId, projectId, effectiveEventName, typeId, reachyIdToContactId, allContactIds, triggerNarrow)
      }

      // --- Fallback: fetch matching rows from ClickHouse, apply JS post-processing ---
      return this._executeFetchAndProcess(
        rule, cfg, organizationId, projectId, effectiveEventName, typeId,
        projectTimezone, ruleFilters, reachyIdToContactId, allContactIds, triggerNarrow
      )
    } catch (err) {
      this.logger.error('[ClickHouseEventQueryExecutor] execute error:', err)
      return new Set()
    }
  }

  // ---------------------------------------------------------------------------
  // first_time: contacts whose FIRST occurrence of the event is within the window
  // ---------------------------------------------------------------------------
  private async _executeFirstTime(
    rule: any, cfg: any, orgId: string, projId: string,
    eventName: string, reachyIdToContactId: Map<string, string>, allContactIds: Set<string>,
    triggerEvent?: TriggerEventRef
  ): Promise<Set<string>> {
    const params: ParamBag = {}
    const paramIdx = { n: 0 }
    const windowBounds = this._resolveWindowBounds(rule, cfg)
    let whereClause = this._baseWhere(orgId, projId, eventName, params, triggerEvent)

    const attrWhere = this._buildAttributeWhere(rule, params, paramIdx)
    if (attrWhere) whereClause += ` AND ${attrWhere}`

    let havingClause = ''
    if (windowBounds.start) {
      params['ft_window_start'] = chDateTime(windowBounds.start)
      havingClause = `min(event_timestamp) >= {ft_window_start:DateTime64(3)}`
    }
    if (windowBounds.end) {
      params['ft_window_end'] = chDateTime(windowBounds.end)
      whereClause += ` AND event_timestamp <= {ft_window_end:DateTime64(3)}`
    }

    const query = `
      SELECT contact_id, reachy_id
      FROM contact_events
      WHERE ${whereClause}
      GROUP BY contact_id, reachy_id
      ${havingClause ? `HAVING ${havingClause}` : ''}
    `
    return this._queryToContactIdSet(query, params, reachyIdToContactId, allContactIds)
  }

  // last_time: contacts whose LAST occurrence is within (or before) the window end
  private async _executeLastTime(
    rule: any, cfg: any, orgId: string, projId: string,
    eventName: string, reachyIdToContactId: Map<string, string>, allContactIds: Set<string>,
    triggerEvent?: TriggerEventRef
  ): Promise<Set<string>> {
    const params: ParamBag = {}
    const paramIdx = { n: 0 }
    const windowBounds = this._resolveWindowBounds(rule, cfg)
    let whereClause = this._baseWhere(orgId, projId, eventName, params, triggerEvent)

    const attrWhere = this._buildAttributeWhere(rule, params, paramIdx)
    if (attrWhere) whereClause += ` AND ${attrWhere}`
    if (windowBounds.start) {
      params['lt_window_start'] = chDateTime(windowBounds.start)
      whereClause += ` AND event_timestamp >= {lt_window_start:DateTime64(3)}`
    }

    let havingClause = ''
    if (windowBounds.end) {
      params['lt_window_end'] = chDateTime(windowBounds.end)
      havingClause = `max(event_timestamp) <= {lt_window_end:DateTime64(3)}`
    }

    const query = `
      SELECT contact_id, reachy_id
      FROM contact_events
      WHERE ${whereClause}
      GROUP BY contact_id, reachy_id
      ${havingClause ? `HAVING ${havingClause}` : ''}
    `
    return this._queryToContactIdSet(query, params, reachyIdToContactId, allContactIds)
  }

  private async _executeFirstAndLastTime(
    rule: any, cfg: any, orgId: string, projId: string,
    eventName: string, reachyIdToContactId: Map<string, string>, allContactIds: Set<string>,
    triggerEvent?: TriggerEventRef
  ): Promise<Set<string>> {
    const params: ParamBag = {}
    const windowBounds = this._resolveWindowBounds(rule, cfg)
    const whereClause = this._baseWhere(orgId, projId, eventName, params, triggerEvent)

    const havingParts: string[] = []
    if (windowBounds.start) {
      params['flt_window_start'] = chDateTime(windowBounds.start)
      havingParts.push(`min(event_timestamp) >= {flt_window_start:DateTime64(3)}`)
    }
    if (windowBounds.end) {
      params['flt_window_end'] = chDateTime(windowBounds.end)
      havingParts.push(`max(event_timestamp) <= {flt_window_end:DateTime64(3)}`)
    }

    const query = `
      SELECT contact_id, reachy_id
      FROM contact_events
      WHERE ${whereClause}
      GROUP BY contact_id, reachy_id
      ${havingParts.length > 0 ? `HAVING ${havingParts.join(' AND ')}` : ''}
    `
    return this._queryToContactIdSet(query, params, reachyIdToContactId, allContactIds)
  }

  // ---------------------------------------------------------------------------
  // Frequency: GROUP BY contact + HAVING count/sum/avg
  // ---------------------------------------------------------------------------
  private async _executeFrequency(
    rule: any, cfg: any, orgId: string, projId: string, eventName: string, typeId: string,
    reachyIdToContactId: Map<string, string>, allContactIds: Set<string>,
    triggerEvent?: TriggerEventRef
  ): Promise<Set<string>> {
    const params: ParamBag = {}
    const paramIdx = { n: 0 }
    const { op, value, value2, type: freqType = 'count', field = 'value' } = rule.frequency
    let whereClause = this._baseWhere(orgId, projId, eventName, params, triggerEvent)
    const timeWhere = buildTimeWhereClause(rule, cfg, params, paramIdx)
    if (timeWhere) whereClause += ` AND ${timeWhere}`

    const attrWhere = this._buildAttributeWhere(rule, params, paramIdx)
    if (attrWhere) whereClause += ` AND ${attrWhere}`

    const liveWhere = this._buildLivePresetWhere(cfg, typeId, params, paramIdx)
    if (liveWhere) whereClause += ` AND ${liveWhere}`

    let aggExpr = 'count()'
    if (freqType === 'sum') {
      const { expr } = resolveEventFieldExpr(field)
      aggExpr = `sum(toFloat64OrZero(${expr}))`
    } else if (freqType === 'avg') {
      const { expr } = resolveEventFieldExpr(field)
      aggExpr = `avg(toFloat64OrZero(${expr}))`
    }

    params['freq_val'] = Number(value)
    let having = ''
    if (op === 'between') {
      params['freq_val2'] = Number(value2)
      having = `agg_val >= {freq_val:Float64} AND agg_val <= {freq_val2:Float64}`
    } else {
      having = `agg_val ${op} {freq_val:Float64}`
    }

    const cleanQuery = `
      SELECT contact_id, reachy_id
      FROM (
        SELECT contact_id, reachy_id, ${aggExpr} AS agg_val
        FROM contact_events
        WHERE ${whereClause}
        GROUP BY contact_id, reachy_id
      )
      WHERE ${having}
    `
    return this._queryToContactIdSet(cleanQuery, params, reachyIdToContactId, allContactIds)
  }

  // ---------------------------------------------------------------------------
  // Generic fetch + optional JS post-processing
  // ---------------------------------------------------------------------------
  private async _executeFetchAndProcess(
    rule: any, cfg: any, orgId: string, projId: string, eventName: string, typeId: string,
    projectTimezone: string, ruleFilters: any[],
    reachyIdToContactId: Map<string, string>, allContactIds: Set<string>,
    triggerEvent?: TriggerEventRef
  ): Promise<Set<string>> {
    const params: ParamBag = {}
    const paramIdx = { n: 0 }
    let whereClause = this._baseWhere(orgId, projId, eventName, params, triggerEvent)
    const timeWhere = buildTimeWhereClause(rule, cfg, params, paramIdx)
    if (timeWhere) whereClause += ` AND ${timeWhere}`

    const attrWhere = this._buildAttributeWhere(rule, params, paramIdx)
    if (attrWhere) whereClause += ` AND ${attrWhere}`

    const liveWhere = this._buildLivePresetWhere(cfg, typeId, params, paramIdx)
    if (liveWhere) whereClause += ` AND ${liveWhere}`

    // Determine minimal SELECT fields
    const needsEventData = !!(
      (rule.interest && rule.interest.key) ||
      ruleFilters.some((f: any) => ['event_property'].includes(String(f?.type || '').trim())) ||
      (rule.frequency && rule.frequency.type && rule.frequency.type !== 'count') ||
      typeId === 'live-page-count'
    )
    const needsTimestamp = ruleFilters.some((f: any) =>
      ['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'

    const query = `
      SELECT ${selectFields}
      FROM contact_events
      WHERE ${whereClause}
      LIMIT ${MAX_FETCH_ROWS}
    `

    let rows: any[] = []
    try {
      const result = await this.client.query({ query, query_params: params, format: 'JSONEachRow' })
      rows = await result.json()
    } catch (err) {
      this.logger.error('[ClickHouseEventQueryExecutor] fetch error:', err)
      return new Set()
    }

    if (!rows || rows.length === 0) return new Set()

    // Parse event_data from JSON string if needed
    if (needsEventData) {
      for (const r of rows) {
        if (typeof r.event_data === 'string') {
          try { r.event_data = JSON.parse(r.event_data) } catch { r.event_data = {} }
        }
      }
    }

    // Apply in-memory filters (time_of_day, day_of_week, event_property)
    if (ruleFilters.length > 0) {
      rows = applyEventRuleFilters(rows, ruleFilters, { timezone: projectTimezone })
    }

    // User Interest
    const interest = rule.interest
    if (interest && interest.key) {
      return computeInterestContactIds(rows, interest, (row) => {
        return resolveContactId(row, reachyIdToContactId, allContactIds)
      })
    }

    // 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 cid = resolveContactId(row, reachyIdToContactId, allContactIds)
        if (cid) counts.set(cid, (counts.get(cid) || 0) + 1)
      }
      const res = new Set<string>()
      counts.forEach((cnt, id) => { if (cnt >= threshold) res.add(id) })
      return res
    }

    // Frequency with in-memory post-processing
    if (rule.frequency && rule.frequency.value != null) {
      const { op, value, value2, type: freqType = 'count', field = 'value' } = rule.frequency
      const counts = new Map<string, number>()
      const denoms = new Map<string, number>()
      for (const row of rows) {
        const cid = resolveContactId(row, reachyIdToContactId, allContactIds)
        if (!cid) continue
        if (freqType === 'count') {
          counts.set(cid, (counts.get(cid) || 0) + 1)
        } else {
          const raw = getEventPropertyValue(row.event_data, field) ?? row.event_data?.value ?? row.event_data?.amount
          const numVal = Number(raw)
          if (Number.isFinite(numVal)) {
            counts.set(cid, (counts.get(cid) || 0) + numVal)
            if (freqType === 'avg') denoms.set(cid, (denoms.get(cid) || 0) + 1)
          }
        }
      }
      const res = new Set<string>()
      counts.forEach((agg, id) => {
        let finalVal = agg
        if (freqType === 'avg') {
          const d = denoms.get(id) || 0
          finalVal = d > 0 ? agg / d : 0
        }
        if (op === 'between') {
          if (finalVal >= Number(value) && finalVal <= Number(value2)) res.add(id)
        } else if (
          (op === '>=' && finalVal >= value) || (op === '>' && finalVal > value) ||
          (op === '=' && finalVal === value) || (op === '!=' && finalVal !== value) ||
          (op === '<=' && finalVal <= value) || (op === '<' && finalVal < value)
        ) {
          res.add(id)
        }
      })
      return res
    }

    // Default: any match
    const res = new Set<string>()
    for (const row of rows) {
      const cid = resolveContactId(row, reachyIdToContactId, allContactIds)
      if (cid) res.add(cid)
    }
    return res
  }

  // ---------------------------------------------------------------------------
  // Query helpers — all parameterized
  // ---------------------------------------------------------------------------

  private _baseWhere(
    orgId: string,
    projId: string,
    eventName: string,
    params: ParamBag,
    triggerEvent?: TriggerEventRef
  ): string {
    params['p_org_id'] = orgId
    params['p_proj_id'] = projId
    params['p_event_name'] = eventName
    let where = `organization_id = {p_org_id:String} AND project_id = {p_proj_id:String} AND event_name = {p_event_name:String}`

    // Narrow para o evento que disparou a journey. Preferencia por id (exato);
    // fallback por event_timestamp. Valores via query_params (sem SQL injection).
    if (triggerEvent) {
      if (triggerEvent.event_id) {
        params['p_trigger_event_id'] = triggerEvent.event_id
        where += ` AND id = {p_trigger_event_id:String}`
      } else if (triggerEvent.event_timestamp) {
        params['p_trigger_event_ts'] = chDateTime(triggerEvent.event_timestamp)
        where += ` AND event_timestamp = {p_trigger_event_ts:DateTime64(3)}`
      }
    }

    return where
  }

  private _buildAttributeWhere(rule: any, params: ParamBag, paramIdx: { n: number }): string {
    let attributes = Array.isArray(rule.attributes) ? rule.attributes : (rule.attributes ? [rule.attributes] : [])
    const interestKey = rule.interest?.key ? String(rule.interest.key).trim() : ''
    if (interestKey) {
      attributes = attributes.filter((a: any) => String(a?.key || '').trim() !== interestKey)
    }
    return buildAttributeWhereClause(attributes, params, paramIdx)
  }

  private _buildLivePresetWhere(cfg: any, typeId: string, params: ParamBag, paramIdx: { n: number }): string {
    const parts: string[] = []
    if (typeId === 'live-page-visit') {
      const v = cfg.pageUrl
      const d = cfg.domain
      const ors: string[] = []
      if (v && String(v).trim()) {
        const pPath = `lp_path_${paramIdx.n++}`
        const pUrl = `lp_url_${paramIdx.n++}`
        params[pPath] = `%${String(v)}%`
        params[pUrl] = `%${String(v)}%`
        ors.push(`path LIKE {${pPath}:String}`)
        ors.push(`current_url LIKE {${pUrl}:String}`)
      }
      if (d && String(d).trim()) {
        const pDomain = `lp_domain_${paramIdx.n++}`
        params[pDomain] = `%${String(d)}%`
        ors.push(`domain LIKE {${pDomain}:String}`)
      }
      if (ors.length > 0) parts.push(`(${ors.join(' OR ')})`)
    }
    if (typeId === 'live-referrer') {
      const v = cfg.referrerUrl
      if (v && String(v).trim()) {
        const pRef = `lr_ref_${paramIdx.n++}`
        params[pRef] = `%${String(v)}%`
        parts.push(`referrer LIKE {${pRef}:String}`)
      }
      if (cfg.utm_source) {
        const p = `lr_src_${paramIdx.n++}`
        params[p] = `%${String(cfg.utm_source)}%`
        parts.push(`utm_source LIKE {${p}:String}`)
      }
      if (cfg.utm_medium) {
        const p = `lr_med_${paramIdx.n++}`
        params[p] = `%${String(cfg.utm_medium)}%`
        parts.push(`utm_medium LIKE {${p}:String}`)
      }
      if (cfg.utm_campaign) {
        const p = `lr_camp_${paramIdx.n++}`
        params[p] = `%${String(cfg.utm_campaign)}%`
        parts.push(`utm_campaign LIKE {${p}:String}`)
      }
    }
    return parts.join(' AND ')
  }

  private _resolveWindowBounds(rule: any, cfg: any): { start?: string; end?: string } {
    if (rule.time) {
      if (rule.time.unit && rule.time.value != null) {
        type TU = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
        const unit = String(rule.time.unit) as TU
        const value = Number(rule.time.value)
        const unitMs: Record<TU, number> = {
          minutes: 60000, hours: 3600000, days: 86400000, weeks: 604800000, months: 2592000000
        }
        const from = new Date(Date.now() - value * (unitMs[unit] ?? unitMs.days))
        return { start: from.toISOString(), end: new Date().toISOString() }
      }
      return { start: rule.time.from ? String(rule.time.from) : undefined, end: rule.time.to ? String(rule.time.to) : undefined }
    }
    if (cfg?.timeFrame) {
      const tf = String(cfg.timeFrame).trim()
      type TU = 'minutes' | 'hours' | 'days' | 'weeks' | 'months'
      let unit: TU = '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 unitMs: Record<TU, number> = {
        minutes: 60000, hours: 3600000, days: 86400000, weeks: 604800000, months: 2592000000
      }
      const from = new Date(Date.now() - value * (unitMs[unit] ?? unitMs.days))
      return { start: from.toISOString(), end: new Date().toISOString() }
    }
    return {}
  }

  private async _queryToContactIdSet(
    query: string,
    params: ParamBag,
    reachyIdToContactId: Map<string, string>,
    allContactIds: Set<string>
  ): Promise<Set<string>> {
    const result = await this.client.query({ query, query_params: params, format: 'JSONEachRow' })
    const rows: Array<{ contact_id: string | null; reachy_id: string | null }> = await result.json()
    const res = new Set<string>()
    for (const row of rows) {
      const cid = resolveContactId(row, reachyIdToContactId, allContactIds)
      if (cid) res.add(cid)
    }
    return res
  }
}

// ---------------------------------------------------------------------------
// In-memory helpers (copied from V2AudienceEngine to maintain parity)
// ---------------------------------------------------------------------------

function 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\./, ''))
  return getByPath(ed, key) ?? getByPath(ed?.custom_data, key)
}

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

function matchesEventPropertyFilter(raw: any, opRaw: any, expected: any): boolean {
  const op = String(opRaw ?? '').trim()
  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())
  const aNum = Number(raw); const bNum = Number(expected)
  if (Number.isFinite(aNum) && Number.isFinite(bNum)) {
    if (op === 'gt') return aNum > bNum
    if (op === 'gte') return aNum >= bNum
    if (op === 'lt') return aNum < bNum
    if (op === 'lte') return aNum <= bNum
  }
  return false
}

function parseTimeToMinutes(raw: any): number | null {
  const s = String(raw || '').trim()
  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 (ampm === 'AM') hh = hh === 12 ? 0 : hh
  if (ampm === 'PM') hh = hh === 12 ? 12 : hh + 12
  return hh * 60 + mm
}

function getLocalTimeParts(iso: any, tz: string) {
  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 = '', minStr = '', weekdayStr = '', 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
  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 }
}

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

function 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
      out = out.filter((row) => matchesEventPropertyFilter(getEventPropertyValue(row?.event_data, key), f?.op, f?.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 || startMin > endMin) { out = []; continue }
      out = out.filter((row) => { const p = getLocalTimeParts(row?.event_timestamp, tz); return p ? p.minutes >= startMin && p.minutes <= endMin : false })
    } else if (type === 'day_of_week') {
      if (!tz) continue
      const allowed = new Set<number>()
      for (const d of Array.isArray(f?.days) ? f.days : []) { const idx = parseWeekdayToIndex(d); if (idx != null) allowed.add(idx) }
      if (allowed.size === 0) continue
      out = out.filter((row) => { const p = getLocalTimeParts(row?.event_timestamp, tz); return p ? allowed.has(p.weekday) : false })
    } else if (type === 'day_of_month') {
      if (!tz) continue
      const allowed = new Set<number>()
      for (const d of Array.isArray(f?.days) ? f.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 p = getLocalTimeParts(row?.event_timestamp, tz); return p ? allowed.has(p.dayOfMonth) : false })
    }
  }
  return out
}

function 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())
}

function computeInterestContactIds(
  rows: any[],
  interest: { key: string; op: string; value: any; occurrenceType?: string; occurrencePercentage?: number },
  resolveId: (row: any) => string | null
): Set<string> {
  const { key, op, value, occurrenceType = 'predominantly', occurrencePercentage = 0 } = interest
  const threshold = Number.isFinite(occurrencePercentage) ? Math.max(0, Math.min(100, occurrencePercentage)) / 100 : 0
  const totals = new Map<string, number>()
  const matchCounts = new Map<string, number>()
  const valueCounts = new Map<string, Map<string, number>>()

  for (const row of rows) {
    const id = resolveId(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)
    if (!valueCounts.has(id)) valueCounts.set(id, new Map())
    const per = valueCounts.get(id)!
    per.set(strVal, (per.get(strVal) || 0) + 1)
    if (matchesInterest(rawVal, op, value)) 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 (occurrenceType === 'at_least') {
      if (match > 0 && match / total >= threshold) res.add(id)
      return
    }
    const per = valueCounts.get(id)
    if (!per) return
    let maxOther = 0
    per.forEach((cnt, valStr) => { if (!matchesInterest(valStr, op, value)) { if (cnt > maxOther) maxOther = cnt } })
    if (match > 0 && match >= maxOther) res.add(id)
  })
  return res
}