LyndonCatan/audio-forensic-app

1-- Create audio_analyses table2CREATE TABLE IF NOT EXISTS audio_analyses (3    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,4    filename TEXT NOT NULL,5    duration REAL NOT NULL,6    sample_rate INTEGER NOT NULL,7    average_rms REAL NOT NULL,8    detected_sounds INTEGER NOT NULL,9    dominant_frequency REAL NOT NULL,10    max_decibels REAL NOT NULL,11    sound_events JSONB NOT NULL DEFAULT '[]',12    frequency_spectrum JSONB NOT NULL DEFAULT '[]',13    analysis_results JSONB NOT NULL DEFAULT '{}',14    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),15    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE16);17 18-- Create analysis_sessions table19CREATE TABLE IF NOT EXISTS analysis_sessions (20    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,21    session_name TEXT NOT NULL,22    audio_file_url TEXT,23    analysis_data JSONB NOT NULL DEFAULT '{}',24    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),25    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE26);27 28-- Create indexes for better performance29CREATE INDEX IF NOT EXISTS idx_audio_analyses_created_at ON audio_analyses(created_at DESC);30CREATE INDEX IF NOT EXISTS idx_audio_analyses_user_id ON audio_analyses(user_id);31CREATE INDEX IF NOT EXISTS idx_analysis_sessions_created_at ON analysis_sessions(created_at DESC);32CREATE INDEX IF NOT EXISTS idx_analysis_sessions_user_id ON analysis_sessions(user_id);33 34-- Enable Row Level Security (RLS)35ALTER TABLE audio_analyses ENABLE ROW LEVEL SECURITY;36ALTER TABLE analysis_sessions ENABLE ROW LEVEL SECURITY;37 38-- Create RLS policies39CREATE POLICY "Users can view their own audio analyses" ON audio_analyses40    FOR SELECT USING (auth.uid() = user_id OR user_id IS NULL);41 42CREATE POLICY "Users can insert their own audio analyses" ON audio_analyses43    FOR INSERT WITH CHECK (auth.uid() = user_id OR user_id IS NULL);44 45CREATE POLICY "Users can update their own audio analyses" ON audio_analyses46    FOR UPDATE USING (auth.uid() = user_id OR user_id IS NULL);47 48CREATE POLICY "Users can delete their own audio analyses" ON audio_analyses49    FOR DELETE USING (auth.uid() = user_id OR user_id IS NULL);50 51CREATE POLICY "Users can view their own analysis sessions" ON analysis_sessions52    FOR SELECT USING (auth.uid() = user_id OR user_id IS NULL);53 54CREATE POLICY "Users can insert their own analysis sessions" ON analysis_sessions55    FOR INSERT WITH CHECK (auth.uid() = user_id OR user_id IS NULL);56 57CREATE POLICY "Users can update their own analysis sessions" ON analysis_sessions58    FOR UPDATE USING (auth.uid() = user_id OR user_id IS NULL);59 60CREATE POLICY "Users can delete their own analysis sessions" ON analysis_sessions61    FOR DELETE USING (auth.uid() = user_id OR user_id IS NULL);62