-- =====================================================
-- TELEGRAM MULTI-TENANT BOT SYSTEM - COMPLETE DATABASE
-- =====================================================
-- This script creates the entire database schema for the
-- Telegram Multi-Tenant Bot System. It is idempotent and
-- works on MySQL 5.7+.
-- Run this script after creating the database.
-- =====================================================

-- Select the database (change to your actual database name)
-- Make sure you have created the database first, e.g.:
-- CREATE DATABASE telegram_bot_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE telegram_bot_system;


-- =====================================================
-- HELPER PROCEDURE FOR CONDITIONAL INDEX CREATION
-- =====================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS CreateIndexIfNotExists$$
CREATE PROCEDURE CreateIndexIfNotExists(
    IN p_table_name VARCHAR(64),
    IN p_index_name VARCHAR(64),
    IN p_index_definition TEXT
)
BEGIN
    DECLARE index_exists INT;

    SELECT COUNT(*) INTO index_exists
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = p_table_name
      AND index_name = p_index_name;

    IF index_exists = 0 THEN
        SET @sql = CONCAT('CREATE INDEX ', p_index_name, ' ON ', p_table_name, ' ', p_index_definition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

-- =====================================================
-- SECTION 1: OWNER TABLES
-- =====================================================

CREATE TABLE IF NOT EXISTS owners (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key',
    telegram_id BIGINT UNIQUE NOT NULL COMMENT 'Telegram ID of the owner',
    username VARCHAR(100) UNIQUE NOT NULL COMMENT 'Owner username',
    first_name VARCHAR(255) COMMENT 'First name',
    last_name VARCHAR(255) COMMENT 'Last name',
    email VARCHAR(255) COMMENT 'Email address',
    password_hash VARCHAR(255) NOT NULL COMMENT 'Bcrypt hash of password',
    two_factor_enabled BOOLEAN DEFAULT FALSE COMMENT 'Is 2FA enabled?',
    two_factor_secret VARCHAR(255) COMMENT 'Secret for 2FA',
    notification_preferences JSON DEFAULT NULL COMMENT 'JSON preferences for notifications',
    language VARCHAR(10) DEFAULT 'en' COMMENT 'Preferred language',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
    last_login TIMESTAMP NULL COMMENT 'Last successful login',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update timestamp'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Super admin accounts';

CALL CreateIndexIfNotExists('owners', 'idx_owners_telegram', '(telegram_id)');
CALL CreateIndexIfNotExists('owners', 'idx_owners_username', '(username)');

-- =====================================================
-- SECTION 2: ADMIN TABLES
-- =====================================================

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key',
    owner_id INT NOT NULL COMMENT 'Owner who created this admin',
    telegram_id BIGINT UNIQUE NOT NULL COMMENT 'Admin Telegram ID',
    admin_username VARCHAR(100) UNIQUE NOT NULL COMMENT 'Admin login username',
    admin_first_name VARCHAR(255) COMMENT 'First name',
    admin_last_name VARCHAR(255) COMMENT 'Last name',
    email VARCHAR(255) COMMENT 'Email address',
    password_hash VARCHAR(255) NOT NULL COMMENT 'Bcrypt hash of password',
    reserve_id VARCHAR(50) UNIQUE NOT NULL COMMENT 'Unique bot identifier (e.g., GAME-001)',
    bot_name VARCHAR(100) NOT NULL COMMENT 'Display name of the bot',
    bot_username VARCHAR(100) UNIQUE NOT NULL COMMENT 'Telegram bot username (@bot)',
    bot_token VARCHAR(255) UNIQUE NOT NULL COMMENT 'Telegram bot token',
    server_name VARCHAR(255) COMMENT 'Assigned server name',
    server_ip VARCHAR(50) COMMENT 'Server IP address',
    server_port INT DEFAULT 443 COMMENT 'Server port',
    domain_name VARCHAR(255) COMMENT 'Custom domain for admin panel',
    database_name VARCHAR(100) UNIQUE COMMENT 'Tenant database name',
    database_host VARCHAR(255) DEFAULT 'localhost' COMMENT 'Database host',
    database_port INT DEFAULT 3306 COMMENT 'Database port',
    database_user VARCHAR(100) COMMENT 'Database user',
    database_password TEXT COMMENT 'Database password',
    payment_gateway VARCHAR(50) DEFAULT 'razorpay' COMMENT 'Selected payment gateway',
    payment_api_key TEXT COMMENT 'API key for gateway',
    payment_api_secret TEXT COMMENT 'API secret',
    payment_merchant_id VARCHAR(255) COMMENT 'Merchant ID if applicable',
    payment_webhook_secret TEXT COMMENT 'Secret for webhook verification',
    payment_currency VARCHAR(10) DEFAULT 'INR' COMMENT 'Currency code',
    recharge_status VARCHAR(20) DEFAULT 'inactive' COMMENT 'active/expired/pending',
    recharge_plan VARCHAR(20) COMMENT 'Plan name',
    recharge_amount DECIMAL(10,2) COMMENT 'Amount paid',
    recharge_date TIMESTAMP NULL COMMENT 'Last recharge date',
    recharge_expiry TIMESTAMP NULL COMMENT 'Expiry date',
    auto_renew BOOLEAN DEFAULT FALSE COMMENT 'Auto-renew subscription',
    monthly_key_limit INT DEFAULT 1000 COMMENT 'Key limit per month',
    keys_generated_this_month INT DEFAULT 0 COMMENT 'Keys used this month',
    total_keys_generated INT DEFAULT 0 COMMENT 'Lifetime keys generated',
    status VARCHAR(20) DEFAULT 'active' COMMENT 'active/blocked/inactive',
    settings JSON DEFAULT NULL COMMENT 'JSON settings for bot',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation',
    last_login TIMESTAMP NULL COMMENT 'Last login timestamp',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update',
    CONSTRAINT valid_reserve_id CHECK (reserve_id REGEXP '^[A-Z0-9]{3,10}-[0-9]{3,10}$'),
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bot admin accounts';

CALL CreateIndexIfNotExists('admins', 'idx_admins_owner', '(owner_id)');
CALL CreateIndexIfNotExists('admins', 'idx_admins_telegram', '(telegram_id)');
CALL CreateIndexIfNotExists('admins', 'idx_admins_reserve', '(reserve_id)');
CALL CreateIndexIfNotExists('admins', 'idx_admins_status', '(status)');
CALL CreateIndexIfNotExists('admins', 'idx_admins_recharge', '(recharge_expiry)');

CREATE TABLE IF NOT EXISTS admin_login_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(50),
    user_agent TEXT,
    login_successful BOOLEAN DEFAULT TRUE,
    failure_reason TEXT,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Admin login attempts';

CALL CreateIndexIfNotExists('admin_login_history', 'idx_admin_login_admin', '(admin_id)');
CALL CreateIndexIfNotExists('admin_login_history', 'idx_admin_login_time', '(login_time)');

CREATE TABLE IF NOT EXISTS admin_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    reserve_id VARCHAR(50) NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_details JSON NOT NULL,
    ip_address VARCHAR(50),
    performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Admin actions log';

CALL CreateIndexIfNotExists('admin_activity_log', 'idx_admin_activity_admin', '(admin_id)');
CALL CreateIndexIfNotExists('admin_activity_log', 'idx_admin_activity_type', '(activity_type)');
CALL CreateIndexIfNotExists('admin_activity_log', 'idx_admin_activity_time', '(performed_at)');

-- =====================================================
-- SECTION 3: TENANT REGISTRY
-- =====================================================

CREATE TABLE IF NOT EXISTS tenant_registry (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL UNIQUE COMMENT 'Each admin has one registry entry',
    reserve_id VARCHAR(50) UNIQUE NOT NULL,
    database_name VARCHAR(100) NOT NULL COMMENT 'Name of tenant database',
    database_host VARCHAR(255) DEFAULT 'localhost',
    database_port INT DEFAULT 3306,
    database_user VARCHAR(100),
    database_password TEXT,
    users_table VARCHAR(100) NOT NULL,
    keys_table VARCHAR(100) NOT NULL,
    transactions_table VARCHAR(100) NOT NULL,
    key_rules_table VARCHAR(100) NOT NULL,
    key_batches_table VARCHAR(100) NOT NULL,
    apk_versions_table VARCHAR(100) NOT NULL,
    broadcasts_table VARCHAR(100) NOT NULL,
    user_activity_table VARCHAR(100) NOT NULL,
    user_sessions_table VARCHAR(100) NOT NULL,
    referrals_table VARCHAR(100) NOT NULL,
    tickets_table VARCHAR(100) NOT NULL,
    key_alert_config_table VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Maps admins to their tenant database details';

CALL CreateIndexIfNotExists('tenant_registry', 'idx_tenant_admin', '(admin_id)');
CALL CreateIndexIfNotExists('tenant_registry', 'idx_tenant_reserve', '(reserve_id)');

-- =====================================================
-- SECTION 4: TENANT TABLES
-- =====================================================

CREATE TABLE IF NOT EXISTS tenant_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL COMMENT 'References tenant_registry.id',
    telegram_id BIGINT NOT NULL COMMENT 'User Telegram ID',
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    language_code VARCHAR(10) DEFAULT 'en',
    is_bot BOOLEAN DEFAULT FALSE,
    is_premium BOOLEAN DEFAULT FALSE,
    photo_url TEXT,
    status VARCHAR(20) DEFAULT 'active' COMMENT 'active/blocked/inactive',
    block_reason TEXT,
    blocked_at TIMESTAMP NULL,
    blocked_by INT,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_active TIMESTAMP NULL,
    last_interaction TIMESTAMP NULL,
    total_interactions INT DEFAULT 0,
    total_purchases INT DEFAULT 0,
    total_spent DECIMAL(10,2) DEFAULT 0,
    avg_purchase_value DECIMAL(10,2) DEFAULT 0,
    last_purchase_date TIMESTAMP NULL,
    active_keys_count INT DEFAULT 0,
    total_keys_received INT DEFAULT 0,
    current_keys JSON DEFAULT NULL,
    referred_by BIGINT,
    referral_code VARCHAR(50) UNIQUE,
    referral_earnings DECIMAL(10,2) DEFAULT 0,
    notifications_enabled BOOLEAN DEFAULT TRUE,
    quiet_hours_start INT,
    quiet_hours_end INT,
    metadata JSON DEFAULT NULL,
    deleted_at TIMESTAMP NULL COMMENT 'Soft delete timestamp',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_tenant_user (tenant_id, telegram_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='End users per tenant';

CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_telegram', '(telegram_id)');
CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_username', '(username)');
CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_status', '(status)');
CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_referral', '(referral_code)');
CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_tenant', '(tenant_id)');
CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_deleted', '(deleted_at)');
CALL CreateIndexIfNotExists('tenant_users', 'idx_tenant_users_last_active', '(last_active)');

CREATE TABLE IF NOT EXISTS tenant_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    key_value VARCHAR(255) NOT NULL COMMENT 'The key string',
    key_type VARCHAR(20) NOT NULL COMMENT 'stored/generated',
    duration VARCHAR(20) NOT NULL COMMENT '1d,3d,7d,30d,60d',
    price DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'available' COMMENT 'available/sold/blocked/expired',
    is_used BOOLEAN DEFAULT FALSE,
    used_by BIGINT COMMENT 'Telegram ID of user who used it',
    used_at TIMESTAMP NULL,
    issued_at TIMESTAMP NULL COMMENT 'When assigned to user',
    expires_at TIMESTAMP NULL COMMENT 'Expiration date',
    extended_count INT DEFAULT 0,
    last_extended_at TIMESTAMP NULL,
    generation_rule_id INT,
    generation_date TIMESTAMP NULL,
    batch_id VARCHAR(100),
    uploaded_at TIMESTAMP NULL,
    is_blocked BOOLEAN DEFAULT FALSE,
    block_reason TEXT,
    blocked_at TIMESTAMP NULL,
    blocked_by INT,
    notes TEXT,
    metadata JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_tenant_key (tenant_id, key_value),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Keys inventory per tenant';

CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_value', '(key_value)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_status', '(status)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_duration', '(duration)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_used_by', '(used_by)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_expiry', '(expires_at)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_batch', '(batch_id)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_tenant', '(tenant_id)');
CALL CreateIndexIfNotExists('tenant_keys', 'idx_tenant_keys_composite', '(tenant_id, status, duration)');

CREATE TABLE IF NOT EXISTS tenant_key_generation_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    rule_name VARCHAR(100) NOT NULL,
    pattern VARCHAR(255) NOT NULL COMMENT 'Key pattern with variables',
    description TEXT,
    prefix VARCHAR(50),
    include_date BOOLEAN DEFAULT TRUE,
    date_format VARCHAR(20) DEFAULT 'YYYYMMDD',
    random_length INT DEFAULT 8,
    random_charset VARCHAR(100) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
    exclude_similar BOOLEAN DEFAULT TRUE,
    checksum_enabled BOOLEAN DEFAULT FALSE,
    checksum_algorithm VARCHAR(50),
    duration VARCHAR(20) COMMENT 'For which duration this rule applies',
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    priority INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rules for generating keys';

CALL CreateIndexIfNotExists('tenant_key_generation_rules', 'idx_tenant_rules_duration', '(duration)');
CALL CreateIndexIfNotExists('tenant_key_generation_rules', 'idx_tenant_rules_active', '(is_active)');
CALL CreateIndexIfNotExists('tenant_key_generation_rules', 'idx_tenant_rules_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_key_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    batch_id VARCHAR(100) NOT NULL,
    batch_name VARCHAR(255),
    file_name VARCHAR(255),
    file_size INT,
    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) DEFAULT 'processing' COMMENT 'processing/completed/failed',
    error_log TEXT,
    uploaded_by INT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    UNIQUE KEY unique_tenant_batch (tenant_id, batch_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bulk upload batches';

CALL CreateIndexIfNotExists('tenant_key_batches', 'idx_tenant_batches_id', '(batch_id)');
CALL CreateIndexIfNotExists('tenant_key_batches', 'idx_tenant_batches_status', '(status)');
CALL CreateIndexIfNotExists('tenant_key_batches', 'idx_tenant_batches_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_key_alert_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    duration VARCHAR(20) NOT NULL,
    threshold INT DEFAULT 100,
    notify_admin BOOLEAN DEFAULT TRUE,
    notify_owner BOOLEAN DEFAULT FALSE,
    auto_generate BOOLEAN DEFAULT FALSE,
    last_alert_sent TIMESTAMP NULL,
    UNIQUE KEY unique_tenant_duration (tenant_id, duration),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Low stock alert settings';

CALL CreateIndexIfNotExists('tenant_key_alert_config', 'idx_tenant_alert_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    transaction_id VARCHAR(100) NOT NULL,
    user_telegram_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'INR',
    payment_method VARCHAR(50),
    payment_gateway VARCHAR(50),
    key_id INT,
    key_value VARCHAR(255),
    duration VARCHAR(20),
    gateway_transaction_id VARCHAR(255),
    gateway_order_id VARCHAR(255),
    gateway_payment_id VARCHAR(255),
    gateway_signature VARCHAR(255),
    status VARCHAR(20) NOT NULL COMMENT 'pending/success/failed/refunded',
    failure_reason TEXT,
    refunded BOOLEAN DEFAULT FALSE,
    refund_amount DECIMAL(10,2),
    refund_date TIMESTAMP NULL,
    refund_reason TEXT,
    initiated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    UNIQUE KEY unique_tenant_transaction (tenant_id, transaction_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Payment transactions';

CALL CreateIndexIfNotExists('tenant_transactions', 'idx_tenant_transactions_user', '(user_telegram_id)');
CALL CreateIndexIfNotExists('tenant_transactions', 'idx_tenant_transactions_status', '(status)');
CALL CreateIndexIfNotExists('tenant_transactions', 'idx_tenant_transactions_gateway', '(gateway_transaction_id)');
CALL CreateIndexIfNotExists('tenant_transactions', 'idx_tenant_transactions_completed', '(completed_at)');
CALL CreateIndexIfNotExists('tenant_transactions', 'idx_tenant_transactions_tenant', '(tenant_id)');
CALL CreateIndexIfNotExists('tenant_transactions', 'idx_tenant_transactions_composite', '(tenant_id, status, completed_at)');

CREATE TABLE IF NOT EXISTS tenant_apk_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    version_name VARCHAR(50) NOT NULL,
    version_code INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    file_hash VARCHAR(255),
    release_notes TEXT,
    min_sdk INT,
    target_sdk INT,
    download_count INT DEFAULT 0,
    unique_downloads INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    is_mandatory BOOLEAN DEFAULT FALSE,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    uploaded_by INT,
    released_at TIMESTAMP NULL,
    UNIQUE KEY unique_tenant_version (tenant_id, version_code),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='APK versions for each bot';

CALL CreateIndexIfNotExists('tenant_apk_versions', 'idx_tenant_apk_version', '(version_code)');
CALL CreateIndexIfNotExists('tenant_apk_versions', 'idx_tenant_apk_active', '(is_active)');
CALL CreateIndexIfNotExists('tenant_apk_versions', 'idx_tenant_apk_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_broadcasts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    broadcast_id VARCHAR(50) NOT NULL,
    sender_type VARCHAR(20) NOT NULL COMMENT 'admin/system',
    sender_id INT NOT NULL,
    message TEXT NOT NULL,
    preview_text TEXT,
    media_url TEXT,
    buttons JSON DEFAULT NULL,
    target_type VARCHAR(50) NOT NULL COMMENT 'all/premium/active/inactive/custom',
    target_filters JSON DEFAULT NULL,
    scheduled_for TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    status VARCHAR(20) DEFAULT 'draft' COMMENT 'draft/scheduled/sending/completed/failed/cancelled',
    priority INT DEFAULT 1,
    total_recipients INT,
    delivered_count INT,
    failed_count INT,
    opened_count INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_tenant_broadcast (tenant_id, broadcast_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Broadcast messages';

CALL CreateIndexIfNotExists('tenant_broadcasts', 'idx_tenant_broadcasts_id', '(broadcast_id)');
CALL CreateIndexIfNotExists('tenant_broadcasts', 'idx_tenant_broadcasts_status', '(status)');
CALL CreateIndexIfNotExists('tenant_broadcasts', 'idx_tenant_broadcasts_scheduled', '(scheduled_for)');
CALL CreateIndexIfNotExists('tenant_broadcasts', 'idx_tenant_broadcasts_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_broadcast_recipients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    broadcast_id VARCHAR(50) NOT NULL,
    user_telegram_id BIGINT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/delivered/failed/opened',
    delivered_at TIMESTAMP NULL,
    opened_at TIMESTAMP NULL,
    failed_reason TEXT,
    retry_count INT DEFAULT 0,
    last_retry TIMESTAMP NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id, broadcast_id) REFERENCES tenant_broadcasts(tenant_id, broadcast_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Per-user broadcast delivery status';

CALL CreateIndexIfNotExists('tenant_broadcast_recipients', 'idx_tenant_recipients_broadcast', '(broadcast_id)');
CALL CreateIndexIfNotExists('tenant_broadcast_recipients', 'idx_tenant_recipients_user', '(user_telegram_id)');
CALL CreateIndexIfNotExists('tenant_broadcast_recipients', 'idx_tenant_recipients_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_user_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    user_telegram_id BIGINT NOT NULL,
    activity_type VARCHAR(50) NOT NULL,
    activity_details JSON NOT NULL,
    ip_address VARCHAR(50),
    user_agent TEXT,
    key_id INT,
    transaction_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User activity logs';

CALL CreateIndexIfNotExists('tenant_user_activity_log', 'idx_tenant_activity_user', '(user_telegram_id)');
CALL CreateIndexIfNotExists('tenant_user_activity_log', 'idx_tenant_activity_type', '(activity_type)');
CALL CreateIndexIfNotExists('tenant_user_activity_log', 'idx_tenant_activity_time', '(created_at)');
CALL CreateIndexIfNotExists('tenant_user_activity_log', 'idx_tenant_activity_tenant', '(tenant_id)');
CALL CreateIndexIfNotExists('tenant_user_activity_log', 'idx_tenant_activity_composite', '(tenant_id, user_telegram_id, created_at)');

CREATE TABLE IF NOT EXISTS tenant_user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    user_telegram_id BIGINT NOT NULL,
    session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_end TIMESTAMP NULL,
    interactions_count INT DEFAULT 0,
    commands_used JSON DEFAULT NULL,
    pages_viewed JSON DEFAULT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User sessions';

CALL CreateIndexIfNotExists('tenant_user_sessions', 'idx_tenant_sessions_user', '(user_telegram_id)');
CALL CreateIndexIfNotExists('tenant_user_sessions', 'idx_tenant_sessions_start', '(session_start)');
CALL CreateIndexIfNotExists('tenant_user_sessions', 'idx_tenant_sessions_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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) NOT NULL,
    commission_rate DECIMAL(5,2) DEFAULT 10.00,
    commission_earned DECIMAL(10,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending/converted/paid',
    converted_at TIMESTAMP NULL,
    level INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_tenant_referred (tenant_id, referred_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Referral tracking';

CALL CreateIndexIfNotExists('tenant_referrals', 'idx_tenant_referrals_referrer', '(referrer_id)');
CALL CreateIndexIfNotExists('tenant_referrals', 'idx_tenant_referrals_code', '(referral_code)');
CALL CreateIndexIfNotExists('tenant_referrals', 'idx_tenant_referrals_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    ticket_id VARCHAR(50) NOT NULL,
    user_telegram_id BIGINT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    category VARCHAR(50),
    status VARCHAR(20) DEFAULT 'open' COMMENT 'open/in_progress/resolved/closed',
    priority VARCHAR(20) DEFAULT 'medium' COMMENT 'low/medium/high/urgent',
    assigned_to INT,
    assigned_at TIMESTAMP NULL,
    resolved_by INT,
    resolved_at TIMESTAMP NULL,
    resolution_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_ticket_search (subject, message),
    UNIQUE KEY unique_tenant_ticket (tenant_id, ticket_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Support tickets';

CALL CreateIndexIfNotExists('tenant_support_tickets', 'idx_tenant_tickets_user', '(user_telegram_id)');
CALL CreateIndexIfNotExists('tenant_support_tickets', 'idx_tenant_tickets_status', '(status)');
CALL CreateIndexIfNotExists('tenant_support_tickets', 'idx_tenant_tickets_id', '(ticket_id)');
CALL CreateIndexIfNotExists('tenant_support_tickets', 'idx_tenant_tickets_tenant', '(tenant_id)');

CREATE TABLE IF NOT EXISTS tenant_ticket_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    ticket_id VARCHAR(50) NOT NULL,
    sender_type VARCHAR(20) NOT NULL COMMENT 'user/admin/system',
    sender_id BIGINT NOT NULL,
    message TEXT NOT NULL,
    attachments JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenant_registry(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id, ticket_id) REFERENCES tenant_support_tickets(tenant_id, ticket_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Replies to support tickets';

CALL CreateIndexIfNotExists('tenant_ticket_replies', 'idx_tenant_replies_ticket', '(ticket_id)');
CALL CreateIndexIfNotExists('tenant_ticket_replies', 'idx_tenant_replies_tenant', '(tenant_id)');

-- =====================================================
-- SECTION 5: SERVER MONITORING
-- =====================================================

CREATE TABLE IF NOT EXISTS server_health_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    reserve_id VARCHAR(50) NOT NULL,
    server_name VARCHAR(255),
    cpu_usage DECIMAL(5,2),
    ram_usage DECIMAL(5,2),
    disk_usage DECIMAL(5,2),
    network_in DECIMAL(10,2),
    network_out DECIMAL(10,2),
    bot_status VARCHAR(20),
    webhook_status VARCHAR(20),
    database_status VARCHAR(20),
    redis_status VARCHAR(20),
    response_time INT,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Periodic server health snapshots';

CALL CreateIndexIfNotExists('server_health_logs', 'idx_health_admin', '(admin_id)');
CALL CreateIndexIfNotExists('server_health_logs', 'idx_health_time', '(checked_at)');

CREATE TABLE IF NOT EXISTS server_alert_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    owner_id INT,
    rule_name VARCHAR(100) NOT NULL,
    metric VARCHAR(50) NOT NULL,
    threshold DECIMAL(5,2) NOT NULL,
    operator VARCHAR(10) NOT NULL,
    duration INT DEFAULT 5,
    notify_channels JSON DEFAULT NULL,
    notify_owner BOOLEAN DEFAULT FALSE,
    notify_admin BOOLEAN DEFAULT TRUE,
    auto_restart BOOLEAN DEFAULT FALSE,
    auto_scale BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Alert rules for monitoring';

CALL CreateIndexIfNotExists('server_alert_rules', 'idx_alert_admin', '(admin_id)');

CREATE TABLE IF NOT EXISTS server_incidents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    reserve_id VARCHAR(50) NOT NULL,
    incident_type VARCHAR(50) NOT NULL,
    severity VARCHAR(20) NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NULL,
    duration INT,
    affected_users INT,
    root_cause TEXT,
    resolution TEXT,
    status VARCHAR(20) DEFAULT 'open' COMMENT 'open/resolved',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Record of incidents';

CALL CreateIndexIfNotExists('server_incidents', 'idx_incident_admin', '(admin_id)');
CALL CreateIndexIfNotExists('server_incidents', 'idx_incident_status', '(status)');

-- =====================================================
-- SECTION 6: NOTIFICATION SYSTEM
-- =====================================================

CREATE TABLE IF NOT EXISTS notification_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT,
    template_name VARCHAR(100) NOT NULL,
    template_type VARCHAR(50) NOT NULL,
    subject TEXT,
    message TEXT NOT NULL,
    variables JSON DEFAULT NULL,
    channels JSON DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Templates for notifications';

CREATE TABLE IF NOT EXISTS notification_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    owner_id INT,
    notification_type VARCHAR(50) NOT NULL,
    channel VARCHAR(20) NOT NULL,
    recipient_type VARCHAR(20),
    recipient_id BIGINT,
    subject TEXT,
    message TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'sent' COMMENT 'sent/failed',
    error_message TEXT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivered_at TIMESTAMP NULL,
    metadata JSON DEFAULT NULL,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log of sent notifications';

CALL CreateIndexIfNotExists('notification_logs', 'idx_notification_recipient', '(recipient_type, recipient_id)');
CALL CreateIndexIfNotExists('notification_logs', 'idx_notification_time', '(sent_at)');
CALL CreateIndexIfNotExists('notification_logs', 'idx_notification_logs_composite', '(recipient_type, recipient_id, sent_at)');

-- =====================================================
-- SECTION 7: BLOCKED USERS
-- =====================================================

CREATE TABLE IF NOT EXISTS blocked_users_global (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL,
    block_type VARCHAR(20) DEFAULT 'global' COMMENT 'global or per-bot',
    reserve_id VARCHAR(50) COMMENT 'If per-bot, which bot',
    blocked_by INT,
    block_reason TEXT,
    blocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    UNIQUE KEY unique_block (telegram_id, reserve_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Globally blocked users';

CALL CreateIndexIfNotExists('blocked_users_global', 'idx_blocked_telegram', '(telegram_id)');

-- =====================================================
-- SECTION 8: RECHARGE SYSTEM
-- =====================================================

CREATE TABLE IF NOT EXISTS recharge_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_name VARCHAR(50) NOT NULL UNIQUE,
    duration_days INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    key_limit INT DEFAULT 1000,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Recharge plans for admins';

INSERT IGNORE INTO recharge_plans (plan_name, duration_days, price, key_limit, description) VALUES
('Monthly', 30, 200, 1000, 'Monthly recharge plan'),
('6 Months', 180, 900, 6000, 'Half year plan with savings'),
('Yearly', 365, 1700, 12000, 'Yearly plan with best value');

CREATE TABLE IF NOT EXISTS recharge_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    owner_id INT NOT NULL,
    plan_type VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50),
    transaction_id VARCHAR(255),
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,
    status VARCHAR(20) DEFAULT 'completed' COMMENT 'completed/refunded/failed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='History of admin recharges';

CALL CreateIndexIfNotExists('recharge_history', 'idx_recharge_admin', '(admin_id)');
CALL CreateIndexIfNotExists('recharge_history', 'idx_recharge_dates', '(start_date, end_date)');

-- =====================================================
-- SECTION 9: SYSTEM CONFIGURATION
-- =====================================================

CREATE TABLE IF NOT EXISTS system_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    config_type VARCHAR(50) DEFAULT 'string' COMMENT 'string/boolean/number/json',
    description TEXT,
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='System-wide settings';

INSERT IGNORE INTO system_config (config_key, config_value, config_type, description) VALUES
('system_name', 'Telegram Multi-Tenant Bot System', 'string', 'Name of the system'),
('version', '1.0.0', 'string', 'System version'),
('maintenance_mode', 'false', 'boolean', 'Put system in maintenance mode'),
('default_language', 'en', 'string', 'Default language'),
('currency', 'INR', 'string', 'Default currency'),
('timezone', 'Asia/Kolkata', 'string', 'System timezone'),
('max_admins_per_owner', '1000', 'number', 'Maximum admins per owner'),
('max_keys_per_batch', '10000', 'number', 'Maximum keys per batch upload'),
('session_timeout', '3600', 'number', 'Admin session timeout in seconds'),
('backup_enabled', 'true', 'boolean', 'Enable automatic backups'),
('backup_frequency', 'daily', 'string', 'Backup frequency'),
('log_retention_days', '90', 'number', 'Days to keep logs'),
('api_rate_limit', '100', 'number', 'API rate limit per minute'),
('user_activity_tracking', 'true', 'boolean', 'Track user activities'),
('admin_activity_tracking', 'true', 'boolean', 'Track admin activities');

-- =====================================================
-- SECTION 10: AUDIT LOGS
-- =====================================================

CREATE TABLE IF NOT EXISTS audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    actor_type VARCHAR(20) NOT NULL COMMENT 'owner/admin/system',
    actor_id INT,
    actor_telegram_id BIGINT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id VARCHAR(100),
    old_values JSON DEFAULT NULL,
    new_values JSON DEFAULT NULL,
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Audit trail for sensitive actions';

CALL CreateIndexIfNotExists('audit_logs', 'idx_audit_actor', '(actor_type, actor_id)');
CALL CreateIndexIfNotExists('audit_logs', 'idx_audit_entity', '(entity_type, entity_id)');
CALL CreateIndexIfNotExists('audit_logs', 'idx_audit_time', '(created_at)');

-- =====================================================
-- SECTION 11: BACKUP HISTORY
-- =====================================================

CREATE TABLE IF NOT EXISTS backup_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_id VARCHAR(100) UNIQUE NOT NULL,
    backup_type VARCHAR(50) NOT NULL COMMENT 'manual/automated',
    backup_size BIGINT,
    file_path VARCHAR(500),
    included_tables JSON DEFAULT NULL,
    status VARCHAR(20) DEFAULT 'completed' COMMENT 'completed/failed',
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    error_message TEXT,
    created_by INT,
    restored_at TIMESTAMP NULL,
    restored_by INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Record of database backups';

-- =====================================================
-- SECTION 12: API KEYS
-- =====================================================

CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT,
    admin_id INT,
    api_key VARCHAR(255) UNIQUE NOT NULL,
    api_secret VARCHAR(255),
    api_name VARCHAR(100),
    permissions JSON DEFAULT NULL,
    rate_limit INT DEFAULT 100,
    last_used TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='API keys for external integrations';

CALL CreateIndexIfNotExists('api_keys', 'idx_api_key', '(api_key)');
CALL CreateIndexIfNotExists('api_keys', 'idx_api_owner', '(owner_id)');
CALL CreateIndexIfNotExists('api_keys', 'idx_api_admin', '(admin_id)');

-- =====================================================
-- SECTION 13: GLOBAL BROADCASTS
-- =====================================================

CREATE TABLE IF NOT EXISTS broadcasts_global (
    id INT AUTO_INCREMENT PRIMARY KEY,
    broadcast_id VARCHAR(50) UNIQUE NOT NULL,
    owner_id INT,
    message TEXT NOT NULL,
    target_bots JSON DEFAULT NULL COMMENT 'List of bot reserve_ids or null for all',
    scheduled_for TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    status VARCHAR(20),
    total_recipients INT,
    delivered_count INT,
    failed_count INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Broadcasts sent by owner to all bots';

-- =====================================================
-- SECTION 14: VIEWS FOR COMMON QUERIES
-- =====================================================

DROP VIEW IF EXISTS v_admin_summary;
DROP VIEW IF EXISTS v_activity_summary;
DROP VIEW IF EXISTS v_revenue_summary;

CREATE VIEW v_admin_summary AS
SELECT 
    a.id,
    a.reserve_id,
    a.admin_username,
    a.bot_name,
    a.recharge_status,
    a.recharge_expiry,
    a.status,
    (SELECT COUNT(DISTINCT tu.id) FROM tenant_users tu WHERE tu.tenant_id = a.id) as total_users,
    a.total_keys_generated,
    a.last_login,
    (SELECT COUNT(*) FROM admin_activity_log aal WHERE aal.admin_id = a.id) as total_activities
FROM admins a;

CREATE VIEW v_activity_summary AS
SELECT 
    DATE(created_at) as activity_date,
    COUNT(*) as total_activities,
    SUM(CASE WHEN activity_type LIKE 'purchase%' THEN 1 ELSE 0 END) as purchases,
    SUM(CASE WHEN activity_type = 'download' THEN 1 ELSE 0 END) as downloads,
    SUM(CASE WHEN activity_type = 'start' THEN 1 ELSE 0 END) as starts
FROM tenant_user_activity_log
GROUP BY DATE(created_at);

CREATE VIEW v_revenue_summary AS
SELECT 
    DATE(completed_at) as revenue_date,
    COUNT(*) as transactions,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_transaction
FROM tenant_transactions
WHERE status = 'success'
GROUP BY DATE(completed_at);

-- =====================================================
-- SECTION 15: STORED PROCEDURES
-- =====================================================

DROP PROCEDURE IF EXISTS GetTenantRevenue;
DROP PROCEDURE IF EXISTS GetTopUsers;
DROP PROCEDURE IF EXISTS GetKeyInventory;

DELIMITER $$

CREATE PROCEDURE GetTenantRevenue(IN p_tenant_id INT, IN p_start DATE, IN p_end DATE)
BEGIN
    SELECT
        DATE(completed_at) as revenue_date,
        COUNT(*) as transactions,
        SUM(amount) as daily_revenue
    FROM tenant_transactions
    WHERE tenant_id = p_tenant_id
      AND status = 'success'
      AND DATE(completed_at) BETWEEN p_start AND p_end
    GROUP BY DATE(completed_at)
    ORDER BY revenue_date;
END$$

CREATE PROCEDURE GetTopUsers(IN p_tenant_id INT, IN p_limit INT)
BEGIN
    SELECT
        tu.telegram_id,
        tu.username,
        tu.total_purchases,
        tu.total_spent,
        tu.last_purchase_date
    FROM tenant_users tu
    WHERE tu.tenant_id = p_tenant_id
      AND tu.deleted_at IS NULL
    ORDER BY tu.total_spent DESC
    LIMIT p_limit;
END$$

CREATE PROCEDURE GetKeyInventory(IN p_tenant_id INT)
BEGIN
    SELECT
        duration,
        COUNT(*) as total,
        SUM(CASE WHEN status = 'available' THEN 1 ELSE 0 END) as available,
        SUM(CASE WHEN status = 'sold' THEN 1 ELSE 0 END) as sold,
        SUM(CASE WHEN status = 'blocked' THEN 1 ELSE 0 END) as blocked
    FROM tenant_keys
    WHERE tenant_id = p_tenant_id
    GROUP BY duration;
END$$

DELIMITER ;

-- =====================================================
-- SECTION 16: TRIGGERS
-- =====================================================

DROP TRIGGER IF EXISTS update_admins_updated_at;
DROP TRIGGER IF EXISTS update_owners_updated_at;
DROP TRIGGER IF EXISTS check_admin_recharge_expiry;
DROP TRIGGER IF EXISTS log_admin_action_update;
DROP TRIGGER IF EXISTS update_key_status_on_use;

DELIMITER $$

CREATE TRIGGER update_admins_updated_at 
    BEFORE UPDATE ON admins 
    FOR EACH ROW 
    SET NEW.updated_at = CURRENT_TIMESTAMP;

CREATE TRIGGER update_owners_updated_at 
    BEFORE UPDATE ON owners 
    FOR EACH ROW 
    SET NEW.updated_at = CURRENT_TIMESTAMP;

CREATE TRIGGER check_admin_recharge_expiry
    BEFORE UPDATE ON admins
    FOR EACH ROW
    BEGIN
        IF NEW.recharge_expiry < CURRENT_TIMESTAMP AND NEW.recharge_status = 'active' THEN
            SET NEW.recharge_status = 'expired';
            SET NEW.status = 'inactive';
        END IF;
    END;

CREATE TRIGGER log_admin_action_update
    AFTER UPDATE ON admins
    FOR EACH ROW
    BEGIN
        INSERT INTO admin_activity_log (admin_id, reserve_id, activity_type, activity_details, performed_at)
        VALUES (NEW.id, NEW.reserve_id, 'profile_updated', 
                JSON_OBJECT('old', JSON_OBJECT('username', OLD.admin_username, 'status', OLD.status), 
                            'new', JSON_OBJECT('username', NEW.admin_username, 'status', NEW.status)), 
                CURRENT_TIMESTAMP);
    END;

CREATE TRIGGER update_key_status_on_use
    BEFORE UPDATE ON tenant_keys
    FOR EACH ROW
    BEGIN
        IF NEW.status = 'sold' AND OLD.status = 'available' THEN
            SET NEW.used_at = CURRENT_TIMESTAMP;
            SET NEW.issued_at = CURRENT_TIMESTAMP;
        END IF;
    END;

DELIMITER ;

-- =====================================================
-- SECTION 17: EVENTS FOR AUTOMATED CLEANUP
-- =====================================================

DROP EVENT IF EXISTS clean_old_activity_logs;
DROP EVENT IF EXISTS clean_old_sessions;
DROP EVENT IF EXISTS expire_keys;

DELIMITER $$

CREATE EVENT IF NOT EXISTS clean_old_activity_logs
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
    DELETE FROM tenant_user_activity_log
    WHERE created_at < NOW() - INTERVAL 90 DAY;
END$$

CREATE EVENT IF NOT EXISTS clean_old_sessions
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 2 HOUR
DO
BEGIN
    DELETE FROM tenant_user_sessions
    WHERE session_end IS NOT NULL AND session_end < NOW() - INTERVAL 30 DAY;
END$$

CREATE EVENT IF NOT EXISTS expire_keys
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    UPDATE tenant_keys
    SET status = 'expired'
    WHERE status = 'sold' AND expires_at < NOW();
END$$

DELIMITER ;

-- =====================================================
-- SECTION 18: INITIAL DATA
-- =====================================================

INSERT IGNORE INTO owners (telegram_id, username, first_name, password_hash) 
VALUES (123456789, 'owner', 'System Owner', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'); -- password: password

INSERT IGNORE INTO admins (
    owner_id, telegram_id, admin_username, admin_first_name, 
    password_hash, reserve_id, bot_name, bot_username, bot_token,
    payment_gateway, payment_api_key, payment_api_secret
) VALUES (
    1, 987654321, 'admin', 'Admin User',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'TEST-001',
    'Test Bot', '@test_bot', '123456:ABCdefGHIjklMNOpqrsTUVwxyz',
    'razorpay', 'test_key', 'test_secret'
);

-- =====================================================
-- CLEANUP HELPER PROCEDURE
-- =====================================================
DROP PROCEDURE IF EXISTS CreateIndexIfNotExists;

-- =====================================================
-- FINALIZE
-- =====================================================

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