384 lines
17 KiB
PHP
Executable File
384 lines
17 KiB
PHP
Executable File
<?php
|
|
/**
|
|
* ══════════════════════════════════════════════════════════════
|
|
* OFFER QUALITY GUARD — Zero-Tolerance Pre-Send Validation
|
|
* ══════════════════════════════════════════════════════════════
|
|
* RULES (INVIOLABLE):
|
|
* 1. NEVER send expired offer or offer without live tracking
|
|
* 2. NEVER send without a creative — each offer has its own
|
|
* 3. NEVER mix creatives between offers
|
|
* 4. NEVER send to someone on offer's suppression list
|
|
* 5. NEVER send if tracking doesn't redirect to sponsor
|
|
* 6. NEVER send without sub IDs (sponsor attribution)
|
|
* 7. Match offers to audience ISP/country/history
|
|
* 8. Auto-refresh links every 3 days
|
|
* ══════════════════════════════════════════════════════════════
|
|
*/
|
|
|
|
class OfferQualityGuard {
|
|
private $db;
|
|
private $tracking_domain;
|
|
private $errors = [];
|
|
|
|
public function __construct($db, $tracking_domain = 'culturellemejean.charity') {
|
|
$this->db = $db;
|
|
$this->tracking_domain = $tracking_domain;
|
|
}
|
|
|
|
/**
|
|
* SELECT a valid offer for sending — ALL rules enforced
|
|
* @param string $target_isp ISP of recipient (GMX, T-ONLINE, OUTLOOK...)
|
|
* @param string $recipient Recipient email
|
|
* @param string $country Target country (DE, FR, UK...)
|
|
* @return array|null {offer, creative, subject, from_name, link, full_url} or null
|
|
*/
|
|
public function selectOffer($target_isp, $recipient, $country = 'DE') {
|
|
$this->errors = [];
|
|
|
|
// RULE 4: Check global suppression first
|
|
if ($this->isGlobalSuppressed($recipient)) {
|
|
$this->errors[] = "BLOCKED: $recipient in global suppression";
|
|
return null;
|
|
}
|
|
|
|
// Get eligible offers (active, approved, live links, has creatives)
|
|
$offers = $this->getEligibleOffers($target_isp, $country);
|
|
if (empty($offers)) {
|
|
$this->errors[] = "NO_OFFERS: No eligible offers for ISP=$target_isp country=$country";
|
|
return null;
|
|
}
|
|
|
|
// Try each offer in priority order
|
|
foreach ($offers as $offer) {
|
|
// RULE 4: Check offer-specific suppression
|
|
if ($this->isOfferSuppressed($offer['id'], $recipient)) {
|
|
continue;
|
|
}
|
|
|
|
// RULE 2: Get creative for THIS offer (no mixing!)
|
|
$creative = $this->getCreative($offer['id']);
|
|
if (!$creative) {
|
|
$this->errors[] = "NO_CREATIVE: Offer #{$offer['id']} has no valid creative";
|
|
continue;
|
|
}
|
|
|
|
// RULE 3: Verify creative belongs to this offer
|
|
if ((int)$creative['offer_id'] !== (int)$offer['id']) {
|
|
$this->errors[] = "MISMATCH: Creative #{$creative['id']} doesn't belong to offer #{$offer['id']}";
|
|
continue;
|
|
}
|
|
|
|
// RULE 1: Get live tracking link
|
|
$link = $this->getLiveLink($offer['id']);
|
|
if (!$link) {
|
|
$this->errors[] = "NO_LINK: Offer #{$offer['id']} has no live tracking link";
|
|
continue;
|
|
}
|
|
|
|
// Get subject and from_name for this offer
|
|
$subject = $this->getSubject($offer['id']);
|
|
$from_name = $this->getFromName($offer['id']);
|
|
|
|
// RULE 6: Build full URL with sub IDs
|
|
$full_url = $link['value'];
|
|
|
|
return [
|
|
'offer' => $offer,
|
|
'creative' => $creative,
|
|
'subject' => $subject,
|
|
'from_name' => $from_name,
|
|
'link' => $link,
|
|
'full_url' => $full_url,
|
|
'network' => $offer['affiliate_network_name'] ?? 'unknown'
|
|
];
|
|
}
|
|
|
|
$this->errors[] = "ALL_FAILED: No offers passed all quality checks";
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* VALIDATE an offer before sending — returns true/false + errors
|
|
*/
|
|
public function validateBeforeSend($offer_id, $creative_id, $link_url, $recipient) {
|
|
$valid = true;
|
|
$checks = [];
|
|
|
|
// CHECK 1: Offer active
|
|
$r = pg_query($this->db, "SELECT status FROM affiliate.offers WHERE id=$offer_id");
|
|
$offer = pg_fetch_assoc($r);
|
|
if (!$offer || $offer['status'] !== 'Activated') {
|
|
$checks[] = ['check' => 'offer_active', 'pass' => false, 'msg' => 'Offer not active'];
|
|
$valid = false;
|
|
} else {
|
|
$checks[] = ['check' => 'offer_active', 'pass' => true];
|
|
}
|
|
|
|
// CHECK 2: Creative exists and belongs to offer
|
|
$r = pg_query($this->db, "SELECT id, offer_id FROM affiliate.creatives WHERE id=$creative_id");
|
|
$cr = pg_fetch_assoc($r);
|
|
if (!$cr || (int)$cr['offer_id'] !== (int)$offer_id) {
|
|
$checks[] = ['check' => 'creative_match', 'pass' => false, 'msg' => 'Creative mismatch'];
|
|
$valid = false;
|
|
} else {
|
|
$checks[] = ['check' => 'creative_match', 'pass' => true];
|
|
}
|
|
|
|
// CHECK 3: Recipient not suppressed
|
|
if ($this->isGlobalSuppressed($recipient) || $this->isOfferSuppressed($offer_id, $recipient)) {
|
|
$checks[] = ['check' => 'suppression', 'pass' => false, 'msg' => 'Recipient suppressed'];
|
|
$valid = false;
|
|
} else {
|
|
$checks[] = ['check' => 'suppression', 'pass' => true];
|
|
}
|
|
|
|
// CHECK 4: Link redirects (cached check)
|
|
$r = pg_query($this->db, "SELECT link_status, link_http_code FROM admin.brain_offer_config WHERE offer_id=$offer_id");
|
|
$cfg = pg_fetch_assoc($r);
|
|
if (!$cfg || $cfg['link_status'] !== 'live') {
|
|
$checks[] = ['check' => 'link_live', 'pass' => false, 'msg' => 'Link not verified live'];
|
|
$valid = false;
|
|
} else {
|
|
$checks[] = ['check' => 'link_live', 'pass' => true];
|
|
}
|
|
|
|
return ['valid' => $valid, 'checks' => $checks];
|
|
}
|
|
|
|
/**
|
|
* VALIDATE all offer links — run every 3 days or on-demand
|
|
*/
|
|
public function validateAllLinks() {
|
|
$results = [];
|
|
$r = pg_query($this->db, "SELECT o.id, o.name, l.value as link_url
|
|
FROM affiliate.offers o
|
|
JOIN affiliate.links l ON l.offer_id = o.id AND l.type = 'preview'
|
|
WHERE o.status = 'Activated'
|
|
GROUP BY o.id, o.name, l.value");
|
|
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
$http_code = $this->checkLinkLive($row['link_url']);
|
|
$is_live = in_array($http_code, [200, 204, 301, 302]);
|
|
|
|
// Update brain_offer_config
|
|
pg_query($this->db, "INSERT INTO admin.brain_offer_config (offer_id, link_status, link_http_code, link_last_checked)
|
|
VALUES ({$row['id']}, '".($is_live ? 'live' : 'dead')."', $http_code, NOW())
|
|
ON CONFLICT (offer_id) DO UPDATE SET
|
|
link_status = '".($is_live ? 'live' : 'dead')."',
|
|
link_http_code = $http_code,
|
|
link_last_checked = NOW()");
|
|
|
|
// If dead, pause the offer
|
|
if (!$is_live) {
|
|
pg_query($this->db, "UPDATE admin.brain_offer_config SET is_active = false WHERE offer_id = {$row['id']}");
|
|
}
|
|
|
|
$results[] = [
|
|
'offer_id' => $row['id'],
|
|
'name' => $row['name'],
|
|
'http_code' => $http_code,
|
|
'status' => $is_live ? 'live' : 'dead'
|
|
];
|
|
}
|
|
return $results;
|
|
}
|
|
|
|
/**
|
|
* VALIDATE all creatives — check tracking placeholders
|
|
*/
|
|
public function validateCreatives() {
|
|
$r = pg_query($this->db, "SELECT id, offer_id, value FROM affiliate.creatives WHERE status = 'Activated'");
|
|
$results = ['valid' => 0, 'invalid' => 0, 'details' => []];
|
|
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
$html = $row['value'];
|
|
// Check if it's base64 encoded
|
|
$decoded = @base64_decode($html);
|
|
if ($decoded && strlen($decoded) > 50) $html = $decoded;
|
|
|
|
$has_url = (strpos($html, '[url]') !== false || strpos($html, '{url}') !== false);
|
|
$has_unsub = (strpos($html, '[unsub]') !== false || strpos($html, '{unsub}') !== false);
|
|
$has_open = (strpos($html, '[open]') !== false || strpos($html, '{open}') !== false);
|
|
$has_html_tags = (strpos($html, '<') !== false && strpos($html, '>') !== false);
|
|
|
|
$score = 0;
|
|
if ($has_url) $score += 3;
|
|
if ($has_unsub) $score += 2;
|
|
if ($has_open) $score += 2;
|
|
if ($has_html_tags) $score += 1;
|
|
if (strlen($html) > 200) $score += 1;
|
|
if (strlen($html) > 1000) $score += 1;
|
|
|
|
$valid = ($has_url && $has_html_tags && strlen($html) > 200);
|
|
|
|
pg_query($this->db, "UPDATE affiliate.creatives SET
|
|
quality_score = $score,
|
|
has_tracking_placeholders = ".($has_url ? 'true' : 'false').",
|
|
last_validated = NOW()
|
|
WHERE id = {$row['id']}");
|
|
|
|
if ($valid) $results['valid']++;
|
|
else $results['invalid']++;
|
|
}
|
|
return $results;
|
|
}
|
|
|
|
// ── PRIVATE HELPERS ──
|
|
|
|
private function getEligibleOffers($target_isp, $country) {
|
|
$sql = "SELECT o.id, o.name, o.status, o.affiliate_network_name,
|
|
bc.priority, bc.link_status, bc.good_creatives,
|
|
bc.target_isps, bc.target_countries
|
|
FROM affiliate.offers o
|
|
JOIN admin.brain_offer_config bc ON bc.offer_id = o.id
|
|
WHERE o.status = 'Activated'
|
|
AND bc.is_active = true
|
|
AND bc.is_approved = true
|
|
AND bc.link_status = 'live'
|
|
AND bc.good_creatives > 0
|
|
ORDER BY bc.priority DESC, RANDOM()";
|
|
$r = pg_query($this->db, $sql);
|
|
$offers = [];
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
$offers[] = $row;
|
|
}
|
|
return $offers;
|
|
}
|
|
|
|
private function isGlobalSuppressed($email) {
|
|
$email = pg_escape_string($this->db, strtolower(trim($email)));
|
|
$r = pg_query($this->db, "SELECT 1 FROM admin.global_suppression WHERE email = '$email' LIMIT 1");
|
|
if ($r && pg_num_rows($r) > 0) return true;
|
|
// Also check unsubscribe tracking events
|
|
$r = pg_query($this->db, "SELECT 1 FROM admin.tracking_events WHERE event_type = 'unsub' AND tracking_id LIKE '%$email%' LIMIT 1");
|
|
return ($r && pg_num_rows($r) > 0);
|
|
}
|
|
|
|
private function isOfferSuppressed($offer_id, $email) {
|
|
$email = pg_escape_string($this->db, strtolower(trim($email)));
|
|
$r = pg_query($this->db, "SELECT 1 FROM admin.offer_suppression WHERE offer_id = $offer_id AND email = '$email' LIMIT 1");
|
|
return ($r && pg_num_rows($r) > 0);
|
|
}
|
|
|
|
private function getCreative($offer_id) {
|
|
// Only get creatives that belong to THIS offer AND have tracking placeholders
|
|
$r = pg_query($this->db, "SELECT id, offer_id, name, value FROM affiliate.creatives
|
|
WHERE offer_id = $offer_id AND status = 'Activated'
|
|
AND (has_tracking_placeholders = true OR quality_score >= 3)
|
|
ORDER BY quality_score DESC, RANDOM() LIMIT 1");
|
|
if (!$r || pg_num_rows($r) === 0) {
|
|
// Fallback: any creative for this offer
|
|
$r = pg_query($this->db, "SELECT id, offer_id, name, value FROM affiliate.creatives
|
|
WHERE offer_id = $offer_id AND status = 'Activated'
|
|
ORDER BY RANDOM() LIMIT 1");
|
|
}
|
|
return ($r && pg_num_rows($r) > 0) ? pg_fetch_assoc($r) : null;
|
|
}
|
|
|
|
private function getLiveLink($offer_id) {
|
|
$r = pg_query($this->db, "SELECT id, offer_id, value, type FROM affiliate.links
|
|
WHERE offer_id = $offer_id AND type = 'preview' AND status = 'Activated' LIMIT 1");
|
|
return ($r && pg_num_rows($r) > 0) ? pg_fetch_assoc($r) : null;
|
|
}
|
|
|
|
private function getSubject($offer_id) {
|
|
$r = pg_query($this->db, "SELECT value FROM affiliate.subjects WHERE offer_id = $offer_id ORDER BY RANDOM() LIMIT 1");
|
|
return ($r && pg_num_rows($r) > 0) ? pg_fetch_assoc($r)['value'] : null;
|
|
}
|
|
|
|
private function getFromName($offer_id) {
|
|
$r = pg_query($this->db, "SELECT value FROM affiliate.from_names WHERE offer_id = $offer_id ORDER BY RANDOM() LIMIT 1");
|
|
return ($r && pg_num_rows($r) > 0) ? pg_fetch_assoc($r)['value'] : null;
|
|
}
|
|
|
|
private function checkLinkLive($url) {
|
|
$ch = curl_init($url . 'test_validation');
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_FOLLOWLOCATION => true,
|
|
CURLOPT_TIMEOUT => 10,
|
|
CURLOPT_NOBODY => true,
|
|
]);
|
|
curl_exec($ch);
|
|
$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
|
|
curl_close($ch);
|
|
return $code;
|
|
}
|
|
|
|
public function getErrors() { return $this->errors; }
|
|
}
|
|
|
|
// ── CLI / API MODE ──
|
|
if (php_sapi_name() === 'cli' || isset($_GET['action'])) {
|
|
require_once('/opt/wevads/config/credentials.php');
|
|
$db = pg_connect("host=localhost dbname=adx_system user=admin password=".WEVADS_DB_PASS);
|
|
pg_query($db, "SET search_path TO admin, affiliate, public");
|
|
|
|
$guard = new OfferQualityGuard($db);
|
|
$action = $argv[1] ?? $_GET['action'] ?? 'status';
|
|
|
|
switch ($action) {
|
|
case 'validate_links':
|
|
$results = $guard->validateAllLinks();
|
|
echo json_encode(['action' => 'validate_links', 'results' => $results], JSON_PRETTY_PRINT);
|
|
break;
|
|
|
|
case 'validate_creatives':
|
|
$results = $guard->validateCreatives();
|
|
echo json_encode(['action' => 'validate_creatives', 'results' => $results], JSON_PRETTY_PRINT);
|
|
break;
|
|
|
|
case 'check_offer':
|
|
$offer_id = $argv[2] ?? $_GET['offer_id'] ?? null;
|
|
$recipient = $argv[3] ?? $_GET['recipient'] ?? 'test@example.com';
|
|
if (!$offer_id) { echo json_encode(['error' => 'offer_id required']); break; }
|
|
$result = $guard->selectOffer('GMX', $recipient);
|
|
echo json_encode(['result' => $result, 'errors' => $guard->getErrors()], JSON_PRETTY_PRINT);
|
|
break;
|
|
|
|
case 'status':
|
|
$r = pg_query($db, "SELECT
|
|
(SELECT COUNT(*) FROM affiliate.offers WHERE status='Activated') as active_offers,
|
|
(SELECT COUNT(*) FROM admin.brain_offer_config WHERE is_active=true AND link_status='live') as live_offers,
|
|
(SELECT COUNT(*) FROM affiliate.creatives WHERE has_tracking_placeholders=true) as good_creatives,
|
|
(SELECT COUNT(*) FROM admin.global_suppression) as global_suppressed,
|
|
(SELECT COUNT(*) FROM admin.offer_suppression) as offer_suppressed");
|
|
echo json_encode(['status' => pg_fetch_assoc($r)], JSON_PRETTY_PRINT);
|
|
break;
|
|
|
|
case 'init':
|
|
// Initial setup: validate everything
|
|
echo "Validating links...\n";
|
|
$links = $guard->validateAllLinks();
|
|
$live = count(array_filter($links, fn($l) => $l['status'] === 'live'));
|
|
echo " Links: $live live / " . count($links) . " total\n";
|
|
|
|
echo "Validating creatives...\n";
|
|
$cr = $guard->validateCreatives();
|
|
echo " Creatives: {$cr['valid']} valid / {$cr['invalid']} invalid\n";
|
|
|
|
// Update good_creatives count in brain_offer_config
|
|
pg_query($db, "UPDATE admin.brain_offer_config bc SET good_creatives = (
|
|
SELECT COUNT(*) FROM affiliate.creatives c
|
|
WHERE c.offer_id = bc.offer_id AND c.quality_score >= 3
|
|
)");
|
|
|
|
// Auto-approve offers with live links + good creatives
|
|
pg_query($db, "UPDATE admin.brain_offer_config SET is_approved = true
|
|
WHERE link_status = 'live' AND good_creatives > 0");
|
|
|
|
echo "\nReady offers:\n";
|
|
$r = pg_query($db, "SELECT bc.offer_id, o.name, bc.link_status, bc.good_creatives, bc.is_approved
|
|
FROM admin.brain_offer_config bc
|
|
JOIN affiliate.offers o ON o.id = bc.offer_id
|
|
WHERE bc.is_active = true
|
|
ORDER BY bc.priority DESC");
|
|
while ($row = pg_fetch_assoc($r)) {
|
|
$icon = $row['is_approved'] === 't' ? '✅' : '❌';
|
|
echo " $icon #{$row['offer_id']} {$row['name']} | link:{$row['link_status']} | creatives:{$row['good_creatives']}\n";
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|