     1	import { analyzeRequiredIndexes, buildJoinChain } from './knex-cross-table-search.js'
     2	
     3	// Resolve operator with sensible defaults for fields declared in searchSchema.
     4	// - If filterOperator is provided, use it as-is
     5	// - If field type is string and no operator provided, default to 'like' (contains)
     6	// - Otherwise default to '='
     7	export function resolveSearchOperator (fieldDef) {
     8	  if (fieldDef && fieldDef.filterOperator) return String(fieldDef.filterOperator)
     9	  if (fieldDef && fieldDef.type === 'string') return 'like'
    10	  return '='
    11	}
    12	
    13	// Apply a comparison for a single field/operator/value onto a query builder.
    14	// Handles contains/startsWith/endsWith, IN, BETWEEN, =/== null semantics,
    15	// and uses ILIKE for case-insensitive matching on Postgres.
    16	export function applyWhereForOperator ({ builder, columnRef, operator, value, knex, or = false }) {
    17	  const method = or ? 'orWhere' : 'where'
    18	  const methodNull = or ? 'orWhereNull' : 'whereNull'
    19	  const methodIn = or ? 'orWhereIn' : 'whereIn'
    20	  const likeOp = 'like'
    21	
    22	  const op = typeof operator === 'string' ? operator.toLowerCase() : operator
    23	
    24	  // Normalize scalar from possibly array input
    25	  const firstVal = Array.isArray(value) ? value[0] : value
    26	
    27	  // Null handling for equality and string ops
    28	  if (firstVal === null || firstVal === undefined) {
    29	    if (op === 'like' || op === 'contains' || op === 'startswith' || op === 'endswith') {
    30	      builder[methodNull](columnRef)
    31	      return
    32	    }
    33	    if (op === '=' || op === '==') {
    34	      builder[methodNull](columnRef)
    35	      return
    36	    }
    37	  }
    38	
    39	  // Text search operators
    40	  if (op === 'like' || op === 'contains') {
    41	    builder[method](columnRef, likeOp, `%${String(firstVal)}%`)
    42	    return
    43	  }
    44	  if (op === 'startswith') {
    45	    builder[method](columnRef, likeOp, `${String(firstVal)}%`)
    46	    return
    47	  }
    48	  if (op === 'endswith') {
    49	    builder[method](columnRef, likeOp, `%${String(firstVal)}`)
    50	    return
    51	  }
    52	
    53	  // Array-based operators
    54	  if (op === 'in') {
    55	    const values = Array.isArray(value) ? value : [value]
    56	    builder[methodIn](columnRef, values)
    57	    return
    58	  }
    59	  if (op === 'between') {
    60	    const values = Array.isArray(value) ? value : [value]
    61	    if (values.length === 2) {
    62	      builder.whereBetween(columnRef, values)
    63	    } else if (values.length === 1) {
    64	      if (values[0] === null || values[0] === undefined) {
    65	        builder[methodNull](columnRef)
    66	      } else {
    67	        builder[method](columnRef, '=', values[0])
    68	      }
    69	    }
    70	    return
    71	  }
    72	
    73	  // Default
    74	  builder[method](columnRef, operator || '=', firstVal)
    75	}
    76	
    77	const createAdapterUtilities = (hookParams, { getStorageAdapter } = {}) => {
    78	  const context = hookParams.context || {}
    79	  const storageCache = new Map()
    80	
    81	  const fetchStorageAdapter = (scopeName) => {
    82	    if (!scopeName) return null
    83	    if (storageCache.has(scopeName)) return storageCache.get(scopeName)
    84	
    85	    let adapter = null
    86	    if (scopeName === context?.knexQuery?.scopeName) {
    87	      adapter = context.storageAdapter ||
    88	        context.knexQuery?.storageAdapter ||
    89	        (typeof getStorageAdapter === 'function' ? getStorageAdapter(scopeName) : null)
    90	      if (adapter && !context.storageAdapter) {
    91	        context.storageAdapter = adapter
    92	      }
    93	    } else {
    94	      adapter = typeof getStorageAdapter === 'function' ? getStorageAdapter(scopeName) : null
    95	    }
    96	
    97	    storageCache.set(scopeName, adapter)
    98	    return adapter
    99	  }
   100	
   101	  const defaultAliasForScope = (scopeName) => {
   102	    if (scopeName === context?.knexQuery?.scopeName) {
   103	      return context.knexQuery?.tableName || scopeName
   104	    }
   105	    return scopeName
   106	  }
   107	
   108	  const translateColumn = (scopeName, field, alias = defaultAliasForScope(scopeName)) => {
   109	    const adapter = fetchStorageAdapter(scopeName)
   110	    const translated = adapter?.translateColumn?.(field) ?? field
   111	    if (!alias) return translated
   112	    return `${alias}.${translated}`
   113	  }
   114	
   115	  const translateFilterValue = (scopeName, field, value) => {
   116	    const adapter = fetchStorageAdapter(scopeName)
   117	    if (!adapter?.translateFilterValue) return value
   118	    return adapter.translateFilterValue(field, value)
   119	  }
   120	
   121	  return {
   122	    fetchStorageAdapter,
   123	    defaultAliasForScope,
   124	    translateColumn,
   125	    translateFilterValue,
   126	  }
   127	}
   128	
   129	/**
   130	 * Processes filters that target polymorphic relationships where a single relationship can point to different types of resources
   131	 *
   132	 * @param {Object} hookParams - Hook parameters containing context
   133	 * @param {Object} dependencies - Dependencies injected by the plugin
   134	 *
   135	 * @example
   136	 * // Input: Search schema with polymorphic filter
   137	 * const searchSchema = {
   138	 *   commentable_title: {
   139	 *     type: 'string',
   140	 *     polymorphicField: 'commentable',  // Points to the polymorphic relationship
   141	 *     targetFields: {
   142	 *       posts: 'title',      // When commentable_type='posts', search posts.title
   143	 *       videos: 'title',     // When commentable_type='videos', search videos.title
   144	 *       articles: 'headline' // When commentable_type='articles', search articles.headline
   145	 *     },
   146	 *     filterOperator: 'like'
   147	 *   }
   148	 * };
   149	 *
   150	 * // Filter request: { commentable_title: 'JavaScript' }
   151	 *
   152	 * // Result: Adds conditional LEFT JOINs and WHERE conditions
   153	 * // SQL generated:
   154	 * // LEFT JOIN posts ON comments.commentable_type = 'posts' AND comments.commentable_id = posts.id
   155	 * // LEFT JOIN videos ON comments.commentable_type = 'videos' AND comments.commentable_id = videos.id
   156	 * // LEFT JOIN articles ON comments.commentable_type = 'articles' AND comments.commentable_id = articles.id
   157	 * // WHERE (
   158	 * //   (comments.commentable_type = 'posts' AND posts.title LIKE '%JavaScript%') OR
   159	 * //   (comments.commentable_type = 'videos' AND videos.title LIKE '%JavaScript%') OR
   160	 * //   (comments.commentable_type = 'articles' AND articles.headline LIKE '%JavaScript%')
   161	 * // )
   162	 *
   163	 * @example
   164	 * // Input: Complex polymorphic filter with cross-table paths
   165	 * const searchSchema = {
   166	 *   commentable_author: {
   167	 *     type: 'string',
   168	 *     polymorphicField: 'commentable',
   169	 *     targetFields: {
   170	 *       posts: 'author.name',     // Search post author's name
   171	 *       videos: 'creator.name'    // Search video creator's name
   172	 *     }
   173	 *   }
   174	 * };
   175	 *
   176	 * // Result: Creates nested JOINs for each polymorphic type
   177	 * // LEFT JOIN posts ON comments.commentable_type = 'posts' AND comments.commentable_id = posts.id
   178	 * // LEFT JOIN users AS posts_author ON posts.author_id = posts_author.id
   179	 * // LEFT JOIN videos ON comments.commentable_type = 'videos' AND comments.commentable_id = videos.id
   180	 * // LEFT JOIN users AS videos_creator ON videos.creator_id = videos_creator.id
   181	 *
   182	 * @description
   183	 * Used by:
   184	 * - rest-api-knex-plugin calls this hook first during query building
   185	 * - Must run before other filter hooks to establish JOINs
   186	 * - Applied when searchSchema contains polymorphicField definitions
   187	 *
   188	 * Purpose:
   189	 * - Enables filtering on polymorphic relationships without knowing the concrete type
   190	 * - Searches across multiple tables with a single filter parameter
   191	 * - Supports queries like "find all comments on content containing 'JavaScript'"
   192	 * - Uses conditional JOINs that only match when type field equals expected value
   193	 * - Maintains performance by leveraging indexed type/id columns
   194	 *
   195	 * Data flow:
   196	 * 1. Identifies filters with polymorphicField in searchSchema
   197	 * 2. For each polymorphic type, adds conditional LEFT JOIN
   198	 * 3. Builds OR conditions checking type field and target field together
   199	 * 4. Sets hasJoins flag for subsequent hooks
   200	 * 5. Returns modified query with polymorphic search capabilities
   201	 */
   202	export const polymorphicFiltersHook = async (hookParams, dependencies) => {
   203	  const { log, scopes, knex } = dependencies
   204	  const adapterUtils = createAdapterUtilities(hookParams, dependencies)
   205	
   206	  // Extract context
   207	  const scopeName = hookParams.context?.knexQuery?.scopeName
   208	  const filters = hookParams.context?.knexQuery?.filters
   209	  const query = hookParams.context?.knexQuery?.query
   210	  const db = hookParams.context?.knexQuery?.db || knex
   211	
   212	  const schemaInfo = scopes[scopeName].vars.schemaInfo
   213	  const tableAlias = adapterUtils.defaultAliasForScope(scopeName)
   214	
   215	  if (!filters) {
   216	    return
   217	  }
   218	
   219	  // Step 1: Identify polymorphic searches
   220	  const polymorphicSearches = new Map()
   221	  const polymorphicJoins = new Map()
   222	
   223	  for (const [filterKey, filterValue] of Object.entries(filters)) {
   224	    const fieldDef = schemaInfo.searchSchemaStructure[filterKey]
   225	
   226	    if (fieldDef?.polymorphicField && fieldDef?.targetFields && filterValue !== undefined) {
   227	      log.trace('[POLYMORPHIC-SEARCH] Found polymorphic search:', {
   228	        filterKey,
   229	        polymorphicField: fieldDef.polymorphicField
   230	      })
   231	
   232	      polymorphicSearches.set(filterKey, {
   233	        fieldDef,
   234	        filterValue,
   235	        polymorphicField: fieldDef.polymorphicField
   236	      })
   237	    }
   238	  }
   239	
   240	  if (polymorphicSearches.size === 0) {
   241	    return
   242	  }
   243	
   244	  // Step 2: Build polymorphic JOINs
   245	  log.trace('[POLYMORPHIC-SEARCH] Building JOINs for polymorphic searches')
   246	
   247	  for (const [filterKey, searchInfo] of polymorphicSearches) {
   248	    const { fieldDef, polymorphicField } = searchInfo
   249	
   250	    // Get the relationship definition
   251	    const relationships = scopes[scopeName].vars.schemaInfo.schemaRelationships
   252	    const polyRel = relationships[polymorphicField]
   253	
   254	    if (!polyRel?.belongsToPolymorphic) {
   255	      throw new Error(
   256	        `Polymorphic field '${polymorphicField}' not found in relationships for scope '${scopeName}'`
   257	      )
   258	    }
   259	
   260	    const { typeField, idField } = polyRel.belongsToPolymorphic
   261	
   262	    // Build JOINs for each target type
   263	    for (const [targetType, targetFieldPath] of Object.entries(fieldDef.targetFields)) {
   264	      const baseAlias = `${tableAlias}_${polymorphicField}_${targetType}`
   265	
   266	      // Skip if we already added this JOIN
   267	      if (!polymorphicJoins.has(baseAlias)) {
   268	        const targetSchema = scopes[targetType].vars.schemaInfo.schemaInstance
   269	        const targetTable = targetSchema?.tableName || targetType
   270	        const targetIdField = scopes[targetType].vars.schemaInfo.idProperty || 'id'
   271	
   272	        log.trace('[POLYMORPHIC-SEARCH] Adding conditional JOIN:', {
   273	          targetType,
   274	          alias: baseAlias
   275	        })
   276	
   277	        // Conditional JOIN - only matches when type is correct
   278	        query.leftJoin(`${targetTable} as ${baseAlias}`, function () {
   279	          const typeColumn = adapterUtils.translateColumn(scopeName, typeField, tableAlias)
   280	          const idColumn = adapterUtils.translateColumn(scopeName, idField, tableAlias)
   281	          const targetIdColumn = adapterUtils.translateColumn(targetType, targetIdField, baseAlias)
   282	
   283	          this.on(typeColumn, db.raw('?', [adapterUtils.translateFilterValue(scopeName, typeField, targetType)]))
   284	            .andOn(idColumn, targetIdColumn)
   285	        })
   286	
   287	        polymorphicJoins.set(baseAlias, {
   288	          targetType,
   289	          targetTable,
   290	          targetFieldPath
   291	        })
   292	        polymorphicJoins.get(baseAlias).baseAlias = baseAlias
   293	        polymorphicJoins.get(baseAlias).targetIdField = targetIdField
   294	
   295	        if (!polymorphicJoins.get(baseAlias).aliasScopeMap) {
   296	          polymorphicJoins.get(baseAlias).aliasScopeMap = new Map()
   297	        }
   298	        polymorphicJoins.get(baseAlias).aliasScopeMap.set(baseAlias, targetType)
   299	
   300	        // Handle cross-table paths
   301	        if (targetFieldPath.includes('.')) {
   302	          log.trace('[POLYMORPHIC-SEARCH] Building cross-table JOINs for path:', targetFieldPath)
   303	
   304	          const pathParts = targetFieldPath.split('.')
   305	          let currentAlias = baseAlias
   306	          let currentScope = targetType
   307	
   308	          // Build JOIN for each segment except the last
   309	          for (let i = 0; i < pathParts.length - 1; i++) {
   310	            const relationshipName = pathParts[i]
   311	
   312	            // Find the foreign key for this relationship
   313	            const currentSchema = scopes[currentScope].vars.schemaInfo.schemaInstance
   314	            let foreignKeyField = null
   315	            let nextScope = null
   316	
   317	            // Search schema for matching belongsTo
   318	            for (const [fieldName, fieldDef] of Object.entries(currentSchema.structure)) {
   319	              if (fieldDef.as === relationshipName && fieldDef.belongsTo) {
   320	                foreignKeyField = fieldName
   321	                nextScope = fieldDef.belongsTo
   322	                break
   323	              }
   324	            }
   325	
   326	            if (!foreignKeyField) {
   327	              // Check relationships for hasOne
   328	              const currentRelationships = scopes[currentScope].vars.schemaInfo.schemaRelationships
   329	              const rel = currentRelationships?.[relationshipName]
   330	              if (rel?.hasOne) {
   331	                // Handle hasOne - more complex
   332	                throw new Error(
   333	                  'Cross-table polymorphic search through hasOne relationships not yet supported'
   334	                )
   335	              }
   336	
   337	              throw new Error(
   338	                `Cannot resolve relationship '${relationshipName}' in path '${targetFieldPath}' for scope '${currentScope}'`
   339	              )
   340	            }
   341	
   342	            // Build next JOIN
   343	            const nextAlias = `${currentAlias}_${relationshipName}`
   344	            const nextSchema = scopes[nextScope].vars.schemaInfo.schemaInstance
   345	            const nextTable = nextSchema?.tableName || nextScope
   346	
   347	            log.trace('[POLYMORPHIC-SEARCH] Adding cross-table JOIN:', {
   348	              from: currentAlias,
   349	              to: nextAlias,
   350	              table: nextTable
   351	            })
   352	
   353	            const nextIdField = scopes[nextScope].vars.schemaInfo.idProperty || 'id'
   354	            const sourceColumn = adapterUtils.translateColumn(currentScope, foreignKeyField, currentAlias)
   355	            const targetColumn = adapterUtils.translateColumn(nextScope, nextIdField, nextAlias)
   356	
   357	            query.leftJoin(`${nextTable} as ${nextAlias}`, sourceColumn, targetColumn)
   358	
   359	            currentAlias = nextAlias
   360	            currentScope = nextScope
   361	
   362	            polymorphicJoins.get(baseAlias).aliasScopeMap.set(currentAlias, currentScope)
   363	          }
   364	        }
   365	      }
   366	    }
   367	  }
   368	
   369	  // Pre-fetch relationships for WHERE clause processing
   370	  const polymorphicRelationships = new Map()
   371	  const relationships = scopes[scopeName].vars.schemaInfo.schemaRelationships
   372	  for (const [filterKey, searchInfo] of polymorphicSearches) {
   373	    const { polymorphicField } = searchInfo
   374	    const polyRel = relationships[polymorphicField]
   375	    if (polyRel?.belongsToPolymorphic) {
   376	      polymorphicRelationships.set(filterKey, polyRel)
   377	    }
   378	  }
   379	
   380	  // Mark that we have JOINs for other hooks
   381	  hookParams.context.knexQuery.hasJoins = true
   382	
   383	  // Step 3: Apply WHERE conditions
   384	  query.where(function applyPolymorphicWhere () {
   385	    const applyComparison = (builder, scope, alias, field, operator, rawValue) => {
   386	      const columnRef = adapterUtils.translateColumn(scope, field, alias)
   387	      const normalizedValue = adapterUtils.translateFilterValue(scope, field, rawValue)
   388	      applyWhereForOperator({ builder, columnRef, operator, value: normalizedValue, knex })
   389	    }
   390	
   391	    for (const [filterKey] of Object.entries(filters)) {
   392	      if (!polymorphicSearches.has(filterKey)) continue
   393	
   394	      const searchInfo = polymorphicSearches.get(filterKey)
   395	      const polyRel = polymorphicRelationships.get(filterKey)
   396	      if (!polyRel) continue
   397	
   398	      const { typeField } = polyRel.belongsToPolymorphic
   399	
   400	      this.where(function applyTypeOrBranch () {
   401	        for (const [targetType, targetFieldPath] of Object.entries(searchInfo.fieldDef.targetFields)) {
   402	          this.orWhere(function applyTargetBranch () {
   403	            const typeColumnAlias = tableAlias
   404	            applyComparison(this, scopeName, typeColumnAlias, typeField, '=', targetType)
   405	
   406	            const baseAlias = `${tableAlias}_${searchInfo.polymorphicField}_${targetType}`
   407	            const joinMeta = polymorphicJoins.get(baseAlias)
   408	            const aliasScopeMap = joinMeta?.aliasScopeMap || new Map([[baseAlias, targetType]])
   409	
   410	            if (targetFieldPath.includes('.')) {
   411	              const pathParts = targetFieldPath.split('.')
   412	              const fieldName = pathParts[pathParts.length - 1]
   413	
   414	              let finalAlias = baseAlias
   415	              for (let i = 0; i < pathParts.length - 1; i++) {
   416	                finalAlias = `${finalAlias}_${pathParts[i]}`
   417	              }
   418	
   419	              const finalScope = aliasScopeMap.get(finalAlias) || targetType
   420	              const operator = resolveSearchOperator(searchInfo.fieldDef)
   421	              applyComparison(this, finalScope, finalAlias, fieldName, operator, searchInfo.filterValue)
   422	            } else {
   423	              const operator = resolveSearchOperator(searchInfo.fieldDef)
   424	              const finalScope = aliasScopeMap.get(baseAlias) || targetType
   425	              applyComparison(this, finalScope, baseAlias, targetFieldPath, operator, searchInfo.filterValue)
   426	            }
   427	          })
   428	        }
   429	      })
   430	    }
   431	  })
   432	}
   433	
   434	/**
   435	 * Processes filters that require JOINs to access fields in related tables using dot notation
   436	 *
   437	 * @param {Object} hookParams - Hook parameters containing context
   438	 * @param {Object} dependencies - Dependencies injected by the plugin
   439	 *
   440	 * @example
   441	 * // Input: Simple cross-table filter
   442	 * const searchSchema = {
   443	 *   author_name: {
   444	 *     type: 'string',
   445	 *     actualField: 'author.name',  // Dot notation indicates JOIN needed
   446	 *     filterOperator: 'like'
   447	 *   }
   448	 * };
   449	 *
   450	 * // Filter request: { author_name: 'Smith' }
   451	 * // Query before: SELECT * FROM articles
   452	 *
   453	 * // Result: Adds JOIN and qualified WHERE
   454	 * // Query after:
   455	 * // SELECT * FROM articles
   456	 * // LEFT JOIN users AS articles_author ON articles.author_id = articles_author.id
   457	 * // WHERE articles_author.name LIKE '%Smith%'
   458	 *
   459	 * @example
   460	 * // Input: Multi-field search across tables
   461	 * const searchSchema = {
   462	 *   search: {
   463	 *     type: 'string',
   464	 *     oneOf: [
   465	 *       'title',           // Local field
   466	 *       'content',         // Local field
   467	 *       'author.name',     // Requires JOIN to users
   468	 *       'category.title'   // Requires JOIN to categories
   469	 *     ],
   470	 *     filterOperator: 'like'
   471	 *   }
   472	 * };
   473	 *
   474	 * // Filter request: { search: 'JavaScript' }
   475	 *
   476	 * // Result: Multiple JOINs and OR conditions
   477	 * // SELECT DISTINCT * FROM articles
   478	 * // LEFT JOIN users AS articles_author ON articles.author_id = articles_author.id
   479	 * // LEFT JOIN categories AS articles_category ON articles.category_id = articles_category.id
   480	 * // WHERE (
   481	 * //   articles.title LIKE '%JavaScript%' OR
   482	 * //   articles.content LIKE '%JavaScript%' OR
   483	 * //   articles_author.name LIKE '%JavaScript%' OR
   484	 * //   articles_category.title LIKE '%JavaScript%'
   485	 * // )
   486	 *
   487	 * @example
   488	 * // Input: Deep nested relationships (3 levels)
   489	 * const searchSchema = {
   490	 *   company_country: {
   491	 *     type: 'string',
   492	 *     actualField: 'author.company.country.name'
   493	 *   }
   494	 * };
   495	 *
   496	 * // Filter request: { company_country: 'USA' }
   497	 *
   498	 * // Result: Chain of JOINs following relationships
   499	 * // SELECT * FROM articles
   500	 * // LEFT JOIN users AS articles_author ON articles.author_id = articles_author.id
   501	 * // LEFT JOIN companies AS articles_author_company ON articles_author.company_id = articles_author_company.id
   502	 * // LEFT JOIN countries AS articles_author_company_country ON articles_author_company.country_id = articles_author_company_country.id
   503	 * // WHERE articles_author_company_country.name = 'USA'
   504	 *
   505	 * @description
   506	 * Used by:
   507	 * - rest-api-knex-plugin calls this hook second during query building
   508	 * - Runs after polymorphicFiltersHook but before basicFiltersHook
   509	 * - Applied when filters contain dot notation in actualField or oneOf
   510	 *
   511	 * Purpose:
   512	 * - Enables filtering on related table fields without manual JOIN writing
   513	 * - Automatically builds JOIN chains from dot notation paths
   514	 * - Detects one-to-many relationships and adds DISTINCT to prevent duplicates
   515	 * - Creates unique aliases to avoid naming conflicts
   516	 * - Validates that target fields are indexed for performance
   517	 *
   518	 * Data flow:
   519	 * 1. Scans filters for dot notation in actualField or oneOf arrays
   520	 * 2. For each cross-table reference, builds JOIN chain via buildJoinChain
   521	 * 3. Applies JOINs to query with proper aliasing
   522	 * 4. Adds DISTINCT if any one-to-many JOINs detected
   523	 * 5. Applies WHERE conditions using qualified field names
   524	 * 6. Sets hasJoins flag for basicFiltersHook to use
   525	 */
   526	export const crossTableFiltersHook = async (hookParams, dependencies) => {
   527	  const { log, scopes, knex } = dependencies
   528	  const adapterUtils = createAdapterUtilities(hookParams, dependencies)
   529	
   530	  // Extract context
   531	  const scopeName = hookParams.context?.knexQuery?.scopeName
   532	  const filters = hookParams.context?.knexQuery?.filters
   533	  const query = hookParams.context?.knexQuery?.query
   534	  const db = hookParams.context?.knexQuery?.db || knex
   535	
   536	  const schemaInfo = scopes[scopeName].vars.schemaInfo
   537	  const tableName = schemaInfo.tableName
   538	  const tableAlias = adapterUtils.defaultAliasForScope(scopeName)
   539	  const aliasScopeMap = new Map()
   540	  aliasScopeMap.set(tableAlias, scopeName)
   541	
   542	  if (!filters) {
   543	    return
   544	  }
   545	
   546	  // Step 1: Analyze indexes
   547	  const requiredIndexes = analyzeRequiredIndexes(scopes, log, scopeName, schemaInfo)
   548	  if (requiredIndexes.length > 0) {
   549	    log.debug('Cross-table search requires indexes:', requiredIndexes)
   550	  }
   551	
   552	  // Step 2: Build JOIN maps
   553	  const joinMap = new Map()
   554	  const fieldPathMap = new Map()
   555	  let hasCrossTableFilters = false
   556	
   557	  for (const [filterKey, fieldDef] of Object.entries(schemaInfo.searchSchemaStructure)) {
   558	    if (filters[filterKey] === undefined) continue
   559	
   560	    // Skip polymorphic filters
   561	    if (fieldDef.polymorphicField) continue
   562	
   563	    // Check actualField for cross-table references
   564	    if (fieldDef.actualField?.includes('.')) {
   565	      hasCrossTableFilters = true
   566	      log.trace('[JOIN-DETECTION] Cross-table actualField found', { filterKey, actualField: fieldDef.actualField, scopeName })
   567	      const joinInfo = await buildJoinChain(scopes, log, scopeName, fieldDef.actualField)
   568	      if (!joinMap.has(joinInfo.joinAlias)) {
   569	        joinMap.set(joinInfo.joinAlias, joinInfo)
   570	      }
   571	      fieldPathMap.set(fieldDef.actualField, `${joinInfo.joinAlias}.${joinInfo.targetField}`)
   572	    }
   573	
   574	    // Check oneOf for cross-table references
   575	    if (fieldDef.oneOf && Array.isArray(fieldDef.oneOf)) {
   576	      for (const field of fieldDef.oneOf) {
   577	        if (field.includes('.')) {
   578	          hasCrossTableFilters = true
   579	          log.trace('[JOIN-DETECTION] Cross-table oneOf field found', { filterKey, field, scopeName })
   580	          const joinInfo = await buildJoinChain(scopes, log, scopeName, field)
   581	          if (!joinMap.has(joinInfo.joinAlias)) {
   582	            joinMap.set(joinInfo.joinAlias, joinInfo)
   583	          }
   584	          fieldPathMap.set(field, `${joinInfo.joinAlias}.${joinInfo.targetField}`)
   585	        }
   586	      }
   587	    }
   588	  }
   589	
   590	  if (!hasCrossTableFilters) {
   591	    return
   592	  }
   593	
   594	  joinMap.forEach((joinInfo) => {
   595	    if (joinInfo.joinAlias) {
   596	      aliasScopeMap.set(joinInfo.joinAlias, joinInfo.targetScopeName || joinInfo.targetTableName)
   597	    }
   598	    if (joinInfo.isMultiLevel && Array.isArray(joinInfo.joinChain)) {
   599	      joinInfo.joinChain.forEach((join) => {
   600	        if (join.joinAlias) {
   601	          aliasScopeMap.set(join.joinAlias, join.targetScopeName || join.targetTableName)
   602	        }
   603	      })
   604	    }
   605	  })
   606	
   607	  const translateQualifiedColumn = (qualified) => {
   608	    const trimmed = qualified.trim()
   609	    if (!trimmed.includes('.')) {
   610	      return adapterUtils.translateColumn(scopeName, trimmed, tableAlias)
   611	    }
   612	
   613	    const [alias, ...fieldParts] = trimmed.split('.')
   614	    const field = fieldParts.join('.')
   615	    const scopeForAlias = aliasScopeMap.get(alias) || scopeName
   616	    return adapterUtils.translateColumn(scopeForAlias, field, alias)
   617	  }
   618	
   619	  const resolveFieldColumn = (field) => {
   620	    if (field.includes('.')) {
   621	      const qualified = fieldPathMap.get(field) || field
   622	      return translateQualifiedColumn(qualified)
   623	    }
   624	    return adapterUtils.translateColumn(scopeName, field, tableAlias)
   625	  }
   626	
   627	  const normalizeFieldValue = (field, value) => {
   628	    if (field.includes('.')) {
   629	      const qualified = fieldPathMap.get(field) || field
   630	      const [alias, ...rest] = qualified.split('.')
   631	      const fieldName = rest.join('.')
   632	      const valueScope = aliasScopeMap.get(alias) || scopeName
   633	      if (Array.isArray(value)) {
   634	        return value.map((entry) => adapterUtils.translateFilterValue(valueScope, fieldName, entry))
   635	      }
   636	      return adapterUtils.translateFilterValue(valueScope, fieldName, value)
   637	    }
   638	    if (Array.isArray(value)) {
   639	      return value.map((entry) => adapterUtils.translateFilterValue(scopeName, field, entry))
   640	    }
   641	    return adapterUtils.translateFilterValue(scopeName, field, value)
   642	  }
   643	
   644	  // Step 3: Apply JOINs
   645	  const appliedJoins = new Set()
   646	
   647	  const applyPolymorphicJoin = (join) => {
   648	    query.leftJoin(`${join.targetTableName} as ${join.joinAlias}`, function () {
   649	      const parts = join.joinCondition.split(' AND ')
   650	      const [typeCondition, idCondition] = parts
   651	
   652	      const typeMatch = typeCondition.match(/(.+?)\s*=\s*'(.+?)'/)
   653	      if (typeMatch) {
   654	        const typeColumnToken = typeMatch[1].trim()
   655	        const typeAlias = typeColumnToken.split('.')[0]
   656	        const typeField = typeColumnToken.split('.').slice(1).join('.')
   657	        const typeScope = aliasScopeMap.get(typeAlias) || scopeName
   658	        const translatedColumn = translateQualifiedColumn(typeColumnToken)
   659	        const translatedValue = adapterUtils.translateFilterValue(typeScope, typeField, typeMatch[2])
   660	        this.on(translatedColumn, db.raw('?', [translatedValue]))
   661	      }
   662	
   663	      const idMatch = idCondition.match(/(.+?)\s*=\s*(.+)/)
   664	      if (idMatch) {
   665	        const leftToken = idMatch[1].trim()
   666	        const rightToken = idMatch[2].trim()
   667	        const leftColumn = translateQualifiedColumn(leftToken)
   668	        const rightColumn = translateQualifiedColumn(rightToken)
   669	        this.andOn(leftColumn, rightColumn)
   670	      }
   671	    })
   672	  }
   673	
   674	  const applyStandardJoin = (join) => {
   675	    const [leftSide, rightSide] = join.joinCondition.split(' = ')
   676	    const translatedLeft = translateQualifiedColumn(leftSide.trim())
   677	    const translatedRight = translateQualifiedColumn(rightSide.trim())
   678	    query.leftJoin(`${join.targetTableName} as ${join.joinAlias}`, function () {
   679	      this.on(translatedLeft, translatedRight)
   680	    })
   681	  }
   682	
   683	  const processJoin = (join) => {
   684	    const joinKey = `${join.joinAlias}:${join.joinCondition}`
   685	    if (appliedJoins.has(joinKey)) return
   686	
   687	    if (join.isPolymorphic && join.joinCondition.includes(' AND ')) {
   688	      applyPolymorphicJoin(join)
   689	    } else {
   690	      applyStandardJoin(join)
   691	    }
   692	
   693	    appliedJoins.add(joinKey)
   694	  }
   695	
   696	  joinMap.forEach((joinInfo) => {
   697	    if (joinInfo.isMultiLevel && Array.isArray(joinInfo.joinChain)) {
   698	      joinInfo.joinChain.forEach(processJoin)
   699	    } else {
   700	      processJoin(joinInfo)
   701	    }
   702	  })
   703	
   704	  // Step 4: Handle DISTINCT
   705	  let hasOneToManyJoins = false
   706	  joinMap.forEach((joinInfo) => {
   707	    if (joinInfo.isOneToMany) {
   708	      hasOneToManyJoins = true
   709	    } else if (joinInfo.isMultiLevel && joinInfo.joinChain) {
   710	      joinInfo.joinChain.forEach(join => {
   711	        if (join.isOneToMany) hasOneToManyJoins = true
   712	      })
   713	    }
   714	  })
   715	
   716	  if (hasOneToManyJoins) {
   717	    log.trace('[DISTINCT] Adding DISTINCT to query due to one-to-many JOINs')
   718	    query.distinct()
   719	  }
   720	
   721	  // Store state for basic filters hook
   722	  hookParams.context.knexQuery.hasJoins = true
   723	
   724	  // Step 5: Apply WHERE conditions for cross-table filters
   725	  query.where(function () {
   726	    for (const [filterKey, filterValue] of Object.entries(filters)) {
   727	      const fieldDef = schemaInfo.searchSchemaStructure[filterKey]
   728	      if (!fieldDef) continue
   729	
   730	      // Skip non-cross-table and polymorphic filters
   731	      if (fieldDef.polymorphicField) continue
   732	      if (!fieldDef.actualField?.includes('.') &&
   733	          !fieldDef.oneOf?.some(f => f.includes('.'))) {
   734	        continue
   735	      }
   736	
   737	      // Process cross-table filters
   738	      switch (true) {
   739	        case fieldDef.oneOf && Array.isArray(fieldDef.oneOf): {
   740	          const operator = resolveSearchOperator(fieldDef)
   741	
   742	          let searchTerms = [filterValue]
   743	          if (fieldDef.splitBy && typeof filterValue === 'string') {
   744	            searchTerms = filterValue.split(fieldDef.splitBy).filter(term => term.trim())
   745	          } else if (Array.isArray(filterValue)) {
   746	            searchTerms = filterValue
   747	          }
   748	
   749	          const applyTermComparison = (builder, method, field, raw) => {
   750	            const columnRef = resolveFieldColumn(field)
   751	            const normalized = normalizeFieldValue(field, raw)
   752	            applyWhereForOperator({
   753	              builder,
   754	              columnRef,
   755	              operator,
   756	              value: normalized,
   757	              knex,
   758	              or: method === 'or',
   759	            })
   760	          }
   761	
   762	          this.where(function () {
   763	            if (fieldDef.matchAll && searchTerms.length > 1) {
   764	              searchTerms.forEach(term => {
   765	                this.andWhere(function () {
   766	                  fieldDef.oneOf.forEach((field, index) => {
   767	                    const method = index === 0 ? 'and' : 'or'
   768	                    applyTermComparison(this, method, field, term)
   769	                  })
   770	                })
   771	              })
   772	            } else {
   773	              fieldDef.oneOf.forEach((field, index) => {
   774	                const method = index === 0 ? 'and' : 'or'
   775	                applyTermComparison(this, method, field, filterValue)
   776	              })
   777	            }
   778	          })
   779	          break
   780	        }
   781	
   782	        case fieldDef.applyFilter && typeof fieldDef.applyFilter === 'function':
   783	          fieldDef.applyFilter.call(this, this, filterValue)
   784	          break
   785	
   786	        default:
   787	          const targetField = fieldDef.actualField || filterKey
   788	          const columnRef = resolveFieldColumn(targetField)
   789	          const operator = resolveSearchOperator(fieldDef)
   790	          const normalized = normalizeFieldValue(targetField, filterValue)
   791	          applyWhereForOperator({ builder: this, columnRef, operator, value: normalized, knex })
   792	          break
   793	        }
   794	    }
   795	  })
   796	}
   797	
   798	/**
   799	 * Processes filters that apply directly to fields on the main table
   800	 *
   801	 * @param {Object} hookParams - Hook parameters containing context
   802	 * @param {Object} dependencies - Dependencies injected by the plugin
   803	 *
   804	 * @example
   805	 * // Input: Basic equality filter
   806	 * const searchSchema = {
   807	 *   status: {
   808	 *     type: 'string',
   809	 *     filterOperator: '='  // Default is '=' if not specified
   810	 *   }
   811	 * };
   812	 *
   813	 * // Filter request: { status: 'published' }
   814	 * // Query before: SELECT * FROM articles
   815	 *
   816	 * // Result: Adds qualified WHERE clause
   817	 * // Query after: SELECT * FROM articles WHERE articles.status = 'published'
   818	 *
   819	 * @example
   820	 * // Input: LIKE filter for partial text matching
   821	 * const searchSchema = {
   822	 *   title: {
   823	 *     type: 'string',
   824	 *     filterOperator: 'like'
   825	 *   },
   826	 *   content: {
   827	 *     type: 'string',
   828	 *     filterOperator: 'like'
   829	 *   }
   830	 * };
   831	 *
   832	 * // Filter request: { title: 'JavaScript', content: 'async' }
   833	 *
   834	 * // Result: Multiple LIKE conditions
   835	 * // WHERE articles.title LIKE '%JavaScript%'
   836	 * // AND articles.content LIKE '%async%'
   837	 *
   838	 * @example
   839	 * // Input: Multi-field OR search with oneOf
   840	 * const searchSchema = {
   841	 *   search: {
   842	 *     type: 'string',
   843	 *     oneOf: ['title', 'content', 'summary'],
   844	 *     filterOperator: 'like',
   845	 *     splitBy: ' ',        // Split search terms by space
   846	 *     matchAll: true       // All terms must match somewhere
   847	 *   }
   848	 * };
   849	 *
   850	 * // Filter request: { search: 'REST API' }
   851	 *
   852	 * // Result: Each term must match in at least one field
   853	 * // WHERE (
   854	 * //   (articles.title LIKE '%REST%' OR articles.content LIKE '%REST%' OR articles.summary LIKE '%REST%')
   855	 * //   AND
   856	 * //   (articles.title LIKE '%API%' OR articles.content LIKE '%API%' OR articles.summary LIKE '%API%')
   857	 * // )
   858	 *
   859	 * @example
   860	 * // Input: Advanced operators - IN and BETWEEN
   861	 * const searchSchema = {
   862	 *   category_id: {
   863	 *     type: 'array',
   864	 *     filterOperator: 'in'
   865	 *   },
   866	 *   price: {
   867	 *     type: 'array',
   868	 *     filterOperator: 'between'
   869	 *   },
   870	 *   tags: {
   871	 *     type: 'array',
   872	 *     filterOperator: 'in'
   873	 *   }
   874	 * };
   875	 *
   876	 * // Filter request: {
   877	 * //   category_id: [1, 2, 3],
   878	 * //   price: [10.00, 99.99],
   879	 * //   tags: ['javascript', 'nodejs']
   880	 * // }
   881	 *
   882	 * // Result: IN and BETWEEN clauses
   883	 * // WHERE articles.category_id IN (1, 2, 3)
   884	 * // AND articles.price BETWEEN 10.00 AND 99.99
   885	 * // AND articles.tags IN ('javascript', 'nodejs')
   886	 *
   887	 * @example
   888	 * // Input: Custom filter function
   889	 * const searchSchema = {
   890	 *   has_comments: {
   891	 *     type: 'boolean',
   892	 *     applyFilter: function(query, value) {
   893	 *       if (value === true) {
   894	 *         query.whereExists(function() {
   895	 *           this.select('id')
   896	 *               .from('comments')
   897	 *               .whereRaw('comments.article_id = articles.id');
   898	 *         });
   899	 *       } else if (value === false) {
   900	 *         query.whereNotExists(function() {
   901	 *           this.select('id')
   902	 *               .from('comments')
   903	 *               .whereRaw('comments.article_id = articles.id');
   904	 *         });
   905	 *       }
   906	 *     }
   907	 *   }
   908	 * };
   909	 *
   910	 * // Filter request: { has_comments: true }
   911	 *
   912	 * // Result: Subquery to check existence
   913	 * // WHERE EXISTS (
   914	 * //   SELECT id FROM comments WHERE comments.article_id = articles.id
   915	 * // )
   916	 *
   917	 * @description
   918	 * Used by:
   919	 * - rest-api-knex-plugin calls this hook last during query building
   920	 * - Runs after all JOINs have been established by previous hooks
   921	 * - Handles all non-cross-table, non-polymorphic filters
   922	 *
   923	 * Purpose:
   924	 * - Implements standard SQL filtering operations safely via Knex
   925	 * - Always qualifies field names with table name to prevent ambiguity
   926	 * - Supports various operators: =, like, in, between, and custom
   927	 * - Enables multi-field OR searches with optional term splitting
   928	 * - Handles null values appropriately (using whereNull)
   929	 * - Allows custom filter logic via applyFilter functions
   930	 *
   931	 * Data flow:
   932	 * 1. Skips filters already handled by polymorphic/cross-table hooks
   933	 * 2. Qualifies all field names with table name (e.g., articles.title)
   934	 * 3. Applies appropriate WHERE clause based on filterOperator
   935	 * 4. For oneOf filters, creates OR conditions across specified fields
   936	 * 5. For custom filters, delegates to applyFilter function
   937	 * 6. Returns query with all basic filters applied
   938	 */
   939	export const basicFiltersHook = async (hookParams, dependencies) => {
   940	  const { log, scopes, knex } = dependencies
   941	  const adapterUtils = createAdapterUtilities(hookParams, dependencies)
   942	
   943	  // Extract context
   944	  const scopeName = hookParams.context?.knexQuery?.scopeName
   945	  const filters = hookParams.context?.knexQuery?.filters
   946	  const query = hookParams.context?.knexQuery?.query
   947	  const db = hookParams.context?.knexQuery?.db || knex
   948	
   949	  const schemaInfo = scopes[scopeName].vars.schemaInfo
   950	  const tableName = schemaInfo.tableName
   951	  const tableAlias = adapterUtils.defaultAliasForScope(scopeName)
   952	
   953	  log.trace('[DEBUG basicFiltersHook] Called with:', {
   954	    scopeName,
   955	    hasFilters: !!filters,
   956	    filters,
   957	    searchSchemaKeys: Object.keys(schemaInfo.searchSchemaStructure || {}),
   958	    tableName
   959	  })
   960	
   961	  if (!filters) {
   962	    log.trace('[DEBUG basicFiltersHook] Returning early - no filters')
   963	    return
   964	  }
   965	
   966	  // Check if we have any JOINs applied (to know if we need to qualify fields)
   967	  // Instead of relying on hasJoins flag, always qualify fields for safety
   968	  const qualifyField = (field) => adapterUtils.translateColumn(scopeName, field, tableAlias)
   969	  const normalizeValue = (field, value) => {
   970	    if (Array.isArray(value)) {
   971	      return value.map((entry) => adapterUtils.translateFilterValue(scopeName, field, entry))
   972	    }
   973	    return adapterUtils.translateFilterValue(scopeName, field, value)
   974	  }
   975	
   976	  // Main WHERE group
   977	  query.where(function () {
   978	    for (const [filterKey, filterValue] of Object.entries(filters)) {
   979	      const fieldDef = schemaInfo.searchSchemaStructure[filterKey]
   980	      if (!fieldDef) {
   981	        log.trace(`[DEBUG basicFiltersHook] No field definition for filter key: ${filterKey}`)
   982	        continue
   983	      }
   984	      log.trace(`[DEBUG basicFiltersHook] Processing filter: ${filterKey} = ${filterValue}, fieldDef:`, fieldDef)
   985	
   986	      // Skip if this is a cross-table filter
   987	      if (fieldDef.actualField?.includes('.') ||
   988	          fieldDef.oneOf?.some(f => f.includes('.')) ||
   989	          fieldDef.polymorphicField) {
   990	        log.trace(`[DEBUG basicFiltersHook] Skipping filter ${filterKey} - is cross-table or polymorphic`)
   991	        continue
   992	      }
   993	
   994	      // Process basic filters
   995	      switch (true) {
   996	        case fieldDef.oneOf && Array.isArray(fieldDef.oneOf): {
   997	          // Multi-field OR search
   998	          const operator = resolveSearchOperator(fieldDef)
   999	
  1000	          // Handle split search terms
  1001	          let searchTerms = [filterValue]
  1002	          if (fieldDef.splitBy && typeof filterValue === 'string') {
  1003	            searchTerms = filterValue.split(fieldDef.splitBy).filter(term => term.trim())
  1004	          }
  1005	
  1006	          this.where(function () {
  1007	            if (fieldDef.matchAll && searchTerms.length > 1) {
  1008	              searchTerms.forEach(term => {
  1009	                const normalizedTerm = normalizeValue(fieldDef.oneOf[0], term)
  1010	                this.andWhere(function () {
  1011	                  fieldDef.oneOf.forEach((field, index) => {
  1012	                    const columnRef = qualifyField(field)
  1013	                    const perFieldValue = Array.isArray(normalizedTerm)
  1014	                      ? normalizeValue(field, term)
  1015	                      : normalizeValue(field, term)
  1016	                    applyWhereForOperator({
  1017	                      builder: this,
  1018	                      columnRef,
  1019	                      operator,
  1020	                      value: perFieldValue,
  1021	                      knex,
  1022	                      or: index !== 0,
  1023	                    })
  1024	                  })
  1025	                })
  1026	              })
  1027	            } else {
  1028	              fieldDef.oneOf.forEach((field, index) => {
  1029	                const columnRef = qualifyField(field)
  1030	                const normalizedValue = normalizeValue(field, filterValue)
  1031	                applyWhereForOperator({
  1032	                  builder: this,
  1033	                  columnRef,
  1034	                  operator,
  1035	                  value: normalizedValue,
  1036	                  knex,
  1037	                  or: index !== 0,
  1038	                })
  1039	              })
  1040	            }
  1041	          })
  1042	          break
  1043	        }
  1044	
  1045	        case fieldDef.applyFilter && typeof fieldDef.applyFilter === 'function':
  1046	          // Custom filter
  1047	          fieldDef.applyFilter.call(this, this, filterValue)
  1048	          break
  1049	
  1050	        default:
  1051	          // Standard filtering
  1052	          const actualField = fieldDef.actualField || filterKey
  1053	          // Always qualify field names
  1054	          const dbField = qualifyField(actualField)
  1055	
  1056	          const operator = resolveSearchOperator(fieldDef)
  1057	          const normalized = normalizeValue(actualField, filterValue)
  1058	          log.trace(`[DEBUG basicFiltersHook] Applying filter: ${dbField} ${operator} ${filterValue}`)
  1059	          applyWhereForOperator({ builder: this, columnRef: dbField, operator, value: normalized, knex })
  1060	          break
  1061	        }
  1062	    }
  1063	  })
  1064	}
