-- Banking System Database Schema
-- Version: 1.0.0
-- Created: January 2026

SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- CORE TABLES
-- =====================================================

-- Users Table (Both Admin and Regular Users)
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(36) UNIQUE NOT NULL,
    `user_type` ENUM('admin', 'user', 'business') DEFAULT 'user',
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `phone` VARCHAR(20) UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `pin` VARCHAR(255) DEFAULT NULL COMMENT 'Transaction PIN',
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `middle_name` VARCHAR(100) DEFAULT NULL,
    `date_of_birth` DATE DEFAULT NULL,
    `gender` ENUM('male', 'female', 'other') DEFAULT NULL,
    `address` TEXT DEFAULT NULL,
    `city` VARCHAR(100) DEFAULT NULL,
    `state` VARCHAR(100) DEFAULT NULL,
    `country` VARCHAR(100) DEFAULT 'Nigeria',
    `postal_code` VARCHAR(20) DEFAULT NULL,
    `profile_photo` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('active', 'inactive', 'suspended', 'pending') DEFAULT 'pending',
    `email_verified_at` DATETIME DEFAULT NULL,
    `phone_verified_at` DATETIME DEFAULT NULL,
    `last_login_at` DATETIME DEFAULT NULL,
    `last_login_ip` VARCHAR(45) DEFAULT NULL,
    `remember_token` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_users_email` (`email`),
    INDEX `idx_users_phone` (`phone`),
    INDEX `idx_users_status` (`status`),
    INDEX `idx_users_type` (`user_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- KYC (Know Your Customer) Table
DROP TABLE IF EXISTS `kyc`;
CREATE TABLE `kyc` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `tier` TINYINT DEFAULT 1 COMMENT '1=Basic, 2=Standard, 3=Premium',
    `bvn` VARCHAR(20) DEFAULT NULL COMMENT 'Bank Verification Number',
    `nin` VARCHAR(20) DEFAULT NULL COMMENT 'National ID Number',
    `id_type` ENUM('passport', 'national_id', 'drivers_license', 'voters_card') DEFAULT NULL,
    `id_number` VARCHAR(50) DEFAULT NULL,
    `id_front_image` VARCHAR(255) DEFAULT NULL,
    `id_back_image` VARCHAR(255) DEFAULT NULL,
    `selfie_image` VARCHAR(255) DEFAULT NULL,
    `proof_of_address` VARCHAR(255) DEFAULT NULL,
    `verification_status` ENUM('pending', 'verified', 'rejected', 'expired') DEFAULT 'pending',
    `rejection_reason` TEXT DEFAULT NULL,
    `verified_by` INT UNSIGNED DEFAULT NULL,
    `verified_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`verified_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_kyc_user` (`user_id`),
    INDEX `idx_kyc_status` (`verification_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Account Tiers (Limits & Features)
DROP TABLE IF EXISTS `account_tiers`;
CREATE TABLE `account_tiers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `description` TEXT,
    `daily_transaction_limit` DECIMAL(15,2) DEFAULT 50000.00,
    `single_transaction_limit` DECIMAL(15,2) DEFAULT 10000.00,
    `monthly_transaction_limit` DECIMAL(15,2) DEFAULT 500000.00,
    `max_balance` DECIMAL(15,2) DEFAULT 300000.00,
    `can_transfer_international` BOOLEAN DEFAULT FALSE,
    `can_have_virtual_card` BOOLEAN DEFAULT TRUE,
    `can_have_physical_card` BOOLEAN DEFAULT FALSE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ACCOUNTS & WALLETS
-- =====================================================

-- Bank Accounts Table
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `account_number` VARCHAR(20) UNIQUE NOT NULL,
    `account_type` ENUM('savings', 'current', 'virtual', 'fixed_deposit') DEFAULT 'savings',
    `account_name` VARCHAR(255) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `tier_id` INT UNSIGNED DEFAULT 1,
    `status` ENUM('active', 'inactive', 'frozen', 'dormant', 'closed') DEFAULT 'active',
    `is_primary` BOOLEAN DEFAULT FALSE,
    `frozen_reason` TEXT DEFAULT NULL,
    `frozen_at` DATETIME DEFAULT NULL,
    `frozen_by` INT UNSIGNED DEFAULT NULL,
    `last_activity_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`tier_id`) REFERENCES `account_tiers`(`id`),
    FOREIGN KEY (`frozen_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_accounts_user` (`user_id`),
    INDEX `idx_accounts_number` (`account_number`),
    INDEX `idx_accounts_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Wallets (Multi-currency support)
DROP TABLE IF EXISTS `wallets`;
CREATE TABLE `wallets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `account_id` INT UNSIGNED NOT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
    `ledger_balance` DECIMAL(18,4) DEFAULT 0.0000 COMMENT 'Actual balance including pending',
    `available_balance` DECIMAL(18,4) DEFAULT 0.0000 COMMENT 'Balance available for transactions',
    `pending_credits` DECIMAL(18,4) DEFAULT 0.0000,
    `pending_debits` DECIMAL(18,4) DEFAULT 0.0000,
    `locked_amount` DECIMAL(18,4) DEFAULT 0.0000 COMMENT 'Amount on hold',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_account_currency` (`account_id`, `currency`),
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
    INDEX `idx_wallets_account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- LEDGER SYSTEM (Double-Entry)
-- =====================================================

-- Ledger Entries (Double-Entry Accounting)
DROP TABLE IF EXISTS `ledger_entries`;
CREATE TABLE `ledger_entries` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `transaction_id` BIGINT UNSIGNED NOT NULL,
    `wallet_id` INT UNSIGNED NOT NULL,
    `entry_type` ENUM('debit', 'credit') NOT NULL,
    `amount` DECIMAL(18,4) NOT NULL,
    `balance_before` DECIMAL(18,4) NOT NULL,
    `balance_after` DECIMAL(18,4) NOT NULL,
    `description` VARCHAR(255),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    INDEX `idx_ledger_transaction` (`transaction_id`),
    INDEX `idx_ledger_wallet` (`wallet_id`),
    INDEX `idx_ledger_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TRANSACTIONS
-- =====================================================

-- Main Transactions Table
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `reference` VARCHAR(50) UNIQUE NOT NULL,
    `type` ENUM('transfer', 'deposit', 'withdrawal', 'bill_payment', 'card_funding', 'card_withdrawal', 'reversal', 'fee', 'interest', 'adjustment') NOT NULL,
    `category` VARCHAR(50) DEFAULT NULL COMMENT 'airtime, electricity, water, etc',
    `source_account_id` INT UNSIGNED DEFAULT NULL,
    `destination_account_id` INT UNSIGNED DEFAULT NULL,
    `source_wallet_id` INT UNSIGNED DEFAULT NULL,
    `destination_wallet_id` INT UNSIGNED DEFAULT NULL,
    `amount` DECIMAL(18,4) NOT NULL,
    `fee` DECIMAL(18,4) DEFAULT 0.0000,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `exchange_rate` DECIMAL(18,6) DEFAULT 1.000000,
    `narration` VARCHAR(255),
    `status` ENUM('pending', 'processing', 'completed', 'failed', 'reversed', 'cancelled') DEFAULT 'pending',
    `failure_reason` TEXT DEFAULT NULL,
    `metadata` JSON DEFAULT NULL COMMENT 'Additional transaction data',
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `scheduled_at` DATETIME DEFAULT NULL COMMENT 'For scheduled transfers',
    `processed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`source_account_id`) REFERENCES `accounts`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`destination_account_id`) REFERENCES `accounts`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`source_wallet_id`) REFERENCES `wallets`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`destination_wallet_id`) REFERENCES `wallets`(`id`) ON DELETE SET NULL,
    INDEX `idx_transactions_reference` (`reference`),
    INDEX `idx_transactions_type` (`type`),
    INDEX `idx_transactions_status` (`status`),
    INDEX `idx_transactions_source` (`source_account_id`),
    INDEX `idx_transactions_dest` (`destination_account_id`),
    INDEX `idx_transactions_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Holds/Pending Transactions
DROP TABLE IF EXISTS `transaction_holds`;
CREATE TABLE `transaction_holds` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `wallet_id` INT UNSIGNED NOT NULL,
    `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
    `amount` DECIMAL(18,4) NOT NULL,
    `reason` VARCHAR(255),
    `status` ENUM('active', 'released', 'captured') DEFAULT 'active',
    `expires_at` DATETIME DEFAULT NULL,
    `released_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL,
    INDEX `idx_holds_wallet` (`wallet_id`),
    INDEX `idx_holds_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TRANSFERS
-- =====================================================

-- Beneficiaries (Saved Recipients)
DROP TABLE IF EXISTS `beneficiaries`;
CREATE TABLE `beneficiaries` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `account_number` VARCHAR(20) NOT NULL,
    `bank_code` VARCHAR(10) DEFAULT NULL,
    `bank_name` VARCHAR(100) DEFAULT NULL,
    `beneficiary_type` ENUM('internal', 'external', 'international') DEFAULT 'internal',
    `currency` VARCHAR(3) DEFAULT 'USD',
    `is_favorite` BOOLEAN DEFAULT FALSE,
    `last_used_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_beneficiaries_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Scheduled/Recurring Transfers
DROP TABLE IF EXISTS `scheduled_transfers`;
CREATE TABLE `scheduled_transfers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `source_account_id` INT UNSIGNED NOT NULL,
    `beneficiary_id` INT UNSIGNED DEFAULT NULL,
    `destination_account_number` VARCHAR(20) NOT NULL,
    `destination_bank_code` VARCHAR(10) DEFAULT NULL,
    `amount` DECIMAL(18,4) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `narration` VARCHAR(255),
    `frequency` ENUM('once', 'daily', 'weekly', 'monthly', 'yearly') DEFAULT 'once',
    `start_date` DATE NOT NULL,
    `end_date` DATE DEFAULT NULL,
    `next_run_date` DATE,
    `last_run_at` DATETIME DEFAULT NULL,
    `status` ENUM('active', 'paused', 'completed', 'cancelled') DEFAULT 'active',
    `total_runs` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`source_account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`beneficiary_id`) REFERENCES `beneficiaries`(`id`) ON DELETE SET NULL,
    INDEX `idx_scheduled_user` (`user_id`),
    INDEX `idx_scheduled_next_run` (`next_run_date`),
    INDEX `idx_scheduled_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bulk Transfer Batches
DROP TABLE IF EXISTS `bulk_transfer_batches`;
CREATE TABLE `bulk_transfer_batches` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `source_account_id` INT UNSIGNED NOT NULL,
    `batch_reference` VARCHAR(50) UNIQUE NOT NULL,
    `total_count` INT DEFAULT 0,
    `successful_count` INT DEFAULT 0,
    `failed_count` INT DEFAULT 0,
    `total_amount` DECIMAL(18,4) DEFAULT 0,
    `status` ENUM('pending', 'processing', 'completed', 'partially_completed', 'failed') DEFAULT 'pending',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `processed_at` DATETIME DEFAULT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`source_account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bulk Transfer Items
DROP TABLE IF EXISTS `bulk_transfer_items`;
CREATE TABLE `bulk_transfer_items` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `batch_id` INT UNSIGNED NOT NULL,
    `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
    `recipient_account` VARCHAR(20) NOT NULL,
    `recipient_bank_code` VARCHAR(10) DEFAULT NULL,
    `recipient_name` VARCHAR(255),
    `amount` DECIMAL(18,4) NOT NULL,
    `narration` VARCHAR(255),
    `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    `failure_reason` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`batch_id`) REFERENCES `bulk_transfer_batches`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL,
    INDEX `idx_bulk_items_batch` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- BILL PAYMENTS
-- =====================================================

-- Bill Categories
DROP TABLE IF EXISTS `bill_categories`;
CREATE TABLE `bill_categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) UNIQUE NOT NULL,
    `icon` VARCHAR(100) DEFAULT NULL,
    `description` TEXT,
    `is_active` BOOLEAN DEFAULT TRUE,
    `display_order` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bill Providers
DROP TABLE IF EXISTS `bill_providers`;
CREATE TABLE `bill_providers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) UNIQUE NOT NULL,
    `logo` VARCHAR(255) DEFAULT NULL,
    `provider_code` VARCHAR(50) DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `bill_categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bill Products (Plans/Packages)
DROP TABLE IF EXISTS `bill_products`;
CREATE TABLE `bill_products` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `provider_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `product_code` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(18,4) DEFAULT NULL COMMENT 'NULL for variable amounts',
    `min_amount` DECIMAL(18,4) DEFAULT NULL,
    `max_amount` DECIMAL(18,4) DEFAULT NULL,
    `fee` DECIMAL(18,4) DEFAULT 0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`provider_id`) REFERENCES `bill_providers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bill Payments History
DROP TABLE IF EXISTS `bill_payments`;
CREATE TABLE `bill_payments` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `transaction_id` BIGINT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `category_id` INT UNSIGNED NOT NULL,
    `provider_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED DEFAULT NULL,
    `customer_id` VARCHAR(100) NOT NULL COMMENT 'Meter number, phone, smart card, etc',
    `customer_name` VARCHAR(255) DEFAULT NULL,
    `amount` DECIMAL(18,4) NOT NULL,
    `fee` DECIMAL(18,4) DEFAULT 0,
    `token` VARCHAR(255) DEFAULT NULL COMMENT 'Electricity token, etc',
    `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    `provider_reference` VARCHAR(100) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`category_id`) REFERENCES `bill_categories`(`id`),
    FOREIGN KEY (`provider_id`) REFERENCES `bill_providers`(`id`),
    FOREIGN KEY (`product_id`) REFERENCES `bill_products`(`id`),
    INDEX `idx_bill_payments_user` (`user_id`),
    INDEX `idx_bill_payments_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Saved Bill Beneficiaries
DROP TABLE IF EXISTS `saved_billers`;
CREATE TABLE `saved_billers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `category_id` INT UNSIGNED NOT NULL,
    `provider_id` INT UNSIGNED NOT NULL,
    `nickname` VARCHAR(100) DEFAULT NULL,
    `customer_id` VARCHAR(100) NOT NULL,
    `customer_name` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`category_id`) REFERENCES `bill_categories`(`id`),
    FOREIGN KEY (`provider_id`) REFERENCES `bill_providers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CARDS
-- =====================================================

-- Cards Table
DROP TABLE IF EXISTS `cards`;
CREATE TABLE `cards` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `card_type` ENUM('virtual', 'physical') DEFAULT 'virtual',
    `card_brand` ENUM('visa', 'mastercard', 'verve') DEFAULT 'mastercard',
    `card_number_masked` VARCHAR(20) NOT NULL COMMENT 'e.g., **** **** **** 1234',
    `card_hash` VARCHAR(255) NOT NULL COMMENT 'Encrypted full card number',
    `expiry_month` VARCHAR(2) NOT NULL,
    `expiry_year` VARCHAR(4) NOT NULL,
    `cvv_hash` VARCHAR(255) NOT NULL COMMENT 'Encrypted CVV',
    `cardholder_name` VARCHAR(100) NOT NULL,
    `billing_address` TEXT,
    `balance` DECIMAL(18,4) DEFAULT 0.0000,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `daily_limit` DECIMAL(18,4) DEFAULT 1000.00,
    `monthly_limit` DECIMAL(18,4) DEFAULT 10000.00,
    `single_transaction_limit` DECIMAL(18,4) DEFAULT 500.00,
    `status` ENUM('active', 'inactive', 'frozen', 'blocked', 'expired') DEFAULT 'inactive',
    `pin_set` BOOLEAN DEFAULT FALSE,
    `pin_hash` VARCHAR(255) DEFAULT NULL,
    `is_online_enabled` BOOLEAN DEFAULT TRUE,
    `is_pos_enabled` BOOLEAN DEFAULT TRUE,
    `is_atm_enabled` BOOLEAN DEFAULT FALSE,
    `is_international_enabled` BOOLEAN DEFAULT FALSE,
    `frozen_reason` TEXT DEFAULT NULL,
    `frozen_at` DATETIME DEFAULT NULL,
    `activated_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
    INDEX `idx_cards_user` (`user_id`),
    INDEX `idx_cards_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Card Transactions
DROP TABLE IF EXISTS `card_transactions`;
CREATE TABLE `card_transactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `card_id` INT UNSIGNED NOT NULL,
    `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
    `type` ENUM('funding', 'withdrawal', 'purchase', 'refund', 'chargeback') NOT NULL,
    `merchant_name` VARCHAR(255) DEFAULT NULL,
    `merchant_category` VARCHAR(100) DEFAULT NULL,
    `amount` DECIMAL(18,4) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `exchange_rate` DECIMAL(18,6) DEFAULT 1.000000,
    `status` ENUM('pending', 'approved', 'declined', 'reversed') DEFAULT 'pending',
    `decline_reason` VARCHAR(255) DEFAULT NULL,
    `authorization_code` VARCHAR(50) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`card_id`) REFERENCES `cards`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL,
    INDEX `idx_card_trans_card` (`card_id`),
    INDEX `idx_card_trans_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Card Disputes
DROP TABLE IF EXISTS `card_disputes`;
CREATE TABLE `card_disputes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `card_id` INT UNSIGNED NOT NULL,
    `card_transaction_id` BIGINT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `dispute_type` ENUM('fraud', 'duplicate', 'not_received', 'wrong_amount', 'other') NOT NULL,
    `description` TEXT NOT NULL,
    `evidence_files` JSON DEFAULT NULL,
    `amount_disputed` DECIMAL(18,4) NOT NULL,
    `status` ENUM('open', 'under_review', 'resolved_favor_customer', 'resolved_favor_merchant', 'closed') DEFAULT 'open',
    `resolution_notes` TEXT DEFAULT NULL,
    `resolved_at` DATETIME DEFAULT NULL,
    `resolved_by` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`card_id`) REFERENCES `cards`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`card_transaction_id`) REFERENCES `card_transactions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- FEES & INTEREST
-- =====================================================

-- Fee Configuration
DROP TABLE IF EXISTS `fee_configurations`;
CREATE TABLE `fee_configurations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `transaction_type` VARCHAR(50) NOT NULL,
    `fee_type` ENUM('flat', 'percentage', 'flat_plus_percentage') DEFAULT 'flat',
    `flat_amount` DECIMAL(18,4) DEFAULT 0,
    `percentage` DECIMAL(5,4) DEFAULT 0 COMMENT 'e.g., 0.0150 for 1.5%',
    `min_fee` DECIMAL(18,4) DEFAULT 0,
    `max_fee` DECIMAL(18,4) DEFAULT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Interest Configurations
DROP TABLE IF EXISTS `interest_configurations`;
CREATE TABLE `interest_configurations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `account_type` VARCHAR(50) NOT NULL,
    `tier_id` INT UNSIGNED DEFAULT NULL,
    `annual_rate` DECIMAL(5,4) NOT NULL COMMENT 'e.g., 0.0500 for 5%',
    `calculation_method` ENUM('daily', 'monthly', 'quarterly') DEFAULT 'daily',
    `min_balance_required` DECIMAL(18,4) DEFAULT 0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tier_id`) REFERENCES `account_tiers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Interest Accruals
DROP TABLE IF EXISTS `interest_accruals`;
CREATE TABLE `interest_accruals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `account_id` INT UNSIGNED NOT NULL,
    `wallet_id` INT UNSIGNED NOT NULL,
    `accrual_date` DATE NOT NULL,
    `balance` DECIMAL(18,4) NOT NULL,
    `rate_applied` DECIMAL(5,4) NOT NULL,
    `interest_amount` DECIMAL(18,4) NOT NULL,
    `status` ENUM('accrued', 'credited') DEFAULT 'accrued',
    `credited_transaction_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`wallet_id`) REFERENCES `wallets`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`credited_transaction_id`) REFERENCES `transactions`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_daily_accrual` (`wallet_id`, `accrual_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SAVINGS GOALS
-- =====================================================

DROP TABLE IF EXISTS `savings_goals`;
CREATE TABLE `savings_goals` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `target_amount` DECIMAL(18,4) NOT NULL,
    `current_amount` DECIMAL(18,4) DEFAULT 0,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `target_date` DATE DEFAULT NULL,
    `auto_debit` BOOLEAN DEFAULT FALSE,
    `auto_debit_amount` DECIMAL(18,4) DEFAULT NULL,
    `auto_debit_frequency` ENUM('daily', 'weekly', 'monthly') DEFAULT NULL,
    `status` ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    `completed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE,
    INDEX `idx_savings_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- NOTIFICATIONS
-- =====================================================

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `data` JSON DEFAULT NULL,
    `is_read` BOOLEAN DEFAULT FALSE,
    `read_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_notifications_user` (`user_id`),
    INDEX `idx_notifications_read` (`is_read`),
    INDEX `idx_notifications_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- AUDIT & SECURITY
-- =====================================================

-- Audit Logs
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50) DEFAULT NULL,
    `entity_id` VARCHAR(50) DEFAULT NULL,
    `old_values` JSON DEFAULT NULL,
    `new_values` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_user` (`user_id`),
    INDEX `idx_audit_action` (`action`),
    INDEX `idx_audit_entity` (`entity_type`, `entity_id`),
    INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login Attempts
DROP TABLE IF EXISTS `login_attempts`;
CREATE TABLE `login_attempts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT,
    `success` BOOLEAN DEFAULT FALSE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_login_email` (`email`),
    INDEX `idx_login_ip` (`ip_address`),
    INDEX `idx_login_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessions
DROP TABLE IF EXISTS `user_sessions`;
CREATE TABLE `user_sessions` (
    `id` VARCHAR(128) PRIMARY KEY,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT,
    `payload` TEXT NOT NULL,
    `last_activity` INT UNSIGNED NOT NULL,
    INDEX `idx_sessions_user` (`user_id`),
    INDEX `idx_sessions_activity` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CURRENCIES & EXCHANGE RATES
-- =====================================================

DROP TABLE IF EXISTS `currencies`;
CREATE TABLE `currencies` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(3) UNIQUE NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `symbol` VARCHAR(10) DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `exchange_rates`;
CREATE TABLE `exchange_rates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `base_currency` VARCHAR(3) NOT NULL,
    `target_currency` VARCHAR(3) NOT NULL,
    `buy_rate` DECIMAL(18,6) NOT NULL,
    `sell_rate` DECIMAL(18,6) NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_currency_pair` (`base_currency`, `target_currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SYSTEM SETTINGS
-- =====================================================

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) UNIQUE NOT NULL,
    `value` TEXT,
    `type` VARCHAR(20) DEFAULT 'string',
    `description` TEXT,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INITIAL DATA
-- =====================================================

-- Insert Account Tiers
INSERT INTO `account_tiers` (`name`, `description`, `daily_transaction_limit`, `single_transaction_limit`, `monthly_transaction_limit`, `max_balance`, `can_transfer_international`, `can_have_virtual_card`, `can_have_physical_card`) VALUES
('Basic', 'Basic tier with limited features', 50000.00, 10000.00, 200000.00, 300000.00, FALSE, TRUE, FALSE),
('Standard', 'Standard tier with more features', 200000.00, 50000.00, 1000000.00, 2000000.00, FALSE, TRUE, TRUE),
('Premium', 'Premium tier with full features', 1000000.00, 500000.00, 10000000.00, 50000000.00, TRUE, TRUE, TRUE);

-- Insert Currencies
INSERT INTO `currencies` (`code`, `name`, `symbol`) VALUES
('USD', 'Nigerian Naira', '$'),
('USD', 'US Dollar', '$'),
('GBP', 'British Pound', '£'),
('EUR', 'Euro', '€'),
('GHS', 'Ghanaian Cedi', 'GH₵'),
('KES', 'Kenyan Shilling', 'KSh'),
('ZAR', 'South African Rand', 'R');

-- Insert Exchange Rates (USD base)
INSERT INTO `exchange_rates` (`base_currency`, `target_currency`, `buy_rate`, `sell_rate`) VALUES
('USD', 'USD', 1550.00, 1580.00),
('GBP', 'USD', 1950.00, 1980.00),
('EUR', 'USD', 1680.00, 1710.00),
('USD', 'USD', 0.000645, 0.000632),
('USD', 'GBP', 0.000513, 0.000505),
('USD', 'EUR', 0.000595, 0.000585);

-- Insert Bill Categories
INSERT INTO `bill_categories` (`name`, `slug`, `icon`, `display_order`) VALUES
('Airtime', 'airtime', 'call-outline', 1),
('Data', 'data', 'wifi-outline', 2),
('Electricity', 'electricity', 'flash-outline', 3),
('TV Subscription', 'tv', 'tv-outline', 4),
('Internet', 'internet', 'globe-outline', 5),
('Water', 'water', 'water-outline', 6),
('Education', 'education', 'school-outline', 7),
('Government', 'government', 'business-outline', 8);

-- Insert Bill Providers
INSERT INTO `bill_providers` (`category_id`, `name`, `slug`, `provider_code`) VALUES
-- Airtime Providers (US Mobile Carriers)
(1, 'AT&T', 'att', 'ATT'),
(1, 'Verizon', 'verizon', 'VERIZON'),
(1, 'T-Mobile', 'tmobile', 'TMOBILE'),
(1, 'Cricket', 'cricket', 'CRICKET'),
-- Data Providers (US Mobile Carriers)
(2, 'AT&T Data', 'att-data', 'ATT_DATA'),
(2, 'Verizon Data', 'verizon-data', 'VERIZON_DATA'),
(2, 'T-Mobile Data', 'tmobile-data', 'TMOBILE_DATA'),
(2, 'Cricket Data', 'cricket-data', 'CRICKET_DATA'),
-- US Electricity Providers
(3, 'PG&E', 'pge', 'PGE'),
(3, 'SCE', 'sce', 'SCE'),
(3, 'Duke Energy', 'duke-energy', 'DUKE'),
(3, 'FPL', 'fpl', 'FPL'),
(3, 'Con Edison', 'coned', 'CONED'),
(3, 'ComEd', 'comed', 'COMED'),
-- US TV/Streaming Providers
(4, 'DirecTV', 'directv', 'DIRECTV'),
(4, 'Dish Network', 'dish', 'DISH'),
(4, 'Xfinity', 'xfinity', 'XFINITY'),
(4, 'Spectrum', 'spectrum', 'SPECTRUM'),
(4, 'YouTube TV', 'youtube-tv', 'YOUTUBETV'),
(4, 'Hulu', 'hulu', 'HULU');

-- Insert Fee Configurations
INSERT INTO `fee_configurations` (`name`, `transaction_type`, `fee_type`, `flat_amount`, `percentage`, `min_fee`, `max_fee`) VALUES
('Internal Transfer Fee', 'internal_transfer', 'flat', 0.00, 0.0000, 0.00, NULL),
('Bank Transfer Fee', 'bank_transfer', 'flat', 25.00, 0.0000, 25.00, NULL),
('International Transfer Fee', 'international_transfer', 'flat_plus_percentage', 500.00, 0.0100, 500.00, 5000.00),
('Card Funding Fee', 'card_funding', 'percentage', 0.00, 0.0150, 10.00, 2000.00),
('Bill Payment Fee', 'bill_payment', 'flat', 100.00, 0.0000, 100.00, NULL);

-- Insert System Settings
INSERT INTO `settings` (`key`, `value`, `type`, `description`) VALUES
('app_name', 'MeridianBank', 'string', 'Application Name'),
('app_currency', 'USD', 'string', 'Default Currency'),
('app_email', 'support@meridianbank.com', 'string', 'Support Email'),
('maintenance_mode', '0', 'boolean', 'Maintenance Mode'),
('min_transfer_amount', '100', 'number', 'Minimum Transfer Amount'),
('max_daily_transfers', '10', 'number', 'Maximum Daily Transfers'),
('session_timeout', '30', 'number', 'Session Timeout in Minutes'),
('allow_registration', '1', 'boolean', 'Allow New Registrations');

-- Insert Default Admin User (password: admin123)
INSERT INTO `users` (`uuid`, `user_type`, `email`, `phone`, `password`, `pin`, `first_name`, `last_name`, `status`, `email_verified_at`, `phone_verified_at`) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'admin', 'admin@meridianbank.com', '08000000001', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System', 'Administrator', 'active', NOW(), NOW());

-- Insert Default Regular User (password: user123)
INSERT INTO `users` (`uuid`, `user_type`, `email`, `phone`, `password`, `pin`, `first_name`, `last_name`, `status`, `email_verified_at`, `phone_verified_at`) VALUES
('550e8400-e29b-41d4-a716-446655440002', 'user', 'user@meridianbank.com', '08000000002', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'John', 'Doe', 'active', NOW(), NOW());

-- Create KYC records
INSERT INTO `kyc` (`user_id`, `tier`, `verification_status`, `verified_at`) VALUES
(1, 3, 'verified', NOW()),
(2, 2, 'verified', NOW());

-- Create Accounts for Admin
INSERT INTO `accounts` (`user_id`, `account_number`, `account_type`, `account_name`, `currency`, `tier_id`, `status`, `is_primary`) VALUES
(1, '1000000001', 'current', 'System Administrator', 'USD', 3, 'active', TRUE);

-- Create Accounts for Regular User
INSERT INTO `accounts` (`user_id`, `account_number`, `account_type`, `account_name`, `currency`, `tier_id`, `status`, `is_primary`) VALUES
(2, '2000000001', 'savings', 'John Doe Savings', 'USD', 2, 'active', TRUE),
(2, '2000000002', 'current', 'John Doe Current', 'USD', 2, 'active', FALSE);

-- Create Wallets for Accounts
INSERT INTO `wallets` (`account_id`, `currency`, `ledger_balance`, `available_balance`) VALUES
(1, 'USD', 10000000.0000, 10000000.0000),
(2, 'USD', 250000.0000, 250000.0000),
(3, 'USD', 150000.0000, 150000.0000);

-- Create a Virtual Card for Regular User
INSERT INTO `cards` (`user_id`, `account_id`, `card_type`, `card_brand`, `card_number_masked`, `card_hash`, `expiry_month`, `expiry_year`, `cvv_hash`, `cardholder_name`, `balance`, `status`, `activated_at`) VALUES
(2, 2, 'virtual', 'mastercard', '**** **** **** 5019', 'encrypted_card_number_hash', '12', '2028', 'encrypted_cvv_hash', 'JOHN DOE', 500.00, 'active', NOW());

-- =====================================================
-- CRYPTO DEPOSIT WALLETS (admin-managed)
-- =====================================================
DROP TABLE IF EXISTS `crypto_addresses`;
DROP TABLE IF EXISTS `crypto_wallets`;
CREATE TABLE `crypto_wallets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT 'e.g. Bitcoin, Ethereum',
    `symbol` VARCHAR(20) NOT NULL COMMENT 'e.g. BTC, ETH',
    `network` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. ERC-20, Mainnet',
    `instructions` TEXT DEFAULT NULL COMMENT 'Deposit instructions for users',
    `is_active` TINYINT(1) DEFAULT 1,
    `display_order` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_crypto_wallets_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `crypto_addresses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `crypto_wallet_id` INT UNSIGNED NOT NULL,
    `address` VARCHAR(255) NOT NULL COMMENT 'Deposit address',
    `label` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. Primary',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`crypto_wallet_id`) REFERENCES `crypto_wallets`(`id`) ON DELETE CASCADE,
    INDEX `idx_crypto_addresses_wallet` (`crypto_wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
