­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ 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 { $date = date('Y-m-d H:i:s'); $file = $this->logDir . '/meta_insights_' . date('Ymd') . '.log'; $line = [ 'ts' => $date, 'level' => $level, 'run_id' => $this->runId, 'msg' => $message, 'ctx' => $context, ]; file_put_contents($file, json_encode($line, JSON_UNESCAPED_UNICODE) . PHP_EOL, FILE_APPEND); } } class MetaAdsInsightsClient { 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; } public function getCampaignInsights( string $adAccountId, string $since, string $until, array $fields = [], array $extraParams = [] ): array { if (empty($fields)) { $fields = [ 'date_start','date_stop','campaign_id','campaign_name', 'impressions','clicks','spend','reach','cpc','cpm','ctr', 'actions','action_values' ]; } $endpoint = "https://graph.facebook.com/{$this->apiVersion}/act_{$adAccountId}/insights"; $params = array_merge([ 'access_token' => $this->accessToken, 'level' => 'campaign', 'fields' => implode(',', $fields), 'time_range' => ['since' => $since, 'until' => $until], ], $extraParams); $all = []; $nextUrl = $endpoint . '?' . 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; } 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; } } class MetaInsightsRepository { 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 meta_campaign_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) NOT NULL, campaign_name VARCHAR(255) NULL, objective VARCHAR(128) 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, 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, campaign_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); $this->pdo->exec(" CREATE TABLE IF NOT EXISTS meta_api_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, run_id VARCHAR(64) NOT NULL, level VARCHAR(10) NOT NULL, message VARCHAR(500) NOT NULL, context_json JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "); } public function logToDb(string $runId, string $level, string $message, array $context = []): void { $stmt = $this->pdo->prepare(" INSERT INTO meta_api_logs (run_id, level, message, context_json) VALUES (:run_id, :level, :message, :context_json) "); $stmt->execute([ ':run_id' => $runId, ':level' => $level, ':message' => mb_substr($message, 0, 500), ':context_json' => $context ? json_encode($context, JSON_UNESCAPED_UNICODE) : null, ]); } public function upsertInsightsRows(string $runId, string $adAccountId, array $rows): int { $sql = " INSERT INTO meta_campaign_insights (run_id, ad_account_id, date_start, date_stop, campaign_id, campaign_name, objective, impressions, clicks, reach, spend, ctr, cpc, cpm, actions_json, raw_json) VALUES (:run_id, :ad_account_id, :date_start, :date_stop, :campaign_id, :campaign_name, :objective, :impressions, :clicks, :reach, :spend, :ctr, :cpc, :cpm, :actions_json, :raw_json) ON DUPLICATE KEY UPDATE run_id = VALUES(run_id), campaign_name = VALUES(campaign_name), objective = VALUES(objective), impressions = VALUES(impressions), clicks = VALUES(clicks), reach = VALUES(reach), spend = VALUES(spend), ctr = VALUES(ctr), cpc = VALUES(cpc), cpm = VALUES(cpm), 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) { // Defensive extraction $dateStart = $r['date_start'] ?? null; $dateStop = $r['date_stop'] ?? null; $campaignId = $r['campaign_id'] ?? null; if (!$dateStart || !$dateStop || !$campaignId) { continue; // skip malformed row } $stmt->execute([ ':run_id' => $runId, ':ad_account_id' => $adAccountId, ':date_start' => $dateStart, ':date_stop' => $dateStop, ':campaign_id' => (string)$campaignId, ':campaign_name' => $r['campaign_name'] ?? null, ':objective' => $r['objective'] ?? 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, ':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; } public function fetchInsightsAsJson(string $adAccountId, string $since, string $until): array { $stmt = $this->pdo->prepare(" SELECT ad_account_id, date_start, date_stop, campaign_id, campaign_name, objective, impressions, clicks, reach, (spend*2) as spend, ctr, (cpc*2) as cpc, (cpm*2) as cpm, actions_json, null as raw_json, updated_at FROM meta_campaign_insights WHERE ad_account_id = :ad_account_id AND lower(campaign_name) like '%changan%' AND date_start >= :since AND date_stop <= :until ORDER BY date_start ASC, campaign_name ASC "); $stmt->execute([ ':ad_account_id' => $adAccountId, ':since' => $since, ':until' => $until, ]); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // Decode JSON columns for clean output foreach ($rows as &$r) { if (!empty($r['actions_json'])) { $r['actions'] = json_decode($r['actions_json'], true); } unset($r['actions_json']); if (!empty($r['raw_json'])) { $r['raw'] = json_decode($r['raw_json'], true); } unset($r['raw_json']); } return $rows; } } /** ------------------- CONFIG ------------------- **/ // IMPORTANT: move token to env in real usage $ACCESS_TOKEN = getenv('META_ACCESS_TOKEN') ?: 'EAAKPXbAZCthYBQSrQUeV4FuZA5QkR825IhAbmt9wIW0zhkTU14c3zZCo9nzX660b9xl6yG1XkXoZCfLOa8Dv6bi2nHy2bvu1EHjFq6ZA41ZA6lnyTeAcEwZA5lj8dI3ZB8gmZBNmZCj8cVDtFTsTOZCwty66MkOQZA9bQ0JEnZBeZBGw9JeebZBzdEdYV6QRkk5svSyuEcF'; // Ad account id numeric only (without act_) $AD_ACCOUNT_ID = '625694088463609'; // Date range //$SINCE = '2025-12-23'; //$UNTIL = '2025-12-23'; $SINCE = $_POST['from']; $UNTIL = $_POST['to']; // DB config $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)); $logger = new Logger(__DIR__ . '/logs', $RUN_ID); /** ------------------- RUN ------------------- **/ header('Content-Type: application/json; charset=utf-8'); try { // PDO init $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 MetaInsightsRepository($pdo); $repo->ensureTables(); $repo->logToDb($RUN_ID, 'INFO', 'Run started', [ 'ad_account_id' => $AD_ACCOUNT_ID, 'since' => $SINCE, 'until' => $UNTIL ]); $logger->info('Run started', ['ad_account_id' => $AD_ACCOUNT_ID, 'since' => $SINCE, 'until' => $UNTIL]); // Fetch from Meta $client = new MetaAdsInsightsClient($ACCESS_TOKEN, 'v24.0'); $fields = [ 'date_start','date_stop','campaign_id','campaign_name','objective', 'impressions','clicks','spend','reach','ctr','cpc','cpm','actions' ]; $logger->info('Fetching insights from Meta...'); $rows = $client->getCampaignInsights( $AD_ACCOUNT_ID, $SINCE, $UNTIL, fields: $fields, extraParams: [ 'time_increment' => 1, 'limit' => 500 ] ); $logger->info('Meta fetch done', ['fetched_rows' => count($rows)]); $repo->logToDb($RUN_ID, 'INFO', 'Meta fetch done', ['fetched_rows' => count($rows)]); // Save raw fetch output (snapshot file) $snapDir = __DIR__ . '/output'; if (!is_dir($snapDir)) { mkdir($snapDir, 0755, true); } $rawFile = $snapDir . "/meta_raw_{$RUN_ID}.json"; file_put_contents($rawFile, json_encode($rows, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); // Upsert into DB (dump first) $inserted = $repo->upsertInsightsRows($RUN_ID, $AD_ACCOUNT_ID, $rows); $logger->info('DB upsert done', ['upserted_rows' => $inserted]); $repo->logToDb($RUN_ID, 'INFO', 'DB upsert done', ['upserted_rows' => $inserted]); // Query from DB and output JSON $dbRows = $repo->fetchInsightsAsJson($AD_ACCOUNT_ID, $SINCE, $UNTIL); // Save DB output JSON (cleaned) $dbFile = $snapDir . "/meta_from_db_{$RUN_ID}.json"; file_put_contents($dbFile, json_encode($dbRows, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)); $response = [ 'ok' => true, 'run_id' => $RUN_ID, 'ad_account_id' => $AD_ACCOUNT_ID, 'since' => $SINCE, 'until' => $UNTIL, 'meta_fetched_rows' => count($rows), 'db_rows_returned' => count($dbRows), //'files' => [ // 'raw_snapshot' => $rawFile, // 'db_output' => $dbFile //], 'data' => $dbRows ]; echo json_encode($response, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); } catch (Throwable $e) { // Best-effort log 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); } } else{ header('Content-Type: application/json; charset=utf-8'); http_response_code(500); echo json_encode([ 'ok' => false, 'error' => 'Authentication Error', ], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE); }