/**
 * Deal-drafts query layer.
 *
 * All DB access for the deal_drafts table goes through this file.
 * No business logic — pure data access following query-layer-exclusivity rule.
 */

import { and, desc, eq, lt, sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { dealDrafts } from '../schema.js';

export type { DealDraft, NewDealDraft } from '../schema.js';

export async function insertDraft(
  db: DrizzleClient,
  row: typeof dealDrafts.$inferInsert,
): Promise<typeof dealDrafts.$inferSelect> {
  const [r] = await db.insert(dealDrafts).values(row).returning();
  return r!;
}

export async function getDraftById(
  db: DrizzleClient,
  id: string,
): Promise<typeof dealDrafts.$inferSelect | null> {
  const [r] = await db.select().from(dealDrafts).where(eq(dealDrafts.id, id)).limit(1);
  return r ?? null;
}

export async function listDraftsByVendor(
  db: DrizzleClient,
  vendorId: string,
): Promise<(typeof dealDrafts.$inferSelect)[]> {
  return db
    .select()
    .from(dealDrafts)
    .where(eq(dealDrafts.vendorId, vendorId))
    .orderBy(desc(dealDrafts.isStarred), desc(dealDrafts.updatedAt));
}

export async function countDraftsByVendor(db: DrizzleClient, vendorId: string): Promise<number> {
  const [r] = await db
    .select({ n: sql<number>`count(*)::int` })
    .from(dealDrafts)
    .where(eq(dealDrafts.vendorId, vendorId));
  return r?.n ?? 0;
}

export async function updateDraft(
  db: DrizzleClient,
  id: string,
  patch: { payload: Record<string, unknown>; title: string | null },
): Promise<typeof dealDrafts.$inferSelect | null> {
  const [r] = await db
    .update(dealDrafts)
    .set({ ...patch, updatedAt: new Date() })
    .where(eq(dealDrafts.id, id))
    .returning();
  return r ?? null;
}

export async function setStarred(
  db: DrizzleClient,
  id: string,
  starred: boolean,
): Promise<typeof dealDrafts.$inferSelect | null> {
  const [r] = await db
    .update(dealDrafts)
    .set({ isStarred: starred, updatedAt: new Date() })
    .where(eq(dealDrafts.id, id))
    .returning();
  return r ?? null;
}

export async function deleteDraft(db: DrizzleClient, id: string): Promise<void> {
  await db.delete(dealDrafts).where(eq(dealDrafts.id, id));
}

export async function purgeStaleUnstarred(db: DrizzleClient, olderThan: Date): Promise<number> {
  const rows = await db
    .delete(dealDrafts)
    .where(and(eq(dealDrafts.isStarred, false), lt(dealDrafts.updatedAt, olderThan)))
    .returning({ id: dealDrafts.id });
  return rows.length;
}

/**
 * Advisory lock scoped to vendor's draft cap check.
 * Call inside a transaction before counting + inserting to prevent race conditions.
 */
export async function acquireCapLock(db: DrizzleClient, vendorId: string): Promise<void> {
  await db.execute(sql`SELECT pg_advisory_xact_lock(hashtext('deal_drafts_cap_' || ${vendorId}))`);
}