HyperFocus Dev

-- Enable the uuid-ossp extension for generating UUIDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users Table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    supabase_auth_id UUID UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE, -- Link to Supabase auth.users table
    name TEXT,
    avatar_url TEXT,
    stripe_customer_id TEXT,
    subscription_status TEXT DEFAULT 'free' NOT NULL, -- 'free', 'pro', 'canceled'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE users IS 'Stores user profiles and subscription details.';
COMMENT ON COLUMN users.supabase_auth_id IS 'Foreign key to the auth.users table in Supabase.';
COMMENT ON COLUMN users.subscription_status IS 'Current subscription tier of the user.';

-- Focus Sessions Table
CREATE TABLE focus_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE, -- Null if session is ongoing
    duration_minutes INTEGER, -- Calculated on end_time
    blocked_urls_during_session TEXT[], -- Array of URLs blocked during this session
    interruption_count INTEGER DEFAULT 0, -- How many times user tried to access blocked sites
    productivity_score REAL, -- A score based on duration, interruptions, and blocked_urls (calculated)
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_focus_sessions_user_id ON focus_sessions(user_id);
CREATE INDEX idx_focus_sessions_start_time ON focus_sessions(start_time);
COMMENT ON TABLE focus_sessions IS 'Records individual focus work sessions for each user.';
COMMENT ON COLUMN focus_sessions.blocked_urls_during_session IS 'List of specific URLs that were blocked during this session.';
COMMENT ON COLUMN focus_sessions.productivity_score IS 'A calculated score indicating session effectiveness.';


-- Blocked Sites Configuration Table
CREATE TABLE blocked_sites_config (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    url_pattern TEXT NOT NULL, -- e.g., 'youtube.com', 'reddit.com/*', 'facebook.com'
    label TEXT, -- e.g., 'Social Media', 'Distracting News'
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    block_duration_minutes INTEGER, -- Optional: block for a specific duration (e.g., 60 minutes)
    block_mode TEXT DEFAULT 'strict' NOT NULL, -- 'strict' (always block), 'contextual' (block based on context - future feature)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_blocked_sites_config_user_id ON blocked_sites_config(user_id);
COMMENT ON TABLE blocked_sites_config IS 'Stores user-defined rules for blocking distracting websites.';
COMMENT ON COLUMN blocked_sites_config.url_pattern IS 'The URL pattern to be blocked (e.g., domain, subdomain, path).';
COMMENT ON COLUMN blocked_sites_config.block_mode IS 'Defines how the site should be blocked (e.g., always, or based on specific context).';


-- AI Suggestions Table
CREATE TABLE ai_suggestions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    session_id UUID REFERENCES focus_sessions(id) ON DELETE CASCADE, -- Optional: link to a specific session
    suggestion_text TEXT NOT NULL,
    suggestion_type TEXT, -- e.g., 'break_reminder', 'focus_technique', 'environment_tip'
    is_read BOOLEAN DEFAULT FALSE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_ai_suggestions_user_id ON ai_suggestions(user_id);
COMMENT ON TABLE ai_suggestions IS 'Stores AI-generated personalized suggestions for improving focus.';