-- ============================================================================
-- Fintech Application Database Schema (SQLite Compatible)
-- Complete database schema for Laravel fintech application
-- Generated from model analysis and migration review
-- ============================================================================

PRAGMA foreign_keys = ON;

-- Laravel sessions
CREATE TABLE `sessions` (
  `id` TEXT PRIMARY KEY NOT NULL,
  `user_id` INTEGER,
  `ip_address` TEXT,
  `user_agent` TEXT,
  `payload` TEXT NOT NULL,
  `last_activity` INTEGER NOT NULL
);
CREATE INDEX `sessions_user_id_index` ON `sessions` (`user_id`);
CREATE INDEX `sessions_last_activity_index` ON `sessions` (`last_activity`);

-- ============================================================================
-- Wallet and Transaction Tables
-- ============================================================================

-- Wallets table with all required fields
CREATE TABLE `wallets` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER NOT NULL,
  `currency` TEXT NOT NULL DEFAULT 'NGN',
  `balance` REAL NOT NULL DEFAULT 0.00,
  `locked_balance` REAL NOT NULL DEFAULT 0.00,
  `is_frozen` INTEGER NOT NULL DEFAULT 0,
  `frozen_at` TEXT,
  `daily_transaction_limit` REAL NOT NULL DEFAULT 1000000.00,
  `monthly_transaction_limit` REAL NOT NULL DEFAULT 5000000.00,
  `daily_spent` REAL NOT NULL DEFAULT 0.00,
  `monthly_spent` REAL NOT NULL DEFAULT 0.00,
  `daily_spent_reset_date` TEXT NOT NULL DEFAULT '1970-01-01',
  `monthly_spent_reset_date` TEXT NOT NULL DEFAULT '1970-01-01',
  `is_active` INTEGER NOT NULL DEFAULT 1,
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
CREATE UNIQUE INDEX `wallets_user_id_currency_unique` ON `wallets` (`user_id`,`currency`);
CREATE INDEX `wallets_currency_index` ON `wallets` (`currency`);
CREATE INDEX `wallets_is_frozen_index` ON `wallets` (`is_frozen`);
CREATE INDEX `wallets_is_active_index` ON `wallets` (`is_active`);

-- Transactions table with all categories and statuses
CREATE TABLE `transactions` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `reference` TEXT UNIQUE NOT NULL,
  `user_id` INTEGER NOT NULL,
  `wallet_id` INTEGER NOT NULL,
  `type` TEXT CHECK (`type` IN ('credit','debit')) NOT NULL,
  `category` TEXT CHECK (`category` IN ('wallet_funding','p2p_transfer','bank_transfer','airtime_purchase','data_purchase','bill_payment','card_transaction','refund','charge','commission','admin_adjustment')) NOT NULL,
  `amount` REAL NOT NULL,
  `fee` REAL NOT NULL DEFAULT 0.00,
  `total_amount` REAL NOT NULL,
  `currency` TEXT NOT NULL DEFAULT 'NGN',
  `status` TEXT CHECK (`status` IN ('pending','processing','completed','failed','cancelled')) DEFAULT 'pending',
  `description` TEXT,
  `metadata` TEXT, -- JSON stored as TEXT
  `external_reference` TEXT,
  `payment_method` TEXT,
  `recipient_user_id` INTEGER,
  `recipient_account_number` TEXT,
  `recipient_bank_code` TEXT,
  `recipient_bank_name` TEXT,
  `processed_at` TEXT,
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`recipient_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
);
CREATE INDEX `transactions_user_id_created_at_index` ON `transactions` (`user_id`,`created_at`);
CREATE INDEX `transactions_status_created_at_index` ON `transactions` (`status`,`created_at`);
CREATE INDEX `transactions_external_reference_index` ON `transactions` (`external_reference`);
CREATE INDEX `transactions_category_index` ON `transactions` (`category`);
CREATE INDEX `transactions_type_index` ON `transactions` (`type`);
CREATE INDEX `transactions_wallet_id_index` ON `transactions` (`wallet_id`);
CREATE INDEX `transactions_recipient_user_id_index` ON `transactions` (`recipient_user_id`);

-- ============================================================================
-- KYC and Verification Tables
-- ============================================================================

-- KYC Verifications table
CREATE TABLE `kyc_verifications` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER NOT NULL,
  `type` TEXT CHECK (`type` IN ('bvn','nin','passport','drivers_license','voters_card')) NOT NULL,
  `verification_id` TEXT,
  `status` TEXT CHECK (`status` IN ('pending','verified','failed','expired')) DEFAULT 'pending',
  `provider` TEXT,
  `provider_reference` TEXT,
  `verification_data` TEXT, -- JSON stored as TEXT
  `document_path` TEXT,
  `failure_reason` TEXT,
  `verified_at` TEXT,
  `expires_at` TEXT,
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
CREATE UNIQUE INDEX `kyc_verifications_user_id_type_unique` ON `kyc_verifications` (`user_id`,`type`);
CREATE INDEX `kyc_verifications_user_id_status_index` ON `kyc_verifications` (`user_id`,`status`);
CREATE INDEX `kyc_verifications_provider_reference_index` ON `kyc_verifications` (`provider_reference`);
CREATE INDEX `kyc_verifications_status_index` ON `kyc_verifications` (`status`);
CREATE INDEX `kyc_verifications_type_index` ON `kyc_verifications` (`type`);

-- ============================================================================
-- Virtual Cards and Accounts Tables
-- ============================================================================

-- Virtual Cards table
CREATE TABLE `virtual_cards` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER NOT NULL,
  `card_id` TEXT UNIQUE NOT NULL,
  `masked_pan` TEXT NOT NULL,
  `card_type` TEXT NOT NULL DEFAULT 'virtual',
  `brand` TEXT NOT NULL,
  `currency` TEXT NOT NULL DEFAULT 'USD',
  `balance` REAL NOT NULL DEFAULT 0.00,
  `daily_limit` REAL NOT NULL DEFAULT 1000.00,
  `monthly_limit` REAL NOT NULL DEFAULT 5000.00,
  `status` TEXT CHECK (`status` IN ('active','frozen','terminated')) DEFAULT 'active',
  `provider` TEXT NOT NULL,
  `provider_reference` TEXT UNIQUE NOT NULL,
  `metadata` TEXT, -- JSON stored as TEXT
  `expires_at` TEXT,
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
CREATE INDEX `virtual_cards_user_id_status_index` ON `virtual_cards` (`user_id`,`status`);
CREATE INDEX `virtual_cards_status_index` ON `virtual_cards` (`status`);
CREATE INDEX `virtual_cards_provider_index` ON `virtual_cards` (`provider`);

-- Virtual Accounts table
CREATE TABLE `virtual_accounts` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER NOT NULL,
  `account_number` TEXT UNIQUE NOT NULL,
  `account_name` TEXT NOT NULL,
  `bank_code` TEXT NOT NULL,
  `bank_name` TEXT NOT NULL,
  `provider` TEXT NOT NULL,
  `provider_reference` TEXT UNIQUE NOT NULL,
  `currency` TEXT NOT NULL DEFAULT 'NGN',
  `is_active` INTEGER NOT NULL DEFAULT 1,
  `metadata` TEXT, -- JSON stored as TEXT
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
CREATE INDEX `virtual_accounts_user_id_is_active_index` ON `virtual_accounts` (`user_id`,`is_active`);
CREATE INDEX `virtual_accounts_provider_index` ON `virtual_accounts` (`provider`);

-- ============================================================================
-- Services and Configuration Tables
-- ============================================================================

-- Services table for bill payment and airtime services
CREATE TABLE `services` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT NOT NULL,
  `slug` TEXT UNIQUE NOT NULL,
  `category` TEXT CHECK (`category` IN ('airtime','data','electricity','cable_tv','internet')) NOT NULL,
  `provider_code` TEXT,
  `provider_name` TEXT,
  `available_amounts` TEXT, -- JSON stored as TEXT
  `minimum_amount` REAL,
  `maximum_amount` REAL,
  `commission_percentage` REAL NOT NULL DEFAULT 0.00,
  `commission_cap` REAL,
  `is_active` INTEGER NOT NULL DEFAULT 1,
  `metadata` TEXT, -- JSON stored as TEXT
  `created_at` TEXT,
  `updated_at` TEXT
);
CREATE INDEX `services_category_is_active_index` ON `services` (`category`,`is_active`);
CREATE INDEX `services_provider_code_index` ON `services` (`provider_code`);

-- ============================================================================
-- Notification and Communication Tables
-- ============================================================================

-- Notifications table
CREATE TABLE `notifications` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER,
  `admin_id` INTEGER,
  `title` TEXT NOT NULL,
  `message` TEXT NOT NULL,
  `type` TEXT CHECK (`type` IN ('success','warning','error','info')) DEFAULT 'info',
  `channel` TEXT CHECK (`channel` IN ('email','sms','push','in_app')) DEFAULT 'in_app',
  `recipients` TEXT, -- JSON stored as TEXT
  `metadata` TEXT, -- JSON stored as TEXT
  `sent_at` TEXT,
  `read_at` TEXT,
  `is_broadcast` INTEGER NOT NULL DEFAULT 0,
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
);
CREATE INDEX `notifications_user_id_created_at_index` ON `notifications` (`user_id`,`created_at`);
CREATE INDEX `notifications_type_created_at_index` ON `notifications` (`type`,`created_at`);
CREATE INDEX `notifications_channel_created_at_index` ON `notifications` (`channel`,`created_at`);
CREATE INDEX `notifications_is_broadcast_index` ON `notifications` (`is_broadcast`);
CREATE INDEX `notifications_admin_id_index` ON `notifications` (`admin_id`);

-- ============================================================================
-- Audit and Logging Tables
-- ============================================================================

-- Audit Logs table for compliance and tracking
CREATE TABLE `audit_logs` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` INTEGER,
  `admin_id` INTEGER,
  `action` TEXT NOT NULL,
  `model_type` TEXT,
  `model_id` INTEGER,
  `target_type` TEXT,
  `target_id` INTEGER,
  `old_values` TEXT, -- JSON stored as TEXT
  `new_values` TEXT, -- JSON stored as TEXT
  `metadata` TEXT, -- JSON stored as TEXT
  `data` TEXT, -- JSON stored as TEXT
  `ip_address` TEXT,
  `user_agent` TEXT,
  `created_at` TEXT,
  `updated_at` TEXT,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
);
CREATE INDEX `audit_logs_user_id_created_at_index` ON `audit_logs` (`user_id`,`created_at`);
CREATE INDEX `audit_logs_admin_id_created_at_index` ON `audit_logs` (`admin_id`,`created_at`);
CREATE INDEX `audit_logs_action_created_at_index` ON `audit_logs` (`action`,`created_at`);
CREATE INDEX `audit_logs_model_type_model_id_index` ON `audit_logs` (`model_type`,`model_id`);
CREATE INDEX `audit_logs_target_type_target_id_index` ON `audit_logs` (`target_type`,`target_id`);

-- Cron Logs table for scheduled task tracking
CREATE TABLE `cron_logs` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `task_name` TEXT NOT NULL,
  `status` TEXT CHECK (`status` IN ('success','failed','warning')) DEFAULT 'success',
  `message` TEXT,
  `executed_at` TEXT NOT NULL,
  `execution_time` REAL,
  `memory_usage` INTEGER,
  `error_details` TEXT, -- JSON stored as TEXT
  `created_at` TEXT,
  `updated_at` TEXT
);
CREATE INDEX `cron_logs_task_name_executed_at_index` ON `cron_logs` (`task_name`,`executed_at`);
CREATE INDEX `cron_logs_status_executed_at_index` ON `cron_logs` (`status`,`executed_at`);

-- ============================================================================
-- Webhook and Integration Tables
-- ============================================================================

-- Webhooks table for external webhook processing
CREATE TABLE `webhooks` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `provider` TEXT NOT NULL,
  `event_type` TEXT NOT NULL,
  `external_reference` TEXT,
  `payload` TEXT NOT NULL, -- JSON stored as TEXT
  `status` TEXT CHECK (`status` IN ('pending','processed','failed')) DEFAULT 'pending',
  `failure_reason` TEXT,
  `retry_count` INTEGER NOT NULL DEFAULT 0,
  `processed_at` TEXT,
  `created_at` TEXT,
  `updated_at` TEXT
);
CREATE INDEX `webhooks_provider_event_type_index` ON `webhooks` (`provider`,`event_type`);
CREATE INDEX `webhooks_status_created_at_index` ON `webhooks` (`status`,`created_at`);
CREATE INDEX `webhooks_external_reference_index` ON `webhooks` (`external_reference`);

-- ============================================================================
-- End of Database Schema
-- ============================================================================