-- Update database schema for stock synchronization system
-- Run these SQL commands to add missing columns

-- Add updated_at column to amazon_products table
ALTER TABLE `amazon_products` 
ADD COLUMN `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add index for better performance on updated_at queries
CREATE INDEX `idx_amazon_products_updated_at` ON `amazon_products` (`updated_at`);

-- Update existing records to have updated_at timestamp
UPDATE `amazon_products` SET `updated_at` = NOW() WHERE `updated_at` IS NULL;

-- Check if shopify_variant_id column exists, if not add it
-- (This is used in the sync functions)
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'amazon_products' 
     AND COLUMN_NAME = 'shopify_variant_id') > 0,
    'SELECT "shopify_variant_id column already exists" as message',
    'ALTER TABLE `amazon_products` ADD COLUMN `shopify_variant_id` VARCHAR(255) NULL'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check if abe_id column exists, if not add it
-- (This is used for AbeBooks synchronization)
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'amazon_products' 
     AND COLUMN_NAME = 'abe_id') > 0,
    'SELECT "abe_id column already exists" as message',
    'ALTER TABLE `amazon_products` ADD COLUMN `abe_id` VARCHAR(255) NULL'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS `idx_amazon_products_sku` ON `amazon_products` (`sku`);
CREATE INDEX IF NOT EXISTS `idx_amazon_products_abe_id` ON `amazon_products` (`abe_id`);
CREATE INDEX IF NOT EXISTS `idx_amazon_products_shopify_variant_id` ON `amazon_products` (`shopify_variant_id`);
CREATE INDEX IF NOT EXISTS `idx_amazon_products_fulfillable_quantity` ON `amazon_products` (`fulfillable_quantity`);

-- Verify the changes
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'amazon_products' 
AND COLUMN_NAME IN ('updated_at', 'shopify_variant_id', 'abe_id')
ORDER BY COLUMN_NAME;

-- Show sample data to verify
SELECT 
    sku,
    fulfillable_quantity,
    abe_id,
    shopify_variant_id,
    updated_at
FROM amazon_products 
LIMIT 5;
