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))

np.random.seed(42)
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))

num_txns = df.shape[0]
print('Total number of transactions:', num_txns)

total_volume = df['amount'].sum()
print('Total transaction volume:', total_volume)

txn_by_channel = df['channel'].value_counts()
print('Transactions by channel:')
print(txn_by_channel)

active_customers = df['customer_id'].nunique()
print('Number of active customers:', active_customers)

customers_with_accounts = customers.merge(accounts, on='customer_id', how='left')
no_txn_customers = customers_with_accounts[~customers_with_accounts['customer_id'].isin(df['customer_id'])]
print('Customers with accounts but no transactions:', len(no_txn_customers))

avg_amount = df['amount'].mean()
print('Average transaction amount:', round(avg_amount, 2))

avg_by_segment = df.merge(customers, on='customer_id').groupby('segment')['amount'].mean()
print('Average transaction by segment:')
print(avg_by_segment)

df['date_only'] = df['date'].dt.date
txns_per_day = df.groupby('date_only')['transaction_id'].count()
print(txns_per_day.head(7))

clv = df.groupby('customer_id')['amount'].sum().sort_values(ascending=False)
print('Top 5 Customer Lifetime Values:')
print(clv.head(5))

accounts_per_customer = accounts.groupby('customer_id')['account_id'].count()
avg_accounts_per_customer = accounts_per_customer.mean()
print('Average number of accounts per customer:', round(avg_accounts_per_customer, 2))

high_product_customers = accounts_per_customer[accounts_per_customer > 2]
print('Customers with more than 2 accounts:', len(high_product_customers))

active_accounts = df.merge(accounts, on='customer_id')[['customer_id','account_id']].drop_duplicates()
active_prods_per_customer = active_accounts.groupby('customer_id')['account_id'].count()
print('Average actively used products per customer:', round(active_prods_per_customer.mean(),2))

df_missing = df.copy()
df_missing.loc[np.random.choice(df.index, 20, replace=False), 'amount'] = np.nan
print(df_missing['amount'].isnull().sum(), 'missing values introduced.')
print('Mean with missing:', df_missing['amount'].mean())
print('Mean after fill:', df_missing['amount'].fillna(0).mean())

outliers = df[df['amount'] > df['amount'].mean() + 3*df['amount'].std()]
print('Unusual transactions found:', outliers.shape[0])
print(outliers[['transaction_id','amount']].head())

def kpi_total_transactions(df, txn_type=None):
    if txn_type:
        return df[df['transaction_type']==txn_type].shape[0]
    return df.shape[0]

print('KPIs:')
print('Total:', kpi_total_transactions(df))
print('Debit:', kpi_total_transactions(df, 'Debit'))

seg_kpi = df.merge(customers, on='customer_id').groupby(['region','segment'])['amount'].sum()
print('Transaction Volume by Region & Segment:')
print(seg_kpi)

np.random.seed(42)
launch_customers = np.random.choice(customer_ids, 25, replace=False)
new_accounts = pd.DataFrame({
    'account_id': [f'ACC_990{i:02d}' for i in range(25)],
    'customer_id': launch_customers,
    'account_type': ['New_Savings']*25,
    'open_date': pd.to_datetime('2024-02-01')
})
accounts_full = pd.concat([accounts, new_accounts], ignore_index=True)
new_txns = pd.DataFrame({
    'transaction_id': np.arange(2001,2026),
    'customer_id': launch_customers,
    'amount': np.random.normal(200, 30, 25),
    'transaction_type': ['Credit']*25,
    'channel': ['Online']*25,
    'date': pd.date_range(start='2024-02-01', periods=25, freq='2D')
})
df_full = pd.concat([df, new_txns], ignore_index=True)

uptake = accounts_full[accounts_full['account_type']=='New_Savings'].shape[0]
active = new_txns.shape[0]
avg_txn_new = new_txns['amount'].mean()
print('New product uptake:', uptake)
print('Number of new product transactions:', active)
print('Average transaction (new product):', round(avg_txn_new,2))