-- Migration: GDPR compliance - 3-state access policy for store data
-- Date: 2025-11-17
-- Description: Enhances data access control from 2-state (allowed/not-allowed) to 3-state policy:
--   - sync: Store data in cache and Qdrant for fast access
--   - api_only: Direct API access only, no local caching
--   - not_allowed: No access permitted

-- Create enum type for data access policy
CREATE TYPE data_access_policy AS ENUM ('sync', 'api_only', 'not_allowed');

-- Add new columns to store_sync_config table
ALTER TABLE store_sync_config
  ADD COLUMN products_access_policy data_access_policy DEFAULT 'sync',
  ADD COLUMN customers_access_policy data_access_policy DEFAULT 'sync',
  ADD COLUMN orders_access_policy data_access_policy DEFAULT 'sync';

-- Migrate existing boolean values to enum
-- If sync_products = true, set to 'sync', otherwise 'not_allowed'
UPDATE store_sync_config
SET products_access_policy = CASE
  WHEN sync_products = true THEN 'sync'::data_access_policy
  ELSE 'not_allowed'::data_access_policy
END;

UPDATE store_sync_config
SET customers_access_policy = CASE
  WHEN sync_customers = true THEN 'sync'::data_access_policy
  ELSE 'not_allowed'::data_access_policy
END;

UPDATE store_sync_config
SET orders_access_policy = CASE
  WHEN sync_orders = true THEN 'sync'::data_access_policy
  ELSE 'not_allowed'::data_access_policy
END;

-- Make new columns NOT NULL after migration
ALTER TABLE store_sync_config
  ALTER COLUMN products_access_policy SET NOT NULL,
  ALTER COLUMN customers_access_policy SET NOT NULL,
  ALTER COLUMN orders_access_policy SET NOT NULL;

-- Keep old columns for backward compatibility during transition
-- We'll deprecate them in a future migration
COMMENT ON COLUMN store_sync_config.sync_products IS 'DEPRECATED: Use products_access_policy instead. Will be removed in future version.';
COMMENT ON COLUMN store_sync_config.sync_customers IS 'DEPRECATED: Use customers_access_policy instead. Will be removed in future version.';
COMMENT ON COLUMN store_sync_config.sync_orders IS 'DEPRECATED: Use orders_access_policy instead. Will be removed in future version.';

-- Add comments to new columns
COMMENT ON COLUMN store_sync_config.products_access_policy IS 'Product data access policy: sync (cache + Qdrant), api_only (direct API only), not_allowed (no access)';
COMMENT ON COLUMN store_sync_config.customers_access_policy IS 'Customer data access policy: sync (cache + Qdrant), api_only (direct API only), 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), not_allowed (no access)';

-- Create helper function to get data access policy
CREATE OR REPLACE FUNCTION get_data_access_policy(
  p_store_id UUID,
  p_data_type TEXT
)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_policy TEXT;
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;

  -- Get the appropriate policy based on data type
  EXECUTE format(
    'SELECT %I::TEXT FROM store_sync_config WHERE store_id = $1',
    p_data_type || '_access_policy'
  )
  INTO v_policy
  USING p_store_id;

  -- Return policy or default to 'sync' if no config exists
  RETURN COALESCE(v_policy, 'sync');
END;
$$;

COMMENT ON FUNCTION get_data_access_policy IS 'Get data access policy for a specific store and data type (products, customers, orders)';

-- Create helper function to update data access policy
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
  IF NOT FOUND THEN
    -- Insert with the specified policy
    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 'sync'::data_access_policy END,
      CASE WHEN p_data_type = 'orders' THEN p_policy::data_access_policy ELSE 'sync'::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)';
