"""
Data Migration: Populate new Serial No fields from Device Condition Report data.

Run this IMMEDIATELY after `bench migrate` on production.
Requires: all new fields already exist (grade, cpu_test, ram_test, etc.)

Maps cosmetic_grade C# values from latest Device Condition Report → new grade field:
  C5-C9 → High
  C4 → Med
  C3 → Low
  C0-C2, blank, anything else → Flagged
"""

import frappe
from frappe.utils import now

def run():
    print(f"[{now()}] Starting Serial No grade migration...")
    
    # Get all Serial Nos with their latest Device Condition Report
    serials = frappe.get_all("Serial No",
        fields=["name"],
        limit_page_length=0
    )
    
    stats = {"high": 0, "med": 0, "low": 0, "flagged": 0, "skipped": 0, "errors": 0, "no_report": 0}
    
    for i, s in enumerate(serials):
        try:
            # Find latest Device Condition Report for this serial
            reports = frappe.get_all("Device Condition Report",
                filters={"serial_no": s.name},
                fields=["cosmetic_grade"],
                order_by="creation DESC",
                limit=1
            )
            
            if not reports:
                # No report found - flag it
                new_grade = "Flagged"
                stats["no_report"] += 1
            else:
                cosmetic = (reports[0].cosmetic_grade or "").strip().upper()
                
                # Map cosmetic_grade → new grade
                if cosmetic in ("C5", "C6", "C7", "C8", "C9"):
                    new_grade = "High"
                    stats["high"] += 1
                elif cosmetic == "C4":
                    new_grade = "Med"
                    stats["med"] += 1
                elif cosmetic == "C3":
                    new_grade = "Low"
                    stats["low"] += 1
                else:
                    new_grade = "Flagged"
                    stats["flagged"] += 1
            
            # Only update if grade is blank
            current_grade = frappe.db.get_value("Serial No", s.name, "grade")
            if current_grade and current_grade in ("High", "Med", "Low", "Flagged"):
                stats["skipped"] += 1
                continue
            
            # Determine pricing_status
            if new_grade == "Flagged":
                pricing_status = "Flagged"
            else:
                pricing_status = "Needs Pricing"
            
            frappe.db.set_value("Serial No", s.name, {
                "grade": new_grade,
                "pricing_status": pricing_status
            })
            
            if (i + 1) % 1000 == 0:
                frappe.db.commit()
                print(f"  Processed {i + 1}/{len(serials)}...")
        
        except Exception as e:
            stats["errors"] += 1
            frappe.log_error(f"Migration error for {s.name}: {e}", "Serial Grade Migration")
    
    frappe.db.commit()
    
    print(f"\n[{now()}] Migration complete!")
    print(f"  Total Serial Nos: {len(serials)}")
    print(f"  High: {stats['high']}")
    print(f"  Med: {stats['med']}")
    print(f"  Low: {stats['low']}")
    print(f"  Flagged (from missing/bad grade): {stats['flagged']}")
    print(f"  Flagged (no report found): {stats['no_report']}")
    print(f"  Skipped (already migrated): {stats['skipped']}")
    print(f"  Errors: {stats['errors']}")

if __name__ == "__main__":
    run()