const assert = require('node:assert/strict')
const fs = require('node:fs')
const path = require('node:path')
const crypto = require('node:crypto')
const { execFileSync } = require('node:child_process')

const schema = `content_permissions_${crypto.randomBytes(6).toString('hex')}`
const migration = fs.readFileSync(path.join(__dirname, '../migrations/versioned/000207_content_action_permissions.up.sql'), 'utf8')
const checks = []
const literal = value => `'${String(value).replaceAll("'", "''")}'`
function sql(query) {
  return execFileSync('docker', ['--context', 'colima-yawan', 'exec', '-i', 'WeKnora-postgres',
    'psql', '-X', '-q', '-U', 'postgres', '-d', 'yawan_native_acceptance_20260915', '-At', '-v', 'ON_ERROR_STOP=1'],
  { input: `SET search_path TO ${schema}, public;\n${query}`, encoding: 'utf8', stdio: ['pipe', 'pipe', 'pipe'] }).trim()
}
function permissions(id) { return JSON.parse(sql(`SELECT permissions FROM workbench_access_roles WHERE id=${literal(id)}`)) }
function check(name, actual, expected) { assert.deepEqual(actual, expected, name); checks.push(name) }
function legacyRole(id, sourcePermissions, nativePermissions = [], version = 1) {
  const spec = { id, source_role: id, name: id, source_permissions: sourcePermissions, permissions: nativePermissions, review_notes: [] }
  return `INSERT INTO workbench_access_roles(id,tenant_id,name,permissions,version) VALUES(${literal(id)},10059,${literal(id)},${literal(JSON.stringify(nativePermissions))},${version});
    INSERT INTO yawan_legacy_access_mappings(source_system,target_tenant_id,entity_key,source_spec,plan_sha256)
    VALUES('yawan-workbench',10059,${literal('role/' + id)},${literal(JSON.stringify(spec))},'test');`
}
try {
  sql(`CREATE SCHEMA ${schema};
    CREATE TABLE ${schema}.workbench_access_roles (LIKE public.workbench_access_roles INCLUDING ALL);
    CREATE TABLE ${schema}.yawan_legacy_access_mappings (LIKE public.yawan_legacy_access_mappings INCLUDING ALL);
    CREATE TABLE ${schema}.workbench_content_permission_migrations(version integer PRIMARY KEY, changes jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
    INSERT INTO workbench_access_roles(id,tenant_id,name,permissions) VALUES
      ('producer',10059,'producer','["content.read","content.write"]'),
      ('reader',10059,'reader','["content.read"]'),
      ('other-module',10059,'other-module','["agents.read"]');
    ${legacyRole('reviewer', ['audit:read', 'read', 'review'])}
    ${legacyRole('viewer', ['read'])}
    ${legacyRole('edited-reviewer', ['review'], [], 2)}
    ${legacyRole('editor', ['tasks:review', 'tasks:write', 'tasks:deliver'], ['content.read', 'content.write'])}
    ${legacyRole('changed-reviewer', ['review'])}
    UPDATE workbench_access_roles SET permissions='["agents.read"]' WHERE id='changed-reviewer';`)
  const identityBefore = sql(`SELECT md5(coalesce(jsonb_agg(to_jsonb(u) ORDER BY id)::text,'')) FROM public.users u`)
  const membershipBefore = sql(`SELECT md5(coalesce(jsonb_agg(to_jsonb(m) ORDER BY id)::text,'')) FROM public.tenant_members m`)
  sql(`BEGIN; ${migration} COMMIT;`)
  check('native_producer_preserves_review_and_delivery', permissions('producer'), ['content.deliver', 'content.read', 'content.review', 'content.write'])
  check('native_reader_preserves_delivery_without_review', permissions('reader'), ['content.deliver', 'content.read'])
  check('legacy_reviewer_restored_without_generation_or_delivery', permissions('reviewer'), ['content.read', 'content.review'])
  check('generic_legacy_read_does_not_invent_task_access', permissions('viewer'), [])
  check('edited_import_role_is_preserved', permissions('edited-reviewer'), [])
  check('changed_import_permissions_are_preserved', permissions('changed-reviewer'), ['agents.read'])
  check('legacy_editor_preserves_all_existing_content_actions', permissions('editor'), ['content.deliver', 'content.read', 'content.review', 'content.write'])
  check('unrelated_module_unchanged', permissions('other-module'), ['agents.read'])
  check('original_legacy_ledger_is_not_rewritten', JSON.parse(sql(`SELECT source_spec->'permissions' FROM yawan_legacy_access_mappings WHERE entity_key='role/reviewer'`)), [])
  check('backfill_provenance_records_new_mapping', JSON.parse(sql(`SELECT c->'mapped_permissions' FROM workbench_content_permission_migrations,jsonb_array_elements(changes)c WHERE c->>'role_id'='reviewer'`)), ['content.read', 'content.review'])
  sql(`UPDATE workbench_access_roles SET permissions='[]',version=version+1 WHERE id='reviewer';
    INSERT INTO workbench_access_roles(id,tenant_id,name,permissions) VALUES('new-reader',10059,'new-reader','["content.read"]');
    BEGIN; ${migration} COMMIT;`)
  check('rerun_preserves_explicit_revocation', permissions('reviewer'), [])
  check('rerun_does_not_expand_post_migration_roles', permissions('new-reader'), ['content.read'])
  check('users_unchanged', sql(`SELECT md5(coalesce(jsonb_agg(to_jsonb(u) ORDER BY id)::text,'')) FROM public.users u`), identityBefore)
  check('memberships_unchanged', sql(`SELECT md5(coalesce(jsonb_agg(to_jsonb(m) ORDER BY id)::text,'')) FROM public.tenant_members m`), membershipBefore)
  console.log(JSON.stringify({ ok: true, checks }, null, 2))
} finally {
  sql(`DROP SCHEMA IF EXISTS ${schema} CASCADE;`)
}