supabase-fix-notification-sound-sync.sql

-- Fix notification sound sync issues
-- This script addresses problems with user_settings table creation and sound_type field

-- First, ensure the user_settings table exists with correct structure
CREATE TABLE IF NOT EXISTS public.user_settings (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE UNIQUE,
  pomodoro_length INTEGER NOT NULL DEFAULT 25,
  short_break_length INTEGER NOT NULL DEFAULT 5,
  long_break_length INTEGER NOT NULL DEFAULT 15,
  sessions_until_long_break INTEGER NOT NULL DEFAULT 4,
  auto_start_breaks BOOLEAN NOT NULL DEFAULT false,
  auto_start_pomodoros BOOLEAN NOT NULL DEFAULT false,
  sound_enabled BOOLEAN NOT NULL DEFAULT true,
  sound_type TEXT NOT NULL DEFAULT 'beep',
  notifications_enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Add constraint to ensure sound_type is one of the allowed values
DO $$
BEGIN
    -- Drop existing constraint if it exists
    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints 
        WHERE constraint_name = 'user_settings_sound_type_check' 
        AND table_name = 'user_settings'
    ) THEN
        ALTER TABLE public.user_settings DROP CONSTRAINT user_settings_sound_type_check;
    END IF;
    
    -- Add the constraint
    ALTER TABLE public.user_settings 
    ADD CONSTRAINT user_settings_sound_type_check 
    CHECK (sound_type IN ('beep', 'chime', 'bell', 'notification', 'success'));
    
    RAISE NOTICE 'Added sound_type constraint to user_settings table';
END $$;

-- Ensure sound_type column exists and has correct default
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
        AND table_name = 'user_settings'
        AND column_name = 'sound_type'
    ) THEN
        ALTER TABLE public.user_settings 
        ADD COLUMN sound_type TEXT NOT NULL DEFAULT 'beep';
        
        RAISE NOTICE 'Added sound_type column to user_settings table';
    ELSE
        -- Update existing records that might have invalid sound_type values
        UPDATE public.user_settings 
        SET sound_type = 'beep' 
        WHERE sound_type IS NULL OR sound_type NOT IN ('beep', 'chime', 'bell', 'notification', 'success');
        
        RAISE NOTICE 'Updated invalid sound_type values in user_settings table';
    END IF;
END $$;

-- Enable RLS if not already enabled
ALTER TABLE public.user_settings ENABLE ROW LEVEL SECURITY;

-- Drop and recreate RLS policies to ensure they're correct
DROP POLICY IF EXISTS "Users can view their own settings" ON public.user_settings;
CREATE POLICY "Users can view their own settings" 
  ON public.user_settings 
  FOR SELECT 
  USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can insert their own settings" ON public.user_settings;
CREATE POLICY "Users can insert their own settings" 
  ON public.user_settings 
  FOR INSERT 
  WITH CHECK (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can update their own settings" ON public.user_settings;
CREATE POLICY "Users can update their own settings" 
  ON public.user_settings 
  FOR UPDATE 
  USING (auth.uid() = user_id);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS user_settings_user_id_idx ON public.user_settings (user_id);

-- Grant permissions
GRANT ALL ON public.user_settings TO service_role;
GRANT SELECT, INSERT, UPDATE ON public.user_settings TO authenticated;

-- Create or replace the trigger function for new users
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  -- Create profile
  INSERT INTO public.profiles (id, username, display_name)
  VALUES (NEW.id, NULL, NEW.raw_user_meta_data->>'full_name')
  ON CONFLICT (id) DO NOTHING;
  
  -- Create default user settings with proper sound_type
  INSERT INTO public.user_settings (
    user_id, 
    pomodoro_length, 
    short_break_length, 
    long_break_length, 
    sessions_until_long_break, 
    auto_start_breaks, 
    auto_start_pomodoros, 
    sound_enabled, 
    sound_type, 
    notifications_enabled
  )
  VALUES (
    NEW.id, 
    25, -- default pomodoro length (minutes)
    5,  -- default short break length (minutes)
    15, -- default long break length (minutes)
    4,  -- default sessions until long break
    false, -- default auto start breaks
    false, -- default auto start pomodoros
    true,  -- default sound enabled
    'beep', -- default sound type
    true   -- default notifications enabled
  )
  ON CONFLICT (user_id) DO NOTHING;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public;

-- Refresh the trigger
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION public.handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = timezone('utc'::text, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Add updated_at trigger to user_settings
DROP TRIGGER IF EXISTS user_settings_updated_at ON public.user_settings;
CREATE TRIGGER user_settings_updated_at
  BEFORE UPDATE ON public.user_settings
  FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();

-- Verify the table structure
SELECT 
  column_name, 
  data_type, 
  is_nullable, 
  column_default,
  CASE 
    WHEN column_name = 'sound_type' THEN (
      SELECT string_agg(unnest, ', ') 
      FROM unnest(string_to_array(replace(replace(check_clause, 'sound_type IN (', ''), ')', ''), ', '))
      FROM information_schema.check_constraints cc
      WHERE cc.constraint_name = 'user_settings_sound_type_check'
    )
    ELSE NULL
  END as allowed_values
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'user_settings'
ORDER BY ordinal_position;

-- Test that sound_type constraint works
DO $$
BEGIN
  -- This should succeed
  PERFORM 1 WHERE 'beep' IN ('beep', 'chime', 'bell', 'notification', 'success');
  RAISE NOTICE 'Sound type constraint test passed';
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Sound type constraint test failed: %', SQLERRM;
END $$;