-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `snaplimit_quiz_app`
--

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

--
-- Table structure for table `app_settings`
--

CREATE TABLE `app_settings` (
  `id` int(11) NOT NULL DEFAULT 1,
  `claim_timer` int(11) DEFAULT 30,
  `website_task_enabled` tinyint(1) DEFAULT 0,
  `referrer_coins` int(11) DEFAULT 50,
  `referred_coins` int(11) DEFAULT 50,
  `referral_coin_threshold` int(11) DEFAULT 500,
  `app_link` varchar(255) DEFAULT '',
  `version` varchar(10) DEFAULT '1.0.0',
  `version_code` int(11) DEFAULT 1,
  `changelogs` text DEFAULT '',
  `maintenance_mode` tinyint(1) DEFAULT 0,
  `vpn_allowed` tinyint(1) DEFAULT 1,
  `how_to_link` varchar(255) DEFAULT '',
  `telegram_link` varchar(255) DEFAULT '',
  `support_link` varchar(255) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `app_settings`
--

INSERT INTO `app_settings` (`id`, `claim_timer`, `website_task_enabled`, `referrer_coins`, `referred_coins`, `referral_coin_threshold`, `app_link`, `version`, `version_code`, `changelogs`, `maintenance_mode`, `vpn_allowed`, `how_to_link`, `telegram_link`, `support_link`) VALUES
(1, 30, 0, 50, 50, 500, '', '1.0.0', 1, '', 0, 1, '', '', '');

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

--
-- Table structure for table `promo_codes`
--

CREATE TABLE `promo_codes` (
  `id` int(11) NOT NULL,
  `code` varchar(20) NOT NULL,
  `coins` int(11) NOT NULL,
  `usage_limit` int(11) NOT NULL,
  `expiry_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `promo_codes`
--

INSERT INTO `promo_codes` (`id`, `code`, `coins`, `usage_limit`, `expiry_date`, `created_at`) VALUES
(1, 'WELCOME2025', 100, 500, '2025-12-31 18:29:59', '2025-06-01 04:30:00'),
(2, 'SUMMER25', 50, 1000, '2025-08-31 18:29:59', '2025-06-02 06:30:00'),
(3, 'QUIZWIN', 200, 200, '2025-07-31 18:29:59', '2025-06-03 08:30:00'),
(4, 'REFERBONUS', 75, 300, '2025-09-30 18:29:59', '2025-06-04 10:30:00'),
(5, 'FESTIVE25', 150, 400, '2025-11-30 18:29:59', '2025-06-05 12:30:00');

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

--
-- Table structure for table `promo_code_usage`
--

CREATE TABLE `promo_code_usage` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `promo_code_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `rate_limit`
--

CREATE TABLE `rate_limit` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `timestamp` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `rate_limit`
--

INSERT INTO `rate_limit` (`id`, `user_id`, `timestamp`) VALUES
(34, 0, 1749493527),
(31, 1, 1749490312),
(32, 1, 1749490337),
(33, 1, 1749490524),
(35, 1, 1749493527),
(36, 1, 1749493541);

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

--
-- Table structure for table `redeem_history`
--

CREATE TABLE `redeem_history` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `subcategory_id` int(11) NOT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `gift_code` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `rejection_reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `redeem_options_categories`
--

CREATE TABLE `redeem_options_categories` (
  `id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL,
  `category_image_url` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `redeem_options_categories`
--

INSERT INTO `redeem_options_categories` (`id`, `category_name`, `category_image_url`, `created_at`) VALUES
(1, 'Gift Card & Payments', 'https://example.com/images/giftcard_payments.jpg', '2025-06-01 04:30:00'),
(2, 'Voucher', 'https://example.com/images/voucher.jpg', '2025-06-01 04:30:00'),
(3, 'Gaming', 'https://example.com/images/gaming.jpg', '2025-06-01 04:30:00');

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

--
-- Table structure for table `redeem_options_subcategories`
--

CREATE TABLE `redeem_options_subcategories` (
  `id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `sub_category_name` varchar(100) NOT NULL,
  `sub_category_image_url` varchar(255) NOT NULL,
  `coins` int(11) NOT NULL,
  `cash` decimal(10,2) NOT NULL,
  `is_enabled` tinyint(4) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `redeem_options_subcategories`
--

INSERT INTO `redeem_options_subcategories` (`id`, `category_id`, `sub_category_name`, `sub_category_image_url`, `coins`, `cash`, `is_enabled`, `created_at`) VALUES
(1, 1, 'Amazon', 'https://example.com/images/amazon.jpg', 1000, 10.00, 1, '2025-06-01 04:30:00'),
(2, 1, 'Flipkart', 'https://example.com/images/flipkart.jpg', 900, 9.00, 1, '2025-06-01 04:30:00'),
(3, 1, 'Google Play 10', 'https://example.com/images/googleplay.jpg', 800, 8.00, 1, '2025-06-01 04:30:00'),
(4, 1, 'Google Play 20', 'https://example.com/images/googleplay.jpg', 1500, 15.00, 1, '2025-06-01 04:30:00'),
(5, 1, 'Google Play 50', 'https://example.com/images/googleplay.jpg', 3000, 30.00, 1, '2025-06-01 04:30:00'),
(6, 1, 'UPI', 'https://example.com/images/upi.jpg', 1200, 12.00, 1, '2025-06-01 04:30:00'),
(7, 2, 'Starbucks', 'https://example.com/images/starbucks.jpg', 500, 5.00, 1, '2025-06-01 04:30:00'),
(8, 2, 'Walmart', 'https://example.com/images/walmart.jpg', 700, 7.00, 1, '2025-06-01 04:30:00'),
(9, 3, 'Steam', 'https://example.com/images/steam.jpg', 900, 9.00, 1, '2025-06-01 04:30:00'),
(10, 3, 'PlayStation', 'https://example.com/images/playstation.jpg', 1100, 11.00, 1, '2025-06-01 04:30:00');

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

--
-- Table structure for table `referral_rewards`
--

CREATE TABLE `referral_rewards` (
  `id` int(11) NOT NULL,
  `referrer_id` int(11) NOT NULL,
  `referred_id` int(11) NOT NULL,
  `referrer_coins` int(11) NOT NULL,
  `referred_coins` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

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

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `task_type` enum('quiz','correct_spelling','image_puzzle','find_faster','math_check') NOT NULL DEFAULT 'quiz',
  `reward_coins` int(11) NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `task_questions`
--

CREATE TABLE `task_questions` (
  `id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `question` varchar(255) DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `option_1` varchar(255) DEFAULT NULL,
  `option_2` varchar(255) DEFAULT NULL,
  `option_3` varchar(255) DEFAULT NULL,
  `option_4` varchar(255) DEFAULT NULL,
  `correct_option` varchar(255) DEFAULT NULL,
  `correctness_answer` enum('Yes','No') DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `device_id` varchar(255) NOT NULL,
  `coins` int(11) DEFAULT 0,
  `refercode` varchar(8) NOT NULL,
  `referred_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_blocked` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `email`, `device_id`, `coins`, `refercode`, `referred_by`, `created_at`, `is_blocked`) VALUES
(1, 'user@example.com', 'device123', 1000, 'userxIrP', NULL, '2025-06-09 16:29:31', 0),
(2, 'user1@example.com', 'device1123', 0, 'user11sP', 1, '2025-06-09 16:31:40', 0);

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

--
-- Table structure for table `website_visit_tasks`
--

CREATE TABLE `website_visit_tasks` (
  `id` int(11) NOT NULL,
  `web_url` varchar(255) NOT NULL,
  `timer` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `app_settings`
--
ALTER TABLE `app_settings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `promo_codes`
--
ALTER TABLE `promo_codes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`),
  ADD KEY `idx_promo_code` (`code`);

--
-- Indexes for table `promo_code_usage`
--
ALTER TABLE `promo_code_usage`
  ADD PRIMARY KEY (`id`),
  ADD KEY `promo_code_id` (`promo_code_id`),
  ADD KEY `idx_promo_usage` (`user_id`,`promo_code_id`);

--
-- Indexes for table `rate_limit`
--
ALTER TABLE `rate_limit`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_rate_limit_user` (`user_id`,`timestamp`);

--
-- Indexes for table `redeem_history`
--
ALTER TABLE `redeem_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `subcategory_id` (`subcategory_id`);

--
-- Indexes for table `redeem_options_categories`
--
ALTER TABLE `redeem_options_categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `redeem_options_subcategories`
--
ALTER TABLE `redeem_options_subcategories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `referral_rewards`
--
ALTER TABLE `referral_rewards`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `referrer_id` (`referrer_id`,`referred_id`),
  ADD KEY `referred_id` (`referred_id`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `task_questions`
--
ALTER TABLE `task_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `task_id` (`task_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `device_id` (`device_id`),
  ADD UNIQUE KEY `refercode` (`refercode`),
  ADD KEY `referred_by` (`referred_by`),
  ADD KEY `idx_refercode` (`refercode`);

--
-- Indexes for table `website_visit_tasks`
--
ALTER TABLE `website_visit_tasks`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `promo_codes`
--
ALTER TABLE `promo_codes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `promo_code_usage`
--
ALTER TABLE `promo_code_usage`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rate_limit`
--
ALTER TABLE `rate_limit`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `redeem_history`
--
ALTER TABLE `redeem_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `redeem_options_categories`
--
ALTER TABLE `redeem_options_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `redeem_options_subcategories`
--
ALTER TABLE `redeem_options_subcategories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `referral_rewards`
--
ALTER TABLE `referral_rewards`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tasks`
--
ALTER TABLE `tasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `task_questions`
--
ALTER TABLE `task_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `website_visit_tasks`
--
ALTER TABLE `website_visit_tasks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `promo_code_usage`
--
ALTER TABLE `promo_code_usage`
  ADD CONSTRAINT `promo_code_usage_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `promo_code_usage_ibfk_2` FOREIGN KEY (`promo_code_id`) REFERENCES `promo_codes` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `redeem_history`
--
ALTER TABLE `redeem_history`
  ADD CONSTRAINT `redeem_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `redeem_history_ibfk_2` FOREIGN KEY (`subcategory_id`) REFERENCES `redeem_options_subcategories` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `redeem_options_subcategories`
--
ALTER TABLE `redeem_options_subcategories`
  ADD CONSTRAINT `redeem_options_subcategories_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `redeem_options_categories` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `referral_rewards`
--
ALTER TABLE `referral_rewards`
  ADD CONSTRAINT `referral_rewards_ibfk_1` FOREIGN KEY (`referrer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `referral_rewards_ibfk_2` FOREIGN KEY (`referred_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `task_questions`
--
ALTER TABLE `task_questions`
  ADD CONSTRAINT `task_questions_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`referred_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;
COMMIT;

