database-schema.sql

-- ADHD Tracking Database Schema for Supabase
-- This schema creates tables for storing WebGazer.js eye tracking data and ADHD metrics

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Eye tracking data table
CREATE TABLE IF NOT EXISTS eye_tracking_data (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id TEXT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    x DECIMAL(10,4) NOT NULL,
    y DECIMAL(10,4) NOT NULL,
    confidence DECIMAL(3,2) NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    session_id TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ADHD metrics table
CREATE TABLE IF NOT EXISTS adhd_metrics (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id TEXT NOT NULL,
    session_id TEXT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Attention metrics
    attention_score INTEGER NOT NULL CHECK (attention_score >= 0 AND attention_score <= 100),
    focus_duration DECIMAL(10,2) DEFAULT 0,
    distraction_count INTEGER DEFAULT 0,
    gaze_variance DECIMAL(10,4) DEFAULT 0,
    
    -- Hyperactivity metrics
    head_movement_frequency DECIMAL(10,2) DEFAULT 0,
    blink_rate DECIMAL(10,2) DEFAULT 0,
    fixation_duration_avg DECIMAL(10,2) DEFAULT 0,
    
    -- Behavioral metrics
    task_completion_time DECIMAL(10,2),
    error_count INTEGER,
    task_type TEXT,
    
    -- Environmental context
    location TEXT,
    time_of_day TEXT NOT NULL,
    lighting_conditions TEXT,
    
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tracking sessions table
CREATE TABLE IF NOT EXISTS tracking_sessions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id TEXT NOT NULL,
    start_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    end_time TIMESTAMPTZ,
    session_type TEXT NOT NULL CHECK (session_type IN ('attention_test', 'behavioral_assessment', 'continuous_monitoring')),
    task_description TEXT,
    total_duration DECIMAL(10,2),
    avg_attention_score DECIMAL(5,2),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_eye_tracking_user_id ON eye_tracking_data(user_id);
CREATE INDEX IF NOT EXISTS idx_eye_tracking_session_id ON eye_tracking_data(session_id);
CREATE INDEX IF NOT EXISTS idx_eye_tracking_timestamp ON eye_tracking_data(timestamp);

CREATE INDEX IF NOT EXISTS idx_adhd_metrics_user_id ON adhd_metrics(user_id);
CREATE INDEX IF NOT EXISTS idx_adhd_metrics_session_id ON adhd_metrics(session_id);
CREATE INDEX IF NOT EXISTS idx_adhd_metrics_timestamp ON adhd_metrics(timestamp);

CREATE INDEX IF NOT EXISTS idx_tracking_sessions_user_id ON tracking_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_tracking_sessions_start_time ON tracking_sessions(start_time);
CREATE INDEX IF NOT EXISTS idx_tracking_sessions_session_type ON tracking_sessions(session_type);

-- Create RLS (Row Level Security) policies for data privacy
ALTER TABLE eye_tracking_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE adhd_metrics ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracking_sessions ENABLE ROW LEVEL SECURITY;

-- Policy for eye_tracking_data (users can only access their own data)
CREATE POLICY "Users can view own eye tracking data" ON eye_tracking_data
    FOR SELECT USING (auth.uid()::text = user_id);

CREATE POLICY "Users can insert own eye tracking data" ON eye_tracking_data
    FOR INSERT WITH CHECK (auth.uid()::text = user_id);

-- Policy for adhd_metrics (users can only access their own data)
CREATE POLICY "Users can view own adhd metrics" ON adhd_metrics
    FOR SELECT USING (auth.uid()::text = user_id);

CREATE POLICY "Users can insert own adhd metrics" ON adhd_metrics
    FOR INSERT WITH CHECK (auth.uid()::text = user_id);

-- Policy for tracking_sessions (users can only access their own data)
CREATE POLICY "Users can view own tracking sessions" ON tracking_sessions
    FOR SELECT USING (auth.uid()::text = user_id);

CREATE POLICY "Users can insert own tracking sessions" ON tracking_sessions
    FOR INSERT WITH CHECK (auth.uid()::text = user_id);

CREATE POLICY "Users can update own tracking sessions" ON tracking_sessions
    FOR UPDATE USING (auth.uid()::text = user_id);

-- Create a view for aggregated session data
CREATE OR REPLACE VIEW session_summary AS
SELECT 
    ts.id,
    ts.user_id,
    ts.start_time,
    ts.end_time,
    ts.session_type,
    ts.task_description,
    ts.total_duration,
    ts.avg_attention_score,
    ts.notes,
    COUNT(etd.id) as eye_tracking_points,
    COUNT(am.id) as adhd_metrics_count,
    AVG(am.attention_score) as calculated_avg_attention,
    AVG(am.gaze_variance) as avg_gaze_variance,
    MAX(am.distraction_count) as max_distractions
FROM tracking_sessions ts
LEFT JOIN eye_tracking_data etd ON ts.id::text = etd.session_id
LEFT JOIN adhd_metrics am ON ts.id::text = am.session_id
GROUP BY ts.id, ts.user_id, ts.start_time, ts.end_time, ts.session_type, 
         ts.task_description, ts.total_duration, ts.avg_attention_score, ts.notes;

-- Grant permissions on the view
GRANT SELECT ON session_summary TO authenticated;

-- Create a function to clean up old data (optional)
CREATE OR REPLACE FUNCTION cleanup_old_tracking_data(days_to_keep INTEGER DEFAULT 90)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Delete old eye tracking data
    DELETE FROM eye_tracking_data 
    WHERE created_at < NOW() - INTERVAL '1 day' * days_to_keep;
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    
    -- Delete old ADHD metrics
    DELETE FROM adhd_metrics 
    WHERE created_at < NOW() - INTERVAL '1 day' * days_to_keep;
    
    -- Delete old sessions (only if no related data exists)
    DELETE FROM tracking_sessions 
    WHERE created_at < NOW() - INTERVAL '1 day' * days_to_keep
    AND id NOT IN (
        SELECT DISTINCT session_id FROM eye_tracking_data 
        UNION 
        SELECT DISTINCT session_id FROM adhd_metrics
    );
    
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create a trigger to automatically update session end time and duration
CREATE OR REPLACE FUNCTION update_session_on_end()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.end_time IS NOT NULL AND OLD.end_time IS NULL THEN
        NEW.total_duration := EXTRACT(EPOCH FROM (NEW.end_time - NEW.start_time));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_session_on_end
    BEFORE UPDATE ON tracking_sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_session_on_end();

-- Sample data insertion (for testing - remove in production)
-- INSERT INTO tracking_sessions (user_id, session_type, task_description) 
-- VALUES ('current_user', 'attention_test', 'WebGazer ADHD Assessment');

COMMENT ON TABLE eye_tracking_data IS 'Stores individual eye tracking data points from WebGazer.js';
COMMENT ON TABLE adhd_metrics IS 'Stores calculated ADHD-related metrics and attention scores';
COMMENT ON TABLE tracking_sessions IS 'Stores metadata about tracking sessions';
COMMENT ON VIEW session_summary IS 'Aggregated view of session data with related metrics';