// SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
// The committed kernel schema, and the rule that keeps it committable.
//
// `migrations/postgres/0001_kernel.sql` is `schemaStatements()` over the kernel's own entities,
// written once. It is a snapshot of the *kernel's* tables alone, not of the whole registry, so
// adding a module does not change this file -- a module ships its own numbered migration, which
// is what a forward-only runner needs (it refuses a migration whose text changed after it ran).
// A kit upgrade that changes a kernel table does change it, and that is exactly the moment a
// customer has to see a new migration in review.

import { readFileSync } from "node:fs"
import { authModule } from "@conusai-kit/auth"
import { buildRegistry } from "@conusai-kit/core"
import { type AnyEntityDef, defineEntity, entitiesOf, managedColumnsFor } from "@conusai-kit/data"
import {
  columnName,
  createTable,
  enableRowLevelSecurity,
  isNullable,
  postgresType,
  quoted,
  schemaStatements,
} from "@conusai-kit/data-kysely"
import { createMysqlTable, mysqlQuoted, mysqlType } from "@conusai-kit/data-kysely/mysql"
import { createMongoCollection, type MongoCommand, mongoValidator } from "@conusai-kit/data-mongo"
import { KIT_JOBS_ENTITIES } from "@conusai-kit/jobs-pgboss"
import {
  appUserIdentities,
  invitations,
  orgMemberships,
  projectMemberships,
  projects,
  tenancyModule,
  tenancyRoles,
  workspaceMemberships,
} from "@conusai-kit/tenancy"
import { describe, expect, it } from "vitest"
import { z } from "zod"
import { kernelSchema } from "../apps/worker/src/migrations.js"
import { SPDX_LINE } from "../scripts/licence.mjs"

/**
 * The columns the R-wave roles migration (0008) added to tables 0001 created: a role key on the
 * memberships and invitations, a project's visibility. 0001 is the snapshot from before them.
 */
const ADDED_BY_0008: ReadonlyArray<readonly [AnyEntityDef, string]> = [
  [orgMemberships, "roleKey"],
  [workspaceMemberships, "roleKey"],
  [invitations, "roleKey"],
  [projects, "visibility"],
]

/** The column 0009 added to a table 0001 created: a project's description. */
const ADDED_BY_0009: ReadonlyArray<readonly [AnyEntityDef, string]> = [[projects, "description"]]

/** The column 0010 added to a table 0001 created: when an invitation was revoked or re-issued. */
const ADDED_BY_0010: ReadonlyArray<readonly [AnyEntityDef, string]> = [[invitations, "revokedAt"]]

const fieldsOf = (added: ReadonlyArray<readonly [AnyEntityDef, string]>, entity: AnyEntityDef) =>
  added.filter(([changed]) => changed.table === entity.table).map(([, field]) => field)

/** An entity as 0001 created it: without the fields a later migration added, nor their indexes. */
const asOf0001 = (entity: AnyEntityDef): AnyEntityDef =>
  without(entity, [
    ...fieldsOf(ADDED_BY_0008, entity),
    ...fieldsOf(ADDED_BY_0009, entity),
    ...fieldsOf(ADDED_BY_0010, entity),
  ])

/** An entity as 0008 left it: a committed validator is the whole of its table at that point. */
const asOf0008 = (entity: AnyEntityDef): AnyEntityDef =>
  without(entity, [...fieldsOf(ADDED_BY_0009, entity), ...fieldsOf(ADDED_BY_0010, entity)])

/** An entity as 0009 left it. */
const asOf0009 = (entity: AnyEntityDef): AnyEntityDef => without(entity, fieldsOf(ADDED_BY_0010, entity))

function without(entity: AnyEntityDef, later: readonly string[]): AnyEntityDef {
  if (later.length === 0) return entity
  const schema = entity.schema
  if (!(schema instanceof z.ZodObject)) throw new Error(`${entity.table}: an entity's schema is an object`)
  const managed = managedColumnsFor(entity.tenancy)
  const fields = Object.fromEntries(
    Object.entries(schema.shape).filter(([field]) => !managed.includes(field) && !later.includes(field)),
  )
  const before = (groups: readonly (readonly string[])[]) =>
    groups.filter((group) => !group.some((field) => later.includes(field))).map((group) => [...group])
  return defineEntity({
    name: entity.name,
    table: entity.table,
    tenancy: entity.tenancy,
    fields,
    unique: before(entity.unique),
    indexes: before(entity.indexes),
  })
}

// Tenancy and sign-in alone: later kit tables have their own forward migrations, because 0001 is
// never edited once it has run. app_user_identity belongs to 0006 and the custom roles and project
// memberships to 0008, not this historical snapshot.
const LATER_TABLES = new Set([appUserIdentities.table, tenancyRoles.table, projectMemberships.table])
const kernelEntities = [...entitiesOf(buildRegistry([tenancyModule, authModule]), "postgres"), ...KIT_JOBS_ENTITIES]
  .filter((entity) => !LATER_TABLES.has(entity.table))
  .map(asOf0001)

describe("the kernel migration", () => {
  it("matches the committed 0001_kernel.sql", async () => {
    // The file leads with the template's SPDX line (AT5.1); the runner reads it as the comment it is.
    await expect(`-- ${SPDX_LINE}\n${kernelSchema(kernelEntities)}`).toMatchFileSnapshot(
      "../migrations/postgres/0001_kernel.sql",
    )
  })

  it("puts forced row-level security on every tenant table", () => {
    const sql = schemaStatements(kernelEntities).join(";\n")
    for (const entity of kernelEntities) {
      if (entity.tenancy === "global") continue
      expect(sql, `${entity.table} is not forced`).toContain(`alter table "${entity.table}" force row level security`)
      expect(sql).toContain(`create policy "${entity.table}_tenant" on "${entity.table}"`)
    }
  })

  it("compares a tenant row against the transaction's organization and nothing else", () => {
    const sql = schemaStatements(kernelEntities).join(";\n")
    // The policy reads NULLIF(current_setting(...), '')::uuid: a pooled connection whose previous
    // transaction set app.org_id sees no rows rather than an error (kit-data-kysely's decision 2).
    expect(sql).toContain("nullif(current_setting('app.org_id', true), '')::uuid")
  })
})

describe("the custom roles migration (R-wave, ADR-062)", () => {
  const shapeOf = (entity: AnyEntityDef) => {
    if (!(entity.schema instanceof z.ZodObject)) throw new Error(`${entity.table}: an entity's schema is an object`)
    return entity.schema.shape
  }

  it("is 0008_tenancy-roles.sql: the two new tables, the added columns with their indexes, and forced RLS", async () => {
    const statements = [
      ...createTable(tenancyRoles),
      ...createTable(projectMemberships),
      ...ADDED_BY_0008.map(([entity, field]) => {
        const type = shapeOf(entity)[field]
        // Added to tables that already hold rows: a forward migration cannot add a NOT NULL column.
        expect(isNullable(type), `${entity.table}.${field}`).toBe(true)
        return `alter table ${quoted(entity.table)} add column ${quoted(columnName(field))} ${postgresType(type)}`
      }),
      ...ADDED_BY_0008.flatMap(([entity, field]) =>
        createTable(entity).filter(
          (statement) => statement.startsWith("create index") && statement.includes(`, ${quoted(columnName(field))})`),
        ),
      ),
      ...enableRowLevelSecurity(tenancyRoles),
      ...enableRowLevelSecurity(projectMemberships),
    ]
    await expect(
      `-- ${SPDX_LINE}\n${statements.map((statement) => `${statement};`).join("\n\n")}\n`,
    ).toMatchFileSnapshot("../migrations/postgres/0008_tenancy-roles.sql")
  })

  it("forces row-level security on the organization column of both new tables", () => {
    const sql = readFileSync(new URL("../migrations/postgres/0008_tenancy-roles.sql", import.meta.url), "utf8")
    for (const table of [tenancyRoles.table, projectMemberships.table]) {
      expect(sql).toContain(`alter table "${table}" force row level security`)
      expect(sql).toContain(`create policy "${table}_tenant" on "${table}"`)
    }
    expect(sql).not.toContain("drop ")
  })
})

// The same migration on the dialect profiles (ADR-062: every profile has the new tables under its own
// isolation mechanism). Neither engine has row-level security: the wall is the repository's tenant
// predicate on `org_id`, which the shared contract suites prove, and each unique group is scoped by the
// organization column as every kernel table's is.
describe("the custom roles migration on MariaDB and MongoDB", () => {
  const read = (path: string) => readFileSync(new URL(`../${path}`, import.meta.url), "utf8")
  const addedColumns = (entity: AnyEntityDef) =>
    ADDED_BY_0008.filter(([added]) => added === entity).map(([, field]) => columnName(field))

  it("is profiles/mariadb/migrations/0008_tenancy-roles.sql: the two tables, the added columns and their indexes", async () => {
    const statements = [
      ...createMysqlTable(tenancyRoles),
      ...createMysqlTable(projectMemberships),
      ...ADDED_BY_0008.map(([entity, field]) => {
        const type = shapeOfEntity(entity)[field]
        expect(isNullable(type), `${entity.table}.${field}`).toBe(true)
        return `alter table ${mysqlQuoted(entity.table)} add column ${mysqlQuoted(columnName(field))} ${mysqlType(type)}`
      }),
      ...ADDED_BY_0008.flatMap(([entity, field]) =>
        createMysqlTable(entity).filter(
          (statement) =>
            statement.startsWith("create index") &&
            new RegExp(`, ${mysqlQuoted(columnName(field))}(\\(191\\))?\\)$`).test(statement),
        ),
      ),
    ]
    await expect(
      `-- ${SPDX_LINE}\n${statements.map((statement) => `${statement};`).join("\n\n")}\n`,
    ).toMatchFileSnapshot("../profiles/mariadb/migrations/0008_tenancy-roles.sql")
  })

  it("scopes both new tables' unique groups by the organization, and drops nothing", () => {
    const sql = read("profiles/mariadb/migrations/0008_tenancy-roles.sql")
    expect(sql).toContain("unique (`org_id`, `key`)")
    expect(sql).toContain("unique (`org_id`, `project_id`, `user_id`)")
    expect(sql).not.toContain("drop ")
  })

  it("is profiles/mongodb/migrations/0008_tenancy-roles.json: two collections, the four validators with the new fields, their indexes", () => {
    const changed = [...new Set(ADDED_BY_0008.map(([entity]) => entity))]
    const expected: MongoCommand[] = [
      ...createMongoCollection(tenancyRoles),
      ...createMongoCollection(projectMemberships),
      // A collection's validator refuses a field it does not list (additionalProperties: false), so the
      // added fields arrive as each collection's whole current validator.
      ...changed.map((entity) => ({ collMod: entity.table, validator: mongoValidator(asOf0008(entity)) })),
      ...changed.flatMap((entity) =>
        createMongoCollection(entity).filter(
          (command) =>
            "createIndex" in command &&
            addedColumns(entity).some((column) => Object.keys(command.createIndex.keys).includes(column)),
        ),
      ),
    ]
    // Compared as data: the committed file is formatted by Biome, the generator's output is not. It ran before
    // a nullable enum listed null, which 0009 adds, and a file that ran is never edited.
    expect(JSON.parse(read("profiles/mongodb/migrations/0008_tenancy-roles.json"))).toEqual(
      JSON.parse(
        JSON.stringify(expected, (_, item) => (Array.isArray(item) ? item.filter((entry) => entry !== null) : item)),
      ),
    )
    for (const entity of changed) {
      const validator = JSON.stringify(mongoValidator(asOf0008(entity)))
      for (const column of addedColumns(entity)) expect(validator, `${entity.table}.${column}`).toContain(`"${column}"`)
    }
  })

  it("is profiles/mongodb/migrations/0009_nullable-enums.json: an invitation with no workspace may have no workspace role", () => {
    expect(JSON.parse(read("profiles/mongodb/migrations/0009_nullable-enums.json"))).toEqual([
      { collMod: invitations.table, validator: mongoValidator(asOf0009(invitations)) },
    ])
    const { properties } = mongoValidator(asOf0009(invitations)).$jsonSchema as { properties: Record<string, unknown> }
    expect(properties.workspace_role).toEqual({
      bsonType: ["string", "null"],
      enum: ["viewer", "editor", "admin", null],
    })
  })
})

// One added column on every profile. Convex takes it from the generated schema (`convex/schema.ts`,
// which the Convex generator writes from the entities), and the Postgres reader's whole-row grant is
// built from the entity after these files ran.
describe("the project description migration", () => {
  const read = (path: string) => readFileSync(new URL(`../${path}`, import.meta.url), "utf8")
  const shape = shapeOfEntity(projects)

  it("adds a nullable column to the existing table: a forward migration over rows that exist", () => {
    for (const [entity, field] of ADDED_BY_0009) {
      expect(isNullable(shapeOfEntity(entity)[field]), `${entity.table}.${field}`).toBe(true)
    }
  })

  it("is 0009_project-description.sql on Postgres", async () => {
    const statement = `alter table ${quoted(projects.table)} add column ${quoted(columnName("description"))} ${postgresType(shape.description)}`
    await expect(`-- ${SPDX_LINE}\n${statement};\n`).toMatchFileSnapshot(
      "../migrations/postgres/0009_project-description.sql",
    )
  })

  it("is profiles/mariadb/migrations/0009_project-description.sql", async () => {
    const statement = `alter table ${mysqlQuoted(projects.table)} add column ${mysqlQuoted(columnName("description"))} ${mysqlType(shape.description)}`
    await expect(`-- ${SPDX_LINE}\n${statement};\n`).toMatchFileSnapshot(
      "../profiles/mariadb/migrations/0009_project-description.sql",
    )
  })

  it("is profiles/mongodb/migrations/0009_project-description.json: the collection's whole current validator", () => {
    // A collection's validator refuses a field it does not list (additionalProperties: false).
    const expected: MongoCommand[] = [{ collMod: projects.table, validator: mongoValidator(asOf0009(projects)) }]
    expect(JSON.parse(read("profiles/mongodb/migrations/0009_project-description.json"))).toEqual(
      JSON.parse(JSON.stringify(expected)),
    )
    expect(JSON.stringify(mongoValidator(projects))).toContain('"description"')
  })
})

function shapeOfEntity(entity: AnyEntityDef) {
  if (!(entity.schema instanceof z.ZodObject)) throw new Error(`${entity.table}: an entity's schema is an object`)
  return entity.schema.shape
}

// An invitation that was revoked keeps its row, so its link can say so. One nullable column on every
// profile; Convex takes it from the generated schema.
describe("the invitation revoked migration", () => {
  const read = (path: string) => readFileSync(new URL(`../${path}`, import.meta.url), "utf8")
  const shape = shapeOfEntity(invitations)

  it("adds a nullable column to the existing table: a forward migration over rows that exist", () => {
    for (const [entity, field] of ADDED_BY_0010) {
      expect(isNullable(shapeOfEntity(entity)[field]), `${entity.table}.${field}`).toBe(true)
    }
  })

  it("is 0010_invitation-revoked.sql on Postgres", async () => {
    const statement = `alter table ${quoted(invitations.table)} add column ${quoted(columnName("revokedAt"))} ${postgresType(shape.revokedAt)}`
    await expect(`-- ${SPDX_LINE}\n${statement};\n`).toMatchFileSnapshot(
      "../migrations/postgres/0010_invitation-revoked.sql",
    )
  })

  it("is profiles/mariadb/migrations/0010_invitation-revoked.sql", async () => {
    const statement = `alter table ${mysqlQuoted(invitations.table)} add column ${mysqlQuoted(columnName("revokedAt"))} ${mysqlType(shape.revokedAt)}`
    await expect(`-- ${SPDX_LINE}\n${statement};\n`).toMatchFileSnapshot(
      "../profiles/mariadb/migrations/0010_invitation-revoked.sql",
    )
  })

  it("is profiles/mongodb/migrations/0010_invitation-revoked.json: the collection's whole current validator", () => {
    const expected: MongoCommand[] = [{ collMod: invitations.table, validator: mongoValidator(invitations) }]
    expect(JSON.parse(read("profiles/mongodb/migrations/0010_invitation-revoked.json"))).toEqual(
      JSON.parse(JSON.stringify(expected)),
    )
    expect(JSON.stringify(mongoValidator(invitations))).toContain('"revoked_at"')
  })
})