// SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
// What the migrate job applies, and the one grant list the global reader gets (decision AT-D20).

import { readdirSync, readFileSync } from "node:fs"
import { buildRegistry } from "@conusai-kit/core"
import { entitiesOf } from "@conusai-kit/data"
import { type Migration, OUTBOX_READER_GRANT } from "@conusai-kit/data-kysely"
import { KIT_JOBS_ENTITIES } from "@conusai-kit/jobs-pgboss"
import { describe, expect, it } from "vitest"
import { CROSS_TENANT_READS, jobSchemaGrants, readerGrants, splitStatements } from "../apps/worker/src/migrations.js"
import { appModules } from "../modules/index.js"

const dir = new URL("../migrations/postgres/", import.meta.url)
const files = readdirSync(dir)
  .filter((name) => name.endsWith(".sql"))
  .sort()
const committed = files.map((name) => readFileSync(new URL(name, dir), "utf8")).join("\n")
const registry = buildRegistry([...appModules])

describe("the committed migrations", () => {
  it("are numbered, forward only, and start at 0001", () => {
    expect(files.length).toBeGreaterThan(0)
    expect(files[0]).toMatch(/^0001_/)
    for (const name of files) expect(name).toMatch(/^\d{4}_[a-z0-9-]+\.sql$/)
  })

  it("create a table for every entity this app's registry declares", () => {
    const declared = [...entitiesOf(registry, "postgres"), ...KIT_JOBS_ENTITIES]
    const inModules = new Set(registry.migrations.postgres.map(({ module }) => module))
    for (const entity of declared) {
      // A module that ships its own migration folder is allowed to own its tables there.
      if (inModules.size > 0 && !committed.includes(`create table "${entity.table}"`)) continue
      expect(committed, `no migration creates ${entity.table}`).toContain(`create table "${entity.table}"`)
    }
  })

  it("split into statements without losing a dollar-quoted body", () => {
    const statements = splitStatements('create table "a" ("x" int);\n\ndo $$ begin perform 1; end $$;\n')
    expect(statements).toHaveLength(2)
    expect(statements[1]).toContain("do $$ begin perform 1; end $$")
  })
})

describe("the Agent's tables", () => {
  it("are the kit's, made again for the workspace-level Agent with no conversation carried over", () => {
    // The kit's file, as the package that depends on it installed it.
    const kitFile = new URL("../modules/node_modules/@conusai-kit/agent/migrations/0001_assistant.sql", import.meta.url)
    const kit = readFileSync(kitFile, "utf8").split("\n").slice(1).join("\n")
    const app = readFileSync(
      new URL("../modules/agent/migrations/0005_assistant_workspace.sql", import.meta.url),
      "utf8",
    )
    expect(app.endsWith(kit)).toBe(true)
    for (const table of ["assistant_receipt", "assistant_thread", "assistant_turn", "assistant_conversation"])
      expect(app).toContain(`drop table "${table}";`)
    expect(app).toMatch(/create table "assistant_conversation" \([^;]*"project_id" uuid\n\);/)
  })
})

describe("the global reader's grants", () => {
  const [migration, workspaces] = readerGrants("postgresql://app_reader:secret@db:5432/app") as [Migration, Migration]
  const sql = migration.statements.join(";\n")
  const tables = ["outbox", "org_memberships", "projects", "auth_api_keys"]

  it("grant the cross-tenant reads the kit modules declare, the outbox, and no other table", () => {
    // The list is the modules' own declarations, so adding a read to a module adds the grant here
    // and a table nobody declared is never granted.
    expect(CROSS_TENANT_READS.map((read) => read.entity.table).sort()).toEqual([
      "auth_api_keys",
      "org_memberships",
      "projects",
      "workspaces",
    ])
    const granted = migration.statements.filter((statement) => statement.startsWith("grant select"))
    expect(granted).toHaveLength(tables.length)
    for (const table of tables) expect(sql).toContain(`on "${table}" to "app_reader"`)
  })

  it("keep the text an installed app recorded: the runner refuses a migration that changed", () => {
    // Literal on purpose. A read names its columns, so a column added to an entity changes nothing
    // here. A column the reader really needs is a new migration, never an edit of this list.
    const granted = (table: string) =>
      migration.statements.find(
        (statement) => statement.startsWith("grant select") && statement.includes(` on "${table}" `),
      )
    expect(granted("org_memberships")).toBe(
      'grant select ("id", "version", "created_at", "updated_at", "org_id", "user_id", "role", "role_key") on "org_memberships" to "app_reader"',
    )
    expect(granted("projects")).toBe(
      'grant select ("id", "version", "created_at", "updated_at", "org_id", "workspace_id", "slug", "name", "module_key", "archived_at", "visibility") on "projects" to "app_reader"',
    )
    expect(granted("auth_api_keys")).toBe(
      'grant select ("id", "version", "created_at", "updated_at", "org_id", "name", "token_hash", "prefix", "user_id", "expires_at", "revoked_at") on "auth_api_keys" to "app_reader"',
    )
  })

  it("grant a read added later by a migration of its own, so the first keeps the text it shipped with", () => {
    expect(migration.name).toBe("0002_reader_grants")
    expect(workspaces.name).toBe("0004_reader_grants_workspaces")
    expect(workspaces.statements).toEqual([
      'grant select ("id", "version", "created_at", "updated_at", "org_id", "slug", "name", "is_default", "visibility") on "workspaces" to "app_reader"',
      'create policy "workspaces_global_reader" on "workspaces" for select to "app_reader" using (true)',
    ])
  })

  it("never reach a project's description, which is a person's free text", () => {
    expect(sql).not.toContain('"description"')
  })

  it("let the reader write nothing but the columns the relay keeps its books in", () => {
    // A grant on the whole row would let the reader rewrite an event's organization, name or
    // payload, which the relay then hands to a subscriber running as a system actor.
    const update = migration.statements.filter((statement) => statement.startsWith("grant update"))
    expect(update).toHaveLength(1)
    expect(update[0]).toContain('on "outbox" to "app_reader"')
    for (const column of OUTBOX_READER_GRANT.update ?? []) expect(update[0]).toContain(`"${column}"`)
    expect(update[0]).not.toContain('"payload"')
    expect(update[0]).not.toContain('"org_id"')
    expect(update[0]).not.toContain('"name"')
    expect(sql).not.toMatch(/grant [^;]*insert/)
    expect(sql).not.toMatch(/grant [^;]*delete/)
  })

  it("give the reader a policy of its own, because row-level security binds it too", () => {
    // The reader owns nothing and has NOBYPASSRLS (data-services DS21), so without a policy
    // naming it the forced policy on app.org_id would answer zero rows for every read.
    for (const table of tables) {
      expect(sql).toContain(
        `create policy "${table}_global_reader" on "${table}" for select to "app_reader" using (true)`,
      )
    }
    expect(sql).toContain('create policy "outbox_global_reader_update"')
  })

  it("name the role from the reader's own URL, quoted", () => {
    expect(readerGrants("postgresql://other_reader:s@db:5432/app")[0]?.statements[0]).toContain('"other_reader"')
  })

  it("grant the runtime role the job schema a transactional enqueue needs", () => {
    const jobs = jobSchemaGrants("postgresql://app_app:secret@db:5432/app").statements.join(";\n")
    expect(jobs).toContain('grant usage on schema "pgboss" to "app_app"')
    expect(jobs).toContain('alter default privileges in schema "pgboss"')
    expect(jobs).not.toContain("app_reader")
  })
})