nearle_daily_admin_web/scripts/db.mjs

/**
 * Direct database access, through Hasura.
 *
 * A scratchpad for reading and fixing rows that no screen exposes — setting a
 * password on an account that was spawned without one, flipping a status,
 * checking what the API is actually reading. It talks to the Hasura instance
 * the old console proxies to (`api.workolik.com`), using the admin secret from
 * `daily_merchant_web/.env`, which is gitignored and stays there.
 *
 *   node scripts/db.mjs tables
 *   node scripts/db.mjs user care@nearle.in
 *   node scripts/db.mjs setpw care@nearle.in <password>
 *   node scripts/db.mjs sql "select userid, authname from app_users limit 5"
 *
 * The secret is read from disk or the environment and never printed, never
 * written anywhere, and never passed on the command line.
 *
 * ── Read this before using `setpw` ────────────────────────────────────────
 * This points at PRODUCTION. Every write here is immediate and unversioned.
 * `setpw` refuses to run unless the account's password column is already
 * empty, so it can only ever complete a setup that was never finished — it
 * cannot overwrite a working login. Lift that guard only deliberately.
 *
 * Passwords in `app_users` are stored in clear. That is a property of this
 * backend, not of this script; anything written here is readable by anyone
 * with database access.
 */

import { readFileSync, existsSync } from 'node:fs';
import { resolve, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';

const HERE = dirname(fileURLToPath(import.meta.url));

/**
 * Where Hasura actually lives, discovered rather than assumed.
 *
 * The first version of this hardcoded `/v1/graphql` at the host root and got a
 * 404. The old console's proxy is the clue it should have read: it rewrites
 * `/hasura` to `/api/rest/`, which means Hasura is mounted under `/api`, not at
 * the root. Rather than swap one guess for another, this tries the candidates
 * and uses whichever answers.
 *
 * Override with HASURA_URL if it moves again — pass the full GraphQL URL.
 */
const ENDPOINT_CANDIDATES = process.env.HASURA_URL
  ? [process.env.HASURA_URL]
  : [
      'https://api.workolik.com/api/v1/graphql',
      'https://api.workolik.com/v1/graphql',
      'https://api.workolik.com/hasura/v1/graphql',
    ];

let ENDPOINT = ENDPOINT_CANDIDATES[0];

/** Finds the first candidate that answers a trivial query. */
async function resolveEndpoint() {
  for (const candidate of ENDPOINT_CANDIDATES) {
    try {
      const response = await fetch(candidate, {
        method: 'POST',
        headers: { 'content-type': 'application/json', 'x-hasura-admin-secret': SECRET },
        body: JSON.stringify({ query: '{ __typename }' }),
      });
      if (!response.ok) continue;
      const payload = await response.json().catch(() => null);
      if (payload && !payload.errors) {
        ENDPOINT = candidate;
        return candidate;
      }
    } catch {
      // Next candidate.
    }
  }
  console.error(
    'Could not find the Hasura GraphQL endpoint. Tried:\n' +
      ENDPOINT_CANDIDATES.map((c) => `  ${c}`).join('\n') +
      '\nSet HASURA_URL to the full GraphQL URL and run again.',
  );
  process.exit(1);
}

/** Where the old console keeps its gitignored secret, relative to this repo. */
const ENV_CANDIDATES = [
  resolve(HERE, '../../../nearle-daily/daily_merchant_web/.env'),
  resolve(HERE, '../../daily_merchant_web/.env'),
  'D:/nearle-daily/daily_merchant_web/.env',
];

function readSecret() {
  if (process.env.HASURA_ADMIN_SECRET) return process.env.HASURA_ADMIN_SECRET;

  for (const path of ENV_CANDIDATES) {
    if (!existsSync(path)) continue;
    const line = readFileSync(path, 'utf8')
      .split(/\r?\n/)
      .find((row) => row.startsWith('HASURA_ADMIN_SECRET='));
    if (!line) continue;
    const value = line.slice('HASURA_ADMIN_SECRET='.length).trim().replace(/^["']|["']$/g, '');
    if (value) return value;
  }

  console.error(
    'No admin secret found.\n' +
      'Expected HASURA_ADMIN_SECRET in one of:\n' +
      ENV_CANDIDATES.map((p) => `  ${p}`).join('\n') +
      '\nor set it in the environment for this command.',
  );
  process.exit(1);
}

const SECRET = readSecret();

async function gql(query, variables = {}) {
  let response;
  try {
    response = await fetch(ENDPOINT, {
      method: 'POST',
      headers: { 'content-type': 'application/json', 'x-hasura-admin-secret': SECRET },
      body: JSON.stringify({ query, variables }),
    });
  } catch (cause) {
    console.error(`Could not reach ${ENDPOINT} — ${cause.message}`);
    process.exit(1);
  }

  const payload = await response.json().catch(() => null);
  if (!payload) {
    console.error(`Malformed response (HTTP ${response.status})`);
    process.exit(1);
  }
  if (payload.errors) {
    for (const error of payload.errors) console.error(`✗ ${error.message}`);
    process.exit(1);
  }
  return payload.data;
}

/* ── Commands ─────────────────────────────────────────────────────────────── */

/** Every table Hasura has tracked. Start here if a query says "field not found". */
async function tables() {
  const data = await gql(`{ __schema { queryType { fields { name } } } }`);
  const names = data.__schema.queryType.fields
    .map((field) => field.name)
    .filter((name) => !name.endsWith('_aggregate') && !name.endsWith('_by_pk'))
    .sort();
  console.log(names.join('\n'));
  console.log(`\n${names.length} tables`);
}

const USER_FIELDS = `userid authname firstname lastname contactno roleid status tenantid locationid configid`;

async function findUser(email) {
  const data = await gql(
    `query ($email: String!) {
       app_users(where: { authname: { _eq: $email } }) { ${USER_FIELDS} password }
     }`,
    { email },
  );
  return data.app_users ?? [];
}

async function user(email) {
  const rows = await findUser(email);
  if (rows.length === 0) {
    console.log(`No account with authname "${email}".`);
    return;
  }
  for (const row of rows) {
    // The password itself is never printed — only whether one exists, which is
    // the only thing anyone needs to know from here.
    const { password, ...rest } = row;
    console.log({ ...rest, haspassword: String(password ?? '').trim() !== '' });
  }
}

/**
 * Completes a password setup that was never finished.
 *
 * Refuses if a password is already set. An account that can sign in must not
 * be changeable from a scratchpad — that is a support action with a person
 * behind it, not a one-liner.
 */
async function setpw(email, password) {
  if (!password || password.length < 6) {
    console.error('Password must be at least 6 characters (the backend enforces this too).');
    process.exit(1);
  }

  const rows = await findUser(email);
  if (rows.length === 0) {
    console.error(`No account with authname "${email}".`);
    process.exit(1);
  }
  if (rows.length > 1) {
    console.error(
      `${rows.length} accounts share that email (configid ${rows.map((r) => r.configid).join(', ')}).\n` +
        'Refusing to guess. Use `sql` with an explicit userid.',
    );
    process.exit(1);
  }

  const row = rows[0];
  if (String(row.password ?? '').trim() !== '') {
    console.error(
      `userid ${row.userid} already has a password. This command only completes an unfinished setup.\n` +
        'To reset a working login, do it deliberately with `sql`.',
    );
    process.exit(1);
  }
  if (row.roleid === 7 || row.roleid === 8) {
    console.error(
      `userid ${row.userid} is a till account (roleid ${row.roleid}). Those sign in at the terminal with a PIN, not here.`,
    );
    process.exit(1);
  }

  const data = await gql(
    `mutation ($userid: Int!, $password: String!) {
       update_app_users(where: { userid: { _eq: $userid } }, _set: { password: $password }) {
         affected_rows
       }
     }`,
    { userid: row.userid, password },
  );

  const affected = data.update_app_users?.affected_rows ?? 0;
  if (affected !== 1) {
    console.error(`Expected to update 1 row, updated ${affected}. Nothing assumed — check manually.`);
    process.exit(1);
  }
  console.log(
    `✓ Password set on userid ${row.userid} (${email}), roleid ${row.roleid}, tenantid ${row.tenantid}.`,
  );
  console.log('  Sign in at the console with it now.');
}

/**
 * Arbitrary read-only SQL, via Hasura's `run_sql`.
 *
 * Reads only. A statement that writes is refused here — writes go through a
 * named command above, where they can carry their own guard.
 */
async function sql(statement) {
  if (/^\s*(insert|update|delete|drop|alter|truncate|create)\b/i.test(statement)) {
    console.error('This command runs reads only. Add a named command for a write.');
    process.exit(1);
  }

  const endpoint = ENDPOINT.replace(/\/v1\/graphql$/, '/v2/query');
  const response = await fetch(endpoint, {
    method: 'POST',
    headers: { 'content-type': 'application/json', 'x-hasura-admin-secret': SECRET },
    body: JSON.stringify({
      type: 'run_sql',
      args: { source: 'default', sql: statement, read_only: true },
    }),
  });

  const payload = await response.json().catch(() => null);
  if (!response.ok || !payload) {
    console.error(payload?.error ?? `HTTP ${response.status}`);
    process.exit(1);
  }

  const rows = payload.result ?? [];
  for (const row of rows) console.log(row.join('\t'));
  console.log(`\n${Math.max(0, rows.length - 1)} rows`);
}

/**
 * Why the app shows fewer products than the console does.
 *
 *   node scripts/db.mjs appgap <tenantid> <locationid>
 *
 * `getproductsbysubcategory` is what the customer app browses with, and three
 * separate conditions decide whether a product survives it. None of them is an
 * error and none of them is logged — a product that fails any one simply is not
 * in the response, which is why the console can be full and the app empty.
 *
 *  A. `WHERE a.categoryid = ?` — the caller passes 2, and the filter is not
 *     optional (`productRepository.go:865`). A product in any other category is
 *     invisible to this endpoint no matter what else is true of it.
 *
 *  B. `WHERE pl.locationid = ?` on a LEFT JOIN to `productlocations`. A product
 *     with no row for THIS outlet joins to NULL, and the WHERE then drops it.
 *     Being in the catalogue is not the same as being on a shelf: something has
 *     to write `productlocations`, and nothing does that automatically.
 *
 *  C. The grouping in `GetProductsBySubcategory` walks the real subcategories
 *     of category 2 and collects products matching each, then sweeps up
 *     everything with `subcategoryid = 0` as "Uncategorized". A product whose
 *     subcategoryid is non-zero but is NOT a subcategory of category 2 matches
 *     neither loop and vanishes — it is in the query results and absent from
 *     the response. This one is worth looking for first, because it looks like
 *     nothing at all.
 */
async function appgap(tenantid, locationid) {
  const tid = Number(tenantid);
  const lid = Number(locationid);
  if (!tid || !lid) {
    console.error('Usage: node scripts/db.mjs appgap <tenantid> <locationid>');
    process.exit(1);
  }

  // GraphQL, not `run_sql`.
  //
  // `run_sql` lives on Hasura's `/v2/query` admin API, which answered 404 here —
  // it is disabled on managed instances and behind a different path on others.
  // Three ordinary queries and the bucketing done in JS needs none of that, and
  // works on any Hasura the admin secret can reach.
  const data = await gql(
    `query ($tid: Int!, $lid: Int!) {
       products(where: { tenantid: { _eq: $tid } }) {
         productid productname categoryid subcategoryid
       }
       productlocations(where: { tenantid: { _eq: $tid }, locationid: { _eq: $lid } }) {
         productid
       }
       productsubcategories(where: { categoryid: { _eq: 2 } }) {
         subcategoryid subcategoryname
       }
     }`,
    { tid, lid },
  );

  const products = data.products ?? [];
  const listed = new Set((data.productlocations ?? []).map((row) => row.productid));
  const realSubs = new Map(
    (data.productsubcategories ?? []).map((row) => [row.subcategoryid, row.subcategoryname]),
  );

  if (products.length === 0) {
    console.log(`Tenant ${tid} has no products at all.`);
    return;
  }

  const buckets = new Map();
  const examples = new Map();
  for (const product of products) {
    let reason;
    if (product.categoryid !== 2) {
      reason = 'A. categoryid is not 2 — the app only asks for category 2';
    } else if (!listed.has(product.productid)) {
      reason = 'B. not listed at this outlet — no productlocations row';
    } else if (product.subcategoryid !== 0 && !realSubs.has(product.subcategoryid)) {
      reason = 'C. subcategoryid is not a real subcategory of 2 — silently dropped';
    } else {
      reason = 'OK. should appear in the app';
    }
    buckets.set(reason, (buckets.get(reason) ?? 0) + 1);
    if (!examples.has(reason)) examples.set(reason, product);
  }

  console.log(`${products.length} products on tenant ${tid}\n`);
  const ordered = [...buckets.entries()].sort((a, b) => b[1] - a[1]);
  for (const [reason, count] of ordered) {
    const sample = examples.get(reason);
    console.log(`  ${String(count).padStart(5)}  ${reason}`);
    console.log(
      `         e.g. ${sample.productname} (id ${sample.productid}, category ${sample.categoryid}, subcategory ${sample.subcategoryid})`,
    );
  }

  console.log(`\nReal subcategories of category 2: ${[...realSubs.values()].join(', ') || '(none)'}`);
}

/* ── Dispatch ─────────────────────────────────────────────────────────────── */

const [command, ...rest] = process.argv.slice(2);

const COMMANDS = {
  tables: () => tables(),
  user: () => user(rest[0]),
  setpw: () => setpw(rest[0], rest[1]),
  sql: () => sql(rest.join(' ')),
  appgap: () => appgap(rest[0], rest[1]),
};

if (!command || !COMMANDS[command]) {
  console.log(
    [
      'node scripts/db.mjs <command>',
      '',
      '  tables                     every table Hasura has tracked',
      '  user <email>               show an account (never prints the password)',
      '  setpw <email> <password>   set a password on an account that has none',
      '  sql "<select ...>"         read-only SQL',
      '  appgap <tenant> <outlet>   why the app shows fewer products than the console',
    ].join('\n'),
  );
  process.exit(command ? 1 : 0);
}

// Locate Hasura before anything talks to it.
await resolveEndpoint();

await COMMANDS[command]();