db = SessionLocal()

try:
    # ===================================================================
    # 1. School
    # ===================================================================
    school = School(
        id="schl_001",
        name="Preble High School",
        domain="preble.k12.wi.us",
        config={
            "primary_color": "#0f766e",
            "logo_url": "/school/preble/logo.svg",
            "sport_names": {
                "Football": "Varsity Football",
                "Soccer": "Boys Soccer",
                "Basketball": "Varsity Basketball"
            }
        },
        created_at=datetime.utcnow(),
        updated_at=datetime.utcnow()
    )
    db.add(school)
    db.commit()
    print(f"Created school: {school.name}")

    # ===================================================================
    # 2. Users  (ALL password: testpass123, bcrypt-hashed)
    # ===================================================================
    hashed = hash_password("testpass123")

    users = [
        User(
            id="usr_parent001",
            school_id="schl_001",
            email="parent@example.com",
            hashed_password=hashed,
            role="parent",
            first_name="Jennifer",
            last_name="Smith",
            is_active=True,
            assigned_sports=[],
            assigned_teams=[],
            created_at=datetime.utcnow(),
            updated_at=datetime.utcnow()
        ),
        User(
            id="usr_coach001",
            school_id="schl_001",
            email="coach@preble.k12.wi.us",
            hashed_password=hashed,
            role="coach",
            first_name="Mike",
            last_name="Andrews",
            is_active=True,
            assigned_sports=["Football"],
            assigned_teams=[],
            created_at=datetime.utcnow(),
            updated_at=datetime.utcnow()
        ),
        User(
            id="usr_at001",
            school_id="schl_001",
            email="at@preble.k12.wi.us",
            hashed_password=hashed,
            role="at",
            first_name="Lisa",
            last_name="Johnson",
            is_active=True,
            assigned_sports=[],
            assigned_teams=[],
            created_at=datetime.utcnow(),
            updated_at=datetime.utcnow()
        ),
        User(
            id="usr_admin001",
            school_id="schl_001",
            email="admin@preble.k12.wi.us",
            hashed_password=hashed,
            role="admin",
            first_name="David",
            last_name="Reynolds",
            is_active=True,
            assigned_sports=[],
            assigned_teams=[],
            created_at=datetime.utcnow(),
            updated_at=datetime.utcnow()
        ),
    ]
    for u in users:
        db.add(u)
    db.commit()
    print(f"Created {len(users)} users")

    # ===================================================================
    # 3. Athletes  (8+, per spec)
    # ===================================================================
    athletes_data = [
        ("ath_001", "Jake",     "Larson",   12, ["Football"],    "Varsity", []),
        ("ath_002", "Marcus",   "Johnson",  11, ["Football"],    "Varsity", []),
        ("ath_003", "Tyler",    "Wilson",   10, ["Football"],    "Varsity", []),
        ("ath_004", "Derek",    "Lee",      11, ["Football"],    "Varsity", []),
        ("ath_005", "Cameron",  "Davis",    10, ["Football"],    "JV",      []),
        ("ath_006", "Ethan",    "Brooks",   11, ["Football"],    "Varsity", []),
        ("ath_007", "Emma",     "Larson",    9, ["Soccer"],      "JV",      []),
        ("ath_008", "Ryan",     "Miller",   12, ["Basketball"],  "Varsity", []),
    ]

    created_athletes = {}
    for aid, first, last, grade, sports, team, parent_ids in athletes_data:
        athlete = Athlete(
            id=aid,
            school_id="schl_001",
            first_name=first,
            last_name=last,
            grade=grade,
            sports=sports,
            team=team,
            parent_ids=parent_ids,
            current_status="cleared",  # updated per case below
            active_case_ids=[],
            created_at=datetime.utcnow(),
            updated_at=datetime.utcnow()
        )
        db.add(athlete)
        created_athletes[aid] = athlete
    db.commit()
    print(f"Created {len(created_athletes)} athletes")

    # ===================================================================
    # 4. Cases  (5 active, per spec)
    # ===================================================================
    # Jake → out (ankle sprain)
    # Marcus → out (ACL)
    # Tyler → modified (knee)
    # Derek → modified (shoulder)
    # Cameron → out (hamstring)
    # Ethan → cleared (no case — stays cleared)
    # Emma → cleared (no case)
    # Ryan → out (basketball injury)
    now = datetime.utcnow()

    cases_data = [
        ("case_001", "ath_001", "Right ankle sprain",       "moderate", "active_rehab",         "active"),
        ("case_002", "ath_002", "ACL tear — left knee",     "severe",   "initial_assessment",    "active"),
        ("case_003", "ath_003", "Knee injury — left",       "mild",     "return_to_play",        "active"),
        ("case_004", "ath_004", "Shoulder injury — left",   "moderate", "return_to_play",        "active"),
        ("case_005", "ath_005", "Hamstring strain — right", "moderate", "initial_assessment",    "active"),
    ]

    status_map = {
        "case_001": "out",
        "case_002": "out",
        "case_003": "modified",
        "case_004": "modified",
        "case_005": "out",
        "ath_006": "cleared",   # Ethan
        "ath_007": "cleared",   # Emma
        "ath_008": "out",       # Ryan (no case but should be out)
    }

    # Add a 6th case for Ryan Miller (basketball) to make him "out"
    cases_data.append(
        ("case_006", "ath_008", "Right wrist fracture", "moderate", "active_rehab", "active")
    )

    created_cases = {}
    for i, (cid, aid, title, severity, phase, status) in enumerate(cases_data):
        opened_at = now - timedelta(days=21)
        case = Case(
            id=cid,
            school_id="schl_001",
            athlete_id=aid,
            title=title,
            severity=severity,
            attention_level="stable",
            status=status,
            opened_at=opened_at,
            resolved_at=None,
            primary_at_id="usr_at001",
            created_at=opened_at,
            updated_at=now
        )
        db.add(case)
        created_cases[cid] = case

        # Update athlete status
        athlete = created_athletes.get(aid)
        if athlete:
            athlete.current_status = status_map.get(cid, "out") if status == "active" else "cleared"
            athlete.active_case_ids = [cid]

    db.commit()

    # Update Ryan Miller's status (case_006 makes him out)
    ryan = created_athletes.get("ath_008")
    if ryan:
        ryan.current_status = "out"
        ryan.active_case_ids = ["case_006"]

    # Ethan and Emma stay cleared
    for eid in ["ath_006", "ath_007"]:
        a = created_athletes.get(eid)
        if a:
            a.current_status = "cleared"
            a.active_case_ids = []

    db.commit()
    print(f"Created {len(created_cases)} cases")

    # ===================================================================
    # 5. Milestones  (4 per case, per spec)
    # ===================================================================
    milestone_defs = [
        ("Initial Assessment",     0),
        ("Active Rehab",           7),
        ("Return to Play",        14),
        ("Full Clearance",        21),
    ]

    milestones_created = 0
    for cid, case_obj in created_cases.items():
        opened = case_obj.opened_at
        for j, (m_title, offset_days) in enumerate(milestone_defs):
            target_date = opened + timedelta(days=offset_days)
            # Determine status based on elapsed time
            if target_date < now - timedelta(days=1):
                m_status = "achieved"
                achieved_at = target_date
            elif target_date < now + timedelta(days=7):
                m_status = "pending"
                achieved_at = None
            else:
                m_status = "pending"
                achieved_at = None

            milestone = Milestone(
                id=f"mil_{cid}_{j+1}",
                school_id="schl_001",
                case_id=cid,
                title=m_title,
                target_date=target_date,
                status=m_status,
                achieved_at=achieved_at,
                created_at=datetime.utcnow(),
                updated_at=datetime.utcnow()
            )
            db.add(milestone)
            milestones_created += 1

    db.commit()
    print(f"Created {milestones_created} milestones")

    # ===================================================================
    # Summary
    # ===================================================================
    print("\n" + "=" * 60)
    print("  ✅  Database seeding complete!")
    print("=" * 60)
    print(f"\n  DB path: rtsport.db")
    print(f"\n  Users (password: testpass123):")
    print(f"    parent@example.com          → parent")
    print(f"    coach@preble.k12.wi.us      → coach  (Football)")
    print(f"    at@preble.k12.wi.us         → at")
    print(f"    admin@preble.k12.wi.us      → admin")
    print(f"\n  Athletes: {len(created_athletes)}")
    print(f"  Cases:    {len(created_cases)}")
    print(f"  Milestones: {milestones_created}")
    print(f"\n  Athlete Status:")
    for aid in ["ath_001","ath_002","ath_003","ath_004","ath_005","ath_006","ath_007","ath_008"]:
        a = created_athletes.get(aid)
        if a:
            print(f"    {a.id}: {a.first_name} {a.last_name} ({a.sports[0]}/{a.team}) → {a.current_status}")

except Exception as e:
    db.rollback()
    print(f"ERROR seeding database: {e}")
    raise
finally:
    db.close()