-- Migration: Fix data_access_permissions Constraint for GDPR Compliance
-- Description: Updates CHECK constraint to only require product access after customer/order sync removal
-- Date: 2025-10-31
-- Related: Issue #51 - WooCommerce connection error due to constraint mismatch

-- ============================================================================
-- STEP 1: Drop Old Constraint
-- ============================================================================

ALTER TABLE stores DROP CONSTRAINT IF EXISTS data_access_permissions_structure;

RAISE NOTICE 'Dropped old data_access_permissions_structure constraint';

-- ============================================================================
-- STEP 2: Update Existing Stores to Remove Customer/Order Access Keys
-- ============================================================================

-- Since customer/order data is no longer cached (GDPR compliance),
-- we should remove these keys or set them to false
UPDATE stores
SET data_access_permissions = jsonb_build_object(
  'allow_product_access',
  COALESCE((data_access_permissions->>'allow_product_access')::boolean, true)
)
WHERE data_access_permissions IS NOT NULL;

RAISE NOTICE 'Updated existing stores data_access_permissions to product-only';

-- ============================================================================
-- STEP 3: Update Default Value for New Stores
-- ============================================================================

-- Update default to only include product access
ALTER TABLE stores ALTER COLUMN data_access_permissions
  SET DEFAULT '{"allow_product_access": true}'::jsonb;

RAISE NOTICE 'Updated default value for data_access_permissions';

-- ============================================================================
-- STEP 4: Add New Constraint (Product Access Only)
-- ============================================================================

-- New constraint only requires product access key
ALTER TABLE stores ADD CONSTRAINT data_access_permissions_structure
  CHECK (data_access_permissions ? 'allow_product_access');

RAISE NOTICE 'Added new data_access_permissions_structure constraint (product-only)';

-- ============================================================================
-- STEP 5: Update Helper Functions
-- ============================================================================

-- Update can_access_store_data function to only support 'product' type
CREATE OR REPLACE FUNCTION can_access_store_data(
  p_user_id UUID,
  p_store_id UUID,
  p_data_type TEXT -- Only 'product' is valid now
)
RETURNS BOOLEAN AS $$
DECLARE
  store_record RECORD;
  permission_key TEXT;
BEGIN
  -- After GDPR compliance, only 'product' is supported
  IF p_data_type NOT IN ('product') THEN
    RAISE EXCEPTION 'Invalid data_type. Only "product" is supported after GDPR compliance. Customer and order data must be accessed in real-time via API.';
  END IF;

  -- Build permission key
  permission_key := 'allow_' || p_data_type || '_access';

  -- Check if store belongs to user and has permission enabled
  SELECT
    s.id,
    s.user_id,
    s.is_active,
    (s.data_access_permissions->permission_key)::boolean AS has_permission
  INTO store_record
  FROM stores s
  WHERE s.id = p_store_id AND s.user_id = p_user_id AND s.is_active = true;

  -- Return false if store not found or not active
  IF NOT FOUND THEN
    RETURN false;
  END IF;

  -- Return the permission value
  RETURN COALESCE(store_record.has_permission, false);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

RAISE NOTICE 'Updated can_access_store_data function to support product-only';

-- Update update_store_data_access function to only support product access
CREATE OR REPLACE FUNCTION update_store_data_access(
  p_store_id UUID,
  p_user_id UUID,
  p_allow_product_access BOOLEAN DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
  current_permissions JSONB;
  updated_permissions JSONB;
  rows_affected INTEGER;
BEGIN
  -- Get current permissions
  SELECT data_access_permissions INTO current_permissions
  FROM stores
  WHERE id = p_store_id AND user_id = p_user_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Store not found or access denied';
  END IF;

  -- Build updated permissions (only product access)
  updated_permissions := jsonb_build_object(
    'allow_product_access',
    COALESCE(p_allow_product_access, (current_permissions->>'allow_product_access')::boolean, true)
  );

  -- Update the store
  UPDATE stores
  SET
    data_access_permissions = updated_permissions,
    updated_at = NOW()
  WHERE id = p_store_id AND user_id = p_user_id;

  GET DIAGNOSTICS rows_affected = ROW_COUNT;
  RETURN rows_affected > 0;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

RAISE NOTICE 'Updated update_store_data_access function to support product-only';

-- Update get_stores_with_data_access function to only support 'product'
CREATE OR REPLACE FUNCTION get_stores_with_data_access(
  p_user_id UUID,
  p_data_type TEXT -- Only 'product' is valid now
)
RETURNS TABLE (
  store_id UUID,
  store_name TEXT,
  platform_name TEXT,
  store_url TEXT
) AS $$
DECLARE
  permission_key TEXT;
BEGIN
  -- After GDPR compliance, only 'product' is supported
  IF p_data_type NOT IN ('product') THEN
    RAISE EXCEPTION 'Invalid data_type. Only "product" is supported after GDPR compliance.';
  END IF;

  -- Build permission key
  permission_key := 'allow_' || p_data_type || '_access';

  RETURN QUERY
  SELECT
    s.id AS store_id,
    s.store_name,
    s.platform_name,
    s.store_url
  FROM stores s
  WHERE
    s.user_id = p_user_id
    AND s.is_active = true
    AND (s.data_access_permissions->>permission_key)::boolean = true
  ORDER BY s.store_name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

RAISE NOTICE 'Updated get_stores_with_data_access function to support product-only';

-- ============================================================================
-- STEP 6: Update Comments
-- ============================================================================

COMMENT ON COLUMN stores.data_access_permissions IS 'JSONB object controlling access to product data (customer/order removed for GDPR compliance)';
COMMENT ON FUNCTION can_access_store_data(UUID, UUID, TEXT) IS 'Checks if a user can access product data for a store (GDPR: customer/order not supported)';
COMMENT ON FUNCTION update_store_data_access(UUID, UUID, BOOLEAN) IS 'Updates product data access permission for a store (GDPR: customer/order removed)';
COMMENT ON FUNCTION get_stores_with_data_access(UUID, TEXT) IS 'Returns stores where product data access is enabled (GDPR: customer/order not supported)';

-- ============================================================================
-- STEP 7: Summary
-- ============================================================================

DO $$
BEGIN
  RAISE NOTICE '=================================================================';
  RAISE NOTICE 'Data Access Permissions Constraint Fix Complete';
  RAISE NOTICE '=================================================================';
  RAISE NOTICE 'Changes:';
  RAISE NOTICE '  - Updated CHECK constraint to only require allow_product_access';
  RAISE NOTICE '  - Removed allow_customer_access and allow_order_access from existing stores';
  RAISE NOTICE '  - Updated helper functions to only support product data type';
  RAISE NOTICE '  - Updated default value for new stores';
  RAISE NOTICE '';
  RAISE NOTICE 'Rationale:';
  RAISE NOTICE '  - Customer and order cache tables were dropped for GDPR compliance';
  RAISE NOTICE '  - Customer/order data now accessed in real-time via webshop-data-api';
  RAISE NOTICE '  - Only product data is cached in database';
  RAISE NOTICE '=================================================================';
END $$;
