PostgreSQL

-- Create users table with authentication
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  full_name TEXT,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Create posts table with user relationship
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Create comments table
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for performance
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Add triggers
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Get posts with comment counts and author info
WITH post_stats AS (
  SELECT 
    p.id AS post_id,
    COUNT(c.id) AS comment_count,
    json_agg(json_build_object(
      'id', c.id,
      'content', c.content,
      'user', (SELECT json_build_object(
        'id', u2.id,
        'name', u2.full_name
      ) FROM users u2 WHERE u2.id = c.user_id)
    )) FILTER (WHERE c.id IS NOT NULL) AS recent_comments
  FROM posts p
  LEFT JOIN comments c ON c.post_id = p.id
  GROUP BY p.id
)
SELECT 
  p.id,
  p.title,
  p.content,
  p.created_at,
  json_build_object(
    'id', u.id,
    'name', u.full_name,
    'email', u.email
  ) AS author,
  ps.comment_count,
  ps.recent_comments,
  ROW_NUMBER() OVER (
    PARTITION BY DATE_TRUNC('month', p.created_at)
    ORDER BY ps.comment_count DESC
  ) AS monthly_rank
FROM posts p
JOIN users u ON u.id = p.user_id
JOIN post_stats ps ON ps.post_id = p.id
WHERE p.published = true
ORDER BY p.created_at DESC;

-- Search posts with full text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX posts_title_trgm_idx ON posts USING GIN (title gin_trgm_ops);
CREATE INDEX posts_content_trgm_idx ON posts USING GIN (content gin_trgm_ops);

SELECT 
  p.id,
  p.title,
  p.content,
  similarity(p.title, 'search term') AS title_similarity,
  similarity(p.content, 'search term') AS content_similarity
FROM posts p
WHERE 
  p.title % 'search term' OR 
  p.content % 'search term'
ORDER BY 
  GREATEST(
    similarity(p.title, 'search term'),
    similarity(p.content, 'search term')
  ) DESC;

-- Function to get user statistics
CREATE OR REPLACE FUNCTION get_user_statistics(user_id UUID)
RETURNS TABLE (
  total_posts BIGINT,
  total_comments BIGINT,
  avg_comments_per_post NUMERIC,
  most_active_month DATE
) AS $$
BEGIN
  RETURN QUERY
  WITH user_activity AS (
    SELECT
      COUNT(DISTINCT p.id) AS post_count,
      COUNT(DISTINCT c.id) AS comment_count,
      CASE 
        WHEN COUNT(DISTINCT p.id) = 0 THEN 0
        ELSE ROUND(COUNT(DISTINCT c.id)::NUMERIC / COUNT(DISTINCT p.id), 2)
      END AS avg_comments,
      DATE_TRUNC('month', COALESCE(p.created_at, c.created_at)) AS activity_month,
      COUNT(*) AS activity_count
    FROM users u
    LEFT JOIN posts p ON p.user_id = u.id
    LEFT JOIN comments c ON c.user_id = u.id
    WHERE u.id = user_id
    GROUP BY activity_month
  )
  SELECT
    SUM(post_count)::BIGINT,
    SUM(comment_count)::BIGINT,
    MAX(avg_comments),
    activity_month::DATE
  FROM user_activity
  WHERE activity_count = (
    SELECT MAX(activity_count)
    FROM user_activity
  )
  GROUP BY activity_month;
END;
$$ LANGUAGE plpgsql;

-- Procedure to publish/unpublish post with notifications
CREATE OR REPLACE PROCEDURE toggle_post_status(
  post_id UUID,
  should_publish BOOLEAN,
  notify_followers BOOLEAN DEFAULT true
)
LANGUAGE plpgsql
AS $$
DECLARE
  post_author_id UUID;
  follower_ids UUID[];
BEGIN
  -- Update post status
  UPDATE posts
  SET 
    published = should_publish,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = post_id
  RETURNING user_id INTO post_author_id;

  -- Create notification if requested
  IF notify_followers AND should_publish THEN
    -- Get followers (assuming a followers table exists)
    SELECT ARRAY_AGG(follower_id)
    INTO follower_ids
    FROM user_followers
    WHERE followed_id = post_author_id;

    -- Insert notifications
    INSERT INTO notifications (
      user_id,
      type,
      content,
      reference_id
    )
    SELECT 
      unnest(follower_ids),
      'new_post',
      format('New post from %s', (
        SELECT full_name 
        FROM users 
        WHERE id = post_author_id
      )),
      post_id;
  END IF;

  COMMIT;
END;
$$;