"""
Management command: fix_legacy_scholar_programmes
==================================================
Re-assigns legacy scholars from the generic "Legacy RUFORUM Scholars" bucket
to specific Scholarship programme records based on their original grant_type
in the legacy ruforum_rims_db.

Two sources are used:
  1. contacts_student.grant_type_id → grant_types_granttype.name
     (covers 1,939 of 2,004 students)
  2. scholarships_scholarshipapplication.call_id → calls_call.title
     (fallback for students matched via scholarship application call)

Usage:
  docker compose run --rm web python manage.py fix_legacy_scholar_programmes
  docker compose run --rm web python manage.py fix_legacy_scholar_programmes --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,
)

LEGACY_TAG = "[migrated_from:ruforum_rims_db]"


class Command(BaseCommand):
    help = "Re-link legacy scholars to proper Scholarship programme records"

    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.scholarships.models import Scholar, Scholarship

        default_scholarship = Scholarship.objects.filter(
            name="Legacy RUFORUM Scholars"
        ).first()
        if not default_scholarship:
            self.stdout.write(self.style.ERROR("  'Legacy RUFORUM Scholars' not found — nothing to fix."))
            return

        # ── Load legacy grant type names ──────────────────────────────────────
        self.cur.execute("""
            SELECT cs.user_id, gt.name as programme_name
            FROM contacts_student cs
            JOIN grant_types_granttype gt ON gt.id = cs.grant_type_id
            WHERE cs.grant_type_id IS NOT NULL
        """)
        grant_type_map: dict[int, str] = {
            row["user_id"]: row["programme_name"].strip()
            for row in self.cur.fetchall()
        }

        # ── Load call-based programme names (for scholarship applicants) ───────
        self.cur.execute("""
            SELECT cs.user_id, cc.title as programme_name
            FROM contacts_student cs
            JOIN scholarships_scholarshipapplication sa ON sa.user_id = cs.user_id
            JOIN calls_call cc ON cc.id = sa.call_id
            WHERE cs.grant_type_id IS NULL
              AND cc.title IS NOT NULL AND cc.title != ''
        """)
        for row in self.cur.fetchall():
            uid = row["user_id"]
            if uid not in grant_type_map:
                grant_type_map[uid] = row["programme_name"].strip()

        self.stdout.write(f"  Legacy programme mappings found: {len(grant_type_map)}")

        # ── Build scholar map: legacy_student_id → Scholar ────────────────────
        scholar_map: dict[int, Scholar] = {}
        for s in Scholar.objects.filter(notes__contains="[legacy_student_id:").select_related("scholarship"):
            try:
                legacy_uid = int(s.notes.split("[legacy_student_id:")[1].split("]")[0])
                scholar_map[legacy_uid] = s
            except (ValueError, IndexError):
                pass

        self.stdout.write(f"  Legacy scholars in system: {len(scholar_map)}")

        # ── Get or create Scholarship per unique programme name ───────────────
        programme_names = set(grant_type_map.values())
        scholarship_cache: dict[str, Scholarship] = {}

        for name in sorted(programme_names):
            if self.dry_run:
                scholarship_cache[name] = None  # type: ignore
                self.stdout.write(f"    Would create/get: {name}")
                continue
            obj, created = Scholarship.objects.get_or_create(
                name=name,
                defaults={
                    "description": f"{LEGACY_TAG} — Migrated from grant_types_granttype / calls_call",
                    "is_active": True,
                },
            )
            scholarship_cache[name] = obj
            if created:
                self.stdout.write(f"    Created scholarship: {name}")

        # ── Re-link scholars ──────────────────────────────────────────────────
        updated = already_linked = no_mapping = 0

        for legacy_uid, scholar in scholar_map.items():
            programme_name = grant_type_map.get(legacy_uid)
            if not programme_name:
                no_mapping += 1
                continue

            if scholar.scholarship_id != default_scholarship.pk:
                # Already moved to a proper programme
                already_linked += 1
                continue

            if self.dry_run:
                updated += 1
                continue

            target_scholarship = scholarship_cache.get(programme_name)
            if not target_scholarship:
                no_mapping += 1
                continue

            # Check unique constraint: (user, scholarship)
            if Scholar.objects.filter(
                user_id=scholar.user_id, scholarship=target_scholarship
            ).exclude(pk=scholar.pk).exists():
                already_linked += 1
                continue

            with transaction.atomic():
                Scholar.objects.filter(pk=scholar.pk).update(
                    scholarship=target_scholarship
                )
            updated += 1

        self.stdout.write(
            self.style.SUCCESS(
                f"\n  Done — updated: {updated}, already correct: {already_linked}, "
                f"no mapping: {no_mapping}"
            )
        )

        # ── Summary of programmes after fix ───────────────────────────────────
        if not self.dry_run:
            from django.db.models import Count
            self.stdout.write("\n  Scholar distribution by programme:")
            rows = (
                Scholar.objects.values("scholarship__name")
                .annotate(n=Count("id"))
                .order_by("-n")[:20]
            )
            for r in rows:
                self.stdout.write(f"    {r['scholarship__name']}: {r['n']}")