631 lines
27 KiB
TypeScript
631 lines
27 KiB
TypeScript
import { Pool } from 'pg';
|
|
import fs from 'node:fs';
|
|
import path from 'node:path';
|
|
import type { Ticket, TicketStatus } from './types';
|
|
|
|
// Single shared pool. DATABASE_URL points at the `forge` Postgres database; the
|
|
// schema self-bootstraps in initDB() on boot (no ORM, no migrations), mirroring
|
|
// the Husky template's approach.
|
|
export const pool = new Pool({ connectionString: process.env.DATABASE_URL });
|
|
|
|
export async function initDB(): Promise<void> {
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS tickets (
|
|
number TEXT PRIMARY KEY,
|
|
status TEXT NOT NULL,
|
|
state TEXT NOT NULL DEFAULT '',
|
|
short_desc TEXT NOT NULL DEFAULT '',
|
|
assigned_to TEXT,
|
|
assignment_group TEXT,
|
|
brand TEXT,
|
|
market TEXT,
|
|
business_unit TEXT,
|
|
requested_for TEXT,
|
|
opened_at TIMESTAMPTZ,
|
|
due_date DATE,
|
|
state_changed_at TIMESTAMPTZ,
|
|
state_changed_by TEXT,
|
|
last_activity_at TIMESTAMPTZ,
|
|
last_activity_by TEXT,
|
|
last_comment TEXT,
|
|
description TEXT,
|
|
link TEXT,
|
|
final_cost NUMERIC,
|
|
ttfr_minutes INTEGER,
|
|
client_resp_minutes INTEGER,
|
|
fulfillment_date TIMESTAMPTZ,
|
|
first_reply_at TIMESTAMPTZ,
|
|
first_assigned_date TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ,
|
|
jira JSONB,
|
|
activity JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)
|
|
`);
|
|
await pool.query(`CREATE INDEX IF NOT EXISTS tickets_status_idx ON tickets (status)`);
|
|
await pool.query(`CREATE INDEX IF NOT EXISTS tickets_state_idx ON tickets (state)`);
|
|
await pool.query(`CREATE INDEX IF NOT EXISTS tickets_group_idx ON tickets (assignment_group)`);
|
|
|
|
// Analytics columns (additive — never changes an existing column's shape).
|
|
for (const col of [
|
|
'currency_code TEXT', 'opened_by TEXT', 'opened_date DATE', 'closed_date DATE',
|
|
'to_do_at TIMESTAMPTZ', 'in_uat_at TIMESTAMPTZ', 'jira_key TEXT',
|
|
'ticket_year INTEGER', 'size TEXT',
|
|
'po_number TEXT', 'invoiced TEXT',
|
|
]) {
|
|
await pool.query(`ALTER TABLE tickets ADD COLUMN IF NOT EXISTS ${col}`);
|
|
}
|
|
await pool.query(`CREATE INDEX IF NOT EXISTS tickets_year_idx ON tickets (ticket_year)`);
|
|
await pool.query(`CREATE INDEX IF NOT EXISTS tickets_closed_date_idx ON tickets (closed_date)`);
|
|
|
|
// Key/value config (SLA norms, thresholds, FX, size thresholds, palettes).
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS app_config (
|
|
key TEXT PRIMARY KEY,
|
|
value JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)
|
|
`);
|
|
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS api_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
token_id TEXT UNIQUE NOT NULL,
|
|
token_hash TEXT UNIQUE NOT NULL,
|
|
label TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_used_at TIMESTAMPTZ,
|
|
revoked BOOLEAN NOT NULL DEFAULT false,
|
|
expires_at TIMESTAMPTZ
|
|
)
|
|
`);
|
|
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS app_users (
|
|
id SERIAL PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'viewer',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_login_at TIMESTAMPTZ
|
|
)
|
|
`);
|
|
// Existing deployments: add the role column (defaults viewer for prior rows).
|
|
await pool.query(`ALTER TABLE app_users ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'viewer'`);
|
|
|
|
await seedUser();
|
|
await seedConfig();
|
|
await seedFromArchives();
|
|
}
|
|
|
|
// --- Config (SLA norms / thresholds / FX / size / palettes) ----------------
|
|
export interface ForgeConfig {
|
|
insightsThresholds: Record<string, number>;
|
|
fxRates: Record<string, number>;
|
|
sizeThresholds: Record<string, number>;
|
|
norms: Record<string, Record<string, number>>;
|
|
brandColors: Record<string, string>;
|
|
snowColors: Record<string, string>;
|
|
statesOrder: string[];
|
|
displayCurrency: string;
|
|
colleagues: string[];
|
|
latamAssignees: string[];
|
|
jiraColumns: string[];
|
|
}
|
|
|
|
const DEFAULT_CONFIG: ForgeConfig = {
|
|
insightsThresholds: {},
|
|
fxRates: { GBP: 1, EUR: 1.2, MXN: 20 },
|
|
sizeThresholds: { XS: 120, S: 300, M: 600, L: 1200, XL: 3000, XXL: 9000 },
|
|
norms: {},
|
|
brandColors: {},
|
|
snowColors: {},
|
|
statesOrder: [],
|
|
displayCurrency: 'GBP',
|
|
colleagues: [],
|
|
latamAssignees: [],
|
|
jiraColumns: [],
|
|
};
|
|
|
|
let _config: ForgeConfig | null = null;
|
|
export function loadConfig(): ForgeConfig {
|
|
if (_config) return _config;
|
|
let cfg: ForgeConfig;
|
|
try {
|
|
const raw = JSON.parse(fs.readFileSync(path.join(__dirname, 'data', 'config.json'), 'utf-8'));
|
|
cfg = { ...DEFAULT_CONFIG, ...raw };
|
|
} catch {
|
|
cfg = DEFAULT_CONFIG;
|
|
}
|
|
_config = cfg;
|
|
return cfg;
|
|
}
|
|
|
|
async function seedConfig(): Promise<void> {
|
|
const cfg = loadConfig();
|
|
for (const [key, value] of Object.entries(cfg)) {
|
|
await pool.query(
|
|
`INSERT INTO app_config (key, value) VALUES ($1, $2)
|
|
ON CONFLICT (key) DO UPDATE SET value = $2, updated_at = NOW()`,
|
|
[key, JSON.stringify(value)],
|
|
);
|
|
}
|
|
}
|
|
|
|
// Convert a cost to GBP and map to a t-shirt size (largest nominal that fits,
|
|
// the chart path used by the initial app). Zero/absent cost → null ("no cost").
|
|
const SIZE_ORDER = ['XS', 'S', 'M', 'L', 'XL', 'XXL'];
|
|
export function sizeOf(finalCost: number | null, currencyCode: string | null, cfg: ForgeConfig): string | null {
|
|
if (finalCost == null || !(finalCost > 0)) return null;
|
|
const rate = cfg.fxRates[(currencyCode || 'GBP').toUpperCase()] ?? 1; // units per 1 GBP
|
|
const gbp = finalCost / rate;
|
|
const th = cfg.sizeThresholds;
|
|
let best = 'XS'; // largest nominal ≤ gbp, capped at XXL; sub-XS cost still reads XS
|
|
for (const s of SIZE_ORDER) if (gbp >= (th[s] ?? Infinity)) best = s;
|
|
return best;
|
|
}
|
|
|
|
// Seed the single login account from AUTH_USER/AUTH_PASS on first boot. If the
|
|
// user already exists its password is left untouched (change it in the DB, or
|
|
// delete the row and reboot to re-seed). No-op when the env vars are unset.
|
|
async function seedUser(): Promise<void> {
|
|
const username = process.env.AUTH_USER;
|
|
const password = process.env.AUTH_PASS;
|
|
if (!username || !password) {
|
|
console.warn('AUTH_USER/AUTH_PASS unset — no login account seeded (read API will reject all logins)');
|
|
return;
|
|
}
|
|
const { rows } = await pool.query('SELECT 1 FROM app_users WHERE username = $1', [username]);
|
|
if (rows.length > 0) {
|
|
// Bootstrap account is always admin (fixes the default 'viewer' for pre-role rows).
|
|
await pool.query(`UPDATE app_users SET role='admin' WHERE username=$1 AND role<>'admin'`, [username]);
|
|
return;
|
|
}
|
|
const bcrypt = await import('bcryptjs');
|
|
const hash = await bcrypt.hash(password, 12);
|
|
await pool.query('INSERT INTO app_users (username, password_hash, role) VALUES ($1, $2, $3)', [username, hash, 'admin']);
|
|
console.log(`Seeded admin account: ${username}`);
|
|
}
|
|
|
|
// --- User management (Project Leadership / Admin) --------------------------
|
|
export type Role = 'viewer' | 'pm' | 'lead' | 'admin';
|
|
export const ROLES: Role[] = ['viewer', 'pm', 'lead', 'admin'];
|
|
export function isRole(v: unknown): v is Role {
|
|
return typeof v === 'string' && (ROLES as string[]).includes(v);
|
|
}
|
|
|
|
export interface AppUser { id: number; username: string; role: Role; createdAt: string | null; lastLoginAt: string | null; }
|
|
|
|
export async function listUsers(): Promise<AppUser[]> {
|
|
const { rows } = await pool.query(
|
|
`SELECT id, username, role, created_at, last_login_at FROM app_users ORDER BY username`,
|
|
);
|
|
return rows.map(r => ({
|
|
id: r.id, username: r.username, role: r.role,
|
|
createdAt: r.created_at instanceof Date ? r.created_at.toISOString() : r.created_at,
|
|
lastLoginAt: r.last_login_at instanceof Date ? r.last_login_at.toISOString() : r.last_login_at,
|
|
}));
|
|
}
|
|
|
|
// Create a user. Returns 'exists' if the username is taken, else the new user.
|
|
export async function createUser(username: string, password: string, role: Role): Promise<AppUser | 'exists'> {
|
|
const bcrypt = await import('bcryptjs');
|
|
const hash = await bcrypt.hash(password, 12);
|
|
try {
|
|
const { rows } = await pool.query(
|
|
`INSERT INTO app_users (username, password_hash, role) VALUES ($1, $2, $3)
|
|
RETURNING id, username, role, created_at, last_login_at`,
|
|
[username, hash, role],
|
|
);
|
|
const r = rows[0];
|
|
return { id: r.id, username: r.username, role: r.role,
|
|
createdAt: r.created_at instanceof Date ? r.created_at.toISOString() : r.created_at, lastLoginAt: null };
|
|
} catch (err) {
|
|
if ((err as { code?: string }).code === '23505') return 'exists'; // unique violation
|
|
throw err;
|
|
}
|
|
}
|
|
|
|
export async function countAdmins(): Promise<number> {
|
|
const { rows } = await pool.query<{ n: string }>(`SELECT count(*)::text AS n FROM app_users WHERE role = 'admin'`);
|
|
return Number(rows[0]?.n ?? '0');
|
|
}
|
|
|
|
export async function getUserRole(username: string): Promise<Role | null> {
|
|
const { rows } = await pool.query<{ role: Role }>(`SELECT role FROM app_users WHERE username = $1`, [username]);
|
|
return rows[0]?.role ?? null;
|
|
}
|
|
|
|
// Delete a user AND purge their active sessions (connect-pg-simple stores the
|
|
// session as JSON in user_sessions.sess), so a removed user is logged out at once
|
|
// instead of keeping their cached role for the cookie's lifetime.
|
|
export async function deleteUser(username: string): Promise<void> {
|
|
await pool.query(`DELETE FROM app_users WHERE username = $1`, [username]);
|
|
await pool.query(`DELETE FROM user_sessions WHERE (sess -> 'user' ->> 'username') = $1`, [username])
|
|
.catch(err => console.error('session purge failed:', (err as Error).message));
|
|
}
|
|
|
|
// --- Empty helpers ---------------------------------------------------------
|
|
function ts(v: unknown): string | null {
|
|
if (v == null) return null;
|
|
const s = String(v).trim();
|
|
return s === '' ? null : s;
|
|
}
|
|
function num(v: unknown): number | null {
|
|
if (v == null || v === '') return null;
|
|
const n = Number(v);
|
|
return Number.isFinite(n) ? n : null;
|
|
}
|
|
|
|
// Derive the calendar year from the best available opened date.
|
|
function yearOf(...vals: (string | null)[]): number | null {
|
|
for (const v of vals) {
|
|
if (v) { const y = Number(String(v).slice(0, 4)); if (y > 2000 && y < 3000) return y; }
|
|
}
|
|
return null;
|
|
}
|
|
|
|
// Reshape a raw active-archive record into a normalized Ticket.
|
|
function fromActive(r: Record<string, unknown>, cfg: ForgeConfig): Ticket {
|
|
const meta = (r.meta ?? {}) as Record<string, unknown>;
|
|
const finalCost = num(meta.finalCost);
|
|
const currencyCode = ts(meta.currencyCode);
|
|
return {
|
|
number: String(r.number),
|
|
status: 'active',
|
|
state: String(r.state ?? ''),
|
|
shortDesc: String(r.shortDesc ?? ''),
|
|
assignedTo: ts(r.assignedTo),
|
|
assignmentGroup: ts(r.assignmentGroup),
|
|
brand: ts(meta.brand),
|
|
market: ts(meta.market),
|
|
businessUnit: ts(meta.businessUnit),
|
|
requestedFor: ts(meta.requestedFor),
|
|
openedBy: null,
|
|
openedAt: ts(meta.openedAt),
|
|
openedDate: ts(meta.openedAt),
|
|
closedDate: null,
|
|
toDoAt: null,
|
|
inUatAt: null,
|
|
jiraKey: null,
|
|
currencyCode,
|
|
ticketYear: yearOf(ts(meta.openedAt)),
|
|
size: sizeOf(finalCost, currencyCode, cfg),
|
|
poNumber: null,
|
|
invoiced: null,
|
|
dueDate: ts(meta.dueDate),
|
|
stateChangedAt: ts(meta.stateChangedAt),
|
|
stateChangedBy: ts(meta.stateChangedBy),
|
|
lastActivityAt: ts(meta.lastActivityAt),
|
|
lastActivityBy: ts(meta.lastActivityBy),
|
|
lastComment: ts(r.lastComment),
|
|
description: ts(meta.description),
|
|
link: ts(r.link),
|
|
finalCost,
|
|
ttfrMinutes: null,
|
|
clientRespMinutes: null,
|
|
fulfillmentDate: null,
|
|
firstReplyAt: null,
|
|
firstAssignedDate: null,
|
|
updatedAt: ts(r._updated),
|
|
jira: null,
|
|
activity: Array.isArray(meta.activity) ? (meta.activity as Ticket['activity']) : [],
|
|
};
|
|
}
|
|
|
|
function fromClosed(number: string, r: Record<string, unknown>, cfg: ForgeConfig): Ticket {
|
|
const finalCost = num(r.finalCost);
|
|
const currencyCode = ts(r.currencyCode);
|
|
return {
|
|
number,
|
|
status: 'closed',
|
|
state: String(r.state ?? ''),
|
|
shortDesc: String(r.shortDesc ?? ''),
|
|
assignedTo: ts(r.assignedTo),
|
|
assignmentGroup: ts(r.assignmentGroup),
|
|
brand: ts(r.brand),
|
|
market: ts(r.market),
|
|
businessUnit: ts(r.businessUnit),
|
|
requestedFor: ts(r.requestedFor),
|
|
openedBy: ts(r.openedBy),
|
|
openedAt: ts(r.openedAt),
|
|
openedDate: ts(r.openedDate) ?? ts(r.openedAt),
|
|
closedDate: ts(r.closedDate),
|
|
toDoAt: ts(r.toDoAt),
|
|
inUatAt: ts(r.inUatAt),
|
|
jiraKey: ts(r.jiraKey),
|
|
currencyCode,
|
|
ticketYear: (num(r.year) ?? yearOf(ts(r.closedDate), ts(r.openedDate), ts(r.openedAt))),
|
|
size: sizeOf(finalCost, currencyCode, cfg),
|
|
poNumber: null,
|
|
invoiced: null,
|
|
dueDate: null,
|
|
stateChangedAt: ts(r.stateChangedAt),
|
|
stateChangedBy: ts(r.stateChangedBy),
|
|
lastActivityAt: ts(r.lastActivityAt),
|
|
lastActivityBy: ts(r.lastActivityBy),
|
|
lastComment: null,
|
|
description: null,
|
|
link: null,
|
|
finalCost,
|
|
ttfrMinutes: num(r.ttfrMinutes),
|
|
clientRespMinutes: num(r.clientRespMinutes),
|
|
fulfillmentDate: ts(r.fulfillmentDate),
|
|
firstReplyAt: ts(r.firstReplyAt),
|
|
firstAssignedDate: ts(r.firstAssignedDate),
|
|
updatedAt: ts(r._updated),
|
|
jira: null,
|
|
activity: [],
|
|
};
|
|
}
|
|
|
|
// Build the normalized ticket list from the bundled JSON archives. Jira info
|
|
// (keyed by ticket number) is attached to BOTH active and closed tickets.
|
|
//
|
|
// Order matters: closed first, then active. A ticket number can appear in both
|
|
// archives (a still-open request whose analytics row was also cached); upsert
|
|
// applies later entries last, so putting active last makes the live active
|
|
// board win over the stale closed snapshot.
|
|
// Finance rows keyed by ticket number: cost / currency / PO / invoiced.
|
|
interface FinanceRow { cost?: unknown; currency?: unknown; po?: unknown; invoiced?: unknown; }
|
|
function loadFinance(dir: string): Record<string, FinanceRow> {
|
|
try {
|
|
return (JSON.parse(fs.readFileSync(path.join(dir, 'finance.json'), 'utf-8')).rows ?? {}) as Record<string, FinanceRow>;
|
|
} catch { return {}; }
|
|
}
|
|
|
|
// Enrich a ticket from its finance row: fill missing cost/currency (recomputing
|
|
// size), and set the PO number ('' = in finance with no PO yet) + invoiced flag.
|
|
function applyFinance(t: Ticket, fin: FinanceRow | undefined, cfg: ForgeConfig): void {
|
|
if (!fin) return;
|
|
t.poNumber = fin.po == null ? null : String(fin.po);
|
|
t.invoiced = ts(fin.invoiced);
|
|
if (t.finalCost == null) {
|
|
const c = num(fin.cost);
|
|
if (c != null) {
|
|
t.finalCost = c;
|
|
if (!t.currencyCode) t.currencyCode = ts(fin.currency);
|
|
t.size = sizeOf(t.finalCost, t.currencyCode, cfg);
|
|
}
|
|
}
|
|
}
|
|
|
|
function loadArchiveTickets(): Ticket[] {
|
|
const dir = path.join(__dirname, 'data');
|
|
const cfg = loadConfig();
|
|
const finance = loadFinance(dir);
|
|
const activeTickets: Ticket[] = [];
|
|
const closedTickets: Ticket[] = [];
|
|
let jira: Record<string, unknown> = {};
|
|
|
|
try {
|
|
const active = JSON.parse(fs.readFileSync(path.join(dir, 'active_archive.json'), 'utf-8'));
|
|
jira = (active.jira ?? {}) as Record<string, unknown>;
|
|
for (const raw of active.tickets ?? []) {
|
|
const t = fromActive(raw as Record<string, unknown>, cfg);
|
|
const j = (jira[t.number] as { key?: string | null }) ?? null;
|
|
t.jira = j;
|
|
if (j?.key) t.jiraKey = j.key;
|
|
applyFinance(t, finance[t.number], cfg);
|
|
activeTickets.push(t);
|
|
}
|
|
} catch (err) {
|
|
console.warn('seed: active archive skipped —', (err as Error).message);
|
|
}
|
|
|
|
try {
|
|
const closed = JSON.parse(fs.readFileSync(path.join(dir, 'closed_archive.json'), 'utf-8'));
|
|
const map = (closed.tickets ?? {}) as Record<string, Record<string, unknown>>;
|
|
for (const [number, raw] of Object.entries(map)) {
|
|
const t = fromClosed(number, raw, cfg);
|
|
const j = (jira[number] as { key?: string | null }) ?? null;
|
|
t.jira = j;
|
|
if (!t.jiraKey && j?.key) t.jiraKey = j.key;
|
|
applyFinance(t, finance[number], cfg);
|
|
closedTickets.push(t);
|
|
}
|
|
} catch (err) {
|
|
console.warn('seed: closed archive skipped —', (err as Error).message);
|
|
}
|
|
|
|
return [...closedTickets, ...activeTickets];
|
|
}
|
|
|
|
// One-time seed: if the tickets table is empty, load the bundled JSON archives.
|
|
// Idempotent via upsert, so a redeploy never duplicates. Skipped silently once
|
|
// the table has rows (real syncs from the extension take over).
|
|
async function seedFromArchives(): Promise<void> {
|
|
const { rows } = await pool.query<{ n: string }>(`SELECT count(*)::text AS n FROM tickets`);
|
|
if (Number(rows[0]?.n ?? '0') > 0) return;
|
|
|
|
const tickets = loadArchiveTickets();
|
|
if (tickets.length === 0) return;
|
|
await upsertTickets(tickets);
|
|
console.log(`Seeded ${tickets.length} tickets from bundled archives`);
|
|
}
|
|
|
|
// Replace all tickets with a fresh load from the archives. Used by the reseed
|
|
// CLI (server/reseed.ts) to reload after regenerating the archives from a dump.
|
|
export async function reseedFromArchives(): Promise<number> {
|
|
const tickets = loadArchiveTickets();
|
|
await pool.query('TRUNCATE tickets');
|
|
await upsertTickets(tickets);
|
|
return tickets.length;
|
|
}
|
|
|
|
// Attach-only Jira enrichment: update ONLY the `jira` JSONB (merged) and `jira_key`
|
|
// on tickets that already exist, keyed by RITM number. Never touches
|
|
// status/state/assignee/activity (see ADR — a Jira payload must not ride the
|
|
// clobbering ticket upsert). Returns how many existing tickets matched.
|
|
export interface JiraAttach { number: string; jira: Record<string, unknown>; jiraKey?: string | null; }
|
|
export async function attachJira(items: JiraAttach[]): Promise<number> {
|
|
if (items.length === 0) return 0;
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
let matched = 0;
|
|
for (const it of items) {
|
|
const { rowCount } = await client.query(
|
|
`UPDATE tickets
|
|
SET jira = COALESCE(jira, '{}'::jsonb) || $2::jsonb,
|
|
jira_key = COALESCE($3, jira_key),
|
|
synced_at = NOW()
|
|
WHERE number = $1`,
|
|
[it.number, JSON.stringify(it.jira ?? {}), it.jiraKey ?? null],
|
|
);
|
|
matched += rowCount ?? 0;
|
|
}
|
|
await client.query('COMMIT');
|
|
return matched;
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
// Upsert a batch of tickets by number. Used by both the seed and the sync
|
|
// ingest endpoint. Runs in a single transaction.
|
|
export async function upsertTickets(tickets: Ticket[]): Promise<number> {
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
for (const t of tickets) {
|
|
await client.query(
|
|
`INSERT INTO tickets (
|
|
number, status, state, short_desc, assigned_to, assignment_group,
|
|
brand, market, business_unit, requested_for, opened_at, due_date,
|
|
state_changed_at, state_changed_by, last_activity_at, last_activity_by,
|
|
last_comment, description, link, final_cost, ttfr_minutes,
|
|
client_resp_minutes, fulfillment_date, first_reply_at, first_assigned_date,
|
|
updated_at, jira, activity,
|
|
currency_code, opened_by, opened_date, closed_date, to_do_at, in_uat_at,
|
|
jira_key, ticket_year, size, po_number, invoiced, synced_at
|
|
) VALUES (
|
|
$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,
|
|
$21,$22,$23,$24,$25,$26,$27,$28,
|
|
$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39, NOW()
|
|
)
|
|
-- Non-destructive: the sync collector sends a thin payload (state, assignee,
|
|
-- group, timestamps). For the rich fields it omits (brand/market/description/
|
|
-- lastComment/jira/analytics), COALESCE keeps the existing value when the
|
|
-- incoming one is NULL, so a real sync never nulls seeded richness. Activity
|
|
-- is kept unless the incoming payload actually carries entries.
|
|
ON CONFLICT (number) DO UPDATE SET
|
|
status=EXCLUDED.status, state=EXCLUDED.state, short_desc=EXCLUDED.short_desc,
|
|
assigned_to=EXCLUDED.assigned_to, assignment_group=EXCLUDED.assignment_group,
|
|
opened_at=COALESCE(EXCLUDED.opened_at, tickets.opened_at),
|
|
due_date=COALESCE(EXCLUDED.due_date, tickets.due_date),
|
|
state_changed_at=COALESCE(EXCLUDED.state_changed_at, tickets.state_changed_at),
|
|
state_changed_by=COALESCE(EXCLUDED.state_changed_by, tickets.state_changed_by),
|
|
last_activity_at=EXCLUDED.last_activity_at, last_activity_by=EXCLUDED.last_activity_by,
|
|
last_comment=COALESCE(EXCLUDED.last_comment, tickets.last_comment),
|
|
description=COALESCE(EXCLUDED.description, tickets.description),
|
|
link=COALESCE(EXCLUDED.link, tickets.link),
|
|
brand=COALESCE(EXCLUDED.brand, tickets.brand),
|
|
market=COALESCE(EXCLUDED.market, tickets.market),
|
|
business_unit=COALESCE(EXCLUDED.business_unit, tickets.business_unit),
|
|
requested_for=COALESCE(EXCLUDED.requested_for, tickets.requested_for),
|
|
final_cost=COALESCE(EXCLUDED.final_cost, tickets.final_cost),
|
|
ttfr_minutes=COALESCE(EXCLUDED.ttfr_minutes, tickets.ttfr_minutes),
|
|
client_resp_minutes=COALESCE(EXCLUDED.client_resp_minutes, tickets.client_resp_minutes),
|
|
fulfillment_date=COALESCE(EXCLUDED.fulfillment_date, tickets.fulfillment_date),
|
|
first_reply_at=COALESCE(EXCLUDED.first_reply_at, tickets.first_reply_at),
|
|
first_assigned_date=COALESCE(EXCLUDED.first_assigned_date, tickets.first_assigned_date),
|
|
updated_at=EXCLUDED.updated_at,
|
|
jira=COALESCE(EXCLUDED.jira, tickets.jira),
|
|
activity=CASE WHEN jsonb_array_length(EXCLUDED.activity) > 0
|
|
THEN EXCLUDED.activity ELSE tickets.activity END,
|
|
currency_code=COALESCE(EXCLUDED.currency_code, tickets.currency_code),
|
|
opened_by=COALESCE(EXCLUDED.opened_by, tickets.opened_by),
|
|
opened_date=COALESCE(EXCLUDED.opened_date, tickets.opened_date),
|
|
closed_date=COALESCE(EXCLUDED.closed_date, tickets.closed_date),
|
|
to_do_at=COALESCE(EXCLUDED.to_do_at, tickets.to_do_at),
|
|
in_uat_at=COALESCE(EXCLUDED.in_uat_at, tickets.in_uat_at),
|
|
jira_key=COALESCE(EXCLUDED.jira_key, tickets.jira_key),
|
|
ticket_year=COALESCE(EXCLUDED.ticket_year, tickets.ticket_year),
|
|
size=COALESCE(EXCLUDED.size, tickets.size),
|
|
po_number=COALESCE(EXCLUDED.po_number, tickets.po_number),
|
|
invoiced=COALESCE(EXCLUDED.invoiced, tickets.invoiced),
|
|
synced_at=NOW()`,
|
|
[
|
|
t.number, t.status, t.state, t.shortDesc, t.assignedTo, t.assignmentGroup,
|
|
t.brand, t.market, t.businessUnit, t.requestedFor, t.openedAt, t.dueDate,
|
|
t.stateChangedAt, t.stateChangedBy, t.lastActivityAt, t.lastActivityBy,
|
|
t.lastComment, t.description, t.link, t.finalCost, t.ttfrMinutes,
|
|
t.clientRespMinutes, t.fulfillmentDate, t.firstReplyAt, t.firstAssignedDate,
|
|
t.updatedAt, t.jira ? JSON.stringify(t.jira) : null, JSON.stringify(t.activity ?? []),
|
|
t.currencyCode, t.openedBy, t.openedDate, t.closedDate, t.toDoAt, t.inUatAt,
|
|
t.jiraKey, t.ticketYear, t.size, t.poNumber, t.invoiced,
|
|
],
|
|
);
|
|
}
|
|
await client.query('COMMIT');
|
|
return tickets.length;
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
// Map a DB row back to the wire Ticket shape.
|
|
export function rowToTicket(r: Record<string, unknown>): Ticket {
|
|
const iso = (v: unknown) => (v instanceof Date ? v.toISOString() : (v as string | null));
|
|
// DATE columns come back as a JS Date at LOCAL midnight; toISOString() would
|
|
// shift the calendar day under a non-UTC offset. Emit YYYY-MM-DD from local parts.
|
|
const dateOnly = (v: unknown): string | null => {
|
|
if (v == null) return null;
|
|
if (v instanceof Date) {
|
|
const y = v.getFullYear();
|
|
const m = String(v.getMonth() + 1).padStart(2, '0');
|
|
const d = String(v.getDate()).padStart(2, '0');
|
|
return `${y}-${m}-${d}`;
|
|
}
|
|
return String(v);
|
|
};
|
|
return {
|
|
number: r.number as string,
|
|
status: r.status as TicketStatus,
|
|
state: (r.state as string) ?? '',
|
|
shortDesc: (r.short_desc as string) ?? '',
|
|
assignedTo: (r.assigned_to as string) ?? null,
|
|
assignmentGroup: (r.assignment_group as string) ?? null,
|
|
brand: (r.brand as string) ?? null,
|
|
market: (r.market as string) ?? null,
|
|
businessUnit: (r.business_unit as string) ?? null,
|
|
requestedFor: (r.requested_for as string) ?? null,
|
|
openedBy: (r.opened_by as string) ?? null,
|
|
openedAt: iso(r.opened_at),
|
|
openedDate: dateOnly(r.opened_date),
|
|
closedDate: dateOnly(r.closed_date),
|
|
toDoAt: iso(r.to_do_at),
|
|
inUatAt: iso(r.in_uat_at),
|
|
jiraKey: (r.jira_key as string) ?? null,
|
|
currencyCode: (r.currency_code as string) ?? null,
|
|
ticketYear: r.ticket_year != null ? Number(r.ticket_year) : null,
|
|
size: (r.size as string) ?? null,
|
|
poNumber: (r.po_number as string) ?? null,
|
|
invoiced: (r.invoiced as string) ?? null,
|
|
dueDate: dateOnly(r.due_date),
|
|
stateChangedAt: iso(r.state_changed_at),
|
|
stateChangedBy: (r.state_changed_by as string) ?? null,
|
|
lastActivityAt: iso(r.last_activity_at),
|
|
lastActivityBy: (r.last_activity_by as string) ?? null,
|
|
lastComment: (r.last_comment as string) ?? null,
|
|
description: (r.description as string) ?? null,
|
|
link: (r.link as string) ?? null,
|
|
finalCost: r.final_cost != null ? Number(r.final_cost) : null,
|
|
ttfrMinutes: r.ttfr_minutes != null ? Number(r.ttfr_minutes) : null,
|
|
clientRespMinutes: r.client_resp_minutes != null ? Number(r.client_resp_minutes) : null,
|
|
fulfillmentDate: iso(r.fulfillment_date),
|
|
firstReplyAt: iso(r.first_reply_at),
|
|
firstAssignedDate: iso(r.first_assigned_date),
|
|
updatedAt: iso(r.updated_at),
|
|
jira: (r.jira as Ticket['jira']) ?? null,
|
|
activity: (r.activity as Ticket['activity']) ?? [],
|
|
};
|
|
}
|