import { readFileSync } from 'node:fs'
import { join } from 'node:path'
import { describe, expect, it } from 'vitest'

// The schema is applied by the skill, not by any code this suite can call, so
// its shape is asserted textually. A missing column here is not a test failure
// in some other file — it is an upload naming a column D1 does not have.
const SQL = readFileSync(
  join(__dirname, '../../skills/data-portal/schema.sql'),
  'utf8',
)

describe('portal schema shape', () => {
  it('carries the manifest lifecycle columns', () => {
    expect(SQL).toMatch(/targetPath\s+TEXT\s+NOT NULL\s+DEFAULT\s+''/)
    expect(SQL).toMatch(/devicePath\s+TEXT\s+NOT NULL\s+DEFAULT\s+''/)
    expect(SQL).toMatch(/deleted\s+INTEGER\s+NOT NULL\s+DEFAULT\s+0/)
  })

  it('carries the directory generation and its pointer table', () => {
    expect(SQL).toMatch(/generation\s+INTEGER\s+NOT NULL\s+DEFAULT\s+0/)
    expect(SQL).toMatch(/CREATE TABLE IF NOT EXISTS directory_state/)
    expect(SQL).toMatch(/currentGeneration\s+INTEGER\s+NOT NULL/)
  })

  it('keeps objectKey UNIQUE, which the upsert conflict target still needs', () => {
    // objectKey is now ownerId/targetPath/filename. The upsert still conflicts
    // on it, so this pins that the constraint was not dropped by accident while
    // the key shape changed underneath it.
    expect(SQL).toMatch(/objectKey\s+TEXT\s+UNIQUE\s+NOT NULL/)
  })

  it('scopes the directory uniqueness by generation', () => {
    // Two generations hold the same relPath at once between the write and the
    // flip. UNIQUE (accountId, relPath) would reject the second write and the
    // push could never stage a new generation at all.
    expect(SQL).toMatch(/UNIQUE\s*\(\s*accountId,\s*relPath,\s*generation\s*\)/)
  })

  it('documents the hand-run ALTER for every added column', () => {
    // IF NOT EXISTS adds no column to an existing table, so a portal deployed
    // before this needs each one by hand. An undocumented column is an operator
    // reading "no such column" with nothing telling them what to run.
    for (const col of ['targetPath', 'devicePath', 'deleted', 'generation']) {
      expect(SQL).toMatch(new RegExp(`ALTER TABLE \\w+ ADD COLUMN ${col}\\b`))
    }
  })
})