"""
Management command: fix_scholar_award_links
===========================================
Links Scholar records to their corresponding Award records using three paths:

  Path 1 — grants_grant.pi_id = contacts_student.user_id (PI of own grant)
            Covers GRG, CARP, FAPA, Post-Doc students who are the grant PI

  Path 2 — grants_studentmembership: student linked to a grant
            Covers students listed as members of a grant team

  Path 3 — grants_applications_grantapplication: student applied, was funded,
            and a grants_grant was created from their application

  For each path: find the legacy grants_grant.id → look up Award with
  [legacy_grant_id:X] in its narrative → set Scholar.award.

Usage:
  docker compose run --rm web python manage.py fix_scholar_award_links
  docker compose run --rm web python manage.py fix_scholar_award_links --dry-run
"""
from __future__ import annotations

import pymysql
import pymysql.cursors
from django.core.management.base import BaseCommand
from django.db import transaction

LEGACY_DB = dict(
    host="host.docker.internal",
    user="root",
    password="Joshua..123",
    db="ruforum_rims_db",
    charset="utf8mb4",
    use_unicode=True,
)


class Command(BaseCommand):
    help = "Link Scholar records to their Award records via legacy grant membership"

    def add_arguments(self, parser):
        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"))

        self.conn = pymysql.connect(cursorclass=pymysql.cursors.DictCursor, **LEGACY_DB)
        self.cur = self.conn.cursor()

        self._run()

        self.cur.close()
        self.conn.close()

    def _run(self):
        from apps.rims.grants.models import Award
        from apps.rims.scholarships.models import Scholar

        # ── Build Award map: legacy_grant_id → Award.pk ──────────────────────
        self.stdout.write("  Building award map from legacy_grant_id tags...")
        award_map: dict[int, int] = {}
        for pk, narrative in Award.objects.values_list("pk", "narrative"):
            if narrative and "[legacy_grant_id:" in narrative:
                try:
                    grant_id = int(narrative.split("[legacy_grant_id:")[1].split("]")[0])
                    award_map[grant_id] = pk
                except (ValueError, IndexError):
                    pass
        self.stdout.write(f"  Awards with legacy_grant_id tag: {len(award_map)}")

        # ── Build Scholar map: legacy_user_id → Scholar ───────────────────────
        self.stdout.write("  Building scholar map from legacy_student_id tags...")
        scholar_map: dict[int, Scholar] = {}
        for s in Scholar.objects.filter(notes__contains="[legacy_student_id:").select_related("scholarship"):
            try:
                uid = int(s.notes.split("[legacy_student_id:")[1].split("]")[0])
                scholar_map[uid] = s
            except (ValueError, IndexError):
                pass
        self.stdout.write(f"  Legacy scholars in system: {len(scholar_map)}")

        # ── Collect user_id → grant_id mappings from all 3 legacy paths ───────
        user_grant_map: dict[int, list[int]] = {}

        # Path 1: PI of own grant
        self.cur.execute("""
            SELECT cs.user_id, g.id AS grant_id
            FROM contacts_student cs
            JOIN grants_grant g ON g.pi_id = cs.user_id
            WHERE g.approval_status = 'approved'
        """)
        for row in self.cur.fetchall():
            user_grant_map.setdefault(row["user_id"], []).append(row["grant_id"])

        # Path 2: student membership in a grant
        self.cur.execute("""
            SELECT gsm.student_id AS user_id, gsm.grant_id
            FROM grants_studentmembership gsm
        """)
        for row in self.cur.fetchall():
            user_grant_map.setdefault(row["user_id"], []).append(row["grant_id"])

        # Path 3: funded grant application
        self.cur.execute("""
            SELECT ga.user_id, g.id AS grant_id
            FROM grants_applications_grantapplication ga
            JOIN grants_grant g ON g.grant_application_id = ga.id
            WHERE ga.selected_for_funding = 1
        """)
        for row in self.cur.fetchall():
            user_grant_map.setdefault(row["user_id"], []).append(row["grant_id"])

        total_mapped = sum(len(v) for v in user_grant_map.values())
        self.stdout.write(f"  User→grant links from legacy (all paths): {total_mapped} across {len(user_grant_map)} users")

        # ── Link scholars to awards ───────────────────────────────────────────
        linked = already_linked = no_award = no_scholar = conflict = 0

        for user_id, grant_ids in user_grant_map.items():
            scholar = scholar_map.get(user_id)
            if not scholar:
                no_scholar += 1
                continue

            if scholar.award_id is not None:
                already_linked += 1
                continue

            # Find first grant_id that maps to a known Award
            matched_award_pk = None
            for gid in grant_ids:
                apk = award_map.get(gid)
                if apk:
                    matched_award_pk = apk
                    break

            if not matched_award_pk:
                no_award += 1
                continue

            # Check the Award isn't already linked to a different Scholar
            if Scholar.objects.filter(award_id=matched_award_pk).exclude(pk=scholar.pk).exists():
                conflict += 1
                continue

            if self.dry_run:
                linked += 1
                continue

            with transaction.atomic():
                Scholar.objects.filter(pk=scholar.pk).update(award_id=matched_award_pk)
                scholar.award_id = matched_award_pk
            linked += 1

        self.stdout.write(
            self.style.SUCCESS(
                f"\n  Done — linked: {linked}, already had award: {already_linked}, "
                f"no matching award: {no_award}, award conflict: {conflict}, "
                f"scholar not found: {no_scholar}"
            )
        )

        if not self.dry_run:
            scholars_with = Scholar.objects.exclude(award__isnull=True).count()
            scholars_without = Scholar.objects.filter(award__isnull=True).count()
            self.stdout.write(
                f"\n  After fix: {scholars_with} scholars linked to awards, "
                f"{scholars_without} still unlinked"
            )