     1	import { RestApiResourceError } from '../../../../lib/rest-api-errors.js'
     2	import { transformSimplifiedToJsonApi } from '../querying-writing/simplified-helpers.js'
     3	
     4	/**
     5	 * Updates many-to-many relationships intelligently by synchronizing pivot table records
     6	 *
     7	 * @param {Object} scope - The scope object (not used directly, for consistency)
     8	 * @param {Object} deps - Dependencies object
     9	 * @returns {Promise<void>}
    10	 *
    11	 * @example
    12	 * // Input: Article currently has tags [1, 2], want to change to [1, 3]
    13	 * const deps = {
    14	 *   api,
    15	 *   context: {
    16	 *     resourceId: '100',
    17	 *     relDef: {
    18	 *       through: 'article_tags',     // Pivot table
    19	 *       foreignKey: 'article_id',    // Points to article
    20	 *       otherKey: 'tag_id'          // Points to tag
    21	 *     },
    22	 *     relData: [
    23	 *       { type: 'tags', id: '1' },  // Keep this
    24	 *       { type: 'tags', id: '3' }   // Add this
    25	 *     ],
    26	 *     transaction: trx
    27	 *   }
    28	 * };
    29	 *
    30	 * // Before: article_tags table
    31	 * // article_id | tag_id | created_at
    32	 * // 100        | 1      | 2024-01-01
    33	 * // 100        | 2      | 2024-01-02
    34	 *
    35	 * await updateManyToManyRelationship(null, deps);
    36	 *
    37	 * // After: article_tags table
    38	 * // article_id | tag_id | created_at
    39	 * // 100        | 1      | 2024-01-01  (preserved!)
    40	 * // 100        | 3      | 2024-12-01  (new)
    41	 * // Tag 2 was deleted, Tag 1 kept its metadata
    42	 *
    43	 * @example
    44	 * // Input: Pivot table has extra fields to preserve
    45	 * // article_tags has: article_id, tag_id, display_order, featured
    46	 *
    47	 * // Current data:
    48	 * // article_id | tag_id | display_order | featured
    49	 * // 100        | 1      | 1            | true
    50	 * // 100        | 2      | 2            | false
    51	 *
    52	 * const deps = {
    53	 *   context: {
    54	 *     resourceId: '100',
    55	 *     relData: [
    56	 *       { type: 'tags', id: '1' },  // Keep tag 1
    57	 *       { type: 'tags', id: '5' }   // Add tag 5
    58	 *     ]
    59	 *   }
    60	 * };
    61	 *
    62	 * await updateManyToManyRelationship(null, deps);
    63	 *
    64	 * // Result:
    65	 * // article_id | tag_id | display_order | featured
    66	 * // 100        | 1      | 1            | true      (preserved!)
    67	 * // 100        | 5      | NULL         | NULL      (new with defaults)
    68	 *
    69	 * @example
    70	 * // Input: Clear all relationships
    71	 * const deps = {
    72	 *   context: {
    73	 *     resourceId: '100',
    74	 *     relData: []  // Empty array means remove all
    75	 *   }
    76	 * };
    77	 *
    78	 * await updateManyToManyRelationship(null, deps);
    79	 * // All article_tags records for article 100 are deleted
    80	 *
    81	 * @description
    82	 * Used by:
    83	 * - relationship-processor.js calls this for many-to-many updates
    84	 * - Used in PATCH operations to sync relationships
    85	 * - Also used in PUT operations (replaces deleteExistingPivotRecords pattern)
    86	 *
    87	 * Purpose:
    88	 * - Intelligently syncs pivot table to match desired state
    89	 * - Preserves existing pivot records that should remain (with their metadata)
    90	 * - Only deletes records that should be removed
    91	 * - Only creates records that are new
    92	 * - Much better than delete-all-then-recreate pattern
    93	 *
    94	 * Data flow:
    95	 * 1. Queries existing pivot records for the resource
    96	 * 2. Compares existing IDs with desired IDs
    97	 * 3. Calculates which to delete and which to add
    98	 * 4. Validates new related resources exist (optional)
    99	 * 5. Performs bulk delete for removed relationships
   100	 * 6. Performs bulk insert for new relationships
   101	 * 7. Records that exist in both are untouched (metadata preserved)
   102	 */
   103	export const updateManyToManyRelationship = async (scope, deps) => {
   104	  // Extract values from deps
   105	  const { api, context } = deps
   106	  const { resourceId, relDef, relData, transaction: trx } = context
   107	
   108	  // Get the knex instance from the pivot scope
   109	  const pivotScope = api.resources[relDef.through]
   110	  if (!pivotScope) {
   111	    throw new Error(`Pivot table resource '${relDef.through}' not found`)
   112	  }
   113	
   114	  // Get the actual database table name (might be different from scope name)
   115	  const tableName = pivotScope.vars.schemaInfo.tableName || relDef.through
   116	
   117	  // Get existing pivot records directly from database
   118	  const existingRecords = await trx(tableName)
   119	    .where(relDef.foreignKey, resourceId)
   120	    .select(relDef.otherKey)
   121	
   122	  // Create sets for efficient comparison
   123	  const existingIds = new Set(existingRecords.map(r => String(r[relDef.otherKey])))
   124	  const newIds = new Set(relData.map(r => String(r.id)))
   125	
   126	  // Determine what to delete and add
   127	  const toDelete = [...existingIds].filter(id => !newIds.has(id))
   128	  const toAdd = [...newIds].filter(id => !existingIds.has(id))
   129	
   130	  // Validate related resources exist if needed (do this before any changes)
   131	  if (relDef.validateExists !== false && toAdd.length > 0) {
   132	    for (const relatedId of toAdd) {
   133	      const related = relData.find(r => String(r.id) === relatedId)
   134	      try {
   135	        await api.resources[related.type].get({
   136	          id: related.id,
   137	          transaction: trx
   138	        })
   139	      } catch (error) {
   140	        throw new RestApiResourceError(
   141	          `Related ${related.type} with id ${related.id} not found`,
   142	          {
   143	            subtype: 'not_found',
   144	            resourceType: related.type,
   145	            resourceId: related.id
   146	          }
   147	        )
   148	      }
   149	    }
   150	  }
   151	
   152	  // Bulk delete records that should be removed
   153	  if (toDelete.length > 0) {
   154	    await trx(tableName)
   155	      .where(relDef.foreignKey, resourceId)
   156	      .whereIn(relDef.otherKey, toDelete)
   157	      .delete()
   158	  }
   159	
   160	  // Bulk insert new records
   161	  if (toAdd.length > 0) {
   162	    const recordsToInsert = toAdd.map(relatedId => ({
   163	      [relDef.foreignKey]: resourceId,
   164	      [relDef.otherKey]: relatedId
   165	    }))
   166	
   167	    await trx(tableName).insert(recordsToInsert)
   168	  }
   169	
   170	  // Records that exist in both are automatically preserved with their pivot data
   171	}
   172	
   173	// Note: deleteExistingPivotRecords has been removed in favor of using
   174	// updateManyToManyRelationship for all sync operations (including PUT).
   175	// This aligns with industry standards where ORMs use intelligent sync
   176	// rather than delete-all-then-recreate patterns.
   177	
   178	/**
   179	 * Creates new pivot table records for many-to-many relationships
   180	 *
   181	 * @param {Object} api - The API instance with access to resources
   182	 * @param {string|number} resourceId - The ID of the primary resource
   183	 * @param {Object} relDef - The relationship definition
   184	 * @param {Array} relData - Array of related resources to link
   185	 * @param {Object} trx - Database transaction object
   186	 * @returns {Promise<void>}
   187	 *
   188	 * @example
   189	 * // Input: Create article-tag relationships
   190	 * const relDef = {
   191	 *   through: 'article_tags',
   192	 *   foreignKey: 'article_id',
   193	 *   otherKey: 'tag_id'
   194	 * };
   195	 * const relData = [
   196	 *   { type: 'tags', id: '10' },
   197	 *   { type: 'tags', id: '20' },
   198	 *   { type: 'tags', id: '30' }
   199	 * ];
   200	 *
   201	 * await createPivotRecords(api, '100', relDef, relData, trx);
   202	 *
   203	 * // Result: 3 new records in article_tags table
   204	 * // article_id | tag_id
   205	 * // 100        | 10
   206	 * // 100        | 20
   207	 * // 100        | 30
   208	 *
   209	 * @example
   210	 * // Input: Validation ensures related resources exist
   211	 * const relData = [
   212	 *   { type: 'tags', id: '999' }  // Non-existent tag
   213	 * ];
   214	 *
   215	 * try {
   216	 *   await createPivotRecords(api, '100', relDef, relData, trx);
   217	 * } catch (error) {
   218	 *   console.log(error.message);
   219	 *   // "Related tags with id 999 not found"
   220	 *   // Transaction rolled back, no records created
   221	 * }
   222	 *
   223	 * @example
   224	 * // Input: Skip validation for performance
   225	 * const relDef = {
   226	 *   through: 'user_permissions',
   227	 *   foreignKey: 'user_id',
   228	 *   otherKey: 'permission_id',
   229	 *   validateExists: false  // Skip GET requests
   230	 * };
   231	 *
   232	 * // With 100 permissions, saves 100 GET requests
   233	 * await createPivotRecords(api, userId, relDef, permissions, trx);
   234	 *
   235	 * // Risk: Could create orphaned relationships if permissions don't exist
   236	 * // Benefit: Much faster for bulk operations when you trust the data
   237	 *
   238	 * @description
   239	 * Used by:
   240	 * - relationship-processor.js for POST operations
   241	 * - updateManyToManyRelationship internally for new relationships
   242	 * - Any code that needs to create pivot records
   243	 *
   244	 * Purpose:
   245	 * - Creates pivot table records to link resources
   246	 * - Validates related resources exist by default (referential integrity)
   247	 * - Supports bulk insert for efficiency
   248	 * - Works within transactions for atomicity
   249	 * - Allows skipping validation when performance matters
   250	 *
   251	 * Data flow:
   252	 * 1. Validates pivot table resource exists
   253	 * 2. Gets actual database table name
   254	 * 3. Optionally validates each related resource exists (GET requests)
   255	 * 4. Prepares bulk insert data with foreign keys
   256	 * 5. Performs single INSERT with all records
   257	 * 6. Returns (no data returned, throws on error)
   258	 */
   259	export const createPivotRecords = async (api, resourceId, relDef, relData, trx) => {
   260	  if (relData.length === 0) return // Early exit if nothing to create
   261	
   262	  // Get pivot table info
   263	  const pivotScope = api.resources[relDef.through]
   264	  if (!pivotScope) {
   265	    throw new Error(`Pivot table resource '${relDef.through}' not found`)
   266	  }
   267	
   268	  // Get the actual database table name (might be different from scope name)
   269	  const tableName = pivotScope.vars.schemaInfo.tableName || relDef.through
   270	
   271	  // Validate all related resources exist if needed (do this before any inserts)
   272	  if (relDef.validateExists !== false) {
   273	    for (const related of relData) {
   274	      try {
   275	        await api.resources[related.type].get({
   276	          id: related.id,
   277	          transaction: trx
   278	        })
   279	      } catch (error) {
   280	        throw new RestApiResourceError(
   281	          `Related ${related.type} with id ${related.id} not found`,
   282	          {
   283	            subtype: 'not_found',
   284	            resourceType: related.type,
   285	            resourceId: related.id
   286	          }
   287	        )
   288	      }
   289	    }
   290	  }
   291	
   292	  // Prepare records for bulk insert
   293	  const recordsToInsert = relData.map(related => ({
   294	    [relDef.foreignKey]: resourceId,
   295	    [relDef.otherKey]: related.id
   296	  }))
   297	
   298	  // Bulk insert all pivot records in a single query
   299	  await trx(tableName).insert(recordsToInsert)
   300	}
