← back to Norma
app/api/social/bulk-schedule/route.ts
285 lines
import { NextRequest, NextResponse } from 'next/server';
import { query, getClient } from '@/lib/db';
export const dynamic = 'force-dynamic';
type Distribution = 'even' | 'business-hours' | 'custom';
interface BulkPostInput {
body: string;
hashtags?: string[];
link_url?: string;
media_urls?: string[];
}
interface BulkScheduleBody {
posts: BulkPostInput[];
target_platforms: string[];
target_account_ids?: string[];
distribution: Distribution;
start_at: string;
end_at: string;
custom_times?: string[];
needs_approval?: boolean;
created_by?: string;
}
/**
* Compute evenly-spaced timestamps between start and end (inclusive start).
*/
function evenlySpaced(startMs: number, endMs: number, n: number): Date[] {
if (n <= 0) return [];
if (n === 1) return [new Date(startMs)];
const step = (endMs - startMs) / (n - 1);
return Array.from({ length: n }, (_, i) => new Date(startMs + step * i));
}
/**
* Produce business-hour timestamps (Mon-Fri, 9am-5pm) evenly distributed
* between start and end. Uses UTC hours as a simple approximation — the
* request doesn't include timezone info. Skips weekends + off-hours.
*/
function businessHoursSchedule(startMs: number, endMs: number, n: number): Date[] {
if (n <= 0) return [];
// Build list of valid business-hour minute slots in the range
const slots: number[] = [];
const cursor = new Date(startMs);
cursor.setUTCMinutes(0, 0, 0);
while (cursor.getTime() <= endMs) {
const dow = cursor.getUTCDay(); // 0 = Sun, 6 = Sat
const hour = cursor.getUTCHours();
if (dow >= 1 && dow <= 5 && hour >= 9 && hour < 17) {
if (cursor.getTime() >= startMs) slots.push(cursor.getTime());
}
cursor.setUTCHours(cursor.getUTCHours() + 1);
}
if (slots.length === 0) return evenlySpaced(startMs, endMs, n);
if (n >= slots.length) return slots.map((t) => new Date(t));
// Pick n evenly spaced indices from slots
const step = (slots.length - 1) / (n - 1 || 1);
return Array.from({ length: n }, (_, i) => new Date(slots[Math.round(i * step)]));
}
/**
* GET /api/social/bulk-schedule — last 20 bulk imports with stats.
*/
export async function GET(_request: NextRequest) {
try {
const result = await query(
`SELECT id, source, total_rows, successful, failed, distribution,
start_at, end_at, target_platforms, created_by, created_at
FROM social_bulk_imports
ORDER BY created_at DESC
LIMIT 20`,
);
return NextResponse.json({ imports: result.rows });
} catch (err) {
console.error('[bulk-schedule GET] error:', (err as Error).message);
return NextResponse.json(
{ error: 'Failed to load bulk imports' },
{ status: 500 },
);
}
}
/**
* POST /api/social/bulk-schedule
*/
export async function POST(request: NextRequest) {
let body: BulkScheduleBody;
try {
body = await request.json();
} catch {
return NextResponse.json({ error: 'Invalid JSON body' }, { status: 400 });
}
const {
posts,
target_platforms,
target_account_ids,
distribution,
start_at,
end_at,
custom_times,
needs_approval = false,
created_by,
} = body;
if (!Array.isArray(posts) || posts.length === 0) {
return NextResponse.json({ error: 'posts array is required' }, { status: 400 });
}
if (!Array.isArray(target_platforms) || target_platforms.length === 0) {
return NextResponse.json(
{ error: 'target_platforms array is required' },
{ status: 400 },
);
}
if (!['even', 'business-hours', 'custom'].includes(distribution)) {
return NextResponse.json(
{ error: "distribution must be 'even', 'business-hours', or 'custom'" },
{ status: 400 },
);
}
const startMs = Date.parse(start_at);
const endMs = Date.parse(end_at);
if (!Number.isFinite(startMs) || !Number.isFinite(endMs) || endMs < startMs) {
return NextResponse.json(
{ error: 'start_at/end_at must be valid ISO timestamps with end >= start' },
{ status: 400 },
);
}
// Compute timestamps
let timestamps: Date[] = [];
if (distribution === 'custom') {
if (!Array.isArray(custom_times) || custom_times.length !== posts.length) {
return NextResponse.json(
{ error: 'custom_times must be an array with one timestamp per post' },
{ status: 400 },
);
}
timestamps = custom_times.map((t) => new Date(t));
if (timestamps.some((d) => Number.isNaN(d.getTime()))) {
return NextResponse.json(
{ error: 'custom_times contains invalid timestamps' },
{ status: 400 },
);
}
} else if (distribution === 'business-hours') {
timestamps = businessHoursSchedule(startMs, endMs, posts.length);
} else {
timestamps = evenlySpaced(startMs, endMs, posts.length);
}
const client = await getClient();
let importId: string | null = null;
let successful = 0;
let failed = 0;
const errors: { index: number; message: string }[] = [];
try {
await client.query('BEGIN');
const importRes = await client.query(
`INSERT INTO social_bulk_imports
(source, total_rows, successful, failed, distribution, start_at, end_at,
target_platforms, raw_payload, created_by)
VALUES ('api', $1, 0, 0, $2, $3, $4, $5, $6, $7)
RETURNING id`,
[
posts.length,
distribution,
new Date(startMs).toISOString(),
new Date(endMs).toISOString(),
target_platforms,
JSON.stringify(body),
created_by ?? null,
],
);
importId = importRes.rows[0].id;
const status = needs_approval ? 'pending_approval' : 'draft';
// Resolve account id per platform: use first matching account unless target_account_ids provided.
let accountIdsByPlatform: Record<string, string | null> = {};
if (Array.isArray(target_account_ids) && target_account_ids.length > 0) {
const accRes = await client.query(
`SELECT id, platform FROM social_accounts WHERE id = ANY($1::uuid[])`,
[target_account_ids],
);
for (const row of accRes.rows) {
if (!accountIdsByPlatform[row.platform]) {
accountIdsByPlatform[row.platform] = row.id;
}
}
} else {
const accRes = await client.query(
`SELECT DISTINCT ON (platform) id, platform
FROM social_accounts
WHERE platform = ANY($1)
ORDER BY platform, created_at ASC`,
[target_platforms],
);
for (const row of accRes.rows) {
accountIdsByPlatform[row.platform] = row.id;
}
}
for (let i = 0; i < posts.length; i++) {
const p = posts[i];
const scheduledAt = timestamps[i];
try {
for (const platform of target_platforms) {
const accountId = accountIdsByPlatform[platform] ?? null;
const postRes = await client.query(
`INSERT INTO social_posts
(account_id, platform, body, hashtags, link_url, media_urls,
status, needs_approval, bulk_import_id, submitted_at, created_by)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), $10)
RETURNING id`,
[
accountId,
platform,
p.body,
p.hashtags ?? [],
p.link_url ?? null,
p.media_urls ?? [],
status,
needs_approval,
importId,
created_by ?? null,
],
);
const postId = postRes.rows[0].id;
await client.query(
`INSERT INTO social_schedule
(post_id, scheduled_at, target_platforms, status, next_run_at)
VALUES ($1, $2, $3, 'pending', $2)`,
[postId, scheduledAt.toISOString(), [platform]],
);
}
successful += 1;
} catch (err) {
failed += 1;
errors.push({ index: i, message: (err as Error).message });
}
}
await client.query(
`UPDATE social_bulk_imports
SET successful = $1, failed = $2, errors = $3
WHERE id = $4`,
[successful, failed, JSON.stringify(errors), importId],
);
await client.query('COMMIT');
return NextResponse.json({
import_id: importId,
total: posts.length,
successful,
failed,
distribution,
start_at,
end_at,
errors,
});
} catch (err) {
try {
await client.query('ROLLBACK');
} catch {
/* ignore */
}
console.error('[bulk-schedule POST] error:', (err as Error).message);
return NextResponse.json(
{ error: 'Failed to create bulk schedule', detail: (err as Error).message },
{ status: 500 },
);
} finally {
client.release();
}
}