"""Bulk import legacy alumni records from a CSV file.

Expected columns (first row):
    email, first_name, last_name, graduated_on (YYYY-MM-DD),
    programme_name, institution, current_employer, current_position,
    country, orcid_id, linkedin_url, consent_marker

``consent_marker`` is truthy ("yes", "y", "true", "1") → sets
``visibility_consent=True``. Dry-run prints intended writes without
committing.
"""
from __future__ import annotations

import csv
from datetime import datetime

from django.conf import settings
from django.contrib.auth import get_user_model
from django.core.management.base import BaseCommand, CommandError
from django.db import transaction
from django.utils import timezone

from apps.alumni.profiles.models import (
    AlumniProfile,
    AlumniSource,
    Degree,
    DegreeType,
)
from apps.core.authentication.models import Institution


_TRUTHY = {"yes", "y", "true", "1", "on"}


class Command(BaseCommand):
    help = "Import alumni from a CSV file (PRD 3.3.5: legacy Excel lists)."

    def add_arguments(self, parser):
        parser.add_argument("--file", required=True, help="Path to the CSV file.")
        parser.add_argument(
            "--dry-run",
            action="store_true",
            help="Validate without writing.",
        )
        parser.add_argument(
            "--source-label",
            default=AlumniSource.CSV_IMPORT,
            help="Source label written to the profile.",
        )

    def handle(self, *args, **options):
        path = options["file"]
        dry = options["dry_run"]
        source_label = options["source_label"]
        max_rows = int(getattr(settings, "ALUMNI_MAX_CSV_ROWS", 10_000))

        User = get_user_model()
        created_profiles = 0
        updated_profiles = 0
        skipped = 0
        errors: list[str] = []

        try:
            f = open(path, encoding="utf-8", newline="")  # noqa: SIM115
        except OSError as exc:
            raise CommandError(f"Could not open {path}: {exc}") from exc

        try:
            reader = csv.DictReader(f)
            for i, row in enumerate(reader, start=2):
                if i - 1 > max_rows:
                    errors.append(
                        f"row {i}: exceeds ALUMNI_MAX_CSV_ROWS={max_rows}, stopping."
                    )
                    break
                email = (row.get("email") or "").strip().lower()
                if not email:
                    skipped += 1
                    errors.append(f"row {i}: missing email")
                    continue

                try:
                    with transaction.atomic():
                        user, user_created = User.objects.get_or_create(
                            email=email,
                            defaults={
                                "first_name": (row.get("first_name") or "").strip(),
                                "last_name": (row.get("last_name") or "").strip(),
                                "is_active": False,  # force password reset flow
                            },
                        )
                        if user_created and not user.has_usable_password():
                            user.set_unusable_password()
                            user.save(update_fields=["password"])

                        institution = None
                        inst_name = (row.get("institution") or "").strip()
                        if inst_name:
                            institution, _ = Institution.objects.get_or_create(
                                name=inst_name
                            )

                        profile, prof_created = AlumniProfile.objects.get_or_create(
                            user=user,
                            defaults={
                                "source": source_label,
                                "source_refs": {"csv_rows": [i]},
                                "current_employer": (row.get("current_employer") or "").strip(),
                                "current_position": (row.get("current_position") or "").strip(),
                                "current_institution": institution,
                                "orcid_id": (row.get("orcid_id") or "").strip(),
                                "linkedin_url": (row.get("linkedin_url") or "").strip(),
                            },
                        )
                        if not prof_created:
                            profile.source_refs.setdefault("csv_rows", []).append(i)
                            profile.save(update_fields=["source_refs", "updated_at"])

                        consent = (row.get("consent_marker") or "").strip().lower() in _TRUTHY
                        if consent and not profile.visibility_consent:
                            profile.visibility_consent = True
                            profile.published_at = timezone.now()
                            profile.save(
                                update_fields=[
                                    "visibility_consent",
                                    "published_at",
                                    "updated_at",
                                ]
                            )

                        grad = (row.get("graduated_on") or "").strip()
                        programme = (row.get("programme_name") or "").strip()
                        if grad and programme:
                            try:
                                graduated_on = datetime.strptime(grad, "%Y-%m-%d").date()
                            except ValueError:
                                errors.append(f"row {i}: bad graduated_on '{grad}'")
                                raise
                            Degree.objects.get_or_create(
                                profile=profile,
                                programme_name=programme,
                                graduated_on=graduated_on,
                                defaults={
                                    "institution": institution,
                                    "degree_type": DegreeType.OTHER,
                                },
                            )

                        if dry:
                            # Rollback the per-row savepoint so nothing persists.
                            transaction.set_rollback(True)

                        if prof_created:
                            created_profiles += 1
                        else:
                            updated_profiles += 1
                except Exception as exc:  # pragma: no cover - defensive
                    errors.append(f"row {i}: {exc}")
                    skipped += 1
        finally:
            f.close()

        self.stdout.write(self.style.SUCCESS(
            f"csv_import {'dry-run' if dry else 'applied'}: "
            f"created={created_profiles} updated={updated_profiles} skipped={skipped}"
        ))
        for err in errors[:50]:
            self.stdout.write(self.style.WARNING(err))
        if len(errors) > 50:
            self.stdout.write(self.style.WARNING(f"... and {len(errors) - 50} more errors"))