Build a Social Media Scheduler

import { pool } from "../db";
import { Redis } from "ioredis";
import { randomBytes } from "node:crypto";
const redis = new Redis(process.env.REDIS_URL!);

interface ScheduledPost {
  id: string;
  content: PlatformContent[];
  mediaUrls: string[];
  scheduledAt: string;
  status: "draft" | "pending_approval" | "approved" | "scheduled" | "published" | "failed";
  approvedBy: string | null;
  publishedResults: Array<{ platform: string; postId: string; url: string }>;
  analytics: Record<string, { impressions: number; engagement: number; clicks: number }>;
  createdBy: string;
  createdAt: string;
}

interface PlatformContent {
  platform: "twitter" | "linkedin" | "instagram" | "facebook";
  text: string;
  hashtags: string[];
  mediaType: "image" | "video" | "carousel" | "none";
}

const OPTIMAL_TIMES: Record<string, { day: number; hour: number; reason: string }[]> = {
  twitter: [{ day: 3, hour: 13, reason: "Peak engagement Wed 1PM" }, { day: 2, hour: 10, reason: "Tuesday morning scroll" }],
  linkedin: [{ day: 2, hour: 9, reason: "Professional morning" }, { day: 3, hour: 12, reason: "Lunch break browsing" }],
  instagram: [{ day: 1, hour: 11, reason: "Monday mid-morning" }, { day: 4, hour: 14, reason: "Thursday afternoon" }],
  facebook: [{ day: 3, hour: 13, reason: "Mid-week afternoon" }, { day: 5, hour: 10, reason: "Friday morning" }],
};

const PLATFORM_LIMITS: Record<string, { maxChars: number; maxHashtags: number; mediaRequired: boolean }> = {
  twitter: { maxChars: 280, maxHashtags: 3, mediaRequired: false },
  linkedin: { maxChars: 3000, maxHashtags: 5, mediaRequired: false },
  instagram: { maxChars: 2200, maxHashtags: 30, mediaRequired: true },
  facebook: { maxChars: 63206, maxHashtags: 5, mediaRequired: false },
};

// Create scheduled post
export async function createPost(params: {
  content: PlatformContent[]; mediaUrls?: string[]; scheduledAt?: string; createdBy: string;
}): Promise<ScheduledPost> {
  const id = `post-${randomBytes(8).toString("hex")}`;

  // Validate per-platform limits
  for (const content of params.content) {
    const limits = PLATFORM_LIMITS[content.platform];
    if (content.text.length > limits.maxChars) throw new Error(`${content.platform}: text exceeds ${limits.maxChars} chars (got ${content.text.length})`);
    if (content.hashtags.length > limits.maxHashtags) throw new Error(`${content.platform}: max ${limits.maxHashtags} hashtags`);
    if (limits.mediaRequired && (!params.mediaUrls || params.mediaUrls.length === 0)) throw new Error(`${content.platform}: media required`);
  }

  const scheduledAt = params.scheduledAt || suggestOptimalTime(params.content[0]?.platform || "twitter");

  const post: ScheduledPost = {
    id, content: params.content, mediaUrls: params.mediaUrls || [],
    scheduledAt, status: "pending_approval", approvedBy: null,
    publishedResults: [], analytics: {},
    createdBy: params.createdBy, createdAt: new Date().toISOString(),
  };

  await pool.query(
    `INSERT INTO scheduled_posts (id, content, media_urls, scheduled_at, status, created_by, created_at)
     VALUES ($1, $2, $3, $4, 'pending_approval', $5, NOW())`,
    [id, JSON.stringify(params.content), JSON.stringify(post.mediaUrls), scheduledAt, params.createdBy]
  );

  // Notify approver
  await redis.rpush("notification:queue", JSON.stringify({ type: "post_pending_approval", postId: id, scheduledAt }));

  return post;
}

// Approve post
export async function approvePost(postId: string, approvedBy: string): Promise<void> {
  await pool.query("UPDATE scheduled_posts SET status = 'scheduled', approved_by = $2 WHERE id = $1 AND status = 'pending_approval'", [postId, approvedBy]);
  const { rows: [post] } = await pool.query("SELECT scheduled_at FROM scheduled_posts WHERE id = $1", [postId]);
  if (post) {
    const delay = Math.max(0, new Date(post.scheduled_at).getTime() - Date.now());
    await redis.setex(`post:publish:${postId}`, Math.ceil(delay / 1000) + 60, "pending");
  }
}

// Publish post to all platforms
export async function publishPost(postId: string): Promise<void> {
  const { rows: [post] } = await pool.query("SELECT * FROM scheduled_posts WHERE id = $1", [postId]);
  if (!post || post.status !== "scheduled") return;

  await pool.query("UPDATE scheduled_posts SET status = 'publishing' WHERE id = $1", [postId]);
  const content: PlatformContent[] = JSON.parse(post.content);
  const results: ScheduledPost["publishedResults"] = [];

  for (const platform of content) {
    try {
      // In production: call each platform's API
      const postUrl = await publishToPlatform(platform, JSON.parse(post.media_urls));
      results.push({ platform: platform.platform, postId: `${platform.platform}-${randomBytes(4).toString("hex")}`, url: postUrl });
    } catch (e: any) {
      results.push({ platform: platform.platform, postId: "", url: `error: ${e.message}` });
    }
  }

  const allSucceeded = results.every((r) => !r.url.startsWith("error"));
  await pool.query(
    "UPDATE scheduled_posts SET status = $2, published_results = $3 WHERE id = $1",
    [postId, allSucceeded ? "published" : "failed", JSON.stringify(results)]
  );
}

async function publishToPlatform(content: PlatformContent, mediaUrls: string[]): Promise<string> {
  // In production: call Twitter API, LinkedIn API, etc.
  return `https://${content.platform}.com/post/${randomBytes(6).toString("hex")}`;
}

function suggestOptimalTime(platform: string): string {
  const times = OPTIMAL_TIMES[platform] || [{ day: 2, hour: 10, reason: "Default" }];
  const now = new Date();
  for (const t of times) {
    const next = new Date(now);
    next.setDate(now.getDate() + ((t.day - now.getDay() + 7) % 7 || 7));
    next.setHours(t.hour, 0, 0, 0);
    if (next > now) return next.toISOString();
  }
  const fallback = new Date(now.getTime() + 86400000);
  fallback.setHours(10, 0, 0, 0);
  return fallback.toISOString();
}

// Content calendar view
export async function getCalendar(startDate: string, endDate: string): Promise<Record<string, ScheduledPost[]>> {
  const { rows } = await pool.query(
    "SELECT * FROM scheduled_posts WHERE scheduled_at BETWEEN $1 AND $2 ORDER BY scheduled_at",
    [startDate, endDate]
  );
  const calendar: Record<string, ScheduledPost[]> = {};
  for (const row of rows) {
    const day = new Date(row.scheduled_at).toISOString().slice(0, 10);
    if (!calendar[day]) calendar[day] = [];
    calendar[day].push({ ...row, content: JSON.parse(row.content), mediaUrls: JSON.parse(row.media_urls), publishedResults: JSON.parse(row.published_results || "[]"), analytics: {} });
  }
  return calendar;
}

// Analytics
export async function getPostAnalytics(postId: string): Promise<Record<string, { impressions: number; engagement: number; clicks: number }>> {
  // In production: fetch from each platform's analytics API
  return {};
}

// Process scheduled posts (cron)
export async function processScheduled(): Promise<number> {
  const { rows } = await pool.query(
    "SELECT id FROM scheduled_posts WHERE status = 'scheduled' AND scheduled_at <= NOW()"
  );
  for (const row of rows) await publishPost(row.id);
  return rows.length;
}