-- ⚡ NEXUS SMM Telegram Bot - Complete MySQL Schema
-- Import this file into phpMyAdmin or MySQL Database

CREATE TABLE IF NOT EXISTS `users` (
    `user_id` BIGINT PRIMARY KEY,
    `username` VARCHAR(255) DEFAULT '',
    `full_name` VARCHAR(255) DEFAULT '',
    `balance` DOUBLE DEFAULT 0.0,
    `total_spent` DOUBLE DEFAULT 0.0,
    `total_orders` INT DEFAULT 0,
    `level` VARCHAR(255) DEFAULT '🥉 Bronze',
    `discount_pct` DOUBLE DEFAULT 0.0,
    `referred_by` BIGINT DEFAULT NULL,
    `ref_earnings` DOUBLE DEFAULT 0.0,
    `is_banned` INT DEFAULT 0,
    `ban_reason` VARCHAR(255) DEFAULT '',
    `force_join_verified` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `services` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `platform` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `api_service_id` VARCHAR(255) NOT NULL,
    `service_type` VARCHAR(255) DEFAULT 'auto',
    `price_per_unit` DOUBLE NOT NULL,
    `min_qty` INT NOT NULL,
    `max_qty` INT NOT NULL,
    `target_prompt` VARCHAR(255) DEFAULT '',
    `description` TEXT DEFAULT '',
    `speed` VARCHAR(255) DEFAULT 'Fast',
    `quality` VARCHAR(255) DEFAULT 'High',
    `is_enabled` INT DEFAULT 1,
    `display_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `platforms` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `emoji` VARCHAR(255) DEFAULT '🌐',
    `web_link` TEXT DEFAULT '',
    `is_web_link` INT DEFAULT 0,
    `is_enabled` INT DEFAULT 1,
    `display_order` INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `service_id` INT NOT NULL,
    `api_service_id` VARCHAR(255) DEFAULT '',
    `api_order_id` VARCHAR(255) DEFAULT '',
    `target_link` TEXT NOT NULL,
    `quantity` INT NOT NULL,
    `cost` DOUBLE NOT NULL,
    `start_count` INT DEFAULT 0,
    `remains` INT DEFAULT 0,
    `status` VARCHAR(255) DEFAULT 'pending',
    `is_manual` INT DEFAULT 0,
    `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;

CREATE TABLE IF NOT EXISTS `transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `type` VARCHAR(255) NOT NULL,
    `amount` DOUBLE NOT NULL,
    `balance_after` DOUBLE NOT NULL,
    `description` TEXT DEFAULT '',
    `trx_id` VARCHAR(255) DEFAULT '',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `force_join_channels` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `channel_id` VARCHAR(255) NOT NULL,
    `channel_name` VARCHAR(255) NOT NULL,
    `invite_link` VARCHAR(255) DEFAULT '',
    `is_active` INT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `manual_deposits` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `method` VARCHAR(255) NOT NULL,
    `amount` DOUBLE NOT NULL,
    `trx_id` VARCHAR(255) NOT NULL,
    `screenshot_file_id` VARCHAR(255) DEFAULT '',
    `status` VARCHAR(255) DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `gift_codes` (
    `code` VARCHAR(255) PRIMARY KEY,
    `amount` DOUBLE NOT NULL,
    `max_uses` INT DEFAULT 1,
    `used_count` INT DEFAULT 0,
    `created_by` BIGINT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `gift_code_redemptions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(255) NOT NULL,
    `user_id` BIGINT NOT NULL,
    `amount` DOUBLE NOT NULL,
    `redeemed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bot_settings` (
    `key` VARCHAR(255) PRIMARY KEY,
    `value` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `admin_id` BIGINT NOT NULL,
    `action` VARCHAR(255) NOT NULL,
    `details` TEXT DEFAULT '',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `daily_spins` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `prize_amount` DOUBLE NOT NULL,
    `spun_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_states` (
    `user_id` BIGINT PRIMARY KEY,
    `state_data` TEXT NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
