#!/usr/bin/env python3 """ Import script: reads Contentful export folders (offers/, services/), generates SQL INSERT statements for aws_offers / aws_services, and copies assets to import/uploads/{type}/{hash}/ folders. Usage: python3 import_offers_services.py """ import json import os import re import shutil import sys from datetime import datetime BASE_DIR = os.path.dirname(os.path.abspath(__file__)) OUT_DIR = os.path.join(BASE_DIR, "output") SQL_OFFERS_FILE = os.path.join(BASE_DIR, "aws_offers.sql") SQL_SERVICES_FILE = os.path.join(BASE_DIR, "aws_services.sql") OFFERS_SRC = os.path.join(BASE_DIR, "offers") SERVICES_SRC = os.path.join(BASE_DIR, "services") BRAND_MAP = { "Abarth": 3, "AlfaRomeo": 5, "Fiat": 1, "FiatPro": 2, "Fiat Professional": 2, "Hyundai": 6, "Jeep": 4, "KGM": 10, "Maxus": 11, "MgMotor": 9, "MG": 9, "Nissan": 7, } NISSAN_AT_ID = 8 STATUS_MAP = { "draft": "Draft", "published": "Published", "archived": "Internal", } def parse_sql_hashes(sql_path): """Parse existing SQL dump to build {ext_id -> hash} mapping.""" mapping = {} if not os.path.isfile(sql_path): print(f" [warn] SQL file not found: {sql_path}") return mapping with open(sql_path, "r", encoding="utf-8") as f: content = f.read() match = re.search( r"INSERT INTO\s+`?\w+`?\s*\([^)]+\)\s*VALUES\s*(.+?);", content, re.DOTALL | re.IGNORECASE ) if not match: return mapping values_block = match.group(1) rows = [] depth = 0 current = [] for ch in values_block: if ch == "(" and depth == 0: depth = 1 current = [] elif ch == "(": depth += 1 current.append(ch) elif ch == ")": depth -= 1 if depth == 0: rows.append("".join(current)) else: current.append(ch) else: if depth > 0: current.append(ch) for row_str in rows: parts = split_sql_values(row_str) if len(parts) >= 30: hash_val = parts[1].strip("'\" ") if parts[1].strip("'\" ") != "NULL" else None ext_id = parts[25].strip("'\" ") if len(parts) > 25 and parts[25].strip("'\" ") != "NULL" else None if ext_id and hash_val: mapping[ext_id] = hash_val return mapping def split_sql_values(row_str): """Split a SQL value row like '102','hash','Draft',... into parts.""" parts = [] current = [] in_string = False string_char = None i = 0 while i < len(row_str): ch = row_str[i] if in_string: if ch == "\\" and i + 1 < len(row_str): current.append(ch) current.append(row_str[i + 1]) i += 2 continue elif ch == string_char: in_string = False current.append(ch) else: current.append(ch) else: if ch in ("'", '"'): in_string = True string_char = ch current.append(ch) elif ch == ",": parts.append("".join(current).strip()) current = [] else: current.append(ch) i += 1 if current: parts.append("".join(current).strip()) return parts def esc(val): """Basic SQL escaping and quoting.""" if val is None: return "NULL" s = str(val).replace("\\", "\\\\").replace("'", "\\'") return f"'{s}'" def read_json(path): if not os.path.isfile(path): return None with open(path, "r", encoding="utf-8") as f: return json.load(f) def get_locale_val(d, locale, default=""): if not isinstance(d, dict): return default v = d.get(locale) if v is None: return default return str(v) def find_teaser_asset(assets): for a in assets: if a.get("sourceField") == "fields.teaserImage" and a.get("language") == "de": return a for a in assets: if a.get("sourceField") == "fields.teaserImage": return a return None def find_detail_asset(assets): for a in assets: if a.get("sourceField") == "fields.detailsImage" and a.get("language") == "de": return a for a in assets: if a.get("sourceField") == "fields.detailsImage": return a return None def get_id_brands(brand_name, market): key = brand_name.strip() if key == "Nissan" and market == "AT": return NISSAN_AT_ID return BRAND_MAP.get(key, None) def process_item(item_dir, entity_type, hash_map): meta_path = os.path.join(item_dir, "metadata.json") content_path = os.path.join(item_dir, "content.json") meta = read_json(meta_path) content = read_json(content_path) if not meta and not content: print(f" [warn] No metadata.json or content.json in {item_dir}") return None, [] data = meta if meta else {} ctx = (content or {}).get("context", {}) ext_id = data.get("id") or (content or {}).get("id", "") if not ext_id: print(f" [warn] No id in {item_dir}, skipping") return None, [] entity_hash = hash_map.get(ext_id, ext_id) # Skip items that already exist in DB (ext_id was found in SQL hash map) if entity_hash != ext_id: return None, [] raw_status = (data.get("status") or "").lower() status = STATUS_MAP.get(raw_status, "Draft") market = ctx.get("market", "CH") brand_name = ctx.get("brand", "") if not brand_name and data.get("brands"): brand_name = data["brands"][0] id_brands = get_id_brands(brand_name, market) if id_brands is None: print(f" [warn] Unknown brand '{brand_name}' in {item_dir}, skipping") return None, [] energy_label = data.get("energyLabel", {}) energy_str = get_locale_val(energy_label, "de", "") energy = energy_str[0] if energy_str else None title = data.get("teaserHeadline") or data.get("title", {}) teaser_title_de = get_locale_val(title, "de") teaser_title_fr = get_locale_val(title, "fr") teaser_title_it = get_locale_val(title, "it") desc = data.get("teaserSubheadline", {}) teaser_desc_de = get_locale_val(desc, "de") if desc else "" teaser_desc_fr = get_locale_val(desc, "fr") if desc else "" teaser_desc_it = get_locale_val(desc, "it") if desc else "" assets = data.get("assets", []) teaser_asset = find_teaser_asset(assets) detail_asset = find_detail_asset(assets) teaser_image = None teaser_real_name = None teaser_ext = None detail_image = None detail_real_name = None detail_ext = None if teaser_asset: fname = teaser_asset.get("fileName", "") name_no_ext, ext = os.path.splitext(fname) ext = ext.lstrip(".") teaser_image = name_no_ext teaser_ext = ext if ext else None teaser_real_name = teaser_asset.get("title") or fname if detail_asset: fname = detail_asset.get("fileName", "") name_no_ext, ext = os.path.splitext(fname) ext = ext.lstrip(".") detail_image = name_no_ext detail_ext = ext if ext else None detail_real_name = detail_asset.get("title") or fname files_to_copy = [] for a in assets: src_path = os.path.join(item_dir, a.get("path", "")) if os.path.isfile(src_path): files_to_copy.append((src_path, a.get("fileName", ""))) for locale in ("de", "fr", "it"): locale_dir = os.path.join(item_dir, locale, "assets") if os.path.isdir(locale_dir): for fname in os.listdir(locale_dir): src = os.path.join(locale_dir, fname) if os.path.isfile(src): if not any(fn == fname for _, fn in files_to_copy): files_to_copy.append((src, fname)) created_at_str = data.get("createdAt") or "" updated_at_str = data.get("updatedAt") or "" created_at = created_at_str.replace("T", " ").replace("Z", "") if created_at_str else None updated_at = updated_at_str.replace("T", " ").replace("Z", "") if updated_at_str else None row = { "hash": entity_hash, "status": status, "market": market, "id_brands": id_brands, "old_brand": None, "energy": energy, "teaser_title_DE": teaser_title_de, "teaser_title_FR": teaser_title_fr, "teaser_title_IT": teaser_title_it, "teaser_description_DE": teaser_desc_de, "teaser_description_FR": teaser_desc_fr, "teaser_description_IT": teaser_desc_it, "teaser_image": teaser_image, "teaser_real_name_image": teaser_real_name, "teaser_ext_image": teaser_ext, "detail_page_image": detail_image, "detail_page_real_name_image": detail_real_name, "detail_page_ext_image": detail_ext, "created_by": None, "created_at": created_at, "updated_by": None, "updated_at": updated_at, "published_by": None, "published_at": None, "ext_id": ext_id, "legacy": 0, "scheduled_date": None, "global": 1, "prio": 0, } return row, files_to_copy def generate_sql(rows, table_name): columns = [ "hash", "status", "market", "id_brands", "old_brand", "energy", "teaser_title_DE", "teaser_title_FR", "teaser_title_IT", "teaser_description_DE", "teaser_description_FR", "teaser_description_IT", "teaser_image", "teaser_real_name_image", "teaser_ext_image", "detail_page_image", "detail_page_real_name_image", "detail_page_ext_image", "created_by", "created_at", "updated_by", "updated_at", "published_by", "published_at", "ext_id", "legacy", "scheduled_date", "global", "prio", ] now = datetime.now().isoformat() lines = [ f"-- Import for {table_name}", f"-- Generated: {now}", "", f"INSERT INTO `{table_name}` ({', '.join(f'`{c}`' for c in columns)}) VALUES", ] value_rows = [] for row in rows: vals = [esc(row.get(c)) for c in columns] value_rows.append(f" ({', '.join(vals)})") lines.append(",\n".join(value_rows) + ";") lines.append("\n\n-- End of import") return "\n".join(lines) def count_hash_dirs(uploads_dir, entity_type): d = os.path.join(uploads_dir, entity_type) if not os.path.isdir(d): return 0 return len([x for x in os.listdir(d) if os.path.isdir(os.path.join(d, x))]) def main(): print("=== Import Offers & Services ===\n") print("[1/4] Parsing existing SQL hashes...") offer_hash_map = parse_sql_hashes(SQL_OFFERS_FILE) service_hash_map = parse_sql_hashes(SQL_SERVICES_FILE) print(f" Found {len(offer_hash_map)} ext_id->hash mappings in aws_offers.sql") print(f" Found {len(service_hash_map)} ext_id->hash mappings in aws_services.sql") print("\n[2/4] Scanning source folders...") all_offer_rows = [] all_service_rows = [] all_files = [] for entity_type, src_dir, hash_map in [ ("offers", OFFERS_SRC, offer_hash_map), ("services", SERVICES_SRC, service_hash_map), ]: if not os.path.isdir(src_dir): print(f" [warn] Source directory not found: {src_dir}") continue for brand_name in sorted(os.listdir(src_dir)): brand_dir = os.path.join(src_dir, brand_name) if not os.path.isdir(brand_dir) or brand_name.startswith("."): continue for item_name in sorted(os.listdir(brand_dir)): item_dir = os.path.join(brand_dir, item_name) if not os.path.isdir(item_dir) or item_name.startswith("."): continue row, files = process_item(item_dir, entity_type, hash_map) if row is None: continue if entity_type == "offers": all_offer_rows.append(row) else: all_service_rows.append(row) for src_path, fname in files: all_files.append((src_path, f"{entity_type}/{row['hash']}/{fname}")) print(f" Found {len(all_offer_rows)} offers, {len(all_service_rows)} services") print(f" Total assets to copy: {len(all_files)}") print("\n[3/4] Writing output...") try: os.makedirs(OUT_DIR, exist_ok=True) except FileExistsError: pass uploads_dir = os.path.join(OUT_DIR, "uploads") if all_offer_rows: sql = generate_sql(all_offer_rows, "aws_offers") sql_path = os.path.join(OUT_DIR, "aws_offers_import.sql") with open(sql_path, "w", encoding="utf-8") as f: f.write(sql) print(f" SQL: {sql_path} ({len(all_offer_rows)} rows)") if all_service_rows: sql = generate_sql(all_service_rows, "aws_services") sql_path = os.path.join(OUT_DIR, "aws_services_import.sql") with open(sql_path, "w", encoding="utf-8") as f: f.write(sql) print(f" SQL: {sql_path} ({len(all_service_rows)} rows)") copied = 0 errors = 0 for src_path, rel_dst in all_files: dst_path = os.path.join(uploads_dir, rel_dst) os.makedirs(os.path.dirname(dst_path), exist_ok=True) try: shutil.copy2(src_path, dst_path) copied += 1 except Exception as e: print(f" [error] Copy failed: {src_path} -> {dst_path}: {e}") errors += 1 print(f"\n Assets copied: {copied}, errors: {errors}") print("\n[4/4] Summary") print(f" Output dir: {OUT_DIR}") print(f" +-- uploads/offers/ ({count_hash_dirs(uploads_dir, 'offers')} hash dirs)") print(f" +-- uploads/services/ ({count_hash_dirs(uploads_dir, 'services')} hash dirs)") print(f" +-- aws_offers_import.sql") print(f" +-- aws_services_import.sql") print("\nDone.") if __name__ == "__main__": main()