Jyotisha/frontend/tests/database-admin-account-reset.test.ts

import assert from "node:assert/strict";
import { spawnSync } from "node:child_process";
import { fileURLToPath } from "node:url";
import test from "node:test";
import { Client } from "pg";

import { startPostgresFixture } from "./helpers/postgres-fixture.ts";

const runnerPath = fileURLToPath(new URL("../scripts/db-migrate.mjs", import.meta.url));
const ownerId = "81000000-0000-4000-8000-000000000001";
const targetId = "81000000-0000-4000-8000-000000000002";

function postgresCode(error: unknown): string | undefined {
  return typeof error === "object" && error !== null && "code" in error
    ? String((error as { code?: unknown }).code)
    : undefined;
}

test("admin customer reset clears only rebuildable application state", async () => {
  const fixture = startPostgresFixture();
  const adminRuntime = new Client({
    connectionString: fixture.connectionUrl("admin_runtime", "admin-runtime-test-password"),
  });

  try {
    const migration = spawnSync(process.execPath, [runnerPath], {
      encoding: "utf8",
      env: {
        ...process.env,
        SCHEMA_DATABASE_URL: fixture.connectionUrl("schema_owner", "schema-owner-test-password"),
      },
    });
    assert.equal(migration.status, 0, `${migration.stdout}${migration.stderr}`);

    fixture.psqlAs("identity_runtime", "identity-runtime-test-password", `
      insert into identity.users (id, name, email, email_verified, email_verified_at, role) values
        ('${ownerId}', 'Owner', 'reset-owner@example.com', true, now(), 'admin'),
        ('${targetId}', 'Target Admin', 'reset-target@example.com', true, now(), 'admin');
      insert into identity.accounts (id, account_id, provider_id, user_id, password)
      values ('82000000-0000-4000-8000-000000000001', 'reset-target@example.com', 'credential', '${targetId}', 'password-hash');
      insert into identity.sessions (id, token, user_id, expires_at)
      values ('82000000-0000-4000-8000-000000000002', 'reset-session-token', '${targetId}', now() + interval '1 day');
    `);

    fixture.psql(`
      insert into public.admin_users (user_id, created_by) values
        ('${ownerId}', '${ownerId}'), ('${targetId}', '${ownerId}');
      insert into public.admin_user_roles (admin_user_id, role_id, assigned_by)
      select values.user_id, roles.id, '${ownerId}'::uuid
      from (values
        ('${ownerId}'::uuid, 'owner'),
        ('${targetId}'::uuid, 'support')
      ) values(user_id, role_code)
      join public.admin_roles roles on roles.code = values.role_code;

      update public.profiles set
        credits = 73,
        name = 'Reset Me',
        birth_date = '1990-01-02',
        birth_time = '03:04',
        country_code = 'CN',
        province_code = '11',
        city_code = '1101',
        district_code = '110101',
        onboarding_payload = '{"ready":true}'::jsonb,
        onboarding_version = 'test-v1',
        onboarding_generated_at = now(),
        latitude = 39.9,
        longitude = 116.4,
        timezone_offset = 8,
        reported_birth_time = '03:04',
        active_birth_time = '03:04',
        birth_time_source = 'legacy_import',
        birth_time_status = 'confirmed',
        birth_place_label = 'Test Place',
        birth_place_type = 'city',
        birth_place_provider = 'geonames',
        birth_place_provider_id = 'test-place',
        timezone_id = 'Asia/Shanghai',
        timezone_source = 'iana_historical'
      where id = '${targetId}';

      insert into public.chat_sessions (user_id, title, theme, messages)
      values ('${targetId}', 'Reset Chat', 'general', '[]'::jsonb);
      insert into public.chart_profiles (user_id, role, profile)
      values ('${targetId}', 'self', '{}'::jsonb);
      insert into public.synastry_reports (user_id, partner_name, report)
      values ('${targetId}', 'Partner', '{}'::jsonb);
      insert into public.credit_transactions (user_id, transaction_type, amount, balance_after, request_id)
      values ('${targetId}', 'redeem', 73, 73, 'reset-preserved-credit');
      insert into audit.admin_audit_logs (
        actor_user_id, actor_email, actor_role, action, target_type, target_id,
        after_value, request_id, permission_used, reason
      ) values (
        '${targetId}', 'reset-target@example.com', 'admin', 'admin.customer.birth_data.read',
        'customer', '${targetId}', '{}'::jsonb, 'reset-preserved-audit',
        'admin.customers.birth_data.read', 'existing audit history'
      );
    `);

    await adminRuntime.connect();
    await assert.rejects(
      adminRuntime.query(
        "select * from public.admin_reset_customer_account($1, $2, $3, $4)",
        [targetId, targetId, "unauthorized self reset", "reset-denied"],
      ),
      (error) => postgresCode(error) === "42501",
    );

    const reset = await adminRuntime.query<{
      user_id: string;
      email: string;
      credits: number;
      chat_sessions_deleted: number;
      chart_profiles_deleted: number;
      synastry_reports_deleted: number;
    }>(
      "select * from public.admin_reset_customer_account($1, $2, $3, $4)",
      [ownerId, targetId, "prepare account for a fresh onboarding test", "reset-success"],
    );
    assert.deepEqual(reset.rows, [{
      user_id: targetId,
      email: "reset-target@example.com",
      credits: 73,
      chat_sessions_deleted: 1,
      chart_profiles_deleted: 1,
      synastry_reports_deleted: 1,
    }]);

    const state = JSON.parse(fixture.psql(`
      select jsonb_build_object(
        'identityUsers', (select count(*) from identity.users where id = '${targetId}'),
        'authUsers', (select count(*) from auth.users where id = '${targetId}'),
        'identityAccounts', (select count(*) from identity.accounts where user_id = '${targetId}'),
        'identitySessions', (select count(*) from identity.sessions where user_id = '${targetId}'),
        'credits', (select credits from public.profiles where id = '${targetId}'),
        'profileReset', (select name is null and birth_date is null and birth_time is null and onboarding_payload is null and reported_birth_time is null and active_birth_time is null and birth_place_label is null from public.profiles where id = '${targetId}'),
        'chatSessions', (select count(*) from public.chat_sessions where user_id = '${targetId}'),
        'chartProfiles', (select count(*) from public.chart_profiles where user_id = '${targetId}'),
        'synastryReports', (select count(*) from public.synastry_reports where user_id = '${targetId}'),
        'creditTransactions', (select count(*) from public.credit_transactions where user_id = '${targetId}'),
        'adminRoles', (select count(*) from public.admin_user_roles where admin_user_id = '${targetId}'),
        'existingAudit', (select count(*) from audit.admin_audit_logs where actor_user_id = '${targetId}' and request_id = 'reset-preserved-audit'),
        'resetAudit', (select count(*) from audit.admin_audit_logs where actor_user_id = '${ownerId}' and request_id = 'reset-success' and action = 'admin.customer.account.reset')
      )
    `)) as Record<string, number | boolean>;

    assert.deepEqual(state, {
      identityUsers: 1,
      authUsers: 1,
      identityAccounts: 1,
      identitySessions: 1,
      credits: 73,
      profileReset: true,
      chatSessions: 0,
      chartProfiles: 0,
      synastryReports: 0,
      creditTransactions: 1,
      adminRoles: 1,
      existingAudit: 1,
      resetAudit: 1,
    });
  } finally {
    await adminRuntime.end().catch(() => undefined);
    fixture.stop();
  }
});