2067 lines
81 KiB
PHP
2067 lines
81 KiB
PHP
<?php
|
||
header('Content-Type: application/json');
|
||
header('Access-Control-Allow-Origin: *');
|
||
header('Access-Control-Allow-Methods: GET, POST, DELETE, OPTIONS');
|
||
header('Access-Control-Allow-Headers: Content-Type');
|
||
|
||
// Handle preflight requests
|
||
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
|
||
http_response_code(200);
|
||
exit();
|
||
}
|
||
|
||
// Load database class and naming convention data
|
||
require_once __DIR__ . '/Database.php';
|
||
$dataFile = __DIR__ . '/../backend/data.json';
|
||
|
||
function loadData() {
|
||
global $dataFile;
|
||
if (!file_exists($dataFile)) {
|
||
return ['error' => 'Data file not found'];
|
||
}
|
||
$json = file_get_contents($dataFile);
|
||
return json_decode($json, true);
|
||
}
|
||
|
||
// Database instance - only create when needed
|
||
$db = null;
|
||
|
||
function getDatabase() {
|
||
global $db;
|
||
if ($db === null) {
|
||
try {
|
||
$db = Database::getInstance();
|
||
} catch (Exception $e) {
|
||
http_response_code(500);
|
||
echo json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]);
|
||
exit;
|
||
}
|
||
}
|
||
return $db;
|
||
}
|
||
|
||
// Helper function to validate metadata
|
||
function validateMetadata($metadata) {
|
||
$errors = [];
|
||
$data = loadData();
|
||
|
||
if (isset($metadata['brand_code'])) {
|
||
$code = $metadata['brand_code'];
|
||
if (strlen($code) < 2 || strlen($code) > 5) {
|
||
$errors[] = 'Brand code must be 2-5 characters';
|
||
}
|
||
if (!empty($code) && !array_key_exists($code, $data['brands'])) {
|
||
$errors[] = 'Invalid brand code';
|
||
}
|
||
}
|
||
|
||
if (isset($metadata['country_code'])) {
|
||
$code = $metadata['country_code'];
|
||
if (strlen($code) !== 2) {
|
||
$errors[] = 'Country code must be exactly 2 characters';
|
||
}
|
||
if (!empty($code) && !array_key_exists($code, $data['countries'])) {
|
||
$errors[] = 'Invalid country code';
|
||
}
|
||
}
|
||
|
||
if (isset($metadata['language_code'])) {
|
||
$code = $metadata['language_code'];
|
||
if (strlen($code) < 2 || strlen($code) > 3) {
|
||
$errors[] = 'Language code must be 2-3 characters';
|
||
}
|
||
if (!empty($code) && !array_key_exists($code, $data['languages'])) {
|
||
$errors[] = 'Invalid language code';
|
||
}
|
||
}
|
||
|
||
if (isset($metadata['subject_title'])) {
|
||
$title = $metadata['subject_title'];
|
||
if (strlen($title) > 25) {
|
||
$errors[] = 'Subject title must be max 25 characters';
|
||
}
|
||
if (strpos($title, '_') !== false) {
|
||
$errors[] = 'Subject title cannot contain underscores';
|
||
}
|
||
}
|
||
|
||
if (isset($metadata['asset_type'])) {
|
||
$type = $metadata['asset_type'];
|
||
if (strlen($type) !== 3) {
|
||
$errors[] = 'Asset type must be exactly 3 characters';
|
||
}
|
||
if (!empty($type) && !array_key_exists($type, $data['asset_types'])) {
|
||
$errors[] = 'Invalid asset type';
|
||
}
|
||
}
|
||
|
||
if (isset($metadata['duration_seconds'])) {
|
||
if (!is_numeric($metadata['duration_seconds']) || $metadata['duration_seconds'] < 0) {
|
||
$errors[] = 'Duration must be a positive number';
|
||
}
|
||
}
|
||
|
||
if (isset($metadata['aspect_ratio'])) {
|
||
$ratio = $metadata['aspect_ratio'];
|
||
$validRatios = ['16x9', '1x1', '2x3', '3x2', '4x5', '5x4', '9x16', '4:3'];
|
||
if (!empty($ratio) && !in_array($ratio, $validRatios)) {
|
||
$errors[] = 'Invalid aspect ratio';
|
||
}
|
||
}
|
||
|
||
return $errors;
|
||
}
|
||
|
||
// Helper function to parse filename into components for Creative X reports
|
||
// Uses the V3 naming convention: JOB_BRAND_SUBJECT_ASSET_[DURATION]_RATIO_[SPOT]_COUNTRY_LANGUAGE_[SOCIAL]_[TRACKING]
|
||
function parseReportFilename($filename) {
|
||
$result = [
|
||
'omg_job' => '',
|
||
'brand_code' => '',
|
||
'subject_title' => '',
|
||
'asset_type' => '',
|
||
'country_code' => '',
|
||
'language_code' => '',
|
||
'tracking_id' => ''
|
||
];
|
||
|
||
if (empty($filename)) return $result;
|
||
|
||
// Remove file extension
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filename);
|
||
$parts = explode('_', $filename);
|
||
|
||
if (count($parts) < 4) return $result;
|
||
|
||
// Position 1: OMG Job Number (all digits)
|
||
$startIdx = 0;
|
||
if (ctype_digit($parts[0])) {
|
||
$result['omg_job'] = $parts[0];
|
||
$startIdx = 1;
|
||
}
|
||
|
||
// Fixed positions after job number
|
||
$result['brand_code'] = $parts[$startIdx] ?? '';
|
||
$result['subject_title'] = $parts[$startIdx + 1] ?? '';
|
||
$result['asset_type'] = $parts[$startIdx + 2] ?? '';
|
||
|
||
// Scan remaining parts for country, language, and tracking ID
|
||
for ($i = $startIdx + 3; $i < count($parts); $i++) {
|
||
$part = $parts[$i];
|
||
|
||
// Skip duration (digits followed by S, e.g. 6S, 10S, 15S)
|
||
if (preg_match('/^\d+S$/i', $part)) continue;
|
||
|
||
// Skip aspect ratio (contains x with digits, e.g. 1x1, 16X9, 9x16)
|
||
if (preg_match('/^\d+[xX]\d+$/', $part)) continue;
|
||
|
||
// Skip spot version
|
||
if ($part === 'MST' || $part === 'REF') continue;
|
||
|
||
// Tracking ID: 6 alphanumeric chars (possibly with extension remnants)
|
||
// Usually last or second-to-last part
|
||
if (preg_match('/^[a-zA-Z0-9]{6}$/', $part) && $i >= count($parts) - 2) {
|
||
$result['tracking_id'] = $part;
|
||
continue;
|
||
}
|
||
|
||
// Country code: exactly 2 uppercase alpha chars
|
||
if (empty($result['country_code']) && strlen($part) == 2 && ctype_alpha($part) && ctype_upper($part)) {
|
||
$result['country_code'] = $part;
|
||
continue;
|
||
}
|
||
|
||
// Language code: 2-6 alpha chars (after country is found)
|
||
if (!empty($result['country_code']) && empty($result['language_code'])
|
||
&& strlen($part) >= 2 && strlen($part) <= 6 && ctype_alpha($part)) {
|
||
$result['language_code'] = strtolower($part);
|
||
continue;
|
||
}
|
||
}
|
||
|
||
return $result;
|
||
}
|
||
|
||
// Helper function to parse PostgreSQL array
|
||
function parsePostgresArray($pgArray) {
|
||
if (empty($pgArray) || $pgArray === '{}') {
|
||
return [];
|
||
}
|
||
$pgArray = trim($pgArray, '{}');
|
||
if (empty($pgArray)) {
|
||
return [];
|
||
}
|
||
return explode(',', $pgArray);
|
||
}
|
||
|
||
// Get action from URL
|
||
$action = isset($_GET['action']) ? $_GET['action'] : '';
|
||
$method = $_SERVER['REQUEST_METHOD'];
|
||
|
||
switch ($action) {
|
||
case 'data':
|
||
// Return naming convention data (brands, countries, etc.)
|
||
echo json_encode(loadData());
|
||
break;
|
||
|
||
case 'generate-tracking-id':
|
||
if ($method === 'GET') {
|
||
try {
|
||
$trackingId = getDatabase()->generateTrackingId();
|
||
echo json_encode(['tracking_id' => $trackingId]);
|
||
} catch (Exception $e) {
|
||
http_response_code(500);
|
||
echo json_encode(['error' => 'Failed to generate tracking ID: ' . $e->getMessage()]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'lookup-tracking-id':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingIdInput = $input['tracking_id'] ?? '';
|
||
|
||
if (empty($trackingIdInput)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
// Check for -N suffix (New Asset Mode)
|
||
$hasNewAssetMode = substr($trackingIdInput, -2) === '-N';
|
||
$trackingId = $hasNewAssetMode ? substr($trackingIdInput, 0, -2) : $trackingIdInput;
|
||
|
||
// Validate base tracking ID (must be 6 alphanumeric characters)
|
||
if (strlen($trackingId) !== 6 || !ctype_alnum($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Invalid tracking ID format']);
|
||
exit;
|
||
}
|
||
|
||
$result = getDatabase()->lookupTrackingId($trackingId);
|
||
|
||
if ($result['success']) {
|
||
// Add mode information to result
|
||
$result['new_asset_mode'] = $hasNewAssetMode;
|
||
echo json_encode($result);
|
||
} else {
|
||
http_response_code(404);
|
||
echo json_encode($result);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'lookup-master-file':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filenameInput = $input['filename'] ?? '';
|
||
|
||
if (empty($filenameInput)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Master filename is required']);
|
||
exit;
|
||
}
|
||
|
||
// 1. Extract Tracking ID from the input filename
|
||
// Remove extension first
|
||
$filenameNoExt = preg_replace('/\.[^.]+$/', '', $filenameInput);
|
||
$trackingId = '';
|
||
|
||
// Strategy 1: Try splitting by underscore and check last part
|
||
$parts = explode('_', $filenameNoExt);
|
||
$lastPart = end($parts);
|
||
if (preg_match('/^[a-zA-Z0-9]{6}(-N)?$/', $lastPart)) {
|
||
$trackingId = $lastPart;
|
||
}
|
||
|
||
// Strategy 2: If not found, try splitting by hyphen
|
||
if (empty($trackingId)) {
|
||
$parts = explode('-', $filenameNoExt);
|
||
$lastPart = end($parts);
|
||
if (preg_match('/^[a-zA-Z0-9]{6}(-N)?$/', $lastPart)) {
|
||
$trackingId = $lastPart;
|
||
}
|
||
}
|
||
|
||
// Strategy 3: Fallback - just grab last 6 characters before extension
|
||
if (empty($trackingId) && strlen($filenameNoExt) >= 6) {
|
||
$lastSix = substr($filenameNoExt, -6);
|
||
if (preg_match('/^[a-zA-Z0-9]{6}$/', $lastSix)) {
|
||
$trackingId = $lastSix;
|
||
}
|
||
}
|
||
|
||
// Strip -N suffix if present
|
||
if (!empty($trackingId) && substr($trackingId, -2) === '-N') {
|
||
$trackingId = substr($trackingId, 0, -2);
|
||
}
|
||
|
||
if (empty($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Could not extract a valid Tracking ID from the filename. Please ensure the filename ends with a 6-character Tracking ID.']);
|
||
exit;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
|
||
// 2. Lookup the specific master record for this Tracking ID
|
||
$initialMaster = $db->lookupTrackingId($trackingId);
|
||
|
||
if (!$initialMaster['success']) {
|
||
http_response_code(404);
|
||
echo json_encode(['error' => 'Tracking ID found in filename (' . $trackingId . ') but not found in database.']);
|
||
exit;
|
||
}
|
||
|
||
$originalFilename = $initialMaster['data']['master_filename'];
|
||
|
||
// 3. Find ALL master records that share this original filename
|
||
$relatedMasters = $db->getMastersByOriginalFilename($originalFilename);
|
||
|
||
// 4. Batch-fetch B1 master CX scores for all related tracking IDs
|
||
$cxScores = [];
|
||
$relatedIds = array_filter(array_map(fn($m) => $m['tracking_id'], $relatedMasters));
|
||
if (!empty($relatedIds)) {
|
||
try {
|
||
$pdo = $db->getConnection();
|
||
$placeholders = implode(',', array_fill(0, count($relatedIds), '?'));
|
||
$cxSql = "SELECT DISTINCT ON (tracking_id) tracking_id, quality_score, creativex_url, extracted_at
|
||
FROM creativex_scores
|
||
WHERE tracking_id IN ($placeholders)
|
||
AND status = 'b1-master-cx-score'
|
||
ORDER BY tracking_id, extracted_at DESC";
|
||
$cxStmt = $pdo->prepare($cxSql);
|
||
$cxStmt->execute(array_values($relatedIds));
|
||
while ($row = $cxStmt->fetch(PDO::FETCH_ASSOC)) {
|
||
$cxScores[$row['tracking_id']] = $row;
|
||
}
|
||
} catch (PDOException $e) {
|
||
error_log("Master lookup CX score fetch error: " . $e->getMessage());
|
||
}
|
||
}
|
||
|
||
// 5. Aggregate data
|
||
$results = [];
|
||
foreach ($relatedMasters as $master) {
|
||
$tid = $master['tracking_id'];
|
||
|
||
// Get derivatives for this specific tracking ID
|
||
$derivatives = $db->getDerivativesByTrackingId($tid);
|
||
|
||
// Get usage stats
|
||
$usageCount = count($derivatives);
|
||
|
||
$cx = $cxScores[$tid] ?? null;
|
||
|
||
$results[] = [
|
||
'tracking_id' => $tid,
|
||
'local_campaign_id' => $master['local_campaign_id'] ?? 'N/A',
|
||
'master_filename' => $master['original_filename'] . ($master['file_extension'] ?? ''),
|
||
'created_at' => $master['created_at'],
|
||
'upload_directory' => $master['upload_directory'],
|
||
'opentext_id' => $master['opentext_id'],
|
||
'usage_count' => $usageCount,
|
||
'derivatives' => $derivatives,
|
||
'cx_score' => $cx['quality_score'] ?? null,
|
||
'cx_url' => $cx['creativex_url'] ?? null,
|
||
'cx_extracted_at' => $cx['extracted_at'] ?? null
|
||
];
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'original_filename' => $originalFilename,
|
||
'extracted_tracking_id' => $trackingId,
|
||
'related_tracking_ids' => $results
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'get-derivatives':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
|
||
if (empty($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
$derivatives = getDatabase()->getDerivativesByTrackingId($trackingId);
|
||
echo json_encode([
|
||
'success' => true,
|
||
'tracking_id' => $trackingId,
|
||
'derivatives' => $derivatives
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'get-lifecycle-events':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
$limit = isset($input['limit']) ? (int)$input['limit'] : 50;
|
||
|
||
if (empty($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
$events = getDatabase()->getLifecycleEvents($trackingId, $limit);
|
||
echo json_encode([
|
||
'success' => true,
|
||
'tracking_id' => $trackingId,
|
||
'events' => $events
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'search-masters':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$searchTerm = $input['search'] ?? '';
|
||
$field = $input['field'] ?? 'all';
|
||
|
||
if (empty($searchTerm)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Search term is required']);
|
||
exit;
|
||
}
|
||
|
||
$results = getDatabase()->searchMasterAssets($searchTerm, $field);
|
||
echo json_encode([
|
||
'success' => true,
|
||
'results' => $results,
|
||
'count' => count($results)
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'get-statistics':
|
||
if ($method === 'GET') {
|
||
$stats = getDatabase()->getStatistics();
|
||
echo json_encode([
|
||
'success' => true,
|
||
'statistics' => $stats
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'test-connection':
|
||
if ($method === 'GET') {
|
||
$isConnected = getDatabase()->testConnection();
|
||
if ($isConnected) {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'message' => 'Database connection successful'
|
||
]);
|
||
} else {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'message' => 'Database connection failed'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'creativex-lookup':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
|
||
if (empty($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
// Validate tracking ID format
|
||
if (strlen($trackingId) !== 6 || !ctype_alnum($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Invalid tracking ID format']);
|
||
exit;
|
||
}
|
||
|
||
try {
|
||
// Query creativex_scores table by tracking_id field OR by tracking ID in filename
|
||
// Many records have tracking_id = null but include the tracking ID at the end of the filename
|
||
$db = getDatabase();
|
||
$pdo = $db->getConnection();
|
||
|
||
$sql = "SELECT quality_score, creativex_url, creativex_id, extracted_at, status
|
||
FROM creativex_scores
|
||
WHERE (tracking_id = :tracking_id
|
||
OR filename LIKE '%_' || :tracking_id || '.%'
|
||
OR filename LIKE '%_' || :tracking_id || '(%')
|
||
AND status = 'b1-master-cx-score'
|
||
ORDER BY extracted_at DESC
|
||
LIMIT 1";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute(['tracking_id' => $trackingId]);
|
||
$result = $stmt->fetch();
|
||
|
||
if ($result) {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'tracking_id' => $trackingId,
|
||
'score' => [
|
||
'creativex_score' => $result['quality_score'],
|
||
'creativex_url' => $result['creativex_url'],
|
||
'creativex_id' => $result['creativex_id'],
|
||
'updated_at' => $result['extracted_at']
|
||
]
|
||
]);
|
||
} else {
|
||
http_response_code(404);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'No Creative X score found for this tracking ID'
|
||
]);
|
||
}
|
||
} catch (PDOException $e) {
|
||
error_log("Creative X lookup error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Database query failed'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'creativex-lookup-filename':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filename = $input['filename'] ?? '';
|
||
|
||
if (empty($filename)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename is required']);
|
||
exit;
|
||
}
|
||
|
||
try {
|
||
// Query creativex_scores table by filename (partial match allowed)
|
||
$db = getDatabase();
|
||
$pdo = $db->getConnection();
|
||
|
||
$sql = "SELECT quality_score, creativex_url, creativex_id, extracted_at, filename
|
||
FROM creativex_scores
|
||
WHERE filename ILIKE :filename
|
||
AND status = 'active'
|
||
ORDER BY extracted_at DESC
|
||
LIMIT 1";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute(['filename' => '%' . $filename . '%']);
|
||
$result = $stmt->fetch();
|
||
|
||
if ($result) {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'filename' => $filename,
|
||
'score' => [
|
||
'creativex_score' => $result['quality_score'],
|
||
'creativex_url' => $result['creativex_url'],
|
||
'creativex_id' => $result['creativex_id'],
|
||
'updated_at' => $result['extracted_at']
|
||
]
|
||
]);
|
||
} else {
|
||
http_response_code(404);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'No Creative X score found for this filename'
|
||
]);
|
||
}
|
||
} catch (PDOException $e) {
|
||
error_log("Creative X filename lookup error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Database query failed'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'creativex-report':
|
||
// Search Creative X scores with filters for reporting & CSV export
|
||
// Parses filename to extract brand/country/asset/subject since most
|
||
// creativex_scores records don't have a matching master_assets row
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$searchTerm = $input['search'] ?? '';
|
||
$brandFilter = strtoupper($input['brand'] ?? '');
|
||
$countryFilter = strtoupper($input['country'] ?? '');
|
||
$assetTypeFilter = strtoupper($input['asset_type'] ?? '');
|
||
$statusFilter = $input['status'] ?? '';
|
||
$dateFrom = $input['date_from'] ?? '';
|
||
$dateTo = $input['date_to'] ?? '';
|
||
$limit = isset($input['limit']) ? min((int)$input['limit'], 1000) : 500;
|
||
|
||
try {
|
||
$db = getDatabase();
|
||
$pdo = $db->getConnection();
|
||
|
||
// Simple query - just get CX scores, we parse filenames in PHP
|
||
$conditions = [];
|
||
$params = [];
|
||
|
||
$sql = "SELECT
|
||
cs.filename,
|
||
cs.quality_score AS cx_score,
|
||
cs.creativex_url AS cx_link,
|
||
cs.creativex_id,
|
||
cs.tracking_id,
|
||
cs.status,
|
||
cs.extracted_at
|
||
FROM creativex_scores cs";
|
||
|
||
// Search term filter (on filename - the one reliable column)
|
||
if (!empty($searchTerm)) {
|
||
$conditions[] = "cs.filename ILIKE :search";
|
||
$params['search'] = '%' . $searchTerm . '%';
|
||
}
|
||
|
||
// Status filter
|
||
if (!empty($statusFilter)) {
|
||
$conditions[] = "cs.status = :status";
|
||
$params['status'] = $statusFilter;
|
||
}
|
||
|
||
// Date range filters
|
||
if (!empty($dateFrom)) {
|
||
$conditions[] = "cs.extracted_at >= :date_from";
|
||
$params['date_from'] = $dateFrom . ' 00:00:00';
|
||
}
|
||
if (!empty($dateTo)) {
|
||
$conditions[] = "cs.extracted_at <= :date_to";
|
||
$params['date_to'] = $dateTo . ' 23:59:59';
|
||
}
|
||
|
||
if (!empty($conditions)) {
|
||
$sql .= " WHERE " . implode(" AND ", $conditions);
|
||
}
|
||
|
||
$sql .= " ORDER BY cs.extracted_at DESC LIMIT :limit";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
foreach ($params as $key => $value) {
|
||
$stmt->bindValue(':' . $key, $value);
|
||
}
|
||
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
|
||
$stmt->execute();
|
||
$rawResults = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// Build a cache of master CX scores keyed by tracking_id
|
||
// so we can look up master scores for each result
|
||
$trackingIds = [];
|
||
foreach ($rawResults as $row) {
|
||
$parsed = parseReportFilename($row['filename']);
|
||
if (!empty($parsed['tracking_id'])) {
|
||
$trackingIds[$parsed['tracking_id']] = true;
|
||
}
|
||
if (!empty($row['tracking_id'])) {
|
||
$trackingIds[$row['tracking_id']] = true;
|
||
}
|
||
}
|
||
|
||
$masterScores = [];
|
||
if (!empty($trackingIds)) {
|
||
$idList = array_keys($trackingIds);
|
||
// Query master CX scores for all relevant tracking IDs
|
||
$placeholders = implode(',', array_fill(0, count($idList), '?'));
|
||
$mSql = "SELECT DISTINCT ON (tracking_id) tracking_id, quality_score
|
||
FROM creativex_scores
|
||
WHERE tracking_id IN ($placeholders)
|
||
AND status = 'b1-master-cx-score'
|
||
ORDER BY tracking_id, extracted_at DESC";
|
||
$mStmt = $pdo->prepare($mSql);
|
||
$mStmt->execute($idList);
|
||
while ($mRow = $mStmt->fetch(PDO::FETCH_ASSOC)) {
|
||
$masterScores[$mRow['tracking_id']] = $mRow['quality_score'];
|
||
}
|
||
}
|
||
|
||
// Parse filenames and apply brand/country/asset_type filters
|
||
$results = [];
|
||
foreach ($rawResults as $row) {
|
||
$parsed = parseReportFilename($row['filename']);
|
||
|
||
// Apply filters on parsed data
|
||
if (!empty($brandFilter) && strtoupper($parsed['brand_code']) !== $brandFilter) {
|
||
continue;
|
||
}
|
||
if (!empty($countryFilter) && strtoupper($parsed['country_code']) !== $countryFilter) {
|
||
continue;
|
||
}
|
||
if (!empty($assetTypeFilter) && strtoupper($parsed['asset_type']) !== $assetTypeFilter) {
|
||
continue;
|
||
}
|
||
|
||
// Look up master score using tracking ID from DB or parsed from filename
|
||
$tid = !empty($row['tracking_id']) ? $row['tracking_id'] : $parsed['tracking_id'];
|
||
$masterScore = isset($masterScores[$tid]) ? $masterScores[$tid] : null;
|
||
|
||
$results[] = [
|
||
'filename' => $row['filename'],
|
||
'cx_score' => $row['cx_score'],
|
||
'cx_link' => $row['cx_link'],
|
||
'creativex_id' => $row['creativex_id'],
|
||
'tracking_id' => $tid,
|
||
'status' => $row['status'],
|
||
'extracted_at' => $row['extracted_at'],
|
||
'brand_code' => $parsed['brand_code'],
|
||
'country_code' => $parsed['country_code'],
|
||
'language_code' => $parsed['language_code'],
|
||
'asset_type' => $parsed['asset_type'],
|
||
'subject_title' => $parsed['subject_title'],
|
||
'omg_job' => $parsed['omg_job'],
|
||
'master_cx_score' => $masterScore
|
||
];
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'results' => $results,
|
||
'count' => count($results)
|
||
]);
|
||
|
||
} catch (PDOException $e) {
|
||
error_log("Creative X report error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Database query failed: ' . $e->getMessage()
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'creativex-debug':
|
||
// Debug endpoint to check CreativeX data without status filter
|
||
if ($method === 'GET') {
|
||
try {
|
||
$db = getDatabase();
|
||
$pdo = $db->getConnection();
|
||
|
||
$trackingId = $_GET['tracking_id'] ?? '';
|
||
|
||
$result = [
|
||
'total_records' => 0,
|
||
'status_breakdown' => [],
|
||
'recent_records' => [],
|
||
'searched_tracking_id' => null
|
||
];
|
||
|
||
// Total count
|
||
$stmt = $pdo->query("SELECT COUNT(*) as total FROM creativex_scores");
|
||
$result['total_records'] = (int)$stmt->fetchColumn();
|
||
|
||
// Status breakdown
|
||
$stmt = $pdo->query("SELECT status, COUNT(*) as count FROM creativex_scores GROUP BY status ORDER BY count DESC");
|
||
$result['status_breakdown'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// Recent 20 records
|
||
$stmt = $pdo->query("
|
||
SELECT tracking_id, filename, quality_score, creativex_url, status, extracted_at
|
||
FROM creativex_scores
|
||
ORDER BY extracted_at DESC
|
||
LIMIT 20
|
||
");
|
||
$result['recent_records'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// Specific tracking ID search (without status filter)
|
||
if ($trackingId) {
|
||
$stmt = $pdo->prepare("
|
||
SELECT tracking_id, quality_score, creativex_url, creativex_id, status, extracted_at
|
||
FROM creativex_scores
|
||
WHERE tracking_id = :tracking_id
|
||
ORDER BY extracted_at DESC
|
||
");
|
||
$stmt->execute(['tracking_id' => $trackingId]);
|
||
$result['searched_tracking_id'] = [
|
||
'tracking_id' => $trackingId,
|
||
'found' => false,
|
||
'records' => []
|
||
];
|
||
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
if ($records) {
|
||
$result['searched_tracking_id']['found'] = true;
|
||
$result['searched_tracking_id']['records'] = $records;
|
||
}
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'data' => $result
|
||
]);
|
||
|
||
} catch (PDOException $e) {
|
||
error_log("CreativeX debug error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Database query failed: ' . $e->getMessage()
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'build':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
|
||
$omgJobNumber = $input['omg_job_number'] ?? '';
|
||
$brandCode = strtoupper($input['brand_code'] ?? '');
|
||
$subjectTitle = strtoupper($input['subject_title'] ?? '');
|
||
// Convert underscores to hyphens in subject title
|
||
$subjectTitle = str_replace('_', '-', $subjectTitle);
|
||
$assetType = strtoupper($input['asset_type'] ?? '');
|
||
$seconds = $input['seconds'] ?? '';
|
||
$aspectRatio = $input['aspect_ratio'] ?? '';
|
||
$spotVersion = strtoupper($input['spot_version'] ?? '');
|
||
$countryCode = strtoupper($input['country_code'] ?? '');
|
||
$languageCode = strtolower($input['language_code'] ?? '');
|
||
$socialMedia = strtoupper($input['social_media'] ?? '');
|
||
$includeDuration = $input['include_duration'] ?? false;
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
|
||
// Validation
|
||
$errors = [];
|
||
|
||
if (empty($omgJobNumber) || !ctype_digit($omgJobNumber) || strlen($omgJobNumber) > 10) {
|
||
$errors[] = 'OMG Job Number is required, must be numbers only, and max 10 digits';
|
||
}
|
||
|
||
if (empty($brandCode) || strlen($brandCode) < 2 || strlen($brandCode) > 5) {
|
||
$errors[] = 'Brand code must be 2-5 characters';
|
||
}
|
||
|
||
if (empty($countryCode) || strlen($countryCode) != 2) {
|
||
$errors[] = 'Country code must be exactly 2 characters';
|
||
}
|
||
|
||
if (empty($languageCode) || strlen($languageCode) < 2 || strlen($languageCode) > 6) {
|
||
$errors[] = 'Language code is required and must be 2-6 characters';
|
||
}
|
||
|
||
if (empty($subjectTitle) || strlen($subjectTitle) > 25) {
|
||
$errors[] = 'Subject title is required and must be max 25 characters';
|
||
}
|
||
|
||
if (empty($assetType) || strlen($assetType) != 3) {
|
||
$errors[] = 'Asset type must be exactly 3 characters';
|
||
}
|
||
|
||
// Validate spot version - must be MST or REF if provided
|
||
if (!empty($spotVersion) && !in_array($spotVersion, ['MST', 'REF'])) {
|
||
$errors[] = 'Spot Version must be either MST or REF if provided';
|
||
}
|
||
|
||
if ($includeDuration && empty($seconds)) {
|
||
$errors[] = 'Duration in seconds is required when "Include Duration" is checked';
|
||
}
|
||
|
||
if (empty($aspectRatio)) {
|
||
$errors[] = 'Aspect ratio is required';
|
||
}
|
||
|
||
// Validate social media version - must be valid code if provided
|
||
if (!empty($socialMedia)) {
|
||
$data = loadData();
|
||
$validSocialMediaCodes = array_keys($data['social_media_versions'] ?? []);
|
||
if (!in_array($socialMedia, $validSocialMediaCodes)) {
|
||
$errors[] = 'Invalid Social Media Version code. Please select from the dropdown.';
|
||
}
|
||
}
|
||
|
||
if (!empty($errors)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => implode(', ', $errors)]);
|
||
exit;
|
||
}
|
||
|
||
// Build filename - NEW ORDER: Job, Brand, Subject, Asset, Duration, Ratio, Spot, Country, Language, Social, Tracking
|
||
$parts = [$omgJobNumber, $brandCode, $subjectTitle, $assetType];
|
||
|
||
// Add duration if enabled
|
||
if ($includeDuration && !empty($seconds)) {
|
||
$parts[] = $seconds . 'S';
|
||
}
|
||
|
||
// Add aspect ratio
|
||
$parts[] = $aspectRatio;
|
||
|
||
// Add spot version (MST or REF) if selected
|
||
if (!empty($spotVersion)) {
|
||
$parts[] = $spotVersion;
|
||
}
|
||
|
||
// Add country code
|
||
$parts[] = $countryCode;
|
||
|
||
// Add language code
|
||
$parts[] = $languageCode;
|
||
|
||
// Add social media version if provided
|
||
if (!empty($socialMedia)) {
|
||
$parts[] = $socialMedia;
|
||
}
|
||
|
||
// Add tracking ID if provided
|
||
if (!empty($trackingId)) {
|
||
$parts[] = $trackingId;
|
||
}
|
||
|
||
$filename = implode('_', $parts);
|
||
|
||
// Calculate the final upload filename (without job number and tracking ID)
|
||
$uploadParts = [$brandCode, $subjectTitle, $assetType];
|
||
if ($includeDuration && !empty($seconds)) {
|
||
$uploadParts[] = $seconds . 'S';
|
||
}
|
||
$uploadParts[] = $aspectRatio;
|
||
if (!empty($spotVersion)) {
|
||
$uploadParts[] = $spotVersion;
|
||
}
|
||
$uploadParts[] = $countryCode;
|
||
$uploadParts[] = $languageCode;
|
||
if (!empty($socialMedia)) {
|
||
$uploadParts[] = $socialMedia;
|
||
}
|
||
$uploadFilename = implode('_', $uploadParts);
|
||
|
||
// Log filename generation event if tracking ID provided
|
||
if (!empty($trackingId)) {
|
||
getDatabase()->logLifecycleEvent($trackingId, 'filename_generated', [
|
||
'filename' => $filename,
|
||
'omg_job_number' => $omgJobNumber,
|
||
'upload_filename' => $uploadFilename
|
||
]);
|
||
}
|
||
|
||
echo json_encode([
|
||
'filename' => $filename,
|
||
'upload_filename' => $uploadFilename,
|
||
'has_tracking_id' => !empty($trackingId)
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'decode':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filename = $input['filename'] ?? '';
|
||
|
||
if (empty($filename)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename is required']);
|
||
exit;
|
||
}
|
||
|
||
// Remove file extension if present
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filename);
|
||
|
||
// Split by underscore
|
||
$parts = explode('_', $filename);
|
||
|
||
if (count($parts) < 6) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Invalid filename format. Expected at least 6 parts separated by underscores']);
|
||
exit;
|
||
}
|
||
|
||
// Parse components - NEW ORDER: Job, Brand, Subject, Asset, [Duration], Ratio, [Spot], Country, Language, [Social], [Tracking]
|
||
$result = [
|
||
'omg_job_number' => $parts[0],
|
||
'brand_code' => $parts[1],
|
||
'subject_title' => $parts[2],
|
||
'asset_type' => $parts[3],
|
||
'seconds' => '',
|
||
'aspect_ratio' => '',
|
||
'spot_version' => '',
|
||
'has_master' => false,
|
||
'country_code' => '',
|
||
'language_code' => '',
|
||
'social_media' => '',
|
||
'tracking_id' => '',
|
||
'new_asset_mode' => false
|
||
];
|
||
|
||
// Parse remaining parts - NEW ORDER: [Duration], Ratio, [Spot], Country, Language, [Social], [Tracking]
|
||
$currentIndex = 4;
|
||
$foundRatio = false;
|
||
$foundCountry = false;
|
||
$foundLanguage = false;
|
||
|
||
while ($currentIndex < count($parts)) {
|
||
$part = $parts[$currentIndex];
|
||
|
||
// Duration (ends with S and has numbers) - comes before ratio
|
||
if (!$foundRatio && preg_match('/^\d+S$/', $part)) {
|
||
$result['seconds'] = rtrim($part, 'S');
|
||
$currentIndex++;
|
||
}
|
||
// Aspect ratio (contains x or :)
|
||
elseif (!$foundRatio && preg_match('/[x:]/', $part)) {
|
||
$result['aspect_ratio'] = $part;
|
||
$foundRatio = true;
|
||
$currentIndex++;
|
||
}
|
||
// Spot version (MST or REF) - comes after ratio
|
||
elseif ($foundRatio && !$foundCountry && ($part === 'MST' || $part === 'REF')) {
|
||
$result['spot_version'] = $part;
|
||
if ($part === 'MST') {
|
||
$result['has_master'] = true;
|
||
}
|
||
$currentIndex++;
|
||
}
|
||
// Country code (2 chars) - comes after ratio/spot
|
||
elseif ($foundRatio && !$foundCountry && strlen($part) == 2 && ctype_alpha($part)) {
|
||
$result['country_code'] = $part;
|
||
$foundCountry = true;
|
||
$currentIndex++;
|
||
}
|
||
// Language code (2-6 chars) - comes after country
|
||
elseif ($foundCountry && !$foundLanguage && strlen($part) >= 2 && strlen($part) <= 6 && ctype_alpha($part)) {
|
||
$result['language_code'] = $part;
|
||
$foundLanguage = true;
|
||
$currentIndex++;
|
||
}
|
||
// Tracking ID (6 alphanumeric chars, optionally with -N) - at the end
|
||
elseif (preg_match('/^[a-zA-Z0-9]{6}(-N)?$/', $part)) {
|
||
$result['tracking_id'] = $part;
|
||
$result['new_asset_mode'] = substr($part, -2) === '-N';
|
||
$currentIndex++;
|
||
}
|
||
// Social media placement codes - comes after language, before tracking ID
|
||
elseif ($foundLanguage) {
|
||
// Load valid social media codes
|
||
$data = loadData();
|
||
$validSocialMediaCodes = array_keys($data['social_media_versions'] ?? []);
|
||
if (in_array($part, $validSocialMediaCodes)) {
|
||
$result['social_media'] = $part;
|
||
$currentIndex++;
|
||
} else {
|
||
// Not a social media code, skip it
|
||
$currentIndex++;
|
||
}
|
||
}
|
||
// If we haven't found ratio yet, assume this is it
|
||
elseif (!$foundRatio) {
|
||
$result['aspect_ratio'] = $part;
|
||
$foundRatio = true;
|
||
$currentIndex++;
|
||
}
|
||
else {
|
||
// Skip unknown parts
|
||
$currentIndex++;
|
||
}
|
||
}
|
||
|
||
// Load data to get full names
|
||
$data = loadData();
|
||
|
||
$result['brand_name'] = $data['brands'][$result['brand_code']] ?? 'Unknown Brand';
|
||
$result['country_name'] = $data['countries'][$result['country_code']] ?? 'Unknown Country';
|
||
$result['language_name'] = $data['languages'][$result['language_code']] ?? 'Unknown Language';
|
||
$result['asset_type_name'] = $data['asset_types'][$result['asset_type']] ?? 'Unknown Asset Type';
|
||
$result['social_media_name'] = !empty($result['social_media']) ? ($data['social_media_versions'][$result['social_media']] ?? 'Unknown Social Media') : '';
|
||
|
||
// If tracking ID exists, lookup metadata from database
|
||
if (!empty($result['tracking_id'])) {
|
||
// Strip -N suffix if present for database lookup
|
||
$baseTrackingId = $result['new_asset_mode']
|
||
? substr($result['tracking_id'], 0, -2)
|
||
: $result['tracking_id'];
|
||
|
||
$trackingResult = getDatabase()->lookupTrackingId($baseTrackingId);
|
||
if ($trackingResult['success']) {
|
||
$result['tracking_metadata'] = $trackingResult['data'];
|
||
}
|
||
}
|
||
|
||
echo json_encode($result);
|
||
}
|
||
break;
|
||
|
||
case 'list-a2-campaigns':
|
||
if ($method === 'GET') {
|
||
try {
|
||
// Load DAM client
|
||
require_once __DIR__ . '/DamClient.php';
|
||
$config = require __DIR__ . '/../config.php';
|
||
|
||
$damClient = new DamClient($config);
|
||
|
||
// Test connection first
|
||
if (!$damClient->testConnection()) {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to connect to DAM'
|
||
]);
|
||
exit;
|
||
}
|
||
|
||
// Search for A2 campaigns
|
||
$campaigns = $damClient->searchCampaigns('A2', 'Local Adaptation');
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'campaigns' => $campaigns,
|
||
'count' => count($campaigns)
|
||
]);
|
||
} catch (Exception $e) {
|
||
error_log("List A2 campaigns error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to retrieve campaigns: ' . $e->getMessage()
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'list-a6-campaigns':
|
||
if ($method === 'GET') {
|
||
try {
|
||
// Load DAM client
|
||
require_once __DIR__ . '/DamClient.php';
|
||
$config = require __DIR__ . '/../config.php';
|
||
|
||
$damClient = new DamClient($config);
|
||
|
||
// Test connection first
|
||
if (!$damClient->testConnection()) {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to connect to DAM'
|
||
]);
|
||
exit;
|
||
}
|
||
|
||
// Search for A6 campaigns
|
||
$campaigns = $damClient->searchCampaigns('A6', 'Local Adaptation');
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'campaigns' => $campaigns,
|
||
'count' => count($campaigns)
|
||
]);
|
||
} catch (Exception $e) {
|
||
error_log("List A6 campaigns error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to retrieve campaigns: ' . $e->getMessage()
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'advance-to-a3':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$campaignId = $input['campaign_id'] ?? '';
|
||
|
||
if (empty($campaignId)) {
|
||
http_response_code(400);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Campaign ID is required'
|
||
]);
|
||
exit;
|
||
}
|
||
|
||
try {
|
||
// Load DAM client
|
||
require_once __DIR__ . '/DamClient.php';
|
||
$config = require __DIR__ . '/../config.php';
|
||
|
||
$damClient = new DamClient($config);
|
||
|
||
// Update campaign status to A3
|
||
$result = $damClient->updateCampaignStatus($campaignId, 'A3');
|
||
|
||
if ($result['success']) {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'message' => $result['message']
|
||
]);
|
||
} else {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => $result['error']
|
||
]);
|
||
}
|
||
} catch (Exception $e) {
|
||
error_log("Advance to A3 error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to update campaign status: ' . $e->getMessage()
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'get-campaign-files':
|
||
if ($method === 'GET') {
|
||
$campaignId = $_GET['campaign_id'] ?? '';
|
||
|
||
if (empty($campaignId)) {
|
||
http_response_code(400);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Campaign ID is required'
|
||
]);
|
||
exit;
|
||
}
|
||
|
||
try {
|
||
// Query database for files associated with this campaign
|
||
// This is a simplified version - you may need to adjust based on your database schema
|
||
$db = getDatabase();
|
||
$pdo = $db->getConnection();
|
||
|
||
// Log the campaign ID being searched
|
||
error_log("Searching for files with local_campaign_id: " . $campaignId);
|
||
|
||
// Query derivative_assets linked to master_assets by tracking_id
|
||
// Filter by local_campaign_id in master_assets
|
||
// Group by filename to show unique files with version count
|
||
$sql = "SELECT
|
||
da.derivative_filename as filename,
|
||
MAX(da.created_at) as uploaded_at,
|
||
COUNT(*) as version_count
|
||
FROM derivative_assets da
|
||
JOIN master_assets ma ON da.tracking_id = ma.tracking_id
|
||
WHERE ma.local_campaign_id = :campaign_id
|
||
GROUP BY da.derivative_filename
|
||
ORDER BY uploaded_at DESC
|
||
LIMIT 100";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute(['campaign_id' => $campaignId]);
|
||
$files = $stmt->fetchAll();
|
||
|
||
error_log("Found " . count($files) . " unique files for campaign " . $campaignId);
|
||
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'campaign_id' => $campaignId,
|
||
'files' => $files,
|
||
'count' => count($files)
|
||
]);
|
||
} catch (PDOException $e) {
|
||
error_log("Get campaign files error: " . $e->getMessage());
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Database query failed'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'simulate-upload':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filename = $input['filename'] ?? '';
|
||
|
||
if (empty($filename)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename is required']);
|
||
exit;
|
||
}
|
||
|
||
// Remove file extension if present
|
||
$ext = '';
|
||
if (preg_match('/(\.[^.]+)$/', $filename, $matches)) {
|
||
$ext = $matches[1];
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filename);
|
||
}
|
||
|
||
// Split by underscore
|
||
$parts = explode('_', $filename);
|
||
|
||
// Remove job number (first part if it's all digits)
|
||
if (ctype_digit($parts[0])) {
|
||
array_shift($parts);
|
||
}
|
||
|
||
// Remove tracking ID (last part if it's 6 alphanumeric characters)
|
||
$trackingId = '';
|
||
if (count($parts) > 0 && preg_match('/^[a-zA-Z0-9]{6}$/', end($parts))) {
|
||
$trackingId = array_pop($parts);
|
||
|
||
// Log upload simulation event
|
||
if (!empty($trackingId)) {
|
||
getDatabase()->logLifecycleEvent($trackingId, 'upload_simulated', [
|
||
'original_filename' => $input['filename'],
|
||
'final_filename' => implode('_', $parts) . $ext
|
||
]);
|
||
}
|
||
}
|
||
|
||
$uploadFilename = implode('_', $parts) . $ext;
|
||
|
||
// Check for override metadata
|
||
$db = getDatabase();
|
||
$filenameWithoutExt = preg_replace('/\.[^.]+$/', '', $input['filename']);
|
||
$override = $db->getOverrideMetadata($filenameWithoutExt);
|
||
$hasOverride = $override !== null;
|
||
$overrideFields = [];
|
||
|
||
if ($hasOverride && isset($override['override_fields'])) {
|
||
$overrideFields = $override['override_fields'];
|
||
}
|
||
|
||
echo json_encode([
|
||
'original_filename' => $input['filename'],
|
||
'upload_filename' => $uploadFilename,
|
||
'stripped_job_number' => true,
|
||
'stripped_tracking_id' => !empty($trackingId),
|
||
'tracking_id' => $trackingId,
|
||
'has_override' => $hasOverride,
|
||
'override_fields' => $overrideFields,
|
||
'override_info' => $hasOverride ? [
|
||
'created_at' => $override['created_at'],
|
||
'created_by' => $override['created_by']
|
||
] : null
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'get-master-metadata':
|
||
// GET endpoint to load metadata for editing
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
|
||
if (empty($trackingId) || strlen($trackingId) !== 6) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Valid 6-character tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
$asset = $db->getMasterAssetForEdit($trackingId);
|
||
|
||
if ($asset) {
|
||
// Parse PostgreSQL array for tags
|
||
if (isset($asset['tags'])) {
|
||
$asset['tags'] = parsePostgresArray($asset['tags']);
|
||
}
|
||
|
||
// Add CreativeX debug info
|
||
$cxDebug = [
|
||
'creativex_in_mvp_fields' => isset($asset['mvp_fields']['creativex_score']) || isset($asset['mvp_fields']['creativex_link']),
|
||
'creativex_score' => $asset['mvp_fields']['creativex_score'] ?? 'NOT SET',
|
||
'creativex_link' => $asset['mvp_fields']['creativex_link'] ?? 'NOT SET'
|
||
];
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'asset' => $asset,
|
||
'creativex_debug' => $cxDebug
|
||
]);
|
||
} else {
|
||
http_response_code(404);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Master asset not found'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'search-masters-autocomplete':
|
||
// Search for filename autocomplete
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$searchTerm = $input['search'] ?? '';
|
||
|
||
if (strlen($searchTerm) < 2) {
|
||
echo json_encode(['success' => true, 'results' => []]);
|
||
exit;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
$results = $db->searchMastersByFilename($searchTerm, 10);
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'results' => $results
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'update-master-metadata':
|
||
// POST endpoint to save metadata changes
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
$metadata = $input['metadata'] ?? [];
|
||
|
||
// Validation
|
||
if (empty($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
// Validate metadata fields
|
||
$errors = validateMetadata($metadata);
|
||
if (!empty($errors)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => implode(', ', $errors)]);
|
||
exit;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
$result = $db->updateMasterMetadata($trackingId, $metadata, $_SESSION['user_id'] ?? null);
|
||
|
||
if ($result) {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'message' => 'Metadata updated successfully'
|
||
]);
|
||
} else {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to update metadata'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'get-metadata-history':
|
||
// GET metadata change history
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$trackingId = $input['tracking_id'] ?? '';
|
||
|
||
if (empty($trackingId)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
$history = $db->getMetadataChangeHistory($trackingId, 20);
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'history' => $history
|
||
]);
|
||
}
|
||
break;
|
||
|
||
// ========================================================================
|
||
// PRE-UPLOAD METADATA OVERRIDE ENDPOINTS
|
||
// ========================================================================
|
||
|
||
case 'lookup-metadata-by-filename':
|
||
// Load metadata for a filename before upload (for pre-upload editing)
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filenameInput = $input['filename'] ?? '';
|
||
|
||
if (empty($filenameInput)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename or tracking ID is required']);
|
||
exit;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
|
||
// Remove file extension if present
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filenameInput);
|
||
|
||
// Try to extract tracking ID from filename
|
||
$trackingId = null;
|
||
$parts = explode('_', $filename);
|
||
if (count($parts) > 0) {
|
||
$lastPart = end($parts);
|
||
// Check if last part is a tracking ID (6 alphanumeric chars, may have -N suffix)
|
||
if (preg_match('/^([a-zA-Z0-9]{6})(-N)?$/', $lastPart, $matches)) {
|
||
$trackingId = $matches[1];
|
||
}
|
||
}
|
||
|
||
// Step 1: Check if filename exists in master_assets
|
||
$existingAsset = null;
|
||
if ($trackingId) {
|
||
$existingAsset = $db->lookupTrackingId($trackingId);
|
||
}
|
||
|
||
// Step 2: Determine metadata source
|
||
$metadataSource = '';
|
||
$isExistingAsset = false;
|
||
$metadata = [];
|
||
|
||
if ($existingAsset) {
|
||
// Existing asset - load from master_assets
|
||
$isExistingAsset = true;
|
||
$metadataSource = 'existing_asset';
|
||
$asset = $db->getMasterAssetForEdit($trackingId);
|
||
if ($asset) {
|
||
$metadata = $asset;
|
||
}
|
||
} else if ($trackingId) {
|
||
// New asset with tracking ID - try lookup
|
||
$masterData = $db->lookupTrackingId($trackingId);
|
||
if ($masterData) {
|
||
$metadataSource = 'tracking_id';
|
||
// Convert master data to metadata format
|
||
$metadata = [
|
||
'tracking_id' => $trackingId,
|
||
'brand_code' => $masterData['brand_code'] ?? '',
|
||
'brand_name' => $masterData['brand_name'] ?? '',
|
||
'country_code' => $masterData['country_code'] ?? '',
|
||
'country_name' => $masterData['country_name'] ?? '',
|
||
'language_code' => $masterData['language_code'] ?? '',
|
||
'language_name' => $masterData['language_name'] ?? '',
|
||
'asset_type' => $masterData['asset_type'] ?? '',
|
||
'asset_type_name' => $masterData['asset_type_name'] ?? '',
|
||
'subject_title' => $masterData['subject_title'] ?? '',
|
||
'duration_seconds' => $masterData['duration_seconds'] ?? '',
|
||
'aspect_ratio' => $masterData['aspect_ratio'] ?? '',
|
||
'mvp_fields' => $masterData['mvp_fields'] ?? []
|
||
];
|
||
} else {
|
||
// Tracking ID exists in filename but not in database - parse filename
|
||
$metadataSource = 'filename_parsing';
|
||
}
|
||
} else {
|
||
// No tracking ID - parse filename only
|
||
$metadataSource = 'filename_parsing';
|
||
}
|
||
|
||
// Step 3: If no metadata yet, parse filename
|
||
if (empty($metadata) && $metadataSource === 'filename_parsing') {
|
||
// Use existing decode logic to parse filename
|
||
// This is a simplified version - you may want to reuse the decode logic
|
||
$parts = explode('_', $filename);
|
||
$metadata = [
|
||
'tracking_id' => $trackingId,
|
||
'brand_code' => $parts[1] ?? '',
|
||
'subject_title' => $parts[2] ?? '',
|
||
'asset_type' => $parts[3] ?? '',
|
||
'mvp_fields' => []
|
||
];
|
||
|
||
// Try to find language, country from later parts
|
||
// This is a simplified extraction - the full decode logic is more complex
|
||
foreach ($parts as $part) {
|
||
if (strlen($part) === 2 && ctype_alpha($part)) {
|
||
if (empty($metadata['country_code'])) {
|
||
$metadata['country_code'] = $part;
|
||
} else {
|
||
$metadata['language_code'] = $part;
|
||
}
|
||
}
|
||
}
|
||
|
||
// Load data.json to get display names
|
||
$data = loadData();
|
||
if (isset($metadata['brand_code']) && isset($data['brands'][$metadata['brand_code']])) {
|
||
$metadata['brand_name'] = $data['brands'][$metadata['brand_code']];
|
||
}
|
||
if (isset($metadata['country_code']) && isset($data['countries'][$metadata['country_code']])) {
|
||
$metadata['country_name'] = $data['countries'][$metadata['country_code']];
|
||
}
|
||
if (isset($metadata['language_code']) && isset($data['languages'][$metadata['language_code']])) {
|
||
$metadata['language_name'] = $data['languages'][$metadata['language_code']];
|
||
}
|
||
}
|
||
|
||
// Step 4: Check for existing override
|
||
$override = $db->getOverrideMetadata($filename);
|
||
$hasOverride = $override !== null;
|
||
|
||
// Step 5: Merge override fields if exists
|
||
if ($hasOverride && isset($override['override_fields'])) {
|
||
if (!isset($metadata['mvp_fields'])) {
|
||
$metadata['mvp_fields'] = [];
|
||
}
|
||
$metadata['mvp_fields'] = array_merge(
|
||
$metadata['mvp_fields'],
|
||
$override['override_fields']
|
||
);
|
||
}
|
||
|
||
// Return complete response
|
||
echo json_encode([
|
||
'success' => true,
|
||
'is_existing_asset' => $isExistingAsset,
|
||
'metadata_source' => $metadataSource,
|
||
'tracking_id' => $trackingId,
|
||
'filename' => $filename,
|
||
'has_override' => $hasOverride,
|
||
'metadata' => $metadata
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'save-override-metadata':
|
||
// Save pre-upload metadata override
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filename = $input['filename'] ?? '';
|
||
$trackingId = $input['tracking_id'] ?? null;
|
||
$overrideFields = $input['override_fields'] ?? [];
|
||
|
||
if (empty($filename)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename is required']);
|
||
exit;
|
||
}
|
||
|
||
// Remove file extension if present
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filename);
|
||
|
||
// Try to extract tracking ID from filename if not provided
|
||
if (empty($trackingId)) {
|
||
$parts = explode('_', $filename);
|
||
if (count($parts) > 0) {
|
||
$lastPart = end($parts);
|
||
if (preg_match('/^([a-zA-Z0-9]{6})(-N)?$/', $lastPart, $matches)) {
|
||
$trackingId = $matches[1];
|
||
}
|
||
}
|
||
}
|
||
|
||
// Get user ID from session (if available)
|
||
session_start();
|
||
$userId = $_SESSION['user_id'] ?? $_SESSION['username'] ?? 'unknown';
|
||
|
||
// Save override metadata
|
||
$db = getDatabase();
|
||
$result = $db->saveOverrideMetadata($filename, $trackingId, $overrideFields, $userId);
|
||
|
||
if ($result['success']) {
|
||
// Log lifecycle event
|
||
if ($trackingId) {
|
||
$db->logLifecycleEvent($trackingId, 'override_metadata_created', [
|
||
'filename' => $filename,
|
||
'override_id' => $result['override_id'],
|
||
'fields_count' => count($overrideFields)
|
||
], $userId);
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'override_id' => $result['override_id'],
|
||
'message' => 'Pre-upload metadata saved successfully'
|
||
]);
|
||
} else {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => $result['error']
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
// ========================================================================
|
||
// EXTERNAL UPLOAD SYSTEM INTEGRATION ENDPOINTS
|
||
// ========================================================================
|
||
|
||
case 'get-override-for-upload':
|
||
// Get override metadata for external upload system
|
||
// This endpoint is called by the external upload system to check
|
||
// if there's pre-upload metadata override for a filename
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filename = $input['filename'] ?? '';
|
||
|
||
if (empty($filename)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename is required']);
|
||
exit;
|
||
}
|
||
|
||
// Remove file extension if present
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filename);
|
||
|
||
$db = getDatabase();
|
||
$override = $db->getOverrideMetadata($filename);
|
||
|
||
if ($override) {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'has_override' => true,
|
||
'override_fields' => $override['override_fields'],
|
||
'override_id' => $override['id'],
|
||
'tracking_id' => $override['tracking_id'],
|
||
'created_at' => $override['created_at'],
|
||
'created_by' => $override['created_by']
|
||
]);
|
||
} else {
|
||
echo json_encode([
|
||
'success' => true,
|
||
'has_override' => false
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
case 'mark-override-applied':
|
||
// Mark override as applied after successful upload
|
||
// This endpoint is called by the external upload system after
|
||
// it successfully applies the override metadata during upload
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$filename = $input['filename'] ?? '';
|
||
|
||
if (empty($filename)) {
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Filename is required']);
|
||
exit;
|
||
}
|
||
|
||
// Remove file extension if present
|
||
$filename = preg_replace('/\.[^.]+$/', '', $filename);
|
||
|
||
$db = getDatabase();
|
||
$success = $db->markOverrideApplied($filename);
|
||
|
||
if ($success) {
|
||
// Try to get tracking ID for logging
|
||
$parts = explode('_', $filename);
|
||
$trackingId = null;
|
||
if (count($parts) > 0) {
|
||
$lastPart = end($parts);
|
||
if (preg_match('/^([a-zA-Z0-9]{6})(-N)?$/', $lastPart, $matches)) {
|
||
$trackingId = $matches[1];
|
||
}
|
||
}
|
||
|
||
// Log lifecycle event
|
||
if ($trackingId) {
|
||
$db->logLifecycleEvent($trackingId, 'override_metadata_applied', [
|
||
'filename' => $filename,
|
||
'applied_by_external_system' => true
|
||
]);
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'message' => 'Override marked as applied'
|
||
]);
|
||
} else {
|
||
http_response_code(500);
|
||
echo json_encode([
|
||
'success' => false,
|
||
'error' => 'Failed to mark override as applied'
|
||
]);
|
||
}
|
||
}
|
||
break;
|
||
|
||
// ========================================================================
|
||
// DATABASE MIGRATION ENDPOINT (Run once to create override_metadata table)
|
||
// ========================================================================
|
||
|
||
case 'create-override-table':
|
||
// Web-based migration to create override_metadata table
|
||
// Access via: /api.php?action=create-override-table
|
||
// Returns HTML page with migration results
|
||
|
||
header('Content-Type: text/html; charset=utf-8');
|
||
|
||
echo '<!DOCTYPE html>
|
||
<html>
|
||
<head>
|
||
<title>Create Override Metadata Table</title>
|
||
<style>
|
||
body { font-family: Arial, sans-serif; max-width: 800px; margin: 50px auto; padding: 20px; }
|
||
.success { background: #d4edda; border: 1px solid #c3e6cb; color: #155724; padding: 15px; border-radius: 5px; }
|
||
.error { background: #f8d7da; border: 1px solid #f5c6cb; color: #721c24; padding: 15px; border-radius: 5px; }
|
||
.info { background: #d1ecf1; border: 1px solid #bee5eb; color: #0c5460; padding: 15px; border-radius: 5px; }
|
||
.warning { background: #fff3cd; border: 1px solid #ffeaa7; color: #856404; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
|
||
pre { background: #f4f4f4; padding: 10px; border-radius: 5px; overflow-x: auto; }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<h1>🔧 Create Override Metadata Table</h1>
|
||
|
||
<div class="warning">
|
||
<strong>⚠️ SECURITY:</strong> After running this once, remove this case from api.php or restrict access.
|
||
</div>';
|
||
|
||
try {
|
||
$db = getDatabase();
|
||
$pdo = $db->getConnection();
|
||
|
||
echo '<div class="info"><strong>✓ Database connection successful</strong></div><br>';
|
||
|
||
// Check if table already exists
|
||
$checkSql = "SELECT to_regclass('public.override_metadata') IS NOT NULL as exists";
|
||
$stmt = $pdo->query($checkSql);
|
||
$result = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if ($result['exists']) {
|
||
echo '<div class="info">
|
||
<h2>ℹ️ Table Already Exists</h2>
|
||
<p>The <code>override_metadata</code> table already exists in the database.</p>
|
||
<p><strong>You can now:</strong></p>
|
||
<ol>
|
||
<li>Test the Metadata Editor tab in your application</li>
|
||
<li>Remove this case statement from api.php for security</li>
|
||
</ol>
|
||
</div>';
|
||
} else {
|
||
// Create the table
|
||
$sql = "
|
||
CREATE TABLE override_metadata (
|
||
id SERIAL PRIMARY KEY,
|
||
filename VARCHAR(500) NOT NULL,
|
||
tracking_id VARCHAR(6),
|
||
override_fields JSONB NOT NULL DEFAULT '{}',
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
created_by VARCHAR(255),
|
||
updated_by VARCHAR(255),
|
||
applied_to_upload BOOLEAN DEFAULT FALSE,
|
||
applied_at TIMESTAMP,
|
||
CONSTRAINT unique_filename UNIQUE (filename)
|
||
);
|
||
|
||
CREATE INDEX idx_override_metadata_filename ON override_metadata(filename);
|
||
CREATE INDEX idx_override_metadata_tracking_id ON override_metadata(tracking_id) WHERE tracking_id IS NOT NULL;
|
||
CREATE INDEX idx_override_metadata_created_at ON override_metadata(created_at DESC);
|
||
CREATE INDEX idx_override_metadata_applied ON override_metadata(applied_to_upload, applied_at);
|
||
|
||
COMMENT ON TABLE override_metadata IS 'Stores pre-upload metadata overrides';
|
||
COMMENT ON COLUMN override_metadata.filename IS 'Filename without extension';
|
||
COMMENT ON COLUMN override_metadata.override_fields IS 'JSONB object containing MVP fields';
|
||
";
|
||
|
||
$pdo->exec($sql);
|
||
|
||
echo '<div class="success">
|
||
<h2>✅ Success!</h2>
|
||
<p>The <code>override_metadata</code> table has been created successfully!</p>
|
||
<p><strong>Table structure:</strong></p>
|
||
<pre>
|
||
- id (Primary Key)
|
||
- filename (VARCHAR(500), UNIQUE)
|
||
- tracking_id (VARCHAR(6))
|
||
- override_fields (JSONB)
|
||
- created_at, updated_at (TIMESTAMP)
|
||
- created_by, updated_by (VARCHAR(255))
|
||
- applied_to_upload (BOOLEAN)
|
||
- applied_at (TIMESTAMP)
|
||
</pre>
|
||
<p><strong>Next steps:</strong></p>
|
||
<ol>
|
||
<li>✅ Test the Metadata Editor tab in your application</li>
|
||
<li>✅ Remove this case statement from api.php for security (optional)</li>
|
||
</ol>
|
||
</div>';
|
||
}
|
||
|
||
// Show table info
|
||
echo '<br><div class="info">
|
||
<h3>📊 Table Verification</h3>';
|
||
$verifySql = "SELECT COUNT(*) as count FROM override_metadata";
|
||
$stmt = $pdo->query($verifySql);
|
||
$count = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
echo '<p>Current records: <strong>' . $count['count'] . '</strong></p>
|
||
</div>';
|
||
|
||
} catch (Exception $e) {
|
||
echo '<div class="error">
|
||
<h2>❌ Error</h2>
|
||
<p><strong>Failed to create table:</strong></p>
|
||
<pre>' . htmlspecialchars($e->getMessage()) . '</pre>
|
||
<p><strong>Possible solutions:</strong></p>
|
||
<ul>
|
||
<li>Check database connection settings</li>
|
||
<li>Ensure database user has CREATE TABLE permissions</li>
|
||
<li>Contact your database administrator</li>
|
||
</ul>
|
||
</div>';
|
||
}
|
||
|
||
echo '</body></html>';
|
||
exit;
|
||
|
||
case 'brands':
|
||
case 'countries':
|
||
case 'asset-types':
|
||
case 'languages':
|
||
// These endpoints remain the same as v1 - using JSON file
|
||
if ($method === 'GET') {
|
||
$data = loadData();
|
||
$key = str_replace('-', '_', $action);
|
||
echo json_encode($data[$key] ?? []);
|
||
}
|
||
break;
|
||
|
||
case 'reporting-summary':
|
||
if ($method === 'GET') {
|
||
$db = getDatabase();
|
||
$dateFrom = $_GET['date_from'] ?? '';
|
||
$dateTo = $_GET['date_to'] ?? '';
|
||
$stats = $db->getReportingSummaryCounts();
|
||
|
||
// Parse filenames to find most active brand (uses date-filtered data)
|
||
$masters = $db->getReportingMasterData($dateFrom, $dateTo);
|
||
$data = loadData();
|
||
$brandDerivCounts = [];
|
||
foreach ($masters as $m) {
|
||
$parsed = parseReportFilename($m['original_filename']);
|
||
$bc = $parsed['brand_code'];
|
||
if (!empty($bc)) {
|
||
if (!isset($brandDerivCounts[$bc])) $brandDerivCounts[$bc] = 0;
|
||
$brandDerivCounts[$bc] += (int)$m['derivative_count'];
|
||
}
|
||
}
|
||
arsort($brandDerivCounts);
|
||
$topBrandCode = key($brandDerivCounts);
|
||
if ($topBrandCode) {
|
||
$brandName = $data['brands'][$topBrandCode] ?? $topBrandCode;
|
||
$stats['most_active_brand'] = $brandName . ' (' . $topBrandCode . ')';
|
||
$stats['most_active_brand_count'] = $brandDerivCounts[$topBrandCode];
|
||
} else {
|
||
$stats['most_active_brand'] = 'N/A';
|
||
$stats['most_active_brand_count'] = 0;
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'summary' => $stats
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'reporting-breakdowns':
|
||
if ($method === 'GET') {
|
||
$db = getDatabase();
|
||
$dateFrom = $_GET['date_from'] ?? '';
|
||
$dateTo = $_GET['date_to'] ?? '';
|
||
$masters = $db->getReportingMasterData($dateFrom, $dateTo);
|
||
$data = loadData();
|
||
|
||
// Aggregate by parsing filenames
|
||
$brands = [];
|
||
$assetTypes = [];
|
||
$countries = [];
|
||
|
||
foreach ($masters as $m) {
|
||
$parsed = parseReportFilename($m['original_filename']);
|
||
$derivCount = (int)$m['derivative_count'];
|
||
$createdAt = $m['created_at'];
|
||
|
||
// Brand aggregation
|
||
$bc = $parsed['brand_code'];
|
||
if (!empty($bc)) {
|
||
if (!isset($brands[$bc])) {
|
||
$brands[$bc] = ['brand_code' => $bc, 'brand_name' => $data['brands'][$bc] ?? $bc, 'master_count' => 0, 'derivative_count' => 0, 'latest_asset' => null];
|
||
}
|
||
$brands[$bc]['master_count']++;
|
||
$brands[$bc]['derivative_count'] += $derivCount;
|
||
if ($createdAt && (!$brands[$bc]['latest_asset'] || $createdAt > $brands[$bc]['latest_asset'])) {
|
||
$brands[$bc]['latest_asset'] = $createdAt;
|
||
}
|
||
}
|
||
|
||
// Asset type aggregation
|
||
$at = $parsed['asset_type'];
|
||
if (!empty($at)) {
|
||
if (!isset($assetTypes[$at])) {
|
||
$assetTypes[$at] = ['type_code' => $at, 'type_name' => $data['asset_types'][$at] ?? $at, 'master_count' => 0, 'derivative_count' => 0];
|
||
}
|
||
$assetTypes[$at]['master_count']++;
|
||
$assetTypes[$at]['derivative_count'] += $derivCount;
|
||
}
|
||
|
||
// Country aggregation
|
||
$cc = $parsed['country_code'];
|
||
if (!empty($cc)) {
|
||
if (!isset($countries[$cc])) {
|
||
$countries[$cc] = ['country_code' => $cc, 'country_name' => $data['countries'][$cc] ?? $cc, 'master_count' => 0, 'derivative_count' => 0];
|
||
}
|
||
$countries[$cc]['master_count']++;
|
||
$countries[$cc]['derivative_count'] += $derivCount;
|
||
}
|
||
}
|
||
|
||
// Sort by master_count descending
|
||
usort($brands, fn($a, $b) => $b['master_count'] - $a['master_count']);
|
||
usort($assetTypes, fn($a, $b) => $b['master_count'] - $a['master_count']);
|
||
usort($countries, fn($a, $b) => $b['master_count'] - $a['master_count']);
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'brands' => array_values($brands),
|
||
'countries' => array_values($countries)
|
||
]);
|
||
}
|
||
break;
|
||
|
||
case 'reporting-asset-explorer':
|
||
if ($method === 'POST') {
|
||
$input = json_decode(file_get_contents('php://input'), true);
|
||
$searchTerm = trim($input['search'] ?? '');
|
||
|
||
if (empty($searchTerm)) {
|
||
echo json_encode(['success' => false, 'error' => 'Please enter a filename, tracking ID, or search term.']);
|
||
break;
|
||
}
|
||
|
||
$db = getDatabase();
|
||
$result = $db->assetExplorerSearch($searchTerm);
|
||
$data = loadData();
|
||
|
||
// Parse filenames for display names
|
||
foreach ($result['masters'] as &$m) {
|
||
$parsed = parseReportFilename($m['original_filename']);
|
||
$m['brand_code'] = $parsed['brand_code'];
|
||
$m['brand_name'] = $data['brands'][$parsed['brand_code']] ?? $parsed['brand_code'];
|
||
$m['asset_type'] = $parsed['asset_type'];
|
||
$m['asset_type_name'] = $data['asset_types'][$parsed['asset_type']] ?? $parsed['asset_type'];
|
||
$m['country_code'] = $parsed['country_code'];
|
||
$m['country_name'] = $data['countries'][$parsed['country_code']] ?? $parsed['country_code'];
|
||
$m['subject_title'] = $parsed['subject_title'];
|
||
}
|
||
|
||
foreach ($result['derivatives'] as &$d) {
|
||
$parsed = parseReportFilename($d['derivative_filename']);
|
||
$d['brand_code'] = $parsed['brand_code'];
|
||
$d['country_code'] = $parsed['country_code'];
|
||
$d['asset_type'] = $parsed['asset_type'];
|
||
}
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'masters' => $result['masters'],
|
||
'derivatives' => $result['derivatives'],
|
||
'total_masters' => count($result['masters']),
|
||
'total_derivatives' => count($result['derivatives'])
|
||
]);
|
||
}
|
||
break;
|
||
|
||
default:
|
||
http_response_code(400);
|
||
echo json_encode(['error' => 'Invalid action']);
|
||
break;
|
||
}
|
||
?>
|