optima/dalpuri/temp-find-current-config-value-store.ts

import { PrismaMssql } from "@prisma/adapter-mssql";
import { Prisma, PrismaClient } from "./generated/prisma/client";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL is not set.");
}

const adapter = new PrismaMssql(connectionString);
const prisma = new PrismaClient({ adapter });

type CandidateTable = { table_name: string };
type CandidateColumn = { table_name: string; column_name: string };

type DmmfField = {
  name: string;
  dbName: string | null;
};

type DmmfModel = {
  name: string;
  dbName: string | null;
  fields: DmmfField[];
};

const TABLE_PATTERN = /config|configur/i;
const VALUE_COLUMN_PATTERN = /value|field|question|token/i;
const TOP_CONFIG_LIMIT = 150;
const CONFIG_KEY_COLUMNS = new Set([
  "Config_RecID",
  "Configuration_RecID",
  "Configuration_RecId",
]);

function byName(a: string, b: string) {
  return a.localeCompare(b);
}

try {
  const models = Prisma.dmmf.datamodel.models as unknown as DmmfModel[];

  const configModels = models
    .map((model) => ({
      model,
      tableName: model.dbName ?? model.name,
    }))
    .filter(({ tableName }) => TABLE_PATTERN.test(tableName));

  const candidateTables: CandidateTable[] = configModels
    .map(({ tableName }) => ({ table_name: tableName }))
    .sort((a, b) => byName(a.table_name, b.table_name));

  const candidateColumns: CandidateColumn[] = configModels
    .flatMap(({ model, tableName }) =>
      model.fields
        .map((field) => field.dbName ?? field.name)
        .filter((columnName) => VALUE_COLUMN_PATTERN.test(columnName))
        .map((columnName) => ({
          table_name: tableName,
          column_name: columnName,
        }))
    )
    .sort(
      (a, b) =>
        byName(a.table_name, b.table_name) ||
        byName(a.column_name, b.column_name)
    );

  const valueTablesWithConfigKey: CandidateTable[] = configModels
    .filter(({ model }) => {
      const columnNames = model.fields.map(
        (field) => field.dbName ?? field.name
      );
      const hasConfigKey = columnNames.some((column) =>
        CONFIG_KEY_COLUMNS.has(column)
      );
      const hasValueLikeColumn = columnNames.some((column) =>
        VALUE_COLUMN_PATTERN.test(column)
      );
      return hasConfigKey && hasValueLikeColumn;
    })
    .map(({ tableName }) => ({ table_name: tableName }))
    .sort((a, b) => byName(a.table_name, b.table_name));

  const [
    configRows,
    auditRows,
    auditValueRows,
    nonNullCustomFields,
    groupedAuditTokens,
    topConfigs,
  ] = await prisma.$transaction([
    prisma.configuration.count(),
    prisma.configurationAudit.count(),
    prisma.configurationAuditValue.count(),
    prisma.configuration.findMany({
      where: { customField: { not: null } },
      select: { customField: true },
    }),
    prisma.configurationAuditValue.groupBy({
      by: ["auditToken"],
      _count: true,
      orderBy: [{ _count: { auditToken: "desc" } }, { auditToken: "asc" }],
      take: 20,
    }),
    prisma.configuration.findMany({
      take: TOP_CONFIG_LIMIT,
      orderBy: { configRecId: "asc" },
      include: {
        configurationAudits: {
          orderBy: { configurationAuditRecId: "asc" },
          include: {
            configurationValues: {
              orderBy: { configurationAuditValueRecId: "asc" },
            },
          },
        },
      },
    }),
  ]);

  const configCustomFieldNonempty = nonNullCustomFields.reduce((count, row) => {
    return row.customField?.trim() ? count + 1 : count;
  }, 0);

  const rowStats = {
    config_rows: configRows,
    config_custom_field_nonempty: configCustomFieldNonempty,
    audit_rows: auditRows,
    audit_value_rows: auditValueRows,
  };

  const topAuditTokens = groupedAuditTokens.map(({ auditToken, _count }) => ({
    audit_token: auditToken,
    row_count: _count,
  }));

  const output = {
    candidateTables,
    candidateColumns,
    valueTablesWithConfigKey,
    rowStats,
    topAuditTokens,
    topConfigs,
  };

  console.log(JSON.stringify(output, null, 2));
} finally {
  await prisma.$disconnect();
}