setup-supabase.sql

-- Supabase Migration Script for Voice-to-Task App
-- Run this in your Supabase SQL Editor

-- Enable the UUID extension if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create tasks table
CREATE TABLE IF NOT EXISTS public.tasks (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title VARCHAR(500) NOT NULL,
  description TEXT,
  quadrant VARCHAR(20) NOT NULL CHECK (quadrant IN ('urgent-important', 'important', 'urgent', 'neither')),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'in-progress', 'completed')),
  voice_note_url VARCHAR(500),
  transcription TEXT,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  link VARCHAR(500),
  archived BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create voice_uploads table for tracking transcription status
CREATE TABLE IF NOT EXISTS public.voice_uploads (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  filename VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size INTEGER NOT NULL,
  transcription_id VARCHAR(255),
  transcription_status VARCHAR(50) DEFAULT 'pending' CHECK (transcription_status IN ('pending', 'processing', 'completed', 'failed')),
  transcription_text TEXT,
  title VARCHAR(255),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  error_message TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_tasks_quadrant ON public.tasks(quadrant);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON public.tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON public.tasks(user_id);
CREATE INDEX IF NOT EXISTS idx_tasks_created_at ON public.tasks(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_tasks_archived ON public.tasks(archived);

CREATE INDEX IF NOT EXISTS idx_voice_uploads_transcription_id ON public.voice_uploads(transcription_id);
CREATE INDEX IF NOT EXISTS idx_voice_uploads_status ON public.voice_uploads(transcription_status);
CREATE INDEX IF NOT EXISTS idx_voice_uploads_user_id ON public.voice_uploads(user_id);

-- Create function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updated_at
DROP TRIGGER IF EXISTS update_tasks_updated_at ON public.tasks;
CREATE TRIGGER update_tasks_updated_at
  BEFORE UPDATE ON public.tasks
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

DROP TRIGGER IF EXISTS update_voice_uploads_updated_at ON public.voice_uploads;
CREATE TRIGGER update_voice_uploads_updated_at
  BEFORE UPDATE ON public.voice_uploads
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- Enable Row Level Security (RLS)
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.voice_uploads ENABLE ROW LEVEL SECURITY;

-- Create RLS policies for tasks table
CREATE POLICY "Users can view their own tasks" ON public.tasks
  FOR SELECT USING (auth.uid() = user_id);

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

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

CREATE POLICY "Users can delete their own tasks" ON public.tasks
  FOR DELETE USING (auth.uid() = user_id);

-- Create RLS policies for voice_uploads table
CREATE POLICY "Users can view their own voice uploads" ON public.voice_uploads
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own voice uploads" ON public.voice_uploads
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own voice uploads" ON public.voice_uploads
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete their own voice uploads" ON public.voice_uploads
  FOR DELETE USING (auth.uid() = user_id);

-- Create a function to handle new user signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  -- You can add any additional user setup here if needed
  -- For example, creating a user profile or default tasks
  RETURN NEW;
END;
$$ language 'plpgsql' security definer;

-- Create trigger for new user signup
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();

-- Grant necessary permissions
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA public TO authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO authenticated;

-- Create a view for task statistics (optional)
CREATE OR REPLACE VIEW public.task_stats AS
SELECT 
  user_id,
  quadrant,
  status,
  archived,
  COUNT(*) as task_count
FROM public.tasks
GROUP BY user_id, quadrant, status, archived;

-- Grant access to the view
GRANT SELECT ON public.task_stats TO authenticated;

-- Create real-time publication for task changes
DROP PUBLICATION IF EXISTS supabase_realtime;
CREATE PUBLICATION supabase_realtime;

-- Add tables to the publication for real-time updates
ALTER PUBLICATION supabase_realtime ADD TABLE public.tasks;
ALTER PUBLICATION supabase_realtime ADD TABLE public.voice_uploads;

-- Enable realtime for the tables
ALTER TABLE public.tasks REPLICA IDENTITY FULL;
ALTER TABLE public.voice_uploads REPLICA IDENTITY FULL;

COMMENT ON TABLE public.tasks IS 'Tasks in the Eisenhower Matrix with user ownership';
COMMENT ON TABLE public.voice_uploads IS 'Voice uploads and transcription tracking';

-- Verify the setup
SELECT 
  schemaname, 
  tablename, 
  rowsecurity,
  (SELECT count(*) FROM information_schema.table_constraints 
   WHERE constraint_type = 'CHECK' 
   AND table_schema = 'public' 
   AND table_name = tables.tablename) as check_constraints
FROM pg_tables tables
WHERE schemaname = 'public' 
  AND tablename IN ('tasks', 'voice_uploads');

-- Show RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies 
WHERE schemaname = 'public' 
  AND tablename IN ('tasks', 'voice_uploads');