USE asktbpks_metropol; -- uniqueness moves to (user + task + day + CONTRACT): -- the same task CAN be assigned again under a NEW contract on the same day ALTER TABLE task_assignments DROP INDEX uq_assign; ALTER TABLE task_assignments ADD UNIQUE KEY uq_assign_ctr (user_id, task_id, assign_date, contract_id); -- Run once: re-prices all still-pending jobs of ACTIVE contracts -- to the package-configured daily split. UPDATE task_assignments ta JOIN contracts c ON c.id = ta.contract_id JOIN contract_packages p ON p.id = c.package_id SET ta.reward_rwf = ROUND(p.expected_daily_rwf / p.tasks_per_day, 2) WHERE ta.status IN ('PENDING','STARTED') AND c.status = 'ACTIVE' AND p.tasks_per_day > 0 AND p.expected_daily_rwf > 0; -- fund_platform_accounts_fix.sql · asktbpks_metropol · safe to re-run USE asktbpks_metropol; -- 1) Platform MoMo accounts (skip if you already created it) CREATE TABLE IF NOT EXISTS platform_accounts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, label VARCHAR(60) NOT NULL DEFAULT 'MTN MoMo', account_name VARCHAR(120) NOT NULL, momo_number VARCHAR(20) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, current_load INT UNSIGNED NOT NULL DEFAULT 0, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_pa_number (momo_number) ) ENGINE=InnoDB; -- 2) Add columns ONLY if missing (MySQL 5.7/8.0 safe — no IF NOT EXISTS on ADD COLUMN) SET @db := DATABASE(); SET @sql := (SELECT IF( (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'deposit_requests' AND COLUMN_NAME = 'payer_name') = 0, 'ALTER TABLE deposit_requests ADD COLUMN payer_name VARCHAR(120) NULL AFTER payer_phone', 'SELECT "payer_name exists — skipped" AS info')); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql := (SELECT IF( (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'deposit_requests' AND COLUMN_NAME = 'account_id') = 0, 'ALTER TABLE deposit_requests ADD COLUMN account_id BIGINT UNSIGNED NULL AFTER payer_name', 'SELECT "account_id exists — skipped" AS info')); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 3) Foreign key (skip if already added) SET @sql := (SELECT IF( (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = @db AND TABLE_NAME = 'deposit_requests' AND CONSTRAINT_NAME = 'fk_dep_account') = 0, 'ALTER TABLE deposit_requests ADD CONSTRAINT fk_dep_account FOREIGN KEY (account_id) REFERENCES platform_accounts(id)', 'SELECT "fk_dep_account exists — skipped" AS info')); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 4) Seed the first account ONLY if the table is empty (edit before going live!) INSERT INTO platform_accounts (label, account_name, momo_number) SELECT 'MTN MoMo', 'YOUR REAL HOLDER NAME', '2507XXXXXXXX' WHERE NOT EXISTS (SELECT 1 FROM platform_accounts); USE asktbpks_metropol; -- 1) Ledger: add deposit custody type ALTER TABLE ledger_transactions MODIFY COLUMN type ENUM( 'TASK_EARNING','REFERRAL_COMMISSION','LEVEL_BENEFIT','DEPOSIT_CREDIT', 'CONTRACT_STAKE_HOLD','CONTRACT_STAKE_RELEASE', 'WITHDRAWAL_HOLD','WITHDRAWAL_PAID','WITHDRAWAL_REVERSAL', 'ADJUSTMENT_CREDIT','ADJUSTMENT_DEBIT','REVERSAL') NOT NULL; -- 2) Packages: admin-declared daily job-pay budget -- ⚠️ MUST equal the sum of rewards of the jobs actually assigned to that -- contract each day. Set it from your real task budget, not from wishes. ALTER TABLE contract_packages ADD COLUMN expected_daily_rwf DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER tasks_per_day; -- 3) Deposits: user declares a MoMo payment, admin verifies & credits CREATE TABLE IF NOT EXISTS deposit_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference VARCHAR(30) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(18,2) NOT NULL, payer_phone VARCHAR(20) NOT NULL, status ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING', approved_by BIGINT UNSIGNED NULL, note VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, processed_at DATETIME NULL, UNIQUE KEY uq_dep_ref (reference), KEY idx_dep_user (user_id, status), CONSTRAINT fk_dep_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_dep_admin FOREIGN KEY (approved_by) REFERENCES admins(id) ) ENGINE=InnoDB; -- 4) Settings INSERT INTO app_settings (setting_key, setting_value, description) VALUES ('deposit.min_amount', '1000', 'Minimum wallet funding amount (RWF)'), ('wallet.momo_number', '2507XXXXXXXX', 'Platform MTN MoMo number shown on the funding card'); -- 5) Daily job-pay budgets (EDIT these to match your real task rewards) UPDATE contract_packages SET expected_daily_rwf = 135 WHERE code='LOCK-1K-3D'; UPDATE contract_packages SET expected_daily_rwf = 190 WHERE code='LOCK-9K-7D'; UPDATE contract_packages SET expected_daily_rwf = 355 WHERE code='LOCK-22K-17D'; UPDATE contract_packages SET expected_daily_rwf = 1050 WHERE code='DAILY-50K'; UPDATE contract_packages SET expected_daily_rwf = 2700 WHERE code='DAILY-113K'; UPDATE contract_packages SET expected_daily_rwf = 7500 WHERE code='DAILY-274K'; UPDATE contract_packages SET expected_daily_rwf = 25000 WHERE code='DAILY-981K'; UPDATE contract_packages SET expected_daily_rwf = 80000 WHERE code='DAILY-3M'; UPDATE contract_packages SET expected_daily_rwf = 170000 WHERE code='DAILY-6M'; -- 6) Activate a package AFTER a second admin approves it (dual approval): -- UPDATE contract_packages SET approved_by=, approved_at=NOW(), status='ACTIVE' WHERE code='LOCK-1K-3D'; USE asktbpks_metropol; -- ============================================================ -- 1) EXTEND LEDGER with stake movements (money never created — -- stake moves available → locked → back to available) -- ============================================================ ALTER TABLE ledger_transactions MODIFY COLUMN type ENUM( 'TASK_EARNING','REFERRAL_COMMISSION','LEVEL_BENEFIT', 'WITHDRAWAL_HOLD','WITHDRAWAL_PAID','WITHDRAWAL_REVERSAL', 'CONTRACT_STAKE_HOLD','CONTRACT_STAKE_RELEASE', 'ADJUSTMENT_CREDIT','ADJUSTMENT_DEBIT','REVERSAL') NOT NULL; -- ============================================================ -- 2) CONTRACT PACKAGES — stake (refundable) + duration + daily -- jobs. NO earnings field, NO payout field, NO multiplier. -- ============================================================ CREATE TABLE IF NOT EXISTS contract_packages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL, name_en VARCHAR(120) NOT NULL, name_rw VARCHAR(120) NOT NULL, type ENUM('LOCKED','DAILY') NOT NULL, stake_amount DECIMAL(18,2) NOT NULL, -- fully refundable duration_days SMALLINT UNSIGNED NOT NULL, tasks_per_day TINYINT UNSIGNED NOT NULL DEFAULT 2, min_level VARCHAR(20) NOT NULL DEFAULT 'STUDENT', max_active INT UNSIGNED NULL, -- capacity cap status ENUM('DRAFT','ACTIVE','PAUSED','RETIRED') NOT NULL DEFAULT 'DRAFT', created_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, -- dual approval required approved_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_pkg_code (code), CONSTRAINT fk_pkg_level FOREIGN KEY (min_level) REFERENCES levels(code), CONSTRAINT fk_pkg_creator FOREIGN KEY (created_by) REFERENCES admins(id), CONSTRAINT fk_pkg_approver FOREIGN KEY (approved_by) REFERENCES admins(id) ) ENGINE=InnoDB; -- ============================================================ -- 3) CONTRACTS — one row per user commitment -- ============================================================ CREATE TABLE IF NOT EXISTS contracts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference VARCHAR(30) NOT NULL, -- CTR-000001 user_id BIGINT UNSIGNED NOT NULL, package_id BIGINT UNSIGNED NOT NULL, stake_amount DECIMAL(18,2) NOT NULL, -- snapshot start_date DATE NOT NULL, end_date DATE NOT NULL, status ENUM('ACTIVE','COMPLETED','CANCELLED') NOT NULL DEFAULT 'ACTIVE', stake_released TINYINT(1) NOT NULL DEFAULT 0, -- idempotent maturity flag released_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_ctr_ref (reference), KEY idx_ctr_user (user_id, status), KEY idx_ctr_maturity (status, end_date), CONSTRAINT fk_ctr_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_ctr_pkg FOREIGN KEY (package_id) REFERENCES contract_packages(id) ) ENGINE=InnoDB; -- ============================================================ -- 4) Link daily job assignments to contracts -- ============================================================ ALTER TABLE task_assignments ADD COLUMN contract_id BIGINT UNSIGNED NULL AFTER program_id, ADD KEY idx_asg_contract (contract_id), ADD CONSTRAINT fk_asg_contract FOREIGN KEY (contract_id) REFERENCES contracts(id); -- ============================================================ -- 5) Settings -- ============================================================ INSERT INTO app_settings (setting_key, setting_value, description) VALUES ('contract.max_active_per_user', '1', 'Active contracts a user may hold at once'), ('contract.stake_refund', 'FULL_ON_MATURITY', 'Stake is returned in full; earnings come from approved work only'), ('contract.earnings_mode', 'VARIABLE_FROM_TASKS', 'No guaranteed daily/maturity amount — ever'); -- ============================================================ -- 6) SEED PACKAGES — your amounts become refundable STAKES. -- Activate them only after a second admin approves (dual approval): -- UPDATE contract_packages SET approved_by=, approved_at=NOW(), status='ACTIVE' WHERE code='...'; -- ============================================================ INSERT INTO contract_packages (code, name_en, name_rw, type, stake_amount, duration_days, tasks_per_day, min_level, status, created_by) VALUES -- Locked (Amasezerano Afunze) ('LOCK-1K-3D', '3-Day Work Contract', 'Amasezerano y''Akazi — Iminsi 3', 'LOCKED', 1000.00, 3, 2, 'STUDENT', 'DRAFT', 1), ('LOCK-9K-7D', '7-Day Work Contract', 'Amasezerano y''Akazi — Iminsi 7', 'LOCKED', 9122.00, 7, 2, 'STUDENT', 'DRAFT', 1), ('LOCK-22K-17D', '17-Day Work Contract', 'Amasezerano y''Akazi — Iminsi 17', 'LOCKED', 22000.00, 17, 3, 'STUDENT', 'DRAFT', 1), -- Daily (Amasezerano y''Akazi ya Buri Munsi) ('DAILY-50K', 'Daily 50K Program', 'Gahunda ya Buri Munsi — 50K', 'DAILY', 50000.00, 365, 2, 'STUDENT', 'DRAFT', 1), ('DAILY-113K', 'Daily 113K Program', 'Gahunda ya Buri Munsi — 113K', 'DAILY', 112813.00,365, 3, 'STUDENT', 'DRAFT', 1), ('DAILY-274K', 'Daily 274K Program', 'Gahunda ya Buri Munsi — 274K', 'DAILY', 274158.00,365, 3, 'TEACHER', 'DRAFT', 1), ('DAILY-981K', 'Daily 981K Program', 'Gahunda ya Buri Munsi — 981K', 'DAILY', 981428.00,365, 4, 'TEACHER', 'DRAFT', 1), ('DAILY-3M', 'Daily 3M Program', 'Gahunda ya Buri Munsi — 3M', 'DAILY', 3000000.00,365, 5, 'GOAT', 'DRAFT', 1), ('DAILY-6M', 'Daily 6M Program', 'Gahunda ya Buri Munsi — 6M', 'DAILY', 6000000.00,365, 5, 'GOAT', 'DRAFT', 1); -- ===================================================================== -- asktbpks_metropol — Work & Earn Platform -- MySQL 8.x / MariaDB 10.6+ · utf8mb4 · English + Kinyarwanda -- -- AUTH: Users log in with PHONE NUMBER · Admins with USERNAME + PASSWORD -- WITHDRAWAL: Requires verified TRANSACTION PIN -- LANGUAGE: Every content/message table carries _en and _rw columns. -- API responses are returned in users.locale, fallback 'en'. -- KYC: REMOVED per requirement (full name + MoMo destination only) -- EARNINGS: Credited ONLY from verified task work — never from deposits -- ===================================================================== CREATE DATABASE IF NOT EXISTS asktbpks_metropol CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE asktbpks_metropol; -- --------------------------------------------------------------------- -- 1. LEVELS (Urwego) & REFERRAL RATES -- --------------------------------------------------------------------- CREATE TABLE levels ( code VARCHAR(20) PRIMARY KEY, -- STUDENT/TEACHER/GOAT/LEOPARD/LION tier TINYINT UNSIGNED NOT NULL, name_en VARCHAR(60) NOT NULL, name_rw VARCHAR(60) NOT NULL, min_active_referrals INT UNSIGNED NOT NULL DEFAULT 0, -- active = completed >= tasks in last 30 days (see app_settings) monthly_benefit_rwf DECIMAL(18,2) NOT NULL DEFAULT 0.00, -- funded from REAL platform revenue only is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB; CREATE TABLE referral_rates ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, referrer_level VARCHAR(20) NOT NULL, depth TINYINT UNSIGNED NOT NULL, -- 1 = direct, 2, 3 percent DECIMAL(5,2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_rate (referrer_level, depth), CONSTRAINT fk_rate_level FOREIGN KEY (referrer_level) REFERENCES levels(code) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 2. ADMINS (username + password) & USERS (phone number) -- --------------------------------------------------------------------- CREATE TABLE admins ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, password_hash VARCHAR(255) NOT NULL, -- bcrypt/argon2 generated by your app full_name VARCHAR(120) NOT NULL, role ENUM('SUPER_ADMIN','ADMIN','FINANCE','SUPPORT') NOT NULL DEFAULT 'ADMIN', locale ENUM('en','rw') NOT NULL DEFAULT 'en', is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_admin_username (username) ) ENGINE=InnoDB; CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) NOT NULL, -- login identifier, e.g. 2507XXXXXXXX password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(120) NOT NULL, momo_number VARCHAR(20) NULL, -- withdrawal destination momo_holder_name VARCHAR(120) NULL, -- should match full_name (fraud control) locale ENUM('en','rw') NOT NULL DEFAULT 'en', -- ALL success/fail messages returned in this language phone_verified_at DATETIME NULL, level_code VARCHAR(20) NOT NULL DEFAULT 'STUDENT', referred_by BIGINT UNSIGNED NULL, referral_code VARCHAR(12) NOT NULL, status ENUM('ACTIVE','SUSPENDED','BLOCKED','DELETED') NOT NULL DEFAULT 'ACTIVE', risk_level ENUM('LOW','MEDIUM','HIGH') NOT NULL DEFAULT 'LOW', last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_users_phone (phone), UNIQUE KEY uq_users_refcode (referral_code), KEY idx_users_referrer (referred_by), KEY idx_users_level (level_code), CONSTRAINT fk_users_level FOREIGN KEY (level_code) REFERENCES levels(code), CONSTRAINT fk_users_ref FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- TRANSACTION PIN (required for withdrawals). Never store plaintext. CREATE TABLE user_transaction_pins ( user_id BIGINT UNSIGNED PRIMARY KEY, pin_hash VARCHAR(255) NOT NULL, failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL, last_changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_pin_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE user_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash VARCHAR(128) NOT NULL, device_info VARCHAR(255) NULL, ip_address VARCHAR(45) NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_session_token (token_hash), KEY idx_session_user (user_id), CONSTRAINT fk_session_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE otp_codes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(20) NOT NULL, purpose ENUM('LOGIN','VERIFY_PHONE','RESET_PASSWORD') NOT NULL, code_hash VARCHAR(255) NOT NULL, attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL, consumed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_otp (phone, purpose) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 3. WALLET & LEDGER (ledger = source of truth; wallets are a cache) -- --------------------------------------------------------------------- CREATE TABLE wallets ( user_id BIGINT UNSIGNED PRIMARY KEY, available DECIMAL(18,2) NOT NULL DEFAULT 0.00, locked DECIMAL(18,2) NOT NULL DEFAULT 0.00, pending_earnings DECIMAL(18,2) NOT NULL DEFAULT 0.00, lifetime_earned DECIMAL(18,2) NOT NULL DEFAULT 0.00, lifetime_withdrawn DECIMAL(18,2) NOT NULL DEFAULT 0.00, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE ledger_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference VARCHAR(30) NOT NULL, -- TXN-000001 user_id BIGINT UNSIGNED NOT NULL, type ENUM('TASK_EARNING','REFERRAL_COMMISSION','LEVEL_BENEFIT', 'WITHDRAWAL_HOLD','WITHDRAWAL_PAID','WITHDRAWAL_REVERSAL', 'ADJUSTMENT_CREDIT','ADJUSTMENT_DEBIT','REVERSAL') NOT NULL, direction ENUM('CREDIT','DEBIT') NOT NULL, amount DECIMAL(18,2) NOT NULL, balance_after DECIMAL(18,2) NOT NULL, -- available balance after this entry idempotency_key VARCHAR(80) NOT NULL, -- e.g. task_sub:123 — prevents double payment related_type VARCHAR(40) NULL, -- task_submission / withdrawal / etc. related_id BIGINT UNSIGNED NULL, note VARCHAR(255) NULL, created_by BIGINT UNSIGNED NULL, -- admin id (adjustments) created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_ledger_ref (reference), UNIQUE KEY uq_ledger_idem (idempotency_key), KEY idx_ledger_user (user_id, created_at), CONSTRAINT fk_ledger_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB; -- Reconciliation view: true balance always = SUM of the ledger CREATE OR REPLACE VIEW v_user_ledger_balances AS SELECT user_id, SUM(CASE WHEN direction = 'CREDIT' THEN amount ELSE -amount END) AS ledger_balance FROM ledger_transactions GROUP BY user_id; -- --------------------------------------------------------------------- -- 4. MEDIA LIBRARY (shared by banners / announcements / promotions / tasks) -- --------------------------------------------------------------------- CREATE TABLE media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, file_path VARCHAR(255) NOT NULL, file_name VARCHAR(150) NULL, mime_type VARCHAR(80) NULL, size_bytes INT UNSIGNED NULL, width SMALLINT UNSIGNED NULL, height SMALLINT UNSIGNED NULL, uploaded_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_media_admin FOREIGN KEY (uploaded_by) REFERENCES admins(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 5. TASKS & EARNING PROGRAMS (the "daily contract" layer) -- --------------------------------------------------------------------- CREATE TABLE task_categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL, name_en VARCHAR(60) NOT NULL, name_rw VARCHAR(60) NOT NULL, icon VARCHAR(50) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_cat_code (code) ) ENGINE=InnoDB; CREATE TABLE tasks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NULL, title_en VARCHAR(150) NOT NULL, title_rw VARCHAR(150) NOT NULL, description_en TEXT NULL, description_rw TEXT NULL, reward_rwf DECIMAL(10,2) NOT NULL, required_proof ENUM('SCREENSHOT','TEXT','LINK','CODE') NOT NULL DEFAULT 'SCREENSHOT', daily_limit_per_user TINYINT UNSIGNED NOT NULL DEFAULT 1, min_level VARCHAR(20) NOT NULL DEFAULT 'STUDENT', is_active TINYINT(1) NOT NULL DEFAULT 1, starts_at DATETIME NULL, ends_at DATETIME NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_tasks_active (is_active, min_level), CONSTRAINT fk_task_cat FOREIGN KEY (category_id) REFERENCES task_categories(id), CONSTRAINT fk_task_level FOREIGN KEY (min_level) REFERENCES levels(code), CONSTRAINT fk_task_admin FOREIGN KEY (created_by) REFERENCES admins(id) ) ENGINE=InnoDB; -- Programs group tasks into a daily quota ("contract") — FREE to join. -- NOTE: deliberately NO enrollment fee / capital / guaranteed-return fields. CREATE TABLE earning_programs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL, name_en VARCHAR(120) NOT NULL, name_rw VARCHAR(120) NOT NULL, tasks_per_day TINYINT UNSIGNED NOT NULL DEFAULT 2, expected_daily_rwf DECIMAL(12,2) NOT NULL DEFAULT 0.00, -- derived from assigned task rewards duration_days SMALLINT UNSIGNED NOT NULL DEFAULT 30, min_level VARCHAR(20) NOT NULL DEFAULT 'STUDENT', max_enrollments INT UNSIGNED NULL, status ENUM('DRAFT','ACTIVE','PAUSED','RETIRED') NOT NULL DEFAULT 'DRAFT', created_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, -- dual approval before ACTIVE approved_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_prog_code (code), CONSTRAINT fk_prog_level FOREIGN KEY (min_level) REFERENCES levels(code), CONSTRAINT fk_prog_creator FOREIGN KEY (created_by) REFERENCES admins(id), CONSTRAINT fk_prog_approver FOREIGN KEY (approved_by) REFERENCES admins(id) ) ENGINE=InnoDB; CREATE TABLE program_tasks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, program_id BIGINT UNSIGNED NOT NULL, task_id BIGINT UNSIGNED NOT NULL, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 1, UNIQUE KEY uq_prog_task (program_id, task_id), CONSTRAINT fk_pt_program FOREIGN KEY (program_id) REFERENCES earning_programs(id) ON DELETE CASCADE, CONSTRAINT fk_pt_task FOREIGN KEY (task_id) REFERENCES tasks(id) ) ENGINE=InnoDB; CREATE TABLE program_enrollments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, program_id BIGINT UNSIGNED NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, status ENUM('ACTIVE','COMPLETED','CANCELLED') NOT NULL DEFAULT 'ACTIVE', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_enroll (user_id, program_id, start_date), KEY idx_enroll_active (status), CONSTRAINT fk_enroll_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_enroll_prog FOREIGN KEY (program_id) REFERENCES earning_programs(id) ) ENGINE=InnoDB; CREATE TABLE task_assignments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, task_id BIGINT UNSIGNED NOT NULL, program_id BIGINT UNSIGNED NULL, assign_date DATE NOT NULL, reward_rwf DECIMAL(10,2) NOT NULL, -- snapshot of reward at assignment status ENUM('PENDING','STARTED','SUBMITTED','UNDER_REVIEW', 'APPROVED','REJECTED','EXPIRED') NOT NULL DEFAULT 'PENDING', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_assign (user_id, task_id, assign_date), KEY idx_assign_user_date (user_id, assign_date), CONSTRAINT fk_asg_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_asg_task FOREIGN KEY (task_id) REFERENCES tasks(id), CONSTRAINT fk_asg_prog FOREIGN KEY (program_id) REFERENCES earning_programs(id) ) ENGINE=InnoDB; CREATE TABLE task_submissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, assignment_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, proof_media_id BIGINT UNSIGNED NULL, proof_text VARCHAR(500) NULL, status ENUM('SUBMITTED','UNDER_REVIEW','APPROVED','REJECTED') NOT NULL DEFAULT 'SUBMITTED', approved_amount DECIMAL(10,2) NULL, reviewed_by BIGINT UNSIGNED NULL, review_note VARCHAR(255) NULL, reviewed_at DATETIME NULL, submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_submission (assignment_id), KEY idx_sub_status (status), CONSTRAINT fk_sub_assign FOREIGN KEY (assignment_id) REFERENCES task_assignments(id), CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_sub_media FOREIGN KEY (proof_media_id) REFERENCES media(id), CONSTRAINT fk_sub_admin FOREIGN KEY (reviewed_by) REFERENCES admins(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 6. REFERRALS & LEVEL BENEFITS (paid only on VERIFIED task earnings) -- --------------------------------------------------------------------- CREATE TABLE referral_commissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, beneficiary_id BIGINT UNSIGNED NOT NULL, -- the referrer who earns source_user_id BIGINT UNSIGNED NOT NULL, -- the user whose work generated it source_ledger_ref VARCHAR(30) NOT NULL, -- the source TASK_EARNING ledger entry depth TINYINT UNSIGNED NOT NULL, rate_percent DECIMAL(5,2) NOT NULL, amount DECIMAL(18,2) NOT NULL, status ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING', paid_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_refcomm (source_ledger_ref, beneficiary_id, depth), -- idempotent KEY idx_refcomm_benef (beneficiary_id, created_at), CONSTRAINT fk_rc_benef FOREIGN KEY (beneficiary_id) REFERENCES users(id), CONSTRAINT fk_rc_source FOREIGN KEY (source_user_id) REFERENCES users(id) ) ENGINE=InnoDB; CREATE TABLE level_benefit_payouts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, level_code VARCHAR(20) NOT NULL, benefit_month CHAR(7) NOT NULL, -- '2025-09' active_referrals_count INT UNSIGNED NOT NULL, amount DECIMAL(18,2) NOT NULL, status ENUM('PENDING','PAID','CANCELLED') NOT NULL DEFAULT 'PENDING', paid_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_benefit (user_id, benefit_month), -- one payout per user per month CONSTRAINT fk_lb_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_lb_level FOREIGN KEY (level_code) REFERENCES levels(code) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 7. WITHDRAWALS (transaction PIN required — verified by the app before insert) -- --------------------------------------------------------------------- CREATE TABLE withdrawal_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference VARCHAR(30) NOT NULL, -- WDR-000001 user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(18,2) NOT NULL, fee DECIMAL(18,2) NOT NULL DEFAULT 0.00, net_amount DECIMAL(18,2) NOT NULL, destination_type ENUM('MTN_MOMO','AIRTEL_MONEY') NOT NULL DEFAULT 'MTN_MOMO', destination_phone VARCHAR(20) NOT NULL, pin_verified TINYINT(1) NOT NULL DEFAULT 0, -- MUST be 1; app rejects request otherwise pin_verified_at DATETIME NULL, status ENUM('PENDING','PROCESSING','COMPLETED','FAILED','CANCELLED') NOT NULL DEFAULT 'PENDING', processed_by BIGINT UNSIGNED NULL, provider_reference VARCHAR(80) NULL, failure_reason VARCHAR(255) NULL, requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, processed_at DATETIME NULL, UNIQUE KEY uq_wdr_ref (reference), KEY idx_wdr_status (status, requested_at), KEY idx_wdr_user (user_id, requested_at), CONSTRAINT fk_wdr_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_wdr_admin FOREIGN KEY (processed_by) REFERENCES admins(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 8. MARKETING CMS — BANNERS / ANNOUNCEMENTS / PROMOTIONS -- --------------------------------------------------------------------- CREATE TABLE banners ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title_en VARCHAR(150) NULL, title_rw VARCHAR(150) NULL, subtitle_en VARCHAR(255) NULL, subtitle_rw VARCHAR(255) NULL, image_media_id BIGINT UNSIGNED NOT NULL, link_url VARCHAR(255) NULL, position ENUM('HOME_TOP','HOME_MIDDLE','HOME_BOTTOM','TASKS','PROGRAMS','WITHDRAW') NOT NULL DEFAULT 'HOME_TOP', sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, starts_at DATETIME NULL, ends_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_banner_pos (position, is_active, sort_order), CONSTRAINT fk_banner_media FOREIGN KEY (image_media_id) REFERENCES media(id), CONSTRAINT fk_banner_admin FOREIGN KEY (created_by) REFERENCES admins(id) ) ENGINE=InnoDB; CREATE TABLE announcements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title_en VARCHAR(150) NOT NULL, title_rw VARCHAR(150) NOT NULL, body_en TEXT NOT NULL, body_rw TEXT NOT NULL, style ENUM('INFO','WARNING','SUCCESS','PROMO') NOT NULL DEFAULT 'INFO', placement ENUM('TOP_BAR','DASHBOARD','MODAL') NOT NULL DEFAULT 'DASHBOARD', audience ENUM('ALL','BY_LEVEL') NOT NULL DEFAULT 'ALL', min_level VARCHAR(20) NULL, is_dismissible TINYINT(1) NOT NULL DEFAULT 1, show_once TINYINT(1) NOT NULL DEFAULT 0, starts_at DATETIME NULL, ends_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_ann_active (is_active, starts_at, ends_at), CONSTRAINT fk_ann_level FOREIGN KEY (min_level) REFERENCES levels(code), CONSTRAINT fk_ann_admin FOREIGN KEY (created_by) REFERENCES admins(id) ) ENGINE=InnoDB; CREATE TABLE announcement_reads ( announcement_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (announcement_id, user_id), CONSTRAINT fk_ar_ann FOREIGN KEY (announcement_id) REFERENCES announcements(id) ON DELETE CASCADE, CONSTRAINT fk_ar_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE promotions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, title_en VARCHAR(150) NULL, title_rw VARCHAR(150) NULL, body_en TEXT NULL, body_rw TEXT NULL, image_media_id BIGINT UNSIGNED NOT NULL, cta_text_en VARCHAR(60) NULL, cta_text_rw VARCHAR(60) NULL, cta_url VARCHAR(255) NULL, placement ENUM('POPUP','CARD','SIDEBAR') NOT NULL DEFAULT 'POPUP', frequency ENUM('EVERY_LOGIN','ONCE_PER_DAY','ONCE') NOT NULL DEFAULT 'ONCE_PER_DAY', starts_at DATETIME NULL, ends_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_promo_media FOREIGN KEY (image_media_id) REFERENCES media(id), CONSTRAINT fk_promo_admin FOREIGN KEY (created_by) REFERENCES admins(id) ) ENGINE=InnoDB; CREATE TABLE promotion_views ( promotion_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, view_count INT UNSIGNED NOT NULL DEFAULT 1, last_viewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (promotion_id, user_id), CONSTRAINT fk_pv_promo FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE, CONSTRAINT fk_pv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 9. LOCALIZED MESSAGES (every success/fail response resolves from here, -- in users.locale, falling back to English) -- --------------------------------------------------------------------- CREATE TABLE message_templates ( code VARCHAR(80) PRIMARY KEY, -- e.g. auth.login.fail text_en TEXT NOT NULL, text_rw TEXT NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 10. NOTIFICATIONS, SETTINGS, AUDIT, FRAUD, CRON -- --------------------------------------------------------------------- CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, type VARCHAR(40) NOT NULL, -- TASK_APPROVED, LEVEL_UP, WITHDRAWAL... title_en VARCHAR(150) NOT NULL, title_rw VARCHAR(150) NOT NULL, body_en VARCHAR(500) NOT NULL, body_rw VARCHAR(500) NOT NULL, related_type VARCHAR(40) NULL, related_id BIGINT UNSIGNED NULL, read_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_notif_user (user_id, read_at), CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE app_settings ( setting_key VARCHAR(80) PRIMARY KEY, setting_value VARCHAR(255) NOT NULL, description VARCHAR(255) NULL, updated_by BIGINT UNSIGNED NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_type ENUM('ADMIN','USER','SYSTEM') NOT NULL, actor_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, entity VARCHAR(40) NULL, entity_id BIGINT UNSIGNED NULL, before_json JSON NULL, after_json JSON NULL, ip_address VARCHAR(45) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_audit (entity, entity_id), KEY idx_audit_actor (actor_type, actor_id) ) ENGINE=InnoDB; CREATE TABLE fraud_flags ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, flag_type VARCHAR(60) NOT NULL, -- SAME_DEVICE, SELF_REFERRAL, RAPID_WITHDRAW... severity ENUM('LOW','MEDIUM','HIGH') NOT NULL DEFAULT 'LOW', details VARCHAR(500) NULL, status ENUM('OPEN','REVIEWED','CLOSED') NOT NULL DEFAULT 'OPEN', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_fraud (status, severity), CONSTRAINT fk_fraud_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB; -- Daily idempotency for scheduled jobs (task generation, benefits, expiries) CREATE TABLE cron_runs ( job_name VARCHAR(60) NOT NULL, ran_date DATE NOT NULL, details VARCHAR(255) NULL, PRIMARY KEY (job_name, ran_date) ) ENGINE=InnoDB; -- ===================================================================== -- SEED DATA -- ===================================================================== INSERT INTO levels (code, tier, name_en, name_rw, min_active_referrals, monthly_benefit_rwf) VALUES ('STUDENT', 0, 'Student', 'Umunyeshuri', 0, 0.00), ('TEACHER', 1, 'Teacher', 'Mwarimu', 3, 0.00), ('GOAT', 2, 'GOAT', 'Ihene (GOAT)', 20, 10000.00), ('LEOPARD', 3, 'LEOPARD', 'Ingwe (LEOPARD)',100, 50000.00), ('LION', 4, 'LION', 'Intare (LION)', 150, 150000.00); INSERT INTO referral_rates (referrer_level, depth, percent) VALUES ('STUDENT', 1, 2.00), ('TEACHER', 1, 10.00), ('TEACHER', 2, 5.00), ('TEACHER', 3, 3.00), ('GOAT', 1, 10.00), ('GOAT', 2, 5.00), ('GOAT', 3, 3.00), ('LEOPARD', 1, 10.00), ('LEOPARD', 2, 5.00), ('LEOPARD', 3, 3.00), ('LION', 1, 10.00), ('LION', 2, 5.00), ('LION', 3, 3.00); INSERT INTO task_categories (code, name_en, name_rw, icon) VALUES ('DATA', 'Data Entry', 'Kwinjiza Amakuru', 'keyboard'), ('SURVEY', 'Surveys', 'Ibijyisho', 'clipboard'), ('TESTING', 'App Testing', 'Kugerageza App', 'phone'), ('SOCIAL', 'Social Tasks', 'Akazi ku Mbuga', 'share'); -- Admin created WITHOUT a usable password. Generate a bcrypt/argon2 hash -- in your application and run the UPDATE below immediately after setup. INSERT INTO admins (username, password_hash, full_name, role) VALUES ('superadmin', '$2y$10$DISABLED.SET.THIS.VIA.APPLICATION.UPDATE', 'Super Admin', 'SUPER_ADMIN'); -- UPDATE admins SET password_hash = '' WHERE username = 'superadmin'; INSERT INTO app_settings (setting_key, setting_value, description) VALUES ('default_locale', 'en', 'Fallback language for all messages'), ('supported_locales', 'en,rw', 'Enabled languages'), ('withdrawal.min_amount', '2000', 'Minimum withdrawal in RWF'), ('withdrawal.fee_percent', '1.00', 'Withdrawal fee percent'), ('pin.max_attempts', '5', 'PIN attempts before temporary lock'), ('active_user.tasks_30d', '10', 'Tasks completed in last 30 days to count as active referral'), ('referral.basis', 'VERIFIED_TASK_EARNINGS', 'Commissions calculated on approved task earnings only'); INSERT INTO message_templates (code, text_en, text_rw) VALUES ('auth.login.success', 'Welcome back, {name}!', 'Murakaza neza, {name}!'), ('auth.login.fail', 'Incorrect phone number or password.', 'Nimero ya telefoni cyangwa ijambo ry''ibanga sibyo.'), ('auth.register.success', 'Account created successfully. Welcome!', 'Konti yakozwe neza. Murakaza neza!'), ('auth.register.fail', 'Registration failed. This phone number may already be registered.', 'Byanze. Iyi nimero ishobora kuba isanzye ifite konti.'), ('pin.set.success', 'Transaction PIN set successfully.', 'PIN y''ibikorwa yashyizweho neza.'), ('pin.invalid', 'Incorrect transaction PIN.', 'PIN y''ibikorwa itemewe.'), ('withdrawal.request.success','Withdrawal request submitted successfully.', 'Gusaba kubikuza byoherejwe neza.'), ('withdrawal.request.fail', 'Withdrawal failed. Check your available balance and transaction PIN.', 'Kubikuza byanze. Reba amafaranga ufite na PIN yawe.'), ('task.submit.success', 'Task submitted. It is now under review.', 'Akazi kanyujijwe. Kiri ku isuzuma.'), ('task.approved', 'Task approved! {amount} RWF has been credited.', 'Akazi kemejwe! Amafaranga {amount} RWF yongewe.'), ('program.enroll.success', 'You have joined the program successfully.', 'Winjiye muri gahunda neza.'), ('language.switch.success', 'Language changed successfully.', 'Ururimi rwahinduwe neza.'), ('language.switch.fail', 'Could not change language. Please try again.', 'Hindura ururimi byanze. Ongera ugerageze.'); -- Sample tasks (bilingual) INSERT INTO tasks (category_id, title_en, title_rw, description_en, description_rw, reward_rwf, required_proof) VALUES (1, 'Enter 20 records into the sheet', 'Injiza ibyanditswe 20 muri urupapuro', 'Enter the 20 records assigned to your account and submit a screenshot.', 'Injiza ibyanditswe 20 byagenewe konti yawe, hanyuma ohereza screenshot.', 250.00, 'SCREENSHOT'), (2, 'Complete a 5-minute survey', 'Uzuza ibijyisho by''aminute 5', 'Complete the survey link and submit the completion code.', 'Uzuza links y''ibijyisho, hanyuma ohereza kode yo kurangiza.', 300.00, 'CODE'); -- Sample banner placeholder (attach media after first upload) -- INSERT INTO banners (title_en, title_rw, image_media_id, position, sort_order) -- VALUES ('Welcome', 'Murakaza neza', 1, 'HOME_TOP', 1);