-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 30, 2026 at 11:59 AM
-- Server version: 10.6.25-MariaDB-cll-lve
-- PHP Version: 8.4.20

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `brightqa_bestmula`
--

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

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('super_admin','assistant_admin') DEFAULT 'assistant_admin',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `permissions` text DEFAULT NULL COMMENT 'JSON encoded permissions',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admins`
--

INSERT INTO `admins` (`id`, `username`, `email`, `password`, `role`, `status`, `permissions`, `created_at`) VALUES
(4, 'bestmula', 'bestmula@gmail.com', 'Y9euPWmHutOGM', 'super_admin', 'active', NULL, '2026-01-29 07:02:24');

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

--
-- Table structure for table `admin_logs`
--

CREATE TABLE `admin_logs` (
  `id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `action` varchar(100) NOT NULL,
  `details` text DEFAULT NULL,
  `target_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (unix_timestamp(`created_at`))
(
PARTITION p_2026_01 VALUES LESS THAN (1769904000) ENGINE=InnoDB,
PARTITION p_2026_02 VALUES LESS THAN (1772323200) ENGINE=InnoDB,
PARTITION p_2026_03 VALUES LESS THAN (1775001600) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

--
-- Dumping data for table `admin_logs`
--

INSERT INTO `admin_logs` (`id`, `admin_id`, `action`, `details`, `target_id`, `ip_address`, `user_agent`, `created_at`) VALUES
(1, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 17:27:27'),
(2, 4, 'login', 'Admin logged in', NULL, '196.98.180.211', NULL, '2026-04-25 17:30:00'),
(3, 4, 'login', 'Admin logged in', NULL, '196.98.180.211', NULL, '2026-04-25 17:42:36'),
(4, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 18:12:50'),
(5, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 19:05:08'),
(6, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 19:39:50'),
(7, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 21:05:09'),
(8, 4, 'grant_cf_access', 'Granted CF access to Mkuu #2', 2, '105.161.208.24', NULL, '2026-04-25 21:06:00'),
(9, 4, 'revoke_cf_access', 'Revoked CF access user #2', 2, '105.161.208.24', NULL, '2026-04-25 21:08:00'),
(10, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 21:09:44'),
(11, 4, 'grant_cf_access', 'Granted CF access to Mkuu #2', 2, '105.161.208.24', NULL, '2026-04-25 21:11:49'),
(12, 4, 'revoke_cf_access', 'Revoked CF access user #2', 2, '105.161.208.24', NULL, '2026-04-25 21:11:58'),
(13, 4, 'login', 'Admin logged in', NULL, '105.161.208.24', NULL, '2026-04-25 21:15:57'),
(14, 4, 'login', 'Admin logged in', NULL, '196.98.180.211', NULL, '2026-04-25 22:50:01'),
(15, 4, 'login', 'Admin logged in', NULL, '196.98.180.211', NULL, '2026-04-25 23:29:52'),
(16, 4, 'edit_user', 'Edited user: Saaa (ID: 3)', 3, '196.98.180.211', NULL, '2026-04-25 23:48:59'),
(17, 4, 'login', 'Admin logged in', NULL, '196.98.180.211', NULL, '2026-04-26 00:48:42'),
(18, 4, 'login', 'Admin logged in', NULL, '196.98.180.211', NULL, '2026-04-26 04:11:43'),
(19, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-27 00:48:21'),
(20, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-27 02:53:38'),
(21, 4, 'login_as_user', 'Admin logged in as user: Saaa (ID: 3)', 3, '196.98.176.247', NULL, '2026-04-27 03:03:26'),
(22, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-27 09:32:43'),
(23, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-27 14:51:20'),
(24, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-27 17:47:43'),
(25, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-28 01:00:07'),
(26, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-28 01:15:00'),
(27, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-28 02:34:41'),
(28, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-28 19:19:26'),
(29, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-28 19:20:02'),
(30, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-28 19:53:19'),
(31, 4, 'award_bonus', 'Awarded 100 to Mkuu', 2, '105.161.221.145', NULL, '2026-04-28 19:55:43'),
(32, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-28 21:04:07'),
(33, 4, 'login', 'Admin logged in', NULL, '196.98.176.247', NULL, '2026-04-28 22:18:25'),
(34, 4, 'login', 'Admin logged in', NULL, '105.164.4.56', NULL, '2026-04-28 23:45:46'),
(35, 4, 'login', 'Admin logged in', NULL, '105.164.86.135', NULL, '2026-04-29 00:39:15'),
(36, 4, 'login', 'Admin logged in', NULL, '105.164.4.56', NULL, '2026-04-29 00:44:10'),
(37, 4, 'edit_user', 'Edited user: TEST (ID: 5)', 5, '105.164.4.56', NULL, '2026-04-29 01:20:08'),
(38, 4, 'activate_user', 'Activated user: Cameroon (ID: 6)', 6, '105.164.4.56', NULL, '2026-04-29 01:20:26'),
(39, 4, 'deactivate_user', 'Deactivated user ID: 6', 6, '105.164.4.56', NULL, '2026-04-29 01:21:14'),
(40, 4, 'edit_user', 'Edited user: Cameroon (ID: 6)', 6, '105.164.4.56', NULL, '2026-04-29 01:21:35'),
(41, 4, 'activate_user', 'Activated user: Cameroon (ID: 6)', 6, '105.164.4.56', NULL, '2026-04-29 01:21:47'),
(42, 4, 'deactivate_user', 'Deactivated user ID: 6', 6, '105.164.4.56', NULL, '2026-04-29 01:25:45'),
(43, 4, 'activate_user', 'Activated user: Cameroon (ID: 6)', 6, '105.164.4.56', NULL, '2026-04-29 01:25:59'),
(44, 4, 'login', 'Admin logged in', NULL, '105.164.4.56', NULL, '2026-04-29 02:04:55'),
(45, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-29 06:36:19'),
(46, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-29 06:38:57'),
(47, 4, 'edit_user', 'Edited user: test223 (ID: 7)', 7, '105.161.221.145', NULL, '2026-04-29 06:40:30'),
(48, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-29 06:56:57'),
(49, 4, 'delete_user', 'Deleted user: Cameroon (ID: 6)', 6, '105.161.221.145', NULL, '2026-04-29 07:01:45'),
(50, 4, 'edit_user', 'Edited user: Test22 (ID: 8)', 8, '105.161.221.145', NULL, '2026-04-29 07:14:40'),
(51, 4, 'edit_user', 'Edited user: Test22 (ID: 8)', 8, '105.161.221.145', NULL, '2026-04-29 07:23:34'),
(52, 4, 'edit_user', 'Edited user: Test22 (ID: 8)', 8, '105.161.221.145', NULL, '2026-04-29 07:24:19'),
(53, 4, 'reset_password', 'Reset password for user: Test22 (ID: 8)', 8, '105.161.221.145', NULL, '2026-04-29 07:24:31'),
(54, 4, 'login', 'Admin logged in', NULL, '196.98.178.102', NULL, '2026-04-29 15:48:56'),
(55, 4, 'login', 'Admin logged in', NULL, '196.98.178.102', NULL, '2026-04-30 00:24:33'),
(56, 4, 'login', 'Admin logged in', NULL, '196.98.178.102', NULL, '2026-04-30 01:50:53'),
(57, 4, 'activate_user', 'Activated user: Ghana (ID: 10)', 10, '196.98.178.102', NULL, '2026-04-30 01:55:26'),
(58, 4, 'activate_user', 'Activated user: Malawi (ID: 14)', 14, '196.98.178.102', NULL, '2026-04-30 02:00:34'),
(59, 4, 'login_as_user', 'Admin logged in as user: bestmula (ID: 1)', 1, '196.98.178.102', NULL, '2026-04-30 02:04:02'),
(60, 4, 'activate_user', 'Activated user: Zambia (ID: 11)', 11, '196.98.178.102', NULL, '2026-04-30 02:11:25'),
(61, 4, 'reset_password', 'Reset password for user: Tanzania (ID: 15)', 15, '196.98.178.102', NULL, '2026-04-30 02:16:53'),
(62, 4, 'activate_user', 'Activated user: Tanzania (ID: 15)', 15, '196.98.178.102', NULL, '2026-04-30 02:18:50'),
(63, 4, 'activate_user', 'Activated user: Cameroon (ID: 13)', 13, '196.98.178.102', NULL, '2026-04-30 02:20:08'),
(64, 4, 'activate_user', 'Activated user: Uganda (ID: 12)', 12, '196.98.178.102', NULL, '2026-04-30 02:24:31'),
(65, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-30 07:58:35'),
(66, 4, 'edit_user', 'Edited user: Cameroon (ID: 13)', 13, '105.161.221.145', NULL, '2026-04-30 08:06:17'),
(67, 4, 'login_as_user', 'Admin logged in as user: Cameroon (ID: 13)', 13, '105.161.221.145', NULL, '2026-04-30 08:06:22'),
(68, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-30 08:21:54'),
(69, 4, 'activate_user', 'Activated: Mkuu (ID: 16, Country: Cameroon)', 16, '105.161.221.145', NULL, '2026-04-30 08:24:02'),
(70, 4, 'edit_user', 'Edited: Cameroon (ID: 13)', 13, '105.161.221.145', NULL, '2026-04-30 08:26:51'),
(71, 4, 'login', 'Admin logged in', NULL, '105.161.221.145', NULL, '2026-04-30 08:46:43');

--
-- Triggers `admin_logs`
--
DELIMITER $$
CREATE TRIGGER `trg_admin_logs_validate_admin_id` BEFORE INSERT ON `admin_logs` FOR EACH ROW BEGIN
  IF NOT EXISTS (SELECT 1 FROM admins WHERE id = NEW.admin_id) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid admin_id: Admin does not exist';
  END IF;
END
$$
DELIMITER ;

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

--
-- Table structure for table `admin_permissions`
--

CREATE TABLE `admin_permissions` (
  `id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `permission` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `bonus_awards`
--

CREATE TABLE `bonus_awards` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `bonus_type` enum('referral_milestone','manual','promotion','welcome','achievement','other') NOT NULL,
  `bonus_tier_id` int(11) DEFAULT NULL COMMENT 'FK to referral_bonus_tiers if applicable',
  `amount` decimal(10,2) NOT NULL,
  `reason` varchar(500) NOT NULL,
  `notes` text DEFAULT NULL COMMENT 'Admin notes',
  `awarded_by` int(11) DEFAULT NULL COMMENT 'Admin ID who awarded (NULL for auto)',
  `status` enum('pending','credited','cancelled') DEFAULT 'credited',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `week_key` varchar(10) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bonus_awards`
--

INSERT INTO `bonus_awards` (`id`, `user_id`, `bonus_type`, `bonus_tier_id`, `amount`, `reason`, `notes`, `awarded_by`, `status`, `created_at`, `week_key`) VALUES
(1, 2, 'promotion', NULL, 100.00, 'Vsb', '0', 4, 'credited', '2026-04-28 19:55:43', '2026-W18');

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

--
-- Table structure for table `chat_foreigners_access`
--

CREATE TABLE `chat_foreigners_access` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('active','revoked') DEFAULT 'active',
  `granted_by` int(11) DEFAULT NULL,
  `granted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `revoked_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `chat_foreigners_access`
--

INSERT INTO `chat_foreigners_access` (`id`, `user_id`, `status`, `granted_by`, `granted_at`, `revoked_at`) VALUES
(1, 2, 'revoked', 4, '2026-04-25 21:11:49', '2026-04-26 00:11:58');

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

--
-- Table structure for table `chat_foreigners_payments`
--

CREATE TABLE `chat_foreigners_payments` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `checkout_request_id` varchar(200) DEFAULT NULL,
  `mpesa_receipt` varchar(50) DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `chat_foreigners_payments`
--

INSERT INTO `chat_foreigners_payments` (`id`, `user_id`, `phone`, `amount`, `reference`, `checkout_request_id`, `mpesa_receipt`, `status`, `created_at`, `completed_at`) VALUES
(1, 2, '254718308478', 100.00, 'CF_UNLOCK_2_1777147178', 'PFX202604252259380481DB', NULL, 'completed', '2026-04-25 19:59:40', '2026-04-25 23:00:04'),
(2, 2, '254718308478', 100.00, 'CF_UNLOCK_2_1777151525', 'PFX20260426001205D1D2D3', NULL, 'failed', '2026-04-25 21:12:07', NULL),
(3, 5, '254718308478', 100.00, 'CF_UNLOCK_5_1777410551', 'PFX20260429000911107580', NULL, 'completed', '2026-04-28 21:09:13', '2026-04-29 00:09:34'),
(4, 6, '254790612444', 100.00, 'CF_UNLOCK_6_1777426789', 'PFX20260429043949C1EC87', NULL, 'completed', '2026-04-29 01:39:58', '2026-04-29 04:40:14'),
(5, 6, '254790612444', 100.00, 'CF_UNLOCK_6_1777426798', 'CF_UNLOCK_6_1777426798', NULL, 'pending', '2026-04-29 01:40:00', NULL);

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

--
-- Table structure for table `commission_levels`
--

CREATE TABLE `commission_levels` (
  `id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL COMMENT 'FK to countries table',
  `level` int(11) NOT NULL COMMENT 'Level number: 1, 2, 3, 4, 5',
  `commission` decimal(10,2) NOT NULL COMMENT 'Fixed commission amount in local currency',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `commission_levels`
--

INSERT INTO `commission_levels` (`id`, `country_id`, `level`, `commission`, `created_at`, `updated_at`) VALUES
(229, 1, 1, 80.00, '2026-04-29 06:37:08', '2026-04-29 06:37:08'),
(230, 1, 2, 5.00, '2026-04-29 06:37:08', '2026-04-29 06:37:08'),
(240, 6, 1, 2175.00, '2026-04-30 00:32:10', '2026-04-30 00:32:10'),
(241, 6, 2, 436.00, '2026-04-30 00:32:10', '2026-04-30 00:32:10'),
(242, 6, 3, 219.00, '2026-04-30 00:32:10', '2026-04-30 00:32:10'),
(249, 3, 1, 10000.00, '2026-04-30 00:51:18', '2026-04-30 00:51:18'),
(250, 3, 2, 2850.00, '2026-04-30 00:51:18', '2026-04-30 00:51:18'),
(251, 3, 3, 1440.00, '2026-04-30 00:51:18', '2026-04-30 00:51:18'),
(252, 2, 1, 6000.00, '2026-04-30 00:54:46', '2026-04-30 00:54:46'),
(253, 2, 2, 2000.00, '2026-04-30 00:54:46', '2026-04-30 00:54:46'),
(254, 2, 3, 1000.00, '2026-04-30 00:54:46', '2026-04-30 00:54:46'),
(255, 4, 1, 45.80, '2026-04-30 01:05:56', '2026-04-30 01:05:56'),
(256, 4, 2, 8.30, '2026-04-30 01:05:56', '2026-04-30 01:05:56'),
(257, 4, 3, 4.20, '2026-04-30 01:05:56', '2026-04-30 01:05:56'),
(261, 5, 1, 70.00, '2026-04-30 01:14:28', '2026-04-30 01:14:28'),
(262, 5, 2, 20.00, '2026-04-30 01:14:28', '2026-04-30 01:14:28'),
(263, 5, 3, 10.00, '2026-04-30 01:14:28', '2026-04-30 01:14:28'),
(264, 7, 1, 4735.00, '2026-04-30 01:37:18', '2026-04-30 01:37:18'),
(265, 7, 2, 1350.00, '2026-04-30 01:37:18', '2026-04-30 01:37:18'),
(266, 7, 3, 680.00, '2026-04-30 01:37:18', '2026-04-30 01:37:18');

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

--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `currency_code` varchar(10) NOT NULL,
  `currency_symbol` varchar(10) NOT NULL,
  `exchange_rate` decimal(10,4) DEFAULT 1.0000 COMMENT 'Rate to KES',
  `activation_fee` decimal(10,2) NOT NULL,
  `min_withdrawal` decimal(10,2) NOT NULL,
  `withdrawal_fee` decimal(10,2) DEFAULT 0.00,
  `activation_instructions` text DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`id`, `name`, `currency_code`, `currency_symbol`, `exchange_rate`, `activation_fee`, `min_withdrawal`, `withdrawal_fee`, `activation_instructions`, `contact_email`, `contact_phone`, `status`, `created_at`) VALUES
(1, 'Kenya', 'KES', 'Ksh', 1.0000, 100.00, 130.00, 0.00, '', '', '', 'active', '2026-01-20 19:12:12'),
(2, 'Tanzania', 'TZS', 'TSh', 0.0500, 12000.00, 8000.00, 1000.00, '🇹🇿 TANZANIA PAYMENT\r\n\r\nDial\r\n🔰 *150*00# \r\n🔰 Send Money\r\n🔰 International Transfer\r\n🔰 Kenya (Safaricom)\r\n🔰 Enter recipient number (254790612444) BRIAN KIHONGE MWAGANU \r\n🔰 Amount: Tsh 12,000\r\n🔰 Reason: business/investment\r\n🔰 Send', 'mwaganubrian@gmail.com', '+254188102045', 'active', '2026-01-20 19:12:12'),
(3, 'Uganda', 'UGX', 'UGX', 0.0350, 19000.00, 10000.00, 300.00, 'MTN users 👇 \r\n1 Dial *165#\r\n2 Select 2 (International Money Transfer)\r\n3 Select 5 Kenya (Safaricom)\r\n4 Enter number 254790612444\r\n5 Recipient names Brian Kihonge mwaganu \r\n6 Amount 19000 ugx \r\n7 Enter Pin to complete payment \r\n\r\nAirtel Money users👇\r\n1 Dial *185#\r\n2 Select 2 (International Money Transfer)\r\n3 Select 5 Kenya (Safaricom)\r\n4 Enter number 254790612444\r\n5 Recipient names Brian Kihonge mwaganu \r\n6 Amount 19000 ugx \r\n7 Enter Pin to complete payment', 'mwaganubrian@gmail.com', '+254188102045', 'active', '2026-01-20 19:12:12'),
(4, 'Ghana', 'GHS', 'GHS', 12.0000, 90.00, 70.00, 10.00, 'Pay 90 GHC to the MTN number below 👇 \r\n0245000517\r\nLeticia selorm Dzakpasu\r\nSend a screenshot of payment, username and momo names when done to the person who invited you\r\nFor activation and guide on how to start making 100-400 cedis Daily', 'mwaganubrian@gmail.com', '+254188102045', 'active', '2026-01-20 19:12:12'),
(5, 'Zambia', 'ZMW', 'ZMW', 5.0000, 130.00, 60.00, 10.00, 'PAY WITH AIRTEL\r\n▪️Send K130 to 0776714345 (Ngosa Petrina)\r\n▪️Once done send:\r\n📌Screenshot of payment\r\n📌Username &amp;amp;amp; number registered\r\nTo the person who invited you\r\nFor guide on how to make 100-400 kwachas Daily\r\n\r\nPAY WITH MTN\r\nMTN Zambia → Airtel Money Zambia \r\n1. Dial: *115#\r\n2. Select Send Money\r\n3. Choose To Other Networks\r\n4. Select Airtel Money\r\n5. Enter number 0776714345 (Ngosa Petrina) \r\n6. Enter amount K130 \r\n7. Enter PIN\r\nSend details to the person who invited you for activation and guide on how to start earning', 'mwaganubrian@gmail.com', '+254188102045', 'active', '2026-01-29 10:51:33'),
(6, 'Cameroon', 'XAF', 'XAF', 0.2300, 4100.00, 4000.00, 300.00, 'https://bluepay.sbs/prd_vwp0018/checkout\r\nClick the link above and follow instructions given to make payments and submit to the person who has invited you for activation and guide on how to start earning', 'mwaganubrian@gmail.com', '+254188102045', 'active', '2026-01-29 10:52:32'),
(7, 'Malawi', 'MWK', 'MWK', 0.0740, 12000.00, 5000.00, 1000.00, 'MALAWI PAYMENT METHOD (Tnm Users) \r\nWithdrawal Through Agent (Tnm Mpamba)\r\nDial *444#\r\nSelect 3 (Cash out)\r\nAmount 12000MWK\r\nSelect 1 (Agent code)\r\nEnter Agent Code (3204272) Leonard Banda \r\nEnter Amount...\r\nEnter Pin...\r\n\r\nMALAWI PAYMENT METHOD(Airtel Users) \r\nWithdrawal Through Agent  (Airtel Money)\r\nDial *211#\r\nSelect 4 (withdrawal)\r\nAmount 12000MWK\r\nEnter Agent code (10158417) Leonard Banda \r\nEnter Amount \r\nEnter Pin', 'mwaganubrian@gmail.com', '+254188102045', 'active', '2026-04-30 01:37:18');

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

--
-- Table structure for table `digital_products`
--

CREATE TABLE `digital_products` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `category` enum('ebook','source_code','template','software','course','other') DEFAULT 'other',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `original_price` decimal(10,2) DEFAULT NULL,
  `commission_percent` decimal(5,2) NOT NULL DEFAULT 10.00,
  `file_path` varchar(500) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` bigint(20) DEFAULT 0,
  `file_type` varchar(100) DEFAULT NULL,
  `delivery_type` enum('file','link') NOT NULL DEFAULT 'file',
  `delivery_link` varchar(2048) DEFAULT NULL,
  `cover_image` varchar(500) DEFAULT NULL,
  `preview_text` text DEFAULT NULL,
  `max_downloads` int(11) NOT NULL DEFAULT 10,
  `sales_count` int(11) DEFAULT 0,
  `downloads_count` int(11) DEFAULT 0,
  `status` enum('active','inactive','draft') DEFAULT 'draft',
  `featured` tinyint(1) DEFAULT 0,
  `created_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `digital_products`
--

INSERT INTO `digital_products` (`id`, `title`, `description`, `category`, `price`, `original_price`, `commission_percent`, `file_path`, `file_name`, `file_size`, `file_type`, `delivery_type`, `delivery_link`, `cover_image`, `preview_text`, `max_downloads`, `sales_count`, `downloads_count`, `status`, `featured`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'Chat with Rina to Earn', '', 'other', 100.00, 180.00, 50.00, '', '', 0, '', 'link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'uploads/covers/cover_1777420450_276.jpeg', '', 10, 1, 0, 'active', 0, 4, '2026-04-25 17:35:00', '2026-04-29 02:10:41'),
(2, 'YouTube Earn(Ksh300)', 'Earn 300 Daily by answering questions', 'ebook', 80.00, 150.00, 50.00, '', '', 0, '', 'link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'uploads/covers/cover_1777423958_294.jpeg', '', 10, 1, 1, 'active', 0, 4, '2026-04-29 00:51:50', '2026-04-29 02:09:34'),
(3, 'LanaTech Trivia Earn(Ksh450)', 'You earn by answering simple questions to your MPESA', 'ebook', 99.00, 145.00, 50.00, '', '', 0, '', 'link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'uploads/covers/cover_1777424285_815.jpeg', '', 10, 0, 0, 'active', 0, 4, '2026-04-29 00:58:05', '2026-04-29 02:08:59'),
(4, 'YouTube Earn(Ksh600)', 'You earn by watching YouTube videos', 'ebook', 200.00, 310.00, 50.00, '', '', 0, '', 'link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'uploads/covers/cover_1777424751_722.jpeg', '', 10, 0, 0, 'active', 0, 4, '2026-04-29 01:05:51', '2026-04-29 02:08:35'),
(5, 'Survey questions earn', 'You earn by answering simple questions', 'ebook', 300.00, 500.00, 50.00, '', '', 0, '', 'link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'uploads/covers/cover_1777424945_295.jpeg', '', 10, 0, 0, 'active', 0, 4, '2026-04-29 01:09:05', '2026-04-29 02:07:43'),
(6, 'Chat with foreigners (Ksh3600)', 'Earn by chatting foreigners', 'ebook', 500.00, 1100.00, 50.00, '', '', 0, '', 'link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'uploads/covers/cover_1777425222_892.jpeg', '', 10, 0, 0, 'active', 0, 4, '2026-04-29 01:13:42', '2026-04-29 02:06:16');

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

--
-- Table structure for table `product_commissions`
--

CREATE TABLE `product_commissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `purchase_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `buyer_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `level_number` tinyint(3) UNSIGNED NOT NULL,
  `commission_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` varchar(20) NOT NULL DEFAULT 'credited',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `product_commissions`
--

INSERT INTO `product_commissions` (`id`, `purchase_id`, `product_id`, `buyer_id`, `recipient_id`, `level_number`, `commission_amount`, `status`, `created_at`) VALUES
(1, 1, 1, 2, 1, 1, 30.00, 'credited', '2026-04-25 20:34:18'),
(2, 2, 2, 6, 5, 1, 33.60, 'credited', '2026-04-29 01:38:19'),
(3, 2, 2, 6, 2, 2, 16.80, 'credited', '2026-04-29 01:38:19'),
(4, 2, 2, 6, 1, 3, 5.60, 'credited', '2026-04-29 01:38:19');

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

--
-- Table structure for table `product_commission_splits`
--

CREATE TABLE `product_commission_splits` (
  `id` int(10) UNSIGNED NOT NULL,
  `country_id` int(11) NOT NULL,
  `l1_pct` decimal(5,2) NOT NULL DEFAULT 60.00 COMMENT '% of pool to L1. 0 = disabled',
  `l2_pct` decimal(5,2) NOT NULL DEFAULT 30.00 COMMENT '% of pool to L2. 0 = disabled',
  `l3_pct` decimal(5,2) NOT NULL DEFAULT 10.00 COMMENT '% of pool to L3. 0 = disabled',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `product_commission_splits`
--

INSERT INTO `product_commission_splits` (`id`, `country_id`, `l1_pct`, `l2_pct`, `l3_pct`, `updated_at`) VALUES
(1, 6, 60.00, 30.00, 10.00, '2026-04-25 17:31:34'),
(2, 4, 60.00, 30.00, 10.00, '2026-04-25 17:31:34'),
(3, 1, 60.00, 30.00, 10.00, '2026-04-25 17:31:34'),
(4, 2, 60.00, 30.00, 10.00, '2026-04-25 17:31:34'),
(5, 3, 60.00, 30.00, 10.00, '2026-04-25 17:31:34'),
(6, 5, 60.00, 30.00, 10.00, '2026-04-25 17:31:34');

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

--
-- Table structure for table `product_purchases`
--

CREATE TABLE `product_purchases` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `mpesa_receipt` varchar(100) DEFAULT NULL,
  `checkout_request_id` varchar(200) DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending',
  `download_token` varchar(64) DEFAULT NULL,
  `delivery_link` varchar(2048) DEFAULT NULL,
  `download_count` int(11) DEFAULT 0,
  `max_downloads` int(11) DEFAULT 10,
  `commission_paid` tinyint(1) DEFAULT 0,
  `purchased_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_purchases`
--

INSERT INTO `product_purchases` (`id`, `user_id`, `product_id`, `amount_paid`, `created_at`, `mpesa_receipt`, `checkout_request_id`, `status`, `download_token`, `delivery_link`, `download_count`, `max_downloads`, `commission_paid`, `purchased_at`) VALUES
(1, 2, 1, 100.00, '2026-04-25 20:33:57', 'UDPCQ288VE', 'PFX202604252333575E330B', 'completed', '64636ca6838eb87bf8935c90ed447306fa8483b1313f27d407abdc8175971583', NULL, 0, 10, 1, '2026-04-25 20:34:18'),
(2, 6, 2, 80.00, '2026-04-29 01:38:08', 'UDTLN2CPME', 'PFX20260429043808BF89A4', 'completed', '737f77cd3b5dd57d3bd147aadbc645027f8a687703ef347217c917439b82b18e', NULL, 1, 10, 1, '2026-04-29 01:38:19'),
(3, 8, 6, 500.00, '2026-04-30 08:33:26', NULL, 'PFX202604301133268F8A9D', 'pending', NULL, NULL, 0, 10, 0, '2026-04-30 08:33:26');

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

--
-- Table structure for table `referral_bonus_tiers`
--

CREATE TABLE `referral_bonus_tiers` (
  `id` int(11) NOT NULL,
  `country_id` int(11) DEFAULT NULL COMMENT 'NULL = applies to all countries',
  `referral_count` int(11) NOT NULL COMMENT 'Number of active referrals needed',
  `bonus_amount` decimal(10,2) NOT NULL COMMENT 'Bonus amount in local currency',
  `bonus_name` varchar(100) NOT NULL COMMENT 'e.g., Bronze Referrer, Silver Referrer',
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `referral_bonus_tiers`
--

INSERT INTO `referral_bonus_tiers` (`id`, `country_id`, `referral_count`, `bonus_amount`, `bonus_name`, `description`, `status`, `created_at`, `updated_at`) VALUES
(1, NULL, 5, 50.00, 'Starter', '', 'active', '2026-04-28 19:57:13', '2026-04-28 19:57:13');

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

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES
(1, 'site_name', 'BESTMULA', 'Site name', '2026-04-24 20:19:12'),
(2, 'theme_color', '#f5ec00', 'Primary theme color', '2026-04-25 17:38:47'),
(3, 'mpesa_api_key', 'PSFXqjWEgZJb', 'SmartPanel MPESA API Key', '2026-03-23 19:02:08'),
(4, 'mpesa_email', 'briandomoo20@gmail.com', 'SmartPanel MPESA Email', '2026-03-23 18:37:40'),
(5, 'smtp_host', 'mail.royalduka.co.ke', 'SMTP Host', '2026-03-19 06:50:29'),
(6, 'smtp_port', '465', 'SMTP Port', '2026-03-19 06:50:29'),
(7, 'smtp_username', 'a3d55e001@smtp-brevo.com', 'SMTP Username', '2026-03-03 08:17:37'),
(8, 'admin_email', 'dakatjk@gmail.com', 'Admin notification email', '2026-03-02 07:45:57'),
(9, 'maintenance_mode', '0', 'Maintenance mode status', '2026-01-20 19:12:12'),
(12, 'mpesa_callback_url', 'https://earnovix.co.ke/mpesa/callback.php', NULL, '2026-03-21 19:22:34'),
(15, 'site_description', 'Turn Task into Cash', NULL, '2026-02-07 22:56:22'),
(39, 'smtp_password', 'j4qvbXMOaRsNZmGz', NULL, '2026-03-03 08:45:05'),
(41, 'email_notifications', '1', NULL, '2026-03-02 15:14:15'),
(75, 'site_logo', 'logo_1777157494.png', NULL, '2026-04-25 22:51:34'),
(358, 'sms_api_key', 'd46e8c61e98ab75484a9934ffabf6d77', NULL, '2026-03-03 09:47:12'),
(359, 'sms_partner_id', '14266', NULL, '2026-03-03 09:13:38'),
(360, 'sms_shortcode', 'SMARTPANEL', NULL, '2026-03-03 09:13:38'),
(361, 'admin_phone', '254718308478', NULL, '2026-03-03 09:13:38'),
(362, 'sms_notifications', '1', NULL, '2026-03-03 09:13:38'),
(418, 'smtp_pass', 'Dalmas2462@', NULL, '2026-03-19 06:50:29'),
(421, 'smtp_user', 'noreply@royalduka.co.ke', NULL, '2026-03-19 06:50:29'),
(422, 'smtp_from', 'noreply@royalduka.co.ke', NULL, '2026-03-19 06:50:29'),
(423, 'smtp_from_name', 'BESTMULA', NULL, '2026-04-24 20:18:40'),
(424, 'smtp_secure', 'ssl', NULL, '2026-03-19 06:50:29'),
(467, 'whatsapp_group_link', 'https://chat.whatsapp.com/KAUP4JANpi9BaBISXMVe8T?mode=gi_t', NULL, '2026-04-22 07:57:28'),
(539, 'pesaflux_api_key', 'pfx_573635fd984f363ece726b16fbbef8f240598ef0', NULL, '2026-04-28 01:04:12'),
(540, 'pesaflux_business_id', '54', NULL, '2026-04-28 01:04:12'),
(541, 'pesaflux_callback_url', 'https://bestmula.co.ke/mpesa/callback.php', NULL, '2026-04-25 17:45:58'),
(575, 'active_gateway', 'lipalink', NULL, '2026-04-30 08:52:57'),
(576, 'hashpay_api_key', 'h26524dWWzolI', NULL, '2026-04-24 05:52:56'),
(577, 'hashpay_account_id', 'HP779984', NULL, '2026-04-24 05:52:56'),
(615, 'chat_foreigners_unlock_fee', '100', 'Fee to unlock Chat Foreigners (local currency)', '2026-04-25 19:25:13'),
(616, 'chat_foreigners_redirect_link', 'https://chatmula.co.ke/register.php?ref=Bryangenuine', 'URL to redirect after successful Chat Foreigners payment', '2026-04-29 01:34:15'),
(617, 'chat_foreigners_api_key', 'pfx_573635fd984f363ece726b16fbbef8f240598ef0', 'LipaLink API key dedicated to Chat Foreigners payments', '2026-04-29 01:34:15'),
(618, 'chat_foreigners_business_id', '39', 'LipaLink Business ID dedicated to Chat Foreigners payments', '2026-04-29 01:34:15'),
(619, 'spin_rtp_red', '6', 'Red win probability % (RTP)', '2026-04-26 01:07:23'),
(620, 'spin_rtp_black', '7', 'Black win probability % (RTP)', '2026-04-26 01:07:23'),
(621, 'spin_rtp_even', '7', 'Even win probability % (RTP)', '2026-04-26 01:07:23'),
(622, 'spin_rtp_odd', '8', 'Odd win probability % (RTP)', '2026-04-26 01:07:23'),
(623, 'spin_min_bet', '49', 'Minimum spin bet amount', '2026-04-28 02:36:36'),
(624, 'spin_max_bet', '1000', 'Maximum spin bet amount', '2026-04-25 19:25:13'),
(625, 'spin_win_multiplier', '1.9', 'Payout multiplier on win (e.g. 1.9 = 90% profit)', '2026-04-25 19:25:13'),
(626, 'spin_api_key', 'pfx_573635fd984f363ece726b16fbbef8f240598ef0', 'LipaLink API key dedicated to Spin & Win payments', '2026-04-29 01:36:19'),
(627, 'spin_business_id', '39', 'LipaLink Business ID dedicated to Spin & Win payments', '2026-04-29 01:36:19'),
(689, 'cf_commission_l1', '50', NULL, '2026-04-29 02:05:48'),
(690, 'cf_commission_l2', '0', NULL, '2026-04-28 21:05:58'),
(691, 'cf_commission_l3', '0', NULL, '2026-04-28 21:05:58');

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

--
-- Table structure for table `site_settings`
--

CREATE TABLE `site_settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `site_settings`
--

INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `created_at`, `updated_at`) VALUES
(1, 'site_logo', '', '2026-02-05 17:14:50', '2026-02-05 17:14:50'),
(2, 'dashboard_notification', '🚀⚡🔥 LETS CHESS THE BAG🤑{SITE_NAME}! 🔥⚡🚀', '2026-02-05 18:40:16', '2026-03-01 18:13:22'),
(3, 'dashboard_notification_link_text', 'Click here', '2026-02-05 18:40:16', '2026-02-05 18:40:16'),
(4, 'dashboard_notification_link_url', 'tasks.php', '2026-02-05 18:40:16', '2026-02-05 18:40:16'),
(5, 'dashboard_notification_enabled', '1', '2026-02-05 18:40:16', '2026-02-05 18:40:16');

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

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

CREATE TABLE `tasks` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `task_type` varchar(50) NOT NULL DEFAULT 'trivia' COMMENT 'trivia, quiz, blog, survey, video, ads, chat, meme, transcription, spin',
  `content_type` varchar(20) NOT NULL DEFAULT 'internal' COMMENT 'internal or external',
  `task_url` varchar(500) DEFAULT NULL COMMENT 'Required for external tasks',
  `task_image` varchar(500) DEFAULT NULL COMMENT 'Path to task image',
  `reward` decimal(10,2) NOT NULL DEFAULT 0.00,
  `daily_limit_per_user` int(11) NOT NULL DEFAULT 1,
  `total_limit` int(11) DEFAULT NULL COMMENT 'Max total completions (NULL = unlimited)',
  `total_completions` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `min_duration_seconds` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `enable_timer` tinyint(1) NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'active' COMMENT 'active or inactive',
  `featured` tinyint(1) NOT NULL DEFAULT 0,
  `show_in_sidebar` tinyint(1) NOT NULL DEFAULT 0,
  `difficulty` varchar(20) NOT NULL DEFAULT 'easy' COMMENT 'easy, medium, hard',
  `category` varchar(100) NOT NULL DEFAULT 'general',
  `pass_percentage` int(11) NOT NULL DEFAULT 70 COMMENT 'Pass percentage for quizzes',
  `auto_approve` tinyint(1) NOT NULL DEFAULT 0,
  `requires_proof` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `last_modified_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tasks`
--

INSERT INTO `tasks` (`id`, `title`, `description`, `instructions`, `task_type`, `content_type`, `task_url`, `task_image`, `reward`, `daily_limit_per_user`, `total_limit`, `total_completions`, `min_duration_seconds`, `enable_timer`, `status`, `featured`, `show_in_sidebar`, `difficulty`, `category`, `pass_percentage`, `auto_approve`, `requires_proof`, `created_by`, `last_modified_by`, `created_at`, `updated_at`) VALUES
(1, 'Questions Earn', 'Let there be 5 simple questions where one chooses the correct answer among 4 choices then profits credited on total earnings not withdrawable profits ', '', 'trivia', 'internal', '', NULL, 25.00, 1, NULL, 0, 40, 0, 'active', 0, 1, 'easy', '', 70, 0, 0, NULL, NULL, '2026-04-25 23:35:53', '2026-04-25 23:35:53');

--
-- Triggers `tasks`
--
DELIMITER $$
CREATE TRIGGER `trg_tasks_after_delete` AFTER DELETE ON `tasks` FOR EACH ROW BEGIN
  INSERT INTO `task_audit_log` (`task_id`, `action`, `old_value`, `created_at`)
  VALUES (OLD.id, 'DELETE', CONCAT('Task deleted: ', OLD.title), NOW());
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `trg_tasks_after_insert` AFTER INSERT ON `tasks` FOR EACH ROW BEGIN
  INSERT INTO `task_audit_log` (`task_id`, `action`, `new_value`, `created_at`)
  VALUES (NEW.id, 'CREATE', CONCAT('Task created: ', NEW.title), NOW());
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `trg_tasks_after_update` AFTER UPDATE ON `tasks` FOR EACH ROW BEGIN
  -- Track status changes
  IF OLD.status != NEW.status THEN
    INSERT INTO `task_audit_log` (`task_id`, `action`, `field_name`, `old_value`, `new_value`, `created_at`)
    VALUES (NEW.id, 'UPDATE', 'status', OLD.status, NEW.status, NOW());
  END IF;
  
  -- Track title changes
  IF OLD.title != NEW.title THEN
    INSERT INTO `task_audit_log` (`task_id`, `action`, `field_name`, `old_value`, `new_value`, `created_at`)
    VALUES (NEW.id, 'UPDATE', 'title', OLD.title, NEW.title, NOW());
  END IF;
  
  -- Track reward changes
  IF OLD.reward != NEW.reward THEN
    INSERT INTO `task_audit_log` (`task_id`, `action`, `field_name`, `old_value`, `new_value`, `created_at`)
    VALUES (NEW.id, 'UPDATE', 'reward', CAST(OLD.reward AS CHAR), CAST(NEW.reward AS CHAR), NOW());
  END IF;
  
  -- Track sidebar visibility changes
  IF OLD.show_in_sidebar != NEW.show_in_sidebar THEN
    INSERT INTO `task_audit_log` (`task_id`, `action`, `field_name`, `old_value`, `new_value`, `created_at`)
    VALUES (NEW.id, 'UPDATE', 'show_in_sidebar', CAST(OLD.show_in_sidebar AS CHAR), CAST(NEW.show_in_sidebar AS CHAR), NOW());
  END IF;
END
$$
DELIMITER ;

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

--
-- Table structure for table `task_audit_log`
--

CREATE TABLE `task_audit_log` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `action` varchar(50) NOT NULL COMMENT 'CREATE, UPDATE, DELETE',
  `field_name` varchar(100) DEFAULT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `changed_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `task_audit_log`
--

INSERT INTO `task_audit_log` (`id`, `task_id`, `action`, `field_name`, `old_value`, `new_value`, `changed_by`, `created_at`) VALUES
(1, 1, 'CREATE', NULL, NULL, 'Task created: Questions Earn', NULL, '2026-04-25 23:35:53');

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

--
-- Table structure for table `task_blog_requirements`
--

CREATE TABLE `task_blog_requirements` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `topic` varchar(500) NOT NULL,
  `min_words` int(11) NOT NULL DEFAULT 300,
  `max_words` int(11) DEFAULT NULL,
  `keywords` text DEFAULT NULL COMMENT 'Comma-separated keywords',
  `guidelines` text DEFAULT NULL,
  `enable_word_count` tinyint(1) NOT NULL DEFAULT 1,
  `sample_outline` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `task_chat_requirements`
--

CREATE TABLE `task_chat_requirements` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `platform` varchar(100) NOT NULL COMMENT 'WhatsApp, Telegram, Discord, etc.',
  `min_messages` int(11) NOT NULL DEFAULT 10,
  `duration_minutes` int(11) NOT NULL DEFAULT 5,
  `topic` varchar(500) DEFAULT NULL,
  `guidelines` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `task_completions` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `task_type` varchar(50) NOT NULL,
  `submission_data` text DEFAULT NULL COMMENT 'JSON data for quiz answers, blog content, etc.',
  `screenshot` varchar(500) DEFAULT NULL COMMENT 'Path to screenshot/proof',
  `time_spent_seconds` int(11) DEFAULT NULL,
  `reward_earned` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending, completed, rejected',
  `admin_notes` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` timestamp NULL 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=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `task_completions`
--

INSERT INTO `task_completions` (`id`, `task_id`, `user_id`, `task_type`, `submission_data`, `screenshot`, `time_spent_seconds`, `reward_earned`, `status`, `admin_notes`, `reviewed_by`, `reviewed_at`, `created_at`, `updated_at`) VALUES
(1, 1, 2, '', '{\"answers\":[{\"question_id\":\"1\",\"user_answer\":\"B\",\"correct_answer\":\"B\",\"is_correct\":true}],\"score\":100,\"earned_points\":5,\"total_points\":5}', NULL, NULL, 25.00, 'completed', '', NULL, '2026-04-25 23:39:22', '2026-04-25 23:38:45', '2026-04-25 23:39:22'),
(2, 1, 2, '', '{\"answers\":[{\"question_id\":\"1\",\"user_answer\":\"A\",\"correct_answer\":\"B\",\"is_correct\":false}],\"score\":0,\"earned_points\":0,\"total_points\":5}', NULL, NULL, 0.00, 'pending', NULL, NULL, NULL, '2026-04-27 14:55:58', '2026-04-27 14:55:58'),
(3, 1, 2, '', '{\"answers\":[{\"question_id\":\"1\",\"user_answer\":\"B\",\"correct_answer\":\"B\",\"is_correct\":true}],\"score\":100,\"earned_points\":5,\"total_points\":5}', NULL, NULL, 25.00, 'pending', NULL, NULL, NULL, '2026-04-28 12:33:27', '2026-04-28 12:33:27');

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

--
-- Table structure for table `task_meme_requirements`
--

CREATE TABLE `task_meme_requirements` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `theme` varchar(500) NOT NULL,
  `allowed_formats` varchar(100) NOT NULL DEFAULT 'jpg,png,gif',
  `max_file_size_mb` int(11) NOT NULL DEFAULT 5,
  `min_dimensions` varchar(50) DEFAULT NULL COMMENT 'e.g., 800x600',
  `guidelines` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `task_quizzes`
--

CREATE TABLE `task_quizzes` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `question` text NOT NULL,
  `option_a` varchar(500) NOT NULL,
  `option_b` varchar(500) NOT NULL,
  `option_c` varchar(500) DEFAULT NULL,
  `option_d` varchar(500) DEFAULT NULL,
  `correct_answer` char(1) NOT NULL COMMENT 'A, B, C, or D',
  `explanation` text DEFAULT NULL,
  `order_number` int(11) NOT NULL DEFAULT 0,
  `points` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `task_quizzes`
--

INSERT INTO `task_quizzes` (`id`, `task_id`, `question`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_answer`, `explanation`, `order_number`, `points`, `created_at`) VALUES
(1, 1, 'How much is the joining fee in Bestmula', '80', '100', '200', '50', 'B', 'It’s 100', 0, 5, '2026-04-25 23:35:53');

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

--
-- Table structure for table `task_settings`
--

CREATE TABLE `task_settings` (
  `id` int(11) NOT NULL,
  `task_type` varchar(50) NOT NULL,
  `auto_approve` tinyint(1) DEFAULT 0,
  `requires_screenshot` tinyint(1) DEFAULT 0,
  `min_completion_time` int(11) DEFAULT 30 COMMENT 'Seconds',
  `max_daily_per_user` int(11) DEFAULT NULL,
  `is_active` tinyint(1) 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=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `task_submissions`
--

CREATE TABLE `task_submissions` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_completion_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `submission_type` varchar(20) NOT NULL,
  `submission_content` longtext DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `quiz_answers` text DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `max_score` int(11) 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=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `task_survey_questions`
--

CREATE TABLE `task_survey_questions` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `question` text NOT NULL,
  `question_type` varchar(50) NOT NULL DEFAULT 'text' COMMENT 'text, multiple_choice, rating, yes_no',
  `options` text DEFAULT NULL COMMENT 'Pipe-separated for multiple choice',
  `required` tinyint(1) NOT NULL DEFAULT 1,
  `order_number` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `task_transcription_content`
--

CREATE TABLE `task_transcription_content` (
  `id` int(11) UNSIGNED NOT NULL,
  `task_id` int(11) UNSIGNED NOT NULL,
  `audio_url` varchar(500) DEFAULT NULL,
  `video_url` varchar(500) DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL,
  `language` varchar(50) NOT NULL DEFAULT 'English',
  `expected_word_count` int(11) DEFAULT NULL,
  `difficulty_level` varchar(20) NOT NULL DEFAULT 'medium' COMMENT 'easy, medium, hard',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `task_wallets`
--

CREATE TABLE `task_wallets` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT 'General Tasks',
  `balance` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `task_wallet_transactions`
--

CREATE TABLE `task_wallet_transactions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `type` enum('credit','debit') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `balance_before` decimal(10,2) NOT NULL,
  `balance_after` decimal(10,2) NOT NULL,
  `description` varchar(500) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `timetable`
--

CREATE TABLE `timetable` (
  `id` int(11) NOT NULL,
  `day_of_week` int(11) NOT NULL COMMENT '1=Monday, 7=Sunday',
  `activity_type` enum('tasks','videos','trivia','ads') NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `wallet_type` enum('referral','task','video','trivia','ads','bonus','blog','activation') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `type` enum('credit','debit','activation_fee') NOT NULL,
  `description` varchar(255) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled','awarded') DEFAULT 'pending',
  `meta_json` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `gateway` varchar(20) DEFAULT 'lipalink' COMMENT 'lipalink or hashpay'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
PARTITION BY RANGE (unix_timestamp(`created_at`))
(
PARTITION p_2026_01 VALUES LESS THAN (1769904000) ENGINE=InnoDB,
PARTITION p_2026_02 VALUES LESS THAN (1772323200) ENGINE=InnoDB,
PARTITION p_2026_03 VALUES LESS THAN (1775001600) ENGINE=InnoDB,
PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` (`id`, `user_id`, `wallet_type`, `amount`, `type`, `description`, `reference`, `status`, `meta_json`, `created_at`, `gateway`) VALUES
(1, 2, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX2026042423130153996C', 'failed', '{\"synced_from_api\":true,\"pf_status\":\"failed\",\"result_desc\":\"\",\"mpesa_receipt\":null,\"reason\":\"failed\",\"synced_at\":\"2026-04-24 23:13:35\"}', '2026-04-24 20:13:01', 'lipalink'),
(2, 2, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX20260424231414889405', 'completed', '{\"mpesa_receipt\":\"UDOCQ243WY\",\"transaction_amount\":100,\"processed_at\":\"2026-04-24 23:14:35\",\"payer_id\":2,\"activating_user_id\":2}', '2026-04-24 20:14:14', 'lipalink'),
(3, 1, '', 75.00, '', 'Level 1 referral bonus from user #2 (client_activation)', 'REF_L1_2_1777061675_2054', 'completed', NULL, '2026-04-24 20:14:35', 'lipalink'),
(4, 3, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX20260424231619912C47', 'completed', '{\"mpesa_receipt\":\"UDOCQ243XL\",\"transaction_amount\":100,\"processed_at\":\"2026-04-24 23:16:43\",\"payer_id\":3,\"activating_user_id\":3}', '2026-04-24 20:16:19', 'lipalink'),
(5, 2, '', 75.00, '', 'Level 1 referral bonus from user #3 (client_activation)', 'REF_L1_3_1777061803_6001', 'completed', NULL, '2026-04-24 20:16:43', 'lipalink'),
(6, 1, '', 5.00, '', 'Level 2 referral bonus from user #3 (client_activation)', 'REF_L2_3_1777061807_6099', 'completed', NULL, '2026-04-24 20:16:47', 'lipalink'),
(7, 2, '', 50.00, 'credit', 'Admin spin top-up via M-PESA: Ksh 50', 'PFX20260425231340E432DB', 'pending', '{\"spin_credit\":true,\"admin_uid\":4}', '2026-04-25 20:13:42', 'lipalink'),
(8, 2, '', 50.00, 'credit', 'Admin spin top-up via M-PESA: Ksh 50', 'PFX2026042523151440A1CA', 'completed', '{\"mpesa_receipt\":\"UDPCQ289XJ\",\"transaction_amount\":50,\"processed_at\":\"2026-04-25 23:15:36\",\"payer_id\":2,\"activating_user_id\":2}', '2026-04-25 20:15:17', 'lipalink'),
(9, 1, '', 80.00, '', 'Level 1 referral bonus from user #2 (client_activation)', 'REF_L1_2_1777148136_4163', 'completed', NULL, '2026-04-25 20:15:36', 'lipalink'),
(10, 2, '', 100.00, 'credit', 'Spin wallet deposit: Ksh100', 'PFX20260425232325745065', 'completed', '{\"mpesa_receipt\":\"UDPCQ28EAA\",\"transaction_amount\":100,\"processed_at\":\"2026-04-25 23:23:52\",\"payer_id\":2,\"activating_user_id\":2}', '2026-04-25 20:23:32', 'lipalink'),
(11, 1, '', 80.00, '', 'Level 1 referral bonus from user #2 (client_activation)', 'REF_L1_2_1777148632_7646', 'completed', NULL, '2026-04-25 20:23:52', 'lipalink'),
(12, 2, '', 50.00, 'credit', 'Spin wallet deposit: Ksh50', 'PFX20260425232728EEEEEE', 'completed', '{\"spin_deposit\":true,\"original_ref\":\"SPIN_DEP_2_1777148848\"}', '2026-04-25 20:27:30', 'lipalink'),
(13, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777148900', 'completed', NULL, '2026-04-25 20:28:20', 'lipalink'),
(14, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777148913', 'completed', NULL, '2026-04-25 20:28:33', 'lipalink'),
(15, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777148922', 'completed', NULL, '2026-04-25 20:28:42', 'lipalink'),
(16, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777148931', 'completed', NULL, '2026-04-25 20:28:51', 'lipalink'),
(17, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149058', 'completed', NULL, '2026-04-25 20:30:58', 'lipalink'),
(18, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149067', 'completed', NULL, '2026-04-25 20:31:07', 'lipalink'),
(19, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149100', 'completed', NULL, '2026-04-25 20:31:40', 'lipalink'),
(20, 2, '', 19.00, 'credit', 'Spin WIN: bet odd Ksh10 → Ksh19', 'SPIN_2_1777149109', 'completed', NULL, '2026-04-25 20:31:49', 'lipalink'),
(21, 2, '', 19.00, 'credit', 'Spin WIN: bet odd Ksh10 → Ksh19', 'SPIN_2_1777149120', 'completed', NULL, '2026-04-25 20:32:00', 'lipalink'),
(22, 2, '', 19.00, 'credit', 'Spin WIN: bet odd Ksh10 → Ksh19', 'SPIN_2_1777149129', 'completed', NULL, '2026-04-25 20:32:09', 'lipalink'),
(23, 2, '', 10.00, 'debit', 'Spin LOSS: bet odd Ksh10', 'SPIN_2_1777149137', 'completed', NULL, '2026-04-25 20:32:17', 'lipalink'),
(24, 2, '', 10.00, 'debit', 'Spin LOSS: bet odd Ksh10', 'SPIN_2_1777149147', 'completed', NULL, '2026-04-25 20:32:27', 'lipalink'),
(25, 2, '', 10.00, 'debit', 'Spin LOSS: bet odd Ksh10', 'SPIN_2_1777149156', 'completed', NULL, '2026-04-25 20:32:36', 'lipalink'),
(26, 2, '', 10.00, 'debit', 'Spin LOSS: bet odd Ksh10', 'SPIN_2_1777149163', 'completed', NULL, '2026-04-25 20:32:43', 'lipalink'),
(27, 2, '', 10.00, 'debit', 'Spin LOSS: bet odd Ksh10', 'SPIN_2_1777149171', 'completed', NULL, '2026-04-25 20:32:51', 'lipalink'),
(28, 2, '', 10.00, 'debit', 'Spin LOSS: bet odd Ksh10', 'SPIN_2_1777149179', 'completed', NULL, '2026-04-25 20:32:59', 'lipalink'),
(29, 2, '', 100.00, '', 'Digital product: Bestmula', 'PFX202604252333575E330B', 'completed', '{\"mpesa_receipt\":\"UDPCQ288VE\",\"processed_at\":\"2026-04-25 23:34:18\",\"type\":\"product_purchase\"}', '2026-04-25 20:33:57', 'lipalink'),
(30, 1, '', 30.00, '', 'L1 product commission (60% of pool) — \"Bestmula\" (buyer: Mkuu)', 'PCOMM_L1_1_1777149258_873', 'completed', NULL, '2026-04-25 20:34:18', 'lipalink'),
(31, 2, '', 100.00, 'credit', 'Spin wallet deposit: Ksh100', 'PFX20260425233642A784DE', 'failed', '{\"spin_deposit\":true,\"original_ref\":\"SPIN_DEP_2_1777149402\"}', '2026-04-25 20:36:44', 'lipalink'),
(32, 2, '', 100.00, 'credit', 'Spin wallet deposit: Ksh100', 'PFX20260425233753B44310', 'completed', '{\"spin_deposit\":true,\"original_ref\":\"SPIN_DEP_2_1777149473\"}', '2026-04-25 20:37:55', 'lipalink'),
(33, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149607', 'completed', NULL, '2026-04-25 20:40:07', 'lipalink'),
(34, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149613', 'completed', NULL, '2026-04-25 20:40:13', 'lipalink'),
(35, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149619', 'completed', NULL, '2026-04-25 20:40:19', 'lipalink'),
(36, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149625', 'completed', NULL, '2026-04-25 20:40:25', 'lipalink'),
(37, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149631', 'completed', NULL, '2026-04-25 20:40:31', 'lipalink'),
(38, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149637', 'completed', NULL, '2026-04-25 20:40:37', 'lipalink'),
(39, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149645', 'completed', NULL, '2026-04-25 20:40:45', 'lipalink'),
(40, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149653', 'completed', NULL, '2026-04-25 20:40:53', 'lipalink'),
(41, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149660', 'completed', NULL, '2026-04-25 20:41:00', 'lipalink'),
(42, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149666', 'completed', NULL, '2026-04-25 20:41:06', 'lipalink'),
(43, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149671', 'completed', NULL, '2026-04-25 20:41:11', 'lipalink'),
(44, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149677', 'completed', NULL, '2026-04-25 20:41:17', 'lipalink'),
(45, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149733', 'completed', NULL, '2026-04-25 20:42:13', 'lipalink'),
(46, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149739', 'completed', NULL, '2026-04-25 20:42:19', 'lipalink'),
(47, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149838', 'completed', NULL, '2026-04-25 20:43:58', 'lipalink'),
(48, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149844', 'completed', NULL, '2026-04-25 20:44:04', 'lipalink'),
(49, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149871', 'completed', NULL, '2026-04-25 20:44:31', 'lipalink'),
(50, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149878', 'completed', NULL, '2026-04-25 20:44:38', 'lipalink'),
(51, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149886', 'completed', NULL, '2026-04-25 20:44:46', 'lipalink'),
(52, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777149894', 'completed', NULL, '2026-04-25 20:44:54', 'lipalink'),
(53, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149926', 'completed', NULL, '2026-04-25 20:45:26', 'lipalink'),
(54, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777149933', 'completed', NULL, '2026-04-25 20:45:33', 'lipalink'),
(55, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777150022', 'completed', NULL, '2026-04-25 20:47:02', 'lipalink'),
(56, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777150028', 'completed', NULL, '2026-04-25 20:47:08', 'lipalink'),
(57, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150042', 'completed', NULL, '2026-04-25 20:47:22', 'lipalink'),
(58, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150047', 'completed', NULL, '2026-04-25 20:47:27', 'lipalink'),
(59, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150053', 'completed', NULL, '2026-04-25 20:47:33', 'lipalink'),
(60, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777150059', 'completed', NULL, '2026-04-25 20:47:39', 'lipalink'),
(61, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150065', 'completed', NULL, '2026-04-25 20:47:45', 'lipalink'),
(62, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777150071', 'completed', NULL, '2026-04-25 20:47:51', 'lipalink'),
(63, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150076', 'completed', NULL, '2026-04-25 20:47:56', 'lipalink'),
(64, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777150950', 'completed', NULL, '2026-04-25 21:02:30', 'lipalink'),
(65, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150957', 'completed', NULL, '2026-04-25 21:02:37', 'lipalink'),
(66, 2, '', 19.00, 'credit', 'Spin WIN: bet red Ksh10 → Ksh19', 'SPIN_2_1777150963', 'completed', NULL, '2026-04-25 21:02:43', 'lipalink'),
(67, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777150981', 'completed', NULL, '2026-04-25 21:03:01', 'lipalink'),
(68, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777151028', 'completed', NULL, '2026-04-25 21:03:48', 'lipalink'),
(69, 2, '', 20.00, 'credit', 'Spin wallet deposit: Ksh20', 'PFX20260426003126E9A40F', 'failed', '{\"spin_deposit\":true,\"original_ref\":\"SPIN_DEP_2_1777152686\"}', '2026-04-25 21:31:29', 'lipalink'),
(70, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236124', 'completed', NULL, '2026-04-26 20:42:04', 'lipalink'),
(71, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236130', 'completed', NULL, '2026-04-26 20:42:10', 'lipalink'),
(72, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236136', 'completed', NULL, '2026-04-26 20:42:16', 'lipalink'),
(73, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236142', 'completed', NULL, '2026-04-26 20:42:22', 'lipalink'),
(74, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236148', 'completed', NULL, '2026-04-26 20:42:28', 'lipalink'),
(75, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236154', 'completed', NULL, '2026-04-26 20:42:34', 'lipalink'),
(76, 2, '', 10.00, 'debit', 'Spin LOSS: bet red Ksh10', 'SPIN_2_1777236160', 'completed', NULL, '2026-04-26 20:42:40', 'lipalink'),
(77, 4, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX202604280406120AC6CC', 'completed', '{\"mpesa_receipt\":\"UDSLN289FK\",\"transaction_amount\":100,\"processed_at\":\"2026-04-28 04:06:24\",\"payer_id\":4,\"activating_user_id\":4}', '2026-04-28 01:06:12', 'lipalink'),
(78, 1, '', 80.00, '', 'Level 1 referral bonus from user #4 (client_activation)', 'REF_L1_4_1777338384_5137', 'completed', NULL, '2026-04-28 01:06:24', 'lipalink'),
(79, 5, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX20260429000812BBFA6A', 'completed', '{\"mpesa_receipt\":\"UDTCQ2KRM5\",\"transaction_amount\":100,\"processed_at\":\"2026-04-29 00:08:22\",\"payer_id\":5,\"activating_user_id\":5}', '2026-04-28 21:08:12', 'lipalink'),
(80, 2, '', 80.00, '', 'Level 1 referral bonus from user #5 (client_activation)', 'REF_L1_5_1777410502_2806', 'completed', NULL, '2026-04-28 21:08:22', 'lipalink'),
(81, 1, '', 5.00, '', 'Level 2 referral bonus from user #5 (client_activation)', 'REF_L2_5_1777410504_3148', 'completed', NULL, '2026-04-28 21:08:24', 'lipalink'),
(82, 2, '', 50.00, 'credit', 'Chat Foreigners L1 commission from user #5 payment (Ksh 100)', 'CF_COMM_L1_5', 'completed', NULL, '2026-04-28 21:09:34', 'lipalink'),
(83, 1, '', 80.00, '', 'Level 1 referral bonus from user #6 (admin_activation)', 'REF_L1_6_1777425623_9922', 'completed', NULL, '2026-04-29 01:20:23', 'lipalink'),
(84, 5, '', 80.00, '', 'Level 1 referral bonus from user #6 (admin_activation)', 'REF_L1_6_1777425702_1234', 'completed', NULL, '2026-04-29 01:21:42', 'lipalink'),
(85, 2, '', 5.00, '', 'Level 2 referral bonus from user #6 (admin_activation)', 'REF_L2_6_1777425704_6611', 'completed', NULL, '2026-04-29 01:21:44', 'lipalink'),
(86, 5, '', 80.00, '', 'Level 1 referral bonus from user #6 (admin_activation)', 'REF_L1_6_1777425954_8240', 'completed', NULL, '2026-04-29 01:25:54', 'lipalink'),
(87, 2, '', 5.00, '', 'Level 2 referral bonus from user #6 (admin_activation)', 'REF_L2_6_1777425957_5183', 'completed', NULL, '2026-04-29 01:25:57', 'lipalink'),
(89, 5, '', 33.60, '', 'L1 product commission (60% of pool) — \"YouTube Earn(Ksh300)\" (buyer: Cameroon)', 'PCOMM_L1_2_1777426699_324', 'completed', NULL, '2026-04-29 01:38:19', 'lipalink'),
(90, 2, '', 16.80, '', 'L2 product commission (30% of pool) — \"YouTube Earn(Ksh300)\" (buyer: Cameroon)', 'PCOMM_L2_2_1777426699_704', 'completed', NULL, '2026-04-29 01:38:19', 'lipalink'),
(91, 1, '', 5.60, '', 'L3 product commission (10% of pool) — \"YouTube Earn(Ksh300)\" (buyer: Cameroon)', 'PCOMM_L3_2_1777426699_887', 'completed', NULL, '2026-04-29 01:38:19', 'lipalink'),
(95, 9, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX20260429200241924054', 'failed', '{\"synced_from_api\":true,\"pf_status\":\"failed\",\"result_desc\":\"\",\"mpesa_receipt\":null,\"reason\":\"failed\",\"synced_at\":\"2026-04-29 20:03:10\"}', '2026-04-29 17:02:41', 'lipalink'),
(96, 1, '', 80.00, '', 'Level 1 referral bonus from user #10 (admin_activation)', 'REF_L1_10_1777514125_4579', 'completed', NULL, '2026-04-30 01:55:25', 'lipalink'),
(97, 1, '', 80.00, '', 'Level 1 referral bonus from user #14 (admin_activation)', 'REF_L1_14_1777514433_2874', 'completed', NULL, '2026-04-30 02:00:33', 'lipalink'),
(98, 1, '', 80.00, '', 'Level 1 referral bonus from user #11 (admin_activation)', 'REF_L1_11_1777515084_6255', 'completed', NULL, '2026-04-30 02:11:24', 'lipalink'),
(99, 1, '', 80.00, '', 'Level 1 referral bonus from user #15 (admin_activation)', 'REF_L1_15_1777515529_4892', 'completed', NULL, '2026-04-30 02:18:49', 'lipalink'),
(100, 1, '', 80.00, '', 'Level 1 referral bonus from user #13 (admin_activation)', 'REF_L1_13_1777515607_2398', 'completed', NULL, '2026-04-30 02:20:07', 'lipalink'),
(101, 1, '', 80.00, '', 'Level 1 referral bonus from user #12 (admin_activation)', 'REF_L1_12_1777515871_5162', 'completed', NULL, '2026-04-30 02:24:31', 'lipalink'),
(102, 2, '', 500.25, '', 'Level 1 referral bonus from user #16 (admin_activation)', 'REF_L1_16_1777537440_9111', 'completed', NULL, '2026-04-30 08:24:00', 'lipalink'),
(103, 1, '', 100.28, '', 'Level 2 referral bonus from user #16 (admin_activation)', 'REF_L2_16_1777537441_9875', 'completed', NULL, '2026-04-30 08:24:01', 'lipalink'),
(104, 1, '', 500.25, '', 'Level 1 referral bonus from user #13 (admin_activation)', 'REF_L1_13_1777537610_4356', 'completed', NULL, '2026-04-30 08:26:50', 'lipalink'),
(105, 8, '', 500.00, '', 'Digital product: Chat with foreigners (Ksh3600)', 'PFX202604301133268F8A9D', 'pending', '{\"purchase_id\":3,\"product_id\":6,\"type\":\"product_purchase\"}', '2026-04-30 08:33:26', 'lipalink'),
(106, 17, 'activation', 100.00, 'activation_fee', 'Account activation payment', 'PFX202604301139482A5268', 'completed', '{\"mpesa_receipt\":\"UDUCQ2Q376\",\"transaction_amount\":100,\"processed_at\":\"2026-04-30 11:40:16\",\"payer_id\":17,\"activating_user_id\":17}', '2026-04-30 08:39:48', 'lipalink'),
(107, 2, '', 80.00, '', 'Level 1 referral bonus from user #17 (client_activation)', 'REF_L1_17_1777538416_3854', 'completed', NULL, '2026-04-30 08:40:16', 'lipalink'),
(108, 1, '', 5.00, '', 'Level 2 referral bonus from user #17 (client_activation)', 'REF_L2_17_1777538417_4732', 'completed', NULL, '2026-04-30 08:40:17', 'lipalink'),
(109, 2, '', 100.00, 'credit', 'Spin wallet deposit: Ksh100', 'PFX20260430114156E18CCF', 'failed', '{\"spin_deposit\":true,\"original_ref\":\"SPIN_DEP_2_1777538516\"}', '2026-04-30 08:41:58', 'lipalink');

--
-- Triggers `transactions`
--
DELIMITER $$
CREATE TRIGGER `trg_transactions_validate_ids` BEFORE INSERT ON `transactions` FOR EACH ROW BEGIN
  IF NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Invalid user_id: User does not exist';
  END IF;
END
$$
DELIMITER ;

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `country` varchar(50) DEFAULT 'Kenya',
  `password` varchar(255) NOT NULL,
  `referral_code` varchar(20) NOT NULL,
  `referred_by` int(11) DEFAULT NULL,
  `status` enum('inactive','active') DEFAULT 'inactive',
  `activation_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `users` (`id`, `username`, `email`, `phone`, `country`, `password`, `referral_code`, `referred_by`, `status`, `activation_date`, `created_at`) VALUES
(1, 'bestmula', 'admin@DALITECH.com', '0700765000', 'Kenya', '$2y$10$2JzqmTKekDCycAiEiafoq.5Gt4SBAtfHJ1M7GjVKfUlKUW660VUjS', 'bestmula', NULL, 'active', NULL, '2026-04-24 20:11:06'),
(2, 'Mkuu', 'dakatjk@gmail.com', '0718308478', 'Kenya', '$2y$10$hU49ReB97vQL/qH1LfvczuEpDwe0RpNZ43Aqz3l1yx9eZmAvnmaiu', '6ZJKSJZQ', 1, 'active', '2026-04-24 23:14:35', '2026-04-24 20:11:18'),
(3, 'Saaa', 'realqashagencies@gmail.com', '0718308479', 'Kenya', '$2y$10$tM/2XHWPZKex9gsqcpIBmex/96IT2/Vzyxi4iFRUGnx6HBQ5syj32', 'G77HXEHH', 2, 'active', '2026-04-24 23:16:43', '2026-04-24 20:16:14'),
(4, 'Brian', 'yellow@gmail.com', '072222222', 'Kenya', '$2y$10$HXQD2HyLWsezlSFvk2VVS./yqlitsK.HoTOQhY4s0OVduWBIxXl0.', '9HWX3SCB', 1, 'active', '2026-04-28 04:06:24', '2026-04-28 01:05:36'),
(5, 'TEST', 'mwaganubrian@gmail.com', '0718308477', 'Kenya', '$2y$10$pXWvg5pxm0klRCfiUoBdwOCkRX0lzW9z81oqo81eNTc2aBZ5o9hCa', 'MMBXHPSN', 2, 'active', '2026-04-29 00:08:22', '2026-04-28 21:07:33'),
(7, 'test223', 'hvhcf@gmail.com', '0714433434', 'Kenya', '$2y$10$6zfL11hAFB0hnPEWB6CewuqFLg87k4N9.39nOVT3l3jI4EJA5qVwm', 'EJ8RZAQZ', 1, 'inactive', NULL, '2026-04-29 06:38:22'),
(8, 'Test22', 'dakastjk@gmail.com', '0718308455', 'Ghana', '$2y$10$DMBc7NYIbAfLtIo1yxqldepLDcEhUSnjmuV3XCpwRPBS/4nK1yWJy', '7DU2ASAG', 1, 'active', NULL, '2026-04-29 07:14:06'),
(9, 'Davwil', 'davwilmayenga01@gmail.com', '0740678979', 'Kenya', '$2y$10$73mobAnhPKd5nJWWzB.xfeJS4FvPpcJuTBhmnpUUxpQ.B.XFxKfcC', 'J34AUJ4U', 1, 'inactive', NULL, '2026-04-29 17:01:23'),
(10, 'Ghana', 'Ghana@gmail.com', '0712121212', 'Ghana', '$2y$10$T/ZoRVNZJrHI4NGgK/ROWONuPvd5V7rDSXgKJI.JRXXE7QQmRSGkq', 'MWHG6URF', 1, 'active', '2026-04-30 04:55:25', '2026-04-30 01:04:22'),
(11, 'Zambia', 'zambia@gmail.com', '0721212121', 'Zambia', '$2y$10$Lt8VFiu7PY8Bt0M017.j7OGRss3wEOa3bZeZQVFAdzaoiJ4mnlLQK', 'Y5RK7HFS', 1, 'active', '2026-04-30 05:11:24', '2026-04-30 01:09:53'),
(12, 'Uganda', 'uganda@gmail.com', '0790612444', 'Uganda', '$2y$10$NSwlIY6cHMke8EyfruhyQuzJNww94vCro6NDbCb/Lyf8VuL2SIzAS', 'CQU6HTRW', 1, 'active', '2026-04-30 05:24:31', '2026-04-30 01:18:38'),
(13, 'Cameroon', 'cameroon@gmail.com', '0731313131', 'Cameroon', '$2y$10$Cb0PTQJOazTC/TZKi19FaerxHgwNUxiD.mercZnVNFWmlw0eF/iQm', 'F5DFKM9P', 1, 'active', '2026-04-30 05:20:07', '2026-04-30 01:20:34'),
(14, 'Malawi', 'malawi@gmail.com', '074545454', 'Malawi', '$2y$10$QtZk/n.k1rnRrdkePHZjn.MNAVRtYSeqwJOJpqnDwCIYc0zIS1LPO', 'SUSF9496', 1, 'active', '2026-04-30 05:00:33', '2026-04-30 01:39:41'),
(15, 'Tanzania', 'Tanzania@gmail.com', '075858585', 'Tanzania', '$2y$10$Zje2c8IOUfAT7qcBlwGK2.UYw7rkperPyBSCC4ph9wdh5hKNH/bmW', 'Z8Z3VFEF', 1, 'active', '2026-04-30 05:18:49', '2026-04-30 01:43:36'),
(16, 'Mkuu', 'faaf@gmail.com', '0718308452', 'Cameroon', '$2y$10$fmpHGQyPCpqtFWDFB0g8P.O.39mV.Neh91OAmrJ6bu4y7JAuy8BuK', 'KTZH799X', 2, 'active', '2026-04-30 11:24:00', '2026-04-30 08:21:25'),
(17, 'Test254', 'bahgg@gmail.com', '0718308855', 'Kenya', '$2y$10$uvBkdnsQGYpOb2dIEdEudOlA.tGPQPQ98qp2/Xi4r6Vy0WyuGXGiW', 'RBSGA9FS', 2, 'active', '2026-04-30 11:40:16', '2026-04-30 08:39:39');

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

--
-- Table structure for table `user_bonus_claims`
--

CREATE TABLE `user_bonus_claims` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `bonus_tier_id` int(11) NOT NULL,
  `claimed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `week_key` varchar(10) DEFAULT '' COMMENT 'ISO year-week e.g. 2026-W17'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `view_task_completions_summary`
--

CREATE TABLE `view_task_completions_summary` (
  `id` int(11) UNSIGNED DEFAULT NULL,
  `task_id` int(11) UNSIGNED DEFAULT NULL,
  `task_title` varchar(255) DEFAULT NULL,
  `task_type` varchar(50) DEFAULT NULL,
  `user_id` int(11) UNSIGNED DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `reward_earned` decimal(10,2) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `reviewed_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `vw_active_tasks`
--

CREATE TABLE `vw_active_tasks` (
  `id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `task_type` varchar(50) DEFAULT NULL,
  `content_type` varchar(20) DEFAULT NULL,
  `task_url` varchar(500) DEFAULT NULL,
  `reward` decimal(10,2) DEFAULT NULL,
  `daily_limit_per_user` int(11) DEFAULT NULL,
  `total_limit` int(11) DEFAULT NULL,
  `total_completions` int(11) UNSIGNED DEFAULT NULL,
  `min_duration_seconds` int(11) UNSIGNED DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `featured` tinyint(1) DEFAULT NULL,
  `difficulty` varchar(20) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `show_in_sidebar` tinyint(1) DEFAULT NULL,
  `auto_approve` tinyint(1) DEFAULT NULL,
  `requires_proof` tinyint(1) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `last_modified_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `unique_users` bigint(21) DEFAULT NULL,
  `total_paid` decimal(32,2) DEFAULT NULL,
  `completion_count` bigint(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `vw_task_statistics`
--

CREATE TABLE `vw_task_statistics` (
  `id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `task_type` varchar(50) DEFAULT NULL,
  `content_type` varchar(20) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `reward` decimal(10,2) DEFAULT NULL,
  `unique_users` bigint(21) DEFAULT NULL,
  `total_attempts` bigint(21) DEFAULT NULL,
  `completed_count` decimal(22,0) DEFAULT NULL,
  `pending_count` decimal(22,0) DEFAULT NULL,
  `rejected_count` decimal(22,0) DEFAULT NULL,
  `total_rewards_paid` decimal(32,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `wallets`
--

CREATE TABLE `wallets` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Total balance across all wallets',
  `total_earned` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Lifetime earnings',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `main_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Main/MLM earnings',
  `referral_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Referral commissions',
  `bonus_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Bonuses and promotions',
  `video_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Video task earnings',
  `survey_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Survey task earnings',
  `chat_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Chat task earnings',
  `transcription_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Transcription earnings',
  `trivia_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Trivia quiz earnings',
  `ads_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Ad viewing earnings',
  `spin_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Spin wheel earnings',
  `blog_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Blog writing earnings',
  `meme_wallet` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Meme contest earnings'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='One row per user with all wallet types';

--
-- Dumping data for table `wallets`
--

INSERT INTO `wallets` (`id`, `user_id`, `balance`, `total_earned`, `created_at`, `updated_at`, `main_wallet`, `referral_wallet`, `bonus_wallet`, `video_wallet`, `survey_wallet`, `chat_wallet`, `transcription_wallet`, `trivia_wallet`, `ads_wallet`, `spin_wallet`, `blog_wallet`, `meme_wallet`) VALUES
(1, 1, 1526.13, 1526.13, '2026-04-24 20:11:06', '2026-04-30 08:40:17', 35.60, 1490.53, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(2, 2, 847.05, 762.05, '2026-04-24 20:11:18', '2026-04-30 08:40:16', 16.80, 745.25, 100.00, 0.00, 0.00, 50.00, 0.00, 25.00, 0.00, 35.00, 0.00, 0.00),
(3, 3, 0.00, 0.00, '2026-04-24 20:16:14', '2026-04-25 23:48:58', 0.00, 1.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(5, 4, 0.00, 0.00, '2026-04-28 01:05:36', '2026-04-28 01:05:36', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(6, 5, 193.60, 193.60, '2026-04-28 21:07:33', '2026-04-29 01:38:19', 33.60, 160.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(8, 7, 0.00, 0.00, '2026-04-29 06:38:22', '2026-04-29 06:38:22', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(9, 8, 0.00, 0.00, '2026-04-29 07:14:06', '2026-04-29 07:14:06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(10, 9, 0.00, 0.00, '2026-04-29 17:01:23', '2026-04-29 17:01:23', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(11, 10, 0.00, 0.00, '2026-04-30 01:04:22', '2026-04-30 01:04:22', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(12, 11, 0.00, 0.00, '2026-04-30 01:09:53', '2026-04-30 01:09:53', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(13, 12, 0.00, 0.00, '2026-04-30 01:18:38', '2026-04-30 01:18:38', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(14, 13, 0.00, 0.00, '2026-04-30 01:20:34', '2026-04-30 08:26:51', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(15, 14, 0.00, 0.00, '2026-04-30 01:39:41', '2026-04-30 01:39:41', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(16, 15, 0.00, 0.00, '2026-04-30 01:43:36', '2026-04-30 01:43:36', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(17, 16, 0.00, 0.00, '2026-04-30 08:21:25', '2026-04-30 08:21:25', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00),
(18, 17, 0.00, 0.00, '2026-04-30 08:39:39', '2026-04-30 08:39:39', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00);

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

--
-- Table structure for table `wallet_recharges`
--

CREATE TABLE `wallet_recharges` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `phone` varchar(20) NOT NULL DEFAULT '',
  `checkout_request_id` varchar(100) NOT NULL DEFAULT '',
  `mpesa_receipt` varchar(50) DEFAULT NULL,
  `status` enum('pending','completed','failed','cancelled') DEFAULT 'pending',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `wallet_recharges`
--

INSERT INTO `wallet_recharges` (`id`, `user_id`, `amount`, `phone`, `checkout_request_id`, `mpesa_receipt`, `status`, `created_at`, `completed_at`) VALUES
(1, 2, 100.00, '254791706089', 'PFX20260428125451B1A27D', NULL, 'pending', '2026-04-28 12:54:51', NULL);

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

--
-- Table structure for table `wallet_task`
--

CREATE TABLE `wallet_task` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `balance` decimal(10,2) DEFAULT 0.00,
  `total_earned` decimal(10,2) DEFAULT 0.00,
  `withdrawable` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `wallet_task`
--

INSERT INTO `wallet_task` (`id`, `user_id`, `balance`, `total_earned`, `withdrawable`) VALUES
(1, 2, 0.00, 0.00, 1),
(2, 3, 0.00, 0.00, 1),
(3, 4, 0.00, 0.00, 1),
(4, 5, 0.00, 0.00, 1),
(5, 6, 0.00, 0.00, 1),
(6, 7, 0.00, 0.00, 1),
(7, 8, 0.00, 0.00, 1),
(8, 9, 0.00, 0.00, 1),
(9, 10, 0.00, 0.00, 1),
(10, 11, 0.00, 0.00, 1),
(11, 12, 0.00, 0.00, 1),
(12, 13, 0.00, 0.00, 1),
(13, 14, 0.00, 0.00, 1),
(14, 15, 0.00, 0.00, 1),
(15, 16, 0.00, 0.00, 1),
(16, 17, 0.00, 0.00, 1);

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

--
-- Table structure for table `wallet_withdrawal_settings`
--

CREATE TABLE `wallet_withdrawal_settings` (
  `id` int(11) NOT NULL,
  `wallet_type` varchar(50) NOT NULL,
  `status` enum('enabled','disabled') DEFAULT 'enabled',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `wallet_withdrawal_settings`
--

INSERT INTO `wallet_withdrawal_settings` (`id`, `wallet_type`, `status`, `created_at`, `updated_at`) VALUES
(1, 'referral_wallet', 'enabled', '2026-04-26 00:54:19', '2026-04-27 14:55:43'),
(2, 'bonus_wallet', 'enabled', '2026-04-26 00:54:19', '2026-04-27 14:55:43'),
(3, 'main_wallet', 'enabled', '2026-04-26 00:54:33', '2026-04-27 14:55:43'),
(6, 'survey_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-26 00:54:59'),
(7, 'ads_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-26 00:55:43'),
(8, 'spin_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-26 00:56:36'),
(9, 'blog_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-26 00:56:20'),
(10, 'meme_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-26 00:56:04'),
(11, 'chat_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-27 14:55:43'),
(12, 'transcription_wallet', 'disabled', '2026-04-26 00:54:33', '2026-04-26 00:55:47'),
(23, 'trivia_wallet', 'disabled', '2026-04-26 00:55:05', '2026-04-26 00:55:37'),
(33, 'video_wallet', 'disabled', '2026-04-26 00:55:18', '2026-04-26 00:55:28');

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

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

CREATE TABLE `withdrawals` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `wallet_type` enum('referral','task','video','trivia','ads','bonus','blog') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `fee` decimal(10,2) DEFAULT 0.00,
  `net_amount` decimal(10,2) NOT NULL,
  `payment_method` varchar(50) DEFAULT 'MPESA',
  `phone_number` varchar(20) NOT NULL,
  `country` varchar(100) DEFAULT 'Kenya',
  `status` enum('pending','processing','completed','rejected') DEFAULT 'pending',
  `hashback_reference` varchar(100) DEFAULT NULL,
  `hashback_status` varchar(30) DEFAULT NULL,
  `hashback_result_desc` text DEFAULT NULL,
  `admin_note` text DEFAULT NULL,
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `processed_at` datetime DEFAULT NULL,
  `processed_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `withdrawal_fee_tiers`
--

CREATE TABLE `withdrawal_fee_tiers` (
  `id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `min_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_amount` decimal(10,2) NOT NULL,
  `fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `withdrawal_fee_tiers`
--

INSERT INTO `withdrawal_fee_tiers` (`id`, `country_id`, `min_amount`, `max_amount`, `fee`, `created_at`) VALUES
(9, 1, 100.00, 499.00, 30.00, '2026-04-27 01:17:03'),
(10, 1, 500.00, 1999.00, 45.00, '2026-04-27 01:17:03'),
(11, 1, 2000.00, 3999.00, 60.00, '2026-04-27 01:17:03'),
(12, 1, 4000.00, 6999.00, 80.00, '2026-04-27 01:17:03'),
(13, 1, 7000.00, 9999.00, 100.00, '2026-04-27 01:17:03'),
(14, 1, 10000.00, 14999.00, 130.00, '2026-04-27 01:17:03'),
(15, 1, 15000.00, 24999.00, 150.00, '2026-04-27 01:17:03'),
(16, 1, 25000.00, 50000.00, 200.00, '2026-04-27 01:17:03'),
(17, 1, 50000.00, 200000.00, 300.00, '2026-04-27 01:17:03');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_admin_status` (`status`),
  ADD KEY `idx_admin_role` (`role`);

--
-- Indexes for table `admin_logs`
--
ALTER TABLE `admin_logs`
  ADD PRIMARY KEY (`id`,`created_at`),
  ADD KEY `idx_admin_id` (`admin_id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_target_id` (`target_id`);

--
-- Indexes for table `admin_permissions`
--
ALTER TABLE `admin_permissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `admin_id` (`admin_id`),
  ADD KEY `permission` (`permission`),
  ADD KEY `idx_permission_lookup` (`admin_id`,`permission`);

--
-- Indexes for table `bonus_awards`
--
ALTER TABLE `bonus_awards`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_type` (`bonus_type`),
  ADD KEY `idx_tier` (`bonus_tier_id`),
  ADD KEY `idx_awarded_by` (`awarded_by`),
  ADD KEY `idx_created` (`created_at`);

--
-- Indexes for table `chat_foreigners_access`
--
ALTER TABLE `chat_foreigners_access`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `chat_foreigners_payments`
--
ALTER TABLE `chat_foreigners_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `reference` (`reference`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_checkout` (`checkout_request_id`);

--
-- Indexes for table `commission_levels`
--
ALTER TABLE `commission_levels`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `country_level` (`country_id`,`level`),
  ADD KEY `idx_country` (`country_id`),
  ADD KEY `idx_level` (`level`);

--
-- Indexes for table `countries`
--
ALTER TABLE `countries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Indexes for table `digital_products`
--
ALTER TABLE `digital_products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_featured` (`featured`),
  ADD KEY `idx_delivery_type` (`delivery_type`);

--
-- Indexes for table `product_commissions`
--
ALTER TABLE `product_commissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_purchase` (`purchase_id`),
  ADD KEY `idx_recipient` (`recipient_id`),
  ADD KEY `idx_buyer` (`buyer_id`);

--
-- Indexes for table `product_commission_splits`
--
ALTER TABLE `product_commission_splits`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_country` (`country_id`),
  ADD KEY `idx_country_id` (`country_id`);

--
-- Indexes for table `product_purchases`
--
ALTER TABLE `product_purchases`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `one_per_user` (`user_id`,`product_id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_token` (`download_token`);

--
-- Indexes for table `referral_bonus_tiers`
--
ALTER TABLE `referral_bonus_tiers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_country` (`country_id`),
  ADD KEY `idx_referral_count` (`referral_count`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);

--
-- Indexes for table `site_settings`
--
ALTER TABLE `site_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key_unique` (`setting_key`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_task_type` (`task_type`),
  ADD KEY `idx_content_type` (`content_type`),
  ADD KEY `idx_show_sidebar` (`show_in_sidebar`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_composite_active` (`status`,`show_in_sidebar`,`content_type`),
  ADD KEY `idx_task_image` (`task_image`);

--
-- Indexes for table `task_audit_log`
--
ALTER TABLE `task_audit_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_task_id` (`task_id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_created_at` (`created_at`);

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

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

--
-- Indexes for table `task_completions`
--
ALTER TABLE `task_completions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_task_id` (`task_id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_composite_user_task` (`user_id`,`task_id`,`created_at`);

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

--
-- Indexes for table `task_quizzes`
--
ALTER TABLE `task_quizzes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_task_id` (`task_id`),
  ADD KEY `idx_order` (`order_number`);

--
-- Indexes for table `task_settings`
--
ALTER TABLE `task_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `task_type` (`task_type`);

--
-- Indexes for table `task_submissions`
--
ALTER TABLE `task_submissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_completion_id` (`task_completion_id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_task_id` (`task_id`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `task_survey_questions`
--
ALTER TABLE `task_survey_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_task_id` (`task_id`),
  ADD KEY `idx_order` (`order_number`);

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

--
-- Indexes for table `task_wallets`
--
ALTER TABLE `task_wallets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_task` (`user_id`,`task_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `task_id` (`task_id`);

--
-- Indexes for table `task_wallet_transactions`
--
ALTER TABLE `task_wallet_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_task` (`user_id`,`task_id`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_reference` (`reference`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `task_id` (`task_id`);

--
-- Indexes for table `timetable`
--
ALTER TABLE `timetable`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_day_activity` (`day_of_week`,`activity_type`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`,`created_at`),
  ADD KEY `idx_user_wallet` (`user_id`,`wallet_type`),
  ADD KEY `idx_reference` (`reference`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_transactions_user_created` (`user_id`,`created_at`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `phone` (`phone`),
  ADD UNIQUE KEY `referral_code` (`referral_code`),
  ADD KEY `idx_referral_code` (`referral_code`),
  ADD KEY `idx_referred_by` (`referred_by`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_users_country` (`country`),
  ADD KEY `idx_users_status_created` (`status`,`created_at`),
  ADD KEY `idx_user_active_created` (`status`,`created_at`);

--
-- Indexes for table `user_bonus_claims`
--
ALTER TABLE `user_bonus_claims`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_tier` (`user_id`,`bonus_tier_id`),
  ADD UNIQUE KEY `user_tier_week` (`user_id`,`bonus_tier_id`,`week_key`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_tier` (`bonus_tier_id`);

--
-- Indexes for table `wallets`
--
ALTER TABLE `wallets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_balance` (`balance`);

--
-- Indexes for table `wallet_recharges`
--
ALTER TABLE `wallet_recharges`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_ref` (`checkout_request_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `wallet_task`
--
ALTER TABLE `wallet_task`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user` (`user_id`);

--
-- Indexes for table `wallet_withdrawal_settings`
--
ALTER TABLE `wallet_withdrawal_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `wallet_type` (`wallet_type`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_wallet_type` (`wallet_type`);

--
-- Indexes for table `withdrawals`
--
ALTER TABLE `withdrawals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `processed_by` (`processed_by`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_withdrawals_status_requested` (`status`,`requested_at`),
  ADD KEY `idx_country` (`country`),
  ADD KEY `idx_user_status` (`user_id`,`status`);

--
-- Indexes for table `withdrawal_fee_tiers`
--
ALTER TABLE `withdrawal_fee_tiers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_id` (`country_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `product_commissions`
--
ALTER TABLE `product_commissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `product_commission_splits`
--
ALTER TABLE `product_commission_splits`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `wallet_recharges`
--
ALTER TABLE `wallet_recharges`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

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

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

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

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

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
