#!/usr/bin/env python3 """Import migration_2026_rows.json into gazenergie DB as report_year=2026.""" import argparse import json import random import string from pathlib import Path import pymysql JSON_PATH = Path(__file__).resolve().parent / "migration_2026_rows.json" REPORT_YEAR = 2026 ADMIN_USER_ID = 1 def connect(): return pymysql.connect( host="127.0.0.1", user="root", password="ra8raffi!", database="gazenergie", charset="utf8mb4", autocommit=False, ) def rand_pw(n=10): chars = "ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnpqrstuvwxyz23456789" return "".join(random.choice(chars) for _ in range(n)) def sanitize_username(v): u = "".join(c for c in (v or "").lower() if c.isalnum() or c in "._-") return (u or f"kontakt{random.randint(1000,9999)}")[:80] def n(v): try: return int(round(float(v or 0))) except (TypeError, ValueError): return 0 def main(): ap = argparse.ArgumentParser() ap.add_argument("--execute", action="store_true", help="Write to DB (default: dry-run)") args = ap.parse_args() rows = json.loads(JSON_PATH.read_text(encoding="utf-8")) print(f"Rows in JSON: {len(rows)} mode={'EXECUTE' if args.execute else 'DRY-RUN'}") conn = connect() cur = conn.cursor() cur.execute("SELECT id, company_name FROM companies WHERE id <> 1") existing = {name.lower().strip(): cid for cid, name in cur.fetchall()} stats = {"created": 0, "matched": 0, "contacts": 0, "imported": 0, "errors": 0, "sector_warn": 0} for row in rows: name = (row.get("companyName") or "").strip() if not name: continue try: sector_sum = ( n(row["sectorPrivate"]) + n(row["sectorAgriculture"]) + n(row["sectorIndustry"]) + n(row["sectorServices"]) + n(row["sectorDistrict"]) + n(row["sectorOther"]) + n(row["sectorFuel"]) + n(row["sectorWkk"]) ) end_kwh = n(row["endconsumersKwh"]) if end_kwh and sector_sum != end_kwh: stats["sector_warn"] += 1 print(f"WARN row {row['row']} {name}: sectors {sector_sum} != end {end_kwh}") if not args.execute: key = name.lower() tag = "MATCH" if key in existing else "NEW" print(f"{tag} row {row['row']}: {name} | {row.get('email')} | {row.get('periodType')} | total={n(row['totalSaleKwh'])}") continue key = name.lower() if key in existing: company_id = existing[key] stats["matched"] += 1 else: cur.execute( """INSERT INTO companies (company_name, location, annual_kwh, company_type, is_active, is_vsg_member, invite_enabled, reminder_enabled) VALUES (%s, '', 0, 'Gasversorger', 1, 1, 1, 1)""", (name,), ) company_id = cur.lastrowid existing[key] = company_id stats["created"] += 1 email = (row.get("email") or "").strip() fn = (row.get("firstName") or "").strip() ln = (row.get("lastName") or "").strip() phone = (row.get("phone") or "").strip() contact_id = None if email: cur.execute( "SELECT id FROM supplier_contacts WHERE company_id=%s AND LOWER(email)=LOWER(%s) LIMIT 1", (company_id, email), ) hit = cur.fetchone() if hit: contact_id = hit[0] if contact_id is None and fn and ln: cur.execute( "SELECT id FROM supplier_contacts WHERE company_id=%s AND LOWER(first_name)=LOWER(%s) AND LOWER(last_name)=LOWER(%s) LIMIT 1", (company_id, fn, ln), ) hit = cur.fetchone() if hit: contact_id = hit[0] mod_scope = "user_data,gas_sectors,gas_kio,gas_structure" if contact_id: cur.execute( """UPDATE supplier_contacts SET first_name=%s, last_name=%s, email=%s, module_scope=%s, is_active=1 WHERE id=%s AND company_id=%s""", (fn, ln, email, mod_scope, contact_id, company_id), ) else: base = sanitize_username(email.split("@")[0] if email else f"{fn}.{ln}") username = base for attempt in range(1, 30): cur.execute("SELECT id FROM supplier_contacts WHERE username=%s LIMIT 1", (username,)) if not cur.fetchone(): break username = f"{base[:70]}{attempt}" cur.execute( """INSERT INTO supplier_contacts (company_id, username, password_hash, first_name, last_name, email, department, module_scope, is_active, invite_enabled, reminder_enabled) VALUES (%s,%s,%s,%s,%s,%s,NULL,%s,1,1,1)""", (company_id, username, rand_pw(), fn, ln, email, mod_scope), ) stats["contacts"] += 1 # import reserve cur.execute( """INSERT INTO company_year_import_reserve (company_id, report_year, supplier_comment, admin_comment, raw_payload) VALUES (%s,%s,'','',%s) ON DUPLICATE KEY UPDATE raw_payload=VALUES(raw_payload), updated_at=CURRENT_TIMESTAMP""", (company_id, REPORT_YEAR, json.dumps({"source": "MigrationDef_20260723", "excelRow": row["row"]}, ensure_ascii=False)), ) # user_data user_payload = { "company_name": name, "company_suffix": row.get("companySuffix") or "", "upstream_operator": row.get("upstreamOperator") or "", "downstream_count": 0, "downstream_names": "", "sectors_contact_firstname": fn, "sectors_contact_lastname": ln, "sectors_contact_email": email, "sectors_contact_phone": phone, "sectors_contact_comment": "", "kio_contact_firstname": fn, "kio_contact_lastname": ln, "kio_contact_email": email, "kio_contact_phone": phone, "kio_contact_comment": "", "structure_contact_firstname": fn, "structure_contact_lastname": ln, "structure_contact_email": email, "structure_contact_phone": phone, "structure_contact_comment": "", "is_completed": 0, "_touched": 1, } cur.execute( """INSERT INTO module_forms (company_id, module_key, report_year, payload_json, is_completed, is_started, updated_by) VALUES (%s,'user_data',%s,%s,0,1,%s) ON DUPLICATE KEY UPDATE payload_json=VALUES(payload_json), is_started=1, updated_by=VALUES(updated_by)""", (company_id, REPORT_YEAR, json.dumps(user_payload, ensure_ascii=False), ADMIN_USER_ID), ) # gas_reports cur.execute( """INSERT INTO gas_reports (company_id, module_key, report_year, created_by, updated_by) VALUES (%s,'gas_sectors',%s,%s,%s) ON DUPLICATE KEY UPDATE updated_by=VALUES(updated_by)""", (company_id, REPORT_YEAR, ADMIN_USER_ID, ADMIN_USER_ID), ) cur.execute( """UPDATE gas_reports SET period_type=%s, total_sale_kwh=%s, resellers_kwh=%s, endconsumers_kwh=%s, grosscustomers_count=0, grosscustomers_total_kwh=0, sector_private_households_kwh=%s, sector_agriculture_kwh=%s, sector_industry_kwh=%s, sector_services_kwh=%s, sector_district_heating_kwh=%s, sector_other_kwh=%s, sector_fuel_kwh=%s, sector_wkk_kwh=%s, biogas_import_total_kwh=%s, biogas_import_lbg_kwh=%s, biogas_import_network_kwh=%s, biogas_import_certificate_kwh=%s, deviation_comment='', is_completed=0, status='draft', updated_by=%s WHERE company_id=%s AND module_key='gas_sectors' AND report_year=%s""", ( row.get("periodType") or "calendar", n(row["totalSaleKwh"]), n(row["resellersKwh"]), end_kwh, n(row["sectorPrivate"]), n(row["sectorAgriculture"]), n(row["sectorIndustry"]), n(row["sectorServices"]), n(row["sectorDistrict"]), n(row["sectorOther"]), n(row["sectorFuel"]), n(row["sectorWkk"]), n(row["biogasTotal"]), n(row["biogasLbg"]), n(row["biogasNetwork"]), n(row["biogasCertificate"]), ADMIN_USER_ID, company_id, REPORT_YEAR, ), ) # structure structure_payload = { "heating_total": n(row["heatingTotal"]), "heating_private": n(row["heatingPrivate"]), "heating_agriculture": n(row["heatingAgriculture"]), "heating_industry": n(row["heatingIndustry"]), "heating_services": n(row["heatingServices"]), "wkk_total": n(row["wkkTotal"]), "wkk_private": n(row["wkkPrivate"]), "wkk_agriculture": n(row["wkkAgriculture"]), "wkk_industry": n(row["wkkIndustry"]), "wkk_services": n(row["wkkServices"]), "gas_meters": n(row["gasMeters"]), "employees": n(row["employees"]), "annual_report_print_count": n(row["annualReportPrintCount"]), "investment_total": n(row["investmentTotal"]), "investment_high_pressure": n(row["investmentHigh"]), "investment_mid_low": n(row["investmentMidLow"]), "investment_storage": n(row["investmentStorage"]), "investment_other": n(row["investmentOther"]), "is_completed": 0, "_touched": 1, } cur.execute( """INSERT INTO module_forms (company_id, module_key, report_year, payload_json, is_completed, is_started, updated_by) VALUES (%s,'gas_structure',%s,%s,0,1,%s) ON DUPLICATE KEY UPDATE payload_json=VALUES(payload_json), is_started=1, updated_by=VALUES(updated_by)""", (company_id, REPORT_YEAR, json.dumps(structure_payload, ensure_ascii=False), ADMIN_USER_ID), ) # kio kio_payload = dict(row.get("kio") or {}) for k, v in list(kio_payload.items()): kio_payload[k] = n(v) kio_payload["deviation_comment"] = "" kio_payload["is_completed"] = 0 kio_payload["_touched"] = 1 cur.execute( """INSERT INTO module_forms (company_id, module_key, report_year, payload_json, is_completed, is_started, updated_by) VALUES (%s,'gas_kio',%s,%s,0,1,%s) ON DUPLICATE KEY UPDATE payload_json=VALUES(payload_json), is_started=1, updated_by=VALUES(updated_by)""", (company_id, REPORT_YEAR, json.dumps(kio_payload, ensure_ascii=False), ADMIN_USER_ID), ) stats["imported"] += 1 except Exception as exc: stats["errors"] += 1 print(f"ERROR row {row.get('row')}: {exc}") if args.execute: conn.rollback() raise if args.execute: conn.commit() print("COMMITTED") else: conn.rollback() print("DRY-RUN (no writes)") print("STATS", stats) if args.execute: cur.execute("SELECT COUNT(*) FROM companies WHERE id<>1") print("companies", cur.fetchone()[0]) cur.execute("SELECT COUNT(*) FROM module_forms WHERE report_year=%s", (REPORT_YEAR,)) print("module_forms_2026", cur.fetchone()[0]) cur.execute("SELECT COUNT(*) FROM gas_reports WHERE report_year=%s", (REPORT_YEAR,)) print("gas_reports_2026", cur.fetchone()[0]) conn.close() if __name__ == "__main__": main()