-- Additional Row Level Security Policies for enhanced security

-- Create policy for users to read their own analytics
CREATE POLICY "Users can read own analytics" ON analytics
    FOR SELECT USING (auth.uid() = user_id);

-- Create policy for users to insert their own analytics
CREATE POLICY "Users can insert own analytics" ON analytics
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Create policy for users to read their own favorites
CREATE POLICY "Users can read own favorites" ON favorites
    FOR SELECT USING (auth.uid() = user_id);

-- Create policy for users to insert their own favorites
CREATE POLICY "Users can insert own favorites" ON favorites
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Create policy for users to delete their own favorites
CREATE POLICY "Users can delete own favorites" ON favorites
    FOR DELETE USING (auth.uid() = user_id);

-- Create policy for users to update their own favorites
CREATE POLICY "Users can update own favorites" ON favorites
    FOR UPDATE USING (auth.uid() = user_id);

-- Create function to handle new user creation
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, username, email, avatar, join_date, last_active)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email, '@', 1)),
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'avatar', 'https://api.dicebear.com/7.x/avataaars/svg?seed=' || NEW.email),
    NOW(),
    NOW()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger for new user creation
CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Create function to update user last_active
CREATE OR REPLACE FUNCTION public.update_user_last_active()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.users
  SET last_active = NOW(), updated_at = NOW()
  WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger to update last_active on analytics insert
CREATE OR REPLACE TRIGGER on_analytics_created
  AFTER INSERT ON analytics
  FOR EACH ROW EXECUTE FUNCTION public.update_user_last_active();

-- Create function to get server statistics
CREATE OR REPLACE FUNCTION get_server_stats(server_uuid VARCHAR(255))
RETURNS TABLE(
    total_upvotes BIGINT,
    total_analytics INTEGER,
    avg_rating DECIMAL,
    total_favorites INTEGER,
    last_activity TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(SUM(a.upvotes), 0) as total_upvotes,
        COUNT(a.id)::INTEGER as total_analytics,
        s.rating as avg_rating,
        (SELECT COUNT(*)::INTEGER FROM favorites WHERE server_id = server_uuid) as total_favorites,
        MAX(a.created_at) as last_activity
    FROM servers s
    LEFT JOIN analytics a ON s.id = a.server_id
    WHERE s.id = server_uuid
    GROUP BY s.id, s.rating;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function to get top servers
CREATE OR REPLACE FUNCTION get_top_servers(limit_count INTEGER DEFAULT 10)
RETURNS TABLE(
    id VARCHAR(255),
    hostname TEXT,
    clients INTEGER,
    sv_maxclients INTEGER,
    country VARCHAR(100),
    rating DECIMAL,
    total_upvotes BIGINT,
    premium BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.id,
        s.hostname,
        s.clients,
        s.sv_maxclients,
        s.country,
        s.rating,
        COALESCE(SUM(a.upvotes), 0) as total_upvotes,
        s.premium
    FROM servers s
    LEFT JOIN analytics a ON s.id = a.server_id
    GROUP BY s.id, s.hostname, s.clients, s.sv_maxclients, s.country, s.rating, s.premium
    ORDER BY total_upvotes DESC, s.rating DESC, s.clients DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function to search servers with full-text search
CREATE OR REPLACE FUNCTION search_servers_advanced(
    search_query TEXT,
    limit_count INTEGER DEFAULT 20
)
RETURNS TABLE(
    id VARCHAR(255),
    hostname TEXT,
    clients INTEGER,
    sv_maxclients INTEGER,
    country VARCHAR(100),
    rating DECIMAL,
    tags TEXT[],
    premium BOOLEAN,
    rank REAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.id,
        s.hostname,
        s.clients,
        s.sv_maxclients,
        s.country,
        s.rating,
        s.tags,
        s.premium,
        ts_rank(
            to_tsvector('english', s.hostname || ' ' || s.description || ' ' || array_to_string(s.tags, ' ')),
            plainto_tsquery('english', search_query)
        ) as rank
    FROM servers s
    WHERE 
        to_tsvector('english', s.hostname || ' ' || s.description || ' ' || array_to_string(s.tags, ' ')) 
        @@ plainto_tsquery('english', search_query)
        OR s.hostname ILIKE '%' || search_query || '%'
        OR s.description ILIKE '%' || search_query || '%'
        OR EXISTS (
            SELECT 1 FROM unnest(s.tags) as tag 
            WHERE tag ILIKE '%' || search_query || '%'
        )
    ORDER BY rank DESC, s.rating DESC, s.clients DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create materialized view for analytics dashboard
CREATE MATERIALIZED VIEW IF NOT EXISTS analytics_dashboard AS
SELECT 
    DATE_TRUNC('hour', created_at) as hour,
    COUNT(*) as total_processes,
    SUM(upvotes) as total_upvotes,
    AVG(speed) as avg_speed,
    AVG(duration) as avg_duration,
    COUNT(DISTINCT user_id) as unique_users,
    COUNT(DISTINCT server_id) as unique_servers
FROM analytics
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour DESC;

-- Create index on materialized view
CREATE INDEX IF NOT EXISTS idx_analytics_dashboard_hour ON analytics_dashboard(hour);

-- Create function to refresh analytics dashboard
CREATE OR REPLACE FUNCTION refresh_analytics_dashboard()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY analytics_dashboard;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function to get user leaderboard
CREATE OR REPLACE FUNCTION get_user_leaderboard(limit_count INTEGER DEFAULT 10)
RETURNS TABLE(
    username VARCHAR(50),
    total_upvotes BIGINT,
    total_processes INTEGER,
    avg_speed DECIMAL,
    plan VARCHAR(20),
    country VARCHAR(100),
    verified BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.username,
        u.total_upvotes,
        COUNT(a.id)::INTEGER as total_processes,
        COALESCE(AVG(a.speed), 0)::DECIMAL as avg_speed,
        u.plan,
        u.country,
        u.verified
    FROM users u
    LEFT JOIN analytics a ON u.id = a.user_id
    GROUP BY u.id, u.username, u.total_upvotes, u.plan, u.country, u.verified
    ORDER BY u.total_upvotes DESC, total_processes DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
