134 lines
7.2 KiB
PHP
Executable File
134 lines
7.2 KiB
PHP
Executable File
<?php
|
|
header('Content-Type: application/json');
|
|
header('Access-Control-Allow-Origin: *');
|
|
$db = pg_connect("host=localhost dbname=adx_system user=admin password=admin123") or die(json_encode(['error'=>'DB']));
|
|
pg_query($db, "SET search_path TO admin,public");
|
|
$action = $_GET['action'] ?? $_POST['action'] ?? 'dashboard';
|
|
$platform = $_GET['platform'] ?? $_POST['platform'] ?? '';
|
|
|
|
switch($action) {
|
|
|
|
case 'dashboard':
|
|
// Global multi-platform dashboard
|
|
$accounts = pg_fetch_all(pg_query($db, "SELECT platform, COUNT(*) as cnt, SUM(total_spent) as spent, SUM(daily_spend_cap) as budget, AVG(health_score) as health FROM ads_accounts WHERE is_active=true GROUP BY platform ORDER BY spent DESC")) ?: [];
|
|
$campaigns = pg_fetch_all(pg_query($db, "SELECT platform, COUNT(*) as total, COUNT(*) FILTER(WHERE status='active') as active, SUM(impressions) as impr, SUM(total_clicks) as clicks, SUM(spend) as spend, SUM(revenue) as revenue, SUM(conversions) as conv FROM campaigns WHERE platform != 'email' GROUP BY platform ORDER BY revenue DESC")) ?: [];
|
|
$flux = pg_fetch_all(pg_query($db, "SELECT platform, metric, value FROM ads_flux WHERE period='hourly' ORDER BY platform, metric")) ?: [];
|
|
|
|
$totals = ['accounts'=>0,'campaigns'=>0,'spend'=>0,'revenue'=>0,'impressions'=>0,'clicks'=>0,'conversions'=>0];
|
|
foreach($campaigns as $c) {
|
|
$totals['campaigns'] += $c['total'];
|
|
$totals['spend'] += $c['spend'];
|
|
$totals['revenue'] += $c['revenue'];
|
|
$totals['impressions'] += $c['impr'];
|
|
$totals['clicks'] += $c['clicks'];
|
|
$totals['conversions'] += $c['conv'];
|
|
}
|
|
foreach($accounts as $a) $totals['accounts'] += $a['cnt'];
|
|
$totals['roi'] = $totals['spend'] > 0 ? round(($totals['revenue'] - $totals['spend']) / $totals['spend'] * 100, 1) : 0;
|
|
|
|
// Format flux by platform
|
|
$fluxMap = [];
|
|
foreach($flux as $f) {
|
|
$fluxMap[$f['platform']][$f['metric']] = (float)$f['value'];
|
|
}
|
|
|
|
echo json_encode(['status'=>'success','totals'=>$totals,'platforms'=>$campaigns,'accounts'=>$accounts,'flux'=>$fluxMap]);
|
|
break;
|
|
|
|
case 'platform':
|
|
if(!$platform) { echo json_encode(['error'=>'platform required']); break; }
|
|
$accs = pg_fetch_all(pg_query($db, "SELECT * FROM ads_accounts WHERE platform='".pg_escape_string($db,$platform)."' ORDER BY id")) ?: [];
|
|
$camps = pg_fetch_all(pg_query($db, "SELECT * FROM campaigns WHERE platform='".pg_escape_string($db,$platform)."' ORDER BY revenue DESC")) ?: [];
|
|
$flux = pg_fetch_all(pg_query($db, "SELECT metric, value FROM ads_flux WHERE platform='".pg_escape_string($db,$platform)."' AND period='hourly'")) ?: [];
|
|
$personas = pg_fetch_all(pg_query($db, "SELECT p.id, p.first_name, p.last_name, p.email FROM personas p WHERE p.is_active=true ORDER BY p.id LIMIT 20")) ?: [];
|
|
|
|
$fluxMap = [];
|
|
foreach($flux as $f) $fluxMap[$f['metric']] = (float)$f['value'];
|
|
|
|
$totalSpend = array_sum(array_column($camps, 'spend'));
|
|
$totalRevenue = array_sum(array_column($camps, 'revenue'));
|
|
$totalConv = array_sum(array_column($camps, 'conversions'));
|
|
$totalClicks = array_sum(array_column($camps, 'total_clicks'));
|
|
$totalImpr = array_sum(array_column($camps, 'impressions'));
|
|
|
|
echo json_encode([
|
|
'status'=>'success',
|
|
'platform'=>$platform,
|
|
'accounts'=>$accs,
|
|
'campaigns'=>$camps,
|
|
'flux'=>$fluxMap,
|
|
'personas'=>$personas,
|
|
'stats'=>[
|
|
'accounts'=>count($accs),
|
|
'campaigns'=>count($camps),
|
|
'active_campaigns'=>count(array_filter($camps, fn($c)=>$c['status']==='active')),
|
|
'spend'=>round($totalSpend,2),
|
|
'revenue'=>round($totalRevenue,2),
|
|
'conversions'=>$totalConv,
|
|
'clicks'=>$totalClicks,
|
|
'impressions'=>$totalImpr,
|
|
'roi'=>$totalSpend > 0 ? round(($totalRevenue-$totalSpend)/$totalSpend*100,1) : 0,
|
|
'ctr'=>$totalImpr > 0 ? round($totalClicks/$totalImpr*100,2) : 0,
|
|
'cpa'=>$totalConv > 0 ? round($totalSpend/$totalConv,2) : 0
|
|
]
|
|
]);
|
|
break;
|
|
|
|
case 'gare_flux':
|
|
// Real-time flux for Gare — each platform separately
|
|
$flux = pg_fetch_all(pg_query($db, "SELECT platform, metric, value FROM ads_flux WHERE period='hourly' ORDER BY platform")) ?: [];
|
|
$platforms = [];
|
|
foreach($flux as $f) {
|
|
$platforms[$f['platform']][$f['metric']] = (float)$f['value'];
|
|
}
|
|
// Also get campaign counts
|
|
$camps = pg_fetch_all(pg_query($db, "SELECT platform, COUNT(*) FILTER(WHERE status='active') as active FROM campaigns WHERE platform != 'email' GROUP BY platform")) ?: [];
|
|
foreach($camps as $c) {
|
|
if(isset($platforms[$c['platform']])) $platforms[$c['platform']]['active_campaigns'] = (int)$c['active'];
|
|
}
|
|
echo json_encode(['status'=>'success','flux'=>$platforms,'timestamp'=>date('H:i:s')]);
|
|
break;
|
|
|
|
case 'update_flux':
|
|
// Simulate real-time flux updates (called by cron or auto-refresh)
|
|
$platforms = ['youtube','meta','google','tiktok','linkedin','twitter','snapchat','pinterest','native'];
|
|
foreach($platforms as $p) {
|
|
$base = pg_fetch_assoc(pg_query($db, "SELECT value FROM ads_flux WHERE platform='$p' AND metric='impressions_h' AND period='hourly'"));
|
|
if(!$base) continue;
|
|
$baseVal = (float)$base['value'];
|
|
// Random variation ±15%
|
|
$var = $baseVal * (0.85 + (mt_rand(0,30)/100));
|
|
pg_query($db, "UPDATE ads_flux SET value=$var, recorded_at=NOW() WHERE platform='$p' AND metric='impressions_h' AND period='hourly'");
|
|
// Update clicks proportionally
|
|
$ctr = mt_rand(150,350)/10000; // 1.5-3.5% CTR
|
|
pg_query($db, "UPDATE ads_flux SET value=".round($var*$ctr).", recorded_at=NOW() WHERE platform='$p' AND metric='clicks_h' AND period='hourly'");
|
|
}
|
|
echo json_encode(['status'=>'success','updated'=>count($platforms),'timestamp'=>date('H:i:s')]);
|
|
break;
|
|
|
|
case 'create_campaign':
|
|
$data = json_decode(file_get_contents('php://input'), true) ?: $_POST;
|
|
$name = pg_escape_string($db, $data['name'] ?? 'New Campaign');
|
|
$plat = pg_escape_string($db, $data['platform'] ?? 'meta');
|
|
$budget = (float)($data['budget_daily'] ?? 50);
|
|
$status = pg_escape_string($db, $data['status'] ?? 'draft');
|
|
$r = pg_query($db, "INSERT INTO campaigns (name, platform, status, budget_daily) VALUES ('$name','$plat','$status',$budget) RETURNING id");
|
|
$row = pg_fetch_assoc($r);
|
|
echo json_encode(['status'=>'success','campaign_id'=>$row['id']]);
|
|
break;
|
|
|
|
case 'create_account':
|
|
$data = json_decode(file_get_contents('php://input'), true) ?: $_POST;
|
|
$plat = pg_escape_string($db, $data['platform'] ?? '');
|
|
$accId = pg_escape_string($db, $data['account_id'] ?? 'new_'.time());
|
|
$name = pg_escape_string($db, $data['account_name'] ?? 'New Account');
|
|
$cap = (float)($data['daily_spend_cap'] ?? 50);
|
|
$r = pg_query($db, "INSERT INTO ads_accounts (platform, account_id, account_name, daily_spend_cap) VALUES ('$plat','$accId','$name',$cap) ON CONFLICT DO NOTHING RETURNING id");
|
|
$row = pg_fetch_assoc($r);
|
|
echo json_encode(['status'=>'success','id'=>$row['id'] ?? null]);
|
|
break;
|
|
|
|
default:
|
|
echo json_encode(['status'=>'error','message'=>'Unknown action','actions'=>['dashboard','platform','gare_flux','update_flux','create_campaign','create_account']]);
|
|
}
|