import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')

np.random.seed(42)
n_transactions = 1000
n_customers = 200
df = pd.DataFrame({
    'transaction_id': range(1, n_transactions + 1),
    'customer_id': np.random.choice([f'CUST_{i:04d}' for i in range(1, n_customers + 1)], n_transactions),
    'amount': np.round(np.random.normal(150, 60, n_transactions), 2),
    'transaction_type': np.random.choice(['Debit', 'Credit'], n_transactions),
    'channel': np.random.choice(['ATM', 'Online', 'Branch', 'POS'], n_transactions),
    'date': pd.date_range(start='2024-01-01', periods=n_transactions, freq='h')
})
print(df.shape)
print(df.head(3))

customer_ids = [f'CUST_{i:04d}' for i in range(1, 201)]
customers = pd.DataFrame({
    'customer_id': customer_ids,
    'segment': ['Retail'] * 150 + ['Business'] * 50,
    'region': ['Metro'] * 100 + ['Regional'] * 100
})
print(customers.shape)
print(customers.head(3))

accounts = pd.DataFrame({
    'account_id': [f'ACC_{i:05d}' for i in range(1, 201)],
    'customer_id': customer_ids,
    'account_type': np.random.choice(['Savings', 'Cheque', 'Credit'], size=200),
    'open_date': pd.date_range(start='2015-01-01', periods=200, freq='30D')
})
print(accounts.shape)
print(accounts.head(3))

# Beginner Example 1: Select all debit transactions
debits_only = df.query("transaction_type == 'Debit'")
print(debits_only.head(3))

# Beginner Example 2: Top 5 largest transactions
top_transactions = df.nlargest(5, 'amount')
print(top_transactions[['transaction_id', 'amount', 'customer_id']])

# Beginner Example 3: Count of transactions by channel
channel_counts = df['channel'].value_counts()
print(channel_counts)

# Intermediate Example 1: Monthly sum of debits vs credits
df['month'] = df['date'].dt.to_period('M')
monthly_sums = df.pivot_table(index='month', columns='transaction_type', values='amount', aggfunc='sum', fill_value=0)
print(monthly_sums)

# Intermediate Example 2: Join transactions to customers for region analysis
region_merged = df.merge(customers, on='customer_id', how='left')
print(region_merged[['customer_id', 'region', 'amount']].head(3))

# Intermediate Example 3: Average transaction per segment
segment_merged = df.merge(customers, on='customer_id', how='left')
avg_by_segment = segment_merged.groupby('segment')['amount'].mean()
print(avg_by_segment)

# Advanced Example 1: Number of unique customers per channel per month
unique_per_channel = df.groupby(['month', 'channel'])['customer_id'].nunique().unstack(fill_value=0)
print(unique_per_channel)

# Advanced Example 2: Top 3 account types by total credit volume
merged_accounts = df.merge(accounts, on='customer_id', how='left')
credit_vol = merged_accounts[merged_accounts['transaction_type'] == 'Credit']
top_account_types = credit_vol.groupby('account_type')['amount'].sum().nlargest(3)
print(top_account_types)

# Advanced Example 3: Running balance for a sample account
sample_account = merged_accounts['account_id'].iloc[0]
sample_txns = merged_accounts[merged_accounts['account_id'] == sample_account].sort_values('date')
sample_txns['signed_amount'] = sample_txns['amount'] * sample_txns['transaction_type'].map({'Debit': -1, 'Credit': 1})
sample_txns['balance'] = sample_txns['signed_amount'].cumsum()
print(sample_txns[['date', 'amount', 'transaction_type', 'balance']].head(10))

# Error Handling Example: Bad column name in query
try:
    bad_query = df.query("transaction_typ == 'Debit'")
except Exception as e:
    print(f'Error: {e}')

# Error Handling Example: Mismatched join keys
try:
    bad_merge = df.merge(accounts, left_on='customer_id', right_on='account_id', how='left')
except Exception as e:
    print(f'Error: {e}')

# Best Practice: Always inspect your data before analysis
df.info()
df.describe()

# Pattern: Save your analysis as a CSV for sharing
important_results = avg_by_segment.reset_index()
important_results.to_csv('avg_transaction_by_segment.csv', index=False)
print('Saved: avg_transaction_by_segment.csv')

# End-to-end mini-case: Flag customers with monthly debit total > $10,000
monthly_cust_debit = df[df['transaction_type'] == 'Debit'].groupby(['customer_id', 'month'])['amount'].sum().reset_index()
flagged = monthly_cust_debit[monthly_cust_debit['amount'] > 10000]
flagged_customers = flagged.merge(customers, on='customer_id', how='left')
print(flagged_customers[['customer_id', 'month', 'amount', 'segment', 'region']])