-- ============================================================
--  Profit Shop App — Complete MySQL Database Schema
--  Version: 1.0.0 (Step 1)
--  Engine: InnoDB | Charset: utf8mb4
--  Import: phpMyAdmin > Import > database.sql  (no CREATE DATABASE
--          needed for shared hosting; create DB first then import)
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1. ADMINS (admin panel login)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin','manager','support') NOT NULL DEFAULT 'super_admin',
  `status` ENUM('active','banned') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admins_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. APP SETTINGS (key-value, editable from admin panel)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `skey` VARCHAR(100) NOT NULL,
  `svalue` TEXT NULL,
  `description` VARCHAR(255) NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_settings_key` (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. PAYMENT METHODS (bKash / Nagad / Rocket / Bank / Auto gateway)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `method_key` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'bkash, nagad, rocket, upay — stable API/admin key',
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('manual','auto') NOT NULL DEFAULT 'manual',
  `account_type` ENUM('Personal','Agent','Merchant') NOT NULL DEFAULT 'Personal',
  `account_number` VARCHAR(100) NULL,
  `account_name` VARCHAR(100) NULL,
  `instructions` TEXT NULL,
  `qr_image_url` VARCHAR(255) NULL,
  `logo` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `min_deposit` DECIMAL(10,2) NOT NULL DEFAULT 50.00 COMMENT 'Floor enforced by deposits/create',
  `max_deposit` DECIMAL(10,2) NOT NULL DEFAULT 25000.00 COMMENT 'Ceiling enforced by deposits/create',
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_method_key` (`method_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. VERIFICATION PACKAGES (e.g. Gold Package)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `verification_packages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `fee` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `duration_days` INT NOT NULL DEFAULT 365,
  `features` TEXT NULL COMMENT 'JSON: list of unlocked features',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. MEMBERSHIP PLANS (v2 tiers: Free/Bronze/Silver/Gold/Platinum)
--    Defined BEFORE users so the users.package_id FK always resolves,
--    even on hosts that validate references at CREATE TABLE time.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `membership_plans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `daily_task_limit` INT NOT NULL DEFAULT 2,
  `mlm_levels_unlocked` INT NOT NULL DEFAULT 1,
  `direct_commission_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Level-1 rate (display)',
  `cashback_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `min_withdraw` DECIMAL(12,2) NOT NULL DEFAULT 200.00,
  `validity_days` INT NOT NULL DEFAULT 0 COMMENT '0 = lifetime access',
  `description` TEXT NULL COMMENT 'Short plan pitch shown in the app',
  `badge_color` VARCHAR(20) NOT NULL DEFAULT '#9E9E9E',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_plan_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `membership_level_rates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `plan_id` INT UNSIGNED NOT NULL,
  `level` INT UNSIGNED NOT NULL COMMENT '1..10',
  `commission_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_plan_level` (`plan_id`,`level`),
  CONSTRAINT `fk_mlr_plan` FOREIGN KEY (`plan_id`)
    REFERENCES `membership_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NOTE: membership_payments is defined with the transaction tables
-- below (it references users, so it must come after the users table).

-- ------------------------------------------------------------
-- 6. USERS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) NOT NULL COMMENT 'Primary login identity',
  `email` VARCHAR(150) NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(255) NULL,
  `referral_code` VARCHAR(20) NOT NULL COMMENT 'Own code to invite others',
  `referred_by` INT UNSIGNED NULL COMMENT 'FK -> users.id (upline)',
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_package_id` INT UNSIGNED NULL,
  `package_id` INT UNSIGNED NULL COMMENT 'FK -> membership_plans.id (v2 tiers)',
  `verified_at` DATETIME NULL,
  `membership_expiry` DATETIME NULL COMMENT 'NULL = lifetime; set from plan validity_days on upgrade',
  `status` ENUM('active','banned','pending','deleted') NOT NULL DEFAULT 'pending',
  `operator` VARCHAR(50) NULL COMMENT 'Auto-detected or manual mobile operator',
  `device_id` VARCHAR(64) NULL COMMENT 'Anti-fraud: bound Android SSAID (one device, one account)',
  `fcm_token` VARCHAR(512) NULL COMMENT 'Firebase Cloud Messaging registration token',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_phone` (`phone`),
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_referral_code` (`referral_code`),
  UNIQUE KEY `uq_users_device_id` (`device_id`),
  KEY `idx_users_referred_by` (`referred_by`),
  KEY `idx_users_status` (`status`),
  KEY `idx_users_verified` (`is_verified`),
  CONSTRAINT `fk_users_referred_by` FOREIGN KEY (`referred_by`)
    REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_users_ver_package` FOREIGN KEY (`verification_package_id`)
    REFERENCES `verification_packages` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_package` FOREIGN KEY (`package_id`)
    REFERENCES `membership_plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. OTPS (registration / login / password reset)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `otps` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone` VARCHAR(20) NULL,
  `email` VARCHAR(150) NULL,
  `otp_code` VARCHAR(10) NOT NULL,
  `purpose` ENUM('register','login','reset','verify_payment') NOT NULL DEFAULT 'register',
  `is_used` TINYINT(1) NOT NULL DEFAULT 0,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_otps_phone` (`phone`),
  KEY `idx_otps_email` (`email`),
  KEY `idx_otps_code` (`otp_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. VERIFICATION PAYMENTS (profile verification fee)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `verification_payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `package_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method_id` INT UNSIGNED NULL,
  `txn_id` VARCHAR(100) NULL,
  `screenshot` VARCHAR(255) NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vp_user` (`user_id`),
  KEY `idx_vp_status` (`status`),
  CONSTRAINT `fk_vp_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vp_package` FOREIGN KEY (`package_id`)
    REFERENCES `verification_packages` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. WALLETS (one row per user)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallets` (
  `user_id` INT UNSIGNED NOT NULL,
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_earned` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_withdrawn` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_wallets_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. WALLET TRANSACTIONS (immutable ledger)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `type` ENUM('credit','debit') NOT NULL,
  `source` VARCHAR(50) NOT NULL COMMENT 'task, referral, cashback, recharge, purchase, deposit, withdraw, bonus, verification_fee, adjustment',
  `amount` DECIMAL(12,2) NOT NULL,
  `balance_after` DECIMAL(12,2) NOT NULL,
  `reference_id` VARCHAR(100) NULL,
  `note` VARCHAR(255) NULL,
  `status` ENUM('pending','completed','failed') NOT NULL DEFAULT 'completed',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wt_user` (`user_id`),
  KEY `idx_wt_source` (`source`),
  KEY `idx_wt_created` (`created_at`),
  CONSTRAINT `fk_wt_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. DEPOSITS (add fund requests)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `deposits` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `payment_method_id` INT UNSIGNED NULL,
  `method` VARCHAR(50) NULL COMMENT 'Denormalized method key (bkash/nagad/...) for direct display',
  `amount` DECIMAL(12,2) NOT NULL,
  `txn_id` VARCHAR(100) NULL COMMENT 'Legacy id (mirrors trx_id; nullable so inserts never fail)',
  `trx_id` VARCHAR(100) NULL COMMENT 'Canonical transaction id (mirrors txn_id)',
  `screenshot` VARCHAR(255) NULL,
  `screenshot_url` VARCHAR(500) NULL COMMENT 'Mirror of screenshot (extended URL length)',
  `sender_account` VARCHAR(50) NULL COMMENT 'Number the user paid from',
  `sender_number` VARCHAR(50) NULL COMMENT 'Mirror of sender_account',
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dep_txn` (`txn_id`),
  UNIQUE KEY `uq_dep_trx` (`trx_id`),
  KEY `idx_dep_user` (`user_id`),
  KEY `idx_dep_status` (`status`),
  KEY `idx_dep_user_status` (`user_id`,`status`),
  CONSTRAINT `fk_dep_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. WITHDRAWALS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `method` VARCHAR(50) NOT NULL COMMENT 'bkash, nagad, rocket, bank',
  `account_number` VARCHAR(50) NOT NULL,
  `account_type` VARCHAR(20) NULL COMMENT 'Personal / Agent (user-declared)',
  `amount` DECIMAL(12,2) NOT NULL,
  `charge` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `net_amount` DECIMAL(12,2) NOT NULL,
  `payout_trx_id` VARCHAR(100) NULL COMMENT 'Admin payout transaction id (set on approve)',
  `status` ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wd_user` (`user_id`),
  KEY `idx_wd_status` (`status`),
  CONSTRAINT `fk_wd_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. MLM LEVELS (commission % per level, editable in admin)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mlm_levels` (
  `level` INT UNSIGNED NOT NULL COMMENT '1..20',
  `name` VARCHAR(50) NULL COMMENT 'Display label, e.g. Level 1',
  `commission_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `commission_fixed` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Used if percent is 0',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13. REFERRAL EARNINGS (team income ledger)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `referral_earnings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `earner_user_id` INT UNSIGNED NOT NULL COMMENT 'Who receives commission',
  `from_user_id` INT UNSIGNED NOT NULL COMMENT 'Whose action triggered it',
  `level` INT UNSIGNED NOT NULL,
  `source_type` VARCHAR(50) NOT NULL COMMENT 'verification_fee, task, purchase, deposit',
  `source_id` INT UNSIGNED NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `status` ENUM('pending','paid') NOT NULL DEFAULT 'paid',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_re_earner` (`earner_user_id`),
  KEY `idx_re_from` (`from_user_id`),
  KEY `idx_re_level` (`level`),
  CONSTRAINT `fk_re_earner` FOREIGN KEY (`earner_user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_re_from` FOREIGN KEY (`from_user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13b. REFERRAL COMMISSIONS (standardized MLM ledger mirror)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `referral_commissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `beneficiary_user_id` INT UNSIGNED NOT NULL COMMENT 'Upline earner (mirrors referral_earnings.earner_user_id)',
  `source_user_id` INT UNSIGNED NOT NULL COMMENT 'Downline trigger (mirrors from_user_id)',
  `level` INT UNSIGNED NOT NULL,
  `source_event` VARCHAR(50) NOT NULL COMMENT 'task, plan_upgrade, deposit, ...',
  `source_amount` DECIMAL(10,2) NULL COMMENT 'Base amount the commission was calculated from',
  `commission_amount` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rc_beneficiary` (`beneficiary_user_id`),
  KEY `idx_rc_source` (`source_user_id`),
  KEY `idx_rc_level` (`level`),
  CONSTRAINT `fk_rc_beneficiary` FOREIGN KEY (`beneficiary_user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rc_source` FOREIGN KEY (`source_user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 14. TASK CATEGORIES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `task_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL COMMENT 'ads, quiz, typing, marketing, microjob, jobpost, course, digital_service, gmail, instagram, whatsapp, telegram',
  `icon` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_taskcat_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 15. TASKS (all earning modules, dynamic via admin)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `reward` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `instructions` TEXT NULL COMMENT 'Step-by-step guide shown in the app',
  `action_url` VARCHAR(500) NULL COMMENT 'Ad link / social link / course link (YouTube, website, Telegram...)',
  `image` VARCHAR(255) NULL,
  `task_type` ENUM('visit_timer','watch_ad','quiz','screenshot','social') NOT NULL DEFAULT 'visit_timer' COMMENT 'Guided completion method driving the app UI',
  `timer_seconds` INT NOT NULL DEFAULT 20 COMMENT 'Countdown requirement for visit_timer tasks',
  `required_plan_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = all users; else minimum membership_plans.id required',
  `required_proof` ENUM('none','screenshot','text','both') NOT NULL DEFAULT 'screenshot',
  `daily_limit` INT NOT NULL DEFAULT 1 COMMENT 'Max completions per user per day',
  `total_limit` INT NOT NULL DEFAULT 0 COMMENT '0 = unlimited overall',
  `requires_verification` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = only verified users',
  `start_date` DATE NULL,
  `end_date` DATE NULL,
  `status` ENUM('active','paused','expired') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tasks_cat` (`category_id`),
  KEY `idx_tasks_status` (`status`),
  CONSTRAINT `fk_tasks_cat` FOREIGN KEY (`category_id`)
    REFERENCES `task_categories` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 16. QUIZ QUESTIONS (for quiz-type tasks).
--     Canonical store: JSON `options` (4 entries = A..D) + zero-based
--     `correct_index`. The admin builder + API accept A/B/C/D keys and
--     map them onto this shape, so one table serves every client.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `quiz_questions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id` INT UNSIGNED NOT NULL,
  `question` VARCHAR(500) NOT NULL,
  `options` TEXT NOT NULL COMMENT 'JSON array of 4 options (A, B, C, D)',
  `correct_index` TINYINT NOT NULL DEFAULT 0 COMMENT '0=A, 1=B, 2=C, 3=D',
  PRIMARY KEY (`id`),
  KEY `idx_qq_task` (`task_id`),
  CONSTRAINT `fk_qq_task` FOREIGN KEY (`task_id`)
    REFERENCES `tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 17. TASK SUBMISSIONS (user proof + review)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `task_submissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `task_id` INT UNSIGNED NOT NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `proof_text` TEXT NULL,
  `proof_image` VARCHAR(255) NULL,
  `elapsed_seconds` INT UNSIGNED NULL COMMENT 'Visit-timer audit: seconds the user spent before submitting',
  `reward_earned` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `admin_note` VARCHAR(255) NULL,
  `rejection_reason` VARCHAR(255) NULL,
  `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ts_user` (`user_id`),
  KEY `idx_ts_task` (`task_id`),
  KEY `idx_ts_status` (`status`),
  CONSTRAINT `fk_ts_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ts_task` FOREIGN KEY (`task_id`)
    REFERENCES `tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18. QUICK EARN — INTERACTIVE QUIZZES + CAPTCHA TYPING
--      Standalone engines (Home > Easy Earning tiles). Separate from
--      the guided task quizzes in `tasks` / `quiz_questions`.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `quizzes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `question` TEXT NOT NULL,
  `option_a` VARCHAR(255) NOT NULL,
  `option_b` VARCHAR(255) NOT NULL,
  `option_c` VARCHAR(255) NOT NULL,
  `option_d` VARCHAR(255) NOT NULL,
  `correct_option` ENUM('a','b','c','d') NOT NULL,
  `reward_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.50,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_quizzes_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_quiz_attempts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `quiz_id` INT UNSIGNED NOT NULL,
  `selected_option` ENUM('a','b','c','d') NOT NULL,
  `is_correct` TINYINT(1) NOT NULL DEFAULT 0,
  `earned_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_uqa_user_date` (`user_id`,`created_at`),
  KEY `idx_uqa_quiz` (`quiz_id`),
  CONSTRAINT `fk_uqa_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uqa_quiz` FOREIGN KEY (`quiz_id`)
    REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `typing_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `captcha_code` VARCHAR(50) NOT NULL,
  `reward_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.30,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_typing_user_date` (`user_id`,`created_at`),
  CONSTRAINT `fk_typing_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Internal challenge store: keeps the issued captcha server-side so a
-- stateless JWT API can verify it, enforce expiry/retry limits and the
-- 4-second human delay. Never exposed to clients beyond the token.
CREATE TABLE IF NOT EXISTS `typing_challenges` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `captcha_code` VARCHAR(50) NOT NULL,
  `reward_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.30,
  `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `used` TINYINT(1) NOT NULL DEFAULT 0,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_typing_token` (`token`),
  KEY `idx_typing_challenge_user` (`user_id`,`used`,`created_at`),
  CONSTRAINT `fk_typing_challenge_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18c. REWARDED VIDEO AD VIEWS (Phase 3 earning engine)
-- One row per completed rewarded video. The API locks the user row,
-- re-counts today's views and re-checks the cooldown before inserting
-- here, so parallel claims cannot exceed the daily quota.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ad_views` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `ad_network` VARCHAR(50) NOT NULL DEFAULT 'admob' COMMENT 'admob | startio | unity',
  `reward_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.25,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ad_views_user_created` (`user_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18d. WEEKLY LEADERBOARD PRIZE HISTORY (Phase 3 payouts)
-- The unique key is the final duplicate-payout guard: even if two
-- admins/cron runs race, only one row can exist per week + rank.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `leaderboard_prize_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `period_type` ENUM('weekly','monthly') NOT NULL DEFAULT 'weekly',
  `rank_position` INT NOT NULL COMMENT '1-based board rank',
  `prize_amount` DECIMAL(10,2) NOT NULL,
  `period_label` VARCHAR(50) NOT NULL COMMENT 'ISO week label, e.g. 2026-W42',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_lph_user` (`user_id`),
  KEY `idx_lph_period` (`period_label`),
  UNIQUE KEY `uq_lph_period_rank` (`period_label`,`period_type`,`rank_position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 19. OPERATORS + DRIVE OFFERS (recharge)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `operators` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(50) NOT NULL COMMENT 'grameenphone, banglalink, robi, teletalk',
  `prefixes` VARCHAR(255) NULL COMMENT 'CSV: 017,013,... for auto-detect',
  `logo` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_op_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `drive_offers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `operator_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `offer_type` ENUM('airtime','data','minute','combo') NOT NULL DEFAULT 'data',
  `price` DECIMAL(12,2) NOT NULL COMMENT 'User pays',
  `mrp` DECIMAL(12,2) NULL COMMENT 'Regular price for strike-through UI',
  `cashback` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `validity` VARCHAR(50) NULL COMMENT 'e.g. 30 days',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_do_op` (`operator_id`),
  CONSTRAINT `fk_do_op` FOREIGN KEY (`operator_id`)
    REFERENCES `operators` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `recharge_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `phone_number` VARCHAR(20) NOT NULL,
  `operator_id` INT UNSIGNED NULL,
  `offer_id` INT UNSIGNED NULL COMMENT 'NULL = custom airtime amount',
  `amount_paid` DECIMAL(12,2) NOT NULL,
  `status` ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
  `txn_id` VARCHAR(100) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ro_user` (`user_id`),
  KEY `idx_ro_status` (`status`),
  CONSTRAINT `fk_ro_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 20. SHOP: CATEGORIES, PRODUCTS, ORDERS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `image` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pcat_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vendor_id` INT UNSIGNED NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `title` VARCHAR(200) NULL COMMENT 'Alias of name for vendor API compat (mirrors name)',
  `description` TEXT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `mrp` DECIMAL(12,2) NULL,
  `cashback` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Buyer earning',
  `stock` INT NOT NULL DEFAULT 10,
  `images` TEXT NULL COMMENT 'JSON array of image URLs',
  `image_url` VARCHAR(500) NULL COMMENT 'Primary image URL for vendor API compat (mirrors images[0])',
  `is_popular` TINYINT(1) NOT NULL DEFAULT 0,
  `product_type` ENUM('physical','virtual') NOT NULL DEFAULT 'physical' COMMENT 'physical=hand delivery, virtual=gift card/digital code',
  `delivery_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '0 for virtual products',
  `status` ENUM('active','inactive','out_of_stock') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_prod_cat` (`category_id`),
  KEY `idx_prod_popular` (`is_popular`),
  KEY `idx_prod_status` (`status`),
  KEY `idx_prod_vendor` (`vendor_id`),
  CONSTRAINT `fk_prod_cat` FOREIGN KEY (`category_id`)
    REFERENCES `product_categories` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `order_code` VARCHAR(30) NOT NULL,
  `total_amount` DECIMAL(12,2) NOT NULL,
  `cashback_earned` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('pending','processing','confirmed','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',
  `payment_method` VARCHAR(50) NOT NULL DEFAULT 'wallet',
  `address` VARCHAR(500) NULL,
  `phone` VARCHAR(20) NULL,
  `delivery_info` VARCHAR(500) NULL COMMENT 'address (physical) or email/whatsapp (virtual)',
  `delivery_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'delivery charges debited at order time',
  `courier_name` VARCHAR(100) NULL COMMENT 'Fulfillment courier (Steadfast, Pathao, RedX, ...)',
  `tracking_code` VARCHAR(150) NULL COMMENT 'Consignment / tracking number given to the customer',
  `fulfillment_note` TEXT NULL COMMENT 'Digital code (virtual) or courier remarks, visible to customer',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_orders_code` (`order_code`),
  KEY `idx_orders_user` (`user_id`),
  KEY `idx_orders_status` (`status`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `vendor_id` INT UNSIGNED NULL COMMENT 'NULL = platform stock; vendor rows reference vendors.id (strict per-vendor fulfillment)',
  `product_id` INT UNSIGNED NOT NULL,
  `qty` INT NOT NULL DEFAULT 1,
  `quantity` INT NULL COMMENT 'Mirror of qty (vendor order API canonical)',
  `price` DECIMAL(12,2) NOT NULL,
  `unit_price` DECIMAL(10,2) NULL COMMENT 'Mirror of price (vendor order API canonical)',
  `cashback` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `delivery_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Line delivery total (unit delivery x qty; 0 for virtual)',
  `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'qty x unit_price + delivery_charge (vendor payout base)',
  `status` ENUM('pending','processing','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending' COMMENT 'Per-item vendor fulfillment state',
  `customer_name` VARCHAR(255) NULL,
  `customer_phone` VARCHAR(50) NULL,
  `shipping_address` TEXT NULL,
  `fulfillment_note` TEXT NULL COMMENT 'Tracking number (physical) or digital code (virtual)',
  `is_payout_credited` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = vendor wallet already credited for this line',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_oi_order` (`order_id`),
  KEY `idx_oi_vendor` (`vendor_id`),
  KEY `idx_oi_status` (`status`),
  CONSTRAINT `fk_oi_order` FOREIGN KEY (`order_id`)
    REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_oi_product` FOREIGN KEY (`product_id`)
    REFERENCES `products` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 21. VENDORS (apply + approval + fee)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vendors` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `shop_name` VARCHAR(150) NOT NULL,
  `shop_description` TEXT NULL COMMENT 'Vendor shop description (simplified fee-based flow)',
  `phone` VARCHAR(20) NULL COMMENT 'Vendor contact phone (simplified flow)',
  `logo_url` VARCHAR(500) NULL COMMENT 'Vendor logo/image URL (optional)',
  `address` VARCHAR(500) NULL COMMENT 'Legacy KYC shop address (nullable for simplified flow)',
  `district` VARCHAR(100) NULL COMMENT 'Vendor district / store location',
  `trade_license_image` VARCHAR(500) NULL COMMENT 'KYC trade license (file path, URL or base64-stored path)',
  `nid_front` VARCHAR(500) NULL COMMENT 'KYC NID front (file path, URL or base64-stored path)',
  `nid_back` VARCHAR(500) NULL COMMENT 'KYC NID back (file path, URL or base64-stored path)',
  `application_fee` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Legacy column; mirrors fee_paid',
  `fee_paid` DECIMAL(10,2) NOT NULL DEFAULT 500.00 COMMENT 'Fee-based application amount (mirrors application_fee)',
  `payment_txn_id` VARCHAR(100) NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL COMMENT 'Legacy admin note; mirrors rejection_reason when rejected',
  `rejection_reason` TEXT NULL COMMENT 'Rejection reason (mirrors admin_note when rejected)',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vendors_user` (`user_id`),
  KEY `idx_vendors_user` (`user_id`),
  KEY `idx_vendors_status` (`status`),
  CONSTRAINT `fk_vendors_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add FK from products.vendor_id now that vendors exists
ALTER TABLE `products`
  ADD CONSTRAINT `fk_prod_vendor` FOREIGN KEY (`vendor_id`)
  REFERENCES `vendors` (`id`) ON DELETE SET NULL;

-- ------------------------------------------------------------
-- 22. AGENCIES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `agencies` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner_user_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT NULL,
  `logo` VARCHAR(255) NULL,
  `cover` VARCHAR(255) NULL,
  `followers_required` INT NOT NULL DEFAULT 10000,
  `status` ENUM('pending','approved','suspended','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL COMMENT 'Reject/suspend reason shown to the owner',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ag_owner` (`owner_user_id`),
  KEY `idx_ag_status` (`status`),
  CONSTRAINT `fk_ag_owner` FOREIGN KEY (`owner_user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `agency_posts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL COMMENT 'Author (agency owner/admin)',
  `title` VARCHAR(200) NULL COMMENT 'Campaign title (new flow; legacy rows use content)',
  `description` TEXT NULL COMMENT 'Campaign description',
  `action_url` VARCHAR(500) NULL COMMENT 'Campaign target URL',
  `content` TEXT NOT NULL,
  `image` VARCHAR(255) NULL,
  `image_url` VARCHAR(500) NULL COMMENT 'Direct banner link (new campaign flow)',
  `likes_count` INT NOT NULL DEFAULT 0,
  `total_budget` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Wallet-funded budget',
  `reward_per_action` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `spent_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Paid out to completers',
  `timer_seconds` INT NOT NULL DEFAULT 20 COMMENT 'Required visit length for earners',
  `daily_limit` INT NOT NULL DEFAULT 0 COMMENT 'Traffic pacing: max completions per day (0 = unlimited)',
  `frequency_cap` ENUM('once','daily') NOT NULL DEFAULT 'once' COMMENT 'once = 1x per user lifetime, daily = 1x per 24h',
  `status` ENUM('active','paused','completed','cancelled') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ap_agency` (`agency_id`),
  CONSTRAINT `fk_ap_agency` FOREIGN KEY (`agency_id`)
    REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ap_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `agency_follows` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_agfollow` (`agency_id`,`user_id`),
  CONSTRAINT `fk_af_agency` FOREIGN KEY (`agency_id`)
    REFERENCES `agencies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_af_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `agency_campaign_completions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaign_id` INT UNSIGNED NOT NULL COMMENT 'FK -> agency_posts.id',
  `user_id` INT UNSIGNED NOT NULL,
  `reward` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_campaign_user_created` (`campaign_id`,`user_id`,`created_at`),
  CONSTRAINT `fk_acc_campaign` FOREIGN KEY (`campaign_id`)
    REFERENCES `agency_posts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_acc_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 22b. COMMUNITY MICRO JOBS (user-to-user marketplace)
--      `campaigns.user_id` is the employer/poster (users.id).
--      Budget is escrowed from the employer's wallet on create.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NULL COMMENT 'Employer/poster (users.id); NULL for admin posts',
  `is_admin_post` TINYINT(1) NOT NULL DEFAULT 0,
  `title` VARCHAR(255) NOT NULL,
  `work_link` VARCHAR(500) NULL,
  `work_limit` INT NOT NULL DEFAULT 0 COMMENT 'Total workers required',
  `completed_workers` INT NOT NULL DEFAULT 0,
  `per_work_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Reward per completed work',
  `total_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Escrowed/admin-funded total',
  `image_url` VARCHAR(255) NULL COMMENT 'Job thumbnail/banner',
  `description` TEXT NOT NULL,
  `status` ENUM('active','paused','completed','cancelled') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- Legacy columns kept in sync for the existing proof/review flow.
  `category` VARCHAR(80) NULL COMMENT 'Legacy UI category',
  `proof_instruction` TEXT NOT NULL COMMENT 'Legacy worker proof instructions',
  `proof_type` ENUM('screenshot','text','both') NOT NULL DEFAULT 'both',
  `reward_per_worker` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Legacy mirror of per_work_cost',
  `total_workers` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Legacy mirror of work_limit',
  `total_budget` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Legacy mirror of total_cost',
  `spent_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Approved worker payouts',
  PRIMARY KEY (`id`),
  KEY `idx_campaigns_user` (`user_id`),
  KEY `idx_campaigns_admin` (`is_admin_post`),
  KEY `idx_campaigns_status` (`status`),
  KEY `idx_campaigns_category` (`category`),
  CONSTRAINT `fk_campaigns_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `campaign_submissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaign_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL COMMENT 'Worker (users.id)',
  `proof_text` TEXT NULL,
  `proof_image` VARCHAR(255) NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `rejection_reason` VARCHAR(255) NULL,
  `reward` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `reviewed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_campaign_worker` (`campaign_id`,`user_id`),
  KEY `idx_cs_worker` (`user_id`),
  KEY `idx_cs_status` (`status`),
  CONSTRAINT `fk_cs_campaign` FOREIGN KEY (`campaign_id`)
    REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cs_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 23. BANNERS / SLIDERS + NOTIFICATIONS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `banners` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(150) NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  `link_type` VARCHAR(50) NULL COMMENT 'task, product, offer, url, none',
  `link_value` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NULL COMMENT 'NULL means broadcast to all users, specific INT means private notification',
  `title` VARCHAR(150) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('general','deposit','withdraw','bonus','system') NOT NULL DEFAULT 'general',
  `action_url` VARCHAR(255) NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user_created` (`user_id`,`created_at`),
  KEY `idx_notif_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-user read receipts for broadcast notifications. Broadcast rows keep
-- is_read = 0 forever; only this table records who has opened them.
CREATE TABLE IF NOT EXISTS `user_notification_reads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `notification_id` INT UNSIGNED NOT NULL,
  `read_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_notif_unique` (`user_id`,`notification_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 24. MEMBERSHIP PAYMENTS (upgrade ledger; plans tables are defined
--     up in section 5, before users, so all FKs resolve in order)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `membership_payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `plan_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `payment_method_id` INT UNSIGNED NULL,
  `txn_id` VARCHAR(100) NULL,
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mp_user` (`user_id`),
  KEY `idx_mp_status` (`status`),
  CONSTRAINT `fk_mp_user` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mp_plan` FOREIGN KEY (`plan_id`)
    REFERENCES `membership_plans` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK checks stay OFF through all seed INSERTs below and are
-- re-enabled at the very end of this file.

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default admin: email admin@modernearning.com / password admin123
INSERT INTO `admins` (`name`,`email`,`password_hash`,`role`,`status`) VALUES
('Super Admin','admin@modernearning.com','$2y$10$pcEKXMXCQd05I8bVKoIPs./KIgvYOwOUNe/P8u4LB.9d2R2nNXNDO','super_admin','active');

-- Core settings
INSERT INTO `app_settings` (`skey`,`svalue`,`description`) VALUES
('app_name','Profit Shop','App display name'),
('mlm_max_levels','10','Max referral levels to pay commission (1-20)'),
('verification_required','1','1 = unverified users have locked features'),
('min_withdraw','200','Minimum withdrawal amount (uniform, all tiers)'),
('withdraw_charge_percent','5','Withdrawal charge %'),
('vendor_application_fee','500','Fee to apply as vendor'),
('vendor_registration_fee','500','Vendor registration fee (৳) — fee-based application, mirrors vendor_application_fee'),
('agency_min_followers','10000','Followers required to create agency'),
('support_whatsapp','8801XXXXXXXXX','Support contact'),
('support_phone','8801XXXXXXXXX','Support hotline'),
('app_version','1.0.0','Current app version'),
('ads_active_network','disabled','Ad network: startio | unity | admob | disabled'),
('ads_startio_app_id','','Start.io App ID'),
('ads_unity_game_id','','Unity Game ID'),
('ads_admob_app_id','','AdMob App ID (ca-app-pub-…)'),
('ads_rewarded_ad_unit_id','','Rewarded ad unit ID (active network)'),
('ads_banner_ad_unit_id','','Banner ad unit ID (active network)'),
('ads_unity_test_mode','0','Unity test ads (1 = on)'),
('ads_unity_rewarded_placement','','Unity rewarded placement ID (blank = shared unit)'),
('ads_unity_banner_placement','','Unity banner placement ID (blank = shared unit)'),
('ads_admob_rewarded_unit','','AdMob rewarded ad unit ID (blank = shared unit)'),
('ads_admob_banner_unit','','AdMob banner ad unit ID (blank = shared unit)'),
('ads_banner_enabled','1','Master toggle for banners'),
('ads_rewarded_enabled','1','Master toggle for rewarded ads on tasks'),
('telegram_url','https://t.me/','Home community link: Telegram group'),
('support_video_url','https://meet.google.com/','Home community link: support video channel'),
('facebook_url','https://facebook.com/','Home community link: Facebook group/page'),
('youtube_url','https://youtube.com/','Home community link: YouTube channel'),
('quiz_daily_limit','15','Quiz engine: max quizzes a user can answer per day'),
('quiz_default_reward','0.50','Quiz engine: fallback reward per correct answer (৳)'),
('typing_daily_limit','20','Typing engine: max captchas a user can verify per day'),
('typing_default_reward','0.30','Typing engine: reward per verified captcha (৳)'),
('typing_case_sensitive','0','Typing engine: 1 = captcha match is case-sensitive, 0 = ignore case'),
('marquee_notice_text','🎉 Welcome to our platform! Complete daily tasks, solve quizzes, and invite friends to earn big rewards daily!','Home scrolling notice text (marquee)'),
('marquee_is_active','1','Home scrolling notice enabled (1 = on)'),
('marquee_speed','50','Home scrolling notice speed (px/second)'),
-- Phase 3: ad network aliases + weekly leaderboard prizes
('admob_rewarded_unit_id','ca-app-pub-3940256099942544/5224354917','AdMob rewarded test unit (replace with live unit)'),
('startio_app_id','','Start.io App ID for rewarded ads'),
('leaderboard_weekly_prize_pool','1000.00','Weekly leaderboard total prize pool (৳)'),
('leaderboard_rank1_prize','300.00','Weekly leaderboard rank 1 prize (৳)'),
('leaderboard_rank2_prize','200.00','Weekly leaderboard rank 2 prize (৳)'),
('leaderboard_rank3_prize','100.00','Weekly leaderboard rank 3 prize (৳)');

-- MLM: 10 levels default (admin can edit % later)
INSERT INTO `mlm_levels` (`level`,`name`,`commission_percent`,`commission_fixed`,`is_active`) VALUES
(1,'Level 1',10.00,0.00,1),
(2,'Level 2',5.00,0.00,1),
(3,'Level 3',2.00,0.00,1),
(4,'Level 4',1.00,0.00,1),
(5,'Level 5',0.50,0.00,1),
(6,'Level 6',1.00,0.00,1),
(7,'Level 7',1.00,0.00,1),
(8,'Level 8',1.00,0.00,1),
(9,'Level 9',0.50,0.00,1),
(10,'Level 10',0.50,0.00,1);

-- Payment methods (deposit gateways; numbers edited live in Admin > Settings)
INSERT INTO `payment_methods` (`method_key`,`name`,`type`,`account_type`,`account_number`,`account_name`,`instructions`,`qr_image_url`,`is_active`,`sort_order`) VALUES
('bkash','bKash','manual','Personal','01XXXXXXXXX','Profit Shop','Send Money korar por TrxID niche din.',NULL,1,1),
('nagad','Nagad','manual','Personal','01XXXXXXXXX','Profit Shop','Send Money korar por TrxID niche din.',NULL,1,2),
('rocket','Rocket','manual','Personal','01XXXXXXXXX','Profit Shop','Send Money korar por TrxID niche din.',NULL,1,3),
('upay','Upay','manual','Personal','01XXXXXXXXX','Profit Shop','Send Money korar por TrxID niche din.',NULL,0,4);

-- Verification package (legacy v1; v2 uses membership_plans)
INSERT INTO `verification_packages` (`name`,`fee`,`duration_days`,`features`,`is_active`) VALUES
('Gold Package',500.00,365,'["ads","quiz","typing","marketing","microjob","referral","recharge","shop_cashback"]',1);

-- Membership plans (v2 tiers, min withdraw 200 uniform)
INSERT INTO `membership_plans`
  (slug, name, price, daily_task_limit, mlm_levels_unlocked, direct_commission_pct, cashback_pct, min_withdraw, badge_color, is_active) VALUES
('free','Free',0,2,1,5,0,200,'#9E9E9E',1),
('bronze','Bronze',500,8,3,12,1,200,'#CD7F32',1),
('silver','Silver',1000,18,5,15,2,200,'#C0C0C0',1),
('gold','Gold',2000,30,8,18,3,200,'#FFD700',1),
('platinum','Platinum',5000,50,10,22,5,200,'#7C3AED',1);

-- Per-level commission rates per plan
INSERT INTO `membership_level_rates` (plan_id, level, commission_pct) VALUES
((SELECT id FROM membership_plans WHERE slug='free'),1,5),
((SELECT id FROM membership_plans WHERE slug='bronze'),1,12),
((SELECT id FROM membership_plans WHERE slug='bronze'),2,3),
((SELECT id FROM membership_plans WHERE slug='bronze'),3,1),
((SELECT id FROM membership_plans WHERE slug='silver'),1,15),
((SELECT id FROM membership_plans WHERE slug='silver'),2,5),
((SELECT id FROM membership_plans WHERE slug='silver'),3,3),
((SELECT id FROM membership_plans WHERE slug='silver'),4,2),
((SELECT id FROM membership_plans WHERE slug='silver'),5,1),
((SELECT id FROM membership_plans WHERE slug='gold'),1,18),
((SELECT id FROM membership_plans WHERE slug='gold'),2,6),
((SELECT id FROM membership_plans WHERE slug='gold'),3,4),
((SELECT id FROM membership_plans WHERE slug='gold'),4,2),
((SELECT id FROM membership_plans WHERE slug='gold'),5,1),
((SELECT id FROM membership_plans WHERE slug='gold'),6,1),
((SELECT id FROM membership_plans WHERE slug='gold'),7,0.5),
((SELECT id FROM membership_plans WHERE slug='gold'),8,0.5),
((SELECT id FROM membership_plans WHERE slug='platinum'),1,22),
((SELECT id FROM membership_plans WHERE slug='platinum'),2,8),
((SELECT id FROM membership_plans WHERE slug='platinum'),3,5),
((SELECT id FROM membership_plans WHERE slug='platinum'),4,3),
((SELECT id FROM membership_plans WHERE slug='platinum'),5,2),
((SELECT id FROM membership_plans WHERE slug='platinum'),6,1),
((SELECT id FROM membership_plans WHERE slug='platinum'),7,1),
((SELECT id FROM membership_plans WHERE slug='platinum'),8,0.5),
((SELECT id FROM membership_plans WHERE slug='platinum'),9,0.5),
((SELECT id FROM membership_plans WHERE slug='platinum'),10,0.5);

-- Task categories (Easy + Special)
INSERT INTO `task_categories` (`name`,`slug`,`sort_order`) VALUES
('Ads Watching','ads',1),
('Quiz','quiz',2),
('Typing Jobs','typing',3),
('Marketing Tasks','marketing',4),
('Micro Jobs','microjob',5),
('Job Posts','jobpost',6),
('Courses','course',7),
('Digital Services','digital_service',8),
('Gmail Tasks','gmail',9),
('Instagram Tasks','instagram',10),
('WhatsApp Tasks','whatsapp',11),
('Telegram Tasks','telegram',12);

-- Full task catalog: 12 categories x 3 tasks (rewards 2-25).
-- Column mapping: proof_required -> required_proof, url -> action_url,
-- verified_only -> requires_verification.
INSERT INTO `task_categories` (`name`,`slug`,`icon`,`sort_order`) VALUES
('Video Watching','video-watching','🎬',13),
('Website Browsing','website-browsing','🌐',14),
('Facebook & Social','social-tasks','👍',15),
('Telegram Community','telegram-tasks','✈️',16),
('App Install & Review','app-reviews','⭐',17),
('Surveys & Polls','survey-polls','📝',18),
('General Knowledge Quiz','quiz-challenge','🧠',19),
('Captcha & Data Entry','captcha-tasks','⌨️',20),
('YouTube Engagement','youtube-tasks','▶️',21),
('Shortlink & Ad View','ad-view','📢',22),
('Sign-Up & Register','signup-tasks','📝',23),
('Special VIP Tasks','vip-tasks','👑',24);

INSERT INTO `tasks`
  (`category_id`,`title`,`description`,`reward`,`instructions`,`action_url`,`image`,
   `required_proof`,`daily_limit`,`total_limit`,`requires_verification`,`status`) VALUES
((SELECT id FROM task_categories WHERE slug='video-watching'),'Watch Tech Review (2 Min)','Watch a full 2-minute tech review video without skipping.',4,'Open the video link, watch it fully without skipping, then submit.','https://example.com/video/tech-review-2min','https://picsum.photos/seed/cat2vid1/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='video-watching'),'Movie Trailer Watch','Watch the latest movie trailer till the end.',3,'Watch the trailer completely, then submit.','https://example.com/video/trailer','https://picsum.photos/seed/cat2vid2/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='video-watching'),'Watch Cooking Show','Watch a 5-minute cooking show episode.',5,'Watch the episode fully, then submit.','https://example.com/video/cooking','https://picsum.photos/seed/cat2vid3/600/400','none',3,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='website-browsing'),'Visit News Portal','Open the news portal homepage and browse headlines.',3,'Open the link, scroll through the homepage, take a screenshot of the completed page and submit.','https://example.com/browse/news','https://picsum.photos/seed/cat2web1/600/400','screenshot',3,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='website-browsing'),'Read Blog Article','Read a 500-word blog article to the end.',4,'Read the full article and submit the last heading as proof text.','https://example.com/browse/blog','https://picsum.photos/seed/cat2web2/600/400','text',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='website-browsing'),'Browse Online Store','Browse 5 product pages in the online store.',2,'Open the store link and submit once done.','https://example.com/browse/store','https://picsum.photos/seed/cat2web3/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='social-tasks'),'Like FB Photo','Like the campaign photo on Facebook.',2,'Open the post link, tap Like, and submit.','https://example.com/social/like','https://picsum.photos/seed/cat2soc1/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='social-tasks'),'Share Post Publicly','Share the campaign post publicly on your timeline.',4,'Share the post with Public privacy and submit the post link.','https://example.com/social/share','https://picsum.photos/seed/cat2soc2/600/400','text',3,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='social-tasks'),'Follow Instagram Page','Follow the brand page on Instagram.',5,'Follow the page and upload a screenshot showing Following.','https://example.com/social/ig','https://picsum.photos/seed/cat2soc3/600/400','screenshot',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='telegram-tasks'),'Join Promo Channel','Join the official promo broadcast channel.',3,'Tap the invite link, press Join, and upload a screenshot.','https://example.com/tg/channel','https://picsum.photos/seed/cat2tg1/600/400','screenshot',3,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='telegram-tasks'),'Join Chat Group','Join the community chat group.',4,'Join the group and upload a screenshot of the welcome screen.','https://example.com/tg/group','https://picsum.photos/seed/cat2tg2/600/400','screenshot',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='telegram-tasks'),'React to Pinned Post','React with emoji to the pinned announcement.',2,'Open the pinned post, add any emoji reaction, and submit.','https://example.com/tg/react','https://picsum.photos/seed/cat2tg3/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='app-reviews'),'Install Utility App','Install the utility app and open it once.',8,'Install from the link, open the app, and upload a screenshot of the home screen.','https://example.com/app/utility','https://picsum.photos/seed/cat2app1/600/400','screenshot',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='app-reviews'),'Rate Food Delivery App','Give the food delivery app a 5-star rating.',10,'Install, rate 5 stars on the store, and submit a screenshot of your rating.','https://example.com/app/food','https://picsum.photos/seed/cat2app2/600/400','both',1,0,1,'active'),
((SELECT id FROM task_categories WHERE slug='app-reviews'),'Review Music App','Write a short honest review for the music app.',12,'Submit your written review text of at least 20 characters.','https://example.com/app/music','https://picsum.photos/seed/cat2app3/600/400','text',1,0,1,'active'),
((SELECT id FROM task_categories WHERE slug='survey-polls'),'Weekend Plans Poll','Answer one quick poll about weekend plans.',2,'Pick an option and submit.','https://example.com/survey/weekend','https://picsum.photos/seed/cat2sur1/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='survey-polls'),'Food Delivery Survey','Complete a short survey about food delivery habits.',7,'Answer all survey questions honestly and submit.','https://example.com/survey/food','https://picsum.photos/seed/cat2sur2/600/400','text',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='survey-polls'),'Transport Feedback Form','Share feedback about city transport.',9,'Fill the feedback form and submit the confirmation text.','https://example.com/survey/transport','https://picsum.photos/seed/cat2sur3/600/400','text',1,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='quiz-challenge'),'World Capitals Quiz','Answer both geography questions correctly.',8,'Pick one option per question and submit together.','','https://picsum.photos/seed/cat2quiz1/600/400','none',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='quiz-challenge'),'Everyday Science Quiz','Answer both science questions correctly.',10,'Pick one option per question and submit together.','','https://picsum.photos/seed/cat2quiz2/600/400','none',1,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='quiz-challenge'),'Math Master Quiz','Answer both math questions correctly.',12,'Pick one option per question and submit together.','','https://picsum.photos/seed/cat2quiz3/600/400','none',1,0,1,'active'),
((SELECT id FROM task_categories WHERE slug='captcha-tasks'),'Type 5 Captcha Codes','Type the characters shown in 5 captcha images.',3,'Submit all 5 codes separated by commas.','https://example.com/captcha/batch1','https://picsum.photos/seed/cat2cap1/600/400','text',3,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='captcha-tasks'),'Enter Receipt Totals','Type the shop name and total from 3 receipts.',5,'Submit in the format: ShopName | Total, one per line.','https://example.com/captcha/receipt','https://picsum.photos/seed/cat2cap2/600/400','text',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='captcha-tasks'),'Tag Product Photos','Write a one-line tag for each of 10 product photos.',6,'Submit the 10 tags numbered 1 to 10.','https://example.com/captcha/labels','https://picsum.photos/seed/cat2cap3/600/400','text',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='youtube-tasks'),'Like Cooking Video','Watch 1 minute of a cooking video and like it.',3,'Watch at least 1 minute, tap Like, and submit.','https://example.com/yt/cooking','https://picsum.photos/seed/cat2yt1/600/400','none',3,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='youtube-tasks'),'Subscribe News Channel','Subscribe to the news channel.',5,'Subscribe and upload a screenshot showing Subscribed.','https://example.com/yt/news','https://picsum.photos/seed/cat2yt2/600/400','screenshot',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='youtube-tasks'),'Comment on Vlog','Leave a genuine comment on the travel vlog.',4,'Post your comment and submit the comment text.','https://example.com/yt/vlog','https://picsum.photos/seed/cat2yt3/600/400','text',2,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='ad-view'),'Click & View Banner Ad','Click the banner and view the page for 30 seconds.',2,'Click through and submit once the timer finishes.','https://example.com/ads/banner','https://picsum.photos/seed/catad1/600/400','none',8,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='ad-view'),'Complete Shortlink Step','Pass through the shortlink verification step.',3,'Complete the human-verification step and submit.','https://example.com/ads/shortlink','https://picsum.photos/seed/catad2/600/400','none',5,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='ad-view'),'Watch Rewarded Ad 15s','Watch the full-screen rewarded ad for 15 seconds.',2,'Wait for the ad to finish and submit.','https://example.com/ads/rewarded','https://picsum.photos/seed/catad3/600/400','none',8,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='signup-tasks'),'Join Newsletter List','Subscribe to the newsletter with your email.',6,'Enter your email on the form and submit.','https://example.com/signup/newsletter','https://picsum.photos/seed/cat2sign1/600/400','text',1,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='signup-tasks'),'Create Forum Account','Create an account on the community forum.',8,'Register and submit your forum username.','https://example.com/signup/forum','https://picsum.photos/seed/cat2sign2/600/400','text',1,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='signup-tasks'),'Verify Email Account','Verify your email on the partner site.',10,'Click the verification link in your inbox and upload a screenshot.','https://example.com/signup/verify','https://picsum.photos/seed/cat2sign3/600/400','screenshot',1,0,0,'active'),
((SELECT id FROM task_categories WHERE slug='vip-tasks'),'VIP Cashback Mission','Exclusive cashback mission for VIP members.',20,'Complete all mission steps and upload proof screenshots.','https://example.com/vip/cashback','https://picsum.photos/seed/cat2vip1/600/400','screenshot',2,0,1,'active'),
((SELECT id FROM task_categories WHERE slug='vip-tasks'),'VIP Beta App Review','Review the VIP-only beta application.',25,'Install the beta, use it 10 minutes, submit findings plus a screenshot.','https://example.com/vip/beta','https://picsum.photos/seed/cat2vip2/600/400','both',1,0,1,'active'),
((SELECT id FROM task_categories WHERE slug='vip-tasks'),'VIP High-Stakes Poll','High-reward opinion poll for VIP members.',22,'Answer carefully and submit.','https://example.com/vip/poll','https://picsum.photos/seed/cat2vip3/600/400','text',2,0,1,'active');

-- Quiz questions for the quiz-challenge tasks (options JSON + zero-based key).
INSERT INTO `quiz_questions` (`task_id`,`question`,`options`,`correct_index`) VALUES
((SELECT t.id FROM tasks t JOIN task_categories c ON c.id = t.category_id WHERE c.slug='quiz-challenge' AND t.title='World Capitals Quiz'),'What is the capital of Japan?','["Beijing","Seoul","Tokyo","Bangkok"]',2),
((SELECT t.id FROM tasks t JOIN task_categories c ON c.id = t.category_id WHERE c.slug='quiz-challenge' AND t.title='World Capitals Quiz'),'What is the capital of Australia?','["Sydney","Melbourne","Canberra","Perth"]',2),
((SELECT t.id FROM tasks t JOIN task_categories c ON c.id = t.category_id WHERE c.slug='quiz-challenge' AND t.title='Everyday Science Quiz'),'Which gas do plants absorb for photosynthesis?','["Oxygen","Carbon dioxide","Nitrogen","Hydrogen"]',1),
((SELECT t.id FROM tasks t JOIN task_categories c ON c.id = t.category_id WHERE c.slug='quiz-challenge' AND t.title='Everyday Science Quiz'),'How many legs does a spider have?','["6","8","10","4"]',1),
((SELECT t.id FROM tasks t JOIN task_categories c ON c.id = t.category_id WHERE c.slug='quiz-challenge' AND t.title='Math Master Quiz'),'What is 9 x 7?','["56","61","63","72"]',2),
((SELECT t.id FROM tasks t JOIN task_categories c ON c.id = t.category_id WHERE c.slug='quiz-challenge' AND t.title='Math Master Quiz'),'What is half of 150?','["60","70","75","80"]',2);

-- Standalone quick-earn quizzes (Home > Easy Earning > Quiz).
-- 7 Math + 7 General Knowledge, all active with a 0.50 default reward.
INSERT INTO `quizzes`
  (`question`,`option_a`,`option_b`,`option_c`,`option_d`,`correct_option`,`reward_amount`,`is_active`) VALUES
('What is 7 x 8?','54','56','58','64','b',0.50,1),
('What is 144 / 12?','10','11','12','14','c',0.50,1),
('What is 15% of 200?','20','25','30','35','c',0.50,1),
('What is the square root of 81?','7','8','9','11','c',0.50,1),
('What is 25 + 37?','52','62','72','82','b',0.50,1),
('If 3x = 27, what is x?','6','7','8','9','d',0.50,1),
('Which number is a prime number?','21','27','29','33','c',0.50,1),
('Which planet is known as the Red Planet?','Venus','Mars','Jupiter','Mercury','b',0.50,1),
('What is the capital of Bangladesh?','Chattogram','Khulna','Dhaka','Sylhet','c',0.50,1),
('Which gas do plants absorb for photosynthesis?','Carbon dioxide','Oxygen','Nitrogen','Hydrogen','a',0.50,1),
('How many continents are there on Earth?','5','6','7','8','c',0.50,1),
('Which famous writer wrote Romeo and Juliet?','Charles Dickens','William Shakespeare','Mark Twain','Jane Austen','b',0.50,1),
('What is the largest ocean on Earth?','Atlantic','Indian','Arctic','Pacific','d',0.50,1),
('Which country is known as the Land of the Rising Sun?','China','Japan','Thailand','South Korea','b',0.50,1);

-- Guided execution backfill (same rules as seed_demo.php Step 0):
-- quiz where questions exist, screenshot where proof requires an image.
UPDATE `tasks` SET `task_type` = 'quiz'
 WHERE `id` IN (SELECT `task_id` FROM `quiz_questions`);
UPDATE `tasks` SET `task_type` = 'screenshot'
 WHERE `task_type` = 'visit_timer' AND `required_proof` IN ('screenshot','both')
   AND `id` NOT IN (SELECT `task_id` FROM `quiz_questions`);

-- Operators (BD). Column mapping: icon -> logo, status -> is_active.
INSERT INTO `operators` (`name`,`code`,`prefixes`,`logo`,`is_active`) VALUES
('Grameenphone (GP)','grameenphone','017,013','https://logo.clearbit.com/grameenphone.com',1),
('Banglalink (BL)','banglalink','019,014','https://logo.clearbit.com/banglalink.net',1),
('Robi (ROBI)','robi','018','https://logo.clearbit.com/robi.com.bd',1),
('Airtel (AIRTEL)','airtel','016','https://logo.clearbit.com/airtel.com.bd',1),
('Teletalk (TELETALK)','teletalk','015','https://logo.clearbit.com/teletalk.com.bd',1);

-- Drive offer catalog: 5 offers x 5 operators (data, combo, voice, weekly).
-- Column mapping: package_details -> title (+validity), regular_price -> mrp,
-- offer_price -> price, validity_days -> validity, status -> is_active.
INSERT INTO `drive_offers` (`operator_id`,`title`,`offer_type`,`price`,`mrp`,`cashback`,`validity`,`is_active`) VALUES
((SELECT id FROM operators WHERE code='grameenphone'),'GP 10GB 30 Days','data',249,299,8,'30 days',1),
((SELECT id FROM operators WHERE code='grameenphone'),'GP 500 Min Bundle','minute',299,349,10,'30 days',1),
((SELECT id FROM operators WHERE code='grameenphone'),'GP 5GB + 200 Min Combo','combo',199,249,7,'30 days',1),
((SELECT id FROM operators WHERE code='grameenphone'),'GP 2GB Weekly Pack','data',79,99,3,'7 days',1),
((SELECT id FROM operators WHERE code='grameenphone'),'GP 1000 Min + 1GB Monthly','combo',599,649,18,'30 days',1),
((SELECT id FROM operators WHERE code='banglalink'),'BL 8GB 30 Days','data',229,279,8,'30 days',1),
((SELECT id FROM operators WHERE code='banglalink'),'BL 400 Min Bundle','minute',269,319,9,'30 days',1),
((SELECT id FROM operators WHERE code='banglalink'),'BL 4GB + 150 Min Combo','combo',179,229,6,'30 days',1),
((SELECT id FROM operators WHERE code='banglalink'),'BL 1.5GB Weekly Pack','data',69,89,2,'7 days',1),
((SELECT id FROM operators WHERE code='banglalink'),'BL 800 Min Monthly','minute',499,549,15,'30 days',1),
((SELECT id FROM operators WHERE code='robi'),'Robi 12GB 30 Days','data',259,309,9,'30 days',1),
((SELECT id FROM operators WHERE code='robi'),'Robi 600 Min Bundle','minute',319,369,11,'30 days',1),
((SELECT id FROM operators WHERE code='robi'),'Robi 6GB + 250 Min Combo','combo',219,269,8,'30 days',1),
((SELECT id FROM operators WHERE code='robi'),'Robi 3GB Weekly Pack','data',99,129,4,'7 days',1),
((SELECT id FROM operators WHERE code='robi'),'Robi 1000 Min Monthly','minute',599,649,18,'30 days',1),
((SELECT id FROM operators WHERE code='airtel'),'Airtel 15GB 30 Days','data',279,329,10,'30 days',1),
((SELECT id FROM operators WHERE code='airtel'),'Airtel 350 Min Bundle','minute',209,249,7,'30 days',1),
((SELECT id FROM operators WHERE code='airtel'),'Airtel 7GB + 300 Min Combo','combo',239,289,9,'30 days',1),
((SELECT id FROM operators WHERE code='airtel'),'Airtel 2.5GB Weekly Pack','data',89,109,3,'7 days',1),
((SELECT id FROM operators WHERE code='airtel'),'Airtel 900 Min Monthly','minute',549,599,16,'30 days',1),
((SELECT id FROM operators WHERE code='teletalk'),'Teletalk 5GB 30 Days','data',159,199,6,'30 days',1),
((SELECT id FROM operators WHERE code='teletalk'),'Teletalk 300 Min Bundle','minute',189,229,7,'30 days',1),
((SELECT id FROM operators WHERE code='teletalk'),'Teletalk 3GB + 100 Min Combo','combo',139,179,5,'30 days',1),
((SELECT id FROM operators WHERE code='teletalk'),'Teletalk 1GB Weekly Pack','data',55,69,2,'7 days',1),
((SELECT id FROM operators WHERE code='teletalk'),'Teletalk 700 Min Monthly','minute',449,499,14,'30 days',1);

-- Sample banner
INSERT INTO `banners` (`title`,`image`,`link_type`,`link_value`,`is_active`,`sort_order`) VALUES
('Welcome Offer','uploads/banners/welcome.png','none','',1,1);

-- Demo users: 5-level MLM chain, password 123456 for all.
-- package_id resolves via slug so auto-increment offsets never matter.
INSERT INTO `users`
  (name, phone, password_hash, referral_code, referred_by,
   is_verified, package_id, verified_at, status) VALUES
('Tanvir','01711000001','$2y$10$gH9HAVhr/Ov7tBZv5wPwEOiECTfSJNfzQmVkiw5wYKXNtvr6Dc7xC','REF101',NULL,1,(SELECT id FROM membership_plans WHERE slug='platinum'),NOW(),'active'),
('Rashed','01711000002','$2y$10$gH9HAVhr/Ov7tBZv5wPwEOiECTfSJNfzQmVkiw5wYKXNtvr6Dc7xC','REF102',NULL,1,(SELECT id FROM membership_plans WHERE slug='gold'),NOW(),'active'),
('Sabbir','01711000003','$2y$10$gH9HAVhr/Ov7tBZv5wPwEOiECTfSJNfzQmVkiw5wYKXNtvr6Dc7xC','REF103',NULL,1,(SELECT id FROM membership_plans WHERE slug='silver'),NOW(),'active'),
('Mahmud','01711000004','$2y$10$gH9HAVhr/Ov7tBZv5wPwEOiECTfSJNfzQmVkiw5wYKXNtvr6Dc7xC','REF104',NULL,1,(SELECT id FROM membership_plans WHERE slug='bronze'),NOW(),'active'),
('Mehedi','01711000005','$2y$10$gH9HAVhr/Ov7tBZv5wPwEOiECTfSJNfzQmVkiw5wYKXNtvr6Dc7xC','REF105',NULL,0,(SELECT id FROM membership_plans WHERE slug='free'),NULL,'active');

-- Link the referral chain: Rashed -> Tanvir -> ... -> Mehedi -> Mahmud.
UPDATE `users` AS u JOIN `users` AS p ON p.phone = '01711000001' SET u.referred_by = p.id WHERE u.phone = '01711000002';
UPDATE `users` AS u JOIN `users` AS p ON p.phone = '01711000002' SET u.referred_by = p.id WHERE u.phone = '01711000003';
UPDATE `users` AS u JOIN `users` AS p ON p.phone = '01711000003' SET u.referred_by = p.id WHERE u.phone = '01711000004';
UPDATE `users` AS u JOIN `users` AS p ON p.phone = '01711000004' SET u.referred_by = p.id WHERE u.phone = '01711000005';

-- Demo wallets (balances only; earnings accrue through real flows).
INSERT INTO `wallets` (user_id, balance, total_earned) VALUES
((SELECT id FROM users WHERE phone = '01711000001'), 4850, 4850),
((SELECT id FROM users WHERE phone = '01711000002'), 2150, 2150),
((SELECT id FROM users WHERE phone = '01711000003'), 940, 940),
((SELECT id FROM users WHERE phone = '01711000004'), 350, 350),
((SELECT id FROM users WHERE phone = '01711000005'), 85, 85);

-- Demo Community Micro Jobs (admin posts: user_id NULL, no wallet escrow).
-- Both canonical and legacy column mirrors are set so the feed/detail
-- queries and the legacy proof/review flow agree. Safe to re-import only
-- on a fresh database (these tables start empty).
INSERT INTO `campaigns`
  (`user_id`,`is_admin_post`,`title`,`work_link`,`work_limit`,`completed_workers`,
   `per_work_cost`,`total_cost`,`image_url`,`description`,`status`,`category`,
   `proof_instruction`,`proof_type`,`reward_per_worker`,`total_workers`,`total_budget`,
   `spent_amount`) VALUES
(NULL,1,'Subscribe & Like YouTube Video','https://www.youtube.com/results?search_query=modern+earning',100,0,
 4.00,400.00,NULL,'Support our YouTube channel by subscribing and liking the latest video.','active','YouTube Engagement',
 'Search our channel, watch 2 minutes of the latest video, like, and subscribe. Upload a screenshot as proof.','screenshot',4.00,100,400.00,
 0),
(NULL,1,'Sign up on Website and Verify Email','https://example.com/signup/verify',50,0,
 10.00,500.00,NULL,'Create an account on our partner website and confirm your email address.','active','Sign-Up & Register',
 'Click the link, create a new account, verify your email address, and upload a screenshot of your new dashboard.','screenshot',10.00,50,500.00,
 0),
(NULL,1,'Download App & Give 5-Star Rating','https://example.com/app/review',200,0,
 8.00,1600.00,NULL,'Install our app from the Play Store and leave a 5-star review.','active','App Reviews',
 'Download the app from the Play Store, give a 5-star rating with a positive review, and upload the screenshot of your review.','screenshot',8.00,200,1600.00,
 0);

-- Demo shop catalog: 6 categories (column mapping: icon -> image,
-- status -> is_active) + 12 products, all popular + active.
-- NOTE: 2-per-category math gives 8 physical + 4 virtual.
INSERT INTO `product_categories` (`name`,`slug`,`image`,`is_active`) VALUES
('Electronics & Gadgets','electronics-gadgets','https://picsum.photos/seed/shopcat2-eg/600/400',1),
('Men''s Fashion','mens-fashion','https://picsum.photos/seed/shopcat2-mf/600/400',1),
('Women''s Lifestyle','womens-fashion','https://picsum.photos/seed/shopcat2-wf/600/400',1),
('Mobile & Computer Accessories','accessories','https://picsum.photos/seed/shopcat2-ac/600/400',1),
('Digital Gift Cards','gift-cards','https://picsum.photos/seed/shopcat2-gc/600/400',1),
('Gaming Top-Up & Subscriptions','game-subs','https://picsum.photos/seed/shopcat2-gs/600/400',1);

INSERT INTO `products`
  (`category_id`,`name`,`description`,`price`,`mrp`,`cashback`,`stock`,`images`,
   `is_popular`,`product_type`,`delivery_charge`,`status`) VALUES
((SELECT id FROM product_categories WHERE slug='electronics-gadgets'),'Bluetooth Party Speaker','Portable party speaker with deep bass and LED lights.',2200,2599,70,18,'["https://images.unsplash.com/photo-1545454675-3531b543be5d?auto=format&fit=crop&w=600&q=80"]',1,'physical',80,'active'),
((SELECT id FROM product_categories WHERE slug='electronics-gadgets'),'Power Bank 20000mAh','20000mAh fast-charging power bank with dual output.',1850,2199,55,30,'["https://images.unsplash.com/photo-1609091839311-d5365f9ff1c5?auto=format&fit=crop&w=600&q=80"]',1,'physical',60,'active'),
((SELECT id FROM product_categories WHERE slug='mens-fashion'),'Men Panjabi Premium','Premium cotton panjabi with elegant embroidery.',1450,1799,40,40,'["https://images.unsplash.com/photo-1594938298603-c8148c4dae35?auto=format&fit=crop&w=600&q=80"]',1,'physical',50,'active'),
((SELECT id FROM product_categories WHERE slug='mens-fashion'),'Running Shoes Pro','Lightweight running shoes with cushioned sole.',2350,2799,75,22,'["https://images.unsplash.com/photo-1542291026-7eec264c27ff?auto=format&fit=crop&w=600&q=80"]',1,'physical',70,'active'),
((SELECT id FROM product_categories WHERE slug='womens-fashion'),'Silk Saree Dhakai','Original Dhakai silk saree with traditional motifs.',3200,3899,100,15,'["https://images.unsplash.com/photo-1610030469983-98e550d6193c?auto=format&fit=crop&w=600&q=80"]',1,'physical',90,'active'),
((SELECT id FROM product_categories WHERE slug='womens-fashion'),'Handbag Leather Tote','Genuine leather tote bag with spacious compartments.',1650,1999,50,28,'["https://images.unsplash.com/photo-1584917865442-de89df76afd3?auto=format&fit=crop&w=600&q=80"]',1,'physical',60,'active'),
((SELECT id FROM product_categories WHERE slug='accessories'),'USB-C Fast Charger 25W','25W USB-C fast charger with safety protection.',450,599,15,100,'["https://images.unsplash.com/photo-1583863788434-e58a36330cf0?auto=format&fit=crop&w=600&q=80"]',1,'physical',50,'active'),
((SELECT id FROM product_categories WHERE slug='accessories'),'Wireless Mouse Pro','Silent-click wireless mouse, 12-month battery.',750,899,25,60,'["https://images.unsplash.com/photo-1527864550417-7fd91fc51a46?auto=format&fit=crop&w=600&q=80"]',1,'physical',50,'active'),
((SELECT id FROM product_categories WHERE slug='gift-cards'),'Google Play Gift Card 500','Google Play redeem code worth BDT 500, instant email delivery.',520,550,12,999,'["https://images.unsplash.com/photo-1512941937669-90a1b58e7e9c?auto=format&fit=crop&w=600&q=80"]',1,'virtual',0,'active'),
((SELECT id FROM product_categories WHERE slug='gift-cards'),'Netflix Premium 1 Month','Netflix gift voucher code for 1 month premium.',650,699,20,999,'["https://images.unsplash.com/photo-1593359677879-a4bb92f829d1?auto=format&fit=crop&w=600&q=80"]',1,'virtual',0,'active'),
((SELECT id FROM product_categories WHERE slug='game-subs'),'Free Fire 1000 Diamonds','Digital code for 1000 Free Fire diamonds via player ID.',890,950,25,999,'["https://images.unsplash.com/photo-1511512578047-dfb367046420?auto=format&fit=crop&w=600&q=80"]',1,'virtual',0,'active'),
((SELECT id FROM product_categories WHERE slug='game-subs'),'PUBG UC 600 Pack','PUBG Mobile 600 UC redeem pack via character ID.',1050,1150,30,999,'["https://images.unsplash.com/photo-1542751371-adc38448a05e?auto=format&fit=crop&w=600&q=80"]',1,'virtual',0,'active');

-- ------------------------------------------------------------
-- 25. RESELLING (Life Good model)
-- Resellers browse wholesale products, set their own retail price,
-- and place customer orders. Profit is settled after delivery.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `resell_products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `category` VARCHAR(100) NOT NULL DEFAULT 'Home Accessories',
  `wholesale_price` DECIMAL(10,2) NOT NULL COMMENT 'Cost price from platform/admin',
  `suggested_retail_price` DECIMAL(10,2) NOT NULL,
  `max_sell_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Maximum price reseller may charge per unit',
  `delivery_charge_inside` DECIMAL(10,2) DEFAULT 70.00,
  `delivery_charge_outside` DECIMAL(10,2) DEFAULT 130.00,
  `stock_quantity` INT DEFAULT 100,
  `image_url` VARCHAR(255) NOT NULL,
  `gallery_images` TEXT NULL COMMENT 'JSON array of extra photo URLs',
  `vendor_id` INT UNSIGNED NULL COMMENT 'NULL = platform/admin product; otherwise vendors.id',
  `vendor_name` VARCHAR(150) NULL,
  `vendor_whatsapp` VARCHAR(30) NULL,
  `is_approved` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Admin moderation flag for vendor-submitted products',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_resell_products_active` (`is_active`),
  INDEX `idx_resell_products_category` (`category`),
  INDEX `idx_resell_products_vendor` (`vendor_id`),
  CONSTRAINT `fk_resell_products_vendor` FOREIGN KEY (`vendor_id`)
    REFERENCES `vendors` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `resell_orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL COMMENT 'The Reseller',
  `product_id` INT NOT NULL,
  `quantity` INT DEFAULT 1,
  `wholesale_price` DECIMAL(10,2) NOT NULL,
  `reseller_selling_price` DECIMAL(10,2) NOT NULL COMMENT 'Price charged to customer',
  `profit_amount` DECIMAL(10,2) NOT NULL COMMENT '(selling_price - wholesale_price) * qty',
  `delivery_zone` ENUM('inside_dhaka', 'outside_dhaka') DEFAULT 'inside_dhaka',
  `delivery_charge` DECIMAL(10,2) NOT NULL,
  `delivery_charge_paid` TINYINT(1) NOT NULL DEFAULT 0,
  `delivery_charge_payment_method` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'wallet, bkash, nagad, rocket',
  `delivery_charge_sender_phone` VARCHAR(30) NULL,
  `delivery_charge_trx_id` VARCHAR(100) NULL,
  `delivery_payment_status` ENUM('paid_wallet', 'pending_verification', 'rejected') NOT NULL DEFAULT 'pending_verification',
  `customer_name` VARCHAR(150) NOT NULL,
  `customer_phone` VARCHAR(25) NOT NULL,
  `district` VARCHAR(100) NOT NULL DEFAULT '',
  `upazila` VARCHAR(100) NOT NULL DEFAULT '',
  `customer_address` TEXT NOT NULL,
  `additional_instruction` TEXT NULL,
  `payment_method` VARCHAR(50) NOT NULL DEFAULT 'cod_manual',
  `courier_name` VARCHAR(100) NULL COMMENT 'e.g. Steadfast / Pathao / RedX',
  `tracking_code` VARCHAR(100) NULL,
  `status` ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'returned') DEFAULT 'pending',
  `is_profit_settled` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_resell_orders_user_created` (`user_id`, `created_at`),
  INDEX `idx_resell_orders_status` (`status`),
  INDEX `idx_resell_orders_district` (`district`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Re-enable FK enforcement now that schema + seeds are fully in place.
SET FOREIGN_KEY_CHECKS = 1;
