-- ============================================

-- Vivo Health - Supabase Database Schema

-- ============================================

-- Lab Reports Table

CREATE TABLE IF NOT EXISTS lab_reports (

id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

file_name TEXT NOT NULL,

raw_text TEXT NOT NULL,

structured_data JSONB,

ai_analysis TEXT,

uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

CREATE INDEX IF NOT EXISTS idx_lab_reports_user_id ON lab_reports(user_id);

CREATE INDEX IF NOT EXISTS idx_lab_reports_uploaded_at ON lab_reports(uploaded_at DESC);

ALTER TABLE lab_reports ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view own lab reports" ON lab_reports;

DROP POLICY IF EXISTS "Users can insert own lab reports" ON lab_reports;

DROP POLICY IF EXISTS "Users can update own lab reports" ON lab_reports;

DROP POLICY IF EXISTS "Users can delete own lab reports" ON lab_reports;

CREATE POLICY "Users can view own lab reports"

ON lab_reports

FOR SELECT

USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own lab reports"

ON lab_reports

FOR INSERT

WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own lab reports"

ON lab_reports

FOR UPDATE

USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own lab reports"

ON lab_reports

FOR DELETE

USING (auth.uid() = user_id);

-- Notes Table

CREATE TABLE IF NOT EXISTS notes (

id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

title TEXT NOT NULL,

content TEXT NOT NULL,

encrypted BOOLEAN DEFAULT FALSE,

created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()

);

CREATE INDEX IF NOT EXISTS idx_notes_user_id ON notes(user_id);

CREATE INDEX IF NOT EXISTS idx_notes_created_at ON notes(created_at DESC);

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view own notes" ON notes;

DROP POLICY IF EXISTS "Users can insert own notes" ON notes;

DROP POLICY IF EXISTS "Users can update own notes" ON notes;

DROP POLICY IF EXISTS "Users can delete own notes" ON notes;

CREATE POLICY "Users can view own notes"

ON notes

FOR SELECT

USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own notes"

ON notes

FOR INSERT

WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own notes"

ON notes

FOR UPDATE

USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own notes"

ON notes

FOR DELETE

USING (auth.uid() = user_id);

-- Optional tags column for notes (run only if missing)

DO $$

BEGIN

IF NOT EXISTS (

SELECT 1

FROM information_schema.columns

WHERE table_name = 'notes' AND column_name = 'tags'

) THEN

ALTER TABLE notes ADD COLUMN tags TEXT[];

END IF;

END $$;