-- Migration: Update entity_images table for signed URLs
-- Date: 2025-11-04
-- Description: Replace image_url with s3_key for secure signed URL generation

-- Add new s3_key column
ALTER TABLE entity_images ADD COLUMN s3_key VARCHAR(500) AFTER entity_type;

-- For existing images, extract S3 key from URL
-- Handle both formats: with and without bucket name in path
UPDATE entity_images 
SET s3_key = CASE 
    -- Format: https://hds-spixer.s3.fr-par.scw.cloud/hds-spixer/club/uuid/image.jpg
    WHEN image_url LIKE '%/hds-spixer/%' THEN 
        SUBSTRING(image_url FROM POSITION('/hds-spixer/' IN image_url) + 12)
    -- Format: https://hds-spixer.s3.fr-par.scw.cloud/club/uuid/image.jpg  
    WHEN image_url LIKE '%.scw.cloud/%' THEN 
        SUBSTRING(image_url FROM POSITION('.cloud/' IN image_url) + 7)
    -- Fallback for any other format
    ELSE 
        CONCAT('legacy/', id, '.unknown')
END
WHERE s3_key IS NULL;

-- Make s3_key required after migration
ALTER TABLE entity_images MODIFY COLUMN s3_key VARCHAR(500) NOT NULL;

-- Add index for better performance
CREATE INDEX idx_entity_images_lookup ON entity_images(entity_type, entity_id);

-- Optional: Drop image_url column after verifying migration worked
-- ALTER TABLE entity_images DROP COLUMN image_url;