database/schema.sql

-- Portfolio Intelligence Platform Database Schema
-- For Supabase PostgreSQL with Auth Integration

-- Enable UUID extension (if not already enabled)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Helper function to convert strings to UUIDs (for demo portfolio IDs)
CREATE OR REPLACE FUNCTION string_to_portfolio_uuid(input_string TEXT)
RETURNS UUID
LANGUAGE plpgsql
IMMUTABLE
SECURITY INVOKER
SET search_path = ''
AS $$
BEGIN
    -- Use URL namespace UUID constant (6ba7b811-9dad-11d1-80b4-00c04fd430c8)
    -- uuid_ns_url() is a constant in PostgreSQL, not a function
    RETURN public.uuid_generate_v5('6ba7b811-9dad-11d1-80b4-00c04fd430c8'::uuid, input_string);
END;
$$;

-- Grant execute to authenticated and anon roles
GRANT EXECUTE ON FUNCTION string_to_portfolio_uuid TO authenticated, anon;

-- Users table (linked to Supabase Auth - CRITICAL FIX)
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    is_demo 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_users_id ON users(id);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Portfolios table
CREATE TABLE IF NOT EXISTS portfolios (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    risk_tolerance VARCHAR(20) DEFAULT 'moderate',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_portfolios_user_id ON portfolios(user_id);
CREATE INDEX IF NOT EXISTS idx_portfolios_created_at ON portfolios(created_at DESC);

-- Portfolio inputs table (for auto-save history - keeps last 3 per user)
CREATE TABLE IF NOT EXISTS portfolio_inputs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    description TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_portfolio_inputs_user_created ON portfolio_inputs(user_id, created_at DESC);

-- Trigger function to limit portfolio inputs to 3 per user
CREATE OR REPLACE FUNCTION limit_portfolio_inputs_per_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = ''
AS $$
BEGIN
    -- Keep only the 3 most recent entries per user
    DELETE FROM public.portfolio_inputs
    WHERE id IN (
        SELECT id
        FROM (
            SELECT id,
                   ROW_NUMBER() OVER (
                       PARTITION BY user_id
                       ORDER BY created_at DESC
                   ) AS row_num
            FROM public.portfolio_inputs
            WHERE user_id = NEW.user_id
        ) ranked
        WHERE row_num > 3
    );

    RETURN NULL;
END;
$$;

-- Create trigger for automatic cleanup
DROP TRIGGER IF EXISTS trigger_limit_portfolio_inputs ON portfolio_inputs;
CREATE TRIGGER trigger_limit_portfolio_inputs
    AFTER INSERT ON portfolio_inputs
    FOR EACH ROW EXECUTE FUNCTION limit_portfolio_inputs_per_user();

-- Portfolio holdings table
CREATE TABLE IF NOT EXISTS portfolio_holdings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    portfolio_id UUID NOT NULL REFERENCES portfolios(id) ON DELETE CASCADE,
    ticker VARCHAR(20) NOT NULL,
    quantity NUMERIC(20, 8) NOT NULL,
    cost_basis NUMERIC(20, 2),
    asset_type VARCHAR(20) DEFAULT 'stock',
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_holdings_portfolio_id ON portfolio_holdings(portfolio_id);
CREATE INDEX IF NOT EXISTS idx_holdings_ticker ON portfolio_holdings(ticker);

-- Portfolio analyses table
CREATE TABLE IF NOT EXISTS portfolio_analyses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    portfolio_id UUID NOT NULL REFERENCES portfolios(id) ON DELETE CASCADE,
    analysis_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Analysis results (JSONB for flexibility)
    holdings_snapshot JSONB NOT NULL,
    market_data JSONB,
    risk_metrics JSONB,
    optimisation_results JSONB,
    ai_synthesis TEXT,
    recommendations JSONB,

    -- Metadata
    reasoning_steps JSONB,
    mcp_calls JSONB,
    sentiment_data JSONB,
    execution_time_ms INTEGER,
    model_version VARCHAR(50),

    -- Computed fields
    total_value NUMERIC(20, 2),
    health_score INTEGER CHECK (health_score >= 0 AND health_score <= 10),
    risk_level VARCHAR(20),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_analyses_portfolio_id ON portfolio_analyses(portfolio_id);
CREATE INDEX IF NOT EXISTS idx_analyses_date ON portfolio_analyses(analysis_date DESC);
CREATE INDEX IF NOT EXISTS idx_analyses_health_score ON portfolio_analyses(health_score);
CREATE INDEX IF NOT EXISTS idx_analyses_sentiment_data ON portfolio_analyses USING GIN (sentiment_data);

-- Function to automatically create user profile on signup (CRITICAL FOR AUTH)
-- Uses SECURITY DEFINER with empty search_path to bypass RLS on INSERT
-- Fully qualifies table name (public.users) to work with empty search_path
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = ''
AS $$
BEGIN
    INSERT INTO public.users (id, email, username, is_active, is_demo)
    VALUES (
        NEW.id,
        NEW.email,
        COALESCE(
            NEW.raw_user_meta_data ->> 'username',
            split_part(NEW.email, '@', 1)
        ),
        true,
        false
    );
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        -- Log error but don't block signup
        RAISE WARNING 'Failed to create user profile: %', SQLERRM;
        RETURN NEW;
END;
$$;

-- Create trigger for automatic profile creation
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = ''
AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

-- Triggers for updated_at
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_portfolios_updated_at ON portfolios;
CREATE TRIGGER update_portfolios_updated_at BEFORE UPDATE ON portfolios
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Grant permissions (Supabase uses RLS - Row Level Security)
-- These are basic permissions; adjust based on your auth setup
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE portfolios ENABLE ROW LEVEL SECURITY;
ALTER TABLE portfolio_inputs ENABLE ROW LEVEL SECURITY;
ALTER TABLE portfolio_holdings ENABLE ROW LEVEL SECURITY;
ALTER TABLE portfolio_analyses ENABLE ROW LEVEL SECURITY;

-- RLS policies with performance optimizations (wrapped auth.uid() = 20x faster)
-- Users table policies
DROP POLICY IF EXISTS users_read_own ON users;
CREATE POLICY users_read_own ON users
    FOR SELECT
    TO authenticated
    USING ((SELECT auth.uid()) = id);

DROP POLICY IF EXISTS users_update_own ON users;
CREATE POLICY users_update_own ON users
    FOR UPDATE
    TO authenticated
    USING ((SELECT auth.uid()) = id)
    WITH CHECK ((SELECT auth.uid()) = id);

DROP POLICY IF EXISTS users_insert_own ON users;
CREATE POLICY users_insert_own ON users
    FOR INSERT
    TO authenticated
    WITH CHECK ((SELECT auth.uid()) = id);

-- Portfolios table policies
DROP POLICY IF EXISTS portfolios_read_own ON portfolios;
CREATE POLICY portfolios_read_own ON portfolios
    FOR SELECT
    TO authenticated
    USING ((SELECT auth.uid()) = user_id);

DROP POLICY IF EXISTS portfolios_insert_own ON portfolios;
CREATE POLICY portfolios_insert_own ON portfolios
    FOR INSERT
    TO authenticated
    WITH CHECK ((SELECT auth.uid()) = user_id);

DROP POLICY IF EXISTS portfolios_update_own ON portfolios;
CREATE POLICY portfolios_update_own ON portfolios
    FOR UPDATE
    TO authenticated
    USING ((SELECT auth.uid()) = user_id)
    WITH CHECK ((SELECT auth.uid()) = user_id);

DROP POLICY IF EXISTS portfolios_delete_own ON portfolios;
CREATE POLICY portfolios_delete_own ON portfolios
    FOR DELETE
    TO authenticated
    USING ((SELECT auth.uid()) = user_id);

-- Portfolio inputs table policies
DROP POLICY IF EXISTS portfolio_inputs_read_own ON portfolio_inputs;
CREATE POLICY portfolio_inputs_read_own ON portfolio_inputs
    FOR SELECT
    TO authenticated
    USING ((SELECT auth.uid()) = user_id);

DROP POLICY IF EXISTS portfolio_inputs_insert_own ON portfolio_inputs;
CREATE POLICY portfolio_inputs_insert_own ON portfolio_inputs
    FOR INSERT
    TO authenticated
    WITH CHECK ((SELECT auth.uid()) = user_id);

DROP POLICY IF EXISTS portfolio_inputs_delete_own ON portfolio_inputs;
CREATE POLICY portfolio_inputs_delete_own ON portfolio_inputs
    FOR DELETE
    TO authenticated
    USING ((SELECT auth.uid()) = user_id);

-- Holdings policies (optimized subquery pattern)
DROP POLICY IF EXISTS holdings_access_own ON portfolio_holdings;
CREATE POLICY holdings_access_own ON portfolio_holdings
    FOR ALL
    TO authenticated
    USING (
        portfolio_id IN (
            SELECT id FROM portfolios
            WHERE user_id = (SELECT auth.uid())
        )
    )
    WITH CHECK (
        portfolio_id IN (
            SELECT id FROM portfolios
            WHERE user_id = (SELECT auth.uid())
        )
    );

-- Analyses policies
DROP POLICY IF EXISTS analyses_access_own ON portfolio_analyses;
CREATE POLICY analyses_access_own ON portfolio_analyses
    FOR ALL
    TO authenticated
    USING (
        portfolio_id IN (
            SELECT id FROM portfolios
            WHERE user_id = (SELECT auth.uid())
        )
    )
    WITH CHECK (
        portfolio_id IN (
            SELECT id FROM portfolios
            WHERE user_id = (SELECT auth.uid())
        )
    );

-- Grant permissions
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL TABLES IN SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO anon, authenticated;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO anon, authenticated;