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

START TRANSACTION;



--
-- Database: `xyzcpan2_Xyz`
--

DELIMITER $$
--
-- Procedures
--
$$

$$

$$

DELIMITER ;



--
-- Dumping data for table `admins`
--

INSERT INTO `admins` (`id`, `owner_id`, `telegram_id`, `admin_username`, `admin_first_name`, `admin_last_name`, `email`, `password_hash`, `reserve_id`, `bot_name`, `bot_username`, `bot_token`, `server_name`, `server_ip`, `server_port`, `domain_name`, `database_name`, `database_host`, `database_port`, `database_user`, `database_password`, `payment_gateway`, `payment_api_key`, `payment_api_secret`, `payment_merchant_id`, `payment_webhook_secret`, `payment_currency`, `recharge_status`, `recharge_plan`, `recharge_amount`, `recharge_date`, `recharge_expiry`, `auto_renew`, `monthly_key_limit`, `keys_generated_this_month`, `total_keys_generated`, `status`, `settings`, `created_at`, `last_login`, `updated_at`) VALUES
(1, 1, 987654321, 'admin', 'Admin User', NULL, NULL, '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'TEST-001', 'Test Bot', '@test_bot', '123456:ABCdefGHIjklMNOpqrsTUVwxyz', NULL, NULL, 443, NULL, NULL, 'localhost', 3306, NULL, NULL, 'razorpay', 'test_key', 'test_secret', NULL, NULL, 'INR', 'inactive', NULL, NULL, NULL, NULL, 0, 1000, 0, 0, 'active', NULL, '2026-02-22 14:35:46', NULL, '2026-02-22 14:35:46');

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

--
-- Table structure for table `admin_activity_log`
--

CREATE TABLE `admin_activity_log` (
  `id` int NOT NULL,
  `admin_id` int NOT NULL,
  `reserve_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activity_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activity_details` json NOT NULL,
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `performed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Admin actions log';

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

--
-- Table structure for table `admin_login_history`
--

CREATE TABLE `admin_login_history` (
  `id` int NOT NULL,
  `admin_id` int NOT NULL,
  `login_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `login_successful` tinyint(1) DEFAULT '1',
  `failure_reason` text COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Admin login attempts';

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

--
-- Table structure for table `api_keys`
--

CREATE TABLE `api_keys` (
  `id` int NOT NULL,
  `owner_id` int DEFAULT NULL,
  `admin_id` int DEFAULT NULL,
  `api_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_secret` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `api_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permissions` json DEFAULT NULL,
  `rate_limit` int DEFAULT '100',
  `last_used` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API keys for external integrations';

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

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

CREATE TABLE `audit_logs` (
  `id` int NOT NULL,
  `actor_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'owner/admin/system',
  `actor_id` int DEFAULT NULL,
  `actor_telegram_id` bigint DEFAULT NULL,
  `action` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entity_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Audit trail for sensitive actions';

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

--
-- Table structure for table `backup_history`
--

CREATE TABLE `backup_history` (
  `id` int NOT NULL,
  `backup_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `backup_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'manual/automated',
  `backup_size` bigint DEFAULT NULL,
  `file_path` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `included_tables` json DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'completed' COMMENT 'completed/failed',
  `started_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `created_by` int DEFAULT NULL,
  `restored_at` timestamp NULL DEFAULT NULL,
  `restored_by` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Record of database backups';

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

--
-- Table structure for table `blocked_users_global`
--

CREATE TABLE `blocked_users_global` (
  `id` int NOT NULL,
  `telegram_id` bigint NOT NULL,
  `block_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'global' COMMENT 'global or per-bot',
  `reserve_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If per-bot, which bot',
  `blocked_by` int DEFAULT NULL,
  `block_reason` text COLLATE utf8mb4_unicode_ci,
  `blocked_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Globally blocked users';

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

--
-- Table structure for table `broadcasts_global`
--

CREATE TABLE `broadcasts_global` (
  `id` int NOT NULL,
  `broadcast_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_id` int DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_bots` json DEFAULT NULL COMMENT 'List of bot reserve_ids or null for all',
  `scheduled_for` timestamp NULL DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total_recipients` int DEFAULT NULL,
  `delivered_count` int DEFAULT NULL,
  `failed_count` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Broadcasts sent by owner to all bots';

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

--
-- Table structure for table `notification_logs`
--

CREATE TABLE `notification_logs` (
  `id` int NOT NULL,
  `admin_id` int DEFAULT NULL,
  `owner_id` int DEFAULT NULL,
  `notification_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `channel` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `recipient_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recipient_id` bigint DEFAULT NULL,
  `subject` text COLLATE utf8mb4_unicode_ci,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'sent' COMMENT 'sent/failed',
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `sent_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `metadata` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log of sent notifications';

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

--
-- Table structure for table `notification_templates`
--

CREATE TABLE `notification_templates` (
  `id` int NOT NULL,
  `owner_id` int DEFAULT NULL,
  `template_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` text COLLATE utf8mb4_unicode_ci,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `variables` json DEFAULT NULL,
  `channels` json DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Templates for notifications';



--
-- Dumping data for table `owners`
--

INSERT INTO `owners` (`id`, `telegram_id`, `username`, `first_name`, `last_name`, `email`, `password_hash`, `two_factor_enabled`, `two_factor_secret`, `notification_preferences`, `language`, `created_at`, `last_login`, `updated_at`) VALUES
(1, 123456789, 'owner', 'System Owner', NULL, NULL, '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 0, NULL, NULL, 'en', '2026-02-22 14:35:46', NULL, '2026-02-22 14:35:46');

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

--
-- Table structure for table `recharge_history`
--

CREATE TABLE `recharge_history` (
  `id` int NOT NULL,
  `admin_id` int NOT NULL,
  `owner_id` int NOT NULL,
  `plan_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_date` timestamp NOT NULL,
  `end_date` timestamp NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'completed' COMMENT 'completed/refunded/failed',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='History of admin recharges';

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

--
-- Table structure for table `recharge_plans`
--

CREATE TABLE `recharge_plans` (
  `id` int NOT NULL,
  `plan_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `duration_days` int NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `key_limit` int DEFAULT '1000',
  `description` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Recharge plans for admins';

--
-- Dumping data for table `recharge_plans`
--

INSERT INTO `recharge_plans` (`id`, `plan_name`, `duration_days`, `price`, `key_limit`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Monthly', 30, 200.00, 1000, 'Monthly recharge plan', 1, '2026-02-22 14:35:44', '2026-02-22 14:35:44'),
(2, '6 Months', 180, 900.00, 6000, 'Half year plan with savings', 1, '2026-02-22 14:35:44', '2026-02-22 14:35:44'),
(3, 'Yearly', 365, 1700.00, 12000, 'Yearly plan with best value', 1, '2026-02-22 14:35:44', '2026-02-22 14:35:44');

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

--
-- Table structure for table `server_alert_rules`
--

CREATE TABLE `server_alert_rules` (
  `id` int NOT NULL,
  `admin_id` int DEFAULT NULL,
  `owner_id` int DEFAULT NULL,
  `rule_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `metric` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `threshold` decimal(5,2) NOT NULL,
  `operator` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `duration` int DEFAULT '5',
  `notify_channels` json DEFAULT NULL,
  `notify_owner` tinyint(1) DEFAULT '0',
  `notify_admin` tinyint(1) DEFAULT '1',
  `auto_restart` tinyint(1) DEFAULT '0',
  `auto_scale` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Alert rules for monitoring';

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

--
-- Table structure for table `server_health_logs`
--

CREATE TABLE `server_health_logs` (
  `id` int NOT NULL,
  `admin_id` int NOT NULL,
  `reserve_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `server_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cpu_usage` decimal(5,2) DEFAULT NULL,
  `ram_usage` decimal(5,2) DEFAULT NULL,
  `disk_usage` decimal(5,2) DEFAULT NULL,
  `network_in` decimal(10,2) DEFAULT NULL,
  `network_out` decimal(10,2) DEFAULT NULL,
  `bot_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `webhook_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `database_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `redis_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_time` int DEFAULT NULL,
  `checked_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Periodic server health snapshots';

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

--
-- Table structure for table `server_incidents`
--

CREATE TABLE `server_incidents` (
  `id` int NOT NULL,
  `admin_id` int DEFAULT NULL,
  `reserve_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `incident_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `severity` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_time` timestamp NOT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `duration` int DEFAULT NULL,
  `affected_users` int DEFAULT NULL,
  `root_cause` text COLLATE utf8mb4_unicode_ci,
  `resolution` text COLLATE utf8mb4_unicode_ci,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'open' COMMENT 'open/resolved',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Record of incidents';

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

--
-- Table structure for table `system_config`
--

CREATE TABLE `system_config` (
  `id` int NOT NULL,
  `config_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `config_value` text COLLATE utf8mb4_unicode_ci,
  `config_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'string' COMMENT 'string/boolean/number/json',
  `description` text COLLATE utf8mb4_unicode_ci,
  `updated_by` int DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='System-wide settings';

--
-- Dumping data for table `system_config`
--

INSERT INTO `system_config` (`id`, `config_key`, `config_value`, `config_type`, `description`, `updated_by`, `updated_at`) VALUES
(1, 'system_name', 'Telegram Multi-Tenant Bot System', 'string', 'Name of the system', NULL, '2026-02-22 14:35:44'),
(2, 'version', '1.0.0', 'string', 'System version', NULL, '2026-02-22 14:35:44'),
(3, 'maintenance_mode', 'false', 'boolean', 'Put system in maintenance mode', NULL, '2026-02-22 14:35:44'),
(4, 'default_language', 'en', 'string', 'Default language', NULL, '2026-02-22 14:35:44'),
(5, 'currency', 'INR', 'string', 'Default currency', NULL, '2026-02-22 14:35:44'),
(6, 'timezone', 'Asia/Kolkata', 'string', 'System timezone', NULL, '2026-02-22 14:35:44'),
(7, 'max_admins_per_owner', '1000', 'number', 'Maximum admins per owner', NULL, '2026-02-22 14:35:44'),
(8, 'max_keys_per_batch', '10000', 'number', 'Maximum keys per batch upload', NULL, '2026-02-22 14:35:44'),
(9, 'session_timeout', '3600', 'number', 'Admin session timeout in seconds', NULL, '2026-02-22 14:35:44'),
(10, 'backup_enabled', 'true', 'boolean', 'Enable automatic backups', NULL, '2026-02-22 14:35:44'),
(11, 'backup_frequency', 'daily', 'string', 'Backup frequency', NULL, '2026-02-22 14:35:44'),
(12, 'log_retention_days', '90', 'number', 'Days to keep logs', NULL, '2026-02-22 14:35:44'),
(13, 'api_rate_limit', '100', 'number', 'API rate limit per minute', NULL, '2026-02-22 14:35:44'),
(14, 'user_activity_tracking', 'true', 'boolean', 'Track user activities', NULL, '2026-02-22 14:35:44'),
(15, 'admin_activity_tracking', 'true', 'boolean', 'Track admin activities', NULL, '2026-02-22 14:35:44');

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

--
-- Table structure for table `tenant_apk_versions`
--

CREATE TABLE `tenant_apk_versions` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `version_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `version_code` int NOT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` bigint NOT NULL,
  `file_hash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `release_notes` text COLLATE utf8mb4_unicode_ci,
  `min_sdk` int DEFAULT NULL,
  `target_sdk` int DEFAULT NULL,
  `download_count` int DEFAULT '0',
  `unique_downloads` int DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `is_mandatory` tinyint(1) DEFAULT '0',
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `uploaded_by` int DEFAULT NULL,
  `released_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='APK versions for each bot';

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

--
-- Table structure for table `tenant_broadcasts`
--

CREATE TABLE `tenant_broadcasts` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `broadcast_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sender_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'admin/system',
  `sender_id` int NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `preview_text` text COLLATE utf8mb4_unicode_ci,
  `media_url` text COLLATE utf8mb4_unicode_ci,
  `buttons` json DEFAULT NULL,
  `target_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'all/premium/active/inactive/custom',
  `target_filters` json DEFAULT NULL,
  `scheduled_for` timestamp NULL DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'draft' COMMENT 'draft/scheduled/sending/completed/failed/cancelled',
  `priority` int DEFAULT '1',
  `total_recipients` int DEFAULT NULL,
  `delivered_count` int DEFAULT NULL,
  `failed_count` int DEFAULT NULL,
  `opened_count` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Broadcast messages';

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

--
-- Table structure for table `tenant_broadcast_recipients`
--

CREATE TABLE `tenant_broadcast_recipients` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `broadcast_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_telegram_id` bigint NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'pending' COMMENT 'pending/delivered/failed/opened',
  `delivered_at` timestamp NULL DEFAULT NULL,
  `opened_at` timestamp NULL DEFAULT NULL,
  `failed_reason` text COLLATE utf8mb4_unicode_ci,
  `retry_count` int DEFAULT '0',
  `last_retry` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Per-user broadcast delivery status';

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

--
-- Table structure for table `tenant_keys`
--

CREATE TABLE `tenant_keys` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `key_value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The key string',
  `key_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'stored/generated',
  `duration` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '1d,3d,7d,30d,60d',
  `price` decimal(10,2) NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'available' COMMENT 'available/sold/blocked/expired',
  `is_used` tinyint(1) DEFAULT '0',
  `used_by` bigint DEFAULT NULL COMMENT 'Telegram ID of user who used it',
  `used_at` timestamp NULL DEFAULT NULL,
  `issued_at` timestamp NULL DEFAULT NULL COMMENT 'When assigned to user',
  `expires_at` timestamp NULL DEFAULT NULL COMMENT 'Expiration date',
  `extended_count` int DEFAULT '0',
  `last_extended_at` timestamp NULL DEFAULT NULL,
  `generation_rule_id` int DEFAULT NULL,
  `generation_date` timestamp NULL DEFAULT NULL,
  `batch_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uploaded_at` timestamp NULL DEFAULT NULL,
  `is_blocked` tinyint(1) DEFAULT '0',
  `block_reason` text COLLATE utf8mb4_unicode_ci,
  `blocked_at` timestamp NULL DEFAULT NULL,
  `blocked_by` int DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Keys inventory per tenant';

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

--
-- Table structure for table `tenant_key_alert_config`
--

CREATE TABLE `tenant_key_alert_config` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `duration` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `threshold` int DEFAULT '100',
  `notify_admin` tinyint(1) DEFAULT '1',
  `notify_owner` tinyint(1) DEFAULT '0',
  `auto_generate` tinyint(1) DEFAULT '0',
  `last_alert_sent` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Low stock alert settings';

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

--
-- Table structure for table `tenant_key_batches`
--

CREATE TABLE `tenant_key_batches` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `batch_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_size` int DEFAULT NULL,
  `total_keys` int NOT NULL,
  `valid_keys` int DEFAULT '0',
  `invalid_keys` int DEFAULT '0',
  `duplicate_keys` int DEFAULT '0',
  `keys_by_duration` json DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'processing' COMMENT 'processing/completed/failed',
  `error_log` text COLLATE utf8mb4_unicode_ci,
  `uploaded_by` int DEFAULT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bulk upload batches';

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

--
-- Table structure for table `tenant_key_generation_rules`
--

CREATE TABLE `tenant_key_generation_rules` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `rule_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pattern` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Key pattern with variables',
  `description` text COLLATE utf8mb4_unicode_ci,
  `prefix` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `include_date` tinyint(1) DEFAULT '1',
  `date_format` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'YYYYMMDD',
  `random_length` int DEFAULT '8',
  `random_charset` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  `exclude_similar` tinyint(1) DEFAULT '1',
  `checksum_enabled` tinyint(1) DEFAULT '0',
  `checksum_algorithm` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `duration` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'For which duration this rule applies',
  `is_default` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `priority` int DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rules for generating keys';

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

--
-- Table structure for table `tenant_referrals`
--

CREATE TABLE `tenant_referrals` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `referrer_id` bigint NOT NULL COMMENT 'User who referred',
  `referred_id` bigint NOT NULL COMMENT 'User who was referred',
  `referral_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `commission_rate` decimal(5,2) DEFAULT '10.00',
  `commission_earned` decimal(10,2) DEFAULT '0.00',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'pending' COMMENT 'pending/converted/paid',
  `converted_at` timestamp NULL DEFAULT NULL,
  `level` int DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Referral tracking';

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

--
-- Table structure for table `tenant_registry`
--

CREATE TABLE `tenant_registry` (
  `id` int NOT NULL,
  `admin_id` int NOT NULL COMMENT 'Each admin has one registry entry',
  `reserve_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `database_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of tenant database',
  `database_host` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT 'localhost',
  `database_port` int DEFAULT '3306',
  `database_user` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `database_password` text COLLATE utf8mb4_unicode_ci,
  `users_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `keys_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `transactions_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `key_rules_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `key_batches_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `apk_versions_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `broadcasts_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_activity_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_sessions_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `referrals_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tickets_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `key_alert_config_table` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Maps admins to their tenant database details';

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

--
-- Table structure for table `tenant_support_tickets`
--

CREATE TABLE `tenant_support_tickets` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `ticket_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_telegram_id` bigint NOT NULL,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'open' COMMENT 'open/in_progress/resolved/closed',
  `priority` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'medium' COMMENT 'low/medium/high/urgent',
  `assigned_to` int DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT NULL,
  `resolved_by` int DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolution_notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Support tickets';

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

--
-- Table structure for table `tenant_ticket_replies`
--

CREATE TABLE `tenant_ticket_replies` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `ticket_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sender_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'user/admin/system',
  `sender_id` bigint NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `attachments` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Replies to support tickets';

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

--
-- Table structure for table `tenant_transactions`
--

CREATE TABLE `tenant_transactions` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `transaction_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_telegram_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'INR',
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_gateway` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `key_id` int DEFAULT NULL,
  `key_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `duration` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gateway_transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gateway_order_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gateway_payment_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gateway_signature` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'pending/success/failed/refunded',
  `failure_reason` text COLLATE utf8mb4_unicode_ci,
  `refunded` tinyint(1) DEFAULT '0',
  `refund_amount` decimal(10,2) DEFAULT NULL,
  `refund_date` timestamp NULL DEFAULT NULL,
  `refund_reason` text COLLATE utf8mb4_unicode_ci,
  `initiated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Payment transactions';

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

--
-- Table structure for table `tenant_users`
--

CREATE TABLE `tenant_users` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL COMMENT 'References tenant_registry.id',
  `telegram_id` bigint NOT NULL COMMENT 'User Telegram ID',
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `language_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `is_bot` tinyint(1) DEFAULT '0',
  `is_premium` tinyint(1) DEFAULT '0',
  `photo_url` text COLLATE utf8mb4_unicode_ci,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'active' COMMENT 'active/blocked/inactive',
  `block_reason` text COLLATE utf8mb4_unicode_ci,
  `blocked_at` timestamp NULL DEFAULT NULL,
  `blocked_by` int DEFAULT NULL,
  `joined_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_active` timestamp NULL DEFAULT NULL,
  `last_interaction` timestamp NULL DEFAULT NULL,
  `total_interactions` int DEFAULT '0',
  `total_purchases` int DEFAULT '0',
  `total_spent` decimal(10,2) DEFAULT '0.00',
  `avg_purchase_value` decimal(10,2) DEFAULT '0.00',
  `last_purchase_date` timestamp NULL DEFAULT NULL,
  `active_keys_count` int DEFAULT '0',
  `total_keys_received` int DEFAULT '0',
  `current_keys` json DEFAULT NULL,
  `referred_by` bigint DEFAULT NULL,
  `referral_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referral_earnings` decimal(10,2) DEFAULT '0.00',
  `notifications_enabled` tinyint(1) DEFAULT '1',
  `quiet_hours_start` int DEFAULT NULL,
  `quiet_hours_end` int DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT 'Soft delete timestamp',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='End users per tenant';

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

--
-- Table structure for table `tenant_user_activity_log`
--

CREATE TABLE `tenant_user_activity_log` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `user_telegram_id` bigint NOT NULL,
  `activity_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activity_details` json NOT NULL,
  `ip_address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `key_id` int DEFAULT NULL,
  `transaction_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User activity logs';

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

--
-- Table structure for table `tenant_user_sessions`
--

CREATE TABLE `tenant_user_sessions` (
  `id` int NOT NULL,
  `tenant_id` int NOT NULL,
  `user_telegram_id` bigint NOT NULL,
  `session_start` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `session_end` timestamp NULL DEFAULT NULL,
  `interactions_count` int DEFAULT '0',
  `commands_used` json DEFAULT NULL,
  `pages_viewed` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User sessions';

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

--
-- Stand-in structure for view `v_activity_summary`
-- (See below for the actual view)
--
CREATE TABLE `v_activity_summary` (
`activity_date` date
,`total_activities` bigint
,`purchases` decimal(23,0)
,`downloads` decimal(23,0)
,`starts` decimal(23,0)
);

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

--
-- Stand-in structure for view `v_admin_summary`
-- (See below for the actual view)
--
CREATE TABLE `v_admin_summary` (
`id` int
,`reserve_id` varchar(50)
,`admin_username` varchar(100)
,`bot_name` varchar(100)
,`recharge_status` varchar(20)
,`recharge_expiry` timestamp
,`status` varchar(20)
,`total_users` bigint
,`total_keys_generated` int
,`last_login` timestamp
,`total_activities` bigint
);

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

--
-- Stand-in structure for view `v_revenue_summary`
-- (See below for the actual view)
--
CREATE TABLE `v_revenue_summary` (
`revenue_date` date
,`transactions` bigint
,`total_revenue` decimal(32,2)
,`avg_transaction` decimal(14,6)
);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `telegram_id` (`telegram_id`),
  ADD UNIQUE KEY `admin_username` (`admin_username`),
  ADD UNIQUE KEY `reserve_id` (`reserve_id`),
  ADD UNIQUE KEY `bot_username` (`bot_username`),
  ADD UNIQUE KEY `bot_token` (`bot_token`),
  ADD UNIQUE KEY `database_name` (`database_name`),
  ADD KEY `idx_admins_owner` (`owner_id`),
  ADD KEY `idx_admins_telegram` (`telegram_id`),
  ADD KEY `idx_admins_reserve` (`reserve_id`),
  ADD KEY `idx_admins_status` (`status`),
  ADD KEY `idx_admins_recharge` (`recharge_expiry`);

--
-- Indexes for table `admin_activity_log`
--
ALTER TABLE `admin_activity_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_admin_activity_admin` (`admin_id`),
  ADD KEY `idx_admin_activity_type` (`activity_type`),
  ADD KEY `idx_admin_activity_time` (`performed_at`);

--
-- Indexes for table `admin_login_history`
--
ALTER TABLE `admin_login_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_admin_login_admin` (`admin_id`),
  ADD KEY `idx_admin_login_time` (`login_time`);

--
-- Indexes for table `api_keys`
--
ALTER TABLE `api_keys`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `api_key` (`api_key`),
  ADD KEY `idx_api_key` (`api_key`),
  ADD KEY `idx_api_owner` (`owner_id`),
  ADD KEY `idx_api_admin` (`admin_id`);

--
-- Indexes for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit_actor` (`actor_type`,`actor_id`),
  ADD KEY `idx_audit_entity` (`entity_type`,`entity_id`),
  ADD KEY `idx_audit_time` (`created_at`);

--
-- Indexes for table `backup_history`
--
ALTER TABLE `backup_history`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `backup_id` (`backup_id`);

--
-- Indexes for table `blocked_users_global`
--
ALTER TABLE `blocked_users_global`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_block` (`telegram_id`,`reserve_id`),
  ADD KEY `idx_blocked_telegram` (`telegram_id`);

--
-- Indexes for table `broadcasts_global`
--
ALTER TABLE `broadcasts_global`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `broadcast_id` (`broadcast_id`),
  ADD KEY `owner_id` (`owner_id`);

--
-- Indexes for table `notification_logs`
--
ALTER TABLE `notification_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `admin_id` (`admin_id`),
  ADD KEY `owner_id` (`owner_id`),
  ADD KEY `idx_notification_recipient` (`recipient_type`,`recipient_id`),
  ADD KEY `idx_notification_time` (`sent_at`),
  ADD KEY `idx_notification_logs_composite` (`recipient_type`,`recipient_id`,`sent_at`);

--
-- Indexes for table `notification_templates`
--
ALTER TABLE `notification_templates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_id` (`owner_id`);

--
-- Indexes for table `owners`
--
ALTER TABLE `owners`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `telegram_id` (`telegram_id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `idx_owners_telegram` (`telegram_id`),
  ADD KEY `idx_owners_username` (`username`);

--
-- Indexes for table `recharge_history`
--
ALTER TABLE `recharge_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_id` (`owner_id`),
  ADD KEY `idx_recharge_admin` (`admin_id`),
  ADD KEY `idx_recharge_dates` (`start_date`,`end_date`);

--
-- Indexes for table `recharge_plans`
--
ALTER TABLE `recharge_plans`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `plan_name` (`plan_name`);

--
-- Indexes for table `server_alert_rules`
--
ALTER TABLE `server_alert_rules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `owner_id` (`owner_id`),
  ADD KEY `idx_alert_admin` (`admin_id`);

--
-- Indexes for table `server_health_logs`
--
ALTER TABLE `server_health_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_health_admin` (`admin_id`),
  ADD KEY `idx_health_time` (`checked_at`);

--
-- Indexes for table `server_incidents`
--
ALTER TABLE `server_incidents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_incident_admin` (`admin_id`),
  ADD KEY `idx_incident_status` (`status`);

--
-- Indexes for table `system_config`
--
ALTER TABLE `system_config`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `config_key` (`config_key`);

--
-- Indexes for table `tenant_apk_versions`
--
ALTER TABLE `tenant_apk_versions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_version` (`tenant_id`,`version_code`),
  ADD KEY `idx_tenant_apk_version` (`version_code`),
  ADD KEY `idx_tenant_apk_active` (`is_active`),
  ADD KEY `idx_tenant_apk_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_broadcasts`
--
ALTER TABLE `tenant_broadcasts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_broadcast` (`tenant_id`,`broadcast_id`),
  ADD KEY `idx_tenant_broadcasts_id` (`broadcast_id`),
  ADD KEY `idx_tenant_broadcasts_status` (`status`),
  ADD KEY `idx_tenant_broadcasts_scheduled` (`scheduled_for`),
  ADD KEY `idx_tenant_broadcasts_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_broadcast_recipients`
--
ALTER TABLE `tenant_broadcast_recipients`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tenant_id` (`tenant_id`,`broadcast_id`),
  ADD KEY `idx_tenant_recipients_broadcast` (`broadcast_id`),
  ADD KEY `idx_tenant_recipients_user` (`user_telegram_id`),
  ADD KEY `idx_tenant_recipients_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_keys`
--
ALTER TABLE `tenant_keys`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_key` (`tenant_id`,`key_value`),
  ADD KEY `idx_tenant_keys_value` (`key_value`),
  ADD KEY `idx_tenant_keys_status` (`status`),
  ADD KEY `idx_tenant_keys_duration` (`duration`),
  ADD KEY `idx_tenant_keys_used_by` (`used_by`),
  ADD KEY `idx_tenant_keys_expiry` (`expires_at`),
  ADD KEY `idx_tenant_keys_batch` (`batch_id`),
  ADD KEY `idx_tenant_keys_tenant` (`tenant_id`),
  ADD KEY `idx_tenant_keys_composite` (`tenant_id`,`status`,`duration`);

--
-- Indexes for table `tenant_key_alert_config`
--
ALTER TABLE `tenant_key_alert_config`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_duration` (`tenant_id`,`duration`),
  ADD KEY `idx_tenant_alert_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_key_batches`
--
ALTER TABLE `tenant_key_batches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_batch` (`tenant_id`,`batch_id`),
  ADD KEY `idx_tenant_batches_id` (`batch_id`),
  ADD KEY `idx_tenant_batches_status` (`status`),
  ADD KEY `idx_tenant_batches_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_key_generation_rules`
--
ALTER TABLE `tenant_key_generation_rules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tenant_rules_duration` (`duration`),
  ADD KEY `idx_tenant_rules_active` (`is_active`),
  ADD KEY `idx_tenant_rules_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_referrals`
--
ALTER TABLE `tenant_referrals`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_referred` (`tenant_id`,`referred_id`),
  ADD KEY `idx_tenant_referrals_referrer` (`referrer_id`),
  ADD KEY `idx_tenant_referrals_code` (`referral_code`),
  ADD KEY `idx_tenant_referrals_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_registry`
--
ALTER TABLE `tenant_registry`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `admin_id` (`admin_id`),
  ADD UNIQUE KEY `reserve_id` (`reserve_id`),
  ADD KEY `idx_tenant_admin` (`admin_id`),
  ADD KEY `idx_tenant_reserve` (`reserve_id`);

--
-- Indexes for table `tenant_support_tickets`
--
ALTER TABLE `tenant_support_tickets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_ticket` (`tenant_id`,`ticket_id`),
  ADD KEY `idx_tenant_tickets_user` (`user_telegram_id`),
  ADD KEY `idx_tenant_tickets_status` (`status`),
  ADD KEY `idx_tenant_tickets_id` (`ticket_id`),
  ADD KEY `idx_tenant_tickets_tenant` (`tenant_id`);
ALTER TABLE `tenant_support_tickets` ADD FULLTEXT KEY `ft_ticket_search` (`subject`,`message`);

--
-- Indexes for table `tenant_ticket_replies`
--
ALTER TABLE `tenant_ticket_replies`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tenant_id` (`tenant_id`,`ticket_id`),
  ADD KEY `idx_tenant_replies_ticket` (`ticket_id`),
  ADD KEY `idx_tenant_replies_tenant` (`tenant_id`);

--
-- Indexes for table `tenant_transactions`
--
ALTER TABLE `tenant_transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_transaction` (`tenant_id`,`transaction_id`),
  ADD KEY `idx_tenant_transactions_user` (`user_telegram_id`),
  ADD KEY `idx_tenant_transactions_status` (`status`),
  ADD KEY `idx_tenant_transactions_gateway` (`gateway_transaction_id`),
  ADD KEY `idx_tenant_transactions_completed` (`completed_at`),
  ADD KEY `idx_tenant_transactions_tenant` (`tenant_id`),
  ADD KEY `idx_tenant_transactions_composite` (`tenant_id`,`status`,`completed_at`);

--
-- Indexes for table `tenant_users`
--
ALTER TABLE `tenant_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_tenant_user` (`tenant_id`,`telegram_id`),
  ADD UNIQUE KEY `referral_code` (`referral_code`),
  ADD KEY `idx_tenant_users_telegram` (`telegram_id`),
  ADD KEY `idx_tenant_users_username` (`username`),
  ADD KEY `idx_tenant_users_status` (`status`),
  ADD KEY `idx_tenant_users_referral` (`referral_code`),
  ADD KEY `idx_tenant_users_tenant` (`tenant_id`),
  ADD KEY `idx_tenant_users_deleted` (`deleted_at`),
  ADD KEY `idx_tenant_users_last_active` (`last_active`);

--
-- Indexes for table `tenant_user_activity_log`
--
ALTER TABLE `tenant_user_activity_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tenant_activity_user` (`user_telegram_id`),
  ADD KEY `idx_tenant_activity_type` (`activity_type`),
  ADD KEY `idx_tenant_activity_time` (`created_at`),
  ADD KEY `idx_tenant_activity_tenant` (`tenant_id`),
  ADD KEY `idx_tenant_activity_composite` (`tenant_id`,`user_telegram_id`,`created_at`);

--
-- Indexes for table `tenant_user_sessions`
--
ALTER TABLE `tenant_user_sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tenant_sessions_user` (`user_telegram_id`),
  ADD KEY `idx_tenant_sessions_start` (`session_start`),
  ADD KEY `idx_tenant_sessions_tenant` (`tenant_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

--
-- AUTO_INCREMENT for table `admin_activity_log`
--
ALTER TABLE `admin_activity_log`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admin_login_history`
--
ALTER TABLE `admin_login_history`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `api_keys`
--
ALTER TABLE `api_keys`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `audit_logs`
--
ALTER TABLE `audit_logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `backup_history`
--
ALTER TABLE `backup_history`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `blocked_users_global`
--
ALTER TABLE `blocked_users_global`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `broadcasts_global`
--
ALTER TABLE `broadcasts_global`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notification_logs`
--
ALTER TABLE `notification_logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notification_templates`
--
ALTER TABLE `notification_templates`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `owners`
--
ALTER TABLE `owners`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key', AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `recharge_history`
--
ALTER TABLE `recharge_history`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `recharge_plans`
--
ALTER TABLE `recharge_plans`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `server_alert_rules`
--
ALTER TABLE `server_alert_rules`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `server_health_logs`
--
ALTER TABLE `server_health_logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `server_incidents`
--
ALTER TABLE `server_incidents`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_config`
--
ALTER TABLE `system_config`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `tenant_apk_versions`
--
ALTER TABLE `tenant_apk_versions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_broadcasts`
--
ALTER TABLE `tenant_broadcasts`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_broadcast_recipients`
--
ALTER TABLE `tenant_broadcast_recipients`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_keys`
--
ALTER TABLE `tenant_keys`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_key_alert_config`
--
ALTER TABLE `tenant_key_alert_config`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_key_batches`
--
ALTER TABLE `tenant_key_batches`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_key_generation_rules`
--
ALTER TABLE `tenant_key_generation_rules`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_referrals`
--
ALTER TABLE `tenant_referrals`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_registry`
--
ALTER TABLE `tenant_registry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_support_tickets`
--
ALTER TABLE `tenant_support_tickets`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_ticket_replies`
--
ALTER TABLE `tenant_ticket_replies`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_transactions`
--
ALTER TABLE `tenant_transactions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_users`
--
ALTER TABLE `tenant_users`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_user_activity_log`
--
ALTER TABLE `tenant_user_activity_log`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenant_user_sessions`
--
ALTER TABLE `tenant_user_sessions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

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

--
-- Structure for view `v_activity_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`xyzcpan2_Xyz`@`%` SQL SECURITY DEFINER VIEW `v_activity_summary`  AS SELECT cast(`tenant_user_activity_log`.`created_at` as date) AS `activity_date`, count(0) AS `total_activities`, sum((case when (`tenant_user_activity_log`.`activity_type` like 'purchase%') then 1 else 0 end)) AS `purchases`, sum((case when (`tenant_user_activity_log`.`activity_type` = 'download') then 1 else 0 end)) AS `downloads`, sum((case when (`tenant_user_activity_log`.`activity_type` = 'start') then 1 else 0 end)) AS `starts` FROM `tenant_user_activity_log` GROUP BY cast(`tenant_user_activity_log`.`created_at` as date) ;

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

--
-- Structure for view `v_admin_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`xyzcpan2_Xyz`@`%` SQL SECURITY DEFINER VIEW `v_admin_summary`  AS SELECT `a`.`id` AS `id`, `a`.`reserve_id` AS `reserve_id`, `a`.`admin_username` AS `admin_username`, `a`.`bot_name` AS `bot_name`, `a`.`recharge_status` AS `recharge_status`, `a`.`recharge_expiry` AS `recharge_expiry`, `a`.`status` AS `status`, (select count(distinct `tu`.`id`) from `tenant_users` `tu` where (`tu`.`tenant_id` = `a`.`id`)) AS `total_users`, `a`.`total_keys_generated` AS `total_keys_generated`, `a`.`last_login` AS `last_login`, (select count(0) from `admin_activity_log` `aal` where (`aal`.`admin_id` = `a`.`id`)) AS `total_activities` FROM `admins` AS `a` ;

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

--
-- Structure for view `v_revenue_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`xyzcpan2_Xyz`@`%` SQL SECURITY DEFINER VIEW `v_revenue_summary`  AS SELECT cast(`tenant_transactions`.`completed_at` as date) AS `revenue_date`, count(0) AS `transactions`, sum(`tenant_transactions`.`amount`) AS `total_revenue`, avg(`tenant_transactions`.`amount`) AS `avg_transaction` FROM `tenant_transactions` WHERE (`tenant_transactions`.`status` = 'success') GROUP BY cast(`tenant_transactions`.`completed_at` as date) ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admins`
--
ALTER TABLE `admins`
  ADD CONSTRAINT `admins_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `admin_activity_log`
--
ALTER TABLE `admin_activity_log`
  ADD CONSTRAINT `admin_activity_log_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `admin_login_history`
--
ALTER TABLE `admin_login_history`
  ADD CONSTRAINT `admin_login_history_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `api_keys`
--
ALTER TABLE `api_keys`
  ADD CONSTRAINT `api_keys_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `api_keys_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `broadcasts_global`
--
ALTER TABLE `broadcasts_global`
  ADD CONSTRAINT `broadcasts_global_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notification_logs`
--
ALTER TABLE `notification_logs`
  ADD CONSTRAINT `notification_logs_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `notification_logs_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notification_templates`
--
ALTER TABLE `notification_templates`
  ADD CONSTRAINT `notification_templates_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `recharge_history`
--
ALTER TABLE `recharge_history`
  ADD CONSTRAINT `recharge_history_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `recharge_history_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `server_alert_rules`
--
ALTER TABLE `server_alert_rules`
  ADD CONSTRAINT `server_alert_rules_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `server_alert_rules_ibfk_2` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `server_health_logs`
--
ALTER TABLE `server_health_logs`
  ADD CONSTRAINT `server_health_logs_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `server_incidents`
--
ALTER TABLE `server_incidents`
  ADD CONSTRAINT `server_incidents_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_apk_versions`
--
ALTER TABLE `tenant_apk_versions`
  ADD CONSTRAINT `tenant_apk_versions_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_broadcasts`
--
ALTER TABLE `tenant_broadcasts`
  ADD CONSTRAINT `tenant_broadcasts_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_broadcast_recipients`
--
ALTER TABLE `tenant_broadcast_recipients`
  ADD CONSTRAINT `tenant_broadcast_recipients_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `tenant_broadcast_recipients_ibfk_2` FOREIGN KEY (`tenant_id`,`broadcast_id`) REFERENCES `tenant_broadcasts` (`tenant_id`, `broadcast_id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_keys`
--
ALTER TABLE `tenant_keys`
  ADD CONSTRAINT `tenant_keys_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_key_alert_config`
--
ALTER TABLE `tenant_key_alert_config`
  ADD CONSTRAINT `tenant_key_alert_config_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_key_batches`
--
ALTER TABLE `tenant_key_batches`
  ADD CONSTRAINT `tenant_key_batches_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_key_generation_rules`
--
ALTER TABLE `tenant_key_generation_rules`
  ADD CONSTRAINT `tenant_key_generation_rules_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_referrals`
--
ALTER TABLE `tenant_referrals`
  ADD CONSTRAINT `tenant_referrals_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_registry`
--
ALTER TABLE `tenant_registry`
  ADD CONSTRAINT `tenant_registry_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_support_tickets`
--
ALTER TABLE `tenant_support_tickets`
  ADD CONSTRAINT `tenant_support_tickets_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_ticket_replies`
--
ALTER TABLE `tenant_ticket_replies`
  ADD CONSTRAINT `tenant_ticket_replies_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `tenant_ticket_replies_ibfk_2` FOREIGN KEY (`tenant_id`,`ticket_id`) REFERENCES `tenant_support_tickets` (`tenant_id`, `ticket_id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_transactions`
--
ALTER TABLE `tenant_transactions`
  ADD CONSTRAINT `tenant_transactions_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_users`
--
ALTER TABLE `tenant_users`
  ADD CONSTRAINT `tenant_users_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_user_activity_log`
--
ALTER TABLE `tenant_user_activity_log`
  ADD CONSTRAINT `tenant_user_activity_log_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tenant_user_sessions`
--
ALTER TABLE `tenant_user_sessions`
  ADD CONSTRAINT `tenant_user_sessions_ibfk_1` FOREIGN KEY (`tenant_id`) REFERENCES `tenant_registry` (`id`) ON DELETE CASCADE;

DELIMITER $$
--
-- Events
--
CREATE DEFINER=`xyzcpan2_Xyz`@`%` EVENT `clean_old_activity_logs` ON SCHEDULE EVERY 1 DAY STARTS '2026-02-22 18:35:46' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DELETE FROM tenant_user_activity_log
    WHERE created_at < NOW() - INTERVAL 90 DAY;
END$$

CREATE DEFINER=`xyzcpan2_Xyz`@`%` EVENT `clean_old_sessions` ON SCHEDULE EVERY 1 DAY STARTS '2026-02-22 19:35:46' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DELETE FROM tenant_user_sessions
    WHERE session_end IS NOT NULL AND session_end < NOW() - INTERVAL 30 DAY;
END$$

CREATE DEFINER=`xyzcpan2_Xyz`@`%` EVENT `expire_keys` ON SCHEDULE EVERY 1 HOUR STARTS '2026-02-22 17:35:46' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    UPDATE tenant_keys
    SET status = 'expired'
    WHERE status = 'sold' AND expires_at < NOW();
END$$

DELIMITER ;
COMMIT;

