-- Migration: Riding Cover Photos
-- Description: Create table for community-submitted riding cover photos
-- Create the riding_cover_photos table
CREATE TABLE IF NOT EXISTS riding_cover_photos (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
riding_name VARCHAR(255) NOT NULL,
riding_id VARCHAR(100),
image_url TEXT NOT NULL,
submitted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
submitter_username VARCHAR(50),
submitted_at TIMESTAMPTZ DEFAULT NOW(),
submission_day_of_year INT NOT NULL,
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
approved_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
approved_at TIMESTAMPTZ,
rejected_at TIMESTAMPTZ,
rejection_reason TEXT,
caption TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Add comments for documentation
COMMENT ON TABLE riding_cover_photos IS 'Community-submitted cover photos for user profiles based on their riding';
COMMENT ON COLUMN riding_cover_photos.submission_day_of_year IS 'Day of year (1-366) when photo was submitted, used for seasonal rotation';
COMMENT ON COLUMN riding_cover_photos.status IS 'Moderation status: pending, approved, or rejected';
-- Create index for fast lookup by riding + day of year range + status
CREATE INDEX IF NOT EXISTS idx_riding_cover_lookup
ON riding_cover_photos(riding_name, submission_day_of_year, status);
-- Create index for admin moderation (pending photos)
CREATE INDEX IF NOT EXISTS idx_riding_cover_pending
ON riding_cover_photos(status, submitted_at) WHERE status = 'pending';
-- Create index for user's submissions
CREATE INDEX IF NOT EXISTS idx_riding_cover_user
ON riding_cover_photos(submitted_by, created_at);
-- Enable RLS
ALTER TABLE riding_cover_photos ENABLE ROW LEVEL SECURITY;
-- RLS Policies
-- Anyone can view approved photos
CREATE POLICY "Anyone can view approved photos"
ON riding_cover_photos
FOR SELECT
USING (status = 'approved');
-- Authenticated users can view their own submissions (any status)
CREATE POLICY "Users can view their own submissions"
ON riding_cover_photos
FOR SELECT
TO authenticated
USING (submitted_by = auth.uid());
-- Authenticated users can submit photos
CREATE POLICY "Authenticated users can submit photos"
ON riding_cover_photos
FOR INSERT
TO authenticated
WITH CHECK (submitted_by = auth.uid());
-- Users can delete their own pending submissions
CREATE POLICY "Users can delete own pending submissions"
ON riding_cover_photos
FOR DELETE
TO authenticated
USING (submitted_by = auth.uid() AND status = 'pending');
-- Create storage bucket for riding cover photos
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
'riding-covers',
'riding-covers',
true,
5242880, -- 5MB limit
ARRAY['image/jpeg', 'image/png', 'image/webp']
)
ON CONFLICT (id) DO NOTHING;
-- Storage policies for riding-covers bucket
-- Anyone can view files in the bucket (public)
CREATE POLICY "Public read access for riding covers"
ON storage.objects
FOR SELECT
USING (bucket_id = 'riding-covers');
-- Authenticated users can upload to the bucket
CREATE POLICY "Authenticated users can upload riding covers"
ON storage.objects
FOR INSERT
TO authenticated
WITH CHECK (
bucket_id = 'riding-covers' AND
(storage.foldername(name))[1] = auth.uid()::text
);
-- Users can delete their own uploads
CREATE POLICY "Users can delete own riding cover uploads"
ON storage.objects
FOR DELETE
TO authenticated
USING (
bucket_id = 'riding-covers' AND
(storage.foldername(name))[1] = auth.uid()::text
);
-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_riding_cover_photos_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create trigger for updated_at
DROP TRIGGER IF EXISTS riding_cover_photos_updated_at ON riding_cover_photos;
CREATE TRIGGER riding_cover_photos_updated_at
BEFORE UPDATE ON riding_cover_photos
FOR EACH ROW
EXECUTE FUNCTION update_riding_cover_photos_updated_at();
