`app/api/social/my-posts/route.ts`

96 lines
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';

export const dynamic = 'force-dynamic';

const DEFAULT_DAYS = 90;
const MAX_DAYS = 365;

export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  const { searchParams } = new URL(request.url);
  const daysParam = parseInt(searchParams.get('days') ?? String(DEFAULT_DAYS), 10);
  const days = Number.isFinite(daysParam) && daysParam > 0 ? Math.min(daysParam, MAX_DAYS) : DEFAULT_DAYS;
  const platform = (searchParams.get('platform') ?? 'all').toLowerCase();
  const status = (searchParams.get('status') ?? 'all').toLowerCase();

  const conds: string[] = [
    `COALESCE(p.published_at, s.scheduled_at, p.created_at) >= (NOW() - ($1 || ' days')::interval)`,
  ];
  const vals: unknown[] = [String(days)];
  let idx = 2;

  if (platform !== 'all') {
    conds.push(`p.platform = $${idx++}`);
    vals.push(platform);
  }
  if (status !== 'all') {
    conds.push(`p.status = $${idx++}`);
    vals.push(status);
  }

  const sql = `
    SELECT
      p.id,
      p.platform,
      p.post_type,
      p.body,
      p.media_urls,
      p.link_url,
      p.hashtags,
      p.platform_url,
      p.status,
      p.published_at,
      p.created_at,
      s.scheduled_at,
      s.status AS schedule_status,
      COALESCE(p.likes, 0)    AS likes,
      COALESCE(p.comments, 0) AS comments,
      COALESCE(p.shares, 0)   AS shares,
      COALESCE(p.views, 0)    AS views,
      p.error_message,
      a.account_name,
      a.display_name,
      a.avatar_url
    FROM social_posts p
    LEFT JOIN social_accounts a ON a.id = p.account_id
    LEFT JOIN LATERAL (
      SELECT scheduled_at, status
      FROM social_schedule
      WHERE post_id = p.id
      ORDER BY scheduled_at DESC
      LIMIT 1
    ) s ON true
    WHERE ${conds.join(' AND ')}
    ORDER BY COALESCE(p.published_at, s.scheduled_at, p.created_at) DESC
    LIMIT 500
  `;

  const result = await query(sql, vals);

  const counts = await query(
    `SELECT
       COUNT(*) FILTER (WHERE p.status = 'published')::int AS published,
       COUNT(*) FILTER (WHERE p.status = 'scheduled' OR s.scheduled_at IS NOT NULL)::int AS scheduled,
       COUNT(*) FILTER (WHERE p.status = 'failed')::int AS failed,
       COUNT(*) FILTER (WHERE p.status = 'draft')::int AS draft,
       COUNT(*)::int AS total
     FROM social_posts p
     LEFT JOIN LATERAL (
       SELECT scheduled_at, status FROM social_schedule
       WHERE post_id = p.id ORDER BY scheduled_at DESC LIMIT 1
     ) s ON true
     WHERE COALESCE(p.published_at, s.scheduled_at, p.created_at) >= (NOW() - ($1 || ' days')::interval)`,
    [String(days)],
  );

  return NextResponse.json({
    posts: result.rows,
    counts: counts.rows[0] ?? { published: 0, scheduled: 0, failed: 0, draft: 0, total: 0 },
    range: { days, platform, status },
  });
}