social.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE user_profiles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    full_name VARCHAR(120),
    bio TEXT,
    avatar_url TEXT,
    cover_url TEXT,
    gender VARCHAR(20),
    birthday DATE
);

-- Followers / Following
CREATE TABLE user_follows (
    id BIGSERIAL PRIMARY KEY,
    follower_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    following_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (follower_id, following_id)
);

-- Posts
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    content TEXT,
    media_url TEXT, -- image or video link
    created_at TIMESTAMP DEFAULT NOW()
);

-- Likes on posts
CREATE TABLE post_likes (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (post_id, user_id)
);

-- Comments
CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Likes on comments
CREATE TABLE comment_likes (
    id BIGSERIAL PRIMARY KEY,
    comment_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (comment_id, user_id)
);

-- Notification system
CREATE TABLE notifications (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),             -- who receives notification
    from_user_id BIGINT REFERENCES users(id),       -- who triggered it
    type VARCHAR(50),                                -- like, comment, follow
    reference_id BIGINT,                             -- post_id or comment_id
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);


-- 🧩 Core Features Covered

-- Users

-- Followers / Following (social graph)

-- Posts (text, image, video, etc)

-- Comments

-- Likes / Reactions

-- Notifications

-- Messages (optional, you can add later)


-- | Feature                    | Status            |
-- | -------------------------- | ----------------- |
-- | Post Media (Image / Video) | ✅ via `media_url` |
-- | Followers System           | ✅ `user_follows`  |
-- | Likes on Posts             | ✅ `post_likes`    |
-- | Comments                   | ✅                 |
-- | Likes on Comments          | ✅                 |
-- | Notification System        | ✅                 |
-- | Extend to Chat / DM        | Easy              |