getConnection();
// ===== ANTI-BOT: Rate limit verification attempts per IP =====
$clientIP = $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';
try {
// Check if IP is blocked
$stmt = $pdo->prepare("SELECT id FROM ip_blocklist WHERE ip_address = ? AND (expires_at IS NULL OR expires_at > NOW())");
$stmt->execute([$clientIP]);
if ($stmt->fetch()) {
showVerificationPage(false, 'Access Restricted', 'Verification is temporarily unavailable from your network. Please contact support.');
}
// Rate limit: max 10 verification attempts per IP per hour (prevents token brute-forcing)
$stmt = $pdo->prepare("SELECT COUNT(*) as cnt FROM registration_attempts WHERE ip_address = ? AND attempt_type = 'verification' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)");
$stmt->execute([$clientIP]);
$attempts = $stmt->fetch();
if ($attempts && $attempts['cnt'] >= 10) {
showVerificationPage(false, 'Too Many Attempts', 'Too many verification attempts. Please try again later.');
}
// Log this attempt
$pdo->prepare("INSERT INTO registration_attempts (ip_address, attempt_type, user_agent) VALUES (?, 'verification', ?)")
->execute([$clientIP, $_SERVER['HTTP_USER_AGENT'] ?? '']);
} catch (Exception $e) {
// Don't block verification if logging fails
}
// Start transaction
$pdo->beginTransaction();
// Find the verification token
$stmt = $pdo->prepare("
SELECT ev.user_id, ev.expires_at, u.email, u.email_verified
FROM email_verifications ev
JOIN users u ON ev.user_id = u.id
WHERE ev.token = ?
AND ev.expires_at > NOW()
");
$stmt->execute([$token]);
$verification = $stmt->fetch();
if (!$verification) {
// Check if token exists but is expired
$stmt = $pdo->prepare("
SELECT ev.expires_at, u.email
FROM email_verifications ev
JOIN users u ON ev.user_id = u.id
WHERE ev.token = ?
");
$stmt->execute([$token]);
$expiredToken = $stmt->fetch();
if ($expiredToken) {
showVerificationPage(false, 'Verification Link Expired', 'This verification link has expired. Please register again to receive a new verification email.');
} else {
// Token not found and not expired = it was already consumed (user already verified)
showVerificationPage(true, 'Email Already Verified', 'Your email has already been verified successfully. You can now log in to your account.');
}
}
// Check if email is already verified
if ($verification['email_verified']) {
showVerificationPage(true, 'Email Already Verified', 'Your email has already been verified. You can now log in to your account.');
}
$userId = $verification['user_id'];
// Update user status
$stmt = $pdo->prepare("
UPDATE users
SET email_verified = 1, status = 'active', updated_at = NOW()
WHERE id = ?
");
$stmt->execute([$userId]);
// Delete used verification token
$stmt = $pdo->prepare("DELETE FROM email_verifications WHERE token = ?");
$stmt->execute([$token]);
// Commit transaction
$pdo->commit();
// Log successful verification
logError('Email verification successful', [
'user_id' => $userId,
'email' => $verification['email']
]);
// ===== AFFILIATE TRACKING: Update affiliate signup tracking =====
try {
// Connect to admin database
$adminPdo = new PDO(
"mysql:host=localhost;dbname=u752449863_rrshop;charset=utf8mb4",
'u752449863_rradmin',
'S@n@h2016',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
// Update affiliate signup tracking - mark as verified
$updateStmt = $adminPdo->prepare("
UPDATE affiliate_signups
SET email_verified = 1,
verified_at = NOW()
WHERE panel_user_id = ?
AND email_verified = 0
");
$updateStmt->execute([$userId]);
$rowsAffected = $updateStmt->rowCount();
// If a row was updated, update the affiliate's total verified count
if ($rowsAffected > 0) {
// Get the affiliate_id
$affiliateStmt = $adminPdo->prepare("
SELECT affiliate_id, reward_amount
FROM affiliate_signups
WHERE panel_user_id = ?
");
$affiliateStmt->execute([$userId]);
$affiliateInfo = $affiliateStmt->fetch();
if ($affiliateInfo) {
// Update affiliate total_verified_signups count
$countStmt = $adminPdo->prepare("
UPDATE affiliates
SET total_verified_signups = (
SELECT COUNT(*)
FROM affiliate_signups
WHERE affiliate_id = ?
AND email_verified = 1
)
WHERE id = ?
");
$countStmt->execute([$affiliateInfo['affiliate_id'], $affiliateInfo['affiliate_id']]);
// Award points to the user (onboarding bonus from affiliate)
if ($affiliateInfo['reward_amount'] > 0) {
// Check if user_points record exists
$pointsCheckStmt = $pdo->prepare("SELECT id FROM user_points WHERE user_id = ?");
$pointsCheckStmt->execute([$userId]);
if ($pointsCheckStmt->fetch()) {
// Update existing points
$pointsStmt = $pdo->prepare("
UPDATE user_points
SET points = points + ?,
total_earned = total_earned + ?,
updated_at = NOW()
WHERE user_id = ?
");
$pointsStmt->execute([
$affiliateInfo['reward_amount'],
$affiliateInfo['reward_amount'],
$userId
]);
} else {
// Create new points record
$pointsStmt = $pdo->prepare("
INSERT INTO user_points (user_id, points, total_earned, created_at)
VALUES (?, ?, ?, NOW())
");
$pointsStmt->execute([
$userId,
$affiliateInfo['reward_amount'],
$affiliateInfo['reward_amount']
]);
}
// Log the transaction
$transactionStmt = $pdo->prepare("
INSERT INTO point_transactions
(user_id, transaction_type, points, source, description, status, created_at)
VALUES (?, 'earned', ?, 'affiliate_bonus', ?, 'completed', NOW())
");
$transactionStmt->execute([
$userId,
$affiliateInfo['reward_amount'],
'Affiliate signup bonus - Welcome reward for joining through our partner'
]);
}
logError('Affiliate verification tracked successfully', [
'user_id' => $userId,
'affiliate_id' => $affiliateInfo['affiliate_id'],
'reward_amount' => $affiliateInfo['reward_amount']
]);
}
}
} catch (Exception $e) {
// Log error but don't fail the verification
logError('Failed to update affiliate verification tracking', [
'error' => $e->getMessage(),
'user_id' => $userId
]);
}
// ===== END AFFILIATE TRACKING =====
// ===== MEMBER REFERRAL: Award referrer Rs.5 (10 pts) on email verification =====
try {
$mrefPdo2 = new PDO(
"mysql:host=localhost;dbname=u752449863_rrpanel;charset=utf8mb4",
'u752449863_rrpaneladmin', 'S@n@h2016',
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
);
// Find if this user was referred by a member
$stmt = $mrefPdo2->prepare("
SELECT id, referrer_user_id FROM member_referral_clicks
WHERE referee_user_id = ? AND email_verified = 0 AND signup_completed = 1
LIMIT 1
");
$stmt->execute([$userId]);
$mrefRow = $stmt->fetch();
if ($mrefRow && $mrefRow['referrer_user_id'] != $userId) {
$referrerId = $mrefRow['referrer_user_id'];
$mrefClickId = $mrefRow['id'];
$rewardPts = 10; // Rs.5 = 10 points (1 pt = Rs.0.50)
// Mark click as verified + reward paid
$mrefPdo2->prepare("
UPDATE member_referral_clicks
SET email_verified = 1, verified_at = NOW(), signup_reward_paid = 1
WHERE id = ?
")->execute([$mrefClickId]);
// Award points to referrer
$mrefPdo2->prepare("
INSERT INTO user_points (user_id, points, total_earned)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE points = points + ?, total_earned = total_earned + ?
")->execute([$referrerId, $rewardPts, $rewardPts, $rewardPts, $rewardPts]);
$mrefPdo2->prepare("
INSERT INTO point_transactions (user_id, transaction_type, points, source, description, reference_id, created_at)
VALUES (?, 'earned', ?, 'member_referral', ?, ?, NOW())
")->execute([
$referrerId,
$rewardPts,
'Referral reward: your friend verified their email (₹5 = 10 pts)',
$userId
]);
}
} catch (Exception $e) {
logError('Member referral verify reward error: ' . $e->getMessage());
}
// ===== END MEMBER REFERRAL =====
showVerificationPage(
true,
'Email Verified Successfully!',
'Your email has been verified successfully. Your account is now active and you can start taking surveys and earning rewards!'
);
} catch (PDOException $e) {
// Rollback transaction on error
if ($pdo->inTransaction()) {
$pdo->rollback();
}
logError('Database error during email verification', [
'error' => $e->getMessage(),
'token' => substr($token, 0, 10) . '...' // Log partial token for debugging
]);
showVerificationPage(
false,
'Verification Failed',
'We encountered a system error while verifying your email. Please try again later or contact support.'
);
} catch (Exception $e) {
// Rollback transaction on error
if (isset($pdo) && $pdo->inTransaction()) {
$pdo->rollback();
}
logError('General error during email verification', [
'error' => $e->getMessage(),
'token' => substr($token, 0, 10) . '...'
]);
showVerificationPage(
false,
'Verification Failed',
'An unexpected error occurred. Please try again later or contact support.'
);
}
function showVerificationPage($success, $title, $message) {
$statusClass = $success ? 'success' : 'error';
$statusColor = $success ? '#28a745' : '#dc3545';
$iconClass = $success ? 'fa-check-circle' : 'fa-times-circle';
$iconColor = $success ? '#28a745' : '#dc3545';
?>
<?php echo htmlspecialchars($title); ?> - Relevant Reflex
What's Next?
- Log in with your registered email and password
- Complete your profile to get targeted surveys
- Start participating in surveys and earn rewards
- Redeem your points for real money via UPI
Need Help?
- Check your spam/junk folder for the verification email
- Make sure you clicked the complete link from the email
- Contact our support team at support@relevantreflex.com
- Our team responds within 24 hours
FILE: RelevantReflexCom/assets/css/bootstrap.min.css