22

✏️ Editing: backupsnapchat.php

<?php
declare(strict_types=1);

/**
 * Snapchat Marketing API - Meta-style extractor with auto refresh token support
 *
 * ENDPOINTS:
 *   - ?endpoint=campaign  => 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') ?: 'coreagen_marketing_insights';
$DB_USER = getenv('DB_USER') ?: 'coreagen_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;
    }

$campaignRaw = $client->listCampaigns($AD_ACCOUNT_ID);
    $campaignMap = [];
    foreach ($campaignRaw as $item) {
        $campaign = $item['campaign'] ?? null;
        if (!is_array($campaign) || empty($campaign['id'])) 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);
}

MR SKB