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_order_map` (
                `reverb_order_number` VARCHAR(64) NOT NULL,
                `order_id`            INT(11) NOT NULL DEFAULT 0,
                `reverb_status`       VARCHAR(32) NOT NULL DEFAULT '',
                `created_at`          DATETIME NOT NULL,
                PRIMARY KEY (`reverb_order_number`)
            ) 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`");
        $this->db->query("DROP TABLE IF EXISTS `" . DB_PREFIX . "reverb_order_map`");
    }

    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');
        // Create order map table for upgrades from earlier versions
        $this->db->query("
            CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "reverb_order_map` (
                `reverb_order_number` VARCHAR(64) NOT NULL,
                `order_id`            INT(11) NOT NULL DEFAULT 0,
                `reverb_status`       VARCHAR(32) NOT NULL DEFAULT '',
                `created_at`          DATETIME NOT NULL,
                PRIMARY KEY (`reverb_order_number`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
        ");
    }

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

        return $this->refreshReverbCategories();
    }

    public function refreshReverbCategories() {
        $cached = $this->config->get('module_reverb_categories_cache');

        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) : [];
        }
    }

    /**
     * Return Reverb categories grouped by their root (top-level) category name.
     * Parses the full_name field (e.g. "Guitars > Electric Guitars > Solidbody").
     *
     * @return array  ['Root Name' => [['uuid'=>..., 'full_name'=>..., 'name'=>...], ...], ...]
     */
    public function getReverbCategoriesGrouped() {
        $flat    = $this->getReverbCategories();
        $grouped = [];

        foreach ($flat as $cat) {
            $full = $cat['full_name'] ?? $cat['name'] ?? '';
            $parts = array_map('trim', explode('>', $full));
            $root  = $parts[0] ?: 'Other';
            $grouped[$root][] = $cat;
        }

        ksort($grouped);
        return $grouped;
    }

    // -------------------------------------------------------------------------
    // 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 clearListingId($product_id) {
        $this->db->query("
            UPDATE `" . DB_PREFIX . "reverb_product_map`
            SET `reverb_listing_id` = '', `last_synced_at` = NULL
            WHERE `product_id` = '" . (int)$product_id . "'
        ");
    }

    public function clearSyncLog() {
        $this->db->query("DELETE FROM `" . DB_PREFIX . "reverb_sync_log`");
    }

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

    // -------------------------------------------------------------------------
    // Order import (Reverb → OpenCart)
    // -------------------------------------------------------------------------

    public function importOrdersFromReverb(array $settings) {
        require_once(DIR_SYSTEM . 'library/reverb/ReverbApi.php');
        $this->migrate();

        $api        = $this->getApi();
        $last_sync  = $this->config->get('module_reverb_order_last_sync');
        $imported   = 0;
        $skipped    = 0;
        $page       = 1;

        do {
            $response    = $api->getOrders($page, 50, $last_sync ?: null);
            $orders      = $response['orders']      ?? [];
            $total_pages = (int)($response['total_pages'] ?? 1);

            foreach ($orders as $reverb_order) {
                $order_number = $reverb_order['order_number'] ?? null;
                if (!$order_number) { $skipped++; continue; }

                // Skip already-imported orders
                $existing = $this->db->query("
                    SELECT order_id FROM `" . DB_PREFIX . "reverb_order_map`
                    WHERE `reverb_order_number` = '" . $this->db->escape($order_number) . "'
                ");
                if ($existing->num_rows) { $skipped++; continue; }

                // Skip cancelled/unpaid
                $status = $reverb_order['status'] ?? '';
                if (in_array($status, ['cancelled', 'blocked', 'unpaid', 'payment_pending', 'pending_review'])) {
                    $skipped++; continue;
                }

                $order_id = $this->createOcOrderFromReverb($reverb_order, $settings);
                if ($order_id) {
                    $this->db->query("
                        INSERT INTO `" . DB_PREFIX . "reverb_order_map`
                        (`reverb_order_number`, `order_id`, `reverb_status`, `created_at`)
                        VALUES (
                            '" . $this->db->escape($order_number) . "',
                            '" . (int)$order_id . "',
                            '" . $this->db->escape($status) . "',
                            NOW()
                        )
                    ");
                    $this->log(0, 'pull', 'success', 'Imported Reverb order #' . $order_number . ' → OC order #' . $order_id);
                    $imported++;
                }
            }
            $page++;
        } while ($page <= $total_pages);

        // Save timestamp so next import only fetches newer orders
        $this->load->model('setting/setting');
        $this->model_setting_setting->editSettingValue('module_reverb', 'module_reverb_order_last_sync', date('c'));

        return ['imported' => $imported, 'skipped' => $skipped];
    }

    private function createOcOrderFromReverb(array $o, array $settings) {
        // Buyer name
        $buyer_name  = trim($o['buyer_name'] ?? 'Reverb Buyer');
        $np          = explode(' ', $buyer_name, 2);
        $firstname   = $np[0];
        $lastname    = $np[1] ?? '';

        // Shipping address
        $addr        = $o['shipping_address'] ?? [];
        $anp         = explode(' ', trim($addr['name'] ?? $buyer_name), 2);
        $ship_first  = $anp[0];
        $ship_last   = $anp[1] ?? '';

        // Amounts
        $product_amt  = (float)($o['amount_product']['amount']  ?? $o['amount_product']  ?? 0);
        $shipping_amt = (float)($o['shipping']['amount']        ?? $o['shipping']        ?? 0);
        $total_amt    = (float)($o['total']['amount']           ?? $o['total']           ?? ($product_amt + $shipping_amt));
        $currency     = $o['total']['currency'] ?? $settings['currency'] ?? 'AUD';

        // Store and quantity from settings
        $order_stores = $settings['order_stores'] ?? [0];
        $store_id     = (int)($order_stores[0] ?? 0);
        $default_qty  = max(1, (int)($settings['default_qty'] ?? 1));
        $qty          = (int)($o['quantity'] ?? 0) ?: $default_qty;

        // OC order status mapping
        $status_map  = ['paid' => 2, 'shipped' => 3, 'received' => 5, 'picked_up' => 5];
        $status_id   = $status_map[$o['status'] ?? ''] ?? 1;

        // Match OC product by Reverb listing ID or SKU
        $listing_id  = (string)($o['listing_id'] ?? $o['product_id'] ?? '');
        $sku         = $o['sku'] ?? '';
        $title       = $o['title'] ?? 'Reverb Item';

        $oc_product  = ($listing_id ? $this->findOcProductByListingId($listing_id) : null)
                    ?: ($sku        ? $this->findOcProductBySku($sku)               : null);

        $product_id    = $oc_product ? (int)$oc_product['product_id'] : 0;
        $product_name  = $oc_product ? $oc_product['name']  : $title;
        $product_model = $oc_product ? $oc_product['model'] : $sku;

        // Currency ID from OC DB
        $cq          = $this->db->query("SELECT currency_id FROM `" . DB_PREFIX . "currency` WHERE code = '" . $this->db->escape($currency) . "' LIMIT 1");
        $currency_id = $cq->num_rows ? (int)$cq->row['currency_id'] : 1;

        $date_added  = date('Y-m-d H:i:s', strtotime($o['paid_at'] ?? $o['created_at'] ?? 'now'));
        $store_name  = $this->db->escape($this->config->get('config_name') ?? '');
        $store_url   = $this->db->escape($settings['store_url'] ?? '');
        $lang_id     = (int)$this->config->get('config_language_id');
        $comment     = $this->db->escape('Reverb order #' . ($o['order_number'] ?? ''));

        $this->db->query("
            INSERT INTO `" . DB_PREFIX . "order` SET
            `invoice_prefix`          = 'REV-',
            `invoice_no`              = 0,
            `store_id`                = $store_id,
            `store_name`              = '$store_name',
            `store_url`               = '$store_url',
            `customer_id`             = 0,
            `customer_group_id`       = 1,
            `firstname`               = '" . $this->db->escape($firstname) . "',
            `lastname`                = '" . $this->db->escape($lastname) . "',
            `email`                   = '" . $this->db->escape($o['buyer_id'] ?? '') . "',
            `telephone`               = '" . $this->db->escape($addr['phone'] ?? '') . "',
            `fax`                     = '',
            `custom_field`            = '{}',
            `payment_firstname`       = '" . $this->db->escape($ship_first) . "',
            `payment_lastname`        = '" . $this->db->escape($ship_last) . "',
            `payment_company`         = '',
            `payment_address_1`       = '" . $this->db->escape($addr['street_address'] ?? '') . "',
            `payment_address_2`       = '" . $this->db->escape($addr['extended_address'] ?? '') . "',
            `payment_city`            = '" . $this->db->escape($addr['locality'] ?? '') . "',
            `payment_postcode`        = '" . $this->db->escape($addr['postal_code'] ?? '') . "',
            `payment_country`         = '" . $this->db->escape($addr['country_code'] ?? '') . "',
            `payment_country_id`      = 0,
            `payment_zone`            = '" . $this->db->escape($addr['region'] ?? '') . "',
            `payment_zone_id`         = 0,
            `payment_address_format`  = '',
            `payment_custom_field`    = '{}',
            `payment_method`          = 'Reverb',
            `payment_code`            = 'reverb',
            `shipping_firstname`      = '" . $this->db->escape($ship_first) . "',
            `shipping_lastname`       = '" . $this->db->escape($ship_last) . "',
            `shipping_company`        = '',
            `shipping_address_1`      = '" . $this->db->escape($addr['street_address'] ?? '') . "',
            `shipping_address_2`      = '" . $this->db->escape($addr['extended_address'] ?? '') . "',
            `shipping_city`           = '" . $this->db->escape($addr['locality'] ?? '') . "',
            `shipping_postcode`       = '" . $this->db->escape($addr['postal_code'] ?? '') . "',
            `shipping_country`        = '" . $this->db->escape($addr['country_code'] ?? '') . "',
            `shipping_country_id`     = 0,
            `shipping_zone`           = '" . $this->db->escape($addr['region'] ?? '') . "',
            `shipping_zone_id`        = 0,
            `shipping_address_format` = '',
            `shipping_custom_field`   = '{}',
            `shipping_method`         = 'Reverb',
            `shipping_code`           = 'reverb.reverb',
            `comment`                 = '$comment',
            `total`                   = '" . number_format($total_amt, 4, '.', '') . "',
            `order_status_id`         = $status_id,
            `affiliate_id`            = 0,
            `commission`              = '0.0000',
            `marketing_id`            = 0,
            `tracking`                = '',
            `language_id`             = $lang_id,
            `currency_id`             = $currency_id,
            `currency_code`           = '" . $this->db->escape($currency) . "',
            `currency_value`          = '1.00000000',
            `ip`                      = '',
            `forwarded_ip`            = '',
            `user_agent`              = 'Reverb Import',
            `accept_language`         = '',
            `date_added`              = '" . $this->db->escape($date_added) . "',
            `date_modified`           = NOW()
        ");

        $order_id = (int)$this->db->getLastId();
        if (!$order_id) return null;

        // Order product
        $unit_price = $qty > 0 ? $product_amt / $qty : $product_amt;
        $this->db->query("
            INSERT INTO `" . DB_PREFIX . "order_product` SET
            `order_id`   = $order_id,
            `product_id` = $product_id,
            `master_id`  = 0,
            `name`       = '" . $this->db->escape($product_name) . "',
            `model`      = '" . $this->db->escape($product_model) . "',
            `quantity`   = $qty,
            `price`      = '" . number_format($unit_price, 4, '.', '') . "',
            `total`      = '" . number_format($product_amt, 4, '.', '') . "',
            `tax`        = '0.0000',
            `reward`     = 0
        ");

        // Totals
        $this->db->query("INSERT INTO `" . DB_PREFIX . "order_total` SET `order_id`=$order_id, `code`='sub_total', `title`='Sub-Total', `value`='" . number_format($product_amt, 4, '.', '') . "', `sort_order`=1");
        if ($shipping_amt > 0) {
            $this->db->query("INSERT INTO `" . DB_PREFIX . "order_total` SET `order_id`=$order_id, `code`='shipping', `title`='Shipping', `value`='" . number_format($shipping_amt, 4, '.', '') . "', `sort_order`=3");
        }
        $this->db->query("INSERT INTO `" . DB_PREFIX . "order_total` SET `order_id`=$order_id, `code`='total', `title`='Total', `value`='" . number_format($total_amt, 4, '.', '') . "', `sort_order`=9");

        // History
        $this->db->query("INSERT INTO `" . DB_PREFIX . "order_history` SET `order_id`=$order_id, `order_status_id`=$status_id, `notify`=0, `comment`='Imported from Reverb', `date_added`=NOW()");

        // Decrement OC stock
        if ($product_id) {
            $this->db->query("UPDATE `" . DB_PREFIX . "product` SET `quantity`=GREATEST(0,`quantity`-$qty) WHERE `product_id`=$product_id");
        }

        return $order_id;
    }

    private function findOcProductByListingId($listing_id) {
        if (!$listing_id) return null;
        $q = $this->db->query("
            SELECT p.product_id, pd.name, p.model, p.price
            FROM `" . DB_PREFIX . "reverb_product_map` r
            JOIN `" . DB_PREFIX . "product` p ON p.product_id = r.product_id
            JOIN `" . DB_PREFIX . "product_description` pd ON pd.product_id = p.product_id
                AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
            WHERE r.reverb_listing_id = '" . $this->db->escape($listing_id) . "'
            LIMIT 1
        ");
        return $q->num_rows ? $q->row : null;
    }

    private function findOcProductBySku($sku) {
        if (!$sku) return null;
        $q = $this->db->query("
            SELECT p.product_id, pd.name, p.model, p.price
            FROM `" . DB_PREFIX . "product` p
            JOIN `" . DB_PREFIX . "product_description` pd ON pd.product_id = p.product_id
                AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
            WHERE p.model = '" . $this->db->escape($sku) . "'
            LIMIT 1
        ");
        return $q->num_rows ? $q->row : null;
    }

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