-- Migration: GDPR compliance - Set default access policy to api_only for customers and orders
-- Date: 2025-11-19
-- Description: Changes default access policies for GDPR compliance:
--   - customers_access_policy: default 'api_only' (no local caching of PII)
--   - orders_access_policy: default 'api_only' (no local caching of PII)
--   - products_access_policy: remains 'sync' (non-PII data)

-- ============================================================================
-- Update column defaults for new stores
-- ============================================================================

-- Change default for customers_access_policy to 'api_only'
ALTER TABLE store_sync_config
  ALTER COLUMN customers_access_policy SET DEFAULT 'api_only'::data_access_policy;

-- Change default for orders_access_policy to 'api_only'
ALTER TABLE store_sync_config
  ALTER COLUMN orders_access_policy SET DEFAULT 'api_only'::data_access_policy;

-- Update comments to reflect GDPR-compliant defaults
COMMENT ON COLUMN store_sync_config.customers_access_policy IS 'Customer data access policy: sync (cache + Qdrant), api_only (direct API only - GDPR default), not_allowed (no access)';
COMMENT ON COLUMN store_sync_config.orders_access_policy IS 'Order data access policy: sync (cache + Qdrant), api_only (direct API only - GDPR default), not_allowed (no access)';

-- ============================================================================
-- Update the auto_create_store_sync_config trigger function
-- ============================================================================

CREATE OR REPLACE FUNCTION auto_create_store_sync_config()
RETURNS TRIGGER AS $$
BEGIN
  -- Only create config for platforms that support scheduled sync
  IF NEW.platform_name IN ('woocommerce', 'shoprenter', 'shopify') THEN
    INSERT INTO store_sync_config (
      store_id,
      enabled,
      sync_frequency,
      products_access_policy,
      customers_access_policy,
      orders_access_policy
    )
    VALUES (
      NEW.id,
      true,
      'hourly',
      'sync'::data_access_policy,      -- Products: allow full sync (non-PII)
      'api_only'::data_access_policy,  -- Customers: API only (GDPR compliant)
      'api_only'::data_access_policy   -- Orders: API only (GDPR compliant)
    )
    ON CONFLICT (store_id) DO NOTHING;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- Update the set_data_access_policy function to use GDPR-compliant defaults
-- ============================================================================

CREATE OR REPLACE FUNCTION set_data_access_policy(
  p_store_id UUID,
  p_data_type TEXT,
  p_policy TEXT
)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Validate data type
  IF p_data_type NOT IN ('products', 'customers', 'orders') THEN
    RAISE EXCEPTION 'Invalid data type: %. Must be products, customers, or orders', p_data_type;
  END IF;

  -- Validate policy
  IF p_policy NOT IN ('sync', 'api_only', 'not_allowed') THEN
    RAISE EXCEPTION 'Invalid policy: %. Must be sync, api_only, or not_allowed', p_policy;
  END IF;

  -- Update the appropriate policy based on data type
  EXECUTE format(
    'UPDATE store_sync_config SET %I = $1::data_access_policy, updated_at = now() WHERE store_id = $2',
    p_data_type || '_access_policy'
  )
  USING p_policy, p_store_id;

  -- If no rows were updated, insert a new config row with GDPR-compliant defaults
  IF NOT FOUND THEN
    -- Insert with the specified policy and GDPR-compliant defaults for others
    INSERT INTO store_sync_config (store_id, products_access_policy, customers_access_policy, orders_access_policy)
    VALUES (
      p_store_id,
      CASE WHEN p_data_type = 'products' THEN p_policy::data_access_policy ELSE 'sync'::data_access_policy END,
      CASE WHEN p_data_type = 'customers' THEN p_policy::data_access_policy ELSE 'api_only'::data_access_policy END,
      CASE WHEN p_data_type = 'orders' THEN p_policy::data_access_policy ELSE 'api_only'::data_access_policy END
    );
  END IF;
END;
$$;

COMMENT ON FUNCTION set_data_access_policy IS 'Set data access policy for a specific store and data type (products, customers, orders). New stores default to api_only for customers and orders (GDPR compliant).';

-- ============================================================================
-- Log migration completion
-- ============================================================================

DO $$
BEGIN
  RAISE NOTICE 'Updated default access policies for GDPR compliance:';
  RAISE NOTICE '  - customers_access_policy: default api_only';
  RAISE NOTICE '  - orders_access_policy: default api_only';
  RAISE NOTICE '  - products_access_policy: remains sync';
END $$;
