movement_id: string
movement_date: date32
sender_chr_number: int64
sender_herd_number: string
sender_address: string
sender_municipality_code: string
receiver_chr_number: int64
receiver_herd_number: string
receiver_address: string
receiver_municipality_code: string
total_animals: int64
sow_count: int64
slaughter_pig_count: int64
piglet_count: int64
boar_count: int64
vehicle_registration: string
[51 columns total]

reporting_herd_number: int64
animal_type: string
period_start: date32
period_end: date32
animals_in: int64
animals_out: int64
births: int64
deaths: int64
animal_count: int64
[11 columns total]

import pyarrow.parquet as pq
from google.cloud import storage
import io

client = storage.Client()
bucket = client.bucket('$GCS_BUCKET')

# Read svineflytning movements
blob = bucket.blob('silver/svineflytning/2025-01-10/movements.parquet')
buffer = io.BytesIO()
blob.download_to_file(buffer)
buffer.seek(0)
df = pq.read_table(buffer).to_pandas()

chr_number = 123456

# Find all movements involving this CHR (as sender or receiver)
outgoing = df[df['sender_chr_number'] == chr_number]
incoming = df[df['receiver_chr_number'] == chr_number]

print(f"Outgoing movements: {len(outgoing)}, Animals sent: {outgoing['total_animals'].sum()}")
print(f"Incoming movements: {len(incoming)}, Animals received: {incoming['total_animals'].sum()}")

# Get movement data to estimate animal counts
movements = df.groupby('receiver_municipality_code').agg({
    'total_animals': 'sum'
}).reset_index()

# Join with land area data (from landbrugsareal skill)
# to calculate animals per hectare

# Create network of farm connections
import networkx as nx

G = nx.DiGraph()

for _, row in df.iterrows():
    sender = row['sender_chr_number']
    receiver = row['receiver_chr_number']
    animals = row['total_animals']

    if G.has_edge(sender, receiver):
        G[sender][receiver]['weight'] += animals
    else:
        G.add_edge(sender, receiver, weight=animals)

# Find most connected farms
centrality = nx.degree_centrality(G)
top_farms = sorted(centrality.items(), key=lambda x: x[1], reverse=True)[:10]

import pandas as pd

# Convert to datetime and aggregate by month
df['movement_month'] = pd.to_datetime(df['movement_date']).dt.to_period('M')

monthly_movements = df.groupby('movement_month').agg({
    'movement_id': 'count',
    'total_animals': 'sum'
}).reset_index()
monthly_movements.columns = ['month', 'movement_count', 'total_animals']

# Aggregate by sender-receiver municipality pairs
routes = df.groupby(['sender_municipality_code', 'receiver_municipality_code']).agg({
    'total_animals': 'sum',
    'movement_id': 'count'
}).reset_index()
routes.columns = ['from_muni', 'to_muni', 'total_animals', 'trips']

# Top routes
top_routes = routes.nlargest(10, 'total_animals')

# Calculate proportion of different animal types
df['pct_slaughter'] = df['slaughter_pig_count'] / df['total_animals'] * 100
df['pct_sows'] = df['sow_count'] / df['total_animals'] * 100
df['pct_piglets'] = df['piglet_count'] / df['total_animals'] * 100

# Movements by type
slaughter_movements = df[df['slaughter_pig_count'] > 0]
breeding_movements = df[df['sow_count'] > 0]

# CHR to CVR mapping typically comes from:
# - bronze/chr/*/chr_bedrifter.parquet
# - Contains chr_number -> cvr_number mapping

def trace_contacts(chr_number, df, days_back=21):
    """Find all farms that had contact with a CHR within time window."""
    # Get movement dates for this CHR
    outgoing = df[df['sender_chr_number'] == chr_number]
    incoming = df[df['receiver_chr_number'] == chr_number]

    if len(outgoing) == 0 and len(incoming) == 0:
        return []

    # Get date range
    all_dates = pd.concat([outgoing['movement_date'], incoming['movement_date']])
    max_date = all_dates.max()
    min_date = max_date - pd.Timedelta(days=days_back)

    # Filter to time window
    recent_out = outgoing[outgoing['movement_date'] >= min_date]
    recent_in = incoming[incoming['movement_date'] >= min_date]

    # Get contact CHRs
    contacts = set(recent_out['receiver_chr_number'].tolist())
    contacts.update(recent_in['sender_chr_number'].tolist())
    contacts.discard(chr_number)

    return list(contacts)

# List svineflytning snapshots
gsutil ls gs://$GCS_BUCKET/silver/svineflytning/

# List CHR data
gsutil ls gs://$GCS_BUCKET/bronze/chr/

# List animal welfare data
gsutil ls gs://$GCS_BUCKET/silver/animal\ welfare/

# List animal mortality data
gsutil ls gs://$GCS_BUCKET/silver/animal\ mortality/