""" Management command: migrate_legacy_p1 ====================================== Priority 1 migration from ruforum_rims_db (MySQL): Phase 6 — Grant progress reports (firstreport, month12…month108, lastreport) Phase 7 — Student memberships (grants_studentmembership → Scholar) Phase 8 — Collaborators (grants_applications_collaborator → ApplicationDocument) Phase 9 — Fellowship applications (fellowship_applications_fellowshipapplication → Application) Usage: docker compose run --rm web python manage.py migrate_legacy_p1 docker compose run --rm web python manage.py migrate_legacy_p1 --phase 6 docker compose run --rm web python manage.py migrate_legacy_p1 --phase 6 7 8 9 docker compose run --rm web python manage.py migrate_legacy_p1 --dry-run """ from __future__ import annotations import re from datetime import datetime, timezone as dt_timezone from decimal import Decimal import MySQLdb from django.core.management.base import BaseCommand, CommandError from django.db import transaction from django.utils import timezone from apps.core.legacy_db import LEGACY_DB LEGACY_TAG = "LEGACY_MIGRATED" # All monthly report tables in the old system MONTHLY_REPORT_TABLES = [ ("grants_reports_firstreport", "first_report"), ("grants_reports_month12report", "month_12"), ("grants_reports_month18report", "month_18"), ("grants_reports_month24report", "month_24"), ("grants_reports_month30report", "month_30"), ("grants_reports_month36report", "month_36"), ("grants_reports_month42report", "month_42"), ("grants_reports_month48report", "month_48"), ("grants_reports_month54report", "month_54"), ("grants_reports_month60report", "month_60"), ("grants_reports_month66report", "month_66"), ("grants_reports_month72report", "month_72"), ("grants_reports_month78report", "month_78"), ("grants_reports_month84report", "month_84"), ("grants_reports_month90report", "month_90"), ("grants_reports_month96report", "month_96"), ("grants_reports_month102report", "month_102"), ("grants_reports_month108report", "month_108"), ] FINAL_REPORT_TABLE = "grants_reports_lastreport" def _dt(val): """Convert legacy date/datetime to timezone-aware datetime.""" if val is None: return timezone.now() if isinstance(val, datetime): return val.replace(tzinfo=dt_timezone.utc) if val.tzinfo is None else val try: return datetime.strptime(str(val), "%Y-%m-%d").replace(tzinfo=dt_timezone.utc) except (ValueError, TypeError): return timezone.now() class Command(BaseCommand): help = "Priority 1 legacy migration: reports, scholars, collaborators, fellowships" def add_arguments(self, parser): parser.add_argument( "--phase", nargs="+", type=int, choices=[6, 7, 8, 9], default=[6, 7, 8, 9], help="Phases to run (default: all). e.g. --phase 6 7", ) 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) # Build award map: legacy grant_id → new Award.pk self._build_award_map() runners = { 6: self._phase6_reports, 7: self._phase7_scholars, 8: self._phase8_collaborators, 9: self._phase9_fellowships, } for phase in sorted(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("\nMigration complete.")) # ── Helpers ────────────────────────────────────────────────────────── def _build_award_map(self): """Map legacy grants_grant.id → new Award.pk. Awards store [legacy_grant_id:N] in their narrative field — this gives us all 121 migrated awards directly without needing the application chain. """ from apps.rims.grants.models import Award self.award_map: dict[int, int] = {} for award in Award.objects.filter(narrative__contains="[legacy_grant_id:"): m = re.search(r"\[legacy_grant_id:(\d+)\]", award.narrative) if m: self.award_map[int(m.group(1))] = award.pk self.stdout.write(f" Award map loaded: {len(self.award_map)} entries") def _get_or_create_fellowship_call(self): """Ensure the legacy fellowship call exists as a GrantCall in the new system.""" from apps.rims.grants.models import FundingRecord, GrantCall from apps.core.authentication.models import CustomUser from datetime import date as date_cls, datetime as datetime_cls tag = "[legacy_fellowshipcall_id:1]" existing = GrantCall.objects.filter(description__contains=tag).first() if existing: return existing.pk self.cur.execute("SELECT * FROM calls_fellowshipcall WHERE id = 1") row = self.cur.fetchone() if not row: return None admin = CustomUser.objects.filter(is_superuser=True).first() start = row.get("start_date") or date_cls.today() end = row.get("end_date") or date_cls.today() if isinstance(start, str): start = datetime_cls.strptime(start, "%Y-%m-%d").date() if isinstance(end, str): end = datetime_cls.strptime(end, "%Y-%m-%d").date() closes_at = datetime_cls.combine( row.get("submission_deadline") or end, datetime_cls.min.time() ).replace(tzinfo=dt_timezone.utc) opens_at = datetime_cls.combine(start, datetime_cls.min.time()).replace(tzinfo=dt_timezone.utc) with transaction.atomic(): # Need a FundingRecord parent fr = FundingRecord.objects.create( public_grant_id=f"LEG-FELL-{row['call_id']}"[:40], title=row["title"], amount=0, currency="USD", start_date=start, end_date=end, status=FundingRecord.Status.APPROVED, strategic_objectives=LEGACY_TAG, created_by=admin, ) from django.utils.text import slugify slug = slugify(row["title"])[:100] or "legacy-fellowship" # Ensure unique slug if GrantCall.objects.filter(slug=slug).exists(): slug = f"{slug}-legacy" gc = GrantCall( title=row["title"], slug=slug, call_type=GrantCall.CallType.FELLOWSHIP, description=( f"{LEGACY_TAG}\n" f"{tag}\n" f"[legacy_ref:{row['call_id']}]\n" f"goal: {(row.get('goal') or '')[:500]}" ), opens_at=opens_at, closes_at=closes_at, status=GrantCall.Status.CLOSED, funding_record=fr, created_by=admin, ) GrantCall.objects.bulk_create([gc]) gc = GrantCall.objects.get(slug=slug) self.stdout.write(f" Created fellowship call: {gc.title} (pk={gc.pk})") return gc.pk # ── Phase 6 — Progress reports ─────────────────────────────────────── def _phase6_reports(self): from apps.rims.grants.models import ( AcademicProgressReport, Award, AwardCloseoutRecord, FinalNarrativeReport, FinalFinancialReport, ) created_progress = skipped_progress = errors_progress = 0 created_final = skipped_final = errors_final = 0 # ── 6a: periodic reports → AcademicProgressReport ──────────────── for table, period_label in MONTHLY_REPORT_TABLES: self.cur.execute(f"SELECT * FROM {table}") rows = self.cur.fetchall() for row in rows: grant_id = row.get("grant_id") award_pk = self.award_map.get(grant_id) if not award_pk: errors_progress += 1 continue # Check unique constraint (award_id, period_label) if AcademicProgressReport.objects.filter( award_id=award_pk, period_label=period_label ).exists(): skipped_progress += 1 continue # Compose summary from available text fields parts = [] for field in [ "progress_against_objectives", "problems_and_challenges", "modifications", "skills_gap", "teamwork_and_mentoring", "planned_activities", "project_activities", ]: val = row.get(field, "") if val and str(val).strip(): parts.append(f"**{field.replace('_', ' ').title()}:**\n{val}") summary = "\n\n".join(parts) or f"Migrated from {table}" submitted_at = _dt(row.get("last_submitted") or row.get("date_submitted")) if self.dry_run: created_progress += 1 continue with transaction.atomic(): AcademicProgressReport.objects.create( award_id=award_pk, period_label=period_label, summary=summary, supervisor_name="", status="approved", # historical — treat as accepted submitted_at=submitted_at, review_comment=f"{LEGACY_TAG} source:{table} id:{row['id']}", ) created_progress += 1 self.stdout.write( self.style.SUCCESS( f" Progress reports — created: {created_progress}, " f"skipped: {skipped_progress}, no_award: {errors_progress}" ) ) # ── 6b: final reports → FinalNarrativeReport + FinalFinancialReport self.cur.execute(f"SELECT * FROM {FINAL_REPORT_TABLE}") rows = self.cur.fetchall() for row in rows: grant_id = row.get("grant_id") award_pk = self.award_map.get(grant_id) if not award_pk: errors_final += 1 continue # Final reports require an AwardCloseoutRecord — create one if needed closeout = AwardCloseoutRecord.objects.filter(award_id=award_pk).first() if not closeout: if self.dry_run: created_final += 1 continue with transaction.atomic(): closeout = AwardCloseoutRecord.objects.create( award_id=award_pk, financial_snapshot={}, checklist={}, forced_closure_justification=LEGACY_TAG, ) # Skip if already migrated if FinalNarrativeReport.objects.filter( closeout_id=closeout.pk, notes__contains=f"[legacy_lastreport_id:{row['id']}]", ).exists(): skipped_final += 1 continue notes_parts = [] for field in [ "outcome_vs_targets", "project_highlights", "lessons_learned", "recommendations", "sustainability", "teamwork_and_mentoring", "problems_and_challenges", ]: val = row.get(field, "") if val and str(val).strip(): notes_parts.append(f"**{field.replace('_',' ').title()}:**\n{val}") notes = "\n\n".join(notes_parts) or "Migrated final report" notes += f"\n\n{LEGACY_TAG}\n[legacy_lastreport_id:{row['id']}]" submitted_at = _dt(row.get("last_submitted") or row.get("date_submitted")) if self.dry_run: created_final += 1 continue with transaction.atomic(): FinalNarrativeReport.objects.create( closeout=closeout, notes=notes, status="approved", submitted_at=submitted_at, review_comment="", response_note="", ) FinalFinancialReport.objects.create( closeout=closeout, notes=f"{LEGACY_TAG} [legacy_lastreport_id:{row['id']}]", status="approved", submitted_at=submitted_at, queries="", response_note="", ) created_final += 1 self.stdout.write( self.style.SUCCESS( f" Final reports — created: {created_final}, " f"skipped: {skipped_final}, no_award: {errors_final}" ) ) # ── Phase 7 — Student memberships → Scholar ────────────────────────── def _phase7_scholars(self): from apps.rims.scholarships.models import Scholar, Scholarship from apps.core.authentication.models import CustomUser from django.contrib.auth.hashers import make_password # Ensure a default scholarship programme for legacy scholar records default_scholarship, _ = Scholarship.objects.get_or_create( name="Legacy GRG Scholars", defaults={ "description": ( f"{LEGACY_TAG} — Scholar records migrated from " "ruforum_rims_db grants_studentmembership" ), "is_active": True, }, ) # Full student data from legacy (need name + email for user creation) self.cur.execute( "SELECT id, business_email as email, first_name, last_name, is_active " "FROM contacts_user" ) user_rows = {r["id"]: r for r in self.cur.fetchall()} self.cur.execute("SELECT * FROM grants_studentmembership ORDER BY grant_id, id") rows = self.cur.fetchall() created = skipped = errors = 0 users_created = 0 existing_emails: set[str] = set(CustomUser.objects.values_list("email", flat=True)) # Track which award_ids have already been assigned to a Scholar (unique constraint) used_award_ids: set[int] = set( Scholar.objects.exclude(award_id__isnull=True).values_list("award_id", flat=True) ) for row in rows: grant_id = row.get("grant_id") student_id = row.get("student_id") award_pk = self.award_map.get(grant_id) if not award_pk: errors += 1 continue student_data = user_rows.get(student_id) if not student_data: errors += 1 continue email = (student_data.get("email") or "").strip().lower() if not email: errors += 1 continue # Get or create the user user = CustomUser.objects.filter(email=email).first() if not user: if self.dry_run: created += 1 continue # Create the student user with unusable password user = CustomUser.objects.create( email=email, first_name=(student_data.get("first_name") or "").strip()[:150], last_name=(student_data.get("last_name") or "").strip()[:150], is_active=bool(student_data.get("is_active", True)), password=make_password(None), ) existing_emails.add(email) users_created += 1 # Skip if already migrated if Scholar.objects.filter( notes__contains=f"[legacy_membership_id:{row['id']}]" ).exists(): skipped += 1 continue # Unique constraint: (user, scholarship) if Scholar.objects.filter(user=user, scholarship=default_scholarship).exists(): skipped += 1 continue if self.dry_run: created += 1 continue # Only first scholar per award gets the award_id link (unique constraint) assign_award = award_pk if award_pk not in used_award_ids else None if assign_award: used_award_ids.add(assign_award) with transaction.atomic(): Scholar.objects.create( user=user, scholarship=default_scholarship, award_id=assign_award, status="active", enrolled_at=_dt(row.get("enrollment_date")), notes=( f"{LEGACY_TAG}\n" f"[legacy_membership_id:{row['id']}]\n" f"[legacy_grant_id:{grant_id}]\n" f"support_type: {row.get('Support_type', '')}\n" f"study_year: {row.get('study_year', '')}" ), ) created += 1 self.stdout.write( self.style.SUCCESS( f" Scholars — created: {created}, users_created: {users_created}, " f"skipped: {skipped}, errors: {errors}" ) ) # ── Phase 8 — Collaborators → ApplicationDocument ──────────────────── def _phase8_collaborators(self): from apps.rims.grants.models import Application, ApplicationDocument # Build app_map: legacy_app_id → Application.pk app_map: dict[int, int] = {} for app in Application.objects.filter(eligibility_notes__contains="[legacy_app_id:"): m = re.search(r"\[legacy_app_id:(\d+)\]", app.eligibility_notes) if m: app_map[int(m.group(1))] = app.pk self.cur.execute("SELECT * FROM grants_applications_collaborator") rows = self.cur.fetchall() created = skipped = errors = 0 for row in rows: legacy_app_id = row.get("application_id") app_pk = app_map.get(legacy_app_id) if not app_pk: errors += 1 continue # Skip if already migrated if ApplicationDocument.objects.filter( application_id=app_pk, label__contains=f"[legacy_collab_id:{row['id']}]", ).exists(): skipped += 1 continue cv_path = (row.get("cv") or "").strip()[:99] # varchar(100) limit label = f"Collaborator CV {LEGACY_TAG} [legacy_collab_id:{row['id']}]" if self.dry_run: created += 1 continue with transaction.atomic(): ApplicationDocument.objects.create( application_id=app_pk, label=label, file=cv_path or "legacy/placeholder.pdf", is_budget=False, ) created += 1 self.stdout.write( self.style.SUCCESS( f" Collaborators — created: {created}, skipped: {skipped}, errors: {errors}" ) ) # ── Phase 9 — Fellowship applications → Application ────────────────── def _phase9_fellowships(self): from apps.rims.grants.models import Application, GrantCall from apps.core.authentication.models import CustomUser # Build call_map: legacy grantcall_id → GrantCall.pk call_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: call_map[int(m.group(1))] = gc.pk # Also map the fellowship call (separate legacy table calls_fellowshipcall) # All fellowship applications reference calls_fellowshipcall.id=1 if not self.dry_run: fellowship_call_pk = self._get_or_create_fellowship_call() else: existing_fc = GrantCall.objects.filter( description__contains="[legacy_fellowshipcall_id:1]" ).first() fellowship_call_pk = existing_fc.pk if existing_fc else None # fellowship applications use call_id=1 → maps to calls_fellowshipcall.id=1 FELLOWSHIP_CALL_LEGACY_ID = 1 # sentinel: not a grantcall id # Build user email map self.cur.execute("SELECT id, business_email as email FROM contacts_user") email_map = {r["id"]: (r["email"] or "").strip().lower() for r in self.cur.fetchall()} self.cur.execute("SELECT * FROM fellowship_applications_fellowshipapplication") rows = self.cur.fetchall() STATUS_MAP = { "Submitted": Application.Status.SUBMITTED, "submitted": Application.Status.SUBMITTED, "validated": Application.Status.SHORTLISTED, "noncompliant": Application.Status.INELIGIBLE, "selected": Application.Status.AWARDED, "rejected": Application.Status.REJECTED, } existing_pairs: set[tuple] = set( Application.objects.values_list("call_id", "applicant_id") ) created = skipped = errors = 0 for row in rows: user_id = row.get("user_id") call_id = row.get("call_id") email = email_map.get(user_id, "") user = CustomUser.objects.filter(email=email).first() if email else None if not user: errors += 1 continue # Fellowship apps use calls_fellowshipcall.id=1, not calls_grantcall if call_id == FELLOWSHIP_CALL_LEGACY_ID: call_pk = fellowship_call_pk else: call_pk = call_map.get(call_id) if call_id else None if not call_pk: errors += 1 continue pair = (call_pk, user.pk) if pair in existing_pairs: skipped += 1 continue # Skip if already migrated if Application.objects.filter( eligibility_notes__contains=f"[legacy_fellowship_id:{row['id']}]" ).exists(): skipped += 1 continue old_status = (row.get("status") or "Submitted") new_status = STATUS_MAP.get(old_status, Application.Status.SUBMITTED) proposal = "\n\n".join(filter(None, [ row.get("motivation") or "", row.get("proposed_value") or "", row.get("other_activities") or "", ])) notes = ( f"{LEGACY_TAG}\n" f"[legacy_fellowship_id:{row['id']}]\n" f"subject_area: {row.get('subject_area', '')}\n" f"home_institute: {row.get('home_institute_name', '')}\n" f"host_institute: {row.get('host_institute_name', '')}\n" f"country: {row.get('country', '')}\n" f"duration_months: {row.get('duration', '')}" ) if self.dry_run: created += 1 existing_pairs.add(pair) continue with transaction.atomic(): Application.objects.bulk_create([ Application( call_id=call_pk, applicant=user, proposal=proposal, status=new_status, eligibility_notes=notes, eligibility_passed=old_status not in ("noncompliant",), submitted_at=timezone.now(), anonymized_code=f"LEG-FELL-{row['id']}", review_conflict_acknowledged=False, interview_completed=False, shortlist_recommended=False, ) ]) existing_pairs.add(pair) created += 1 self.stdout.write( self.style.SUCCESS( f" Fellowship applications — created: {created}, " f"skipped: {skipped}, errors: {errors}" ) )