import { createDbService } from '@/server/services/db.js';
/**
 * shipment-sweeps.ts — cron sweeps for shipment lifecycle health:
 *   1. sweepStuckShipments   — SLA-breached pending shipments → flag with event
 *   2. detectLostShipments   — in_transit/out_for_delivery with no carrier update in 14d → lost
 */

import type { MultidealEnv } from '@/server/env.js';
import { shipments, shipmentEvents } from '@/server/db/schema.js';
import { eq, and, lt, gt, or, sql } from 'drizzle-orm';
import { captureCaught } from '@/server/observability/capture.server';
import { advanceShipmentStatus } from '@/server/workflows/shipment-lifecycle.js';

/**
 * sweepStuckShipments — find shipments where status='pending' AND
 * handleByAt < NOW() AND handleByAt > NOW() - INTERVAL '7 days'.
 * For each, insert a shipmentEvents row flagging the SLA breach.
 * Does NOT update shipments.flaggedAt (column does not exist in schema).
 * Returns count of affected shipments.
 */
export async function sweepStuckShipments(env: MultidealEnv): Promise<number> {
  const db = createDbService({ DATABASE_URL: env.DATABASE_URL });
  const now = new Date();
  const sevenDaysAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);

  let stuckShipments: { id: string; status: string }[];
  try {
    stuckShipments = await db
      .select({ id: shipments.id, status: shipments.status })
      .from(shipments)
      .where(
        and(
          eq(shipments.status, 'pending'),
          lt(shipments.handleByAt, now),
          gt(shipments.handleByAt, sevenDaysAgo),
        ),
      );
  } catch (e) {
    captureCaught(e, {
      scope: 'shipment.sweeps.stuck.query',
      severity: 'error',
    });
    return 0;
  }

  if (stuckShipments.length === 0) return 0;

  const eventRows = stuckShipments.map((shipment) => ({
    shipmentId: shipment.id,
    // fromStatus == toStatus because we are flagging, not transitioning
    fromStatus: 'pending' as const,
    toStatus: 'pending' as const,
    source: 'cron' as const,
    note: 'handle_sla_breached',
  }));

  try {
    await db.insert(shipmentEvents).values(eventRows);
    return eventRows.length;
  } catch (e) {
    captureCaught(e, {
      scope: 'shipment.sweeps.stuck.insert_event',
      severity: 'warning',
      extra: { shipmentCount: eventRows.length },
    });
    return 0;
  }
}

/**
 * detectLostShipments — find shipments in in_transit or out_for_delivery
 * whose most recent shipmentEvent is older than 14 days.
 * Advances each to 'lost' via advanceShipmentStatus (T17).
 * Falls back to direct DB event insert if T17 isn't available yet.
 * Returns count of shipments marked lost.
 */
export async function detectLostShipments(env: MultidealEnv): Promise<number> {
  const db = createDbService({ DATABASE_URL: env.DATABASE_URL });
  const now = new Date();
  const fourteenDaysAgo = new Date(now.getTime() - 14 * 24 * 60 * 60 * 1000);

  // Find shipments in transit states whose last event is older than 14d.
  // We use a subquery: last event per shipment, filter < fourteenDaysAgo.
  let candidateShipments: { id: string; status: string }[];
  try {
    // Drizzle raw SQL helper for subquery pattern
    candidateShipments = await db
      .select({ id: shipments.id, status: shipments.status })
      .from(shipments)
      .where(
        and(
          or(eq(shipments.status, 'in_transit'), eq(shipments.status, 'out_for_delivery')),
          sql`${shipments.id} IN (
            SELECT se.shipment_id
            FROM shipment_events se
            WHERE se.shipment_id = ${shipments.id}
            GROUP BY se.shipment_id
            HAVING MAX(se.created_at) < ${fourteenDaysAgo}
          )`,
        ),
      );
  } catch (e) {
    captureCaught(e, {
      scope: 'shipment.sweeps.lost.query',
      severity: 'error',
    });
    return 0;
  }

  if (candidateShipments.length === 0) return 0;

  let count = 0;

  for (const shipment of candidateShipments) {
    try {
      await advanceShipmentStatus(env, {
        shipmentId: shipment.id,
        toStatus: 'lost',
        source: 'cron',
        note: 'no_carrier_update_14d',
      });
      count++;
    } catch (e) {
      captureCaught(e, {
        scope: 'shipment.sweeps.lost.advance',
        severity: 'warning',
        extra: { shipmentId: shipment.id },
      });
    }
  }

  return count;
}