-- Create tables with relationships
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

INSERT INTO posts (user_id, title, content, status) VALUES 
    (1, 'First Post', 'This is my first blog post.', 'published'),
    (1, 'Second Post', 'Another interesting post.', 'published'),
    (2, 'Draft Post', 'This is still a draft.', 'draft');

INSERT INTO comments (post_id, user_id, content) VALUES 
    (1, 2, 'Great first post!'),
    (1, 3, 'Thanks for sharing.'),
    (2, 3, 'Very informative.');

-- Complex queries with JOINs, aggregations, and CTEs
WITH user_stats AS (
    SELECT 
        u.id,
        u.username,
        COUNT(DISTINCT p.id) as post_count,
        COUNT(DISTINCT c.id) as comment_count,
        MAX(p.created_at) as last_post_date
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
    LEFT JOIN comments c ON u.id = c.user_id
    GROUP BY u.id, u.username
),
popular_posts AS (
    SELECT 
        p.id,
        p.title,
        u.username as author,
        COUNT(c.id) as comment_count,
        RANK() OVER (ORDER BY COUNT(c.id) DESC) as popularity_rank
    FROM posts p
    JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    WHERE p.status = 'published'
    GROUP BY p.id, p.title, u.username
)
SELECT 
    us.username,
    us.post_count,
    us.comment_count,
    us.last_post_date,
    COALESCE(pp.title, 'No posts') as most_popular_post,
    COALESCE(pp.comment_count, 0) as most_popular_post_comments
FROM user_stats us
LEFT JOIN popular_posts pp ON pp.popularity_rank = 1
ORDER BY us.post_count DESC, us.comment_count DESC;

-- Window functions example
SELECT 
    u.username,
    p.title,
    p.created_at,
    LAG(p.created_at) OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as previous_post_date,
    COUNT(*) OVER (
        PARTITION BY u.id
    ) as total_user_posts,
    ROW_NUMBER() OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as post_sequence
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY u.username, p.created_at;

Demonstrates advanced SQL features including JOINs, CTEs, window functions, and complex aggregations.