-- Migration: Drop deprecated boolean sync columns from store_sync_config
-- These have been replaced by the new access policy enum columns
-- Created: 2025-11-17 (Issue #95 - GDPR compliance)

-- Drop the deprecated boolean columns
ALTER TABLE store_sync_config
  DROP COLUMN IF EXISTS sync_products CASCADE,
  DROP COLUMN IF EXISTS sync_orders CASCADE,
  DROP COLUMN IF EXISTS sync_customers CASCADE;

-- Add comments to confirm the new system
COMMENT ON COLUMN store_sync_config.products_access_policy IS 'GDPR-compliant access policy for products: sync (full sync to cache), api_only (direct API access only), not_allowed (no access)';
COMMENT ON COLUMN store_sync_config.customers_access_policy IS 'GDPR-compliant access policy for customers: sync (full sync to cache), api_only (direct API access only), not_allowed (no access)';
COMMENT ON COLUMN store_sync_config.orders_access_policy IS 'GDPR-compliant access policy for orders: sync (full sync to cache), api_only (direct API access only), not_allowed (no access)';

-- Ensure all existing stores have default policies if not set
UPDATE store_sync_config
SET
  products_access_policy = COALESCE(products_access_policy, 'sync'::data_access_policy),
  customers_access_policy = COALESCE(customers_access_policy, 'sync'::data_access_policy),
  orders_access_policy = COALESCE(orders_access_policy, 'sync'::data_access_policy)
WHERE
  products_access_policy IS NULL
  OR customers_access_policy IS NULL
  OR orders_access_policy IS NULL;

-- Log completion
DO $$
BEGIN
  RAISE NOTICE 'Migration completed: Dropped deprecated sync_products, sync_orders, sync_customers columns from store_sync_config';
  RAISE NOTICE 'All stores now use the new GDPR-compliant data_access_policy enum columns';
END $$;
