import { describe, it, expect } from 'vitest'
import { execFile } from 'node:child_process'
import { promisify } from 'node:util'
import { fileURLToPath } from 'node:url'
import { dirname, resolve } from 'node:path'
import {
  PASSCODE_ALPHABET,
  PASSCODE_LENGTH,
  verifyPasscode,
} from '../../skills/data-portal/template/functions/api/_lib/passcode.mjs'

const run = promisify(execFile)
const SCRIPT = resolve(dirname(fileURLToPath(import.meta.url)), '../../bin/portal-enrol.mjs')

/** `--account` is required (it binds the person to the sub-account whose folders
 * they may read), but it is irrelevant to the passcode behaviour most of these
 * tests cover, so the helper supplies one unless the caller names its own. Tests
 * that assert the requirement itself call `run` directly. */
const enrol = (args: string[]) =>
  run('node', [SCRIPT, ...(args.includes('--account') ? args : [...args, '--account', 'acc-1'])])

/** The script's contract: stdout is SQL and nothing else, so it pipes straight
 * into `wrangler d1 execute --file=-`; the passcode goes to stderr, where the
 * operator still sees it on their terminal but a pipe never carries it. The two
 * streams are the whole point — a passcode on stdout would be fed to wrangler,
 * which echoes the failing statement into its error output. */
function parse(r: { stdout: string; stderr: string }) {
  const passcode = /^passcode:\s*(\S+)$/m.exec(r.stderr)?.[1]
  const sql = /^(INSERT INTO people .*)$/m.exec(r.stdout)?.[1]
  return { passcode, sql }
}

const valuesOf = (sql: string) =>
  /VALUES \('([^']*)', '((?:[^']|'')*)', '([0-9a-f]+)', '([0-9a-f]+)', '([^']*)', '([^']*)', '([^']*)'\)/.exec(
    sql,
  )!

/** Apply the emitted SQL to a real SQLite database built from the template's own
 * schema.sql, the way `wrangler d1 execute` applies it to D1. A fake would model
 * the constraint we are trying to prove we satisfy, which is how 1689 shipped a
 * UNIQUE-violating upload: its fake accepted duplicates the schema rejected. */
async function d1(dir: string) {
  const { mkdtemp } = await import('node:fs/promises')
  const { tmpdir } = await import('node:os')
  const root = await mkdtemp(resolve(tmpdir(), dir))
  const db = resolve(root, 'portal.db')
  const schema = resolve(
    dirname(fileURLToPath(import.meta.url)),
    '../../skills/data-portal/schema.sql',
  )
  // SQL as an argv entry, not stdin: execFile has no `input` option. The schema
  // goes via `.read` because it opens with a `--` comment, which sqlite3 would
  // otherwise parse as a CLI option.
  await run('sqlite3', [db, `.read ${schema}`])
  return {
    exec: (sql: string) => run('sqlite3', [db, sql]),
    query: async (sql: string) => (await run('sqlite3', [db, sql])).stdout.trim(),
  }
}

describe('portal-enrol', () => {
  it('prints a passcode at the pinned length from the pinned alphabet', async () => {
    const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const { passcode } = parse(r)
    expect(passcode).toHaveLength(PASSCODE_LENGTH)
    for (const c of passcode!) expect(PASSCODE_ALPHABET).toContain(c)
  })

  it('emits a hash the portal will actually accept for that passcode', async () => {
    // This is the test that matters. It proves enrolment and the Worker agree,
    // which is the whole reason the hash has one implementation rather than a
    // twin in this script.
    const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const { passcode, sql } = parse(r)
    const [, , , salt, hash] = valuesOf(sql!)
    expect(await verifyPasscode(passcode!, salt, hash)).toBe(true)
  })

  it('emits a hash that rejects a different passcode', async () => {
    const a = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const [, , , salt, hash] = valuesOf(parse(a).sql!)
    const b = await enrol(['--owner', 'bob', '--name', 'Bob'])
    expect(await verifyPasscode(parse(b).passcode!, salt, hash)).toBe(false)
  })

  it('never puts the passcode in the SQL', async () => {
    // A passcode reaching D1 or a log in plaintext is the one thing enrolment
    // must never do.
    const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const { passcode, sql } = parse(r)
    expect(sql).not.toContain(passcode!)
  })

  it('does not repeat a passcode across enrolments', async () => {
    const a = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const b = await enrol(['--owner', 'alice', '--name', 'Alice'])
    expect(parse(a).passcode).not.toBe(parse(b).passcode)
  })

  it('does not repeat a salt across enrolments', async () => {
    const saltOf = async (owner: string) => {
      const r = await enrol(['--owner', owner, '--name', owner])
      return valuesOf(parse(r).sql!)[3]
    }
    expect(await saltOf('alice')).not.toBe(await saltOf('bob'))
  })

  it('refuses an ownerId the isolation gate would reject', async () => {
    // `alice` and `alice/2024` co-enrolled would breach the prefix isolation
    // rule, so enrolment fails closed on the same charset the gate enforces.
    await expect(enrol(['--owner', 'alice/2024', '--name', 'Alice'])).rejects.toThrow()
    await expect(enrol(['--owner', 'Alice', '--name', 'Alice'])).rejects.toThrow()
    await expect(enrol(['--owner', '../etc', '--name', 'Alice'])).rejects.toThrow()
  })

  it('refuses a missing owner or name', async () => {
    await expect(enrol(['--name', 'Alice'])).rejects.toThrow()
    await expect(enrol(['--owner', 'alice'])).rejects.toThrow()
    await expect(enrol([])).rejects.toThrow()
  })

  it('escapes a quote in the name rather than breaking the statement', async () => {
    const r = await enrol(['--owner', 'alice', '--name', "O'Brien"])
    expect(parse(r).sql).toContain("'O''Brien'")
  })

  it('writes the row the schema declares, in order', async () => {
    const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const { sql } = parse(r)
    expect(sql).toContain(
      'INSERT INTO people (ownerId, name, salt, hash, createdAt, accountId, folders)',
    )
    const [, owner, name, salt, hash, createdAt, accountId, folders] = valuesOf(sql!)
    expect(owner).toBe('alice')
    expect(name).toBe('Alice')
    expect(salt).toMatch(/^[0-9a-f]{32}$/)
    expect(hash).toMatch(/^[0-9a-f]{64}$/)
    expect(Number.isNaN(Date.parse(createdAt))).toBe(false)
    expect(accountId).toBe('acc-1')
    expect(folders).toBe('')
  })

  it('refuses enrolment with no --account', async () => {
    // A person with no account bound resolves to an empty folder list, which
    // looks exactly like a sub-account that has no deliverables. Refuse here
    // rather than mint a row that fails silently at read time.
    await expect(run('node', [SCRIPT, '--owner', 'alice', '--name', 'Alice'])).rejects.toThrow()
  })

  it('refuses an accountId that is not an account id', async () => {
    await expect(enrol(['--owner', 'alice', '--name', 'Alice', '--account', '../etc'])).rejects.toThrow()
    await expect(enrol(['--owner', 'alice', '--name', 'Alice', '--account', "a'b"])).rejects.toThrow()
  })

  it('puts SQL and only SQL on stdout, so the documented pipe works', async () => {
    // SKILL.md tells the agent to pipe stdout into `wrangler d1 execute`. Anything
    // else on stdout goes into wrangler as SQL — and a passcode there would be
    // echoed back in wrangler's syntax error, which is the one place a plaintext
    // passcode must never reach.
    const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
    expect(r.stdout.trim()).toMatch(/^INSERT INTO people [^\n]*;$/)
    expect(r.stdout).not.toContain('passcode')
  })

  it('keeps the passcode off stdout entirely, on stderr where a pipe cannot take it', async () => {
    const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
    const { passcode } = parse(r)
    expect(passcode).toHaveLength(PASSCODE_LENGTH)
    expect(r.stdout).not.toContain(passcode!)
  })

  it('rejects a newline in the name, which would break the pipeable contract', async () => {
    await expect(enrol(['--owner', 'alice', '--name', 'Bob\nDROP'])).rejects.toThrow()
  })

  describe('against a real SQLite database built from schema.sql', () => {
    it('applies cleanly', async () => {
      const db = await d1('enrol-apply-')
      const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
      await db.exec(parse(r).sql!)
      expect(await db.query('SELECT ownerId, name FROM people;')).toBe('alice|Alice')
    })

    it('rotates a passcode by re-running, which is the ONLY sanctioned rotation path', async () => {
      // SKILL.md: "Rotating a passcode is a re-run of the script for that person."
      // A bare INSERT cannot do that — ownerId is TEXT PRIMARY KEY, so the second
      // run dies on a UNIQUE violation in a SEPARATE process, AFTER the script has
      // already printed a new passcode and exited 0. The operator hands out a
      // passcode that never worked while the leaked one still does, and pcCheck
      // revocation never fires. Rotation is exactly the case where a remedy that
      // silently no-ops is worse than no remedy.
      const db = await d1('enrol-rotate-')

      const first = await enrol(['--owner', 'alice', '--name', 'Alice'])
      await db.exec(parse(first).sql!)

      const second = await enrol(['--owner', 'alice', '--name', 'Alice'])
      await db.exec(parse(second).sql!)

      const [salt, hash] = (await db.query("SELECT salt, hash FROM people WHERE ownerId='alice';"))
        .split('|')
      expect(await verifyPasscode(parse(second).passcode!, salt, hash)).toBe(true)
      expect(await verifyPasscode(parse(first).passcode!, salt, hash)).toBe(false)
    })

    it('rotating leaves exactly one row, and does not reset createdAt', async () => {
      // createdAt is when the person was enrolled. Rotation is not creation, so
      // it does not move; nothing reads it, but a field that lies about what it
      // holds is the defect this component keeps being reviewed for.
      const db = await d1('enrol-one-row-')
      const first = await enrol(['--owner', 'alice', '--name', 'Alice'])
      await db.exec(parse(first).sql!)
      const createdAt = await db.query("SELECT createdAt FROM people WHERE ownerId='alice';")

      await db.exec(parse(await enrol(['--owner', 'alice', '--name', 'Alice'])).sql!)

      expect(await db.query('SELECT COUNT(*) FROM people;')).toBe('1')
      expect(await db.query("SELECT createdAt FROM people WHERE ownerId='alice';")).toBe(createdAt)
    })

    it('binds the person to the account, and rotation keeps the binding current', async () => {
      const db = await d1('enrol-account-')
      await db.exec(parse(await enrol(['--owner', 'alice', '--name', 'Alice', '--account', 'acc-a'])).sql!)
      expect(await db.query("SELECT accountId FROM people WHERE ownerId='alice';")).toBe('acc-a')

      // Re-enrolling to a different account moves the binding rather than
      // leaving the person pointed at folders they no longer belong to.
      await db.exec(parse(await enrol(['--owner', 'alice', '--name', 'Alice', '--account', 'acc-b'])).sql!)
      expect(await db.query("SELECT accountId FROM people WHERE ownerId='alice';")).toBe('acc-b')
      expect(await db.query('SELECT COUNT(*) FROM people;')).toBe('1')
    })

    it('declares a directory table keyed uniquely on account and path', async () => {
      const db = await d1('enrol-directory-')
      await db.exec(
        "INSERT INTO directory (accountId, relPath, sizeBytes, modifiedAt, indexedAt) VALUES ('a','output/x.pdf',1,'t','t');",
      )
      // The full-replace push relies on this: a second insert of the same path
      // must not silently double the row.
      await expect(
        db.exec(
          "INSERT INTO directory (accountId, relPath, sizeBytes, modifiedAt, indexedAt) VALUES ('a','output/x.pdf',1,'t','t');",
        ),
      ).rejects.toThrow()
      expect(await db.query('SELECT COUNT(*) FROM directory;')).toBe('1')
    })

    it('scopes directory rows by account', async () => {
      const db = await d1('enrol-directory-scope-')
      await db.exec(
        "INSERT INTO directory (accountId, relPath, sizeBytes, modifiedAt, indexedAt) VALUES ('a','output/x.pdf',1,'t','t'),('b','output/y.pdf',1,'t','t');",
      )
      expect(await db.query("SELECT relPath FROM directory WHERE accountId='a';")).toBe('output/x.pdf')
    })

    it('lands a folders grant', async () => {
      const r = await enrol(['--owner', 'alice', '--name', 'Alice', '--folders', 'quotes,output'])
      const [, , , , , , , folders] = valuesOf(parse(r).sql!)
      expect(folders).toBe('quotes,output')
    })

    it('defaults folders to empty when the flag is absent', async () => {
      const r = await enrol(['--owner', 'alice', '--name', 'Alice'])
      const [, , , , , , , folders] = valuesOf(parse(r).sql!)
      expect(folders).toBe('')
    })

    it('dedupes repeated folder names', async () => {
      const r = await enrol(['--owner', 'alice', '--name', 'Alice', '--folders', 'quotes,quotes,output'])
      const [, , , , , , , folders] = valuesOf(parse(r).sql!)
      expect(folders).toBe('quotes,output')
    })

    it('rejects a folder name outside the segment charset', async () => {
      await expect(
        enrol(['--owner', 'alice', '--name', 'Alice', '--folders', '../etc']),
      ).rejects.toThrow()
      await expect(
        enrol(['--owner', 'alice', '--name', 'Alice', '--folders', 'a/b']),
      ).rejects.toThrow()
      await expect(
        enrol(['--owner', 'alice', '--name', 'Alice', '--folders', "a'b"]),
      ).rejects.toThrow()
    })

    it('re-enrolment replaces the grant', async () => {
      const db = await d1('enrol-folders-replace-')
      await db.exec(
        parse(await enrol(['--owner', 'alice', '--name', 'Alice', '--folders', 'quotes'])).sql!,
      )
      expect(await db.query("SELECT folders FROM people WHERE ownerId='alice';")).toBe('quotes')
      await db.exec(
        parse(await enrol(['--owner', 'alice', '--name', 'Alice', '--folders', 'output'])).sql!,
      )
      expect(await db.query("SELECT folders FROM people WHERE ownerId='alice';")).toBe('output')
      expect(await db.query('SELECT COUNT(*) FROM people;')).toBe('1')
    })

    it('declares people.folders defaulting to empty', async () => {
      const db = await d1('enrol-folders-col-')
      await db.exec(
        "INSERT INTO people (ownerId, name, salt, hash, createdAt, accountId) VALUES ('alice','Alice','aa','bb','t','acc-a');",
      )
      expect(await db.query("SELECT folders FROM people WHERE ownerId='alice';")).toBe('')
    })

    it('stores a crafted name as data rather than executing it', async () => {
      const db = await d1('enrol-inject-')
      const r = await enrol(['--owner', 'alice', '--name', "x'); DROP TABLE people; --"])
      await db.exec(parse(r).sql!)
      expect(await db.query("SELECT name FROM people WHERE ownerId='alice';")).toBe(
        "x'); DROP TABLE people; --",
      )
    })
  })
})