LyndonCatan/audio-forensic-app

1-- ================================2-- Audio Forensic Detector Database Setup3-- Run this script in your Supabase SQL Editor4-- ================================5 6-- Enable UUID extension if not already enabled7CREATE EXTENSION IF NOT EXISTS "uuid-ossp";8 9-- Create audio_analyses table10CREATE TABLE IF NOT EXISTS public.audio_analyses (11    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,12    filename TEXT NOT NULL,13    duration REAL NOT NULL,14    sample_rate INTEGER NOT NULL,15    average_rms REAL NOT NULL,16    detected_sounds INTEGER NOT NULL,17    dominant_frequency REAL NOT NULL,18    max_decibels REAL NOT NULL,19    sound_events JSONB NOT NULL DEFAULT '[]',20    frequency_spectrum JSONB NOT NULL DEFAULT '[]',21    analysis_results JSONB NOT NULL DEFAULT '{}',22    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),23    user_id UUID DEFAULT NULL24);25 26-- Create analysis_sessions table27CREATE TABLE IF NOT EXISTS public.analysis_sessions (28    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,29    session_name TEXT NOT NULL,30    audio_file_url TEXT,31    analysis_data JSONB NOT NULL DEFAULT '{}',32    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),33    user_id UUID DEFAULT NULL34);35 36-- Create indexes for better performance37CREATE INDEX IF NOT EXISTS idx_audio_analyses_created_at ON public.audio_analyses(created_at DESC);38CREATE INDEX IF NOT EXISTS idx_audio_analyses_filename ON public.audio_analyses(filename);39CREATE INDEX IF NOT EXISTS idx_analysis_sessions_created_at ON public.analysis_sessions(created_at DESC);40CREATE INDEX IF NOT EXISTS idx_analysis_sessions_name ON public.analysis_sessions(session_name);41 42-- Enable Row Level Security (RLS) - Optional for public access43ALTER TABLE public.audio_analyses ENABLE ROW LEVEL SECURITY;44ALTER TABLE public.analysis_sessions ENABLE ROW LEVEL SECURITY;45 46-- Create policies for public access (since you don't have auth yet)47-- These allow anyone to read/write - you can restrict later when you add authentication48 49DROP POLICY IF EXISTS "Allow public access to audio_analyses" ON public.audio_analyses;50CREATE POLICY "Allow public access to audio_analyses" ON public.audio_analyses51    FOR ALL USING (true) WITH CHECK (true);52 53DROP POLICY IF EXISTS "Allow public access to analysis_sessions" ON public.analysis_sessions;54CREATE POLICY "Allow public access to analysis_sessions" ON public.analysis_sessions55    FOR ALL USING (true) WITH CHECK (true);56 57-- Insert some sample data for testing (optional)58INSERT INTO public.audio_analyses (59    filename, 60    duration, 61    sample_rate, 62    average_rms, 63    detected_sounds, 64    dominant_frequency, 65    max_decibels,66    sound_events,67    frequency_spectrum,68    analysis_results69) VALUES (70    'sample_audio.wav',71    10.5,72    44100,73    0.0234,74    5,75    440.0,76    -12.3,77    '[{"time": 1.2, "type": "Voice", "frequency": 440, "amplitude": 0.8, "decibels": -10}]'::jsonb,78    '[{"frequency": 440, "magnitude": 0.8}]'::jsonb,79    '{"analysisType": "sample", "timestamp": "2024-01-01T00:00:00Z"}'::jsonb80) ON CONFLICT DO NOTHING;81 82-- Verify tables were created83SELECT 84    table_name, 85    column_name, 86    data_type 87FROM information_schema.columns 88WHERE table_schema = 'public' 89    AND table_name IN ('audio_analyses', 'analysis_sessions')90ORDER BY table_name, ordinal_position;91 92-- Show table counts93SELECT 94    'audio_analyses' as table_name, 95    COUNT(*) as record_count 96FROM public.audio_analyses97UNION ALL98SELECT 99    'analysis_sessions' as table_name, 100    COUNT(*) as record_count 101FROM public.analysis_sessions;102