-- Create Users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- Create Posts table
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Create Comments table
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Create Likes table
CREATE TABLE likes (
    like_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Create Followers table
CREATE TABLE followers (
    follower_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    follows_user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (follows_user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Insert Users
INSERT INTO users (username, email) VALUES
('John', '[email protected]'),
('Anna', '[email protected]'),
('Mike', '[email protected]'),
('Emma', '[email protected]'),
('Sophia', '[email protected]'),
('Liam', '[email protected]'),
('Olivia', '[email protected]'),
('Noah', '[email protected]');

-- Insert Posts
INSERT INTO posts (user_id, content) VALUES
(1, 'This is John''s first post.'),
(2, 'Anna''s first post.'),
(3, 'Mike loves SQL.'),
(4, 'Emma''s first post.'),
(5, 'Sophia shares her thoughts.'),
(6, 'Liam joins the conversation.'),
(7, 'Olivia loves SQL!'),
(8, 'Noah''s insights on analytics.');

-- Insert Comments
INSERT INTO comments (post_id, user_id, content) VALUES
(1, 2, 'Anna commented on John''s first post.'),
(3, 4, 'Emma joins the discussion.'),
(4, 5, 'Sophia comments on Emma''s post.'),
(5, 6, 'Liam responds to Sophia''s thoughts.'),
(6, 7, 'Olivia shares her views on Liam''s post.'),
(7, 8, 'Noah comments on Olivia''s love for SQL.');

-- Insert Likes
INSERT INTO likes (post_id, user_id) VALUES
(1, 2),
(2, 3),
(3, 4),
(4, 5),
(5, 6),
(6, 7),
(7, 8),
(8, 1);

-- Insert Followers
INSERT INTO followers (user_id, follows_user_id) VALUES
(2, 1), -- Anna follows John
(3, 1), -- Mike follows John
(4, 5), -- Emma follows Sophia
(5, 6), -- Sophia follows Liam
(6, 7), -- Liam follows Olivia
(7, 8), -- Olivia follows Noah
(8, 4); -- Noah follows Emma

-- Fetch All Posts with Authors
SELECT 
    p.post_id, 
    p.content, 
    p.created_at, 
    u.username AS author
FROM posts p
JOIN users u ON p.user_id = u.user_id;

-- Fetch Engagement Metrics for Users
SELECT 
    u.username,
    COUNT(DISTINCT p.post_id) AS posts_count,
    COUNT(DISTINCT c.comment_id) AS comments_count,
    COUNT(DISTINCT l.like_id) AS likes_count,
    COUNT(DISTINCT f.follower_id) AS followers_count
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
LEFT JOIN comments c ON u.user_id = c.user_id
LEFT JOIN likes l ON u.user_id = l.user_id
LEFT JOIN followers f ON u.user_id = f.follows_user_id
GROUP BY u.username;

-- Fetch Top Active Users
SELECT 
    u.username,
    COUNT(p.post_id) AS total_posts,
    COUNT(c.comment_id) AS total_comments,
    COUNT(l.like_id) AS total_likes,
    COUNT(f.follower_id) AS total_followers,
    COUNT(f2.follows_user_id) AS total_following
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
LEFT JOIN comments c ON u.user_id = c.user_id
LEFT JOIN likes l ON u.user_id = l.user_id
LEFT JOIN followers f ON u.user_id = f.follows_user_id
LEFT JOIN followers f2 ON u.user_id = f2.user_id
GROUP BY u.username
ORDER BY total_posts DESC, total_comments DESC, total_likes DESC;

-- Fetch Follower and Following Relationships
SELECT 
    u1.username AS follower,
    u2.username AS following
FROM followers f
JOIN users u1 ON f.user_id = u1.user_id
JOIN users u2 ON f.follows_user_id = u2.user_id;

-- Create Function to Calculate Engagement Rate
CREATE OR REPLACE FUNCTION calculate_engagement_rate(user_id INT)
RETURNS NUMERIC AS $$
DECLARE
    posts_count INT;
    comments_count INT;
    likes_count INT;
BEGIN
    SELECT 
        COUNT(*) INTO posts_count
    FROM posts
    WHERE user_id = user_id;

    SELECT 
        COUNT(*) INTO comments_count
    FROM comments
    WHERE user_id = user_id;

    SELECT 
        COUNT(*) INTO likes_count
    FROM likes
    WHERE user_id = user_id;

    RETURN (comments_count + likes_count) * 100.0 / NULLIF(posts_count, 0);
END;
$$ LANGUAGE plpgsql;
DROP FUNCTION IF EXISTS calculate_engagement_rate(integer);

-- Example Usage of Engagement Rate
CREATE OR REPLACE FUNCTION calculate_engagement_rate(input_user_id INT)
RETURNS NUMERIC AS $$
DECLARE
    posts_count INT;
    comments_count INT;
    likes_count INT;
BEGIN
    SELECT 
        COUNT(*) INTO posts_count
    FROM posts
    WHERE user_id = input_user_id;

    SELECT 
        COUNT(*) INTO comments_count
    FROM comments
    WHERE user_id = input_user_id;

    SELECT 
        COUNT(*) INTO likes_count
    FROM likes
    WHERE user_id = input_user_id;

    RETURN (comments_count + likes_count) * 100.0 / NULLIF(posts_count, 0);
END;


$$ LANGUAGE plpgsql;

-- Step 1: Drop the function if it already exists to avoid conflict
DROP FUNCTION IF EXISTS log_new_like();

-- Step 2: Create the function
CREATE OR REPLACE FUNCTION log_new_like()
RETURNS TRIGGER AS $$
BEGIN
    -- Log information about the new like
    RAISE NOTICE 'New like added: Post ID = %, User ID = %', NEW.post_id, NEW.user_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 3: Drop the trigger if it already exists to avoid conflict
DROP TRIGGER IF EXISTS after_like_insert ON likes;

-- Step 4: Create the trigger
CREATE TRIGGER after_like_insert
AFTER INSERT ON likes
FOR EACH ROW
EXECUTE FUNCTION log_new_like();

-- Step 5: Test the setup by inserting a new like
INSERT INTO likes (post_id, user_id) VALUES (2, 3);


-- Create Comprehensive View for User Activity
CREATE OR REPLACE VIEW user_activity_summary AS
SELECT
    u.username,
    COUNT(DISTINCT p.post_id) AS total_posts,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    COUNT(DISTINCT l.like_id) AS total_likes,
    COUNT(DISTINCT f.follower_id) AS total_followers,
    COUNT(DISTINCT f2.user_id) AS total_following
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
LEFT JOIN comments c ON u.user_id = c.user_id
LEFT JOIN likes l ON u.user_id = l.user_id
LEFT JOIN followers f ON u.user_id = f.follows_user_id
LEFT JOIN followers f2 ON u.user_id = f2.user_id
GROUP BY u.username;