--
-- Host: 127.0.0.1    Database: tenant_gamma_db
-- ------------------------------------------------------


--
-- Table structure for table `alert_rules`
--

CREATE TABLE `alert_rules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rule_name` varchar(100) NOT NULL,
  `condition_sql` text NOT NULL,
  `severity` enum('low','medium','high','critical') NOT NULL,
  `action` varchar(100) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `alert_rules_is_active_index` (`is_active`),
  KEY `alert_rules_severity_index` (`severity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `alert_rules`
--


--
-- Table structure for table `alerts`
--

CREATE TABLE `alerts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `alert_type` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'info',
  `entity_type` varchar(255) DEFAULT NULL,
  `entity_id` bigint(20) unsigned DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `action_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alerts_alert_type_index` (`alert_type`),
  KEY `alerts_is_read_index` (`is_read`),
  KEY `alerts_severity_index` (`severity`),
  KEY `alerts_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `alerts`
--


--
-- Table structure for table `algorithm_metrics`
--

CREATE TABLE `algorithm_metrics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `metric_date` date NOT NULL,
  `algorithm_name` varchar(100) NOT NULL,
  `operation_count` int(11) NOT NULL DEFAULT 0,
  `success_count` int(11) NOT NULL DEFAULT 0,
  `error_count` int(11) NOT NULL DEFAULT 0,
  `avg_execution_time_ms` decimal(10,2) DEFAULT NULL,
  `discrepancies_detected` int(11) NOT NULL DEFAULT 0,
  `auto_corrections` int(11) NOT NULL DEFAULT 0,
  `manual_reviews` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `algorithm_metrics_metric_date_algorithm_name_unique` (`metric_date`,`algorithm_name`),
  KEY `algorithm_metrics_metric_date_index` (`metric_date`),
  KEY `algorithm_metrics_algorithm_name_index` (`algorithm_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `algorithm_metrics`
--


--
-- Table structure for table `api_rate_limits`
--

CREATE TABLE `api_rate_limits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `endpoint` varchar(255) NOT NULL,
  `max_requests` int(11) NOT NULL DEFAULT 100,
  `window_seconds` int(11) NOT NULL DEFAULT 3600,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_rate_limits_endpoint_unique` (`endpoint`),
  KEY `api_rate_limits_is_active_index` (`is_active`),
  KEY `api_rate_limits_endpoint_index` (`endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `api_rate_limits`
--


--
-- Table structure for table `app_configs`
--

CREATE TABLE `app_configs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `setting` text NOT NULL,
  `value` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_configs_setting_unique` (`setting`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `app_configs`
--


--
-- Table structure for table `appconfig`
--

CREATE TABLE `appconfig` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `setting` mediumtext NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `appconfig_setting_index` (`setting`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `appconfig`
--


--
-- Table structure for table `application_logs`
--

CREATE TABLE `application_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(20) NOT NULL,
  `message` text NOT NULL,
  `context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`context`)),
  `source` varchar(255) DEFAULT NULL,
  `router_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `application_logs_created_at_index` (`created_at`),
  KEY `application_logs_level_created_at_index` (`level`,`created_at`),
  KEY `application_logs_source_index` (`source`),
  KEY `application_logs_level_index` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `application_logs`
--


--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `resource_type` varchar(255) NOT NULL,
  `resource_id` bigint(20) unsigned NOT NULL,
  `changes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`changes`)),
  `ip_address` varchar(255) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_user_id_index` (`user_id`),
  KEY `audit_logs_resource_type_index` (`resource_type`),
  KEY `audit_logs_action_index` (`action`),
  KEY `audit_logs_created_at_index` (`created_at`),
  CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_logs`
--


--
-- Table structure for table `balance_calculation_rules`
--

CREATE TABLE `balance_calculation_rules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rule_name` varchar(100) NOT NULL,
  `rule_type` enum('plan_based','time_based','usage_based','custom') NOT NULL,
  `plan_type` varchar(50) DEFAULT NULL,
  `calculation_formula` text NOT NULL,
  `validation_rules` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `priority` int(11) NOT NULL DEFAULT 100,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `balance_calculation_rules_is_active_index` (`is_active`),
  KEY `balance_calculation_rules_rule_type_index` (`rule_type`),
  KEY `balance_calculation_rules_priority_index` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `balance_calculation_rules`
--


--
-- Table structure for table `balance_history`
--

CREATE TABLE `balance_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `type` enum('credit','debit','refund','transfer_in','transfer_out','recharge','refill','purchase','adjustment') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `balance_before` decimal(10,2) NOT NULL,
  `balance_after` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `payment_method` enum('cash','mpesa','paypal','bank','card','voucher','transfer') DEFAULT NULL,
  `transaction_id` bigint(20) unsigned DEFAULT NULL,
  `processed_by` bigint(20) unsigned DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `balance_history_transaction_id_foreign` (`transaction_id`),
  KEY `balance_history_processed_by_foreign` (`processed_by`),
  KEY `balance_history_customer_id_index` (`customer_id`),
  KEY `balance_history_type_index` (`type`),
  KEY `balance_history_payment_method_index` (`payment_method`),
  KEY `balance_history_reference_index` (`reference`),
  KEY `balance_history_created_at_index` (`created_at`),
  CONSTRAINT `balance_history_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `balance_history_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `balance_history_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `balance_history`
--


--
-- Table structure for table `balance_operations_audit`
--

CREATE TABLE `balance_operations_audit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `operation_type` varchar(100) NOT NULL,
  `operation_id` varchar(255) DEFAULT NULL,
  `previous_balance` decimal(10,2) DEFAULT NULL,
  `new_balance` decimal(10,2) DEFAULT NULL,
  `adjustment_amount` decimal(10,2) DEFAULT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `transaction_id` bigint(20) unsigned DEFAULT NULL,
  `algorithm_used` varchar(100) DEFAULT NULL,
  `validation_passed` tinyint(1) DEFAULT NULL,
  `execution_time_ms` decimal(10,2) DEFAULT NULL,
  `admin_id` bigint(20) unsigned DEFAULT NULL,
  `gateway` varchar(100) DEFAULT NULL,
  `operation_metadata` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `balance_operations_audit_customer_id_index` (`customer_id`),
  KEY `balance_operations_audit_operation_type_index` (`operation_type`),
  KEY `balance_operations_audit_created_at_index` (`created_at`),
  KEY `balance_operations_audit_plan_id_foreign` (`plan_id`),
  KEY `balance_operations_audit_admin_id_foreign` (`admin_id`),
  CONSTRAINT `balance_operations_audit_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `balance_operations_audit_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `balance_operations_audit_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `balance_operations_audit`
--


--
-- Table structure for table `balance_snapshots`
--

CREATE TABLE `balance_snapshots` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `snapshot_date` date NOT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `balance_cached` decimal(10,2) NOT NULL DEFAULT 0.00,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `plan_price` decimal(10,2) DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `customer_status` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `balance_snapshots_customer_id_snapshot_date_unique` (`customer_id`,`snapshot_date`),
  KEY `balance_snapshots_customer_id_index` (`customer_id`),
  KEY `balance_snapshots_snapshot_date_index` (`snapshot_date`),
  KEY `balance_snapshots_plan_id_foreign` (`plan_id`),
  CONSTRAINT `balance_snapshots_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `balance_snapshots_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `balance_snapshots`
--


--
-- Table structure for table `bandwidths`
--

CREATE TABLE `bandwidths` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `rate_down` int(11) NOT NULL,
  `rate_down_unit` enum('Kbps','Mbps') NOT NULL DEFAULT 'Mbps',
  `rate_up` int(11) NOT NULL,
  `rate_up_unit` enum('Kbps','Mbps') NOT NULL DEFAULT 'Mbps',
  `burst` varchar(255) DEFAULT NULL,
  `rate_limit` varchar(255) DEFAULT NULL COMMENT 'MikroTik rate limit format: upload/download burst-upload/burst-download priority time',
  `burst_limit` int(11) DEFAULT NULL COMMENT 'Burst limit in Kbps',
  `burst_threshold` int(11) DEFAULT NULL COMMENT 'Burst threshold in Kbps',
  `burst_time` int(11) DEFAULT NULL COMMENT 'Burst time in seconds',
  `priority` int(11) NOT NULL DEFAULT 8 COMMENT 'Queue priority (1-8, 8 is lowest)',
  `limit_at` enum('rx','tx','both') NOT NULL DEFAULT 'both' COMMENT 'Limit direction: rx (download), tx (upload), both',
  `max_limit_down` int(11) DEFAULT NULL COMMENT 'Maximum download limit in Kbps',
  `max_limit_up` int(11) DEFAULT NULL COMMENT 'Maximum upload limit in Kbps',
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bandwidths_name_unique` (`name`),
  KEY `bandwidths_name_index` (`name`),
  KEY `bandwidths_is_active_index` (`is_active`),
  KEY `bandwidths_sort_order_index` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bandwidths`
--


--
-- Table structure for table `cache`
--

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cache`
--


--
-- Table structure for table `cache_locks`
--

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cache_locks`
--


--
-- Table structure for table `coupon_usage`
--

CREATE TABLE `coupon_usage` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `coupon_id` bigint(20) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `original_amount` decimal(10,2) NOT NULL,
  `discount_amount` decimal(10,2) NOT NULL,
  `final_amount` decimal(10,2) NOT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `coupon_usage_coupon_id_index` (`coupon_id`),
  KEY `coupon_usage_customer_id_index` (`customer_id`),
  KEY `coupon_usage_created_at_index` (`created_at`),
  CONSTRAINT `coupon_usage_coupon_id_foreign` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `coupon_usage_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `coupon_usage`
--


--
-- Table structure for table `coupons`
--

CREATE TABLE `coupons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `value` decimal(10,2) NOT NULL,
  `min_amount` decimal(10,2) DEFAULT NULL,
  `max_discount` decimal(10,2) DEFAULT NULL,
  `max_uses` int(11) DEFAULT NULL,
  `used_count` int(11) NOT NULL DEFAULT 0,
  `valid_from` timestamp NULL DEFAULT NULL,
  `valid_until` timestamp NULL DEFAULT NULL,
  `status` enum('active','inactive','expired','exhausted') NOT NULL DEFAULT 'active',
  `description` text DEFAULT NULL,
  `applicable_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`applicable_to`)),
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `coupons_code_unique` (`code`),
  KEY `coupons_created_by_foreign` (`created_by`),
  KEY `coupons_code_index` (`code`),
  KEY `coupons_status_index` (`status`),
  KEY `coupons_type_index` (`type`),
  KEY `coupons_valid_from_index` (`valid_from`),
  KEY `coupons_valid_until_index` (`valid_until`),
  CONSTRAINT `coupons_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `coupons`
--


--
-- Table structure for table `custom_field_values`
--

CREATE TABLE `custom_field_values` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `custom_field_id` bigint(20) unsigned NOT NULL,
  `entity_id` bigint(20) unsigned NOT NULL,
  `value` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `custom_field_values_custom_field_id_entity_id_unique` (`custom_field_id`,`entity_id`),
  KEY `custom_field_values_custom_field_id_index` (`custom_field_id`),
  KEY `custom_field_values_entity_id_index` (`entity_id`),
  CONSTRAINT `custom_field_values_custom_field_id_foreign` FOREIGN KEY (`custom_field_id`) REFERENCES `custom_fields` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `custom_field_values`
--


--
-- Table structure for table `custom_fields`
--

CREATE TABLE `custom_fields` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `entity_type` varchar(255) NOT NULL,
  `field_name` varchar(255) NOT NULL,
  `field_type` enum('text','email','phone','number','select','textarea','date') NOT NULL DEFAULT 'text',
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `custom_fields_entity_type_field_name_unique` (`entity_type`,`field_name`),
  KEY `custom_fields_entity_type_index` (`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `custom_fields`
--


--
-- Table structure for table `customer_balance`
--

CREATE TABLE `customer_balance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `static_ip_customer_id` bigint(20) unsigned DEFAULT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Current balance',
  `total_credits` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Lifetime credits',
  `total_debits` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Lifetime debits',
  `last_transaction_at` timestamp NULL DEFAULT NULL COMMENT 'Last transaction timestamp',
  `last_transaction_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Last finance_ledger entry ID',
  `transaction_count` int(11) NOT NULL DEFAULT 0 COMMENT 'Total number of transactions',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_customer_balance` (`customer_id`,`static_ip_customer_id`),
  KEY `customer_balance_customer_id_index` (`customer_id`),
  KEY `customer_balance_static_ip_customer_id_index` (`static_ip_customer_id`),
  KEY `idx_customer_balance_balance` (`balance`),
  KEY `idx_customer_balance_last_transaction` (`last_transaction_at`),
  CONSTRAINT `customer_balance_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `customer_balance_static_ip_customer_id_foreign` FOREIGN KEY (`static_ip_customer_id`) REFERENCES `static_ip_customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chk_customer_balance_customer_type` CHECK (`customer_id` is not null and `static_ip_customer_id` is null or `customer_id` is null and `static_ip_customer_id` is not null)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_balance`
--


--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fullname` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `service_type` enum('Static IP','VPN') NOT NULL DEFAULT 'Static IP',
  `router_id` bigint(20) unsigned DEFAULT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_ip_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('Active','Inactive','Expired','Banned') DEFAULT 'Active',
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `balance_cached` decimal(12,2) NOT NULL DEFAULT 0.00,
  `coordinates` varchar(255) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customers_username_unique` (`username`),
  UNIQUE KEY `customers_email_unique` (`email`),
  UNIQUE KEY `customers_phone_unique` (`phone`),
  KEY `customers_username_index` (`username`),
  KEY `customers_email_index` (`email`),
  KEY `customers_status_index` (`status`),
  KEY `customers_service_type_index` (`service_type`),
  KEY `customers_router_id_index` (`router_id`),
  KEY `customers_plan_id_index` (`plan_id`),
  KEY `customers_assigned_ip_id_index` (`assigned_ip_id`),
  CONSTRAINT `customers_assigned_ip_id_foreign` FOREIGN KEY (`assigned_ip_id`) REFERENCES `static_ips` (`id`) ON DELETE SET NULL,
  CONSTRAINT `customers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `static_ip_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `customers_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customers`
--


--
-- Table structure for table `customers_fields`
--

CREATE TABLE `customers_fields` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `field_name` varchar(255) NOT NULL,
  `field_value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customers_fields_customer_id_index` (`customer_id`),
  KEY `customers_fields_field_name_index` (`field_name`),
  CONSTRAINT `customers_fields_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customers_fields`
--


--
-- Table structure for table `customers_inbox`
--

CREATE TABLE `customers_inbox` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL,
  `date_read` datetime DEFAULT NULL,
  `subject` varchar(64) NOT NULL,
  `body` text DEFAULT NULL,
  `from` varchar(8) NOT NULL DEFAULT 'System' COMMENT 'System or Admin or Else',
  `admin_id` bigint(20) unsigned DEFAULT NULL COMMENT 'other than admin is null',
  PRIMARY KEY (`id`),
  KEY `customers_inbox_customer_id_index` (`customer_id`),
  KEY `customers_inbox_date_created_index` (`date_created`),
  KEY `customers_inbox_date_read_index` (`date_read`),
  KEY `customers_inbox_admin_id_foreign` (`admin_id`),
  CONSTRAINT `customers_inbox_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `customers_inbox_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customers_inbox`
--


--
-- Table structure for table `duplicate_prevention_stats`
--

CREATE TABLE `duplicate_prevention_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `total_checks` int(11) NOT NULL DEFAULT 0,
  `duplicates_prevented` int(11) NOT NULL DEFAULT 0,
  `cache_hits` int(11) NOT NULL DEFAULT 0,
  `database_hits` int(11) NOT NULL DEFAULT 0,
  `lock_failures` int(11) NOT NULL DEFAULT 0,
  `errors` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `duplicate_prevention_stats_date_unique` (`date`),
  KEY `duplicate_prevention_stats_date_index` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `duplicate_prevention_stats`
--


--
-- Table structure for table `email_templates`
--

CREATE TABLE `email_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body_html` text NOT NULL,
  `body_text` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `available_variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`available_variables`)),
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_templates_name_unique` (`name`),
  UNIQUE KEY `email_templates_slug_unique` (`slug`),
  KEY `email_templates_slug_index` (`slug`),
  KEY `email_templates_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `email_templates`
--


--
-- Table structure for table `enhanced_logs`
--

CREATE TABLE `enhanced_logs` (
  `id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  `level` varchar(10) NOT NULL,
  `message` text NOT NULL,
  `context` longtext DEFAULT NULL,
  `userid` bigint(20) unsigned NOT NULL DEFAULT 0,
  `ip` varchar(45) NOT NULL DEFAULT '',
  `user_agent` text DEFAULT NULL,
  `request_id` varchar(32) NOT NULL DEFAULT '',
  `memory_usage` bigint(20) unsigned NOT NULL DEFAULT 0,
  `execution_time` decimal(10,3) NOT NULL DEFAULT 0.000,
  PRIMARY KEY (`id`),
  KEY `enhanced_logs_timestamp_index` (`timestamp`),
  KEY `enhanced_logs_level_index` (`level`),
  KEY `enhanced_logs_userid_index` (`userid`),
  KEY `enhanced_logs_request_id_index` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `enhanced_logs`
--


--
-- Table structure for table `enterprise_transaction_categories`
--

CREATE TABLE `enterprise_transaction_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(50) NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `category` enum('financial_recharge','service_extension','standard_recharge') NOT NULL,
  `classification_reason` text DEFAULT NULL,
  `financial_impact` tinyint(1) NOT NULL DEFAULT 1,
  `ledger_recorded` tinyint(1) NOT NULL DEFAULT 1,
  `amount` decimal(12,2) DEFAULT NULL,
  `extension_days` int(11) DEFAULT NULL,
  `processed_by` bigint(20) unsigned DEFAULT NULL,
  `processing_mode` enum('enhanced','legacy','hybrid') NOT NULL DEFAULT 'enhanced',
  `execution_time_ms` decimal(8,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `enterprise_transaction_categories_transaction_id_index` (`transaction_id`),
  KEY `enterprise_transaction_categories_customer_id_index` (`customer_id`),
  KEY `enterprise_transaction_categories_category_index` (`category`),
  KEY `enterprise_transaction_categories_created_at_index` (`created_at`),
  KEY `enterprise_transaction_categories_processed_by_foreign` (`processed_by`),
  CONSTRAINT `enterprise_transaction_categories_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `enterprise_transaction_categories_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `enterprise_transaction_categories`
--


--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `failed_jobs`
--


--
-- Table structure for table `finance_ledger`
--

CREATE TABLE `finance_ledger` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `static_ip_customer_id` bigint(20) unsigned DEFAULT NULL COMMENT 'FK to static_ip_customers table (nullable for backward compatibility)',
  `pppoe_customer_id` bigint(20) unsigned DEFAULT NULL COMMENT 'FK to pppoe_customers table (nullable for backward compatibility)',
  `type` enum('admin_cash','user_payment','burn','reset','plan_purchase','refund','adjustment','balance_state_machine_audit','static_ip_recharge','static_ip_burn','pppoe_recharge','pppoe_burn') NOT NULL,
  `amount` decimal(12,2) NOT NULL COMMENT 'Positive for credits, negative for debits',
  `ref` varchar(64) NOT NULL DEFAULT '' COMMENT 'Reference: mpesa_receipt, admin_note, cron_batch_id, etc',
  `meta` longtext DEFAULT NULL COMMENT 'Additional data: plan_id, cycle_start/end, etc',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `finance_ledger_customer_id_index` (`customer_id`),
  KEY `finance_ledger_ref_index` (`ref`),
  KEY `finance_ledger_created_at_index` (`created_at`),
  KEY `finance_ledger_type_index` (`type`),
  KEY `finance_ledger_static_ip_customer_id_index` (`static_ip_customer_id`),
  KEY `finance_ledger_pppoe_customer_id_index` (`pppoe_customer_id`),
  CONSTRAINT `finance_ledger_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `finance_ledger_pppoe_customer_id_foreign` FOREIGN KEY (`pppoe_customer_id`) REFERENCES `pppoe_customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `finance_ledger_static_ip_customer_id_foreign` FOREIGN KEY (`static_ip_customer_id`) REFERENCES `static_ip_customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chk_finance_ledger_customer_type_v2` CHECK (`customer_id` is not null and `static_ip_customer_id` is null and `pppoe_customer_id` is null or `customer_id` is null and `static_ip_customer_id` is not null and `pppoe_customer_id` is null or `customer_id` is null and `static_ip_customer_id` is null and `pppoe_customer_id` is not null)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `finance_ledger`
--


--
-- Table structure for table `finance_ledger_archive`
--

CREATE TABLE `finance_ledger_archive` (
  `id` int(10) unsigned NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `type` enum('admin_cash','user_payment','burn','reset','plan_purchase','refund','adjustment') NOT NULL,
  `amount` decimal(12,2) NOT NULL COMMENT 'Positive for credits, negative for debits',
  `ref` varchar(64) NOT NULL DEFAULT '' COMMENT 'Reference: mpesa_receipt, admin_note, cron_batch_id, etc',
  `meta` longtext DEFAULT NULL COMMENT 'Additional data: plan_id, cycle_start/end, etc',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `finance_ledger_archive_customer_id_index` (`customer_id`),
  KEY `finance_ledger_archive_ref_index` (`ref`),
  KEY `finance_ledger_archive_created_at_index` (`created_at`),
  KEY `finance_ledger_archive_type_index` (`type`),
  CONSTRAINT `finance_ledger_archive_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `finance_ledger_archive`
--


--
-- Table structure for table `financial_settings`
--

CREATE TABLE `financial_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `auto_deactivate_threshold` decimal(10,2) NOT NULL DEFAULT 1.00 COMMENT 'Balance threshold below which customer is auto-deactivated (KES)',
  `grace_period_days` int(11) NOT NULL DEFAULT 0 COMMENT 'Days to wait before auto-deactivation after balance drops below threshold',
  `notify_before_disconnect` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Send notification to customer before disconnection',
  `transfer_approval_threshold` decimal(10,2) NOT NULL DEFAULT 10000.00 COMMENT 'Transfers above this amount require approval',
  `require_transfer_approval` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether transfer approval is enabled',
  `max_transfers_per_day` int(11) NOT NULL DEFAULT 100 COMMENT 'Maximum transfers per customer per day',
  `max_transfer_amount_per_day` int(11) NOT NULL DEFAULT 1000000 COMMENT 'Maximum total transfer amount per customer per day (KES)',
  `require_2fa_for_large_transfers` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Require 2FA for transfers above threshold',
  `two_fa_threshold` decimal(10,2) NOT NULL DEFAULT 50000.00 COMMENT 'Amount threshold for 2FA requirement',
  `audit_all_transactions` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Log all financial transactions',
  `audit_retention_days` int(11) NOT NULL DEFAULT 365 COMMENT 'Days to retain audit logs',
  `auto_reconcile_enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Enable automatic daily reconciliation',
  `auto_reconcile_time` time NOT NULL DEFAULT '03:00:00' COMMENT 'Time to run automatic reconciliation (EAT)',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `financial_settings_id_unique` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `financial_settings`
--

INSERT INTO `financial_settings` VALUES
(1,1.00,0,1,10000.00,0,100,1000000,0,50000.00,1,365,1,'03:00:00','2025-11-09 07:22:24','2025-11-09 07:22:24');

--
-- Table structure for table `idempotent_operations`
--

CREATE TABLE `idempotent_operations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idempotency_key` varchar(255) NOT NULL,
  `result` text DEFAULT NULL,
  `status` enum('pending','completed','failed') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idempotent_operations_idempotency_key_unique` (`idempotency_key`),
  KEY `idempotent_operations_idempotency_key_index` (`idempotency_key`),
  KEY `idempotent_operations_status_index` (`status`),
  KEY `idempotent_operations_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `idempotent_operations`
--


--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(50) NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `subscription_id` bigint(20) unsigned DEFAULT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('subscription','topup','manual','renewal') NOT NULL DEFAULT 'subscription',
  `subtotal` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `status` enum('draft','pending','paid','overdue','cancelled','refunded') NOT NULL DEFAULT 'pending',
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `paid_date` date DEFAULT NULL,
  `description` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`items`)),
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
  KEY `invoices_invoice_number_index` (`invoice_number`),
  KEY `invoices_customer_id_index` (`customer_id`),
  KEY `invoices_status_index` (`status`),
  KEY `invoices_due_date_index` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `invoices`
--


--
-- Table structure for table `ip_pool_addresses`
--

CREATE TABLE `ip_pool_addresses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ip_pool_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `status` enum('available','assigned','reserved') NOT NULL DEFAULT 'available',
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_pool_addresses_ip_pool_id_ip_address_unique` (`ip_pool_id`,`ip_address`),
  KEY `ip_pool_addresses_status_index` (`status`),
  KEY `ip_pool_addresses_customer_id_index` (`customer_id`),
  CONSTRAINT `ip_pool_addresses_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `ip_pool_addresses_ip_pool_id_foreign` FOREIGN KEY (`ip_pool_id`) REFERENCES `ip_pools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ip_pool_addresses`
--


--
-- Table structure for table `ip_pools`
--

CREATE TABLE `ip_pools` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pool_name` varchar(100) NOT NULL,
  `local_ip` varchar(50) NOT NULL,
  `range_ip` varchar(100) NOT NULL,
  `gateway` varchar(45) DEFAULT NULL,
  `dns_primary` varchar(45) DEFAULT NULL,
  `dns_secondary` varchar(45) DEFAULT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_public` tinyint(1) NOT NULL DEFAULT 0,
  `total_ips` int(11) NOT NULL DEFAULT 0,
  `used_ips` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_pools_pool_name_unique` (`pool_name`),
  KEY `ip_pools_pool_name_index` (`pool_name`),
  KEY `ip_pools_router_id_index` (`router_id`),
  KEY `ip_pools_is_active_index` (`is_active`),
  CONSTRAINT `ip_pools_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ip_pools`
--


--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jobs`
--


--
-- Table structure for table `login_customization`
--

CREATE TABLE `login_customization` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `logo_url` varchar(255) DEFAULT NULL,
  `background_image_url` varchar(255) DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `tagline` varchar(255) DEFAULT NULL,
  `primary_color` varchar(255) NOT NULL DEFAULT '#3B82F6',
  `secondary_color` varchar(255) NOT NULL DEFAULT '#10B981',
  `enable_social_login` tinyint(1) NOT NULL DEFAULT 0,
  `enable_forgot_password` tinyint(1) NOT NULL DEFAULT 1,
  `enable_signup` tinyint(1) NOT NULL DEFAULT 0,
  `footer_text` text DEFAULT NULL,
  `support_email` varchar(255) DEFAULT NULL,
  `support_phone` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `login_customization`
--


--
-- Table structure for table `manual_review_queue`
--

CREATE TABLE `manual_review_queue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `review_type` varchar(255) NOT NULL,
  `entity_type` varchar(255) NOT NULL,
  `entity_id` bigint(20) unsigned NOT NULL,
  `reason` text NOT NULL,
  `priority` enum('low','normal','high','critical') NOT NULL DEFAULT 'normal',
  `status` enum('pending','assigned','completed','rejected') NOT NULL DEFAULT 'pending',
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `decision` varchar(255) DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `manual_review_queue_review_type_index` (`review_type`),
  KEY `manual_review_queue_status_index` (`status`),
  KEY `manual_review_queue_priority_index` (`priority`),
  KEY `manual_review_queue_assigned_to_index` (`assigned_to`),
  CONSTRAINT `manual_review_queue_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `manual_review_queue`
--


--
-- Table structure for table `message_queue`
--

CREATE TABLE `message_queue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message_type` enum('email','sms','telegram','whatsapp') NOT NULL DEFAULT 'email',
  `recipient` varchar(255) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `body` longtext NOT NULL,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `attempts` int(11) NOT NULL DEFAULT 0,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `message_queue_message_type_index` (`message_type`),
  KEY `message_queue_status_index` (`status`),
  KEY `message_queue_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `message_queue`
--


--
-- Table structure for table `message_templates`
--

CREATE TABLE `message_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('email','sms','whatsapp') NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variables`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `message_templates_type_index` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `message_templates`
--


--
-- Table structure for table `meta`
--

CREATE TABLE `meta` (
  `id` bigint(20) unsigned NOT NULL,
  `tbl` varchar(32) NOT NULL COMMENT 'Table name',
  `tbl_id` bigint(20) unsigned NOT NULL COMMENT 'table value id',
  `name` varchar(32) NOT NULL,
  `value` mediumtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `meta_tbl_index` (`tbl`),
  KEY `meta_tbl_id_index` (`tbl_id`),
  KEY `meta_tbl_tbl_id_index` (`tbl`,`tbl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `meta`
--


--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES
(1,'2024_01_01_000001_create_customers_table',1),
(2,'2024_01_01_000002_create_routers_table',1),
(3,'2024_01_01_000003_create_plans_table',1),
(4,'2024_01_01_000003a_create_users_table',1),
(5,'2024_01_01_000004_create_subscriptions_table',1),
(6,'2024_01_01_000005_create_transactions_table',1),
(7,'2024_01_01_000006_add_role_to_users_table',1),
(8,'2024_01_15_000000_create_app_configs_table',1),
(9,'2025_01_24_000001_create_static_ip_routers_table',1),
(10,'2025_01_24_000002_create_static_ip_pools_table',1),
(11,'2025_01_24_000003_create_static_ip_plans_table',1),
(12,'2025_01_24_000004_create_static_ips_table',1),
(13,'2025_01_24_000005_create_static_ip_customers_table',1),
(14,'2025_01_24_000006_create_static_ip_billing_table',1),
(15,'2025_01_24_000007_add_customer_fk_to_static_ips',1),
(16,'2025_10_16_000000_create_settings_table',1),
(17,'2025_10_16_100000_create_vouchers_table',1),
(18,'2025_10_16_101000_create_balance_history_table',1),
(19,'2025_10_16_102000_create_coupons_table',1),
(20,'2025_10_16_102100_create_coupon_usage_table',1),
(21,'2025_10_17_000000_create_ip_pools_table',1),
(22,'2025_10_17_000000_create_notifications_table',1),
(23,'2025_10_17_000001_create_nas_table',1),
(24,'2025_10_17_000001_create_static_ip_assignments_table',1),
(25,'2025_10_17_000002_create_message_templates_table',1),
(26,'2025_10_17_000002_create_nasreload_table',1),
(27,'2025_10_17_000003_create_radacct_table',1),
(28,'2025_10_17_000004_create_radcheck_table',1),
(29,'2025_10_17_000005_create_radgroupcheck_table',1),
(30,'2025_10_17_000006_create_radgroupreply_table',1),
(31,'2025_10_17_000007_create_radpostauth_table',1),
(32,'2025_10_17_000008_create_radreply_table',1),
(33,'2025_10_17_000009_create_radusergroup_table',1),
(34,'2025_10_17_000010_create_rad_acct_table',1),
(35,'2025_10_17_110000_create_email_templates_table',1),
(36,'2025_10_17_160000_create_payment_gateways_table',1),
(37,'2025_10_17_160000a_create_invoices_table',1),
(38,'2025_10_17_160001_create_payment_transactions_table',1),
(39,'2025_10_17_160003_create_payment_logs_table',1),
(40,'2025_10_17_170000_create_tickets_table',1),
(41,'2025_10_17_170001_create_ticket_replies_table',1),
(42,'2025_10_17_180000_create_bandwidths_table',1),
(43,'2025_10_17_200000_create_logs_table',1),
(44,'2025_10_17_200001_create_message_logs_table',1),
(45,'2025_10_17_200002_add_coordinates_to_customers_and_users',1),
(46,'2025_10_19_000001_add_expired_status_to_customers',1),
(47,'2025_10_19_092936_add_default_to_balance_before_in_transactions_table',1),
(48,'2025_10_20_000001_add_unique_constraint_to_finance_ledger_ref',1),
(49,'2025_10_20_000001_create_ip_pool_addresses_table',1),
(50,'2025_10_20_000002_add_ssl_to_routers_table',1),
(51,'2025_10_20_000002_create_financial_settings_table',1),
(52,'2025_10_20_000003_add_dns_gateway_to_ip_pools_table',1),
(53,'2025_10_20_000003_create_transaction_approvals_table',1),
(54,'2025_10_20_000004_add_coverage_to_routers_table',1),
(55,'2025_10_20_100000_add_missing_fields_to_plans_table',1),
(56,'2025_10_20_100001_add_rate_limit_to_bandwidths_table',1),
(57,'2025_10_20_100002_add_missing_fields_to_subscriptions_table',1),
(58,'2025_10_20_101051_add_foreign_key_to_static_ips_table',1),
(59,'2025_10_20_101114_remove_pppoe_ip_from_customers_table',1),
(60,'2025_10_20_110000_create_finance_ledger_table',1),
(61,'2025_10_20_110001_add_balance_cached_to_customers_table',1),
(62,'2025_10_20_110002_create_operation_locks_table',1),
(63,'2025_10_20_120000_create_system_settings_table',1),
(64,'2025_10_20_120001_create_static_ips_table',1),
(65,'2025_10_20_120002_create_message_queue_table',1),
(66,'2025_10_20_120003_create_custom_fields_table',1),
(67,'2025_10_20_120004_create_custom_field_values_table',1),
(68,'2025_10_20_120005_create_themes_table',1),
(69,'2025_10_20_120006_create_widgets_table',1),
(70,'2025_10_20_120007_create_alerts_table',1),
(71,'2025_10_20_120008_create_manual_review_queue_table',1),
(72,'2025_10_20_120009_create_login_customization_table',1),
(73,'2025_10_20_120010_create_user_sessions_table',1),
(74,'2025_10_20_120011_create_plugins_table',1),
(75,'2025_10_20_120012_create_api_rate_limits_table',1),
(76,'2025_10_20_120013_create_audit_logs_table',1),
(77,'2025_10_20_130000_create_finance_ledger_archive_table',1),
(78,'2025_10_20_130001_create_balance_operations_audit_table',1),
(79,'2025_10_20_130002_create_balance_snapshots_table',1),
(80,'2025_10_20_130003_create_customers_inbox_table',1),
(81,'2025_10_20_130004_create_idempotent_operations_table',1),
(82,'2025_10_20_130005_create_service_extension_audit_table',1),
(83,'2025_10_20_130006_create_staticip_plan_pools_table',1),
(84,'2025_10_20_130007_create_transaction_locks_table',1),
(85,'2025_10_20_130008_create_transaction_tracking_table',1),
(86,'2025_10_20_130009_create_user_recharges_table',1),
(87,'2025_10_20_130010_create_alert_rules_table',1),
(88,'2025_10_20_130011_create_algorithm_metrics_table',1),
(89,'2025_10_20_130012_create_appconfig_table',1),
(90,'2025_10_20_130013_create_balance_calculation_rules_table',1),
(91,'2025_10_20_130014_create_customers_fields_table',1),
(92,'2025_10_20_130015_create_duplicate_prevention_stats_table',1),
(93,'2025_10_20_130016_create_enhanced_logs_table',1),
(94,'2025_10_20_130017_create_enterprise_transaction_categories_table',1),
(95,'2025_10_20_130018_create_meta_table',1),
(96,'2025_10_20_130020_update_customers_table_for_static_ip',1),
(97,'2025_10_22_000001_fix_password_column_size',1),
(98,'2025_10_22_000002_create_tbl_subscriptions_table',1),
(99,'2025_10_22_105935_create_personal_access_tokens_table',1),
(100,'2025_10_23_182259_create_application_logs_table',1),
(101,'2025_10_23_184009_fix_static_ip_plans_table_schema',1),
(102,'2025_10_24_000001_add_balance_and_expiry_to_static_ip_customers',1),
(103,'2025_10_24_120000_extend_finance_ledger_for_static_ip',1),
(104,'2025_10_24_120001_make_finance_ledger_customer_id_nullable',1),
(105,'2025_10_24_150000_partition_finance_ledger',1),
(106,'2025_10_24_150001_create_customer_balance_table',1),
(107,'2025_10_24_150754_create_jobs_table',1),
(108,'2025_10_24_150808_create_failed_jobs_table',1),
(109,'2025_10_24_160845_create_cache_table',1),
(110,'2025_10_24_170000_remove_hotspot_pppoe_service_types',1),
(111,'2025_10_24_180000_add_expired_status_to_static_ip_customers',1),
(112,'2025_10_27_000001_fix_customer_foreign_keys_to_tbl_customers',1),
(113,'2025_10_28_000001_add_photo_to_users_table',1),
(114,'2025_10_30_000001_create_pppoe_pools_table',1),
(115,'2025_10_30_000002_create_pppoe_plans_table',1),
(116,'2025_10_30_000003_create_pppoe_customers_table',1),
(117,'2025_10_30_000004_create_pppoe_ip_addresses_table',1),
(118,'2025_10_30_000005_create_pppoe_balance_ledger_table',1),
(119,'2025_10_30_000006_add_missing_columns_to_routers_table',1),
(120,'2025_10_30_000007_add_sync_columns_to_pppoe_tables',1),
(121,'2025_10_30_120000_extend_finance_ledger_for_pppoe',1),
(122,'2025_10_30_193800_drop_old_finance_ledger_constraint',1),
(123,'2025_11_02_000001_fix_static_ip_foreign_keys_to_routers',1),
(124,'2025_11_07_100001_add_tenant_id_to_users_table',1);

--
-- Table structure for table `nas`
--

CREATE TABLE `nas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `nasname` varchar(128) NOT NULL,
  `shortname` varchar(32) DEFAULT NULL,
  `type` varchar(30) NOT NULL DEFAULT 'other',
  `ports` int(11) DEFAULT NULL,
  `secret` varchar(60) NOT NULL DEFAULT 'secret',
  `server` varchar(64) DEFAULT NULL,
  `community` varchar(50) DEFAULT NULL,
  `description` varchar(200) NOT NULL DEFAULT 'RADIUS Client',
  `routers` varchar(32) NOT NULL DEFAULT '',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nas_nasname_index` (`nasname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `nas`
--


--
-- Table structure for table `nasreload`
--

CREATE TABLE `nasreload` (
  `nasipaddress` varchar(15) NOT NULL,
  `reloadtime` datetime NOT NULL,
  PRIMARY KEY (`nasipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `nasreload`
--


--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `type` enum('service_activated','service_refilled','service_recharged','service_cancelled','service_expiring','service_expired','payment_received','low_balance','welcome','custom') NOT NULL,
  `channel` enum('email','sms','both') NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `sent_at` timestamp NULL DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_customer_id_index` (`customer_id`),
  KEY `notifications_type_index` (`type`),
  KEY `notifications_status_index` (`status`),
  KEY `notifications_created_at_index` (`created_at`),
  CONSTRAINT `notifications_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--


--
-- Table structure for table `operation_locks`
--

CREATE TABLE `operation_locks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `resource_type` varchar(255) NOT NULL,
  `resource_id` bigint(20) unsigned NOT NULL,
  `locked_by` bigint(20) unsigned DEFAULT NULL,
  `locked_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `operation_locks_resource_type_resource_id_unique` (`resource_type`,`resource_id`),
  KEY `operation_locks_resource_type_resource_id_index` (`resource_type`,`resource_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `operation_locks`
--


--
-- Table structure for table `payment_gateways`
--

CREATE TABLE `payment_gateways` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'inactive',
  `api_key` text DEFAULT NULL,
  `api_secret` text DEFAULT NULL,
  `merchant_id` text DEFAULT NULL,
  `webhook_secret` text DEFAULT NULL,
  `mode` enum('sandbox','live') NOT NULL DEFAULT 'sandbox',
  `transaction_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `transaction_fee_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `min_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_amount` decimal(10,2) NOT NULL DEFAULT 999999.00,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `supported_currencies` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`supported_currencies`)),
  `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`config`)),
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `logo_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_gateways_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payment_gateways`
--


--
-- Table structure for table `payment_logs`
--

CREATE TABLE `payment_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `payment_transaction_id` bigint(20) unsigned DEFAULT NULL,
  `gateway_name` varchar(50) NOT NULL,
  `type` enum('request','response','callback','webhook','error','info') NOT NULL DEFAULT 'info',
  `level` enum('debug','info','warning','error') NOT NULL DEFAULT 'info',
  `message` text NOT NULL,
  `data` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_logs_payment_transaction_id_index` (`payment_transaction_id`),
  KEY `payment_logs_gateway_name_index` (`gateway_name`),
  KEY `payment_logs_type_index` (`type`),
  KEY `payment_logs_created_at_index` (`created_at`),
  CONSTRAINT `payment_logs_payment_transaction_id_foreign` FOREIGN KEY (`payment_transaction_id`) REFERENCES `payment_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payment_logs`
--


--
-- Table structure for table `payment_transactions`
--

CREATE TABLE `payment_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(100) NOT NULL,
  `gateway_transaction_id` varchar(512) DEFAULT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `payment_gateway_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `router_id` bigint(20) unsigned DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `gateway_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `status` enum('pending','processing','completed','failed','cancelled','refunded') NOT NULL DEFAULT 'pending',
  `payment_method` varchar(50) DEFAULT NULL,
  `payment_channel` varchar(50) DEFAULT NULL,
  `payment_url` varchar(512) DEFAULT NULL,
  `pg_request` text DEFAULT NULL,
  `pg_response` text DEFAULT NULL,
  `pg_callback_data` text DEFAULT NULL,
  `expired_at` timestamp NULL DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_transactions_transaction_id_unique` (`transaction_id`),
  KEY `payment_transactions_transaction_id_index` (`transaction_id`),
  KEY `payment_transactions_gateway_transaction_id_index` (`gateway_transaction_id`),
  KEY `payment_transactions_customer_id_index` (`customer_id`),
  KEY `payment_transactions_status_index` (`status`),
  KEY `payment_transactions_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payment_transactions`
--


--
-- Table structure for table `personal_access_tokens`
--

CREATE TABLE `personal_access_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint(20) unsigned NOT NULL,
  `name` text NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `personal_access_tokens`
--


--
-- Table structure for table `plans`
--

CREATE TABLE `plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('Hotspot','PPPoE','Static IP','VPN') NOT NULL DEFAULT 'Hotspot',
  `prepaid` enum('yes','no') NOT NULL DEFAULT 'yes' COMMENT 'Prepaid or Postpaid plan',
  `plan_type` enum('Personal','Business') NOT NULL DEFAULT 'Personal' COMMENT 'Personal or Business package',
  `typebp` enum('Unlimited','Limited') NOT NULL DEFAULT 'Unlimited' COMMENT 'Unlimited or Limited package',
  `limit_type` enum('Time_Limit','Data_Limit','Both_Limit') DEFAULT NULL COMMENT 'Type of limit for Limited packages',
  `router_id` bigint(20) unsigned DEFAULT NULL,
  `bandwidth_id` bigint(20) unsigned DEFAULT NULL,
  `pool_id` bigint(20) unsigned DEFAULT NULL,
  `device` varchar(255) NOT NULL DEFAULT 'MikrotikHotspot' COMMENT 'Device driver name (MikrotikHotspot, MikrotikPPPOE, etc.)',
  `is_radius` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a RADIUS plan',
  `plan_expired` bigint(20) unsigned DEFAULT NULL,
  `on_login` text DEFAULT NULL COMMENT 'Custom script to run on user login',
  `on_logout` text DEFAULT NULL COMMENT 'Custom script to run on user logout',
  `staticip_speed_tier` varchar(255) DEFAULT NULL COMMENT 'Speed tier description for Static IP plans',
  `preferred_isp` varchar(255) DEFAULT NULL COMMENT 'Preferred ISP for routing',
  `suspend_add_to_raw` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Add suspended users to raw table',
  `enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Is plan enabled (replaces is_active)',
  `price` decimal(10,2) NOT NULL,
  `validity_days` int(11) NOT NULL DEFAULT 30,
  `validity_unit` enum('Mins','Hrs','Days','Weeks','Months','Period') NOT NULL DEFAULT 'Days' COMMENT 'Unit for validity period',
  `expired_date` int(11) NOT NULL DEFAULT 20 COMMENT 'Day of month for postpaid expiration (1-28)',
  `download_speed` varchar(255) DEFAULT NULL,
  `upload_speed` varchar(255) DEFAULT NULL,
  `data_limit` bigint(20) DEFAULT NULL,
  `data_unit` enum('MB','GB') DEFAULT NULL COMMENT 'Unit for data limit',
  `time_limit` int(11) DEFAULT NULL,
  `time_unit` enum('Mins','Hrs') DEFAULT NULL COMMENT 'Unit for time limit',
  `shared_users` int(11) NOT NULL DEFAULT 1,
  `validity` int(11) NOT NULL DEFAULT 30 COMMENT 'Validity period numeric value',
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_public` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `plans_router_id_foreign` (`router_id`),
  KEY `plans_type_index` (`type`),
  KEY `plans_is_active_index` (`is_active`),
  KEY `plans_is_public_index` (`is_public`),
  KEY `plans_bandwidth_id_foreign` (`bandwidth_id`),
  KEY `plans_pool_id_foreign` (`pool_id`),
  KEY `plans_plan_expired_foreign` (`plan_expired`),
  KEY `plans_prepaid_index` (`prepaid`),
  KEY `plans_plan_type_index` (`plan_type`),
  KEY `plans_typebp_index` (`typebp`),
  KEY `plans_enabled_index` (`enabled`),
  KEY `plans_is_radius_index` (`is_radius`),
  KEY `plans_device_index` (`device`),
  CONSTRAINT `plans_bandwidth_id_foreign` FOREIGN KEY (`bandwidth_id`) REFERENCES `bandwidths` (`id`) ON DELETE SET NULL,
  CONSTRAINT `plans_plan_expired_foreign` FOREIGN KEY (`plan_expired`) REFERENCES `plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `plans_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `ip_pools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `plans_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `plans`
--


--
-- Table structure for table `plugins`
--

CREATE TABLE `plugins` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `version` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `author` varchar(255) DEFAULT NULL,
  `plugin_file` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plugins_slug_unique` (`slug`),
  KEY `plugins_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `plugins`
--


--
-- Table structure for table `pppoe_balance_ledger`
--

CREATE TABLE `pppoe_balance_ledger` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `transaction_type` enum('credit','debit','refund','adjustment') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `balance_before` decimal(10,2) NOT NULL,
  `balance_after` decimal(10,2) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `admin_id` bigint(20) unsigned DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pppoe_balance_ledger_admin_id_foreign` (`admin_id`),
  KEY `pppoe_balance_ledger_customer_id_index` (`customer_id`),
  KEY `pppoe_balance_ledger_transaction_type_index` (`transaction_type`),
  KEY `pppoe_balance_ledger_reference_index` (`reference`),
  KEY `pppoe_balance_ledger_created_at_index` (`created_at`),
  CONSTRAINT `pppoe_balance_ledger_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pppoe_balance_ledger_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `pppoe_customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pppoe_balance_ledger`
--


--
-- Table structure for table `pppoe_customers`
--

CREATE TABLE `pppoe_customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `pool_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_ip` varchar(45) DEFAULT NULL,
  `mac_address` varchar(17) DEFAULT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expiry_date` date DEFAULT NULL,
  `status` enum('active','inactive','suspended','expired') NOT NULL DEFAULT 'inactive',
  `auto_renewal` tinyint(1) NOT NULL DEFAULT 0,
  `notes` text DEFAULT NULL,
  `last_synced_at` timestamp NULL DEFAULT NULL,
  `sync_status` enum('pending','success','failed') NOT NULL DEFAULT 'pending',
  `sync_error` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pppoe_customers_username_unique` (`username`),
  KEY `pppoe_customers_router_id_index` (`router_id`),
  KEY `pppoe_customers_plan_id_index` (`plan_id`),
  KEY `pppoe_customers_pool_id_index` (`pool_id`),
  KEY `pppoe_customers_status_index` (`status`),
  KEY `pppoe_customers_username_index` (`username`),
  KEY `pppoe_customers_phone_index` (`phone`),
  KEY `pppoe_customers_email_index` (`email`),
  KEY `pppoe_customers_expiry_date_index` (`expiry_date`),
  CONSTRAINT `pppoe_customers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `pppoe_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pppoe_customers_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pppoe_pools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pppoe_customers_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pppoe_customers`
--


--
-- Table structure for table `pppoe_ip_addresses`
--

CREATE TABLE `pppoe_ip_addresses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('available','assigned','reserved') NOT NULL DEFAULT 'available',
  `assigned_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pppoe_ip_addresses_ip_address_unique` (`ip_address`),
  KEY `pppoe_ip_addresses_pool_id_index` (`pool_id`),
  KEY `pppoe_ip_addresses_customer_id_index` (`customer_id`),
  KEY `pppoe_ip_addresses_status_index` (`status`),
  KEY `pppoe_ip_addresses_ip_address_index` (`ip_address`),
  CONSTRAINT `pppoe_ip_addresses_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `pppoe_customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pppoe_ip_addresses_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pppoe_pools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pppoe_ip_addresses`
--


--
-- Table structure for table `pppoe_plans`
--

CREATE TABLE `pppoe_plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(100) NOT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `pool_id` bigint(20) unsigned DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `validity_days` int(11) NOT NULL DEFAULT 30,
  `download_speed` varchar(20) NOT NULL,
  `upload_speed` varchar(20) NOT NULL,
  `burst_download` varchar(20) DEFAULT NULL,
  `burst_upload` varchar(20) DEFAULT NULL,
  `data_limit` bigint(20) DEFAULT NULL,
  `time_limit` int(11) DEFAULT NULL,
  `profile_name` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `is_public` tinyint(1) NOT NULL DEFAULT 1,
  `description` text DEFAULT NULL,
  `last_synced_at` timestamp NULL DEFAULT NULL,
  `sync_status` enum('pending','success','failed') NOT NULL DEFAULT 'pending',
  `sync_error` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pppoe_plans_router_id_index` (`router_id`),
  KEY `pppoe_plans_pool_id_index` (`pool_id`),
  KEY `pppoe_plans_status_index` (`status`),
  KEY `pppoe_plans_is_public_index` (`is_public`),
  CONSTRAINT `pppoe_plans_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `pppoe_pools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pppoe_plans_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pppoe_plans`
--


--
-- Table structure for table `pppoe_pools`
--

CREATE TABLE `pppoe_pools` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pool_name` varchar(100) NOT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `ip_range_start` varchar(45) NOT NULL,
  `ip_range_end` varchar(45) NOT NULL,
  `subnet_mask` varchar(20) DEFAULT NULL,
  `gateway` varchar(45) DEFAULT NULL,
  `dns_primary` varchar(45) DEFAULT NULL,
  `dns_secondary` varchar(45) DEFAULT NULL,
  `total_ips` int(11) NOT NULL DEFAULT 0,
  `used_ips` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `description` text DEFAULT NULL,
  `last_synced_at` timestamp NULL DEFAULT NULL,
  `sync_status` enum('pending','success','failed') NOT NULL DEFAULT 'pending',
  `sync_error` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pppoe_pools_router_id_index` (`router_id`),
  KEY `pppoe_pools_status_index` (`status`),
  KEY `pppoe_pools_pool_name_index` (`pool_name`),
  CONSTRAINT `pppoe_pools_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pppoe_pools`
--


--
-- Table structure for table `rad_acct`
--

CREATE TABLE `rad_acct` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(128) NOT NULL DEFAULT '',
  `nasid` varchar(32) NOT NULL DEFAULT '',
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(32) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  `acctsessiontime` bigint(20) NOT NULL DEFAULT 0,
  `acctinputoctets` bigint(20) NOT NULL DEFAULT 0,
  `acctoutputoctets` bigint(20) NOT NULL DEFAULT 0,
  `acctstatustype` varchar(32) DEFAULT NULL,
  `macaddr` varchar(50) NOT NULL DEFAULT '',
  `dateAdded` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `rad_acct_username_index` (`username`),
  KEY `rad_acct_nasipaddress_index` (`nasipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `rad_acct`
--


--
-- Table structure for table `radacct`
--

CREATE TABLE `radacct` (
  `radacctid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(64) DEFAULT NULL,
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(32) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `acctstarttime` datetime DEFAULT NULL,
  `acctupdatetime` datetime DEFAULT NULL,
  `acctstoptime` datetime DEFAULT NULL,
  `acctinterval` int(11) DEFAULT NULL,
  `acctsessiontime` int(10) unsigned DEFAULT NULL,
  `acctauthentic` varchar(32) DEFAULT NULL,
  `connectinfo_start` varchar(128) DEFAULT NULL,
  `connectinfo_stop` varchar(128) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `calledstationid` varchar(50) NOT NULL DEFAULT '',
  `callingstationid` varchar(50) NOT NULL DEFAULT '',
  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
  `servicetype` varchar(32) DEFAULT NULL,
  `framedprotocol` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`radacctid`),
  KEY `radacct_acctsessionid_index` (`acctsessionid`),
  KEY `radacct_username_index` (`username`),
  KEY `radacct_nasipaddress_index` (`nasipaddress`),
  KEY `radacct_acctstarttime_index` (`acctstarttime`),
  KEY `radacct_acctstoptime_index` (`acctstoptime`),
  KEY `radacct_framedipaddress_index` (`framedipaddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radacct`
--


--
-- Table structure for table `radcheck`
--

CREATE TABLE `radcheck` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `attribute` varchar(64) NOT NULL DEFAULT '',
  `op` char(2) NOT NULL DEFAULT '==',
  `value` varchar(253) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `radcheck_username_idx` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radcheck`
--


--
-- Table structure for table `radgroupcheck`
--

CREATE TABLE `radgroupcheck` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `attribute` varchar(64) NOT NULL DEFAULT '',
  `op` char(2) NOT NULL DEFAULT '==',
  `value` varchar(253) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `radgroupcheck_groupname_idx` (`groupname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radgroupcheck`
--


--
-- Table structure for table `radgroupreply`
--

CREATE TABLE `radgroupreply` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `attribute` varchar(64) NOT NULL DEFAULT '',
  `op` char(2) NOT NULL DEFAULT '=',
  `value` varchar(253) NOT NULL DEFAULT '',
  `plan_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `radgroupreply_groupname_idx` (`groupname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radgroupreply`
--


--
-- Table structure for table `radpostauth`
--

CREATE TABLE `radpostauth` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `pass` varchar(64) NOT NULL DEFAULT '',
  `reply` varchar(32) NOT NULL DEFAULT '',
  `authdate` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
  `class` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radpostauth`
--


--
-- Table structure for table `radreply`
--

CREATE TABLE `radreply` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `attribute` varchar(64) NOT NULL DEFAULT '',
  `op` char(2) NOT NULL DEFAULT '=',
  `value` varchar(253) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `radreply_username_idx` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radreply`
--


--
-- Table structure for table `radusergroup`
--

CREATE TABLE `radusergroup` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `priority` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `radusergroup_username_idx` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `radusergroup`
--


--
-- Table structure for table `routers`
--

CREATE TABLE `routers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `type` enum('Static IP','VPN','RADIUS') NOT NULL DEFAULT 'Static IP',
  `port` int(11) NOT NULL DEFAULT 8728,
  `api_ssl` tinyint(1) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `coordinates` varchar(255) DEFAULT NULL,
  `coverage` int(11) NOT NULL DEFAULT 0 COMMENT 'Coverage radius in meters',
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('Online','Offline','Maintenance') NOT NULL DEFAULT 'Offline',
  `last_seen` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `coverage_radius` int(11) DEFAULT NULL COMMENT 'Coverage radius in meters',
  PRIMARY KEY (`id`),
  UNIQUE KEY `routers_name_unique` (`name`),
  KEY `routers_name_index` (`name`),
  KEY `routers_type_index` (`type`),
  KEY `routers_enabled_index` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `routers`
--


--
-- Table structure for table `service_extension_audit`
--

CREATE TABLE `service_extension_audit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `extension_days` int(11) NOT NULL,
  `previous_expiry` datetime NOT NULL,
  `new_expiry` datetime NOT NULL,
  `processed_by` bigint(20) unsigned NOT NULL,
  `processed_by_name` varchar(100) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `metadata` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `service_extension_audit_customer_id_index` (`customer_id`),
  KEY `service_extension_audit_processed_by_index` (`processed_by`),
  KEY `service_extension_audit_created_at_index` (`created_at`),
  CONSTRAINT `service_extension_audit_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `service_extension_audit_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_extension_audit`
--


--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` longtext DEFAULT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'string',
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`),
  KEY `settings_key_index` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--


--
-- Table structure for table `static_ip_assignments`
--

CREATE TABLE `static_ip_assignments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `ip_pool_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `status` enum('ASSIGNED','SUSPENDED','RELEASED','EXPIRED') NOT NULL DEFAULT 'ASSIGNED',
  `upload_speed` int(11) DEFAULT NULL,
  `download_speed` int(11) DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT NULL,
  `suspended_at` timestamp NULL DEFAULT NULL,
  `resumed_at` timestamp NULL DEFAULT NULL,
  `released_at` timestamp NULL DEFAULT NULL,
  `last_reconciled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `static_ip_assignments_ip_address_ip_pool_id_unique` (`ip_address`,`ip_pool_id`),
  KEY `static_ip_assignments_plan_id_foreign` (`plan_id`),
  KEY `static_ip_assignments_ip_pool_id_foreign` (`ip_pool_id`),
  KEY `static_ip_assignments_customer_id_index` (`customer_id`),
  KEY `static_ip_assignments_status_index` (`status`),
  CONSTRAINT `static_ip_assignments_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `static_ip_assignments_ip_pool_id_foreign` FOREIGN KEY (`ip_pool_id`) REFERENCES `ip_pools` (`id`) ON DELETE CASCADE,
  CONSTRAINT `static_ip_assignments_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_assignments`
--


--
-- Table structure for table `static_ip_billing`
--

CREATE TABLE `static_ip_billing` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `status` enum('paid','unpaid','overdue') NOT NULL DEFAULT 'unpaid',
  `payment_method` enum('cash','mpesa','bank','card','other') DEFAULT NULL,
  `transaction_ref` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `static_ip_billing_customer_id_index` (`customer_id`),
  KEY `static_ip_billing_plan_id_index` (`plan_id`),
  KEY `static_ip_billing_status_index` (`status`),
  KEY `static_ip_billing_period_start_index` (`period_start`),
  KEY `static_ip_billing_period_end_index` (`period_end`),
  KEY `static_ip_billing_transaction_ref_index` (`transaction_ref`),
  CONSTRAINT `static_ip_billing_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `static_ip_customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `static_ip_billing_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `static_ip_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_billing`
--


--
-- Table structure for table `static_ip_customers`
--

CREATE TABLE `static_ip_customers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `address` varchar(150) DEFAULT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `pool_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_ip` varchar(45) DEFAULT NULL,
  `static_ip_id` bigint(20) unsigned DEFAULT NULL,
  `bandwidth_limit` varchar(20) DEFAULT NULL,
  `status` enum('active','inactive','pending','expired') DEFAULT 'pending',
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expiry_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `static_ip_customers_router_id_index` (`router_id`),
  KEY `static_ip_customers_plan_id_index` (`plan_id`),
  KEY `static_ip_customers_pool_id_index` (`pool_id`),
  KEY `static_ip_customers_static_ip_id_index` (`static_ip_id`),
  KEY `static_ip_customers_status_index` (`status`),
  KEY `static_ip_customers_assigned_ip_index` (`assigned_ip`),
  KEY `static_ip_customers_phone_index` (`phone`),
  KEY `static_ip_customers_email_index` (`email`),
  CONSTRAINT `static_ip_customers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `static_ip_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `static_ip_customers_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `static_ip_pools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `static_ip_customers_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `static_ip_customers_static_ip_id_foreign` FOREIGN KEY (`static_ip_id`) REFERENCES `static_ips` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_customers`
--


--
-- Table structure for table `static_ip_plans`
--

CREATE TABLE `static_ip_plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(100) NOT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `ip_pool_id` bigint(20) unsigned DEFAULT NULL,
  `download_speed` varchar(20) NOT NULL,
  `upload_speed` varchar(20) NOT NULL,
  `price_monthly` decimal(10,2) NOT NULL,
  `validity_days` int(11) NOT NULL DEFAULT 30,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `static_ip_plans_router_id_index` (`router_id`),
  KEY `static_ip_plans_ip_pool_id_index` (`ip_pool_id`),
  KEY `static_ip_plans_status_index` (`status`),
  CONSTRAINT `static_ip_plans_ip_pool_id_foreign` FOREIGN KEY (`ip_pool_id`) REFERENCES `static_ip_pools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `static_ip_plans_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_plans`
--


--
-- Table structure for table `static_ip_pools`
--

CREATE TABLE `static_ip_pools` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pool_name` varchar(100) NOT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `ip_range_start` varchar(45) NOT NULL,
  `ip_range_end` varchar(45) NOT NULL,
  `subnet_mask` varchar(20) DEFAULT NULL,
  `gateway` varchar(45) DEFAULT NULL,
  `total_ips` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `static_ip_pools_router_id_index` (`router_id`),
  KEY `static_ip_pools_status_index` (`status`),
  CONSTRAINT `static_ip_pools_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_pools`
--


--
-- Table structure for table `static_ip_routers`
--

CREATE TABLE `static_ip_routers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `api_port` int(11) NOT NULL DEFAULT 8728,
  `username` varchar(50) NOT NULL,
  `password` text NOT NULL,
  `location` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `static_ip_routers_name_unique` (`name`),
  KEY `static_ip_routers_status_index` (`status`),
  KEY `static_ip_routers_ip_address_index` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_routers`
--


--
-- Table structure for table `static_ip_speed_changes`
--

CREATE TABLE `static_ip_speed_changes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `static_ip_assignment_id` bigint(20) unsigned NOT NULL,
  `old_upload_speed` int(11) NOT NULL,
  `old_download_speed` int(11) NOT NULL,
  `new_upload_speed` int(11) NOT NULL,
  `new_download_speed` int(11) NOT NULL,
  `changed_by` bigint(20) unsigned NOT NULL,
  `changed_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `static_ip_speed_changes_changed_by_foreign` (`changed_by`),
  KEY `static_ip_speed_changes_static_ip_assignment_id_index` (`static_ip_assignment_id`),
  CONSTRAINT `static_ip_speed_changes_changed_by_foreign` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`),
  CONSTRAINT `static_ip_speed_changes_static_ip_assignment_id_foreign` FOREIGN KEY (`static_ip_assignment_id`) REFERENCES `static_ip_assignments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ip_speed_changes`
--


--
-- Table structure for table `static_ips`
--

CREATE TABLE `static_ips` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` bigint(20) unsigned NOT NULL,
  `router_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `assigned_to_customer_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('available','assigned','reserved') NOT NULL DEFAULT 'available',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `static_ips_ip_address_unique` (`ip_address`),
  KEY `static_ips_pool_id_index` (`pool_id`),
  KEY `static_ips_router_id_index` (`router_id`),
  KEY `static_ips_plan_id_index` (`plan_id`),
  KEY `static_ips_assigned_to_customer_id_index` (`assigned_to_customer_id`),
  KEY `static_ips_status_index` (`status`),
  KEY `static_ips_ip_address_index` (`ip_address`),
  CONSTRAINT `static_ips_assigned_to_customer_id_foreign` FOREIGN KEY (`assigned_to_customer_id`) REFERENCES `static_ip_customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `static_ips_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `static_ip_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `static_ips_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `static_ip_pools` (`id`) ON DELETE CASCADE,
  CONSTRAINT `static_ips_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `static_ips`
--


--
-- Table structure for table `staticip_plan_pools`
--

CREATE TABLE `staticip_plan_pools` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `plan_id` bigint(20) unsigned NOT NULL,
  `pool_id` bigint(20) unsigned NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `staticip_plan_pools_plan_id_pool_id_unique` (`plan_id`,`pool_id`),
  KEY `staticip_plan_pools_plan_id_index` (`plan_id`),
  KEY `staticip_plan_pools_pool_id_index` (`pool_id`),
  CONSTRAINT `staticip_plan_pools_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `staticip_plan_pools_pool_id_foreign` FOREIGN KEY (`pool_id`) REFERENCES `ip_pools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `staticip_plan_pools`
--


--
-- Table structure for table `subscriptions`
--

CREATE TABLE `subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `router_id` bigint(20) unsigned DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL COMMENT 'Service username (Hotspot/PPPoE)',
  `password` varchar(100) DEFAULT NULL COMMENT 'Service password',
  `type` enum('Hotspot','PPPoE','Static IP','VPN') NOT NULL DEFAULT 'Hotspot' COMMENT 'Service type',
  `ip_pool_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('active','expired','cancelled') NOT NULL DEFAULT 'active',
  `activated_at` timestamp NULL DEFAULT NULL,
  `activated_by` bigint(20) unsigned DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL COMMENT 'When subscription was cancelled',
  `cancelled_by` bigint(20) unsigned DEFAULT NULL,
  `cancellation_reason` text DEFAULT NULL COMMENT 'Reason for cancellation',
  `auto_renew` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Auto-renew subscription on expiry',
  `validity` int(11) DEFAULT NULL COMMENT 'Validity period value',
  `validity_unit` enum('Mins','Hrs','Days','Weeks','Months','Period') DEFAULT NULL COMMENT 'Validity period unit',
  `price_paid` decimal(10,2) NOT NULL,
  `payment_method` enum('balance','mpesa','paypal','cash','other') NOT NULL DEFAULT 'balance',
  `transaction_id` varchar(255) DEFAULT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `data_used` bigint(20) NOT NULL DEFAULT 0,
  `data_limit` bigint(20) DEFAULT NULL COMMENT 'Data limit in bytes',
  `time_used` int(11) NOT NULL DEFAULT 0,
  `time_limit` int(11) DEFAULT NULL COMMENT 'Time limit in minutes',
  `synced_to_router` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether service is synced to MikroTik router',
  `synced_at` timestamp NULL DEFAULT NULL COMMENT 'Last sync timestamp',
  `sync_error` text DEFAULT NULL COMMENT 'Last sync error message if any',
  `admin_notes` text DEFAULT NULL COMMENT 'Admin notes for this subscription',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subscriptions_router_id_foreign` (`router_id`),
  KEY `subscriptions_customer_id_index` (`customer_id`),
  KEY `subscriptions_plan_id_index` (`plan_id`),
  KEY `subscriptions_status_index` (`status`),
  KEY `subscriptions_expires_at_index` (`expires_at`),
  KEY `subscriptions_ip_pool_id_foreign` (`ip_pool_id`),
  KEY `subscriptions_ip_address_id_foreign` (`ip_address_id`),
  KEY `subscriptions_invoice_id_foreign` (`invoice_id`),
  KEY `subscriptions_activated_by_foreign` (`activated_by`),
  KEY `subscriptions_cancelled_by_foreign` (`cancelled_by`),
  KEY `subscriptions_username_index` (`username`),
  KEY `subscriptions_type_index` (`type`),
  KEY `subscriptions_synced_to_router_index` (`synced_to_router`),
  KEY `subscriptions_auto_renew_index` (`auto_renew`),
  KEY `subscriptions_activated_at_index` (`activated_at`),
  KEY `subscriptions_cancelled_at_index` (`cancelled_at`),
  CONSTRAINT `subscriptions_activated_by_foreign` FOREIGN KEY (`activated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `subscriptions_cancelled_by_foreign` FOREIGN KEY (`cancelled_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `subscriptions_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `subscriptions_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `subscriptions_ip_address_id_foreign` FOREIGN KEY (`ip_address_id`) REFERENCES `ip_pool_addresses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `subscriptions_ip_pool_id_foreign` FOREIGN KEY (`ip_pool_id`) REFERENCES `ip_pools` (`id`) ON DELETE SET NULL,
  CONSTRAINT `subscriptions_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `subscriptions_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `subscriptions`
--


--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `value` longtext DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'string',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `system_settings_key_unique` (`key`),
  KEY `system_settings_key_index` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_settings`
--


--
-- Table structure for table `tbl_logs`
--

CREATE TABLE `tbl_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `tenant_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tbl_logs_type_index` (`type`),
  KEY `tbl_logs_user_id_index` (`user_id`),
  KEY `tbl_logs_created_at_index` (`created_at`),
  KEY `idx_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_logs`
--


--
-- Table structure for table `tbl_message_logs`
--

CREATE TABLE `tbl_message_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message_type` varchar(20) NOT NULL,
  `recipient` varchar(255) NOT NULL,
  `message_content` text NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `error_message` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tbl_message_logs_message_type_index` (`message_type`),
  KEY `tbl_message_logs_status_index` (`status`),
  KEY `tbl_message_logs_sent_at_index` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_message_logs`
--


--
-- Table structure for table `themes`
--

CREATE TABLE `themes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `primary_color` varchar(255) NOT NULL DEFAULT '#3B82F6',
  `secondary_color` varchar(255) NOT NULL DEFAULT '#10B981',
  `background_color` varchar(255) NOT NULL DEFAULT '#F3F4F6',
  `text_color` varchar(255) NOT NULL DEFAULT '#1F2937',
  `logo_url` varchar(255) DEFAULT NULL,
  `favicon_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `themes_name_unique` (`name`),
  KEY `themes_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `themes`
--


--
-- Table structure for table `ticket_replies`
--

CREATE TABLE `ticket_replies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `user_type` enum('customer','admin') NOT NULL DEFAULT 'customer',
  `message` text NOT NULL,
  `attachment_url` varchar(255) DEFAULT NULL,
  `is_internal` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ticket_replies_ticket_id_index` (`ticket_id`),
  KEY `ticket_replies_user_id_index` (`user_id`),
  KEY `ticket_replies_customer_id_index` (`customer_id`),
  KEY `ticket_replies_user_type_index` (`user_type`),
  KEY `ticket_replies_created_at_index` (`created_at`),
  CONSTRAINT `ticket_replies_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `ticket_replies_ticket_id_foreign` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ticket_replies_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ticket_replies`
--


--
-- Table structure for table `tickets`
--

CREATE TABLE `tickets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_number` varchar(50) NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('open','in_progress','waiting_customer','waiting_admin','resolved','closed','reopened') NOT NULL DEFAULT 'open',
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `category` enum('billing','technical','account','service','complaint','other') NOT NULL DEFAULT 'other',
  `attachment_url` varchar(255) DEFAULT NULL,
  `reply_count` int(11) NOT NULL DEFAULT 0,
  `first_response_at` timestamp NULL DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tickets_ticket_number_unique` (`ticket_number`),
  KEY `tickets_created_by_foreign` (`created_by`),
  KEY `tickets_ticket_number_index` (`ticket_number`),
  KEY `tickets_customer_id_index` (`customer_id`),
  KEY `tickets_assigned_to_index` (`assigned_to`),
  KEY `tickets_status_index` (`status`),
  KEY `tickets_priority_index` (`priority`),
  KEY `tickets_category_index` (`category`),
  KEY `tickets_created_at_index` (`created_at`),
  CONSTRAINT `tickets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tickets_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tickets_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tickets`
--


--
-- Table structure for table `transaction_approvals`
--

CREATE TABLE `transaction_approvals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `from_customer_id` bigint(20) unsigned NOT NULL,
  `to_customer_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `reference` varchar(64) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `requested_by` bigint(20) unsigned DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approval_notes` text DEFAULT NULL,
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `approved_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_approvals_reference_unique` (`reference`),
  KEY `transaction_approvals_from_customer_id_index` (`from_customer_id`),
  KEY `transaction_approvals_to_customer_id_index` (`to_customer_id`),
  KEY `transaction_approvals_status_index` (`status`),
  KEY `transaction_approvals_created_at_index` (`created_at`),
  KEY `transaction_approvals_requested_by_foreign` (`requested_by`),
  KEY `transaction_approvals_approved_by_foreign` (`approved_by`),
  CONSTRAINT `transaction_approvals_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transaction_approvals_from_customer_id_foreign` FOREIGN KEY (`from_customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transaction_approvals_requested_by_foreign` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transaction_approvals_to_customer_id_foreign` FOREIGN KEY (`to_customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `transaction_approvals`
--


--
-- Table structure for table `transaction_locks`
--

CREATE TABLE `transaction_locks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fingerprint` varchar(64) NOT NULL,
  `locked_at` datetime NOT NULL,
  `locked_until` datetime NOT NULL,
  `admin_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_locks_fingerprint_unique` (`fingerprint`),
  KEY `transaction_locks_fingerprint_index` (`fingerprint`),
  KEY `transaction_locks_locked_until_index` (`locked_until`),
  KEY `transaction_locks_admin_id_foreign` (`admin_id`),
  CONSTRAINT `transaction_locks_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `transaction_locks`
--


--
-- Table structure for table `transaction_tracking`
--

CREATE TABLE `transaction_tracking` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `fingerprint` varchar(64) NOT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `method` varchar(100) NOT NULL,
  `gateway` varchar(100) NOT NULL,
  `admin_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_tracking_fingerprint_unique` (`fingerprint`),
  KEY `transaction_tracking_fingerprint_index` (`fingerprint`),
  KEY `transaction_tracking_customer_id_index` (`customer_id`),
  KEY `transaction_tracking_created_at_index` (`created_at`),
  KEY `transaction_tracking_plan_id_foreign` (`plan_id`),
  KEY `transaction_tracking_admin_id_foreign` (`admin_id`),
  CONSTRAINT `transaction_tracking_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transaction_tracking_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transaction_tracking_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `transaction_tracking`
--


--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `subscription_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('credit','debit') NOT NULL DEFAULT 'debit',
  `amount` decimal(10,2) NOT NULL,
  `balance_before` decimal(10,2) NOT NULL DEFAULT 0.00,
  `balance_after` decimal(10,2) NOT NULL DEFAULT 0.00,
  `description` varchar(255) NOT NULL,
  `payment_method` enum('balance','mpesa','paypal','cash','other') DEFAULT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled') NOT NULL DEFAULT 'completed',
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transactions_subscription_id_foreign` (`subscription_id`),
  KEY `transactions_customer_id_index` (`customer_id`),
  KEY `transactions_type_index` (`type`),
  KEY `transactions_status_index` (`status`),
  KEY `transactions_created_at_index` (`created_at`),
  CONSTRAINT `transactions_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `transactions_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `transactions`
--


--
-- Table structure for table `user_recharges`
--

CREATE TABLE `user_recharges` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `username` varchar(32) NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `namebp` varchar(40) NOT NULL,
  `recharged_on` date NOT NULL,
  `recharged_time` time NOT NULL DEFAULT '00:00:00',
  `expiration` date NOT NULL,
  `time` time NOT NULL,
  `status` varchar(20) NOT NULL,
  `method` varchar(128) NOT NULL DEFAULT '',
  `routers` varchar(32) NOT NULL,
  `type` varchar(15) NOT NULL,
  `admin_id` bigint(20) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `user_recharges_customer_id_index` (`customer_id`),
  KEY `user_recharges_username_index` (`username`),
  KEY `user_recharges_recharged_on_index` (`recharged_on`),
  KEY `user_recharges_expiration_index` (`expiration`),
  KEY `user_recharges_plan_id_foreign` (`plan_id`),
  KEY `user_recharges_admin_id_foreign` (`admin_id`),
  CONSTRAINT `user_recharges_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`),
  CONSTRAINT `user_recharges_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_recharges_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_recharges`
--


--
-- Table structure for table `user_sessions`
--

CREATE TABLE `user_sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `user_agent` text NOT NULL,
  `last_activity` timestamp NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_sessions_user_id_index` (`user_id`),
  KEY `user_sessions_is_active_index` (`is_active`),
  KEY `user_sessions_last_activity_index` (`last_activity`),
  CONSTRAINT `user_sessions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_sessions`
--


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `role` varchar(255) NOT NULL DEFAULT 'Admin',
  `tenant_id` bigint(20) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login` timestamp NULL DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_username_unique` (`username`),
  KEY `users_tenant_id_index` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--


--
-- Table structure for table `vouchers`
--

CREATE TABLE `vouchers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `plan_id` bigint(20) unsigned DEFAULT NULL,
  `router_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('Static IP','VPN') NOT NULL DEFAULT 'Static IP',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `validity_days` int(11) DEFAULT NULL,
  `validity_hours` int(11) DEFAULT NULL,
  `data_limit_mb` bigint(20) DEFAULT NULL,
  `speed_limit_mbps` int(11) DEFAULT NULL,
  `status` enum('available','activated','expired','used') NOT NULL DEFAULT 'available',
  `activated_by` bigint(20) unsigned DEFAULT NULL,
  `activated_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `batch_id` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vouchers_code_unique` (`code`),
  KEY `vouchers_plan_id_foreign` (`plan_id`),
  KEY `vouchers_router_id_foreign` (`router_id`),
  KEY `vouchers_code_index` (`code`),
  KEY `vouchers_status_index` (`status`),
  KEY `vouchers_batch_id_index` (`batch_id`),
  KEY `vouchers_type_index` (`type`),
  KEY `vouchers_activated_by_index` (`activated_by`),
  KEY `vouchers_created_by_index` (`created_by`),
  CONSTRAINT `vouchers_activated_by_foreign` FOREIGN KEY (`activated_by`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `vouchers_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `vouchers_plan_id_foreign` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `vouchers_router_id_foreign` FOREIGN KEY (`router_id`) REFERENCES `routers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `vouchers`
--


--
-- Table structure for table `widgets`
--

CREATE TABLE `widgets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `widget_type` varchar(255) NOT NULL,
  `page` varchar(255) NOT NULL,
  `position` int(11) NOT NULL DEFAULT 1,
  `width` varchar(255) NOT NULL DEFAULT 'full',
  `height` varchar(255) NOT NULL DEFAULT 'auto',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `widgets_page_index` (`page`),
  KEY `widgets_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `widgets`
--



