     1	import { RestApiResourceError } from '../../../../lib/rest-api-errors.js'
     2	import { ROW_NUMBER_KEY, DEFAULT_QUERY_LIMIT, DEFAULT_MAX_QUERY_LIMIT, DEFAULT_MAX_INCLUDE_LIMIT } from '../querying-writing/knex-constants.js'
     3	
     4	/**
     5	 * Builds a window function query for limited includes per parent record
     6	 *
     7	 * @param {Object} knex - Knex instance
     8	 * @param {string} tableName - Target table name
     9	 * @param {string} foreignKey - Foreign key field
    10	 * @param {Array} parentIds - Parent record IDs
    11	 * @param {Array} fieldsToSelect - Fields to select
    12	 * @param {Object} includeConfig - Include configuration (limit, orderBy)
    13	 * @param {Object} capabilities - Database capabilities
    14	 * @param {Object} scopeVars - Scope variables for defaults and limits
    15	 * @returns {Object} Knex query
    16	 *
    17	 * @example
    18	 * // Input: Load max 3 comments per article, ordered by newest first
    19	 * const query = buildWindowedIncludeQuery(
    20	 *   knex,
    21	 *   'comments',
    22	 *   'article_id',
    23	 *   [1, 2, 3],  // Article IDs
    24	 *   ['id', 'text', 'created_at'],
    25	 *   { limit: 3, orderBy: ['-created_at'] },
    26	 *   { windowFunctions: true },
    27	 *   { queryDefaultLimit: 10 }
    28	 * );
    29	 *
    30	 * // Generated SQL:
    31	 * // WITH _windowed AS (
    32	 * //   SELECT id, text, created_at, article_id,
    33	 * //          ROW_NUMBER() OVER (
    34	 * //            PARTITION BY article_id
    35	 * //            ORDER BY created_at DESC
    36	 * //          ) as __$jsonrestapi_rn$__
    37	 * //   FROM comments
    38	 * //   WHERE article_id IN (1, 2, 3)
    39	 * // )
    40	 * // SELECT * FROM _windowed
    41	 * // WHERE __$jsonrestapi_rn$__ <= 3
    42	 *
    43	 * // Result: Each article gets max 3 comments, not 3 total
    44	 *
    45	 * @example
    46	 * // Input: Database doesn't support window functions
    47	 * const query = buildWindowedIncludeQuery(
    48	 *   knex,
    49	 *   'comments',
    50	 *   'article_id',
    51	 *   [1, 2, 3],
    52	 *   '*',
    53	 *   { limit: 5 },
    54	 *   { windowFunctions: false, dbInfo: { client: 'mysql', version: '5.7' } }
    55	 * );
    56	 *
    57	 * // Throws RestApiResourceError:
    58	 * // "Include limits require window function support. Your database (mysql 5.7)
    59	 * //  does not support this feature. Window functions are supported in:
    60	 * //  PostgreSQL 8.4+, MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+, SQL Server 2005+"
    61	 *
    62	 * @description
    63	 * Used by:
    64	 * - loadHasMany when strategy: 'window' and database supports window functions
    65	 * - Enables per-parent limits for one-to-many relationships
    66	 *
    67	 * Purpose:
    68	 * - Solves the "N+1 limit" problem where you want X records per parent
    69	 * - Without window functions, LIMIT 10 gives 10 total across all parents
    70	 * - With window functions, each parent gets up to 10 related records
    71	 * - Critical for consistent API responses with includes
    72	 *
    73	 * Data flow:
    74	 * 1. Creates subquery with ROW_NUMBER() partitioned by foreign key
    75	 * 2. Numbers rows within each partition based on orderBy
    76	 * 3. Outer query filters to keep only rows within limit
    77	 * 4. Returns query ready for execution
    78	 * 5. Caller removes the ROW_NUMBER column from results
    79	 */
    80	export const buildWindowedIncludeQuery = (
    81	  knex,
    82	  tableName,
    83	  foreignKey,
    84	  parentIds,
    85	  fieldsToSelect,
    86	  includeConfig,
    87	  capabilities,
    88	  scopeVars = {}
    89	) => {
    90	  const { orderBy = [] } = includeConfig
    91	
    92	  // Apply defaults for limit
    93	  const effectiveLimit = includeConfig.limit ?? scopeVars.queryDefaultLimit ?? DEFAULT_QUERY_LIMIT
    94	
    95	  // Validate against max
    96	  if (scopeVars.queryMaxLimit && effectiveLimit > scopeVars.queryMaxLimit) {
    97	    throw new RestApiResourceError({
    98	      title: 'Include Limit Exceeds Maximum',
    99	      detail: `Requested include limit (${effectiveLimit}) exceeds queryMaxLimit (${scopeVars.queryMaxLimit})`,
   100	      status: 400
   101	    })
   102	  }
   103	
   104	  // Check if window functions are supported
   105	  if (!capabilities.windowFunctions) {
   106	    const { dbInfo } = capabilities
   107	    throw new RestApiResourceError(
   108	      `Include limits require window function support. Your database (${dbInfo.client} ${dbInfo.version}) does not support this feature. ` +
   109	      'Window functions are supported in: PostgreSQL 8.4+, MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+, SQL Server 2005+',
   110	      {
   111	        subtype: 'unsupported_operation',
   112	        database: dbInfo.client,
   113	        version: dbInfo.version,
   114	        requiredFeature: 'window_functions'
   115	      }
   116	    )
   117	  }
   118	
   119	  // Build the window function query
   120	  // This creates a subquery that partitions by the foreign key and numbers rows
   121	  const subquery = knex(tableName)
   122	    .select('*')
   123	    .select(
   124	      knex.raw(
   125	        'ROW_NUMBER() OVER (PARTITION BY ?? ORDER BY ' +
   126	        buildOrderByClause(orderBy) +
   127	        ') as ' + ROW_NUMBER_KEY,
   128	        [foreignKey]
   129	      )
   130	    )
   131	    .whereIn(foreignKey, parentIds)
   132	
   133	  // Apply field selection if specified
   134	  if (fieldsToSelect !== '*' && Array.isArray(fieldsToSelect)) {
   135	    // Include the foreign key and row number in selection
   136	    const fieldsWithFK = [...new Set([...fieldsToSelect, foreignKey, ROW_NUMBER_KEY])]
   137	    subquery.select(fieldsWithFK)
   138	  }
   139	
   140	  // Wrap in outer query to filter by row number
   141	  const query = knex
   142	    .select('*')
   143	    .from(subquery.as('_windowed'))
   144	    .where(ROW_NUMBER_KEY, '<=', effectiveLimit)
   145	
   146	  return query
   147	}
   148	
   149	/**
   150	 * Builds ORDER BY clause from array of sort fields
   151	 *
   152	 * @param {Array<string>} orderBy - Array of field names, prefix with '-' for DESC
   153	 * @param {string} tablePrefix - Optional table name to prefix fields
   154	 * @returns {string} SQL ORDER BY clause
   155	 *
   156	 * @example
   157	 * // Input: Simple ascending sort
   158	 * const clause = buildOrderByClause(['name', 'created_at']);
   159	 * // Output: "name ASC, created_at ASC"
   160	 *
   161	 * @example
   162	 * // Input: Mixed ascending/descending with '-' prefix
   163	 * const clause = buildOrderByClause(['name', '-created_at', 'status']);
   164	 * // Output: "name ASC, created_at DESC, status ASC"
   165	 *
   166	 * @example
   167	 * // Input: With table prefix for joins
   168	 * const clause = buildOrderByClause(['-updated_at', 'title'], 'articles');
   169	 * // Output: "articles.updated_at DESC, articles.title ASC"
   170	 *
   171	 * @example
   172	 * // Input: Empty array (default to id)
   173	 * const clause = buildOrderByClause([]);
   174	 * // Output: "id ASC"
   175	 *
   176	 * @description
   177	 * Used by:
   178	 * - buildWindowedIncludeQuery for PARTITION BY ordering
   179	 * - applyStandardIncludeConfig for regular ORDER BY
   180	 * - Query builders that need consistent sort syntax
   181	 *
   182	 * Purpose:
   183	 * - Converts API sort syntax (with '-' prefix) to SQL ORDER BY
   184	 * - Handles table prefixing for queries with joins
   185	 * - Provides default ordering by id when none specified
   186	 * - Ensures consistent sort behavior across the API
   187	 *
   188	 * Data flow:
   189	 * 1. Receives array of sort fields from API parameters
   190	 * 2. Detects DESC sorts by '-' prefix
   191	 * 3. Optionally prefixes with table name
   192	 * 4. Joins into SQL-compatible ORDER BY clause
   193	 */
   194	export const buildOrderByClause = (orderBy, tablePrefix) => {
   195	  if (!orderBy || orderBy.length === 0) {
   196	    const defaultField = tablePrefix ? `${tablePrefix}.id` : 'id'
   197	    return `${defaultField} ASC` // Default ordering
   198	  }
   199	
   200	  return orderBy.map(field => {
   201	    const isDesc = field.startsWith('-')
   202	    const fieldName = isDesc ? field.substring(1) : field
   203	    const qualifiedField = tablePrefix ? `${tablePrefix}.${fieldName}` : fieldName
   204	    return `${qualifiedField} ${isDesc ? 'DESC' : 'ASC'}`
   205	  }).join(', ')
   206	}
   207	
   208	/**
   209	 * Applies standard (non-windowed) include configuration to a query
   210	 *
   211	 * @param {Object} query - Knex query builder instance
   212	 * @param {Object} includeConfig - Include configuration object
   213	 * @param {Object} scopeVars - Scope variables with defaults and limits
   214	 * @param {Object} log - Logger instance
   215	 * @returns {Object} Modified query builder
   216	 *
   217	 * @example
   218	 * // Input: Basic include with limit and ordering
   219	 * let query = knex('comments').whereIn('article_id', [1, 2, 3]);
   220	 * query = applyStandardIncludeConfig(
   221	 *   query,
   222	 *   { limit: 20, orderBy: ['-created_at', 'id'] },
   223	 *   { queryDefaultLimit: 100, queryMaxLimit: 1000 },
   224	 *   logger
   225	 * );
   226	 *
   227	 * // Result: Query modified with:
   228	 * // ORDER BY created_at DESC, id ASC
   229	 * // LIMIT 20
   230	 *
   231	 * @example
   232	 * // Input: No explicit limit, uses defaults
   233	 * let query = knex('tags');
   234	 * query = applyStandardIncludeConfig(
   235	 *   query,
   236	 *   { orderBy: ['name'] },  // No limit specified
   237	 *   { queryDefaultLimit: 50, queryMaxLimit: 500 },
   238	 *   logger
   239	 * );
   240	 *
   241	 * // Result: Uses default limit
   242	 * // ORDER BY name ASC
   243	 * // LIMIT 50
   244	 * // Log: "Using default limit"
   245	 *
   246	 * @example
   247	 * // Input: Limit exceeds maximum
   248	 * let query = knex('reviews');
   249	 * query = applyStandardIncludeConfig(
   250	 *   query,
   251	 *   { limit: 5000 },  // Exceeds max
   252	 *   { queryMaxLimit: 1000, maxIncludeLimit: 500 },
   253	 *   logger
   254	 * );
   255	 *
   256	 * // Result: Clamped to effective maximum
   257	 * // LIMIT 500 (min of maxIncludeLimit and queryMaxLimit)
   258	 *
   259	 * @example
   260	 * // Input: Explicitly disable limit
   261	 * let query = knex('categories');
   262	 * query = applyStandardIncludeConfig(
   263	 *   query,
   264	 *   { limit: null },  // Explicitly no limit
   265	 *   { queryDefaultLimit: 100 },
   266	 *   logger
   267	 * );
   268	 *
   269	 * // Result: No LIMIT clause added
   270	 * // Log: "No limit applied to include query (explicitly disabled)"
   271	 *
   272	 * @description
   273	 * Used by:
   274	 * - loadHasMany when window functions not available or not requested
   275	 * - loadReversePolymorphic for standard relationship queries
   276	 * - Any include loader that needs consistent limit/order handling
   277	 *
   278	 * Purpose:
   279	 * - Provides fallback when window functions unavailable
   280	 * - Applies consistent ordering based on API sort syntax
   281	 * - Enforces limit hierarchy: explicit > default > max
   282	 * - Respects both queryMaxLimit and maxIncludeLimit
   283	 * - Logs decisions for debugging
   284	 *
   285	 * Data flow:
   286	 * 1. Applies ORDER BY for each field in orderBy array
   287	 * 2. Determines effective limit from explicit/default/max values
   288	 * 3. Clamps limit to maximum allowed values
   289	 * 4. Adds LIMIT clause unless explicitly disabled
   290	 * 5. Logs the reasoning for the applied limit
   291	 */
   292	export const applyStandardIncludeConfig = (query, includeConfig, scopeVars, log) => {
   293	  const { orderBy = [] } = includeConfig
   294	
   295	  // Apply ordering
   296	  orderBy.forEach(field => {
   297	    const desc = field.startsWith('-')
   298	    const column = desc ? field.substring(1) : field
   299	    query = query.orderBy(column, desc ? 'desc' : 'asc')
   300	  })
   301	
   302	  // Apply limit with defaults
   303	  const requestedLimit = includeConfig.limit
   304	  const defaultLimit = scopeVars.queryDefaultLimit ?? DEFAULT_QUERY_LIMIT
   305	  const limit = requestedLimit ?? defaultLimit
   306	
   307	  // Allow explicit null/false to mean no limit
   308	  if (limit !== null && limit !== false) {
   309	    const maxInclude = scopeVars.maxIncludeLimit || DEFAULT_MAX_INCLUDE_LIMIT
   310	    const maxQuery = scopeVars.queryMaxLimit || DEFAULT_MAX_QUERY_LIMIT
   311	    const effectiveMax = Math.min(maxInclude, maxQuery)
   312	    const effectiveLimit = Math.min(limit, effectiveMax)
   313	
   314	    query = query.limit(effectiveLimit)
   315	
   316	    log.debug('Applied include limit:', {
   317	      requested: requestedLimit,
   318	      default: defaultLimit,
   319	      effective: effectiveLimit,
   320	      maxAllowed: effectiveMax,
   321	      note: requestedLimit !== undefined ? 'Using explicit limit' : 'Using default limit'
   322	    })
   323	  } else {
   324	    log.debug('No limit applied to include query (explicitly disabled)')
   325	  }
   326	
   327	  return query
   328	}
