-- phpMyAdmin SQL Dump -- version 5.2.2 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Apr 29, 2026 at 11:28 AM -- Server version: 10.11.11-MariaDB-cll-lve -- PHP Version: 8.4.11 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: PlexiTrust Online Banking -- (import this into the database you create in cPanel) -- -- -------------------------------------------------------- -- -- Table structure for table `2fa_settings` -- CREATE TABLE `2fa_settings` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `enabled` tinyint(1) DEFAULT 0, `secret_key` varchar(255) DEFAULT NULL, `backup_codes` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `2fa_settings` -- -- -------------------------------------------------------- -- -- Table structure for table `accounts` -- CREATE TABLE `accounts` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `account_number` varchar(20) NOT NULL, `iban` varchar(34) DEFAULT NULL, `bic_swift` varchar(11) DEFAULT NULL, `routing_number` varchar(9) DEFAULT NULL, `sort_code` varchar(6) DEFAULT NULL, `bank_code` varchar(20) DEFAULT NULL, `branch_code` varchar(20) DEFAULT NULL, `account_format` varchar(50) DEFAULT NULL, `account_type` enum('checking','savings','business','credit') DEFAULT 'checking', `account_name` varchar(100) DEFAULT 'Primary Account', `balance` decimal(15,2) DEFAULT 0.00, `available_balance` decimal(15,2) DEFAULT 0.00, `interest_rate` decimal(5,4) DEFAULT 0.0000, `currency` varchar(3) DEFAULT 'USD', `country_code` char(2) DEFAULT NULL, `status` enum('active','inactive','pending','frozen') DEFAULT 'active', `freeze_reason` text DEFAULT NULL, `freeze_date` datetime DEFAULT NULL, `unfreeze_date` datetime DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejection_reason` text DEFAULT NULL, `overdraft_limit` decimal(10,2) DEFAULT 0.00, `minimum_balance` decimal(10,2) DEFAULT 0.00, `opened_date` timestamp NOT NULL DEFAULT current_timestamp(), `closed_date` timestamp NULL DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `last_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `accounts` -- -- -------------------------------------------------------- -- -- Table structure for table `account_alerts` -- CREATE TABLE `account_alerts` ( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `alert_type` enum('info','warning','danger') DEFAULT NULL, `alert_title` varchar(255) DEFAULT NULL, `alert_message` text DEFAULT NULL, `is_resolved` tinyint(1) DEFAULT 0, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `account_closure_requests` -- CREATE TABLE `account_closure_requests` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `reason` text DEFAULT NULL, `status` enum('pending','approved','rejected','cancelled') DEFAULT 'pending', `admin_notes` text DEFAULT NULL, `requested_at` timestamp NOT NULL DEFAULT current_timestamp(), `processed_at` timestamp NULL DEFAULT NULL, `processed_by` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `account_limits` -- CREATE TABLE `account_limits` ( `id` int(11) NOT NULL, `account_id` int(11) NOT NULL, `max_monthly_transfers` int(11) DEFAULT 10, `current_monthly_transfers` int(11) DEFAULT 0, `limit_exceeded_at` datetime DEFAULT NULL, `monthly_reset_date` date DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `account_limits` -- -- -------------------------------------------------------- -- -- Table structure for table `admin_activities` -- CREATE TABLE `admin_activities` ( `id` int(11) NOT NULL, `admin_id` int(11) NOT NULL, `action` varchar(100) NOT NULL, `description` text DEFAULT NULL, `priority` enum('low','medium','high') DEFAULT 'medium', `ip_address` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `admin_activities` -- -- -------------------------------------------------------- -- -- Table structure for table `admin_activity_log` -- CREATE TABLE `admin_activity_log` ( `id` int(11) NOT NULL, `admin_id` int(11) NOT NULL, `action` varchar(100) NOT NULL, `description` text DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `admin_settings` -- CREATE TABLE `admin_settings` ( `id` int(11) NOT NULL, `setting_key` varchar(100) NOT NULL, `setting_value` text DEFAULT NULL, `setting_type` enum('string','integer','boolean','json') DEFAULT 'string', `description` text DEFAULT NULL, `updated_by` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `admin_settings` -- INSERT INTO `admin_settings` (`id`, `setting_key`, `setting_value`, `setting_type`, `description`, `updated_by`, `created_at`, `updated_at`) VALUES (1, 'admin_session_timeout', '1800', 'integer', 'Admin session timeout in seconds (30 minutes)', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (2, 'max_login_attempts', '5', 'integer', 'Maximum allowed login attempts before lockout', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (3, 'account_lockout_duration', '900', 'integer', 'Account lockout duration in seconds (15 minutes)', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (4, 'require_account_approval', '1', 'boolean', 'Whether new accounts require admin approval', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (5, 'enable_two_factor', '0', 'boolean', 'Enable two-factor authentication for users', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (6, 'maintenance_mode', '0', 'boolean', 'Enable maintenance mode', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (7, 'currency_default', 'USD', 'string', 'Default currency', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (8, 'interest_rate_savings', '0.025', 'string', 'Default savings account interest rate', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'), (9, 'interest_rate_checking', '0.01', 'string', 'Default checking account interest rate', NULL, '2025-10-23 07:23:24', '2025-10-23 07:23:24'); -- -------------------------------------------------------- -- -- Table structure for table `api_rate_limits` -- CREATE TABLE `api_rate_limits` ( `id` int(11) NOT NULL, `api_name` varchar(50) NOT NULL, `identifier` varchar(255) NOT NULL, `request_count` int(11) DEFAULT 1, `first_request` timestamp NULL DEFAULT current_timestamp(), `last_request` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `api_rate_limits` -- -- -------------------------------------------------------- -- -- Table structure for table `bank_identifiers` -- CREATE TABLE `bank_identifiers` ( `id` int(11) NOT NULL, `country_code` char(2) NOT NULL, `bank_name` varchar(200) NOT NULL, `bank_code` varchar(20) NOT NULL, `bic_prefix` varchar(8) DEFAULT NULL, `address` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `bank_identifiers` -- INSERT INTO `bank_identifiers` (`id`, `country_code`, `bank_name`, `bank_code`, `bic_prefix`, `address`, `created_at`) VALUES (1, 'DK', 'Danske Bank', '0216', 'DABADKKK', NULL, '2026-03-06 08:25:00'), (2, 'DK', 'Nordea', '2200', 'NDEADKKK', NULL, '2026-03-06 08:25:00'), (3, 'DK', 'Jyske Bank', '7115', 'JYBADKKK', NULL, '2026-03-06 08:25:00'), (4, 'DK', 'Sydbank', '7110', 'SYBKDK22', NULL, '2026-03-06 08:25:00'), (5, 'GB', 'Barclays', '20', 'BARCGB22', NULL, '2026-03-06 08:25:00'), (6, 'GB', 'HSBC', '40', 'HBUKGB4B', NULL, '2026-03-06 08:25:00'), (7, 'US', 'Chase', '021000021', 'CHASUS33', NULL, '2026-03-06 08:25:00'), (8, 'US', 'Bank of America', '026009593', 'BOFAUS3N', NULL, '2026-03-06 08:25:00'), (9, 'DE', 'Deutsche Bank', '10070000', 'DEUTDEBB', NULL, '2026-03-06 08:25:00'), (10, 'FR', 'BNP Paribas', '30004', 'BNPAFRPP', NULL, '2026-03-06 08:25:00'), (11, 'NL', 'ING Bank', 'INGB', 'INGBNL2A', NULL, '2026-03-06 08:25:00'), (12, 'SE', 'SEB', '500', 'ESSESESS', NULL, '2026-03-06 08:25:00'), (13, 'NO', 'DNB', '7033', 'DNBANOKK', NULL, '2026-03-06 08:25:00'), (14, 'CH', 'UBS', '230', 'UBSWCHZH', NULL, '2026-03-06 08:25:00'); -- -------------------------------------------------------- -- -- Table structure for table `bills` -- CREATE TABLE `bills` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `payee_name` varchar(100) NOT NULL, `payee_account` varchar(50) DEFAULT NULL, `amount` decimal(10,2) NOT NULL, `due_date` date NOT NULL, `status` enum('pending','paid','overdue','cancelled') DEFAULT 'pending', `payment_date` timestamp NULL DEFAULT NULL, `paid_at` timestamp NULL DEFAULT NULL, `description` text DEFAULT NULL, `recurring` enum('none','monthly','quarterly','yearly') DEFAULT 'none', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `currency` varchar(3) DEFAULT 'USD' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `bills` -- -- -------------------------------------------------------- -- -- Table structure for table `bill_payments` -- CREATE TABLE `bill_payments` ( `id` int(11) NOT NULL, `bill_id` int(11) DEFAULT NULL, `user_id` int(11) NOT NULL, `payee_name` varchar(100) NOT NULL, `payee_account` varchar(50) NOT NULL, `amount` decimal(10,2) NOT NULL, `account_id` int(11) NOT NULL, `payment_date` date NOT NULL, `description` text DEFAULT NULL, `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `chat_attachments` -- CREATE TABLE `chat_attachments` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `file_name` varchar(255) NOT NULL, `file_path` varchar(500) NOT NULL, `file_size` int(11) NOT NULL, `uploaded_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `check_deposits` -- CREATE TABLE `check_deposits` ( `id` int(11) NOT NULL, `transaction_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `account_id` int(11) NOT NULL, `amount` decimal(15,2) NOT NULL, `currency` varchar(3) DEFAULT 'USD', `check_number` varchar(50) DEFAULT NULL, `check_type` varchar(50) DEFAULT NULL, `front_image` varchar(255) DEFAULT NULL, `back_image` varchar(255) DEFAULT NULL, `status` enum('pending','approved','rejected','completed') DEFAULT 'pending', `processed_by` int(11) DEFAULT NULL, `processed_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `ocr_data` text DEFAULT NULL, `ocr_confidence` int(11) DEFAULT NULL, `extracted_routing` varchar(20) DEFAULT NULL, `extracted_account` varchar(50) DEFAULT NULL, `extracted_check_number` varchar(20) DEFAULT NULL, `extracted_payee` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `check_deposits` -- -- -------------------------------------------------------- -- -- Table structure for table `countries` -- CREATE TABLE `countries` ( `code` varchar(2) NOT NULL, `name` varchar(100) NOT NULL, `phone_code` varchar(10) DEFAULT NULL, `currency_code` varchar(3) DEFAULT NULL, `currency_symbol` varchar(10) DEFAULT NULL, `date_format` varchar(20) DEFAULT NULL, `active` tinyint(1) DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `countries` -- INSERT INTO `countries` (`code`, `name`, `phone_code`, `currency_code`, `currency_symbol`, `date_format`, `active`) VALUES ('AU', 'Australia', '+61', 'AUD', '$', 'DD/MM/YYYY', 1), ('CA', 'Canada', '+1', 'CAD', '$', 'YYYY-MM-DD', 1), ('CN', 'China', '+86', 'CNY', '¥', 'YYYY-MM-DD', 1), ('DE', 'Germany', '+49', 'EUR', '€', 'DD.MM.YYYY', 1), ('ES', 'Spain', '+34', 'EUR', '€', 'DD/MM/YYYY', 1), ('FR', 'France', '+33', 'EUR', '€', 'DD/MM/YYYY', 1), ('GB', 'United Kingdom', '+44', 'GBP', '£', 'DD/MM/YYYY', 1), ('JP', 'Japan', '+81', 'JPY', '¥', 'YYYY/MM/DD', 1), ('US', 'United States', '+1', 'USD', '$', 'MM/DD/YYYY', 1); -- -------------------------------------------------------- -- -- Table structure for table `country_banking_formats` -- CREATE TABLE `country_banking_formats` ( `id` int(11) NOT NULL, `country_code` char(2) NOT NULL, `country_name` varchar(100) NOT NULL, `currency_code` varchar(3) NOT NULL, `banking_system` enum('european','us','uk','canadian','australian','asian','other') NOT NULL, `uses_iban` tinyint(1) DEFAULT 0, `uses_bic` tinyint(1) DEFAULT 0, `uses_routing_number` tinyint(1) DEFAULT 0, `uses_sort_code` tinyint(1) DEFAULT 0, `uses_bank_code` tinyint(1) DEFAULT 0, `uses_branch_code` tinyint(1) DEFAULT 0, `iban_length` int(11) DEFAULT NULL, `iban_format` varchar(50) DEFAULT NULL, `bic_format` varchar(50) DEFAULT NULL, `routing_number_format` varchar(20) DEFAULT NULL, `sort_code_format` varchar(20) DEFAULT NULL, `account_number_format` varchar(50) DEFAULT NULL, `bank_code_length` int(11) DEFAULT NULL, `branch_code_length` int(11) DEFAULT NULL, `description` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `country_banking_formats` -- INSERT INTO `country_banking_formats` (`id`, `country_code`, `country_name`, `currency_code`, `banking_system`, `uses_iban`, `uses_bic`, `uses_routing_number`, `uses_sort_code`, `uses_bank_code`, `uses_branch_code`, `iban_length`, `iban_format`, `bic_format`, `routing_number_format`, `sort_code_format`, `account_number_format`, `bank_code_length`, `branch_code_length`, `description`, `created_at`, `updated_at`) VALUES (1, 'DK', 'Denmark', 'DKK', 'european', 1, 1, 0, 0, 1, 1, 18, 'DKpp aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (2, 'SE', 'Sweden', 'SEK', 'european', 1, 1, 0, 0, 1, 1, 24, 'SEpp aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (3, 'NO', 'Norway', 'NOK', 'european', 1, 1, 0, 0, 1, 1, 15, 'NOpp aaaa aaaa aaa', NULL, NULL, NULL, 'aaaa aaaa aaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (4, 'DE', 'Germany', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 22, 'DEpp aaaa aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (5, 'FR', 'France', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 27, 'FRpp aaaa aaaa aaaa aaaa aaaa aaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (6, 'GB', 'United Kingdom', 'GBP', 'uk', 1, 1, 0, 1, 1, 1, 22, 'GBpp aaaa aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aa-aa-aa aaaaaaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (7, 'ES', 'Spain', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 24, 'ESpp aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (8, 'IT', 'Italy', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 27, 'ITpp aaaa aaaa aaaa aaaa aaaa aaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (9, 'NL', 'Netherlands', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 18, 'NLpp aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (10, 'BE', 'Belgium', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 16, 'BEpp aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (11, 'CH', 'Switzerland', 'CHF', 'european', 1, 1, 0, 0, 1, 1, 21, 'CHpp aaaa aaaa aaaa aaaa a', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (12, 'AT', 'Austria', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 20, 'ATpp aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (13, 'PL', 'Poland', 'PLN', 'european', 1, 1, 0, 0, 1, 1, 28, 'PLpp aaaa aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (14, 'CZ', 'Czech Republic', 'CZK', 'european', 1, 1, 0, 0, 1, 1, 24, 'CZpp aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (15, 'HU', 'Hungary', 'HUF', 'european', 1, 1, 0, 0, 1, 1, 28, 'HUpp aaaa aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (16, 'RO', 'Romania', 'RON', 'european', 1, 1, 0, 0, 1, 1, 24, 'ROpp aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (17, 'BG', 'Bulgaria', 'BGN', 'european', 1, 1, 0, 0, 1, 1, 22, 'BGpp aaaa aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (18, 'HR', 'Croatia', 'HRK', 'european', 1, 1, 0, 0, 1, 1, 21, 'HRpp aaaa aaaa aaaa aaaa a', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (19, 'IS', 'Iceland', 'ISK', 'european', 1, 1, 0, 0, 1, 1, 26, 'ISpp aaaa aaaa aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (20, 'FI', 'Finland', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 18, 'FIpp aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (21, 'PT', 'Portugal', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 25, 'PTpp aaaa aaaa aaaa aaaa aaaa a', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (22, 'GR', 'Greece', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 27, 'GRpp aaaa aaaa aaaa aaaa aaaa aaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (23, 'IE', 'Ireland', 'EUR', 'european', 1, 1, 0, 1, 1, 1, 22, 'IEpp aaaa aaaa aaaa aaaa aa', NULL, NULL, NULL, 'aa-aa-aa aaaaaaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (24, 'LU', 'Luxembourg', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 20, 'LUpp aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (25, 'MT', 'Malta', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 31, 'MTpp aaaa aaaa aaaa aaaa aaaa aaaa aaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (26, 'SI', 'Slovenia', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 19, 'SIpp aaaa aaaa aaaa aaa', NULL, NULL, NULL, 'aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (27, 'SK', 'Slovakia', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 24, 'SKpp aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (28, 'EE', 'Estonia', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 20, 'EEpp aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (29, 'LV', 'Latvia', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 21, 'LVpp aaaa aaaa aaaa aaaa a', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (30, 'LT', 'Lithuania', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 20, 'LTpp aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (31, 'CY', 'Cyprus', 'EUR', 'european', 1, 1, 0, 0, 1, 1, 28, 'CYpp aaaa aaaa aaaa aaaa aaaa aaaa', NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (32, 'TR', 'Turkey', 'TRY', 'asian', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (33, 'RU', 'Russia', 'RUB', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (34, 'UA', 'Ukraine', 'UAH', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (35, 'RS', 'Serbia', 'RSD', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (36, 'MK', 'North Macedonia', 'MKD', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (37, 'AL', 'Albania', 'ALL', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (38, 'BA', 'Bosnia and Herzegovina', 'BAM', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (39, 'MD', 'Moldova', 'MDL', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (40, 'GE', 'Georgia', 'GEL', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (41, 'AM', 'Armenia', 'AMD', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (42, 'AZ', 'Azerbaijan', 'AZN', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (43, 'KZ', 'Kazakhstan', 'KZT', 'other', 0, 1, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (44, 'US', 'United States', 'USD', 'us', 0, 0, 1, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (45, 'CA', 'Canada', 'CAD', 'canadian', 0, 0, 1, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (46, 'AU', 'Australia', 'AUD', 'australian', 0, 0, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (47, 'JP', 'Japan', 'JPY', 'asian', 0, 0, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (48, 'CN', 'China', 'CNY', 'asian', 0, 0, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'), (49, 'IN', 'India', 'INR', 'asian', 0, 0, 0, 0, 1, 1, NULL, NULL, NULL, NULL, NULL, 'aaaa aaaa aaaa aaaa', NULL, NULL, NULL, '2026-03-06 07:53:04', '2026-03-06 07:53:04'); -- -------------------------------------------------------- -- -- Table structure for table `credit_cards` -- CREATE TABLE `credit_cards` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `card_number` varchar(20) NOT NULL, `card_holder_name` varchar(100) DEFAULT NULL, `card_type` enum('visa','mastercard','amex') DEFAULT 'visa', `expiry_month` int(11) NOT NULL, `expiry_year` int(11) NOT NULL, `cvv` varchar(4) NOT NULL, `credit_limit` decimal(10,2) DEFAULT 5000.00, `available_credit` decimal(10,2) DEFAULT 5000.00, `current_balance` decimal(10,2) DEFAULT 0.00, `minimum_payment` decimal(10,2) DEFAULT 0.00, `payment_due_date` date DEFAULT NULL, `status` enum('active','inactive','blocked','expired') DEFAULT 'active', `issued_date` timestamp NOT NULL DEFAULT current_timestamp(), `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `external_accounts` -- CREATE TABLE `external_accounts` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `account_name` varchar(100) NOT NULL, `account_number` varchar(50) NOT NULL, `bank_name` varchar(100) DEFAULT NULL, `bank_country` varchar(50) DEFAULT NULL, `routing_number` varchar(50) DEFAULT NULL, `account_type` varchar(20) DEFAULT 'checking', `country` varchar(50) DEFAULT 'USA', `currency` varchar(3) DEFAULT 'USD', `status` enum('active','inactive') DEFAULT 'active', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `external_accounts` -- -- -------------------------------------------------------- -- -- Table structure for table `external_transfers` -- CREATE TABLE `external_transfers` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `from_account_id` int(11) NOT NULL, `to_account_number` varchar(50) NOT NULL, `to_routing_number` varchar(20) NOT NULL, `to_account_name` varchar(100) NOT NULL, `to_institution` varchar(100) DEFAULT NULL, `amount` decimal(15,2) NOT NULL, `description` text DEFAULT NULL, `status` enum('pending','approved','rejected','completed','failed') DEFAULT 'pending', `approved_by` int(11) DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejection_reason` text DEFAULT NULL, `transfer_date` timestamp NULL DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `faqs` -- CREATE TABLE `faqs` ( `id` int(11) NOT NULL, `question` text NOT NULL, `answer` text NOT NULL, `category` enum('general','account','transactions','loans','investments','insurance','technical') NOT NULL, `is_active` tinyint(1) DEFAULT 1, `display_order` int(11) DEFAULT 0, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `insurance_products` -- CREATE TABLE `insurance_products` ( `id` int(11) NOT NULL, `product_name` varchar(255) NOT NULL, `description` text DEFAULT NULL, `product_type` enum('health','auto','home','life') NOT NULL, `min_premium` decimal(10,2) NOT NULL, `key_features` text DEFAULT NULL, `is_popular` tinyint(1) DEFAULT 0, `status` enum('active','inactive') DEFAULT 'active', `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `investments` -- CREATE TABLE `investments` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `plan_type` enum('fixed_deposit','mutual_fund','stocks','bonds','retirement') DEFAULT 'fixed_deposit', `investment_amount` decimal(15,2) NOT NULL, `current_value` decimal(15,2) DEFAULT NULL, `interest_rate` decimal(5,4) NOT NULL, `term_months` int(11) NOT NULL, `maturity_date` date NOT NULL, `status` enum('active','matured','withdrawn','cancelled') DEFAULT 'active', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `investment_products` -- CREATE TABLE `investment_products` ( `id` int(11) NOT NULL, `product_name` varchar(255) NOT NULL, `description` text DEFAULT NULL, `product_type` enum('stocks','bonds','mutual_funds','etfs','retirement') NOT NULL, `risk_level` enum('low','medium','high') NOT NULL, `min_investment` decimal(15,2) NOT NULL, `expected_return` decimal(5,4) NOT NULL, `management_fee` decimal(5,4) NOT NULL, `status` enum('active','inactive') DEFAULT 'active', `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `ip_location_cache` -- CREATE TABLE `ip_location_cache` ( `id` int(11) NOT NULL, `ip_address` varchar(45) NOT NULL, `location` varchar(255) NOT NULL, `source` varchar(20) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; -- -- Dumping data for table `ip_location_cache` -- -- -------------------------------------------------------- -- -- Table structure for table `kyc_documents` -- CREATE TABLE `kyc_documents` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `loan_id` int(11) NOT NULL, `document_type` enum('id_front','id_back','proof_of_income','proof_of_address','bank_statement') NOT NULL, `file_name` varchar(255) NOT NULL, `file_path` varchar(500) NOT NULL, `file_size` int(11) NOT NULL, `mime_type` varchar(100) NOT NULL, `status` enum('pending','verified','rejected') DEFAULT 'pending', `verified_by` int(11) DEFAULT NULL, `verified_at` timestamp NULL DEFAULT NULL, `rejection_reason` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `kyc_documents` -- -- -------------------------------------------------------- -- -- Table structure for table `limit_activity_log` -- CREATE TABLE `limit_activity_log` ( `id` int(11) NOT NULL, `admin_id` int(11) DEFAULT NULL, `account_id` int(11) DEFAULT NULL, `action_type` enum('limit_exceeded','limit_reset','manual_override') DEFAULT NULL, `details` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `live_chat_messages` -- CREATE TABLE `live_chat_messages` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `message` text NOT NULL, `is_admin` tinyint(1) DEFAULT 0, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `live_chat_sessions` -- CREATE TABLE `live_chat_sessions` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `admin_id` int(11) DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1, `created_at` timestamp NULL DEFAULT current_timestamp(), `last_activity` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `ended_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `loans` -- CREATE TABLE `loans` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `loan_type` enum('mortgage','auto','personal','business','student') NOT NULL, `loan_amount` decimal(15,2) NOT NULL, `currency` varchar(3) DEFAULT 'USD', `country` varchar(2) DEFAULT 'US', `interest_rate` decimal(5,4) NOT NULL, `term_months` int(11) NOT NULL, `purpose` text DEFAULT NULL, `employment_status` enum('employed','self-employed','unemployed','retired') DEFAULT NULL, `annual_income` decimal(15,2) DEFAULT NULL, `property_value` decimal(15,2) DEFAULT NULL, `down_payment` decimal(15,2) DEFAULT NULL, `vehicle_type` enum('new','used') DEFAULT NULL, `vehicle_value` decimal(15,2) DEFAULT NULL, `kyc_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`kyc_data`)), `kyc_files` longtext DEFAULT NULL, `kyc_status` enum('pending','completed','failed') DEFAULT 'pending', `kyc_submitted_at` timestamp NULL DEFAULT NULL, `status` enum('draft','pending','under_review','approved','rejected','active','paid','default') DEFAULT 'draft', `approved_by` int(11) DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejection_reason` text DEFAULT NULL, `rejected_by` int(11) DEFAULT NULL, `rejected_at` datetime DEFAULT NULL, `disbursed_at` timestamp NULL DEFAULT NULL, `monthly_payment` decimal(15,2) DEFAULT NULL, `remaining_balance` decimal(15,2) DEFAULT NULL, `remaining_term` int(11) DEFAULT NULL, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `agreement_signed` tinyint(4) DEFAULT 0, `agreement_signed_at` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `loans` -- -- -------------------------------------------------------- -- -- Table structure for table `loan_agreements` -- CREATE TABLE `loan_agreements` ( `id` int(11) NOT NULL, `loan_id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `filename` varchar(255) DEFAULT 'agreement.pdf', `verification_code` varchar(50) NOT NULL, `digital_signature` varchar(100) DEFAULT NULL, `generated_at` datetime NOT NULL, `signed_at` datetime DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `loan_kyc_requirements` -- CREATE TABLE `loan_kyc_requirements` ( `id` int(11) NOT NULL, `loan_type` varchar(50) NOT NULL, `document_type` varchar(50) NOT NULL, `is_required` tinyint(1) DEFAULT 1, `description` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `loan_kyc_requirements` -- INSERT INTO `loan_kyc_requirements` (`id`, `loan_type`, `document_type`, `is_required`, `description`, `created_at`) VALUES (1, 'personal', 'id_front', 1, 'Government-issued ID front', '2025-11-09 15:08:00'), (2, 'personal', 'id_back', 1, 'Government-issued ID back', '2025-11-09 15:08:00'), (3, 'personal', 'proof_of_income', 1, 'Recent pay stubs or employment letter', '2025-11-09 15:08:00'), (4, 'personal', 'proof_of_address', 1, 'Utility bill or bank statement', '2025-11-09 15:08:00'), (5, 'mortgage', 'id_front', 1, 'Government-issued ID front', '2025-11-09 15:08:00'), (6, 'mortgage', 'id_back', 1, 'Government-issued ID back', '2025-11-09 15:08:00'), (7, 'mortgage', 'proof_of_income', 1, 'Recent pay stubs or employment letter', '2025-11-09 15:08:00'), (8, 'mortgage', 'proof_of_address', 1, 'Utility bill or bank statement', '2025-11-09 15:08:00'), (9, 'mortgage', 'bank_statement', 1, '3 months of bank statements', '2025-11-09 15:08:00'), (10, 'mortgage', 'tax_return', 1, 'Previous year tax return', '2025-11-09 15:08:00'), (11, 'auto', 'id_front', 1, 'Government-issued ID front', '2025-11-09 15:08:00'), (12, 'auto', 'id_back', 1, 'Government-issued ID back', '2025-11-09 15:08:01'), (13, 'auto', 'proof_of_income', 1, 'Recent pay stubs or employment letter', '2025-11-09 15:08:01'), (14, 'business', 'id_front', 1, 'Government-issued ID front', '2025-11-09 15:08:01'), (15, 'business', 'id_back', 1, 'Government-issued ID back', '2025-11-09 15:08:01'), (16, 'business', 'proof_of_income', 1, 'Business income documentation', '2025-11-09 15:08:01'), (17, 'business', 'bank_statement', 1, '6 months of business bank statements', '2025-11-09 15:08:01'), (18, 'student', 'id_front', 1, 'Government-issued ID front', '2025-11-09 15:08:01'), (19, 'student', 'id_back', 1, 'Government-issued ID back', '2025-11-09 15:08:01'), (20, 'student', 'proof_of_income', 1, 'Income documentation (or parents)', '2025-11-09 15:08:01'), (21, 'student', 'proof_of_address', 1, 'Utility bill or bank statement', '2025-11-09 15:08:02'); -- -------------------------------------------------------- -- -- Table structure for table `loan_products` -- CREATE TABLE `loan_products` ( `id` int(11) NOT NULL, `loan_type` enum('mortgage','auto','personal','business') NOT NULL, `name` varchar(100) NOT NULL, `description` text DEFAULT NULL, `min_amount` decimal(15,2) NOT NULL, `max_amount` decimal(15,2) NOT NULL, `min_term` int(11) NOT NULL, `max_term` int(11) NOT NULL, `min_interest_rate` decimal(5,4) NOT NULL, `max_interest_rate` decimal(5,4) NOT NULL, `min_credit_score` int(11) NOT NULL, `requirements` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `features` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `terms_conditions` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `loan_products` -- INSERT INTO `loan_products` (`id`, `loan_type`, `name`, `description`, `min_amount`, `max_amount`, `min_term`, `max_term`, `min_interest_rate`, `max_interest_rate`, `min_credit_score`, `requirements`, `features`, `is_active`, `created_at`, `terms_conditions`) VALUES (1, 'mortgage', 'Standard Mortgage', 'Fixed-rate mortgage for primary residence', 50000.00, 1000000.00, 60, 360, 0.0350, 0.0550, 650, '{\"min_down_payment\": 5, \"max_debt_to_income\": 43, \"property_types\": [\"single_family\", \"condo\", \"townhouse\"]}', '{\"fixed_rate\": true, \"prepayment_penalty\": false, \"tax_deductible\": true}', 1, '2025-11-13 08:38:38', NULL), (2, 'auto', 'Auto Loan', 'Vehicle financing for new and used cars', 5000.00, 100000.00, 12, 84, 0.0390, 0.0890, 580, '{\"min_down_payment\": 10, \"max_vehicle_age\": 10, \"vehicle_types\": [\"car\", \"suv\", \"truck\", \"motorcycle\"]}', '{\"gap_insurance\": true, \"early_payoff\": true, \"online_management\": true}', 1, '2025-11-13 08:38:38', NULL), (3, 'personal', 'Personal Loan', 'Unsecured personal loan for various needs', 1000.00, 50000.00, 12, 60, 0.0590, 0.1590, 550, '{\"min_income\": 20000, \"max_debt_to_income\": 50}', '{\"no_collateral\": true, \"fixed_monthly_payments\": true, \"fast_funding\": true}', 1, '2025-11-13 08:38:38', NULL), (4, 'business', 'SME Business Loan', 'Financing for small and medium business growth', 10000.00, 500000.00, 12, 84, 0.0590, 0.1290, 620, '{\"business_age\": 2, \"annual_revenue\": 50000, \"business_plan\": true}', '{\"flexible_repayment\": true, \"business_advisory\": true, \"equipment_financing\": true}', 1, '2025-11-13 08:38:38', NULL), (6, 'mortgage', 'Mortgage Loan', 'Home purchase and refinancing', 50000.00, 2000000.00, 60, 360, 3.5000, 6.5000, 0, '{\"min_income\": 50000, \"max_debt_to_income\": 36, \"min_credit_score\": 620}', '[\"Fixed rates\", \"Low down payment\", \"Tax benefits\"]', 1, '2025-11-13 08:38:47', NULL); -- -------------------------------------------------------- -- -- Table structure for table `loan_repayments` -- CREATE TABLE `loan_repayments` ( `id` int(11) NOT NULL, `loan_id` int(11) NOT NULL, `installment_number` int(11) NOT NULL, `user_id` int(11) NOT NULL, `due_date` date NOT NULL, `due_amount` decimal(15,2) NOT NULL, `principal_amount` decimal(15,2) NOT NULL, `interest_amount` decimal(15,2) NOT NULL, `paid_amount` decimal(15,2) DEFAULT 0.00, `paid_at` datetime DEFAULT NULL, `status` enum('pending','paid','overdue','partial') DEFAULT 'pending', `late_fee` decimal(10,2) DEFAULT 0.00, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `login_attempts` -- CREATE TABLE `login_attempts` ( `id` int(11) NOT NULL, `username` varchar(50) NOT NULL, `ip_address` varchar(45) NOT NULL, `user_agent` text DEFAULT NULL, `attempt_time` timestamp NOT NULL DEFAULT current_timestamp(), `success` tinyint(1) DEFAULT 0, `failure_reason` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `login_verifications` -- CREATE TABLE `login_verifications` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `session_id` varchar(255) NOT NULL, `verification_token` varchar(64) NOT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `location` varchar(255) DEFAULT NULL, `status` enum('pending','approved','denied') DEFAULT 'pending', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `expires_at` timestamp NULL DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `denied_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `notifications` -- CREATE TABLE `notifications` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `title` varchar(255) NOT NULL, `message` text NOT NULL, `type` enum('info','success','warning','error') DEFAULT 'info', `is_read` tinyint(1) DEFAULT 0, `related_entity_type` enum('transaction','bill','account','security') DEFAULT 'transaction', `related_entity_id` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `notifications` -- -- -------------------------------------------------------- -- -- Table structure for table `password_reset_tokens` -- CREATE TABLE `password_reset_tokens` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `token` varchar(100) NOT NULL, `expires_at` timestamp NOT NULL DEFAULT current_timestamp(), `used` tinyint(1) DEFAULT 0, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `payee_management` -- CREATE TABLE `payee_management` ( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `payee_name` varchar(100) NOT NULL, `payee_account` varchar(50) NOT NULL, `bank_name` varchar(100) DEFAULT NULL, `routing_number` varchar(20) DEFAULT NULL, `bic_code` varchar(20) DEFAULT NULL, `sort_code` varchar(20) DEFAULT NULL, `payee_type` enum('individual','company') DEFAULT 'individual', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `region` varchar(50) DEFAULT 'Global', `is_global` tinyint(1) DEFAULT 0, `created_by` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `payee_management` -- -- -------------------------------------------------------- -- -- Table structure for table `regions` -- CREATE TABLE `regions` ( `id` int(11) NOT NULL, `country_code` varchar(2) NOT NULL, `code` varchar(10) NOT NULL, `name` varchar(100) NOT NULL, `type` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `regions` -- INSERT INTO `regions` (`id`, `country_code`, `code`, `name`, `type`) VALUES (1, 'US', 'AL', 'Alabama', 'state'), (2, 'US', 'AK', 'Alaska', 'state'), (3, 'US', 'AZ', 'Arizona', 'state'), (4, 'US', 'AR', 'Arkansas', 'state'), (5, 'US', 'CA', 'California', 'state'), (6, 'US', 'CO', 'Colorado', 'state'), (7, 'US', 'CT', 'Connecticut', 'state'), (8, 'US', 'DE', 'Delaware', 'state'), (9, 'US', 'FL', 'Florida', 'state'), (10, 'US', 'GA', 'Georgia', 'state'), (11, 'US', 'HI', 'Hawaii', 'state'), (12, 'US', 'ID', 'Idaho', 'state'), (13, 'US', 'IL', 'Illinois', 'state'), (14, 'US', 'IN', 'Indiana', 'state'), (15, 'US', 'IA', 'Iowa', 'state'), (16, 'US', 'KS', 'Kansas', 'state'), (17, 'US', 'KY', 'Kentucky', 'state'), (18, 'US', 'LA', 'Louisiana', 'state'), (19, 'US', 'ME', 'Maine', 'state'), (20, 'US', 'MD', 'Maryland', 'state'), (21, 'US', 'MA', 'Massachusetts', 'state'), (22, 'US', 'MI', 'Michigan', 'state'), (23, 'US', 'MN', 'Minnesota', 'state'), (24, 'US', 'MS', 'Mississippi', 'state'), (25, 'US', 'MO', 'Missouri', 'state'), (26, 'US', 'MT', 'Montana', 'state'), (27, 'US', 'NE', 'Nebraska', 'state'), (28, 'US', 'NV', 'Nevada', 'state'), (29, 'US', 'NH', 'New Hampshire', 'state'), (30, 'US', 'NJ', 'New Jersey', 'state'), (31, 'US', 'NM', 'New Mexico', 'state'), (32, 'US', 'NY', 'New York', 'state'), (33, 'US', 'NC', 'North Carolina', 'state'), (34, 'US', 'ND', 'North Dakota', 'state'), (35, 'US', 'OH', 'Ohio', 'state'), (36, 'US', 'OK', 'Oklahoma', 'state'), (37, 'US', 'OR', 'Oregon', 'state'), (38, 'US', 'PA', 'Pennsylvania', 'state'), (39, 'US', 'RI', 'Rhode Island', 'state'), (40, 'US', 'SC', 'South Carolina', 'state'), (41, 'US', 'SD', 'South Dakota', 'state'), (42, 'US', 'TN', 'Tennessee', 'state'), (43, 'US', 'TX', 'Texas', 'state'), (44, 'US', 'UT', 'Utah', 'state'), (45, 'US', 'VT', 'Vermont', 'state'), (46, 'US', 'VA', 'Virginia', 'state'), (47, 'US', 'WA', 'Washington', 'state'), (48, 'US', 'WV', 'West Virginia', 'state'), (49, 'US', 'WI', 'Wisconsin', 'state'), (50, 'US', 'WY', 'Wyoming', 'state'), (51, 'GB', 'ENG', 'England', 'country'), (52, 'GB', 'SCT', 'Scotland', 'country'), (53, 'GB', 'WLS', 'Wales', 'country'), (54, 'GB', 'NIR', 'Northern Ireland', 'country'), (55, 'AU', 'NSW', 'New South Wales', 'state'), (56, 'AU', 'QLD', 'Queensland', 'state'), (57, 'AU', 'SA', 'South Australia', 'state'), (58, 'AU', 'TAS', 'Tasmania', 'state'), (59, 'AU', 'VIC', 'Victoria', 'state'), (60, 'AU', 'WA', 'Western Australia', 'state'), (61, 'AU', 'ACT', 'Australian Capital Territory', 'territory'), (62, 'AU', 'NT', 'Northern Territory', 'territory'), (63, 'CA', 'AB', 'Alberta', 'province'), (64, 'CA', 'BC', 'British Columbia', 'province'), (65, 'CA', 'MB', 'Manitoba', 'province'), (66, 'CA', 'NB', 'New Brunswick', 'province'), (67, 'CA', 'NL', 'Newfoundland and Labrador', 'province'), (68, 'CA', 'NS', 'Nova Scotia', 'province'), (69, 'CA', 'ON', 'Ontario', 'province'), (70, 'CA', 'PE', 'Prince Edward Island', 'province'), (71, 'CA', 'QC', 'Quebec', 'province'), (72, 'CA', 'SK', 'Saskatchewan', 'province'), (73, 'CA', 'NT', 'Northwest Territories', 'territory'), (74, 'CA', 'NU', 'Nunavut', 'territory'), (75, 'CA', 'YT', 'Yukon', 'territory'), (76, 'CN', 'BJ', 'Beijing', 'municipality'), (77, 'CN', 'SH', 'Shanghai', 'municipality'), (78, 'CN', 'TJ', 'Tianjin', 'municipality'), (79, 'CN', 'CQ', 'Chongqing', 'municipality'), (80, 'CN', 'GD', 'Guangdong', 'province'), (81, 'CN', 'JS', 'Jiangsu', 'province'), (82, 'FR', 'IDF', 'Île-de-France', 'region'), (83, 'FR', 'ARA', 'Auvergne-Rhône-Alpes', 'region'), (84, 'FR', 'BFC', 'Bourgogne-Franche-Comté', 'region'), (85, 'FR', 'BRE', 'Brittany', 'region'), (86, 'FR', 'CVL', 'Centre-Val de Loire', 'region'), (87, 'FR', 'COR', 'Corsica', 'region'), (88, 'DE', 'BY', 'Bavaria', 'state'), (89, 'DE', 'BW', 'Baden-Württemberg', 'state'), (90, 'DE', 'BE', 'Berlin', 'state'), (91, 'DE', 'HB', 'Bremen', 'state'), (92, 'DE', 'HH', 'Hamburg', 'state'), (93, 'DE', 'HE', 'Hesse', 'state'), (94, 'ES', 'AN', 'Andalusia', 'autonomous community'), (95, 'ES', 'CAT', 'Catalonia', 'autonomous community'), (96, 'ES', 'MAD', 'Madrid', 'autonomous community'), (97, 'ES', 'VAL', 'Valencia', 'autonomous community'), (98, 'ES', 'GAL', 'Galicia', 'autonomous community'), (99, 'ES', 'PV', 'Basque Country', 'autonomous community'), (100, 'JP', 'TK', 'Tokyo', 'prefecture'), (101, 'JP', 'OS', 'Osaka', 'prefecture'), (102, 'JP', 'KY', 'Kyoto', 'prefecture'), (103, 'JP', 'HK', 'Hokkaido', 'prefecture'), (104, 'JP', 'FK', 'Fukuoka', 'prefecture'), (105, 'JP', 'NG', 'Nagoya', 'prefecture'); -- -------------------------------------------------------- -- -- Table structure for table `security_logs` -- CREATE TABLE `security_logs` ( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `event_type` varchar(50) NOT NULL, `description` text NOT NULL, `ip_address` varchar(45) DEFAULT NULL, `location` varchar(255) DEFAULT NULL, `user_agent` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `security_logs` -- -- -------------------------------------------------------- -- -- Table structure for table `security_questions` -- CREATE TABLE `security_questions` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `question_1` varchar(255) NOT NULL, `answer_1` varchar(255) NOT NULL, `question_2` varchar(255) NOT NULL, `answer_2` varchar(255) NOT NULL, `question_3` varchar(255) NOT NULL, `answer_3` varchar(255) NOT NULL, `failed_attempts` int(11) DEFAULT 0, `is_locked` tinyint(4) DEFAULT 0, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `security_questions` -- -- -------------------------------------------------------- -- -- Table structure for table `support_tickets` -- CREATE TABLE `support_tickets` ( `id` int(11) NOT NULL, `ticket_number` varchar(50) NOT NULL, `user_id` int(11) NOT NULL, `assigned_to` int(11) DEFAULT NULL, `subject` varchar(255) NOT NULL, `category` varchar(50) NOT NULL, `priority` varchar(20) DEFAULT 'medium', `message` text NOT NULL, `admin_notes` text DEFAULT NULL, `status` varchar(20) DEFAULT 'open', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `system_settings` -- CREATE TABLE `system_settings` ( `id` int(11) NOT NULL, `setting_name` varchar(100) NOT NULL, `setting_value` text NOT NULL, `description` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `system_settings` -- INSERT INTO `system_settings` (`id`, `setting_name`, `setting_value`, `description`, `created_at`, `updated_at`) VALUES (1, 'base_currency', 'USD', 'Default system currency for all calculations', '2025-11-05 02:55:37', '2025-11-05 02:55:37'), (2, 'system_name', 'PlexiTrust Consolidated', 'Name of the banking system', '2025-11-05 02:55:37', '2025-11-05 02:55:37'), (3, 'maintenance_mode', '0', 'Whether system is in maintenance mode (1) or not (0)', '2025-11-05 02:55:37', '2025-11-05 02:55:37'), (4, 'max_accounts_per_user', '3', 'Maximum number of accounts a user can have', '2025-11-05 02:55:37', '2025-11-05 02:55:37'), (0, 'transfer_limit_control', '1', 'Transfer Limit Control System - 0=Disabled, 1=Enabled', '2025-12-16 18:46:58', '2026-03-03 01:56:17'); -- -------------------------------------------------------- -- -- Table structure for table `ticket_attachments` -- CREATE TABLE `ticket_attachments` ( `id` int(11) NOT NULL, `ticket_id` int(11) NOT NULL, `reply_id` int(11) DEFAULT NULL, `file_name` varchar(255) NOT NULL, `file_path` varchar(500) NOT NULL, `file_size` int(11) NOT NULL, `uploaded_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `ticket_replies` -- CREATE TABLE `ticket_replies` ( `id` int(11) NOT NULL, `ticket_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `message` text NOT NULL, `is_admin` tinyint(1) DEFAULT 0, `created_at` timestamp NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `transactions` -- CREATE TABLE `transactions` ( `id` int(11) NOT NULL, `from_account_id` int(11) DEFAULT NULL, `to_account_id` int(11) DEFAULT NULL, `to_external_account_id` int(11) DEFAULT NULL, `from_user_id` int(11) DEFAULT NULL, `to_user_id` int(11) DEFAULT NULL, `amount` decimal(15,2) NOT NULL, `currency` varchar(3) DEFAULT 'USD', `transaction_type` enum('transfer','deposit','withdrawal','payment','fee','loan_disbursement') DEFAULT NULL, `description` text DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `status` enum('pending','completed','failed','cancelled','processing') DEFAULT 'pending', `approved_by` int(11) DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `is_external` tinyint(1) DEFAULT 0, `reference_number` varchar(50) DEFAULT NULL, `transaction_date` timestamp NOT NULL DEFAULT current_timestamp(), `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL, `created_by` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `transactions` -- -- -------------------------------------------------------- -- -- Table structure for table `transaction_categories` -- CREATE TABLE `transaction_categories` ( `id` int(11) NOT NULL, `category_name` varchar(100) DEFAULT NULL, `description` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `transaction_categories` -- INSERT INTO `transaction_categories` (`id`, `category_name`, `description`) VALUES (1, 'Shopping', 'Retail purchases and shopping'), (4, 'Bills & Utilities', 'Monthly bills and utilities'), (7, 'Transfer', 'Account transfers'), (8, 'Deposit', 'Account deposits'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(11) NOT NULL, `username` varchar(50) NOT NULL, `password` varchar(255) NOT NULL, `email` varchar(100) NOT NULL, `phone` varchar(20) NOT NULL, `profile_picture` varchar(255) DEFAULT NULL, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `date_of_birth` date NOT NULL, `government_id_type` enum('ssn','passport','national_id','nin','tfn','drivers_license','other') NOT NULL DEFAULT 'national_id', `government_id_display` varchar(100) DEFAULT NULL, `tax_id` varchar(100) DEFAULT NULL, `address` text NOT NULL, `city` varchar(100) NOT NULL, `state` varchar(50) DEFAULT NULL, `region` varchar(100) DEFAULT NULL, `zip_code` varchar(20) DEFAULT NULL, `postal_code` varchar(20) DEFAULT NULL, `country` varchar(2) NOT NULL DEFAULT 'US', `currency` varchar(3) DEFAULT 'USD', `employment_status` enum('employed','self_employed','unemployed','retired','student') DEFAULT NULL, `employer_name` varchar(100) DEFAULT NULL, `occupation` varchar(100) DEFAULT NULL, `annual_income` enum('0-25000','25001-50000','50001-75000','75001-100000','100001+') DEFAULT NULL, `source_of_funds` enum('employment','investments','inheritance','savings','other') DEFAULT NULL, `user_type` enum('admin','user') DEFAULT 'user', `terms_accepted` tinyint(1) DEFAULT 0, `communications` tinyint(1) DEFAULT 0, `registration_ip` varchar(45) DEFAULT NULL, `registration_user_agent` text DEFAULT NULL, `status` enum('active','inactive','suspended','pending') DEFAULT 'pending', `session_valid` tinyint(1) DEFAULT 1, `account_status_message` text DEFAULT NULL, `tier` enum('basic','premium','business') DEFAULT 'basic', `login_attempts` int(11) DEFAULT 0, `last_login_attempt` timestamp NULL DEFAULT NULL, `last_login` timestamp NULL DEFAULT NULL, `last_password_change` timestamp NOT NULL DEFAULT current_timestamp(), `account_locked_until` timestamp NULL DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `two_factor_enabled` tinyint(1) DEFAULT 0, `security_questions_setup` tinyint(1) DEFAULT 0, `approved_by` int(11) DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejected_by` int(11) DEFAULT NULL, `rejected_at` timestamp NULL DEFAULT NULL, `rejection_reason` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `users` -- -- -------------------------------------------------------- -- -- Table structure for table `user_accounts_summary` -- CREATE TABLE `user_accounts_summary` ( `user_id` int(11) DEFAULT NULL, `username` varchar(50) DEFAULT NULL, `first_name` varchar(50) DEFAULT NULL, `last_name` varchar(50) DEFAULT NULL, `user_type` enum('admin','user') DEFAULT NULL, `total_accounts` bigint(20) DEFAULT NULL, `total_balance` decimal(37,2) DEFAULT NULL, `last_activity` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_otps` -- CREATE TABLE `user_otps` ( `id` int(11) NOT NULL, `email` varchar(255) NOT NULL, `otp_code` varchar(10) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `expires_at` timestamp NOT NULL DEFAULT (current_timestamp() + interval 10 minute), `used` tinyint(4) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `user_otps` -- -- -------------------------------------------------------- -- -- Table structure for table `user_payee_assignments` -- CREATE TABLE `user_payee_assignments` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `payee_id` int(11) NOT NULL, `assigned_by` int(11) NOT NULL, `assigned_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_preferences` -- CREATE TABLE `user_preferences` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `preference_key` varchar(100) NOT NULL, `preference_value` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `user_preferences` -- -- -------------------------------------------------------- -- -- Table structure for table `user_profiles` -- CREATE TABLE `user_profiles` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `government_id_display` varchar(100) DEFAULT NULL, `preferred_language` varchar(10) DEFAULT 'en', `timezone` varchar(50) DEFAULT 'UTC', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `user_profiles` -- -- -------------------------------------------------------- -- -- Table structure for table `user_sessions` -- CREATE TABLE `user_sessions` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `session_id` varchar(128) NOT NULL, `ip_address` varchar(45) NOT NULL, `user_agent` text DEFAULT NULL, `login_time` timestamp NOT NULL DEFAULT current_timestamp(), `last_activity` timestamp NOT NULL DEFAULT current_timestamp(), `expires_at` timestamp NULL DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_settings` -- CREATE TABLE `user_settings` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `login_alerts` tinyint(1) DEFAULT 1, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `user_verification_status` -- CREATE TABLE `user_verification_status` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `email_verified` tinyint(1) DEFAULT 0, `phone_verified` tinyint(1) DEFAULT 0, `identity_verified` tinyint(1) DEFAULT 0, `address_verified` tinyint(1) DEFAULT 0, `overall_status` enum('unverified','pending','verified','rejected') DEFAULT 'unverified', `verification_level` enum('basic','intermediate','advanced') DEFAULT 'basic', `last_verified_at` datetime DEFAULT NULL, `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; -- -- Indexes for dumped tables -- -- -- Indexes for table `2fa_settings` -- ALTER TABLE `2fa_settings` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `accounts` -- ALTER TABLE `accounts` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `account_number` (`account_number`), ADD KEY `user_id` (`user_id`), ADD KEY `approved_by` (`approved_by`); -- -- Indexes for table `account_alerts` -- ALTER TABLE `account_alerts` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `account_closure_requests` -- ALTER TABLE `account_closure_requests` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `user_id_active` (`user_id`,`status`), ADD KEY `processed_by` (`processed_by`); -- -- Indexes for table `account_limits` -- ALTER TABLE `account_limits` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_account` (`account_id`); -- -- Indexes for table `admin_activities` -- ALTER TABLE `admin_activities` ADD PRIMARY KEY (`id`), ADD KEY `idx_admin_activities_admin_id` (`admin_id`), ADD KEY `idx_admin_activities_created_at` (`created_at`); -- -- Indexes for table `admin_activity_log` -- ALTER TABLE `admin_activity_log` ADD PRIMARY KEY (`id`), ADD KEY `admin_id` (`admin_id`); -- -- Indexes for table `api_rate_limits` -- ALTER TABLE `api_rate_limits` ADD PRIMARY KEY (`id`), ADD KEY `idx_api_identifier` (`api_name`,`identifier`), ADD KEY `idx_first_request` (`first_request`); -- -- Indexes for table `bank_identifiers` -- ALTER TABLE `bank_identifiers` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `country_code` (`country_code`,`bank_code`); -- -- Indexes for table `bills` -- ALTER TABLE `bills` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `bill_payments` -- ALTER TABLE `bill_payments` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `account_id` (`account_id`), ADD KEY `bill_id` (`bill_id`); -- -- Indexes for table `chat_attachments` -- ALTER TABLE `chat_attachments` ADD PRIMARY KEY (`id`), ADD KEY `uploaded_by` (`uploaded_by`), ADD KEY `idx_user_id` (`user_id`); -- -- Indexes for table `check_deposits` -- ALTER TABLE `check_deposits` ADD PRIMARY KEY (`id`), ADD KEY `transaction_id` (`transaction_id`), ADD KEY `user_id` (`user_id`), ADD KEY `account_id` (`account_id`), ADD KEY `processed_by` (`processed_by`); -- -- Indexes for table `countries` -- ALTER TABLE `countries` ADD PRIMARY KEY (`code`); -- -- Indexes for table `country_banking_formats` -- ALTER TABLE `country_banking_formats` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `country_code` (`country_code`); -- -- Indexes for table `credit_cards` -- ALTER TABLE `credit_cards` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `card_number` (`card_number`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `external_accounts` -- ALTER TABLE `external_accounts` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `external_transfers` -- ALTER TABLE `external_transfers` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `from_account_id` (`from_account_id`), ADD KEY `approved_by` (`approved_by`); -- -- Indexes for table `investments` -- ALTER TABLE `investments` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `ip_location_cache` -- ALTER TABLE `ip_location_cache` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_ip` (`ip_address`), ADD KEY `idx_created` (`created_at`); -- -- Indexes for table `kyc_documents` -- ALTER TABLE `kyc_documents` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `loan_id` (`loan_id`), ADD KEY `verified_by` (`verified_by`); -- -- Indexes for table `limit_activity_log` -- ALTER TABLE `limit_activity_log` ADD PRIMARY KEY (`id`), ADD KEY `admin_id` (`admin_id`), ADD KEY `account_id` (`account_id`); -- -- Indexes for table `live_chat_messages` -- ALTER TABLE `live_chat_messages` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `live_chat_sessions` -- ALTER TABLE `live_chat_sessions` ADD PRIMARY KEY (`id`), ADD KEY `admin_id` (`admin_id`), ADD KEY `idx_user_id` (`user_id`), ADD KEY `idx_is_active` (`is_active`), ADD KEY `idx_last_activity` (`last_activity`); -- -- Indexes for table `loans` -- ALTER TABLE `loans` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `approved_by` (`approved_by`), ADD KEY `rejected_by` (`rejected_by`); -- -- Indexes for table `loan_agreements` -- ALTER TABLE `loan_agreements` ADD PRIMARY KEY (`id`), ADD KEY `loan_id` (`loan_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `loan_products` -- ALTER TABLE `loan_products` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_loan_type_name` (`loan_type`,`name`); -- -- Indexes for table `loan_repayments` -- ALTER TABLE `loan_repayments` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `idx_loan_id` (`loan_id`), ADD KEY `idx_due_date` (`due_date`), ADD KEY `idx_status` (`status`), ADD KEY `idx_installment` (`loan_id`,`installment_number`); -- -- Indexes for table `login_attempts` -- ALTER TABLE `login_attempts` ADD PRIMARY KEY (`id`), ADD KEY `idx_username` (`username`), ADD KEY `idx_ip_address` (`ip_address`), ADD KEY `idx_attempt_time` (`attempt_time`); -- -- Indexes for table `login_verifications` -- ALTER TABLE `login_verifications` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `notifications` -- ALTER TABLE `notifications` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `password_reset_tokens` -- ALTER TABLE `password_reset_tokens` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `token` (`token`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `payee_management` -- ALTER TABLE `payee_management` ADD PRIMARY KEY (`id`), ADD KEY `idx_payee_id` (`id`); -- -- Indexes for table `regions` -- ALTER TABLE `regions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_region` (`country_code`,`code`); -- -- Indexes for table `security_logs` -- ALTER TABLE `security_logs` ADD PRIMARY KEY (`id`), ADD KEY `idx_user_id` (`user_id`), ADD KEY `idx_event_type` (`event_type`), ADD KEY `idx_created_at` (`created_at`); -- -- Indexes for table `security_questions` -- ALTER TABLE `security_questions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_user` (`user_id`); -- -- Indexes for table `support_tickets` -- ALTER TABLE `support_tickets` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `ticket_number` (`ticket_number`), ADD KEY `user_id` (`user_id`), ADD KEY `idx_support_tickets_status` (`status`), ADD KEY `idx_support_tickets_priority` (`priority`), ADD KEY `idx_support_tickets_created_at` (`created_at`), ADD KEY `idx_support_tickets_assigned_to` (`assigned_to`); -- -- Indexes for table `ticket_attachments` -- ALTER TABLE `ticket_attachments` ADD PRIMARY KEY (`id`), ADD KEY `reply_id` (`reply_id`), ADD KEY `uploaded_by` (`uploaded_by`), ADD KEY `idx_ticket_id` (`ticket_id`); -- -- Indexes for table `ticket_replies` -- ALTER TABLE `ticket_replies` ADD PRIMARY KEY (`id`), ADD KEY `ticket_id` (`ticket_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `transactions` -- ALTER TABLE `transactions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `reference_number` (`reference_number`), ADD KEY `from_account_id` (`from_account_id`), ADD KEY `to_account_id` (`to_account_id`), ADD KEY `from_user_id` (`from_user_id`), ADD KEY `to_user_id` (`to_user_id`), ADD KEY `approved_by` (`approved_by`), ADD KEY `created_by` (`created_by`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`), ADD UNIQUE KEY `email` (`email`), ADD KEY `idx_username` (`username`), ADD KEY `idx_status` (`status`), ADD KEY `idx_user_type` (`user_type`), ADD KEY `approved_by` (`approved_by`); -- -- Indexes for table `user_otps` -- ALTER TABLE `user_otps` ADD PRIMARY KEY (`id`); -- -- Indexes for table `user_payee_assignments` -- ALTER TABLE `user_payee_assignments` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_user_payee` (`user_id`,`payee_id`), ADD KEY `user_id` (`user_id`), ADD KEY `payee_id` (`payee_id`), ADD KEY `assigned_by` (`assigned_by`); -- -- Indexes for table `user_preferences` -- ALTER TABLE `user_preferences` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_user_preference` (`user_id`,`preference_key`); -- -- Indexes for table `user_profiles` -- ALTER TABLE `user_profiles` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `user_id` (`user_id`); -- -- Indexes for table `user_sessions` -- ALTER TABLE `user_sessions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `session_id` (`session_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `user_settings` -- ALTER TABLE `user_settings` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `user_verification_status` -- ALTER TABLE `user_verification_status` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_user` (`user_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `2fa_settings` -- ALTER TABLE `2fa_settings` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `accounts` -- ALTER TABLE `accounts` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=65; -- -- AUTO_INCREMENT for table `account_alerts` -- ALTER TABLE `account_alerts` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `account_closure_requests` -- ALTER TABLE `account_closure_requests` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `account_limits` -- ALTER TABLE `account_limits` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32; -- -- AUTO_INCREMENT for table `admin_activities` -- ALTER TABLE `admin_activities` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=905; -- -- AUTO_INCREMENT for table `admin_activity_log` -- ALTER TABLE `admin_activity_log` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `api_rate_limits` -- ALTER TABLE `api_rate_limits` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=439; -- -- AUTO_INCREMENT for table `bank_identifiers` -- ALTER TABLE `bank_identifiers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15; -- -- AUTO_INCREMENT for table `bills` -- ALTER TABLE `bills` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9; -- -- AUTO_INCREMENT for table `bill_payments` -- ALTER TABLE `bill_payments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `chat_attachments` -- ALTER TABLE `chat_attachments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `check_deposits` -- ALTER TABLE `check_deposits` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; -- -- AUTO_INCREMENT for table `country_banking_formats` -- ALTER TABLE `country_banking_formats` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=50; -- -- AUTO_INCREMENT for table `credit_cards` -- ALTER TABLE `credit_cards` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `external_accounts` -- ALTER TABLE `external_accounts` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40; -- -- AUTO_INCREMENT for table `external_transfers` -- ALTER TABLE `external_transfers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `investments` -- ALTER TABLE `investments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `ip_location_cache` -- ALTER TABLE `ip_location_cache` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10; -- -- AUTO_INCREMENT for table `kyc_documents` -- ALTER TABLE `kyc_documents` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44; -- -- AUTO_INCREMENT for table `limit_activity_log` -- ALTER TABLE `limit_activity_log` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `live_chat_messages` -- ALTER TABLE `live_chat_messages` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9; -- -- AUTO_INCREMENT for table `live_chat_sessions` -- ALTER TABLE `live_chat_sessions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `loans` -- ALTER TABLE `loans` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11; -- -- AUTO_INCREMENT for table `loan_agreements` -- ALTER TABLE `loan_agreements` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12; -- -- AUTO_INCREMENT for table `loan_products` -- ALTER TABLE `loan_products` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1544; -- -- AUTO_INCREMENT for table `loan_repayments` -- ALTER TABLE `loan_repayments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=121; -- -- AUTO_INCREMENT for table `login_attempts` -- ALTER TABLE `login_attempts` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `login_verifications` -- ALTER TABLE `login_verifications` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38; -- -- AUTO_INCREMENT for table `notifications` -- ALTER TABLE `notifications` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=301; -- -- AUTO_INCREMENT for table `password_reset_tokens` -- ALTER TABLE `password_reset_tokens` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `payee_management` -- ALTER TABLE `payee_management` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10; -- -- AUTO_INCREMENT for table `regions` -- ALTER TABLE `regions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=153; -- -- AUTO_INCREMENT for table `security_logs` -- ALTER TABLE `security_logs` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2806; -- -- AUTO_INCREMENT for table `security_questions` -- ALTER TABLE `security_questions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `support_tickets` -- ALTER TABLE `support_tickets` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `ticket_attachments` -- ALTER TABLE `ticket_attachments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `ticket_replies` -- ALTER TABLE `ticket_replies` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `transactions` -- ALTER TABLE `transactions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=301; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36; -- -- AUTO_INCREMENT for table `user_otps` -- ALTER TABLE `user_otps` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=589; -- -- AUTO_INCREMENT for table `user_payee_assignments` -- ALTER TABLE `user_payee_assignments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `user_preferences` -- ALTER TABLE `user_preferences` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40; -- -- AUTO_INCREMENT for table `user_profiles` -- ALTER TABLE `user_profiles` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `user_sessions` -- ALTER TABLE `user_sessions` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `user_settings` -- ALTER TABLE `user_settings` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `user_verification_status` -- ALTER TABLE `user_verification_status` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `2fa_settings` -- ALTER TABLE `2fa_settings` ADD CONSTRAINT `2fa_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `accounts` -- ALTER TABLE `accounts` ADD CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `accounts_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `account_alerts` -- ALTER TABLE `account_alerts` ADD CONSTRAINT `account_alerts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); -- -- Constraints for table `account_closure_requests` -- ALTER TABLE `account_closure_requests` ADD CONSTRAINT `account_closure_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_closure_requests_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `account_limits` -- ALTER TABLE `account_limits` ADD CONSTRAINT `account_limits_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_10` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_11` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_12` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_13` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_14` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_15` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_16` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_17` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_18` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_19` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_20` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_21` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_22` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_23` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_24` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_3` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_4` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_5` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_6` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_7` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_8` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `account_limits_ibfk_9` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE; -- -- Constraints for table `admin_activities` -- ALTER TABLE `admin_activities` ADD CONSTRAINT `admin_activities_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `admin_activity_log` -- ALTER TABLE `admin_activity_log` ADD CONSTRAINT `admin_activity_log_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `bills` -- ALTER TABLE `bills` ADD CONSTRAINT `bills_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `bill_payments` -- ALTER TABLE `bill_payments` ADD CONSTRAINT `bill_payments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `bill_payments_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `bill_payments_ibfk_3` FOREIGN KEY (`bill_id`) REFERENCES `bills` (`id`) ON DELETE SET NULL; -- -- Constraints for table `chat_attachments` -- ALTER TABLE `chat_attachments` ADD CONSTRAINT `chat_attachments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `chat_attachments_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `check_deposits` -- ALTER TABLE `check_deposits` ADD CONSTRAINT `check_deposits_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `check_deposits_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `check_deposits_ibfk_3` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `check_deposits_ibfk_4` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`); -- -- Constraints for table `credit_cards` -- ALTER TABLE `credit_cards` ADD CONSTRAINT `credit_cards_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `external_accounts` -- ALTER TABLE `external_accounts` ADD CONSTRAINT `external_accounts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `external_transfers` -- ALTER TABLE `external_transfers` ADD CONSTRAINT `external_transfers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `external_transfers_ibfk_2` FOREIGN KEY (`from_account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `external_transfers_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `investments` -- ALTER TABLE `investments` ADD CONSTRAINT `investments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `kyc_documents` -- ALTER TABLE `kyc_documents` ADD CONSTRAINT `kyc_documents_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), ADD CONSTRAINT `kyc_documents_ibfk_2` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`), ADD CONSTRAINT `kyc_documents_ibfk_3` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`); -- -- Constraints for table `limit_activity_log` -- ALTER TABLE `limit_activity_log` ADD CONSTRAINT `limit_activity_log_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`), ADD CONSTRAINT `limit_activity_log_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`); -- -- Constraints for table `live_chat_messages` -- ALTER TABLE `live_chat_messages` ADD CONSTRAINT `live_chat_messages_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); -- -- Constraints for table `live_chat_sessions` -- ALTER TABLE `live_chat_sessions` ADD CONSTRAINT `live_chat_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `live_chat_sessions_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `loans` -- ALTER TABLE `loans` ADD CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), ADD CONSTRAINT `loans_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `loans_ibfk_3` FOREIGN KEY (`rejected_by`) REFERENCES `users` (`id`); -- -- Constraints for table `loan_agreements` -- ALTER TABLE `loan_agreements` ADD CONSTRAINT `loan_agreements_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `loan_agreements_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `loan_repayments` -- ALTER TABLE `loan_repayments` ADD CONSTRAINT `loan_repayments_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `loan_repayments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `login_verifications` -- ALTER TABLE `login_verifications` ADD CONSTRAINT `login_verifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `notifications` -- ALTER TABLE `notifications` ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `password_reset_tokens` -- ALTER TABLE `password_reset_tokens` ADD CONSTRAINT `password_reset_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `regions` -- ALTER TABLE `regions` ADD CONSTRAINT `regions_ibfk_1` FOREIGN KEY (`country_code`) REFERENCES `countries` (`code`); -- -- Constraints for table `security_logs` -- ALTER TABLE `security_logs` ADD CONSTRAINT `security_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `security_questions` -- ALTER TABLE `security_questions` ADD CONSTRAINT `security_questions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `support_tickets` -- ALTER TABLE `support_tickets` ADD CONSTRAINT `support_tickets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), ADD CONSTRAINT `support_tickets_ibfk_2` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `ticket_attachments` -- ALTER TABLE `ticket_attachments` ADD CONSTRAINT `ticket_attachments_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `ticket_attachments_ibfk_2` FOREIGN KEY (`reply_id`) REFERENCES `ticket_replies` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `ticket_attachments_ibfk_3` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `ticket_replies` -- ALTER TABLE `ticket_replies` ADD CONSTRAINT `ticket_replies_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `support_tickets` (`id`), ADD CONSTRAINT `ticket_replies_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); -- -- Constraints for table `transactions` -- ALTER TABLE `transactions` ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`from_account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`to_account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `transactions_ibfk_3` FOREIGN KEY (`from_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `transactions_ibfk_4` FOREIGN KEY (`to_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `transactions_ibfk_5` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `transactions_ibfk_6` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `users` -- ALTER TABLE `users` ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `user_payee_assignments` -- ALTER TABLE `user_payee_assignments` ADD CONSTRAINT `user_payee_assignments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `user_payee_assignments_ibfk_2` FOREIGN KEY (`payee_id`) REFERENCES `payee_management` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `user_payee_assignments_ibfk_3` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_preferences` -- ALTER TABLE `user_preferences` ADD CONSTRAINT `user_preferences_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_profiles` -- ALTER TABLE `user_profiles` ADD CONSTRAINT `user_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_sessions` -- ALTER TABLE `user_sessions` ADD CONSTRAINT `user_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_settings` -- ALTER TABLE `user_settings` ADD CONSTRAINT `user_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_verification_status` -- ALTER TABLE `user_verification_status` ADD CONSTRAINT `user_verification_status_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -------------------------------------------------------- -- Fresh administrator account (clean install) -- username: admin | password: ChangeMe!2026#PTC (CHANGE THIS IMMEDIATELY AFTER FIRST LOGIN) -- -------------------------------------------------------- INSERT INTO `users` (`id`, `username`, `password`, `email`, `phone`, `profile_picture`, `first_name`, `last_name`, `date_of_birth`, `government_id_type`, `government_id_display`, `tax_id`, `address`, `city`, `state`, `region`, `zip_code`, `postal_code`, `country`, `currency`, `employment_status`, `employer_name`, `occupation`, `annual_income`, `source_of_funds`, `user_type`, `terms_accepted`, `communications`, `registration_ip`, `registration_user_agent`, `status`, `session_valid`, `account_status_message`, `tier`, `login_attempts`, `last_login_attempt`, `last_login`, `last_password_change`, `account_locked_until`, `created_at`, `updated_at`, `two_factor_enabled`, `security_questions_setup`, `approved_by`, `approved_at`, `rejected_by`, `rejected_at`, `rejection_reason`) VALUES (1, 'admin', '$2y$10$x/OYLM3vGhEputlwiBruAeoPtqGlAsmrwz/SPwTga5zCv.Ar81ucq', 'plexitrustconsolidated@gmail.com', '+44 79 5199 0425', NULL, 'System', 'Administrator', '1990-01-01', 'national_id', 'XXX-XX-0000', NULL, 'Head Office', 'London', 'London', NULL, 'EC3N 2EX', NULL, 'GB', 'GBP', 'employed', 'PlexiTrust Consolidated', 'System Administrator', '100001+', 'employment', 'admin', 1, 0, '127.0.0.1', 'System', 'active', 1, NULL, 'premium', 0, NULL, NULL, NOW(), NULL, NOW(), NOW(), 0, 0, NULL, NULL, NULL, NULL, NULL); COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;