-- User email notification preferences
-- Depends on: 001_000_initial_auth_setup.sql

-- Create email preferences table
CREATE TABLE email_preferences (
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  weekly_progress_digest BOOLEAN DEFAULT true,
  product_updates BOOLEAN DEFAULT true,
  account_notifications BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Enable RLS on email_preferences
ALTER TABLE email_preferences ENABLE ROW LEVEL SECURITY;

-- Create policies for email_preferences
CREATE POLICY "Users can view their own email preferences" ON email_preferences
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own email preferences" ON email_preferences
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own email preferences" ON email_preferences
  FOR UPDATE USING (auth.uid() = user_id);

-- Create indexes for better performance
CREATE INDEX idx_email_preferences_user_id ON email_preferences(user_id);

-- Function to handle email preferences for new users
CREATE OR REPLACE FUNCTION handle_new_user_email_preferences()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO email_preferences (user_id)
  VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to create email preferences when a profile is created
CREATE TRIGGER on_profile_created_email_preferences
  AFTER INSERT ON profiles
  FOR EACH ROW 
  EXECUTE FUNCTION handle_new_user_email_preferences();

-- Add comment
COMMENT ON TABLE email_preferences IS 'User email notification preferences for newsletters and updates';