-- Migration: Drop deprecated data_access_permissions column from stores table
-- Description: The data_access_permissions column has been replaced by store_sync_config policies
-- Issue: #98

-- STEP 1: Drop trigger that depends on data_access_permissions
DROP TRIGGER IF EXISTS trigger_log_permission_change ON stores;

-- STEP 2: Drop the function used by the trigger
DROP FUNCTION IF EXISTS log_permission_change();

-- STEP 3: Drop constraints that depend on data_access_permissions
ALTER TABLE stores DROP CONSTRAINT IF EXISTS data_access_permissions_structure;
ALTER TABLE stores DROP CONSTRAINT IF EXISTS stores_data_access_permissions_check;

-- STEP 4: Drop indexes on data_access_permissions
DROP INDEX IF EXISTS idx_stores_data_access;
DROP INDEX IF EXISTS idx_stores_data_access_permissions;

-- STEP 5: Drop the column itself
ALTER TABLE stores DROP COLUMN IF EXISTS data_access_permissions;

-- STEP 6: Add comment to clarify where access policies are now stored
COMMENT ON TABLE store_sync_config IS 'Store sync configuration and data access policies. Contains products_access_policy, customers_access_policy, and orders_access_policy which replace the deprecated stores.data_access_permissions column.';

-- Verify the migration was successful
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
    AND table_name = 'stores'
    AND column_name = 'data_access_permissions'
  ) THEN
    RAISE EXCEPTION 'Migration failed: data_access_permissions column still exists';
  ELSE
    RAISE NOTICE 'Migration successful: data_access_permissions column has been removed from stores table';
  END IF;
END $$;
