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

daily_counts = df.groupby(df['date'].dt.date).size()
print(daily_counts.head(3))

daily_sums = df.groupby([df['date'].dt.date, 'transaction_type'])['amount'].sum().unstack().fillna(0)
print(daily_sums.head(3))

all_days = pd.date_range(df['date'].min().date(), df['date'].max().date(), freq='D')
transaction_days = pd.to_datetime(daily_counts.index)
missing_days = set(all_days.date) - set(transaction_days.date)
print(sorted(list(missing_days))[:5])

df['amount_signed'] = np.where(df['transaction_type'] == 'Debit', -df['amount'], df['amount'])
df_merged = df.merge(accounts[['account_id', 'customer_id']], on='customer_id')
eod_balances = df_merged.groupby(['account_id', df_merged['date'].dt.date])['amount_signed'].sum().groupby('account_id').cumsum()
eod_balances = eod_balances.reset_index(name='balance')
print(eod_balances.head(5))

acct_summary = eod_balances.groupby('account_id').tail(1).sort_values(by='balance', ascending=False)
print(acct_summary.head(5))

interest_rate = 0.01 / 365
eod_interest = eod_balances.copy()
eod_interest['interest'] = np.where(eod_interest['balance'] > 1000, eod_interest['balance'] * interest_rate, 0)
print(eod_interest[['account_id', 'balance', 'interest']].head(5))

total_interest = eod_interest.groupby('account_id')['interest'].sum().sort_values(ascending=False)
print(total_interest.head(5))

suspicious = df.groupby([df['customer_id'], df['date'].dt.date]).size().reset_index(name='num_tx')
flagged = suspicious[suspicious['num_tx'] > 4]
print(flagged.head(5))

acct_region = acct_summary.merge(accounts[['account_id', 'customer_id']], on='account_id')
acct_region = acct_region.merge(customers[['customer_id', 'region']], on='customer_id')
region_totals = acct_region.groupby('region')['balance'].sum()
print(region_totals)

closure_file = 'eod_closure_sample.csv'
acct_summary.to_csv(closure_file, index=False)
print(f'Closure file created: {closure_file}')

invalid = df[df['amount'] <= 0]
dupes = df[df['transaction_id'].duplicated()]
print('Invalid amount rows:', len(invalid))
print('Duplicate transaction IDs:', len(dupes))

# Remove invalid transactions for clean EOD processing
df_clean = df[(df['amount'] > 0) & (~df['transaction_id'].duplicated())]
print(df_clean.shape)

acct_id = acct_summary.iloc[0]['account_id']
account_balances = eod_balances[eod_balances['account_id'] == acct_id].sort_values('date').reset_index(drop=True)
account_balances['prev_balance'] = account_balances['balance'].shift(1)
account_balances['diff'] = account_balances['balance'] - account_balances['prev_balance'].fillna(0)
print(account_balances[['date', 'balance', 'prev_balance', 'diff']].head(5))