// site-sync in-process test suite. Runs against one throwaway SQLite DB set up by
// run.sh, with the plugin registered by location (real onLoad). Grows per build
// phase; each phase is a self-contained section.
//
//   ./test/run.sh

const { getState } = require("@saltcorn/data/db/state");
const Table        = require("@saltcorn/data/models/table");
const Field        = require("@saltcorn/data/models/field");

const {
    db, ok, section, step, summary,
    bootstrapPlugin, mkRes, mkAdminReq, mkPeerReq, responseSigned, routeOf
} = require("./harness");

const protocol = require("../lib/common/peerProtocol");
const peers   = require("../lib/peers");
const env     = require("../lib/env");
const { requirePeerAuth } = require("../lib/peerAuth");
const routes  = require("../lib/routes");
const ops     = require("../lib/operations");
const capture = require("../lib/capture");
const { SYNCABLE_CONFIG_KEYS } = require("../lib/constants");
const { randomUuid } = require("../lib/ids");

const HOST = "localhost:3000";


// Whether fn() rejects (optionally with a message matching re).
const throwsAsync = async (fn, re) => {
    try {
        await fn();
        return false;
    } catch (e) {
        return !re || re.test(e.message);
    }
};


// Delete every row of `name` matching `where` (all rows by default).
const clearRows = async (name, where) => {
    const tbl = Table.findOne({ name });
    for (const r of await tbl.getRows(where || {}, {})) {
        await tbl.deleteRows({ [tbl.pk_name]: r[tbl.pk_name] });
    }
};


const phase1 = async () => {
    section("Phase 1: schema + identity (onLoad)");
    const tablesPresent = ["_ss_peers", "_ss_table_modes"];
    for (const name of tablesPresent) {
        ok(`registered table ${name} exists in _sc_tables`, !!Table.findOne({ name }));
    }
    const rawTableExists = async (name) => {
        if (db.isSQLite) {
            return (await db.query(`SELECT name FROM sqlite_master WHERE type='table' AND name='${name}'`)).rows.length === 1;
        }
        return (await db.query(`SELECT 1 FROM information_schema.tables WHERE table_schema=$1 AND table_name=$2`, [db.getTenantSchema(), name])).rows.length === 1;
    };
    for (const name of ["_ss_seen_nonces", "_ss_plans", "_ss_rollback"]) {
        ok(`raw table ${name} created`, await rawTableExists(name));
    }
    ok("write-only legacy _ss_peer_state is not created", !(await rawTableExists("_ss_peer_state")));
    const myEnv = await env.getEnv();
    ok("env identity created in _sc_config", !!(myEnv && myEnv.env_id));
    ok("default sync_mode is mirror", myEnv.sync_mode === "mirror", myEnv.sync_mode);
    ok("default on_delete is confirm", myEnv.on_delete === "confirm", myEnv.on_delete);
    const csrf = getState().getConfig("disable_csrf_routes", "");
    ok("csrf bypass registered for /site-sync/api/", String(csrf).includes("/site-sync/api/"), csrf);

    section("Phase 1: onLoad idempotency");
    await step("second createAllTables + initEnv is a no-op", async () => {
        const { createAllTables } = require("../lib/schema");
        await createAllTables();
        const again = await env.getEnv();
        ok("env_id unchanged after re-run", again.env_id === myEnv.env_id);
        const cnt = (await db.query(`SELECT COUNT(*) AS c FROM _sc_config WHERE key='site_sync_env'`)).rows[0].c;
        ok("exactly one identity row", Number(cnt) === 1, `count=${cnt}`);
    });

    section("Phase 1: crypto primitives");
    const secret = protocol.randomSecret();
    const sealed = peers.sealedColumns(secret);
    ok("seal/open round-trips the secret", Buffer.compare(peers.openSecret(sealed), secret) === 0);
    const canon = protocol.buildCanonical({ timestamp: "1", nonce: "n", method: "get", path: "/x", targetHost: "h", body: "" });
    ok("sign/verify accepts a good signature", protocol.verifySignature(secret, canon, protocol.sign(secret, canon)));
    ok("verify rejects a bad signature", !protocol.verifySignature(secret, canon, "00".repeat(32)));
    ok("normalizeHost strips :443 not :3000", protocol.normalizeHost("Host:443") === "host" && protocol.normalizeHost("h:3000") === "h:3000");
    ok("timestampWithinSkew rejects a 10-min-old ts", !protocol.timestampWithinSkew(String(Date.now() - 10 * 60 * 1000)));
    await step("a truncated GCM tag is refused (authTagLength pinned to 16)", async () => {
        const s = peers.sealedColumns(protocol.randomSecret());
        let refused = false;
        try {
            peers.openSecret({ ...s, peer_secret_tag: s.peer_secret_tag.slice(0, 8) });
        } catch (e) {
            refused = true;
        }
        ok("4-byte prefix of the real tag does not open the secret", refused);
    });
    await step("the KEK comes ONLY from SALTCORN_SESSION_SECRET or the config file", async () => {
        const connect = require("@saltcorn/data/db/connect");
        const realGetConfigFile = connect.getConfigFile;
        const realEnvSecret     = process.env.SALTCORN_SESSION_SECRET;
        const sealThrows = () => {
            try {
                peers.sealedColumns(Buffer.from("x"));
                return false;
            } catch (e) {
                return /no session_secret configured/.test(e.message);
            }
        };
        try {
            delete process.env.SALTCORN_SESSION_SECRET;
            connect.getConfigFile = () => ({});
            // db.connectObj still holds a session_secret (Saltcorn's resolved value or
            // its generated fallback); it must NOT be used.
            ok("neither env nor config file -> refused (fallback not used)", sealThrows());
            connect.getConfigFile = () => ({ session_secret: "from-config-file" });
            ok("config-file secret accepted", !sealThrows());
        } finally {
            connect.getConfigFile = realGetConfigFile;
            process.env.SALTCORN_SESSION_SECRET = realEnvSecret;
        }
        ok("env secret restored and accepted", !sealThrows());
    });

    section("Phase 1: peer pairing");
    const peerEnvId = randomUuid();
    let peerRow;
    let peerSecretBuf;
    await step("addPeer seals a secret and reads it back", async () => {
        const { peer, secret: s } = await peers.addPeer({ envId: peerEnvId, label: "dev", baseUrl: `http://${HOST}` });
        peerRow = peer;
        peerSecretBuf = s;
        const back = await peers.peerSecret(peer.peer_id);
        ok("peerSecret decrypts to the pairing secret", Buffer.compare(back, s) === 0);
        ok("listPeers omits sealed columns", (await peers.listPeers())[0].peer_secret_ciphertext === undefined);
    });
    await step("addPeer rejects a duplicate env_id", async () => {
        ok("duplicate pairing throws", await throwsAsync(() => peers.addPeer({ envId: peerEnvId, baseUrl: `http://${HOST}` })));
    });
    await step("addPeer rejects a non-http(s) base_url", async () => {
        ok("file: scheme rejected", await throwsAsync(() => peers.addPeer({ envId: randomUuid(), baseUrl: "file:///etc/passwd" })));
    });
    await step("rotatePeerSecret changes the secret", async () => {
        const { secret: rotated } = await peers.rotatePeerSecret(peerRow.peer_id);
        ok("rotated secret differs", Buffer.compare(rotated, peerSecretBuf) !== 0);
        peerSecretBuf = rotated;
    });

    section("Phase 1: inbound HMAC auth (requirePeerAuth)");
    const authOk = async (label, reqOpts) => {
        const req = mkPeerReq(Object.assign({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: peerEnvId, secret: peerSecretBuf }, reqOpts));
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        return { peer, res };
    };
    await step("valid signed GET authenticates", async () => {
        const { peer, res } = await authOk("valid");
        ok("returns the peer row", !!peer && res.statusCode === 200, `status=${res.statusCode}`);
    });
    await step("reused nonce is rejected (replay)", async () => {
        const nonce = protocol.randomNonce().toString("hex");
        const first  = await authOk("first",  { override: { nonce } });
        const second = await authOk("second", { override: { nonce } });
        ok("first accepted", !!first.peer);
        ok("replay rejected 401", second.peer === null && second.res.statusCode === 401, `status=${second.res.statusCode}`);
    });
    await step("bad signature is rejected", async () => {
        const { peer, res } = await authOk("badsig", { override: { badSignature: true } });
        ok("rejected 401", peer === null && res.statusCode === 401);
    });
    await step("stale timestamp is rejected (skew)", async () => {
        const { peer, res } = await authOk("skew", { override: { timestamp: String(Date.now() - 10 * 60 * 1000) } });
        ok("rejected 401", peer === null && res.statusCode === 401);
    });
    await step("cross-tenant host rebinding is rejected", async () => {
        // Signed for HOST, but the request arrives at a different Host header:
        // the canonical no longer matches, so the signature fails.
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: peerEnvId, secret: peerSecretBuf,
            override: { headers: { host: "evil.example.com" } } });
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        ok("rejected 401", peer === null && res.statusCode === 401);
    });
    await step("a spoofed X-Forwarded-Host cannot restore the signed host", async () => {
        // Routed (by Host) to another tenant, but the client claims the signed host
        // via X-Forwarded-Host. With trust proxy off that header is the client's
        // own word, so it must not feed the canonical.
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: peerEnvId, secret: peerSecretBuf,
            override: { headers: { host: "t2.example.com", "x-forwarded-host": HOST } } });
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        ok("XFH spoof rejected 401", peer === null && res.statusCode === 401, `status=${res.statusCode}`);
    });
    await step("a spoofed X-Forwarded-Proto does not satisfy require_tls", async () => {
        await env.setEnvFields({ require_tls: 1 });
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: peerEnvId, secret: peerSecretBuf,
            secure: false, override: { headers: { "x-forwarded-proto": "https" } } });
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        await env.setEnvFields({ require_tls: 0 });
        ok("XFP spoof rejected 403", peer === null && res.statusCode === 403, `status=${res.statusCode}`);
    });
    await step("request body is capped BEFORE the signature check", async () => {
        const { PEER_LIMITS } = require("../lib/transport");
        // (a) declared Content-Length over the cap: refused without reading.
        const declared = mkPeerReq({ method: "POST", path: "/site-sync/api/plan", host: HOST, envId: peerEnvId, secret: peerSecretBuf, body: { a: 1 },
            override: { headers: { "content-length": String(PEER_LIMITS.REQUEST_BODY_BYTES + 1) } } });
        let res = mkRes();
        ok("over-cap Content-Length -> 413", (await requirePeerAuth(declared, res)) === null && res.statusCode === 413, `status=${res.statusCode}`);
        // (b) undeclared stream with a FORGED signature: cut off at the cap, not buffered whole.
        const chunk = Buffer.alloc(1024 * 1024, 0x41);
        const chunks = Math.ceil(PEER_LIMITS.REQUEST_BODY_BYTES / chunk.length) + 8;
        let consumed = 0;
        const flood = mkPeerReq({ method: "POST", path: "/site-sync/api/plan", host: HOST, envId: peerEnvId, secret: Buffer.alloc(32, 9), body: { a: 1 } });
        flood[Symbol.asyncIterator] = async function* () {
            for (let n = 0; n < chunks; n += 1) {
                consumed += chunk.length;
                yield chunk;
            }
        };
        res = mkRes();
        ok("over-cap stream -> 413", (await requirePeerAuth(flood, res)) === null && res.statusCode === 413, `status=${res.statusCode}`);
        ok("stream abandoned at the cap", consumed <= PEER_LIMITS.REQUEST_BODY_BYTES + chunk.length, `consumed=${consumed}`);
    });
    await step("unknown env_id is rejected", async () => {
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: randomUuid(), secret: peerSecretBuf });
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        ok("rejected 401", peer === null && res.statusCode === 401);
    });
    await step("POST body is bound into the signature", async () => {
        const body = { hello: "world", n: 42 };
        const req = mkPeerReq({ method: "POST", path: "/site-sync/api/plan", host: HOST, envId: peerEnvId, secret: peerSecretBuf, body });
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        ok("authenticated and body parsed", !!peer && req.body && req.body.n === 42);
    });
    await step("require_tls rejects non-HTTPS inbound", async () => {
        await env.setEnvFields({ require_tls: 1 });
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: peerEnvId, secret: peerSecretBuf, secure: false });
        const res = mkRes();
        const peer = await requirePeerAuth(req, res);
        ok("rejected 403", peer === null && res.statusCode === 403, `status=${res.statusCode}`);
        await env.setEnvFields({ require_tls: 0 });
    });

    section("Phase 1: admin + machine handlers render");
    await step("GET /admin/site-sync/ dashboard renders", async () => {
        const res = mkRes();
        await routeOf(routes.routes, "get", "/admin/site-sync/")(mkAdminReq(), res);
        ok("sendWrap called with a title", typeof res._wrapTitle === "string" && res._wrapTitle.includes("site-sync"));
    });
    await step("GET /admin/site-sync/peers renders", async () => {
        const res = mkRes();
        await routeOf(routes.routes, "get", "/admin/site-sync/peers")(mkAdminReq({ originalUrl: "/admin/site-sync/peers" }), res);
        ok("sendWrap called", !!res._wrap);
    });
    await step("dashboard refuses a non-admin", async () => {
        const res = mkRes();
        await routeOf(routes.routes, "get", "/admin/site-sync/")(mkAdminReq({ user: { role_id: 80, tenant: db.getTenantSchema() } }), res);
        ok("403 for non-admin", res.statusCode === 403);
    });
    await step("an admin of ANOTHER tenant is not this tenant's admin", async () => {
        // PG sessions are shared by all tenants: tenant B's admin replaying their
        // cookie here arrives as { role_id: 1, tenant: "<B>" }.
        const foreign = { role_id: 1, tenant: `${db.getTenantSchema()}_other`, email: "admin@other" };
        let res = mkRes();
        await routeOf(routes.routes, "get", "/admin/site-sync/")(mkAdminReq({ user: foreign }), res);
        ok("dashboard 403 for a foreign-tenant admin", res.statusCode === 403 && !res._wrap, `status=${res.statusCode}`);
        const before = (await peers.listPeers()).length;
        res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/peers/add")(mkAdminReq({ user: foreign, method: "POST",
            body: { env_id: randomUuid(), base_url: "http://evil.example.com" } }), res);
        ok("peer pairing 403 for a foreign-tenant admin", res.statusCode === 403 && (await peers.listPeers()).length === before);
    });
    await step("GET /site-sync/api/health returns env + plugins, signed", async () => {
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/health", host: HOST, envId: peerEnvId, secret: peerSecretBuf });
        const res = mkRes();
        await routeOf(routes.routes, "get", "/site-sync/api/health")(req, res);
        ok("health body has env_id", !!(res.body && res.body.env_id));
        ok("health lists site-sync among plugins", !!(res.body && Array.isArray(res.body.plugins) && res.body.plugins.some((p) => p.name === "site-sync")));
        ok("health response carries a valid response signature", responseSigned(req, res, peerSecretBuf));
    });
};


const phase2 = async () => {
    section("Phase 2: desired-state capture");
    const myEnv = await env.getEnv();

    let h1;
    await step("buildDesiredState returns a sanitized pack + hash", async () => {
        const cap = await capture.buildDesiredState();
        h1 = cap.hash;
        ok("pack.tables is an array", Array.isArray(cap.pack.tables));
        ok("hash is a 64-hex sha256", /^[0-9a-f]{64}$/.test(cap.hash), cap.hash);
        ok("event_logs forced empty", Array.isArray(cap.pack.event_logs) && cap.pack.event_logs.length === 0);
        ok("model_instances forced empty", Array.isArray(cap.pack.model_instances) && cap.pack.model_instances.length === 0);
    });
    await step("capture is stable for unchanged state", async () => {
        const cap = await capture.buildDesiredState();
        ok("second hash equals first", cap.hash === h1, `${cap.hash} vs ${h1}`);
    });
    await step("infra tables (_ss_/_sc_) are excluded from the pack", async () => {
        const cap = await capture.buildDesiredState();
        const infra = cap.pack.tables.filter((t) => t.name.startsWith("_"));
        ok("_ss_peers not in desired state", !cap.pack.tables.some((t) => t.name === "_ss_peers"), `underscored=${infra.map((t) => t.name).join(",")}`);
    });
    await step("config restricted to the syncable allow-list", async () => {
        const cap = await capture.buildDesiredState();
        const keys = Object.keys(cap.pack.config || {});
        const leaked = keys.filter((k) => !SYNCABLE_CONFIG_KEYS.includes(k));
        ok("no non-allow-listed config key", leaked.length === 0, `leaked=${leaked.join(",")}`);
        ok("disable_csrf_routes not present", !keys.includes("disable_csrf_routes"));
    });
    await step("plugin entries carry no secrets/config", async () => {
        const cap = await capture.buildDesiredState();
        const bad = (cap.pack.plugins || []).filter((p) => "deploy_private_key" in p || "configuration" in p);
        ok("no deploy_private_key or configuration on any plugin", bad.length === 0);
        ok("site-sync present in parity list", (cap.pack.plugins || []).some((p) => p.name === "site-sync"));
    });

    section("Phase 2: capture reflects state changes");
    await step("creating a user table changes the hash + appears in pack", async () => {
        const t = await Table.create("widgets", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: t, name: "title", label: "title", type: "String" });
        const cap = await capture.buildDesiredState();
        ok("hash changed after schema edit", cap.hash !== h1);
        const w = cap.pack.tables.find((x) => x.name === "widgets");
        ok("widgets table in desired state", !!w);
        ok("widgets carries its title field", !!(w && w.fields && w.fields.some((f) => f.name === "title")));
    });

    section("Phase 2: GET /api/state over the signed transport");
    const stateEnvId = randomUuid();
    const { secret: stateSecret } = await peers.addPeer({ envId: stateEnvId, label: "src", baseUrl: `http://${HOST}` });
    await step("apiState serves the sanitized pack to a peer", async () => {
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/state", host: HOST, envId: stateEnvId, secret: stateSecret });
        const res = mkRes();
        await routeOf(routes.routes, "get", "/site-sync/api/state")(req, res);
        ok("returns a pack", !!(res.body && res.body.pack && Array.isArray(res.body.pack.tables)));
        ok("includes widgets", !!(res.body && res.body.pack.tables.some((t) => t.name === "widgets")));
        ok("hash matches a fresh local capture", res.body.hash === (await capture.buildDesiredState()).hash);
        ok("state response carries a valid response signature", responseSigned(req, res, stateSecret));
    });
    await step("apiState refuses an unauthenticated request", async () => {
        const req = mkPeerReq({ method: "GET", path: "/site-sync/api/state", host: HOST, envId: stateEnvId, secret: stateSecret, override: { badSignature: true } });
        const res = mkRes();
        await routeOf(routes.routes, "get", "/site-sync/api/state")(req, res);
        ok("401 without a valid signature", res.statusCode === 401 && !res.body.pack);
    });
};


// Minimal well-formed pack for pure diff tests.
const emptyPack = () => ({
    tables: [], views: [], pages: [], page_groups: [], triggers: [],
    roles: [], library: [], tags: [], models: [], config: {}
});
const mkTable = (name, fields, constraints) => ({
    name, description: null, min_role_read: 1, min_role_write: 1, versioned: false,
    fields: fields || [], constraints: constraints || []
});
const mkField = (name, over) => Object.assign({ name, type: "String", required: false, is_unique: false, primary_key: false, attributes: {} }, over);


const phase3 = async () => {
    const diff    = require("../lib/diff");
    const planLib = require("../lib/plan");

    section("Phase 3: pure diff classification");
    await step("create: source-only table", async () => {
        const d = emptyPack(); d.tables = [mkTable("a", [mkField("id", { primary_key: true, type: "Integer" })])];
        const p = diff.computePlan(d, emptyPack(), { rowCounts: {} });
        ok("a is a create", p.entities.tables.create.some((t) => t.name === "a"));
        ok("summary counts it", p.summary.tables === 1 && p.summary.total === 1);
    });
    await step("delete under mirror; kept under additive", async () => {
        const c = emptyPack(); c.tables = [mkTable("b")];
        const mir = diff.computePlan(emptyPack(), c, { syncMode: "mirror", rowCounts: { b: 0 } });
        const add = diff.computePlan(emptyPack(), c, { syncMode: "additive", rowCounts: { b: 0 } });
        ok("mirror deletes b", mir.entities.tables.delete.some((t) => t.name === "b"));
        ok("additive keeps b", add.entities.tables.delete.length === 0);
    });
    await step("users table is never a delete candidate (capture keeps it out of both packs)", async () => {
        // A mirror plan whose source has NO tables at all: every target table is a
        // delete candidate -- except users, which never enters the target's pack.
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.tables = [mkTable("users", [mkField("email")])];
        const { plan } = await planLib.buildPlan({ peerRow: null, env: await env.getEnv(), desired, desiredHash: "h", sourceEnvId: "src" });
        ok("users not deleted", !plan.entities.tables.delete.some((t) => t.name === "users"));
        ok("users not created/updated from a hostile source spec", !plan.entities.tables.create.some((t) => t.name === "users") && !plan.entities.tables.update.some((u) => u.name === "users"));
    });
    await step("roles keyed by id; roles never deleted", async () => {
        const d = emptyPack(); d.roles = [{ id: 5, role: "editor" }];
        const c = emptyPack(); c.roles = [{ id: 5, role: "old" }, { id: 7, role: "gone" }];
        const p = diff.computePlan(d, c, { syncMode: "mirror", rowCounts: {} });
        ok("role 5 is an update by id", p.entities.roles.update.some((u) => u.key === "5"));
        ok("role 7 is NOT deleted (additive)", p.entities.roles.delete.length === 0);
    });
    await step("nested field create + data-bearing field delete", async () => {
        const d = emptyPack(); d.tables = [mkTable("t", [mkField("a"), mkField("c")])];
        const c = emptyPack(); c.tables = [mkTable("t", [mkField("a"), mkField("b")])];
        const p = diff.computePlan(d, c, { syncMode: "mirror", rowCounts: { t: 3 } });
        const u = p.entities.tables.update.find((x) => x.name === "t");
        ok("field c is created", !!u && u.fields.create.some((f) => f.name === "c"));
        ok("field b is deleted", !!u && u.fields.delete.some((f) => f.name === "b"));
        ok("field-drop flagged data-bearing", p.destructive.some((x) => x.dataBearing && x.field === "b"));
    });
    await step("constraint add/remove diff", async () => {
        const con = (cfg) => ({ type: "Unique", configuration: cfg });
        const d = emptyPack(); d.tables = [mkTable("t", [mkField("a")], [con({ fields: ["a"] })])];
        const c = emptyPack(); c.tables = [mkTable("t", [mkField("a")], [])];
        const p = diff.computePlan(d, c, { rowCounts: { t: 0 } });
        const u = p.entities.tables.update.find((x) => x.name === "t");
        ok("constraint add detected", !!u && u.constraints.add.length === 1);
    });
    await step("rename detection by structural signature", async () => {
        const fields = [mkField("id", { primary_key: true, type: "Integer" }), mkField("title")];
        const d = emptyPack(); d.tables = [mkTable("articles", fields)];
        const c = emptyPack(); c.tables = [mkTable("posts", fields)];
        const p = diff.computePlan(d, c, { syncMode: "mirror", rowCounts: { posts: 4 } });
        ok("posts->articles detected as a rename", p.entities.tables.rename.some((r) => r.from === "posts" && r.to === "articles"));
        ok("rename removed from create", !p.entities.tables.create.some((t) => t.name === "articles"));
        ok("rename removed from delete", !p.entities.tables.delete.some((t) => t.name === "posts"));
    });
    await step("no false diff for identical packs; config overwrite", async () => {
        const a = emptyPack(); a.tables = [mkTable("t", [mkField("a")])]; a.config = { site_name: "X" };
        const b = JSON.parse(JSON.stringify(a));
        ok("identical packs => empty plan", diff.isEmptyPlan(diff.computePlan(a, b, { rowCounts: {} })));
        const d = JSON.parse(JSON.stringify(a)); d.config = { site_name: "Y" };
        const p = diff.computePlan(d, b, { rowCounts: {} });
        ok("config change => a set", p.config.sets.site_name === "Y" && p.summary.config === 1);
    });

    section("Phase 3: live plan build + persistence");
    const myEnv = await env.getEnv();
    await step("unchanged desired => empty plan", async () => {
        const cur = await capture.buildDesiredState();
        const { plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired: cur.pack, desiredHash: cur.hash, sourceEnvId: "src" });
        ok("plan is empty", diff.isEmptyPlan(plan));
    });
    let savedPlanId;
    await step("modified desired => create + delete surfaced and persisted", async () => {
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        // add gadgets, remove widgets from the desired state
        desired.tables = desired.tables.filter((t) => t.name !== "widgets");
        desired.tables.push(mkTable("gadgets", [mkField("id", { primary_key: true, type: "Integer" }), mkField("name")]));
        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired, desiredHash: "srchash", sourceEnvId: "src" });
        savedPlanId = plan_id;
        ok("gadgets is a create", plan.entities.tables.create.some((t) => t.name === "gadgets"));
        ok("widgets is a delete (0 rows, non-data-bearing)", plan.entities.tables.delete.some((t) => t.name === "widgets" && !t.dataBearing));
        ok("precondition hash recorded", plan.precondition_hash === cur.hash);
    });
    await step("loadPlan round-trips the persisted plan", async () => {
        const loaded = await planLib.loadPlan(savedPlanId);
        ok("loaded plan has same id", loaded && loaded.plan_id === savedPlanId);
        ok("desired pack persisted", loaded && Array.isArray(loaded.desired.tables));
        ok("status pending", loaded && loaded.status === "pending");
    });
    await step("renderPlanHtml produces a preview without throwing", async () => {
        const loaded = await planLib.loadPlan(savedPlanId);
        const html = planLib.renderPlanHtml(loaded.plan_id, loaded.plan, "src");
        ok("html mentions gadgets", typeof html === "string" && html.includes("gadgets"));
    });
};


const phase4 = async () => {
    const diff    = require("../lib/diff");
    const planLib = require("../lib/plan");
    const applyLib = require("../lib/apply");
    const myEnv = await env.getEnv();

    section("Phase 4: upsert apply (create + update + config)");
    // Faithful "record desired -> perturb back -> converge": build the desired pack
    // by actually creating the entities (real create_pack_json field shapes), then
    // revert the live DB and prove apply reconstructs it.
    let convergedDesired;
    let convergedHash;
    await step("apply creates a table, adds a field, and sets config", async () => {
        const prevSiteName = getState().getConfig("site_name") || "";
        const sprk = await Table.create("sprockets", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: sprk, name: "kind", label: "kind", type: "String" });
        await Field.create({ table: Table.findOne({ name: "widgets" }), name: "color", label: "color", type: "String" });
        await getState().setConfig("site_name", "SyncedSite");

        const desiredCap = await capture.buildDesiredState();
        convergedDesired = desiredCap.pack;
        convergedHash    = desiredCap.hash;

        // revert the live DB to before the perturbation
        await Table.findOne({ name: "sprockets" }).delete();
        await Table.findOne({ name: "widgets" }).getField("color").delete();
        await getState().setConfig("site_name", prevSiteName);

        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired: convergedDesired, desiredHash: convergedHash, sourceEnvId: "src" });
        ok("plan is non-empty", !diff.isEmptyPlan(plan));
        ok("plan creates sprockets", plan.entities.tables.create.some((t) => t.name === "sprockets"));
        const result = await applyLib.applyPlan(plan_id, { applyDeletes: true });
        ok("apply reported success", result.applied === true);

        const t = Table.findOne({ name: "sprockets" });
        ok("sprockets table created", !!t);
        ok("sprockets has kind field", !!(t && t.getField("kind")));
        const w = Table.findOne({ name: "widgets" });
        ok("widgets gained color field", !!(w && w.getField("color")));
        ok("site_name config applied", getState().getConfig("site_name") === "SyncedSite", getState().getConfig("site_name"));
        const loaded = await planLib.loadPlan(plan_id);
        ok("plan marked applied", loaded.status === "applied");
        ok("persisted desired not mutated by install_pack", loaded.desired.tables.some((x) => x.name === "sprockets"));
    });

    section("Phase 4: idempotency (re-sync of converged state)");
    await step("re-planning the same desired (JSON round-tripped as over the wire) => empty plan", async () => {
        // Round-trip the desired through JSON exactly as the signed transport does,
        // so this also guards the instance-vs-plain-object false-diff regression.
        const overTheWire = JSON.parse(JSON.stringify(convergedDesired));
        const { plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired: overTheWire, desiredHash: "h", sourceEnvId: "src" });
        ok("converged => nothing to do", diff.isEmptyPlan(plan), `total=${plan.summary.total}`);
    });

    section("Phase 4: config allow-list enforced on apply");
    await step("a non-allow-listed config key is never written", async () => {
        const before = getState().getConfig("disable_csrf_routes", "");
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.config = Object.assign({}, desired.config, { disable_csrf_routes: "/hacked", site_name: "AllowListOK" });
        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired, desiredHash: "h", sourceEnvId: "src" });
        ok("plan config.sets excludes disable_csrf_routes", !("disable_csrf_routes" in plan.config.sets));
        ok("plan config.sets includes site_name", plan.config.sets.site_name === "AllowListOK");
        await applyLib.applyPlan(plan_id, { applyDeletes: true });
        ok("disable_csrf_routes unchanged after apply", getState().getConfig("disable_csrf_routes", "") === before, getState().getConfig("disable_csrf_routes", ""));
        ok("site_name did change", getState().getConfig("site_name") === "AllowListOK");
    });

    section("Phase 4: precondition drift refusal");
    await step("applying a plan after the target drifts is refused", async () => {
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.tables.push(mkTable("cogs", [mkField("id", { primary_key: true, type: "Integer" })]));
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired, desiredHash: "h", sourceEnvId: "src" });
        // drift the target after the plan is built
        const drift = await Table.create("drifttable", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: drift, name: "x", label: "x", type: "String" });
        let threw = false;
        try {
            await applyLib.applyPlan(plan_id, { applyDeletes: true });
        } catch (e) {
            threw = /changed since/.test(e.message);
        }
        ok("stale plan refused", threw);
        ok("cogs was NOT created (apply aborted)", !Table.findOne({ name: "cogs" }));
    });
};


// Create a live table with optional String fields; return the fresh Table.
const mkLiveTable = async (name, fields) => {
    const t = await Table.create(name, { min_role_read: 1, min_role_write: 1 });
    for (const fn of (fields || [])) {
        await Field.create({ table: t, name: fn, label: fn, type: "String" });
    }
    return Table.findOne({ name });
};

// A desired pack = current capture minus the named tables/pages (source dropped them).
const desiredWithout = async ({ tables, pages }) => {
    const cur = await capture.buildDesiredState();
    const d = JSON.parse(JSON.stringify(cur.pack));
    if (tables) {
        d.tables = d.tables.filter((t) => !tables.includes(t.name));
    }
    if (pages) {
        d.pages = d.pages.filter((p) => !pages.includes(p.name));
    }
    return d;
};


const phase5 = async () => {
    const planLib  = require("../lib/plan");
    const applyLib = require("../lib/apply");
    const deletes  = require("../lib/deletes");
    const Page     = require("@saltcorn/data/models/page");
    const myEnv    = () => env.getEnv();
    const buildAndApply = async (desired, opts) => {
        const e = await myEnv();
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: e, desired, desiredHash: "h", sourceEnvId: "src" });
        return applyLib.applyPlan(plan_id, Object.assign({ applyDeletes: true }, opts));
    };

    section("Phase 5: mirror prune -- metadata-only vs data-bearing");
    await step("empty table + page pruned; data-bearing table retained without allow-list", async () => {
        await mkLiveTable("temp_empty");
        const td = await mkLiveTable("temp_data", ["val"]);
        await td.insertRow({ val: "keep" });
        await Page.create({ name: "temp_page", title: "t", description: "", min_role: 1, layout: {}, fixed_states: {} });

        const desired = await desiredWithout({ tables: ["temp_empty", "temp_data"], pages: ["temp_page"] });
        const res = await buildAndApply(desired, { prunable: [], confirmDataDrops: false });
        ok("empty table dropped", !Table.findOne({ name: "temp_empty" }));
        ok("page dropped", !Page.findOne({ name: "temp_page" }));
        ok("data-bearing table RETAINED", !!Table.findOne({ name: "temp_data" }));
        ok("skip reason cites allow-list", res.deletes.skipped.some((s) => s.name === "temp_data" && /allow-list/.test(s.reason)));
        ok("its row is intact", (await Table.findOne({ name: "temp_data" }).countRows()) === 1);
    });

    await step("data-bearing drop proceeds with allow-list + typed confirm", async () => {
        const desired = await desiredWithout({ tables: ["temp_data"] });
        const res = await buildAndApply(desired, { prunable: ["temp_data"], confirmDataDrops: true });
        ok("temp_data dropped", !Table.findOne({ name: "temp_data" }));
        ok("delete counted", res.deletes.deleted >= 1);
    });

    section("Phase 5: refuse policy blocks all data-bearing drops");
    await step("refuse policy retains a data-bearing table even when allow-listed", async () => {
        const t = await mkLiveTable("temp_refuse", ["val"]);
        await t.insertRow({ val: "x" });
        await env.setEnvFields({ on_delete: "refuse" });
        const desired = await desiredWithout({ tables: ["temp_refuse"] });
        const res = await buildAndApply(desired, { prunable: ["temp_refuse"], confirmDataDrops: true });
        ok("table retained under refuse", !!Table.findOne({ name: "temp_refuse" }));
        ok("skip reason cites refuse", res.deletes.skipped.some((s) => s.name === "temp_refuse" && /refuse/.test(s.reason)));
        await env.setEnvFields({ on_delete: "confirm" });
    });

    section("Phase 5: users table is never dropped");
    await step("applyDeletes refuses a users drop even if forced into the plan + allow-list", async () => {
        // A real (empty) plan has every entity bucket; force the users drop into it.
        const fakePlan = require("../lib/diff").computePlan(emptyPack(), emptyPack(), { rowCounts: {} });
        fakePlan.entities.tables.delete = [{ name: "users", rowCount: 3, dataBearing: true }];
        const res = await deletes.applyDeletes(fakePlan, { onDelete: "auto", prunable: ["users"], confirmDataDrops: true });
        ok("users still exists", !!Table.findOne({ name: "users" }));
        ok("skip cites locked table", res.skipped.some((s) => s.name === "users" && /locked/.test(s.reason)));
    });

    section("Phase 5: additive mode performs no deletes");
    await step("additive sync keeps a source-absent table", async () => {
        await mkLiveTable("temp_additive");
        await env.setEnvFields({ sync_mode: "additive" });
        const desired = await desiredWithout({ tables: ["temp_additive"] });
        const res = await buildAndApply(desired, { prunable: [] });
        ok("table retained under additive", !!Table.findOne({ name: "temp_additive" }));
        ok("no deletes performed", res.deletes.deleted === 0);
        await env.setEnvFields({ sync_mode: "mirror" });
    });

    section("Phase 5: field drop gate");
    await step("data-bearing column drop gated, then allowed", async () => {
        const t = await mkLiveTable("fielddrop", ["keepme", "extra"]);
        await t.insertRow({ keepme: "a", extra: "b" });
        // desired: fielddrop without "extra"
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        const fd = desired.tables.find((x) => x.name === "fielddrop");
        fd.fields = fd.fields.filter((f) => f.name !== "extra");
        const res1 = await buildAndApply(desired, { prunable: [], confirmDataDrops: false });
        ok("extra field retained without allow-list", !!Table.findOne({ name: "fielddrop" }).getField("extra"));
        ok("field-drop skipped + reported", res1.deletes.skipped.some((s) => s.name === "fielddrop.extra"));
        const res2 = await buildAndApply(desired, { prunable: ["fielddrop"], confirmDataDrops: true });
        ok("extra field dropped with allow-list+confirm", !Table.findOne({ name: "fielddrop" }).getField("extra"));
        ok("dropped-field counted", res2.deletes.droppedFields >= 1);
    });

    section("Phase 5: rename applies on PG, safely falls back on SQLite -- data preserved either way");
    await step("a detected rename never loses data", async () => {
        const t = await mkLiveTable("old_name", ["title"]);
        await t.insertRow({ title: "row1" });
        // desired: same structure under a new name (rename), old_name absent
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        const oldT = desired.tables.find((x) => x.name === "old_name");
        oldT.name = "new_name";
        const e = await myEnv();
        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: e, desired, desiredHash: "h", sourceEnvId: "src" });
        ok("rename detected in plan", plan.entities.tables.rename.some((r) => r.from === "old_name" && r.to === "new_name"));
        const res = await applyLib.applyPlan(plan_id, { applyDeletes: true, prunable: [], confirmDataDrops: false, confirmRenames: ["old_name"] });
        ok("new_name table exists", !!Table.findOne({ name: "new_name" }));
        if (db.isSQLite) {
            // SQLite can't ALTER RENAME: safe fallback = new empty table, old retained.
            ok("SQLite: rename fell back", res.renames.failed.some((r) => r.from === "old_name"));
            ok("SQLite: old_name RETAINED (data preserved)", !!Table.findOne({ name: "old_name" }));
            ok("SQLite: old_name row intact", (await Table.findOne({ name: "old_name" }).countRows()) === 1);
        } else {
            // Postgres: data-preserving Table.rename; old name gone, row moved.
            ok("PG: rename applied", res.renames.renamed.some((r) => r.from === "old_name" && r.to === "new_name"));
            ok("PG: old_name gone (renamed)", !Table.findOne({ name: "old_name" }));
            ok("PG: row preserved under new_name", (await Table.findOne({ name: "new_name" }).countRows()) === 1);
        }
    });

    section("Phase 5: apply form exposes data-drop controls");
    await step("renderApplyForm requires the data-bearing table names to be TYPED", async () => {
        const fakePlan = { destructive: [{ text: "drop table x", dataBearing: true, table: "x" }], entities: { tables: { rename: [] } } };
        const html = planLib.renderApplyForm("pid", fakePlan, "<csrf>");
        ok("form posts to apply", html.includes('action="/admin/site-sync/apply"'));
        ok("prunable input present but NOT pre-filled", /name="prunable"/.test(html) && !/name="prunable"[^>]*value="x"/.test(html) && !/value="x"[^>]*name="prunable"/.test(html));
        ok("confirmation checkbox present", html.includes('name="confirm_data"'));
    });
};


const phase6 = async () => {
    const rowsLib  = require("../lib/rows");
    const valuesOf = async (name, field) =>
        (await Table.findOne({ name }).getRows({}, {})).map((r) => r[field]).sort();

    section("Phase 6: data-mode storage + row-uuid schema");
    await step("setDataMode(managed) registers _ss_row_uuid and backfills", async () => {
        const t = await mkLiveTable("ref_a", ["val"]);
        await t.insertRow({ val: "one" });
        await rowsLib.setDataMode("ref_a", "managed");
        const fresh = Table.findOne({ name: "ref_a" });
        ok("_ss_row_uuid is a registered field", !!fresh.getField("_ss_row_uuid"));
        const rs = await fresh.getRows({}, {});
        ok("existing row backfilled with a uuid", !!(rs[0] && rs[0]._ss_row_uuid));
        ok("dataModeOf reports managed", (await rowsLib.dataModeOf("ref_a")) === "managed");
    });
    await step("users cannot be set to managed", async () => {
        ok("locked table refuses managed mode", await throwsAsync(() => rowsLib.setDataMode("users", "managed"), /locked/));
    });

    section("Phase 6: managed mirror (insert / update / delete by uuid)");
    await step("target converges to the source's managed rows", async () => {
        const t = await mkLiveTable("ref_colors", ["val"]);
        const id1 = await t.insertRow({ val: "red" });
        await t.insertRow({ val: "green" });
        const id3 = await t.insertRow({ val: "blue" });
        await rowsLib.setDataMode("ref_colors", "managed");
        const { manifest } = await rowsLib.buildRowManifest();
        ok("manifest has 3 managed rows", manifest.ref_colors && manifest.ref_colors.rows.length === 3);

        // perturb the "target": lose blue, corrupt red, add a stray row
        const tt = Table.findOne({ name: "ref_colors" });
        await tt.deleteRows({ id: id3 });
        await tt.updateRow({ val: "crimson" }, id1);
        await tt.insertRow({ val: "stray" });

        const res = await rowsLib.applyRowSync(manifest, {});
        ok("converged to exactly the source rows", JSON.stringify(await valuesOf("ref_colors", "val")) === JSON.stringify(["blue", "green", "red"]));
        ok("insert/update/delete all happened", res.perTable.ref_colors.inserted >= 1 && res.perTable.ref_colors.deleted >= 1);
    });

    section("Phase 6: starter seed-once");
    await step("starter seeds missing rows, then is inert", async () => {
        const t = await mkLiveTable("ref_seed", ["val"]);
        const s1 = await t.insertRow({ val: "s1" });
        await t.insertRow({ val: "s2" });
        await rowsLib.setDataMode("ref_seed", "starter");
        const { manifest } = await rowsLib.buildRowManifest();
        // perturb: lose s1, edit s2, add stray
        const tt = Table.findOne({ name: "ref_seed" });
        await tt.deleteRows({ id: s1 });
        await tt.insertRow({ val: "target_only" });
        const res1 = await rowsLib.applyRowSync(manifest, {});
        const vals = await valuesOf("ref_seed", "val");
        ok("s1 re-seeded", vals.includes("s1"));
        ok("target-only row NOT deleted (starter doesn't prune)", vals.includes("target_only"));
        ok("starter marked shipped", res1.perTable.ref_seed.inserted >= 1);
        // second apply: already shipped => no-op
        const res2 = await rowsLib.applyRowSync(manifest, {});
        ok("re-apply is a no-op (shipped)", res2.perTable.ref_seed.note === "starter already shipped");
    });

    section("Phase 6: foreign-key translation across managed tables");
    await step("child FK resolves to the parent's local id on the target", async () => {
        const cat = await mkLiveTable("ref_cat", ["cname"]);
        await cat.insertRow({ cname: "fruit" });
        await cat.insertRow({ cname: "veg" });
        const item = await Table.create("ref_item", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: item, name: "iname", label: "iname", type: "String" });
        await Field.create({ table: item, name: "cat", label: "cat", type: "Key to ref_cat", attributes: {}, reftable_name: "ref_cat" });
        await rowsLib.setDataMode("ref_cat", "managed");
        await rowsLib.setDataMode("ref_item", "managed");
        const catFresh = Table.findOne({ name: "ref_cat" });
        const fruitId = (await catFresh.getRows({ cname: "fruit" }, {}))[0].id;
        const itemFresh = Table.findOne({ name: "ref_item" });
        await itemFresh.insertRow({ iname: "apple", cat: fruitId });
        const { manifest } = await rowsLib.buildRowManifest();
        ok("child row FK carried as a uuid", manifest.ref_item.rows.some((r) => "cat__uuid" in r && r.cat__uuid));

        // wipe all reference rows, then converge from the manifest
        await clearRows("ref_item");
        await clearRows("ref_cat");
        await rowsLib.applyRowSync(manifest, {});

        const newFruit = (await Table.findOne({ name: "ref_cat" }).getRows({ cname: "fruit" }, {}))[0];
        const apple = (await Table.findOne({ name: "ref_item" }).getRows({ iname: "apple" }, {}))[0];
        ok("apple re-created", !!apple);
        ok("apple.cat resolves to the new fruit id", apple && newFruit && apple.cat === newFruit.id);
    });

    section("Phase 6: user data is protected");
    await step("user-mode tables are never in the manifest", async () => {
        const t = await mkLiveTable("user_notes", ["note"]);
        await t.insertRow({ note: "private" });
        const { manifest } = await rowsLib.buildRowManifest();
        ok("user_notes excluded from manifest", !manifest.user_notes);
    });
    await step("applyRowSync skips a table the target has in user mode", async () => {
        const hostile = { user_notes: { mode: "managed", rows: [{ __uuid: "x", note: "injected" }] } };
        const res = await rowsLib.applyRowSync(hostile, {});
        ok("skipped as not-managed on target", res.skipped.some((s) => s.name === "user_notes"));
        ok("no injected row", (await Table.findOne({ name: "user_notes" }).countRows()) === 1);
    });
    await step("applyRowSync refuses row injection into users (locked)", async () => {
        const before = await Table.findOne({ name: "users" }).countRows();
        const hostile = { users: { mode: "managed", rows: [{ __uuid: "evil", email: "evil@x.com", role_id: 1 }] } };
        const res = await rowsLib.applyRowSync(hostile, {});
        ok("users skipped as locked", res.skipped.some((s) => s.name === "users"));
        ok("users row count unchanged", (await Table.findOne({ name: "users" }).countRows()) === before);
    });
};


const phase7 = async () => {
    const planLib = require("../lib/plan");
    const files   = require("../lib/common/fileStore");
    const myEnv = await env.getEnv();

    section("Phase 7: hostile peer -- infra-table injection");
    await step("a desired pack containing a plugin infra table is never applied", async () => {
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.tables.push({ name: "_dd_secret", min_role_read: 1, min_role_write: 1, versioned: false,
            fields: [{ name: "id", type: "Integer", primary_key: true, required: false, is_unique: false, attributes: {} }], constraints: [] });
        const { plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired, desiredHash: "h", sourceEnvId: "src" });
        ok("_dd_secret excluded from the plan (leading-underscore infra filter)", !plan.entities.tables.create.some((t) => t.name === "_dd_secret"));
    });

    section("Phase 7: hostile peer -- config key injection");
    await step("only allow-listed config keys ever reach the plan", async () => {
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.config = { disable_csrf_routes: "/x", smtp_host: "evil", columns: "z", site_name: "ok", site_logo_id: 3 };
        const { plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired, desiredHash: "h", sourceEnvId: "src" });
        const keys = Object.keys(plan.config.sets);
        ok("no security config key present", !keys.some((k) => ["disable_csrf_routes", "smtp_host", "columns"].includes(k)), keys.join(","));
        ok("allow-listed keys allowed", keys.includes("site_name"));
    });

    section("Phase 7: file-store containment");
    await step("toAbsolutePath rejects traversal and absolute paths", async () => {
        const throws = (rel) => {
            try {
                files.toAbsolutePath(db, rel);
                return false;
            } catch (e) {
                return true;
            }
        };
        ok("rejects ../ traversal", throws("../../../etc/passwd"));
        ok("rejects a sibling tenant's store", throws(`../${db.getTenantSchema()}_other/x.png`));
        ok("rejects absolute path", throws("/etc/passwd"));
        ok("rejects the store root itself", throws(""));
        ok("accepts an in-store relative path", !throws("sub/ok.png"));
    });

    section("Phase 7: push-apply gated behind on_apply=auto");
    await step("api/apply refuses remote-triggered apply when on_apply=confirm", async () => {
        await env.setEnvFields({ on_apply: "confirm" });
        const peerEnvId = randomUuid();
        const { secret } = await peers.addPeer({ envId: peerEnvId, label: "pusher", baseUrl: "http://localhost:3000" });
        const req = mkPeerReq({ method: "POST", path: "/site-sync/api/apply", host: HOST, envId: peerEnvId, secret, body: { plan_id: "whatever" } });
        const res = mkRes();
        await routeOf(routes.routes, "post", "/site-sync/api/apply")(req, res);
        ok("403 when confirmation required", res.statusCode === 403);
        ok("error explains admin-UI requirement", /admin/i.test(JSON.stringify(res.body)));
    });
    await step("api/plan (compute-only) is allowed and returns a plan_id", async () => {
        const peerEnvId = randomUuid();
        const { secret } = await peers.addPeer({ envId: peerEnvId, label: "planner", baseUrl: "http://localhost:3000" });
        const cur = await capture.buildDesiredState();
        const req = mkPeerReq({ method: "POST", path: "/site-sync/api/plan", host: HOST, envId: peerEnvId, secret, body: { pack: cur.pack, hash: cur.hash, rows: {} } });
        const res = mkRes();
        await routeOf(routes.routes, "post", "/site-sync/api/plan")(req, res);
        ok("plan computed for authenticated push", !!(res.body && res.body.plan_id));
        ok("empty plan for identical state (round-trip normalized)", res.body.summary && res.body.summary.total === 0, JSON.stringify(res.body.summary));
    });
};


const phase8 = async () => {
    const diff     = require("../lib/diff");
    const planLib  = require("../lib/plan");
    const applyLib = require("../lib/apply");
    const rowsLib  = require("../lib/rows");

    section("Phase 8 (regression): TOCTOU -- drop gate re-counts rows at apply time");
    await step("rows inserted between preview and apply prevent a silent drop", async () => {
        await mkLiveTable("toctou", ["v"]); // 0 rows at preview
        const e = await env.getEnv();
        const desired = await desiredWithout({ tables: ["toctou"] });
        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: e, desired, desiredHash: "h", sourceEnvId: "src" });
        const del = plan.entities.tables.delete.find((d) => d.name === "toctou");
        ok("planned as non-data-bearing at preview", del && !del.dataBearing);
        // rows arrive AFTER preview (metadata hash is unchanged, so precondition still passes)
        await Table.findOne({ name: "toctou" }).insertRow({ v: "late" });
        const res = await applyLib.applyPlan(plan_id, { applyDeletes: true, prunable: [], confirmDataDrops: false });
        ok("table RETAINED (live recount caught the new rows)", !!Table.findOne({ name: "toctou" }));
        ok("drop skipped, not silently applied", res.deletes.skipped.some((s) => s.name === "toctou"));
    });

    section("Phase 8 (regression): users table schema is hard-locked on the upsert half");
    await step("a hostile users table spec never reaches install_pack", async () => {
        const cur = await capture.buildDesiredState();
        ok("users excluded from the captured pack", !cur.pack.tables.some((t) => t.name === "users"));
        const before = Table.findOne({ name: "users" }).min_role_write;
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.tables.push({ name: "users", min_role_read: 100, min_role_write: 100, ownership_formula: "true", versioned: false,
            fields: [{ name: "backdoor", type: "String", required: false, is_unique: false, primary_key: false, attributes: {} }], constraints: [] });
        const e = await env.getEnv();
        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: e, desired, desiredHash: "h", sourceEnvId: "src" });
        ok("users not in the plan", !plan.entities.tables.create.some((t) => t.name === "users") && !plan.entities.tables.update.some((u) => u.name === "users"));
        await applyLib.applyPlan(plan_id, { applyDeletes: true });
        const after = Table.findOne({ name: "users" });
        ok("users min_role_write unchanged", after.min_role_write === before, `${after.min_role_write} vs ${before}`);
        ok("no backdoor field added to users", !after.getField("backdoor"));
    });

    section("Phase 8 (regression): required-FK child rows are inserted, not dropped");
    await step("insert fixpoint handles a required cross-table FK", async () => {
        const parent = await mkLiveTable("fk_parent", ["pname"]);
        await parent.insertRow({ pname: "P1" });
        const child = await Table.create("fk_child", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: child, name: "cname", label: "cname", type: "String" });
        await Field.create({ table: child, name: "parent", label: "parent", type: "Key to fk_parent", required: true, reftable_name: "fk_parent", attributes: {} });
        await rowsLib.setDataMode("fk_parent", "managed");
        await rowsLib.setDataMode("fk_child", "managed");
        const pid = (await Table.findOne({ name: "fk_parent" }).getRows({}, {}))[0].id;
        await Table.findOne({ name: "fk_child" }).insertRow({ cname: "C1", parent: pid });
        const { manifest } = await rowsLib.buildRowManifest();
        await clearRows("fk_child");
        await clearRows("fk_parent");
        await rowsLib.applyRowSync(manifest, {});
        ok("child re-inserted despite required FK", (await Table.findOne({ name: "fk_child" }).countRows()) === 1);
        const ch  = (await Table.findOne({ name: "fk_child" }).getRows({}, {}))[0];
        const par = (await Table.findOne({ name: "fk_parent" }).getRows({}, {}))[0];
        ok("child FK resolves to the parent", ch.parent === par.id);
    });

    section("Phase 8 (regression): remote apply cannot self-confirm data drops, nor apply another peer's plan");
    await step("sender-supplied confirm_data is ignored on api/apply", async () => {
        await env.setEnvFields({ on_apply: "auto", on_delete: "confirm", sync_mode: "mirror" });
        const t = await mkLiveTable("remote_drop", ["v"]);
        await t.insertRow({ v: "data" });
        const peerEnvId = randomUuid();
        const { secret } = await peers.addPeer({ envId: peerEnvId, label: "rp", baseUrl: "http://localhost:3000" });
        const cur = await capture.buildDesiredState();
        const desired = JSON.parse(JSON.stringify(cur.pack));
        desired.tables = desired.tables.filter((x) => x.name !== "remote_drop");
        let req = mkPeerReq({ method: "POST", path: "/site-sync/api/plan", host: HOST, envId: peerEnvId, secret, body: { pack: desired, hash: "h", rows: {} } });
        let res = mkRes();
        await routeOf(routes.routes, "post", "/site-sync/api/plan")(req, res);
        const planId = res.body.plan_id;
        req = mkPeerReq({ method: "POST", path: "/site-sync/api/apply", host: HOST, envId: peerEnvId, secret, body: { plan_id: planId, prunable: ["remote_drop"], confirm_data: true } });
        res = mkRes();
        await routeOf(routes.routes, "post", "/site-sync/api/apply")(req, res);
        // The apply itself must have SUCCEEDED -- a failed apply would also leave the
        // table in place and make the retention check meaningless.
        ok("remote apply succeeded (200, ok)", res.statusCode === 200 && !!(res.body && res.body.ok), `status=${res.statusCode} ${JSON.stringify(res.body).slice(0, 200)}`);
        ok("drop skipped by the gate, not by an error", !!(res.body && res.body.result && res.body.result.deletes.skipped.some((s) => s.name === "remote_drop")));
        ok("data-bearing table RETAINED (sender cannot self-confirm)", !!Table.findOne({ name: "remote_drop" }));
        await env.setEnvFields({ on_delete: "confirm" });
    });
    await step("a peer cannot apply another peer's plan_id", async () => {
        const aEnv = randomUuid();
        const bEnv = randomUuid();
        const { secret: aSec } = await peers.addPeer({ envId: aEnv, label: "A", baseUrl: "http://localhost:3000" });
        const { secret: bSec } = await peers.addPeer({ envId: bEnv, label: "B", baseUrl: "http://localhost:3000" });
        const cur = await capture.buildDesiredState();
        let req = mkPeerReq({ method: "POST", path: "/site-sync/api/plan", host: HOST, envId: bEnv, secret: bSec, body: { pack: cur.pack, hash: cur.hash, rows: {} } });
        let res = mkRes();
        await routeOf(routes.routes, "post", "/site-sync/api/plan")(req, res);
        const bPlan = res.body.plan_id;
        req = mkPeerReq({ method: "POST", path: "/site-sync/api/apply", host: HOST, envId: aEnv, secret: aSec, body: { plan_id: bPlan } });
        res = mkRes();
        await routeOf(routes.routes, "post", "/site-sync/api/apply")(req, res);
        ok("404 -- A cannot apply B's plan", res.statusCode === 404);
        await env.setEnvFields({ on_apply: "confirm" });
    });

    section("Phase 8 (regression): diff/hash normalization");
    await step("root_page_for_roles is not a design diff", async () => {
        const d = emptyPack(); d.pages = [{ name: "home", title: "H", layout: {}, root_page_for_roles: ["public"] }];
        const c = emptyPack(); c.pages = [{ name: "home", title: "H", layout: {}, root_page_for_roles: [] }];
        ok("home page not flagged changed", diff.isEmptyPlan(diff.computePlan(d, c, { rowCounts: {} })));
    });
    await step("hashPack is order-insensitive", async () => {
        const a = emptyPack(); a.roles = [{ id: 1, role: "admin" }, { id: 100, role: "public" }]; a.tables = [mkTable("x", [mkField("a")]), mkTable("y", [mkField("b")])];
        const b = emptyPack(); b.roles = [{ id: 100, role: "public" }, { id: 1, role: "admin" }]; b.tables = [mkTable("y", [mkField("b")]), mkTable("x", [mkField("a")])];
        ok("permuted packs hash equal", capture.hashPack(a) === capture.hashPack(b));
    });
};


const phase9 = async () => {
    const rowsLib = require("../lib/rows");
    const File    = require("@saltcorn/data/models/file");
    const { sha256Hex } = require("../lib/common/hash");
    const CONTENT = Buffer.from("hello world file content");
    const HASH = sha256Hex(CONTENT);

    section("Phase 9: file-content transfer (managed rows)");
    await step("File field produces a content-hash placeholder", async () => {
        const docs = await Table.create("docs", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: docs, name: "title", label: "title", type: "String" });
        await Field.create({ table: docs, name: "attachment", label: "attachment", type: "File" });
        const f = await File.from_contents("hello.txt", "text/plain", CONTENT, 1, 1, "/");
        const fresh = Table.findOne({ name: "docs" });
        await fresh.insertRow({ title: "doc1", attachment: f.field_value });
        await rowsLib.setDataMode("docs", "managed");
        const { manifest } = await rowsLib.buildRowManifest();
        const row = manifest.docs.rows[0];
        ok("placeholder carries the content hash", row.attachment__file && row.attachment__file.hash === HASH);
        ok("raw file path not shipped", !("attachment" in row));
        ok("filename + mime carried", /^hello/.test(row.attachment__file.filename) && /text/.test(row.attachment__file.mime));
    });

    let goodManifest;
    await step("apply fetches bytes by hash and materializes the file", async () => {
        const { manifest } = await rowsLib.buildRowManifest();
        goodManifest = manifest;
        // simulate the target losing the row; a content-addressed fetcher supplies bytes
        await clearRows("docs");
        const fetcher = async (hash) => (hash === HASH ? CONTENT : Buffer.from("?"));
        const res = await rowsLib.applyRowSync(manifest, { fileFetcher: fetcher });
        ok("row re-inserted", res.perTable.docs.inserted === 1);
        const doc = (await Table.findOne({ name: "docs" }).getRows({}, {}))[0];
        ok("attachment field set to a local file", !!(doc && doc.attachment));
        const local = await File.findOne(doc.attachment);
        const bytes = local && await local.get_contents();
        ok("materialized content matches source", !!bytes && Buffer.compare(bytes, CONTENT) === 0);
    });

    await step("re-apply is idempotent (unchanged file not re-fetched)", async () => {
        let fetches = 0;
        const fetcher = async (hash) => { fetches += 1; return hash === HASH ? CONTENT : Buffer.from("?"); };
        await rowsLib.applyRowSync(goodManifest, { fileFetcher: fetcher });
        ok("no fetch for an unchanged managed file", fetches === 0, `fetches=${fetches}`);
        const doc = (await Table.findOne({ name: "docs" }).getRows({}, {}))[0];
        const local = await File.findOne(doc.attachment);
        ok("content still correct", Buffer.compare(await local.get_contents(), CONTENT) === 0);
    });

    await step("content-hash integrity: tampered bytes are refused", async () => {
        await clearRows("docs");
        const tamperer = async () => Buffer.from("tampered payload");
        const res = await rowsLib.applyRowSync(goodManifest, { fileFetcher: tamperer });
        const doc = (await Table.findOne({ name: "docs" }).getRows({}, {}))[0];
        ok("attachment left empty on hash mismatch", !!doc && !doc.attachment);
        ok("mismatch reported", res.warnings.some((w) => /hash mismatch/.test(w)));
    });

    await step("resolveFileByHash finds a synced file by content hash", async () => {
        // restore a good file so the source index can resolve it
        await rowsLib.applyRowSync(goodManifest, { fileFetcher: async () => CONTENT });
        const fv = await rowsLib.resolveFileByHash(HASH);
        ok("hash resolves to a local file value", !!fv);
        const unknown = await rowsLib.resolveFileByHash("f".repeat(64));
        ok("unknown hash resolves to null", unknown === null);
    });
};


const phase10 = async () => {
    const planLib  = require("../lib/plan");
    const applyLib = require("../lib/apply");
    const rollback = require("../lib/rollback");
    const rowsLib  = require("../lib/rows");
    const File     = require("@saltcorn/data/models/file");
    const myEnv    = () => env.getEnv();

    section("Phase 10: rollback -- metadata + config");
    await step("apply captures a restore point; rollback reverts schema + config", async () => {
        const prevSiteName = getState().getConfig("site_name") || "";
        // build a real desired pack that adds a table + changes config
        const rb = await Table.create("rbtable", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: rb, name: "note", label: "note", type: "String" });
        await getState().setConfig("site_name", "RolledChange");
        const cap = await capture.buildDesiredState();
        // revert live to the pre-apply state
        await Table.findOne({ name: "rbtable" }).delete();
        await getState().setConfig("site_name", prevSiteName);

        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: await myEnv(), desired: cap.pack, desiredHash: cap.hash, sourceEnvId: "src" });
        await applyLib.applyPlan(plan_id, { applyDeletes: true });
        ok("rbtable created by apply", !!Table.findOne({ name: "rbtable" }));
        ok("config changed by apply", getState().getConfig("site_name") === "RolledChange");
        ok("restore point recorded", await rollback.hasRestorePoint(plan_id));

        await rollback.rollbackApply(plan_id, {});
        ok("rbtable removed by rollback", !Table.findOne({ name: "rbtable" }));
        ok("config reverted by rollback", getState().getConfig("site_name") === prevSiteName, getState().getConfig("site_name"));
    });

    section("Phase 10: rollback -- managed rows");
    await step("rollback restores changed/deleted/inserted managed rows", async () => {
        const t = await mkLiveTable("rbrows", ["v"]);
        const id1 = await t.insertRow({ v: "orig1" });
        await t.insertRow({ v: "keep2" });
        await rowsLib.setDataMode("rbrows", "managed");
        // craft the "source" manifest: change row1, add row3, drop row2
        const tt = Table.findOne({ name: "rbrows" });
        await tt.updateRow({ v: "changed1" }, id1);
        await tt.insertRow({ v: "new3" });
        const row2 = (await tt.getRows({ v: "keep2" }, {}))[0];
        await tt.deleteRows({ id: row2.id });
        const { manifest } = await rowsLib.buildRowManifest();
        // revert live to pre-apply {orig1, keep2}
        await tt.updateRow({ v: "orig1" }, id1);
        await tt.insertRow({ v: "keep2" });
        await clearRows("rbrows", { v: "new3" });

        const desired = await capture.buildDesiredState();
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: await myEnv(), desired: desired.pack, desiredHash: desired.hash, sourceEnvId: "src", sourceRows: manifest });
        await applyLib.applyPlan(plan_id, { applyDeletes: true });
        const afterApply = (await Table.findOne({ name: "rbrows" }).getRows({}, {})).map((r) => r.v).sort();
        ok("apply changed the managed rows", JSON.stringify(afterApply) === JSON.stringify(["changed1", "new3"]), afterApply.join(","));

        await rollback.rollbackApply(plan_id, {});
        const afterRb = (await Table.findOne({ name: "rbrows" }).getRows({}, {})).map((r) => r.v).sort();
        ok("rollback restored the pre-apply rows", JSON.stringify(afterRb) === JSON.stringify(["keep2", "orig1"]), afterRb.join(","));
    });

    section("Phase 10: rollback -- file reference");
    await step("rollback restores the pre-apply file reference", async () => {
        const A = Buffer.from("original file A");
        const B = Buffer.from("apply-changed file B");
        const HB = require("../lib/common/hash").sha256Hex(B);
        const docs = await Table.create("rbdocs", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: docs, name: "attachment", label: "attachment", type: "File" });
        const fileA = await File.from_contents("a.txt", "text/plain", A, 1, 1, "/");
        const rowId1 = await Table.findOne({ name: "rbdocs" }).insertRow({ attachment: fileA.field_value });
        await rowsLib.setDataMode("rbdocs", "managed");
        // craft source manifest referencing file B
        const fileB = await File.from_contents("b.txt", "text/plain", B, 1, 1, "/");
        await Table.findOne({ name: "rbdocs" }).updateRow({ attachment: fileB.field_value }, rowId1);
        const { manifest } = await rowsLib.buildRowManifest();
        // revert live row to file A
        await Table.findOne({ name: "rbdocs" }).updateRow({ attachment: fileA.field_value }, rowId1);

        const desired = await capture.buildDesiredState();
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: await myEnv(), desired: desired.pack, desiredHash: desired.hash, sourceEnvId: "src", sourceRows: manifest });
        await applyLib.applyPlan(plan_id, { applyDeletes: true, fileFetcher: async (h) => (h === HB ? B : Buffer.from("?")) });
        let doc = (await Table.findOne({ name: "rbdocs" }).getRows({}, {}))[0];
        let bytes = await (await File.findOne(doc.attachment)).get_contents();
        ok("apply switched the file to B", Buffer.compare(bytes, B) === 0);

        await rollback.rollbackApply(plan_id, {});
        doc = (await Table.findOne({ name: "rbdocs" }).getRows({}, {}))[0];
        bytes = await (await File.findOne(doc.attachment)).get_contents();
        ok("rollback restored the original file A reference", Buffer.compare(bytes, A) === 0);
    });
};


const phase11 = async () => {
    const planLib     = require("../lib/plan");
    const applyLib    = require("../lib/apply");
    const rollback    = require("../lib/rollback");
    const rowsLib     = require("../lib/rows");
    const layoutFiles = require("../lib/layoutFiles");
    const File        = require("@saltcorn/data/models/file");
    const Page        = require("@saltcorn/data/models/page");
    const { sha256Hex } = require("../lib/common/hash");
    const myEnv = () => env.getEnv();

    // a real 1x1 PNG (content-sniffing rejects non-images); vary the trailing bytes
    // to get distinct-content images that still carry the PNG magic.
    const PNG = Buffer.from("iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAC0lEQVR42mNkYPhfDwAChwGA60e6kgAAAABJRU5ErkJggg==", "base64");
    const A = Buffer.concat([PNG, Buffer.from("brand")]);
    const HA = sha256Hex(A);

    section("Phase 11: layout/logo file refs -> placeholders on capture");
    await step("layout image + site logo become content-hash placeholders", async () => {
        const fileA = await File.from_contents("logo.png", "image/png", A, 1, 100, "/");
        await Page.create({ name: "brand", title: "Brand", description: "", min_role: 1,
            layout: { above: [{ type: "image", fileid: fileA.field_value, srcType: "File" }] }, fixed_states: {} });
        await getState().setConfig("site_logo_id", fileA.field_value);
        const cap = await capture.buildDesiredState();
        const brand = cap.pack.pages.find((p) => p.name === "brand");
        const ph = brand.layout.above[0].fileid;
        ok("layout image is a placeholder", layoutFiles.isPlaceholder(ph));
        ok("placeholder hash matches the file content", layoutFiles.parsePlaceholder(ph).hash === HA);
        ok("site_logo_id is a placeholder", layoutFiles.isPlaceholder(cap.pack.config.site_logo_id));
    });
    await step("layout/logo files are indexed for serving (by content hash)", async () => {
        const map = new Map();
        await layoutFiles.indexLocalFiles(map);
        ok("brand logo indexed by hash", map.has(HA));
    });

    section("Phase 11: apply materializes layout + logo files");
    let brandDesired;
    let brandHash;
    await step("a fresh target fetches the bytes and resolves placeholders to local files", async () => {
        const cap = await capture.buildDesiredState();
        brandDesired = cap.pack;
        brandHash = cap.hash;
        // "fresh target": unreference file A so it isn't deduped -> forces a fetch
        await Page.findOne({ name: "brand" }).delete();
        await getState().setConfig("site_logo_id", "");
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: await myEnv(), desired: brandDesired, desiredHash: brandHash, sourceEnvId: "src" });
        const res = await applyLib.applyPlan(plan_id, { applyDeletes: true, fileFetcher: async (h) => (h === HA ? A : Buffer.from("?")) });
        const brand = Page.findOne({ name: "brand" });
        ok("brand page re-created", !!brand);
        const fid = brand.layout.above[0].fileid;
        ok("layout image resolved to a local file (not a placeholder)", !layoutFiles.isPlaceholder(fid) && !!fid);
        ok("layout image content matches source", Buffer.compare(await (await File.findOne(fid)).get_contents(), A) === 0);
        const logoVal = getState().getConfig("site_logo_id");
        ok("logo resolved to a local file", !layoutFiles.isPlaceholder(logoVal) && !!logoVal);
        ok("logo content matches source", Buffer.compare(await (await File.findOne(logoVal)).get_contents(), A) === 0);
        ok("no file warnings", !res.fileWarnings || res.fileWarnings.length === 0, JSON.stringify(res.fileWarnings));
    });
    await step("re-sync of the converged brand page is a no-op (content-stable placeholder)", async () => {
        const { plan } = await planLib.buildPlan({ peerRow: null, env: await myEnv(), desired: brandDesired, desiredHash: brandHash, sourceEnvId: "src" });
        const pageChange = plan.entities.pages.create.concat(plan.entities.pages.update).some((p) => (p.name || (p.to && p.to.name)) === "brand");
        ok("brand page not re-flagged as changed", !pageChange);
    });

    section("Phase 11: content-sniffed image gate + cross-path guard");
    await step("non-image content is refused and the reference is removed", async () => {
        const evil = Buffer.from("<script>alert(1)</script> definitely not an image");
        const evilHash = sha256Hex(evil);
        const pack = { pages: [{ name: "x", layout: { above: [{ type: "image", fileid: layoutFiles.makePlaceholder(evilHash) }] } }], views: [], config: {} };
        const warnings = [];
        const res = await layoutFiles.resolvePlaceholders(pack, { fileFetcher: async (h) => (h === evilHash ? evil : Buffer.from("?")), warnings });
        ok("skipped as non-image (content-sniffed)", res.skipped >= 1 && warnings.some((w) => /non-image/.test(w)));
        ok("fileid removed (not persisted as a placeholder)", pack.pages[0].layout.above[0].fileid === "");
    });
    await step("a hash that exists only as a ROW file is not laundered into a layout", async () => {
        const html = Buffer.from("<html><script>x</script></html>");
        const hHtml = sha256Hex(html);
        const t = await Table.create("xpath", { min_role_read: 1, min_role_write: 1 });
        await Field.create({ table: t, name: "f", label: "f", type: "File" });
        const rf = await File.from_contents("evil.html", "text/html", html, 1, 1, "/");
        await Table.findOne({ name: "xpath" }).insertRow({ f: rf.field_value });
        await rowsLib.setDataMode("xpath", "managed"); // now the html file is in the ROW index
        const pack = { pages: [{ name: "y", layout: { above: [{ type: "image", fileid: layoutFiles.makePlaceholder(hHtml) }] } }], views: [], config: {} };
        const res = await layoutFiles.resolvePlaceholders(pack, { fileFetcher: async (h) => (h === hHtml ? html : Buffer.from("?")), warnings: [] });
        ok("row file NOT deduped into the layout (layout-only dedup)", res.deduped === 0);
        ok("and the fetched non-image is removed", pack.pages[0].layout.above[0].fileid === "");
    });

    section("Phase 11: rollback restores the pre-apply logo file");
    await step("apply changes the logo, rollback restores the original", async () => {
        const A2 = Buffer.concat([PNG, Buffer.from("rbA")]);
        const B2 = Buffer.concat([PNG, Buffer.from("rbB")]);
        const HB2 = sha256Hex(B2);
        const fA = await File.from_contents("la.png", "image/png", A2, 1, 100, "/");
        await getState().setConfig("site_logo_id", fA.field_value); // pre-apply logo = A2
        const fB = await File.from_contents("lb.png", "image/png", B2, 1, 100, "/");
        await getState().setConfig("site_logo_id", fB.field_value);
        const capB = await capture.buildDesiredState();             // desired logo = B2 placeholder
        await getState().setConfig("site_logo_id", fA.field_value); // revert live to A2
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: await myEnv(), desired: capB.pack, desiredHash: capB.hash, sourceEnvId: "src" });
        await applyLib.applyPlan(plan_id, { applyDeletes: true, fileFetcher: async (h) => (h === HB2 ? B2 : Buffer.from("?")) });
        const afterApply = getState().getConfig("site_logo_id");
        ok("apply switched the logo to B", Buffer.compare(await (await File.findOne(afterApply)).get_contents(), B2) === 0);
        await rollback.rollbackApply(plan_id, {});
        const afterRb = getState().getConfig("site_logo_id");
        ok("rollback restored the original logo A", Buffer.compare(await (await File.findOne(afterRb)).get_contents(), A2) === 0);
    });
};


// Start an HTTP server on an ephemeral loopback port; resolves to { server, base }.
const listenLocal = (server) => new Promise((resolve) => {
    server.listen(0, "127.0.0.1", () => resolve({ server, base: `http://127.0.0.1:${server.address().port}` }));
});


// Signed request over real HTTP with an explicit Host / extra headers (fetch cannot
// set Host). Resolves to { status, headers, body }.
const rawSignedGet = ({ base, path, envId, secret, signedHost, hostHeader, extraHeaders }) => new Promise((resolve, reject) => {
    const http      = require("http");
    const { HEADERS } = require("../lib/constants");
    const timestamp = String(Date.now());
    const nonce     = protocol.randomNonce().toString("hex");
    const canonical = protocol.buildCanonical({ timestamp, nonce, method: "GET", path, targetHost: protocol.normalizeHost(signedHost), body: "" });
    const url       = new URL(base);
    const headers   = Object.assign({
        Host:                hostHeader,
        [HEADERS.ENV_ID]:    envId,
        [HEADERS.TIMESTAMP]: timestamp,
        [HEADERS.NONCE]:     nonce,
        [HEADERS.SIGNATURE]: protocol.sign(secret, canonical)
    }, extraHeaders || {});
    const r = http.request({ host: url.hostname, port: url.port, method: "GET", path, headers }, (resp) => {
        const chunks = [];
        resp.on("data", (c) => chunks.push(c));
        resp.on("end", () => resolve({ status: resp.statusCode, headers: resp.headers, body: Buffer.concat(chunks), nonce }));
    });
    r.on("error", reject);
    r.end();
});


const phase12 = async () => {
    const http      = require("http");
    const express   = require("express");
    const transport = require("../lib/transport");
    const planLib   = require("../lib/plan");
    const applyLib  = require("../lib/apply");
    const rollback  = require("../lib/rollback");
    const rowsLib   = require("../lib/rows");
    const { HEADERS } = require("../lib/constants");
    const { API_PREFIX, API_PATHS, peerJson, peerBytes, signedRequest, PEER_LIMITS } = transport;
    const { sha256Hex } = require("../lib/common/hash");
    const myEnv     = await env.getEnv();
    // phase 9's managed docs file
    const FILE_HASH = sha256Hex(Buffer.from("hello world file content"));
    // The HTML a mock sendWrap received, with JSON quote-escaping undone.
    const wrapHtml  = (res) => JSON.stringify(res._wrap || {}).replace(/\\"/g, "\"");

    section("Phase 12: signed peer API over REAL HTTP (express + the plugin's own API routes)");
    const app = express();
    for (const r of routes.routes.filter((x) => x.url.startsWith(API_PREFIX))) {
        app[r.method](r.url, r.callback);
    }
    const { server, base } = await listenLocal(http.createServer(app));
    // Self-pairing: the "peer" is this very instance, so the responder's env_id is ours.
    const { peer: self, secret: selfSecret } = await peers.addPeer({ envId: myEnv.env_id, label: "self", baseUrl: base });
    try {
        await step("signed health/state/file round-trip verifies end to end", async () => {
            const health = await peerJson(self, myEnv.env_id, "GET", API_PATHS.HEALTH, null);
            ok("health over HTTP names this env", health.env_id === myEnv.env_id);
            const state = await ops.fetchPeerState(self, myEnv);
            ok("state over HTTP returns a pack", Array.isArray(state.pack.tables));
            const bytes = await peerBytes(self, myEnv.env_id, API_PATHS.file(FILE_HASH));
            ok("file over HTTP returns the verified bytes", sha256Hex(bytes) === FILE_HASH);
            let msg = "";
            try {
                await peerBytes(self, myEnv.env_id, API_PATHS.file("e".repeat(64)));
            } catch (e) {
                msg = e.message;
            }
            ok("unknown file hash -> signed 404 surfaced as an error", /status 404/.test(msg), msg);
        });
        await step("real Express: X-Forwarded-Host spoof does not rebind the signed host", async () => {
            const realHost = new URL(base).host;
            const good = await rawSignedGet({ base, path: API_PATHS.HEALTH, envId: myEnv.env_id, secret: selfSecret, signedHost: realHost, hostHeader: realHost });
            ok("control: correct Host -> 200", good.status === 200, `status=${good.status}`);
            const expected = protocol.buildResponseCanonical({ nonce: good.nonce, status: good.status, body: good.body });
            ok("control: response signature verifies", protocol.verifySignature(selfSecret, expected, good.headers[HEADERS.RESPONSE_SIGNATURE.toLowerCase()]));
            const spoof = await rawSignedGet({ base, path: API_PATHS.HEALTH, envId: myEnv.env_id, secret: selfSecret, signedHost: realHost,
                hostHeader: "t2.example.com", extraHeaders: { "X-Forwarded-Host": realHost } });
            ok("Host=t2 + XFH=<signed host> -> 401", spoof.status === 401, `status=${spoof.status}`);
        });
        await step("admin preview POST pulls over HTTP and persists exactly one plan; GET persists none", async () => {
            const count = async () => Number((await db.query(`SELECT COUNT(*) AS c FROM ${db.getTenantSchemaPrefix()}_ss_plans`)).rows[0].c);
            const n0 = await count();
            let res = mkRes();
            await routeOf(routes.routes, "get", "/admin/site-sync/plan")(mkAdminReq({ originalUrl: "/admin/site-sync/plan" }), res);
            ok("GET plan renders a POST selector", /method="post"/i.test(wrapHtml(res)) && (await count()) === n0);
            res = mkRes();
            const req = mkAdminReq({ method: "POST", originalUrl: "/admin/site-sync/plan", body: { peer: String(self.peer_id) } });
            await routeOf(routes.routes, "post", "/admin/site-sync/plan")(req, res);
            ok("POST plan rendered a preview", !!res._wrap && !res._redirect, JSON.stringify(req._flash));
            ok("POST plan persisted one plan", (await count()) === n0 + 1);
            res = mkRes();
            const bad = mkAdminReq({ method: "POST", body: { peer: "999999" } });
            await routeOf(routes.routes, "post", "/admin/site-sync/plan")(bad, res);
            ok("unknown peer -> flashed error + redirect", res._redirect === "/admin/site-sync/plan" && bad._flash.some((f) => f.type === "danger"));
        });
    } finally {
        await peers.deletePeer(self.peer_id);
        server.close();
    }

    section("Phase 12: the client rejects unauthenticated / tampered responses");
    const tamperEnv = randomUuid();
    let mode = "good";
    const tamper = http.createServer((req, res) => {
        const nonce   = req.headers[HEADERS.NONCE.toLowerCase()];
        const genuine = Buffer.from(JSON.stringify({ env_id: mode === "wrongEnv" ? randomUuid() : tamperEnv, plugins: [] }));
        const signFor = mode === "replayed" ? protocol.randomNonce().toString("hex") : nonce;
        const sig     = protocol.sign(tamperSecret, protocol.buildResponseCanonical({ nonce: signFor, status: 200, body: genuine }));
        if (mode === "hang") {
            return;
        }
        res.setHeader("content-type", "application/json");
        if (mode === "unsigned401") {
            res.statusCode = 401;
            res.end(JSON.stringify({ error: "bad signature" }));
            return;
        }
        if (mode !== "missing") {
            res.setHeader(HEADERS.RESPONSE_SIGNATURE, sig);
        }
        res.end(mode === "tamper" ? Buffer.from(JSON.stringify({ env_id: tamperEnv, plugins: [{ name: "evil" }] })) : genuine);
    });
    const { base: tamperBase } = await listenLocal(tamper);
    const { peer: tamperPeer, secret: tamperSecret } = await peers.addPeer({ envId: tamperEnv, label: "tamper", baseUrl: tamperBase });
    const attempt = async () => {
        try {
            await peerJson(tamperPeer, myEnv.env_id, "GET", API_PATHS.HEALTH, null);
            return "accepted";
        } catch (e) {
            return e.message;
        }
    };
    try {
        await step("signed responses: good accepted; tampered/missing/replayed/wrong-env rejected", async () => {
            mode = "good";
            ok("correctly signed response accepted", (await attempt()) === "accepted");
            mode = "tamper";
            ok("tampered body rejected", /without a valid response signature/.test(await attempt()));
            mode = "missing";
            ok("missing signature rejected", /without a valid response signature/.test(await attempt()));
            mode = "replayed";
            ok("signature for another request's nonce rejected", /without a valid response signature/.test(await attempt()));
            mode = "wrongEnv";
            ok("validly signed answer from another env_id rejected", /answered as env_id/.test(await attempt()));
            mode = "unsigned401";
            const m = await attempt();
            ok("unsigned 401 reported with status + unverified detail", /status 401/.test(m) && /unverified detail: .*bad signature/.test(m), m);
        });
        await step("response byte cap and request timeout", async () => {
            mode = "good";
            let msg = "";
            try {
                await signedRequest({ baseUrl: tamperBase, method: "GET", path: API_PATHS.HEALTH, body: null, sourceEnvId: myEnv.env_id, secret: tamperSecret, maxBytes: 8 });
            } catch (e) {
                msg = e.message;
            }
            ok("over-cap response refused", /too large/.test(msg), msg);
            mode = "hang";
            const saved = PEER_LIMITS.TIMEOUT_MS;
            PEER_LIMITS.TIMEOUT_MS = 300;
            const t0 = Date.now();
            const outcome = await attempt();
            PEER_LIMITS.TIMEOUT_MS = saved;
            ok("hung peer aborted by the timeout", outcome !== "accepted" && Date.now() - t0 < 5000, `${outcome} after ${Date.now() - t0}ms`);
        });
    } finally {
        await peers.deletePeer(tamperPeer.peer_id);
        tamper.closeAllConnections();
        tamper.close();
    }

    section("Phase 12: GET /api/file/:hash error branches (all signed)");
    const fileEnv = randomUuid();
    const { secret: fileSecret } = await peers.addPeer({ envId: fileEnv, label: "files", baseUrl: "http://localhost:3000" });
    const fileCall = async (hash) => {
        const req = mkPeerReq({ method: "GET", path: API_PATHS.file(hash), host: HOST, envId: fileEnv, secret: fileSecret });
        req.params = { hash };
        const res = mkRes();
        await routeOf(routes.routes, "get", API_PATHS.FILE_ROUTE)(req, res);
        return { req, res };
    };
    await step("apiFile: bad hash 400, unknown 404, outside-store 400, missing 404, good 200", async () => {
        let r = await fileCall("NOT-A-HASH");
        ok("malformed hash -> 400 (signed)", r.res.statusCode === 400 && responseSigned(r.req, r.res, fileSecret));
        r = await fileCall("d".repeat(64));
        ok("unindexed hash -> 404 (signed)", r.res.statusCode === 404 && responseSigned(r.req, r.res, fileSecret));
        const realResolve = rowsLib.resolveFileByHash;
        try {
            rowsLib.resolveFileByHash = async () => "../../../../etc/passwd";
            r = await fileCall("c".repeat(64));
            ok("indexed value escaping the tenant store -> 400, nothing read", r.res.statusCode === 400 && !/root:/.test(String(r.res.raw)) && responseSigned(r.req, r.res, fileSecret));
            rowsLib.resolveFileByHash = async () => "no-such-dir/gone.bin";
            r = await fileCall("c".repeat(64));
            ok("indexed but missing from the store -> 404", r.res.statusCode === 404 && responseSigned(r.req, r.res, fileSecret));
        } finally {
            rowsLib.resolveFileByHash = realResolve;
        }
        r = await fileCall(FILE_HASH);
        ok("synced file -> 200 with signed bytes", r.res.statusCode === 200 && sha256Hex(r.res.raw) === FILE_HASH && responseSigned(r.req, r.res, fileSecret));
    });

    section("Phase 12: admin flows (peers, tables, settings, apply page)");
    await step("peers page Plan button posts `peer` to the POST plan route", async () => {
        const res = mkRes();
        await routeOf(routes.routes, "get", "/admin/site-sync/peers")(mkAdminReq({ originalUrl: "/admin/site-sync/peers" }), res);
        ok("a POST form to /admin/site-sync/plan carrying name=peer", /action="\/admin\/site-sync\/plan" method="post"[^>]*><input type="hidden" name="_csrf"[^>]*><input type="hidden" name="peer"/.test(wrapHtml(res)));
    });
    await step("rotate generates; replace-with-pasted-secret installs exactly that secret", async () => {
        const target = (await peers.listPeers()).find((x) => x.env_id === fileEnv);
        const before = await peers.peerSecret(target.peer_id);
        let req = mkAdminReq({ method: "POST", body: { peer_id: String(target.peer_id) } });
        let res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/peers/rotate")(req, res);
        ok("rotate rendered the new secret once", !!res._wrap && Buffer.compare(before, await peers.peerSecret(target.peer_id)) !== 0);
        const pasted = protocol.randomSecret();
        req = mkAdminReq({ method: "POST", body: { peer_id: String(target.peer_id), secret: pasted.toString("hex") } });
        res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/peers/rotate")(req, res);
        ok("pasted secret installed verbatim", Buffer.compare(pasted, await peers.peerSecret(target.peer_id)) === 0 && res._redirect === "/admin/site-sync/peers");
        req = mkAdminReq({ method: "POST", body: { peer_id: String(target.peer_id), secret: "zz" } });
        res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/peers/rotate")(req, res);
        ok("malformed pasted secret refused", req._flash.some((f) => f.type === "danger") && Buffer.compare(pasted, await peers.peerSecret(target.peer_id)) === 0);
    });
    await step("tables page renders every table with its mode (Table.find is async)", async () => {
        const res = mkRes();
        await routeOf(routes.routes, "get", "/admin/site-sync/tables")(mkAdminReq({ originalUrl: "/admin/site-sync/tables" }), res);
        const html = wrapHtml(res);
        ok("the page lists users as locked and offers the mode buttons", !!res._wrap && html.includes("locked (user)") && html.includes("/admin/site-sync/tables/set"));
    });
    await step("tables/set refuses plugin infrastructure tables", async () => {
        const req = mkAdminReq({ method: "POST", body: { table: "_ss_peers", mode: "managed" } });
        const res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/tables/set")(req, res);
        ok("_ss_peers flip refused", req._flash.some((f) => f.type === "danger") && (await rowsLib.dataModeOf("_ss_peers")) === "user");
    });
    await step("settings: invalid choice refused (not silently defaulted); flashes are escaped", async () => {
        const before = await env.getEnv();
        const req = mkAdminReq({ method: "POST", body: { sync_mode: "<b>bogus</b>", on_delete: "confirm", on_apply: "confirm" } });
        const res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/settings")(req, res);
        const flash = req._flash[0] || {};
        ok("refused with an error flash", flash.type === "danger" && (await env.getEnv()).sync_mode === before.sync_mode);
        ok("flash text is HTML-escaped", !/<b>/.test(flash.msg || "") && /&lt;b&gt;/.test(flash.msg || ""));
    });
    await step("apply page renders every warning; a failed apply offers the rollback form", async () => {
        const cur = await capture.buildDesiredState();
        const { plan_id, plan } = await planLib.buildPlan({ peerRow: null, env: myEnv, desired: cur.pack, desiredHash: cur.hash, sourceEnvId: "src" });
        const realApply = applyLib.applyPlan;
        try {
            applyLib.applyPlan = async () => ({
                applied: true, plan_id, summary: { total: 2 },
                deletes: { applied: true, deleted: 0, skipped: [{ name: "t", reason: "r" }] },
                rows: { applied: false }, warnings: ["layout image <img> fetch failed", "row not inserted"]
            });
            let res = mkRes();
            await routeOf(routes.routes, "post", "/admin/site-sync/apply")(mkAdminReq({ method: "POST", body: { plan_id } }), res);
            let html = wrapHtml(res);
            ok("success page lists every warning, escaped", html.includes("Warnings (2)") && html.includes("&lt;img&gt;") && html.includes("row not inserted"));
            await rollback.saveRestorePoint(plan_id, cur.pack, plan);
            applyLib.applyPlan = async () => {
                const err = new Error("install_pack exploded");
                err.planId = plan_id;
                throw err;
            };
            res = mkRes();
            await routeOf(routes.routes, "post", "/admin/site-sync/apply")(mkAdminReq({ method: "POST", body: { plan_id } }), res);
            html = wrapHtml(res);
            ok("failure page shows the error", html.includes("install_pack exploded"));
            ok("failure page offers rollback for the saved restore point", html.includes("/admin/site-sync/rollback") && html.includes(plan_id));
        } finally {
            applyLib.applyPlan = realApply;
        }
    });
    await step("real apply + rollback through the admin routes", async () => {
        const prev = getState().getConfig("site_name") || "";
        await getState().setConfig("site_name", "ViaRoutes");
        const cap = await capture.buildDesiredState();
        await getState().setConfig("site_name", prev);
        const { plan_id } = await planLib.buildPlan({ peerRow: null, env: await env.getEnv(), desired: cap.pack, desiredHash: cap.hash, sourceEnvId: "src" });
        let req = mkAdminReq({ method: "POST", body: { plan_id } });
        let res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/apply")(req, res);
        ok("apply page rendered and the change landed", wrapHtml(res).includes("Applied plan") && getState().getConfig("site_name") === "ViaRoutes");
        ok("apply page offers the rollback form", wrapHtml(res).includes('action="/admin/site-sync/rollback"'));
        req = mkAdminReq({ method: "POST", body: { plan_id } });
        res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/rollback")(req, res);
        ok("rollback via the route reverted the change", req._flash.some((f) => f.type === "success") && getState().getConfig("site_name") === prev, JSON.stringify(req._flash));
        req = mkAdminReq({ method: "POST", body: { plan_id } });
        res = mkRes();
        await routeOf(routes.routes, "post", "/admin/site-sync/rollback")(req, res);
        ok("a second rollback is refused with an error flash", req._flash.some((f) => f.type === "danger") && res._redirect === "/admin/site-sync/");
    });
    await peers.deletePeer((await peers.findPeerByEnvId(fileEnv)).peer_id);
};


const main = async () => {
    console.log(`DB = ${process.env.SQLITE_FILEPATH}`);
    await bootstrapPlugin();
    await phase1();
    await phase2();
    await phase3();
    await phase4();
    await phase5();
    await phase6();
    await phase7();
    await phase8();
    await phase9();
    await phase10();
    await phase11();
    await phase12();
    process.exit(summary());
};


main().catch((err) => {
    console.error(err && err.stack ? err.stack : err);
    process.exit(1);
});