#!/usr/bin/env python3 """ scripts/migrate_global_tsys_to_merchants.py ============================================ PRD-004 HWMP-411 — One-time seed: TSYS credentials for every active merchant. What this script does --------------------- 1. Ensures the payment_providers row (slug='tsys') exists and is up-to-date. 2. Calls TSYS GenerateKey with HW_TSYS_* credentials from .env to obtain a transaction_key (skip with --stub for local dev / CI). 3. Encrypts merchant_id, device_id, developer_id, transaction_key with Fernet using CREDENTIAL_ENCRYPTION_KEY from .env. 4. For EVERY active merchant (deleted_at IS NULL): - Upserts a merchant_provider_configs row (onboarding_status='active') - Replaces merchant_provider_credentials rows for the four stored keys - Sets merchants.active_provider_id = 5. Prints a summary and — in --sql-only mode — prints the raw SQL instead of running it, so you can inspect or run it manually. Usage ----- cd hubwallet-api # Real TSYS API call (staging/prod) python scripts/migrate_global_tsys_to_merchants.py # Stub transaction key (dev / CI, no TSYS API call) python scripts/migrate_global_tsys_to_merchants.py --stub # Dry-run: print SQL without executing python scripts/migrate_global_tsys_to_merchants.py --stub --dry-run # Restrict to specific merchant IDs python scripts/migrate_global_tsys_to_merchants.py --stub --merchant-ids 1,2,3 Required .env variables ----------------------- CREDENTIAL_ENCRYPTION_KEY — Fernet key (base64); e.g. m3yBeRHxRX... DATABASE_URL — PostgreSQL DSN HW_TSYS_MID — TSYS Merchant ID for the platform account HW_TSYS_DEVICE_ID — TSYS Device ID HW_TSYS_DEVELOPER_ID — TSYS Developer ID Required only when --stub is NOT used: HW_TSYS_USER_ID — TSYS User ID (ephemeral; used for GenerateKey) HW_TSYS_PASSWORD — TSYS Password (ephemeral; used for GenerateKey) TSYS_API_BASE_URL — TransIT API base URL Credential keys written to merchant_provider_credentials --------------------------------------------------------- merchant_id → HW_TSYS_MID device_id → HW_TSYS_DEVICE_ID developer_id → HW_TSYS_DEVELOPER_ID transaction_key → returned by GenerateKey (or stub value) user_id and password are NEVER stored. """ import argparse import asyncio import json import os import secrets import sys from datetime import datetime, timezone from pathlib import Path # ── Locate project root and load .env ──────────────────────────────────────── _ROOT = Path(__file__).parent.parent.resolve() sys.path.insert(0, str(_ROOT)) try: from dotenv import load_dotenv load_dotenv(_ROOT / ".env") except ImportError: # python-dotenv not installed; env vars must be set externally pass # ── Read config from environment ────────────────────────────────────────────── def _env(key: str, default: str = "") -> str: return os.environ.get(key, default).strip() DATABASE_URL = _env("DATABASE_URL") CREDENTIAL_ENC_KEY = _env("CREDENTIAL_ENCRYPTION_KEY") TSYS_API_BASE_URL = _env("TSYS_API_BASE_URL", "https://stagegw.transnox.com/servlets/Transnox_API_server") HW_TSYS_MID = _env("HW_TSYS_MID") HW_TSYS_DEVICE_ID = _env("HW_TSYS_DEVICE_ID") HW_TSYS_DEVELOPER_ID = _env("HW_TSYS_DEVELOPER_ID") HW_TSYS_USER_ID = _env("HW_TSYS_USER_ID") HW_TSYS_PASSWORD = _env("HW_TSYS_PASSWORD") PAYMENT_STUB = _env("PAYMENT_PROVIDER_STUB", "false").lower() in {"true", "1", "yes"} # TSYS onboarding schema — v2 (includes developer_id, per PRD-004 tsys004 migration) TSYS_ONBOARDING_SCHEMA_V2 = { "fields": [ {"key": "merchant_id", "label": "TSYS Merchant ID", "type": "text", "required": True, "ephemeral": False, "help_text": "Your TSYS MID"}, {"key": "device_id", "label": "Device ID", "type": "text", "required": True, "ephemeral": False, "help_text": "24-character TSYS device identifier"}, {"key": "developer_id", "label": "Developer ID", "type": "text", "required": True, "ephemeral": False, "help_text": "TSYS Developer ID"}, {"key": "user_id", "label": "User ID", "type": "text", "required": True, "ephemeral": True, "help_text": "TransIT MultiPASS user ID — ephemeral, never stored"}, {"key": "password", "label": "Password", "type": "password", "required": True, "ephemeral": True, "help_text": "TSYS account password — ephemeral, never stored"}, ] } # ── Helpers ─────────────────────────────────────────────────────────────────── def _encrypt(value: str) -> str: """Fernet-encrypt a plain-text credential value.""" from cryptography.fernet import Fernet key = CREDENTIAL_ENC_KEY.encode() if isinstance(CREDENTIAL_ENC_KEY, str) else CREDENTIAL_ENC_KEY return Fernet(key).encrypt(value.encode()).decode() def _validate(stub: bool) -> None: errors = [] if not DATABASE_URL: errors.append("DATABASE_URL is not set") if not CREDENTIAL_ENC_KEY: errors.append("CREDENTIAL_ENCRYPTION_KEY is not set") if not HW_TSYS_MID: errors.append("HW_TSYS_MID is not set") if not HW_TSYS_DEVICE_ID: errors.append("HW_TSYS_DEVICE_ID is not set") if not HW_TSYS_DEVELOPER_ID: errors.append("HW_TSYS_DEVELOPER_ID is not set") if not stub: if not HW_TSYS_USER_ID: errors.append("HW_TSYS_USER_ID is not set (required unless --stub)") if not HW_TSYS_PASSWORD: errors.append("HW_TSYS_PASSWORD is not set (required unless --stub)") if not TSYS_API_BASE_URL: errors.append("TSYS_API_BASE_URL is not set (required unless --stub)") if errors: print("ERROR — missing required configuration:") for e in errors: print(f" • {e}") sys.exit(1) def _db_connect(): """Return a psycopg2-compatible connection. Works with both psycopg2 and psycopg v3.""" # Strip the SQLAlchemy driver prefix so psycopg can parse the DSN dsn = (DATABASE_URL .replace("postgresql+psycopg://", "postgresql://") .replace("postgresql+psycopg2://", "postgresql://")) try: import psycopg2 conn = psycopg2.connect(dsn) except ImportError: import psycopg conn = psycopg.connect(dsn) conn.autocommit = False return conn # ── SQL generation (for --dry-run / --sql-only output) ─────────────────────── def _sql_preview( enc_mid: str, enc_device: str, enc_dev: str, enc_txn: str, key_ts: str, merchant_ids: list[int], ) -> str: schema_json = json.dumps(json.dumps(TSYS_ONBOARDING_SCHEMA_V2)) # double-encode for display lines = [ "-- ═══════════════════════════════════════════════════════════════", "-- TSYS Seed SQL (generated by migrate_global_tsys_to_merchants.py)", f"-- Generated at: {key_ts}", "-- NOTE: credential_value columns contain Fernet-encrypted ciphertext.", "-- ═══════════════════════════════════════════════════════════════", "", "-- 1. Seed / update payment_providers row for TSYS", "INSERT INTO payment_providers", " (name, slug, description, is_active, is_default,", " supported_payment_methods, onboarding_schema, created_at, updated_at)", "VALUES (", " 'TSYS', 'tsys', 'TSYS TransIT payment processing', true, false,", " '{\"card\": true, \"ach\": true}'::json,", f" {schema_json}::json,", " NOW(), NOW()", ")", "ON CONFLICT (slug) DO UPDATE", " SET is_active = true,", " onboarding_schema = EXCLUDED.onboarding_schema,", " updated_at = NOW();", "", "-- 2. Capture TSYS provider id", "-- (used in subsequent statements via a CTE or psql variable)", "", ] lines += [ "DO $$", "DECLARE", " v_provider_id INT;", " v_config_id INT;", " v_key_ts TEXT := " + repr(key_ts) + ";", " v_enc_mid TEXT := " + repr(enc_mid) + ";", " v_enc_device TEXT := " + repr(enc_device) + ";", " v_enc_dev TEXT := " + repr(enc_dev) + ";", " v_enc_txn TEXT := " + repr(enc_txn) + ";", " v_mid INT;", "BEGIN", " SELECT id INTO v_provider_id", " FROM payment_providers WHERE slug = 'tsys' AND deleted_at IS NULL;", "", " IF v_provider_id IS NULL THEN", " RAISE EXCEPTION 'TSYS payment_providers row not found — run the INSERT above first';", " END IF;", "", ] if merchant_ids: for mid in merchant_ids: lines += _merchant_block_sql(mid) else: lines += [ " -- Iterate over ALL active merchants", " FOR v_mid IN", " SELECT id FROM merchants WHERE deleted_at IS NULL ORDER BY id", " LOOP", ] lines += [" " + ln for ln in _merchant_loop_sql()] lines += [ " END LOOP;", ] lines += [ "END $$;", "", "-- Done.", ] return "\n".join(lines) def _merchant_block_sql(merchant_id: int) -> list[str]: return [ f" -- Merchant {merchant_id}", f" v_mid := {merchant_id};", ] + _merchant_loop_sql() + [""] def _merchant_loop_sql() -> list[str]: return [ " INSERT INTO merchant_provider_configs", " (merchant_id, provider_id, is_active, onboarding_status, config_data, created_at, updated_at)", " VALUES", " (v_mid, v_provider_id, true, 'active',", " json_build_object('key_generated_at', v_key_ts)::json,", " NOW(), NOW())", " ON CONFLICT (merchant_id, provider_id) DO UPDATE", " SET onboarding_status = 'active',", " config_data = EXCLUDED.config_data,", " updated_at = NOW()", " RETURNING id INTO v_config_id;", "", " -- Remove any stale credentials", " DELETE FROM merchant_provider_credentials", " WHERE merchant_provider_config_id = v_config_id;", "", " -- Insert 4 encrypted credential rows", " INSERT INTO merchant_provider_credentials", " (merchant_provider_config_id, credential_key, credential_value, created_at, updated_at)", " VALUES", " (v_config_id, 'merchant_id', v_enc_mid, NOW(), NOW()),", " (v_config_id, 'device_id', v_enc_device, NOW(), NOW()),", " (v_config_id, 'developer_id', v_enc_dev, NOW(), NOW()),", " (v_config_id, 'transaction_key', v_enc_txn, NOW(), NOW())", " ON CONFLICT (merchant_provider_config_id, credential_key) DO UPDATE", " SET credential_value = EXCLUDED.credential_value,", " updated_at = NOW();", "", " -- Set merchant's active_provider_id to TSYS", " UPDATE merchants", " SET active_provider_id = v_provider_id,", " updated_at = NOW()", " WHERE id = v_mid", " AND (active_provider_id IS DISTINCT FROM v_provider_id);", ] # ── Main logic ──────────────────────────────────────────────────────────────── async def run(stub: bool, dry_run: bool, merchant_ids: list[int]) -> None: _validate(stub) # Step 1: Get transaction_key if stub or PAYMENT_STUB: txn_key = f"STUB_TXN_KEY_{secrets.token_hex(8)}" print(f"[stub] Using stub transaction_key (real TSYS API NOT called)") else: print(f"[tsys] Calling GenerateKey MID={HW_TSYS_MID} DEVELOPER={HW_TSYS_DEVELOPER_ID}") from src.core.providers.implementations.tsys import TSYSProvider # noqa txn_key = await TSYSProvider._generate_transaction_key( tsys_merchant_id=HW_TSYS_MID, user_id=HW_TSYS_USER_ID, password=HW_TSYS_PASSWORD, developer_id=HW_TSYS_DEVELOPER_ID, api_base_url=TSYS_API_BASE_URL, ) print(f"[tsys] GenerateKey OK — key starts with: {txn_key[:6]}…") key_ts = datetime.now(timezone.utc).isoformat() # Step 2: Fernet-encrypt all stored credential values enc_mid = _encrypt(HW_TSYS_MID) enc_device = _encrypt(HW_TSYS_DEVICE_ID) enc_dev = _encrypt(HW_TSYS_DEVELOPER_ID) enc_txn = _encrypt(txn_key) print("[crypto] Credentials encrypted with Fernet (CREDENTIAL_ENCRYPTION_KEY).") if dry_run: sql = _sql_preview(enc_mid, enc_device, enc_dev, enc_txn, key_ts, merchant_ids) print("\n" + "═" * 70) print("DRY-RUN — SQL that would be executed:") print("═" * 70) print(sql) return # Step 3: Execute against the database conn = _db_connect() cur = conn.cursor() try: # 3a. Seed / update the payment_providers row cur.execute(""" INSERT INTO payment_providers ( name, slug, description, is_active, is_default, supported_payment_methods, onboarding_schema, created_at, updated_at ) VALUES ( 'TSYS', 'tsys', 'TSYS TransIT payment processing', true, false, '{"card": true, "ach": true}', %s, NOW(), NOW() ) ON CONFLICT (slug) DO UPDATE SET is_active = true, onboarding_schema = EXCLUDED.onboarding_schema, updated_at = NOW() """, (json.dumps(TSYS_ONBOARDING_SCHEMA_V2),)) print("[db] payment_providers — TSYS row upserted.") cur.execute( "SELECT id FROM payment_providers WHERE slug = 'tsys' AND deleted_at IS NULL" ) row = cur.fetchone() if not row: raise RuntimeError("Could not fetch TSYS provider id after insert.") tsys_provider_id = row[0] print(f"[db] TSYS provider id = {tsys_provider_id}") # 3b. Determine which merchants to seed if merchant_ids: placeholders = ", ".join(["%s"] * len(merchant_ids)) cur.execute( f"SELECT id FROM merchants WHERE id IN ({placeholders}) AND deleted_at IS NULL ORDER BY id", merchant_ids, ) else: cur.execute("SELECT id FROM merchants WHERE deleted_at IS NULL ORDER BY id") target_ids = [r[0] for r in cur.fetchall()] print(f"[db] Seeding {len(target_ids)} merchant(s)…") config_data_json = json.dumps({"key_generated_at": key_ts}) for mid in target_ids: # 3c. Upsert merchant_provider_configs cur.execute(""" INSERT INTO merchant_provider_configs (merchant_id, provider_id, is_active, onboarding_status, config_data, created_at, updated_at) VALUES (%s, %s, true, 'active', %s, NOW(), NOW()) ON CONFLICT (merchant_id, provider_id) DO UPDATE SET onboarding_status = 'active', config_data = EXCLUDED.config_data, updated_at = NOW() RETURNING id """, (mid, tsys_provider_id, config_data_json)) mpc_id = cur.fetchone()[0] # 3d. Replace credentials (delete-then-insert for clean state) cur.execute( "DELETE FROM merchant_provider_credentials WHERE merchant_provider_config_id = %s", (mpc_id,), ) cur.executemany(""" INSERT INTO merchant_provider_credentials (merchant_provider_config_id, credential_key, credential_value, created_at, updated_at) VALUES (%s, %s, %s, NOW(), NOW()) ON CONFLICT (merchant_provider_config_id, credential_key) DO UPDATE SET credential_value = EXCLUDED.credential_value, updated_at = NOW() """, [ (mpc_id, "merchant_id", enc_mid), (mpc_id, "device_id", enc_device), (mpc_id, "developer_id", enc_dev), (mpc_id, "transaction_key", enc_txn), ]) # 3e. Point merchant.active_provider_id at TSYS cur.execute(""" UPDATE merchants SET active_provider_id = %s, updated_at = NOW() WHERE id = %s AND (active_provider_id IS DISTINCT FROM %s) """, (tsys_provider_id, mid, tsys_provider_id)) print(f"[db] merchant {mid}: config mpc_id={mpc_id} credentials written active_provider_id set") conn.commit() print() print("═" * 60) print(f" Done. Seeded {len(target_ids)} merchant(s).") print(f" key_generated_at : {key_ts}") if not (stub or PAYMENT_STUB): print(" IMPORTANT: TSYS transaction_key is short-lived.") print(" Rotate it before high-volume processing by re-running this") print(" script or using the admin API.") print("═" * 60) except Exception as exc: conn.rollback() print(f"\n[ERROR] {exc}") import traceback traceback.print_exc() sys.exit(1) finally: cur.close() conn.close() def main() -> None: parser = argparse.ArgumentParser( description="Seed TSYS credentials for all active merchants (PRD-004 HWMP-411)", formatter_class=argparse.RawDescriptionHelpFormatter, ) parser.add_argument( "--stub", action="store_true", help="Skip the real TSYS GenerateKey call; use a stub transaction_key (dev/CI only)", ) parser.add_argument( "--dry-run", action="store_true", help="Print the SQL that would be executed without making DB changes", ) parser.add_argument( "--merchant-ids", default="", metavar="1,2,3", help="Comma-separated list of merchant IDs to seed (default: all active merchants)", ) args = parser.parse_args() merchant_ids: list[int] = [] if args.merchant_ids.strip(): try: merchant_ids = [int(x.strip()) for x in args.merchant_ids.split(",") if x.strip()] except ValueError: print("ERROR: --merchant-ids must be a comma-separated list of integers") sys.exit(1) asyncio.run(run(stub=args.stub, dry_run=args.dry_run, merchant_ids=merchant_ids)) if __name__ == "__main__": main()