128 lines
5.4 KiB
SQL
128 lines
5.4 KiB
SQL
-- Add social networking features to recipes database
|
|
|
|
-- Add visibility column to recipes table
|
|
ALTER TABLE recipes
|
|
ADD COLUMN IF NOT EXISTS visibility VARCHAR(20) DEFAULT 'public' CHECK (visibility IN ('public', 'private', 'friends', 'groups'));
|
|
|
|
-- Create friendships table
|
|
CREATE TABLE IF NOT EXISTS friendships (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
friend_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_id, friend_id),
|
|
CHECK (user_id != friend_id)
|
|
);
|
|
|
|
-- Create friend_requests table
|
|
CREATE TABLE IF NOT EXISTS friend_requests (
|
|
id SERIAL PRIMARY KEY,
|
|
sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
receiver_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'rejected')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(sender_id, receiver_id),
|
|
CHECK (sender_id != receiver_id)
|
|
);
|
|
|
|
-- Create conversations table
|
|
CREATE TABLE IF NOT EXISTS conversations (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100),
|
|
is_group BOOLEAN DEFAULT FALSE,
|
|
created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create conversation_members table
|
|
CREATE TABLE IF NOT EXISTS conversation_members (
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(conversation_id, user_id)
|
|
);
|
|
|
|
-- Create messages table
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
edited_at TIMESTAMP,
|
|
is_deleted BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
-- Create recipe_ratings table
|
|
CREATE TABLE IF NOT EXISTS recipe_ratings (
|
|
id SERIAL PRIMARY KEY,
|
|
recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(recipe_id, user_id)
|
|
);
|
|
|
|
-- Create recipe_comments table
|
|
CREATE TABLE IF NOT EXISTS recipe_comments (
|
|
id SERIAL PRIMARY KEY,
|
|
recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
parent_comment_id INTEGER REFERENCES recipe_comments(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
is_deleted BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
-- Create groups table
|
|
CREATE TABLE IF NOT EXISTS groups (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
created_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
is_private BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create group_members table
|
|
CREATE TABLE IF NOT EXISTS group_members (
|
|
id SERIAL PRIMARY KEY,
|
|
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(20) DEFAULT 'member' CHECK (role IN ('admin', 'moderator', 'member')),
|
|
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(group_id, user_id)
|
|
);
|
|
|
|
-- Create recipe_shares table (for sharing recipes to specific groups)
|
|
CREATE TABLE IF NOT EXISTS recipe_shares (
|
|
id SERIAL PRIMARY KEY,
|
|
recipe_id INTEGER NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
|
|
group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
|
|
shared_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
shared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(recipe_id, group_id)
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_friendships_user_id ON friendships(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_friendships_friend_id ON friendships(friend_id);
|
|
CREATE INDEX IF NOT EXISTS idx_friend_requests_receiver ON friend_requests(receiver_id);
|
|
CREATE INDEX IF NOT EXISTS idx_friend_requests_sender ON friend_requests(sender_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_conversation_members_user ON conversation_members(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_recipe_ratings_recipe ON recipe_ratings(recipe_id);
|
|
CREATE INDEX IF NOT EXISTS idx_recipe_comments_recipe ON recipe_comments(recipe_id);
|
|
CREATE INDEX IF NOT EXISTS idx_group_members_user ON group_members(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_group_members_group ON group_members(group_id);
|
|
CREATE INDEX IF NOT EXISTS idx_recipe_shares_group ON recipe_shares(group_id);
|
|
CREATE INDEX IF NOT EXISTS idx_recipes_visibility ON recipes(visibility);
|