-- Code Breaker Mining Database Schema + Demo Data

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Admin Table
-- --------------------------------------------------------
CREATE TABLE `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Settings Table (key-value config store)
-- --------------------------------------------------------
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Announcements Table
-- --------------------------------------------------------
CREATE TABLE `announcements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `type` enum('INFO','WARNING','PROMO','MAINTENANCE') DEFAULT 'INFO',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Users Table
-- --------------------------------------------------------
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `telegram_id` varchar(50) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `profile_photo` varchar(255) DEFAULT NULL,
  `wallet_balance` decimal(15,2) DEFAULT 0.00,
  `mining_points` decimal(15,4) DEFAULT 0.0000,
  `level` int(11) DEFAULT 1,
  `energy` int(11) DEFAULT 100,
  `referral_code` varchar(20) NOT NULL,
  `referred_by` varchar(20) DEFAULT NULL,
  `is_banned` tinyint(1) DEFAULT 0,
  `last_login` datetime DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `telegram_id` (`telegram_id`),
  UNIQUE KEY `referral_code` (`referral_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Daily Codes
-- --------------------------------------------------------
CREATE TABLE `daily_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `active_date` date NOT NULL,
  `is_disabled` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `active_date` (`active_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Mining Sessions
-- --------------------------------------------------------
CREATE TABLE `mining_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `daily_code_id` int(11) NOT NULL,
  `start_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `last_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_daily_code` (`user_id`, `daily_code_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Referrals
-- --------------------------------------------------------
CREATE TABLE `referrals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `referrer_id` int(11) NOT NULL,
  `referred_id` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `reward_amount` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`referred_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Wheel Rewards
-- --------------------------------------------------------
CREATE TABLE `wheel_rewards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reward_type` enum('COINS', 'ENERGY', 'BOOST', 'RARE') NOT NULL,
  `label` varchar(50) NOT NULL,
  `reward_value` decimal(15,2) NOT NULL,
  `probability` decimal(5,2) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- NFT Store
-- --------------------------------------------------------
CREATE TABLE `nft_store` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `image_url` varchar(255) NOT NULL,
  `price_ton` decimal(10,4) NOT NULL,
  `wallet_address` varchar(100) NOT NULL,
  `boost_multiplier` decimal(5,2) DEFAULT 1.00,
  `max_purchases` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- NFT Purchases
-- --------------------------------------------------------
CREATE TABLE `nft_purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `nft_id` int(11) NOT NULL,
  `payment_screenshot` varchar(255) NOT NULL,
  `sender_wallet` varchar(100) NOT NULL,
  `status` enum('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'PENDING',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`nft_id`) REFERENCES `nft_store`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tasks
-- --------------------------------------------------------
CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `reward_coins` decimal(15,2) NOT NULL,
  `action_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- User Tasks (completed)
-- --------------------------------------------------------
CREATE TABLE `user_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `completed_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_task` (`user_id`, `task_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Withdraw Requests
-- --------------------------------------------------------
CREATE TABLE `withdraw_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `wallet_address` varchar(100) NOT NULL,
  `network` varchar(50) DEFAULT 'TON',
  `status` enum('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'PENDING',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Transactions
-- --------------------------------------------------------
CREATE TABLE `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `type` enum('DEPOSIT', 'WITHDRAW', 'MINING', 'REFERRAL', 'WHEEL', 'TASK_REWARD', 'ADMIN_ADJUST') NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================================
-- DEMO DATA
-- ========================================================

-- Admin Account (username: admin, password: admin123)
INSERT INTO `admins` (`username`, `password`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- Default Settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('base_mining_speed', '10'),
('mining_session_duration', '24'),
('mining_cooldown', '0'),
('min_mining_coins', '0'),
('max_mining_coins', '999999'),
('max_offline_hours', '3'),
('level_2_multiplier', '1.2'),
('level_3_multiplier', '1.5'),
('level_4_multiplier', '2.0'),
('nft_boost_default', '1.5'),
('referral_level_1', '500'),
('referral_level_2', '200'),
('referral_level_3', '100'),
('referral_enabled', '1'),
('ads_enabled', '1'),
('ads_script', ''),
('ads_reward_multiplier', '2'),
('ads_reward_duration', '10'),
('bot_token', '7329787661:AAGZi-EljQxCaQ2XKvdtzq7ru6WM6QIAMJA'),
('bot_group_id', ''),
('bot_meme_time', '18:00'),
('bot_leaderboard_interval', '6'),
('withdraw_min', '100'),
('withdraw_max', '50000');

-- Demo Daily Code for Today
INSERT INTO `daily_codes` (`code`, `active_date`) VALUES
('X9F4T', CURDATE());

-- Demo Wheel Rewards
INSERT INTO `wheel_rewards` (`reward_type`, `label`, `reward_value`, `probability`, `is_active`) VALUES
('COINS', '50 Coins', 50.00, 40.00, 1),
('COINS', '100 Coins', 100.00, 25.00, 1),
('BOOST', '2x Mining Boost', 2.00, 20.00, 1),
('ENERGY', 'Energy Refill', 100.00, 10.00, 1),
('RARE', 'Rare Reward', 500.00, 5.00, 1);

-- Demo NFTs
INSERT INTO `nft_store` (`name`, `description`, `image_url`, `price_ton`, `wallet_address`, `boost_multiplier`) VALUES
('Golden Miner', 'Legendary mining boost NFT. Doubles your mining speed permanently.', 'https://via.placeholder.com/300/1a1a2e/00ffff?text=GOLDEN+MINER', 5.0000, 'UQBxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 2.00),
('Silver Shield', 'Epic energy regeneration boost. +50% energy capacity.', 'https://via.placeholder.com/300/1a1a2e/b026ff?text=SILVER+SHIELD', 3.0000, 'UQBxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1.50),
('Cyber Core', 'Rare speed boost. +30% mining speed.', 'https://via.placeholder.com/300/1a1a2e/facc15?text=CYBER+CORE', 2.0000, 'UQBxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 1.30);

-- Demo Tasks
INSERT INTO `tasks` (`title`, `description`, `reward_coins`, `action_url`, `is_active`) VALUES
('Join Telegram Channel', 'Join our official Telegram channel to earn coins.', 500.00, 'https://t.me/your_channel', 1),
('Follow on Twitter/X', 'Follow our official X account.', 400.00, 'https://x.com/your_account', 1),
('Watch Intro Video', 'Watch a short intro video about Code Breaker.', 200.00, 'https://youtube.com/watch?v=demo', 1),
('Invite 3 Friends', 'Invite 3 friends using your referral link.', 1000.00, NULL, 1);

-- Demo Users
INSERT INTO `users` (`telegram_id`, `username`, `first_name`, `wallet_balance`, `mining_points`, `level`, `energy`, `referral_code`, `referred_by`, `last_login`, `created_at`) VALUES
('111111111', 'demo_user1', 'Alice', 1500.00, 245.5000, 2, 100, 'REF001AA', NULL, NOW(), DATE_SUB(NOW(), INTERVAL 7 DAY)),
('222222222', 'demo_user2', 'Bob', 800.00, 120.3000, 1, 80, 'REF002BB', 'REF001AA', NOW(), DATE_SUB(NOW(), INTERVAL 5 DAY)),
('333333333', 'demo_user3', 'Charlie', 2200.00, 530.7500, 3, 100, 'REF003CC', 'REF001AA', NOW(), DATE_SUB(NOW(), INTERVAL 3 DAY)),
('444444444', 'demo_user4', 'Diana', 350.00, 55.2000, 1, 60, 'REF004DD', 'REF002BB', NOW(), DATE_SUB(NOW(), INTERVAL 1 DAY)),
('555555555', 'demo_user5', 'Eve', 4500.00, 980.0000, 4, 100, 'REF005EE', NULL, NOW(), DATE_SUB(NOW(), INTERVAL 10 DAY));

-- Demo Referrals
INSERT INTO `referrals` (`referrer_id`, `referred_id`, `level`, `reward_amount`) VALUES
(1, 2, 1, 500.00),
(1, 3, 1, 500.00),
(2, 4, 1, 500.00),
(1, 4, 2, 200.00);

-- Demo Transactions
INSERT INTO `transactions` (`user_id`, `amount`, `type`, `description`) VALUES
(1, 500.00, 'REFERRAL', 'Referral reward - Level 1'),
(1, 500.00, 'REFERRAL', 'Referral reward - Level 1'),
(1, 200.00, 'REFERRAL', 'Referral reward - Level 2'),
(2, 500.00, 'REFERRAL', 'Referral reward - Level 1'),
(1, 50.00, 'WHEEL', 'Lucky wheel spin'),
(3, 100.00, 'WHEEL', 'Lucky wheel spin'),
(5, 500.00, 'TASK_REWARD', 'Joined Telegram Channel');

-- Demo Announcements
INSERT INTO `announcements` (`title`, `message`, `type`, `is_active`) VALUES
('Welcome to Code Breaker Mining!', 'Start mining by entering the daily code. Watch ads to reveal hints!', 'INFO', 1),
('New NFT Drop: Golden Miner', 'Get 2x mining speed permanently with our latest NFT. Available now in the Store!', 'PROMO', 1);

COMMIT;