-- Migration: 00030_add_review_photos
-- Description: Add photo URL storage to reviews table
-- Author: Claude Code
-- Date: 2025-12-26

-- ============================================
-- ADD PHOTO SUPPORT TO REVIEWS
-- ============================================

-- Add photo_urls column to reviews table
ALTER TABLE reviews
ADD COLUMN photo_urls TEXT[] NOT NULL DEFAULT '{}';

-- Add constraint: must have 2-5 photos
ALTER TABLE reviews
ADD CONSTRAINT reviews_photo_count_check
CHECK (array_length(photo_urls, 1) >= 2 AND array_length(photo_urls, 1) <= 5);

-- Add index for photo queries (GIN index for array operations)
CREATE INDEX idx_reviews_has_photos ON reviews
USING GIN (photo_urls);

COMMENT ON COLUMN reviews.photo_urls IS 'Array of public URLs for review photos (2-5 required)';
COMMENT ON CONSTRAINT reviews_photo_count_check ON reviews IS 'Enforces 2-5 photo requirement';

-- ============================================
-- STORAGE BUCKET SETUP
-- ============================================

-- Create attachments bucket (if not exists)
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'attachments',
  'attachments',
  true,  -- Public bucket for review photos
  10485760,  -- 10MB limit per file
  ARRAY['image/jpeg', 'image/png', 'image/heic', 'image/heif']
)
ON CONFLICT (id) DO UPDATE SET
  file_size_limit = 10485760,
  allowed_mime_types = ARRAY['image/jpeg', 'image/png', 'image/heic', 'image/heif'];

-- ============================================
-- STORAGE RLS POLICIES
-- ============================================

-- Authenticated users can upload to their own review folders
CREATE POLICY "Users can upload review photos"
ON storage.objects FOR INSERT
TO authenticated
WITH CHECK (
  bucket_id = 'attachments' AND
  (storage.foldername(name))[1] = 'reviews'
);

-- Anyone can view public review photos
CREATE POLICY "Anyone can view review photos"
ON storage.objects FOR SELECT
TO public
USING (
  bucket_id = 'attachments' AND
  (storage.foldername(name))[1] = 'reviews'
);

-- Authors can delete their own review photos
-- Note: This relies on RLS at the review level - if user owns the review, they can delete photos
CREATE POLICY "Authenticated users can delete review photos"
ON storage.objects FOR DELETE
TO authenticated
USING (
  bucket_id = 'attachments' AND
  (storage.foldername(name))[1] = 'reviews'
);

-- ============================================
-- ORPHANED PHOTO CLEANUP FUNCTION
-- ============================================

-- Function to identify orphaned photos (photos not referenced by any review)
CREATE OR REPLACE FUNCTION cleanup_orphaned_photos()
RETURNS TABLE(file_path TEXT, file_size BIGINT, created_at TIMESTAMPTZ)
SECURITY DEFINER
AS $$
BEGIN
  -- Return list of orphaned photos older than 7 days
  -- These are files in storage.objects that are NOT referenced in any review.photo_urls
  RETURN QUERY
  SELECT
    objects.name AS file_path,
    objects.metadata->>'size' AS file_size,
    objects.created_at
  FROM storage.objects
  WHERE bucket_id = 'attachments'
    AND (storage.foldername(name))[1] = 'reviews'
    AND created_at < NOW() - INTERVAL '7 days'
    AND NOT EXISTS (
      SELECT 1 FROM reviews
      WHERE photo_urls @> ARRAY[
        'https://' || current_setting('app.settings.supabase_url', true) ||
        '/storage/v1/object/public/attachments/' || objects.name
      ]::TEXT[]
    );
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION cleanup_orphaned_photos() IS 'Returns orphaned review photos older than 7 days that can be safely deleted. Run periodically as a maintenance job.';

-- ============================================
-- HELPER FUNCTION FOR PHOTO VALIDATION
-- ============================================

-- Function to validate photo URLs belong to correct storage path
CREATE OR REPLACE FUNCTION validate_review_photos(photo_urls TEXT[])
RETURNS BOOLEAN
SECURITY DEFINER
AS $$
DECLARE
  photo_url TEXT;
BEGIN
  -- Validate each photo URL
  FOREACH photo_url IN ARRAY photo_urls
  LOOP
    -- Check URL format: must be from attachments/reviews/ bucket
    IF photo_url !~ '^https?://.*/storage/v1/object/public/attachments/reviews/.+\.(jpg|jpeg|png|heic|heif)$' THEN
      RETURN FALSE;
    END IF;
  END LOOP;

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- TRIGGER TO VALIDATE PHOTO URLs
-- ============================================

-- Trigger function to validate photo URLs on insert/update
CREATE OR REPLACE FUNCTION trigger_validate_review_photos()
RETURNS TRIGGER
SECURITY DEFINER
AS $$
BEGIN
  -- Validate photo count (belt-and-suspenders with constraint)
  IF array_length(NEW.photo_urls, 1) < 2 OR array_length(NEW.photo_urls, 1) > 5 THEN
    RAISE EXCEPTION 'Reviews must have between 2 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;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER validate_review_photos_trigger
  BEFORE INSERT OR UPDATE OF photo_urls ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION trigger_validate_review_photos();

COMMENT ON TRIGGER validate_review_photos_trigger ON reviews IS 'Validates photo URLs are from correct storage bucket and count is 2-5';

-- ============================================
-- UPDATE EXISTING DATA (BACKFILL)
-- ============================================

-- NOTE: Since this is a greenfield feature and no reviews currently have photos,
-- we need to temporarily disable the constraint for existing reviews.
-- Once all existing reviews are migrated or deleted, this can be removed.

-- Temporarily make the constraint deferrable for migration
ALTER TABLE reviews DROP CONSTRAINT reviews_photo_count_check;
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
    (array_length(photo_urls, 1) >= 2 AND array_length(photo_urls, 1) <= 5)
  );

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

-- ============================================
-- GRANT PERMISSIONS
-- ============================================

-- Grant execute permission on cleanup function to authenticated users
-- (In production, this would be restricted to admin/cron role)
GRANT EXECUTE ON FUNCTION cleanup_orphaned_photos() TO authenticated;
GRANT EXECUTE ON FUNCTION validate_review_photos(TEXT[]) TO authenticated;
