-- Shopify ↔ NetSuite bridge — MySQL schema
-- Charset: utf8mb4 for international SKUs and variant titles

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `shopify_netsuite_bridge`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `shopify_netsuite_bridge`;

-- ---------------------------------------------------------------------------
-- sku_map: authoritative mapping from Shopify sellable lines to NetSuite items.
-- Never infer NetSuite internal IDs from Shopify; only this table + env defaults.
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS `sku_map`;
CREATE TABLE `sku_map` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shopify_sku` VARCHAR(191) NOT NULL COMMENT 'Shopify line item SKU (may be empty in Shopify; avoid empty for production)',
  `shopify_variant_title` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Empty string = wildcard match for any variant title for this SKU',
  `netsuite_item_internal_id` BIGINT UNSIGNED NOT NULL COMMENT 'NetSuite item internal ID only — never Shopify IDs',
  `netsuite_location_internal_id` BIGINT UNSIGNED NULL DEFAULT NULL COMMENT 'Line-level location override; NULL = use payload default',
  `netsuite_department_internal_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `netsuite_class_internal_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `quantity_multiplier` DECIMAL(18,6) NOT NULL DEFAULT 1.000000 COMMENT 'Shopify qty * multiplier = NetSuite qty (e.g. 5kg packs)',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sku_map_active_sku` (`is_active`, `shopify_sku`),
  KEY `idx_sku_map_lookup` (`is_active`, `shopify_sku`, `shopify_variant_title`(64))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- integration_jobs: durable audit trail — never lose inbound webhooks.
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS `integration_jobs`;
CREATE TABLE `integration_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `source` VARCHAR(32) NOT NULL DEFAULT 'shopify' COMMENT 'Origin system code',
  `event_type` VARCHAR(128) NOT NULL COMMENT 'e.g. orders/create',
  `source_id` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'Shopify order id or name when known',
  `shop_domain` VARCHAR(255) NOT NULL DEFAULT '',
  `status` ENUM('pending','processing','success','failed') NOT NULL DEFAULT 'pending',
  `request_headers` JSON NULL COMMENT 'Subset of HTTP headers for debugging',
  `payload` LONGTEXT NOT NULL COMMENT 'Raw webhook JSON body',
  `mapped_payload` LONGTEXT NULL COMMENT 'JSON sent to NetSuite RESTlet',
  `response_text` LONGTEXT NULL COMMENT 'Raw HTTP response body from NetSuite',
  `error_text` TEXT NULL COMMENT 'Human-readable failure reason',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_jobs_shop_status` (`shop_domain`, `status`, `created_at`),
  KEY `idx_jobs_source_event` (`source`, `event_type`, `created_at`),
  KEY `idx_jobs_source_id` (`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;