Setting the file. One moment.
CSV Layout · Rp Source CSV · wix/skills · Skills Docs
Back to the top of the page function sectionedCandidates
— line 243
This file
Number 17.5
Position 5 of 11
Type JavaScript
Size 39 KB
Lines 1,081 lib/ csv-layout.js
JavaScript · 1,081 lines · 39 KB
}
=
require
(
'./csv-parse.js'
);
14
15 const LAYOUT_SAMPLE_ROWS = 2000 ;
16 const MIN_LAYOUT_ROWS = 5 ;
17 const MIN_GROUPS = 3 ;
18 const SECTION_MAX_LEVELS = 6 ;
19 const SECTION_MIN_ROWS_PER_LEVEL = 3 ;
20 const COLUMN_SKEW = 0.7 ;
21 const LAYOUT_MARGIN = 0.15 ;
22 const LAYOUT_HALT_BELOW = 0.7 ;
23 const CHILD_SKEW = 0.8 ;
24 const PARENT_SKEW = 0.8 ;
25
26 const KNOWN_LEVEL_VOCAB = new Set ([
27 'simple' , 'variable' , 'variation' , 'grouped' , 'external' ,
28 'product' , 'variant' , 'parent' , 'child' , 'item' , 'sku' , 'header' , 'detail' , 'line' ,
29 ]);
30
31 const CATEGORY_COLUMN_NAMES = new Set ([
32 'category' , 'categories' , 'productcategory' , 'producttype' , 'collection' ,
33 'collections' , 'department' , 'taxonomy' , 'tags' , 'tag' ,
34 ]);
35
36 const DERIVED_AUTO_CONFIDENCE = 0.8 ;
37 const DERIVED_PROPOSE_CONFIDENCE = 0.5 ;
38 const HIERARCHY_SEP_MIN_RATE = 0.5 ;
39 const MULTI_VALUE_SEP_MIN_RATE = 0.3 ;
40 const MULTI_VALUE_DISTINCT_DROP = 0.7 ;
41 const MULTI_VALUE_MAX_TOKEN_LENGTH = 40 ;
42 const MULTI_VALUE_MAX_TOKENS = 12 ;
43
44 function isBlank ( value ) {
45 return value === undefined || value === null || String (value). trim () === '' ;
46 }
47
48 function cellAt ( row , index ) {
49 const value = row.values[index];
50 return value === undefined || value === null ? '' : value;
51 }
52
53 function slug ( value ) {
54 return String (value)
55 . trim ()
56 . toLowerCase ()
57 . replace ( / [ ^ a-z0-9] + / g , '-' )
58 . replace ( / ^ - +| - +$ / g , '' ) || 'unnamed' ;
59 }
60
61 function columnIndex ( header , name ) {
62 if ( ! name) {
63 return - 1 ;
64 }
65 const target = normalizeHeaderName (name);
66 return header. findIndex (( column ) => normalizeHeaderName (column) === target);
67 }
68
69 // Per-column statistics every classifier below reads. One pass, head-sampled.
70 function profileColumns ( header , rows ) {
71 return header. map (( name , index ) => {
72 const values = rows. map (( row ) => cellAt (row, index));
73 const nonBlank = values. filter (( value ) => ! isBlank (value));
74 const distinctAll = new Set (values);
75 const distinctNonBlank = new Set (nonBlank);
76
77 let runCount = 0 ;
78 let previous = null ;
79 for ( const value of values) {
80 if (previous === null || value !== previous) {
81 runCount += 1 ;
82 }
83 previous = value;
84 }
85
86 let blanksLead = false ;
87 for ( const value of values) {
88 if ( isBlank (value)) {
89 blanksLead = true ;
90 break ;
91 }
92 if ( ! isBlank (value)) {
93 break ;
94 }
95 }
96
97 const total = values. length || 1 ;
98 return {
99 name,
100 index,
101 total: values. length ,
102 nonBlankCount: nonBlank. length ,
103 blankCount: values. length - nonBlank. length ,
104 blankRate: (values. length - nonBlank. length ) / total,
105 distinct: distinctAll.size,
106 distinctNonBlank: distinctNonBlank.size,
107 distinctRatio: nonBlank. length === 0 ? 0 : distinctNonBlank.size / nonBlank. length ,
108 runCount,
109 meanRunLength: runCount === 0 ? 0 : values. length / runCount,
110 blanksLead,
111 firstRowBlank: values. length > 0 && isBlank (values[ 0 ]),
112 isIdLike: nonBlank. length === values. length && distinctNonBlank.size === values. length && values. length > 0 ,
113 levels: distinctNonBlank.size <= SECTION_MAX_LEVELS ? [ ... distinctNonBlank] : null ,
114 };
115 });
116 }
117
118 // Row indices at which a new group starts. Two candidate key columns that
119 // produce the SAME boundaries describe the same grouping — only the label is
120 // ambiguous, which is not a reason to halt.
121 function blankKeyBoundaries ( rows , index ) {
122 const boundaries = [];
123 rows. forEach (( row , rowIndex ) => {
124 if ( ! isBlank ( cellAt (row, index))) {
125 boundaries. push (rowIndex);
126 }
127 });
128 return boundaries;
129 }
130
131 function repeatKeyBoundaries ( rows , index ) {
132 const boundaries = [];
133 let previous = null ;
134 rows. forEach (( row , rowIndex ) => {
135 const value = cellAt (row, index);
136 if (previous === null || value !== previous) {
137 boundaries. push (rowIndex);
138 }
139 previous = value;
140 });
141 return boundaries;
142 }
143
144 function blankKeyCandidates ( stats , rows ) {
145 return stats
146 . filter (( column ) => column.blankRate > 0
147 && column.blankRate < 0.95
148 && ! column.firstRowBlank
149 && column.distinctNonBlank === column.nonBlankCount
150 && column.distinctNonBlank >= MIN_GROUPS
151 && column.blankCount >= 1 )
152 . map (( column ) => ({
153 column,
154 boundaries: blankKeyBoundaries (rows, column.index),
155 confidence: Math. min ( 0.95 , 0.75 + 0.2 * Math. min ( 1 , column.blankRate / 0.4 )),
156 }));
157 }
158
159 // A repeated key and a file merely SORTED by a categorical column look
160 // identical row-for-row. What separates them is a supporting parent column: in
161 // a real grouped file some other column is constant inside each run and differs
162 // between runs (the product name above its variant rows). A flat product list
163 // sorted by category has no such column — every other column varies inside the
164 // run — so it stays flat instead of inventing a parent/child split.
165 function hasSupportingParentColumn ( stats , rows , keyIndex , boundaries ) {
166 const runs = boundaries. map (( start , i ) => {
167 const end = i + 1 < boundaries. length ? boundaries[i + 1 ] : rows. length ;
168 return rows. slice (start, end);
169 });
170
171 return stats. some (( column ) => {
172 if (column.index === keyIndex) {
173 return false ;
174 }
175 const runValues = [];
176 for ( const run of runs) {
177 const values = new Set (run
178 . map (( row ) => cellAt (row, column.index))
179 . filter (( value ) => ! isBlank (value)));
180 if (values.size > 1 ) {
181 return false ;
182 }
183 if (values.size === 1 ) {
184 runValues. push ([ ... values][ 0 ]);
185 }
186 }
187 return new Set (runValues).size > 1 ;
188 });
189 }
190
191 function repeatKeyCandidates ( stats , rows ) {
192 return stats
193 . filter (( column ) => column.blankRate === 0
194 && column.distinctRatio < 1
195 && column.runCount === column.distinctNonBlank
196 && column.distinctNonBlank >= MIN_GROUPS
197 && column.meanRunLength >= 1.5
198 && hasSupportingParentColumn (stats, rows, column.index, repeatKeyBoundaries (rows, column.index)))
199 . map (( column ) => ({
200 column,
201 boundaries: repeatKeyBoundaries (rows, column.index),
202 // Lower ceiling than blank-key on purpose: a file merely sorted by a
203 // column is indistinguishable from one grouped by it.
204 confidence: Math. min ( 0.9 , 0.7 + 0.2 * Math. min ( 1 , (column.meanRunLength - 1 ) / 2 )),
205 }));
206 }
207
208 // A low-cardinality column is only a section discriminator if the row kinds it
209 // separates actually populate different columns. Without this test every
210 // Published / Tax class column is a false positive.
211 function sectionSkew ( stats , rows , discriminator ) {
212 const partitions = new Map ();
213 rows. forEach (( row ) => {
214 const level = cellAt (row, discriminator.index);
215 if ( ! partitions. has (level)) {
216 partitions. set (level, []);
217 }
218 partitions. get (level). push (row);
219 });
220 if (partitions.size < 2 ) {
221 return { maxSkew: 0 , skewColumn: null };
222 }
223
224 let maxSkew = 0 ;
225 let skewColumn = null ;
226 for ( const column of stats) {
227 if (column.index === discriminator.index) {
228 continue ;
229 }
230 const rates = [ ... partitions. values ()]. map (( partitionRows ) => {
231 const blanks = partitionRows. filter (( row ) => isBlank ( cellAt (row, column.index))). length ;
232 return blanks / (partitionRows. length || 1 );
233 });
234 const skew = Math. max ( ... rates) - Math. min ( ... rates);
235 if (skew > maxSkew) {
236 maxSkew = skew;
237 skewColumn = column.name;
238 }
239 }
240 return { maxSkew, skewColumn };
241 }
242
243 function sectionedCandidates ( stats , rows ) {
244 return stats
245 . filter (( column ) => column.blankRate < 0.05
246 && column.distinctNonBlank >= 2
247 && column.distinctNonBlank <= SECTION_MAX_LEVELS
248 && column.nonBlankCount / column.distinctNonBlank >= SECTION_MIN_ROWS_PER_LEVEL )
249 . map (( column ) => {
250 const { maxSkew , skewColumn } = sectionSkew (stats, rows, column);
251 const vocabBonus = column.levels
252 && column.levels. every (( level ) => KNOWN_LEVEL_VOCAB . has ( normalizeHeaderName (level)))
253 ? 0.1
254 : 0 ;
255 return {
256 column,
257 maxSkew,
258 skewColumn,
259 vocabBonus,
260 confidence: maxSkew < COLUMN_SKEW
261 ? 0
262 : Math. min ( 0.95 , 0.7 + ( 0.15 * (maxSkew - COLUMN_SKEW )) / 0.3 + vocabBonus),
263 };
264 })
265 . filter (( candidate ) => candidate.confidence > 0 );
266 }
267
268 // In a sectioned file, "blank" columns are blank because those ROWS are a
269 // different kind, not because they continue a group above. When a column's
270 // blankness is a pure function of the discriminator value, the blank-key
271 // reading is already explained by the sectioning and must not compete with it —
272 // otherwise every WooCommerce export looks ambiguous.
273 function blankKeyExplainedBySection ( rows , keyIndex , discriminatorIndex ) {
274 const byLevel = new Map ();
275 for ( const row of rows) {
276 const level = cellAt (row, discriminatorIndex);
277 const blank = isBlank ( cellAt (row, keyIndex));
278 if ( ! byLevel. has (level)) {
279 byLevel. set (level, new Set ());
280 }
281 byLevel. get (level). add (blank);
282 }
283 return [ ... byLevel. values ()]. every (( observed ) => observed.size === 1 );
284 }
285
286 function pickGroupCandidate ( candidates ) {
287 if (candidates. length === 0 ) {
288 return { picked: null , equivalents: [], ambiguous: false };
289 }
290 const signatures = new Map ();
291 for ( const candidate of candidates) {
292 const signature = candidate.boundaries. join ( ',' );
293 if ( ! signatures. has (signature)) {
294 signatures. set (signature, []);
295 }
296 signatures. get (signature). push (candidate);
297 }
298 if (signatures.size > 1 ) {
299 return { picked: null , equivalents: [], ambiguous: true };
300 }
301 const [ group ] = [ ... signatures. values ()];
302 const ordered = [ ... group]. sort (( a , b ) => a.column.index - b.column.index);
303 return {
304 picked: ordered[ 0 ],
305 equivalents: ordered. slice ( 1 ). map (( candidate ) => candidate.column.name),
306 ambiguous: false ,
307 };
308 }
309
310 function unknownLayout ( evidence , confidence , candidates ) {
311 return {
312 pattern: 'unknown' ,
313 confidence: Math. min ( 0.5 , confidence),
314 halt: true ,
315 evidence,
316 groupKey: null ,
317 continuation: null ,
318 discriminatorColumn: null ,
319 levels: null ,
320 childLevels: [],
321 parentRefColumn: null ,
322 parentEntity: 'record' ,
323 childEntity: null ,
324 candidates,
325 layoutConflicts: [],
326 source: 'inferred' ,
327 };
328 }
329
330 function classifyLayout ( header , rows , { overlayLayout = null , sampleRows = LAYOUT_SAMPLE_ROWS } = {}) {
331 const sample = rows. slice ( 0 , sampleRows);
332
333 if (overlayLayout) {
334 const applied = applyOverlayLayout (overlayLayout, header, sample);
335 if (applied) {
336 return applied;
337 }
338 }
339
340 const evidence = [ `sampledRows=${ sample . length }` ];
341 if (sample. length < MIN_LAYOUT_ROWS ) {
342 evidence. push ( `fewer than MIN_LAYOUT_ROWS=${ MIN_LAYOUT_ROWS } data rows; not enough signal to classify` );
343 return unknownLayout (evidence, 0.3 , []);
344 }
345
346 const stats = profileColumns (header, sample);
347 const sectioned = sectionedCandidates (stats, sample)
348 . sort (( a , b ) => b.confidence - a.confidence || a.column.index - b.column.index);
349
350 let rawBlankKey = blankKeyCandidates (stats, sample);
351 if (sectioned. length > 0 ) {
352 const discriminator = sectioned[ 0 ].column;
353 const unexplained = rawBlankKey. filter (( candidate ) => ! blankKeyExplainedBySection (sample, candidate.column.index, discriminator.index));
354 if (unexplained. length < rawBlankKey. length ) {
355 evidence. push ( `${ rawBlankKey . length - unexplained . length } blank-key candidate(s) are explained by the "${ discriminator . name }" sections, not by continuation rows` );
356 }
357 rawBlankKey = unexplained;
358 }
359
360 const blankKey = pickGroupCandidate (rawBlankKey);
361 const repeatKey = pickGroupCandidate ( repeatKeyCandidates (stats, sample));
362
363 const candidates = [];
364 if (blankKey.picked) {
365 candidates. push ({
366 pattern: 'grouped-by-key' ,
367 continuation: 'blank-key' ,
368 groupKey: blankKey.picked.column.name,
369 equivalentGroupKeys: blankKey.equivalents,
370 confidence: blankKey.picked.confidence,
371 groupCount: blankKey.picked.boundaries. length ,
372 });
373 }
374 if (repeatKey.picked) {
375 candidates. push ({
376 pattern: 'grouped-by-key' ,
377 continuation: 'repeat-key' ,
378 groupKey: repeatKey.picked.column.name,
379 equivalentGroupKeys: repeatKey.equivalents,
380 confidence: repeatKey.picked.confidence,
381 groupCount: repeatKey.picked.boundaries. length ,
382 });
383 }
384 if (sectioned. length > 0 ) {
385 candidates. push ({
386 pattern: 'sectioned' ,
387 discriminatorColumn: sectioned[ 0 ].column.name,
388 levels: sectioned[ 0 ].column.levels,
389 confidence: sectioned[ 0 ].confidence,
390 skewColumn: sectioned[ 0 ].skewColumn,
391 });
392 }
393
394 if (blankKey.ambiguous) {
395 evidence. push ( 'several blank-key candidates describe DIFFERENT groupings; refusing to pick one' );
396 return unknownLayout (evidence, 0.5 , candidates);
397 }
398 if (repeatKey.ambiguous) {
399 evidence. push ( 'several repeat-key candidates describe DIFFERENT groupings; refusing to pick one' );
400 return unknownLayout (evidence, 0.5 , candidates);
401 }
402
403 if (candidates. length === 0 ) {
404 const idColumn = stats. find (( column ) => column.isIdLike);
405 evidence. push (idColumn
406 ? `no grouping signal; "${ idColumn . name }" is unique and never blank`
407 : 'no grouping signal and no unique id column' );
408 return {
409 pattern: 'flat' ,
410 confidence: idColumn ? 0.95 : 0.8 ,
411 halt: false ,
412 evidence,
413 groupKey: null ,
414 continuation: null ,
415 discriminatorColumn: null ,
416 levels: null ,
417 childLevels: [],
418 parentRefColumn: null ,
419 parentEntity: 'record' ,
420 childEntity: null ,
421 primaryKey: idColumn ? idColumn.name : null ,
422 candidates,
423 layoutConflicts: [],
424 source: 'inferred' ,
425 };
426 }
427
428 const ranked = [ ... candidates]. sort (( a , b ) => b.confidence - a.confidence);
429 const winner = ranked[ 0 ];
430 const runnerUp = ranked[ 1 ];
431 if (runnerUp && runnerUp.confidence >= LAYOUT_HALT_BELOW && winner.confidence - runnerUp.confidence < LAYOUT_MARGIN ) {
432 evidence. push ( `two layouts score within ${ LAYOUT_MARGIN }: ${ winner . pattern }/${ winner . continuation || winner . discriminatorColumn } vs ${ runnerUp . pattern }/${ runnerUp . continuation || runnerUp . discriminatorColumn }` );
433 return unknownLayout (evidence, winner.confidence, candidates);
434 }
435
436 if (winner.pattern === 'grouped-by-key' ) {
437 evidence. push ( `grouped by "${ winner . groupKey }" (${ winner . continuation }); ${ winner . groupCount } groups in the sample` );
438 if (winner.equivalentGroupKeys. length > 0 ) {
439 evidence. push ( `same grouping is described by: ${ winner . equivalentGroupKeys . join ( ', ' ) }` );
440 }
441 } else {
442 evidence. push ( `sectioned on "${ winner . discriminatorColumn }" (levels: ${ ( winner . levels || []). join ( ', ' ) }); population skew found on "${ winner . skewColumn }"` );
443 }
444
445 return {
446 pattern: winner.pattern,
447 confidence: winner.confidence,
448 halt: false ,
449 evidence,
450 groupKey: winner.groupKey || null ,
451 equivalentGroupKeys: winner.equivalentGroupKeys || [],
452 continuation: winner.continuation || null ,
453 discriminatorColumn: winner.discriminatorColumn || null ,
454 levels: winner.levels || null ,
455 // Which levels are children cannot be inferred from a custom file; the user
456 // or an overlay decides. Recorded as unresolved rather than guessed.
457 childLevels: [],
458 parentRefColumn: null ,
459 parentEntity: 'record' ,
460 childEntity: winner.pattern === 'grouped-by-key' ? 'child' : null ,
461 candidates,
462 layoutConflicts: [],
463 source: 'inferred' ,
464 };
465 }
466
467 // The layout an overlay declares, materialized against this file's header.
468 function pinnedLayout ( overlayLayout , header , rows , { confidence , source , evidence , layoutConflicts = [] }) {
469 const parentRefColumn = overlayLayout.parentRefColumn && columnIndex (header, overlayLayout.parentRefColumn) === - 1
470 ? null
471 : overlayLayout.parentRefColumn || null ;
472
473 return {
474 pattern: overlayLayout.pattern,
475 confidence,
476 halt: false ,
477 evidence,
478 groupKey: overlayLayout.groupKey || null ,
479 equivalentGroupKeys: [],
480 continuation: overlayLayout.continuation || null ,
481 discriminatorColumn: overlayLayout.discriminatorColumn || null ,
482 levels: overlayLayout.discriminatorColumn
483 ? [ ...new Set (rows. map (( row ) => cellAt (row, columnIndex (header, overlayLayout.discriminatorColumn))). filter (( value ) => ! isBlank (value)))]
484 : null ,
485 childLevels: overlayLayout.childLevels || [],
486 parentRefColumn,
487 parentEntity: overlayLayout.parentEntity || 'record' ,
488 childEntity: overlayLayout.childEntity || (overlayLayout.pattern === 'grouped-by-key' ? 'child' : null ),
489 columnGroups: overlayLayout.columnGroups || [],
490 candidates: [],
491 layoutConflicts,
492 source,
493 };
494 }
495
496 // A pinned `continuation` is a CLAIM ABOUT THE ROWS, not a fact about the vendor,
497 // and real exports vary: some Shopify files blank the Handle on continuation rows
498 // while others repeat it on every row. When the claim is false nothing errors —
499 // grouping silently collapses and each row becomes its own product — so the mode
500 // is checked against the sample before it is trusted.
501 //
502 // The check deliberately fires only when the two modes would group DIFFERENTLY.
503 // A file with one row per group (every key present, none repeated) is described
504 // equally well by either mode, and flagging it would report drift that has no
505 // consequence.
506 function verifyOverlayContinuation ( overlayLayout , header , rows ) {
507 if (overlayLayout.pattern !== 'grouped-by-key'
508 || (overlayLayout.continuation !== 'blank-key' && overlayLayout.continuation !== 'repeat-key' )) {
509 return { ok: true };
510 }
511 const keyIndex = columnIndex (header, overlayLayout.groupKey);
512 if (keyIndex === - 1 || rows. length === 0 ) {
513 return { ok: true };
514 }
515
516 const blankBoundaries = blankKeyBoundaries (rows, keyIndex);
517 const repeatBoundaries = repeatKeyBoundaries (rows, keyIndex);
518 if (blankBoundaries. join ( ',' ) === repeatBoundaries. join ( ',' )) {
519 return { ok: true };
520 }
521
522 const declared = overlayLayout.continuation;
523 const blankKeyRows = rows. filter (( row ) => isBlank ( cellAt (row, keyIndex))). length ;
524
525 if (declared === 'blank-key' && blankKeyRows === 0 ) {
526 return {
527 ok: false ,
528 declared,
529 observed: 'repeat-key' ,
530 evidence: `overlay-continuation-mismatch: the overlay pins continuation="blank-key" but "${ overlayLayout . groupKey }" is never blank in ${ rows . length } sampled row(s); reading it as blank-key yields ${ blankBoundaries . length } groups where the repeated key yields ${ repeatBoundaries . length }` ,
531 };
532 }
533 // The mirror case: repeat-key grouping cannot start a group on a blank key, so
534 // a blank one lands on whichever group precedes it.
535 if (declared === 'repeat-key' && blankKeyRows > 0 ) {
536 return {
537 ok: false ,
538 declared,
539 observed: 'blank-key' ,
540 evidence: `overlay-continuation-mismatch: the overlay pins continuation="repeat-key" but "${ overlayLayout . groupKey }" is blank on ${ blankKeyRows } of ${ rows . length } sampled row(s); reading it as repeat-key yields ${ repeatBoundaries . length } groups where blank-key yields ${ blankBoundaries . length }` ,
541 };
542 }
543
544 return { ok: true };
545 }
546
547 // An overlay pins the layout only as far as this file's rows corroborate it. Two
548 // things are checked: that its declared columns are actually in the header, and
549 // that its declared continuation mode is the one the rows exhibit. Either way a
550 // failed check falls back to inference and says so — the drift backstop the spec
551 // requires, and what "advisory, never authoritative" means for layout.
552 function applyOverlayLayout ( overlayLayout , header , rows ) {
553 const mismatches = [];
554 const conflicts = [];
555 if (overlayLayout.groupKey && columnIndex (header, overlayLayout.groupKey) === - 1 ) {
556 mismatches. push ( `overlay-groupkey-missing: "${ overlayLayout . groupKey }" is not in the header` );
557 conflicts. push ({
558 kind: 'overlay-groupkey-missing' ,
559 overlayGroupKey: overlayLayout.groupKey,
560 resolution: 'fell back to the generic classifier' ,
561 });
562 }
563 if (overlayLayout.discriminatorColumn && columnIndex (header, overlayLayout.discriminatorColumn) === - 1 ) {
564 mismatches. push ( `overlay-discriminator-missing: "${ overlayLayout . discriminatorColumn }" is not in the header` );
565 conflicts. push ({
566 kind: 'overlay-discriminator-missing' ,
567 overlayDiscriminatorColumn: overlayLayout.discriminatorColumn,
568 resolution: 'fell back to the generic classifier' ,
569 });
570 }
571 if (mismatches. length > 0 ) {
572 const inferred = classifyLayout (header, rows, { overlayLayout: null });
573 return {
574 ... inferred,
575 source: 'inferred-after-overlay-mismatch' ,
576 evidence: [ ... mismatches, ... inferred.evidence],
577 layoutConflicts: conflicts,
578 };
579 }
580
581 const continuation = verifyOverlayContinuation (overlayLayout, header, rows);
582 if ( ! continuation.ok) {
583 const inferred = classifyLayout (header, rows, { overlayLayout: null });
584 // Which COLUMN groups the rows is the part vendors do not change, and the
585 // overlay has just been confirmed to name a column this file has. So the
586 // classifier is consulted about the disputed field only: a candidate naming
587 // the same key column corroborates the observed mode even when the
588 // classifier as a whole halted, because its halt is about *picking* a key.
589 const corroborating = (inferred.candidates || []). find (( candidate ) => candidate.pattern === 'grouped-by-key'
590 && candidate.groupKey
591 && normalizeHeaderName (candidate.groupKey) === normalizeHeaderName (overlayLayout.groupKey)
592 && candidate.continuation === continuation.observed);
593 const conflict = {
594 kind: 'overlay-continuation-mismatch' ,
595 groupKey: overlayLayout.groupKey,
596 overlayContinuation: continuation.declared,
597 observedContinuation: continuation.observed,
598 corroboratedBy: corroborating ? 'generic-classifier-candidate' : null ,
599 resolution: corroborating
600 ? `kept the overlay's grouping and corrected continuation to "${ continuation . observed }"`
601 : 'fell back to the generic classifier' ,
602 };
603
604 if (corroborating) {
605 return pinnedLayout (
606 { ... overlayLayout, continuation: continuation.observed },
607 header,
608 rows,
609 {
610 confidence: corroborating.confidence,
611 source: 'overlay-continuation-corrected' ,
612 evidence: [
613 continuation.evidence,
614 `the generic classifier independently reads "${ overlayLayout . groupKey }" as ${ continuation . observed } with ${ corroborating . groupCount } group(s) in the sample; continuation corrected to "${ continuation . observed }" and the rest of the overlay kept` ,
615 ],
616 layoutConflicts: [conflict],
617 },
618 );
619 }
620
621 // The rows contradict the declared mode AND the classifier finds no grouping
622 // on the declared key. Nothing here is trustworthy enough to pin.
623 return {
624 ... inferred,
625 source: 'inferred-after-overlay-mismatch' ,
626 evidence: [
627 continuation.evidence,
628 `no generic candidate groups on "${ overlayLayout . groupKey }" as ${ continuation . observed } either, so the whole overlay layout was dropped` ,
629 ... inferred.evidence,
630 ],
631 layoutConflicts: [conflict],
632 };
633 }
634
635 return pinnedLayout (overlayLayout, header, rows, {
636 confidence: 1 ,
637 source: 'overlay' ,
638 evidence: [
639 `layout pinned by the vendor overlay (${ overlayLayout . pattern })` ,
640 ... (overlayLayout.pattern === 'grouped-by-key' && overlayLayout.continuation
641 ? [ `continuation="${ overlayLayout . continuation }" verified against ${ rows . length } sampled row(s)` ]
642 : []),
643 ],
644 });
645 }
646
647 // Materialize the row groups the layout describes. Every consumer (column-role
648 // derivation, derived entities, the generated reader) replays grouping through
649 // this one function so they cannot disagree.
650 function buildGroups ( header , rows , layout ) {
651 if (layout.pattern === 'grouped-by-key' ) {
652 const keyIndex = columnIndex (header, layout.groupKey);
653 if (keyIndex === - 1 ) {
654 return [];
655 }
656 const groups = [];
657 let current = null ;
658 let previousKey = null ;
659 rows. forEach (( row , rowIndex ) => {
660 const value = cellAt (row, keyIndex);
661 const startsGroup = layout.continuation === 'blank-key'
662 ? ! isBlank (value)
663 : value !== previousKey;
664 if (startsGroup || current === null ) {
665 current = { key: value, headIndex: rowIndex, rows: [], childRows: [] };
666 groups. push (current);
667 }
668 current.rows. push ({ row, rowIndex });
669 if (rowIndex !== current.headIndex) {
670 current.childRows. push ({ row, rowIndex });
671 }
672 previousKey = value;
673 });
674 return groups;
675 }
676
677 if (layout.pattern === 'sectioned' ) {
678 const discriminatorIndex = columnIndex (header, layout.discriminatorColumn);
679 const parentRefIndex = columnIndex (header, layout.parentRefColumn);
680 const childLevels = new Set ((layout.childLevels || []). map (( level ) => normalizeHeaderName (level)));
681 const groups = [];
682 const byKey = new Map ();
683 let current = null ;
684
685 rows. forEach (( row , rowIndex ) => {
686 const level = normalizeHeaderName ( cellAt (row, discriminatorIndex));
687 const isChild = childLevels. has (level);
688 if ( ! isChild) {
689 current = { key: null , headIndex: rowIndex, rows: [{ row, rowIndex }], childRows: [], level };
690 groups. push (current);
691 if (parentRefIndex !== - 1 ) {
692 // Woo links a variation to its parent by id or SKU, so index the
693 // parent row under every value a child might reference it by.
694 for ( const value of row.values) {
695 if ( ! isBlank (value)) {
696 byKey. set ( String (value). trim (), current);
697 }
698 }
699 }
700 return ;
701 }
702 let target = current;
703 if (parentRefIndex !== - 1 ) {
704 const ref = String ( cellAt (row, parentRefIndex)). trim (). replace ( / ^ id:/ i , '' );
705 target = byKey. get (ref) || current;
706 }
707 if ( ! target) {
708 // A child row before any parent row: keep it visible rather than dropping it.
709 target = { key: null , headIndex: rowIndex, rows: [], childRows: [], orphan: true };
710 groups. push (target);
711 }
712 target.rows. push ({ row, rowIndex });
713 target.childRows. push ({ row, rowIndex });
714 });
715 return groups;
716 }
717
718 return rows. map (( row , rowIndex ) => ({ key: null , headIndex: rowIndex, rows: [{ row, rowIndex }], childRows: [] }));
719 }
720
721 function matchesColumnGroup ( columnName , group ) {
722 const normalized = normalizeHeaderName (columnName);
723 for ( const exact of group.columns || []) {
724 if ( normalizeHeaderName (exact) === normalized) {
725 return true ;
726 }
727 }
728 for ( const prefix of group.prefixes || []) {
729 const normalizedPrefix = normalizeHeaderName (prefix);
730 if (normalizedPrefix && normalized. startsWith (normalizedPrefix)) {
731 return true ;
732 }
733 }
734 return false ;
735 }
736
737 // Which entity does each column belong to?
738 //
739 // The naive rule ("child columns are the ones populated on continuation rows")
740 // is wrong for the most important case: a Shopify product's FIRST variant lives
741 // on the group head row, and a single-variant product has no continuation rows
742 // at all. The reliable generic signal is within-group VARIANCE, plus the mirror
743 // rule that a column blank on every continuation row is a parent column.
744 function deriveColumnRoles ( header , rows , layout , { columnGroups = [] } = {}) {
745 const groups = buildGroups (header, rows, layout);
746 const multiRowGroups = groups. filter (( group ) => group.rows. length > 1 );
747
748 const parentColumns = [];
749 const childColumns = [];
750 const ambiguousColumns = [];
751 const byEntity = {};
752 const layoutConflicts = [];
753 const overlayGroups = columnGroups. length > 0 ? columnGroups : (layout.columnGroups || []);
754
755 const parentEntity = layout.parentEntity || 'record' ;
756 const childEntity = layout.childEntity || 'child' ;
757 byEntity[parentEntity] = [];
758
759 header. forEach (( name , index ) => {
760 const overlayGroup = overlayGroups. find (( group ) => matchesColumnGroup (name, group));
761
762 let derivedRole = 'ambiguous' ;
763 if (multiRowGroups. length === 0 ) {
764 derivedRole = 'parent' ;
765 } else {
766 let varying = 0 ;
767 let constant = 0 ;
768 let blankOnEveryContinuation = 0 ;
769 let populatedOnHead = 0 ;
770
771 for ( const group of multiRowGroups) {
772 const groupValues = group.rows. map (( entry ) => cellAt (entry.row, index));
773 const nonBlank = groupValues. filter (( value ) => ! isBlank (value));
774 if ( new Set (nonBlank).size > 1 ) {
775 varying += 1 ;
776 } else {
777 constant += 1 ;
778 }
779 const continuationValues = group.childRows. map (( entry ) => cellAt (entry.row, index));
780 if (continuationValues. length > 0 && continuationValues. every (( value ) => isBlank (value))) {
781 blankOnEveryContinuation += 1 ;
782 }
783 if ( ! isBlank ( cellAt (group.rows[ 0 ].row, index))) {
784 populatedOnHead += 1 ;
785 }
786 }
787
788 const total = multiRowGroups. length ;
789 if (blankOnEveryContinuation / total >= PARENT_SKEW && populatedOnHead / total >= PARENT_SKEW ) {
790 derivedRole = 'parent' ;
791 } else if (varying / total >= CHILD_SKEW ) {
792 derivedRole = 'child' ;
793 } else if (constant / total >= PARENT_SKEW && populatedOnHead / total >= PARENT_SKEW ) {
794 derivedRole = 'parent' ;
795 }
796 }
797
798 // Overlay wins on bucketing (only a declaration can separate Shopify's
799 // image columns from its variant columns); data wins on visibility, so a
800 // disagreement is recorded rather than silently dropped.
801 const entity = overlayGroup ? overlayGroup.entity : (derivedRole === 'child' ? childEntity : parentEntity);
802 if (overlayGroup && derivedRole !== 'ambiguous' ) {
803 const overlaySaysChild = overlayGroup.entity !== parentEntity;
804 if (overlaySaysChild !== (derivedRole === 'child' )) {
805 layoutConflicts. push ({
806 column: name,
807 overlayEntity: overlayGroup.entity,
808 derivedRole,
809 resolution: 'kept the overlay assignment' ,
810 });
811 }
812 }
813
814 if ( ! byEntity[entity]) {
815 byEntity[entity] = [];
816 }
817 byEntity[entity]. push (name);
818
819 if (entity === parentEntity) {
820 parentColumns. push (name);
821 } else if (entity === childEntity) {
822 childColumns. push (name);
823 }
824 if (derivedRole === 'ambiguous' && ! overlayGroup) {
825 ambiguousColumns. push (name);
826 }
827 });
828
829 const collectionColumns = Object. entries (byEntity)
830 . filter (([ entity ]) => entity !== parentEntity && entity !== childEntity)
831 . flatMap (([, columns ]) => columns);
832
833 return {
834 parentEntity,
835 childEntity: childColumns. length > 0 ? childEntity : null ,
836 parentColumns,
837 childColumns,
838 collectionColumns,
839 ambiguousColumns,
840 byEntity,
841 layoutConflicts,
842 groupCount: groups. length ,
843 };
844 }
845
846 function splitMultiValue ( cell , separator ) {
847 if ( ! separator) {
848 return [ String (cell)];
849 }
850 // Vendors escape a separator inside a value by quoting it within the cell
851 // ("Home, Garden > Tools"), so the inner split has to be RFC-4180 aware too.
852 const rows = parseText ( String (cell), { delimiter: separator });
853 return rows. flatMap (( row ) => row.values);
854 }
855
856 // Distinct values of a column become their own entity. This is how categories
857 // (and tags) arrive in every named vendor's export.
858 function deriveColumnValues ( header , rows , descriptor , { layout = null } = {}) {
859 const index = columnIndex (header, descriptor.fromColumn);
860 if (index === - 1 ) {
861 return {
862 entity: descriptor.entity,
863 records: [],
864 links: [],
865 missingColumn: descriptor.fromColumn,
866 };
867 }
868
869 const hierarchySeparator = descriptor.hierarchySeparator || null ;
870 const multiValueSeparator = descriptor.multiValueSeparator || null ;
871 const groups = layout ? buildGroups (header, rows, layout) : rows. map (( row , rowIndex ) => ({
872 key: null ,
873 headIndex: rowIndex,
874 rows: [{ row, rowIndex }],
875 childRows: [],
876 }));
877
878 const seen = new Map ();
879 const links = [];
880
881 for ( const group of groups) {
882 // Group heads only: a Woo variation row leaves Categories blank, and a
883 // Shopify continuation row would double-count its product's categories.
884 const headEntry = group.rows[ 0 ];
885 if ( ! headEntry) {
886 continue ;
887 }
888 const cell = cellAt (headEntry.row, index);
889 if ( isBlank (cell)) {
890 continue ;
891 }
892
893 // Multi-value split FIRST, then hierarchy per element. The other order
894 // turns "Clothing > Shirts, Sale" into a category named "Shirts, Sale".
895 const values = splitMultiValue (cell, multiValueSeparator)
896 . map (( value ) => value. trim ())
897 . filter (( value ) => value !== '' );
898
899 for ( const value of values) {
900 const segments = (hierarchySeparator ? value. split (hierarchySeparator) : [value])
901 . map (( segment ) => segment. trim ())
902 . filter (( segment ) => segment !== '' );
903 if (segments. length === 0 ) {
904 continue ;
905 }
906
907 let leafId = null ;
908 for ( let depth = 1 ; depth <= segments. length ; depth += 1 ) {
909 const pathSegments = segments. slice ( 0 , depth);
910 const id = pathSegments. map (slug). join ( '/' );
911 if ( ! seen. has (id)) {
912 seen. set (id, {
913 id,
914 name: pathSegments[depth - 1 ],
915 path: pathSegments. join (hierarchySeparator ? ` ${ hierarchySeparator } ` : '/' ),
916 depth,
917 parentId: depth > 1 ? pathSegments. slice ( 0 , depth - 1 ). map (slug). join ( '/' ) : null ,
918 sourceRows: 0 ,
919 });
920 }
921 leafId = id;
922 }
923 seen. get (leafId).sourceRows += 1 ;
924 // Leaf-only linking. Whether ancestors are also attached is a mapping
925 // decision, carried as descriptor.linkPolicy.
926 links. push ({
927 from: group.key !== null && group.key !== '' ? group.key : `row:${ headEntry . rowIndex }` ,
928 to: leafId,
929 });
930 }
931 }
932
933 // Depth-ascending so a parent is always created before its child when the
934 // import walks the extract in order.
935 const records = [ ... seen. values ()]. sort (( a , b ) => a.depth - b.depth || a.id. localeCompare (b.id));
936
937 return {
938 entity: descriptor.entity,
939 origin: {
940 kind: 'column-values' ,
941 column: descriptor.fromColumn,
942 hierarchySeparator,
943 multiValueSeparator,
944 linkPolicy: descriptor.linkPolicy || 'leaf' ,
945 },
946 hierarchical: Boolean (descriptor.hierarchical),
947 records,
948 links,
949 missingColumn: null ,
950 };
951 }
952
953 function looksLikeUrl ( value ) {
954 return / ^ (https ? :) ? \/\/ / i . test ( String (value). trim ());
955 }
956
957 // `<p>Soft cotton tee</p>` contains '>' in every row and would otherwise look
958 // like a two-level hierarchy shared by every product.
959 function looksLikeMarkup ( value ) {
960 return /< \/ ? [a-z!][ ^ >] * >/ i . test ( String (value));
961 }
962
963 function separatorRate ( values , separator ) {
964 if (values. length === 0 ) {
965 return 0 ;
966 }
967 return values. filter (( value ) => value. includes (separator)). length / values. length ;
968 }
969
970 function sharedAncestorCount ( values , separator ) {
971 const ancestors = new Map ();
972 for ( const value of values) {
973 const segments = value. split (separator). map (( segment ) => segment. trim ()). filter (Boolean);
974 for ( let depth = 1 ; depth < segments. length ; depth += 1 ) {
975 const key = segments. slice ( 0 , depth). join ( '>' );
976 if ( ! ancestors. has (key)) {
977 ancestors. set (key, new Set ());
978 }
979 ancestors. get (key). add (value);
980 }
981 }
982 return [ ... ancestors. values ()]. filter (( paths ) => paths.size >= 2 ). length ;
983 }
984
985 // For custom files. Deliberately narrow: only '>' is auto-detected as a
986 // hierarchy separator ('/' matches every URL column), and a column is only
987 // auto-derived when its NAME says it is a taxonomy. Everything else is proposed
988 // to the user rather than silently turned into an entity.
989 function detectDerivedCandidates ( header , rows , { profile = null } = {}) {
990 if (profile && Array. isArray (profile.derived) && profile.derived. length > 0 ) {
991 return profile.derived. map (( descriptor ) => ({
992 ... descriptor,
993 status: columnIndex (header, descriptor.fromColumn) === - 1 ? 'missing-column' : 'overlay' ,
994 confidence: 1 ,
995 evidence: `declared by the ${ profile . vendor } overlay` ,
996 }));
997 }
998
999 const candidates = [];
1000 header. forEach (( name , index ) => {
1001 const values = rows
1002 . map (( row ) => cellAt (row, index))
1003 . filter (( value ) => ! isBlank (value))
1004 . map (( value ) => String (value). trim ());
1005 if (values. length === 0 || values. some (looksLikeUrl) || values. some (looksLikeMarkup)) {
1006 return ;
1007 }
1008
1009 const nameHit = CATEGORY_COLUMN_NAMES . has ( normalizeHeaderName (name));
1010 const hierarchyRate = separatorRate (values, '>' );
1011 const sharedAncestors = hierarchyRate >= HIERARCHY_SEP_MIN_RATE ? sharedAncestorCount (values, '>' ) : 0 ;
1012 const hierarchical = hierarchyRate >= HIERARCHY_SEP_MIN_RATE && sharedAncestors >= 1 ;
1013
1014 const commaRate = separatorRate (values, ',' );
1015 let multiValue = false ;
1016 let distinctDrop = false ;
1017 if (commaRate >= MULTI_VALUE_SEP_MIN_RATE ) {
1018 const tokens = values. flatMap (( value ) => value. split ( ',' ). map (( token ) => token. trim ()). filter (Boolean));
1019 const meanTokenLength = tokens. reduce (( sum , token ) => sum + token. length , 0 ) / (tokens. length || 1 );
1020 const maxTokensPerCell = Math. max ( ... values. map (( value ) => value. split ( ',' ). length ));
1021 const distinctBefore = new Set (values).size;
1022 const distinctAfter = new Set (tokens).size;
1023 // A free-text column split on commas yields near-unique tokens; a real
1024 // multi-value column reuses a small vocabulary.
1025 distinctDrop = distinctAfter < distinctBefore * MULTI_VALUE_DISTINCT_DROP ;
1026 multiValue = distinctDrop
1027 && meanTokenLength <= MULTI_VALUE_MAX_TOKEN_LENGTH
1028 && maxTokensPerCell <= MULTI_VALUE_MAX_TOKENS ;
1029 }
1030
1031 if ( ! nameHit && ! hierarchical) {
1032 return ;
1033 }
1034
1035 const confidence = 0.6
1036 + (nameHit ? 0.2 : 0 )
1037 + (sharedAncestors > 0 ? 0.15 : 0 )
1038 + (distinctDrop ? 0.05 : 0 );
1039 if (confidence < DERIVED_PROPOSE_CONFIDENCE ) {
1040 return ;
1041 }
1042
1043 candidates. push ({
1044 entity: normalizeHeaderName (name). replace ( /ies $ / , 'y' ). replace ( /s $ / , '' ) || 'derived' ,
1045 fromColumn: name,
1046 hierarchySeparator: hierarchical ? '>' : null ,
1047 multiValueSeparator: multiValue ? ',' : null ,
1048 hierarchical,
1049 linkPolicy: 'leaf' ,
1050 status: confidence >= DERIVED_AUTO_CONFIDENCE ? 'auto' : 'proposed' ,
1051 confidence: Number (confidence. toFixed ( 2 )),
1052 evidence: `nameHit=${ nameHit }; hierarchyRate=${ hierarchyRate . toFixed ( 2 ) }; sharedAncestors=${ sharedAncestors }; multiValue=${ multiValue }` ,
1053 });
1054 });
1055
1056 return candidates;
1057 }
1058
1059 module . exports = {
1060 LAYOUT_SAMPLE_ROWS,
1061 MIN_LAYOUT_ROWS,
1062 MIN_GROUPS,
1063 SECTION_MAX_LEVELS,
1064 COLUMN_SKEW,
1065 LAYOUT_MARGIN,
1066 CHILD_SKEW,
1067 PARENT_SKEW,
1068 CATEGORY_COLUMN_NAMES,
1069 DERIVED_AUTO_CONFIDENCE,
1070 slug,
1071 columnIndex,
1072 profileColumns,
1073 classifyLayout,
1074 applyOverlayLayout,
1075 verifyOverlayContinuation,
1076 buildGroups,
1077 deriveColumnRoles,
1078 deriveColumnValues,
1079 detectDerivedCandidates,
1080 splitMultiValue,
1081 };