import { getSqlForDurableStore } from "@/lib/db";

export interface Subscription {
  sub_id: string;
  order_id: string;
  cadence: string;
  webhook_url: string | null;
  status: string;
  next_run_at: string | null;
  created_at: string;
}

const mem = new Map<string, Subscription>();

export async function createSubscription(order_id: string, cadence: string, webhook_url: string | null): Promise<Subscription> {
  const sql = getSqlForDurableStore("0pt.subscriptions");
  if (sql) {
    const [row] = await sql<Subscription[]>`
      INSERT INTO subscriptions (order_id, cadence, webhook_url, status)
      VALUES (${order_id}, ${cadence}, ${webhook_url}, 'active')
      RETURNING sub_id, order_id, cadence, webhook_url, status, next_run_at, now() AS created_at`;
    return row;
  }
  const id = globalThis.crypto?.randomUUID?.() ?? `sub-${Math.floor(Math.random() * 1e9)}`;
  const sub: Subscription = { sub_id: id, order_id, cadence, webhook_url, status: "active", next_run_at: null, created_at: new Date().toISOString() };
  mem.set(id, sub);
  return sub;
}

export async function listSubscriptions(): Promise<Subscription[]> {
  const sql = getSqlForDurableStore("0pt.subscriptions");
  if (sql) {
    return sql<Subscription[]>`SELECT sub_id, order_id, cadence, webhook_url, status, next_run_at, created_at FROM subscriptions ORDER BY created_at DESC LIMIT 100`;
  }
  return [...mem.values()].sort((a, b) => b.created_at.localeCompare(a.created_at));
}