-- phpMyAdmin SQL Dump
-- Clean database structure for Task Zone / Earning247 Project
-- Generation Time: Jun 13, 2026

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

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chat_id` bigint(20) DEFAULT NULL,
  `telegram_id` bigint(20) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `balance` decimal(10,2) DEFAULT 0.00,
  `profile_pic` varchar(255) DEFAULT NULL,
  `referral_balance` decimal(10,2) NOT NULL DEFAULT 0.00,
  `referred_by` varchar(50) DEFAULT NULL,
  `is_admin` tinyint(1) NOT NULL DEFAULT 0,
  `is_premium` tinyint(1) DEFAULT 0,
  `premium_expiry` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `telegram_id` (`telegram_id`),
  UNIQUE KEY `chat_id` (`chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `tasks`
--

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `reward` decimal(10,2) NOT NULL,
  `task_link` varchar(500) NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `task_completions`
--

CREATE TABLE IF NOT EXISTS `task_completions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `completed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_task` (`user_id`, `task_id`),
  KEY `user_id` (`user_id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `fk_completions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_completions_task` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `deposits`
--

CREATE TABLE IF NOT EXISTS `deposits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `transaction_id` varchar(100) NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `withdrawals`
--

CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `account_number` varchar(100) NOT NULL,
  `pin` varchar(20) DEFAULT NULL,
  `screenshot` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `tutorials`
--

CREATE TABLE IF NOT EXISTS `tutorials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `video_link` varchar(500) NOT NULL,
  `thumbnail` varchar(500) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `system_settings`
--

CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `referral_withdrawals`
--

CREATE TABLE IF NOT EXISTS `referral_withdrawals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `method` varchar(50) NOT NULL,
  `account_number` varchar(100) NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_reviews`
--

CREATE TABLE IF NOT EXISTS `user_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `rating` tinyint(4) NOT NULL DEFAULT 5,
  `review_text` text NOT NULL,
  `avatar_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `bc_log`
--

CREATE TABLE IF NOT EXISTS `bc_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chat_id` bigint(20) DEFAULT NULL,
  `msg_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `blocked_users`
--

CREATE TABLE IF NOT EXISTS `blocked_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `reason` text DEFAULT NULL,
  `blocked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `broadcast_history`
--

CREATE TABLE IF NOT EXISTS `broadcast_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(20) NOT NULL,
  `message` text NOT NULL,
  `recipient_type` enum('all','specific') NOT NULL DEFAULT 'all',
  `recipient_ids` text DEFAULT NULL,
  `sent_count` int(11) NOT NULL DEFAULT 0,
  `status` enum('success','partial','failed') NOT NULL DEFAULT 'success',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `message_ids` text DEFAULT NULL COMMENT 'JSON array storing chat_id and message_id pairs for Telegram message deletion',
  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `broadcast_users`
--

CREATE TABLE IF NOT EXISTS `broadcast_users` (
  `chat_id` bigint(20) NOT NULL,
  UNIQUE KEY `chat_id` (`chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `premium_requests`
--

CREATE TABLE IF NOT EXISTS `premium_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 370.00,
  `payment_method` varchar(50) NOT NULL,
  `from_number` varchar(20) NOT NULL,
  `transaction_id` varchar(255) NOT NULL,
  `screenshot` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `payment_numbers`
--

CREATE TABLE IF NOT EXISTS `payment_numbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `method` varchar(50) NOT NULL,
  `number` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `method` (`method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Seeding default data for system configuration
--

-- Default settings
INSERT IGNORE INTO `system_settings` (`setting_key`, `setting_value`) VALUES
('premium_price', '370.00'),
('referral_bonus_lvl1', '2.00'),
('referral_bonus_lvl2', '1.00'),
('whatsapp_number', 'https://t.me/Rakib125421'),
('global_task_price', '370.00');

-- Default payment numbers
INSERT IGNORE INTO `payment_numbers` (`method`, `number`) VALUES
('bkash', '01810641475'),
('nagad', '01700000000'),
('rocket', '01900000000');

-- Default tutorials
INSERT IGNORE INTO `tutorials` (`id`, `title`, `description`, `video_link`, `thumbnail`, `status`) VALUES
(3, 'কিভাবে কাজ করতে হয় ভিডিওটা দেখুন 👇', 'ফুল ভিডিও দেখতে ডাইরেক্ট ইউটিউব লোগোতে ক্লিক করুন ইউটিউবে গিয়ে ভিডিওটা পুরোটা দেখে আসবেন এবং নিজে নিজে কাজ করুন, ✅', 'https://youtu.be/nTq8zHnvT_0?si=pfzCYx_gPCHNCWWz', NULL, 'active');

-- Default reviews with correct UTF-8 Bangla text
INSERT IGNORE INTO `user_reviews` (`username`, `rating`, `review_text`, `avatar_url`) VALUES
('Rakibul Islam', 5, 'খুব সুন্দর একটি আর্নিং অ্যাপস! আমি প্রিমিয়াম মেম্বারশিপ নেওয়ার পর ১ ঘণ্টার মধ্যে টাকা পেয়েছি। ধন্যবাদ এডমিন!', 'https://api.dicebear.com/7.x/adventurer/svg?seed=Rakib'),
('Tasnim Ahmed', 5, 'সবচেয়ে নির্ভরযোগ্য প্ল্যাটফর্ম। ২৪ ঘণ্টা উত্তোলন সুবিধা সত্যিই চমৎকার। প্রতিবারই পেমেন্ট সফলভাবে পেয়েছি।', 'https://api.dicebear.com/7.x/adventurer/svg?seed=Tasnim'),
('Sumaiya Akter', 5, 'কাজগুলো খুবই সহজ এবং ডেইলি পেমেন্ট করা হয়। প্রিমিয়াম মেম্বারশিপে মেম্বার হওয়া খুবই লাভজনক।', 'https://api.dicebear.com/7.x/adventurer/svg?seed=Sumaiya');

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

--
-- Triggers
--

DELIMITER $$

CREATE TRIGGER `deduct_balance_on_withdrawal` AFTER UPDATE ON `withdrawals` FOR EACH ROW 
BEGIN
    IF NEW.status = 'approved' AND OLD.status = 'pending' THEN
        UPDATE users 
        SET balance = balance - NEW.amount 
        WHERE telegram_id = NEW.user_id;
    END IF;
END$$

DELIMITER ;

COMMIT;