supabase-setup.sql

-- =====================================================
-- SUPABASE DATABASE SETUP FOR AI PORTFOLIO
-- =====================================================
-- Run this SQL in your Supabase SQL Editor
-- Dashboard -> SQL Editor -> New Query -> Paste and Run
-- =====================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =====================================================
-- TABLE 1: VISITOR SESSIONS
-- Tracks unique visitor sessions and basic analytics
-- =====================================================
CREATE TABLE IF NOT EXISTS visitor_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id TEXT UNIQUE NOT NULL,
    visitor_id TEXT NOT NULL,
    user_agent TEXT,
    screen_resolution TEXT,
    referrer TEXT,
    landing_page TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index for faster queries
CREATE INDEX IF NOT EXISTS idx_visitor_sessions_visitor_id ON visitor_sessions(visitor_id);
CREATE INDEX IF NOT EXISTS idx_visitor_sessions_created_at ON visitor_sessions(created_at);

-- Enable Row Level Security
ALTER TABLE visitor_sessions ENABLE ROW LEVEL SECURITY;

-- Policy: Allow anyone to insert (public portfolio)
CREATE POLICY "Allow public insert on visitor_sessions"
    ON visitor_sessions
    FOR INSERT
    TO anon
    WITH CHECK (true);

-- Policy: Allow read for authenticated users (you)
CREATE POLICY "Allow authenticated read on visitor_sessions"
    ON visitor_sessions
    FOR SELECT
    TO authenticated
    USING (true);

-- =====================================================
-- TABLE 2: PAGE VIEWS
-- Tracks individual page views
-- =====================================================
CREATE TABLE IF NOT EXISTS page_views (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id TEXT NOT NULL,
    visitor_id TEXT NOT NULL,
    page_url TEXT NOT NULL,
    page_title TEXT,
    viewed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_page_views_session_id ON page_views(session_id);
CREATE INDEX IF NOT EXISTS idx_page_views_visitor_id ON page_views(visitor_id);
CREATE INDEX IF NOT EXISTS idx_page_views_viewed_at ON page_views(viewed_at);

ALTER TABLE page_views ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow public insert on page_views"
    ON page_views
    FOR INSERT
    TO anon
    WITH CHECK (true);

CREATE POLICY "Allow authenticated read on page_views"
    ON page_views
    FOR SELECT
    TO authenticated
    USING (true);

-- =====================================================
-- TABLE 3: CHAT MESSAGES
-- Stores all chat conversations
-- =====================================================
CREATE TABLE IF NOT EXISTS chat_messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id TEXT NOT NULL,
    visitor_id TEXT NOT NULL,
    message TEXT NOT NULL,
    is_user BOOLEAN DEFAULT true,
    ai_response TEXT,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_chat_messages_session_id ON chat_messages(session_id);
CREATE INDEX IF NOT EXISTS idx_chat_messages_visitor_id ON chat_messages(visitor_id);
CREATE INDEX IF NOT EXISTS idx_chat_messages_timestamp ON chat_messages(timestamp);

ALTER TABLE chat_messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow public insert on chat_messages"
    ON chat_messages
    FOR INSERT
    TO anon
    WITH CHECK (true);

CREATE POLICY "Allow authenticated read on chat_messages"
    ON chat_messages
    FOR SELECT
    TO authenticated
    USING (true);

-- =====================================================
-- TABLE 4: CONTACT SUBMISSIONS
-- Stores contact form submissions
-- =====================================================
CREATE TABLE IF NOT EXISTS contact_submissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    visitor_id TEXT NOT NULL,
    session_id TEXT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    message TEXT NOT NULL,
    status TEXT DEFAULT 'new',
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    read_at TIMESTAMP WITH TIME ZONE,
    notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_contact_submissions_status ON contact_submissions(status);
CREATE INDEX IF NOT EXISTS idx_contact_submissions_submitted_at ON contact_submissions(submitted_at);
CREATE INDEX IF NOT EXISTS idx_contact_submissions_email ON contact_submissions(email);

ALTER TABLE contact_submissions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow public insert on contact_submissions"
    ON contact_submissions
    FOR INSERT
    TO anon
    WITH CHECK (true);

CREATE POLICY "Allow authenticated full access on contact_submissions"
    ON contact_submissions
    FOR ALL
    TO authenticated
    USING (true);

-- =====================================================
-- TABLE 5: USER INTERACTIONS
-- Tracks specific user interactions (clicks, actions)
-- =====================================================
CREATE TABLE IF NOT EXISTS user_interactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id TEXT NOT NULL,
    visitor_id TEXT NOT NULL,
    interaction_type TEXT NOT NULL,
    details JSONB,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_user_interactions_session_id ON user_interactions(session_id);
CREATE INDEX IF NOT EXISTS idx_user_interactions_visitor_id ON user_interactions(visitor_id);
CREATE INDEX IF NOT EXISTS idx_user_interactions_type ON user_interactions(interaction_type);
CREATE INDEX IF NOT EXISTS idx_user_interactions_timestamp ON user_interactions(timestamp);

ALTER TABLE user_interactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow public insert on user_interactions"
    ON user_interactions
    FOR INSERT
    TO anon
    WITH CHECK (true);

CREATE POLICY "Allow authenticated read on user_interactions"
    ON user_interactions
    FOR SELECT
    TO authenticated
    USING (true);

-- =====================================================
-- ANALYTICS VIEWS
-- Pre-built views for easy analytics
-- =====================================================

-- Daily visitors count
CREATE OR REPLACE VIEW daily_visitors AS
SELECT 
    DATE(created_at) as date,
    COUNT(DISTINCT visitor_id) as unique_visitors,
    COUNT(*) as total_sessions
FROM visitor_sessions
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Popular chat questions
CREATE OR REPLACE VIEW popular_questions AS
SELECT 
    message,
    COUNT(*) as frequency,
    MAX(timestamp) as last_asked
FROM chat_messages
WHERE is_user = true
GROUP BY message
ORDER BY frequency DESC
LIMIT 20;

-- Contact form summary
CREATE OR REPLACE VIEW contact_summary AS
SELECT 
    status,
    COUNT(*) as count,
    MAX(submitted_at) as latest_submission
FROM contact_submissions
GROUP BY status;

-- Most common interactions
CREATE OR REPLACE VIEW interaction_summary AS
SELECT 
    interaction_type,
    COUNT(*) as count,
    MAX(timestamp) as last_occurrence
FROM user_interactions
GROUP BY interaction_type
ORDER BY count DESC;

-- =====================================================
-- FUNCTIONS
-- =====================================================

-- Function to get chat statistics
CREATE OR REPLACE FUNCTION get_chat_statistics()
RETURNS TABLE (
    total_messages BIGINT,
    unique_chatters BIGINT,
    avg_messages_per_session NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*)::BIGINT as total_messages,
        COUNT(DISTINCT visitor_id)::BIGINT as unique_chatters,
        ROUND(COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT session_id), 0), 2) as avg_messages_per_session
    FROM chat_messages;
END;
$$ LANGUAGE plpgsql;

-- Function to clean old data (optional - run monthly)
CREATE OR REPLACE FUNCTION cleanup_old_data(days_to_keep INTEGER DEFAULT 90)
RETURNS TEXT AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM page_views WHERE viewed_at < NOW() - INTERVAL '1 day' * days_to_keep;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    
    RETURN 'Cleaned up ' || deleted_count || ' old records';
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- INITIAL DATA / SAMPLE QUERIES
-- =====================================================

-- Sample query: Get recent chat conversations
-- SELECT session_id, visitor_id, message, ai_response, timestamp 
-- FROM chat_messages 
-- ORDER BY timestamp DESC 
-- LIMIT 50;

-- Sample query: Get contact submissions
-- SELECT name, email, message, submitted_at, status 
-- FROM contact_submissions 
-- ORDER BY submitted_at DESC;

-- Sample query: Get visitor statistics
-- SELECT * FROM daily_visitors LIMIT 30;

-- Sample query: Get chat statistics
-- SELECT * FROM get_chat_statistics();

-- =====================================================
-- NOTES:
-- 1. After running this script, go to Authentication -> Policies to verify RLS is enabled
-- 2. Get your API keys from Settings -> API
-- 3. Use the anon/public key in your frontend (config.js)
-- 4. Never expose the service_role key in frontend code
-- 5. Consider setting up realtime subscriptions for live updates
-- =====================================================

-- Success message
DO $$ 
BEGIN 
    RAISE NOTICE '✅ Database setup completed successfully!';
    RAISE NOTICE '📊 Tables created: visitor_sessions, page_views, chat_messages, contact_submissions, user_interactions';
    RAISE NOTICE '📈 Views created: daily_visitors, popular_questions, contact_summary, interaction_summary';
    RAISE NOTICE '⚙️  Functions created: get_chat_statistics, cleanup_old_data';
    RAISE NOTICE '🔐 Row Level Security (RLS) enabled on all tables';
    RAISE NOTICE '➡️  Next step: Update config.js with your Supabase URL and anon key';
END $$;