­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ Campaign DETAILS export * - ?endpoint=ad => Ad DETAILS export * - ?endpoint=adset => Adset DETAILS export * - ?endpoint=creative => Creative DETAILS export * - ?endpoint=insights => Ad-level INSIGHTS export * * REQUIRED POST: * AUTH, from=YYYY-MM-DD, to=YYYY-MM-DD * * RULES: * - Strictly filter to ONLY campaigns where campaign_name LIKE '%changan%' (case-insensitive) * - For endpoint=insights only: * multiply CPC, CPM, CPL, SPEND, BUDGET, CPV, CPA by MULTIPLIER * - Output format: * { * "extraction_time": "...", * "run_date": "YYYY-MM-DD", * "data_date": "YYYY-MM-DD", * "source_system": "meta", * "data_type": "campaigns|ads|adsets|creatives|insights", * "record_count": N, * "data": [...] * } */ declare(strict_types=1); 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'; if (($_POST['AUTH'] ?? '') !== 'a2h1cnJhbS5kaGVkaGlAY29yZTNjb25zdWx0YW5jeS5jb206QkdDamVkZGFoQDY5') { 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', 'adset', 'creative', 'insights'], true)) { http_response_code(400); echo json_encode(['ok' => false, 'error' => 'Invalid endpoint. Use campaign|ad|adset|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 ------------------- **/ $ACCESS_TOKEN = getenv('META_ACCESS_TOKEN') ?: 'EAAKPXbAZCthYBRVc3NtiW4Etss3ehNSwyfPtacOlmRM3JMztGwhHh2E8ByZA2foWR75U76zHMzfXRYUhkZAZBaS4gXGeQ9ZAoEfO0MqhvG9QfUrBZALxti6TWZAClgtTtAFr3IDwv6WuZA8AdCVvtlFDv3R9clirlEgqL3DZCpO5XZCsGewFOZCHznv8T1x9Awf'; $AD_ACCOUNT_ID = '625694088463609'; $DB_HOST = getenv('DB_HOST') ?: 'localhost'; $DB_NAME = getenv('DB_NAME') ?: 'coreagen_marketing_insights'; $DB_USER = getenv('DB_USER') ?: 'coreagen_marketing_insights'; $DB_PASS = getenv('DB_PASS') ?: 'Vision@2050'; $DB_PORT = getenv('DB_PORT') ?: '3306'; $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' => 'meta', 'data_type' => $dataType, 'data_date' => $dataDate, 'account_id' => 'act_' . $adAccountId, 'campaign_id' => $r['campaign_id'] ?? null, 'campaign_name' => $r['campaign_name'] ?? null, 'adset_id' => $r['adset_id'] ?? null, 'adset_name' => $r['adset_name'] ?? null, 'ad_id' => $r['ad_id'] ?? ($r['id'] ?? null), 'ad_name' => $r['ad_name'] ?? ($r['name'] ?? null), 'creative_id' => $r['creative_id'] ?? null, 'creative_name' => $r['creative_name'] ?? null, 'date_start' => $r['date_start'] ?? null, 'date_stop' => $r['date_stop'] ?? null, 'impressions' => isset($r['impressions']) ? (int)$r['impressions'] : null, 'reach' => isset($r['reach']) ? (int)$r['reach'] : null, 'clicks' => isset($r['clicks']) ? (int)$r['clicks'] : null, 'spend' => isset($r['spend']) ? (float)$r['spend'] : null, 'ctr' => isset($r['ctr']) ? (float)$r['ctr'] : null, 'cpc' => isset($r['cpc']) ? (float)$r['cpc'] : null, 'cpm' => isset($r['cpm']) ? (float)$r['cpm'] : null, 'leads' => isset($r['leads']) ? (float)$r['leads'] : null, 'purchases' => isset($r['purchases']) ? (float)$r['purchases'] : null, 'video_actions' => isset($r['video_actions']) ? (float)$r['video_actions'] : null, 'cpl' => isset($r['cpl']) ? (float)$r['cpl'] : null, 'cpa' => isset($r['cpa']) ? (float)$r['cpa'] : null, 'cpv' => isset($r['cpv']) ? (float)$r['cpv'] : null, 'objective' => $r['objective'] ?? null, 'campaign_status' => $r['campaign_status'] ?? ($r['status'] ?? null), 'adset_status' => $r['adset_status'] ?? null, 'ad_status' => $r['ad_status'] ?? null, 'currency' => $r['currency'] ?? null, 'thumbnail_url' => $r['thumbnail_url'] ?? null, 'image_url' => $r['image_url'] ?? null, 'video_id' => $r['video_id'] ?? null, 'link_url' => $r['link_url'] ?? null, 'created_time' => $r['created_time'] ?? null, 'updated_time' => $r['updated_time'] ?? null ]; } return $rows; } function strtolower_safe($v): string { return strtolower((string)$v); } function is_changan_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 multiply_if_numeric(&$arr, string $key, float $mult): void { if (!array_key_exists($key, $arr)) return; $f = to_float($arr[$key]); if ($f === null) return; $arr[$key] = $f * $mult; } function get_action_count(array $actions, array $types): float { $sum = 0.0; foreach ($actions as $a) { $t = (string)($a['action_type'] ?? ''); if ($t === '') continue; if (in_array($t, $types, true)) { $v = to_float($a['value'] ?? null); if ($v !== null) $sum += $v; } } return $sum; } function compute_cpl_cpa_cpv(array $row): array { $spend = to_float($row['spend'] ?? null) ?? 0.0; $actions = $row['actions'] ?? []; if (!is_array($actions)) $actions = []; $leads = get_action_count($actions, [ 'lead', 'onsite_conversion.lead_grouped', 'offsite_conversion.fb_pixel_lead', 'offsite_conversion.lead_grouped' ]); $purchases = get_action_count($actions, [ 'purchase', 'offsite_conversion.fb_pixel_purchase', 'offsite_conversion.purchase' ]); $video = get_action_count($actions, [ 'thruplay', 'video_view', 'video_view_10s', 'video_p25_watched_actions', 'video_p50_watched_actions', 'video_p75_watched_actions', 'video_p95_watched_actions', 'video_p100_watched_actions' ]); return [ 'leads' => $leads, 'purchases' => $purchases, 'video_actions' => $video, 'cpl' => ($leads > 0 ? $spend / $leads : null), 'cpa' => ($purchases > 0 ? $spend / $purchases : null), 'cpv' => ($video > 0 ? $spend / $video : null), ]; } function apply_multiplier_and_kpis(array &$row, float $mult): void { multiply_if_numeric($row, 'spend', $mult); multiply_if_numeric($row, 'cpc', $mult); multiply_if_numeric($row, 'cpm', $mult); multiply_if_numeric($row, 'campaign_daily_budget', $mult); multiply_if_numeric($row, 'campaign_lifetime_budget', $mult); multiply_if_numeric($row, 'adset_daily_budget', $mult); multiply_if_numeric($row, 'adset_lifetime_budget', $mult); $k = compute_cpl_cpa_cpv($row); $row['leads'] = $k['leads']; $row['purchases'] = $k['purchases']; $row['video_actions'] = $k['video_actions']; $row['cpl'] = $k['cpl']; $row['cpa'] = $k['cpa']; $row['cpv'] = $k['cpv']; } function extract_creative_link_url(array $cr): ?string { $oss = $cr['object_story_spec'] ?? null; if (is_array($oss)) { $ld = $oss['link_data'] ?? null; if (is_array($ld) && !empty($ld['link'])) { return (string)$ld['link']; } $cta = $oss['call_to_action'] ?? null; if (is_array($cta)) { $val = $cta['value'] ?? null; if (is_array($val) && !empty($val['link'])) return (string)$val['link']; if (is_array($val) && !empty($val['link_url'])) return (string)$val['link_url']; } } $afs = $cr['asset_feed_spec'] ?? null; if (is_array($afs)) { if (!empty($afs['link_urls']) && is_array($afs['link_urls'])) { $first = $afs['link_urls'][0] ?? null; if (is_array($first) && !empty($first['website_url'])) return (string)$first['website_url']; if (is_string($first) && $first !== '') return $first; } if (!empty($afs['website_urls']) && is_array($afs['website_urls'])) { $first = $afs['website_urls'][0] ?? null; if (is_string($first) && $first !== '') return $first; } } if (!empty($cr['link_url'])) return (string)$cr['link_url']; return null; } 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' => 'meta', 'data_type' => $dataType, 'record_count' => count($data), 'data' => array_values($data), ]; } /** ------------------- 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 . '/meta_insights_' . 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); } } /** ------------------- META CLIENT ------------------- **/ class MetaClient { private string $accessToken; private string $apiVersion; private int $timeoutSec; public function __construct(string $accessToken, string $apiVersion = 'v24.0', int $timeoutSec = 60) { $this->accessToken = $accessToken; $this->apiVersion = $apiVersion; $this->timeoutSec = $timeoutSec; } private function httpGetJson(string $url): array { $ch = curl_init($url); curl_setopt_array($ch, [ CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => $this->timeoutSec, CURLOPT_CONNECTTIMEOUT => 15, CURLOPT_SSL_VERIFYPEER => true, CURLOPT_HTTPHEADER => ['Accept: application/json'], ]); $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)); } if (isset($json['error'])) { $msg = $json['error']['message'] ?? 'Unknown Meta error'; $type = $json['error']['type'] ?? ''; $code = $json['error']['code'] ?? ''; $sub = $json['error']['error_subcode'] ?? ''; throw new RuntimeException("Meta API error: {$msg} (type={$type}, code={$code}, subcode={$sub})"); } return $json; } private function httpPostForm(string $url, array $postFields): array { $ch = curl_init($url); curl_setopt_array($ch, [ CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => $this->timeoutSec, CURLOPT_CONNECTTIMEOUT => 15, CURLOPT_SSL_VERIFYPEER => true, CURLOPT_POST => true, CURLOPT_POSTFIELDS => http_build_query($postFields), CURLOPT_HTTPHEADER => ['Accept: application/json'], ]); $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 POST error ({$errno}): {$err}"); if ($body === false || $body === '') throw new RuntimeException("Empty POST response (HTTP {$http})"); $json = json_decode($body, true); if (!is_array($json)) { throw new RuntimeException("Invalid JSON POST response (HTTP {$http}): " . substr($body, 0, 500)); } if (isset($json['error'])) { $msg = $json['error']['message'] ?? 'Unknown Meta error'; $type = $json['error']['type'] ?? ''; $code = $json['error']['code'] ?? ''; $sub = $json['error']['error_subcode'] ?? ''; throw new RuntimeException("Meta API error: {$msg} (type={$type}, code={$code}, subcode={$sub})"); } return $json; } private function getAllPages(string $endpointUrl, array $params): array { $all = []; $nextUrl = $endpointUrl . '?' . http_build_query($params); while ($nextUrl) { $resp = $this->httpGetJson($nextUrl); if (!isset($resp['data']) || !is_array($resp['data'])) { $errMsg = $resp['error']['message'] ?? 'Unknown error (no data returned)'; $errCode = $resp['error']['code'] ?? ''; throw new RuntimeException("Meta API error: {$errMsg} {$errCode}"); } $all = array_merge($all, $resp['data']); $nextUrl = $resp['paging']['next'] ?? null; } return $all; } public function getInsights(string $adAccountId, string $since, string $until, string $level, array $fields, array $extraParams = []): array { if (!in_array($level, ['campaign', 'ad'], true)) { throw new InvalidArgumentException("Invalid level: {$level}"); } $endpoint = "https://graph.facebook.com/{$this->apiVersion}/act_{$adAccountId}/insights"; $params = array_merge([ 'access_token' => $this->accessToken, 'level' => $level, 'fields' => implode(',', $fields), 'time_range' => ['since' => $since, 'until' => $until], ], $extraParams); return $this->getAllPages($endpoint, $params); } public function listCampaigns(string $adAccountId, array $fields, array $extraParams = []): array { $endpoint = "https://graph.facebook.com/{$this->apiVersion}/act_{$adAccountId}/campaigns"; $params = array_merge([ 'access_token' => $this->accessToken, 'fields' => implode(',', $fields), 'limit' => 500, ], $extraParams); return $this->getAllPages($endpoint, $params); } public function listAds(string $adAccountId, array $fields, array $extraParams = []): array { $endpoint = "https://graph.facebook.com/{$this->apiVersion}/act_{$adAccountId}/ads"; $params = array_merge([ 'access_token' => $this->accessToken, 'fields' => implode(',', $fields), 'limit' => 500, ], $extraParams); return $this->getAllPages($endpoint, $params); } public function listAdSets(string $adAccountId, array $fields, array $extraParams = []): array { $endpoint = "https://graph.facebook.com/{$this->apiVersion}/act_{$adAccountId}/adsets"; $params = array_merge([ 'access_token' => $this->accessToken, 'fields' => implode(',', $fields), 'limit' => 500, ], $extraParams); return $this->getAllPages($endpoint, $params); } public function getAdAccountMeta(string $adAccountId): array { $url = "https://graph.facebook.com/{$this->apiVersion}/act_{$adAccountId}" . "?fields=account_status,currency,name" . "&access_token=" . urlencode($this->accessToken); return $this->httpGetJson($url); } public function batchFetchByIds(array $ids, array $fields): array { $ids = array_values(array_unique(array_filter($ids))); if (!$ids) return []; $endpoint = "https://graph.facebook.com/{$this->apiVersion}/"; $out = []; foreach (array_chunk($ids, 50) as $chunk) { $batch = []; foreach ($chunk as $id) { $batch[] = [ 'method' => 'GET', 'relative_url' => $id . '?fields=' . implode(',', $fields) ]; } $resp = $this->httpPostForm($endpoint, [ 'access_token' => $this->accessToken, 'batch' => json_encode($batch, JSON_UNESCAPED_UNICODE), ]); foreach ($resp as $item) { if (!isset($item['code'], $item['body'])) continue; $code = (int)$item['code']; if ($code < 200 || $code >= 300) continue; $body = json_decode($item['body'], true); if (is_array($body) && isset($body['id'])) { $out[(string)$body['id']] = $body; } } } return $out; } } /** ------------------- 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_meta_ad_insights ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(32) NOT NULL, date_start DATE NOT NULL, date_stop DATE NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adset_id VARCHAR(64) NULL, adset_name VARCHAR(255) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, objective VARCHAR(128) NULL, account_status VARCHAR(64) NULL, currency VARCHAR(16) NULL, campaign_status VARCHAR(64) NULL, adset_status VARCHAR(64) NULL, ad_status VARCHAR(64) NULL, campaign_start_time DATETIME NULL, campaign_stop_time DATETIME NULL, adset_start_time DATETIME NULL, adset_stop_time DATETIME NULL, ad_start_time DATETIME NULL, ad_stop_time DATETIME NULL, campaign_daily_budget DECIMAL(18,6) NULL, campaign_lifetime_budget DECIMAL(18,6) NULL, adset_daily_budget DECIMAL(18,6) NULL, adset_lifetime_budget DECIMAL(18,6) NULL, impressions BIGINT NULL, clicks BIGINT NULL, reach BIGINT NULL, spend DECIMAL(18,6) NULL, ctr DECIMAL(18,6) NULL, cpc DECIMAL(18,6) NULL, cpm DECIMAL(18,6) NULL, leads DECIMAL(18,6) NULL, purchases DECIMAL(18,6) NULL, video_actions DECIMAL(18,6) NULL, cpl DECIMAL(18,6) NULL, cpa DECIMAL(18,6) NULL, cpv DECIMAL(18,6) NULL, actions_json JSON 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_row (ad_account_id, date_start, ad_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_meta_campaign_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NOT NULL, campaign_name VARCHAR(255) NULL, objective VARCHAR(128) NULL, buying_type VARCHAR(64) NULL, effective_status VARCHAR(64) NULL, status VARCHAR(64) NULL, start_time DATETIME NULL, stop_time DATETIME NULL, daily_budget DECIMAL(18,6) NULL, lifetime_budget DECIMAL(18,6) NULL, created_time DATETIME NULL, updated_time 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_meta_ad_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adset_id VARCHAR(64) NULL, ad_id VARCHAR(64) NOT NULL, ad_name VARCHAR(255) NULL, effective_status VARCHAR(64) NULL, status VARCHAR(64) NULL, creative_id VARCHAR(64) NULL, created_time DATETIME NULL, updated_time 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_meta_adset_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, adset_id VARCHAR(64) NOT NULL, adset_name VARCHAR(255) NULL, status VARCHAR(64) NULL, start_time DATETIME NULL, created_time DATETIME NULL, updated_time DATETIME NULL, targeting_json JSON 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_adset (ad_account_id, adset_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS hondav2_meta_creative_details ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, ad_account_id VARCHAR(32) NOT NULL, campaign_id VARCHAR(64) NULL, campaign_name VARCHAR(255) NULL, ad_id VARCHAR(64) NULL, ad_name VARCHAR(255) NULL, creative_id VARCHAR(64) NOT NULL, creative_name VARCHAR(255) NULL, thumbnail_url TEXT NULL, image_url TEXT NULL, video_id VARCHAR(64) NULL, url_tags TEXT NULL, link_url TEXT 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, 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_meta_campaign_details (run_id, ad_account_id, campaign_id, campaign_name, objective, buying_type, effective_status, status, start_time, stop_time, daily_budget, lifetime_budget, created_time, updated_time, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :objective, :buying_type, :effective_status, :status, :start_time, :stop_time, :daily_budget, :lifetime_budget, :created_time, :updated_time, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_name=VALUES(campaign_name), objective=VALUES(objective), buying_type=VALUES(buying_type), effective_status=VALUES(effective_status), status=VALUES(status), start_time=VALUES(start_time), stop_time=VALUES(stop_time), daily_budget=VALUES(daily_budget), lifetime_budget=VALUES(lifetime_budget), created_time=VALUES(created_time), updated_time=VALUES(updated_time), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { if (empty($r['id'])) continue; $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => (string)$r['id'], ':campaign_name' => $r['name'] ?? null, ':objective' => $r['objective'] ?? null, ':buying_type' => $r['buying_type'] ?? null, ':effective_status' => $r['effective_status'] ?? null, ':status' => $r['status'] ?? null, ':start_time' => $this->toSqlDateTime($r['start_time'] ?? null), ':stop_time' => $this->toSqlDateTime($r['stop_time'] ?? null), ':daily_budget' => isset($r['daily_budget']) ? (float)$r['daily_budget'] : null, ':lifetime_budget' => isset($r['lifetime_budget']) ? (float)$r['lifetime_budget'] : null, ':created_time' => $this->toSqlDateTime($r['created_time'] ?? null), ':updated_time' => $this->toSqlDateTime($r['updated_time'] ?? 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_meta_ad_details (run_id, ad_account_id, campaign_id, campaign_name, adset_id, ad_id, ad_name, effective_status, status, creative_id, created_time, updated_time, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :adset_id, :ad_id, :ad_name, :effective_status, :status, :creative_id, :created_time, :updated_time, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adset_id=VALUES(adset_id), ad_name=VALUES(ad_name), effective_status=VALUES(effective_status), status=VALUES(status), creative_id=VALUES(creative_id), created_time=VALUES(created_time), updated_time=VALUES(updated_time), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { if (empty($r['ad_id'])) continue; $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adset_id' => $r['adset_id'] ?? null, ':ad_id' => (string)$r['ad_id'], ':ad_name' => $r['ad_name'] ?? null, ':effective_status' => $r['effective_status'] ?? null, ':status' => $r['status'] ?? null, ':creative_id' => $r['creative_id'] ?? null, ':created_time' => $this->toSqlDateTime($r['created_time'] ?? null), ':updated_time' => $this->toSqlDateTime($r['updated_time'] ?? 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 upsertAdsetDetails(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_meta_adset_details (run_id, ad_account_id, campaign_id, campaign_name, adset_id, adset_name, status, start_time, created_time, updated_time, targeting_json, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :adset_id, :adset_name, :status, :start_time, :created_time, :updated_time, :targeting_json, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adset_name=VALUES(adset_name), status=VALUES(status), start_time=VALUES(start_time), created_time=VALUES(created_time), updated_time=VALUES(updated_time), targeting_json=VALUES(targeting_json), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { if (empty($r['id'])) continue; $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adset_id' => (string)$r['id'], ':adset_name' => $r['name'] ?? null, ':status' => $r['status'] ?? null, ':start_time' => $this->toSqlDateTime($r['start_time'] ?? null), ':created_time' => $this->toSqlDateTime($r['created_time'] ?? null), ':updated_time' => $this->toSqlDateTime($r['updated_time'] ?? null), ':targeting_json' => isset($r['targeting']) ? json_encode($r['targeting'], JSON_UNESCAPED_UNICODE) : 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_meta_creative_details (run_id, ad_account_id, campaign_id, campaign_name, ad_id, ad_name, creative_id, creative_name, thumbnail_url, image_url, video_id, url_tags, link_url, raw_json) VALUES (:run_id, :ad_account_id, :campaign_id, :campaign_name, :ad_id, :ad_name, :creative_id, :creative_name, :thumbnail_url, :image_url, :video_id, :url_tags, :link_url, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), ad_id=VALUES(ad_id), ad_name=VALUES(ad_name), creative_name=VALUES(creative_name), thumbnail_url=VALUES(thumbnail_url), image_url=VALUES(image_url), video_id=VALUES(video_id), url_tags=VALUES(url_tags), link_url=VALUES(link_url), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { if (empty($r['creative_id'])) continue; $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':ad_id' => $r['ad_id'] ?? null, ':ad_name' => $r['ad_name'] ?? null, ':creative_id' => (string)$r['creative_id'], ':creative_name' => $r['creative_name'] ?? null, ':thumbnail_url' => $r['thumbnail_url'] ?? null, ':image_url' => $r['image_url'] ?? null, ':video_id' => $r['video_id'] ?? null, ':url_tags' => $r['url_tags'] ?? null, ':link_url' => $r['link_url'] ?? 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 upsertAdInsightsRows(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO hondav2_meta_ad_insights (run_id, ad_account_id, date_start, date_stop, campaign_id, campaign_name, adset_id, adset_name, ad_id, ad_name, objective, account_status, currency, campaign_status, adset_status, ad_status, campaign_start_time, campaign_stop_time, adset_start_time, adset_stop_time, ad_start_time, ad_stop_time, campaign_daily_budget, campaign_lifetime_budget, adset_daily_budget, adset_lifetime_budget, impressions, clicks, reach, spend, ctr, cpc, cpm, leads, purchases, video_actions, cpl, cpa, cpv, actions_json, raw_json) VALUES (:run_id, :ad_account_id, :date_start, :date_stop, :campaign_id, :campaign_name, :adset_id, :adset_name, :ad_id, :ad_name, :objective, :account_status, :currency, :campaign_status, :adset_status, :ad_status, :campaign_start_time, :campaign_stop_time, :adset_start_time, :adset_stop_time, :ad_start_time, :ad_stop_time, :campaign_daily_budget, :campaign_lifetime_budget, :adset_daily_budget, :adset_lifetime_budget, :impressions, :clicks, :reach, :spend, :ctr, :cpc, :cpm, :leads, :purchases, :video_actions, :cpl, :cpa, :cpv, :actions_json, :raw_json) ON DUPLICATE KEY UPDATE run_id=VALUES(run_id), campaign_id=VALUES(campaign_id), campaign_name=VALUES(campaign_name), adset_id=VALUES(adset_id), adset_name=VALUES(adset_name), ad_name=VALUES(ad_name), objective=VALUES(objective), account_status=VALUES(account_status), currency=VALUES(currency), campaign_status=VALUES(campaign_status), adset_status=VALUES(adset_status), ad_status=VALUES(ad_status), campaign_start_time=VALUES(campaign_start_time), campaign_stop_time=VALUES(campaign_stop_time), adset_start_time=VALUES(adset_start_time), adset_stop_time=VALUES(adset_stop_time), ad_start_time=VALUES(ad_start_time), ad_stop_time=VALUES(ad_stop_time), campaign_daily_budget=VALUES(campaign_daily_budget), campaign_lifetime_budget=VALUES(campaign_lifetime_budget), adset_daily_budget=VALUES(adset_daily_budget), adset_lifetime_budget=VALUES(adset_lifetime_budget), impressions=VALUES(impressions), clicks=VALUES(clicks), reach=VALUES(reach), spend=VALUES(spend), ctr=VALUES(ctr), cpc=VALUES(cpc), cpm=VALUES(cpm), leads=VALUES(leads), purchases=VALUES(purchases), video_actions=VALUES(video_actions), cpl=VALUES(cpl), cpa=VALUES(cpa), cpv=VALUES(cpv), actions_json=VALUES(actions_json), raw_json=VALUES(raw_json), updated_at=CURRENT_TIMESTAMP "; $stmt = $this->pdo->prepare($sql); $count = 0; $this->pdo->beginTransaction(); try { foreach ($rows as $r) { if (empty($r['date_start']) || empty($r['date_stop']) || empty($r['ad_id'])) continue; $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':date_start' => $r['date_start'], ':date_stop' => $r['date_stop'], ':campaign_id' => $r['campaign_id'] ?? null, ':campaign_name' => $r['campaign_name'] ?? null, ':adset_id' => $r['adset_id'] ?? null, ':adset_name' => $r['adset_name'] ?? null, ':ad_id' => (string)$r['ad_id'], ':ad_name' => $r['ad_name'] ?? null, ':objective' => $r['objective'] ?? null, ':account_status' => $r['account_status'] ?? null, ':currency' => $r['currency'] ?? null, ':campaign_status' => $r['campaign_status'] ?? null, ':adset_status' => $r['adset_status'] ?? null, ':ad_status' => $r['ad_status'] ?? null, ':campaign_start_time' => $this->toSqlDateTime($r['campaign_start_time'] ?? null), ':campaign_stop_time' => $this->toSqlDateTime($r['campaign_stop_time'] ?? null), ':adset_start_time' => $this->toSqlDateTime($r['adset_start_time'] ?? null), ':adset_stop_time' => $this->toSqlDateTime($r['adset_stop_time'] ?? null), ':ad_start_time' => $this->toSqlDateTime($r['ad_start_time'] ?? null), ':ad_stop_time' => $this->toSqlDateTime($r['ad_stop_time'] ?? null), ':campaign_daily_budget' => $r['campaign_daily_budget'] ?? null, ':campaign_lifetime_budget' => $r['campaign_lifetime_budget'] ?? null, ':adset_daily_budget' => $r['adset_daily_budget'] ?? null, ':adset_lifetime_budget' => $r['adset_lifetime_budget'] ?? null, ':impressions' => isset($r['impressions']) ? (int)$r['impressions'] : null, ':clicks' => isset($r['clicks']) ? (int)$r['clicks'] : null, ':reach' => isset($r['reach']) ? (int)$r['reach'] : null, ':spend' => isset($r['spend']) ? (float)$r['spend'] : null, ':ctr' => isset($r['ctr']) ? (float)$r['ctr'] : null, ':cpc' => isset($r['cpc']) ? (float)$r['cpc'] : null, ':cpm' => isset($r['cpm']) ? (float)$r['cpm'] : null, ':leads' => isset($r['leads']) ? (float)$r['leads'] : null, ':purchases' => isset($r['purchases']) ? (float)$r['purchases'] : null, ':video_actions' => isset($r['video_actions']) ? (float)$r['video_actions'] : null, ':cpl' => isset($r['cpl']) ? (float)$r['cpl'] : null, ':cpa' => isset($r['cpa']) ? (float)$r['cpa'] : null, ':cpv' => isset($r['cpv']) ? (float)$r['cpv'] : null, ':actions_json' => isset($r['actions']) ? json_encode($r['actions'], JSON_UNESCAPED_UNICODE) : 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(); $client = new MetaClient($ACCESS_TOKEN, 'v24.0'); $acct = $client->getAdAccountMeta($AD_ACCOUNT_ID); $accountStatus = isset($acct['account_status']) ? (string)$acct['account_status'] : null; $currency = $acct['currency'] ?? null; $accountName = $acct['name'] ?? null; $snapDir = __DIR__ . '/output'; if (!is_dir($snapDir)) mkdir($snapDir, 0755, true); /** * 1) CAMPAIGN DETAILS */ if ($ENDPOINT_MODE === 'campaign') { $fields = [ 'id','name','objective','buying_type', 'effective_status','status', 'start_time','stop_time', 'daily_budget','lifetime_budget', 'created_time','updated_time' ]; $rows = $client->listCampaigns($AD_ACCOUNT_ID, $fields); $rows = array_values(array_filter($rows, fn($r) => is_changan_campaign($r['name'] ?? null))); $repo->upsertCampaignDetails($RUN_ID, $AD_ACCOUNT_ID, $rows); $final = []; foreach ($rows as $r) { $final[] = [ 'id' => $r['id'] ?? null, 'name' => $r['name'] ?? null, 'status' => $r['effective_status'] ?? ($r['status'] ?? null), 'objective' => $r['objective'] ?? null, 'start_time' => $r['start_time'] ?? null, 'stop_time' => $r['stop_time'] ?? null, 'created_time' => $r['created_time'] ?? null, 'updated_time' => $r['updated_time'] ?? null, 'account_id' => 'act_' . $AD_ACCOUNT_ID, 'account_name' => $accountName ]; } $payload = export_payload('campaigns', $UNTIL, $final); file_put_contents($snapDir . "/meta_campaigns_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 2) AD DETAILS */ if ($ENDPOINT_MODE === 'ad') { $fields = [ 'id', 'name', 'effective_status', 'status', 'adset_id', 'campaign_id', 'campaign{id,name}', 'creative{id}', 'created_time', 'updated_time' ]; $rows = $client->listAds($AD_ACCOUNT_ID, $fields); $out = []; foreach ($rows as $r) { $campaignObj = $r['campaign'] ?? []; $cid = $campaignObj['id'] ?? ($r['campaign_id'] ?? null); $cname = $campaignObj['name'] ?? null; if (!is_changan_campaign($cname)) { continue; } $out[] = [ 'campaign_id' => $cid, 'campaign_name' => $cname, 'adset_id' => $r['adset_id'] ?? null, 'ad_id' => $r['id'] ?? null, 'ad_name' => $r['name'] ?? null, 'effective_status' => $r['effective_status'] ?? null, 'status' => $r['status'] ?? null, 'creative_id' => $r['creative']['id'] ?? null, 'created_time' => $r['created_time'] ?? null, 'updated_time' => $r['updated_time'] ?? null, ]; } $repo->upsertAdDetails($RUN_ID, $AD_ACCOUNT_ID, $out); $final = []; foreach ($out as $r) { $final[] = [ 'id' => $r['ad_id'] ?? null, 'name' => $r['ad_name'] ?? null, 'status' => $r['effective_status'] ?? ($r['status'] ?? null), 'adset_id' => $r['adset_id'] ?? null, 'campaign_id' => $r['campaign_id'] ?? null, 'created_time' => $r['created_time'] ?? null, 'updated_time' => $r['updated_time'] ?? null, 'account_id' => 'act_' . $AD_ACCOUNT_ID, 'account_name' => $accountName ]; } $payload = export_payload('ads', $UNTIL, $final); file_put_contents($snapDir . "/meta_ads_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 3) ADSET DETAILS */ if ($ENDPOINT_MODE === 'adset') { $fields = [ 'id', 'name', 'status', 'campaign_id', 'campaign{id,name}', 'start_time', 'created_time', 'updated_time', 'targeting' ]; $rows = $client->listAdSets($AD_ACCOUNT_ID, $fields); $out = []; foreach ($rows as $r) { $campaignObj = $r['campaign'] ?? []; $cid = $campaignObj['id'] ?? ($r['campaign_id'] ?? null); $cname = $campaignObj['name'] ?? null; if (!is_changan_campaign($cname)) { continue; } $out[] = [ 'id' => $r['id'] ?? null, 'name' => $r['name'] ?? null, 'status' => $r['status'] ?? null, 'campaign_id' => $cid, 'campaign_name' => $cname, 'start_time' => $r['start_time'] ?? null, 'created_time' => $r['created_time'] ?? null, 'updated_time' => $r['updated_time'] ?? null, 'targeting' => $r['targeting'] ?? null, 'account_id' => 'act_' . $AD_ACCOUNT_ID, 'account_name' => $accountName ]; } $repo->upsertAdsetDetails($RUN_ID, $AD_ACCOUNT_ID, $out); $payload = export_payload('adsets', $UNTIL, $out); file_put_contents($snapDir . "/meta_adsets_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 4) CREATIVE DETAILS */ if ($ENDPOINT_MODE === 'creative') { $adFields = [ 'id', 'name', 'campaign_id', 'campaign{id,name}', 'creative{id}', 'effective_status', 'status' ]; $ads = $client->listAds($AD_ACCOUNT_ID, $adFields); $scopedAds = []; $creativeIds = []; foreach ($ads as $a) { $campaignObj = $a['campaign'] ?? []; $cid = $campaignObj['id'] ?? ($a['campaign_id'] ?? null); $cname = $campaignObj['name'] ?? null; if (!is_changan_campaign($cname)) { continue; } $crid = $a['creative']['id'] ?? null; if ($crid) { $creativeIds[] = (string)$crid; } $scopedAds[] = [ 'campaign_id' => $cid, 'campaign_name' => $cname, 'ad_id' => $a['id'] ?? null, 'ad_name' => $a['name'] ?? null, 'creative_id' => $crid ]; } $creativeMeta = $client->batchFetchByIds($creativeIds, [ 'id', 'name', 'thumbnail_url', 'image_url', 'video_id', 'url_tags', 'object_story_spec', 'asset_feed_spec' ]); $out = []; foreach ($scopedAds as $x) { $crid = $x['creative_id'] ?? null; if (!$crid || !isset($creativeMeta[$crid])) { continue; } $cr = $creativeMeta[$crid]; $out[] = [ 'campaign_id' => $x['campaign_id'] ?? null, 'campaign_name' => $x['campaign_name'] ?? null, 'ad_id' => $x['ad_id'] ?? null, 'ad_name' => $x['ad_name'] ?? null, 'creative_id' => (string)$cr['id'], 'creative_name' => $cr['name'] ?? null, 'thumbnail_url' => $cr['thumbnail_url'] ?? null, 'image_url' => $cr['image_url'] ?? null, 'video_id' => isset($cr['video_id']) ? (string)$cr['video_id'] : null, 'url_tags' => $cr['url_tags'] ?? null, 'link_url' => extract_creative_link_url($cr), 'object_story_spec' => $cr['object_story_spec'] ?? null, 'asset_feed_spec' => $cr['asset_feed_spec'] ?? null, ]; } $repo->upsertCreativeDetails($RUN_ID, $AD_ACCOUNT_ID, $out); $final = []; foreach ($out as $r) { $item = [ 'id' => $r['creative_id'] ?? null, 'name' => $r['creative_name'] ?? null, 'thumbnail_url' => $r['thumbnail_url'] ?? null, 'status' => 'ACTIVE', 'ad_id' => $r['ad_id'] ?? null, 'account_id' => 'act_' . $AD_ACCOUNT_ID, 'account_name' => $accountName ]; if (!empty($r['link_url'])) { $item['link_url'] = $r['link_url']; } if (!empty($r['url_tags'])) { $item['url_tags'] = $r['url_tags']; } if (!empty($r['image_url'])) { $item['image_url'] = $r['image_url']; } if (!empty($r['video_id'])) { $item['video_id'] = $r['video_id']; } if (!empty($r['object_story_spec'])) { $item['object_story_spec'] = $r['object_story_spec']; } if (!empty($r['asset_feed_spec'])) { $item['asset_feed_spec'] = $r['asset_feed_spec']; } $final[] = $item; } $payload = export_payload('creatives', $UNTIL, $final); file_put_contents($snapDir . "/meta_creatives_{$RUN_ID}.json", json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); echo json_encode($payload, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); exit; } /** * 5) INSIGHTS */ $fields = [ 'date_start','date_stop', 'campaign_id','campaign_name', 'adset_id','adset_name', 'ad_id','ad_name', 'objective', 'impressions','reach','frequency', 'clicks','unique_clicks', 'outbound_clicks','unique_outbound_clicks', 'spend','cpc','cpm','cpp','ctr','unique_ctr', 'inline_link_clicks','inline_link_click_ctr','cost_per_inline_link_click', 'inline_post_engagement', 'quality_ranking','engagement_rate_ranking','conversion_rate_ranking', 'video_play_actions', 'video_avg_time_watched_actions', 'video_p25_watched_actions', 'video_p50_watched_actions', 'video_p75_watched_actions', 'video_p100_watched_actions', 'video_thruplay_watched_actions', 'video_15_sec_watched_actions', 'video_30_sec_watched_actions', 'actions','cost_per_action_type','social_spend','website_ctr' ]; $extraParams = [ 'time_increment' => 1, 'limit' => 500 ]; $rows = $client->getInsights($AD_ACCOUNT_ID, $SINCE, $UNTIL, 'ad', $fields, $extraParams); $rows = array_values(array_filter($rows, fn($r) => is_changan_campaign($r['campaign_name'] ?? null))); $campaignIds = []; $adsetIds = []; $adIds = []; foreach ($rows as $r) { if (!empty($r['campaign_id'])) $campaignIds[] = (string)$r['campaign_id']; if (!empty($r['adset_id'])) $adsetIds[] = (string)$r['adset_id']; if (!empty($r['ad_id'])) $adIds[] = (string)$r['ad_id']; } $campaignMeta = $client->batchFetchByIds($campaignIds, ['id','effective_status','status','start_time','stop_time','daily_budget','lifetime_budget']); $adsetMeta = $client->batchFetchByIds($adsetIds, ['id','effective_status','status','start_time','end_time','daily_budget','lifetime_budget']); $adMeta = $client->batchFetchByIds($adIds, ['id','effective_status','status','start_time','stop_time']); foreach ($rows as &$r) { $cid = $r['campaign_id'] ?? null; $asid = $r['adset_id'] ?? null; $aid = $r['ad_id'] ?? null; $c = ($cid && isset($campaignMeta[$cid])) ? $campaignMeta[$cid] : []; $a = ($asid && isset($adsetMeta[$asid])) ? $adsetMeta[$asid] : []; $d = ($aid && isset($adMeta[$aid])) ? $adMeta[$aid] : []; $r['account_status'] = $accountStatus; $r['currency'] = $currency; $r['campaign_status'] = $c['effective_status'] ?? ($c['status'] ?? null); $r['adset_status'] = $a['effective_status'] ?? ($a['status'] ?? null); $r['ad_status'] = $d['effective_status'] ?? ($d['status'] ?? null); $r['campaign_start_time'] = $c['start_time'] ?? null; $r['campaign_stop_time'] = $c['stop_time'] ?? null; $r['adset_start_time'] = $a['start_time'] ?? null; $r['adset_stop_time'] = $a['end_time'] ?? null; $r['ad_start_time'] = $d['start_time'] ?? null; $r['ad_stop_time'] = $d['stop_time'] ?? null; $r['campaign_daily_budget'] = isset($c['daily_budget']) ? (float)$c['daily_budget'] : null; $r['campaign_lifetime_budget'] = isset($c['lifetime_budget']) ? (float)$c['lifetime_budget'] : null; $r['adset_daily_budget'] = isset($a['daily_budget']) ? (float)$a['daily_budget'] : null; $r['adset_lifetime_budget'] = isset($a['lifetime_budget']) ? (float)$a['lifetime_budget'] : null; apply_multiplier_and_kpis($r, MULTIPLIER); } unset($r); $repo->upsertAdInsightsRows($RUN_ID, $AD_ACCOUNT_ID, $rows); $final = []; foreach ($rows as $r) { $item = [ 'campaign_id' => $r['campaign_id'] ?? null, 'campaign_name' => $r['campaign_name'] ?? null, 'adset_id' => $r['adset_id'] ?? null, 'adset_name' => $r['adset_name'] ?? null, 'ad_id' => $r['ad_id'] ?? null, 'ad_name' => $r['ad_name'] ?? null, 'date_start' => $r['date_start'] ?? null, 'date_stop' => $r['date_stop'] ?? null, 'impressions' => isset($r['impressions']) ? (int)$r['impressions'] : null, 'reach' => isset($r['reach']) ? (int)$r['reach'] : null, 'clicks' => isset($r['clicks']) ? (int)$r['clicks'] : null, 'spend' => isset($r['spend']) ? (float)$r['spend'] : null, 'cpc' => isset($r['cpc']) ? (float)$r['cpc'] : null, 'cpm' => isset($r['cpm']) ? (float)$r['cpm'] : null, 'ctr' => isset($r['ctr']) ? (float)$r['ctr'] : null, // keep KPI fields 'leads' => isset($r['leads']) ? (float)$r['leads'] : 0.0, 'purchases' => isset($r['purchases']) ? (float)$r['purchases'] : 0.0, 'video_actions' => isset($r['video_actions']) ? (float)$r['video_actions'] : 0.0, 'cpl' => isset($r['cpl']) ? (float)$r['cpl'] : null, 'cpa' => isset($r['cpa']) ? (float)$r['cpa'] : null, 'cpv' => isset($r['cpv']) ? (float)$r['cpv'] : null, 'objective' => $r['objective'] ?? null, 'campaign_status' => $r['campaign_status'] ?? null, 'adset_status' => $r['adset_status'] ?? null, 'ad_status' => $r['ad_status'] ?? null, 'currency' => $r['currency'] ?? null, 'actions' => $r['actions'] ?? [], ]; $optionalFields = [ 'frequency', 'unique_clicks', 'outbound_clicks', 'unique_outbound_clicks', 'cpp', 'unique_ctr', 'inline_link_clicks', 'inline_link_click_ctr', 'cost_per_inline_link_click', 'inline_post_engagement', 'quality_ranking', 'engagement_rate_ranking', 'conversion_rate_ranking', 'video_play_actions', 'video_avg_time_watched_actions', 'video_p25_watched_actions', 'video_p50_watched_actions', 'video_p75_watched_actions', 'video_p100_watched_actions', 'video_thruplay_watched_actions', 'video_15_sec_watched_actions', 'video_30_sec_watched_actions', 'cost_per_action_type', 'social_spend', 'website_ctr' ]; foreach ($optionalFields as $field) { if (array_key_exists($field, $r)) { $item[$field] = $r[$field]; } } $final[] = $item; } $payload = export_dashboard_rows('insights', $UNTIL, $final, $AD_ACCOUNT_ID); file_put_contents($snapDir . "/meta_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); }