75 lines
2.8 KiB
SQL
75 lines
2.8 KiB
SQL
-- Create users table if not exists
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
avatar_url TEXT,
|
|
verified_domain VARCHAR(255),
|
|
online_status VARCHAR(50) DEFAULT 'offline',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create servers table
|
|
CREATE TABLE IF NOT EXISTS servers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
icon_url TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create channels table
|
|
CREATE TABLE IF NOT EXISTS channels (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
server_id UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
|
|
category VARCHAR(100) DEFAULT 'main',
|
|
type VARCHAR(50) DEFAULT 'text',
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create messages table
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
content TEXT NOT NULL,
|
|
channel_id UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
|
|
sender_id UUID NOT NULL REFERENCES users(id),
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create channel_members table
|
|
CREATE TABLE IF NOT EXISTS channel_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel_id UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(50) DEFAULT 'member',
|
|
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(channel_id, user_id)
|
|
);
|
|
|
|
-- Create server_members table
|
|
CREATE TABLE IF NOT EXISTS server_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
server_id UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(50) DEFAULT 'member',
|
|
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(server_id, user_id)
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX idx_messages_channel_id ON messages(channel_id);
|
|
CREATE INDEX idx_messages_sender_id ON messages(sender_id);
|
|
CREATE INDEX idx_messages_created_at ON messages(created_at DESC);
|
|
CREATE INDEX idx_channels_server_id ON channels(server_id);
|
|
CREATE INDEX idx_channel_members_channel_id ON channel_members(channel_id);
|
|
CREATE INDEX idx_channel_members_user_id ON channel_members(user_id);
|
|
CREATE INDEX idx_server_members_server_id ON server_members(server_id);
|
|
CREATE INDEX idx_server_members_user_id ON server_members(user_id);
|