💾 Lets Talk about Mongodb

-- Table 1: posts
CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    user_id INT,
    caption TEXT,
    created_at TIMESTAMP
);

-- Table 2: photos (many photos per post)
CREATE TABLE post_photos (
    photo_id INT PRIMARY KEY,
    post_id INT,
    photo_url VARCHAR(500),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

-- Table 3: hashtags
CREATE TABLE hashtags (
    hashtag_id INT PRIMARY KEY,
    tag_name VARCHAR(100)
);

-- Table 4: post_hashtags (many-to-many)
CREATE TABLE post_hashtags (
    post_id INT,
    hashtag_id INT,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (hashtag_id) REFERENCES hashtags(hashtag_id)
);

-- Table 5: likes
CREATE TABLE likes (
    like_id INT PRIMARY KEY,
    post_id INT,
    user_id INT,
    liked_at TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

-- Table 6: comments
CREATE TABLE comments (
    comment_id INT PRIMARY KEY,
    post_id INT,
    user_id INT,
    comment_text TEXT,
    created_at TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

-- To get ONE complete post? Complex JOIN:
SELECT p.*, 
       ph.photo_url,
       h.tag_name,
       l.user_id as liker,
       c.comment_text
FROM posts p
LEFT JOIN post_photos ph ON p.post_id = ph.post_id
LEFT JOIN post_hashtags pht ON p.post_id = pht.post_id
LEFT JOIN hashtags h ON pht.hashtag_id = h.hashtag_id
LEFT JOIN likes l ON p.post_id = l.post_id
LEFT JOIN comments c ON p.post_id = c.post_id
WHERE p.post_id = 12345;

{
  "_id": ObjectId("507f1f77bcf86cd799439011"),
  "user": {
    "userId": 12345,
    "username": "anuj_sharma",
    "profilePic": "https://cdn.example.com/anuj.jpg"
  },
  "photos": [
    {
      "url": "https://cdn.example.com/img1.jpg",
      "width": 1080,
      "height": 1080
    },
    {
      "url": "https://cdn.example.com/img2.jpg",
      "width": 1080,
      "height": 1080
    }
  ],
  "caption": "Learning MongoDB! Building myPathshala-MongoDB tutorial 🚀",
  "hashtags": ["mongodb", "coding", "database", "learning"],
  "location": {
    "city": "Panvel",
    "state": "Maharashtra",
    "country": "India",
    "coordinates": [73.1103, 18.9894]
  },
  "likes": [
    {
      "userId": 67890,
      "username": "priya_dev",
      "likedAt": ISODate("2024-12-03T08:30:00Z")
    },
    {
      "userId": 54321,
      "username": "raj_codes",
      "likedAt": ISODate("2024-12-03T09:15:00Z")
    }
    // ... 845 more likes
  ],
  "comments": [
    {
      "userId": 11111,
      "username": "sneha_tech",
      "text": "This looks amazing! Can't wait to learn",
      "createdAt": ISODate("2024-12-03T08:45:00Z"),
      "likes": 12
    },
    {
      "userId": 22222,
      "username": "arjun_data",
      "text": "Best MongoDB tutorial! 🔥",
      "createdAt": ISODate("2024-12-03T09:00:00Z"),
      "likes": 8
    }
  ],
  "stats": {
    "likeCount": 847,
    "commentCount": 23,
    "shareCount": 56,
    "viewCount": 12453
  },
  "createdAt": ISODate("2024-12-03T08:00:00Z"),
  "updatedAt": ISODate("2024-12-03T09:30:00Z"),
  "isPublic": true,
  "allowComments": true
}

// To get this complete post? ONE simple query:
db.posts.findOne({ _id: ObjectId("507f1f77bcf86cd799439011") })