// 2026-11-11 — Companies → "Transfer": hand scheduled audits (e.g. all August Initial audits) to one
// Job Documents user (e.g. Tehreem), so she sees exactly that work in Companies and Create Job.
//
// Table job_document_assignments (row_id = audit_schedule_rows.id, user_id, assigned_by, assigned_at, note),
// created by src/company-documents/migrations/run-002.js. Until that script has been run every helper here
// behaves as "no transfers" — nothing breaks and the Initial-only rule (initial-only.ts) still applies.
//
// Rule for an Initial-only user (see initial-only.ts):
//   • has NO transferred audits  → sees every company with an Initial audit (unchanged behaviour);
//   • has transferred audits     → sees only the companies of the audits transferred to her, and the
//                                  Create Job audit picker shows only those audits.
import { DataSource } from 'typeorm';

export const ASSIGN_TABLE = 'job_document_assignments';

let tableKnown: { at: number; ok: boolean } | null = null;

/** true when the assignments table exists (cached 60 s; re-checked so it starts working right after the migration). */
export async function assignmentsReady(db: DataSource): Promise<boolean> {
  if (tableKnown && Date.now() - tableKnown.at < 60_000) return tableKnown.ok;
  let ok = false;
  try {
    const r: any[] = await db.query(`SHOW TABLES LIKE '${ASSIGN_TABLE}'`);
    ok = r.length > 0;
  } catch {
    ok = false;
  }
  tableKnown = { at: Date.now(), ok };
  return ok;
}
export function forgetAssignmentsTable() {
  tableKnown = null;
}

/** Audit row ids transferred to this user, or null when none (or table missing). */
export async function assignedRowIds(db: DataSource, userId: number): Promise<Set<number> | null> {
  if (!userId || !(await assignmentsReady(db))) return null;
  try {
    const rows: { row_id: number }[] = await db.query(`SELECT row_id FROM ${ASSIGN_TABLE} WHERE user_id = ?`, [userId]);
    return rows.length ? new Set(rows.map((r) => Number(r.row_id))) : null;
  } catch {
    return null;
  }
}

/** SQL: company has at least one non-cancelled audit transferred to user :uid (bind the user id as a parameter). */
export const ASSIGNED_COMPANY_SQL = (companyAlias: string) =>
  `EXISTS (SELECT 1 FROM ${ASSIGN_TABLE} jda JOIN audit_schedule_rows jar ON jar.id = jda.row_id
            WHERE jar.company_id = ${companyAlias}.id AND jar.status <> 'CANCELLED' AND jda.user_id = :jdaUserId)`;