"""Replay every existing SP1–SP5 record into SMEHubTrackingRecord rows.

Idempotent — :meth:`SMEHubTrackingRecord.append_milestone` deduplicates on
``(key, payload['id'])`` so running this command multiple times is safe.
Use after deploying SP6 to seed historical tracking data without re-emitting
upstream signals.

Usage::

    docker-compose exec -T web python manage.py backfill_smehub_tracking
"""
from __future__ import annotations

from decimal import Decimal

from django.core.management.base import BaseCommand
from django.db import transaction
from django.utils import timezone


class Command(BaseCommand):
    help = "Backfill SMEHubTrackingRecord rows by replaying SP1–SP5 history."

    def add_arguments(self, parser):
        parser.add_argument(
            "--dry-run",
            action="store_true",
            help="Do not write — just report what would be backfilled.",
        )

    def handle(self, *args, **options):
        dry = options["dry_run"]
        from apps.mel.tracking.models import SMEHubTrackingRecord
        from apps.smehub.onboarding.models import (
            BusinessBaseline,
            EntrepreneurProfile,
        )

        baselines = (
            BusinessBaseline.objects.filter(locked_at__isnull=False)
            .select_related("business__entrepreneur")
            .order_by("locked_at")
        )

        created_records = 0
        replayed_milestones = {"sp2": 0, "sp3": 0, "sp4": 0, "sp5": 0}
        funding_total_bumped = 0

        for baseline in baselines:
            business = baseline.business
            entrepreneur = business.entrepreneur if business is not None else None
            if entrepreneur is None:
                continue
            with transaction.atomic():
                record, created = SMEHubTrackingRecord.objects.get_or_create(
                    entrepreneur=entrepreneur,
                    defaults={
                        "business": business,
                        "baseline_snapshot": baseline,
                        "sp1_baseline_at": baseline.locked_at,
                        "last_milestone_at": baseline.locked_at,
                    },
                )
                # Patch SP1 fields if missing (e.g. record already created by a
                # later signal before this command ran).
                update_fields: list[str] = []
                if record.sp1_baseline_at is None and baseline.locked_at is not None:
                    record.sp1_baseline_at = baseline.locked_at
                    update_fields.append("sp1_baseline_at")
                if record.business_id is None and business is not None:
                    record.business = business
                    update_fields.append("business")
                if record.baseline_snapshot_id is None and baseline is not None:
                    record.baseline_snapshot = baseline
                    update_fields.append("baseline_snapshot")
                if update_fields:
                    record.save(update_fields=update_fields + ["updated_at"])
                if created:
                    created_records += 1

                if dry:
                    continue

                # Replay SP2 / SP3 / SP4 / SP5 milestones.
                replayed_milestones["sp2"] += _replay_sp2(record, entrepreneur)
                replayed_milestones["sp3"] += _replay_sp3(record, entrepreneur)
                replayed_milestones["sp4"] += _replay_sp4(record, entrepreneur)
                bumped, sp5_count = _replay_sp5(record, entrepreneur)
                replayed_milestones["sp5"] += sp5_count
                funding_total_bumped += bumped

        # Set last_milestone_at to the latest known timestamp across all buckets
        # so stall detection has something to compare against.
        if not dry:
            _refresh_last_milestone_at()

        self.stdout.write(self.style.SUCCESS(
            f"backfill complete — {created_records} new records, "
            f"sp2={replayed_milestones['sp2']} sp3={replayed_milestones['sp3']} "
            f"sp4={replayed_milestones['sp4']} sp5={replayed_milestones['sp5']} "
            f"funding_total_added={funding_total_bumped}"
        ))


# ---------------------------------------------------------------------------
# Replay helpers
# ---------------------------------------------------------------------------


def _replay_sp2(record, entrepreneur) -> int:
    from apps.smehub.incubation.models import (
        Application,
        Certificate,
        CohortMember,
        MentorSession,
        Programme,
    )

    count = 0
    for app in Application.objects.filter(entrepreneur=entrepreneur):
        if app.status not in {Application.Status.DRAFT}:
            record.append_milestone(
                bucket="sp2_milestones",
                key="application_submitted",
                payload={
                    "id": app.pk,
                    "programme_id": app.programme_id,
                    "business_id": app.business_id,
                },
            )
            count += 1
        if app.status in {Application.Status.ACCEPTED, Application.Status.REJECTED}:
            record.append_milestone(
                bucket="sp2_milestones",
                key="application_decided",
                payload={
                    "id": app.pk,
                    "accepted": app.status == Application.Status.ACCEPTED,
                    "score": str(app.weighted_score) if app.weighted_score is not None else None,
                },
            )
            count += 1
    for member in CohortMember.objects.filter(entrepreneur=entrepreneur).select_related("cohort"):
        if member.status != CohortMember.Status.WITHDRAWN:
            record.append_milestone(
                bucket="sp2_milestones",
                key="cohort_selected",
                payload={
                    "id": member.pk,
                    "cohort_id": member.cohort_id,
                    "programme_id": member.cohort.programme_id,
                },
            )
            count += 1
        if member.status == CohortMember.Status.PROGRAMME_COMPLETE:
            record.append_milestone(
                bucket="sp2_milestones",
                key="programme_complete",
                payload={
                    "id": member.pk,
                    "cohort_id": member.cohort_id,
                    "manual": False,
                },
            )
            count += 1
    for session in MentorSession.objects.filter(
        assignment__cohort_member__entrepreneur=entrepreneur,
        status=MentorSession.Status.COMPLETED,
    ).select_related("assignment__cohort_member"):
        record.append_milestone(
            bucket="sp2_milestones",
            key="session_completed",
            payload={
                "id": session.pk,
                "member_id": session.assignment.cohort_member_id,
            },
        )
        count += 1
    for cert in Certificate.objects.filter(cohort_member__entrepreneur=entrepreneur):
        record.append_milestone(
            bucket="sp2_milestones",
            key="certificate_issued",
            payload={"id": cert.pk, "member_id": cert.cohort_member_id},
        )
        count += 1
    return count


def _replay_sp3(record, entrepreneur) -> int:
    count = 0
    try:
        from apps.smehub.linkage.models import AdvisorySession, Connection, MoU
    except ImportError:
        return 0

    for connection in Connection.objects.filter(entrepreneur=entrepreneur):
        record.append_milestone(
            bucket="sp3_partnerships",
            key="connection_accepted",
            payload={
                "id": connection.pk,
                "request_id": connection.request_id,
            },
        )
        count += 1
    for mou in MoU.objects.filter(
        connection__entrepreneur=entrepreneur,
        status__in=[MoU.Status.FORMALISED, MoU.Status.AMENDED],
    ):
        record.append_milestone(
            bucket="sp3_partnerships",
            key="mou_formalised",
            payload={"id": mou.pk},
        )
        count += 1
    for session in AdvisorySession.objects.filter(
        entrepreneur=entrepreneur,
        status=AdvisorySession.Status.COMPLETED,
    ):
        record.append_milestone(
            bucket="sp3_partnerships",
            key="advisory_completed",
            payload={"id": session.pk, "provider_id": session.service_provider_id},
        )
        count += 1
    try:
        from apps.smehub.marketplace.models import DemandResponse

        for response in DemandResponse.objects.filter(
            entrepreneur=entrepreneur,
            status=DemandResponse.Status.AWARDED,
        ):
            record.append_milestone(
                bucket="sp3_partnerships",
                key="demand_responded",
                payload={
                    "id": response.pk,
                    "demand_listing_id": response.demand_listing_id,
                    "outcome": DemandResponse.Status.AWARDED,
                },
            )
            count += 1
    except ImportError:
        pass
    return count


def _replay_sp4(record, entrepreneur) -> int:
    count = 0
    try:
        from apps.smehub.showcasing.models import (
            DealAgreement,
            DealRoom,
            ShowcaseApplication,
        )
    except ImportError:
        return 0

    for app in ShowcaseApplication.objects.filter(
        entrepreneur=entrepreneur,
        status=ShowcaseApplication.Status.CONFIRMED,
    ):
        record.append_milestone(
            bucket="sp4_commercialisation",
            key="showcase_participated",
            payload={
                "id": app.pk,
                "target_id": app.target_object_id,
            },
        )
        count += 1
    for room in DealRoom.objects.filter(entrepreneur=entrepreneur):
        record.append_milestone(
            bucket="sp4_commercialisation",
            key="deal_room_opened",
            payload={"id": room.pk, "business_id": room.business_id},
        )
        count += 1
        if room.status == DealRoom.Status.AGREEMENT_REACHED:
            agreement = DealAgreement.objects.filter(deal_room=room).first()
            record.append_milestone(
                bucket="sp4_commercialisation",
                key="agreement_formalised",
                payload={
                    "id": room.pk,
                    "agreement_id": getattr(agreement, "pk", None),
                },
            )
            count += 1
    return count


def _replay_sp5(record, entrepreneur) -> tuple[Decimal, int]:
    """Returns (funding_total_added, milestone_count)."""
    count = 0
    funding_added = Decimal("0")
    try:
        from apps.smehub.investment.models import (
            FundingApplication,
            InvestorConnectionRequest,
            ManualFundingRecord,
        )
    except ImportError:
        return funding_added, 0

    for app in FundingApplication.objects.filter(entrepreneur=entrepreneur):
        if app.status != FundingApplication.Status.DRAFT:
            record.append_milestone(
                bucket="sp5_investment",
                key="funding_application_submitted",
                payload={
                    "id": app.pk,
                    "funding_call_id": app.funding_call_id,
                    "amount": str(app.funding_request_amount)
                    if app.funding_request_amount is not None
                    else None,
                },
            )
            count += 1
        if app.status in {
            FundingApplication.Status.ACCEPTED,
            FundingApplication.Status.REJECTED,
        }:
            record.append_milestone(
                bucket="sp5_investment",
                key="funding_outcome",
                payload={"id": app.pk, "action": app.status},
            )
            count += 1
        if app.status == FundingApplication.Status.ACCEPTED and app.funding_request_amount:
            record.append_milestone(
                bucket="sp5_investment",
                key="funding_secured",
                payload={
                    "amount": str(app.funding_request_amount),
                    "source": "application",
                    "application_id": app.pk,
                },
            )
            count += 1
            funding_added += Decimal(str(app.funding_request_amount))

    for req in InvestorConnectionRequest.objects.filter(
        entrepreneur=entrepreneur,
        status=InvestorConnectionRequest.Status.ACCEPTED,
    ).select_related("deal_room"):
        record.append_milestone(
            bucket="sp5_investment",
            key="investor_connection_accepted",
            payload={
                "id": req.pk,
                "investor_id": req.investor_id,
                "deal_room_id": req.deal_room_id,
            },
        )
        count += 1

    for record_row in ManualFundingRecord.objects.filter(entrepreneur=entrepreneur):
        record.append_milestone(
            bucket="sp5_investment",
            key="funding_secured",
            payload={
                "manual_record_id": record_row.pk,
                "business_id": record_row.business_id,
                "amount": str(record_row.amount) if record_row.amount is not None else None,
                "source": "manual",
            },
        )
        count += 1
        if record_row.amount:
            funding_added += Decimal(str(record_row.amount))

    if funding_added > 0:
        # Replace, not increment — backfill should produce a deterministic total.
        record.funding_secured_total = funding_added
        record.save(update_fields=["funding_secured_total", "updated_at"])
    return funding_added, count


def _refresh_last_milestone_at() -> None:
    """Walk every record and pick the max milestone timestamp across all buckets."""
    from apps.mel.tracking.models import SMEHubTrackingRecord

    for record in SMEHubTrackingRecord.objects.all():
        max_ts = record.sp1_baseline_at
        for bucket in (
            "sp2_milestones",
            "sp3_partnerships",
            "sp4_commercialisation",
            "sp5_investment",
        ):
            for entry in getattr(record, bucket) or []:
                ts_iso = entry.get("ts")
                if not ts_iso:
                    continue
                try:
                    parsed = timezone.datetime.fromisoformat(ts_iso)
                except ValueError:
                    continue
                if max_ts is None or parsed > max_ts:
                    max_ts = parsed
        if max_ts and max_ts != record.last_milestone_at:
            record.last_milestone_at = max_ts
            record.save(update_fields=["last_milestone_at", "updated_at"])