-- Migration: Add webshop scraper integration support
-- Date: 2024-11-21
-- Description: Add scraper configuration columns to stores table

-- Add scraper-related columns to the stores table
ALTER TABLE stores ADD COLUMN IF NOT EXISTS scraper_api_url TEXT;
ALTER TABLE stores ADD COLUMN IF NOT EXISTS scraper_api_secret TEXT; -- Will encrypt later when vault setup is complete
ALTER TABLE stores ADD COLUMN IF NOT EXISTS scraper_registered BOOLEAN DEFAULT false;
ALTER TABLE stores ADD COLUMN IF NOT EXISTS scraper_enabled BOOLEAN DEFAULT true;

-- Add comments to document the columns
COMMENT ON COLUMN stores.scraper_api_url IS 'Per-store scraper API URL override (null = use global default)';
COMMENT ON COLUMN stores.scraper_api_secret IS 'Per-store scraper API secret override (null = use global default) - TODO: encrypt with vault';
COMMENT ON COLUMN stores.scraper_registered IS 'Whether this store is registered with the scraper service';
COMMENT ON COLUMN stores.scraper_enabled IS 'Whether scraper functionality is enabled for this store';

-- Create indexes for efficient querying
CREATE INDEX IF NOT EXISTS idx_stores_scraper_registered ON stores(scraper_registered);
CREATE INDEX IF NOT EXISTS idx_stores_scraper_enabled ON stores(scraper_enabled);

-- Update existing stores to have scraper_enabled = true by default
-- but scraper_registered = false (they need to be registered)
UPDATE stores
SET scraper_enabled = true, scraper_registered = false
WHERE scraper_enabled IS NULL OR scraper_registered IS NULL;