Step 4 — Rewrite planAndScheduleApprovedPosts (Full Algorithmic)

import {
  initPost,
  savePlatformContent,
  getPostStatus,
  botGeneratePost,
  scheduleManyPosts,
} from '../services/posts.service.js'

import {
  initPost,
  savePlatformContent,
  getPostStatus,
  botGeneratePost,
  scheduleManyPosts,
  scheduleManyPlatformPosts,
} from '../services/posts.service.js'
import { listPostingSchedules } from '../services/clients.service.js'
import { FREQUENCY_OPTIONS } from '../constants/posting-frequency.js'

function extractFirstJsonObject(raw: string): string | null { ... }
function toFutureIso(value: string, now: Date): string | null { ... }
function normalizeLlmResponseContent(response: unknown): string { ... }
function buildFallbackScheduleTimes(...): string[] { ... }

const MS_PER_DAY = 24 * 60 * 60 * 1000
const DEFAULT_INTERVAL_DAYS = 3.5
const PREFERRED_POSTING_HOUR_UTC = 10

/** Returns interval in milliseconds for a given platform from the posting schedule. */
function platformIntervalMs(
  platform: string,
  scheduleMap: Map<string, { frequency: string; custom_interval_days: number | null }>
): number {
  const entry = scheduleMap.get(platform)
  if (!entry) return DEFAULT_INTERVAL_DAYS * MS_PER_DAY

  if (entry.frequency === 'custom' && entry.custom_interval_days != null) {
    return entry.custom_interval_days * MS_PER_DAY
  }

  const opt = FREQUENCY_OPTIONS.find((o) => o.value === entry.frequency)
  return (opt?.approxDaysInterval ?? DEFAULT_INTERVAL_DAYS) * MS_PER_DAY
}

/** Advances a cursor to the preferred posting hour (10:00 UTC) on its current or next day. */
function alignToPreferredHour(date: Date, now: Date): Date {
  const aligned = new Date(date)
  aligned.setUTCHours(PREFERRED_POSTING_HOUR_UTC, 0, 0, 0)
  if (aligned <= now) aligned.setUTCDate(aligned.getUTCDate() + 1)
  return aligned
}

async function planAndScheduleApprovedPosts(
  supabase: ReturnType<typeof createSupabaseClient>,
  customerId: number
) {
  const now = new Date()

  // 1. Fetch approved parent posts (no platform posts scheduled yet)
  const { data: approvedData, error: approvedErr } = await supabase
    .from('customer_posts')
    .select('id, title, created_at')
    .eq('customer_id', customerId)
    .eq('status', 'approved')
    .order('created_at', { ascending: true })

  if (approvedErr) throw new Error(approvedErr.message)

  const approvedPosts = approvedData ?? []
  if (approvedPosts.length === 0) {
    return { attempted_count: 0, scheduled_count: 0, failed_count: 0, results: [] }
  }

  const approvedPostIds = approvedPosts.map((p: any) => p.id as number)

  // 2. Fetch platform posts for the approved parent posts
  const { data: platformPostData, error: platformErr } = await supabase
    .from('customer_platform_post')
    .select('id, platform, customer_post_id, scheduled_at')
    .in('customer_post_id', approvedPostIds)

  if (platformErr) throw new Error(platformErr.message)

  const platformPosts = (platformPostData ?? []) as Array<{
    id: number
    platform: string
    customer_post_id: number
    scheduled_at: string | null
  }>

  // Only schedule platform posts that don't already have a scheduled_at
  const unscheduled = platformPosts.filter((pp) => !pp.scheduled_at)
  if (unscheduled.length === 0) {
    return { attempted_count: 0, scheduled_count: 0, failed_count: 0, results: [] }
  }

  // 3. Load platform posting schedules → interval map
  const { schedules: postingSchedules } = await listPostingSchedules(customerId)
  const scheduleMap = new Map(
    postingSchedules.map((s) => [
      s.platform,
      { frequency: s.frequency, custom_interval_days: s.custom_interval_days },
    ])
  )

  // 4. Seed per-platform cursor from latest existing scheduled_at for this customer
  const { data: existingScheduled } = await supabase
    .from('customer_platform_post')
    .select('platform, scheduled_at')
    .eq('customer_posts.customer_id', customerId)
    .not('scheduled_at', 'is', null)
    .gt('scheduled_at', now.toISOString())
    .order('scheduled_at', { ascending: false })

  const platformCursorMap = new Map<string, Date>()
  for (const row of existingScheduled ?? []) {
    const platform = row.platform as string
    const scheduledAt = new Date(row.scheduled_at as string)
    if (!platformCursorMap.has(platform)) {
      platformCursorMap.set(platform, scheduledAt)
    }
  }

  // 5. Group unscheduled platform posts by platform, sorted by parent created_at
  const postCreatedAt = new Map(
    approvedPosts.map((p: any) => [p.id as number, p.created_at as string])
  )
  const byPlatform = new Map<string, typeof unscheduled>()
  for (const pp of unscheduled) {
    const list = byPlatform.get(pp.platform) ?? []
    list.push(pp)
    byPlatform.set(pp.platform, list)
  }
  for (const [, list] of byPlatform) {
    list.sort((a, b) => {
      const ta = postCreatedAt.get(a.customer_post_id) ?? ''
      const tb = postCreatedAt.get(b.customer_post_id) ?? ''
      return ta < tb ? -1 : ta > tb ? 1 : 0
    })
  }

  // 6. Assign scheduled_at per platform post using interval arithmetic
  const toSchedule: Array<{ platformPostId: number; scheduledAt: string; parentPostId: number }> =
    []

  for (const [platform, posts] of byPlatform) {
    const intervalMs = platformIntervalMs(platform, scheduleMap)
    let cursor = platformCursorMap.get(platform)

    for (const pp of posts) {
      if (cursor) {
        cursor = new Date(cursor.getTime() + intervalMs)
      } else {
        // No existing schedule — start from tomorrow at preferred hour
        cursor = alignToPreferredHour(new Date(now.getTime() + intervalMs), now)
      }

      toSchedule.push({
        platformPostId: pp.id,
        scheduledAt: cursor.toISOString(),
        parentPostId: pp.customer_post_id,
      })
    }

    // Update cursor map so subsequent batches respect already-assigned times
    if (cursor) platformCursorMap.set(platform, cursor)
  }

  // 7. Persist in batches of 50
  const BATCH_SIZE = 50
  const combinedResults: Array<Record<string, unknown>> = []
  let scheduledCount = 0
  let failedCount = 0

  for (let i = 0; i < toSchedule.length; i += BATCH_SIZE) {
    const batch = toSchedule.slice(i, i + BATCH_SIZE)
    try {
      const result = await scheduleManyPlatformPosts(batch)
      combinedResults.push(...result.results)
      scheduledCount += result.scheduled_count
      failedCount += result.failed_count
    } catch (err) {
      console.error('plan_post_schedule: batch failed', { customerId, batchStart: i, err })
      failedCount += batch.length
      combinedResults.push(
        ...batch.map((item) => ({
          id: item.platformPostId,
          error: err instanceof Error ? err.message : String(err),
        }))
      )
    }
  }

  return {
    attempted_count: toSchedule.length,
    scheduled_count: scheduledCount,
    failed_count: failedCount,
    results: combinedResults,
  }
}

app.patch(
  '/post/:id/schedule',
  {
    schema: { params: PostIdParamsSchema, body: SchedulePostSchema },
  },
  async (request, reply) => {
    const postId = parseInt(request.params.id, 10)
    if (isNaN(postId)) return reply.code(400).send({ error: 'Invalid post ID' })

    try {
      const result = await schedulePost(postId, request.body.scheduled_at)
      return result
    } catch (err: any) {
      return reply.code(err?.statusCode ?? 500).send({ error: err?.message ?? String(err) })
    }
  }
)

import {
  initPost,
  savePlatformContent,
  getPostStatus,
  botGeneratePost,
  scheduleManyPosts,
  scheduleManyPlatformPosts,
  schedulePost,
  unschedulePost,
} from '../services/posts.service.js'

app.patch(
  '/post/:id/unschedule',
  {
    schema: { params: PostIdParamsSchema },
  },
  async (request, reply) => {
    const postId = parseInt(request.params.id, 10)
    if (isNaN(postId)) return reply.code(400).send({ error: 'Invalid post ID' })

    try {
      const result = await unschedulePost(postId)
      return result
    } catch (err: any) {
      return reply.code(err?.statusCode ?? 500).send({ error: err?.message ?? String(err) })
    }
  }
)