import pandas as pd
from pathlib import Path

# Read the files
orders = pd.read_csv('app/data/orders.csv')
order_lines = pd.read_csv('app/data/order_lines.csv')

# Merge order_lines with orders to get the date
merged = order_lines.merge(
    orders[['order_id', 'order_ts']], 
    on='order_id', 
    how='left'
)

# Rename order_ts to order_date for the forecasting script
merged = merged.rename(columns={'order_ts': 'order_date'})

# Reorder columns: order_date, sku_id, qty
merged = merged[['order_date', 'sku_id', 'qty', 'order_id']]

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

print(f"✓ Created {output_path}")
print(f"  Total rows: {len(merged)}")
print(f"\nFirst few rows:")
print(merged.head(10))
print(f"\nSample SKUs in the data:")
print(merged['sku_id'].unique()[:10])