-- Migration: 00031_update_photo_count_constraint
-- Description: Update photo count constraint from 2-5 to 1-5 photos
-- Author: Claude Code
-- Date: 2025-12-27

-- ============================================
-- UPDATE PHOTO COUNT CONSTRAINT
-- ============================================

-- Drop existing constraint
ALTER TABLE reviews DROP CONSTRAINT reviews_photo_count_check;

-- Add updated constraint: 1-5 photos (was 2-5)
ALTER TABLE reviews ADD CONSTRAINT reviews_photo_count_check
  CHECK (
    -- Allow empty array for existing reviews (backward compatibility)
    array_length(photo_urls, 1) IS NULL OR
    -- New reviews: 1-5 photos (reduced minimum from 2)
    (array_length(photo_urls, 1) >= 1 AND array_length(photo_urls, 1) <= 5)
  );

COMMENT ON CONSTRAINT reviews_photo_count_check ON reviews IS
  'Enforces 1-5 photo requirement for new reviews. Existing reviews may have 0 photos (legacy).';

-- ============================================
-- UPDATE TRIGGER VALIDATION FUNCTION
-- ============================================

-- Update trigger function to validate 1-5 photos (was 2-5)
CREATE OR REPLACE FUNCTION trigger_validate_review_photos()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  -- Validate photo count: 1-5 (reduced minimum from 2)
  IF array_length(NEW.photo_urls, 1) < 1 OR array_length(NEW.photo_urls, 1) > 5 THEN
    RAISE EXCEPTION 'Reviews must have between 1 and 5 photos. Found: %', array_length(NEW.photo_urls, 1);
  END IF;

  -- Validate photo URLs are from correct storage bucket
  IF NOT validate_review_photos(NEW.photo_urls) THEN
    RAISE EXCEPTION 'Invalid photo URLs. All photos must be from attachments/reviews/ bucket.';
  END IF;

  RETURN NEW;
END;
$$;

COMMENT ON FUNCTION trigger_validate_review_photos() IS 'Validates photo URLs are from correct storage bucket and count is 1-5 (updated from 2-5)';
