-- ============================================================
-- SecureBank — Complete MySQL Schema for phpMyAdmin Import
-- ============================================================
-- This file can be imported directly into phpMyAdmin.
-- Compatible with MySQL 8.0+ and cPanel hosting.
--
-- Usage:
--   1. Create a database named `online_banking` in cPanel/phpMyAdmin
--   2. Import this file
--   3. Update your .env file with the correct DB credentials
--   4. Run: php artisan migrate --seed (or use this SQL directly)
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- ============================================================
-- USERS (customers)
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `two_factor_secret` VARCHAR(255) DEFAULT NULL,
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_email_index` (`email`),
  KEY `users_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PASSWORD RESET TOKENS
-- ============================================================
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ROLES
-- ============================================================
CREATE TABLE IF NOT EXISTS `roles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PERMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ROLE_PERMISSION (pivot)
-- ============================================================
CREATE TABLE IF NOT EXISTS `role_permission` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permission_role_id_permission_id_unique` (`role_id`,`permission_id`),
  KEY `role_permission_permission_id_foreign` (`permission_id`),
  CONSTRAINT `role_permission_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_permission_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ADMINS
-- ============================================================
CREATE TABLE IF NOT EXISTS `admins` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role_id` BIGINT UNSIGNED DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admins_email_unique` (`email`),
  KEY `admins_email_index` (`email`),
  KEY `admins_role_id_foreign` (`role_id`),
  CONSTRAINT `admins_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ACCOUNTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `account_number` VARCHAR(20) NOT NULL,
  `account_type` VARCHAR(50) NOT NULL DEFAULT 'savings',
  `currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
  `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `opened_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_account_number_unique` (`account_number`),
  UNIQUE KEY `accounts_user_id_unique` (`user_id`),
  KEY `accounts_status_index` (`status`),
  KEY `accounts_account_number_index` (`account_number`),
  CONSTRAINT `accounts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BALANCES
-- ============================================================
CREATE TABLE IF NOT EXISTS `balances` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` BIGINT UNSIGNED NOT NULL,
  `available_balance` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `ledger_balance` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `total_deposits` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `total_withdrawals` DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `balances_account_id_unique` (`account_id`),
  CONSTRAINT `balances_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BENEFICIARIES
-- ============================================================
CREATE TABLE IF NOT EXISTS `beneficiaries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `account_number` VARCHAR(255) NOT NULL,
  `bank_name` VARCHAR(255) NOT NULL,
  `nickname` VARCHAR(50) DEFAULT NULL,
  `currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `beneficiaries_user_id_account_number_index` (`user_id`,`account_number`),
  CONSTRAINT `beneficiaries_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TRANSACTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `reference` VARCHAR(40) NOT NULL,
  `sender_user_id` BIGINT UNSIGNED NOT NULL,
  `sender_account_id` BIGINT UNSIGNED NOT NULL,
  `beneficiary_name` VARCHAR(255) NOT NULL,
  `beneficiary_account_number` VARCHAR(255) NOT NULL,
  `beneficiary_bank` VARCHAR(255) NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
  `description` TEXT DEFAULT NULL,
  `transfer_pin_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `otp_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `cot_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) DEFAULT NULL,
  `verification_stage` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending_verification',
  `settlement_status` VARCHAR(30) DEFAULT NULL,
  `settlement_reference` VARCHAR(40) DEFAULT NULL,
  `processing_error` TEXT DEFAULT NULL,
  `is_simulated` TINYINT(1) NOT NULL DEFAULT 0,
  `idempotency_key` VARCHAR(60) DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `processed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_reference_unique` (`reference`),
  UNIQUE KEY `transactions_idempotency_key_unique` (`idempotency_key`),
  KEY `transactions_sender_user_id_index` (`sender_user_id`),
  KEY `transactions_status_index` (`status`),
  KEY `transactions_settlement_status_index` (`settlement_status`),
  KEY `transactions_created_at_index` (`created_at`),
  CONSTRAINT `transactions_sender_user_id_foreign` FOREIGN KEY (`sender_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transactions_sender_account_id_foreign` FOREIGN KEY (`sender_account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LEDGER ENTRIES (double-entry bookkeeping)
-- ============================================================
CREATE TABLE IF NOT EXISTS `ledger_entries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `account_id` BIGINT UNSIGNED NOT NULL,
  `entry_type` ENUM('debit','credit') NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
  `description` TEXT DEFAULT NULL,
  `balance_after` DECIMAL(18,2) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ledger_entries_account_id_index` (`account_id`),
  KEY `ledger_entries_transaction_id_index` (`transaction_id`),
  CONSTRAINT `ledger_entries_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ledger_entries_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TRANSFER PINS (hashed)
-- ============================================================
CREATE TABLE IF NOT EXISTS `transfer_pins` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `pin_hash` VARCHAR(255) NOT NULL,
  `changed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transfer_pins_user_id_unique` (`user_id`),
  CONSTRAINT `transfer_pins_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- OTP VERIFICATIONS (hashed codes)
-- ============================================================
CREATE TABLE IF NOT EXISTS `otp_verifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `code_hash` VARCHAR(255) NOT NULL,
  `purpose` VARCHAR(255) NOT NULL,
  `expires_at` TIMESTAMP NOT NULL,
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `max_attempts` INT UNSIGNED NOT NULL DEFAULT 5,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `transaction_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `otp_verifications_user_id_purpose_index` (`user_id`,`purpose`),
  CONSTRAINT `otp_verifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `otp_verifications_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- VERIFICATION RULES (COT/Tax/IMF)
-- ============================================================
CREATE TABLE IF NOT EXISTS `verification_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `required_amount_min` DECIMAL(18,2) DEFAULT NULL,
  `required_amount_max` DECIMAL(18,2) DEFAULT NULL,
  `code_prefix` VARCHAR(255) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `legal_basis` TEXT DEFAULT NULL,
  `calculation_formula` TEXT DEFAULT NULL,
  `is_global` TINYINT(1) NOT NULL DEFAULT 1,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `admin_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `verification_rules_type_index` (`type`),
  KEY `verification_rules_enabled_index` (`enabled`),
  CONSTRAINT `verification_rules_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `verification_rules_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ACCOUNT RESTRICTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `account_restrictions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `status` VARCHAR(255) NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `admin_note` TEXT DEFAULT NULL,
  `customer_facing_message` TEXT DEFAULT NULL,
  `restricted_by` BIGINT UNSIGNED DEFAULT NULL,
  `restricted_at` TIMESTAMP NOT NULL,
  `review_at` TIMESTAMP NULL DEFAULT NULL,
  `restored_at` TIMESTAMP NULL DEFAULT NULL,
  `restored_by` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `account_restrictions_user_id_index` (`user_id`),
  KEY `account_restrictions_status_index` (`status`),
  CONSTRAINT `account_restrictions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `account_restrictions_restricted_by_foreign` FOREIGN KEY (`restricted_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL,
  CONSTRAINT `account_restrictions_restored_by_foreign` FOREIGN KEY (`restored_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SMTP SETTINGS (encrypted password)
-- ============================================================
CREATE TABLE IF NOT EXISTS `smtp_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `host` VARCHAR(255) NOT NULL,
  `port` INT UNSIGNED NOT NULL DEFAULT 587,
  `encryption` VARCHAR(10) NOT NULL DEFAULT 'tls',
  `username` VARCHAR(255) DEFAULT NULL,
  `password` TEXT DEFAULT NULL,
  `sender_email` VARCHAR(255) NOT NULL,
  `sender_name` VARCHAR(255) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `notify_on_transfer` TINYINT(1) NOT NULL DEFAULT 1,
  `notify_on_login` TINYINT(1) NOT NULL DEFAULT 0,
  `notify_on_registration` TINYINT(1) NOT NULL DEFAULT 1,
  `otp_template` LONGTEXT DEFAULT NULL,
  `welcome_template` LONGTEXT DEFAULT NULL,
  `reset_template` LONGTEXT DEFAULT NULL,
  `transfer_template` LONGTEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BANK SETTINGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `bank_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `bank_name` VARCHAR(255) NOT NULL,
  `tagline` VARCHAR(255) DEFAULT NULL,
  `logo_path` VARCHAR(255) DEFAULT NULL,
  `favicon_path` VARCHAR(255) DEFAULT NULL,
  `support_email` VARCHAR(255) NOT NULL,
  `customer_care_phone` VARCHAR(255) NOT NULL,
  `support_hours` VARCHAR(255) DEFAULT NULL,
  `is_live_chat_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `primary_color` VARCHAR(7) NOT NULL DEFAULT '#123B70',
  `cta_color` VARCHAR(7) NOT NULL DEFAULT '#2878DF',
  `accent_color` VARCHAR(7) NOT NULL DEFAULT '#0F9F83',
  `default_currency` VARCHAR(3) NOT NULL DEFAULT 'USD',
  `iban` VARCHAR(255) DEFAULT NULL,
  `swift_code` VARCHAR(255) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AUTHENTICATION SETTINGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `authentication_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `otp_expiry_minutes` INT UNSIGNED NOT NULL DEFAULT 10,
  `otp_max_attempts` INT UNSIGNED NOT NULL DEFAULT 5,
  `otp_resend_cooldown_seconds` INT UNSIGNED NOT NULL DEFAULT 60,
  `otp_length` INT UNSIGNED NOT NULL DEFAULT 6,
  `password_min_length` INT UNSIGNED NOT NULL DEFAULT 8,
  `password_require_uppercase` TINYINT(1) NOT NULL DEFAULT 1,
  `password_require_lowercase` TINYINT(1) NOT NULL DEFAULT 1,
  `password_require_numbers` TINYINT(1) NOT NULL DEFAULT 1,
  `password_require_symbols` TINYINT(1) NOT NULL DEFAULT 0,
  `login_rate_limit_attempts` INT UNSIGNED NOT NULL DEFAULT 5,
  `login_rate_limit_minutes` INT UNSIGNED NOT NULL DEFAULT 1,
  `session_timeout_minutes` INT UNSIGNED NOT NULL DEFAULT 120,
  `transfer_min_amount` DECIMAL(18,2) NOT NULL DEFAULT 1.00,
  `transfer_max_amount` DECIMAL(18,2) NOT NULL DEFAULT 1000000.00,
  `daily_transfer_limit` DECIMAL(18,2) NOT NULL DEFAULT 50000.00,
  `require_2fa` TINYINT(1) NOT NULL DEFAULT 0,
  `require_transfer_pin` TINYINT(1) NOT NULL DEFAULT 1,
  `require_email_verification` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CHAT WIDGET SETTINGS (Tawk.to, SmartSupp, Tidio, WhatsApp, Translator)
-- ============================================================
CREATE TABLE IF NOT EXISTS `chat_widget_settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `widget_type` VARCHAR(255) NOT NULL DEFAULT 'none',
  `embed_code` LONGTEXT DEFAULT NULL,
  `widget_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `widget_position` VARCHAR(50) NOT NULL DEFAULT 'bottom-right',
  `whatsapp_number` VARCHAR(30) DEFAULT NULL,
  `whatsapp_default_message` TEXT DEFAULT NULL,
  `whatsapp_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `whatsapp_position` VARCHAR(50) NOT NULL DEFAULT 'bottom-left',
  `whatsapp_display_name` VARCHAR(100) DEFAULT NULL,
  `whatsapp_avatar_url` VARCHAR(500) DEFAULT NULL,
  `translator_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `translator_default_language` VARCHAR(10) NOT NULL DEFAULT 'en',
  `translator_welcome_message` TEXT DEFAULT NULL,
  `translator_auto_detect` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `type` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `data` JSON DEFAULT NULL,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_user_id_read_at_index` (`user_id`,`read_at`),
  CONSTRAINT `notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SUPPORT CONVERSATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `support_conversations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `assigned_admin_id` BIGINT UNSIGNED DEFAULT NULL,
  `subject` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'open',
  `priority` VARCHAR(255) NOT NULL DEFAULT 'normal',
  `closed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `support_conversations_status_index` (`status`),
  CONSTRAINT `support_conversations_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `support_conversations_assigned_admin_id_foreign` FOREIGN KEY (`assigned_admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SUPPORT MESSAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS `support_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` BIGINT UNSIGNED NOT NULL,
  `sender_type` VARCHAR(255) NOT NULL,
  `sender_id` BIGINT UNSIGNED NOT NULL,
  `body` TEXT NOT NULL,
  `attachments` JSON DEFAULT NULL,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `support_messages_conversation_id_index` (`conversation_id`),
  CONSTRAINT `support_messages_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `support_conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- RECEIPTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `receipts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `receipt_code` VARCHAR(40) NOT NULL,
  `verification_url` VARCHAR(255) DEFAULT NULL,
  `pdf_path` VARCHAR(255) DEFAULT NULL,
  `image_path` VARCHAR(255) DEFAULT NULL,
  `generated_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipts_transaction_id_unique` (`transaction_id`),
  UNIQUE KEY `receipts_receipt_code_unique` (`receipt_code`),
  CONSTRAINT `receipts_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AUDIT LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` BIGINT UNSIGNED DEFAULT NULL,
  `action` VARCHAR(255) NOT NULL,
  `entity_type` VARCHAR(255) DEFAULT NULL,
  `entity_id` BIGINT UNSIGNED 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` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_admin_id_index` (`admin_id`),
  KEY `audit_logs_entity_type_index` (`entity_type`),
  CONSTRAINT `audit_logs_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOGIN HISTORY
-- ============================================================
CREATE TABLE IF NOT EXISTS `login_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `device` VARCHAR(255) DEFAULT NULL,
  `browser` VARCHAR(255) DEFAULT NULL,
  `location` VARCHAR(255) DEFAULT NULL,
  `login_at` TIMESTAMP NOT NULL,
  `logout_at` TIMESTAMP NULL DEFAULT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'success',
  PRIMARY KEY (`id`),
  KEY `login_history_user_id_index` (`user_id`),
  KEY `login_history_login_at_index` (`login_at`),
  CONSTRAINT `login_history_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LARAVEL FRAMEWORK TABLES
-- ============================================================
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(255) NOT NULL,
  `user_id` BIGINT UNSIGNED DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `payload` LONGTEXT NOT NULL,
  `last_activity` INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache` (
  `key` VARCHAR(255) NOT NULL,
  `value` MEDIUMTEXT NOT NULL,
  `expiration` INT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cache_locks` (
  `key` VARCHAR(255) NOT NULL,
  `owner` VARCHAR(255) NOT NULL,
  `expiration` INT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` VARCHAR(255) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `attempts` TINYINT UNSIGNED NOT NULL,
  `reserved_at` INT UNSIGNED DEFAULT NULL,
  `available_at` INT UNSIGNED NOT NULL,
  `created_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(255) NOT NULL,
  `connection` TEXT NOT NULL,
  `queue` TEXT NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `exception` LONGTEXT NOT NULL,
  `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` VARCHAR(255) NOT NULL,
  `batch` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default roles
INSERT INTO `roles` (`name`, `slug`, `description`, `created_at`, `updated_at`) VALUES
('Super Admin', 'admin', 'Full access to all features', NOW(), NOW()),
('Manager', 'manager', 'Manage customers and transactions', NOW(), NOW()),
('Support Agent', 'support', 'Handle customer support queries', NOW(), NOW()),
('Compliance Officer', 'compliance', 'Manage verification rules', NOW(), NOW());

-- Default permissions
INSERT INTO `permissions` (`name`, `slug`, `created_at`, `updated_at`) VALUES
('View Dashboard', 'view-dashboard', NOW(), NOW()),
('Manage Customers', 'manage-customers', NOW(), NOW()),
('Manage Transactions', 'manage-transactions', NOW(), NOW()),
('Manage Verification', 'manage-verification', NOW(), NOW()),
('Manage Settings', 'manage-settings', NOW(), NOW()),
('Manage SMTP', 'manage-smtp', NOW(), NOW()),
('Manage Admins', 'manage-admins', NOW(), NOW()),
('Reply to Support', 'reply-support', NOW(), NOW()),
('View Audit Logs', 'view-audit-logs', NOW(), NOW()),
('View Login History', 'view-login-history', NOW(), NOW());

-- Super Admin gets all permissions
INSERT INTO `role_permission` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM `roles` r CROSS JOIN `permissions` p WHERE r.slug = 'admin';

-- Default bank settings
INSERT INTO `bank_settings` (`bank_name`, `tagline`, `support_email`, `customer_care_phone`, `support_hours`, `is_live_chat_enabled`, `primary_color`, `cta_color`, `accent_color`, `default_currency`, `created_at`, `updated_at`) VALUES
('SecureBank', 'Banking Made Simple', 'support@securebank.com', '+1 (800) 555-0100', '24/7', 1, '#123B70', '#2878DF', '#0F9F83', 'USD', NOW(), NOW());

-- Default authentication settings
INSERT INTO `authentication_settings` (`otp_expiry_minutes`, `otp_max_attempts`, `otp_resend_cooldown_seconds`, `otp_length`, `password_min_length`, `password_require_uppercase`, `password_require_lowercase`, `password_require_numbers`, `password_require_symbols`, `login_rate_limit_attempts`, `login_rate_limit_minutes`, `session_timeout_minutes`, `transfer_min_amount`, `transfer_max_amount`, `daily_transfer_limit`, `require_2fa`, `require_transfer_pin`, `require_email_verification`, `created_at`, `updated_at`) VALUES
(10, 5, 60, 6, 8, 1, 1, 1, 0, 5, 1, 120, 1.00, 1000000.00, 50000.00, 0, 1, 1, NOW(), NOW());

-- Default chat widget and translator settings
INSERT INTO `chat_widget_settings` (`widget_type`, `widget_enabled`, `widget_position`, `whatsapp_enabled`, `whatsapp_position`, `whatsapp_display_name`, `whatsapp_default_message`, `translator_enabled`, `translator_default_language`, `translator_auto_detect`, `translator_welcome_message`, `created_at`, `updated_at`) VALUES
('none', 0, 'bottom-right', 0, 'bottom-left', 'SecureBank Support', 'Hello! I have a question about my SecureBank account.', 1, 'en', 1, 'Select your language to translate this page', NOW(), NOW());

-- Default verification rules (all disabled by default)
INSERT INTO `verification_rules` (`type`, `name`, `enabled`, `required_amount_min`, `description`, `legal_basis`, `calculation_formula`, `is_global`, `created_at`, `updated_at`) VALUES
('cot', 'COT Verification', 0, 10000.00, 'Cost of Transfer verification for large transactions.', 'Banking Regulation 2015/847', 'No fee is charged. Verification only.', 1, NOW(), NOW()),
('tax', 'Tax Verification', 0, 50000.00, 'Tax compliance verification for high-value transfers.', 'FATCA / CRS reporting requirements', '', 1, NOW(), NOW()),
('imf', 'IMF Verification', 0, 100000.00, 'International transfer compliance verification.', 'IMF Anti-Money Laundering guidelines', '', 1, NOW(), NOW());

-- NOTE: To create the admin user, run `php artisan seed:admin` after running migrations.
-- Or use the following SQL with a properly hashed password (use `php artisan tinker` to generate):
-- INSERT INTO `admins` (`name`, `email`, `password`, `role_id`, `is_active`, `created_at`, `updated_at`)
-- VALUES ('Super Admin', 'admin@securebank.com', '$2y$12$...bcrypt_hash...', 1, 1, NOW(), NOW());

-- ============================================================
-- END OF SCHEMA
-- ============================================================