import { ensureSchema, rawDb } from "./db";
import { publishToMeta, type Platform } from "./meta";

type Row = Record<string, unknown>;

export async function publishPost(postId: string, ownerId: string) {
  await ensureSchema();
  const db = rawDb();
  const post = await db.prepare("SELECT * FROM scheduled_posts WHERE id = ? AND owner_id = ?")
    .bind(postId, ownerId).first<Row>();
  if (!post) throw new Error("게시물을 찾을 수 없습니다.");
  if (!["scheduled", "failed"].includes(String(post.status))) throw new Error("게시할 수 없는 상태입니다.");
  const now = Date.now();
  await db.prepare("UPDATE scheduled_posts SET status = 'processing', error_message = NULL, updated_at = ? WHERE id = ?")
    .bind(now, postId).run();
  const targets = await db.prepare("SELECT * FROM scheduled_post_targets WHERE post_id = ?").bind(postId).all<Row>();
  const errors: string[] = [];

  for (const target of targets.results) {
    try {
      let account = target.account_id
        ? await db.prepare("SELECT * FROM social_accounts WHERE id = ? AND owner_id = ? AND status = 'connected'").bind(target.account_id, ownerId).first<Row>()
        : null;
      if (!account) {
        account = await db.prepare("SELECT * FROM social_accounts WHERE owner_id = ? AND brand_id = ? AND platform = ? AND status = 'connected' ORDER BY updated_at DESC LIMIT 1")
          .bind(ownerId, post.brand_id, target.platform).first<Row>();
      }
      if (!account) throw new Error(`${target.platform === "instagram" ? "Instagram" : "Threads"} 계정 연결이 필요합니다.`);
      await db.prepare("UPDATE scheduled_post_targets SET account_id = ?, status = 'processing', updated_at = ? WHERE id = ?")
        .bind(account.id, Date.now(), target.id).run();
      const externalPostId = await publishToMeta({
        platform: String(target.platform) as Platform,
        platformUserId: String(account.platform_user_id),
        tokenEncrypted: String(account.token_encrypted), tokenIv: String(account.token_iv),
        content: String(post.content), mediaUrl: post.media_url ? String(post.media_url) : null,
        mediaType: post.media_type as "image" | "video" | null, altText: post.alt_text ? String(post.alt_text) : null,
      });
      await db.prepare("UPDATE scheduled_post_targets SET status = 'published', external_post_id = ?, published_at = ?, updated_at = ? WHERE id = ?")
        .bind(externalPostId, Date.now(), Date.now(), target.id).run();
    } catch (error) {
      const message = error instanceof Error ? error.message : "게시 실패";
      errors.push(message);
      await db.prepare("UPDATE scheduled_post_targets SET status = 'failed', error_message = ?, updated_at = ? WHERE id = ?")
        .bind(message, Date.now(), target.id).run();
    }
  }

  const status = errors.length ? "failed" : "published";
  await db.prepare("UPDATE scheduled_posts SET status = ?, published_at = ?, error_message = ?, updated_at = ? WHERE id = ?")
    .bind(status, status === "published" ? Date.now() : null, errors.join(" / ") || null, Date.now(), postId).run();
  return { status, errors };
}

export async function publishDuePosts() {
  await ensureSchema();
  const db = rawDb();
  const due = await db.prepare("SELECT id, owner_id FROM scheduled_posts WHERE status = 'scheduled' AND scheduled_for <= ? ORDER BY scheduled_for LIMIT 25")
    .bind(Date.now()).all<{ id: string; owner_id: string }>();
  const results = [];
  for (const row of due.results) {
    try {
      results.push({ id: row.id, ...(await publishPost(row.id, row.owner_id)) });
    } catch (error) {
      results.push({ id: row.id, status: "failed", errors: [error instanceof Error ? error.message : "게시 실패"] });
    }
  }
  return results;
}