# Database Schema Review Issues

## Missing Database Components ⚠️

### Missing Tables:
1. **`notifications`** - For user notifications
2. **`activity_logs`** - For audit trails  
3. **`referrals`** - For referral system
4. **`devices`** - For device management
5. **`transaction_limits`** - For dynamic limits
6. **`failed_jobs`** - For queue job failures (Laravel standard)
7. **`cache`** - For database caching (if using database cache)

### Missing Migrations for Additional Features:
```php
// TODO: Create these migration files
- create_notifications_table.php
- create_activity_logs_table.php  
- create_referrals_table.php
- create_devices_table.php
- create_transaction_limits_table.php
- create_bank_accounts_table.php (for saved user bank accounts)
- create_beneficiaries_table.php (for frequent transfer recipients)
```

## Existing Migration Issues ⚠️

### Users Table Issues:
```php
// TODO: Add these fields to users migration
$table->string('referral_code')->unique()->nullable();
$table->string('bvn')->nullable(); // For BVN storage
$table->string('nin')->nullable(); // For NIN storage  
$table->integer('profile_completion_percentage')->default(0);
$table->enum('verification_tier', ['unverified', 'tier1', 'tier2', 'tier3'])->default('unverified');
$table->timestamp('kyc_verified_at')->nullable();
$table->boolean('two_factor_enabled')->default(false);
$table->string('two_factor_secret')->nullable();
$table->json('two_factor_recovery_codes')->nullable();
$table->softDeletes(); // For soft delete functionality
```

### Wallets Table Issues:
```php
// TODO: Add these fields to wallets migration  
$table->enum('wallet_type', ['personal', 'business', 'escrow'])->default('personal');
$table->string('wallet_name')->nullable(); // Custom wallet name
$table->timestamp('last_transaction_at')->nullable();
$table->boolean('is_frozen')->default(false);
$table->string('frozen_reason')->nullable();
$table->timestamp('frozen_at')->nullable();
$table->softDeletes();
```

### Transactions Table Issues:
```php
// TODO: Add these fields to transactions migration
$table->string('batch_id')->nullable(); // For bulk transactions
$table->foreignId('parent_transaction_id')->nullable()->constrained('transactions'); // For refunds/reversals
$table->string('failure_reason')->nullable();
$table->integer('retry_count')->default(0);
$table->timestamp('scheduled_at')->nullable(); // For scheduled transactions
$table->json('compliance_data')->nullable(); // For AML/compliance data
$table->string('device_id')->nullable();
$table->string('ip_address')->nullable();
$table->string('user_agent')->nullable();
```

### KYC Verifications Table Issues:
```php
// TODO: Add these fields to kyc_verifications migration
$table->integer('verification_level')->default(1); // Tier 1, 2, 3
$table->json('verification_score')->nullable(); // Confidence scores
$table->string('rejection_reason')->nullable();
$table->timestamp('reviewed_at')->nullable();
$table->foreignId('reviewed_by')->nullable()->constrained('admins');
$table->boolean('is_manual_review')->default(false);
```

## Missing Indexes for Performance ⚠️

### Critical Missing Indexes:
```sql
-- TODO: Add these indexes for better performance
ALTER TABLE users ADD INDEX idx_users_phone (phone);
ALTER TABLE users ADD INDEX idx_users_email (email);
ALTER TABLE users ADD INDEX idx_users_kyc_status (verification_tier);
ALTER TABLE users ADD INDEX idx_users_active (is_active);

ALTER TABLE wallets ADD INDEX idx_wallets_currency (currency);
ALTER TABLE wallets ADD INDEX idx_wallets_user_currency (user_id, currency);
ALTER TABLE wallets ADD INDEX idx_wallets_balance (balance);

ALTER TABLE transactions ADD INDEX idx_transactions_type_status (type, status);
ALTER TABLE transactions ADD INDEX idx_transactions_category (category);
ALTER TABLE transactions ADD INDEX idx_transactions_amount (amount);
ALTER TABLE transactions ADD INDEX idx_transactions_date_range (created_at, status);

ALTER TABLE kyc_verifications ADD INDEX idx_kyc_user_type (user_id, type);
ALTER TABLE kyc_verifications ADD INDEX idx_kyc_status_provider (status, provider);

ALTER TABLE virtual_accounts ADD INDEX idx_va_user_active (user_id, is_active);
ALTER TABLE virtual_accounts ADD INDEX idx_va_provider (provider);

ALTER TABLE virtual_cards ADD INDEX idx_vc_user_status (user_id, status);
ALTER TABLE virtual_cards ADD INDEX idx_vc_provider (provider);
```

## Missing Constraints and Validations ❌

### Missing Database Constraints:
```sql
-- TODO: Add these constraints for data integrity
ALTER TABLE transactions ADD CONSTRAINT chk_transaction_amount_positive CHECK (amount > 0);
ALTER TABLE transactions ADD CONSTRAINT chk_total_amount_consistency CHECK (total_amount = amount + fee);
ALTER TABLE wallets ADD CONSTRAINT chk_wallet_balance_positive CHECK (balance >= 0);
ALTER TABLE wallets ADD CONSTRAINT chk_locked_balance_valid CHECK (locked_balance >= 0 AND locked_balance <= balance);
```

### Missing Foreign Key Constraints:
Some migrations have foreign keys, but these are missing:
```sql
-- TODO: Add missing foreign key constraints
ALTER TABLE webhooks ADD FOREIGN KEY (external_reference) REFERENCES transactions(reference);
ALTER TABLE services ADD INDEX idx_services_category_active (category, is_active);
```

## Missing Audit and Compliance Tables ❌

### Audit Trail Tables Needed:
```php
// TODO: Create these audit tables for compliance
create_audit_logs_table.php - Track all sensitive operations
create_compliance_reports_table.php - Store compliance reports
create_suspicious_activities_table.php - Flag suspicious transactions
create_aml_checks_table.php - Anti-money laundering checks
create_transaction_monitoring_table.php - Real-time monitoring
```

## Missing Data Retention Policies ⚠️

### Data Retention Issues:
```php
// TODO: Implement data retention policies
- No automatic data archival after retention period
- No data anonymization for GDPR compliance  
- No data purging for inactive accounts
- No backup/restore procedures for sensitive data
```

## Performance and Scaling Issues ⚠️

### Missing Database Optimizations:
```sql
-- TODO: Add these for production performance
- Table partitioning for large transaction tables
- Read replicas configuration
- Database connection pooling setup
- Query optimization and slow query logging
- Database backup and recovery procedures
```

## Missing Database Seeders ❌

### Required Seeders Missing:
```php
// TODO: Create these seeder files
database/seeders/
├── DatabaseSeeder.php (main seeder)
├── AdminSeeder.php (create admin users)
├── ServiceSeeder.php (populate services table)
├── CurrencySeeder.php (supported currencies)
├── BankSeeder.php (Nigerian banks data)
└── DemoDataSeeder.php (for development/testing)
```

## Database Configuration Issues ⚠️

### Missing Database Config:
```php
// TODO: Add to database configuration
- Connection timeout settings
- Query timeout settings  
- Connection pooling configuration
- Read/write connection separation
- Database health check endpoints
```