-- Create stock_sync_log table for tracking cross-platform stock synchronization
CREATE TABLE IF NOT EXISTS `stock_sync_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `source` varchar(50) NOT NULL COMMENT 'amazon, abebooks, manual, etc.',
  `order_id` varchar(255) DEFAULT NULL COMMENT 'Order ID from source platform',
  `amazon_success` tinyint(1) NOT NULL DEFAULT 0,
  `shopify_success` tinyint(1) NOT NULL DEFAULT 0,
  `ebay_success` tinyint(1) NOT NULL DEFAULT 0,
  `google_merchant_success` tinyint(1) NOT NULL DEFAULT 0,
  `abebooks_success` tinyint(1) NOT NULL DEFAULT 0,
  `errors` text DEFAULT NULL COMMENT 'JSON array of error messages',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sku` (`sku`),
  KEY `idx_source` (`source`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log of stock synchronization across all platforms';

-- Create index for better performance on recent syncs
CREATE INDEX `idx_recent_syncs` ON `stock_sync_log` (`created_at`, `sku`);

-- Create view for failed syncs
CREATE OR REPLACE VIEW `failed_syncs` AS
SELECT 
    sku,
    source,
    order_id,
    created_at,
    errors,
    amazon_success + shopify_success + ebay_success + google_merchant_success + abebooks_success as success_count
FROM stock_sync_log 
WHERE amazon_success = 0 OR shopify_success = 0 OR ebay_success = 0 OR google_merchant_success = 0 OR abebooks_success = 0
ORDER BY created_at DESC;
