­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ Campaign DETAILS export * - ?endpoint=ad => Ad DETAILS export * - ?endpoint=adsquad => Ad Squad DETAILS export * - ?endpoint=creative => Creative DETAILS export * - ?endpoint=insights => Ad-level DAY stats export * * REQUIRED POST: * AUTH, from=YYYY-MM-DD, to=YYYY-MM-DD * * REQUIRED ENV: * APP_AUTH * SNAP_AD_ACCOUNT_ID * SNAP_CLIENT_ID * SNAP_CLIENT_SECRET * SNAP_REFRESH_TOKEN * * OPTIONAL ENV: * SNAP_ACCESS_TOKEN * SNAP_TOKEN_FILE=__DIR__/snap_token.json * SNAP_FORCE_REFRESH=0 * * NOTES: * - Auto refreshes OAuth access token before API calls * - Saves latest token payload to SNAP_TOKEN_FILE * - Retries once on 401/expired token by refreshing token */ date_default_timezone_set('Asia/Riyadh'); $yesterday = new DateTime('yesterday'); $dttt= $yesterday->format('Y-m-d'); $_POST['from'] = $dttt; $_POST['to'] = $dttt; header('Content-Type: application/json; charset=utf-8'); const MULTIPLIER = 2.5; const BRAND_FILTER = 'honda'; const API_BASE = 'https://adsapi.snapchat.com/v1'; const OAUTH_BASE = 'https://accounts.snapchat.com/login/oauth2/access_token'; const MICRO_DIVISOR = 1000000; $EXPECTED_AUTH = getenv('APP_AUTH') ?: 'a2h1cnJhbS5kaGVkaGlAY29yZTNjb25zdWx0YW5jeS5jb206QkdDamVkZGFoQDY5'; if (($_POST['AUTH'] ?? '') !== $EXPECTED_AUTH) { http_response_code(401); echo json_encode(['ok' => false, 'error' => 'Authentication Error'], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $ENDPOINT_MODE = strtolower(trim($_GET['endpoint'] ?? 'insights')); if (!in_array($ENDPOINT_MODE, ['campaign', 'ad', 'adsquad', 'creative', 'insights'], true)) { http_response_code(400); echo json_encode(['ok' => false, 'error' => 'Invalid endpoint. Use campaign|ad|adsquad|creative|insights'], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $SINCE = $_POST['from'] ?? ''; $UNTIL = $_POST['to'] ?? ''; if ($SINCE === '' || $UNTIL === '') { http_response_code(400); echo json_encode(['ok' => false, 'error' => 'Missing from/to date. Send POST: from=YYYY-MM-DD&to=YYYY-MM-DD'], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** ------------------- CONFIG ------------------- **/ $AD_ACCOUNT_ID = getenv('SNAP_AD_ACCOUNT_ID') ?: '24123e2e-9e9d-49e4-94fb-8bbbab193a90'; $SNAP_CLIENT_ID = getenv('SNAP_CLIENT_ID') ?: 'eb3e8220-17dd-4ac1-9562-2cce320b183b'; $SNAP_CLIENT_SECRET = getenv('SNAP_CLIENT_SECRET') ?: 'c050f694bab269a697e5'; $SNAP_REFRESH_TOKEN = getenv('SNAP_REFRESH_TOKEN') ?: 'hCgwKCjE3NzA1OTg4MTgSpQE2ifl3ougd5TiFjOOaDVvOCaI_4oOGCfp3UdJ5AenC-6_xt-WdhPjIQ_3d5N8fndN9Hyicguv38ehh_P2mHMHOudqg_MLdztCVUUrZjO-IbdcDdFh5vq5IYxt4bsJUgf2mvh2sy9ZznYlJ552LwWB6mceEguTaJwJeoU53jRa39M93JLOjuCsE_xCQrh5-gnEduTwhEoNcOM7ko5NFeV0DGSwagtQ'; $SNAP_ACCESS_TOKEN = getenv('SNAP_ACCESS_TOKEN') ?: 'hCgwKCjE3NzU0MzcyMDASzgHQDgq6DtfLFwvh9Isw-DE1sYJWm0qQNBWAyibH9VoQ2wjaEh-GhJqiR8dBQPzbyHBEwz1o9UeY7T-RwrBIHNE53bX6tw0Al9GCAWTGZHG--06JMLUSN0VmK1hNWI3YuMZliDWh4pbON8fFQCejJuXdVKmskyOqfouJMMlO2Tz-unOIsDGTl1mE43VL6b-wHkyjUiQJdWySdaVIJcjZF3e0XiN2Qo0vZmtTzDniqCJez2QqDQhsP_KNttwyv7IuSI14LRSgQszsUyy2PCR0eQ'; $SNAP_TOKEN_FILE = getenv('SNAP_TOKEN_FILE') ?: ('snap_token.json'); $SNAP_FORCE_REFRESH = (getenv('SNAP_FORCE_REFRESH') ?: '0') === '1'; $DB_HOST = getenv('DB_HOST') ?: 'localhost'; $DB_NAME = getenv('DB_NAME') ?: 'balubaid_marketing_insights'; $DB_USER = getenv('DB_USER') ?: 'balubaid_marketing_insights'; $DB_PASS = getenv('DB_PASS') ?: 'Vision@2050'; $DB_PORT = getenv('DB_PORT') ?: '3306'; if ($AD_ACCOUNT_ID === '' || $SNAP_CLIENT_ID === '' || $SNAP_CLIENT_SECRET === '' || $SNAP_REFRESH_TOKEN === '') { http_response_code(500); echo json_encode([ 'ok' => false, 'error' => 'Missing one or more required env vars: SNAP_AD_ACCOUNT_ID, SNAP_CLIENT_ID, SNAP_CLIENT_SECRET, SNAP_REFRESH_TOKEN' ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $RUN_ID = date('Ymd_His') . '_' . bin2hex(random_bytes(4)); /** ------------------- HELPERS ------------------- **/ function export_dashboard_rows(string $dataType, string $dataDate, array $data, string $adAccountId): array { $rows = []; foreach ($data as $r) { $rows[] = [ 'source_system' => 'snapchat', 'data_type' => $dataType, 'data_date' => $r['data_date'] ?? $dataDate, 'account_id' => $adAccountId, 'campaign_id' => $r['campaign_id'] ?? null, 'campaign_name' => $r['campaign_name'] ?? null, 'adsquad_id' => $r['adsquad_id'] ?? null, 'adsquad_name' => $r['adsquad_name'] ?? null, 'ad_id' => $r['ad_id'] ?? null, 'ad_name' => $r['ad_name'] ?? null, 'creative_id' => $r['creative_id'] ?? null, 'currency' => $r['currency'] ?? null, 'impressions' => isset($r['impressions']) ? (int)$r['impressions'] : null, 'swipes' => isset($r['swipes']) ? (int)$r['swipes'] : null, 'spend' => isset($r['spend']) ? (float)$r['spend'] : null, 'video_views' => isset($r['video_views']) ? (int)$r['video_views'] : null, 'quartile_1' => isset($r['quartile_1']) ? (int)$r['quartile_1'] : null, 'quartile_2' => isset($r['quartile_2']) ? (int)$r['quartile_2'] : null, 'quartile_3' => isset($r['quartile_3']) ? (int)$r['quartile_3'] : null, 'view_completion' => isset($r['view_completion']) ? (int)$r['view_completion'] : null, 'screen_time_millis' => isset($r['screen_time_millis']) ? (int)$r['screen_time_millis'] : null, 'leads' => isset($r['leads']) ? (int)$r['leads'] : null, 'sign_ups' => isset($r['sign_ups']) ? (int)$r['sign_ups'] : null, 'cpc' => isset($r['cpc']) ? (float)$r['cpc'] : null, 'cpm' => isset($r['cpm']) ? (float)$r['cpm'] : null, 'cpv' => isset($r['cpv']) ? (float)$r['cpv'] : null, 'cpl' => isset($r['cpl']) ? (float)$r['cpl'] : null, 'cpsu' => isset($r['cpsu']) ? (float)$r['cpsu'] : null ]; } return $rows; } function strtolower_safe($v): string { return strtolower((string)$v); } function is_brand_campaign(?string $campaignName): bool { $n = strtolower_safe($campaignName); return ($n !== '' && strpos($n, BRAND_FILTER) !== false); } function to_float($v): ?float { if ($v === null || $v === '') return null; if (is_numeric($v)) return (float)$v; return null; } function to_int($v): ?int { if ($v === null || $v === '') return null; if (is_numeric($v)) return (int)$v; return null; } function micro_to_currency($v): ?float { $f = to_float($v); if ($f === null) return null; return $f / MICRO_DIVISOR; } function today_date(): string { return date('Y-m-d'); } function iso_now_micro(): string { $micro = microtime(true); $dt = DateTime::createFromFormat('U.u', sprintf('%.6f', $micro)); if (!$dt) return date('Y-m-d\TH:i:s'); return $dt->format('Y-m-d\TH:i:s.u'); } function export_payload(string $dataType, string $dataDate, array $data): array { return [ 'extraction_time' => iso_now_micro(), 'run_date' => today_date(), 'data_date' => $dataDate, 'source_system' => 'snapchat', 'data_type' => $dataType, 'record_count' => count($data), 'data' => array_values($data), ]; } function token_expiring_soon(array $token): bool { $expiresAt = (int)($token['expires_at'] ?? 0); if ($expiresAt <= 0) return true; return time() >= ($expiresAt - 300); // refresh 5 mins early } function read_token_file(string $path): ?array { if (!is_file($path)) return null; $raw = file_get_contents($path); if ($raw === false || $raw === '') return null; $json = json_decode($raw, true); return is_array($json) ? $json : null; } function write_token_file(string $path, array $token): void { $dir = dirname($path); if (!is_dir($dir)) mkdir($dir, 0755, true); file_put_contents($path, json_encode($token, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); } /** ------------------- LOGGER ------------------- **/ class Logger { private string $logDir; private string $runId; public function __construct(string $logDir, string $runId) { $this->logDir = rtrim($logDir, '/'); $this->runId = $runId; if (!is_dir($this->logDir)) mkdir($this->logDir, 0755, true); } public function info(string $message, array $context = []): void { $this->write('INFO', $message, $context); } public function error(string $message, array $context = []): void { $this->write('ERROR', $message, $context); } private function write(string $level, string $message, array $context): void { $file = $this->logDir . '/snap_ads_' . date('Ymd') . '.log'; $line = [ 'ts' => date('Y-m-d H:i:s'), 'level' => $level, 'run_id' => $this->runId, 'msg' => $message, 'ctx' => $context ]; file_put_contents($file, json_encode($line, JSON_UNESCAPED_UNICODE) . PHP_EOL, FILE_APPEND); } } /** ------------------- SNAP TOKEN MANAGER ------------------- **/ class SnapTokenManager { private string $clientId; private string $clientSecret; private string $refreshToken; private string $tokenFile; private ?Logger $logger; public function __construct(string $clientId, string $clientSecret, string $refreshToken, string $tokenFile, ?Logger $logger = null) { $this->clientId = $clientId; $this->clientSecret = $clientSecret; $this->refreshToken = $refreshToken; $this->tokenFile = $tokenFile; $this->logger = $logger; } public function getAccessToken(?string $envAccessToken = null, bool $forceRefresh = false): string { $stored = read_token_file($this->tokenFile); if (!$forceRefresh && $stored && !token_expiring_soon($stored) && !empty($stored['access_token'])) { return (string)$stored['access_token']; } if (!$forceRefresh && !$stored && $envAccessToken !== null && $envAccessToken !== '') { return $envAccessToken; } $newToken = $this->refreshAccessToken(); return (string)$newToken['access_token']; } public function refreshAccessToken(): array { $ch = curl_init(OAUTH_BASE); curl_setopt_array($ch, [ CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => 60, CURLOPT_CONNECTTIMEOUT => 15, CURLOPT_SSL_VERIFYPEER => true, CURLOPT_POST => true, CURLOPT_POSTFIELDS => http_build_query([ 'client_id' => $this->clientId, 'client_secret' => $this->clientSecret, 'grant_type' => 'refresh_token', 'refresh_token' => $this->refreshToken, ]), CURLOPT_HTTPHEADER => [ 'Accept: application/json', 'Content-Type: application/x-www-form-urlencoded', ], ]); $body = curl_exec($ch); $errno = curl_errno($ch); $err = curl_error($ch); $http = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); if ($errno) { throw new RuntimeException("Snap token refresh cURL error ({$errno}): {$err}"); } if ($body === false || $body === '') { throw new RuntimeException("Empty token refresh response (HTTP {$http})"); } $json = json_decode($body, true); if (!is_array($json)) { throw new RuntimeException("Invalid token refresh JSON (HTTP {$http}): " . substr($body, 0, 500)); } if ($http >= 400 || isset($json['error'])) { $msg = $json['error_description'] ?? $json['error'] ?? ('HTTP ' . $http); throw new RuntimeException("Snap token refresh failed: {$msg}"); } if (empty($json['access_token'])) { throw new RuntimeException("Snap token refresh failed: access_token missing"); } $token = [ 'access_token' => (string)$json['access_token'], 'token_type' => $json['token_type'] ?? 'Bearer', 'expires_in' => (int)($json['expires_in'] ?? 3600), 'expires_at' => time() + (int)($json['expires_in'] ?? 3600), 'refresh_token' => (string)($json['refresh_token'] ?? $this->refreshToken), 'refreshed_at' => date('c'), ]; write_token_file($this->tokenFile, $token); if ($this->logger) { $this->logger->info('SNAP_TOKEN_REFRESHED', [ 'expires_in' => $token['expires_in'], 'token_file' => $this->tokenFile, ]); } return $token; } } /** ------------------- SNAP CLIENT ------------------- **/ class SnapClient { private string $accessToken; private int $timeoutSec; private ?SnapTokenManager $tokenManager; private ?Logger $logger; public function __construct(string $accessToken, int $timeoutSec = 60, ?SnapTokenManager $tokenManager = null, ?Logger $logger = null) { $this->accessToken = $accessToken; $this->timeoutSec = $timeoutSec; $this->tokenManager = $tokenManager; $this->logger = $logger; } public function setAccessToken(string $accessToken): void { $this->accessToken = $accessToken; } public function listAdsByCampaign(string $campaignId): array { $url = API_BASE . '/campaigns/' . rawurlencode($campaignId) . '/ads?limit=200&sort=updated_at-desc'; return $this->getAllPages($url, 'ads'); } private function request(string $method, string $url, ?array $payload = null, bool $allowRetry = true): array { $ch = curl_init($url); $headers = [ 'Accept: application/json', 'Authorization: Bearer ' . $this->accessToken, ]; $opts = [ CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => $this->timeoutSec, CURLOPT_CONNECTTIMEOUT => 15, CURLOPT_SSL_VERIFYPEER => true, CURLOPT_CUSTOMREQUEST => strtoupper($method), CURLOPT_HTTPHEADER => $headers, ]; if ($payload !== null) { $headers[] = 'Content-Type: application/json'; $opts[CURLOPT_HTTPHEADER] = $headers; $opts[CURLOPT_POSTFIELDS] = json_encode($payload, JSON_UNESCAPED_UNICODE); } curl_setopt_array($ch, $opts); $body = curl_exec($ch); $errno = curl_errno($ch); $err = curl_error($ch); $http = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); if ($errno) throw new RuntimeException("cURL error ({$errno}): {$err}"); if ($body === false || $body === '') throw new RuntimeException("Empty response (HTTP {$http})"); $json = json_decode($body, true); if (!is_array($json)) { throw new RuntimeException("Invalid JSON response (HTTP {$http}): " . substr($body, 0, 500)); } $status = strtoupper((string)($json['request_status'] ?? '')); $authExpired = ( $http === 401 || stripos((string)($json['debug_message'] ?? ''), 'token') !== false || stripos((string)($json['error'] ?? ''), 'token') !== false ); if (($http >= 400 || ($status !== '' && $status !== 'SUCCESS')) && $allowRetry && $authExpired && $this->tokenManager) { if ($this->logger) { $this->logger->info('SNAP_TOKEN_RETRY', ['url' => $url, 'http' => $http]); } $token = $this->tokenManager->refreshAccessToken(); $this->accessToken = (string)$token['access_token']; return $this->request($method, $url, $payload, false); } if ($http >= 400 || ($status !== '' && $status !== 'SUCCESS')) { $msg = $json['debug_message'] ?? $json['error'] ?? ('HTTP ' . $http); throw new RuntimeException("Snap API error: {$msg}"); } return $json; } private function getAllPages(string $url, string $collectionKey): array { $all = []; $next = $url; while ($next) { $resp = $this->request('GET', $next); $rows = $resp[$collectionKey] ?? []; if (!is_array($rows)) { throw new RuntimeException("Expected key '{$collectionKey}' not found in response."); } foreach ($rows as $item) { $all[] = $item; } $next = $resp['paging']['next_link'] ?? null; } return $all; } public function getAdAccount(string $adAccountId): ?array { $url = API_BASE . '/adaccounts/' . rawurlencode($adAccountId); $resp = $this->request('GET', $url); $items = $resp['adaccounts'] ?? []; if (!is_array($items) || empty($items[0]['adaccount'])) return null; return $items[0]['adaccount']; } public function listCampaigns(string $adAccountId): array { $url = API_BASE . '/adaccounts/' . rawurlencode($adAccountId) . '/campaigns?limit=200&sort=updated_at-desc'; return $this->getAllPages($url, 'campaigns'); } public function listAdsquads(string $adAccountId): array { $url = API_BASE . '/adaccounts/' . rawurlencode($adAccountId) . '/adsquads?limit=200&sort=updated_at-desc&return_placement_v2=true'; return $this->getAllPages($url, 'adsquads'); } public function listAds(string $adAccountId): array { $url = API_BASE . '/adaccounts/' . rawurlencode($adAccountId) . '/ads?limit=200&sort=updated_at-desc'; return $this->getAllPages($url, 'ads'); } public function listCreatives(string $adAccountId): array { $url = API_BASE . '/adaccounts/' . rawurlencode($adAccountId) . '/creatives?limit=200'; return $this->getAllPages($url, 'creatives'); } public function getAdStatsDay(string $adId, string $since, string $until, array $fields, string $accountTimezone = 'Asia/Riyadh'): array { $tz = new DateTimeZone($accountTimezone); // Start at local start of day $start = new DateTime($since . ' 00:00:00', $tz); // End at next local start of day (exclusive) $end = new DateTime($until . ' 00:00:00', $tz); $end->modify('+1 day'); $params = http_build_query([ 'granularity' => 'DAY', 'start_time' => $start->format('Y-m-d\TH:i:sP'), 'end_time' => $end->format('Y-m-d\TH:i:sP'), 'fields' => implode(',', $fields), ]); $url = API_BASE . '/ads/' . rawurlencode($adId) . '/stats?' . $params; return $this->request('GET', $url); } } /** ------------------- REPO ------------------- **/ class Repo { private PDO $pdo; public function __construct(PDO $pdo) { $this->pdo = $pdo; $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } public function ensureTables(): void { $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_snap_campaign_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(64) NOT NULL, campaign_id VARCHAR(64) NOT NULL, campaign_name VARCHAR(255) NULL, objective VARCHAR(128) NULL, status VARCHAR(64) NULL, start_time DATETIME NULL, end_time DATETIME NULL, created_at_api DATETIME NULL, updated_at_api DATETIME NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_campaign (ad_account_id, campaign_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_snap_adsquad_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(64) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adsquad_id VARCHAR(64) NOT NULL, adsquad_name VARCHAR(255) NULL, status VARCHAR(64) NULL, optimization_goal VARCHAR(128) NULL, bid_strategy VARCHAR(128) NULL, start_time DATETIME NULL, end_time DATETIME NULL, created_at_api DATETIME NULL, updated_at_api DATETIME NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_adsquad (ad_account_id, adsquad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_snap_ad_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(64) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adsquad_id VARCHAR(64) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, status VARCHAR(64) NULL, review_status VARCHAR(64) NULL, creative_id VARCHAR(64) NULL, created_at_api DATETIME NULL, updated_at_api DATETIME NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ad (ad_account_id, ad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_snap_creative_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(64) NOT NULL, creative_id VARCHAR(64) NOT NULL, creative_name VARCHAR(255) NULL, type VARCHAR(128) NULL, headline VARCHAR(255) NULL, brand_name VARCHAR(255) NULL, call_to_action VARCHAR(128) NULL, shareable_preview_url TEXT NULL, web_view_url TEXT NULL, created_at_api DATETIME NULL, updated_at_api DATETIME NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_creative (ad_account_id, creative_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_snap_ad_insights ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(64) NOT NULL, data_date DATE NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adsquad_id VARCHAR(64) NULL, adsquad_name VARCHAR(255) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, creative_id VARCHAR(64) NULL, currency VARCHAR(16) NULL, impressions BIGINT NULL, swipes BIGINT NULL, spend DECIMAL(18,6) NULL, paid_impressions BIGINT NULL, ecpm DECIMAL(18,6) NULL, video_views BIGINT NULL, quartile_1 BIGINT NULL, quartile_2 BIGINT NULL, quartile_3 BIGINT NULL, view_completion BIGINT NULL, screen_time_millis BIGINT NULL, cpc DECIMAL(18,6) NULL, cpm DECIMAL(18,6) NULL, cpv DECIMAL(18,6) NULL, raw_json JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_insight (ad_account_id, data_date, ad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); } private function toSqlDateTime(?string $iso): ?string { if (!$iso) return null; $ts = strtotime($iso); if ($ts === false) return null; return date('Y-m-d H:i:s', $ts); } public function upsertCampaignDetails(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_snap_campaign_details (run_id, ad_account_id, campaign_id, campaign_name, objective, status, start_time, end_time, created_at_api, updated_at_api, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :objective, :status, :start_time, :end_time, :created_at_api, :updated_at_api, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_name=VALUES(campaign_name), objective=VALUES(objective), status=VALUES(status), start_time=VALUES(start_time), end_time=VALUES(end_time), created_at_api=VALUES(created_at_api), updated_at_api=VALUES(updated_at_api), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => (string)($r['id'] ?? ''), ':campaign_name' => $r['name'] ?? null, ':objective' => $r['objective'] ?? null, ':status' => $r['status'] ?? null, ':start_time' => $this->toSqlDateTime($r['start_time'] ?? null), ':end_time' => $this->toSqlDateTime($r['end_time'] ?? null), ':created_at_api' => $this->toSqlDateTime($r['created_at'] ?? null), ':updated_at_api' => $this->toSqlDateTime($r['updated_at'] ?? null), ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertAdsquadDetails(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_snap_adsquad_details (run_id, ad_account_id, campaign_id, campaign_name, adsquad_id, adsquad_name, status, optimization_goal, bid_strategy, start_time, end_time, created_at_api, updated_at_api, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :adsquad_id, :adsquad_name, :status, :optimization_goal, :bid_strategy, :start_time, :end_time, :created_at_api, :updated_at_api, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adsquad_name=VALUES(adsquad_name), status=VALUES(status), optimization_goal=VALUES(optimization_goal), bid_strategy=VALUES(bid_strategy), start_time=VALUES(start_time), end_time=VALUES(end_time), created_at_api=VALUES(created_at_api), updated_at_api=VALUES(updated_at_api), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adsquad_id' => (string)($r['id'] ?? ''), ':adsquad_name' => $r['name'] ?? null, ':status' => $r['status'] ?? null, ':optimization_goal' => $r['optimization_goal'] ?? null, ':bid_strategy' => $r['bid_strategy'] ?? null, ':start_time' => $this->toSqlDateTime($r['start_time'] ?? null), ':end_time' => $this->toSqlDateTime($r['end_time'] ?? null), ':created_at_api' => $this->toSqlDateTime($r['created_at'] ?? null), ':updated_at_api' => $this->toSqlDateTime($r['updated_at'] ?? null), ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertAdDetails(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_snap_ad_details (run_id, ad_account_id, campaign_id, campaign_name, adsquad_id, ad_id, ad_name, status, review_status, creative_id, created_at_api, updated_at_api, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :adsquad_id, :ad_id, :ad_name, :status, :review_status, :creative_id, :created_at_api, :updated_at_api, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adsquad_id=VALUES(adsquad_id), ad_name=VALUES(ad_name), status=VALUES(status), review_status=VALUES(review_status), creative_id=VALUES(creative_id), created_at_api=VALUES(created_at_api), updated_at_api=VALUES(updated_at_api), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adsquad_id' => $r['ad_squad_id'] ?? null, ':ad_id' => (string)($r['id'] ?? ''), ':ad_name' => $r['name'] ?? null, ':status' => $r['status'] ?? null, ':review_status' => $r['review_status'] ?? null, ':creative_id' => $r['creative_id'] ?? null, ':created_at_api' => $this->toSqlDateTime($r['created_at'] ?? null), ':updated_at_api' => $this->toSqlDateTime($r['updated_at'] ?? null), ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertCreativeDetails(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_snap_creative_details (run_id, ad_account_id, creative_id, creative_name, type, headline, brand_name, call_to_action, shareable_preview_url, web_view_url, created_at_api, updated_at_api, raw_json) VALUES (:run_id, :ad_account_id, :creative_id, :creative_name, :type, :headline, :brand_name, :call_to_action, :shareable_preview_url, :web_view_url, :created_at_api, :updated_at_api, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), creative_name=VALUES(creative_name), type=VALUES(type), headline=VALUES(headline), brand_name=VALUES(brand_name), call_to_action=VALUES(call_to_action), shareable_preview_url=VALUES(shareable_preview_url), web_view_url=VALUES(web_view_url), created_at_api=VALUES(created_at_api), updated_at_api=VALUES(updated_at_api), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':creative_id' => (string)($r['id'] ?? ''), ':creative_name' => $r['name'] ?? null, ':type' => $r['type'] ?? null, ':headline' => $r['headline'] ?? null, ':brand_name' => $r['brand_name'] ?? null, ':call_to_action' => $r['call_to_action'] ?? null, ':shareable_preview_url' => $r['shareable_preview_url'] ?? null, ':web_view_url' => $r['web_view_properties']['url'] ?? null, ':created_at_api' => $this->toSqlDateTime($r['created_at'] ?? null), ':updated_at_api' => $this->toSqlDateTime($r['updated_at'] ?? null), ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } public function upsertInsights(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_snap_ad_insights (run_id, ad_account_id, data_date, campaign_id, campaign_name, adsquad_id, adsquad_name, ad_id, ad_name, creative_id, currency, impressions, swipes, spend, paid_impressions, ecpm, video_views, quartile_1, quartile_2, quartile_3, view_completion, screen_time_millis, leads, sign_ups, cpc, cpm, cpv, cpl, cpsu, raw_json) VALUES (:run_id, :ad_account_id, :data_date, :campaign_id, :campaign_name, :adsquad_id, :adsquad_name, :ad_id, :ad_name, :creative_id, :currency, :impressions, :swipes, :spend, :paid_impressions, :ecpm, :video_views, :quartile_1, :quartile_2, :quartile_3, :view_completion, :screen_time_millis, :leads, :sign_ups, :cpc, :cpm, :cpv, :cpl, :cpsu, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adsquad_id=VALUES(adsquad_id), adsquad_name=VALUES(adsquad_name), ad_name=VALUES(ad_name), creative_id=VALUES(creative_id), currency=VALUES(currency), impressions=VALUES(impressions), swipes=VALUES(swipes), spend=VALUES(spend), paid_impressions=VALUES(paid_impressions), ecpm=VALUES(ecpm), video_views=VALUES(video_views), quartile_1=VALUES(quartile_1), quartile_2=VALUES(quartile_2), quartile_3=VALUES(quartile_3), view_completion=VALUES(view_completion), screen_time_millis=VALUES(screen_time_millis), cpc=VALUES(cpc), cpm=VALUES(cpm), cpv=VALUES(cpv), leads=VALUES(leads), sign_ups=VALUES(sign_ups), cpl=VALUES(cpl), cpsu=VALUES(cpsu), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':data_date' => $r['data_date'], ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adsquad_id' => $r['adsquad_id'] ?? null, ':adsquad_name' => $r['adsquad_name'] ?? null, ':ad_id' => (string)$r['ad_id'], ':ad_name' => $r['ad_name'] ?? null, ':creative_id' => $r['creative_id'] ?? null, ':currency' => $r['currency'] ?? null, ':impressions' => $r['impressions'] ?? null, ':swipes' => $r['swipes'] ?? null, ':spend' => $r['spend'] ?? null, ':paid_impressions' => $r['paid_impressions'] ?? null, ':ecpm' => $r['ecpm'] ?? null, ':video_views' => $r['video_views'] ?? null, ':quartile_1' => $r['quartile_1'] ?? null, ':quartile_2' => $r['quartile_2'] ?? null, ':quartile_3' => $r['quartile_3'] ?? null, ':view_completion' => $r['view_completion'] ?? null, ':screen_time_millis' => $r['screen_time_millis'] ?? null, ':leads' => $r['leads'] ?? null, ':sign_ups' => $r['sign_ups'] ?? null, ':cpl' => $r['cpl'] ?? null, ':cpsu' => $r['cpsu'] ?? null, ':cpc' => $r['cpc'] ?? null, ':cpm' => $r['cpm'] ?? null, ':cpv' => $r['cpv'] ?? null, ':raw_json' => json_encode($r, JSON_UNESCAPED_UNICODE), ]); $count++; } $this->pdo->commit(); } catch (Throwable $e) { $this->pdo->rollBack(); throw $e; } return $count; } } /** ------------------- RUN ------------------- **/ $logger = new Logger(__DIR__ . '/logs', $RUN_ID); try { $dsn = "mysql:host={$DB_HOST};port={$DB_PORT};dbname={$DB_NAME};charset=utf8mb4"; $pdo = new PDO($dsn, $DB_USER, $DB_PASS, [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]); $repo = new Repo($pdo); $repo->ensureTables(); $tokenManager = new SnapTokenManager( $SNAP_CLIENT_ID, $SNAP_CLIENT_SECRET, $SNAP_REFRESH_TOKEN, $SNAP_TOKEN_FILE, $logger ); $resolvedAccessToken = $tokenManager->getAccessToken($SNAP_ACCESS_TOKEN, $SNAP_FORCE_REFRESH); $client = new SnapClient($resolvedAccessToken, 60, $tokenManager, $logger); $accountMeta = $client->getAdAccount($AD_ACCOUNT_ID); $currency = $accountMeta['currency'] ?? null; $accountName = $accountMeta['name'] ?? null; $outDir = __DIR__ . '/output'; if (!is_dir($outDir)) mkdir($outDir, 0755, true); if ($ENDPOINT_MODE === 'campaign') { $raw = $client->listCampaigns($AD_ACCOUNT_ID); $rows = []; foreach ($raw as $item) { $campaign = $item['campaign'] ?? null; if (!is_array($campaign)) continue; if (!is_brand_campaign($campaign['name'] ?? null)) continue; $rows[] = $campaign; } $repo->upsertCampaignDetails($RUN_ID, $AD_ACCOUNT_ID, $rows); $final = []; foreach ($rows as $r) { $final[] = [ 'id' => $r['id'] ?? null, 'name' => $r['name'] ?? null, 'status' => $r['status'] ?? null, 'objective' => $r['objective'] ?? null, 'start_time' => $r['start_time'] ?? null, 'end_time' => $r['end_time'] ?? null, 'created_time' => $r['created_at'] ?? null, 'updated_time' => $r['updated_at'] ?? null, 'account_id' => $AD_ACCOUNT_ID, 'account_name' => $accountName, ]; } $payload = export_payload('campaigns', $UNTIL, $final); file_put_contents($outDir . "/snap_campaigns_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } function campaign_in_last_30_days(array $campaign): bool { $sinceTs = strtotime('-35 days'); $nowTs = time(); $startTs = !empty($campaign['start_time']) ? strtotime($campaign['start_time']) : null; $endTs = !empty($campaign['end_time']) ? strtotime($campaign['end_time']) : null; $updatedTs = !empty($campaign['updated_at']) ? strtotime($campaign['updated_at']) : null; // Campaign active within last 30 days if ($startTs && $startTs <= $nowTs && (!$endTs || $endTs >= $sinceTs)) { return true; } // Recently updated campaign if ($updatedTs && $updatedTs >= $sinceTs) { return true; } return false; } $campaignRaw = $client->listCampaigns($AD_ACCOUNT_ID); $campaignMap = []; foreach ($campaignRaw as $item) { $campaign = $item['campaign'] ?? null; if (!is_array($campaign) || empty($campaign['id'])) { continue; } // Brand filter if (!is_brand_campaign($campaign['name'] ?? null)) { continue; } // Last 30 days filter if (!campaign_in_last_30_days($campaign)) { continue; } $campaignMap[(string)$campaign['id']] = $campaign; } if ($ENDPOINT_MODE === 'adsquad') { $raw = $client->listAdsquads($AD_ACCOUNT_ID); $rows = []; foreach ($raw as $item) { $adsquad = $item['adsquad'] ?? null; if (!is_array($adsquad)) continue; $campaignId = (string)($adsquad['campaign_id'] ?? ''); $campaignName = $campaignMap[$campaignId]['name'] ?? null; if (!is_brand_campaign($campaignName)) continue; $adsquad['campaign_name'] = $campaignName; $rows[] = $adsquad; } $repo->upsertAdsquadDetails($RUN_ID, $AD_ACCOUNT_ID, $rows); $final = []; foreach ($rows as $r) { $final[] = [ 'id' => $r['id'] ?? null, 'name' => $r['name'] ?? null, 'status' => $r['status'] ?? null, 'campaign_id' => $r['campaign_id'] ?? null, 'campaign_name' => $r['campaign_name'] ?? null, 'optimization_goal' => $r['optimization_goal'] ?? null, 'bid_strategy' => $r['bid_strategy'] ?? null, 'start_time' => $r['start_time'] ?? null, 'end_time' => $r['end_time'] ?? null, 'created_time' => $r['created_at'] ?? null, 'updated_time' => $r['updated_at'] ?? null, 'account_id' => $AD_ACCOUNT_ID, 'account_name' => $accountName, ]; } $payload = export_payload('adsquads', $UNTIL, $final); file_put_contents($outDir . "/snap_adsquads_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $adsRaw = $client->listAds($AD_ACCOUNT_ID); $adsRows = []; foreach ($campaignMap as $campaignId => $campaign) { $campaignName = $campaign['name'] ?? null; // filter ONLY by campaign name if (!is_brand_campaign($campaignName)) continue; $campaignAds = $client->listAdsByCampaign($campaignId); foreach ($campaignAds as $item) { $ad = $item['ad'] ?? null; if (!is_array($ad)) continue; // force campaign linkage from the parent campaign we already know $ad['campaign_id'] = $campaignId; $ad['campaign_name'] = $campaignName; $ad['creative_id'] = $ad['creative_id'] ?? ($ad['creative']['id'] ?? null); $adsRows[] = $ad; } } $debug = [ 'campaign_count' => count($campaignMap), 'ads_after_campaign_name_filter' => count($adsRows), ]; if ($ENDPOINT_MODE === 'ad') { $repo->upsertAdDetails($RUN_ID, $AD_ACCOUNT_ID, $adsRows); $final = []; foreach ($adsRows as $r) { $final[] = [ 'id' => $r['id'] ?? null, 'name' => $r['name'] ?? null, 'status' => $r['status'] ?? null, 'review_status' => $r['review_status'] ?? null, 'ad_squad_id' => $r['ad_squad_id'] ?? null, 'campaign_id' => $r['campaign_id'] ?? null, 'campaign_name' => $r['campaign_name'] ?? null, 'creative_id' => $r['creative_id'] ?? null, 'created_time' => $r['created_at'] ?? null, 'updated_time' => $r['updated_at'] ?? null, 'account_id' => $AD_ACCOUNT_ID, 'account_name' => $accountName, ]; } $payload = export_payload('ads', $UNTIL, $final); file_put_contents($outDir . "/snap_ads_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } if ($ENDPOINT_MODE === 'creative') { $creativeRaw = $client->listCreatives($AD_ACCOUNT_ID); $usedCreativeIds = []; foreach ($adsRows as $ad) { if (!empty($ad['creative_id'])) $usedCreativeIds[(string)$ad['creative_id']] = true; } $rows = []; foreach ($creativeRaw as $item) { $creative = $item['creative'] ?? null; if (!is_array($creative)) continue; $creativeId = (string)($creative['id'] ?? ''); if ($creativeId === '' || !isset($usedCreativeIds[$creativeId])) continue; $rows[] = $creative; } $repo->upsertCreativeDetails($RUN_ID, $AD_ACCOUNT_ID, $rows); $final = []; foreach ($rows as $r) { $final[] = [ 'id' => $r['id'] ?? null, 'name' => $r['name'] ?? null, 'type' => $r['type'] ?? null, 'headline' => $r['headline'] ?? null, 'brand_name' => $r['brand_name'] ?? null, 'call_to_action' => $r['call_to_action'] ?? null, 'shareable_preview_url' => $r['shareable_preview_url'] ?? null, 'web_view_url' => $r['web_view_properties']['url'] ?? null, 'account_id' => $AD_ACCOUNT_ID, 'account_name' => $accountName, ]; } $payload = export_payload('creatives', $UNTIL, $final); file_put_contents($outDir . "/snap_creatives_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } $adsquadRaw = $client->listAdsquads($AD_ACCOUNT_ID); $adsquadMap = []; foreach ($adsquadRaw as $item) { $adsquad = $item['adsquad'] ?? null; if (!is_array($adsquad) || empty($adsquad['id'])) continue; $adsquadMap[(string)$adsquad['id']] = $adsquad; } $fields = [ 'impressions', 'swipes', 'spend', 'video_views', 'quartile_1', 'quartile_2', 'quartile_3', 'view_completion', 'screen_time_millis', 'native_leads', 'conversion_sign_ups' ]; $insightRows = []; foreach ($adsRows as $ad) { $adId = (string)($ad['id'] ?? ''); if ($adId === '') continue; $accountTimezone = $accountMeta['timezone'] ?? 'Asia/Riyadh'; $statsResp = $client->getAdStatsDay( $adId, $SINCE, $UNTIL, $fields, $accountTimezone ); $timeseriesStats = $statsResp['timeseries_stats'] ?? []; if (!is_array($timeseriesStats) || empty($timeseriesStats[0]['timeseries_stat']['timeseries'])) { continue; } $series = $timeseriesStats[0]['timeseries_stat']['timeseries']; $campaignId = (string)($ad['campaign_id'] ?? ''); $adsquadId = (string)($ad['ad_squad_id'] ?? ''); $adsquad = $adsquadMap[$adsquadId] ?? []; foreach ($series as $point) { $stats = $point['stats'] ?? []; if (!is_array($stats)) continue; $spend = micro_to_currency($stats['spend'] ?? null); if ($spend !== null) $spend *= MULTIPLIER; $impressions = to_int($stats['impressions'] ?? null); $swipes = to_int($stats['swipes'] ?? null); $videoViews = to_int($stats['video_views'] ?? null); $leads = to_int($stats['native_leads'] ?? null); $signUps = to_int($stats['conversion_sign_ups'] ?? null); $cpc = ($spend !== null && $swipes !== null && $swipes > 0) ? ($spend / $swipes) : null; $cpm = ($spend !== null && $impressions !== null && $impressions > 0) ? (($spend / $impressions) * 1000) : null; $cpv = ($spend !== null && $videoViews !== null && $videoViews > 0) ? ($spend / $videoViews) : null; $cpl = ($spend !== null && $leads !== null && $leads > 0) ? ($spend / $leads) : null; $cpsu = ($spend !== null && $signUps !== null && $signUps > 0) ? ($spend / $signUps) : null; $insightRows[] = [ 'data_date' => substr((string)($point['start_time'] ?? ''), 0, 10), 'campaign_id' => $campaignId ?: null, 'campaign_name' => $ad['campaign_name'] ?? null, 'adsquad_id' => $adsquadId ?: null, 'adsquad_name' => $adsquad['name'] ?? null, 'ad_id' => $adId, 'ad_name' => $ad['name'] ?? null, 'creative_id' => $ad['creative_id'] ?? null, 'currency' => $currency, 'impressions' => $impressions, 'swipes' => $swipes, 'spend' => $spend, 'video_views' => $videoViews, 'quartile_1' => to_int($stats['quartile_1'] ?? null), 'quartile_2' => to_int($stats['quartile_2'] ?? null), 'quartile_3' => to_int($stats['quartile_3'] ?? null), 'view_completion' => to_int($stats['view_completion'] ?? null), 'screen_time_millis' => to_int($stats['screen_time_millis'] ?? null), // ✅ NEW FIELDS 'leads' => $leads, 'sign_ups' => $signUps, // KPIs 'cpc' => $cpc, 'cpm' => $cpm, 'cpv' => $cpv, 'cpl' => $cpl, 'cpsu' => $cpsu, ]; } } $repo->upsertAdDetails($RUN_ID, $AD_ACCOUNT_ID, $adsRows); $repo->upsertInsights($RUN_ID, $AD_ACCOUNT_ID, $insightRows); $payload = export_dashboard_rows('insights', $UNTIL, $insightRows, $AD_ACCOUNT_ID); file_put_contents( $outDir . "/snap_insights_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) ); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } catch (Throwable $e) { try { $logger->error('FAILED', ['error' => $e->getMessage()]); } catch (Throwable $ignore) { } http_response_code(500); echo json_encode([ 'ok' => false, 'run_id' => $RUN_ID ?? null, 'error' => $e->getMessage() ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); }