migrations/20250523183206_odd_moon.sql

/*
  # Storage and File Management Setup

  1. New Storage
    - Create 'codette-files' storage bucket if it doesn't exist
  
  2. Security
    - Enable Row Level Security on codette_files table
    - Create policies for authenticated users to read files
    - Create policies for authenticated users to insert files
    - Create special policy for admin users to insert files
*/

-- Enable RLS on the codette_files table if not already enabled
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_tables 
        WHERE tablename = 'codette_files' 
        AND rowsecurity = true
    ) THEN
        ALTER TABLE public.codette_files ENABLE ROW LEVEL SECURITY;
    END IF;
END $$;

-- Create storage bucket if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM storage.buckets WHERE name = 'codette-files'
    ) THEN
        INSERT INTO storage.buckets (id, name)
        VALUES ('codette-files', 'codette-files');
    END IF;
END $$;

-- Create policies for the codette_files table
DO $$
BEGIN
    -- Check if the read policy exists
    IF NOT EXISTS (
        SELECT 1 FROM pg_policies 
        WHERE policyname = 'Allow authenticated users to read files'
        AND tablename = 'codette_files'
    ) THEN
        CREATE POLICY "Allow authenticated users to read files"
        ON public.codette_files FOR SELECT
        TO authenticated
        USING (true);
    END IF;

    -- Check if the admin insert policy exists
    IF NOT EXISTS (
        SELECT 1 FROM pg_policies 
        WHERE policyname = 'Allow admin users to insert files'
        AND tablename = 'codette_files'
    ) THEN
        CREATE POLICY "Allow admin users to insert files"
        ON public.codette_files FOR INSERT
        TO authenticated
        WITH CHECK ((auth.jwt() ->> 'role')::text = 'admin');
    END IF;

    -- Check if the authenticated insert policy exists
    IF NOT EXISTS (
        SELECT 1 FROM pg_policies 
        WHERE policyname = 'Allow authenticated users to insert files'
        AND tablename = 'codette_files'
    ) THEN
        CREATE POLICY "Allow authenticated users to insert files"
        ON public.codette_files FOR INSERT
        TO authenticated
        WITH CHECK (true);
    END IF;
END $$;

-- Note: For storage.objects policies, you'll need to create them through the Supabase dashboard
-- as migrations don't have sufficient permissions to create these policies directly.
-- Create these policies manually:
-- 1. Policy name: "Allow authenticated users to read files"
--    - For: SELECT operations
--    - Using expression: bucket_id = 'codette-files'
--
-- 2. Policy name: "Allow admin users to upload files"
--    - For: INSERT operations
--    - Using expression: bucket_id = 'codette-files' AND (auth.jwt() ->> 'role')::text = 'admin'