CREATE TABLE Users(
    UserID INT PRIMARY KEY AUTO_INCREMENT, 
    Username VARCHAR(200) NOT NULL, 
    First_Name VARCHAR(50) NOT NULL,
    Last_Name VARCHAR(50) NOT NULL, 
    Email VARCHAR(50), 
    Date_of_Birth VARCHAR(20), 
    Join_Date VARCHAR(20)
);


CREATE TABLE Posts(
    PostID INT PRIMARY KEY AUTO_INCREMENT, 
    UserID INT, 
    Post_Text VARCHAR(500), 
    Post_Date VARCHAR(20),
    FOREIGN KEY(UserID) REFERENCES Users(UserID)
);


CREATE TABLE Comments(
    CommentID INT PRIMARY KEY AUTO_INCREMENT,
    PostID INT,
    UserID INT,
    Comment_Text VARCHAR(500),
    Comment_Date VARCHAR(20),
    FOREIGN KEY(PostID) REFERENCES Posts(PostID),
    FOREIGN KEY(UserID) REFERENCES Users(UserID)
);


CREATE TABLE Likes(
    LikeID INT PRIMARY KEY AUTO_INCREMENT, 
    PostID INT, 
    UserID INT, 
    Like_Date VARCHAR(20),
    FOREIGN KEY(PostID) REFERENCES Posts(PostID),
    FOREIGN KEY(UserID) REFERENCES Users(UserID)
);


INSERT INTO Users (Username, First_Name, Last_Name, Email, Date_of_Birth, Join_Date)
VALUES 
('john_doe', 'John', 'Doe', 'john.doe@example.com', '1990-01-01', '2023-01-01'),
('jane_smith', 'Jane', 'Smith', 'jane.smith@example.com', '1992-05-10', '2023-02-10'),
('mark_jones', 'Mark', 'Jones', 'mark.jones@example.com', '1985-08-23', '2022-12-01');


INSERT INTO Posts (UserID, Post_Text, Post_Date)
VALUES 
(1, 'Hello, this is my first post!', '2023-01-01'),
(2, 'Had a great day today!', '2023-02-15'),
(1, 'Enjoying the weather. #SunnyDay', '2023-02-20'),
(3, 'Excited for the weekend!', '2022-12-05');


INSERT INTO Comments (PostID, UserID, Comment_Text, Comment_Date)
VALUES 
(1, 2, 'Welcome to the platform, John!', '2023-01-02'),
(2, 1, 'Glad you had a great day, Jane!', '2023-02-16'),
(3, 2, 'I agree, it’s beautiful today!', '2023-02-21'),
(4, 1, 'Weekend plans already? Time flies!', '2022-12-06');


INSERT INTO Likes (PostID, UserID, Like_Date)
VALUES 
(1, 2, '2023-01-02'),
(1, 3, '2023-01-03'),
(2, 3, '2023-02-16'),
(3, 1, '2023-02-21');




SELECT * From Users;
    
SELECT * FROM Posts WHERE UserID = 1;

SELECT * FROM Comments WHERE PostID = 1;

SELECT UserID, COUNT(*) AS TotalPosts
FROM Posts
GROUP BY UserID;