seahaven-slack-bot/lambda/wo-po-lookup/index.ts

import { DynamoDBClient, GetItemCommand, QueryCommand, ScanCommand } from '@aws-sdk/client-dynamodb';
import { unmarshall } from '@aws-sdk/util-dynamodb';

const dynamo = new DynamoDBClient({});

// ── Bedrock action group event / response types ───────────────────────────────

interface ActionParameter {
  name: string;
  type: string;
  value: string;
}

interface BedrockActionEvent {
  messageVersion: string;
  agent: { name: string; id: string; alias: string; version: string };
  inputText: string;
  sessionId: string;
  actionGroup: string;
  function: string;
  parameters?: ActionParameter[];
  sessionAttributes: Record<string, string>;
  promptSessionAttributes: Record<string, string>;
}

interface BedrockActionResponse {
  messageVersion: string;
  response: {
    actionGroup: string;
    function: string;
    functionResponse: {
      responseBody: { TEXT: { body: string } };
    };
  };
}

function makeResponse(event: BedrockActionEvent, body: string): BedrockActionResponse {
  return {
    messageVersion: '1.0',
    response: {
      actionGroup: event.actionGroup,
      function: event.function,
      functionResponse: { responseBody: { TEXT: { body } } },
    },
  };
}

// ── Work Order lookup ────────────────────────────────────────────────────────

async function lookupWorkOrder(workOrderId: string): Promise<string> {
  const woRes = await dynamo.send(
    new GetItemCommand({
      TableName: process.env.WORK_ORDERS_TABLE!,
      Key: { work_order_id: { S: workOrderId } },
    }),
  );

  if (!woRes.Item) {
    return `Work order ${workOrderId} was not found.`;
  }

  const wo = unmarshall(woRes.Item) as Record<string, any>;

  const lines: string[] = [];
  lines.push(`WORK ORDER ${wo.work_order_id}`);
  if (wo.description) lines.push(`Description: ${wo.description}`);
  lines.push('');

  // Details section
  const details: string[] = [];
  if (wo.wo_status) details.push(`Status: ${wo.wo_status}`);
  if (wo.customer) details.push(`Customer: ${wo.customer}`);
  if (wo.site_code) details.push(`Site: ${wo.site_code}${wo.building && wo.building !== wo.site_code ? ` (${wo.building})` : ''}`);
  if (wo.address) details.push(`Address: ${wo.address}`);
  if (wo.severity) details.push(`Severity: ${wo.severity}`);
  if (wo.priority) details.push(`Priority: ${wo.priority}`);
  if (wo.assigned_to) details.push(`Assigned To: ${wo.assigned_to}`);
  if (details.length > 0) {
    lines.push('DETAILS');
    lines.push(...details);
    lines.push('');
  }

  // Dates section
  const dates: string[] = [];
  if (wo.date_reported) dates.push(`Reported: ${wo.date_reported}`);
  if (wo.scheduled_start) dates.push(`Scheduled Start: ${wo.scheduled_start}`);
  if (wo.due_date) dates.push(`Due: ${wo.due_date}`);
  if (wo.updated_at) dates.push(`Last Updated: ${wo.updated_at.split('T')[0]}`);
  if (dates.length > 0) {
    lines.push('DATES');
    lines.push(...dates);
    lines.push('');
  }

  // Fetch comments
  const commentsRes = await dynamo.send(
    new QueryCommand({
      TableName: process.env.COMMENTS_TABLE!,
      KeyConditionExpression: 'work_order_id = :woid',
      ExpressionAttributeValues: { ':woid': { S: workOrderId } },
    }),
  );

  const comments = (commentsRes.Items ?? [])
    .map((item) => unmarshall(item) as Record<string, any>)
    .sort((a, b) => (a.created_at ?? '').localeCompare(b.created_at ?? ''));

  if (comments.length > 0) {
    lines.push('COMMENT HISTORY (oldest to newest)');
    for (const c of comments) {
      const date = (c.created_at ?? 'unknown').split('T')[0];
      const author = c.commenter ?? 'unknown';
      lines.push(`[${date}] ${author}: ${c.text ?? '(no text)'}`);
    }
  }

  return lines.join('\n');
}

// ── Purchase Order lookup ────────────────────────────────────────────────────

async function lookupPurchaseOrder(poNumber: string): Promise<string> {
  const poRes = await dynamo.send(
    new GetItemCommand({
      TableName: process.env.PO_TABLE!,
      Key: { po_number: { S: poNumber } },
    }),
  );

  if (!poRes.Item) {
    return `Purchase order ${poNumber} was not found.`;
  }

  const po = unmarshall(poRes.Item) as Record<string, any>;

  const lines: string[] = [];
  lines.push(`PURCHASE ORDER ${po.po_number}`);
  if (po.po_status) lines.push(`Status: ${po.po_status}`);
  if (po.email_type === 'cancellation') lines.push(`Cancelled: ${po.cancelled_at ?? 'yes'}`);
  if (po.total_amount !== undefined) lines.push(`Total: ${po.currency ?? 'USD'} ${po.total_amount}`);
  lines.push('');

  // Details section
  const details: string[] = [];
  if (po.supplier?.name) details.push(`Supplier: ${po.supplier.name}`);
  if (po.source_system) details.push(`Source: ${po.source_system}`);
  if (po.order_date) details.push(`Order Date: ${po.order_date}`);
  if (po.revision_date) details.push(`Revision Date: ${po.revision_date}`);
  if (po.payment_terms) details.push(`Payment Terms: ${po.payment_terms}`);
  if (po.requisition_number) details.push(`Requisition #: ${po.requisition_number}`);
  if (po.department) details.push(`Department: ${po.department}`);
  if (po.submitted_by) details.push(`Submitted By: ${po.submitted_by}`);
  if (po.on_behalf_of) details.push(`On Behalf Of: ${po.on_behalf_of}`);
  if (details.length > 0) {
    lines.push('DETAILS');
    lines.push(...details);
    lines.push('');
  }

  // Ship-to section
  const ship = po.ship_to;
  if (ship) {
    const parts: string[] = [];
    if (ship.name) parts.push(`Name: ${ship.name}`);
    if (ship.address) parts.push(`Address: ${ship.address}`);
    if (ship.location_code) parts.push(`Location Code: ${ship.location_code}`);
    if (ship.attn) parts.push(`Attn: ${ship.attn}`);
    if (parts.length > 0) {
      lines.push('SHIP TO');
      lines.push(...parts);
      lines.push('');
    }
  }

  // Line items section
  const items = po.line_items;
  if (Array.isArray(items) && items.length > 0) {
    lines.push('LINE ITEMS');
    for (let i = 0; i < items.length; i++) {
      const li = items[i];
      const desc = li.description ?? 'No description';
      const amt = li.amount ? ` | ${li.currency ?? 'USD'} ${li.amount}` : '';
      const needBy = li.need_by ? ` | Need by: ${li.need_by}` : '';
      const cat = li.category ? ` | Category: ${li.category}` : '';
      lines.push(`${i + 1}. ${desc}${amt}${needBy}${cat}`);
    }
  }

  return lines.join('\n');
}

// ── Site lookup ─────────────────────────────────────────────────────────────

async function lookupSite(siteCode: string, state?: string): Promise<string> {
  // Exact site code lookup
  if (siteCode) {
    const res = await dynamo.send(
      new GetItemCommand({
        TableName: process.env.SITES_TABLE!,
        Key: { siteCode: { S: siteCode.toUpperCase() } },
      }),
    );

    if (res.Item) {
      const s = unmarshall(res.Item) as Record<string, any>;
      const lines: string[] = [];
      lines.push(`SITE ${s.siteCode}`);
      lines.push(`Address: ${s.address === 'TBD' ? 'TBD (not yet established)' : s.fullAddress || s.address}`);
      if (s.city) lines.push(`City: ${s.city}`);
      if (s.state) lines.push(`State: ${s.state}`);
      if (s.zip) lines.push(`Zip: ${s.zip}`);
      if (s.latitude && s.longitude) lines.push(`Coordinates: ${s.latitude}, ${s.longitude}`);
      if (s.notes) lines.push(`Notes: ${s.notes}`);
      return lines.join('\n');
    }

    return `No site found for code "${siteCode}". Please provide a valid Amazon site code (e.g., "ABE2", "DFW6").`;
  }

  // State-based search via GSI
  if (state) {
    const res = await dynamo.send(
      new QueryCommand({
        TableName: process.env.SITES_TABLE!,
        IndexName: 'by-state',
        KeyConditionExpression: '#st = :state',
        ExpressionAttributeNames: { '#st': 'state' },
        ExpressionAttributeValues: { ':state': { S: state.toUpperCase() } },
      }),
    );

    const sites = (res.Items ?? []).map((item) => unmarshall(item) as Record<string, any>);
    if (sites.length === 0) {
      return `No sites found in state "${state}".`;
    }

    sites.sort((a, b) => (a.siteCode ?? '').localeCompare(b.siteCode ?? ''));
    const lines = [`Sites in ${state.toUpperCase()} (${sites.length} total):`];
    for (const s of sites) {
      const addr = s.address === 'TBD' ? 'TBD' : `${s.address}, ${s.city}`;
      const note = s.notes ? ` — ${s.notes}` : '';
      lines.push(`- ${s.siteCode}: ${addr}${note}`);
    }
    return lines.join('\n');
  }

  return 'Please provide a site code (e.g., "ABE2") or a state abbreviation (e.g., "TX") to search.';
}

// ── Payment lookups ─────────────────────────────────────────────────────────

function formatPayment(p: Record<string, any>): string {
  const lines: string[] = [];
  lines.push(`Check #${p.check_number ?? 'N/A'} — ${p.payee ?? 'Unknown payee'}`);
  if (p.amount_usd !== undefined) lines.push(`Amount: $${p.amount_usd}`);
  if (p.method) lines.push(`Method: ${p.method}`);
  if (p.status) lines.push(`Status: ${p.status}`);
  if (p.send_payment_on) lines.push(`Send Date: ${p.send_payment_on}`);
  if (p.clear_status) lines.push(`Clear Status: ${p.clear_status}`);
  if (p.cleared_date) lines.push(`Cleared Date: ${p.cleared_date}`);
  if (p.invoice_numbers) lines.push(`Invoices: ${p.invoice_numbers}`);
  if (p.company_subsidiary) lines.push(`Subsidiary: ${p.company_subsidiary}`);
  if (p.bank_reference) lines.push(`Bank Ref: ${p.bank_reference}`);
  return lines.join('\n');
}

async function scanPayments(): Promise<Record<string, any>[]> {
  const items: Record<string, any>[] = [];
  let lastKey: Record<string, any> | undefined;

  do {
    const res = await dynamo.send(
      new ScanCommand({
        TableName: process.env.PAYMENTS_TABLE!,
        FilterExpression: 'begins_with(pk, :prefix)',
        ExpressionAttributeValues: { ':prefix': { S: 'payment#' } },
        ExclusiveStartKey: lastKey,
      }),
    );
    for (const item of res.Items ?? []) {
      items.push(unmarshall(item));
    }
    lastKey = res.LastEvaluatedKey;
  } while (lastKey);

  return items;
}

async function lookupPaymentByVendor(vendorName: string): Promise<string> {
  const all = await scanPayments();
  const needle = vendorName.toLowerCase();
  const matches = all.filter((p) => (p.payee ?? '').toLowerCase().includes(needle));

  if (matches.length === 0) {
    return `No payments found for vendor matching "${vendorName}" in the last 90 days.`;
  }

  const lines = [`Payments matching "${vendorName}" (${matches.length} found):\n`];
  for (const p of matches) {
    lines.push(formatPayment(p));
    lines.push('');
  }
  return lines.join('\n');
}

async function lookupPaymentByInvoice(invoiceNumber: string): Promise<string> {
  const all = await scanPayments();
  const needle = invoiceNumber.toLowerCase();
  const matches = all.filter((p) => (p.invoice_numbers ?? '').toLowerCase().includes(needle));

  if (matches.length === 0) {
    return `No payments found for invoice "${invoiceNumber}" in the last 90 days.`;
  }

  const lines = [`Payments for invoice "${invoiceNumber}" (${matches.length} found):\n`];
  for (const p of matches) {
    lines.push(formatPayment(p));
    lines.push('');
  }
  return lines.join('\n');
}

async function lookupPaymentByCheck(checkNumber: string): Promise<string> {
  const res = await dynamo.send(
    new GetItemCommand({
      TableName: process.env.PAYMENTS_TABLE!,
      Key: { pk: { S: `payment#${checkNumber}` } },
    }),
  );

  if (!res.Item) {
    return `No payment found for check number "${checkNumber}".`;
  }

  return formatPayment(unmarshall(res.Item));
}

// ── Handler ──────────────────────────────────────────────────────────────────

export const handler = async (event: BedrockActionEvent): Promise<BedrockActionResponse> => {
  const params = Object.fromEntries(
    (event.parameters ?? []).map((p) => [p.name, p.value]),
  );

  try {
    let result: string;

    if (event.function === 'lookup_work_order') {
      const woId = params.work_order_id?.trim();
      if (!woId) return makeResponse(event, 'Please provide a work order ID.');
      result = await lookupWorkOrder(woId);
    } else if (event.function === 'lookup_purchase_order') {
      const poNum = params.po_number?.trim();
      if (!poNum) return makeResponse(event, 'Please provide a purchase order number.');
      result = await lookupPurchaseOrder(poNum);
    } else if (event.function === 'lookup_site') {
      const code = params.site_code?.trim() ?? '';
      const state = params.state?.trim();
      if (!code && !state) return makeResponse(event, 'Please provide a site code or state abbreviation.');
      result = await lookupSite(code, state);
    } else if (event.function === 'lookup_payment_by_vendor') {
      const name = params.vendor_name?.trim();
      if (!name) return makeResponse(event, 'Please provide a vendor or payee name.');
      result = await lookupPaymentByVendor(name);
    } else if (event.function === 'lookup_payment_by_invoice') {
      const inv = params.invoice_number?.trim();
      if (!inv) return makeResponse(event, 'Please provide an invoice number.');
      result = await lookupPaymentByInvoice(inv);
    } else if (event.function === 'lookup_payment_by_check') {
      const check = params.check_number?.trim();
      if (!check) return makeResponse(event, 'Please provide a check number.');
      result = await lookupPaymentByCheck(check);
    } else {
      result = `Unknown function: ${event.function}`;
    }

    return makeResponse(event, result);
  } catch (err) {
    console.error('Lookup error:', err);
    return makeResponse(event, `Lookup failed: ${(err as Error).message}`);
  }
};