import pandas as pd
import numpy as np
from datetime import datetime

# Display settings for better output formatting
pd.set_option('display.max_columns', None)
pd.set_option('display.width', None)
pd.set_option('display.float_format', '{:.2f}'.format)

print("Libraries imported successfully!")
print(f"Pandas version: {pd.__version__}")
print(f"NumPy version: {np.__version__}")

# Load the sales data
df = pd.read_csv('/Users/bamr87/github/zer0-mistakes/assets/data/notebooks/sales_data.csv')

# Convert date column to datetime
df['date'] = pd.to_datetime(df['date'])

# Display first few rows
print("📊 Sales Data - First 10 rows:")
df.head(10)

# Get basic information about the dataset
print("📋 Dataset Information:")
print(f"Shape: {df.shape[0]} rows × {df.shape[1]} columns")
print(f"\nColumn names: {list(df.columns)}")
print("\n" + "="*60)
df.info()

# Statistical summary of numeric columns
print("📈 Statistical Summary:")
df.describe()

# Filter: Get all Electronics sales
electronics_sales = df[df['category'] == 'Electronics']
print(f"🔌 Electronics Sales: {len(electronics_sales)} transactions")
electronics_sales.head()

# Multiple conditions: High-value sales (revenue > $3000) in the North region
high_value_north = df[(df['revenue'] > 3000) & (df['region'] == 'North')]
print(f"💰 High-value North sales: {len(high_value_north)} transactions")
print(f"Total revenue: ${high_value_north['revenue'].sum():,.2f}")
high_value_north

# Using query() method - more readable for complex filters
q3_sales = df.query("date >= '2025-07-01' and date <= '2025-09-30'")
print(f"📅 Q3 2025 Sales: {len(q3_sales)} transactions")
print(f"Q3 Total Revenue: ${q3_sales['revenue'].sum():,.2f}")
q3_sales.head()

# Group by category and calculate total revenue
category_revenue = df.groupby('category')['revenue'].sum().sort_values(ascending=False)
print("💵 Revenue by Category:")
category_revenue

# Group by region and calculate multiple metrics
region_stats = df.groupby('region').agg({
    'revenue': ['sum', 'mean', 'count'],
    'quantity': 'sum'
}).round(2)

# Flatten column names
region_stats.columns = ['total_revenue', 'avg_revenue', 'num_transactions', 'total_units']
region_stats = region_stats.sort_values('total_revenue', ascending=False)

print("🌍 Sales Statistics by Region:")
region_stats

# Top salespeople by revenue
salesperson_revenue = df.groupby('salesperson')['revenue'].sum().sort_values(ascending=False)
print("🏆 Top Salespeople by Total Revenue:")
salesperson_revenue

# Pivot table: Revenue by Region and Category
pivot_region_category = pd.pivot_table(
    df,
    values='revenue',
    index='region',
    columns='category',
    aggfunc='sum',
    fill_value=0
)

print("📊 Revenue by Region and Category:")
pivot_region_category

# Add month column for time-based analysis
df['month'] = df['date'].dt.to_period('M')

# Pivot table: Monthly revenue by product
pivot_monthly = pd.pivot_table(
    df,
    values='revenue',
    index='product',
    columns='month',
    aggfunc='sum',
    fill_value=0
)

print("📅 Monthly Revenue by Product:")
pivot_monthly

# Generate key business insights
print("=" * 60)
print("📊 SALES ANALYSIS SUMMARY")
print("=" * 60)

# Overall metrics
total_revenue = df['revenue'].sum()
avg_transaction = df['revenue'].mean()
total_units = df['quantity'].sum()
num_transactions = len(df)

print(f"\n💰 Total Revenue: ${total_revenue:,.2f}")
print(f"📦 Total Units Sold: {total_units:,}")
print(f"🧾 Number of Transactions: {num_transactions:,}")
print(f"💵 Average Transaction Value: ${avg_transaction:,.2f}")

# Best performing
best_product = df.groupby('product')['revenue'].sum().idxmax()
best_region = df.groupby('region')['revenue'].sum().idxmax()
best_salesperson = df.groupby('salesperson')['revenue'].sum().idxmax()

print(f"\n🏆 Best Selling Product: {best_product}")
print(f"🌍 Top Region: {best_region}")
print(f"⭐ Top Salesperson: {best_salesperson}")

# Time range
print(f"\n📅 Date Range: {df['date'].min().date()} to {df['date'].max().date()}")
print("=" * 60)