     1	/**
     2	 * Parses database version string to comparable format
     3	 *
     4	 * @param {string} versionString - Raw version string from database
     5	 * @returns {Object|null} Parsed version with major, minor, patch
     6	 *
     7	 * @example
     8	 * // Input: MySQL version
     9	 * parseVersion("8.0.33");
    10	 * // Output: { major: 8, minor: 0, patch: 33 }
    11	 *
    12	 * @example
    13	 * // Input: PostgreSQL version with extra info
    14	 * parseVersion("14.5 (Ubuntu 14.5-1.pgdg20.04+1)");
    15	 * // Output: { major: 14, minor: 5, patch: 0 }
    16	 *
    17	 * @example
    18	 * // Input: MariaDB version
    19	 * parseVersion("10.6.12-MariaDB");
    20	 * // Output: { major: 10, minor: 6, patch: 12 }
    21	 */
    22	const parseVersion = (versionString) => {
    23	  // MySQL: "8.0.33"
    24	  // PostgreSQL: "14.5 (Ubuntu 14.5-1.pgdg20.04+1)"
    25	  // MariaDB: "10.6.12-MariaDB"
    26	  // SQLite: "3.35.5"
    27	
    28	  const match = versionString.match(/(\d+)\.(\d+)(?:\.(\d+))?/)
    29	  if (!match) return null
    30	
    31	  return {
    32	    major: parseInt(match[1]),
    33	    minor: parseInt(match[2]),
    34	    patch: match[3] ? parseInt(match[3]) : 0
    35	  }
    36	}
    37	
    38	/**
    39	 * Checks if the database supports window functions based on version
    40	 *
    41	 * @param {Object} knex - Knex instance
    42	 * @returns {Promise<boolean>} True if window functions are supported
    43	 *
    44	 * @example
    45	 * // Input: MySQL 8.0.33
    46	 * const supports = await supportsWindowFunctions(knex);
    47	 * // Output: true (MySQL 8.0+ supports window functions)
    48	 *
    49	 * @example
    50	 * // Input: MySQL 5.7.42
    51	 * const supports = await supportsWindowFunctions(knex);
    52	 * // Output: false (MySQL < 8.0 doesn't support window functions)
    53	 *
    54	 * @example
    55	 * // Input: PostgreSQL (any modern version)
    56	 * const supports = await supportsWindowFunctions(knex);
    57	 * // Output: true (PostgreSQL 8.4+ supports window functions)
    58	 *
    59	 * @example
    60	 * // Input: SQLite 3.24.0
    61	 * const supports = await supportsWindowFunctions(knex);
    62	 * // Output: false (SQLite needs 3.25.0+)
    63	 *
    64	 * @description
    65	 * Used by:
    66	 * - buildWindowedIncludeQuery to validate before using window functions
    67	 * - rest-api-knex-plugin to determine query strategies
    68	 *
    69	 * Purpose:
    70	 * - Prevents runtime errors by checking feature support
    71	 * - Enables fallback strategies for older databases
    72	 * - Provides clear error messages about version requirements
    73	 *
    74	 * Data flow:
    75	 * 1. Identifies database client type from Knex config
    76	 * 2. For version-dependent databases, queries version string
    77	 * 3. Parses version and compares against known thresholds
    78	 * 4. Returns boolean indicating support
    79	 * 5. Falls back to false if version can't be determined
    80	 *
    81	 * Version requirements:
    82	 * - PostgreSQL: 8.4+ (all modern versions)
    83	 * - MySQL: 8.0+
    84	 * - MariaDB: 10.2+
    85	 * - SQLite: 3.25.0+
    86	 * - SQL Server: 2005+ (all versions)
    87	 * - Oracle: Supported (long-standing feature)
    88	 */
    89	export const supportsWindowFunctions = async (knex) => {
    90	  try {
    91	    const client = knex.client.config.client
    92	
    93	    let versionString
    94	
    95	    switch (client) {
    96	      case 'pg':
    97	      case 'postgresql':
    98	        // PostgreSQL 8.4+ supports window functions (all modern versions)
    99	        return true
   100	
   101	      case 'mysql':
   102	      case 'mysql2':
   103	        try {
   104	          const row = await knex.first(knex.raw('VERSION() as version'))
   105	          const mysqlVersion = parseVersion(row.version)
   106	
   107	          return mysqlVersion && mysqlVersion.major >= 8
   108	        } catch (dbError) {
   109	          console.warn(`[supportsWindowFunctions] Failed to get MySQL version: ${dbError.message}`)
   110	          return false
   111	        }
   112	
   113	      case 'sqlite3':
   114	      case 'better-sqlite3':
   115	        try {
   116	          versionString = await knex.raw('SELECT sqlite_version() as version')
   117	          const sqliteVersion = versionString[0].version
   118	          // SQLite 3.25.0+ supports window functions
   119	          const parsed = parseVersion(sqliteVersion)
   120	          return parsed && (parsed.major > 3 || (parsed.major === 3 && parsed.minor >= 25))
   121	        } catch (dbError) {
   122	          console.warn(`[supportsWindowFunctions] Failed to get SQLite version: ${dbError.message}`)
   123	          return false
   124	        }
   125	
   126	      case 'mssql':
   127	        // SQL Server 2005+ supports window functions
   128	        return true
   129	
   130	      case 'oracledb':
   131	        // Oracle has supported window functions for a long time
   132	        return true
   133	
   134	      default:
   135	        // For MariaDB, we need to check if it's actually MariaDB or MySQL
   136	        if (client.includes('maria')) {
   137	          try {
   138	            versionString = await knex.raw('SELECT VERSION() as version')
   139	            const version = versionString[0].version.toLowerCase()
   140	            if (version.includes('mariadb')) {
   141	              const mariaVersion = parseVersion(version)
   142	              // MariaDB 10.2+ supports window functions
   143	              return mariaVersion && (mariaVersion.major > 10 ||
   144	                (mariaVersion.major === 10 && mariaVersion.minor >= 2))
   145	            }
   146	          } catch (dbError) {
   147	            console.warn(`[supportsWindowFunctions] Failed to get MariaDB version: ${dbError.message}`)
   148	            return false
   149	          }
   150	        }
   151	        return false
   152	    }
   153	  } catch (error) {
   154	    // Log error with context
   155	    console.error('[supportsWindowFunctions] Unexpected error checking database capabilities:', {
   156	      error: error.message,
   157	      stack: error.stack,
   158	      client: knex?.client?.config?.client || 'unknown'
   159	    })
   160	    // If we can't determine, assume no support for safety
   161	    return false
   162	  }
   163	}
   164	
   165	/**
   166	 * Gets database info for error messages and capability checks
   167	 *
   168	 * @param {Object} knex - Knex instance
   169	 * @returns {Promise<Object>} Database info with client and version
   170	 *
   171	 * @example
   172	 * // Input: MySQL connection
   173	 * const info = await getDatabaseInfo(knex);
   174	 * // Output: { client: 'MySQL', version: '8.0.33' }
   175	 *
   176	 * @example
   177	 * // Input: PostgreSQL connection
   178	 * const info = await getDatabaseInfo(knex);
   179	 * // Output: {
   180	 * //   client: 'PostgreSQL',
   181	 * //   version: 'PostgreSQL 14.5 (Ubuntu 14.5-1.pgdg20.04+1)'
   182	 * // }
   183	 *
   184	 * @example
   185	 * // Input: Connection error case
   186	 * const info = await getDatabaseInfo(knexWithError);
   187	 * // Output: {
   188	 * //   client: 'MySQL',
   189	 * //   version: 'unknown',
   190	 * //   error: 'Connection refused'
   191	 * // }
   192	 *
   193	 * @description
   194	 * Used by:
   195	 * - Error messages to show which database/version lacks a feature
   196	 * - Capability detection for feature flags
   197	 * - Debugging connection issues
   198	 *
   199	 * Purpose:
   200	 * - Provides human-readable database identification
   201	 * - Helps users understand version requirements
   202	 * - Enables detailed error messages
   203	 * - Gracefully handles query failures
   204	 *
   205	 * Data flow:
   206	 * 1. Identifies client type from Knex config
   207	 * 2. Runs version query specific to each database
   208	 * 3. Returns formatted info object
   209	 * 4. Includes error details if version query fails
   210	 * 5. Falls back to 'unknown' for unsupported databases
   211	 */
   212	export const getDatabaseInfo = async (knex) => {
   213	  try {
   214	    const client = knex.client.config.client
   215	
   216	    let versionString
   217	    switch (client) {
   218	      case 'mysql':
   219	      case 'mysql2':
   220	        try {
   221	          const row = await knex.first(knex.raw('VERSION() as version'))
   222	          return { client: 'MySQL', version: row.version }
   223	        } catch (dbError) {
   224	          console.warn(`[getDatabaseInfo] Failed to get MySQL version: ${dbError.message}`)
   225	          return { client: 'MySQL', version: 'unknown', error: dbError.message }
   226	        }
   227	
   228	      case 'sqlite3':
   229	      case 'better-sqlite3':
   230	        try {
   231	          versionString = await knex.raw('SELECT sqlite_version() as version')
   232	          return { client: 'SQLite', version: versionString[0].version }
   233	        } catch (dbError) {
   234	          console.warn(`[getDatabaseInfo] Failed to get SQLite version: ${dbError.message}`)
   235	          return { client: 'SQLite', version: 'unknown', error: dbError.message }
   236	        }
   237	
   238	      case 'pg':
   239	      case 'postgresql':
   240	        try {
   241	          versionString = await knex.raw('SELECT version() as version')
   242	          return { client: 'PostgreSQL', version: versionString.rows[0].version }
   243	        } catch (dbError) {
   244	          console.warn(`[getDatabaseInfo] Failed to get PostgreSQL version: ${dbError.message}`)
   245	          return { client: 'PostgreSQL', version: 'unknown', error: dbError.message }
   246	        }
   247	
   248	      default:
   249	        return { client, version: 'unknown' }
   250	    }
   251	  } catch (error) {
   252	    // Log unexpected errors
   253	    console.error('[getDatabaseInfo] Unexpected error getting database info:', {
   254	      error: error.message,
   255	      stack: error.stack,
   256	      client: knex?.client?.config?.client || 'unknown'
   257	    })
   258	
   259	    return {
   260	      client: knex?.client?.config?.client || 'unknown',
   261	      version: 'unknown',
   262	      error: error.message
   263	    }
   264	  }
   265	}
