""" Migration: Restore all models that 0006 deleted from Django's state. 0006 ran DeleteModel for: - SasaPayIPNLog (db_table: sasapay_ipn_logs) - SasaPaySTKResult (db_table: sasapay_stk_results) - SasaPayDisbursementResult (db_table: sasapay_disbursement_results) - SasaPayUnknownPayment (db_table: sasapay_unknown_payments) - SMSLog (db_table: sms_logs) On this server NONE of those tables exist in the DB. This migration: 1. Puts all models back into Django's migration state 2. Creates each table only if it doesn't already exist (idempotent) 3. SasaPayIPNLog gets the new processing_status/trans_id fields 4. Backfills trans_id and processing_status from existing data """ from django.db import migrations, models import uuid # Tables and their CREATE statements — all use ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 # with NO explicit collation so MySQL inherits the DB default (avoids collation # mismatch errors between tables). TABLE_DEFINITIONS = { 'sasapay_ipn_logs': """ CREATE TABLE sasapay_ipn_logs ( id CHAR(32) NOT NULL PRIMARY KEY, trans_id VARCHAR(100) NOT NULL DEFAULT '', raw_data LONGTEXT NOT NULL, processing_status VARCHAR(20) NOT NULL DEFAULT 'pending', processing_note TEXT NOT NULL, receipt_number VARCHAR(30) NOT NULL DEFAULT '', created_at DATETIME(6) NOT NULL, processed_at DATETIME(6) NULL, INDEX payments_sasapayipnlog_trans_id (trans_id), INDEX payments_sasapayipnlog_processing_status (processing_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 """, 'sasapay_stk_results': """ CREATE TABLE sasapay_stk_results ( id CHAR(32) NOT NULL PRIMARY KEY, checkout_request_id VARCHAR(100) NOT NULL DEFAULT '', merchant_loan_ref VARCHAR(100) NOT NULL DEFAULT '', result_code VARCHAR(20) NOT NULL, result_desc TEXT NOT NULL, trans_amount DECIMAL(12,2) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 """, 'sasapay_disbursement_results': """ CREATE TABLE sasapay_disbursement_results ( id CHAR(32) NOT NULL PRIMARY KEY, loan_reference VARCHAR(100) NOT NULL DEFAULT '', sasapay_transaction_id VARCHAR(100) NOT NULL DEFAULT '', mpesa_reference VARCHAR(100) NOT NULL DEFAULT '', result_code VARCHAR(20) NOT NULL, result_desc TEXT NOT NULL, trans_amount DECIMAL(12,2) NOT NULL DEFAULT 0, merchant_balance DECIMAL(14,2) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 """, 'sasapay_unknown_payments': """ CREATE TABLE sasapay_unknown_payments ( id CHAR(32) NOT NULL PRIMARY KEY, amount DECIMAL(12,2) NOT NULL, paid_by VARCHAR(200) NOT NULL DEFAULT '', msisdn VARCHAR(20) NOT NULL DEFAULT '', bill_ref VARCHAR(100) NOT NULL DEFAULT '', reference VARCHAR(100) NOT NULL DEFAULT '', notes TEXT NOT NULL, resolved TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 """, 'sms_logs': """ CREATE TABLE sms_logs ( id CHAR(32) NOT NULL PRIMARY KEY, sms_type VARCHAR(30) NOT NULL DEFAULT 'other', recipients TEXT NOT NULL, message TEXT NOT NULL, sender_id VARCHAR(20) NOT NULL DEFAULT 'HavGrazuri', status VARCHAR(20) NOT NULL DEFAULT 'sent', at_response LONGTEXT NULL, error_message TEXT NOT NULL, loan_number VARCHAR(30) NOT NULL DEFAULT '', borrower_name VARCHAR(200) NOT NULL DEFAULT '', amount DECIMAL(12,2) NULL, created_at DATETIME(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 """, } def create_missing_tables(apps, schema_editor): """Create each table only if it doesn't already exist.""" with schema_editor.connection.cursor() as cursor: cursor.execute(""" SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() """) existing = {row[0] for row in cursor.fetchall()} for table_name, ddl in TABLE_DEFINITIONS.items(): if table_name not in existing: cursor.execute(ddl) else: # Table exists — ensure the new IPN log columns are present if table_name == 'sasapay_ipn_logs': cursor.execute(""" SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sasapay_ipn_logs' """) existing_cols = {row[0] for row in cursor.fetchall()} new_cols = [ ('trans_id', "VARCHAR(100) NOT NULL DEFAULT ''"), ('processing_status', "VARCHAR(20) NOT NULL DEFAULT 'pending'"), ('processing_note', "TEXT NOT NULL"), ('receipt_number', "VARCHAR(30) NOT NULL DEFAULT ''"), ('processed_at', "DATETIME(6) NULL"), ] for col, defn in new_cols: if col not in existing_cols: cursor.execute( f"ALTER TABLE sasapay_ipn_logs ADD COLUMN {col} {defn}" ) # Add indexes if missing cursor.execute(""" SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sasapay_ipn_logs' """) existing_idx = {row[0] for row in cursor.fetchall()} if 'payments_sasapayipnlog_trans_id' not in existing_idx: cursor.execute( "ALTER TABLE sasapay_ipn_logs " "ADD INDEX payments_sasapayipnlog_trans_id (trans_id)" ) if 'payments_sasapayipnlog_processing_status' not in existing_idx: cursor.execute( "ALTER TABLE sasapay_ipn_logs " "ADD INDEX payments_sasapayipnlog_processing_status " "(processing_status)" ) def backfill_ipn_data(apps, schema_editor): """ Backfill trans_id and processing_status for existing IPN log rows. No-ops if the table is empty. Detects DB collation at runtime to avoid collation mismatch errors. """ with schema_editor.connection.cursor() as cursor: # Check there are actually rows to backfill cursor.execute("SELECT COUNT(*) FROM sasapay_ipn_logs") if cursor.fetchone()[0] == 0: return # Detect DB collation for safe cross-table comparisons cursor.execute("SELECT @@collation_database") col = cursor.fetchone()[0] # Pull TransID from JSON blob into the indexed column cursor.execute(""" UPDATE sasapay_ipn_logs SET trans_id = COALESCE( JSON_UNQUOTE(JSON_EXTRACT(raw_data, '$.TransID')), '' ) WHERE trans_id = '' """) # Mark IPNs that already have a repayment cursor.execute(f""" UPDATE sasapay_ipn_logs l SET l.processing_status = 'processed', l.receipt_number = COALESCE( (SELECT r.receipt_number FROM repayments r WHERE r.mpesa_transaction_id COLLATE {col} = l.trans_id COLLATE {col} LIMIT 1), '' ), l.processed_at = l.created_at WHERE l.trans_id != '' AND l.processing_status = 'pending' AND EXISTS ( SELECT 1 FROM repayments r WHERE r.mpesa_transaction_id COLLATE {col} = l.trans_id COLLATE {col} ) """) # Backfill legacy embedded drop notes for status, pattern in [ ('no_match', '%no_borrower%'), ('no_loan', '%no_active_loan%'), ('no_balance', '%no_balance%'), ]: cursor.execute(""" UPDATE sasapay_ipn_logs SET processing_status = %s, processing_note = COALESCE( JSON_UNQUOTE(JSON_EXTRACT(raw_data, '$._processing_note')), '') WHERE processing_status = 'pending' AND JSON_UNQUOTE(JSON_EXTRACT(raw_data, '$._processing_note')) LIKE %s """, [status, pattern]) def drop_created_tables(apps, schema_editor): """Reverse migration.""" with schema_editor.connection.cursor() as cursor: for table in TABLE_DEFINITIONS: cursor.execute(f"DROP TABLE IF EXISTS {table}") class Migration(migrations.Migration): dependencies = [ ('payments', '0006_delete_sasapaydisbursementresult_and_more'), ] operations = [ # ── 1. Restore all models into Django's migration state ──────────── # database_operations=[] — actual DB work is done by RunPython below. migrations.SeparateDatabaseAndState( state_operations=[ migrations.CreateModel( name='SasaPayIPNLog', fields=[ ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)), ('trans_id', models.CharField(max_length=100, blank=True, default='', db_index=True)), ('raw_data', models.JSONField()), ('processing_status', models.CharField( max_length=20, default='pending', db_index=True, choices=[ ('pending', 'Pending'), ('processed', 'Processed — repayment created'), ('no_match', 'No matching borrower'), ('no_loan', 'Borrower has no active loan'), ('no_balance', 'Loan fully paid / zero balance'), ('duplicate', 'Duplicate — already processed'), ('failed', 'Processing error'), ], )), ('processing_note', models.TextField(blank=True)), ('receipt_number', models.CharField(max_length=30, blank=True, default='')), ('created_at', models.DateTimeField(auto_now_add=True)), ('processed_at', models.DateTimeField(null=True, blank=True)), ], options={'db_table': 'sasapay_ipn_logs', 'ordering': ['-created_at']}, ), migrations.CreateModel( name='SasaPaySTKResult', fields=[ ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)), ('checkout_request_id', models.CharField(max_length=100, blank=True)), ('merchant_loan_ref', models.CharField(max_length=100, blank=True)), ('result_code', models.CharField(max_length=20)), ('result_desc', models.TextField(blank=True)), ('trans_amount', models.DecimalField(max_digits=12, decimal_places=2, default=0)), ('created_at', models.DateTimeField(auto_now_add=True)), ], options={'db_table': 'sasapay_stk_results', 'ordering': ['-created_at']}, ), migrations.CreateModel( name='SasaPayDisbursementResult', fields=[ ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)), ('loan_reference', models.CharField(max_length=100, blank=True)), ('sasapay_transaction_id', models.CharField(max_length=100, blank=True)), ('mpesa_reference', models.CharField(max_length=100, blank=True)), ('result_code', models.CharField(max_length=20)), ('result_desc', models.TextField(blank=True)), ('trans_amount', models.DecimalField(max_digits=12, decimal_places=2, default=0)), ('merchant_balance', models.DecimalField(max_digits=14, decimal_places=2, default=0)), ('created_at', models.DateTimeField(auto_now_add=True)), ], options={'db_table': 'sasapay_disbursement_results', 'ordering': ['-created_at']}, ), migrations.CreateModel( name='SasaPayUnknownPayment', fields=[ ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)), ('amount', models.DecimalField(max_digits=12, decimal_places=2)), ('paid_by', models.CharField(max_length=200, blank=True)), ('msisdn', models.CharField(max_length=20, blank=True)), ('bill_ref', models.CharField(max_length=100, blank=True)), ('reference', models.CharField(max_length=100, blank=True)), ('notes', models.TextField(blank=True, default='Unrecognised payment')), ('resolved', models.BooleanField(default=False)), ('created_at', models.DateTimeField(auto_now_add=True)), ], options={'db_table': 'sasapay_unknown_payments', 'ordering': ['-created_at']}, ), migrations.CreateModel( name='SMSLog', fields=[ ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False)), ('sms_type', models.CharField(max_length=30, default='other')), ('recipients', models.TextField()), ('message', models.TextField()), ('sender_id', models.CharField(max_length=20, default='HavGrazuri')), ('status', models.CharField(max_length=20, default='sent')), ('at_response', models.JSONField(blank=True, null=True)), ('error_message', models.TextField(blank=True)), ('loan_number', models.CharField(max_length=30, blank=True)), ('borrower_name', models.CharField(max_length=200, blank=True)), ('amount', models.DecimalField(max_digits=12, decimal_places=2, null=True, blank=True)), ('created_at', models.DateTimeField(auto_now_add=True)), ], options={'db_table': 'sms_logs', 'ordering': ['-created_at']}, ), ], database_operations=[], ), # ── 2. Create missing tables / add missing columns ───────────────── migrations.RunPython( create_missing_tables, reverse_code=drop_created_tables, ), # ── 3. Backfill IPN log data ─────────────────────────────────────── migrations.RunPython( backfill_ipn_data, reverse_code=migrations.RunPython.noop, ), ]