import pandas as pd
import json

# Read the mock orders file
print("Reading data/mock_orders.csv...")
df = pd.read_csv('data/mock_orders.csv', sep='\t')  # Tab-separated

# Parse the JSON items column
order_lines = []

for idx, row in df.iterrows():
    try:
        # Parse the JSON string in the items column
        items = json.loads(row['items'].replace("'", '"'))
        
        for item in items:
            order_lines.append({
                'order_date': row['order_date'],
                'sku_id': item['sku'],
                'qty': item['quantity'],
                'order_id': row['order_id']
            })
    except Exception as e:
        print(f"Error parsing order {row['order_id']}: {e}")
        continue

# Create DataFrame
result = pd.DataFrame(order_lines)

# Save to file
output_path = 'app/data/orders_for_forecast.csv'
result.to_csv(output_path, index=False)

print(f"\n✓ Created {output_path}")
print(f"  Total order lines: {len(result)}")
print(f"  Date range: {result['order_date'].min()} to {result['order_date'].max()}")
print(f"  Unique SKUs: {result['sku_id'].nunique()}")
print(f"\nFirst few rows:")
print(result.head(10))
print(f"\nSample SKUs:")
print(result['sku_id'].value_counts().head(10))