Reverb_Opencart_Extension/upload/admin/model/extension/module/reverb.php

<?php
class ModelExtensionModuleReverb extends Model {

    // -------------------------------------------------------------------------
    // Install / Uninstall
    // -------------------------------------------------------------------------

    public function install() {
        $this->db->query("
            CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "reverb_product_map` (
                `product_id`           INT(11) NOT NULL,
                `reverb_listing_id`    VARCHAR(64) NOT NULL DEFAULT '',
                `sync_enabled`         TINYINT(1) NOT NULL DEFAULT 0,
                `condition_uuid`       VARCHAR(64) NOT NULL DEFAULT '',
                `reverb_category_uuid` VARCHAR(64) NOT NULL DEFAULT '',
                `last_synced_at`       DATETIME NULL DEFAULT NULL,
                PRIMARY KEY (`product_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
        ");

        $this->db->query("
            CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "reverb_sync_log` (
                `log_id`     INT(11) NOT NULL AUTO_INCREMENT,
                `product_id` INT(11) NOT NULL DEFAULT 0,
                `direction`  ENUM('push','pull') NOT NULL DEFAULT 'push',
                `status`     ENUM('success','error') NOT NULL DEFAULT 'success',
                `message`    TEXT NOT NULL,
                `created_at` DATETIME NOT NULL,
                PRIMARY KEY (`log_id`),
                KEY `product_id` (`product_id`),
                KEY `created_at` (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
        ");
    }

    public function uninstall() {
        $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "reverb_product_map`");
        $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "reverb_sync_log`");
    }

    // -------------------------------------------------------------------------
    // Product map CRUD
    // -------------------------------------------------------------------------

    public function getProductMap($product_id) {
        $query = $this->db->query("
            SELECT * FROM `" . DB_PREFIX . "reverb_product_map`
            WHERE `product_id` = '" . (int)$product_id . "'
        ");
        return $query->num_rows ? $query->row : null;
    }

    public function saveProductMap($product_id, array $data) {
        $existing = $this->getProductMap($product_id);

        $sync_enabled         = isset($data['sync_enabled'])         ? (int)(bool)$data['sync_enabled']                         : 0;
        $condition_uuid       = isset($data['condition_uuid'])       ? $this->db->escape($data['condition_uuid'])                : '';
        $reverb_category_uuid = isset($data['reverb_category_uuid']) ? $this->db->escape($data['reverb_category_uuid'])          : '';
        $reverb_listing_id    = isset($data['reverb_listing_id'])    ? $this->db->escape($data['reverb_listing_id'])             : '';
        $last_synced_at       = isset($data['last_synced_at'])       ? "'" . $this->db->escape($data['last_synced_at']) . "'"    : 'NULL';

        if ($existing) {
            $this->db->query("
                UPDATE `" . DB_PREFIX . "reverb_product_map`
                SET `sync_enabled`         = $sync_enabled,
                    `condition_uuid`       = '$condition_uuid',
                    `reverb_category_uuid` = '$reverb_category_uuid'"
                . (!empty($reverb_listing_id) ? ", `reverb_listing_id` = '$reverb_listing_id'" : '')
                . (isset($data['last_synced_at']) ? ", `last_synced_at` = $last_synced_at" : '')
                . " WHERE `product_id` = '" . (int)$product_id . "'"
            );
        } else {
            $this->db->query("
                INSERT INTO `" . DB_PREFIX . "reverb_product_map`
                (`product_id`, `sync_enabled`, `condition_uuid`, `reverb_category_uuid`, `reverb_listing_id`, `last_synced_at`)
                VALUES (
                    '" . (int)$product_id . "',
                    $sync_enabled,
                    '$condition_uuid',
                    '$reverb_category_uuid',
                    '$reverb_listing_id',
                    $last_synced_at
                )
            ");
        }
    }

    public function updateListingId($product_id, $listing_id) {
        $this->db->query("
            UPDATE `" . DB_PREFIX . "reverb_product_map`
            SET `reverb_listing_id` = '" . $this->db->escape($listing_id) . "',
                `last_synced_at`    = NOW()
            WHERE `product_id` = '" . (int)$product_id . "'
        ");
    }

    /**
     * Return all products eligible for sync (sync_enabled = 1, in allowed categories).
     *
     * @param array $allowed_category_ids  List of OC category IDs.
     * @return array
     */
    public function getSyncEnabledProducts(array $allowed_category_ids) {
        if (empty($allowed_category_ids)) {
            return [];
        }

        $ids = implode(',', array_map('intval', $allowed_category_ids));

        $query = $this->db->query("
            SELECT p.product_id, pd.name, p.model, p.price, p.quantity, p.image,
                   r.reverb_listing_id, r.condition_uuid, r.reverb_category_uuid
            FROM `" . DB_PREFIX . "product` p
            INNER JOIN `" . DB_PREFIX . "product_description` pd
                ON pd.product_id = p.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
            INNER JOIN `" . DB_PREFIX . "product_to_category` ptc
                ON ptc.product_id = p.product_id AND ptc.category_id IN ($ids)
            INNER JOIN `" . DB_PREFIX . "reverb_product_map` r
                ON r.product_id = p.product_id AND r.sync_enabled = 1
            WHERE p.status = 1
            GROUP BY p.product_id
        ");

        return $query->rows;
    }

    // -------------------------------------------------------------------------
    // Product images
    // -------------------------------------------------------------------------

    public function getProductImages($product_id) {
        $images = [];

        $product_query = $this->db->query("
            SELECT image FROM `" . DB_PREFIX . "product`
            WHERE product_id = '" . (int)$product_id . "'
        ");
        if ($product_query->num_rows && !empty($product_query->row['image'])) {
            $images[] = $product_query->row['image'];
        }

        $gallery_query = $this->db->query("
            SELECT image FROM `" . DB_PREFIX . "product_image`
            WHERE product_id = '" . (int)$product_id . "'
            ORDER BY sort_order ASC
        ");
        foreach ($gallery_query->rows as $row) {
            $images[] = $row['image'];
        }

        return $images;
    }

    // -------------------------------------------------------------------------
    // Category mappings (OC category → Reverb category UUID)
    // -------------------------------------------------------------------------

    public function getCategoryMappings() {
        $raw = $this->config->get('module_reverb_category_mappings');
        return $raw ? json_decode($raw, true) : [];
    }

    public function saveCategoryMappings(array $mappings) {
        $this->load->model('setting/setting');
        $this->model_setting_setting->editSettingValue('module_reverb', 'module_reverb_category_mappings', json_encode($mappings));
    }

    // -------------------------------------------------------------------------
    // Reverb metadata cache (conditions + categories)
    // -------------------------------------------------------------------------

    public function getListingConditions() {
        $cached   = $this->config->get('module_reverb_conditions_cache');
        $cached_at = (int)$this->config->get('module_reverb_conditions_cached_at');

        if ($cached && (time() - $cached_at) < 86400) {
            return json_decode($cached, true);
        }

        try {
            $api  = $this->getApi();
            $resp = $api->getListingConditions();
            $conditions = isset($resp['conditions']) ? $resp['conditions'] : [];

            $this->load->model('setting/setting');
            $this->model_setting_setting->editSettingValue('module_reverb', 'module_reverb_conditions_cache', json_encode($conditions));
            $this->model_setting_setting->editSettingValue('module_reverb', 'module_reverb_conditions_cached_at', time());

            return $conditions;
        } catch (Exception $e) {
            return $cached ? json_decode($cached, true) : [];
        }
    }

    public function getReverbCategories() {
        $cached    = $this->config->get('module_reverb_categories_cache');
        $cached_at = (int)$this->config->get('module_reverb_categories_cached_at');

        if ($cached && (time() - $cached_at) < 86400) {
            return json_decode($cached, true);
        }

        try {
            $api  = $this->getApi();
            $resp = $api->getCategories();
            $categories = isset($resp['categories']) ? $resp['categories'] : [];

            $this->load->model('setting/setting');
            $this->model_setting_setting->editSettingValue('module_reverb', 'module_reverb_categories_cache', json_encode($categories));
            $this->model_setting_setting->editSettingValue('module_reverb', 'module_reverb_categories_cached_at', time());

            return $categories;
        } catch (Exception $e) {
            return $cached ? json_decode($cached, true) : [];
        }
    }

    // -------------------------------------------------------------------------
    // Sync log
    // -------------------------------------------------------------------------

    public function log($product_id, $direction, $status, $message) {
        $this->db->query("
            INSERT INTO `" . DB_PREFIX . "reverb_sync_log`
            (`product_id`, `direction`, `status`, `message`, `created_at`)
            VALUES (
                '" . (int)$product_id . "',
                '" . $this->db->escape($direction) . "',
                '" . $this->db->escape($status) . "',
                '" . $this->db->escape(substr($message, 0, 65535)) . "',
                NOW()
            )
        ");
    }

    public function getSyncLog($limit = 100) {
        $query = $this->db->query("
            SELECT l.*, pd.name AS product_name
            FROM `" . DB_PREFIX . "reverb_sync_log` l
            LEFT JOIN `" . DB_PREFIX . "product_description` pd
                ON pd.product_id = l.product_id
                AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
            ORDER BY l.created_at DESC
            LIMIT " . (int)$limit
        );
        return $query->rows;
    }

    // -------------------------------------------------------------------------
    // Sync helpers
    // -------------------------------------------------------------------------

    /**
     * Push a single product to Reverb. Creates or updates the listing and uploads images.
     *
     * @param array $product     Product row (product_id, name, model, price, quantity, image, ...).
     * @param array $reverb_data Row from reverb_product_map.
     * @param array $settings    Global Reverb settings array.
     * @return string            The Reverb listing ID.
     */
    public function syncProductToReverb(array $product, array $reverb_data, array $settings) {
        $this->load->library('reverb/ReverbApi');
        $this->load->library('reverb/ProductMapper');

        $api     = $this->getApi();
        $payload = ProductMapper::toReverb($product, $reverb_data, $settings);

        $listing_id = !empty($reverb_data['reverb_listing_id']) ? $reverb_data['reverb_listing_id'] : null;

        if ($listing_id) {
            $api->updateListing($listing_id, $payload);
        } else {
            $response   = $api->createListing($payload);
            $listing_id = $response['id'] ?? ($response['listing']['id'] ?? null);
            if (!$listing_id) {
                throw new RuntimeException('Reverb did not return a listing ID after create.');
            }
            $this->updateListingId($product['product_id'], $listing_id);
        }

        // Upload images
        $images    = $this->getProductImages($product['product_id']);
        $store_url = $settings['store_url'] ?? '';
        foreach (ProductMapper::buildPhotoPayloads($images, $store_url) as $photo) {
            try {
                $api->uploadPhoto($listing_id, $photo['image_url']);
            } catch (Exception $e) {
                // Non-fatal: log but continue
                $this->log($product['product_id'], 'push', 'error', 'Photo upload failed: ' . $e->getMessage());
            }
        }

        return $listing_id;
    }

    // -------------------------------------------------------------------------
    // Utility
    // -------------------------------------------------------------------------

    private function getApi() {
        $token = $this->config->get('module_reverb_api_token');
        if (empty($token)) {
            throw new RuntimeException('Reverb API token is not configured.');
        }
        $this->load->library('reverb/ReverbApi');
        return new ReverbApi($token);
    }
}