-- Migration: Data Access Permissions for Stores
-- Description: Adds data_access_permissions column to stores table for GDPR compliance
-- Date: 2025-10-31
-- Related: Issue #48 - GDPR-compliant webshop sync refactoring

-- ============================================================================
-- STEP 1: Add data_access_permissions Column to Stores Table
-- ============================================================================

ALTER TABLE stores ADD COLUMN IF NOT EXISTS data_access_permissions JSONB DEFAULT '{
  "allow_customer_access": true,
  "allow_order_access": true,
  "allow_product_access": true
}'::jsonb;

-- Create GIN index for JSONB queries (efficient for searching within JSON)
CREATE INDEX IF NOT EXISTS idx_stores_data_access ON stores USING gin(data_access_permissions);

-- ============================================================================
-- STEP 2: Update Existing Stores with Default Permissions
-- ============================================================================

-- Set default permissions for all existing stores (enabled by default for backward compatibility)
UPDATE stores
SET data_access_permissions = '{
  "allow_customer_access": true,
  "allow_order_access": true,
  "allow_product_access": true
}'::jsonb
WHERE data_access_permissions IS NULL;

-- ============================================================================
-- STEP 3: Add Check Constraint
-- ============================================================================

-- Ensure data_access_permissions always contains required keys
ALTER TABLE stores ADD CONSTRAINT data_access_permissions_structure
  CHECK (
    data_access_permissions ? 'allow_customer_access' AND
    data_access_permissions ? 'allow_order_access' AND
    data_access_permissions ? 'allow_product_access'
  );

-- ============================================================================
-- STEP 4: Create Helper Functions
-- ============================================================================

-- Function to check if a user can access specific data type for a store
CREATE OR REPLACE FUNCTION can_access_store_data(
  p_user_id UUID,
  p_store_id UUID,
  p_data_type TEXT -- 'customer', 'order', or 'product'
)
RETURNS BOOLEAN AS $$
DECLARE
  store_record RECORD;
  permission_key TEXT;
BEGIN
  -- Validate data_type parameter
  IF p_data_type NOT IN ('customer', 'order', 'product') THEN
    RAISE EXCEPTION 'Invalid data_type. Must be customer, order, or product';
  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;

-- Function to update store data access permissions
CREATE OR REPLACE FUNCTION update_store_data_access(
  p_store_id UUID,
  p_user_id UUID,
  p_allow_customer_access BOOLEAN DEFAULT NULL,
  p_allow_order_access BOOLEAN DEFAULT NULL,
  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 (keep existing values if parameter is NULL)
  updated_permissions := jsonb_build_object(
    'allow_customer_access', COALESCE(p_allow_customer_access, (current_permissions->>'allow_customer_access')::boolean),
    'allow_order_access', COALESCE(p_allow_order_access, (current_permissions->>'allow_order_access')::boolean),
    'allow_product_access', COALESCE(p_allow_product_access, (current_permissions->>'allow_product_access')::boolean)
  );

  -- 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;

-- Function to get stores with specific data access enabled
CREATE OR REPLACE FUNCTION get_stores_with_data_access(
  p_user_id UUID,
  p_data_type TEXT -- 'customer', 'order', or 'product'
)
RETURNS TABLE (
  store_id UUID,
  store_name TEXT,
  platform_name TEXT,
  store_url TEXT
) AS $$
DECLARE
  permission_key TEXT;
BEGIN
  -- Validate data_type parameter
  IF p_data_type NOT IN ('customer', 'order', 'product') THEN
    RAISE EXCEPTION 'Invalid data_type. Must be customer, order, or product';
  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;

-- ============================================================================
-- STEP 5: Create Audit Function for Permission Changes
-- ============================================================================

-- Create audit table for tracking permission changes
CREATE TABLE IF NOT EXISTS store_permission_audit (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  changed_by UUID REFERENCES auth.users(id),
  old_permissions JSONB,
  new_permissions JSONB,
  change_reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_store_permission_audit_store_id ON store_permission_audit(store_id);
CREATE INDEX idx_store_permission_audit_created_at ON store_permission_audit(created_at);

-- Trigger to log permission changes
CREATE OR REPLACE FUNCTION log_permission_change()
RETURNS TRIGGER AS $$
BEGIN
  -- Only log if permissions actually changed
  IF OLD.data_access_permissions IS DISTINCT FROM NEW.data_access_permissions THEN
    INSERT INTO store_permission_audit (
      store_id,
      user_id,
      changed_by,
      old_permissions,
      new_permissions,
      change_reason
    ) VALUES (
      NEW.id,
      NEW.user_id,
      auth.uid(),
      OLD.data_access_permissions,
      NEW.data_access_permissions,
      'Permission updated'
    );
  END IF;

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

CREATE TRIGGER trigger_log_permission_change
  AFTER UPDATE ON stores
  FOR EACH ROW
  WHEN (OLD.data_access_permissions IS DISTINCT FROM NEW.data_access_permissions)
  EXECUTE FUNCTION log_permission_change();

-- ============================================================================
-- STEP 6: Enable RLS on Audit Table
-- ============================================================================

ALTER TABLE store_permission_audit ENABLE ROW LEVEL SECURITY;

-- Policy: Users can view audit logs for their own stores
CREATE POLICY "Users can view own permission audit logs"
  ON store_permission_audit
  FOR SELECT
  USING (user_id = auth.uid());

-- ============================================================================
-- STEP 7: Comments for Documentation
-- ============================================================================

COMMENT ON COLUMN stores.data_access_permissions IS 'JSONB object controlling access to customer, order, and product data';
COMMENT ON TABLE store_permission_audit IS 'Audit log for tracking changes to store data access permissions';
COMMENT ON FUNCTION can_access_store_data(UUID, UUID, TEXT) IS 'Checks if a user can access specific data type for a store';
COMMENT ON FUNCTION update_store_data_access(UUID, UUID, BOOLEAN, BOOLEAN, BOOLEAN) IS 'Updates data access permissions for a store';
COMMENT ON FUNCTION get_stores_with_data_access(UUID, TEXT) IS 'Returns stores where specific data type access is enabled';

-- ============================================================================
-- STEP 8: Grant Necessary Permissions
-- ============================================================================

GRANT EXECUTE ON FUNCTION can_access_store_data(UUID, UUID, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION update_store_data_access(UUID, UUID, BOOLEAN, BOOLEAN, BOOLEAN) TO authenticated;
GRANT EXECUTE ON FUNCTION get_stores_with_data_access(UUID, TEXT) TO authenticated;
