""" Management command: migrate_legacy_scholarships ================================================ Migrates scholarship and fellowship data from ruforum_rims_db (MySQL): Phase A — Scholarship calls (calls_call → GrantCall, call_type=scholarship) Phase B — Scholarship applications (scholarships_scholarshipapplication + scholarships_mastercardscholarshipapplication → Application + ScholarshipApplicationProfile) Phase C — GTA applications (calls_call scholarship_type=GTA → Application + ScholarshipApplicationProfile) Phase D — Fellowship profiles (back-fill FellowshipApplicationProfile for the 7 fellowship Applications already migrated) Phase E — GRG scholar chain fix (re-link scholars to per-call Scholarship programmes) Usage: docker compose run --rm web python manage.py migrate_legacy_scholarships docker compose run --rm web python manage.py migrate_legacy_scholarships --phase A docker compose run --rm web python manage.py migrate_legacy_scholarships --dry-run """ from __future__ import annotations import re from datetime import date as date_cls, datetime as datetime_cls, timezone as dt_timezone from decimal import Decimal import MySQLdb from django.contrib.auth.hashers import make_password from django.core.management.base import BaseCommand, CommandError from django.db import transaction from django.utils import timezone from django.utils.text import slugify from apps.core.legacy_db import LEGACY_DB LEGACY_TAG = "LEGACY_MIGRATED" STATE_MAP = { "submitted": "submitted", "validated": "shortlisted", "noncompliant": "ineligible", "selected_for_funding": "awarded", "rejected": "rejected", "home_validation": "submitted", # in-progress screening → stays submitted "draft": "draft", } def _dt(val): if val is None: return timezone.now() if isinstance(val, datetime_cls): return val.replace(tzinfo=dt_timezone.utc) if val.tzinfo is None else val if isinstance(val, date_cls): return datetime_cls.combine(val, datetime_cls.min.time()).replace(tzinfo=dt_timezone.utc) try: return datetime_cls.strptime(str(val), "%Y-%m-%d").replace(tzinfo=dt_timezone.utc) except (ValueError, TypeError): return timezone.now() def _bool(val): if val is None: return None return bool(val) def _str(val, maxlen=None): s = (val or "").strip() if maxlen: s = s[:maxlen] return s def _smallint(val, default=None): """Clamp value to PositiveSmallIntegerField range (0–32767), return None if blank.""" if val is None: return default try: v = int(val) return max(0, min(v, 32767)) except (ValueError, TypeError): return default class Command(BaseCommand): help = "Migrate scholarship/fellowship data from legacy ruforum_rims_db" def add_arguments(self, parser): parser.add_argument( "--phase", nargs="+", choices=["A", "B", "C", "D", "E"], default=["A", "B", "C", "D", "E"], ) parser.add_argument("--dry-run", action="store_true") def handle(self, *args, **options): self.dry_run = options["dry_run"] if self.dry_run: self.stdout.write(self.style.WARNING("DRY RUN — no data will be written.\n")) try: self.conn = MySQLdb.connect(**LEGACY_DB) except Exception as exc: raise CommandError(f"Cannot connect to legacy MySQL: {exc}") from exc self.cur = self.conn.cursor(MySQLdb.cursors.DictCursor) self._build_maps() runners = { "A": self._phase_a_calls, "B": self._phase_b_scholarship_apps, "C": self._phase_c_gta_apps, "D": self._phase_d_fellowship_profiles, "E": self._phase_e_scholar_chain, } for phase in options["phase"]: self.stdout.write(self.style.MIGRATE_HEADING(f"\n=== Phase {phase} ===")) runners[phase]() self.cur.close() self.conn.close() self.stdout.write(self.style.SUCCESS("\nDone.")) # ── Maps ───────────────────────────────────────────────────────────── def _build_maps(self): from apps.core.authentication.models import CustomUser from apps.rims.grants.models import GrantCall # legacy contacts_user.id → CustomUser.pk self.cur.execute("SELECT id, business_email as email FROM contacts_user") legacy_emails = {r["id"]: (r["email"] or "").strip().lower() for r in self.cur.fetchall()} email_to_pk = {u.email: u.pk for u in CustomUser.objects.only("id", "email")} self.user_map = {uid: email_to_pk[e] for uid, e in legacy_emails.items() if e in email_to_pk} # existing_emails for new user creation self.existing_emails: set[str] = set(email_to_pk.keys()) # legacy calls_call.id → GrantCall.pk (after Phase A runs) self.scholarship_call_map: dict[int, int] = {} for gc in GrantCall.objects.filter(description__contains="[legacy_schcall_id:"): m = re.search(r"\[legacy_schcall_id:(\d+)\]", gc.description) if m: self.scholarship_call_map[int(m.group(1))] = gc.pk # legacy calls_grantcall.id → GrantCall.pk (for fellowship call D) self.grantcall_map: dict[int, int] = {} for gc in GrantCall.objects.filter(description__contains="[legacy_call_id:"): m = re.search(r"\[legacy_call_id:(\d+)\]", gc.description) if m: self.grantcall_map[int(m.group(1))] = gc.pk self.stdout.write( f" Maps: {len(self.user_map)} users, " f"{len(self.scholarship_call_map)} scholarship calls already migrated, " f"{len(self.grantcall_map)} grant calls" ) def _get_or_create_user(self, legacy_id, first_name="", last_name="", email_override=None): """Return CustomUser.pk — create if not in map.""" from apps.core.authentication.models import CustomUser if legacy_id in self.user_map: return self.user_map[legacy_id] self.cur.execute( "SELECT id, business_email as email, first_name, last_name, is_active " "FROM contacts_user WHERE id = %s", (legacy_id,), ) row = self.cur.fetchone() if not row: return None email = (email_override or row.get("email") or "").strip().lower() if not email: return None # Deduplicate if email in self.existing_emails: existing = CustomUser.objects.filter(email=email).first() if existing: self.user_map[legacy_id] = existing.pk return existing.pk # suffix base, domain = email.split("@") if "@" in email else (email, "ruforum.org") i = 2 while email in self.existing_emails: email = f"{base}-{i}@{domain}" i += 1 if self.dry_run: return None # can't create in dry-run user = CustomUser.objects.create( email=email, first_name=_str(first_name or row.get("first_name"), 150), last_name=_str(last_name or row.get("last_name"), 150), is_active=bool(row.get("is_active", True)), password=make_password(None), ) self.existing_emails.add(email) self.user_map[legacy_id] = user.pk return user.pk # ── Phase A — Scholarship calls ─────────────────────────────────────── def _phase_a_calls(self): from apps.rims.grants.models import FundingRecord, GrantCall from apps.core.authentication.models import CustomUser admin = CustomUser.objects.filter(is_superuser=True).first() self.cur.execute("SELECT * FROM calls_call WHERE scholarship_type != '' ORDER BY id") rows = self.cur.fetchall() created = skipped = 0 existing_slugs: set[str] = set(GrantCall.objects.values_list("slug", flat=True)) for row in rows: tag = f"[legacy_schcall_id:{row['id']}]" if GrantCall.objects.filter(description__contains=tag).exists(): gc = GrantCall.objects.get(description__contains=tag) self.scholarship_call_map[row["id"]] = gc.pk skipped += 1 continue title = _str(row["title"]) or f"Legacy Scholarship Call #{row['id']}" stype = _str(row.get("scholarship_type") or "other") # Map scholarship_type → call_type call_type = { "mastercard": GrantCall.CallType.SCHOLARSHIP, "GTA": GrantCall.CallType.FELLOWSHIP, "other": GrantCall.CallType.SCHOLARSHIP, }.get(stype, GrantCall.CallType.SCHOLARSHIP) start = row.get("start_date") or date_cls.today() end = row.get("end_date") or date_cls.today() deadline = row.get("submission_deadline") or start if isinstance(start, str): try: start = datetime_cls.strptime(start, "%Y-%m-%d").date() except ValueError: start = date_cls.today() if isinstance(end, str): try: end = datetime_cls.strptime(end, "%Y-%m-%d").date() except ValueError: end = date_cls.today() if isinstance(deadline, str): try: deadline = datetime_cls.strptime(deadline, "%Y-%m-%d").date() except ValueError: deadline = start if self.dry_run: self.stdout.write(f" [DRY] Call: {title[:60]} [{call_type}]") created += 1 continue with transaction.atomic(): pub_id = f"SCH-{row['id']}"[:40] fr = FundingRecord.objects.create( public_grant_id=pub_id, title=title, amount=Decimal("0.00"), currency="USD", start_date=start, end_date=end, status=FundingRecord.Status.APPROVED, strategic_objectives=LEGACY_TAG, created_by=admin, ) slug_base = slugify(title)[:90] or f"sch-call-{row['id']}" slug = slug_base i = 2 while slug in existing_slugs: slug = f"{slug_base}-{i}" i += 1 existing_slugs.add(slug) gc = GrantCall( title=title, slug=slug, call_type=call_type, description=( f"{LEGACY_TAG}\n" f"{tag}\n" f"scholarship_type: {stype}\n" f"call_year: {row.get('call_year', '')}" ), opens_at=_dt(start), closes_at=_dt(deadline), status=GrantCall.Status.CLOSED, funding_record=fr, created_by=admin, ) GrantCall.objects.bulk_create([gc]) gc = GrantCall.objects.get(slug=slug) self.scholarship_call_map[row["id"]] = gc.pk created += 1 self.stdout.write( self.style.SUCCESS(f" Scholarship calls — created: {created}, skipped: {skipped}") ) # ── Phase B — Mastercard / general scholarship applications ────────── def _phase_b_scholarship_apps(self): from apps.rims.grants.models import Application, ScholarshipApplicationProfile from apps.rims.scholarships.models import Scholarship # Get or create a Mastercard Foundation scholarship programme mc_scholarship, _ = Scholarship.objects.get_or_create( name="Mastercard Foundation TAGDev Programme", defaults={ "description": ( f"{LEGACY_TAG} — Mastercard Foundation / TAGDev scholarship programme. " "Migrated from ruforum_rims_db scholarships_mastercardscholarshipapplication." ), "is_active": True, }, ) other_scholarship, _ = Scholarship.objects.get_or_create( name="Legacy Other Scholarships", defaults={ "description": f"{LEGACY_TAG} — Other scholarship programmes.", "is_active": True, }, ) # Load Mastercard-extended data keyed by base application id self.cur.execute("SELECT * FROM scholarships_mastercardscholarshipapplication") mc_rows = {r["scholarshipapplication_ptr_id"]: r for r in self.cur.fetchall()} # Base applications self.cur.execute(""" SELECT sa.*, c.scholarship_type FROM scholarships_scholarshipapplication sa JOIN calls_call c ON c.id = sa.call_id WHERE c.scholarship_type IN ('mastercard', 'other') ORDER BY sa.id """) rows = self.cur.fetchall() existing_pairs: set[tuple] = set( Application.objects.values_list("call_id", "applicant_id") ) created = skipped = errors = users_created = 0 for row in rows: tag = f"[legacy_schapp_id:{row['id']}]" if Application.objects.filter(eligibility_notes__contains=tag).exists(): skipped += 1 continue call_pk = self.scholarship_call_map.get(row.get("call_id")) if not call_pk: errors += 1 continue user_pk = self._get_or_create_user( row["user_id"], first_name=_str(row.get("first_name")), last_name=_str(row.get("last_name")), ) if not user_pk: if not self.dry_run: users_created += 0 errors += 1 continue pair = (call_pk, user_pk) if pair in existing_pairs: skipped += 1 continue old_state = (row.get("state") or "draft").lower() new_status = STATE_MAP.get(old_state, "draft") if self.dry_run: created += 1 existing_pairs.add(pair) continue mc = mc_rows.get(row["id"]) # Mastercard-specific extension, may be None with transaction.atomic(): app = Application( call_id=call_pk, applicant_id=user_pk, proposal="", status=new_status, eligibility_notes=( f"{LEGACY_TAG}\n{tag}\n" f"scholarship_type: {row.get('scholarship_type', '')}\n" f"compliance: {_str(row.get('compliance_comments'), 500)}" ), eligibility_passed=old_state not in ("noncompliant", "draft"), submitted_at=_dt(row.get("application_date")), anonymized_code=f"SCH-{row['id']}", review_conflict_acknowledged=False, interview_completed=False, shortlist_recommended=old_state == "selected_for_funding", ) Application.objects.bulk_create([app]) app = Application.objects.get(eligibility_notes__contains=tag) existing_pairs.add(pair) # Build ScholarshipApplicationProfile profile_kwargs = dict( application=app, date_of_birth=row.get("date_of_birth"), gender=_str(row.get("gender"), 16), english_in_high_school=_bool(row.get("english_in_high_school")), scholarship_call_source=_str(row.get("scholarship_call_source"), 64), validated_academic_document=_bool(row.get("validated_academic_document")), validated_reference_letters=_bool(row.get("validated_reference_letters")), applied_to_university=_bool(row.get("applied_to_university")), ) if mc: profile_kwargs.update(dict( country_of_birth=_str(mc.get("country_of_birth"), 3), country_of_residence=_str(mc.get("country_of_residence"), 3), country_of_origin=_str(mc.get("country_of_origin"), 3), is_refugee=_bool(mc.get("is_refugee")), degree_programme=_str(mc.get("degree_program"), 100), gpa=mc.get("gpa"), grading_criteria=_str(mc.get("grading_creteria"), 64), postgraduate_student=_bool(mc.get("postgraduate_student")), people_in_house=_smallint(mc.get("people_in_house")), rooms_in_house=_smallint(mc.get("rooms_in_house")), how_many_share_toilet=_smallint(mc.get("how_many_share_toilet")), electricity=_bool(mc.get("electricity")), solar_energy=_bool(mc.get("solar_energy")), water_source=_str(mc.get("water_source"), 64), toilet_type=_str(mc.get("toilet_type"), 32), village_of_birth=_str(mc.get("village_of_birth"), 100), district_of_birth=_str(mc.get("district_of_birth"), 100), village_of_residence=_str(mc.get("village_of_residence"), 100), district_of_residence=_str(mc.get("district_of_residence"), 100), nearest_major_road=_str(mc.get("nearest_major_road"), 100), nearest_trading_centre=_str(mc.get("nearest_trading_centre"), 100), distance_to_the_source=_smallint(mc.get("distance_to_the_source")), name_of_guardian_or_spouse=_str(mc.get("name_of_guardian_or_spouse"), 150), guardian_relationship=_str(mc.get("guardian_relationship"), 100), guardian_occupation=_str(mc.get("guardian_occupation"), 100), guardian_or_spouse_phone=_str(mc.get("guardian_or_spouse_phone"), 32), number_of_siblings=_smallint(mc.get("number_of_siblings")), income_source_1=_str(mc.get("income_source_1"), 150), income_source_2=_str(mc.get("income_source_2"), 150), income_source_3=_str(mc.get("income_source_3"), 150), income_source_4=_str(mc.get("income_source_4"), 150), own_livestock=_bool(mc.get("own_livestock")), number_of_cattle=_smallint(mc.get("number_of_cattle")), number_of_goats=_smallint(mc.get("number_of_goats")), number_of_sheep=_smallint(mc.get("number_of_sheep")), number_of_chickens=_smallint(mc.get("number_of_chickens")), number_of_camels=_smallint(mc.get("number_of_camels")), number_of_donkeys=_smallint(mc.get("number_of_donkeys")), have_physical_disability=_bool(mc.get("have_physical_disability")), physical_disability=_str(mc.get("physical_disability"), 200), have_history_of_chronic_illness=_bool(mc.get("have_history_of_chronic_illness")), history_of_chronic_illness=_str(mc.get("history_of_chronic_illness"), 200), have_been_arrested=_bool(mc.get("have_been_arrested")), cause_of_arrest=_str(mc.get("cause_of_arrest"), 200), pending_high_school_balances=_bool(mc.get("pending_high_school_balances")), school_balances=mc.get("school_balances"), held_leadership_position=_bool(mc.get("held_leadership_position")), community_service_participation=_bool(mc.get("community_service_participation")), currently_volunteering=_bool(mc.get("currently_volunteering")), member_of_group=_bool(mc.get("member_of_group")), ever_been_employed=_bool(mc.get("ever_been_employed")), employer_support=_bool(mc.get("employer_support")), challenge=_str(mc.get("challenge")), experience=_str(mc.get("experience")), most_significant_contribution=_str(mc.get("most_significant_contribution")), most_significant_leadership_contribution=_str(mc.get("most_significant_leadership_contribution")), # Home validation — Mastercard apps go through this home_validation_status=ScholarshipApplicationProfile.HomeValidationStatus.COMPLETED if old_state in ("validated", "selected_for_funding") else ScholarshipApplicationProfile.HomeValidationStatus.PENDING if old_state == "home_validation" else ScholarshipApplicationProfile.HomeValidationStatus.NOT_REQUIRED, )) ScholarshipApplicationProfile.objects.create(**profile_kwargs) created += 1 self.stdout.write( self.style.SUCCESS( f" Scholarship applications — created: {created}, " f"skipped: {skipped}, errors: {errors}" ) ) # ── Phase C — GTA applications ──────────────────────────────────────── def _phase_c_gta_apps(self): from apps.rims.grants.models import Application, ScholarshipApplicationProfile self.cur.execute(""" SELECT sa.*, c.scholarship_type FROM scholarships_scholarshipapplication sa JOIN calls_call c ON c.id = sa.call_id WHERE c.scholarship_type = 'GTA' ORDER BY sa.id """) rows = self.cur.fetchall() existing_pairs: set[tuple] = set( Application.objects.values_list("call_id", "applicant_id") ) created = skipped = errors = 0 for row in rows: tag = f"[legacy_gtaapp_id:{row['id']}]" if Application.objects.filter(eligibility_notes__contains=tag).exists(): skipped += 1 continue call_pk = self.scholarship_call_map.get(row.get("call_id")) if not call_pk: errors += 1 continue user_pk = self._get_or_create_user( row["user_id"], first_name=_str(row.get("first_name")), last_name=_str(row.get("last_name")), ) if not user_pk: errors += 1 continue pair = (call_pk, user_pk) if pair in existing_pairs: skipped += 1 continue old_state = (row.get("state") or "draft").lower() new_status = STATE_MAP.get(old_state, "draft") if self.dry_run: created += 1 existing_pairs.add(pair) continue with transaction.atomic(): app = Application( call_id=call_pk, applicant_id=user_pk, proposal="", status=new_status, eligibility_notes=( f"{LEGACY_TAG}\n{tag}\n" f"scholarship_type: GTA" ), eligibility_passed=old_state not in ("noncompliant", "draft"), submitted_at=_dt(row.get("application_date")), anonymized_code=f"GTA-{row['id']}", review_conflict_acknowledged=False, interview_completed=False, shortlist_recommended=old_state == "selected_for_funding", ) Application.objects.bulk_create([app]) app = Application.objects.get(eligibility_notes__contains=tag) existing_pairs.add(pair) ScholarshipApplicationProfile.objects.create( application=app, date_of_birth=row.get("date_of_birth"), gender=_str(row.get("gender"), 16), english_in_high_school=_bool(row.get("english_in_high_school")), scholarship_call_source=_str(row.get("scholarship_call_source"), 64), validated_academic_document=_bool(row.get("validated_academic_document")), validated_reference_letters=_bool(row.get("validated_reference_letters")), ) created += 1 self.stdout.write( self.style.SUCCESS( f" GTA applications — created: {created}, skipped: {skipped}, errors: {errors}" ) ) # ── Phase D — Back-fill FellowshipApplicationProfile ───────────────── def _phase_d_fellowship_profiles(self): from apps.rims.grants.models import Application, FellowshipApplicationProfile self.cur.execute("SELECT * FROM fellowship_applications_fellowshipapplication") rows = self.cur.fetchall() created = skipped = errors = 0 for row in rows: # Find the migrated application app = Application.objects.filter( eligibility_notes__contains=f"[legacy_fellowship_id:{row['id']}]" ).first() if not app: errors += 1 continue if FellowshipApplicationProfile.objects.filter(application=app).exists(): skipped += 1 continue if self.dry_run: created += 1 continue FellowshipApplicationProfile.objects.create( application=app, home_institute_name=_str(row.get("home_institute_name"), 255), home_institute_department=_str(row.get("home_institute_department"), 200), home_institute_faculty=_str(row.get("home_institute_faculty"), 200), home_institute_place=_str(row.get("home_institute_place"), 255), home_country=_str(row.get("country"), 64), host_institute_name=_str(row.get("host_institute_name"), 255), host_institute_department=_str(row.get("host_institute_department"), 200), host_institute_faculty=_str(row.get("host_institute_faculty"), 200), host_institute_place=_str(row.get("host_institute_place"), 255), duration_weeks=row.get("duration"), proposed_start=row.get("proposed_begining"), proposed_end=row.get("proposed_end"), subject_area=_str(row.get("subject_area"), 500), area_of_interest=_str(row.get("area_of_interest"), 255), area_of_specialization=_str(row.get("area_of_specialization"), 200), form_of_service=_str(row.get("form_of_service"), 32), job_title=_str(row.get("job_title"), 100), position=_str(row.get("position"), 150), telephone=_str(row.get("telephone"), 64), proposed_activities=_str(row.get("proposed_value")), other_activities=_str(row.get("other_activities")), motivation=_str(row.get("motivation")), validated_cv=_bool(row.get("validated_cv")), validated_home_institute=_bool(row.get("validated_home_institute")), validated_host_institute=_bool(row.get("validated_host_institute")), validated_letter_of_release=_bool(row.get("validated_letter_of_release")), placement_status=FellowshipApplicationProfile.PlacementStatus.COMPLETED if row.get("status") in ("selected", "validated") else FellowshipApplicationProfile.PlacementStatus.PENDING, ) created += 1 self.stdout.write( self.style.SUCCESS( f" Fellowship profiles — created: {created}, skipped: {skipped}, errors: {errors}" ) ) # ── Phase E — Fix GRG scholar chain ────────────────────────────────── def _phase_e_scholar_chain(self): """Re-link scholars from 'Legacy GRG Scholars' to per-call Scholarship programmes.""" from apps.rims.grants.models import Award, GrantCall from apps.rims.scholarships.models import Scholar, Scholarship generic = Scholarship.objects.filter(name="Legacy GRG Scholars").first() if not generic: self.stdout.write(" No 'Legacy GRG Scholars' scholarship found — skipping.") return updated = skipped = 0 for scholar in Scholar.objects.filter(scholarship=generic).select_related("award"): if not scholar.award: skipped += 1 continue # Get the GrantCall from the award chain call = getattr(scholar.award.application, "call", None) if hasattr(scholar.award, "application") else None if not call: skipped += 1 continue # Get or create a per-call Scholarship programme sch_name = f"{call.title} — Scholar Programme"[:255] if self.dry_run: self.stdout.write(f" [DRY] Would link scholar {scholar.pk} → {sch_name[:60]}") updated += 1 continue programme, _ = Scholarship.objects.get_or_create( name=sch_name, defaults={ "description": ( f"{LEGACY_TAG} — Scholar programme derived from grant call: {call.title}" ), "grant_call": call, "is_active": True, }, ) # Only update if not already on this programme if scholar.scholarship_id != programme.pk: Scholar.objects.filter(pk=scholar.pk).update(scholarship=programme) updated += 1 else: skipped += 1 self.stdout.write( self.style.SUCCESS(f" Scholar chain — updated: {updated}, skipped: {skipped}") )