cms-website/tests/integration/db.jobs.test.ts

// 构建/验收/审计域集成测试（0006 迁移）：build_jobs 幂等键唯一 + 状态 enum 八值、
// build_input_snapshots 不可变触发器 + build_job_id UNIQUE、build_artifacts 三元组唯一、
// audit_logs 追加式（UPDATE/DELETE 拒绝）、validation_issues.severity 可写。
// Task 13 起夹具走公共 helpers（createSiteFixture/withImmutableCleanup/withTestDb/rejectionMessage）；
// 生命周期纪律不变：随机后缀插入 + finally 反序清理；两张不可变表（build_input_snapshots /
// audit_logs）清场走 DISABLE→删→ENABLE 封装。
// audit_logs 组织级行（site_id NULL，spec §5.8）无 site 可挂——清场按 fixture 唯一 command_id 圈定。
import { describe, it, expect } from 'vitest'
import { createSiteFixture, withImmutableCleanup, withTestDb, rejectionMessage, type Db } from './helpers.js'

type Fixture = {
  siteId: string
  buildJobId: string
  idempotencyKey: string
  snapshotId: string
  validationRunId: string
  auditCommandId: string
}

// 建链（FK 依赖顺序）：site → build_job → build_input_snapshot；validation_run 挂 site+job；
// audit_logs 挂 site（站点级行；组织级 NULL 行在测试体内插，由 command_id 清场兜底）。
async function setupJobsFixture(db: Db): Promise<Fixture> {
  const site = await createSiteFixture(db, 'jobs')
  const rand = site.rand
  const siteId = site.siteId
  const job = (await db.execute(`insert into build_jobs (id, site_id, status, idempotency_key, attempt)
    values (gen_random_uuid(), '${siteId}', 'queued', 'build-idem-${rand}', 0) returning id, idempotency_key`)) as any
  const buildJobId = job[0].id
  const snap = (await db.execute(`insert into build_input_snapshots (id, build_job_id, site_id,
      content_snapshot_hash, route_snapshot_hash, asset_manifest_hash,
      theme_key, theme_version, theme_source_hash, dependency_lock_hash, source_commit,
      node_version, pnpm_version, astro_version, locale, timezone, build_timestamp, manifest_path)
    values (gen_random_uuid(), '${buildJobId}', '${siteId}',
      'content-hash-${rand}', 'route-hash-${rand}', 'asset-hash-${rand}',
      'sf-default', '1.0.0', 'theme-hash-${rand}', 'lock-hash-${rand}', 'commit-${rand}',
      '22.11.0', '10.7.1', '5.0.0', 'en', 'UTC', now(), 'manifest://${rand}.json') returning id`)) as any
  const run = (await db.execute(`insert into validation_runs (id, site_id, build_job_id, rule_version, status)
    values (gen_random_uuid(), '${siteId}', '${buildJobId}', 'gate@1', 'running') returning id`)) as any
  await db.execute(`insert into audit_logs (id, site_id, actor_type, actor_label, command_id, action, object_type, object_id)
    values (gen_random_uuid(), '${siteId}', 'cli_user', 'tester@siteforge', 'audit-${rand}', 'build.start', 'build_job', '${buildJobId}')`)
  return {
    siteId, buildJobId, idempotencyKey: job[0].idempotency_key, snapshotId: snap[0].id,
    validationRunId: run[0].id, auditCommandId: `audit-${rand}`,
  }
}

// 反序清理：子表常规删除；两张不可变表逐一 DISABLE→删→ENABLE（嵌套 finally 保证触发器恢复）；
// audit_logs 按 fixture 唯一 command_id 删（含组织级 NULL site 行）；最后删 site。
async function cleanupJobsFixture(db: Db, f: Fixture) {
  await db.execute(`delete from validation_issues where validation_run_id = '${f.validationRunId}'`)
  await db.execute(`delete from validation_runs where site_id = '${f.siteId}'`)
  await db.execute(`delete from build_artifacts where build_job_id = '${f.buildJobId}'`)
  await withImmutableCleanup(db, 'build_input_snapshots', 'build_input_snapshots_immutable', `site_id = '${f.siteId}'`)
  await withImmutableCleanup(db, 'audit_logs', 'audit_logs_immutable', `command_id = '${f.auditCommandId}'`)
  await db.execute(`delete from build_jobs where site_id = '${f.siteId}'`)
  await db.execute(`delete from sites where id = '${f.siteId}'`)
}

async function withJobsFixture(fn: (db: Db, f: Fixture) => Promise<void>) {
  await withTestDb(async (db) => {
    const f = await setupJobsFixture(db)
    try {
      await fn(db, f)
    } finally {
      await cleanupJobsFixture(db, f)
    }
  })
}

// 不可变快照探测插入：换新 id 但复用 fixture 的 build_job_id（唯一键探测用）。
function snapshotProbe(f: Fixture, buildJobId: string) {
  const suffix = Math.random().toString(36).slice(2, 8)
  return `insert into build_input_snapshots (id, build_job_id, site_id,
      content_snapshot_hash, route_snapshot_hash, asset_manifest_hash,
      theme_key, theme_version, theme_source_hash, dependency_lock_hash, source_commit,
      node_version, pnpm_version, astro_version, locale, timezone, build_timestamp, manifest_path)
    values (gen_random_uuid(), '${buildJobId}', '${f.siteId}',
      'content-hash-${suffix}', 'route-hash-${suffix}', 'asset-hash-${suffix}',
      'sf-default', '1.0.0', 'theme-hash-${suffix}', 'lock-hash-${suffix}', 'commit-${suffix}',
      '22.11.0', '10.7.1', '5.0.0', 'en', 'UTC', now(), 'manifest://${suffix}.json')`
}

describe('构建/验收/审计域约束', () => {
  it('build_jobs.idempotency_key 唯一：同 key 第二条拒绝', async () => {
    await withJobsFixture(async (db, f) => {
      expect(await rejectionMessage(db, `insert into build_jobs (id, site_id, status, idempotency_key, attempt)
        values (gen_random_uuid(), '${f.siteId}', 'queued', '${f.idempotencyKey}', 0)`))
        .toMatch(/build_jobs_idempotency_key_unique/)
    })
  })

  it('build_jobs.status enum 八值（queued/snapshotting/building/validating/succeeded/failed/cancelled/stale）可写往返，第九值拒绝', async () => {
    await withJobsFixture(async (db, f) => {
      for (const status of ['snapshotting', 'building', 'validating', 'succeeded', 'failed', 'cancelled', 'stale', 'queued']) {
        await db.execute(`update build_jobs set status = '${status}' where id = '${f.buildJobId}'`)
        const row = (await db.execute(`select status from build_jobs where id = '${f.buildJobId}'`)) as any
        expect(row[0].status).toBe(status)
      }
      // enum 在库：类型化列拒绝八值之外的任意字符串
      expect(await rejectionMessage(db, `update build_jobs set status = 'bogus' where id = '${f.buildJobId}'`))
        .toMatch(/build_job_status/)
    })
  })

  it('build_input_snapshots 不可变：UPDATE 与 DELETE 均被触发器拒绝', async () => {
    await withJobsFixture(async (db, f) => {
      expect(await rejectionMessage(db, `update build_input_snapshots set theme_version = '9.9.9' where id = '${f.snapshotId}'`))
        .toMatch(/immutable table build_input_snapshots/)
      expect(await rejectionMessage(db, `delete from build_input_snapshots where id = '${f.snapshotId}'`))
        .toMatch(/immutable table build_input_snapshots/)
    })
  })

  it('build_input_snapshots.build_job_id UNIQUE：同 job 第二条快照拒绝', async () => {
    await withJobsFixture(async (db, f) => {
      expect(await rejectionMessage(db, snapshotProbe(f, f.buildJobId)))
        .toMatch(/build_input_snapshots_build_job_id_unique/)
    })
  })

  it('build_artifacts UNIQUE(build_job_id, kind, storage_path)：重复拒绝，换 kind 放行', async () => {
    await withJobsFixture(async (db, f) => {
      const artifact = (kind: string, path: string) =>
        `insert into build_artifacts (id, build_job_id, kind, storage_path, sha256, byte_size)
          values (gen_random_uuid(), '${f.buildJobId}', '${kind}', '${path}', 'sha256-${Math.random().toString(36).slice(2, 8)}', 100)`
      await db.execute(artifact('dist', 'build://out/dist.zip'))
      // 同 job 同 kind 同路径（如重试上传同一路径）→ 唯一索引拒绝
      expect(await rejectionMessage(db, artifact('dist', 'build://out/dist.zip')))
        .toMatch(/build_artifacts_uq/)
      // 对照组：同路径换 kind → 允许（log 与 dist 可以同路径前缀语义，约束打在三元组上），由 finally 清理
      await db.execute(artifact('log', 'build://out/dist.zip'))
    })
  })

  it('audit_logs 追加式：UPDATE 与 DELETE 均被触发器拒绝；组织级行（site_id NULL）可写入', async () => {
    await withJobsFixture(async (db, f) => {
      // spec §5.8：组织级操作无站点上下文，site_id 为 NULL 也必须可记
      const orgRow = (await db.execute(`insert into audit_logs (id, site_id, actor_type, actor_label, command_id, action, object_type, object_id)
        values (gen_random_uuid(), null, 'system', 'siteforge-scheduler', '${f.auditCommandId}', 'org.gc', 'organization', 'global') returning id`)) as any
      const siteRow = (await db.execute(`select id from audit_logs where site_id = '${f.siteId}' and command_id = '${f.auditCommandId}'`)) as any
      for (const rowId of [orgRow[0].id, siteRow[0].id]) {
        expect(await rejectionMessage(db, `update audit_logs set action = 'tampered' where id = '${rowId}'`))
          .toMatch(/immutable table audit_logs/)
        expect(await rejectionMessage(db, `delete from audit_logs where id = '${rowId}'`))
          .toMatch(/immutable table audit_logs/)
      }
    })
  })

  it('validation_issues.severity 列存在可写：P0/P1/P2/P3 text 往返', async () => {
    await withJobsFixture(async (db, f) => {
      const issue = (await db.execute(`insert into validation_issues (id, validation_run_id, severity, rule, url, evidence, resolution_status)
        values (gen_random_uuid(), '${f.validationRunId}', 'P0', 'route.unresolved', '/probe', '[]', 'open') returning id`)) as any
      const issueId = issue[0].id
      // severity 是 text 而非 enum：四个级别逐一可写、原样读回（不折叠大小写、不做映射）
      for (const severity of ['P1', 'P2', 'P3', 'P0']) {
        await db.execute(`update validation_issues set severity = '${severity}' where id = '${issueId}'`)
        const row = (await db.execute(`select severity from validation_issues where id = '${issueId}'`)) as any
        expect(row[0].severity).toBe(severity)
      }
    })
  })
})