""" ======================================================= Reconstructs InventoryEvent rows and DeadstockSnapshot rows from the existing InventoryItem, Transfer, and related data already in the database. Usage ───── # Dry-run first (prints counts, writes nothing) python manage.py backfill_history --dry-run # Backfill a single business python manage.py backfill_history --business-id= # Backfill all businesses (runs in chunks, safe to re-run — skips duplicates) python manage.py backfill_history # Skip specific phases python manage.py backfill_history --skip-events python manage.py backfill_history --skip-snapshots # Clear existing backfilled events and redo (use with care!) python manage.py backfill_history --clear-backfilled """ from __future__ import annotations import logging from collections import defaultdict from datetime import date, timedelta from decimal import Decimal from django.core.management.base import BaseCommand, CommandError from django.db import transaction from django.db.models import Avg, Count, Q, Sum from django.utils import timezone logger = logging.getLogger(__name__) # ── Status → event type mapping ─────────────────────────────────────────────── # Maps an InventoryItem.status value to the event type we record for it. STATUS_EVENT_MAP = { "IN_STOCK": "ITEM_CREATED", # item entered inventory "SOLD": "ITEM_SOLD", "RETURNED": "ITEM_RETURNED", "DAMAGED": "MARKED_DAMAGED", "IN_REPAIR": "SENT_TO_REPAIR", "REPAIRED": "REPAIR_COMPLETED", "PENDING": "ITEM_CREATED", # unconfirmed arrivals "CONFIRMED": "CONFIRMED_PENDING", "REJECTED": "ITEM_REJECTED", } # Severity per event type SEVERITY_MAP = { "ITEM_CREATED": "SUCCESS", "BATCH_IMPORTED": "SUCCESS", "ITEM_SOLD": "SUCCESS", "ITEM_RETURNED": "WARNING", "MARKED_DAMAGED": "WARNING", "SENT_TO_REPAIR": "WARNING", "REPAIR_COMPLETED": "SUCCESS", "DEADSTOCK_FLAGGED": "CRITICAL", "CONFIRMED_PENDING": "INFO", "ITEM_REJECTED": "WARNING", "TRANSFER_COMPLETED": "INFO", "STATUS_CHANGED": "INFO", } # ───────────────────────────────────────────────────────────────────────────── # Command # ───────────────────────────────────────────────────────────────────────────── class Command(BaseCommand): help = ( "Backfill InventoryEvent and DeadstockSnapshot records from existing " "InventoryItem, Transfer, and related data." ) def add_arguments(self, parser): parser.add_argument( "--business-id", type=str, default=None, help="Limit backfill to one business UUID. Omit to process all.", ) parser.add_argument( "--dry-run", action="store_true", default=False, help="Print what would be created without writing to the database.", ) parser.add_argument( "--skip-events", action="store_true", default=False, help="Skip InventoryEvent backfill; only create DeadstockSnapshots.", ) parser.add_argument( "--skip-snapshots", action="store_true", default=False, help="Skip DeadstockSnapshot creation; only backfill events.", ) parser.add_argument( "--clear-backfilled", action="store_true", default=False, help=( "Delete events tagged with metadata.backfilled=true before " "re-running. Does NOT delete events created by live hooks." ), ) parser.add_argument( "--chunk-size", type=int, default=500, help="Items processed per DB transaction (default: 500).", ) # ────────────────────────────────────────────────────────────────────────── def handle(self, *args, **options): from apps.inventory.models import InventoryEvent, DeadstockSnapshot # noqa: PLC0415 try: from apps.business.models import Business # adjust to your app layout except ImportError: from apps.accounts.models import Business # fallback dry_run = options["dry_run"] business_id = options["business_id"] skip_events = options["skip_events"] skip_snapshots = options["skip_snapshots"] chunk_size = options["chunk_size"] clear_old = options["clear_backfilled"] if dry_run: self.stdout.write(self.style.WARNING("── DRY RUN — nothing will be written ──\n")) # Resolve businesses if business_id: businesses = Business.objects.filter(id=business_id) if not businesses.exists(): raise CommandError(f"Business {business_id} not found.") else: businesses = Business.objects.filter(is_active=True) total_events_created = 0 total_snapshots_created = 0 for business in businesses.iterator(): self.stdout.write(f"\n{'─'*60}") self.stdout.write(f"Business: {business.name} ({business.id})") self.stdout.write(f"{'─'*60}") # Optionally wipe previous backfill for this business if clear_old and not dry_run: deleted, _ = InventoryEvent.objects.filter( business=business, metadata__backfilled=True, ).delete() self.stdout.write(f" Cleared {deleted} previously backfilled events.") if not skip_events: n = self._backfill_events( business, InventoryEvent, dry_run=dry_run, chunk_size=chunk_size, ) total_events_created += n if not skip_snapshots: n = self._backfill_snapshots( business, DeadstockSnapshot, dry_run=dry_run, ) total_snapshots_created += n self.stdout.write(f"\n{'═'*60}") self.stdout.write( self.style.SUCCESS( f"Done. Events: {total_events_created} | " f"Snapshots: {total_snapshots_created}" + (" (DRY RUN — nothing written)" if dry_run else "") ) ) # ────────────────────────────────────────────────────────────────────────── # Phase 1 — InventoryEvent backfill # ────────────────────────────────────────────────────────────────────────── def _backfill_events(self, business, InventoryEvent, *, dry_run, chunk_size) -> int: """ Create InventoryEvent rows derived from existing InventoryItem records and (if the model exists) Transfer records. Strategy per item ───────────────── 1. ITEM_CREATED — from item.created_at (always) 2. ITEM_SOLD — from item.last_sold_date (if status == SOLD and date exists) 3. STATUS_CHANGED — for items whose current status differs from IN_STOCK and we have no other specific event for it 4. DEADSTOCK_FLAGGED — for items that are currently DEAD tier 5. TRANSFER_COMPLETED — one per completed Transfer row linked to this item All created events are tagged with metadata.backfilled=True so they can be cleared and re-run without affecting live events. """ from apps.inventory.models import InventoryItem # noqa: PLC0415 # Pre-fetch existing backfilled event (item_id, event_type) pairs so we # skip duplicates on re-runs without relying on unique constraints. existing = set( InventoryEvent.objects.filter( business=business, metadata__backfilled=True, ).values_list("inventory_item_id", "event_type") ) items = ( InventoryItem.objects.filter(business=business) .select_related("brand", "category", "location", "supplier") .order_by("created_at") ) total_count = items.count() created_count = 0 to_create: list = [] self.stdout.write(f" Backfilling events for {total_count} items …") def flush(force=False): nonlocal created_count if to_create and (force or len(to_create) >= chunk_size): if not dry_run: with transaction.atomic(): InventoryEvent.objects.bulk_create( to_create, ignore_conflicts=True ) created_count += len(to_create) to_create.clear() for item in items.iterator(chunk_size=chunk_size): events_for_item = self._events_for_item(item, business, existing) to_create.extend(events_for_item) flush() flush(force=True) # Transfers (optional — skip gracefully if no Transfer model exists) transfer_count = self._backfill_transfer_events( business, InventoryEvent, existing, dry_run, chunk_size ) created_count += transfer_count self.stdout.write( self.style.SUCCESS(f" ✓ {created_count} events {'would be ' if dry_run else ''}created") ) return created_count # ── Per-item event builder ──────────────────────────────────────────────── def _events_for_item(self, item, business, existing: set) -> list: """Return a list of unsaved InventoryEvent instances for one item.""" from apps.inventory.models import InventoryEvent # noqa: PLC0415 events = [] def _make(event_type, event_date, **kwargs): """Build an InventoryEvent instance without saving.""" if (item.id, event_type) in existing: return # already backfilled severity = SEVERITY_MAP.get(event_type, "INFO") group_key = getattr(item, "product_group_key", "") or "" brand_name = item.brand.name if item.brand else "" model_name = getattr(item, "model", "") or "" category_nm = item.category.name if item.category else "" loc_name = item.location.name if item.location else "" events.append(InventoryEvent( business_id = business.id, inventory_item = item, event_type = event_type, severity = severity, event_date = event_date, product_group_key = group_key, brand_name = brand_name, model_name = model_name, category_name = category_nm, asset_id = item.asset_id or "", location_name_snapshot = loc_name, metadata = {"backfilled": True, **kwargs.pop("metadata", {})}, **kwargs, )) # 1. ITEM_CREATED — always from created_at _make( "ITEM_CREATED", event_date = item.created_at, status_after = "IN_STOCK", price_after = item.purchase_price, description = f"[Backfilled] {item.brand.name if item.brand else ''} " f"{getattr(item, 'model', '')} added to inventory".strip(), ) # 2. ITEM_SOLD — when status is SOLD and we have a date if item.status == "SOLD": sold_date = getattr(item, "last_sold_date", None) or getattr(item, "sold_date", None) if sold_date: _make( "ITEM_SOLD", event_date = sold_date if hasattr(sold_date, "hour") else timezone.datetime.combine(sold_date, timezone.datetime.min.time(), tzinfo=timezone.get_current_timezone()), status_before = "IN_STOCK", status_after = "SOLD", price_before = item.selling_price, description = f"[Backfilled] Item sold", ) # 3. MARKED_DAMAGED if item.status == "DAMAGED": _make( "MARKED_DAMAGED", event_date = item.updated_at if hasattr(item, "updated_at") else item.created_at, status_before = "IN_STOCK", status_after = "DAMAGED", severity = "WARNING", description = "[Backfilled] Item marked as damaged", ) # 4. SENT_TO_REPAIR / REPAIR_COMPLETED if item.status == "IN_REPAIR": _make( "SENT_TO_REPAIR", event_date = item.updated_at if hasattr(item, "updated_at") else item.created_at, status_before = "DAMAGED", status_after = "IN_REPAIR", description = "[Backfilled] Item sent to repair", ) if item.status == "REPAIRED": _make( "REPAIR_COMPLETED", event_date = item.updated_at if hasattr(item, "updated_at") else item.created_at, status_before = "IN_REPAIR", status_after = "REPAIRED", severity = "SUCCESS", description = "[Backfilled] Repair completed", ) # 5. ITEM_RETURNED if item.status == "RETURNED": returned_date = getattr(item, "returned_date", None) or getattr(item, "updated_at", item.created_at) _make( "ITEM_RETURNED", event_date = returned_date, status_before = "SOLD", status_after = "RETURNED", severity = "WARNING", description = "[Backfilled] Item returned", ) # 6. DEADSTOCK_FLAGGED — for items already in a bad tier tier = getattr(item, "deadstock_tier", "HEALTHY") if tier in ("STALE", "DEAD") and item.status == "IN_STOCK": flagged_at = getattr(item, "deadstock_flagged_at", None) or item.created_at _make( "DEADSTOCK_FLAGGED", event_date = flagged_at, severity = "CRITICAL", description = f"[Backfilled] Item flagged as {tier.lower()} deadstock", metadata = { "backfilled": True, "tier": tier, "score": getattr(item, "deadstock_score", 0), "days_in_stock": getattr(item, "days_in_stock", 0), }, ) return events # ── Transfer events ─────────────────────────────────────────────────────── def _backfill_transfer_events( self, business, InventoryEvent, existing: set, dry_run: bool, chunk_size: int ) -> int: """ Attempt to import Transfer model and create TRANSFER_COMPLETED events. Skips silently if Transfer model doesn't exist or has no relevant fields. """ try: from apps.inventory.models import Transfer # adjust if needed except ImportError: self.stdout.write(" (No Transfer model found — skipping transfer events)") return 0 transfers = ( Transfer.objects.filter(business=business, status="COMPLETED") .select_related("inventory_item", "from_location", "to_location") .order_by("completed_at") ) count = 0 to_create = [] for t in transfers.iterator(chunk_size=chunk_size): item = getattr(t, "inventory_item", None) if not item: continue if (item.id, "TRANSFER_COMPLETED") in existing: continue completed_at = getattr(t, "completed_at", None) or getattr(t, "updated_at", timezone.now()) from_loc = getattr(t, "from_location", None) to_loc = getattr(t, "to_location", None) to_create.append(InventoryEvent( business_id = business.id, inventory_item = item, event_type = "TRANSFER_COMPLETED", severity = "INFO", event_date = completed_at, product_group_key = getattr(item, "product_group_key", "") or "", brand_name = item.brand.name if item.brand else "", model_name = getattr(item, "model", "") or "", category_name = item.category.name if item.category else "", asset_id = item.asset_id or "", location_from = from_loc, location_to = to_loc, location_name_snapshot = to_loc.name if to_loc else "", description = f"[Backfilled] Transfer completed" + (f" → {to_loc.name}" if to_loc else ""), reference_type = "transfer", reference_id = str(t.id), metadata = { "backfilled": True, "transfer_id": str(t.id), "from_location": from_loc.name if from_loc else None, "to_location": to_loc.name if to_loc else None, }, )) if len(to_create) >= chunk_size: if not dry_run: with transaction.atomic(): InventoryEvent.objects.bulk_create(to_create, ignore_conflicts=True) count += len(to_create) to_create.clear() if to_create: if not dry_run: with transaction.atomic(): InventoryEvent.objects.bulk_create(to_create, ignore_conflicts=True) count += len(to_create) self.stdout.write(f" ✓ {count} transfer events {'would be ' if dry_run else ''}created") return count # ────────────────────────────────────────────────────────────────────────── # Phase 2 — DeadstockSnapshot backfill # ────────────────────────────────────────────────────────────────────────── def _backfill_snapshots(self, business, DeadstockSnapshot, *, dry_run) -> int: """ Create one DeadstockSnapshot per product_group_key representing the *current* state of every unsold item group. For historical day-by-day snapshots we would need a time-machine view of old data that doesn't exist, so we create a single "today" snapshot per group which acts as the baseline going forward. DeadstockSnapshot.unique_together = (business, snapshot_date, product_group_key) so this is safe to re-run — duplicates are silently ignored. """ from apps.inventory.models import InventoryItem # noqa: PLC0415 today = date.today() # Aggregate per product_group_key for IN_STOCK items groups = ( InventoryItem.objects.filter(business=business, status="IN_STOCK") .values( "product_group_key", "brand__name", "model", "category__name", "location_id", ) .annotate( healthy_count = Count("id", filter=Q(deadstock_tier="HEALTHY")), aging_count = Count("id", filter=Q(deadstock_tier="AGING")), stale_count = Count("id", filter=Q(deadstock_tier="STALE")), dead_count = Count("id", filter=Q(deadstock_tier="DEAD")), total_count = Count("id"), total_purchase_value = Sum("purchase_price"), deadstock_value = Sum( "purchase_price", filter=Q(deadstock_tier__in=["STALE", "DEAD"]), ), max_days = Count("days_in_stock"), # placeholder; see below avg_score = Avg("deadstock_score"), ) ) # Also get max_days_in_stock per group in a separate query (simpler) from django.db.models import Max # noqa: PLC0415 max_days_map: dict = { row["product_group_key"]: row["max_days"] for row in InventoryItem.objects.filter(business=business, status="IN_STOCK") .values("product_group_key") .annotate(max_days=Max("days_in_stock")) } # Pre-fetch existing snapshots for today to avoid duplicates existing_keys = set( DeadstockSnapshot.objects.filter( business=business, snapshot_date=today ).values_list("product_group_key", flat=True) ) to_create = [] for g in groups: key = g["product_group_key"] or "" if not key or key in existing_keys: continue # Determine dominant tier counts = { "DEAD": g["dead_count"] or 0, "STALE": g["stale_count"] or 0, "AGING": g["aging_count"] or 0, "HEALTHY": g["healthy_count"] or 0, } dominant = max(counts, key=counts.get) to_create.append(DeadstockSnapshot( business_id = business.id, snapshot_date = today, product_group_key = key, brand_name = g.get("brand__name", "") or "", model_name = g.get("model", "") or "", category_name = g.get("category__name", "") or "", location_id = g.get("location_id"), healthy_count = g["healthy_count"] or 0, aging_count = g["aging_count"] or 0, stale_count = g["stale_count"] or 0, dead_count = g["dead_count"] or 0, total_count = g["total_count"] or 0, total_purchase_value = g["total_purchase_value"] or Decimal("0"), total_deadstock_value = g["deadstock_value"] or Decimal("0"), max_days_in_stock = max_days_map.get(key, 0) or 0, avg_deadstock_score = g["avg_score"] or 0.0, deadstock_tier_dominant = dominant, )) if to_create and not dry_run: with transaction.atomic(): DeadstockSnapshot.objects.bulk_create(to_create, ignore_conflicts=True) count = len(to_create) self.stdout.write( self.style.SUCCESS( f" ✓ {count} deadstock snapshots {'would be ' if dry_run else ''}created " f"(date: {today})" ) ) return count