Legacy Migration Plan

# scripts/migrate_legacy_records.py
import os
import mysql.connector
import psycopg2
from cryptography.hazmat.primitives.ciphers.aead import AESGCM
from datetime import datetime

def execute_legacy_migration_pipeline():
    """
    Production ETL script that extracts legacy data, encrypts patient 
    identifiers, maps fields, and stream-writes to PostgreSQL 16.
    """
    # 1. Initialize local cryptographic keys
    master_key = bytes.fromhex(os.getenv("FIELD_ENCRYPTION_MASTER_KEY"))
    aesgcm = AESGCM(master_key)
    
    # 2. Establish connections to source and target databases
    legacy_conn = mysql.connector.connect(host="192.168.10.30", user="migration", password="*", database="legacy_hmis")
    target_conn = psycopg2.connect("postgres://hmis_master:*@127.0.0.1:5432/hmis_prod")
    
    legacy_cursor = legacy_conn.cursor(dictionary=True)
    target_cursor = target_conn.cursor()
    
    # Stream-fetch legacy records to minimize memory consumption
    legacy_cursor.execute("SELECT patient_id, first_name, last_name, national_id, dob, gender FROM tbl_demographics;")
    
    print("Beginning record migration and field encryption pipeline...")
    
    for row in legacy_cursor:
        nonce = os.urandom(12)
        
        # Encrypt cleartext fields before writing to the database
        encrypted_first_name = nonce + aesgcm.encrypt(nonce, row['first_name'].encode('utf-8'), None)
        encrypted_last_name = nonce + aesgcm.encrypt(nonce, row['last_name'].encode('utf-8'), None)
        
        try:
            target_cursor.execute(
                """
                INSERT INTO master_patient_index (
                    national_id_passport, first_name_encrypted, last_name_encrypted, date_of_birth, gender, primary_phone_hash, created_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s);
                """,
                (
                    row['national_id'],
                    psycopg2.Binary(encrypted_first_name),
                    psycopg2.Binary(encrypted_last_name),
                    row['dob'],
                    row['gender'].upper(),
                    "MIGRATED_PRESERVED_RECORD_HASH",
                    datetime.now()
                )
            )
        except psycopg2.errors.UniqueViolation:
            target_conn.rollback()
            continue # Bypass duplicate entries to preserve data integrity
            
    target_conn.commit()
    print("Migration pipeline completed successfully.")

if __name__ == "__main__":
    execute_legacy_migration_pipeline()