import { dbAll, dbGet, dbRun, withTransaction } from "./db.mjs";
import { addAudit, addUsage, hasPermission, httpError, requireEntitlement, requirePermission, requireProjectWritable } from "./tenant.mjs";
import { inspectMediaForProject } from "./media-qa.mjs";
import { latestArtifactForShot, listProjectArtifacts, syncProjectJobArtifacts } from "./media-artifacts.mjs";
import { dispatchNotificationEvent } from "./notifications.mjs";
import { evaluateProjectContractClearance } from "./contracts.mjs";
import { createHash } from "node:crypto";
import { existsSync, mkdirSync, readFileSync, writeFileSync } from "node:fs";
import { resolve } from "node:path";

const projectRoot = resolve(import.meta.dirname, "..");

const REVIEW_LANES = [
  ["single-frame", "一图一画面"],
  ["continuity-lock", "角色 / 场景 / 道具连续性"],
  ["voice-subtitle-asr", "声音 / 字幕 / ASR 对齐"],
  ["clip-bridge", "片段衔接 / 实际末帧"]
];

const DEFAULT_SERIES_TEMPLATE = {
  visualStyle: "原创国产漫画/国漫 2D 动画风格，竖屏 9:16，干净赛璐璐上色，电影感中景，稳定机位。",
  continuityRule: "角色、服装、道具、场景、天气、机位、声音全部进入连续性台账；下一段视频优先使用上一段实际末帧。",
  showEngine: "每集围绕一个可拍摄的冲突推进，结尾留下下一处行动钩子。"
};

const now = () => new Date().toISOString();
const makeId = (prefix) => `${prefix}-${Date.now()}-${Math.random().toString(16).slice(2, 8)}`;

function parseJson(value, fallback) {
  try {
    return JSON.parse(value);
  } catch {
    return fallback;
  }
}

function requireProject(context) {
  if (!context.project) throw httpError(400, "project_required", "生产操作必须绑定项目");
  return context.project;
}

function requireAnyPermission(context, permissions, { mutating = false } = {}) {
  if (!permissions.some((permission) => hasPermission(context, permission))) {
    throw httpError(403, "permission_denied", `缺少生产权限：${permissions.join(" / ")}`, { permissions });
  }
  if (mutating) requireProjectWritable(context);
}

function projectEpisode(context, episodeId) {
  const project = requireProject(context);
  const episode = dbGet(
    `SELECT e.*, se.series_id, sr.project_id
     FROM episodes e
     JOIN seasons se ON se.id = e.season_id
     JOIN series sr ON sr.id = se.series_id
     WHERE e.id = ? AND sr.project_id = ?`,
    [episodeId, project.id]
  );
  if (!episode) throw httpError(404, "episode_not_found", "分集不存在或不属于当前项目", { episodeId });
  return episode;
}

function ensureProductionRoot(context) {
  const project = requireProject(context);
  const timestamp = now();
  let series = dbGet("SELECT * FROM series WHERE project_id = ?", [project.id]);
  if (!series) {
    const seriesId = `series-${project.id}`;
    dbRun("INSERT INTO series(id, project_id, title, logline, format, visual_style, continuity_rule, show_engine, created_at, updated_at) VALUES (?, ?, ?, ?, 'vertical-9:16', ?, ?, ?, ?, ?)", [
      seriesId,
      project.id,
      project.name,
      `${project.name} 的本地化 AI 短剧生产项目。`,
      DEFAULT_SERIES_TEMPLATE.visualStyle,
      DEFAULT_SERIES_TEMPLATE.continuityRule,
      DEFAULT_SERIES_TEMPLATE.showEngine,
      timestamp,
      timestamp
    ]);
    series = dbGet("SELECT * FROM series WHERE id = ?", [seriesId]);
  }
  let season = dbGet("SELECT * FROM seasons WHERE series_id = ? ORDER BY season_number LIMIT 1", [series.id]);
  if (!season) {
    const seasonId = `season-${project.id}-1`;
    dbRun("INSERT INTO seasons(id, series_id, season_number, title, created_at, updated_at) VALUES (?, ?, 1, '第一季', ?, ?)", [seasonId, series.id, timestamp, timestamp]);
    season = dbGet("SELECT * FROM seasons WHERE id = ?", [seasonId]);
  }
  let episode = dbGet("SELECT * FROM episodes WHERE season_id = ? ORDER BY episode_number LIMIT 1", [season.id]);
  if (!episode) {
    const episodeId = `episode-${project.id}-01`;
    dbRun("INSERT INTO episodes(id, season_id, episode_number, title, status, target_duration_sec, hook, cliffhanger, created_at, updated_at) VALUES (?, ?, 1, '试播集', 'draft', 0, '', '', ?, ?)", [episodeId, season.id, timestamp, timestamp]);
    episode = dbGet("SELECT * FROM episodes WHERE id = ?", [episodeId]);
  }
  const shotCount = dbGet("SELECT COUNT(*) AS count FROM shots WHERE episode_id = ?", [episode.id]);
  if (Number(shotCount?.count || 0) === 0) {
    const shotId = `shot-${project.id}-001`;
    const payload = {
      id: shotId,
      title: "新镜头 01",
      durationSec: 6,
      characterIds: [],
      locationId: "",
      propIds: [],
      camera: "稳定中景，保持单一连续画面",
      action: "待编剧填写镜头动作",
      firstFrame: "episode-start",
      lastFrame: "pending-actual-last-frame",
      transitionFromPrevious: "episode-start",
      prompt: "ONE SINGLE STANDALONE STILL IMAGE FOR ONE VIDEO SHOT ONLY.",
      negativePrompt: "no split screen, no comic panel, no collage, no contact sheet",
      videoPrompt: "待填写视频动作和实际末帧要求",
      seed: null
    };
    dbRun("INSERT INTO shots(id, episode_id, shot_number, title, status, first_frame_path, last_frame_path, continuity_json, created_at, updated_at) VALUES (?, ?, 1, ?, 'draft', ?, ?, ?, ?, ?)", [shotId, episode.id, payload.title, payload.firstFrame, payload.lastFrame, JSON.stringify(payload), timestamp, timestamp]);
    dbRun("INSERT INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, 1, ?, 'draft', ?, ?)", [`${shotId}-v1`, shotId, JSON.stringify(payload), context.user.id, timestamp]);
    dbRun("UPDATE shots SET current_version_id = ? WHERE id = ?", [`${shotId}-v1`, shotId]);
  }
  return { series, season, episode };
}

function episodeSummary(row) {
  if (!row) return null;
  return {
    id: row.id,
    seasonId: row.season_id,
    episodeNumber: Number(row.episode_number),
    title: row.title,
    status: row.status,
    targetDurationSec: Number(row.target_duration_sec || 0),
    hook: row.hook || "",
    cliffhanger: row.cliffhanger || "",
    shotCount: Number(row.shot_count || 0),
    scriptVersionCount: Number(row.script_version_count || 0),
    updatedAt: row.updated_at
  };
}

export function productionCatalog(context) {
  const root = ensureProductionRoot(context);
  const seasons = dbAll(
    `SELECT se.*, COUNT(DISTINCT e.id) AS episode_count
     FROM seasons se
     LEFT JOIN episodes e ON e.season_id = se.id
     WHERE se.series_id = ?
     GROUP BY se.id
     ORDER BY se.season_number`,
    [root.series.id]
  ).map((season) => ({
    id: season.id,
    seriesId: season.series_id,
    seasonNumber: Number(season.season_number),
    title: season.title,
    episodeCount: Number(season.episode_count || 0),
    episodes: dbAll(
      `SELECT e.*,
              (SELECT COUNT(*) FROM shots s WHERE s.episode_id = e.id) AS shot_count,
              (SELECT COUNT(*) FROM script_documents d WHERE d.episode_id = e.id) AS script_version_count
       FROM episodes e
       WHERE e.season_id = ?
       ORDER BY e.episode_number`,
      [season.id]
    ).map(episodeSummary)
  }));
  return {
    series: root.series,
    activeSeasonId: root.season.id,
    activeEpisodeId: root.episode.id,
    seasons
  };
}

function insertStarterShot(context, episodeId, shotId, timestamp) {
  const payload = {
    id: shotId,
    title: "新镜头 01",
    durationSec: 6,
    characterIds: [],
    locationId: "",
    propIds: [],
    camera: "稳定中景，保持单一连续画面",
    action: "待编剧填写镜头动作",
    firstFrame: "episode-start",
    lastFrame: "pending-actual-last-frame",
    transitionFromPrevious: "episode-start",
    prompt: "ONE SINGLE STANDALONE STILL IMAGE FOR ONE VIDEO SHOT ONLY.",
    negativePrompt: "no split screen, no comic panel, no collage, no contact sheet",
    videoPrompt: "待填写视频动作和实际末帧要求",
    seed: null
  };
  dbRun("INSERT INTO shots(id, episode_id, shot_number, title, status, first_frame_path, last_frame_path, continuity_json, created_at, updated_at) VALUES (?, ?, 1, ?, 'draft', ?, ?, ?, ?, ?)", [shotId, episodeId, payload.title, payload.firstFrame, payload.lastFrame, JSON.stringify(payload), timestamp, timestamp]);
  dbRun("INSERT INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, 1, ?, 'draft', ?, ?)", [`${shotId}-v1`, shotId, JSON.stringify(payload), context.user.id, timestamp]);
  dbRun("UPDATE shots SET current_version_id = ? WHERE id = ?", [`${shotId}-v1`, shotId]);
}

export function createSeason(context, body = {}) {
  requirePermission(context, "script:edit");
  const root = ensureProductionRoot(context);
  const title = String(body.title || "").trim();
  if (title.length < 1 || title.length > 120) throw httpError(400, "season_title_invalid", "季名称不能为空且不能超过 120 个字符");
  const latest = dbGet("SELECT MAX(season_number) AS season_number FROM seasons WHERE series_id = ?", [root.series.id]);
  const seasonNumber = Number(body.seasonNumber || Number(latest?.season_number || 0) + 1);
  if (!Number.isInteger(seasonNumber) || seasonNumber < 1 || seasonNumber > 999) throw httpError(400, "season_number_invalid", "季编号必须是 1 到 999 的整数");
  if (dbGet("SELECT id FROM seasons WHERE series_id = ? AND season_number = ?", [root.series.id, seasonNumber])) throw httpError(409, "season_exists", "该季编号已经存在");
  const id = body.id || `season-${context.project.id}-${seasonNumber}-${Date.now().toString(36)}`;
  const timestamp = now();
  dbRun("INSERT INTO seasons(id, series_id, season_number, title, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)", [id, root.series.id, seasonNumber, title, timestamp, timestamp]);
  addAudit({ context, action: "season.created", targetType: "season", targetId: id, metadata: { seasonNumber, title } });
  return { season: dbGet("SELECT * FROM seasons WHERE id = ?", [id]), catalog: productionCatalog(context) };
}

export function createEpisode(context, body = {}) {
  requirePermission(context, "script:edit");
  const root = ensureProductionRoot(context);
  const seasonId = String(body.seasonId || root.season.id);
  const season = dbGet("SELECT * FROM seasons WHERE id = ? AND series_id = ?", [seasonId, root.series.id]);
  if (!season) throw httpError(404, "season_not_found", "目标季不存在或不属于当前项目", { seasonId });
  const title = String(body.title || "").trim();
  if (title.length < 1 || title.length > 160) throw httpError(400, "episode_title_invalid", "集标题不能为空且不能超过 160 个字符");
  const latest = dbGet("SELECT MAX(episode_number) AS episode_number FROM episodes WHERE season_id = ?", [seasonId]);
  const episodeNumber = Number(body.episodeNumber || Number(latest?.episode_number || 0) + 1);
  if (!Number.isInteger(episodeNumber) || episodeNumber < 1 || episodeNumber > 9999) throw httpError(400, "episode_number_invalid", "集编号必须是 1 到 9999 的整数");
  if (dbGet("SELECT id FROM episodes WHERE season_id = ? AND episode_number = ?", [seasonId, episodeNumber])) throw httpError(409, "episode_exists", "该集编号已经存在");
  const targetDurationSec = Number(body.targetDurationSec || 0);
  if (!Number.isFinite(targetDurationSec) || targetDurationSec < 0 || targetDurationSec > 3600) throw httpError(400, "target_duration_invalid", "目标时长必须在 0 到 3600 秒之间");
  const id = body.id || `episode-${context.project.id}-${season.season_number}-${episodeNumber}-${Date.now().toString(36)}`;
  const timestamp = now();
  const shotId = `${id}-shot-001`;
  withTransaction(() => {
    dbRun("INSERT INTO episodes(id, season_id, episode_number, title, status, target_duration_sec, hook, cliffhanger, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [id, seasonId, episodeNumber, title, String(body.status || "draft"), targetDurationSec, String(body.hook || ""), String(body.cliffhanger || ""), timestamp, timestamp]);
    insertStarterShot(context, id, shotId, timestamp);
  });
  addAudit({ context, action: "episode.created", targetType: "episode", targetId: id, metadata: { seasonId, episodeNumber, title, starterShotId: shotId } });
  return { episode: episodeSummary(dbGet("SELECT e.*, (SELECT COUNT(*) FROM shots s WHERE s.episode_id = e.id) AS shot_count, (SELECT COUNT(*) FROM script_documents d WHERE d.episode_id = e.id) AS script_version_count FROM episodes e WHERE e.id = ?", [id])), catalog: productionCatalog(context), graph: productionGraph(context, { episodeId: id }) };
}

export function updateEpisode(context, episodeId, body = {}) {
  requirePermission(context, "script:edit");
  const episode = projectEpisode(context, episodeId);
  const title = String(body.title ?? episode.title).trim();
  if (title.length < 1 || title.length > 160) throw httpError(400, "episode_title_invalid", "集标题不能为空且不能超过 160 个字符");
  const targetDurationSec = Number(body.targetDurationSec ?? episode.target_duration_sec ?? 0);
  if (!Number.isFinite(targetDurationSec) || targetDurationSec < 0 || targetDurationSec > 3600) throw httpError(400, "target_duration_invalid", "目标时长必须在 0 到 3600 秒之间");
  const status = String(body.status ?? episode.status).trim();
  if (!["draft", "production", "review", "approved", "archived"].includes(status)) throw httpError(400, "episode_status_invalid", "分集状态无效");
  const timestamp = now();
  dbRun("UPDATE episodes SET title = ?, status = ?, target_duration_sec = ?, hook = ?, cliffhanger = ?, updated_at = ? WHERE id = ?", [title, status, targetDurationSec, String(body.hook ?? episode.hook ?? ""), String(body.cliffhanger ?? episode.cliffhanger ?? ""), timestamp, episodeId]);
  addAudit({ context, action: "episode.updated", targetType: "episode", targetId: episodeId, metadata: { fields: Object.keys(body) } });
  return { episode: episodeSummary(dbGet("SELECT e.*, (SELECT COUNT(*) FROM shots s WHERE s.episode_id = e.id) AS shot_count, (SELECT COUNT(*) FROM script_documents d WHERE d.episode_id = e.id) AS script_version_count FROM episodes e WHERE e.id = ?", [episodeId])), catalog: productionCatalog(context), graph: productionGraph(context, { episodeId }) };
}

function shotRows(context, episodeId = null) {
  const project = requireProject(context);
  const params = [project.id];
  const episodeClause = episodeId ? " AND e.id = ?" : "";
  if (episodeId) params.push(episodeId);
  return dbAll(
    `SELECT s.*, e.episode_number, e.title AS episode_title
     FROM shots s
     JOIN episodes e ON e.id = s.episode_id
     JOIN seasons se ON se.id = e.season_id
     JOIN series sr ON sr.id = se.series_id
     WHERE sr.project_id = ?${episodeClause}
     ORDER BY e.episode_number, s.shot_number`,
    params
  );
}

function shotPayload(row) {
  const latest = (row.current_version_id && dbGet("SELECT * FROM shot_versions WHERE id = ? AND shot_id = ?", [row.current_version_id, row.id]))
    || dbGet("SELECT * FROM shot_versions WHERE shot_id = ? ORDER BY version_number DESC LIMIT 1", [row.id]);
  const payload = latest ? parseJson(latest.payload_json, {}) : parseJson(row.continuity_json, {});
  const voiceLines = dbAll("SELECT * FROM voice_lines WHERE shot_id = ? ORDER BY line_number", [row.id]).map((line) => ({
    id: line.id,
    characterId: line.character_key,
    text: line.text,
    emotion: line.emotion,
    targetDurationSec: line.target_duration_sec,
    audioFile: line.audio_path,
    mouthPlan: line.mouth_plan,
    voiceId: line.voice_id,
    status: line.status
  }));
  return {
    ...payload,
    id: row.id,
    title: row.title,
    status: row.status,
    episodeId: row.episode_id,
    episodeNumber: row.episode_number,
    shotNumber: row.shot_number,
    firstFrame: row.first_frame_path || payload.firstFrame || "episode-start",
    lastFrame: row.last_frame_path || payload.lastFrame || "pending-actual-last-frame",
    continuity: parseJson(row.continuity_json, {}),
    versionNumber: Number(latest?.version_number || 1),
    versionId: latest?.id || "",
    voiceLines
  };
}

function scriptDocuments(context, episodeId = null) {
  const project = requireProject(context);
  const clause = episodeId ? " AND episode_id = ?" : "";
  const params = episodeId ? [project.id, episodeId] : [project.id];
  return dbAll(`SELECT * FROM script_documents WHERE project_id = ?${clause} ORDER BY version_number DESC`, params).map((row) => ({
    ...row,
    analysis: parseJson(row.analysis_json, {}),
    metadata: parseJson(row.metadata_json, {})
  }));
}

function reviewRows(context) {
  const project = requireProject(context);
  return dbAll(
    `SELECT r.*, s.title AS shot_title, s.shot_number, u.display_name AS decision_by_name
     FROM reviews r
     LEFT JOIN shots s ON s.id = r.shot_id
     LEFT JOIN users u ON u.id = r.decision_by
     WHERE r.project_id = ?
     ORDER BY COALESCE(s.shot_number, 999), r.lane`,
    [project.id]
  ).map((row) => ({
    ...row,
    evidence: parseJson(row.evidence_json, {}),
    comments: dbAll("SELECT c.*, u.display_name AS author_name FROM review_comments c LEFT JOIN users u ON u.id = c.author_user_id WHERE c.review_id = ? ORDER BY c.created_at", [row.id])
  }));
}

function ensureReviews(context) {
  const project = requireProject(context);
  const shots = shotRows(context);
  const timestamp = now();
  for (const shot of shots) {
    for (const [lane, label] of REVIEW_LANES) {
      const id = `review-${shot.id}-${lane}`;
      dbRun("INSERT OR IGNORE INTO reviews(id, organization_id, workspace_id, project_id, shot_id, lane, status, evidence_json, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, 'pending', ?, ?, ?)", [id, context.organization.id, context.workspace.id, project.id, shot.id, lane, JSON.stringify({ label, source: "production-gate" }), timestamp, timestamp]);
    }
  }
  return reviewRows(context);
}

function projectAssetLocks(context) {
  const project = requireProject(context);
  const rows = dbAll(
    `SELECT a.*, av.version_number, av.storage_path, av.file_name, av.mime_type, av.file_size,
            av.content_sha256, av.rights_status, av.metadata_json
     FROM assets a
     LEFT JOIN asset_versions av ON av.id = a.current_version_id
     WHERE a.project_id = ?
     ORDER BY CASE a.kind WHEN 'character' THEN 1 WHEN 'location' THEN 2 WHEN 'prop' THEN 3 ELSE 4 END, a.name`,
    [project.id]
  );
  return rows.map((row) => {
    const metadata = parseJson(row.metadata_json, {});
    const base = {
      id: row.id,
      name: row.name,
      kind: row.kind,
      status: row.lock_status,
      lockStatus: row.lock_status,
      version: Number(row.version_number || 1),
      storagePath: row.storage_path || "",
      rightsStatus: row.rights_status || "needs-evidence",
      contentSha256: row.content_sha256 || "",
      metadata,
      detail: metadata.detail || metadata.visualLock || metadata.description || "",
      lock: metadata.lock || metadata.continuityLock || "",
      cameraLock: metadata.cameraLock || metadata.camera || "",
      bindings: dbAll("SELECT shot_id, usage_role FROM asset_bindings WHERE asset_id = ? ORDER BY shot_id", [row.id])
    };
    if (row.kind === "character") {
      return {
        ...base,
        visualLock: metadata.visualLock || metadata.detail || "",
        costumeState: metadata.costumeState || metadata.lock || "",
        voiceLock: {
          status: metadata.voiceStatus || row.rights_status || row.lock_status,
          voiceId: metadata.voiceId || "",
          tone: metadata.tone || "",
          ttsModel: metadata.ttsModel || "",
          asrModel: metadata.asrModel || "",
          referencePolicy: metadata.referencePolicy || metadata.lock || ""
        }
      };
    }
    if (row.kind === "location") return { ...base, visualLock: metadata.visualLock || metadata.detail || "" };
    if (row.kind === "prop") return { ...base, visualLock: metadata.visualLock || metadata.detail || "" };
    return base;
  });
}

function deliveries(context) {
  const project = requireProject(context);
  return dbAll("SELECT d.*, u.display_name AS approved_by_name FROM deliveries d LEFT JOIN users u ON u.id = d.approved_by WHERE d.project_id = ? ORDER BY d.created_at DESC", [project.id]).map((row) => ({
    ...row,
    manifest: row.manifest_path,
    releaseCount: Number(dbGet("SELECT COUNT(*) AS count FROM delivery_releases WHERE delivery_id = ?", [row.id])?.count || 0),
    publishedReleaseCount: Number(dbGet("SELECT COUNT(*) AS count FROM delivery_releases WHERE delivery_id = ? AND status = 'published'", [row.id])?.count || 0)
  }));
}

function safeStoragePath(value) {
  const relative = String(value || "").trim();
  if (!relative || relative.startsWith("/") || relative.includes("..") || !relative.startsWith("storage/")) return null;
  const storageRoot = resolve(projectRoot, "storage");
  const absolute = resolve(projectRoot, relative);
  if (!absolute.startsWith(`${storageRoot}/`)) return null;
  return { relative, absolute };
}

function safePathSegment(value, fallback = "item") {
  const normalized = String(value || "").trim().replace(/[^a-zA-Z0-9._-]+/g, "-").replace(/^-+|-+$/g, "");
  return normalized || fallback;
}

function jsonHash(value) {
  return createHash("sha256").update(JSON.stringify(value)).digest("hex");
}

function objectValue(value, fallback = {}) {
  return value && typeof value === "object" && !Array.isArray(value) ? value : fallback;
}

function uniqueStrings(values) {
  return [...new Set((values || []).map((item) => String(item || "").trim()).filter(Boolean))];
}

function listFromJson(value) {
  const parsed = parseJson(value, []);
  return Array.isArray(parsed) ? parsed : [];
}

function clearanceEvidenceRef(metadata = {}, provenance = {}) {
  const rightsEvidence = objectValue(metadata.rightsEvidence);
  return String(
    provenance.evidenceRef
      || provenance.sourceRef
      || rightsEvidence.reference
      || rightsEvidence.consentRef
      || rightsEvidence.contractRef
      || rightsEvidence.licenseRef
      || rightsEvidence.evidenceRef
      || metadata.consentRef
      || metadata.licenseRef
      || ""
  ).trim();
}

function isDateInPast(value) {
  const timestamp = Date.parse(value || "");
  return Number.isFinite(timestamp) && timestamp <= Date.now();
}

function isDateSoon(value, days = 30) {
  const timestamp = Date.parse(value || "");
  if (!Number.isFinite(timestamp)) return false;
  return timestamp > Date.now() && timestamp <= Date.now() + days * 24 * 60 * 60 * 1000;
}

function clearanceStatus(blockers, reviewItems) {
  if (blockers.length) return "blocked";
  if (reviewItems.length) return "review";
  return "pass";
}

function dedupeBlockers(blockers) {
  const seen = new Set();
  return blockers.filter((blocker) => {
    const key = [blocker.type, blocker.id, blocker.assetId, blocker.documentId, blocker.packId, blocker.voiceId, blocker.shotId, blocker.status, blocker.reason].filter(Boolean).join("|");
    if (seen.has(key)) return false;
    seen.add(key);
    return true;
  });
}

function deliveryBatchItems(batchId) {
  if (!batchId) return [];
  return dbAll(
    `SELECT dbi.*, s.title AS shot_title, s.shot_number
     FROM delivery_batch_items dbi
     LEFT JOIN shots s ON s.id = dbi.shot_id
     WHERE dbi.batch_id = ?
     ORDER BY dbi.sequence_number`,
    [batchId]
  ).map((item) => ({ ...item, metadata: parseJson(item.metadata_json, {}) }));
}

function activeBatchForDelivery(context, delivery) {
  const project = requireProject(context);
  if (!delivery.active_batch_id) return null;
  return dbGet(
    `SELECT * FROM delivery_batches
     WHERE id = ? AND delivery_id = ? AND organization_id = ? AND workspace_id = ? AND project_id = ?`,
    [delivery.active_batch_id, delivery.id, context.organization.id, context.workspace.id, project.id]
  );
}

function mediaClearanceSection(activeBatch, items) {
  const blockers = [];
  const reviewItems = [];
  if (!activeBatch) {
    blockers.push({ type: "delivery_batch", status: "missing", reason: "交付版本没有当前生效批次" });
    return { items: [], blockers, reviewItems };
  }
  if (activeBatch.status !== "active") blockers.push({ type: "delivery_batch", status: activeBatch.status, reason: "当前交付批次不是 active" });
  const batchResult = parseJson(activeBatch.result_json, {});
  if (Array.isArray(batchResult.blockers) && batchResult.blockers.length) blockers.push({ type: "delivery_batch", status: "blocked", reason: "批次创建时已登记媒体阻断项", items: batchResult.blockers });
  const manifest = safeStoragePath(activeBatch.manifest_path);
  if (!manifest || !existsSync(manifest.absolute)) blockers.push({ type: "manifest", status: "missing", reason: "当前批次 Manifest 文件不存在" });
  if (!items.length) blockers.push({ type: "delivery_batch_items", status: "missing", reason: "当前批次没有镜头条目" });
  const mediaItems = items.map((item) => {
    const source = safeStoragePath(item.source_path);
    const actualLastFrame = safeStoragePath(item.actual_last_frame_path);
    const itemBlockers = [];
    if (!source || !existsSync(source.absolute)) itemBlockers.push("视频源文件不存在");
    if (!item.source_sha256) itemBlockers.push("视频源缺少 SHA-256");
    if (!actualLastFrame || !existsSync(actualLastFrame.absolute)) itemBlockers.push("实际末帧证据不存在");
    for (const reason of itemBlockers) blockers.push({ type: reason.includes("末帧") ? "actual-last-frame" : "media", shotId: item.shot_id, status: "blocked", reason });
    return {
      id: item.id,
      shotId: item.shot_id,
      shotTitle: item.shot_title || "",
      sequenceNumber: Number(item.sequence_number || 0),
      sourcePath: item.source_path || "",
      sourceSha256: item.source_sha256 || "",
      actualLastFramePath: item.actual_last_frame_path || "",
      artifactStatus: item.metadata.artifactStatus || "",
      status: itemBlockers.length ? "blocked" : "pass",
      blockers: itemBlockers
    };
  });
  return { items: mediaItems, blockers, reviewItems };
}

function boundAssetsForShotIds(shotIds) {
  const ids = uniqueStrings(shotIds);
  if (!ids.length) return [];
  const placeholders = ids.map(() => "?").join(",");
  return dbAll(
    `SELECT a.id AS asset_id, a.kind, a.name, a.lock_status, a.current_version_id,
            av.id AS version_id, av.version_number, av.storage_path, av.file_name, av.mime_type,
            av.file_size, av.content_sha256, av.rights_status, av.provenance_json, av.risk_json,
            av.tags_json, av.license_scope, av.expires_at, av.metadata_json,
            GROUP_CONCAT(DISTINCT ab.shot_id) AS shot_ids,
            GROUP_CONCAT(DISTINCT ab.usage_role) AS usage_roles
     FROM asset_bindings ab
     JOIN assets a ON a.id = ab.asset_id
     LEFT JOIN asset_versions av ON av.id = a.current_version_id
     WHERE ab.shot_id IN (${placeholders})
     GROUP BY a.id
     ORDER BY CASE a.kind WHEN 'character' THEN 1 WHEN 'location' THEN 2 WHEN 'prop' THEN 3 WHEN 'voice' THEN 4 ELSE 5 END, a.name`,
    ids
  );
}

function voiceAssetsForLines(context, voiceLines) {
  const voiceIds = uniqueStrings(voiceLines.map((line) => line.voice_id));
  if (!voiceIds.length) return [];
  const project = requireProject(context);
  const placeholders = voiceIds.map(() => "?").join(",");
  return dbAll(
    `SELECT a.id AS asset_id, a.kind, a.name, a.lock_status, a.current_version_id,
            av.id AS version_id, av.version_number, av.storage_path, av.file_name, av.mime_type,
            av.file_size, av.content_sha256, av.rights_status, av.provenance_json, av.risk_json,
            av.tags_json, av.license_scope, av.expires_at, av.metadata_json
     FROM assets a
     LEFT JOIN asset_versions av ON av.id = a.current_version_id
     WHERE a.project_id = ? AND a.kind = 'voice'
       AND (${voiceIds.map(() => "av.metadata_json LIKE ?").join(" OR ")})`,
    [project.id, ...voiceIds.map((voiceId) => `%"voiceId":"${voiceId}"%`)]
  );
}

function assetClearanceSection(context, shotIds) {
  const blockers = [];
  const reviewItems = [];
  const rows = boundAssetsForShotIds(shotIds);
  const assets = rows.map((row) => {
    const metadata = parseJson(row.metadata_json, {});
    const provenance = parseJson(row.provenance_json, {});
    const risk = parseJson(row.risk_json, {});
    const shotRefs = uniqueStrings(String(row.shot_ids || "").split(","));
    const usageRoles = uniqueStrings(String(row.usage_roles || "").split(","));
    const evidenceRef = clearanceEvidenceRef(metadata, provenance);
    const itemBlockers = [];
    const itemReviews = [];
    if (!row.version_id) itemBlockers.push("资产没有当前版本");
    if (row.lock_status !== "locked") itemBlockers.push("资产未锁定 continuity lock");
    if ((row.rights_status || "needs-evidence") !== "approved") itemBlockers.push(`资产授权状态为 ${row.rights_status || "needs-evidence"}`);
    if ((risk.status || "unscanned") === "blocked") itemBlockers.push("资产风险扫描为 blocked");
    if (row.expires_at && isDateInPast(row.expires_at)) itemBlockers.push("资产授权已过期");
    if (row.rights_status === "approved" && !evidenceRef) itemBlockers.push("资产缺少授权证据引用");
    if (!row.license_scope) itemReviews.push("资产缺少授权范围说明");
    if (!row.content_sha256) itemReviews.push("资产源文件缺少 SHA-256，可在正式素材入库时补齐");
    if (!risk.status || risk.status === "review" || risk.status === "unscanned") itemReviews.push("资产风险扫描需要复核");
    if (row.expires_at && isDateSoon(row.expires_at)) itemReviews.push("资产授权 30 天内到期");
    for (const reason of itemBlockers) blockers.push({ type: "asset", assetId: row.asset_id, shotIds: shotRefs, status: "blocked", reason });
    for (const reason of itemReviews) reviewItems.push({ type: "asset", assetId: row.asset_id, shotIds: shotRefs, status: "review", reason });
    return {
      assetId: row.asset_id,
      name: row.name,
      kind: row.kind,
      versionId: row.version_id || "",
      versionNumber: Number(row.version_number || 0),
      lockStatus: row.lock_status || "",
      rightsStatus: row.rights_status || "needs-evidence",
      riskStatus: risk.status || "unscanned",
      licenseScope: row.license_scope || "",
      expiresAt: row.expires_at || "",
      evidenceRef,
      contentSha256: row.content_sha256 || "",
      storagePath: row.storage_path || "",
      shotIds: shotRefs,
      usageRoles,
      status: itemBlockers.length ? "blocked" : itemReviews.length ? "review" : "pass",
      blockers: itemBlockers,
      reviewItems: itemReviews
    };
  });
  return { assets, blockers, reviewItems };
}

function voiceClearanceSection(context, shotIds) {
  const blockers = [];
  const reviewItems = [];
  const ids = uniqueStrings(shotIds);
  if (!ids.length) return { voices: [], blockers, reviewItems };
  const placeholders = ids.map(() => "?").join(",");
  const voiceLines = dbAll(
    `SELECT vl.*, s.title AS shot_title
     FROM voice_lines vl
     JOIN shots s ON s.id = vl.shot_id
     WHERE vl.shot_id IN (${placeholders})
     ORDER BY vl.shot_id, vl.line_number`,
    ids
  );
  const assetRows = voiceAssetsForLines(context, voiceLines);
  const assetsByVoiceId = new Map();
  for (const asset of assetRows) {
    const metadata = parseJson(asset.metadata_json, {});
    if (metadata.voiceId) assetsByVoiceId.set(String(metadata.voiceId), asset);
  }
  const grouped = new Map();
  for (const line of voiceLines) {
    const key = line.voice_id || `missing-${line.id}`;
    if (!grouped.has(key)) grouped.set(key, []);
    grouped.get(key).push(line);
  }
  const voices = [...grouped.entries()].map(([voiceId, lines]) => {
    const asset = assetsByVoiceId.get(voiceId) || null;
    const metadata = parseJson(asset?.metadata_json, {});
    const provenance = parseJson(asset?.provenance_json, {});
    const risk = parseJson(asset?.risk_json, {});
    const evidenceRef = clearanceEvidenceRef(metadata, provenance);
    const itemBlockers = [];
    const itemReviews = [];
    if (!voiceId || voiceId.startsWith("missing-")) itemBlockers.push("对白缺少固定 voiceId");
    if (lines.some((line) => !line.audio_path)) itemBlockers.push("对白缺少已登记音频文件");
    if (!asset) itemBlockers.push("固定声线未登记为 voice 资产");
    if (asset && asset.lock_status !== "locked") itemBlockers.push("voice 资产未锁定");
    if (asset && (asset.rights_status || "needs-evidence") !== "approved") itemBlockers.push(`voice 资产授权状态为 ${asset.rights_status || "needs-evidence"}`);
    if (asset && risk.status === "blocked") itemBlockers.push("voice 资产风险扫描为 blocked");
    if (asset?.expires_at && isDateInPast(asset.expires_at)) itemBlockers.push("voice 授权已过期");
    if (asset && !evidenceRef) itemBlockers.push("voice 资产缺少同意书/授权证据引用");
    if (asset && !asset.license_scope) itemReviews.push("voice 资产缺少授权范围说明");
    if (asset && !asset.content_sha256) itemReviews.push("voice 参考音频缺少 SHA-256，可在正式素材入库时补齐");
    for (const reason of itemBlockers) blockers.push({ type: "voice", voiceId, assetId: asset?.asset_id || "", shotIds: uniqueStrings(lines.map((line) => line.shot_id)), status: "blocked", reason });
    for (const reason of itemReviews) reviewItems.push({ type: "voice", voiceId, assetId: asset?.asset_id || "", shotIds: uniqueStrings(lines.map((line) => line.shot_id)), status: "review", reason });
    return {
      voiceId,
      assetId: asset?.asset_id || "",
      name: asset?.name || "",
      rightsStatus: asset?.rights_status || "missing",
      riskStatus: risk.status || "unscanned",
      lockStatus: asset?.lock_status || "missing",
      licenseScope: asset?.license_scope || "",
      evidenceRef,
      lineCount: lines.length,
      shotIds: uniqueStrings(lines.map((line) => line.shot_id)),
      status: itemBlockers.length ? "blocked" : itemReviews.length ? "review" : "pass",
      blockers: itemBlockers,
      reviewItems: itemReviews
    };
  });
  return { voices, blockers, reviewItems };
}

function scriptSourceIdsFromMetadata(metadata) {
  const ids = [];
  const packIds = [];
  const chunkIds = [];
  if (metadata.knowledgeDocumentId) ids.push(metadata.knowledgeDocumentId);
  if (Array.isArray(metadata.knowledgeDocumentIds)) ids.push(...metadata.knowledgeDocumentIds);
  if (metadata.knowledgePackId) packIds.push(metadata.knowledgePackId);
  if (Array.isArray(metadata.knowledgePackIds)) packIds.push(...metadata.knowledgePackIds);
  if (Array.isArray(metadata.knowledgeChunkIds)) chunkIds.push(...metadata.knowledgeChunkIds);
  if (Array.isArray(metadata.knowledgeCitations)) {
    for (const citation of metadata.knowledgeCitations) {
      if (citation?.documentId) ids.push(citation.documentId);
      if (citation?.chunkId) chunkIds.push(citation.chunkId);
    }
  }
  return { documentIds: uniqueStrings(ids), packIds: uniqueStrings(packIds), chunkIds: uniqueStrings(chunkIds) };
}

function auditMappedKnowledgeSources(projectId, scriptIds) {
  if (!scriptIds.length) return { documentIds: [], packIds: [] };
  const rows = dbAll(
    `SELECT action, target_id, metadata_json
     FROM audit_logs
     WHERE project_id = ? AND action IN ('knowledge.document.materialized', 'knowledge.context_pack.materialized')
     ORDER BY created_at DESC
     LIMIT 300`,
    [projectId]
  );
  const documentIds = [];
  const packIds = [];
  const scriptSet = new Set(scriptIds);
  for (const row of rows) {
    const metadata = parseJson(row.metadata_json, {});
    if (!scriptSet.has(String(metadata.scriptDocumentId || ""))) continue;
    if (row.action === "knowledge.document.materialized") documentIds.push(row.target_id);
    if (row.action === "knowledge.context_pack.materialized") packIds.push(row.target_id);
  }
  return { documentIds: uniqueStrings(documentIds), packIds: uniqueStrings(packIds) };
}

function knowledgeDocumentsByIds(context, documentIds) {
  const ids = uniqueStrings(documentIds);
  if (!ids.length) return [];
  const placeholders = ids.map(() => "?").join(",");
  return dbAll(
    `SELECT * FROM knowledge_documents
     WHERE id IN (${placeholders}) AND organization_id = ? AND workspace_id = ?
       AND (project_id IS NULL OR project_id = ?)`,
    [...ids, context.organization.id, context.workspace.id, context.project.id]
  );
}

function knowledgePacksByIds(context, packIds) {
  const ids = uniqueStrings(packIds);
  if (!ids.length) return [];
  const placeholders = ids.map(() => "?").join(",");
  return dbAll(
    `SELECT * FROM knowledge_context_packs
     WHERE id IN (${placeholders}) AND organization_id = ? AND workspace_id = ?
       AND (project_id IS NULL OR project_id = ?)`,
    [...ids, context.organization.id, context.workspace.id, context.project.id]
  );
}

function knowledgeDocumentsFromChunks(context, chunkIds) {
  const ids = uniqueStrings(chunkIds);
  if (!ids.length) return [];
  const placeholders = ids.map(() => "?").join(",");
  return dbAll(
    `SELECT DISTINCT kd.*
     FROM knowledge_chunks kc
     JOIN knowledge_documents kd ON kd.id = kc.document_id
     WHERE kc.id IN (${placeholders}) AND kd.organization_id = ? AND kd.workspace_id = ?
       AND (kd.project_id IS NULL OR kd.project_id = ?)`,
    [...ids, context.organization.id, context.workspace.id, context.project.id]
  );
}

function knowledgeClearanceSection(context) {
  const project = requireProject(context);
  const blockers = [];
  const reviewItems = [];
  const scripts = dbAll("SELECT id, title, source_type, metadata_json, content FROM script_documents WHERE project_id = ? ORDER BY version_number DESC", [project.id])
    .map((row) => ({ ...row, metadata: parseJson(row.metadata_json, {}) }));
  const knowledgeScripts = scripts.filter((script) => /^知识库/.test(script.source_type || "") || objectValue(script.metadata).origin?.startsWith?.("knowledge"));
  const scriptIds = knowledgeScripts.map((script) => script.id);
  const fromMetadata = knowledgeScripts.reduce((accumulator, script) => {
    const sources = scriptSourceIdsFromMetadata(script.metadata);
    accumulator.documentIds.push(...sources.documentIds);
    accumulator.packIds.push(...sources.packIds);
    accumulator.chunkIds.push(...sources.chunkIds);
    return accumulator;
  }, { documentIds: [], packIds: [], chunkIds: [] });
  const fromAudit = auditMappedKnowledgeSources(project.id, scriptIds);
  const packs = knowledgePacksByIds(context, [...fromMetadata.packIds, ...fromAudit.packIds]);
  const packChunkIds = packs.flatMap((pack) => listFromJson(pack.chunk_ids_json));
  const documents = knowledgeDocumentsByIds(context, [...fromMetadata.documentIds, ...fromAudit.documentIds]);
  const chunkDocuments = knowledgeDocumentsFromChunks(context, [...fromMetadata.chunkIds, ...packChunkIds]);
  const documentMap = new Map([...documents, ...chunkDocuments].map((document) => [document.id, document]));
  const knowledgeItems = [];
  for (const pack of packs) {
    const metadata = parseJson(pack.metadata_json, {});
    const governance = objectValue(metadata.governance);
    const citations = listFromJson(pack.citations_json);
    const chunks = listFromJson(pack.chunks_json);
    const itemBlockers = [];
    const itemReviews = [];
    if (pack.status !== "active") itemBlockers.push(`上下文包状态为 ${pack.status}`);
    if (governance.status === "blocked" || Number(governance.blockingCount || 0) > 0) itemBlockers.push("上下文包包含版权不可用或风险阻断片段");
    if (governance.status === "review" || Number(governance.reviewCount || 0) > 0) itemReviews.push("上下文包包含需要复核的素材片段");
    if (!citations.length && !chunks.length) itemReviews.push("上下文包缺少引用清单");
    for (const reason of itemBlockers) blockers.push({ type: "knowledge_pack", packId: pack.id, status: "blocked", reason });
    for (const reason of itemReviews) reviewItems.push({ type: "knowledge_pack", packId: pack.id, status: "review", reason });
    knowledgeItems.push({
      type: "context_pack",
      id: pack.id,
      title: pack.name,
      status: itemBlockers.length ? "blocked" : itemReviews.length ? "review" : "pass",
      rightsStatus: "derived",
      riskStatus: governance.status || "unscanned",
      citationCount: citations.length,
      chunkCount: listFromJson(pack.chunk_ids_json).length,
      blockers: itemBlockers,
      reviewItems: itemReviews
    });
  }
  for (const document of documentMap.values()) {
    const provenance = parseJson(document.provenance_json, {});
    const risk = parseJson(document.risk_json, {});
    const itemBlockers = [];
    const itemReviews = [];
    if (!["indexed", "active"].includes(document.status)) itemBlockers.push(`知识素材状态为 ${document.status}`);
    if ((document.rights_status || "needs-evidence") !== "approved") itemBlockers.push(`知识素材版权状态为 ${document.rights_status || "needs-evidence"}`);
    if (risk.status === "blocked") itemBlockers.push("知识素材风险扫描为 blocked");
    if (!clearanceEvidenceRef({}, provenance)) itemBlockers.push("知识素材缺少来源/授权证据引用");
    if (!risk.status || risk.status === "review" || risk.status === "unscanned") itemReviews.push("知识素材风险扫描需要复核");
    for (const reason of itemBlockers) blockers.push({ type: "knowledge_document", documentId: document.id, status: "blocked", reason });
    for (const reason of itemReviews) reviewItems.push({ type: "knowledge_document", documentId: document.id, status: "review", reason });
    knowledgeItems.push({
      type: "document",
      id: document.id,
      title: document.title,
      sourceType: document.source_type,
      rightsStatus: document.rights_status || "needs-evidence",
      riskStatus: risk.status || "unscanned",
      evidenceRef: clearanceEvidenceRef({}, provenance),
      status: itemBlockers.length ? "blocked" : itemReviews.length ? "review" : "pass",
      blockers: itemBlockers,
      reviewItems: itemReviews
    });
  }
  for (const script of knowledgeScripts) {
    const sources = scriptSourceIdsFromMetadata(script.metadata);
    const hasSource = sources.documentIds.length || sources.packIds.length || sources.chunkIds.length || fromAudit.documentIds.length || fromAudit.packIds.length;
    if (!hasSource) {
      const reason = "知识库来源剧本缺少可追溯的素材 ID，请重新从知识库物化或补来源元数据";
      blockers.push({ type: "script_source", scriptId: script.id, status: "blocked", reason });
      knowledgeItems.push({ type: "script_source", id: script.id, title: script.title, sourceType: script.source_type, status: "blocked", blockers: [reason], reviewItems: [] });
    }
  }
  return { scripts: knowledgeScripts.map((script) => ({ id: script.id, title: script.title, sourceType: script.source_type, origin: script.metadata.origin || "" })), materials: knowledgeItems, blockers, reviewItems };
}

function qaClearanceSection(context) {
  const reviews = ensureReviews(context);
  const blockers = reviews
    .filter((review) => review.status !== "approved")
    .map((review) => ({ id: review.id, type: "review", lane: review.lane, status: review.status, shotId: review.shot_id, reason: `${REVIEW_LANES.find(([lane]) => lane === review.lane)?.[1] || review.lane} 未通过` }));
  return {
    reviews: reviews.map((review) => ({
      id: review.id,
      shotId: review.shot_id,
      lane: review.lane,
      status: review.status,
      decisionByName: review.decision_by_name || ""
    })),
    blockers,
    reviewItems: []
  };
}

function jobClearanceSection(context) {
  const project = requireProject(context);
  const jobs = dbAll("SELECT id, kind, status, shot_id FROM generation_jobs WHERE project_id = ? AND status NOT IN ('completed', 'cancelled') ORDER BY created_at", [project.id]);
  return {
    jobs,
    blockers: jobs.map((job) => ({ id: job.id, type: "job", kind: job.kind, status: job.status, shotId: job.shot_id, reason: "仍有未完成或未取消的生成任务" })),
    reviewItems: []
  };
}

function clearanceReportRow(row) {
  if (!row) return null;
  return {
    ...row,
    blockerCount: Number(row.blocker_count || 0),
    reviewCount: Number(row.review_count || 0),
    certificatePath: row.certificate_path || "",
    report: parseJson(row.report_json, {})
  };
}

function latestClearanceReport(context, deliveryId) {
  return clearanceReportRow(dbGet(
    `SELECT dcr.*, u.display_name AS created_by_name
     FROM delivery_clearance_reports dcr
     LEFT JOIN users u ON u.id = dcr.created_by
     WHERE dcr.organization_id = ? AND dcr.workspace_id = ? AND dcr.project_id = ? AND dcr.delivery_id = ?
     ORDER BY dcr.created_at DESC
     LIMIT 1`,
    [context.organization.id, context.workspace.id, context.project.id, deliveryId]
  ));
}

function buildDeliveryClearanceReport(context, delivery, options = {}) {
  const project = requireProject(context);
  const activeBatch = activeBatchForDelivery(context, delivery);
  const items = deliveryBatchItems(activeBatch?.id);
  const shotIds = items.length ? items.map((item) => item.shot_id) : shotRows(context).map((shot) => shot.id);
  const media = mediaClearanceSection(activeBatch, items);
  const assets = assetClearanceSection(context, shotIds);
  const voices = voiceClearanceSection(context, shotIds);
  const knowledge = knowledgeClearanceSection(context);
  const qa = qaClearanceSection(context);
  const jobs = jobClearanceSection(context);
  const contracts = evaluateProjectContractClearance(context, project.id, {
    channel: options.channel || null,
    channelKind: options.channel?.kind || "",
    deliverables: ["vertical-video", "delivery-manifest"],
    territory: options.territory || "CN"
  });
  const blockers = [...media.blockers, ...assets.blockers, ...voices.blockers, ...knowledge.blockers, ...qa.blockers, ...jobs.blockers, ...contracts.blockers];
  const reviewItems = [...media.reviewItems, ...assets.reviewItems, ...voices.reviewItems, ...knowledge.reviewItems, ...qa.reviewItems, ...jobs.reviewItems, ...contracts.reviewItems];
  const status = clearanceStatus(blockers, reviewItems);
  const checkedAt = now();
  const baseCertificate = {
    schema: "ai-drama-platform.delivery-clearance-certificate.v1",
    issuedAt: checkedAt,
    localOnly: true,
    organizationId: context.organization.id,
    workspaceId: context.workspace.id,
    projectId: project.id,
    deliveryId: delivery.id,
    deliveryVersion: delivery.version,
    batchId: activeBatch?.id || "",
    releaseId: options.releaseId || "",
    status,
    policy: {
      singleFrameOnly: true,
      originalCommercialUse: true,
      approvedAssetsRequired: true,
      approvedKnowledgeRequired: true,
      fixedVoiceEvidenceRequired: true,
      actualLastFrameRequired: true,
      contractAuthorizationRequired: true,
      paidCloudPublishDisabled: true
    },
    counts: {
      blockers: blockers.length,
      reviewItems: reviewItems.length,
      assets: assets.assets.length,
      voices: voices.voices.length,
      knowledgeMaterials: knowledge.materials.length,
      qaReviews: qa.reviews.length,
      mediaItems: media.items.length,
      pendingJobs: jobs.jobs.length,
      contractBindings: contracts.counts?.activeBindings || 0
    }
  };
  const certificateId = `clearance-${jsonHash(baseCertificate).slice(0, 16)}`;
  const certificate = { ...baseCertificate, certificateId, issuerUserId: context.user.id, issuerName: context.user.displayName || context.user.email || context.user.id };
  const report = {
    schema: "ai-drama-platform.delivery-clearance-report.v1",
    id: options.reportId || certificateId,
    checkedAt,
    status,
    organizationId: context.organization.id,
    workspaceId: context.workspace.id,
    projectId: project.id,
    delivery: { id: delivery.id, version: delivery.version, status: delivery.status, manifestPath: delivery.manifest_path },
    batch: activeBatch ? { id: activeBatch.id, status: activeBatch.status, manifestPath: activeBatch.manifest_path, itemCount: items.length } : null,
    releaseId: options.releaseId || "",
    channel: options.channel ? { id: options.channel.id, name: options.channel.name, kind: options.channel.kind } : null,
    blockers,
    reviewItems,
    sections: {
      media,
      assets: assets.assets,
      voices: voices.voices,
      knowledge,
      qa,
      jobs,
      contracts
    },
    certificate
  };
  return report;
}

function persistDeliveryClearanceReport(context, delivery, options = {}) {
  const id = makeId("clearance");
  const report = buildDeliveryClearanceReport(context, delivery, { ...options, reportId: id });
  const certificatePath = `storage/deliveries/${safePathSegment(context.project.id)}/${safePathSegment(delivery.version)}/clearance/${safePathSegment(id)}.json`;
  const target = safeStoragePath(certificatePath);
  if (!target) throw httpError(500, "clearance_path_invalid", "清算证书路径不满足本地存储安全约束");
  mkdirSync(resolve(target.absolute, ".."), { recursive: true });
  writeFileSync(target.absolute, `${JSON.stringify({ ...report.certificate, report }, null, 2)}\n`, "utf8");
  dbRun(
    `INSERT INTO delivery_clearance_reports(
      id, organization_id, workspace_id, project_id, delivery_id, batch_id, release_id, status,
      blocker_count, review_count, certificate_path, report_json, created_by, created_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, context.organization.id, context.workspace.id, context.project.id, delivery.id, report.batch?.id || null, options.releaseId || null, report.status, report.blockers.length, report.reviewItems.length, certificatePath, JSON.stringify(report), context.user.id, report.checkedAt]
  );
  addAudit({ context, action: "delivery.clearance.checked", targetType: "delivery", targetId: delivery.id, result: report.status, metadata: { clearanceId: id, blockers: report.blockers.length, reviewItems: report.reviewItems.length, releaseId: options.releaseId || "" } });
  return clearanceReportRow(dbGet("SELECT * FROM delivery_clearance_reports WHERE id = ?", [id]));
}

function assertDeliveryClearancePass(context, delivery, options = {}) {
  const clearance = persistDeliveryClearanceReport(context, delivery, options);
  const report = clearance.report || {};
  if (report.status === "blocked") {
    throw httpError(409, "delivery_clearance_blocked", "交付权利清算未通过，不能审批或发布", { blockers: report.blockers || [], clearance: report, clearanceId: clearance.id });
  }
  return clearance;
}

function isPrivateHostname(hostname) {
  const host = String(hostname || "").toLowerCase().replace(/^\[|\]$/g, "");
  if (["localhost", "::1"].includes(host) || host.endsWith(".local") || host.endsWith(".internal")) return true;
  const octets = host.split(".").map((part) => Number(part));
  if (octets.length !== 4 || octets.some((part) => !Number.isInteger(part) || part < 0 || part > 255)) return false;
  const [first, second] = octets;
  return first === 10 || first === 127 || (first === 172 && second >= 16 && second <= 31) || (first === 192 && second === 168) || (first === 169 && second === 254);
}

function normalizeChannelEndpoint(kind, endpoint) {
  const value = String(endpoint || "").trim();
  if (kind === "local-file") {
    const path = safeStoragePath(value || "storage/releases");
    if (!path) throw httpError(400, "delivery_channel_endpoint_invalid", "本地文件渠道必须写入 storage/ 下的安全相对目录");
    return path.relative;
  }
  let parsed;
  try {
    parsed = new URL(value);
  } catch {
    throw httpError(400, "delivery_channel_endpoint_invalid", "本地 Webhook 渠道必须填写有效的 HTTP(S) 地址");
  }
  if (!["http:", "https:"].includes(parsed.protocol) || !isPrivateHostname(parsed.hostname)) {
    throw httpError(400, "delivery_channel_endpoint_public", "为遵守本地化约束，Webhook 只能指向 localhost、.local、.internal 或私网地址");
  }
  return parsed.toString();
}

function normalizeAuthEnv(value) {
  const authEnv = String(value || "").trim();
  if (authEnv && !/^[A-Z][A-Z0-9_]{0,99}$/.test(authEnv)) {
    throw httpError(400, "delivery_channel_auth_env_invalid", "认证环境变量名只能使用大写字母、数字和下划线");
  }
  return authEnv;
}

function channelPayload(row) {
  if (!row) return null;
  return {
    ...row,
    enabled: Boolean(row.enabled),
    requireApproval: Boolean(row.require_approval),
    config: parseJson(row.config_json, {})
  };
}

function ensureDefaultDeliveryChannel(context) {
  const existing = dbGet(
    "SELECT * FROM delivery_channels WHERE organization_id = ? AND workspace_id = ? AND project_id IS NULL ORDER BY created_at LIMIT 1",
    [context.organization.id, context.workspace.id]
  );
  if (existing) return existing;
  const timestamp = now();
  const id = `channel-${safePathSegment(context.organization.id)}-${safePathSegment(context.workspace.id)}-local`;
  dbRun(
    `INSERT OR IGNORE INTO delivery_channels(
      id, organization_id, workspace_id, project_id, name, kind, enabled, endpoint, auth_env,
      require_approval, config_json, created_by, created_at, updated_at
    ) VALUES (?, ?, ?, NULL, '本地文件（默认）', 'local-file', 1, 'storage/releases', '', 1, ?, ?, ?, ?)`,
    [id, context.organization.id, context.workspace.id, JSON.stringify({ root: "storage/releases", localOnly: true }), context.user.id, timestamp, timestamp]
  );
  return dbGet("SELECT * FROM delivery_channels WHERE id = ?", [id]);
}

function channelForContext(context, channelId) {
  const projectId = context.project?.id || null;
  const channel = dbGet(
    `SELECT * FROM delivery_channels
     WHERE id = ? AND organization_id = ? AND workspace_id = ?
       AND (project_id IS NULL OR project_id = ?)`,
    [channelId, context.organization.id, context.workspace.id, projectId]
  );
  if (!channel) throw httpError(404, "delivery_channel_not_found", "发布渠道不存在或不属于当前工作区", { channelId });
  return channel;
}

export function listDeliveryChannels(context) {
  requireAnyPermission(context, ["delivery:view", "delivery:approve"]);
  ensureDefaultDeliveryChannel(context);
  const projectId = context.project?.id || null;
  return {
    channels: dbAll(
      `SELECT dc.*, u.display_name AS created_by_name
       FROM delivery_channels dc
       LEFT JOIN users u ON u.id = dc.created_by
       WHERE dc.organization_id = ? AND dc.workspace_id = ?
         AND (dc.project_id IS NULL OR dc.project_id = ?)
       ORDER BY dc.enabled DESC, dc.created_at`,
      [context.organization.id, context.workspace.id, projectId]
    ).map(channelPayload)
  };
}

export function createDeliveryChannel(context, body = {}) {
  requirePermission(context, "delivery:approve");
  requireEntitlement(context, "limit.delivery_channels", 1);
  const projectId = body.projectId ? String(body.projectId).trim() : null;
  if (projectId && (!context.project || context.project.id !== projectId)) {
    throw httpError(403, "delivery_channel_project_scope", "渠道项目范围必须是当前项目或留空作为工作区渠道");
  }
  const name = String(body.name || "").trim();
  if (!name || name.length > 80) throw httpError(400, "delivery_channel_name_invalid", "渠道名称不能为空且不能超过 80 个字符");
  const kind = String(body.kind || "local-file").trim();
  if (!["local-file", "local-webhook"].includes(kind)) throw httpError(400, "delivery_channel_kind_invalid", "只允许 local-file 或 local-webhook 渠道");
  const endpoint = normalizeChannelEndpoint(kind, body.endpoint);
  const authEnv = normalizeAuthEnv(body.authEnv);
  if (kind === "local-file" && authEnv) throw httpError(400, "delivery_channel_auth_env_invalid", "本地文件渠道不需要认证环境变量");
  const config = body.config && typeof body.config === "object" && !Array.isArray(body.config) ? body.config : {};
  const id = makeId("delivery-channel");
  const timestamp = now();
  dbRun(
    `INSERT INTO delivery_channels(
      id, organization_id, workspace_id, project_id, name, kind, enabled, endpoint, auth_env,
      require_approval, config_json, created_by, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [id, context.organization.id, context.workspace.id, projectId, name, kind, body.enabled === false ? 0 : 1, endpoint, authEnv, body.requireApproval === false ? 0 : 1, JSON.stringify(config), context.user.id, timestamp, timestamp]
  );
  addAudit({ context, action: "delivery.channel.created", targetType: "delivery_channel", targetId: id, metadata: { name, kind, projectId, endpoint: kind === "local-webhook" ? endpoint : "local-file" } });
  return { channel: channelPayload(dbGet("SELECT * FROM delivery_channels WHERE id = ?", [id])), ...listDeliveryChannels(context) };
}

export function updateDeliveryChannel(context, channelId, body = {}) {
  requirePermission(context, "delivery:approve");
  const channel = channelForContext(context, channelId);
  const nextName = body.name === undefined ? channel.name : String(body.name || "").trim();
  if (!nextName || nextName.length > 80) throw httpError(400, "delivery_channel_name_invalid", "渠道名称不能为空且不能超过 80 个字符");
  const kind = body.kind === undefined ? channel.kind : String(body.kind || "").trim();
  if (kind !== channel.kind) throw httpError(409, "delivery_channel_kind_immutable", "渠道类型创建后不可修改，请新建渠道");
  const endpoint = body.endpoint === undefined ? channel.endpoint : normalizeChannelEndpoint(kind, body.endpoint);
  const authEnv = body.authEnv === undefined ? channel.auth_env : normalizeAuthEnv(body.authEnv);
  if (kind === "local-file" && authEnv) throw httpError(400, "delivery_channel_auth_env_invalid", "本地文件渠道不需要认证环境变量");
  const config = body.config === undefined ? parseJson(channel.config_json, {}) : (body.config && typeof body.config === "object" && !Array.isArray(body.config) ? body.config : {});
  const timestamp = now();
  dbRun(
    `UPDATE delivery_channels
     SET name = ?, enabled = ?, endpoint = ?, auth_env = ?, require_approval = ?, config_json = ?, updated_at = ?
     WHERE id = ?`,
    [nextName, body.enabled === undefined ? Number(channel.enabled) : (body.enabled ? 1 : 0), endpoint, authEnv, body.requireApproval === undefined ? Number(channel.require_approval) : (body.requireApproval ? 1 : 0), JSON.stringify(config), timestamp, channelId]
  );
  addAudit({ context, action: "delivery.channel.updated", targetType: "delivery_channel", targetId: channelId, metadata: { name: nextName, enabled: body.enabled === undefined ? Boolean(channel.enabled) : Boolean(body.enabled) } });
  return { channel: channelPayload(dbGet("SELECT * FROM delivery_channels WHERE id = ?", [channelId])), ...listDeliveryChannels(context) };
}

function parseScript(content) {
  const clean = String(content || "").replace(/\r/g, "").trim();
  const wordCount = clean.replace(/\s/g, "").length;
  const headingMatches = [...clean.matchAll(/第\s*(\d+)\s*集[^\n]*/g)];
  const chapterBlocks = headingMatches.length
    ? headingMatches.map((match, index) => {
        const start = match.index + match[0].length;
        const end = headingMatches[index + 1]?.index || clean.length;
        const block = clean.slice(start, end).trim();
        return { id: `chapter-${index + 1}`, title: match[0].trim(), words: block.replace(/\s/g, "").length, scenes: Math.max(1, (block.match(/(?:内|外)[：:]/g) || []).length), status: "已拆解" };
      })
    : clean.split(/\n\s*\n/).filter(Boolean).slice(0, 12).map((block, index) => ({ id: `chapter-${index + 1}`, title: `场次 ${String(index + 1).padStart(2, "0")}`, words: block.replace(/\s/g, "").length, scenes: 1, status: "已拆解" }));
  const names = [...clean.matchAll(/([\u4e00-\u9fa5]{2,4})[：:]/g)].map((match) => match[1]).filter((name, index, list) => list.indexOf(name) === index).slice(0, 12);
  const locationKeywords = ["地铁口", "玻璃连廊", "雨棚", "教室", "客厅", "街道", "医院", "仓库", "山路", "门口"];
  const propKeywords = ["手机", "雨伞", "雨披", "路锥", "警戒线", "钥匙", "刀", "书包", "项链", "文件"];
  const locations = locationKeywords.filter((item) => clean.includes(item)).map((name) => ({ type: "场景", name, confidence: 0.88, target: "location-locks" }));
  const props = propKeywords.filter((item) => clean.includes(item)).map((name) => ({ type: "道具", name, confidence: 0.82, target: "prop-locks" }));
  const extracted = [
    ...names.map((name) => ({ type: "角色", name, confidence: 0.94, target: "character-locks" })),
    ...locations,
    ...props
  ];
  const lines = clean.split("\n").map((line) => line.trim()).filter(Boolean);
  const sceneHeaders = lines.map((line, index) => {
    const match = line.match(/^(?:场景\s*)?(?:\d+[.、]\s*)?(内|外)[：:]\s*(.+)$/);
    return match ? { index, title: `${match[1]} · ${match[2]}` } : null;
  }).filter(Boolean);
  const sceneDrafts = (sceneHeaders.length ? sceneHeaders : [{ index: 0, title: "试播集 · 开场" }]).map((header, index) => {
    const end = sceneHeaders[index + 1]?.index || lines.length;
    const blockLines = lines.slice(header.index + (sceneHeaders.length ? 1 : 0), end);
    const dialogueLines = blockLines.map((line) => {
      const match = line.match(/^([\u4e00-\u9fa5]{2,4})[：:]\s*(.+)$/);
      return match ? { characterId: match[1], text: match[2], emotion: "自然、克制", mouthPlan: "侧脸/反应镜头", targetDurationSec: Math.max(1.5, Math.min(8, Math.round(match[2].length * 0.22 * 10) / 10)) } : null;
    }).filter(Boolean);
    const actionLines = blockLines.filter((line) => !/^[\u4e00-\u9fa5]{2,4}[：:]/.test(line));
    return {
      id: `scene-${index + 1}`,
      sceneNumber: index + 1,
      title: header.title,
      action: actionLines.join(" ") || "人物在当前场景内完成一个连续、可拍摄的动作。",
      camera: index === 0 ? "稳定中景，保留环境信息" : "沿用上一镜头轴线的连续中景",
      dialogue: dialogueLines,
      sourceLines: blockLines.length
    };
  });
  return {
    wordCount,
    chapterCount: chapterBlocks.length,
    chapters: chapterBlocks.length ? chapterBlocks : [{ id: "chapter-1", title: "未命名章节", words: wordCount, scenes: 1, status: "已拆解" }],
    extracted,
    sceneDrafts,
    parser: "local-rule-v1"
  };
}

export function productionGraph(context, options = {}) {
  const root = ensureProductionRoot(context);
  const project = requireProject(context);
  const activeEpisode = options.episodeId ? projectEpisode(context, options.episodeId) : root.episode;
  const shots = shotRows(context, activeEpisode.id).map(shotPayload);
  const documents = scriptDocuments(context, activeEpisode.id);
  const reviews = ensureReviews(context).filter((review) => !review.shot_id || shots.some((shot) => shot.id === review.shot_id));
  const locks = projectAssetLocks(context);
  const characters = locks.filter((asset) => asset.kind === "character");
  const locations = locks.filter((asset) => asset.kind === "location");
  const props = locks.filter((asset) => asset.kind === "prop");
  return {
    series: root.series,
    season: root.season,
    episode: activeEpisode,
    activeEpisodeId: activeEpisode.id,
    catalog: { ...productionCatalog(context), activeSeasonId: activeEpisode.season_id, activeEpisodeId: activeEpisode.id },
    documents,
    shots,
    characters,
    locations,
    props,
    assets: locks,
    reviews,
    deliveries: deliveries(context),
    constraints: {
      singleFrameOnly: true,
      requireActualLastFrame: true,
      localOnlyDefault: true,
      blockedImageTerms: ["split-screen", "comic panel", "collage", "contact sheet", "storyboard", "多格", "拼图"]
    }
  };
}

export function updateBible(context, body = {}) {
  requirePermission(context, "script:edit");
  const project = requireProject(context);
  const root = ensureProductionRoot(context);
  const episode = projectEpisode(context, body.episodeId || root.episode.id);
  const timestamp = now();
  const seriesValues = {
    title: body.title ?? body.seriesTitle ?? root.series.title,
    logline: body.logline ?? root.series.logline,
    format: body.format ?? root.series.format,
    visualStyle: body.visualStyle ?? body.visual_style ?? root.series.visual_style,
    continuityRule: body.continuityRule ?? body.continuity_rule ?? root.series.continuity_rule,
    showEngine: body.showEngine ?? body.show_engine ?? root.series.show_engine
  };
  const episodeValues = {
    title: body.episodeTitle ?? episode.title,
    status: body.episodeStatus ?? episode.status,
    targetDurationSec: body.targetDurationSec ?? episode.target_duration_sec,
    hook: body.hook ?? episode.hook,
    cliffhanger: body.cliffhanger ?? episode.cliffhanger
  };
  const targetDuration = Number(episodeValues.targetDurationSec);
  if (!Number.isFinite(targetDuration) || targetDuration < 0 || targetDuration > 3600) {
    throw httpError(400, "target_duration_invalid", "目标时长必须在 0 到 3600 秒之间");
  }
  withTransaction(() => {
    dbRun("UPDATE series SET title = ?, logline = ?, format = ?, visual_style = ?, continuity_rule = ?, show_engine = ?, updated_at = ? WHERE id = ? AND project_id = ?", [
      String(seriesValues.title).trim(),
      String(seriesValues.logline).trim(),
      String(seriesValues.format).trim(),
      String(seriesValues.visualStyle).trim(),
      String(seriesValues.continuityRule).trim(),
      String(seriesValues.showEngine).trim(),
      timestamp,
      root.series.id,
      project.id
    ]);
    dbRun("UPDATE episodes SET title = ?, status = ?, target_duration_sec = ?, hook = ?, cliffhanger = ?, updated_at = ? WHERE id = ?", [
      String(episodeValues.title).trim(),
      String(episodeValues.status).trim(),
      targetDuration,
      String(episodeValues.hook).trim(),
      String(episodeValues.cliffhanger).trim(),
      timestamp,
      episode.id
    ]);
  });
  addAudit({ context, action: "series_bible.updated", targetType: "series", targetId: root.series.id, metadata: { episodeId: episode.id, fields: Object.keys(body) } });
  return {
    bible: {
      series: dbGet("SELECT * FROM series WHERE id = ?", [root.series.id]),
      season: dbGet("SELECT * FROM seasons WHERE id = ?", [root.season.id]),
      episode: dbGet("SELECT * FROM episodes WHERE id = ?", [episode.id])
    },
    graph: productionGraph(context, { episodeId: episode.id })
  };
}

export function importScript(context, body) {
  requirePermission(context, "script:edit");
  const project = requireProject(context);
  const content = String(body.content || "").trim();
  if (content.length < 10) throw httpError(400, "script_content_required", "剧本内容至少需要 10 个字符");
  const root = ensureProductionRoot(context);
  const latest = dbGet("SELECT MAX(version_number) AS version_number FROM script_documents WHERE project_id = ?", [project.id]);
  const versionNumber = Number(latest?.version_number || 0) + 1;
  const analysis = parseScript(content);
  const metadata = body.metadata && typeof body.metadata === "object" && !Array.isArray(body.metadata) ? body.metadata : {};
  const id = makeId("script");
  const timestamp = now();
  dbRun("INSERT INTO script_documents(id, organization_id, workspace_id, project_id, episode_id, version_number, title, source_type, content, status, analysis_json, metadata_json, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'analyzed', ?, ?, ?, ?, ?)", [id, context.organization.id, context.workspace.id, project.id, body.episodeId || root.episode.id, versionNumber, String(body.title || analysis.chapters[0]?.title || "未命名剧本"), String(body.sourceType || "原创短剧剧本"), content, JSON.stringify(analysis), JSON.stringify(metadata), context.user.id, timestamp, timestamp]);
  dbRun("UPDATE episodes SET title = COALESCE(NULLIF(?, ''), title), updated_at = ? WHERE id = ?", [String(body.episodeTitle || "").trim(), timestamp, body.episodeId || root.episode.id]);
  addAudit({ context, action: "script.imported", targetType: "script_document", targetId: id, metadata: { versionNumber, wordCount: analysis.wordCount, chapterCount: analysis.chapterCount } });
  addUsage({ context, kind: "script-analysis", units: 1, unitName: "document", metadata: { scriptId: id, parser: analysis.parser } });
  return { document: { ...dbGet("SELECT * FROM script_documents WHERE id = ?", [id]), analysis, metadata }, graph: productionGraph(context, { episodeId: body.episodeId || root.episode.id }) };
}

export function materializeScript(context, documentId, body = {}) {
  requirePermission(context, "script:edit");
  const project = requireProject(context);
  const document = dbGet("SELECT * FROM script_documents WHERE id = ? AND project_id = ?", [documentId, project.id]);
  if (!document) throw httpError(404, "script_not_found", "剧本版本不存在或不属于当前项目", { documentId });
  const analysis = parseJson(document.analysis_json, {});
  const drafts = Array.isArray(analysis.sceneDrafts) && analysis.sceneDrafts.length ? analysis.sceneDrafts : [{ title: document.title, action: document.content, camera: "稳定中景，保持单一连续画面", dialogue: [] }];
  const episodeId = document.episode_id || ensureProductionRoot(context).episode.id;
  projectEpisode(context, episodeId);
  const existing = dbGet("SELECT MAX(shot_number) AS shot_number FROM shots WHERE episode_id = ?", [episodeId]);
  let nextShotNumber = Number(existing?.shot_number || 0);
  const created = [];
  const timestamp = now();
  withTransaction(() => {
    for (const draft of drafts) {
      nextShotNumber += 1;
      const shotId = `shot-${project.id}-${String(nextShotNumber).padStart(3, "0")}-${Date.now().toString(36)}-${nextShotNumber}`;
      const payload = {
        id: shotId,
        title: String(draft.title || `场景 ${String(nextShotNumber).padStart(2, "0")}`),
        durationSec: Number(body.durationSec || 6),
        characterIds: [],
        locationId: "",
        propIds: [],
        camera: String(draft.camera || "稳定中景，保持单一连续画面"),
        action: String(draft.action || "待补充镜头动作"),
        firstFrame: nextShotNumber === 1 ? "episode-start" : "AUTO_PREVIOUS_ACTUAL_LAST_FRAME",
        lastFrame: "pending-actual-last-frame",
        transitionFromPrevious: nextShotNumber === 1 ? "episode-start" : "actual-last-frame",
        prompt: "ONE SINGLE STANDALONE STILL IMAGE FOR ONE VIDEO SHOT ONLY.",
        negativePrompt: "no split screen, no comic panel, no collage, no contact sheet, no storyboard",
        videoPrompt: "保持角色、服装、道具、天气和机位连续；结尾保留可作为下一段首帧的实际末帧。",
        seed: null
      };
      dbRun("INSERT INTO shots(id, episode_id, shot_number, title, status, first_frame_path, last_frame_path, continuity_json, created_at, updated_at) VALUES (?, ?, ?, ?, 'draft', ?, ?, ?, ?, ?)", [shotId, episodeId, nextShotNumber, payload.title, payload.firstFrame, payload.lastFrame, JSON.stringify(payload), timestamp, timestamp]);
      dbRun("INSERT INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, 1, ?, 'draft', ?, ?)", [`${shotId}-v1`, shotId, JSON.stringify(payload), context.user.id, timestamp]);
      dbRun("UPDATE shots SET current_version_id = ? WHERE id = ?", [`${shotId}-v1`, shotId]);
      for (let index = 0; index < (draft.dialogue || []).length; index += 1) insertVoiceLine(context, shotId, index + 1, draft.dialogue[index], timestamp);
      created.push(shotId);
    }
  });
  addAudit({ context, action: "script.materialized", targetType: "script_document", targetId: documentId, metadata: { shotsCreated: created.length, shotIds: created } });
  return { created, graph: productionGraph(context, { episodeId }) };
}

export function createShot(context, body) {
  requireAnyPermission(context, ["script:edit", "prompt:edit"], { mutating: true });
  const project = requireProject(context);
  const root = ensureProductionRoot(context);
  const episodeId = body.episodeId || root.episode.id;
  projectEpisode(context, episodeId);
  const next = dbGet("SELECT MAX(shot_number) AS shot_number FROM shots WHERE episode_id = ?", [episodeId]);
  const shotNumber = Number(next?.shot_number || 0) + 1;
  const id = body.id || `shot-${project.id}-${String(shotNumber).padStart(3, "0")}-${Date.now().toString(36)}`;
  const timestamp = now();
  const payload = {
    id,
    title: String(body.title || `新镜头 ${String(shotNumber).padStart(2, "0")}`),
    durationSec: Number(body.durationSec || 6),
    characterIds: Array.isArray(body.characterIds) ? body.characterIds : [],
    locationId: String(body.locationId || ""),
    propIds: Array.isArray(body.propIds) ? body.propIds : [],
    camera: String(body.camera || "稳定中景，保持单一连续画面"),
    action: String(body.action || "待填写镜头动作"),
    firstFrame: String(body.firstFrame || (shotNumber === 1 ? "episode-start" : "AUTO_PREVIOUS_ACTUAL_LAST_FRAME")),
    lastFrame: String(body.lastFrame || "pending-actual-last-frame"),
    transitionFromPrevious: String(body.transitionFromPrevious || (shotNumber === 1 ? "episode-start" : "actual-last-frame")),
    prompt: String(body.prompt || "ONE SINGLE STANDALONE STILL IMAGE FOR ONE VIDEO SHOT ONLY."),
    negativePrompt: String(body.negativePrompt || "no split screen, no comic panel, no collage, no contact sheet"),
    videoPrompt: String(body.videoPrompt || "待填写视频动作和实际末帧要求"),
    seed: body.seed === undefined || body.seed === "" ? null : Number(body.seed)
  };
  withTransaction(() => {
    dbRun("INSERT INTO shots(id, episode_id, shot_number, title, status, first_frame_path, last_frame_path, continuity_json, created_at, updated_at) VALUES (?, ?, ?, ?, 'draft', ?, ?, ?, ?, ?)", [id, episodeId, shotNumber, payload.title, payload.firstFrame, payload.lastFrame, JSON.stringify(payload), timestamp, timestamp]);
    dbRun("INSERT INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, 1, ?, 'draft', ?, ?)", [`${id}-v1`, id, JSON.stringify(payload), context.user.id, timestamp]);
    dbRun("UPDATE shots SET current_version_id = ? WHERE id = ?", [`${id}-v1`, id]);
    for (let index = 0; index < (body.voiceLines || []).length; index += 1) insertVoiceLine(context, id, index + 1, body.voiceLines[index], timestamp);
  });
  addAudit({ context, action: "shot.created", targetType: "shot", targetId: id, metadata: { shotNumber, episodeId } });
  return { shot: shotPayload(dbGet("SELECT * FROM shots WHERE id = ?", [id])), graph: productionGraph(context, { episodeId }) };
}

function ensureShot(context, shotId) {
  const project = requireProject(context);
  const row = dbGet(
    `SELECT s.* FROM shots s
     JOIN episodes e ON e.id = s.episode_id
     JOIN seasons se ON se.id = e.season_id
     JOIN series sr ON sr.id = se.series_id
     WHERE s.id = ? AND sr.project_id = ?`,
    [shotId, project.id]
  );
  if (!row) throw httpError(404, "shot_not_found", "镜头不存在或不属于当前项目", { shotId });
  return row;
}

function insertVoiceLine(context, shotId, lineNumber, line, timestamp = now()) {
  const item = line || {};
  const id = item.id || `${shotId}-line-${lineNumber}`;
  dbRun("INSERT INTO voice_lines(id, shot_id, line_number, character_key, text, emotion, mouth_plan, target_duration_sec, voice_id, audio_path, status, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(shot_id, line_number) DO UPDATE SET character_key = excluded.character_key, text = excluded.text, emotion = excluded.emotion, mouth_plan = excluded.mouth_plan, target_duration_sec = excluded.target_duration_sec, voice_id = excluded.voice_id, audio_path = excluded.audio_path, status = excluded.status, updated_at = excluded.updated_at", [id, shotId, lineNumber, String(item.characterId || item.character_key || ""), String(item.text || ""), String(item.emotion || ""), String(item.mouthPlan || item.mouth_plan || "侧脸/反应镜头"), Number(item.targetDurationSec || item.target_duration_sec || 2), String(item.voiceId || item.voice_id || ""), String(item.audioFile || item.audio_path || ""), String(item.status || "draft"), context.user.id, timestamp, timestamp]);
}

export function updateShot(context, shotId, body) {
  requireAnyPermission(context, ["script:edit", "prompt:edit"], { mutating: true });
  const current = ensureShot(context, shotId);
  const existing = shotPayload(current);
  const timestamp = now();
  const payload = {
    ...existing,
    ...body,
    id: shotId,
    characterIds: Array.isArray(body.characterIds) ? body.characterIds : existing.characterIds || [],
    propIds: Array.isArray(body.propIds) ? body.propIds : existing.propIds || [],
    durationSec: Number(body.durationSec ?? existing.durationSec ?? 6)
  };
  withTransaction(() => {
    const nextVersion = Number(existing.versionNumber || 0) + 1;
    dbRun("UPDATE shots SET title = ?, status = ?, first_frame_path = ?, last_frame_path = ?, continuity_json = ?, updated_at = ? WHERE id = ?", [payload.title, String(body.status || current.status || "draft"), payload.firstFrame, payload.lastFrame, JSON.stringify(payload), timestamp, shotId]);
    dbRun("INSERT INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)", [`${shotId}-v${nextVersion}`, shotId, nextVersion, JSON.stringify(payload), String(body.status || current.status || "draft"), context.user.id, timestamp]);
    dbRun("UPDATE shots SET current_version_id = ? WHERE id = ?", [`${shotId}-v${nextVersion}`, shotId]);
    if (Array.isArray(body.voiceLines)) {
      dbRun("DELETE FROM voice_lines WHERE shot_id = ?", [shotId]);
      for (let index = 0; index < body.voiceLines.length; index += 1) insertVoiceLine(context, shotId, index + 1, body.voiceLines[index], timestamp);
    }
  });
  addAudit({ context, action: "shot.updated", targetType: "shot", targetId: shotId, metadata: { version: Number(existing.versionNumber || 0) + 1 } });
  return { shot: shotPayload(dbGet("SELECT * FROM shots WHERE id = ?", [shotId])), graph: productionGraph(context, { episodeId: current.episode_id }) };
}

export function savePromptVersion(context, shotId, body) {
  requirePermission(context, "prompt:edit");
  const current = ensureShot(context, shotId);
  const existing = shotPayload(current);
  const timestamp = now();
  const nextVersion = Number(existing.versionNumber || 0) + 1;
  const payload = {
    ...existing,
    prompt: String(body.imagePrompt ?? body.prompt ?? existing.prompt ?? ""),
    negativePrompt: String(body.negativePrompt ?? existing.negativePrompt ?? ""),
    videoPrompt: String(body.videoPrompt ?? existing.videoPrompt ?? ""),
    seed: body.seed === undefined || body.seed === "" ? existing.seed : Number(body.seed)
  };
  dbRun("UPDATE shots SET continuity_json = ?, updated_at = ? WHERE id = ?", [JSON.stringify(payload), timestamp, shotId]);
  dbRun("INSERT INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, ?, ?, 'draft', ?, ?)", [`${shotId}-v${nextVersion}`, shotId, nextVersion, JSON.stringify(payload), context.user.id, timestamp]);
  dbRun("UPDATE shots SET current_version_id = ? WHERE id = ?", [`${shotId}-v${nextVersion}`, shotId]);
  addAudit({ context, action: "shot.prompt.version.created", targetType: "shot_version", targetId: `${shotId}-v${nextVersion}`, metadata: { shotId, version: nextVersion } });
  return { shot: shotPayload(dbGet("SELECT * FROM shots WHERE id = ?", [shotId])), graph: productionGraph(context, { episodeId: current.episode_id }) };
}

export function listShotVersions(context, shotId) {
  requireAnyPermission(context, ["script:read", "script:edit", "prompt:edit"]);
  const shot = ensureShot(context, shotId);
  return {
    shotId,
    currentVersionId: shot.current_version_id || "",
    versions: dbAll(
      `SELECT sv.*, u.display_name AS created_by_name
       FROM shot_versions sv
       LEFT JOIN users u ON u.id = sv.created_by
       WHERE sv.shot_id = ?
       ORDER BY sv.version_number DESC`,
      [shotId]
    ).map((version) => ({
      ...version,
      payload: parseJson(version.payload_json, {}),
      isCurrent: version.id === shot.current_version_id
    }))
  };
}

export function restoreShotVersion(context, shotId, versionId) {
  requireAnyPermission(context, ["script:edit", "prompt:edit"], { mutating: true });
  const current = ensureShot(context, shotId);
  const version = dbGet("SELECT * FROM shot_versions WHERE id = ? AND shot_id = ?", [versionId, shotId]);
  if (!version) throw httpError(404, "shot_version_not_found", "镜头版本不存在或不属于当前镜头", { shotId, versionId });
  if (version.id === current.current_version_id) return { shot: shotPayload(current), versions: listShotVersions(context, shotId), graph: productionGraph(context, { episodeId: current.episode_id }) };
  const payload = parseJson(version.payload_json, {});
  const timestamp = now();
  withTransaction(() => {
    dbRun(
      `UPDATE shots
       SET title = ?, status = ?, first_frame_path = ?, last_frame_path = ?, current_version_id = ?, continuity_json = ?, updated_at = ?
       WHERE id = ?`,
      [
        String(payload.title || current.title),
        String(version.status || current.status || "draft"),
        String(payload.firstFrame || current.first_frame_path || ""),
        String(payload.lastFrame || current.last_frame_path || ""),
        version.id,
        JSON.stringify(payload),
        timestamp,
        shotId
      ]
    );
    if (Array.isArray(payload.voiceLines)) {
      dbRun("DELETE FROM voice_lines WHERE shot_id = ?", [shotId]);
      for (let index = 0; index < payload.voiceLines.length; index += 1) insertVoiceLine(context, shotId, index + 1, payload.voiceLines[index], timestamp);
    }
  });
  addAudit({ context, action: "shot.version.restored", targetType: "shot_version", targetId: version.id, metadata: { shotId, version: version.version_number, previousVersionId: current.current_version_id || null } });
  const refreshed = dbGet("SELECT * FROM shots WHERE id = ?", [shotId]);
  return { shot: shotPayload(refreshed), versions: listShotVersions(context, shotId), graph: productionGraph(context, { episodeId: current.episode_id }) };
}

export function qaReviews(context) {
  requirePermission(context, "qa:review");
  ensureProductionRoot(context);
  return { reviews: ensureReviews(context), graph: productionGraph(context) };
}

export function runAutomatedQa(context) {
  requirePermission(context, "qa:review");
  requireProjectWritable(context);
  const project = requireProject(context);
  ensureReviews(context);
  const shots = shotRows(context).map(shotPayload);
  const timestamp = now();
  const blockedTerms = ["storyboard", "comic panel", "collage", "contact sheet", "split screen", "多格", "拼图", "分屏", "故事板拼图"];
  const results = [];
  for (const shot of shots) {
    const bindings = dbAll(
      `SELECT a.kind, a.lock_status, av.rights_status
       FROM asset_bindings ab
       JOIN assets a ON a.id = ab.asset_id
       LEFT JOIN asset_versions av ON av.id = a.current_version_id
       WHERE ab.shot_id = ?`,
      [shot.id]
    );
    // The negative prompt is the evidence of the block rule, not a request to generate that layout.
    const promptText = [shot.prompt, shot.action, shot.camera].join(" ").toLowerCase();
    const blocked = blockedTerms.filter((term) => promptText.includes(term));
    const missingLocks = ["character", "location", "prop"].filter((kind) => !bindings.some((binding) => binding.kind === kind && binding.lock_status === "locked" && binding.rights_status === "approved"));
    const hasVoiceIssue = shot.voiceLines.some((line) => !line.voiceId || !line.audioFile);
    const needsActualLastFrame = shot.shotNumber > 1 && shot.transitionFromPrevious !== "episode-start";
    const bridgeIssue = needsActualLastFrame && (!shot.firstFrame || /pending|auto_previous/i.test(shot.firstFrame));
    const checks = [
      { lane: "single-frame", ok: blocked.length === 0, blockers: blocked.length ? [`禁用词：${blocked.join("、")}`] : [] },
      { lane: "continuity-lock", ok: missingLocks.length === 0, blockers: missingLocks.length ? [`未锁定或未确认：${missingLocks.join("、")}`] : [] },
      { lane: "voice-subtitle-asr", ok: !hasVoiceIssue, blockers: hasVoiceIssue ? ["对白缺少固定 voiceId 或音频产物"] : [] },
      { lane: "clip-bridge", ok: !bridgeIssue, blockers: bridgeIssue ? ["连续片段缺少上一段实际末帧首帧"] : [] }
    ];
    for (const check of checks) {
      const reviewId = `review-${shot.id}-${check.lane}`;
      const current = dbGet("SELECT * FROM reviews WHERE id = ?", [reviewId]);
      const status = check.ok ? "approved" : "changes_requested";
      const evidence = { source: "automated-contract-qa", checkedAt: timestamp, blockers: check.blockers, shotId: shot.id };
      if (!current || current.status !== "approved" || parseJson(current.evidence_json, {}).source === "automated-contract-qa") {
        dbRun("UPDATE reviews SET status = ?, score = ?, decision_by = ?, decision_at = ?, evidence_json = ?, updated_at = ? WHERE id = ?", [status, check.ok ? 100 : 40, context.user.id, timestamp, JSON.stringify(evidence), timestamp, reviewId]);
      }
      results.push({ shotId: shot.id, lane: check.lane, status, blockers: check.blockers });
    }
  }
  addAudit({ context, action: "qa.automated.run", targetType: "project", targetId: project.id, metadata: { checks: results.length, passed: results.filter((item) => item.status === "approved").length } });
  return { results, reviews: ensureReviews(context), graph: productionGraph(context) };
}

export async function runMediaQa(context) {
  requirePermission(context, "qa:review");
  requireProjectWritable(context);
  const project = requireProject(context);
  ensureReviews(context);
  await syncProjectJobArtifacts(context);
  const shots = shotRows(context).map(shotPayload);
  const report = await inspectMediaForProject(context, shots);
  const timestamp = now();
  for (const shotReport of report.reports) {
    for (const gate of shotReport.gates) {
      const reviewId = `review-${shotReport.shotId}-${gate.lane}`;
      const current = dbGet("SELECT * FROM reviews WHERE id = ?", [reviewId]);
      const currentEvidence = parseJson(current?.evidence_json, {});
      const isManualDecision = current?.status === "approved" && currentEvidence.source === "manual-review";
      if (!isManualDecision) {
        dbRun(
          `UPDATE reviews
           SET status = ?, score = ?, decision_by = ?, decision_at = ?, evidence_json = ?, updated_at = ?
           WHERE id = ?`,
          [gate.status, gate.status === "approved" ? 100 : gate.status === "changes_requested" ? 40 : null, context.user.id, gate.status === "pending" ? null : timestamp, JSON.stringify({ source: "media-inspection-v1", checkedAt: report.checkedAt, blockers: gate.blockers, evidence: gate.evidence, media: shotReport.media }), timestamp, reviewId]
        );
      }
    }
  }
  addAudit({ context, action: "qa.media_inspection.run", targetType: "project", targetId: project.id, metadata: report.totals });
  return { report, reviews: ensureReviews(context), graph: productionGraph(context) };
}

function ensureReview(context, reviewId) {
  const project = requireProject(context);
  const review = dbGet("SELECT * FROM reviews WHERE id = ? AND project_id = ?", [reviewId, project.id]);
  if (!review) throw httpError(404, "review_not_found", "质检项不存在或不属于当前项目", { reviewId });
  return review;
}

export function decideReview(context, reviewId, body) {
  requirePermission(context, "qa:review");
  requireProjectWritable(context);
  const review = ensureReview(context, reviewId);
  const status = String(body.status || "").trim();
  if (!["approved", "rejected", "changes_requested", "pending"].includes(status)) throw httpError(400, "review_status_invalid", "质检状态无效");
  const timestamp = now();
  const evidence = body.evidence && typeof body.evidence === "object" ? body.evidence : parseJson(review.evidence_json, {});
  dbRun("UPDATE reviews SET status = ?, score = ?, decision_by = ?, decision_at = ?, evidence_json = ?, updated_at = ? WHERE id = ?", [status, body.score === undefined ? null : Number(body.score), context.user.id, status === "pending" ? null : timestamp, JSON.stringify(evidence), timestamp, reviewId]);
  addAudit({ context, action: `qa.review.${status}`, targetType: "review", targetId: reviewId, result: status === "approved" ? "pass" : status, metadata: { lane: review.lane, score: body.score ?? null, evidence } });
  const eventKey = status === "approved" ? "review.approved" : status === "changes_requested" ? "review.changes_requested" : status === "rejected" ? "review.rejected" : "review.updated";
  void dispatchNotificationEvent({ context, eventKey, payload: { reviewId, lane: review.lane, status, shotId: review.shot_id, targetId: reviewId } });
  return { review: dbGet("SELECT * FROM reviews WHERE id = ?", [reviewId]), reviews: ensureReviews(context) };
}

export function addReviewComment(context, reviewId, body) {
  requirePermission(context, "qa:review");
  requireProjectWritable(context);
  ensureReview(context, reviewId);
  const bodyText = String(body.body || "").trim();
  if (!bodyText) throw httpError(400, "comment_required", "质检评论不能为空");
  const id = makeId("comment");
  dbRun("INSERT INTO review_comments(id, review_id, author_user_id, body, created_at) VALUES (?, ?, ?, ?, ?)", [id, reviewId, context.user.id, bodyText, now()]);
  addAudit({ context, action: "qa.comment.created", targetType: "review_comment", targetId: id, metadata: { reviewId } });
  void dispatchNotificationEvent({ context, eventKey: "review.comment", payload: { reviewId, targetId: reviewId, body: bodyText } });
  return { comments: dbAll("SELECT c.*, u.display_name AS author_name FROM review_comments c LEFT JOIN users u ON u.id = c.author_user_id WHERE c.review_id = ? ORDER BY c.created_at", [reviewId]) };
}

export function listDeliveries(context) {
  requireAnyPermission(context, ["delivery:view", "delivery:approve"]);
  return { deliveries: deliveries(context) };
}

export function getDeliveryClearance(context, deliveryId) {
  requireAnyPermission(context, ["delivery:view", "delivery:approve", "compliance:manage"]);
  const delivery = deliveryForContext(context, deliveryId);
  const preview = buildDeliveryClearanceReport(context, delivery);
  return {
    deliveryId,
    clearance: preview,
    latest: latestClearanceReport(context, deliveryId)
  };
}

export function runDeliveryClearance(context, deliveryId, body = {}) {
  requireAnyPermission(context, ["delivery:approve", "compliance:manage"], { mutating: true });
  const delivery = deliveryForContext(context, deliveryId);
  const releaseId = String(body.releaseId || body.release_id || "").trim();
  const release = releaseId ? releaseForContext(context, releaseId) : null;
  if (release && release.delivery_id !== delivery.id) throw httpError(422, "clearance_release_mismatch", "发布申请不属于当前交付版本", { deliveryId, releaseId });
  const channel = release ? channelForContext(context, release.channel_id) : null;
  const clearance = persistDeliveryClearanceReport(context, delivery, { releaseId: release?.id || "", channel });
  return {
    deliveryId,
    clearance: clearance.report,
    latest: clearance,
    deliveries: deliveries(context)
  };
}

export function createDelivery(context, body) {
  requirePermission(context, "delivery:approve");
  const project = requireProject(context);
  const latest = dbGet("SELECT COUNT(*) AS count FROM deliveries WHERE project_id = ?", [project.id]);
  const version = String(body.version || `v${Number(latest?.count || 0) + 1}.0`);
  const id = makeId("delivery");
  const timestamp = now();
  const manifestPath = String(body.manifestPath || `storage/deliveries/${project.id}/${version}/delivery-manifest.json`);
  if (!manifestPath.startsWith("storage/") || manifestPath.includes("..") || manifestPath.startsWith("/")) throw httpError(400, "manifest_path_invalid", "交付 manifest 必须写入 storage/ 下的安全相对路径");
  dbRun("INSERT INTO deliveries(id, organization_id, workspace_id, project_id, version, manifest_path, channel, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, 'draft', ?, ?)", [id, context.organization.id, context.workspace.id, project.id, version, manifestPath, String(body.channel || "internal"), timestamp, timestamp]);
  addAudit({ context, action: "delivery.created", targetType: "delivery", targetId: id, metadata: { version, manifestPath } });
  void dispatchNotificationEvent({ context, eventKey: "delivery.created", payload: { deliveryId: id, version, targetId: id } });
  return { delivery: dbGet("SELECT * FROM deliveries WHERE id = ?", [id]), deliveries: deliveries(context) };
}

function deliveryForContext(context, deliveryId) {
  const project = requireProject(context);
  const delivery = dbGet(
    `SELECT * FROM deliveries
     WHERE id = ? AND organization_id = ? AND workspace_id = ? AND project_id = ?`,
    [deliveryId, context.organization.id, context.workspace.id, project.id]
  );
  if (!delivery) throw httpError(404, "delivery_not_found", "交付版本不存在或不属于当前项目", { deliveryId });
  return delivery;
}

function releasePayload(row) {
  if (!row) return null;
  return {
    ...row,
    channel: {
      id: row.channel_id,
      name: row.channel_name,
      kind: row.channel_kind,
      enabled: Boolean(row.channel_enabled),
      endpoint: row.channel_endpoint || "",
      requireApproval: Boolean(row.channel_require_approval)
    },
    preflight: parseJson(row.preflight_json, {}),
    result: parseJson(row.result_json, {})
  };
}

function releaseForContext(context, releaseId) {
  const project = requireProject(context);
  const release = dbGet(
    `SELECT dr.*, dc.name AS channel_name, dc.kind AS channel_kind, dc.enabled AS channel_enabled,
            dc.endpoint AS channel_endpoint, dc.require_approval AS channel_require_approval,
            d.version AS delivery_version, d.status AS delivery_status,
            requested.display_name AS requested_by_name, reviewed.display_name AS reviewed_by_name,
            published.display_name AS published_by_name
     FROM delivery_releases dr
     JOIN delivery_channels dc ON dc.id = dr.channel_id
     JOIN deliveries d ON d.id = dr.delivery_id
     LEFT JOIN users requested ON requested.id = dr.requested_by
     LEFT JOIN users reviewed ON reviewed.id = dr.reviewed_by
     LEFT JOIN users published ON published.id = dr.published_by
     WHERE dr.id = ? AND dr.organization_id = ? AND dr.workspace_id = ? AND dr.project_id = ?`,
    [releaseId, context.organization.id, context.workspace.id, project.id]
  );
  if (!release) throw httpError(404, "delivery_release_not_found", "发布记录不存在或不属于当前项目", { releaseId });
  return release;
}

function releasePreflight(context, delivery, channel, options = {}) {
  let blockers = [];
  const project = requireProject(context);
  if (!channel.enabled) blockers.push({ type: "channel", status: "disabled", reason: "发布渠道已停用" });
  if (delivery.status !== "approved") blockers.push({ type: "delivery", status: delivery.status, reason: "交付版本必须先通过交付审批" });
  const clearanceRecord = options.persistClearance
    ? persistDeliveryClearanceReport(context, delivery, { releaseId: options.releaseId || "", channel })
    : null;
  const clearance = clearanceRecord?.report || buildDeliveryClearanceReport(context, delivery, { releaseId: options.releaseId || "", channel });
  if (clearance.status === "blocked") blockers.push(...(clearance.blockers || []));
  blockers = dedupeBlockers(blockers);
  return {
    ok: blockers.length === 0,
    checkedAt: clearance.checkedAt || now(),
    projectId: project.id,
    deliveryId: delivery.id,
    channelId: channel.id,
    batchId: clearance.batch?.id || null,
    manifestPath: clearance.batch?.manifestPath || "",
    itemCount: clearance.batch?.itemCount || 0,
    blockers,
    clearance: {
      id: clearanceRecord?.id || "",
      schema: "ai-drama-platform.delivery-clearance-certificate.v1",
      status: clearance.status,
      certificateId: clearance.certificate?.certificateId || "",
      certificatePath: clearanceRecord?.certificatePath || "",
      counts: clearance.certificate?.counts || {}
    }
  };
}

export function listDeliveryReleases(context, deliveryId) {
  requireAnyPermission(context, ["delivery:view", "delivery:approve"]);
  deliveryForContext(context, deliveryId);
  return {
    deliveryId,
    releases: dbAll(
      `SELECT dr.*, dc.name AS channel_name, dc.kind AS channel_kind, dc.enabled AS channel_enabled,
              dc.endpoint AS channel_endpoint, dc.require_approval AS channel_require_approval,
              d.version AS delivery_version, d.status AS delivery_status,
              requested.display_name AS requested_by_name, reviewed.display_name AS reviewed_by_name,
              published.display_name AS published_by_name
       FROM delivery_releases dr
       JOIN delivery_channels dc ON dc.id = dr.channel_id
       JOIN deliveries d ON d.id = dr.delivery_id
       LEFT JOIN users requested ON requested.id = dr.requested_by
       LEFT JOIN users reviewed ON reviewed.id = dr.reviewed_by
       LEFT JOIN users published ON published.id = dr.published_by
       WHERE dr.delivery_id = ? AND dr.organization_id = ? AND dr.workspace_id = ? AND dr.project_id = ?
       ORDER BY dr.created_at DESC`,
      [deliveryId, context.organization.id, context.workspace.id, context.project.id]
    ).map(releasePayload)
  };
}

export function createDeliveryRelease(context, deliveryId, body = {}) {
  requirePermission(context, "delivery:approve");
  const delivery = deliveryForContext(context, deliveryId);
  const requestedChannelId = String(body.channelId || "").trim();
  const channel = requestedChannelId ? channelForContext(context, requestedChannelId) : ensureDefaultDeliveryChannel(context);
  const idempotencyKey = String(body.idempotencyKey || "").trim().slice(0, 160);
  if (idempotencyKey) {
    const existing = dbGet(
      `SELECT dr.*, dc.name AS channel_name, dc.kind AS channel_kind, dc.enabled AS channel_enabled,
              dc.endpoint AS channel_endpoint, dc.require_approval AS channel_require_approval,
              d.version AS delivery_version, d.status AS delivery_status,
              requested.display_name AS requested_by_name, reviewed.display_name AS reviewed_by_name,
              published.display_name AS published_by_name
       FROM delivery_releases dr
       JOIN delivery_channels dc ON dc.id = dr.channel_id
       JOIN deliveries d ON d.id = dr.delivery_id
       LEFT JOIN users requested ON requested.id = dr.requested_by
       LEFT JOIN users reviewed ON reviewed.id = dr.reviewed_by
       LEFT JOIN users published ON published.id = dr.published_by
       WHERE dr.organization_id = ? AND dr.project_id = ? AND dr.idempotency_key = ?`,
      [context.organization.id, context.project.id, idempotencyKey]
    );
    if (existing) return { release: releasePayload(existing), idempotent: true, ...listDeliveryReleases(context, deliveryId) };
  }
  const submit = body.submit !== false;
  const preflight = submit ? releasePreflight(context, delivery, channel, { persistClearance: true }) : { ok: false, checkedAt: now(), blockers: [{ type: "not_submitted", status: "draft", reason: "发布申请尚未提交" }] };
  if (submit && !preflight.ok) throw httpError(409, "release_blocked", "当前交付版本不满足发布申请条件", { blockers: preflight.blockers, preflight });
  const id = makeId("delivery-release");
  const timestamp = now();
  const status = submit ? "submitted" : "draft";
  dbRun(
    `INSERT INTO delivery_releases(
      id, organization_id, workspace_id, project_id, delivery_id, channel_id, status, idempotency_key,
      requested_by, requested_at, preflight_json, result_json, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '{}', ?, ?)`,
    [id, context.organization.id, context.workspace.id, context.project.id, delivery.id, channel.id, status, idempotencyKey, submit ? context.user.id : null, submit ? timestamp : null, JSON.stringify(preflight), timestamp, timestamp]
  );
  addAudit({ context, action: submit ? "delivery.release.submitted" : "delivery.release.created", targetType: "delivery_release", targetId: id, metadata: { deliveryId, channelId: channel.id, status, idempotencyKey: Boolean(idempotencyKey) } });
  void dispatchNotificationEvent({ context, eventKey: submit ? "delivery.release.submitted" : "delivery.release.created", payload: { releaseId: id, deliveryId, targetId: id, status } });
  return { release: releasePayload(releaseForContext(context, id)), ...listDeliveryReleases(context, deliveryId) };
}

export function decideDeliveryRelease(context, releaseId, body = {}) {
  requirePermission(context, "delivery:approve");
  const release = releaseForContext(context, releaseId);
  const status = String(body.status || "").trim();
  const note = String(body.note || "").trim().slice(0, 4000);
  const delivery = deliveryForContext(context, release.delivery_id);
  const channel = channelForContext(context, release.channel_id);
  let preflight = parseJson(release.preflight_json, {});
  if (status === "draft") {
    if (release.status !== "rejected") throw httpError(409, "release_transition_invalid", "只有已驳回的发布申请可以退回草稿");
    const timestamp = now();
    dbRun("UPDATE delivery_releases SET status = 'draft', decision_note = ?, reviewed_by = NULL, reviewed_at = NULL, updated_at = ? WHERE id = ?", [note, timestamp, releaseId]);
  } else if (status === "submitted") {
    if (!["draft", "rejected"].includes(release.status)) throw httpError(409, "release_transition_invalid", "只有草稿或已驳回的发布申请可以重新提交");
    preflight = releasePreflight(context, delivery, channel, { persistClearance: true, releaseId });
    if (!preflight.ok) throw httpError(409, "release_blocked", "发布申请仍未满足发布条件", { blockers: preflight.blockers, preflight });
    const timestamp = now();
    dbRun("UPDATE delivery_releases SET status = 'submitted', requested_by = ?, requested_at = ?, decision_note = ?, preflight_json = ?, updated_at = ? WHERE id = ?", [context.user.id, timestamp, note, JSON.stringify(preflight), timestamp, releaseId]);
  } else if (status === "approved") {
    if (release.status !== "submitted") throw httpError(409, "release_transition_invalid", "只有已提交的发布申请可以批准");
    preflight = releasePreflight(context, delivery, channel, { persistClearance: true, releaseId });
    if (!preflight.ok) throw httpError(409, "release_blocked", "发布申请审批被质量门阻断", { blockers: preflight.blockers, preflight });
    const timestamp = now();
    dbRun("UPDATE delivery_releases SET status = 'approved', reviewed_by = ?, reviewed_at = ?, decision_note = ?, preflight_json = ?, updated_at = ? WHERE id = ?", [context.user.id, timestamp, note, JSON.stringify(preflight), timestamp, releaseId]);
  } else if (status === "rejected") {
    if (release.status !== "submitted") throw httpError(409, "release_transition_invalid", "只有已提交的发布申请可以驳回");
    const timestamp = now();
    dbRun("UPDATE delivery_releases SET status = 'rejected', reviewed_by = ?, reviewed_at = ?, decision_note = ?, updated_at = ? WHERE id = ?", [context.user.id, timestamp, note || "审批人要求修改后重新提交", timestamp, releaseId]);
  } else {
    throw httpError(400, "release_status_invalid", "发布申请状态只能是 draft、submitted、approved 或 rejected");
  }
  addAudit({ context, action: `delivery.release.${status}`, targetType: "delivery_release", targetId: releaseId, result: status === "approved" ? "pass" : status, metadata: { deliveryId: release.delivery_id, channelId: release.channel_id, note } });
  void dispatchNotificationEvent({ context, eventKey: `delivery.release.${status}`, payload: { releaseId, deliveryId: release.delivery_id, targetId: releaseId, status } });
  return { release: releasePayload(releaseForContext(context, releaseId)), ...listDeliveryReleases(context, release.delivery_id) };
}

export async function publishDeliveryRelease(context, releaseId) {
  requirePermission(context, "delivery:approve");
  const release = releaseForContext(context, releaseId);
  if (release.status === "published") return { release: releasePayload(release), idempotent: true, ...listDeliveryReleases(context, release.delivery_id) };
  if (!["approved", "failed"].includes(release.status)) throw httpError(409, "release_transition_invalid", "只有已批准或上次发布失败的申请可以发布");
  const delivery = deliveryForContext(context, release.delivery_id);
  const channel = channelForContext(context, release.channel_id);
  const preflight = releasePreflight(context, delivery, channel, { persistClearance: true, releaseId });
  if (!preflight.ok) throw httpError(409, "release_blocked", "发布前复核未通过", { blockers: preflight.blockers, preflight });
  const outputDirectory = `storage/releases/${safePathSegment(context.project.id)}/${safePathSegment(delivery.version)}/${safePathSegment(release.id)}`;
  const releaseOutputPath = `${outputDirectory}/release.json`;
  const manifestOutputPath = `${outputDirectory}/delivery-manifest.json`;
  const timestamp = now();
  const releaseDocument = {
    schema: "ai-drama-platform.delivery-release.v1",
    publishedAt: timestamp,
    release: { id: release.id, status: "published", requestedBy: release.requested_by, reviewedBy: release.reviewed_by },
    delivery: { id: delivery.id, version: delivery.version, status: delivery.status, manifestPath: delivery.manifest_path },
    channel: { id: channel.id, name: channel.name, kind: channel.kind, endpoint: channel.kind === "local-file" ? channel.endpoint : "private-webhook" },
    batch: { id: preflight.batchId, manifestPath: preflight.manifestPath, itemCount: preflight.itemCount },
    clearanceCertificate: preflight.clearance,
    preflight
  };
  let result = { kind: channel.kind, outputPath: releaseOutputPath, manifestPath: manifestOutputPath, localOnly: true };
  try {
    const outputDirectoryTarget = safeStoragePath(`${outputDirectory}/release.json`);
    const manifestTarget = safeStoragePath(`${outputDirectory}/delivery-manifest.json`);
    const sourceManifest = safeStoragePath(preflight.manifestPath);
    if (!outputDirectoryTarget || !manifestTarget || !sourceManifest) throw httpError(500, "release_output_path_invalid", "发布输出路径不满足本地存储安全约束");
    mkdirSync(resolve(projectRoot, outputDirectory), { recursive: true });
    if (!existsSync(sourceManifest.absolute)) throw httpError(409, "release_manifest_missing", "发布时找不到当前批次 Manifest");
    writeFileSync(manifestTarget.absolute, readFileSync(sourceManifest.absolute));
    writeFileSync(outputDirectoryTarget.absolute, `${JSON.stringify(releaseDocument, null, 2)}\n`, "utf8");
    if (channel.kind === "local-webhook") {
      const authValue = channel.auth_env ? process.env[channel.auth_env] : "";
      if (channel.auth_env && !authValue) throw httpError(409, "delivery_channel_auth_missing", `本地 Webhook 所需环境变量未设置：${channel.auth_env}`);
      const headers = { "content-type": "application/json", accept: "application/json" };
      if (authValue) headers.authorization = `Bearer ${authValue}`;
      const controller = new AbortController();
      const timeout = setTimeout(() => controller.abort(), 10_000);
      try {
        const response = await fetch(channel.endpoint, { method: "POST", headers, body: JSON.stringify(releaseDocument), signal: controller.signal });
        const responseText = await response.text();
        if (!response.ok) throw httpError(502, "delivery_webhook_failed", `本地 Webhook 返回 HTTP ${response.status}`, { status: response.status, body: responseText.slice(0, 1000) });
        result = { ...result, webhookStatus: response.status, webhookBody: responseText.slice(0, 1000) };
      } finally {
        clearTimeout(timeout);
      }
    }
    dbRun("UPDATE delivery_releases SET status = 'published', published_by = ?, published_at = ?, output_path = ?, preflight_json = ?, result_json = ?, updated_at = ? WHERE id = ?", [context.user.id, timestamp, releaseOutputPath, JSON.stringify(preflight), JSON.stringify(result), timestamp, releaseId]);
    addUsage({ context, kind: "delivery_release_published", units: 1, unitName: "release", metadata: { releaseId, deliveryId: delivery.id, channelId: channel.id, channelKind: channel.kind } });
    addAudit({ context, action: "delivery.release.published", targetType: "delivery_release", targetId: releaseId, metadata: { deliveryId: delivery.id, channelId: channel.id, outputPath: releaseOutputPath, channelKind: channel.kind } });
    void dispatchNotificationEvent({ context, eventKey: "delivery.release.published", payload: { releaseId, deliveryId: delivery.id, targetId: releaseId, outputPath: releaseOutputPath } });
  } catch (error) {
    const failure = { kind: channel.kind, error: error.message, code: error.code || "delivery_release_publish_failed" };
    dbRun("UPDATE delivery_releases SET status = 'failed', result_json = ?, updated_at = ? WHERE id = ?", [JSON.stringify(failure), now(), releaseId]);
    addAudit({ context, action: "delivery.release.publish_failed", targetType: "delivery_release", targetId: releaseId, result: "error", metadata: { deliveryId: delivery.id, channelId: channel.id, error: error.message, code: error.code || "delivery_release_publish_failed" } });
    if (error.status) throw error;
    throw httpError(502, "delivery_release_publish_failed", `发布执行失败：${error.message}`);
  }
  return { release: releasePayload(releaseForContext(context, releaseId)), ...listDeliveryReleases(context, delivery.id) };
}

function deliveryBatchPayload(row) {
  return {
    ...row,
    source: parseJson(row.source_json, {}),
    result: parseJson(row.result_json, {}),
    items: dbAll(
      `SELECT dbi.*, s.title AS shot_title
       FROM delivery_batch_items dbi
       LEFT JOIN shots s ON s.id = dbi.shot_id
       WHERE dbi.batch_id = ?
       ORDER BY dbi.sequence_number`,
      [row.id]
    ).map((item) => ({ ...item, metadata: parseJson(item.metadata_json, {}) }))
  };
}

export function listDeliveryBatches(context, deliveryId) {
  requireAnyPermission(context, ["delivery:view", "delivery:approve"]);
  deliveryForContext(context, deliveryId);
  return {
    deliveryId,
    batches: dbAll(
      `SELECT db.*, u.display_name AS created_by_name
       FROM delivery_batches db
       LEFT JOIN users u ON u.id = db.created_by
       WHERE db.delivery_id = ?
       ORDER BY db.batch_number DESC`,
      [deliveryId]
    ).map(deliveryBatchPayload)
  };
}

export function createDeliveryBatch(context, deliveryId, body = {}) {
  requirePermission(context, "delivery:approve");
  const delivery = deliveryForContext(context, deliveryId);
  const compositionId = body.compositionId ? String(body.compositionId) : "";
  const composition = compositionId
    ? dbGet("SELECT * FROM media_compositions WHERE id = ? AND organization_id = ? AND workspace_id = ? AND project_id = ?", [compositionId, context.organization.id, context.workspace.id, context.project.id])
    : null;
  if (compositionId && !composition) throw httpError(404, "composition_not_found", "合成记录不存在或不属于当前项目", { compositionId });
  const latest = dbGet("SELECT MAX(batch_number) AS batch_number FROM delivery_batches WHERE delivery_id = ?", [deliveryId]);
  const batchNumber = Number(latest?.batch_number || 0) + 1;
  const id = makeId("delivery-batch");
  const timestamp = now();
  const shots = shotRows(context).map(shotPayload);
  const shotArtifacts = shots.map((shot) => ({ shot, artifact: latestArtifactForShot(context, shot.id, "video") }));
  const artifactBlockers = shotArtifacts
    .filter(({ artifact }) => !artifact || artifact.status !== "inspected" || !artifact.sha256 || !artifact.path)
    .map(({ shot, artifact }) => ({ shotId: shot.id, reason: artifact ? `视频文件${artifact.status === "missing" ? "不存在" : "未通过文件检查"}` : "没有已登记的视频媒体证据" }));
  const source = {
    schema: "ai-drama-platform.delivery-batch-source.v2",
    capturedAt: timestamp,
    deliveryId,
    deliveryVersion: delivery.version,
    compositionId: composition?.id || null,
    blockers: artifactBlockers,
    shots: shotArtifacts.map(({ shot, artifact }) => ({
      shotId: shot.id,
      versionId: shot.versionId || null,
      versionNumber: shot.versionNumber || 1,
      firstFrame: shot.firstFrame || "",
      lastFrame: artifact?.last_frame_path || shot.lastFrame || "",
      transitionFromPrevious: shot.transitionFromPrevious || "",
      artifactId: artifact?.id || null,
      sourcePath: artifact?.path || "",
      sourceSha256: artifact?.sha256 || "",
      mediaStatus: artifact?.status || "missing"
    }))
  };
  const manifestPath = String(body.manifestPath || `storage/deliveries/${context.project.id}/${delivery.version}/batches/batch-${batchNumber}/manifest.json`);
  if (!manifestPath.startsWith("storage/") || manifestPath.includes("..") || manifestPath.startsWith("/")) throw httpError(400, "manifest_path_invalid", "交付 manifest 必须写入 storage/ 下的安全相对路径");
  withTransaction(() => {
    dbRun(
      `INSERT INTO delivery_batches(
        id, organization_id, workspace_id, project_id, delivery_id, batch_number, label, status,
        manifest_path, composition_id, source_json, result_json, created_by, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, 'prepared', ?, ?, ?, ?, ?, ?, ?)`,
      [id, context.organization.id, context.workspace.id, context.project.id, deliveryId, batchNumber, String(body.label || `批次 ${batchNumber}`), manifestPath, composition?.id || null, JSON.stringify(source), JSON.stringify({ blockers: artifactBlockers, artifactCount: shotArtifacts.filter(({ artifact }) => artifact).length }), context.user.id, timestamp, timestamp]
    );
    for (let index = 0; index < shotArtifacts.length; index += 1) {
      const { shot, artifact } = shotArtifacts[index];
      dbRun(
        `INSERT INTO delivery_batch_items(
          id, batch_id, shot_id, sequence_number, source_path, actual_last_frame_path, source_sha256, metadata_json, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [makeId("delivery-item"), id, shot.id, index + 1, String(artifact?.path || ""), String(artifact?.last_frame_path || shot.lastFrame || ""), String(artifact?.sha256 || ""), JSON.stringify({ versionId: shot.versionId || null, versionNumber: shot.versionNumber || 1, artifactId: artifact?.id || null, artifactStatus: artifact?.status || "missing", durationSec: artifact?.durationSec || 0, width: artifact?.width || 0, height: artifact?.height || 0 }), timestamp]
      );
    }
  });
  const manifest = {
    schema: "ai-drama-platform.delivery-manifest.v2",
    generatedAt: timestamp,
    delivery: { id: delivery.id, version: delivery.version, channel: delivery.channel },
    batch: { id, number: batchNumber, label: String(body.label || `批次 ${batchNumber}`), compositionId: composition?.id || null },
    blockers: artifactBlockers,
    items: source.shots
  };
  mkdirSync(resolve(projectRoot, manifestPath, ".."), { recursive: true });
  writeFileSync(resolve(projectRoot, manifestPath), `${JSON.stringify(manifest, null, 2)}\n`, "utf8");
  dbRun("UPDATE delivery_batches SET result_json = ? WHERE id = ?", [JSON.stringify({ blockers: artifactBlockers, artifactCount: shotArtifacts.filter(({ artifact }) => artifact).length, manifestWritten: true }), id]);
  addAudit({ context, action: "delivery.batch.created", targetType: "delivery_batch", targetId: id, metadata: { deliveryId, batchNumber, shotCount: shots.length, manifestPath, artifactBlockers: artifactBlockers.length } });
  return { batch: deliveryBatchPayload(dbGet("SELECT * FROM delivery_batches WHERE id = ?", [id])), batches: listDeliveryBatches(context, deliveryId).batches };
}

export function activateDeliveryBatch(context, batchId) {
  requirePermission(context, "delivery:approve");
  const project = requireProject(context);
  const batch = dbGet("SELECT * FROM delivery_batches WHERE id = ? AND organization_id = ? AND workspace_id = ? AND project_id = ?", [batchId, context.organization.id, context.workspace.id, project.id]);
  if (!batch) throw httpError(404, "delivery_batch_not_found", "交付批次不存在或不属于当前项目", { batchId });
  if (batch.status === "rolled_back") throw httpError(409, "delivery_batch_rolled_back", "已回滚批次不能重新激活");
  const timestamp = now();
  withTransaction(() => {
    dbRun("UPDATE delivery_batches SET status = 'superseded', updated_at = ? WHERE delivery_id = ? AND status = 'active'", [timestamp, batch.delivery_id]);
    dbRun("UPDATE delivery_batches SET status = 'active', updated_at = ? WHERE id = ?", [timestamp, batchId]);
    dbRun("UPDATE deliveries SET active_batch_id = ?, updated_at = ? WHERE id = ?", [batchId, timestamp, batch.delivery_id]);
  });
  addAudit({ context, action: "delivery.batch.activated", targetType: "delivery_batch", targetId: batchId, metadata: { deliveryId: batch.delivery_id, batchNumber: batch.batch_number } });
  return { batch: deliveryBatchPayload(dbGet("SELECT * FROM delivery_batches WHERE id = ?", [batchId])), batches: listDeliveryBatches(context, batch.delivery_id).batches };
}

export function rollbackDeliveryBatch(context, batchId) {
  requirePermission(context, "delivery:approve");
  const project = requireProject(context);
  const batch = dbGet("SELECT * FROM delivery_batches WHERE id = ? AND organization_id = ? AND workspace_id = ? AND project_id = ?", [batchId, context.organization.id, context.workspace.id, project.id]);
  if (!batch) throw httpError(404, "delivery_batch_not_found", "交付批次不存在或不属于当前项目", { batchId });
  if (batch.status !== "active") throw httpError(409, "delivery_batch_not_active", "只有当前生效批次可以回滚", { status: batch.status });
  const previous = dbGet("SELECT * FROM delivery_batches WHERE delivery_id = ? AND batch_number < ? AND status IN ('superseded', 'prepared', 'active') ORDER BY batch_number DESC LIMIT 1", [batch.delivery_id, batch.batch_number]);
  if (!previous) throw httpError(409, "delivery_batch_no_rollback_target", "当前批次没有可恢复的历史批次");
  const timestamp = now();
  withTransaction(() => {
    dbRun("UPDATE delivery_batches SET status = 'rolled_back', rollback_of_batch_id = ?, updated_at = ? WHERE id = ?", [previous.id, timestamp, batch.id]);
    dbRun("UPDATE delivery_batches SET status = 'active', updated_at = ? WHERE id = ?", [timestamp, previous.id]);
    dbRun("UPDATE deliveries SET active_batch_id = ?, updated_at = ? WHERE id = ?", [previous.id, timestamp, batch.delivery_id]);
  });
  addAudit({ context, action: "delivery.batch.rolled_back", targetType: "delivery_batch", targetId: batchId, metadata: { deliveryId: batch.delivery_id, fromBatch: batch.batch_number, toBatch: previous.batch_number, restoredBatchId: previous.id } });
  return { restoredBatch: deliveryBatchPayload(dbGet("SELECT * FROM delivery_batches WHERE id = ?", [previous.id])), batches: listDeliveryBatches(context, batch.delivery_id).batches };
}

export function approveDelivery(context, deliveryId, body = {}) {
  requirePermission(context, "delivery:approve");
  const project = requireProject(context);
  const delivery = dbGet("SELECT * FROM deliveries WHERE id = ? AND project_id = ?", [deliveryId, project.id]);
  if (!delivery) throw httpError(404, "delivery_not_found", "交付版本不存在或不属于当前项目", { deliveryId });
  const reviews = ensureReviews(context);
  const reviewBlockers = reviews.filter((review) => review.status !== "approved").map((review) => ({ id: review.id, type: "review", lane: review.lane, status: review.status, shotId: review.shot_id }));
  const jobBlockers = dbAll("SELECT id, kind, status, shot_id FROM generation_jobs WHERE project_id = ? AND status NOT IN ('completed', 'cancelled') ORDER BY created_at", [project.id]).map((job) => ({ id: job.id, type: "job", kind: job.kind, status: job.status, shotId: job.shot_id }));
  const activeBatch = delivery.active_batch_id ? dbGet("SELECT * FROM delivery_batches WHERE id = ? AND delivery_id = ?", [delivery.active_batch_id, delivery.id]) : null;
  const batchBlockers = !activeBatch
    ? [{ id: delivery.id, type: "delivery_batch", status: "missing", reason: "交付版本尚未激活一个交付批次" }]
    : dbAll("SELECT id, shot_id, source_path, actual_last_frame_path, source_sha256, metadata_json FROM delivery_batch_items WHERE batch_id = ? ORDER BY sequence_number", [activeBatch.id])
      .flatMap((item) => {
        const metadata = parseJson(item.metadata_json, {});
        const missing = [];
        if (!item.source_path || !item.source_sha256 || metadata.artifactStatus !== "inspected") missing.push("视频文件证据");
        if (!item.actual_last_frame_path) missing.push("实际末帧");
        return missing.length ? [{ id: item.id, type: "delivery_batch_item", shotId: item.shot_id, status: "blocked", reason: missing.join("、") }] : [];
      });
  const clearance = persistDeliveryClearanceReport(context, delivery);
  const clearanceBlockers = clearance.report?.blockers || [];
  const blockers = dedupeBlockers([...reviewBlockers, ...jobBlockers, ...batchBlockers, ...clearanceBlockers]);
  if (clearanceBlockers.length || (blockers.length && !body.force)) throw httpError(409, "delivery_blocked", "交付版本未满足质检、权利清算或媒体证据要求，不能批准交付", { blockers, clearance: clearance.report, clearanceId: clearance.id });
  const timestamp = now();
  dbRun("UPDATE deliveries SET status = 'approved', approved_by = ?, approved_at = ?, updated_at = ? WHERE id = ?", [context.user.id, timestamp, timestamp, deliveryId]);
  addAudit({ context, action: "delivery.approved", targetType: "delivery", targetId: deliveryId, metadata: { forced: Boolean(body.force), blockers: blockers.length, clearanceId: clearance.id, clearanceStatus: clearance.report?.status || "" } });
  void dispatchNotificationEvent({ context, eventKey: "delivery.approved", payload: { deliveryId, version: delivery.version, targetId: deliveryId, forced: Boolean(body.force) } });
  return { delivery: dbGet("SELECT * FROM deliveries WHERE id = ?", [deliveryId]), deliveries: deliveries(context), blockers };
}

function scopedJob(context, jobId) {
  const project = requireProject(context);
  const job = dbGet("SELECT * FROM generation_jobs WHERE id = ? AND organization_id = ? AND workspace_id = ? AND project_id = ?", [jobId, context.organization.id, context.workspace.id, project.id]);
  if (!job) throw httpError(404, "job_not_found", "任务不存在或不属于当前项目", { jobId });
  return job;
}

export function updateJob(context, jobId, action, body = {}) {
  const job = scopedJob(context, jobId);
  if (action === "priority") {
    requireAnyPermission(context, ["job:prioritize", "queue:manage"]);
    requireProjectWritable(context);
    const priority = Number(body.priority);
    if (!Number.isFinite(priority) || priority < 1 || priority > 100) throw httpError(400, "priority_invalid", "优先级必须在 1 到 100 之间");
    dbRun("UPDATE generation_jobs SET priority = ?, updated_at = ? WHERE id = ?", [priority, now(), jobId]);
    addAudit({ context, action: "generation_job.priority.updated", targetType: "generation_job", targetId: jobId, metadata: { previous: job.priority, priority } });
  } else if (action === "retry") {
    requireAnyPermission(context, ["job:prioritize", "queue:manage"]);
    requireProjectWritable(context);
    if (!["failed", "cancelled"].includes(job.status)) throw httpError(409, "job_not_retryable", "只有失败或已取消任务可以重试", { status: job.status });
    const timestamp = now();
    const attempt = Number(dbGet("SELECT MAX(attempt_number) AS attempt_number FROM job_attempts WHERE job_id = ?", [jobId])?.attempt_number || 0) + 1;
    dbRun("UPDATE generation_jobs SET status = 'queued', qa_status = 'wait', updated_at = ? WHERE id = ?", [timestamp, jobId]);
    dbRun("INSERT INTO job_attempts(id, job_id, attempt_number, runner_id, status, created_at) VALUES (?, ?, ?, 'local-runner', 'queued', ?)", [makeId("attempt"), jobId, attempt, timestamp]);
    addAudit({ context, action: "generation_job.retried", targetType: "generation_job", targetId: jobId, metadata: { attempt } });
  } else if (action === "cancel") {
    requireAnyPermission(context, ["job:prioritize", "queue:manage"]);
    requireProjectWritable(context);
    if (!["queued", "running"].includes(job.status)) throw httpError(409, "job_not_cancellable", "当前任务状态不能取消", { status: job.status });
    dbRun("UPDATE generation_jobs SET status = 'cancelled', updated_at = ? WHERE id = ?", [now(), jobId]);
    addAudit({ context, action: "generation_job.cancelled", targetType: "generation_job", targetId: jobId });
  } else {
    throw httpError(400, "job_action_invalid", "不支持的任务动作", { action });
  }
  return { job: dbGet("SELECT * FROM generation_jobs WHERE id = ?", [jobId]) };
}

export function exportAudit(context, limit = 500) {
  requirePermission(context, "audit:view");
  return dbAll(
    `SELECT a.*, u.display_name AS actor_name
     FROM audit_logs a LEFT JOIN users u ON u.id = a.actor_user_id
     WHERE a.organization_id = ? AND (a.workspace_id IS NULL OR a.workspace_id = ?) AND (a.project_id IS NULL OR a.project_id = ?)
     ORDER BY a.created_at DESC LIMIT ?`,
    [context.organization.id, context.workspace.id, context.project?.id || "", limit]
  ).map((item) => ({ ...item, metadata: parseJson(item.metadata_json, {}) }));
}