#!/usr/bin/env python3 """ One-time migration: normalize all existing sizes in product_offers.sizes JSONB. Parses raw size strings like "MAAT5,5/UK5/US5,5/EUR38,5" into clean EU sizes like "38.5". Safe to run multiple times — idempotent. Usage: python3 fix-sizes.py # dry run (default) python3 fix-sizes.py --apply # actually update the database """ import sys, os, io, json, re sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace', line_buffering=True) # Add scripts dir to path for importing normalize_size sys.path.insert(0, os.path.dirname(os.path.abspath(__file__))) import psycopg2 import psycopg2.extras from dotenv import load_dotenv load_dotenv(os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '.env')) DB_URL = os.environ["DATABASE_URL"] # Import normalize_size from feed-sync-v4 without executing its main logic # We re-implement it here to avoid side effects VALID_EU_SIZES = set() for _whole in range(17, 53): VALID_EU_SIZES.add(str(_whole)) VALID_EU_SIZES.add(f"{_whole}.5") US_TO_EU = { '3': '35.5', '3.5': '36', '4': '36.5', '4.5': '37', '5': '37.5', '5.5': '38', '6': '38.5', '6.5': '39', '7': '40', '7.5': '40.5', '8': '41', '8.5': '42', '9': '42.5', '9.5': '43', '10': '44', '10.5': '44.5', '11': '45', '11.5': '45.5', '12': '46', '12.5': '46.5', '13': '47.5', '14': '48.5', '15': '49.5', } UK_TO_EU = { '3': '36', '3.5': '36.5', '4': '37', '4.5': '37.5', '5': '38', '5.5': '38.5', '6': '39', '6.5': '40', '7': '40.5', '7.5': '41', '8': '42', '8.5': '42.5', '9': '43', '9.5': '44', '10': '44.5', '10.5': '45', '11': '45.5', '11.5': '46', '12': '47', '13': '48', } def normalize_size(raw): if not raw or not isinstance(raw, str): return None raw = raw.strip() if not raw or raw.lower() in ('one size', 'onesize', 'os', ''): return None s = raw.replace(',', '.') eur_match = re.search(r'(?:EUR?|EU)\s*(\d{2,3}(?:\.\d{1,2})?)', s, re.I) if eur_match: return _clean_eu_size(eur_match.group(1)) if '/' in s: parts = s.split('/') for part in parts: eur_match = re.search(r'(?:EUR?|EU)\s*(\d{2,3}(?:\.\d{1,2})?)', part, re.I) if eur_match: return _clean_eu_size(eur_match.group(1)) for part in parts: maat_match = re.search(r'MAAT\s*(\d{2,3}(?:\.\d{1,2})?)', part, re.I) if maat_match: size = _clean_eu_size(maat_match.group(1)) if size and 17 <= float(size) <= 52: return size for part in parts: us_match = re.search(r'US\s*(\d{1,2}(?:\.\d{1,2})?)', part, re.I) if us_match: eu = US_TO_EU.get(us_match.group(1)) if eu: return eu maat_match = re.search(r'(?:Maat|Size)\s*(\d{2,3}(?:\.\d{1,2})?)', s, re.I) if maat_match: return _clean_eu_size(maat_match.group(1)) frac_match = re.match(r'^(\d{2})\s+(\d)/(\d)\s*$', s.strip()) if frac_match: whole = int(frac_match.group(1)) frac = int(frac_match.group(2)) / int(frac_match.group(3)) return f"{whole}.5" if frac > 0.25 else str(whole) us_only = re.match(r'^US\s*(\d{1,2}(?:\.\d{1,2})?)\s*$', s, re.I) if us_only: return US_TO_EU.get(us_only.group(1)) uk_only = re.match(r'^UK\s*(\d{1,2}(?:\.\d{1,2})?)\s*$', s, re.I) if uk_only: return UK_TO_EU.get(uk_only.group(1)) plain_match = re.match(r'^(\d{2,3}(?:\.\d{1,2})?)\s*$', s.strip()) if plain_match: return _clean_eu_size(plain_match.group(1)) return None def _clean_eu_size(val): try: n = float(val) except (ValueError, TypeError): return None if not (17 <= n <= 52): return None n = round(n * 2) / 2 if n == int(n): return str(int(n)) return f"{n:.1f}" def main(): apply = '--apply' in sys.argv conn = psycopg2.connect(DB_URL, cursor_factory=psycopg2.extras.RealDictCursor) cur = conn.cursor() # Get all product_offers with sizes cur.execute("SELECT id, sizes FROM product_offers WHERE sizes IS NOT NULL AND sizes != '[]'::jsonb") rows = cur.fetchall() print(f"Found {len(rows)} offers with sizes") updated = 0 examples = [] unparseable = {} for row in rows: offer_id = row['id'] sizes = row['sizes'] if not sizes: continue changed = False new_sizes = [] for entry in sizes: old_size = entry.get('size', '') if old_size in ('One Size', 'one size', '', None): new_sizes.append(entry) continue # Check if this is a comma-separated list of multiple sizes # "36,37,38,39" is a list; "42,5" is a Dutch decimal (handled by normalize_size) # Heuristic: it's a list if it has 3+ parts, or 2 parts where both are valid EU sizes if ',' in old_size: parts = [p.strip() for p in old_size.split(',')] # Skip if it looks like a Dutch decimal (e.g. "42,5" — single digit after comma) is_dutch_decimal = len(parts) == 2 and re.match(r'^\d{1,2}$', parts[0]) and re.match(r'^\d$', parts[1]) if not is_dutch_decimal and len(parts) >= 2: normalized_parts = [normalize_size(p) for p in parts] valid_parts = [n for n in normalized_parts if n is not None] if len(valid_parts) >= 2 and len(valid_parts) >= len(parts) * 0.5: for ns in valid_parts: new_entry = dict(entry) new_entry['size'] = ns new_sizes.append(new_entry) changed = True if len(examples) < 30: examples.append(f" {old_size!r} -> split into {len(valid_parts)} sizes") continue normalized = normalize_size(old_size) if normalized and normalized != old_size: new_entry = dict(entry) new_entry['size'] = normalized new_sizes.append(new_entry) changed = True if len(examples) < 30: examples.append(f" {old_size!r} -> {normalized!r}") elif normalized is None and old_size.lower() not in ('one size', 'one_size'): # Could not parse — keep as-is but track new_sizes.append(entry) unparseable[old_size] = unparseable.get(old_size, 0) + 1 else: new_sizes.append(entry) if changed: updated += 1 if apply: cur.execute( "UPDATE product_offers SET sizes = %s WHERE id = %s", (json.dumps(new_sizes), offer_id) ) if examples: print(f"\nExample conversions:") for ex in examples: print(ex) if unparseable: print(f"\nCould not parse {len(unparseable)} unique size strings (top 20):") for size, count in sorted(unparseable.items(), key=lambda x: -x[1])[:20]: print(f" {size!r} ({count}x)") print(f"\nOffers that need updating: {updated} / {len(rows)}") if apply: conn.commit() print("Changes committed to database.") else: conn.rollback() print("DRY RUN — no changes made. Use --apply to commit.") cur.close() conn.close() if __name__ == '__main__': main()