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

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    avatar TEXT,
    join_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    total_upvotes BIGINT DEFAULT 0,
    total_servers INTEGER DEFAULT 0,
    plan VARCHAR(20) DEFAULT 'free' CHECK (plan IN ('free', 'premium', 'enterprise')),
    favorite_servers TEXT[] DEFAULT '{}',
    last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    country VARCHAR(100) DEFAULT 'Unknown',
    verified BOOLEAN DEFAULT FALSE,
    api_key TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create servers table
CREATE TABLE IF NOT EXISTS servers (
    id VARCHAR(255) PRIMARY KEY,
    hostname TEXT NOT NULL,
    clients INTEGER DEFAULT 0,
    sv_maxclients INTEGER DEFAULT 32,
    mapname VARCHAR(255) DEFAULT 'Unknown',
    gametype VARCHAR(255) DEFAULT 'Unknown',
    upvote_power INTEGER DEFAULT 50,
    country VARCHAR(100) DEFAULT 'Unknown',
    language VARCHAR(10) DEFAULT 'en',
    tags TEXT[] DEFAULT '{}',
    resources TEXT[] DEFAULT '{}',
    variables JSONB DEFAULT '{}',
    connect_endpoints TEXT[] DEFAULT '{}',
    owner_profile JSONB,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    premium BOOLEAN DEFAULT FALSE,
    burst BOOLEAN DEFAULT FALSE,
    fallback BOOLEAN DEFAULT FALSE,
    private BOOLEAN DEFAULT FALSE,
    locale_country VARCHAR(100) DEFAULT 'Unknown',
    hostname_locale TEXT,
    icon TEXT,
    banner TEXT,
    description TEXT,
    website TEXT,
    discord TEXT,
    uptime INTEGER DEFAULT 0,
    avg_players INTEGER DEFAULT 0,
    max_players_record INTEGER DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 4.0,
    reviews INTEGER DEFAULT 0,
    categories TEXT[] DEFAULT '{}',
    mods TEXT[] DEFAULT '{}',
    scripts TEXT[] DEFAULT '{}',
    onesync BOOLEAN DEFAULT FALSE,
    enforce_game_build VARCHAR(50) DEFAULT 'latest',
    pure_level INTEGER DEFAULT 0,
    license_key_token TEXT,
    last_ping INTEGER,
    response_time INTEGER,
    server_load INTEGER,
    memory_usage INTEGER,
    cpu_usage INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create analytics table
CREATE TABLE IF NOT EXISTS analytics (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    server_id VARCHAR(255) REFERENCES servers(id) ON DELETE CASCADE,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    upvotes INTEGER DEFAULT 0,
    speed INTEGER DEFAULT 0,
    duration INTEGER DEFAULT 0,
    success BOOLEAN DEFAULT TRUE,
    type VARCHAR(20) DEFAULT 'upvote' CHECK (type IN ('upvote', 'powerboost')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create favorites table
CREATE TABLE IF NOT EXISTS favorites (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    server_id VARCHAR(255) NOT NULL,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    address TEXT NOT NULL,
    players INTEGER DEFAULT 0,
    max_players INTEGER DEFAULT 32,
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_checked TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'online' CHECK (status IN ('online', 'offline')),
    ping INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, server_id)
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_servers_hostname ON servers(hostname);
CREATE INDEX IF NOT EXISTS idx_servers_country ON servers(country);
CREATE INDEX IF NOT EXISTS idx_servers_premium ON servers(premium);
CREATE INDEX IF NOT EXISTS idx_analytics_user_id ON analytics(user_id);
CREATE INDEX IF NOT EXISTS idx_analytics_server_id ON analytics(server_id);
CREATE INDEX IF NOT EXISTS idx_analytics_timestamp ON analytics(timestamp);
CREATE INDEX IF NOT EXISTS idx_favorites_user_id ON favorites(user_id);
CREATE INDEX IF NOT EXISTS idx_favorites_server_id ON favorites(server_id);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updated_at
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_servers_updated_at BEFORE UPDATE ON servers
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Enable Row Level Security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE analytics ENABLE ROW LEVEL SECURITY;
ALTER TABLE favorites ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
CREATE POLICY "Users can view own profile" ON users
    FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON users
    FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Users can view own analytics" ON analytics
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own analytics" ON analytics
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can view own favorites" ON favorites
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can manage own favorites" ON favorites
    FOR ALL USING (auth.uid() = user_id);

-- Servers table is public for reading
CREATE POLICY "Anyone can view servers" ON servers
    FOR SELECT USING (true);

-- Create functions for common operations
CREATE OR REPLACE FUNCTION get_user_stats(user_uuid UUID)
RETURNS TABLE(
    total_upvotes BIGINT,
    total_servers INTEGER,
    total_analytics INTEGER,
    total_favorites INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        u.total_upvotes,
        u.total_servers,
        (SELECT COUNT(*)::INTEGER FROM analytics WHERE user_id = user_uuid),
        (SELECT COUNT(*)::INTEGER FROM favorites WHERE user_id = user_uuid)
    FROM users u
    WHERE u.id = user_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function to update server metrics
CREATE OR REPLACE FUNCTION update_server_metrics(
    server_uuid VARCHAR(255),
    new_clients INTEGER DEFAULT NULL,
    new_ping INTEGER DEFAULT NULL,
    new_load INTEGER DEFAULT NULL,
    new_memory INTEGER DEFAULT NULL,
    new_cpu INTEGER DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
    UPDATE servers 
    SET 
        clients = COALESCE(new_clients, clients),
        last_ping = COALESCE(new_ping, last_ping),
        server_load = COALESCE(new_load, server_load),
        memory_usage = COALESCE(new_memory, memory_usage),
        cpu_usage = COALESCE(new_cpu, cpu_usage),
        last_seen = NOW(),
        updated_at = NOW()
    WHERE id = server_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function to increment user upvotes
CREATE OR REPLACE FUNCTION increment_user_upvotes(
    user_uuid UUID,
    upvote_amount INTEGER
)
RETURNS VOID AS $$
BEGIN
    UPDATE users 
    SET 
        total_upvotes = total_upvotes + upvote_amount,
        last_active = NOW(),
        updated_at = NOW()
    WHERE id = user_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
