-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- 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 `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;

-- --------------------------------------------------------

--
-- 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 `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;

-- --------------------------------------------------------

--
-- 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');

--
-- 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;

--
-- 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;

-- --------------------------------------------------------

--
-- 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 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 `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_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 `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  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 `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_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 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 `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_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 `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=301;

--
-- 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 `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_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 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 `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_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 `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_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 `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_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;

COMMIT;

