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 '',
                `handmade`             TINYINT(1) NOT NULL DEFAULT 0,
                `upc_does_not_apply`   TINYINT(1) NOT NULL DEFAULT 1,
                `last_synced_at`       DATETIME NULL DEFAULT NULL,
                PRIMARY KEY (`product_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
        ");
        $this->migrate();

        $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`");
    }

    public function migrate() {
        static $done = false;
        if ($done) return;
        $done = true;
        $this->addColumnIfMissing(DB_PREFIX . 'reverb_product_map', 'handmade',           'TINYINT(1) NOT NULL DEFAULT 0');
        $this->addColumnIfMissing(DB_PREFIX . 'reverb_product_map', 'upc_does_not_apply', 'TINYINT(1) NOT NULL DEFAULT 1');
    }

    private function addColumnIfMissing($table, $column, $definition) {
        $r = $this->db->query("
            SELECT COUNT(*) AS cnt FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME    = '" . $this->db->escape($table) . "'
            AND COLUMN_NAME   = '" . $this->db->escape($column) . "'
        ");
        if (empty($r->row['cnt'])) {
            $this->db->query("ALTER TABLE `" . $table . "` ADD COLUMN `" . $column . "` " . $definition);
        }
    }

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

    public function getProductMap($product_id) {
        $this->migrate();
        $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) {
        $this->migrate();
        $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';
        $handmade             = isset($data['handmade'])             ? (int)(bool)$data['handmade']                             : 0;
        $upc_does_not_apply   = isset($data['upc_does_not_apply'])   ? (int)(bool)$data['upc_does_not_apply']                   : 1;

        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',
                    `handmade`             = $handmade,
                    `upc_does_not_apply`   = $upc_does_not_apply"
                . (!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`, `handmade`, `upc_does_not_apply`, `last_synced_at`)
                VALUES (
                    '" . (int)$product_id . "',
                    $sync_enabled,
                    '$condition_uuid',
                    '$reverb_category_uuid',
                    '$reverb_listing_id',
                    $handmade,
                    $upc_does_not_apply,
                    $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, pd.description, p.model, p.price, p.quantity, p.image,
                   r.reverb_listing_id, r.condition_uuid, r.reverb_category_uuid,
                   r.handmade, r.upc_does_not_apply,
                   m.name AS manufacturer
            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
            LEFT JOIN `" . DB_PREFIX . "manufacturer` m
                ON m.manufacturer_id = p.manufacturer_id
            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) {
        require_once(DIR_SYSTEM . 'library/reverb/ReverbApi.php');
        require_once(DIR_SYSTEM . 'library/reverb/ProductMapper.php');

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

        // Photos are plain URL strings sent inside the listing payload (Reverb API v3)
        $store_url = $settings['store_url'] ?? '';
        $images    = $this->getProductImages($product['product_id']);

        if (!empty($store_url) && !empty($images)) {
            $base   = rtrim($store_url, '/') . '/image/';
            $photos = [];
            foreach ($images as $path) {
                if (!empty($path)) {
                    $photos[] = $base . ltrim($path, '/');
                }
            }
            if (!empty($photos)) {
                $payload['photos']  = $photos;
                $payload['publish'] = true;
                $this->log($product['product_id'], 'push', 'success', 'Including ' . count($photos) . ' photo(s): ' . implode(', ', $photos));
            }
        } elseif (empty($store_url)) {
            $this->log($product['product_id'], 'push', 'error', 'Photo upload skipped: store URL not configured (check System > Settings > Store URL)');
        } else {
            $this->log($product['product_id'], 'push', 'error', 'No images found for this product in OpenCart.');
        }

        $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);
        }

        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.');
        }
        require_once(DIR_SYSTEM . 'library/reverb/ReverbApi.php');
        return new ReverbApi($token);
    }
}