131 lines
8.9 KiB
PHP
Executable File
131 lines
8.9 KiB
PHP
Executable File
<?php
|
|
header("Content-Type: application/json");
|
|
$db = new PDO('pgsql:host=localhost;dbname=adx_system', 'admin', 'admin123');
|
|
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
$action = $_GET["action"] ?? $_POST["action"] ?? "status";
|
|
|
|
try {
|
|
switch($action) {
|
|
case "status":
|
|
echo json_encode(["status"=>"success","service"=>"profit-orchestrator","version"=>"2.0","actions"=>["dashboard","e2e_pipeline","conversions","isp_rates","campaigns","record","postback","report","top_offers"]]);
|
|
break;
|
|
|
|
case "dashboard":
|
|
// ONLY count REAL conversions (not test)
|
|
$today_real = $db->query("SELECT COALESCE(SUM(revenue),0) as revenue, COALESCE(SUM(conversions),0) as convs FROM admin.revenue WHERE date=CURRENT_DATE AND (source='real' OR source IS NULL) AND source != 'test'")->fetch(PDO::FETCH_ASSOC);
|
|
$today_test = $db->query("SELECT COALESCE(SUM(revenue),0) as revenue, COALESCE(SUM(conversions),0) as convs FROM admin.revenue WHERE date=CURRENT_DATE AND source='test'")->fetch(PDO::FETCH_ASSOC);
|
|
$today_all = $db->query("SELECT COALESCE(SUM(revenue),0) as revenue, COALESCE(SUM(conversions),0) as convs, COALESCE(SUM(cost),0) as cost, COALESCE(SUM(profit),0) as profit FROM admin.revenue WHERE date=CURRENT_DATE")->fetch(PDO::FETCH_ASSOC);
|
|
|
|
$week_real = $db->query("SELECT COALESCE(SUM(revenue),0) as revenue, COALESCE(SUM(conversions),0) as convs FROM admin.revenue WHERE date >= CURRENT_DATE - INTERVAL '7 days' AND source != 'test'")->fetch(PDO::FETCH_ASSOC);
|
|
|
|
// Pipeline stats
|
|
$sends_today = 0;
|
|
try { $sends_today = $db->query("SELECT COUNT(*) FROM unified_send_log_new WHERE created_at::date = CURRENT_DATE AND status='sent'")->fetchColumn(); } catch(Exception $e) {}
|
|
|
|
$clicks_today = 0;
|
|
try { $clicks_today = $db->query("SELECT COUNT(*) FROM admin.tracking_events WHERE event_type='click' AND created_at::date = CURRENT_DATE")->fetchColumn(); } catch(Exception $e) {}
|
|
|
|
$opens_today = 0;
|
|
try { $opens_today = $db->query("SELECT COUNT(*) FROM admin.tracking_events WHERE event_type='open' AND created_at::date = CURRENT_DATE")->fetchColumn(); } catch(Exception $e) {}
|
|
|
|
// Top offers by REAL revenue only
|
|
$top = $db->query("SELECT network, offer_id, offer_name, SUM(revenue) as rev, SUM(conversions) as convs FROM admin.revenue WHERE source != 'test' AND conversions > 0 GROUP BY network, offer_id, offer_name ORDER BY rev DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Recent conversions with source indicator
|
|
$recent = $db->query("SELECT sub1, offer_id, offer_name, network, payout, source, transaction_id, ip_address, converted_at FROM admin.conversion_log ORDER BY converted_at DESC LIMIT 20")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
echo json_encode([
|
|
"status"=>"success",
|
|
"real_revenue" => ["revenue" => floatval($today_real['revenue']), "conversions" => intval($today_real['convs'])],
|
|
"test_revenue" => ["revenue" => floatval($today_test['revenue']), "conversions" => intval($today_test['convs'])],
|
|
"all_revenue" => $today_all,
|
|
"week_real" => $week_real,
|
|
"pipeline" => ["sends" => intval($sends_today), "opens" => intval($opens_today), "clicks" => intval($clicks_today)],
|
|
"top_offers" => $top,
|
|
"recent_conversions" => $recent
|
|
]);
|
|
break;
|
|
|
|
case "e2e_pipeline":
|
|
// Full E2E visibility: sends → opens → clicks → conversions → revenue
|
|
$pipeline = [];
|
|
|
|
// Sends by method
|
|
try {
|
|
$pipeline['sends'] = $db->query("SELECT send_method as method, COUNT(*) as count, COUNT(CASE WHEN status='sent' THEN 1 END) as success FROM unified_send_log_new WHERE created_at::date = CURRENT_DATE GROUP BY send_method")->fetchAll(PDO::FETCH_ASSOC);
|
|
} catch(Exception $e) { $pipeline['sends'] = []; }
|
|
|
|
// Opens/Clicks from tracking
|
|
try {
|
|
$pipeline['tracking'] = $db->query("SELECT event_type, COUNT(*) as count FROM admin.tracking_events WHERE created_at::date = CURRENT_DATE GROUP BY event_type")->fetchAll(PDO::FETCH_ASSOC);
|
|
} catch(Exception $e) { $pipeline['tracking'] = []; }
|
|
|
|
// Conversions by source
|
|
$pipeline['conversions'] = $db->query("SELECT source, network, COUNT(*) as count, SUM(payout) as revenue FROM admin.conversion_log WHERE converted_at::date = CURRENT_DATE GROUP BY source, network ORDER BY source, revenue DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Revenue breakdown
|
|
$pipeline['revenue'] = $db->query("SELECT source, network, offer_id, offer_name, conversions, revenue FROM admin.revenue WHERE date = CURRENT_DATE ORDER BY source, revenue DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
echo json_encode(["status"=>"success","pipeline"=>$pipeline]);
|
|
break;
|
|
|
|
case "conversions":
|
|
// Detailed conversion log with source filtering
|
|
$source_filter = $_GET['source'] ?? 'all'; // all, real, test
|
|
$where = "";
|
|
if ($source_filter === 'real') $where = "AND source = 'real'";
|
|
if ($source_filter === 'test') $where = "AND source = 'test'";
|
|
|
|
$rows = $db->query("SELECT * FROM admin.conversion_log WHERE converted_at >= CURRENT_DATE - INTERVAL '30 days' $where ORDER BY converted_at DESC LIMIT 100")->fetchAll(PDO::FETCH_ASSOC);
|
|
echo json_encode(["status"=>"success","source_filter"=>$source_filter,"count"=>count($rows),"data"=>$rows]);
|
|
break;
|
|
|
|
case "isp_rates":
|
|
// Clean ISP deliverability rates
|
|
$rates = $db->query("SELECT isp_name, ROUND(success_rate::numeric, 2) as inbox_rate, max_volume_hour, best_send_hours, last_updated FROM admin.brain_isp_profiles ORDER BY success_rate DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
echo json_encode(["status"=>"success","count"=>count($rates),"data"=>$rates,
|
|
"note"=>"Inbox rates from seed testing. 0% = no data yet, not necessarily bad delivery."]);
|
|
break;
|
|
|
|
case "campaigns":
|
|
$rows = $db->query("SELECT * FROM admin.campaign_profit ORDER BY date DESC LIMIT 100")->fetchAll(PDO::FETCH_ASSOC);
|
|
echo json_encode(["status"=>"success","count"=>count($rows),"data"=>$rows]);
|
|
break;
|
|
|
|
case "record":
|
|
$d = json_decode(file_get_contents("php://input"), true) ?: $_POST;
|
|
$rev=floatval($d["revenue"]??0); $cost=floatval($d["cost"]??0); $clicks=intval($d["clicks"]??0);
|
|
$profit=$rev-$cost; $roas=$cost>0?round($rev/$cost,2):0; $epc=$clicks>0?round($rev/$clicks,4):0;
|
|
$stmt = $db->prepare("INSERT INTO admin.campaign_profit (campaign_id,campaign_name,network,offer_id,sends,opens,clicks,conversions,revenue,cost,profit,roas,epc,date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING id");
|
|
$stmt->execute([$d["campaign_id"]??0,$d["campaign_name"]??"",$d["network"]??"",$d["offer_id"]??"",$d["sends"]??0,$d["opens"]??0,$clicks,$d["conversions"]??0,$rev,$cost,$profit,$roas,$epc,$d["date"]??date("Y-m-d")]);
|
|
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
echo json_encode(["status"=>"success","id"=>$row["id"],"profit"=>$profit,"roas"=>$roas,"epc"=>$epc]);
|
|
break;
|
|
|
|
case "postback":
|
|
$p=$_GET;
|
|
$stmt = $db->prepare("INSERT INTO admin.affiliate_conversions (click_id,offer_id,network,payout,sub_id,ip_address,country,status,postback_data) VALUES (?,?,?,?,?,?,?,'confirmed',?) RETURNING id");
|
|
$stmt->execute([$p["click_id"]??"",$p["offer_id"]??"",$p["network"]??"",$p["payout"]??0,$p["sub_id"]??"",$_SERVER["REMOTE_ADDR"]??"",$p["country"]??"",json_encode($p)]);
|
|
$row = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
echo json_encode(["status"=>"success","id"=>$row["id"]]);
|
|
break;
|
|
|
|
case "report":
|
|
$from=$_GET["from"]??date("Y-m-d",strtotime("-30 days")); $to=$_GET["to"]??date("Y-m-d");
|
|
$source_filter = $_GET['source'] ?? 'real';
|
|
$where_source = $source_filter === 'all' ? '' : "AND source = '$source_filter'";
|
|
$stmt = $db->prepare("SELECT network, SUM(conversions) as convs, SUM(revenue) as revenue FROM admin.revenue WHERE date BETWEEN ? AND ? $where_source GROUP BY network ORDER BY revenue DESC");
|
|
$stmt->execute([$from,$to]);
|
|
echo json_encode(["status"=>"success","from"=>$from,"to"=>$to,"source"=>$source_filter,"data"=>$stmt->fetchAll(PDO::FETCH_ASSOC)]);
|
|
break;
|
|
|
|
case "top_offers":
|
|
$rows = $db->query("SELECT offer_id, network, SUM(conversions) as convs, SUM(revenue) as rev FROM admin.revenue WHERE source != 'test' AND conversions > 0 GROUP BY offer_id, network ORDER BY rev DESC LIMIT 20")->fetchAll(PDO::FETCH_ASSOC);
|
|
echo json_encode(["status"=>"success","data"=>$rows]);
|
|
break;
|
|
|
|
default:
|
|
echo json_encode(["error"=>"Unknown action","actions"=>["status","dashboard","e2e_pipeline","conversions","isp_rates","campaigns","record","postback","report","top_offers"]]);
|
|
}
|
|
} catch(Exception $e) { echo json_encode(["error"=>$e->getMessage()]); }
|