import { mkdir, readFile } from "node:fs/promises";
import { resolve } from "node:path";
import { DatabaseSync } from "node:sqlite";
import { randomBytes, scryptSync } from "node:crypto";
import { sampleProject, workflowTemplates } from "../src/data/sampleProject.js";
import { apiClientStorageMarker, hashApiClientKey } from "./api-client-secrets.mjs";

const serverRoot = resolve(import.meta.dirname);
const projectRoot = resolve(serverRoot, "..");
const dataRoot = resolve(projectRoot, "data");
export const dbPath = resolve(process.env.AI_DRAMA_DB_PATH || dataRoot, process.env.AI_DRAMA_DB_PATH ? "" : "platform.sqlite");

await mkdir(process.env.AI_DRAMA_DB_PATH ? resolve(dbPath, "..") : dataRoot, { recursive: true });

const schema = await readFile(resolve(serverRoot, "schema.sql"), "utf8");
export const db = new DatabaseSync(dbPath);
db.exec("PRAGMA busy_timeout = 5000");
db.exec("PRAGMA journal_mode = WAL");
db.exec(schema);

function ensureColumn(table, column, definition) {
  const columns = db.prepare(`PRAGMA table_info(${table})`).all();
  if (!columns.some((item) => item.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  }
}

// Keep the seeded local SQLite database compatible with new platform features.
ensureColumn("model_connectors", "last_probe_at", "TEXT");
ensureColumn("model_connectors", "latency_ms", "INTEGER");
ensureColumn("model_connectors", "error_message", "TEXT NOT NULL DEFAULT ''");
ensureColumn("model_connectors", "protocol_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("model_connectors", "auth_env", "TEXT NOT NULL DEFAULT ''");
ensureColumn("generation_jobs", "request_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("generation_jobs", "result_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("generation_jobs", "error_message", "TEXT NOT NULL DEFAULT ''");
ensureColumn("generation_jobs", "max_attempts", "INTEGER NOT NULL DEFAULT 3");
ensureColumn("generation_jobs", "model_route_approval_id", "TEXT");
ensureColumn("generation_jobs", "next_run_at", "TEXT");
ensureColumn("generation_jobs", "leased_by", "TEXT");
ensureColumn("generation_jobs", "leased_at", "TEXT");
ensureColumn("generation_jobs", "started_at", "TEXT");
ensureColumn("generation_jobs", "finished_at", "TEXT");
ensureColumn("billing_accounts", "billing_cycle", "TEXT NOT NULL DEFAULT 'monthly'");
ensureColumn("billing_accounts", "currency", "TEXT NOT NULL DEFAULT 'CNY'");
ensureColumn("billing_accounts", "base_fee", "REAL NOT NULL DEFAULT 0");
ensureColumn("billing_accounts", "seat_unit_price", "REAL NOT NULL DEFAULT 0");
ensureColumn("billing_accounts", "storage_unit_price", "REAL NOT NULL DEFAULT 0");
ensureColumn("billing_accounts", "clip_unit_price", "REAL NOT NULL DEFAULT 0");
ensureColumn("billing_accounts", "quota_warning_percent", "INTEGER NOT NULL DEFAULT 80");
ensureColumn("billing_accounts", "current_period_start", "TEXT");
ensureColumn("billing_accounts", "current_period_end", "TEXT");
ensureColumn("billing_accounts", "next_invoice_at", "TEXT");
db.exec("CREATE INDEX IF NOT EXISTS idx_jobs_dispatch ON generation_jobs(status, next_run_at, priority, created_at)");
ensureColumn("series", "show_engine", "TEXT NOT NULL DEFAULT ''");
ensureColumn("episodes", "target_duration_sec", "REAL NOT NULL DEFAULT 0");
ensureColumn("episodes", "hook", "TEXT NOT NULL DEFAULT ''");
ensureColumn("episodes", "cliffhanger", "TEXT NOT NULL DEFAULT ''");
ensureColumn("api_clients", "organization_id", "TEXT");
ensureColumn("api_clients", "workspace_id", "TEXT");
ensureColumn("api_clients", "client_key_hash", "TEXT");
ensureColumn("api_clients", "client_key_prefix", "TEXT NOT NULL DEFAULT ''");
ensureColumn("api_clients", "key_version", "INTEGER NOT NULL DEFAULT 1");
ensureColumn("invitations", "token_hash", "TEXT");
ensureColumn("invitations", "token_hint", "TEXT");
ensureColumn("invitations", "accepted_user_id", "TEXT");
ensureColumn("invitations", "accepted_at", "TEXT");
ensureColumn("invitations", "revoked_at", "TEXT");
ensureColumn("identity_providers", "organization_id", "TEXT");
ensureColumn("identity_providers", "workspace_id", "TEXT");
ensureColumn("identity_providers", "jwks_url", "TEXT NOT NULL DEFAULT ''");
ensureColumn("identity_providers", "entry_point", "TEXT NOT NULL DEFAULT ''");
ensureColumn("identity_providers", "idp_cert_ref", "TEXT NOT NULL DEFAULT ''");
ensureColumn("identity_providers", "sp_issuer", "TEXT NOT NULL DEFAULT ''");
ensureColumn("identity_providers", "audience", "TEXT NOT NULL DEFAULT ''");
ensureColumn("identity_providers", "saml_name_id_format", "TEXT NOT NULL DEFAULT 'urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress'");
ensureColumn("identity_providers", "want_assertions_signed", "INTEGER NOT NULL DEFAULT 1");
ensureColumn("identity_providers", "want_authn_response_signed", "INTEGER NOT NULL DEFAULT 1");
ensureColumn("identity_providers", "validate_in_response_to", "TEXT NOT NULL DEFAULT 'ifPresent'");
ensureColumn("identity_providers", "auto_provision", "INTEGER NOT NULL DEFAULT 1");
ensureColumn("identity_providers", "default_role_key", "TEXT NOT NULL DEFAULT 'org_member'");
ensureColumn("identity_providers", "default_workspace_role_key", "TEXT NOT NULL DEFAULT 'writer'");
ensureColumn("asset_versions", "file_name", "TEXT NOT NULL DEFAULT ''");
ensureColumn("asset_versions", "mime_type", "TEXT NOT NULL DEFAULT 'application/octet-stream'");
ensureColumn("asset_versions", "file_size", "INTEGER NOT NULL DEFAULT 0");
ensureColumn("asset_versions", "content_sha256", "TEXT NOT NULL DEFAULT ''");
ensureColumn("asset_versions", "provenance_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("asset_versions", "risk_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("asset_versions", "tags_json", "TEXT NOT NULL DEFAULT '[]'");
ensureColumn("asset_versions", "license_scope", "TEXT NOT NULL DEFAULT ''");
ensureColumn("asset_versions", "expires_at", "TEXT");
ensureColumn("shots", "current_version_id", "TEXT");
ensureColumn("deliveries", "active_batch_id", "TEXT");
ensureColumn("delivery_releases", "idempotency_key", "TEXT NOT NULL DEFAULT ''");
ensureColumn("delivery_releases", "preflight_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("delivery_releases", "result_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("script_documents", "metadata_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("projects", "archived_at", "TEXT");
ensureColumn("projects", "archived_by", "TEXT");
ensureColumn("projects", "archived_from_status", "TEXT");
ensureColumn("projects", "template_id", "TEXT NOT NULL DEFAULT 'ai-manhua-drama'");
ensureColumn("workspace_members", "access_mode", "TEXT NOT NULL DEFAULT 'all'");
ensureColumn("auth_sessions", "device_id", "TEXT");
ensureColumn("auth_sessions", "risk_level", "TEXT NOT NULL DEFAULT 'medium'");
ensureColumn("auth_sessions", "risk_score", "INTEGER NOT NULL DEFAULT 50");
ensureColumn("knowledge_documents", "rights_status", "TEXT NOT NULL DEFAULT 'needs-evidence'");
ensureColumn("knowledge_documents", "provenance_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("knowledge_documents", "tags_json", "TEXT NOT NULL DEFAULT '[]'");
ensureColumn("knowledge_documents", "risk_json", "TEXT NOT NULL DEFAULT '{}'");
ensureColumn("knowledge_documents", "current_version_number", "INTEGER NOT NULL DEFAULT 1");
db.exec("CREATE INDEX IF NOT EXISTS idx_projects_lifecycle ON projects(workspace_id, status, updated_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_invites_token ON invitations(token_hash, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_identity_providers_org ON identity_providers(organization_id, enabled, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_oidc_login_states_hash ON oidc_login_states(state_hash, expires_at, consumed_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_saml_login_states_relay ON saml_login_states(relay_state_hash, expires_at, consumed_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_saml_login_states_request ON saml_login_states(request_id, expires_at, consumed_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_external_identities_user ON external_identities(user_id, provider_id)");
db.exec("CREATE INDEX IF NOT EXISTS idx_auth_sso_tickets_hash ON auth_sso_tickets(ticket_hash, expires_at, consumed_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_api_clients_key_hash ON api_clients(client_key_hash, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_auth_security_events_user_created ON auth_security_events(user_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_auth_security_events_type_created ON auth_security_events(event_type, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_auth_devices_user_last_seen ON auth_devices(user_id, last_seen_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_auth_devices_key_hash ON auth_devices(user_id, device_key_hash)");
db.exec("CREATE INDEX IF NOT EXISTS idx_auth_sessions_device ON auth_sessions(device_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_channels_scope ON delivery_channels(organization_id, workspace_id, project_id, enabled, created_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_releases_scope ON delivery_releases(organization_id, workspace_id, project_id, delivery_id, status, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_releases_idempotency ON delivery_releases(organization_id, project_id, idempotency_key)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_clearance_reports_scope ON delivery_clearance_reports(organization_id, workspace_id, project_id, delivery_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_clearance_reports_release ON delivery_clearance_reports(release_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_access_links_token ON delivery_access_links(token_hash, status, expires_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_access_links_scope ON delivery_access_links(organization_id, workspace_id, project_id, release_id, status, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_access_events_link ON delivery_access_events(link_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_access_events_release ON delivery_access_events(release_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_access_feedback_link ON delivery_access_feedback(link_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_delivery_access_feedback_release ON delivery_access_feedback(release_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_model_route_approvals_scope ON model_route_approval_requests(organization_id, workspace_id, status, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_model_route_approvals_requester ON model_route_approval_requests(requester_user_id, status, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_model_route_approvals_job ON model_route_approval_requests(job_id, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_generation_jobs_model_approval ON generation_jobs(model_route_approval_id)");
db.exec("CREATE INDEX IF NOT EXISTS idx_commercial_approvals_org_status ON commercial_approval_requests(organization_id, status, updated_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_commercial_approvals_requester ON commercial_approval_requests(requester_user_id, status, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_commercial_approvals_target ON commercial_approval_requests(organization_id, request_type, target_key, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_organization_policies_org_category ON organization_policies(organization_id, category, policy_key)");
db.exec("CREATE INDEX IF NOT EXISTS idx_organization_policy_evaluations_scope ON organization_policy_evaluations(organization_id, workspace_id, project_id, result, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_customers_org_status ON customers(organization_id, status, updated_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_customer_contacts_customer ON customer_contacts(customer_id, is_primary DESC, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_production_contracts_org_status ON production_contracts(organization_id, status, approval_status, updated_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_production_contracts_customer ON production_contracts(customer_id, status, updated_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_contract_project_bindings_project ON contract_project_bindings(organization_id, workspace_id, project_id, status)");
db.exec("CREATE INDEX IF NOT EXISTS idx_contract_project_bindings_contract ON contract_project_bindings(contract_id, status, updated_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_contract_license_events_scope ON contract_license_events(organization_id, contract_id, project_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_asset_versions_governance ON asset_versions(asset_id, rights_status, expires_at)");
db.exec("CREATE INDEX IF NOT EXISTS idx_asset_governance_reviews_asset ON asset_governance_reviews(asset_id, created_at DESC)");
db.exec("CREATE INDEX IF NOT EXISTS idx_asset_governance_reviews_scope ON asset_governance_reviews(organization_id, workspace_id, project_id, risk_status, created_at DESC)");

function migrateApiClientSecrets() {
  const rows = dbAll("SELECT id, client_key, client_key_hash, client_key_prefix, key_version FROM api_clients");
  for (const row of rows) {
    if (row.client_key_hash && row.client_key_prefix && Number(row.key_version || 0) >= 2) continue;
    const storedValue = String(row.client_key || "");
    const existingHash = String(row.client_key_hash || "");
    const hash = /^[a-f0-9]{64}$/i.test(existingHash)
      ? existingHash.toLowerCase()
      : storedValue.startsWith("hash:") && /^[a-f0-9]{64}$/i.test(storedValue.slice(5))
        ? storedValue.slice(5).toLowerCase()
        : hashApiClientKey(storedValue);
    const prefix = row.client_key_prefix || (storedValue.startsWith("hash:") ? "local-" : storedValue.slice(0, 12));
    dbRun("UPDATE api_clients SET client_key = ?, client_key_hash = ?, client_key_prefix = ?, key_version = 2 WHERE id = ?", [apiClientStorageMarker(hash), hash, prefix, row.id]);
  }
}

migrateApiClientSecrets();

const now = () => new Date().toISOString();
const isoDaysFromNow = (days) => new Date(Date.now() + days * 86400000).toISOString();
const monthStart = () => {
  const date = new Date();
  date.setUTCDate(1);
  date.setUTCHours(0, 0, 0, 0);
  return date.toISOString();
};
const monthEnd = () => {
  const date = new Date();
  date.setUTCMonth(date.getUTCMonth() + 1, 0);
  date.setUTCHours(23, 59, 59, 999);
  return date.toISOString();
};

export function dbGet(sql, params = []) {
  return db.prepare(sql).get(...params) || null;
}

export function dbAll(sql, params = []) {
  return db.prepare(sql).all(...params);
}

export function dbRun(sql, params = []) {
  return db.prepare(sql).run(...params);
}

export function createPasswordRecord(password) {
  const salt = randomBytes(16).toString("hex");
  const hash = scryptSync(String(password), salt, 64).toString("hex");
  return { salt, hash };
}

export function passwordHash(password, salt) {
  return scryptSync(String(password), salt, 64).toString("hex");
}

export function withTransaction(callback) {
  db.exec("BEGIN IMMEDIATE");
  try {
    const result = callback();
    db.exec("COMMIT");
    return result;
  } catch (error) {
    db.exec("ROLLBACK");
    throw error;
  }
}

function insertIgnore(sql, params) {
  dbRun(sql, params);
}

function seedRoles() {
  const roles = [
    ["org_owner", "organization", "组织所有者", "组织全局管理、账单、成员和模型策略"],
    ["org_admin", "organization", "组织管理员", "组织成员、工作区、模型和审计管理"],
    ["org_member", "organization", "组织成员", "组织内的基础成员身份，具体能力由工作区/项目角色决定"],
    ["producer", "workspace", "制片", "项目、批次、队列、成本和交付管理"],
    ["writer", "workspace", "编剧", "剧本、分集、对白和镜头草稿"],
    ["art_director", "workspace", "资产美术", "角色、场景、道具、prompt 和连续性"],
    ["voice_editor", "workspace", "配音/字幕", "固定声线、TTS、字幕和 ASR"],
    ["reviewer", "workspace", "审片", "QA、审片意见和通过/驳回"],
    ["project_guest", "workspace", "项目受限成员", "只访问被授权项目，不继承工作区其他项目"],
    ["project_editor", "project", "项目编辑", "指定项目的内容编辑"],
    ["project_viewer", "project", "项目查看者", "只读查看和下载授权交付物"]
  ];
  for (const role of roles) {
    insertIgnore("INSERT OR IGNORE INTO roles(key, scope, name, description) VALUES (?, ?, ?, ?)", role);
  }

  const permissions = [
    ["organization:manage", "修改组织设置"],
    ["organization:members:invite", "邀请组织成员"],
    ["organization:roles:manage", "管理组织角色权限策略"],
    ["policy:read", "查看组织生产、安全和合规策略"],
    ["policy:manage", "管理组织生产、安全和合规策略"],
    ["workspace:create", "创建工作区"],
    ["workspace:manage", "管理工作区设置"],
    ["workspace:members:manage", "管理工作区成员"],
    ["project:create", "创建项目"],
    ["project:manage", "管理项目设置"],
    ["project:members:manage", "管理项目成员"],
    ["workflow:manage", "管理生产流程模板和版本"],
    ["task:view", "查看项目协作任务"],
    ["task:manage", "创建、分派和管理项目协作任务"],
    ["task:complete", "更新本人负责的协作任务状态"],
    ["style:read", "查看组织、工作区和项目风格生产标准"],
    ["style:manage", "管理风格标准、品牌规范和项目绑定"],
    ["customer:read", "查看客户主档、联系人和项目归属"],
    ["customer:manage", "管理客户主档、联系人和客户状态"],
    ["contract:read", "查看合同授权、项目绑定和商业放行"],
    ["contract:manage", "管理合同授权、项目绑定和商业放行"],
    ["script:edit", "编辑剧本和对白"],
    ["script:read", "查看剧本、分集和镜头"],
    ["asset:edit", "编辑角色、场景和道具锁"],
    ["prompt:edit", "编辑生成提示"],
    ["voice:edit", "编辑固定声线和字幕"],
    ["voice:approve", "审批角色参考音频和声音授权"],
    ["job:create", "创建生成任务"],
    ["job:prioritize", "调整任务优先级"],
    ["model:manage", "注册和管理模型连接器"],
    ["model:approve", "审批模型路由、外部连接器和一次性生成调用"],
    ["usage:view", "查看用量和成本"],
    ["billing:manage", "管理套餐和账单"],
    ["quota:manage", "管理组织席位与工作区配额"],
    ["qa:review", "执行审片和 QA"],
    ["delivery:approve", "批准交付版本"],
    ["delivery:view", "查看交付版本和导出物"],
    ["compliance:manage", "管理合规策略和证据"],
    ["audit:view", "查看审计日志"],
    ["system:settings:view", "查看系统配置"],
    ["system:settings:edit", "修改系统配置"],
    ["feature_flag:manage", "管理功能开关"],
    ["service:health:view", "查看服务健康状态"],
    ["api_client:manage", "管理 API 客户端"],
    ["notification:manage", "管理通知渠道"],
    ["queue:manage", "管理全局任务队列"],
    ["runner:manage", "管理本地 Runner"]
  ];
  for (const permission of permissions) {
    insertIgnore("INSERT OR IGNORE INTO permissions(key, description) VALUES (?, ?)", permission);
  }

  const rolePermissions = {
    org_owner: permissions.map(([key]) => key),
    org_admin: [
      "organization:manage", "organization:members:invite", "workspace:create", "workspace:manage",
      "workspace:members:manage", "project:create", "project:manage", "project:members:manage",
      "workflow:manage", "task:view", "task:manage", "task:complete", "policy:read", "policy:manage", "style:read", "style:manage", "customer:read", "customer:manage", "contract:read", "contract:manage", "model:manage", "model:approve", "usage:view", "billing:manage", "quota:manage", "qa:review", "delivery:approve", "delivery:view", "compliance:manage", "audit:view", "queue:manage", "voice:approve",
      "system:settings:view", "service:health:view", "organization:roles:manage"
    ],
    producer: ["project:create", "project:manage", "project:members:manage", "workflow:manage", "task:view", "task:manage", "task:complete", "policy:read", "style:read", "style:manage", "customer:read", "contract:read", "script:read", "job:create", "job:prioritize", "usage:view", "delivery:approve", "delivery:view", "voice:approve"],
    writer: ["script:read", "script:edit", "task:view", "task:complete", "policy:read", "style:read", "job:create"],
    art_director: ["asset:edit", "prompt:edit", "task:view", "task:complete", "policy:read", "style:read", "style:manage", "job:create"],
    voice_editor: ["voice:edit", "voice:approve", "task:view", "task:complete", "policy:read", "style:read", "job:create"],
    reviewer: ["script:read", "task:view", "task:complete", "policy:read", "style:read", "qa:review", "voice:approve", "delivery:view"],
    project_guest: ["script:read", "task:view", "style:read", "delivery:view"],
    project_editor: ["script:read", "script:edit", "asset:edit", "prompt:edit", "voice:edit", "task:view", "task:manage", "task:complete", "style:read", "job:create", "delivery:view"],
    project_viewer: ["script:read", "task:view", "style:read", "delivery:view"]
  };
  for (const [roleKey, permissionKeys] of Object.entries(rolePermissions)) {
    for (const permissionKey of permissionKeys) {
      insertIgnore("INSERT OR IGNORE INTO role_permissions(role_key, permission_key) VALUES (?, ?)", [roleKey, permissionKey]);
    }
  }
}

function seedUsers() {
  const timestamp = now();
  const users = [
    ["u-owner", "林制片", "producer@local.test", "#d97757", "active"],
    ["u-producer", "周制片", "producer2@local.test", "#3f7f87", "active"],
    ["u-writer", "白编剧", "writer@local.test", "#a77646", "active"],
    ["u-art", "沈美术", "art@local.test", "#8e6a9f", "active"],
    ["u-voice", "许配音", "voice@local.test", "#477d69", "active"],
    ["u-review", "顾审片", "review@local.test", "#9a6b51", "active"],
    ["u-local-worker", "本地 Worker", "local-worker@system.invalid", "#58656b", "suspended"]
  ];
  for (const [id, displayName, email, color, status] of users) {
    insertIgnore("INSERT OR IGNORE INTO users(id, display_name, email, avatar_color, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)", [id, displayName, email, color, status, timestamp, timestamp]);
  }
}

function seedCredentials() {
  const timestamp = now();
  const demoPassword = "Demo@123456";
  const users = dbAll("SELECT id FROM users WHERE status = 'active'");
  for (const user of users) {
    const existing = dbGet("SELECT user_id FROM user_credentials WHERE user_id = ?", [user.id]);
    if (!existing) {
      const record = createPasswordRecord(demoPassword);
      insertIgnore("INSERT INTO user_credentials(user_id, password_salt, password_hash, created_at, updated_at) VALUES (?, ?, ?, ?, ?)", [user.id, record.salt, record.hash, timestamp, timestamp]);
    }
  }
}

function seedOrganization({ id, name, slug, ownerUserId, description, workspaceId, workspaceName, workspaceSlug, projectId, projectName, projectType, readiness, risk }) {
  const timestamp = now();
  insertIgnore("INSERT OR IGNORE INTO organizations(id, name, slug, owner_user_id, deployment_mode, status, created_at, updated_at) VALUES (?, ?, ?, ?, 'private-local', 'active', ?, ?)", [id, name, slug, ownerUserId, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO organization_members(id, organization_id, user_id, role_key, status, joined_at, created_at, updated_at) VALUES (?, ?, ?, 'org_owner', 'active', ?, ?, ?)", [`om-${id}`, id, ownerUserId, timestamp, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO workspaces(id, organization_id, name, slug, description, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, 'active', ?, ?)", [workspaceId, id, workspaceName, workspaceSlug, description, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO workspace_members(id, workspace_id, user_id, role_key, status, created_at, updated_at) VALUES (?, ?, ?, 'producer', 'active', ?, ?)", [`wm-${workspaceId}-${ownerUserId}`, workspaceId, ownerUserId, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO projects(id, workspace_id, name, type, status, owner_user_id, visibility, readiness, risk, created_at, updated_at) VALUES (?, ?, ?, ?, 'production', ?, 'workspace', ?, ?, ?, ?)", [projectId, workspaceId, projectName, projectType, ownerUserId, readiness, risk, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO project_members(id, project_id, user_id, role_key, status, created_at, updated_at) VALUES (?, ?, ?, 'project_editor', 'active', ?, ?)", [`pm-${projectId}-${ownerUserId}`, projectId, ownerUserId, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO billing_accounts(id, organization_id, plan_name, billing_cycle, currency, seat_limit, storage_gb, monthly_clip_quota, quota_warning_percent, local_runner_only, cloud_connectors_require_approval, current_period_start, current_period_end, next_invoice_at, created_at, updated_at) VALUES (?, ?, 'Studio Local', 'monthly', 'CNY', 12, 1024, 2400, 80, 1, 1, ?, ?, ?, ?, ?)", [`bill-${id}`, id, monthStart(), monthEnd(), monthEnd(), timestamp, timestamp]);
  dbRun("UPDATE billing_accounts SET current_period_start = COALESCE(current_period_start, ?), current_period_end = COALESCE(current_period_end, ?), next_invoice_at = COALESCE(next_invoice_at, ?) WHERE organization_id = ?", [monthStart(), monthEnd(), monthEnd(), id]);
  const costCenters = [
    [`cc-${id}-gpu`, "local-gpu", "本地 GPU / 电力", "本地推理与视频生成的运营成本", 500],
    [`cc-${id}-storage`, "storage", "本地存储", "素材、缓存和交付归档存储", 200],
    [`cc-${id}-ops`, "operations", "平台运营", "通知、审计和运维工作量", 300]
  ];
  for (const [costCenterId, code, name, description, budget] of costCenters) {
    insertIgnore("INSERT OR IGNORE INTO cost_centers(id, organization_id, code, name, description, monthly_budget, currency, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, 'CNY', 'active', ?, ?)", [costCenterId, id, code, name, description, budget, timestamp, timestamp]);
  }
  insertIgnore("INSERT OR IGNORE INTO quota_allocations(id, organization_id, workspace_id, metric, limit_value, used_value, unit, period_start, period_end, created_at, updated_at) VALUES (?, ?, ?, 'clip', 2400, 36, 'clips', ?, ?, ?, ?)", [`quota-${workspaceId}-clip`, id, workspaceId, monthStart(), monthEnd(), timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO quota_allocations(id, organization_id, workspace_id, metric, limit_value, used_value, unit, period_start, period_end, created_at, updated_at) VALUES (?, ?, ?, 'storage', 1024, 128, 'GB', ?, ?, ?, ?)", [`quota-${workspaceId}-storage`, id, workspaceId, monthStart(), monthEnd(), timestamp, timestamp]);
}

function rollQuotaPeriods() {
  const start = monthStart();
  const end = monthEnd();
  const timestamp = now();
  const rows = dbAll("SELECT * FROM quota_allocations");
  for (const row of rows) {
    const current = dbGet(
      "SELECT julianday(?) <= julianday('now') AND julianday(?) >= julianday('now') AS active",
      [row.period_start, row.period_end]
    );
    if (current?.active) continue;
    const usedValue = row.metric === "clip"
      ? Number(dbGet(
          `SELECT COALESCE(SUM(units), 0) AS units
           FROM usage_events
           WHERE organization_id = ?
             AND (workspace_id = ? OR (? IS NULL AND workspace_id IS NULL))
             AND unit_name IN ('job', 'clip', 'clips')
             AND created_at >= datetime('now', 'start of month')`,
          [row.organization_id, row.workspace_id, row.workspace_id]
        )?.units || 0)
      : Number(row.used_value || 0);
    dbRun("UPDATE quota_allocations SET used_value = ?, period_start = ?, period_end = ?, updated_at = ? WHERE id = ?", [usedValue, start, end, timestamp, row.id]);
  }
}

function commercialEntitlementDefaults(billing = {}) {
  const clipLimit = Number(billing.monthly_clip_quota || 2400);
  const storageGb = Number(billing.storage_gb || 1024);
  const seatLimit = Number(billing.seat_limit || 12);
  return [
    ["limit.seats", "组织席位", "tenant", seatLimit, "人", 1, "block", { commercialGate: "member-invite-and-sso" }],
    ["limit.workspaces", "工作区数量", "tenant", 8, "个", 1, "block", { commercialGate: "workspace:create" }],
    ["limit.projects", "项目数量", "production", 36, "个", 1, "block", { commercialGate: "project:create" }],
    ["limit.generation_jobs_monthly", "月度生成任务", "production", clipLimit, "job", 1, "block", { commercialGate: "generation_job:create" }],
    ["limit.style_kits", "风格生产标准", "production", 24, "套", 1, "block", { commercialGate: "style_kit:create" }],
    ["limit.customers", "客户主档", "commercial", 120, "个", 1, "block", { commercialGate: "customer:create" }],
    ["limit.contracts", "合同授权", "commercial", 240, "份", 1, "block", { commercialGate: "contract:create" }],
    ["limit.storage_gb", "存储容量", "storage", storageGb, "GB", 1, "block", { commercialGate: "storage:write" }],
    ["limit.model_connectors", "模型连接器", "modelops", 16, "个", 1, "block", { commercialGate: "model_connector:create" }],
    ["limit.api_clients", "API 客户端", "system", 8, "个", 1, "block", { commercialGate: "api_client:create" }],
    ["limit.knowledge_documents", "知识库素材", "knowledge", 300, "篇", 1, "block", { commercialGate: "knowledge_document:ingest" }],
    ["limit.knowledge_context_packs", "知识上下文包", "knowledge", 180, "包", 1, "block", { commercialGate: "knowledge_context_pack:create" }],
    ["limit.delivery_channels", "交付渠道", "delivery", 12, "个", 1, "block", { commercialGate: "delivery_channel:create" }],
    ["feature.batch_generation", "批量生产", "feature", 1, "开关", 1, "block", { description: "允许创建批量生成与流水线任务" }],
    ["feature.private_delivery_portal", "客户交付门户", "feature", 1, "开关", 1, "block", { description: "允许创建带令牌的客户预览/下载门户" }],
    ["feature.comfyui_adapter", "ComfyUI 可选桥接", "feature", 1, "开关", 0, "block", { description: "默认关闭，明确启用后才可作为适配器" }],
    ["feature.external_cloud_connectors", "外部云连接器", "feature", 1, "开关", 0, "block", { description: "默认关闭，付费/公网模型必须显式审批" }]
  ];
}

function seedCommercialPlans() {
  const timestamp = now();
  const plans = [
    ["plan-starter-local", "starter-local", "Starter Local", "单工作室本地试制版，适合一条短剧流水线验证。", "monthly", "CNY", 0, 5, 256, 500, { workspaces: 2, projects: 8, modelConnectors: 6, apiClients: 3, knowledgeDocuments: 80, knowledgeContextPacks: 40, deliveryChannels: 3 }, { batchGeneration: false, privateDeliveryPortal: true, comfyuiAdapter: false, externalCloudConnectors: false }, { allowedCostModes: ["local"], externalApprovalRequired: true }, "社区支持"],
    ["plan-studio-local", "studio-local", "Studio Local", "商业工作室私有生产版，覆盖剧本、资产、生成、审片、交付和账单。", "monthly", "CNY", 0, 12, 1024, 2400, { workspaces: 8, projects: 36, modelConnectors: 16, apiClients: 8, knowledgeDocuments: 300, knowledgeContextPacks: 180, deliveryChannels: 12 }, { batchGeneration: true, privateDeliveryPortal: true, comfyuiAdapter: false, externalCloudConnectors: false }, { allowedCostModes: ["local", "mixed-with-approval"], externalApprovalRequired: true }, "工作日响应"],
    ["plan-enterprise-private", "enterprise-private", "Enterprise Private", "多组织私有化商业版，适合平台代理、内容厂牌和多项目制片团队。", "annual", "CNY", 0, 50, 8192, 20000, { workspaces: 50, projects: 300, modelConnectors: 80, apiClients: 50, knowledgeDocuments: 5000, knowledgeContextPacks: 2000, deliveryChannels: 60 }, { batchGeneration: true, privateDeliveryPortal: true, comfyuiAdapter: true, externalCloudConnectors: false }, { allowedCostModes: ["local", "mixed-with-approval"], externalApprovalRequired: true }, "专属运维窗口"]
  ];
  for (const [id, tierKey, name, description, billingCycle, currency, baseFee, seatLimit, storageGb, monthlyClipQuota, limits, features, connectorPolicy, supportSla] of plans) {
    insertIgnore(
      "INSERT OR IGNORE INTO subscription_plan_templates(id, tier_key, name, description, billing_cycle, currency, base_fee, seat_limit, storage_gb, monthly_clip_quota, limits_json, features_json, connector_policy_json, support_sla, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, ?)",
      [id, tierKey, name, description, billingCycle, currency, baseFee, seatLimit, storageGb, monthlyClipQuota, JSON.stringify(limits), JSON.stringify(features), JSON.stringify(connectorPolicy), supportSla, timestamp, timestamp]
    );
  }
}

function seedOrganizationEntitlements() {
  const timestamp = now();
  const organizations = dbAll(
    `SELECT o.id AS organization_id,
            b.seat_limit, b.storage_gb, b.monthly_clip_quota
     FROM organizations o
     LEFT JOIN billing_accounts b ON b.organization_id = o.id
     WHERE o.status = 'active'`
  );
  for (const organization of organizations) {
    for (const [key, label, category, limitValue, unit, enabled, enforcement, metadata] of commercialEntitlementDefaults(organization)) {
      insertIgnore(
        "INSERT OR IGNORE INTO organization_entitlements(id, organization_id, entitlement_key, label, category, limit_value, unit, enabled, enforcement, source, metadata_json, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'plan', ?, ?, ?)",
        [`ent-${organization.organization_id}-${key.replace(/[^a-z0-9]+/gi, "-")}`, organization.organization_id, key, label, category, limitValue, unit, enabled, enforcement, JSON.stringify(metadata), timestamp, timestamp]
      );
    }
  }
}

function seedCommercialApprovals() {
  const timestamp = now();
  const requests = [
    {
      id: "commercial-approval-seed-entitlement",
      organizationId: "org-studio-lab",
      workspaceId: "ws-local-aidrama",
      projectId: "thunder-mouth",
      requestType: "entitlement_overage",
      title: "《雨夜来电》本月追加 500 个生成任务",
      status: "submitted",
      priority: "high",
      targetKey: "limit.generation_jobs_monthly",
      currentValue: 2400,
      requestedValue: 2900,
      unit: "job",
      businessReason: "连续三集试制需要补拍转场镜头和反应镜头，仍限定为本地 Runner 执行。",
      risk: { localOnly: true, paidCloud: false, singleFrameGate: true, continuityLedger: true },
      evidence: { project: "thunder-mouth", source: "seed://commercial/entitlement-overage" },
      requester: "u-writer",
      reviewer: null,
      reviewedAt: null,
      decisionNote: "",
      effect: {}
    },
    {
      id: "commercial-approval-seed-connector",
      organizationId: "org-studio-lab",
      workspaceId: "ws-local-aidrama",
      projectId: "thunder-mouth",
      requestType: "external_connector",
      title: "评估 NewAPI 中转的外部官方模型通道",
      status: "submitted",
      priority: "urgent",
      targetKey: "feature.external_cloud_connectors",
      currentValue: 0,
      requestedValue: 1,
      unit: "开关",
      businessReason: "仅申请连接能力评估，不自动调用付费云端节点；需要管理员确认成本和合规边界。",
      risk: { localOnly: false, paidCloud: true, requiresExplicitApproval: true, secretStored: false },
      evidence: { connectorPolicy: "env-secret-only", source: "seed://commercial/external-connector" },
      requester: "u-owner",
      reviewer: null,
      reviewedAt: null,
      decisionNote: "",
      effect: {}
    },
    {
      id: "commercial-approval-seed-compliance",
      organizationId: "org-studio-lab",
      workspaceId: "ws-local-aidrama",
      projectId: "thunder-mouth",
      requestType: "compliance_review",
      title: "原创小说素材版权与分块入库复核",
      status: "approved",
      priority: "medium",
      targetKey: "commercial-rights-review",
      currentValue: 0,
      requestedValue: 1,
      unit: "次",
      businessReason: "确认《雨夜来电》素材按原创来源入库，允许用于剧本拆解和知识上下文包。",
      risk: { sourceType: "original", knowledgeChunking: true, derivativeUse: "internal-production" },
      evidence: { knowledgeDocumentId: "knowledge-rain-night", source: "seed://commercial/compliance-review" },
      requester: "u-owner",
      reviewer: "u-owner",
      reviewedAt: timestamp,
      decisionNote: "示例放行：原创素材，后续交付仍需逐素材证据。",
      effect: { complianceRecordId: "commercial-compliance-seed-rain-night" }
    }
  ];
  for (const request of requests) {
    insertIgnore(
      `INSERT OR IGNORE INTO commercial_approval_requests(
        id, organization_id, workspace_id, project_id, request_type, title, status, priority, target_key,
        current_value, requested_value, unit, business_reason, risk_assessment_json, evidence_json,
        decision_note, effect_json, requester_user_id, reviewer_user_id, reviewed_at, expires_at, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?)`,
      [
        request.id,
        request.organizationId,
        request.workspaceId,
        request.projectId,
        request.requestType,
        request.title,
        request.status,
        request.priority,
        request.targetKey,
        request.currentValue,
        request.requestedValue,
        request.unit,
        request.businessReason,
        JSON.stringify(request.risk),
        JSON.stringify(request.evidence),
        request.decisionNote,
        JSON.stringify(request.effect),
        request.requester,
        request.reviewer,
        request.reviewedAt,
        timestamp,
        timestamp
      ]
    );
  }
  insertIgnore(
    "INSERT OR IGNORE INTO compliance_records(id, organization_id, workspace_id, project_id, subject_type, subject_id, policy_key, status, evidence_json, reviewed_by, reviewed_at, created_at, updated_at) VALUES ('commercial-compliance-seed-rain-night', 'org-studio-lab', 'ws-local-aidrama', 'thunder-mouth', 'knowledge_document', 'knowledge-rain-night', 'commercial-rights-review', 'approved', ?, 'u-owner', ?, ?, ?)",
    [JSON.stringify({ source: "seed://commercial/compliance-review", knowledgeDocumentId: "knowledge-rain-night" }), timestamp, timestamp, timestamp]
  );
}

function seedCustomersAndContracts() {
  const timestamp = now();
  const customers = [
    {
      id: "cust-xinghe-release",
      organizationId: "org-studio-lab",
      name: "星河发行合作方",
      legalName: "星河内容发行（模拟）有限公司",
      code: "XH-RELEASE",
      customerType: "distributor",
      status: "active",
      industry: "短剧发行",
      region: "CN",
      billingEmail: "ops@example.local",
      taxId: "",
      notes: "用于验证《雷雨口》商用交付、客户门户和授权范围清算。",
      tags: ["发行", "本地交付", "商用清算"],
      metadata: { source: "seed://customers/xinghe-release", preferredDelivery: "private-delivery-portal" },
      createdBy: "u-owner"
    },
    {
      id: "cust-northstar-platform",
      organizationId: "org-northstar",
      name: "北辰内部发行台",
      legalName: "北辰内容厂牌（模拟）",
      code: "NS-INTERNAL",
      customerType: "internal",
      status: "active",
      industry: "内容厂牌",
      region: "CN",
      billingEmail: "northstar@example.local",
      taxId: "",
      notes: "用于验证北辰组织的独立合同和跨组织隔离。",
      tags: ["内部试播", "租户隔离"],
      metadata: { source: "seed://customers/northstar-internal" },
      createdBy: "u-producer"
    }
  ];
  for (const customer of customers) {
    insertIgnore(
      `INSERT OR IGNORE INTO customers(
        id, organization_id, name, legal_name, code, customer_type, status, industry, region,
        billing_email, tax_id, notes, tags_json, metadata_json, created_by, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        customer.id,
        customer.organizationId,
        customer.name,
        customer.legalName,
        customer.code,
        customer.customerType,
        customer.status,
        customer.industry,
        customer.region,
        customer.billingEmail,
        customer.taxId,
        customer.notes,
        JSON.stringify(customer.tags),
        JSON.stringify(customer.metadata),
        customer.createdBy,
        timestamp,
        timestamp
      ]
    );
  }

  const contacts = [
    ["contact-xinghe-release-producer", "org-studio-lab", "cust-xinghe-release", "星河发行制片经理", "交付验收", "producer@example.local", "", 1],
    ["contact-xinghe-release-legal", "org-studio-lab", "cust-xinghe-release", "星河法务窗口", "合同授权复核", "legal@example.local", "", 0],
    ["contact-northstar-owner", "org-northstar", "cust-northstar-platform", "北辰试制负责人", "内部验收", "pilot@example.local", "", 1]
  ];
  for (const contact of contacts) {
    insertIgnore(
      "INSERT OR IGNORE INTO customer_contacts(id, organization_id, customer_id, name, role, email, phone, is_primary, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, ?)",
      [...contact, timestamp, timestamp]
    );
  }

  const contracts = [
    {
      id: "contract-xh-rain-night-2026",
      organizationId: "org-studio-lab",
      customerId: "cust-xinghe-release",
      contractNumber: "XH-AIDRAMA-2026-001",
      title: "《雷雨口》AI 漫剧本地化制作与交付授权",
      contractType: "production_license",
      status: "active",
      approvalStatus: "approved",
      effectiveAt: "2026-08-01T00:00:00.000Z",
      expiresAt: "2028-12-31T23:59:59.000Z",
      signedAt: "2026-08-15T09:30:00.000Z",
      amount: 0,
      licenseScope: {
        channels: ["local-file", "local-webhook", "private-delivery-portal"],
        territories: ["CN", "GLOBAL"],
        deliverables: ["vertical-video", "prompt-pack", "subtitle", "delivery-manifest"],
        platforms: ["private-preview", "internal-review", "owned-channel"],
        exclusive: false,
        commercialUse: true,
        language: ["zh-CN"]
      },
      rights: {
        originalStory: true,
        derivativeProduction: true,
        aiGeneratedAssetsAllowed: true,
        voiceCloneAllowed: false,
        fixedVoiceRequired: true,
        singleFramePolicyRequired: true
      },
      deliveryTerms: {
        maxAccessDays: 90,
        customerPortalAllowed: true,
        watermarkRequiredBeforeApproval: true,
        requireClearanceCertificate: true
      },
      evidence: {
        contractRef: "seed://contract/xh-2026-aidrama",
        rightsEvidenceRef: "seed://original/rain-night",
        signedCopyPath: "storage/contracts/xh-2026-aidrama/contract-summary.json"
      },
      risk: { paidCloudAllowed: false, publicModelAllowed: false, rightsReview: "passed" },
      approvalNote: "种子合同用于本地商用闭环验证：固定声线、一图一画面、原创权利证据均为强制项。",
      createdBy: "u-owner"
    },
    {
      id: "contract-ns-pilot-2026",
      organizationId: "org-northstar",
      customerId: "cust-northstar-platform",
      contractNumber: "NS-PILOT-2026-001",
      title: "《山海志异·试播集》内部试制授权",
      contractType: "internal",
      status: "active",
      approvalStatus: "approved",
      effectiveAt: "2026-08-10T00:00:00.000Z",
      expiresAt: "2027-12-31T23:59:59.000Z",
      signedAt: "2026-08-10T10:00:00.000Z",
      amount: 0,
      licenseScope: {
        channels: ["local-file", "private-delivery-portal"],
        territories: ["CN"],
        deliverables: ["vertical-video", "delivery-manifest"],
        platforms: ["internal-review"],
        exclusive: false,
        commercialUse: true,
        language: ["zh-CN"]
      },
      rights: {
        originalStory: true,
        derivativeProduction: true,
        aiGeneratedAssetsAllowed: true,
        voiceCloneAllowed: false,
        fixedVoiceRequired: true,
        singleFramePolicyRequired: true
      },
      deliveryTerms: {
        maxAccessDays: 30,
        customerPortalAllowed: true,
        watermarkRequiredBeforeApproval: true,
        requireClearanceCertificate: true
      },
      evidence: {
        contractRef: "seed://contract/ns-pilot-2026",
        rightsEvidenceRef: "seed://original/northstar-pilot",
        signedCopyPath: "storage/contracts/ns-pilot-2026/contract-summary.json"
      },
      risk: { paidCloudAllowed: false, publicModelAllowed: false, rightsReview: "passed" },
      approvalNote: "北辰组织独立授权，不允许被星河组织项目绑定。",
      createdBy: "u-producer"
    }
  ];
  for (const contract of contracts) {
    insertIgnore(
      `INSERT OR IGNORE INTO production_contracts(
        id, organization_id, customer_id, contract_number, title, contract_type, status,
        approval_status, effective_at, expires_at, signed_at, currency, amount,
        license_scope_json, rights_json, delivery_terms_json, evidence_json, risk_json,
        approval_note, created_by, updated_by, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'CNY', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        contract.id,
        contract.organizationId,
        contract.customerId,
        contract.contractNumber,
        contract.title,
        contract.contractType,
        contract.status,
        contract.approvalStatus,
        contract.effectiveAt,
        contract.expiresAt,
        contract.signedAt,
        contract.amount,
        JSON.stringify(contract.licenseScope),
        JSON.stringify(contract.rights),
        JSON.stringify(contract.deliveryTerms),
        JSON.stringify(contract.evidence),
        JSON.stringify(contract.risk),
        contract.approvalNote,
        contract.createdBy,
        contract.createdBy,
        timestamp,
        timestamp
      ]
    );
  }

  const bindings = [
    ["contract-binding-thunder-mouth-xh", "org-studio-lab", "ws-local-aidrama", "thunder-mouth", "contract-xh-rain-night-2026", "cust-xinghe-release", "primary-license", "active", "《雷雨口》默认商业交付授权。", "u-owner"],
    ["contract-binding-northstar-pilot", "org-northstar", "ws-northstar-main", "northstar-pilot", "contract-ns-pilot-2026", "cust-northstar-platform", "internal-review", "active", "北辰内部试播项目授权。", "u-producer"]
  ];
  for (const binding of bindings) {
    insertIgnore(
      "INSERT OR IGNORE INTO contract_project_bindings(id, organization_id, workspace_id, project_id, contract_id, customer_id, usage_mode, status, notes, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [...binding, timestamp, timestamp]
    );
  }

  const events = [
    ["contract-event-xh-seed-created", "org-studio-lab", "contract-xh-rain-night-2026", "cust-xinghe-release", "ws-local-aidrama", "thunder-mouth", "seeded", "recorded", "u-owner", { action: "seed", bindingId: "contract-binding-thunder-mouth-xh", channelKinds: ["local-file", "private-delivery-portal"] }],
    ["contract-event-ns-seed-created", "org-northstar", "contract-ns-pilot-2026", "cust-northstar-platform", "ws-northstar-main", "northstar-pilot", "seeded", "recorded", "u-producer", { action: "seed", bindingId: "contract-binding-northstar-pilot" }]
  ];
  for (const event of events) {
    insertIgnore(
      "INSERT OR IGNORE INTO contract_license_events(id, organization_id, contract_id, customer_id, workspace_id, project_id, event_type, status, actor_user_id, metadata_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [...event.slice(0, 9), JSON.stringify(event[9]), timestamp]
    );
  }
}

function seedMembersAndProjects() {
  const timestamp = now();
  const membershipRows = [
    ["om-studio-writer", "org-studio-lab", "u-writer", "org_admin"],
    ["om-studio-art", "org-studio-lab", "u-art", "org_admin"],
    ["om-studio-voice", "org-studio-lab", "u-voice", "org_admin"],
    ["om-studio-review", "org-studio-lab", "u-review", "org_admin"],
    ["om-northstar-producer", "org-northstar", "u-producer", "org_admin"]
  ];
  for (const row of membershipRows) {
    insertIgnore("INSERT OR IGNORE INTO organization_members(id, organization_id, user_id, role_key, status, joined_at, created_at, updated_at) VALUES (?, ?, ?, ?, 'active', ?, ?, ?)", [...row, timestamp, timestamp, timestamp]);
  }
  dbRun("UPDATE organization_members SET role_key = 'org_member' WHERE organization_id = 'org-studio-lab' AND user_id IN ('u-writer', 'u-art', 'u-voice', 'u-review')");

  const workspaceRows = [
    ["wm-local-writer", "ws-local-aidrama", "u-writer", "writer"],
    ["wm-local-art", "ws-local-aidrama", "u-art", "art_director"],
    ["wm-local-voice", "ws-local-aidrama", "u-voice", "voice_editor"],
    ["wm-local-review", "ws-local-aidrama", "u-review", "reviewer"],
    ["wm-pilot-owner", "ws-pilot", "u-owner", "producer"],
    ["wm-northstar-producer", "ws-northstar-main", "u-producer", "producer"]
  ];
  for (const row of workspaceRows) {
    insertIgnore("INSERT OR IGNORE INTO workspace_members(id, workspace_id, user_id, role_key, status, created_at, updated_at) VALUES (?, ?, ?, ?, 'active', ?, ?)", [...row, timestamp, timestamp]);
  }

  insertIgnore("INSERT OR IGNORE INTO projects(id, workspace_id, name, type, status, owner_user_id, visibility, readiness, risk, created_at, updated_at) VALUES (?, ?, ?, '模板', 'template', ?, 'workspace', 48, 'low', ?, ?)", ["template-original-manhua", "ws-pilot", "原创国漫短剧模板", "u-owner", timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO project_members(id, project_id, user_id, role_key, status, created_at, updated_at) VALUES (?, ?, ?, 'project_editor', 'active', ?, ?)", ["pm-template-owner", "template-original-manhua", "u-owner", timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO projects(id, workspace_id, name, type, status, owner_user_id, visibility, readiness, risk, created_at, updated_at) VALUES (?, ?, ?, 'AI 漫剧', 'production', ?, 'workspace', 35, 'medium', ?, ?)", ["northstar-pilot", "ws-northstar-main", "山海志异·试播集", "u-producer", timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO project_members(id, project_id, user_id, role_key, status, created_at, updated_at) VALUES (?, ?, ?, 'project_editor', 'active', ?, ?)", ["pm-northstar-producer", "northstar-pilot", "u-producer", timestamp, timestamp]);
}

function seedModels() {
  const timestamp = now();
  const models = [
    ["owned-i2v", "org-studio-lab", "ws-local-aidrama", "自有图生视频平台", "http-json", ["image-to-video", "first-last-frame", "vertical-video"], "http://127.0.0.1:7860/api/generate/i2v", "not-connected", "local", 0, "u-owner"],
    ["owned-image", "org-studio-lab", "ws-local-aidrama", "自有图片/改图平台", "http-json", ["text-to-image", "image-edit", "single-frame"], "http://127.0.0.1:7860/api/generate/image", "not-connected", "local", 0, "u-owner"],
    ["owned-story-parser", "org-studio-lab", "ws-local-aidrama", "自有文本解析网关", "openai-compatible", ["chat", "script-split", "knowledge-extract"], "http://127.0.0.1:7862/v1", "ready", "local", 0, "u-owner"],
    ["owned-embedding", "org-studio-lab", "ws-local-aidrama", "自有向量检索网关", "openai-compatible", ["embedding", "rerank", "knowledge-retrieval"], "http://127.0.0.1:7863/v1", "planned", "local", 0, "u-owner"],
    ["local-tts", "org-studio-lab", "ws-local-aidrama", "本地固定声线 TTS", "http-json", ["tts", "voice-lock", "subtitle-timing"], "http://127.0.0.1:7861/api/tts", "planned", "local", 0, "u-owner"],
    ["newapi-audio-production", "org-studio-lab", "ws-local-aidrama", "NewAPI 音频中转", "openai-compatible-audio", ["tts", "voice-lock", "emotion-control", "asr", "subtitle-timing"], "https://newapi.ysblack.com/v1", "ready", "mixed", 1, "u-owner"],
    ["comfyui-optional", "org-studio-lab", "ws-local-aidrama", "ComfyUI 工作流桥接", "comfyui", ["workflow", "qwen-image", "qwen-edit"], "http://127.0.0.1:8188", "optional", "mixed", 1, "u-owner"],
    ["northstar-image", "org-northstar", "ws-northstar-main", "北辰本地图像 Runner", "http-json", ["text-to-image", "single-frame"], "http://127.0.0.1:7960/api/image", "ready", "local", 0, "u-producer"]
  ];
  for (const [id, organizationId, workspaceId, label, kind, capabilities, endpoint, status, costMode, approvalRequired, createdBy] of models) {
    insertIgnore("INSERT OR IGNORE INTO model_connectors(id, organization_id, workspace_id, label, kind, capabilities_json, endpoint, status, cost_mode, approval_required, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [id, organizationId, workspaceId, label, kind, JSON.stringify(capabilities), endpoint, status, costMode, approvalRequired, createdBy, timestamp, timestamp]);
  }
}

function seedModelCatalog() {
  const timestamp = now();
  const entries = [
    ["catalog-qwen-image-2d", "org-studio-lab", "ws-local-aidrama", "owned-image", "qwen-image-2d-lock", "Qwen Image 国漫单画面", "qwen-image", ["text-to-image", "single-frame", "continuity-lock"], 32768, 4096, { mode: "per-image", estimatedCny: 0.18, locality: "local" }, "active", "approved", { aspect: "9:16", guardrails: ["single-frame-only", "no-collage"] }],
    ["catalog-wan-i2v", "org-studio-lab", "ws-local-aidrama", "owned-i2v", "wan-i2v-lastframe", "Wan 图生视频连续版", "wan-video", ["image-to-video", "first-last-frame", "clip-bridge"], 65536, 4096, { mode: "per-clip", estimatedCny: 0.42, locality: "local" }, "active", "approved", { durationSec: [5, 10], notes: "优先使用上一段实际末帧" }],
    ["catalog-qwen-parser", "org-studio-lab", "ws-local-aidrama", "owned-story-parser", "qwen-script-parser", "Qwen 剧本/小说解析", "qwen-text", ["chat", "script-split", "knowledge-extract", "scene-parse"], 131072, 8192, { mode: "per-1k-tokens", estimatedCny: 0.03, locality: "local" }, "active", "approved", { languages: ["zh-CN"], preferredFor: ["script-import", "knowledge-ingest"] }],
    ["catalog-bge-m3", "org-studio-lab", "ws-local-aidrama", "owned-embedding", "bge-m3-local", "BGE-M3 向量检索", "embedding", ["embedding", "retrieval", "knowledge-search"], 8192, 0, { mode: "per-1k-tokens", estimatedCny: 0.01, locality: "local" }, "planned", "approved", { dimensions: 1024, preferredFor: ["knowledge-search"] }],
    ["catalog-indextts", "org-studio-lab", "ws-local-aidrama", "newapi-audio-production", "IndexTTS-2.5", "IndexTTS-2.5 固定声线", "tts", ["tts", "voice-lock", "emotion-control"], 16384, 2048, { mode: "per-10s-audio", estimatedCny: 0.12, locality: "mixed" }, "active", "review", { referenceRequired: true, approvalGate: "voice-rights" }],
    ["catalog-paraformer", "org-studio-lab", "ws-local-aidrama", "newapi-audio-production", "paraformer-zh-long", "Paraformer 长音频 ASR", "asr", ["asr", "subtitle-timing", "alignment"], 16384, 2048, { mode: "per-minute-audio", estimatedCny: 0.05, locality: "mixed" }, "active", "approved", { output: "verbose_json" }],
    ["catalog-northstar-image", "org-northstar", "ws-northstar-main", "northstar-image", "northstar-sd-xl", "北辰 SDXL 单画面", "sdxl", ["text-to-image", "single-frame"], 32768, 4096, { mode: "per-image", estimatedCny: 0.16, locality: "local" }, "active", "approved", { aspect: "9:16" }]
  ];
  for (const [id, organizationId, workspaceId, connectorId, modelKey, displayName, family, capabilities, contextWindow, maxOutputTokens, cost, status, approvalStatus, metadata] of entries) {
    insertIgnore(
      "INSERT OR IGNORE INTO model_catalog_entries(id, organization_id, workspace_id, connector_id, model_key, display_name, family, capabilities_json, context_window, max_output_tokens, cost_json, status, approval_status, metadata_json, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'u-owner', ?, ?)",
      [id, organizationId, workspaceId, connectorId, modelKey, displayName, family, JSON.stringify(capabilities), contextWindow, maxOutputTokens, JSON.stringify(cost), status, approvalStatus, JSON.stringify(metadata), timestamp, timestamp]
    );
  }
}

function seedModelRoutes() {
  const timestamp = now();
  const routes = [
    ["route-script-ingest", "org-studio-lab", "ws-local-aidrama", "小说/剧本导入解析", "knowledge-ingest", "chunk-and-extract", "catalog-qwen-parser", "catalog-bge-m3", "prefer-local", "follow-model", 15, "active", { chunkStrategy: "chapter-scene-dialogue", maxChunkChars: 520 }],
    ["route-script-materialize", "org-studio-lab", "ws-local-aidrama", "剧本拆解与场景草稿", "script-pipeline", "scene-draft", "catalog-qwen-parser", null, "prefer-local", "follow-model", 10, "active", { target: "script_documents" }],
    ["route-image-keyframe", "org-studio-lab", "ws-local-aidrama", "单画面关键帧", "ai-manhua-drama", "image-keyframe", "catalog-qwen-image-2d", null, "local-only", "follow-model", 60, "active", { qaGate: "single-frame" }],
    ["route-video-clip", "org-studio-lab", "ws-local-aidrama", "图生视频片段", "ai-manhua-drama", "video-clip", "catalog-wan-i2v", null, "local-only", "follow-model", 120, "active", { requireActualLastFrame: true }],
    ["route-voice-tts", "org-studio-lab", "ws-local-aidrama", "角色固定配音", "voice-pipeline", "tts", "catalog-indextts", null, "prefer-approved", "explicit-review", 40, "active", { referenceAssetKind: "voice", forbidRandomNativeVoice: true }],
    ["route-voice-asr", "org-studio-lab", "ws-local-aidrama", "ASR 对齐校验", "voice-pipeline", "asr", "catalog-paraformer", null, "prefer-approved", "follow-model", 20, "active", { output: "verbose_json" }]
  ];
  for (const [id, organizationId, workspaceId, name, workflowKey, operationKey, primaryModelId, fallbackModelId, policyMode, approvalMode, budgetLimitCny, status, policy] of routes) {
    insertIgnore(
      "INSERT OR IGNORE INTO model_routing_policies(id, organization_id, workspace_id, name, workflow_key, operation_key, primary_model_id, fallback_model_id, policy_mode, approval_mode, budget_limit_cny, status, policy_json, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'u-owner', ?, ?)",
      [id, organizationId, workspaceId, name, workflowKey, operationKey, primaryModelId, fallbackModelId, policyMode, approvalMode, budgetLimitCny, status, JSON.stringify(policy), timestamp, timestamp]
    );
  }
}

function seedStyleKits() {
  const timestamp = now();
  const kits = [
    {
      id: "stylekit-studio-guoman-vertical",
      organizationId: "org-studio-lab",
      workspaceId: "ws-local-aidrama",
      projectId: null,
      scopeMode: "workspace",
      name: "星河国漫竖屏生产标准",
      description: "工作区默认商用风格标准：原创国漫 2D、9:16、固定字幕与声线策略，所有关键帧强制单画面。",
      status: "active",
      visualProfile: {
        styleFamily: "国产漫画 / 国漫 2D 动画",
        linework: "干净线稿、清晰轮廓、轻赛璐璐阴影",
        colorScript: "雷雨青绿、暖肤色、低饱和环境光",
        cameraLanguage: "稳定中景、反应镜头、环境插入镜头优先",
        continuityLocks: ["character", "costume", "location", "prop", "weather", "camera"]
      },
      subtitlePreset: {
        language: "zh-CN",
        fontFamily: "PingFang SC / Noto Sans SC",
        position: "bottom-safe-area",
        maxCharsPerLine: 18,
        style: "白字深色描边，避免遮挡人物脸部和关键道具"
      },
      voicePolicy: {
        finalVoiceMode: "fixed-reference-required",
        forbidRandomNativeVoice: true,
        ttsPreference: "local-or-owned-first",
        fallbackMouthPlan: ["侧脸", "背影", "低头", "远景", "反应镜头", "环境插入镜头"]
      },
      deliverySpec: {
        aspectRatio: "9:16",
        resolution: "1080x1920",
        fps: 24,
        clipDurationSec: [5, 10],
        exportFormat: "mp4",
        projectFolder: "exports/{projectId}"
      },
      promptRules: {
        positivePrefix: "ONE SINGLE COMPLETE 9:16 CHINESE ANIMATION FRAME, one continuous scene, one complete image.",
        negativeTerms: ["split-screen", "comic panel", "collage", "contact sheet", "storyboard", "多格", "拼图", "分屏", "故事板拼图"],
        renderNotes: ["角色服装、道具、天气和机位必须继承 continuity ledger", "每次图片生成 batch size 固定为 1"]
      },
      singleFramePolicy: {
        enabled: true,
        imageOutputCount: 1,
        batchSize: 1,
        forbiddenTerms: ["split-screen", "comic panel", "collage", "contact sheet", "storyboard", "多格", "拼图", "分屏", "多时间点"],
        requiredPhrase: "one single complete frame"
      },
      namingRules: {
        shotPattern: "{projectId}/S{season}E{episode}/shot-{shotNumber}-{assetKind}-{version}",
        subtitlePattern: "{episodeId}-{voiceLineId}.srt",
        deliveryPattern: "{projectName}-{episodeId}-{releaseVersion}"
      },
      qaPolicy: {
        gates: ["single-frame", "continuity", "voice-rights", "subtitle-asr-alignment", "clip-transition"],
        minimumReviewScore: 90,
        requireLockedAssets: true
      },
      metadata: {
        source: "seed://style-kits/studio-guoman-vertical",
        commercialReference: "brand-kit-style-guide-production-standard"
      },
      bindProjectId: "thunder-mouth",
      bindingNotes: "《雷雨口》商用 MVP 默认继承工作区国漫竖屏生产标准。"
    },
    {
      id: "stylekit-northstar-fog-pilot",
      organizationId: "org-northstar",
      workspaceId: "ws-northstar-main",
      projectId: "northstar-pilot",
      scopeMode: "project",
      name: "北辰雾境试播标准",
      description: "北辰内容厂牌专用试播标准，用于验证跨组织隔离和项目级风格绑定。",
      status: "active",
      visualProfile: {
        styleFamily: "原创国漫 2D 奇幻",
        linework: "偏硬朗线条、雾中高反差边缘光",
        colorScript: "冷雾灰、青铜色、少年角色暖色点光",
        cameraLanguage: "稳定中景与环境压迫感，禁止多格构图",
        continuityLocks: ["character", "location", "prop", "weather", "camera"]
      },
      subtitlePreset: {
        language: "zh-CN",
        fontFamily: "PingFang SC",
        position: "bottom-safe-area",
        maxCharsPerLine: 16,
        style: "白字深描边"
      },
      voicePolicy: {
        finalVoiceMode: "fixed-reference-required",
        forbidRandomNativeVoice: true,
        ttsPreference: "local-runner-only",
        fallbackMouthPlan: ["远景", "侧脸", "反应镜头"]
      },
      deliverySpec: {
        aspectRatio: "9:16",
        resolution: "1080x1920",
        fps: 24,
        clipDurationSec: [5, 8],
        exportFormat: "mp4",
        projectFolder: "exports/{projectId}"
      },
      promptRules: {
        positivePrefix: "ONE SINGLE COMPLETE 9:16 CHINESE ANIMATION FRAME, fantasy fog gate, one scene only.",
        negativeTerms: ["split-screen", "comic panel", "collage", "contact sheet", "storyboard", "多格", "分屏"],
        renderNotes: ["不得串用星河实验室角色或道具", "首尾帧必须来自当前项目"]
      },
      singleFramePolicy: {
        enabled: true,
        imageOutputCount: 1,
        batchSize: 1,
        forbiddenTerms: ["split-screen", "comic panel", "collage", "contact sheet", "storyboard", "多格", "拼图", "分屏"],
        requiredPhrase: "one single complete frame"
      },
      namingRules: {
        shotPattern: "{projectId}/pilot/shot-{shotNumber}-{version}",
        subtitlePattern: "{projectId}-{voiceLineId}.srt",
        deliveryPattern: "{projectName}-pilot-{releaseVersion}"
      },
      qaPolicy: {
        gates: ["single-frame", "cross-org-isolation", "continuity", "clip-transition"],
        minimumReviewScore: 92,
        requireLockedAssets: true
      },
      metadata: {
        source: "seed://style-kits/northstar-fog-pilot",
        commercialReference: "tenant-isolated-production-standard"
      },
      bindProjectId: "northstar-pilot",
      bindingNotes: "北辰试播项目强制使用项目级雾境风格标准。"
    }
  ];
  for (const kit of kits) {
    insertIgnore(
      `INSERT OR IGNORE INTO style_kits(
        id, organization_id, workspace_id, project_id, scope_mode, name, description, status,
        visual_profile_json, subtitle_preset_json, voice_policy_json, delivery_spec_json,
        prompt_rules_json, single_frame_policy_json, naming_rules_json, qa_policy_json,
        metadata_json, created_by, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'u-owner', ?, ?)`,
      [
        kit.id,
        kit.organizationId,
        kit.workspaceId,
        kit.projectId,
        kit.scopeMode,
        kit.name,
        kit.description,
        kit.status,
        JSON.stringify(kit.visualProfile),
        JSON.stringify(kit.subtitlePreset),
        JSON.stringify(kit.voicePolicy),
        JSON.stringify(kit.deliverySpec),
        JSON.stringify(kit.promptRules),
        JSON.stringify(kit.singleFramePolicy),
        JSON.stringify(kit.namingRules),
        JSON.stringify(kit.qaPolicy),
        JSON.stringify(kit.metadata),
        timestamp,
        timestamp
      ]
    );
    insertIgnore(
      "INSERT OR IGNORE INTO project_style_kit_bindings(id, organization_id, workspace_id, project_id, style_kit_id, enforcement, notes, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, 'strict', ?, 'u-owner', ?, ?)",
      [`binding-${kit.bindProjectId}-style-kit`, kit.organizationId, kit.workspaceId, kit.bindProjectId, kit.id, kit.bindingNotes, timestamp, timestamp]
    );
  }
}

function seedKnowledgeLibrary() {
  const timestamp = now();
  const content = [
    "第一章 雨夜来电",
    "暴雨压在旧城区的玻璃连廊上，陈宇刚结束夜班巡检，就接到唐夏发来的定位。她只说了一句：不要让任何人碰那把蓝伞。",
    "",
    "唐夏：我到地铁口了，可这里的警戒线被人挪开了。",
    "陈宇：别过去，积水边可能有落地电线。你先站在连廊灯下，右手不要离开蓝伞。",
    "",
    "第二章 失踪的雨披",
    "两人会合后发现黄色雨披不见了，警戒锥却比下午多了一组。唐夏开始怀疑，有人提前布置过现场，像是在等他们出现。"
  ].join("\n");
  const analysis = {
    parser: "local-rule-v2",
    chapterCount: 2,
    chunkCount: 4,
    summary: "暴雨夜的地铁口事故线索，包含角色对话、场景限制、关键道具和连续性信息。",
    entities: {
      characters: ["陈宇", "唐夏"],
      locations: ["旧城区玻璃连廊", "地铁口"],
      props: ["蓝伞", "黄色雨披", "警戒线", "警戒锥"]
    }
  };
  const provenance = {
    sourceLabel: "平台原创示例小说",
    author: "AI Drama Platform Demo",
    rightsOwner: "本地示例组织",
    evidenceRef: "seed://original/rain-night",
    licenseNote: "原创演示素材，仅用于本地样例和 smoke 测试",
    sourceUrl: "",
    importedFrom: "seed"
  };
  const tags = ["原创", "雨夜", "连续性样例"];
  const risk = {
    scanner: "local-governance-v1",
    status: "pass",
    score: 0,
    rightsStatus: "approved",
    sourceType: "novel",
    tags,
    issues: [],
    checks: {
      provenanceEvidence: true,
      commercialRightsApproved: true,
      knownIpReferences: 0,
      personaLikenessRisks: 0,
      singleFramePolicyRisks: 0,
      safetySensitiveMatches: 0
    },
    scannedAt: timestamp
  };
  insertIgnore(
    "INSERT OR IGNORE INTO knowledge_documents(id, organization_id, workspace_id, project_id, scope_mode, title, source_type, language, content, status, summary, analysis_json, metadata_json, created_by, created_at, updated_at) VALUES ('knowledge-rain-night', 'org-studio-lab', 'ws-local-aidrama', NULL, 'workspace', '《雨夜来电》原始小说素材', 'novel', 'zh-CN', ?, 'indexed', ?, ?, ?, 'u-owner', ?, ?)",
    [content, analysis.summary, JSON.stringify(analysis), JSON.stringify({ sourceLabel: "原创小说", rights: "original", recommendedWorkflow: "ai-manhua-drama" }), timestamp, timestamp]
  );
  dbRun(
    `UPDATE knowledge_documents
     SET rights_status = 'approved',
         provenance_json = ?,
         tags_json = ?,
         risk_json = ?,
         current_version_number = CASE WHEN current_version_number < 1 THEN 1 ELSE current_version_number END
     WHERE id = 'knowledge-rain-night'
       AND (rights_status = 'needs-evidence' OR provenance_json = '{}' OR risk_json = '{}')`,
    [JSON.stringify(provenance), JSON.stringify(tags), JSON.stringify(risk)]
  );
  insertIgnore(
    `INSERT OR IGNORE INTO knowledge_document_versions(
      id, document_id, version_number, title, source_type, language, content, summary,
      analysis_json, provenance_json, tags_json, risk_json, metadata_json, created_by, created_at
    ) VALUES ('knowledge-rain-night-v1', 'knowledge-rain-night', 1, '《雨夜来电》原始小说素材', 'novel', 'zh-CN', ?, ?, ?, ?, ?, ?, ?, 'u-owner', ?)`,
    [content, analysis.summary, JSON.stringify(analysis), JSON.stringify(provenance), JSON.stringify(tags), JSON.stringify(risk), JSON.stringify({ sourceLabel: "原创小说", rights: "original", recommendedWorkflow: "ai-manhua-drama", seed: true }), timestamp]
  );
  const chunks = [
    ["knowledge-rain-night-chunk-1", 1, "chapter", "第一章 雨夜来电", "暴雨压在旧城区的玻璃连廊上，陈宇刚结束夜班巡检，就接到唐夏发来的定位。她只说了一句：不要让任何人碰那把蓝伞。", 116, ["暴雨", "玻璃连廊", "蓝伞"], { characters: ["陈宇", "唐夏"], locations: ["旧城区玻璃连廊"], props: ["蓝伞"] }, { chapter: 1, sceneHint: "开场建立场景与冲突" }],
    ["knowledge-rain-night-chunk-2", 2, "dialogue", "地铁口对话", "唐夏：我到地铁口了，可这里的警戒线被人挪开了。\n陈宇：别过去，积水边可能有落地电线。你先站在连廊灯下，右手不要离开蓝伞。", 124, ["地铁口", "警戒线", "落地电线"], { characters: ["陈宇", "唐夏"], locations: ["地铁口"], props: ["蓝伞", "警戒线"] }, { chapter: 1, mouthAvoidance: true, sceneHint: "适合侧脸/反应镜头对白" }],
    ["knowledge-rain-night-chunk-3", 3, "chapter", "第二章 失踪的雨披", "两人会合后发现黄色雨披不见了，警戒锥却比下午多了一组。", 58, ["黄色雨披", "警戒锥"], { characters: ["陈宇", "唐夏"], props: ["黄色雨披", "警戒锥"] }, { chapter: 2, sceneHint: "道具连续性检查点" }],
    ["knowledge-rain-night-chunk-4", 4, "lore", "现场异常", "唐夏开始怀疑，有人提前布置过现场，像是在等他们出现。", 42, ["异常布置", "悬念"], { characters: ["唐夏"] }, { chapter: 2, sceneHint: "结尾悬念与下一集钩子" }]
  ];
  for (const [id, chunkIndex, chunkType, heading, body, tokenEstimate, keywords, entities, metadata] of chunks) {
    insertIgnore(
      "INSERT OR IGNORE INTO knowledge_chunks(id, document_id, chunk_index, chunk_type, heading, content, token_estimate, keywords_json, entities_json, metadata_json, created_at) VALUES (?, 'knowledge-rain-night', ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [id, chunkIndex, chunkType, heading, body, tokenEstimate, JSON.stringify(keywords), JSON.stringify(entities), JSON.stringify(metadata), timestamp]
    );
  }
  const openingChunks = chunks.slice(0, 2);
  const citations = openingChunks.map(([id, chunkIndex, chunkType, heading], index) => ({
    key: `K${index + 1}`,
    documentId: "knowledge-rain-night",
    documentTitle: "《雨夜来电》原始小说素材",
    chunkId: id,
    chunkIndex,
    heading,
    sourceType: "novel",
    scopeMode: "workspace",
    rightsStatus: "approved",
    riskStatus: "pass",
    projectId: null,
    chunkType
  }));
  const packChunks = openingChunks.map(([id, chunkIndex, chunkType, heading, body, tokenEstimate, keywords, entities], index) => ({
    citationKey: citations[index].key,
    id,
    documentId: "knowledge-rain-night",
    documentTitle: "《雨夜来电》原始小说素材",
    chunkIndex,
    chunkType,
    heading,
    content: body,
    tokenEstimate,
    keywords,
    entities,
    rightsStatus: "approved",
    riskStatus: "pass"
  }));
  const packGovernance = {
    status: "pass",
    rights: { approved: packChunks.length },
    risk: { pass: packChunks.length },
    blockingCount: 0,
    reviewCount: 0
  };
  const promptContext = [
    "# 知识库上下文包：雨夜开场冲突",
    "使用要求：基于引用素材做原创改编；保留人物、道具、地点和时间线连续性；生成画面仍必须是一张完整单画面，不得输出多格、拼图或分屏。",
    "治理摘要：pass；未批准/需复核片段 0；阻断片段 0。",
    ...packChunks.map((chunk) => [
      `## [${chunk.citationKey}] ${chunk.heading}`,
      `来源：《${chunk.documentTitle}》 / novel / chunk ${chunk.chunkIndex} / rights=approved / risk=pass`,
      `内容：${chunk.content}`
    ].join("\n"))
  ].join("\n\n");
  insertIgnore(
    `INSERT OR IGNORE INTO knowledge_context_packs(
      id, organization_id, workspace_id, project_id, scope_mode, name, query, source_type, max_tokens,
      token_estimate, chunk_ids_json, citations_json, chunks_json, prompt_context, status,
      metadata_json, created_by, created_at, updated_at
    ) VALUES ('knowledge-pack-rain-night-opening', 'org-studio-lab', 'ws-local-aidrama', NULL, 'workspace', '雨夜开场冲突', '蓝伞 地铁口', 'novel', 800, ?, ?, ?, ?, ?, 'active', ?, 'u-owner', ?, ?)`,
    [
      packChunks.reduce((sum, chunk) => sum + Number(chunk.tokenEstimate || 0), 0),
      JSON.stringify(packChunks.map((chunk) => chunk.id)),
      JSON.stringify(citations),
      JSON.stringify(packChunks),
      promptContext,
      JSON.stringify({ seed: true, usage: "script-draft", rights: "original", governance: packGovernance }),
      timestamp,
      timestamp
    ]
  );
  dbRun(
    `UPDATE knowledge_context_packs
     SET citations_json = ?, chunks_json = ?, prompt_context = ?, metadata_json = ?
     WHERE id = 'knowledge-pack-rain-night-opening'`,
    [JSON.stringify(citations), JSON.stringify(packChunks), promptContext, JSON.stringify({ seed: true, usage: "script-draft", rights: "original", governance: packGovernance })]
  );
}

function seedSystemGovernance() {
  const timestamp = now();
  insertIgnore("INSERT OR IGNORE INTO system_admins(user_id, role_key, status, created_at, updated_at) VALUES ('u-owner', 'system_admin', 'active', ?, ?)", [timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO identity_policies(id, password_login_enabled, mfa_required_for_admins, mfa_required_for_all, sso_enabled, local_login_fallback, session_ttl_hours, max_sessions_per_user, updated_by, created_at, updated_at) VALUES ('default', 1, 0, 0, 0, 1, 12, 10, 'u-owner', ?, ?)", [timestamp, timestamp]);
  const settings = [
    ["app.name", "general", "AI 短剧生产平台", "string", "平台显示名称", 0],
    ["app.locale", "general", "zh-CN", "string", "默认界面语言", 0],
    ["app.timezone", "general", "Asia/Shanghai", "string", "默认时区", 0],
    ["deployment.mode", "deployment", "private-local", "string", "部署模式", 0],
    ["deployment.local_runner_only", "deployment", true, "boolean", "默认只允许本地 Runner", 0],
    ["storage.provider", "storage", "local-filesystem", "string", "媒体文件存储提供方；生产可切换为 S3-compatible 适配层", 0],
    ["storage.root_path", "storage", resolve(projectRoot, "storage"), "path", "资产和生成产物根目录", 0],
    ["storage.max_upload_mb", "storage", 1024, "number", "单文件最大上传大小", 0],
    ["storage.retention_days", "storage", 30, "number", "无引用临时媒体的最短保留天数", 0],
    ["storage.cleanup_unreferenced_only", "storage", true, "boolean", "清理时只允许处理未被生产证据引用的临时文件", 0],
    ["queue.max_concurrency", "queue", 2, "number", "全局并发任务数", 0],
    ["generation.single_frame_only", "generation", true, "boolean", "一图一画面策略", 0],
    ["generation.require_actual_last_frame", "generation", true, "boolean", "视频片段必须使用真实末帧连续", 0],
    ["generation.comfyui_optional", "generation", true, "boolean", "ComfyUI 仅作为可选适配器", 0],
    ["voice.fixed_voice_required", "voice", true, "boolean", "角色必须绑定固定声线", 0],
    ["qa.require_asr_alignment", "qa", true, "boolean", "配音任务必须通过 ASR/字幕对齐", 0],
    ["qa.require_continuity_lock", "qa", true, "boolean", "镜头生成必须引用连续性锁", 0],
    ["notifications.email_enabled", "notifications", false, "boolean", "启用邮件通知", 0],
    ["api.rate_limit_per_minute", "api", 120, "number", "API 每分钟请求上限", 0]
  ];
  for (const [key, category, value, valueType, description, sensitive] of settings) {
    insertIgnore("INSERT OR IGNORE INTO system_settings(key, category, value_json, value_type, description, is_sensitive, updated_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, 'u-owner', ?, ?)", [key, category, JSON.stringify(value), valueType, description, sensitive, timestamp, timestamp]);
  }

  const flags = [
    ["creator_portal", "用户创作空间", "项目、剧本、资产和交付工作台", 1],
    ["admin_console", "管理员后台", "组织、成员、模型、队列和计量治理", 1],
    ["system_settings", "系统配置中心", "部署、生成策略、存储和通知配置", 1],
    ["batch_generation", "批量生成", "允许制片人创建批量生成批次", 1],
    ["review_workflow", "审片审批流", "启用多人审片和交付审批", 1],
    ["comfyui_adapter", "ComfyUI 适配器", "可选的本地 ComfyUI 工作流桥接", 0],
    ["external_cloud_connectors", "外部云连接器", "允许接入付费或云端模型", 0]
  ];
  for (const [key, label, description, enabled] of flags) {
    insertIgnore("INSERT OR IGNORE INTO feature_flags(key, label, description, enabled, scope, updated_by, created_at, updated_at) VALUES (?, ?, ?, ?, 'system', 'u-owner', ?, ?)", [key, label, description, enabled, timestamp, timestamp]);
  }

  insertIgnore("INSERT OR IGNORE INTO notification_channels(id, name, kind, endpoint, enabled, events_json, secret_ref, created_by, created_at, updated_at) VALUES ('notify-local-log', '本地事件日志', 'local-log', '', 1, ?, '', 'u-owner', ?, ?)", [JSON.stringify(["job.failed", "review.blocked", "quota.warning", "system.changed"]), timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO notification_channels(id, name, kind, endpoint, enabled, events_json, secret_ref, created_by, created_at, updated_at) VALUES ('notify-webhook-optional', '本地 Webhook（可选）', 'webhook', 'http://127.0.0.1:8790/hooks/ai-drama', 0, ?, '', 'u-owner', ?, ?)", [JSON.stringify(["job.completed", "delivery.approved"]), timestamp, timestamp]);
  const seedApiKeyHash = hashApiClientKey("local-runner-demo-key");
  insertIgnore("INSERT OR IGNORE INTO api_clients(id, name, client_key, client_key_hash, client_key_prefix, key_version, status, scopes_json, created_by, created_at, updated_at) VALUES ('client-local-runner', '本地 Runner 客户端', ?, ?, 'local-runner-', 2, 'active', ?, 'u-owner', ?, ?)", [apiClientStorageMarker(seedApiKeyHash), seedApiKeyHash, JSON.stringify(["jobs:read", "jobs:write", "models:read"]), timestamp, timestamp]);
  dbRun("UPDATE api_clients SET organization_id = COALESCE(organization_id, 'org-studio-lab'), workspace_id = COALESCE(workspace_id, 'ws-local-aidrama') WHERE id = 'client-local-runner'");

  const services = [
    ["service-local-api", "local-api", "本地 API 服务", "local-service", "ready", "http://127.0.0.1:8787/api/health", 2, 0, "node-24"],
    ["service-sqlite", "sqlite", "SQLite 租户数据库", "database", "ready", dbPath, 1, 0, "node:sqlite"],
    ["service-script-runner", "script-runner", "剧本拆解 Runner", "runner", "ready", "local://script", 4, 0, "local"],
    ["service-image-runner", "image-runner", "单画面 Runner", "runner", "waiting-model", "local://image", null, 3, "local"],
    ["service-video-runner", "video-runner", "视频片段 Runner", "runner", "waiting-model", "local://video", null, 2, "local"],
    ["service-voice-runner", "voice-runner", "固定配音 Runner", "runner", "planned", "local://voice", null, 6, "local"],
    ["service-local-worker", "local-worker", "本地后台 Worker", "runner", "ready", "local://worker", 0, 0, "node-24"]
  ];
  for (const [id, serviceKey, label, kind, status, endpoint, latency, queueDepth, version] of services) {
    insertIgnore("INSERT OR IGNORE INTO service_health(id, service_key, label, kind, status, endpoint, latency_ms, queue_depth, version, last_heartbeat, metadata_json, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '{}', ?)", [id, serviceKey, label, kind, status, endpoint, latency, queueDepth, version, status === "ready" ? timestamp : null, timestamp]);
  }
}

function workflowStepDefinition(label, index) {
  const normalized = String(label || "");
  let jobKind = "";
  let requiresShot = false;
  if (/静态图|分镜图|关键帧|插画/.test(normalized)) { jobKind = "单画面关键帧"; requiresShot = true; }
  else if (/图生视频|视频生成|成片/.test(normalized)) { jobKind = "首尾帧图生视频"; requiresShot = true; }
  else if (/旁白|配音|TTS/.test(normalized)) { jobKind = "固定 TTS 配音"; requiresShot = true; }
  else if (/ASR|字幕|对齐/.test(normalized)) { jobKind = "ASR 台词校验"; requiresShot = true; }
  else if (/合成/.test(normalized)) jobKind = "剪辑合成清单";
  return { key: `step-${index + 1}`, label: normalized, jobKind, requiresShot, dependsOnPrevious: index > 0 };
}

function seedWorkflowTemplates() {
  const timestamp = now();
  for (const template of workflowTemplates) {
    const status = template.status === "主流程" || template.status === "可用" ? "active" : "draft";
    const steps = template.steps.map((label, index) => workflowStepDefinition(label, index));
    const gates = [
      { key: "single-frame", label: "一图一画面", blocking: true },
      { key: "continuity-lock", label: "角色 / 场景 / 道具连续性", blocking: true },
      { key: "voice-subtitle-asr", label: "声音 / 字幕 / ASR 对齐", blocking: true },
      { key: "clip-bridge", label: "片段衔接 / 实际末帧", blocking: true }
    ];
    insertIgnore(
      "INSERT OR IGNORE INTO workflow_templates(id, organization_id, workspace_id, template_key, version_number, name, category, status, description, steps_json, gates_json, default_adapter_id, created_by, created_at, updated_at) VALUES (?, NULL, NULL, ?, 1, ?, ?, ?, ?, ?, ?, 'owned-model-platform', 'u-owner', ?, ?)",
      [`workflow-global-${template.id}-v1`, template.id, template.name, template.id, status, template.warning || "", JSON.stringify(steps), JSON.stringify(gates), timestamp, timestamp]
    );
  }
}

function seedUserNotifications() {
  const timestamp = now();
  insertIgnore(
    "INSERT OR IGNORE INTO user_notifications(id, user_id, organization_id, workspace_id, project_id, category, event_key, severity, title, body, target_tab, target_id, metadata_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
    [
      "user-notification-seed-review",
      "u-owner",
      "org-studio-lab",
      "ws-local-aidrama",
      "thunder-mouth",
      "review",
      "review.changes_requested",
      "warning",
      "E01-S02 连续性检查需要确认",
      "角色服装与上一段实际末帧的连续性证据已提交，请在审片中心确认后再进入下一轮生成。",
      "qa",
      "review-thunder-mouth-continuity",
      JSON.stringify({ source: "seed", lane: "continuity-lock" }),
      timestamp
    ]
  );
  insertIgnore(
    "INSERT OR IGNORE INTO user_notifications(id, user_id, organization_id, workspace_id, project_id, category, event_key, severity, title, body, target_tab, target_id, metadata_json, created_at, read_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
    [
      "user-notification-seed-policy",
      "u-owner",
      "org-studio-lab",
      "ws-local-aidrama",
      null,
      "system",
      "system.changed",
      "info",
      "本地生产策略已生效",
      "平台当前保持 local-only：ComfyUI 仅作为可选适配器，外部云连接器默认关闭。",
      "system-generation",
      "generation.single_frame_only",
      JSON.stringify({ source: "seed", policy: "local-only" }),
      timestamp,
      timestamp
    ]
  );
}

function seedProductionGraph() {
  const timestamp = now();
  insertIgnore("INSERT OR IGNORE INTO series(id, project_id, title, logline, format, visual_style, continuity_rule, show_engine, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", ["series-thunder-mouth", "thunder-mouth", sampleProject.series.title, sampleProject.series.logline, sampleProject.series.format, sampleProject.series.visualStyle, sampleProject.series.continuityRule, sampleProject.series.showEngine, timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO seasons(id, series_id, season_number, title, created_at, updated_at) VALUES (?, ?, 1, '第一季', ?, ?)", ["season-thunder-mouth-1", "series-thunder-mouth", timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO episodes(id, season_id, episode_number, title, status, target_duration_sec, hook, cliffhanger, created_at, updated_at) VALUES (?, ?, 1, ?, 'production', ?, ?, ?, ?, ?)", ["episode-thunder-mouth-01", "season-thunder-mouth-1", sampleProject.episode.title, sampleProject.episode.targetDurationSec, sampleProject.episode.hook, sampleProject.episode.cliffhanger, timestamp, timestamp]);
  dbRun("UPDATE series SET show_engine = COALESCE(NULLIF(show_engine, ''), ?) WHERE id = ?", [sampleProject.series.showEngine, "series-thunder-mouth"]);
  dbRun("UPDATE episodes SET target_duration_sec = CASE WHEN target_duration_sec = 0 THEN ? ELSE target_duration_sec END, hook = COALESCE(NULLIF(hook, ''), ?), cliffhanger = COALESCE(NULLIF(cliffhanger, ''), ?) WHERE id = ?", [sampleProject.episode.targetDurationSec, sampleProject.episode.hook, sampleProject.episode.cliffhanger, "episode-thunder-mouth-01"]);
  for (const shot of sampleProject.shots) {
    insertIgnore("INSERT OR IGNORE INTO shots(id, episode_id, shot_number, title, status, first_frame_path, last_frame_path, continuity_json, created_at, updated_at) VALUES (?, ?, ?, ?, 'draft', ?, ?, ?, ?, ?)", [shot.id, "episode-thunder-mouth-01", Number(shot.id.split("-").pop()) || 1, shot.title, shot.firstFrame, shot.lastFrame, JSON.stringify({ transition: shot.transitionFromPrevious, camera: shot.camera }), timestamp, timestamp]);
    insertIgnore("INSERT OR IGNORE INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, 1, ?, 'locked', 'u-owner', ?)", [`${shot.id}-v1`, shot.id, JSON.stringify(shot), timestamp]);
    for (let index = 0; index < (shot.voiceLines || []).length; index += 1) {
      const line = shot.voiceLines[index];
      insertIgnore("INSERT OR IGNORE 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'draft', 'u-owner', ?, ?)", [line.id, shot.id, index + 1, line.characterId, line.text, line.emotion || "", line.mouthPlan || "规避嘴形", line.targetDurationSec || 2, "", line.audioFile || "", timestamp, timestamp]);
    }
  }
  insertIgnore("INSERT OR IGNORE INTO script_documents(id, organization_id, workspace_id, project_id, episode_id, version_number, title, source_type, content, status, analysis_json, created_by, created_at, updated_at) VALUES (?, 'org-studio-lab', 'ws-local-aidrama', 'thunder-mouth', 'episode-thunder-mouth-01', 1, ?, '原创短剧剧本', ?, 'analyzed', ?, 'u-owner', ?, ?)", [
    "script-thunder-mouth-v1",
    `第 1 集《${sampleProject.episode.title}》`,
    `第 1 集《${sampleProject.episode.title}》\n\n${sampleProject.episode.hook}\n\n陈宇：等一下，先别出去。雷暴来了，树下和金属牌旁边都不安全。\n唐夏：那我现在该往哪走？\n陈宇：看左边，积水旁可能有落地电线。别靠近，也别跨警戒线。\n唐夏：我给家里报个平安。`,
    JSON.stringify({ chapters: sampleProject.scriptStudio.chapters, extracted: sampleProject.scriptStudio.extracted, parser: "local-rule-v1" }),
    timestamp,
    timestamp
  ]);
  const assetSeeds = [
    ["asset-character-chen-yu", "character", "陈宇", "青年志愿者", "locked", "approved", "assets/thunder-mouth/characters/chen-yu/v1/portrait.png", { initial: "陈", tags: ["角色锁", "衣橱", "声线"], usage: "S01 全季", detail: sampleProject.characters[0].visualLock, lock: sampleProject.characters[0].costumeState }],
    ["asset-character-tang-xia", "character", "唐夏", "高中女生", "locked", "approved", "assets/thunder-mouth/characters/tang-xia/v1/portrait.png", { initial: "唐", tags: ["角色锁", "衣橱", "声线"], usage: "S01 前 6 集", detail: sampleProject.characters[1].visualLock, lock: sampleProject.characters[1].costumeState }],
    ["asset-location-metro-canopy-rain", "location", "地铁口玻璃连廊", "傍晚雷暴场景", "locked", "approved", "assets/thunder-mouth/locations/metro-canopy-rain/v1/location.png", { initial: "景", tags: ["场景锁", "天气", "机位"], usage: "E01 全部镜头", detail: sampleProject.locations[0].lock, lock: sampleProject.locations[0].cameraLock }],
    ["asset-prop-blue-umbrella", "prop", "折叠蓝伞", "连续性道具", "locked", "approved", "assets/thunder-mouth/props/blue-umbrella/v1/prop.png", { initial: "伞", tags: ["道具锁", "陈宇"], usage: "shot-01 ~ shot-03", detail: sampleProject.props[0].lock, lock: "右手低握，未打开" }],
    ["asset-prop-yellow-poncho", "prop", "黄色雨披", "连续性道具", "locked", "approved", "assets/thunder-mouth/props/yellow-poncho/v1/prop.png", { initial: "披", tags: ["道具锁", "唐夏"], usage: "shot-01 ~ shot-03", detail: sampleProject.props[1].lock, lock: "折在双臂上，不能突然穿上" }],
    ["asset-prop-warning-cones", "prop", "路锥和警戒线", "安全道具", "locked", "approved", "assets/thunder-mouth/props/warning-cones/v1/prop.png", { initial: "警", tags: ["道具锁", "安全"], usage: "shot-02", detail: sampleProject.props[2].lock, lock: "角色不跨越" }],
    ["asset-prop-phone", "prop", "手机", "连续性道具", "locked", "approved", "assets/thunder-mouth/props/phone/v1/prop.png", { initial: "机", tags: ["道具锁", "shot-03"], usage: "shot-03", detail: sampleProject.props[3].lock, lock: "低位拿出，不遮挡脸" }],
    ["asset-voice-chen-yu", "voice", "陈宇固定参考音频", "自然男声 · 已授权", "locked", "approved", "assets/thunder-mouth/voices/chen-yu/v1/reference.wav", { initial: "声", tags: ["声线锁", "IndexTTS-2.5", "已授权"], usage: "S01 全季", detail: "自然、克制、年轻男声；只作为陈宇正式参考音频。", lock: "必须保留授权证据引用，不使用随机原生声线。", voiceId: "voice-chen-yu", ttsModel: "IndexTTS-2.5", consentRef: "local-consent/chen-yu-v1" }],
    ["asset-voice-tang-xia", "voice", "唐夏固定参考音频", "自然女声 · 待补证据", "review", "needs-evidence", "assets/thunder-mouth/voices/tang-xia/v1/reference.wav", { initial: "声", tags: ["声线锁", "待授权证据"], usage: "S01 前 6 集", detail: "自然、清晰、带轻微紧张感的女声；正式批量配音前必须补齐授权证据。", lock: "单句试听可以排队，未审批前禁止批量 TTS。", voiceId: "voice-tang-xia", ttsModel: "IndexTTS-2.5", consentRef: "" }],
    ["asset-style-cel-shading", "style", "国漫 2D 赛璐璐基线", "项目视觉基线", "locked", "approved", "assets/thunder-mouth/styles/cel-shading/v1/style.json", { initial: "风", tags: ["视觉锁", "9:16", "雨景"], usage: "全季复用", detail: sampleProject.series.visualStyle, lock: "禁止多格、拼图、多时间点" }],
    ["asset-lora-rain-local", "lora", "雷雨口 · 本地一致性适配", "自有模型平台资产", "draft", "needs-evidence", "assets/thunder-mouth/lora/rain-local/v1/model.safetensors", { initial: "L", tags: ["local-only", "角色", "场景"], usage: "shot-01 ~ shot-03", detail: "仅作为自有模型平台可选增强，不替代角色、场景、道具锁。", lock: "美术负责人确认后才能进入生产" }]
  ];
  for (const [id, kind, name, subtitle, lockStatus, rightsStatus, storagePath, metadata] of assetSeeds) {
    insertIgnore("INSERT OR IGNORE INTO assets(id, project_id, kind, name, lock_status, created_by, created_at, updated_at) VALUES (?, 'thunder-mouth', ?, ?, ?, 'u-owner', ?, ?)", [id, kind, name, lockStatus, timestamp, timestamp]);
    insertIgnore("INSERT OR IGNORE INTO asset_versions(id, asset_id, version_number, storage_path, rights_status, metadata_json, created_by, created_at) VALUES (?, ?, 1, ?, ?, ?, 'u-owner', ?)", [`${id}-v1`, id, storagePath, rightsStatus, JSON.stringify({ subtitle, ...metadata }), timestamp]);
    dbRun("UPDATE assets SET current_version_id = COALESCE(current_version_id, ?), updated_at = ? WHERE id = ?", [`${id}-v1`, timestamp, id]);
  }
  const bindingSeeds = [
    ["asset-character-chen-yu", ["shot-01", "shot-02", "shot-03"], "character"],
    ["asset-character-tang-xia", ["shot-01", "shot-02", "shot-03"], "character"],
    ["asset-location-metro-canopy-rain", ["shot-01", "shot-02", "shot-03"], "location"],
    ["asset-prop-blue-umbrella", ["shot-01", "shot-02", "shot-03"], "prop"],
    ["asset-prop-yellow-poncho", ["shot-01", "shot-02", "shot-03"], "prop"],
    ["asset-prop-warning-cones", ["shot-02"], "prop"],
    ["asset-prop-phone", ["shot-03"], "prop"]
  ];
  for (const [assetId, shotIds, usageRole] of bindingSeeds) {
    for (const shotId of shotIds) insertIgnore("INSERT OR IGNORE INTO asset_bindings(id, asset_id, shot_id, usage_role, created_by, created_at) VALUES (?, ?, ?, ?, 'u-owner', ?)", [`binding-${assetId}-${shotId}`, assetId, shotId, usageRole, timestamp]);
  }
  for (const job of sampleProject.productionJobs) {
    const shotId = sampleProject.shots.some((shot) => shot.id === job.shotId) ? job.shotId : null;
    insertIgnore("INSERT OR IGNORE INTO generation_jobs(id, organization_id, workspace_id, project_id, episode_id, shot_id, kind, adapter_id, status, priority, cost_policy, output_path, qa_status, created_by, created_at, updated_at) VALUES (?, 'org-studio-lab', 'ws-local-aidrama', 'thunder-mouth', 'episode-thunder-mouth-01', ?, ?, ?, ?, 50, ?, ?, ?, 'u-owner', ?, ?)", [job.id, shotId, job.kind, job.adapter, job.status, job.costPolicy, job.output, job.qa, timestamp, timestamp]);
  }
  insertIgnore("INSERT OR IGNORE INTO usage_events(id, organization_id, workspace_id, project_id, user_id, kind, units, unit_name, estimated_cost, metadata_json, created_at) VALUES ('usage-seed-001', 'org-studio-lab', 'ws-local-aidrama', 'thunder-mouth', 'u-owner', 'image-generation', 36, 'clips', 86, '{\"source\":\"seed\"}', ?)", [timestamp]);
  const audits = [
    ["aud-001", "org-studio-lab", "ws-local-aidrama", "thunder-mouth", "u-owner", "project.created", "project", "thunder-mouth", "ok", "{}"],
    ["aud-002", "org-studio-lab", "ws-local-aidrama", "thunder-mouth", "u-owner", "policy.single-frame.checked", "shot", "shot-01", "pass", "{\"gate\":\"single_frame_only\"}"],
    ["aud-003", "org-studio-lab", "ws-local-aidrama", "thunder-mouth", "u-owner", "exports.write", "delivery", "project-template", "ok", "{}"],
    ["aud-004", "org-studio-lab", "ws-local-aidrama", "thunder-mouth", "u-owner", "cloud.connector.blocked", "model_connector", "paid-node", "requires-approval", "{\"policy\":\"local_runner_only\"}"]
  ];
  for (const audit of audits) {
    insertIgnore("INSERT OR IGNORE INTO audit_logs(id, organization_id, workspace_id, project_id, actor_user_id, action, target_type, target_id, result, metadata_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [...audit, timestamp]);
  }

  insertIgnore("INSERT OR IGNORE INTO series(id, project_id, title, logline, format, visual_style, continuity_rule, show_engine, created_at, updated_at) VALUES (?, ?, ?, ?, 'vertical-9:16', ?, ?, ?, ?, ?)", [
    "series-northstar-pilot",
    "northstar-pilot",
    "山海志异·试播集",
    "北辰试制部用于验证多组织隔离的原创国漫试播项目。",
    "原创国漫 2D 动画风格，竖屏 9:16，单一完整画面，干净线稿与克制动效。",
    "角色、场景、道具、天气、镜头和声线全部随项目隔离，禁止串用其他组织素材。",
    "以单镜头冲突推进试播片段，所有生成先经过本地 Runner 与审片门。",
    timestamp,
    timestamp
  ]);
  insertIgnore("INSERT OR IGNORE INTO seasons(id, series_id, season_number, title, created_at, updated_at) VALUES (?, ?, 1, '第一季', ?, ?)", ["season-northstar-pilot-1", "series-northstar-pilot", timestamp, timestamp]);
  insertIgnore("INSERT OR IGNORE INTO episodes(id, season_id, episode_number, title, status, target_duration_sec, hook, cliffhanger, created_at, updated_at) VALUES (?, ?, 1, '雾中石门', 'production', 45, '雾气里出现不该存在的石门。', '门后传来第二个自己的声音。', ?, ?)", ["episode-northstar-pilot-01", "season-northstar-pilot-1", timestamp, timestamp]);
  const northstarShot = {
    id: "shot-northstar-pilot-001",
    title: "雾中石门出现",
    durationSec: 6,
    characterIds: ["northstar-yun"],
    locationId: "northstar-fog-gate",
    propIds: ["northstar-bronze-bell"],
    camera: "稳定中景，角色在画面右侧停步，石门在远处雾中显现，单一连续画面。",
    action: "少年抬手按住铜铃，雾气向石门方向收束，镜头不切分。",
    firstFrame: "episode-start",
    lastFrame: "pending-actual-last-frame",
    transitionFromPrevious: "episode-start",
    prompt: "ONE SINGLE COMPLETE 9:16 CHINESE ANIMATION FRAME, no panels, one scene only.",
    negativePrompt: "no split screen, no comic panel, no collage, no contact sheet, no storyboard",
    seed: 260831
  };
  insertIgnore("INSERT OR IGNORE INTO shots(id, episode_id, shot_number, title, status, first_frame_path, last_frame_path, continuity_json, created_at, updated_at) VALUES (?, ?, 1, ?, 'draft', ?, ?, ?, ?, ?)", [
    northstarShot.id,
    "episode-northstar-pilot-01",
    northstarShot.title,
    northstarShot.firstFrame,
    northstarShot.lastFrame,
    JSON.stringify({ camera: northstarShot.camera, transition: northstarShot.transitionFromPrevious }),
    timestamp,
    timestamp
  ]);
  insertIgnore("INSERT OR IGNORE INTO shot_versions(id, shot_id, version_number, payload_json, status, created_by, created_at) VALUES (?, ?, 1, ?, 'draft', 'u-producer', ?)", [`${northstarShot.id}-v1`, northstarShot.id, JSON.stringify(northstarShot), timestamp]);
  dbRun("UPDATE shots SET current_version_id = COALESCE(current_version_id, ?) WHERE id = ?", [`${northstarShot.id}-v1`, northstarShot.id]);
  insertIgnore("INSERT OR IGNORE INTO generation_jobs(id, organization_id, workspace_id, project_id, episode_id, shot_id, kind, adapter_id, status, priority, cost_policy, output_path, qa_status, request_json, result_json, error_message, created_by, created_at, updated_at) VALUES (?, 'org-northstar', 'ws-northstar-main', 'northstar-pilot', 'episode-northstar-pilot-01', ?, '单画面关键帧', 'northstar-image', 'queued', 45, 'local', 'storage/jobs/northstar-pilot/shot-001/keyframe.png', 'wait', ?, '{}', '', 'u-producer', ?, ?)", [
    "job-northstar-pilot-keyframe-001",
    northstarShot.id,
    JSON.stringify({ schema: "ai-drama.job.v1", seeded: true, job: { adapterId: "northstar-image" }, constraints: { singleFrameOnly: true, imageOutputCount: 1 } }),
    timestamp,
    timestamp
  ]);
  insertIgnore("INSERT OR IGNORE INTO reviews(id, organization_id, workspace_id, project_id, shot_id, lane, status, score, evidence_json, created_at, updated_at) VALUES (?, 'org-northstar', 'ws-northstar-main', 'northstar-pilot', ?, 'single-frame', 'pending', NULL, ?, ?, ?)", [
    "review-northstar-pilot-single-frame",
    northstarShot.id,
    JSON.stringify({ blockers: ["等待北辰审片员确认一图一画面证据。"], source: "seed" }),
    timestamp,
    timestamp
  ]);
  insertIgnore("INSERT OR IGNORE INTO deliveries(id, organization_id, workspace_id, project_id, version, manifest_path, channel, status, created_at, updated_at) VALUES (?, 'org-northstar', 'ws-northstar-main', 'northstar-pilot', 'v0.1-internal', 'storage/deliveries/northstar-pilot/v0.1/delivery-manifest.json', 'internal', 'review', ?, ?)", [
    "delivery-northstar-pilot-v01",
    timestamp,
    timestamp
  ]);
}

withTransaction(() => {
  seedRoles();
  seedUsers();
  seedCredentials();
  seedOrganization({
    id: "org-studio-lab",
    name: "星河短剧实验室",
    slug: "xinghe-studio",
    ownerUserId: "u-owner",
    description: "本地 AI 漫剧与短剧主生产空间",
    workspaceId: "ws-local-aidrama",
    workspaceName: "短剧生产中心",
    workspaceSlug: "drama-production",
    projectId: "thunder-mouth",
    projectName: "雷雨口",
    projectType: "AI 漫剧",
    readiness: 72,
    risk: "medium"
  });
  seedOrganization({
    id: "org-northstar",
    name: "北辰内容厂牌",
    slug: "northstar-content",
    ownerUserId: "u-owner",
    description: "第二个客户组织，用于验证跨组织隔离",
    workspaceId: "ws-northstar-main",
    workspaceName: "北辰试制部",
    workspaceSlug: "pilot-room",
    projectId: "northstar-pilot",
    projectName: "山海志异·试播集",
    projectType: "AI 漫剧",
    readiness: 35,
    risk: "medium"
  });
  insertIgnore("INSERT OR IGNORE INTO workspaces(id, organization_id, name, slug, description, status, created_at, updated_at) VALUES ('ws-pilot', 'org-studio-lab', '素材实验室', 'asset-lab', '角色、场景和模型试验空间', 'active', ?, ?)", [now(), now()]);
  seedCommercialPlans();
  seedOrganizationEntitlements();
  rollQuotaPeriods();
  seedCommercialApprovals();
  seedCustomersAndContracts();
  seedMembersAndProjects();
  seedModels();
  seedModelCatalog();
  seedModelRoutes();
  seedStyleKits();
  seedSystemGovernance();
  seedWorkflowTemplates();
  seedProductionGraph();
  seedKnowledgeLibrary();
  seedUserNotifications();
});

export function resetDatabaseForTests() {
  db.exec("DELETE FROM audit_logs; DELETE FROM usage_events; DELETE FROM generation_jobs;");
}

export const databaseInfo = {
  path: dbPath,
  engine: "node:sqlite",
  seededAt: now()
};