-- =====================================================================
-- Telegram WebApp Mini Oyun Projesi - Veritabanı Şeması ve Başlangıç Verileri
-- Karakter Seti: utf8mb4 (Emoji ve Çoklu Dil TR, UZ, RU, EN Desteği)
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `user_inventory`;
DROP TABLE IF EXISTS `market_items`;
DROP TABLE IF EXISTS `withdrawals`;
DROP TABLE IF EXISTS `daily_game_scores`;
DROP TABLE IF EXISTS `daily_revenue_pools`;
DROP TABLE IF EXISTS `user_tasks`;
DROP TABLE IF EXISTS `tasks`;
DROP TABLE IF EXISTS `user_social_accounts`;
DROP TABLE IF EXISTS `game_rooms`;
DROP TABLE IF EXISTS `users`;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. KULLANICILAR TABLOSU
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `username` VARCHAR(64) NULL,
  `first_name` VARCHAR(128) NOT NULL,
  `last_name` VARCHAR(128) NULL,
  `language_code` VARCHAR(5) NOT NULL DEFAULT 'tr', -- 'tr', 'uz', 'ru', 'en'
  `energy` INT NOT NULL DEFAULT 5,                  -- Görevle kazanılan enerji
  `max_energy` INT NOT NULL DEFAULT 10,
  `coin_balance` DECIMAL(16, 4) NOT NULL DEFAULT 0.0000, -- Çekilebilir proje parası
  `referrer_id` BIGINT UNSIGNED NULL,
  `selected_skin_id` INT UNSIGNED NOT NULL DEFAULT 1,
  `auth_token` VARCHAR(64) NULL,                    -- API session token
  `is_banned` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_telegram_id` (`telegram_id`),
  KEY `idx_referrer_id` (`referrer_id`),
  CONSTRAINT `fk_users_referrer` FOREIGN KEY (`referrer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. KULLANICI BAĞLI SOSYAL MEDYA HESAPLARI (Profil Menüsü)
CREATE TABLE `user_social_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `provider` ENUM('instagram', 'youtube', 'tiktok', 'telegram') NOT NULL,
  `account_handle` VARCHAR(128) NOT NULL,
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `linked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_provider` (`user_id`, `provider`),
  CONSTRAINT `fk_social_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. GÖREVLER (Surfing, Telegram, Instagram, Youtube, Referans)
CREATE TABLE `tasks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` ENUM('surfing', 'telegram', 'instagram', 'youtube', 'referral') NOT NULL,
  `title_tr` VARCHAR(255) NOT NULL,
  `title_uz` VARCHAR(255) NOT NULL,
  `title_ru` VARCHAR(255) NOT NULL,
  `title_en` VARCHAR(255) NOT NULL,
  `desc_tr` TEXT NULL,
  `desc_uz` TEXT NULL,
  `desc_ru` TEXT NULL,
  `desc_en` TEXT NULL,
  `reward_energy` INT NOT NULL DEFAULT 1,          -- Görev tamamlanınca verilen enerji
  `target_url` TEXT NULL,                          -- Tıklanacak video, kanal veya web linki
  `channel_chat_id` VARCHAR(64) NULL,              -- Telegram kanalı kontrolü için ID (-100...)
  `watch_duration_sec` INT NOT NULL DEFAULT 0,     -- Minimum bekleme süresi (sn)
  `provider_type` VARCHAR(32) NOT NULL DEFAULT 'manual', -- 'adsgram', 'tg_bot', 'youtube_iframe', 'link'
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `order_index` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category_active` (`category`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. KULLANICI GÖREV GEÇMİŞİ
CREATE TABLE `user_tasks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `task_id` INT UNSIGNED NOT NULL,
  `status` ENUM('in_progress', 'completed', 'claimed') NOT NULL DEFAULT 'in_progress',
  `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `completed_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_task` (`user_id`, `task_id`),
  CONSTRAINT `fk_ut_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ut_task` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. GÜNLÜK GELİR VE REYTİNG ÖDÜL HAVUZU (0% Bankrotlik Kafolati)
CREATE TABLE `daily_revenue_pools` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pool_date` DATE NOT NULL,
  `total_ads_revenue` DECIMAL(12, 4) NOT NULL DEFAULT 0.0000,     -- Adsgram + Surfing
  `total_sponsor_revenue` DECIMAL(12, 4) NOT NULL DEFAULT 0.0000, -- B2B Reklamlar
  `total_revenue` DECIMAL(12, 4) GENERATED ALWAYS AS (`total_ads_revenue` + `total_sponsor_revenue`) STORED,
  `pool_amount_40` DECIMAL(12, 4) GENERATED ALWAYS AS ((`total_ads_revenue` + `total_sponsor_revenue`) * 0.40) STORED, -- %40 Top-100 Havuzu
  `platform_profit_60` DECIMAL(12, 4) GENERATED ALWAYS AS ((`total_ads_revenue` + `total_sponsor_revenue`) * 0.60) STORED, -- %60 Şirket Payı
  `is_distributed` TINYINT(1) NOT NULL DEFAULT 0,
  `distributed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_pool_date` (`pool_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. GÜNLÜK OYUN SKORLARI & REYTİNG (0% Inflyatsiya Kafolati)
CREATE TABLE `daily_game_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `score_date` DATE NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `daily_high_score` INT NOT NULL DEFAULT 0, -- O günkü en yüksek kaplanan alan puanı
  `matches_played` INT NOT NULL DEFAULT 0,
  `kills_count` INT NOT NULL DEFAULT 0,
  `final_rank` INT NULL,                    -- Gece 23:59:59'daki kesinleşen sıra (1-100)
  `reward_coin` DECIMAL(14, 4) NOT NULL DEFAULT 0.0000, -- Havuzdan aldığı coin
  `is_paid` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_date_user` (`score_date`, `user_id`),
  KEY `idx_ranking` (`score_date`, `daily_high_score` DESC),
  CONSTRAINT `fk_score_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. MARKET ÜRÜNLERİ (Skinler ve Dijital Ürünler)
CREATE TABLE `market_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_type` ENUM('skin', 'digital_voucher', 'telecom_data') NOT NULL,
  `name_tr` VARCHAR(128) NOT NULL,
  `name_uz` VARCHAR(128) NOT NULL,
  `name_ru` VARCHAR(128) NOT NULL,
  `name_en` VARCHAR(128) NOT NULL,
  `description_tr` TEXT,
  `description_uz` TEXT,
  `description_ru` TEXT,
  `description_en` TEXT,
  `price_coin` DECIMAL(14, 4) NOT NULL,
  `asset_color` VARCHAR(32) NOT NULL DEFAULT '#3b82f6', -- Skin rengi hex
  `asset_url` VARCHAR(255) NULL,
  `stock_quantity` INT NOT NULL DEFAULT -1,             -- -1: Sınırsız
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. KULLANICI ENVANTERİ
CREATE TABLE `user_inventory` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `item_id` INT UNSIGNED NOT NULL,
  `purchased_price` DECIMAL(14, 4) NOT NULL,
  `voucher_code` VARCHAR(128) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_item` (`user_id`, `item_id`),
  CONSTRAINT `fk_inv_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inv_item` FOREIGN KEY (`item_id`) REFERENCES `market_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. PARA ÇEKİM TALEPLERİ (Withdrawals)
CREATE TABLE `withdrawals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `amount_coin` DECIMAL(14, 4) NOT NULL,
  `payout_method` ENUM('usdt_ton', 'usdt_trc20', 'payme', 'click', 'papara') NOT NULL,
  `payout_address` VARCHAR(255) NOT NULL, -- Cüzdan veya Kart Numarası
  `status` ENUM('pending', 'approved', 'rejected', 'completed') NOT NULL DEFAULT 'pending',
  `tx_hash` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `processed_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`, `status`),
  CONSTRAINT `fk_withdraw_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. OYUN ODALARI (Çok Oyunculu Eşleşme)
CREATE TABLE `game_rooms` (
  `room_id` VARCHAR(36) NOT NULL,
  `status` ENUM('waiting', 'countdown', 'active', 'finished') NOT NULL DEFAULT 'waiting',
  `player_count` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `max_players` TINYINT UNSIGNED NOT NULL DEFAULT 10,
  `min_players` TINYINT UNSIGNED NOT NULL DEFAULT 4,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`room_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- BAŞLANGIÇ VERİLERİ (SEED DATA)
-- =====================================================================

-- Varsayılan Skinler ve Dijital Ürünler
INSERT INTO `market_items` (`id`, `item_type`, `name_tr`, `name_uz`, `name_ru`, `name_en`, `description_tr`, `description_uz`, `description_ru`, `description_en`, `price_coin`, `asset_color`, `is_active`) VALUES
(1, 'skin', 'Neon Mavi (Varsayılan)', 'Neon Moviy (Boshlangich)', 'Неоновый Синий (Стандарт)', 'Neon Blue (Default)', 'Klasik neon mavi iz ve alan rengi.', 'Klassik neon moviy iz va maydon rangi.', 'Классический синий неоновый след.', 'Classic neon blue trail and territory.', 0.0000, '#00d2ff', 1),
(2, 'skin', 'Siber Kırmızı', 'Kiber Qizil', 'Кибер Красный', 'Cyber Red', 'Agresif kırmızı siberpunk parıltısı.', 'Agressiv qizil kiberpank porlashi.', 'Агрессивный красный киберпанк свет.', 'Aggressive red cyberpunk glow.', 5.0000, '#ff0055', 1),
(3, 'skin', 'Zehirli Yeşil', 'Zaharli Yashil', 'Ядовито-Зеленый', 'Toxic Green', 'Göz alıcı zümrüt ve asit yeşili.', 'Ko''zni qamashtiruvchi zumrad va kislotali yashil.', 'Яркий ядовито-зеленый след.', 'Striking radioactive green trail.', 8.0000, '#00ff88', 1),
(4, 'skin', 'Kraliyet Altını', 'Qirollik Oltini', 'Королевское Золото', 'Royal Gold', 'Şampiyonlara özel parlak altın deseni.', 'Chempionlar uchun maxsus yaltiroq oltin.', 'Золотой след для чемпионов.', 'Shiny golden pattern for champions.', 15.0000, '#ffd700', 1),
(5, 'digital_voucher', 'Telegram Premium (1 Ay)', 'Telegram Premium (1 Oy)', 'Telegram Premium (1 Месяц)', 'Telegram Premium (1 Month)', 'Resmi Telegram Premium hediye kodu.', 'Rasmiy Telegram Premium sovg''a kodi.', 'Подарочный код Telegram Premium.', 'Official Telegram Premium gift code.', 25.0000, '#a855f7', 1),
(6, 'telecom_data', '10 GB İnternet Paketi', '10 GB Internet Paketi', '10 ГБ Интернет Пакет', '10 GB Data Bundle', 'Yerel operatörler için 10 GB mobil data.', 'Mahalliy aloqa operatorlari uchun 10 GB.', '10 ГБ мобильного интернета.', '10 GB high-speed mobile data voucher.', 12.0000, '#3b82f6', 1);

-- Görevler Listesi (Kategorili: Surfing, Telegram, Instagram, Youtube, Referral)
INSERT INTO `tasks` (`id`, `category`, `title_tr`, `title_uz`, `title_ru`, `title_en`, `desc_tr`, `desc_uz`, `desc_ru`, `desc_en`, `reward_energy`, `target_url`, `channel_chat_id`, `watch_duration_sec`, `provider_type`, `order_index`) VALUES
(1, 'surfing', 'Adsgram Ödüllü Video İzle', 'Adsgram Mukofotli Video Ko''rish', 'Adsgram Просмотр Видео', 'Watch Adsgram Rewarded Video', 'Kısa bir reklam videosu izleyin ve anında +1 Enerji kazanın.', 'Qisqa reklama videosini ko''ring va darhol +1 Energiya oling.', 'Посмотрите короткое видео и получите +1 Энергию.', 'Watch a short rewarded ad video and get +1 Energy instantly.', 1, 'adsgram_trigger', NULL, 15, 'adsgram', 1),
(2, 'surfing', 'Web Sitesi Sörfü (30 Saniye)', 'Web Sayt Serfingi (30 Soniya)', 'Веб-серфинг Сайта (30 Сек)', 'Web Surfing Visit (30 Sec)', 'Sponsor web sitesini 30 saniye boyunca ziyaret edin.', 'Homiylar saytida 30 soniya bo''ling va energiya oling.', 'Посетите сайт спонсора на 30 секунд.', 'Visit sponsor website for 30 seconds to earn energy.', 1, 'https://telegram.org', NULL, 30, 'link', 2),
(3, 'telegram', 'Resmi Duyuru Kanalına Abone Ol', 'Rasmiy Yangiliklar Kanaliga A''zo Bo''ling', 'Подписка на Официальный Канал', 'Subscribe to Official TG Channel', 'Kanalımıza katılın, güncellemeler ve ödül havuzunu kaçırmayın.', 'Kanalimizga qo''shiling va yangiliklardan xabardor bo''ling.', 'Подпишитесь на наш канал новостей.', 'Join our official Telegram announcements channel.', 2, 'https://t.me/durov', '@durov', 0, 'tg_bot', 3),
(4, 'telegram', 'Topluluk Grubuna Katıl', 'Jamiyat Guruhiga Qo''shiling', 'Вступить в Группу Чата', 'Join Discussion Community Group', 'Diğer oyuncularla strateji paylaşmak için gruba katılın.', 'Boshqa o''yinchilar bilan muloqot qilish uchun guruhga kiring.', 'Вступите в чат игроков.', 'Join community chat group to chat with other players.', 2, 'https://t.me/telegram', '@telegram', 0, 'tg_bot', 4),
(5, 'instagram', 'Instagram Sayfamızı Takip Edin', 'Instagram Sahifamizga Obuna Bo''ling', 'Подписаться на Instagram', 'Follow Our Instagram Page', 'Instagram hesabımızı takip ederek enerjinizi artırın.', 'Instagram sahifamizni kuzatib boring va energiya oling.', 'Подпишитесь на наш Instagram аккаунт.', 'Follow our official Instagram profile for energy.', 2, 'https://instagram.com/telegram', NULL, 10, 'manual', 5),
(6, 'youtube', 'Tanıtım Videosunu İzle (Shorts)', 'Taqdimot Shorts Videosini Ko''rish', 'Просмотр YouTube Shorts', 'Watch YouTube Showcase Shorts', 'YouTube Shorts videomuzu sonuna kadar izleyin (+1 Enerji).', 'YouTube Shorts videoni oxirigacha ko''ring (+1 Energiya).', 'Посмотрите короткое видео на YouTube до конца.', 'Watch this short YouTube video until the end.', 1, 'https://www.youtube.com/embed/dQw4w9WgXcQ', NULL, 20, 'youtube_iframe', 6),
(7, 'referral', 'Arkadaşını Davet Et (+3 Enerji)', 'Do''stingizni Taklif Qiling (+3 Energiya)', 'Пригласить Друга (+3 Энергии)', 'Invite a Friend (+3 Energy)', 'Her davet ettiğin aktif arkadaşın için 3 Enerji kazan.', 'Har bir taklif qilingan faol do''stingiz uchun 3 Energiya oling.', 'Получайте по 3 энергии за каждого приглашенного друга.', 'Invite a friend to the game and get +3 Energy when they join.', 3, 'ref_link', NULL, 0, 'manual', 7);

-- Bugün için örnek başlangıç havuzu (Bugünün tarihiyle)
INSERT INTO `daily_revenue_pools` (`pool_date`, `total_ads_revenue`, `total_sponsor_revenue`, `is_distributed`) 
VALUES (CURRENT_DATE(), 125.5000, 74.5000, 0)
ON DUPLICATE KEY UPDATE `total_ads_revenue` = VALUES(`total_ads_revenue`);

-- Örnek Liderlik Tablosu Verileri (Top-10 Örnek Bot/Oyuncular)
INSERT INTO `users` (`id`, `telegram_id`, `username`, `first_name`, `language_code`, `energy`, `coin_balance`, `selected_skin_id`) VALUES
(1, 11111111, 'sardor_pro', 'Sardor', 'uz', 8, 14.5000, 4),
(2, 22222222, 'alex_gamer', 'Alexey', 'ru', 6, 9.2000, 2),
(3, 33333333, 'burak_tr', 'Burak', 'tr', 7, 7.8000, 3),
(4, 44444444, 'john_io', 'John Doe', 'en', 5, 4.3000, 1),
(5, 55555555, 'dilnoza_uz', 'Dilnoza', 'uz', 4, 3.1000, 1)
ON DUPLICATE KEY UPDATE `id` = VALUES(`id`);

INSERT INTO `daily_game_scores` (`score_date`, `user_id`, `daily_high_score`, `matches_played`, `kills_count`) VALUES
(CURRENT_DATE(), 1, 8450, 14, 21),
(CURRENT_DATE(), 2, 7120, 11, 15),
(CURRENT_DATE(), 3, 6200, 9, 12),
(CURRENT_DATE(), 4, 4890, 8, 7),
(CURRENT_DATE(), 5, 3450, 6, 4)
ON DUPLICATE KEY UPDATE `daily_high_score` = VALUES(`daily_high_score`);