ferrero-naming-tool-nv/public-v2/Database.php

<?php
/**
 * Database Connection Class for Ferrero Tracking System
 * Handles PostgreSQL connections and queries
 */

class Database {
    private static $instance = null;
    private $pdo = null;
    private $config = null;

    /**
     * Private constructor for singleton pattern
     */
    private function __construct() {
        $this->config = require __DIR__ . '/../config.php';
        $this->connect();
    }

    /**
     * Get singleton instance
     */
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Get PDO connection (for advanced usage like migrations)
     */
    public function getPDO() {
        return $this->pdo;
    }

    /**
     * Establish database connection
     */
    private function connect() {
        try {
            $db = $this->config['database'];
            $dsn = sprintf(
                "pgsql:host=%s;port=%s;dbname=%s",
                $db['host'],
                $db['port'],
                $db['dbname']
            );

            $this->pdo = new PDO(
                $dsn,
                $db['user'],
                $db['password'],
                $db['options']
            );

            // Set charset
            $this->pdo->exec("SET NAMES 'UTF8'");

        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
    }

    /**
     * Get PDO instance
     */
    public function getConnection() {
        return $this->pdo;
    }

    /**
     * Lookup tracking ID and return master asset data
     */
    public function lookupTrackingId($trackingId) {
        $sql = "SELECT * FROM master_assets WHERE tracking_id = :tracking_id";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['tracking_id' => $trackingId]);
            $result = $stmt->fetch();

            if ($result) {
                return [
                    'success' => true,
                    'tracking_id' => $trackingId,
                    'data' => [
                        'master_filename' => $result['original_filename'],
                        'master_extension' => $result['file_extension'],
                        'upload_directory' => $result['upload_directory'],
                        'opentext_id' => $result['opentext_id'],
                        'created_date' => $result['created_at'],
                        'metadata' => [
                            'brand_code' => $result['brand_code'],
                            'brand_name' => $result['brand_name'],
                            'country_code' => $result['country_code'],
                            'country_name' => $result['country_name'],
                            'language_code' => $result['language_code'],
                            'language_name' => $result['language_name'],
                            'subject_title' => $result['subject_title'],
                            'asset_type' => $result['asset_type'],
                            'asset_type_name' => $result['asset_type_name'],
                            'duration' => $result['duration_seconds'],
                            'aspect_ratio' => $result['aspect_ratio'],
                            'tags' => $this->parseArray($result['tags']),
                            'description' => $result['description']
                        ]
                    ]
                ];
            }

            return ['success' => false, 'error' => 'Tracking ID not found'];

        } catch (PDOException $e) {
            error_log("Lookup error: " . $e->getMessage());
            return ['success' => false, 'error' => 'Database query failed'];
        }
    }

    /**
     * Generate a unique 6-character tracking ID
     */
    public function generateTrackingId() {
        $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $maxAttempts = 100;
        $attempts = 0;

        while ($attempts < $maxAttempts) {
            $trackingId = '';
            for ($i = 0; $i < 6; $i++) {
                $trackingId .= $characters[random_int(0, strlen($characters) - 1)];
            }

            // Check if ID already exists
            $sql = "SELECT COUNT(*) FROM tracking_id_log WHERE tracking_id = :tracking_id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['tracking_id' => $trackingId]);

            if ($stmt->fetchColumn() == 0) {
                // ID is unique, log it
                $insertSql = "INSERT INTO tracking_id_log (tracking_id, assigned_to_asset) VALUES (:tracking_id, FALSE)";
                $insertStmt = $this->pdo->prepare($insertSql);
                $insertStmt->execute(['tracking_id' => $trackingId]);

                return $trackingId;
            }

            $attempts++;
        }

        throw new Exception("Failed to generate unique tracking ID after $maxAttempts attempts");
    }

    /**
     * Get all master assets with a specific original filename
     */
    public function getMastersByOriginalFilename($filename) {
        $sql = "SELECT * FROM master_assets 
                WHERE original_filename = :filename 
                ORDER BY created_at DESC";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['filename' => $filename]);
            
            return $stmt->fetchAll();

        } catch (PDOException $e) {
            error_log("Get masters by filename error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Get all master assets
     */
    public function getAllMasterAssets($limit = 100, $offset = 0) {
        $sql = "SELECT * FROM v_master_assets_complete
                ORDER BY created_at DESC
                LIMIT :limit OFFSET :offset";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll();

        } catch (PDOException $e) {
            error_log("Get all masters error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Get derivative assets for a tracking ID
     */
    public function getDerivativesByTrackingId($trackingId) {
        // Get unique derivative filenames with count of versions and latest upload date
        $sql = "SELECT 
                    derivative_filename,
                    COUNT(*) as version_count,
                    MAX(created_at) as latest_created_at,
                    MAX(uploaded_at) as latest_uploaded_at,
                    array_agg(DISTINCT upload_status) as statuses
                FROM derivative_assets
                WHERE tracking_id = :tracking_id
                GROUP BY derivative_filename
                ORDER BY latest_created_at DESC";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['tracking_id' => $trackingId]);

            return $stmt->fetchAll();

        } catch (PDOException $e) {
            error_log("Get derivatives error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Log a lifecycle event
     */
    public function logLifecycleEvent($trackingId, $eventType, $eventData = [], $userId = null) {
        $sql = "INSERT INTO asset_lifecycle_events
                (tracking_id, event_type, event_data, user_id)
                VALUES (:tracking_id, :event_type, :event_data, :user_id)";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'tracking_id' => $trackingId,
                'event_type' => $eventType,
                'event_data' => json_encode($eventData),
                'user_id' => $userId
            ]);

            return true;

        } catch (PDOException $e) {
            error_log("Log event error: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Get lifecycle events for a tracking ID
     */
    public function getLifecycleEvents($trackingId, $limit = 50) {
        $sql = "SELECT * FROM asset_lifecycle_events
                WHERE tracking_id = :tracking_id
                ORDER BY event_timestamp DESC
                LIMIT :limit";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':tracking_id', $trackingId, PDO::PARAM_STR);
            $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll();

        } catch (PDOException $e) {
            error_log("Get lifecycle events error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Search master assets
     */
    public function searchMasterAssets($searchTerm, $field = 'all') {
        $searchTerm = '%' . $searchTerm . '%';

        $whereClauses = [];
        $params = [];

        if ($field === 'all' || $field === 'filename') {
            $whereClauses[] = "original_filename ILIKE :search_filename";
            $params['search_filename'] = $searchTerm;
        }

        if ($field === 'all' || $field === 'brand') {
            $whereClauses[] = "brand_name ILIKE :search_brand OR brand_code ILIKE :search_brand_code";
            $params['search_brand'] = $searchTerm;
            $params['search_brand_code'] = $searchTerm;
        }

        if ($field === 'all' || $field === 'tracking_id') {
            $whereClauses[] = "tracking_id ILIKE :search_tracking";
            $params['search_tracking'] = $searchTerm;
        }

        if ($field === 'all' || $field === 'opentext_id') {
            $whereClauses[] = "opentext_id ILIKE :search_opentext";
            $params['search_opentext'] = $searchTerm;
        }

        if (empty($whereClauses)) {
            return [];
        }

        $sql = "SELECT * FROM v_master_assets_complete
                WHERE " . implode(' OR ', $whereClauses) . "
                ORDER BY created_at DESC
                LIMIT 100";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);

            return $stmt->fetchAll();

        } catch (PDOException $e) {
            error_log("Search error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Parse PostgreSQL array string to PHP array
     */
    private function parseArray($pgArray) {
        if (empty($pgArray) || $pgArray === '{}') {
            return [];
        }

        // Remove braces and split by comma
        $pgArray = trim($pgArray, '{}');
        if (empty($pgArray)) {
            return [];
        }

        return explode(',', $pgArray);
    }

    /**
     * Test database connection
     */
    public function testConnection() {
        try {
            $stmt = $this->pdo->query('SELECT 1');
            return true;
        } catch (PDOException $e) {
            error_log("Connection test failed: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Get database statistics
     */
    public function getStatistics() {
        $stats = [];

        try {
            // Count master assets
            $stmt = $this->pdo->query('SELECT COUNT(*) FROM master_assets WHERE is_deleted = FALSE');
            $stats['total_masters'] = $stmt->fetchColumn();

            // Count derivative assets
            $stmt = $this->pdo->query('SELECT COUNT(*) FROM derivative_assets');
            $stats['total_derivatives'] = $stmt->fetchColumn();

            // Count tracking IDs
            $stmt = $this->pdo->query('SELECT COUNT(*) FROM tracking_id_log');
            $stats['total_tracking_ids'] = $stmt->fetchColumn();

            // Count lifecycle events
            $stmt = $this->pdo->query('SELECT COUNT(*) FROM asset_lifecycle_events');
            $stats['total_events'] = $stmt->fetchColumn();

            // Recent ingestions (last 7 days)
            $stmt = $this->pdo->query("SELECT COUNT(*) FROM master_assets
                                      WHERE created_at > NOW() - INTERVAL '7 days'");
            $stats['recent_ingestions'] = $stmt->fetchColumn();

            return $stats;

        } catch (PDOException $e) {
            error_log("Get statistics error: " . $e->getMessage());
            return $stats;
        }
    }

    /**
     * Get master asset for editing (returns full record with MVP fields extracted)
     */
    public function getMasterAssetForEdit($trackingId) {
        $sql = "SELECT * FROM master_assets
                WHERE tracking_id = :tracking_id
                AND is_deleted = FALSE";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['tracking_id' => $trackingId]);
            $asset = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($asset && !empty($asset['full_metadata'])) {
                // Decode JSONB metadata
                $fullMetadata = json_decode($asset['full_metadata'], true);

                // Extract MVP fields from JSONB and add to asset array
                $asset['mvp_fields'] = $this->extractMvpFieldsFromJsonb($fullMetadata);

                // Populate read-only fields from extracted metadata if database columns are null
                if (empty($asset['asset_type']) && !empty($asset['mvp_fields']['asset_type'])) {
                    $asset['asset_type'] = $asset['mvp_fields']['asset_type'];
                    // Extract the code (value before display value) if format is "code - display"
                    $asset['asset_type_name'] = $asset['mvp_fields']['asset_type'];
                }

                if (empty($asset['duration_seconds']) && !empty($asset['mvp_fields']['duration_seconds'])) {
                    $asset['duration_seconds'] = $asset['mvp_fields']['duration_seconds'];
                }

                if (empty($asset['aspect_ratio']) && !empty($asset['mvp_fields']['aspect_ratio'])) {
                    $asset['aspect_ratio'] = $asset['mvp_fields']['aspect_ratio'];
                }

                if (empty($asset['subject_title']) && !empty($asset['mvp_fields']['subject_title'])) {
                    $asset['subject_title'] = $asset['mvp_fields']['subject_title'];
                }

                // Populate brand, country, and language fields
                if (empty($asset['brand_name'])) {
                    // Try campaign brand first, then regular brand
                    $brand = $asset['mvp_fields']['brand_name'] ?? $asset['mvp_fields']['campaign_brand'] ?? null;
                    if ($brand) {
                        $asset['brand_name'] = $brand;
                        // For now, use the same value for brand_code (would need code mapping in production)
                        $asset['brand_code'] = $brand;
                    }
                }

                if (empty($asset['country_name']) && !empty($asset['mvp_fields']['country_name'])) {
                    $asset['country_name'] = $asset['mvp_fields']['country_name'];
                    $asset['country_code'] = $asset['mvp_fields']['country_name'];
                }

                if (empty($asset['language_name'])) {
                    $language = $asset['mvp_fields']['language_name'] ?? $asset['mvp_fields']['main_languages'] ?? null;
                    if ($language) {
                        $asset['language_name'] = $language;
                        $asset['language_code'] = $language;
                    }
                }
            }

            // Fetch CreativeX data from creativex_scores table
            if ($asset) {
                // Search for CreativeX score 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
                $cxSql = "SELECT quality_score, creativex_url, creativex_id, status, extracted_at
                          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";

                try {
                    $cxStmt = $this->pdo->prepare($cxSql);
                    $cxStmt->execute(['tracking_id' => $trackingId]);
                    $cxData = $cxStmt->fetch(PDO::FETCH_ASSOC);

                    error_log("CreativeX lookup for tracking_id: $trackingId - Found: " . ($cxData ? 'YES' : 'NO'));
                    if ($cxData) {
                        error_log("CreativeX data: score={$cxData['quality_score']}, url={$cxData['creativex_url']}, status={$cxData['status']}");
                    }

                    if ($cxData) {
                        // Add CreativeX data to mvp_fields if it exists
                        if (!isset($asset['mvp_fields'])) {
                            $asset['mvp_fields'] = [];
                        }
                        $asset['mvp_fields']['creativex_score'] = $cxData['quality_score'];
                        $asset['mvp_fields']['creativex_link'] = $cxData['creativex_url'];
                    }
                } catch (PDOException $e) {
                    error_log("CreativeX lookup error: " . $e->getMessage());
                    // Don't fail the whole request if CreativeX lookup fails
                }
            }

            return $asset;
        } catch (PDOException $e) {
            error_log("Get master for edit error: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Extract MVP field values from full_metadata JSONB
     */
    private function extractMvpFieldsFromJsonb($fullMetadata) {
        $mvpFields = [];

        // Check if metadata structure exists
        if (!isset($fullMetadata['metadata']['metadata_element_list']) ||
            !is_array($fullMetadata['metadata']['metadata_element_list'])) {
            return $mvpFields;
        }

        // Map of field IDs to extract
        $fieldMapping = [
            // Read-only key fields (for database columns)
            'FERRERO.FIELD.MKTG.ASSET TYPE' => 'asset_type',
            'ARTESIA.EMBEDDED.FIELD.DURATION' => 'duration_seconds',
            'FERRERO.ASPECT.RATIO.POC' => 'aspect_ratio',
            'ARTESIA.FIELD.ASSET NAME' => 'subject_title',
            'FERRERO.TABULAR.FIELD.BRAND' => 'brand_name',
            'FERRERO.FIELD.CAMPAIGN_BRAND' => 'campaign_brand',
            'MAIN_LANGUAGES' => 'language_name',
            'FERRERO.TABULAR.FIELD.MAIN LANGUAGES' => 'main_languages',
            'COUNTRY' => 'country_name',

            // Asset Info
            'FERRERO.FIELD.FISCAL YEAR' => 'fiscal_year',
            'FERRERO.TABULAR.FIELD.ASSETCOMPLIANCE' => 'asset_compliance',
            'FERRERO.FIELD.MARKETING.FLAVOUR' => 'flavour',
            'FERRERO.FIELD.MARKETING.SIZE' => 'size',
            'FERRERO.FIELD.SUB BRAND' => 'sub_brand',
            'FERRERO.FIELD.ASSET VALIDITY START PERIOD' => 'validity_start',
            'FERRERO.FIELD.ASSET VALIDITY END PERIOD' => 'validity_end',
            'FERRERO.TABULAR.FIELD.MARKETING.TAG' => 'marketing_tag',

            // Marketing
            'FERRERO.MARKETING.FIELD.AGENCY NAME' => 'agency_name',
            'FERRERO.MARKETING.FIELD.SPOT_VERSION' => 'spot_version',
            'FERRERO.MARKETING.FIELD.DIRECTOR_NAME' => 'director_name',
            'FERRERO.MARKETING.FIELD.VIDEO_POST_PROD_COMPANY' => 'video_post_prod_company',
            'FERRERO.MARKETING.FIELD.VID_POST_PROD_CONTACT' => 'video_post_prod_contact',
            'FERRERO.MARKETING.FIELD.AUDIO_POST_PROD_COMPANY' => 'audio_post_prod_company',
            'FERRERO.MARKETING.FIELD.AUDIO_POST_PROD_CONTACT' => 'audio_post_prod_contact',

            // Market/Rights
            'FERRERO.MARKET.FIELD.TYPE_VID' => 'video_type',
            'FERRERO.MARKET.FIELD.IPRIGHT' => 'ip_rights',
            'FERRERO.MARKET.PROD_COMPANY' => 'production_company',
            'FERRERO.MARKET.FIELD.LICENSIN' => 'licensing',
            'FERRERO.MARKET.FIELD.BUYOUT' => 'buyout',
            'FERRERO.MARKET.FIELD.FERRERO PROPERTY' => 'ferrero_property',
            'FERRERO.MARKET.VID_N_STAT' => 'video_status',
            'FERRERO.MARKET.FIELD.LICENSE' => 'license',

            // CreativeX
            'FERRERO.TAB.FIELD.CREATIVEX' => 'creativex_score',
            'FERRERO.FIELD.CREATIVEX LINK' => 'creativex_link',

            // Validity dates (Campaign category)
            'FERRERO.FIELD.VALIDITY STARTING DATE' => 'promo_validity_start',
            'FERRERO.FIELD.VALIDITY ENDING DATE' => 'promo_validity_end',
        ];

        // Iterate through categories
        foreach ($fullMetadata['metadata']['metadata_element_list'] as $category) {
            // Check if category has metadata_element_list
            if (!isset($category['metadata_element_list']) || !is_array($category['metadata_element_list'])) {
                continue;
            }

            // Extract field values from this category
            foreach ($category['metadata_element_list'] as $field) {
                $fieldId = $field['id'] ?? '';

                if (isset($fieldMapping[$fieldId])) {
                    $key = $fieldMapping[$fieldId];
                    $value = $this->extractFieldValue($field);
                    $mvpFields[$key] = $value;
                }
            }
        }

        return $mvpFields;
    }

    /**
     * Extract value from a DAM field structure
     */
    private function extractFieldValue($field) {
        if (!isset($field['value'])) {
            return null;
        }

        $value = $field['value'];

        // If value is not an array, return it directly (primitive type)
        if (!is_array($value)) {
            return $value;
        }

        // Check if this is just a metadata wrapper with no actual value
        // (only contains is_locked, domain_value, cascading_domain_value, etc.)
        if (!isset($value['value']) && isset($value['is_locked'])) {
            return null;
        }

        // Handle nested value structure from OpenText DAM
        // Structure: field -> value -> value -> {display_value or field_value -> value}
        if (isset($value['value'])) {
            $innerValue = $value['value'];

            // Prefer display_value for domain/dropdown fields (e.g., "Brand Book" vs "brandbook")
            if (is_array($innerValue) && isset($innerValue['display_value'])) {
                return $innerValue['display_value'];
            }

            // Fall back to field_value -> value path
            if (is_array($innerValue) && isset($innerValue['field_value']['value'])) {
                return $innerValue['field_value']['value'];
            }

            // Handle direct value (datetime, numbers, etc.)
            if (is_array($innerValue) && isset($innerValue['value'])) {
                // Continue drilling down
                while (is_array($innerValue) && isset($innerValue['value'])) {
                    $innerValue = $innerValue['value'];
                }
                return $innerValue;
            }

            // Return inner value if it's a primitive
            if (!is_array($innerValue)) {
                return $innerValue;
            }

            // If innerValue is still an array with only metadata, return null
            if (isset($innerValue['is_locked'])) {
                return null;
            }

            return $innerValue;
        }

        // If we get here, it's an array without 'value' key and without 'is_locked' key
        // Return as-is (might be an array of actual data)
        return $value;
    }

    /**
     * Search master assets by filename pattern for autocomplete
     */
    public function searchMastersByFilename($filename, $limit = 10) {
        // Search both original_filename alone and concatenated with file_extension
        // This allows users to search with or without the file extension
        $sql = "SELECT tracking_id, original_filename, file_extension,
                       local_campaign_id, created_at
                FROM master_assets
                WHERE (original_filename ILIKE :filename
                   OR CONCAT(original_filename, file_extension) ILIKE :filename)
                AND is_deleted = FALSE
                ORDER BY created_at DESC
                LIMIT :limit";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':filename', '%' . $filename . '%', PDO::PARAM_STR);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Search by filename error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Update master asset metadata (including JSONB MVP fields)
     */
    public function updateMasterMetadata($trackingId, $metadata, $userId = null) {
        // Allowed fields for direct column update (read-only fields excluded)
        $allowedFields = [
            'original_filename', 'file_extension', 'upload_directory', 'opentext_id',
            'local_campaign_id', 'brand_code', 'brand_name', 'country_code', 'country_name',
            'language_code', 'language_name', 'subject_title', 'asset_type',
            'asset_type_name', 'duration_seconds', 'aspect_ratio', 'tags', 'description'
        ];

        // Check if we have MVP fields to update in JSONB
        $mvpFieldsToUpdate = isset($metadata['mvp_fields']) ? $metadata['mvp_fields'] : [];

        $updateParts = [];
        $params = ['tracking_id' => $trackingId];

        // Handle direct column updates
        foreach ($allowedFields as $field) {
            if (array_key_exists($field, $metadata)) {
                $updateParts[] = "$field = :$field";
                // Special handling for tags array
                if ($field === 'tags' && is_array($metadata[$field])) {
                    $params[$field] = '{' . implode(',', $metadata[$field]) . '}';
                } else {
                    $params[$field] = $metadata[$field];
                }
            }
        }

        try {
            $this->pdo->beginTransaction();

            // Update direct columns if any
            if (!empty($updateParts)) {
                $sql = "UPDATE master_assets SET " . implode(', ', $updateParts) .
                       " WHERE tracking_id = :tracking_id AND is_deleted = FALSE";

                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
            }

            // Update MVP fields in JSONB if any
            if (!empty($mvpFieldsToUpdate)) {
                $this->updateMvpFieldsInJsonb($trackingId, $mvpFieldsToUpdate);
            }

            // Log the metadata change event
            $updatedFields = array_merge(array_keys($metadata), array_keys($mvpFieldsToUpdate));
            if (!empty($updatedFields)) {
                $this->logLifecycleEvent($trackingId, 'metadata_updated', [
                    'updated_fields' => $updatedFields,
                    'user_id' => $userId
                ], $userId);
            }

            $this->pdo->commit();
            return true;

        } catch (PDOException $e) {
            $this->pdo->rollBack();
            error_log("Update metadata error: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Update MVP fields in the full_metadata JSONB column
     */
    private function updateMvpFieldsInJsonb($trackingId, $mvpFields) {
        // First, get the current full_metadata
        $sql = "SELECT full_metadata FROM master_assets WHERE tracking_id = :tracking_id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['tracking_id' => $trackingId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$result || empty($result['full_metadata'])) {
            return false;
        }

        $fullMetadata = json_decode($result['full_metadata'], true);

        if (!isset($fullMetadata['fields']) || !is_array($fullMetadata['fields'])) {
            return false;
        }

        // Map of friendly names back to DAM field IDs
        $reverseFieldMapping = [
            'fiscal_year' => 'FERRERO.FIELD.FISCAL YEAR',
            'asset_compliance' => 'FERRERO.FIELD.ASSETCOMPLIANCE',
            'flavour' => 'FERRERO.FIELD.MARKETING.FLAVOUR',
            'size' => 'FERRERO.FIELD.MARKETING.SIZE',
            'sub_brand' => 'FERRERO.FIELD.SUB BRAND',
            'validity_start' => 'FERRERO.FIELD.ASSET VALIDITY START PERIOD',
            'validity_end' => 'FERRERO.FIELD.ASSET VALIDITY END PERIOD',
            'marketing_tag' => 'MARKETING_TAG',
            'agency_name' => 'FERRERO.MARKETING.FIELD.AGENCY NAME',
            'spot_version' => 'FERRERO.MARKETING.FIELD.SPOT_VERSION',
            'director_name' => 'FERRERO.MARKETING.FIELD.DIRECTOR_NAME',
            'video_post_prod_company' => 'FERRERO.MARKETING.FIELD.VIDEO_POST_PROD_COMPANY',
            'video_post_prod_contact' => 'FERRERO.MARKETING.FIELD.VID_POST_PROD_CONTACT',
            'audio_post_prod_company' => 'FERRERO.MARKETING.FIELD.AUDIO_POST_PROD_COMPANY',
            'audio_post_prod_contact' => 'FERRERO.MARKETING.FIELD.AUDIO_POST_PROD_CONTACT',
            'video_type' => 'FERRERO.MARKET.FIELD.TYPE_VID',
            'ip_rights' => 'FERRERO.MARKET.FIELD.IPRIGHT',
            'production_company' => 'FERRERO.MARKET.PROD_COMPANY',
            'licensing' => 'FERRERO.MARKET.FIELD.LICENSIN',
            'buyout' => 'FERRERO.MARKET.FIELD.BUYOUT',
            'ferrero_property' => 'FERRERO.MARKET.FIELD.FERRERO PROPERTY',
            'video_status' => 'FERRERO.MARKET.VID_N_STAT',
            'license' => 'FERRERO.MARKET.FIELD.LICENSE',
            'creativex_score' => 'FERRERO.TAB.FIELD.CREATIVEX',
            'creativex_link' => 'FERRERO.FIELD.CREATIVEX LINK',
        ];

        // Update fields in the JSONB structure
        foreach ($mvpFields as $friendlyName => $newValue) {
            if (!isset($reverseFieldMapping[$friendlyName])) {
                continue;
            }

            $fieldId = $reverseFieldMapping[$friendlyName];

            // Find and update the field in the fields array
            foreach ($fullMetadata['fields'] as &$field) {
                if (isset($field['id']) && $field['id'] === $fieldId) {
                    // Update the value in the nested structure
                    if (isset($field['value']['value']['value'])) {
                        $field['value']['value']['value'] = $newValue;
                    } elseif (isset($field['value']['value'])) {
                        $field['value']['value'] = $newValue;
                    } elseif (isset($field['value'])) {
                        $field['value'] = $newValue;
                    }
                    break;
                }
            }
        }

        // Update the full_metadata column
        $sql = "UPDATE master_assets SET full_metadata = :full_metadata WHERE tracking_id = :tracking_id";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            'tracking_id' => $trackingId,
            'full_metadata' => json_encode($fullMetadata)
        ]);
    }

    /**
     * Get metadata change history for a tracking ID
     */
    public function getMetadataChangeHistory($trackingId, $limit = 20) {
        $sql = "SELECT * FROM asset_lifecycle_events
                WHERE tracking_id = :tracking_id
                AND event_type = 'metadata_updated'
                ORDER BY event_timestamp DESC
                LIMIT :limit";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':tracking_id', $trackingId, PDO::PARAM_STR);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Get change history error: " . $e->getMessage());
            return [];
        }
    }

    // ========================================================================
    // OVERRIDE METADATA METHODS (Pre-Upload Metadata Management)
    // ========================================================================

    /**
     * Ensure the override_metadata table exists, creating it if needed.
     * Uses IF NOT EXISTS so it's safe to call repeatedly with no overhead.
     */
    private function ensureOverrideMetadataTable() {
        static $checked = false;
        if ($checked) return; // only run once per request

        $sql = "
            CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS idx_override_metadata_filename ON override_metadata(filename);
            CREATE INDEX IF NOT EXISTS idx_override_metadata_tracking_id ON override_metadata(tracking_id) WHERE tracking_id IS NOT NULL;
            CREATE INDEX IF NOT EXISTS idx_override_metadata_created_at ON override_metadata(created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_override_metadata_applied ON override_metadata(applied_to_upload, applied_at);
        ";

        $this->pdo->exec($sql);
        $checked = true;
    }

    /**
     * Get override metadata for a filename
     *
     * @param string $filename Filename without extension
     * @return array|null Override metadata record or null if not found
     */
    public function getOverrideMetadata($filename) {
        $this->ensureOverrideMetadataTable();

        $sql = "SELECT
                    id,
                    filename,
                    tracking_id,
                    override_fields,
                    created_at,
                    updated_at,
                    created_by,
                    updated_by,
                    applied_to_upload,
                    applied_at
                FROM override_metadata
                WHERE filename = :filename
                  AND applied_to_upload = FALSE
                LIMIT 1";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['filename' => $filename]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($result) {
                // Parse JSONB override_fields
                $result['override_fields'] = json_decode($result['override_fields'], true);
            }

            return $result ?: null;
        } catch (PDOException $e) {
            error_log("Get override metadata error: " . $e->getMessage());
            return null;
        }
    }

    /**
     * Save or update override metadata for a filename
     *
     * @param string $filename Filename without extension
     * @param string|null $trackingId Optional tracking ID
     * @param array $overrideFields MVP fields to override
     * @param string|null $userId User who is creating/updating the override
     * @return array Result with success flag and override ID
     */
    public function saveOverrideMetadata($filename, $trackingId, $overrideFields, $userId = null) {
        $this->ensureOverrideMetadataTable();

        $sql = "INSERT INTO override_metadata
                    (filename, tracking_id, override_fields, created_by, updated_by, created_at, updated_at)
                VALUES
                    (:filename, :tracking_id, :override_fields::jsonb, :user_id, :user_id, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                ON CONFLICT (filename)
                DO UPDATE SET
                    tracking_id = EXCLUDED.tracking_id,
                    override_fields = EXCLUDED.override_fields,
                    updated_by = EXCLUDED.updated_by,
                    updated_at = CURRENT_TIMESTAMP,
                    applied_to_upload = FALSE,
                    applied_at = NULL
                RETURNING id";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'filename' => $filename,
                'tracking_id' => $trackingId,
                'override_fields' => json_encode($overrideFields),
                'user_id' => $userId
            ]);

            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            $overrideId = $result['id'] ?? null;

            return [
                'success' => true,
                'override_id' => $overrideId,
                'message' => 'Override metadata saved successfully'
            ];
        } catch (PDOException $e) {
            error_log("Save override metadata error: " . $e->getMessage());
            return [
                'success' => false,
                'error' => 'Failed to save override metadata: ' . $e->getMessage()
            ];
        }
    }

    /**
     * Mark override metadata as applied after upload
     *
     * @param string $filename Filename without extension
     * @return bool Success status
     */
    public function markOverrideApplied($filename) {
        $this->ensureOverrideMetadataTable();

        $sql = "UPDATE override_metadata
                SET applied_to_upload = TRUE,
                    applied_at = CURRENT_TIMESTAMP
                WHERE filename = :filename
                  AND applied_to_upload = FALSE";

        try {
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute(['filename' => $filename]);
        } catch (PDOException $e) {
            error_log("Mark override applied error: " . $e->getMessage());
            return false;
        }
    }

    /**
     * Get paginated list of override metadata records
     *
     * @param int $limit Number of records to return
     * @param int $offset Starting offset for pagination
     * @return array List of override records
     */
    public function getOverrideHistory($limit = 50, $offset = 0) {
        $this->ensureOverrideMetadataTable();

        $sql = "SELECT
                    id,
                    filename,
                    tracking_id,
                    created_at,
                    updated_at,
                    created_by,
                    updated_by,
                    applied_to_upload,
                    applied_at
                FROM override_metadata
                ORDER BY created_at DESC
                LIMIT :limit OFFSET :offset";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("Get override history error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Get all master assets with derivative counts for reporting
     * Brand/country/asset_type columns are typically NULL — caller must parse from original_filename
     */
    public function getReportingMasterData($dateFrom = '', $dateTo = '') {
        $conditions = ['ma.is_deleted = FALSE'];
        $params = [];

        if (!empty($dateFrom)) {
            $conditions[] = 'ma.created_at >= :date_from';
            $params['date_from'] = $dateFrom;
        }
        if (!empty($dateTo)) {
            $conditions[] = "ma.created_at <= :date_to::date + INTERVAL '1 day'";
            $params['date_to'] = $dateTo;
        }

        $where = implode(' AND ', $conditions);

        $sql = "SELECT
                    ma.tracking_id,
                    ma.original_filename,
                    ma.opentext_id,
                    ma.created_at,
                    COUNT(DISTINCT da.derivative_filename) AS derivative_count
                FROM master_assets ma
                LEFT JOIN derivative_assets da ON da.tracking_id = ma.tracking_id
                WHERE {$where}
                GROUP BY ma.tracking_id, ma.original_filename, ma.opentext_id, ma.created_at
                ORDER BY ma.created_at DESC";

        try {
            $stmt = $this->pdo->prepare($sql);
            foreach ($params as $key => $value) {
                $stmt->bindValue(':' . $key, $value, PDO::PARAM_STR);
            }
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Reporting master data error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Asset Explorer: search masters by filename, tracking ID, or opentext_id
     * Returns matching masters with their DAM IDs + all linked derivatives
     */
    public function assetExplorerSearch($searchTerm) {
        $result = ['masters' => [], 'derivatives' => []];
        $search = '%' . $searchTerm . '%';

        try {
            // Search master assets by filename, tracking_id, or opentext_id
            $sql = "SELECT ma.tracking_id, ma.original_filename, ma.opentext_id,
                           ma.file_extension, ma.created_at, ma.description
                    FROM master_assets ma
                    WHERE ma.is_deleted = FALSE
                      AND (ma.original_filename ILIKE :search
                           OR ma.tracking_id ILIKE :search2
                           OR ma.opentext_id ILIKE :search3)
                    ORDER BY ma.created_at DESC
                    LIMIT 50";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'search' => $search,
                'search2' => $search,
                'search3' => $search
            ]);
            $result['masters'] = $stmt->fetchAll();

            // Get tracking IDs from found masters
            $trackingIds = array_column($result['masters'], 'tracking_id');

            if (!empty($trackingIds)) {
                // Find all derivatives linked to those tracking IDs
                $placeholders = implode(',', array_fill(0, count($trackingIds), '?'));
                $sql = "SELECT da.tracking_id, da.derivative_filename, da.file_extension,
                               da.dam_asset_id, da.upload_status, da.created_at, da.uploaded_at
                        FROM derivative_assets da
                        WHERE da.tracking_id IN ({$placeholders})
                        ORDER BY da.tracking_id, da.created_at DESC";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($trackingIds);
                $result['derivatives'] = $stmt->fetchAll();
            }

            return $result;

        } catch (PDOException $e) {
            error_log("Asset explorer search error: " . $e->getMessage());
            return $result;
        }
    }

    /**
     * Get reporting summary counts
     */
    public function getReportingSummaryCounts() {
        $stats = [];

        try {
            $stmt = $this->pdo->query('SELECT COUNT(*) FROM master_assets WHERE is_deleted = FALSE');
            $stats['total_masters'] = (int)$stmt->fetchColumn();

            $stmt = $this->pdo->query('SELECT COUNT(*) FROM derivative_assets');
            $stats['total_derivatives'] = (int)$stmt->fetchColumn();

            $stats['avg_derivatives_per_master'] = $stats['total_masters'] > 0
                ? round($stats['total_derivatives'] / $stats['total_masters'], 1)
                : 0;

            $stmt = $this->pdo->query("SELECT COUNT(*) FROM master_assets
                                      WHERE is_deleted = FALSE AND created_at > NOW() - INTERVAL '30 days'");
            $stats['created_last_30_days'] = (int)$stmt->fetchColumn();

            $stmt = $this->pdo->query('SELECT COUNT(*) FROM asset_lifecycle_events');
            $stats['total_events'] = (int)$stmt->fetchColumn();

            return $stats;

        } catch (PDOException $e) {
            error_log("Reporting summary counts error: " . $e->getMessage());
            return $stats;
        }
    }

    /**
     * Get monthly creation trend for reporting
     */
    public function getReportingMonthlyTrend() {
        $sql = "SELECT
                    TO_CHAR(created_at, 'YYYY-MM') AS month,
                    TO_CHAR(created_at, 'Mon YYYY') AS month_label,
                    COUNT(*) AS master_count
                FROM master_assets
                WHERE is_deleted = FALSE
                    AND created_at > NOW() - INTERVAL '12 months'
                GROUP BY TO_CHAR(created_at, 'YYYY-MM'), TO_CHAR(created_at, 'Mon YYYY')
                ORDER BY month ASC";

        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Monthly trend error: " . $e->getMessage());
            return [];
        }
    }

    /**
     * Prevent cloning
     */
    private function __clone() {}

    /**
     * Prevent unserialization
     */
    public function __wakeup() {
        throw new Exception("Cannot unserialize singleton");
    }
}