Ampire

-- =====================================================================
-- ArshadProject - Media Sharing Social Platform
-- MySQL Schema
-- =====================================================================

CREATE DATABASE IF NOT EXISTS arshadproject_db
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE arshadproject_db;

-- ---------------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(50)  NOT NULL UNIQUE,
    email           VARCHAR(120) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(120),
    bio             VARCHAR(500),
    profile_pic_url VARCHAR(255),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Posts
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS posts (
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    caption      VARCHAR(1000),
    media_url    VARCHAR(255) NOT NULL,
    media_type   ENUM('IMAGE','VIDEO') NOT NULL,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_posts_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- ---------------------------------------------------------------------
-- Comments (top-level, attached directly to a post)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS comments (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    post_id    BIGINT NOT NULL,
    user_id    BIGINT NOT NULL,
    content    VARCHAR(1000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id) REFERENCES posts(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_comments_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_comments_post_id ON comments(post_id);

-- ---------------------------------------------------------------------
-- Replies (nested replies to a comment; self-referencing parent_reply_id
-- allows arbitrarily deep threading while staying a single table)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS replies (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    comment_id      BIGINT NOT NULL,
    parent_reply_id BIGINT DEFAULT NULL,
    user_id         BIGINT NOT NULL,
    content         VARCHAR(1000) NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_replies_comment
        FOREIGN KEY (comment_id) REFERENCES comments(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_replies_parent
        FOREIGN KEY (parent_reply_id) REFERENCES replies(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_replies_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_replies_comment_id ON replies(comment_id);
CREATE INDEX idx_replies_parent_id ON replies(parent_reply_id);

-- ---------------------------------------------------------------------
-- Sample query: fetch a post feed with author info and comment counts
-- (demonstrates the SQL JOIN work referenced in the assignment)
-- ---------------------------------------------------------------------
-- SELECT p.id, p.caption, p.media_url, p.media_type, p.created_at,
--        u.id AS user_id, u.username, u.profile_pic_url,
--        (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
-- FROM posts p
-- JOIN users u ON u.id = p.user_id
-- ORDER BY p.created_at DESC;

-- ---------------------------------------------------------------------
-- Sample query: comments + nested replies for one post (threaded)
-- ---------------------------------------------------------------------
-- SELECT c.id, c.content, c.created_at, u.username AS comment_author,
--        r.id AS reply_id, r.parent_reply_id, r.content AS reply_content,
--        ru.username AS reply_author
-- FROM comments c
-- JOIN users u ON u.id = c.user_id
-- LEFT JOIN replies r ON r.comment_id = c.id
-- LEFT JOIN users ru ON ru.id = r.user_id
-- WHERE c.post_id = ?
-- ORDER BY c.created_at ASC, r.created_at ASC;