panels-origin/api/db.ts

import postgres, { createPool, PgDb, withTenant } from "./pg.ts";
import { config } from "./config.ts";
import { callCoreFn, RpcCallError } from "./rpc.ts";
import { evaluateDocumentValidity, freshnessRequired, type ImssStatus, type WorkerImssContext } from "./document_validity.ts";

export type Db = PgDb;

let corePool: postgres.Sql | null = null;
let coreOwnerPool: postgres.Sql | null = null;

function getCorePool(): postgres.Sql {
  if (!corePool) corePool = createPool(config.databaseUrlCore, { max: 20 });
  return corePool;
}

function getCoreOwnerPool(): postgres.Sql {
  if (!coreOwnerPool) {
    coreOwnerPool = createPool(config.databaseUrlCoreOwner || config.databaseUrlCore, { max: 3 });
  }
  return coreOwnerPool;
}

export async function pingCoreDb(): Promise<void> {
  await getCorePool()`SELECT 1`;
}

export async function withCoreTenant<T>(
  tenantId: number | null,
  fn: (db: Db) => Promise<T>,
): Promise<T> {
  return await withTenant(getCorePool(), tenantId, fn);
}

export async function getCoreDb(): Promise<Db> {
  return new PgDb(getCoreOwnerPool());
}

export async function closeCoreDb(): Promise<void> {
  await corePool?.end({ timeout: 5 });
  await coreOwnerPool?.end({ timeout: 5 });
  corePool = null;
  coreOwnerPool = null;
}

export async function lastInsertId(database: Db): Promise<number> {
  return await database.lastInsertId();
}

export const PROJECT_STATUSES = ["activo", "pausado", "concluido", "cancelado"] as const;
export type ProjectStatus = (typeof PROJECT_STATUSES)[number];

export const PROJECT_STATUS_CATALOG = [
  { code: "activo", label: "Activo" },
  { code: "pausado", label: "Pausado" },
  { code: "concluido", label: "Concluido" },
  { code: "cancelado", label: "Cancelado" },
] as const;

export function isProjectStatus(value: string): value is ProjectStatus {
  return (PROJECT_STATUSES as readonly string[]).includes(value);
}

export async function nextProjectCode(database: Db): Promise<string> {
  const env = await callCoreFn<{ code: string }>(database, "core.fn_next_project_code", {});
  if (!env.ok) throw new RpcCallError(env);
  return String(env.data?.code ?? "PRY-0001");
}

export async function projectById(database: Db, id: number) {
  const env = await callCoreFn<{ project: Record<string, unknown> }>(database, "core.fn_project_get", { id });
  if (!env.ok) return undefined;
  return env.data?.project as { id: number; code: string; name: string; status: string } | undefined;
}

export function projectMustBe(
  project: { status: string } | undefined,
  allowed: readonly ProjectStatus[],
  closedMessage: string,
): { error: string; status: 400 | 404 } | null {
  if (!project) return { error: "Proyecto no encontrado", status: 404 };
  if (!allowed.includes(project.status as ProjectStatus)) {
    return { error: closedMessage, status: 400 };
  }
  return null;
}

export async function workerImssContext(database: Db, workerId: number): Promise<WorkerImssContext> {
  const env = await callCoreFn<{ imss_context: WorkerImssContext }>(
    database, "core.fn_worker_checklist_raw", { worker_id: workerId },
  );
  if (env.ok && env.data?.imss_context) return env.data.imss_context;
  return { imss_status: "sin_alta" as ImssStatus, last_rehire_at: null, current_alta_at: null };
}

export async function checklistItemsFor(database: Db, workerId: number, tenantId: number | null = null) {
  const tz = await tenantTimezone(database, tenantId);
  const today = todayInTimezone(tz);
  const env = await callCoreFn<{
    items?: Array<Record<string, unknown>>;
    ctx?: WorkerImssContext;
    imss_context?: WorkerImssContext;
    freshness_required?: boolean;
    document_types?: Array<Record<string, unknown>>;
    current_documents?: Array<Record<string, unknown>>;
  }>(database, "core.fn_worker_checklist_raw", { worker_id: workerId, tenant_id: tenantId });
  if (env.ok && env.data?.items) {
    return {
      ctx: (env.data.ctx ?? env.data.imss_context) as WorkerImssContext,
      freshness_required: Boolean(env.data.freshness_required),
      items: env.data.items,
    };
  }
  const types = env.data?.document_types ?? [];
  const currentDocs = env.data?.current_documents ?? [];
  const ctx = (env.data?.imss_context ?? await workerImssContext(database, workerId)) as WorkerImssContext;
  const byType = new Map(currentDocs.map((d) => [String(d.type_code), d]));
  const items = types.map((t) => {
    const policy = {
      code: String(t.code),
      label: String(t.label),
      required: !!t.required,
      validity_mode: (String(t.validity_mode || "none")) as "none" | "freshness" | "expiry",
      freshness_days: t.freshness_days as number | null,
      requires_issued_at: !!t.requires_issued_at,
      requires_expires_at: !!t.requires_expires_at,
    };
    const evaled = evaluateDocumentValidity(policy, byType.get(policy.code) as never, ctx, today);
    return {
      code: policy.code,
      label: policy.label,
      required: policy.required,
      validity_mode: policy.validity_mode,
      freshness_days: t.freshness_days,
      requires_issued_at: policy.requires_issued_at,
      requires_expires_at: policy.requires_expires_at,
      category: String(t.category || "otro"),
      present: evaled.present,
      valid: evaled.valid,
      validity_status: evaled.validity_status,
      issued_at: evaled.issued_at,
      expires_at: evaled.expires_at,
    };
  });
  return { ctx, freshness_required: freshnessRequired(ctx), items };
}

export async function imssFlagsFor(database: Db, workerId: number, tenantId: number | null = null) {
  const { ctx, freshness_required: needFresh, items } = await checklistItemsFor(database, workerId, tenantId);
  const requiredOk = items.filter((i) => i.required).every((i) => i.present && i.valid);
  const env = await callCoreFn<{ workers: Array<Record<string, unknown>> }>(
    database, "core.fn_worker_list", { tenant_id: tenantId },
  );
  const row = env.ok ? (env.data?.workers ?? []).find((w) => Number(w.id) === workerId) : undefined;
  if (row) {
    return {
      imss_status: String(row.imss_status ?? ctx.imss_status),
      freshness_required: Boolean(row.freshness_required ?? needFresh),
      imss_ready: Boolean(row.imss_ready),
      in_project_without_imss: Boolean(row.in_project_without_imss),
      expediente_ok: Boolean(row.expediente_ok),
    };
  }
  const hasImss = ctx.imss_status === "alta";
  return {
    imss_status: ctx.imss_status,
    freshness_required: needFresh,
    imss_ready: requiredOk && !hasImss,
    in_project_without_imss: false,
    expediente_ok: requiredOk,
  };
}

export async function checklistFor(database: Db, workerId: number, tenantId: number | null = null) {
  return (await checklistItemsFor(database, workerId, tenantId)).items;
}

export async function refreshPipeline(database: Db, workerId: number, tenantId: number | null = null): Promise<void> {
  const env = await callCoreFn(database, "core.fn_worker_refresh_pipeline", {
    worker_id: workerId, tenant_id: tenantId,
  });
  if (!env.ok) throw new RpcCallError(env);
}

export async function projectChecklistFor(database: Db, projectId: number) {
  const env = await callCoreFn<{ checklist: unknown[] }>(
    database, "core.fn_project_checklist", { project_id: projectId },
  );
  if (!env.ok) throw new RpcCallError(env);
  return env.data?.checklist ?? [];
}

export async function companyChecklistFor(database: Db, companyId: number) {
  const env = await callCoreFn<{ checklist: unknown[] }>(
    database, "core.fn_company_checklist", { company_id: companyId },
  );
  if (!env.ok) throw new RpcCallError(env);
  return env.data?.checklist ?? [];
}

export async function tenantTimezone(database: Db, tenantId: number | null): Promise<string> {
  if (tenantId == null) return "America/Mexico_City";
  const env = await callCoreFn<{ timezone: string }>(
    database, "core.fn_tenant_timezone_get", { tenant_id: tenantId },
  );
  if (!env.ok) return "America/Mexico_City";
  return String(env.data?.timezone ?? "America/Mexico_City");
}

export function todayInTimezone(timezone: string, now = new Date()): string {
  return now.toLocaleDateString("en-CA", { timeZone: timezone });
}