/** The two legacy tables that hold audit-assignment data, in each DB. */
export const AUDIT_TABLES = [
  'clients__clientdatas',
  'newsurve__surves',
] as const;

export type AuditTable = (typeof AUDIT_TABLES)[number];

/** The three audit types and the date column each is derived from. */
export const AUDIT_TYPE_DATE: Record<
  'Initial' | 'Surveillance' | 'Recertification',
  string
> = {
  Initial: 'auditdate',
  Surveillance: 'serv_date',
  Recertification: 'recert_date',
};

export type AuditType = keyof typeof AUDIT_TYPE_DATE;

/**
 * Strict-mode-safe "this date column actually has a value" test.
 * Use everywhere instead of comparing against '0000-00-00'.
 */
export const dateSet = (col: string): string =>
  `(${col} IS NOT NULL AND YEAR(${col}) > 0)`;

/** "This report column has an uploaded file path" test. */
export const reportUploaded = (col: string): string =>
  `(${col} IS NOT NULL AND ${col} <> '')`;

/**
 * The INNER JOIN that expands one audit row to each auditor in its
 * JSON `auditassign` array. `alias` is the audit-table alias (default 'c').
 */
export const auditorJoin = (alias = 'c'): string =>
  `INNER JOIN users u
     ON JSON_VALID(${alias}.auditassign)
     AND JSON_CONTAINS(${alias}.auditassign, JSON_QUOTE(CAST(u.id AS CHAR)))`;

/** Standard "auditor full name" expression. */
export const auditorName = (alias = 'u'): string =>
  `TRIM(CONCAT(COALESCE(${alias}.first_name,''),' ',COALESCE(${alias}.last_name,'')))`;

/** Guard so we only look at rows that actually have an auditassign value. */
export const hasAuditAssign = (alias = 'c'): string =>
  `${alias}.auditassign IS NOT NULL AND ${alias}.auditassign <> ''`;