-- AbeBooks Midnight Refresh Log Table
-- Run this SQL to create the logging table for the midnight refresh script

CREATE TABLE IF NOT EXISTS `abebooks_refresh_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `orders_fetched` INT NOT NULL DEFAULT 0,
  `products_deleted` INT NOT NULL DEFAULT 0,
  `products_uploaded` INT NOT NULL DEFAULT 0,
  `duration_seconds` DECIMAL(10,2) NULL,
  `completed_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_completed_at` (`completed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tracks nightly AbeBooks inventory refresh runs';

-- Ensure AbeBooks FTPS credentials are in platform_settings
-- Note: The API key is used as the FTPS password
INSERT INTO platform_settings (platform, setting_key, setting_value, created_at)
VALUES 
  ('abe', 'ftp_host', 'ftp.abebooks.com', NOW()),
  ('abe', 'username', 'AZBOOKSUK', NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

-- Verify your credentials are set
SELECT * FROM platform_settings WHERE platform = 'abe';





