/**
 * Vendor queries - typed async functions for the vendors table.
 *
 * Read helpers that accept a `locale` option JOIN `vendor_translations` via a
 * LATERAL subquery: preferred locale first, default locale fallback.
 * Non-defensive: no legacy-column fallback — legacy columns are scheduled for
 * drop in a follow-up plan. If no translation row exists the translated fields
 * are NULL and the caller is responsible for logging an integrity warning.
 */

import { firstExecuteRow } from '../execute-rows.js';
import { and, eq, inArray, sql } from 'drizzle-orm';
import { invalidateCatalog } from '@/server/cache/invalidate.js';
import type { DrizzleClient, TxDrizzleClient } from '../client.js';
import {
  vendors,
  vendorTranslations,
  vendorBusinessTypes,
  businessTypes,
  notifications,
  dealDrafts,
  users,
} from '../schema.js';
import type { accountStateVendorEnum, vendorTierEnum } from '../schema.js';
import { resolveImageNotifications } from './notifications.js';
import { VENDOR_DEFAULT_LOCALE } from './vendor-constants.js';

export { VENDOR_DEFAULT_LOCALE };

export type VendorLocaleOptions = {
  /** BCP-47 locale code (e.g. "he", "en"). Defaults to VENDOR_DEFAULT_LOCALE. */
  locale?: string;
};

/**
 * Shape returned by locale-aware vendor read helpers.
 * `displayName` and `description` come from `vendor_translations`; all other
 * columns come from the `vendors` table unchanged.
 */
export type VendorWithTranslation = typeof vendors.$inferSelect & {
  displayName: string | null;
  description: string | null;
  translationLocale: string | null;
  businessTypeIds: string[];
};

/**
 * Build a Drizzle `sql` fragment that LEFT JOIN LATERALs vendor_translations
 * for the requested locale with fallback to the default locale.
 *
 * Returns a column-selection object suitable for use in `.select({...})`.
 * The join must be applied with `.leftJoin(translationLateral, sql`true`)`.
 */
function buildTranslationSelect(_locale: string) {
  return {
    // All vendor columns
    id: vendors.id,
    ownerUserId: vendors.ownerUserId,
    businessName: vendors.businessName,
    // Translated fields — from the LATERAL result alias `vt`
    displayName: sql<string | null>`vt.display_name`,
    description: sql<string | null>`vt.description`,
    translationLocale: sql<string | null>`vt.locale`,
    // Remaining vendor columns
    phone: vendors.phone,
    email: vendors.email,
    // Public-read gate: only return logoUrl when the logo has been approved.
    // Pending/rejected logos fall back to null so the UI shows VendorLogoFallback.
    logoUrl: sql<
      string | null
    >`CASE WHEN ${vendors.logoApprovalStatus} = 'APPROVED' THEN ${vendors.logoUrl} ELSE NULL END`,
    heroImageUrl: vendors.heroImageUrl,
    heroImageR2Key: vendors.heroImageR2Key,
    heroPendingImageUrl: vendors.heroPendingImageUrl,
    heroPendingImageR2Key: vendors.heroPendingImageR2Key,
    heroImageApprovalStatus: vendors.heroImageApprovalStatus,
    heroImageRejectReasonCode: vendors.heroImageRejectReasonCode,
    heroFocalX: vendors.heroFocalX,
    heroFocalY: vendors.heroFocalY,
    website: vendors.website,
    tier: vendors.tier,
    accountState: vendors.accountState,
    flagReason: vendors.flagReason,
    rejectReason: vendors.rejectReason,
    llmDecision: vendors.llmDecision,
    totalSales: vendors.totalSales,
    totalRevenue: vendors.totalRevenue,
    reviewsScore: vendors.reviewsScore,
    reviewsCount: vendors.reviewsCount,
    removedReviewsCount: vendors.removedReviewsCount,
    dealViolationsCount: vendors.dealViolationsCount,
    unresolvedVoucherComplaints: vendors.unresolvedVoucherComplaints,
    firstVoucherComplaintAt: vendors.firstVoucherComplaintAt,
    preferencesProfile: vendors.preferencesProfile,
    createdAt: vendors.createdAt,
    stripeAccountId: vendors.stripeAccountId,
    stripeChargesEnabled: vendors.stripeChargesEnabled,
    stripePayoutsEnabled: vendors.stripePayoutsEnabled,
    stripeOnboardingState: vendors.stripeOnboardingState,
    stripeDetailsSubmitted: vendors.stripeDetailsSubmitted,
    stripeRequirementsCurrentlyDue: vendors.stripeRequirementsCurrentlyDue,
    businessTypeIds: sql<string[]>`COALESCE(
      (SELECT array_agg(vbt.business_type_id)
         FROM vendor_business_types vbt
        WHERE vbt.vendor_id = ${vendors.id}),
      ARRAY[]::uuid[]
    )`,
    logoApprovalStatus: vendors.logoApprovalStatus,
    pendingLogoUrl: vendors.pendingLogoUrl,
    pendingLogoR2Key: vendors.pendingLogoR2Key,
    logoRejectReasonCode: vendors.logoRejectReasonCode,
    selfPickup: vendors.selfPickup,
    pickupAddress: vendors.pickupAddress,
    returnAddress: vendors.returnAddress,
    restockingFeePct: vendors.restockingFeePct,
  } as const;
}

/**
 * Returns a sql lateral join expression for vendor_translations.
 * Tries the requested locale first; falls back to default locale.
 * Parameters are properly bound — no sql.raw string interpolation.
 */
function vendorTranslationLateral(locale: string) {
  const defaultLocale = VENDOR_DEFAULT_LOCALE;
  return sql`LATERAL (
    SELECT vt2.display_name, vt2.description, vt2.locale
    FROM ${vendorTranslations} vt2
    WHERE vt2.vendor_id = ${vendors.id}
      AND vt2.locale IN (${locale}, ${defaultLocale})
    ORDER BY CASE vt2.locale WHEN ${locale} THEN 0 ELSE 1 END
    LIMIT 1
  ) AS vt`;
}

type AccountState = (typeof accountStateVendorEnum.enumValues)[number];
type VendorTier = (typeof vendorTierEnum.enumValues)[number];

export async function findById(
  db: DrizzleClient,
  id: string,
  opts: VendorLocaleOptions = {},
): Promise<VendorWithTranslation | null> {
  const locale = opts.locale ?? VENDOR_DEFAULT_LOCALE;
  const lateral = vendorTranslationLateral(locale);
  const [row] = await db
    .select(buildTranslationSelect(locale))
    .from(vendors)
    .leftJoin(lateral, sql`true`)
    .where(eq(vendors.id, id))
    .limit(1);
  return (row as unknown as VendorWithTranslation) ?? null;
}

/**
 * Find by UUID. Slug routing is handled at the page layer using vendor id or
 * a slug derived from businessName - actual slug column deferred to Phase 2.
 */
export async function findBySlugOrId(
  db: DrizzleClient,
  idOrSlug: string,
  opts: VendorLocaleOptions = {},
): Promise<VendorWithTranslation | null> {
  // v1: slug === id (UUID). Phase 2 adds a slug column + lookup.
  return findById(db, idOrSlug, opts);
}

export async function findByOwnerId(
  db: DrizzleClient,
  ownerUserId: string,
  opts: VendorLocaleOptions = {},
): Promise<VendorWithTranslation[]> {
  const locale = opts.locale ?? VENDOR_DEFAULT_LOCALE;
  const lateral = vendorTranslationLateral(locale);
  return db
    .select(buildTranslationSelect(locale))
    .from(vendors)
    .leftJoin(lateral, sql`true`)
    .where(eq(vendors.ownerUserId, ownerUserId)) as unknown as Promise<VendorWithTranslation[]>;
}

/**
 * Returns true when the user owns an active (non-frozen, non-banned) vendor.
 * Lightweight check — no lateral join — used by session middleware for nav badge.
 */
export async function hasActiveVendor(db: DrizzleClient, userId: string): Promise<boolean> {
  const [row] = await db
    .select({ accountState: vendors.accountState })
    .from(vendors)
    .where(eq(vendors.ownerUserId, userId))
    .limit(1);
  return !!row && row.accountState !== 'FROZEN' && row.accountState !== 'BANNED';
}

/**
 * Returns the single vendor owned by a user, or null if none exists.
 * One user = one vendor in v1.
 */
export async function getVendorByOwnerUser(
  db: DrizzleClient,
  userId: string,
  opts: VendorLocaleOptions = {},
): Promise<VendorWithTranslation | null> {
  const locale = opts.locale ?? VENDOR_DEFAULT_LOCALE;
  const lateral = vendorTranslationLateral(locale);
  const [row] = await db
    .select(buildTranslationSelect(locale))
    .from(vendors)
    .leftJoin(lateral, sql`true`)
    .where(eq(vendors.ownerUserId, userId))
    .limit(1);
  return (row as unknown as VendorWithTranslation) ?? null;
}

export async function listActive(
  db: DrizzleClient,
  opts: VendorLocaleOptions = {},
): Promise<VendorWithTranslation[]> {
  const locale = opts.locale ?? VENDOR_DEFAULT_LOCALE;
  const lateral = vendorTranslationLateral(locale);
  return db
    .select(buildTranslationSelect(locale))
    .from(vendors)
    .leftJoin(lateral, sql`true`)
    .where(inArray(vendors.accountState, ['ACTIVE', 'VETERAN'])) as unknown as Promise<
    VendorWithTranslation[]
  >;
}

export async function upgradeTier(db: DrizzleClient, id: string, tier: VendorTier) {
  const [row] = await db.update(vendors).set({ tier }).where(eq(vendors.id, id)).returning();
  return row ?? null;
}

export async function setVendorTierAndAccountState(
  db: DrizzleClient,
  vendorId: string,
  tier: VendorTier,
  accountState: AccountState,
): Promise<void> {
  await db.update(vendors).set({ tier, accountState }).where(eq(vendors.id, vendorId));
}

export async function updateAccountState(db: DrizzleClient, id: string, state: AccountState) {
  const [row] = await db
    .update(vendors)
    .set({ accountState: state })
    .where(eq(vendors.id, id))
    .returning();
  if (row) {
    await db
      .update(users)
      .set({ mhVersion: sql`${users.mhVersion} + 1` })
      .where(eq(users.id, row.ownerUserId));
  }
  return row ?? null;
}

export type CreateVendorInput = {
  id?: string;
  ownerUserId: string;
  businessName: string;
  displayName: string;
  description?: string;
  /** Business type UUIDs (0..5). Validated ⊆ active types by setVendorBusinessTypes. */
  businessTypeIds?: string[];
  /** Encrypted vendor phone. */
  phone: unknown;
  /** Encrypted vendor email. */
  email: unknown;
};

export type UpdateVendorInput = {
  businessName?: string;
  displayName?: string;
  description?: string;
  /** Encrypted vendor phone. */
  phone?: unknown;
  /** Encrypted vendor email. */
  email?: unknown;
  logoUrl?: string | null;
  heroImageUrl?: string | null;
  heroImageR2Key?: string | null;
  heroFocalX?: number;
  heroFocalY?: number;
  website?: string | null;
  selfPickup?: boolean;
  /** Human-readable pickup address. Null explicitly clears it. */
  pickupAddress?: string | null;
  /** Structured return address. Null explicitly clears it. */
  returnAddress?: {
    line1: string;
    line2?: string;
    city: string;
    postal: string;
    country: string;
  } | null;
  /** Restocking fee as a fraction (0..0.05). Stored as numeric string in DB. */
  restockingFeePct?: number;
};

export async function updateVendor(db: DrizzleClient, id: string, input: UpdateVendorInput) {
  let wrote = false;
  const patch: Partial<typeof vendors.$inferInsert> = {};
  if (input.businessName !== undefined) patch.businessName = input.businessName;
  if (input.displayName !== undefined) patch.displayName = input.displayName;
  if (input.phone !== undefined) patch.phone = input.phone as string;
  if (input.email !== undefined) patch.email = input.email as string;
  if ('logoUrl' in input) patch.logoUrl = input.logoUrl ?? null;
  if ('heroImageUrl' in input) patch.heroImageUrl = input.heroImageUrl ?? null;
  if ('heroImageR2Key' in input) patch.heroImageR2Key = input.heroImageR2Key ?? null;
  if (input.heroFocalX !== undefined) patch.heroFocalX = input.heroFocalX;
  if (input.heroFocalY !== undefined) patch.heroFocalY = input.heroFocalY;
  if ('website' in input) patch.website = input.website ?? null;
  if (input.selfPickup !== undefined) patch.selfPickup = input.selfPickup;
  if ('pickupAddress' in input) patch.pickupAddress = input.pickupAddress ?? null;
  if ('returnAddress' in input) patch.returnAddress = input.returnAddress ?? null;
  if (input.restockingFeePct !== undefined) patch.restockingFeePct = String(input.restockingFeePct);

  if (input.displayName !== undefined) {
    await upsertTranslationDisplayName(db, id, input.displayName);
    wrote = true;
  }

  if (input.description !== undefined) {
    patch.description = input.description;
    await db
      .insert(vendorTranslations)
      .values({
        vendorId: id,
        locale: VENDOR_DEFAULT_LOCALE,
        description: input.description,
        descriptionManualOverride: true,
      })
      .onConflictDoUpdate({
        target: [vendorTranslations.vendorId, vendorTranslations.locale],
        set: {
          description: input.description,
          descriptionManualOverride: true,
          updatedAt: sql`now()`,
        },
      });
    wrote = true;
  }

  if (Object.keys(patch).length === 0) {
    if (wrote) await invalidateCatalog(db, { scope: 'vendor', vendorSlug: id });
    return (await db.select().from(vendors).where(eq(vendors.id, id)).limit(1))[0] ?? null;
  }

  const [row] = await db.update(vendors).set(patch).where(eq(vendors.id, id)).returning();
  if (row) await invalidateCatalog(db, { scope: 'vendor', vendorSlug: id });
  return row ?? null;
}

/**
 * Replace a vendor's business-type associations (delete-all-then-insert).
 * Validates ids ⊆ active business_types, dedupes, caps at 5.
 * Sequential writes — neon-http has no interactive transaction.
 * Returns the ids actually written.
 */
export async function setVendorBusinessTypes(
  db: DrizzleClient,
  vendorId: string,
  typeIds: string[],
): Promise<string[]> {
  const unique = Array.from(new Set(typeIds)).slice(0, 5);
  let valid: string[] = [];
  if (unique.length > 0) {
    const rows = await db
      .select({ id: businessTypes.id })
      .from(businessTypes)
      .where(and(inArray(businessTypes.id, unique), eq(businessTypes.isActive, true)));
    valid = rows.map((r) => r.id);
  }
  await db.delete(vendorBusinessTypes).where(eq(vendorBusinessTypes.vendorId, vendorId));
  if (valid.length > 0) {
    await db
      .insert(vendorBusinessTypes)
      .values(valid.map((id) => ({ vendorId, businessTypeId: id })));
  }
  return valid;
}

/**
 * Upsert the vendor's display name in vendor_translations (default locale).
 * Sets displayNameManualOverride = true so the value is preserved on re-index.
 */
export async function upsertTranslationDisplayName(
  db: DrizzleClient,
  vendorId: string,
  displayName: string,
): Promise<void> {
  await db
    .insert(vendorTranslations)
    .values({
      vendorId,
      locale: VENDOR_DEFAULT_LOCALE,
      displayName,
      displayNameManualOverride: true,
    })
    .onConflictDoUpdate({
      target: [vendorTranslations.vendorId, vendorTranslations.locale],
      set: {
        displayName,
        displayNameManualOverride: true,
        updatedAt: sql`now()`,
      },
    });
}

/**
 * Update the preferencesProfile JSONB column for a vendor.
 * The caller is responsible for merging existing prefs with the patch before calling.
 */
export async function updatePreferencesProfile(
  db: DrizzleClient,
  vendorId: string,
  prefs: object,
): Promise<void> {
  await db.update(vendors).set({ preferencesProfile: prefs }).where(eq(vendors.id, vendorId));
  await invalidateCatalog(db, { scope: 'vendor', vendorSlug: vendorId });
}

export async function createVendor(db: DrizzleClient, input: CreateVendorInput) {
  const [row] = await db
    .insert(vendors)
    .values({
      ...(input.id ? { id: input.id } : {}),
      ownerUserId: input.ownerUserId,
      businessName: input.businessName,
      displayName: input.displayName,
      description: input.description ?? '',
      phone: input.phone as string,
      email: input.email as string,
    })
    .returning();
  if (input.businessTypeIds?.length) {
    await setVendorBusinessTypes(db, row!.id, input.businessTypeIds);
  }
  return row!;
}

export async function setVendorOwnerUserId(
  db: DrizzleClient,
  vendorId: string,
  ownerUserId: string,
): Promise<void> {
  await db.update(vendors).set({ ownerUserId }).where(eq(vendors.id, vendorId));
}

export async function setVendorAccountState(
  db: DrizzleClient,
  vendorId: string,
  accountState: AccountState,
): Promise<void> {
  await db.update(vendors).set({ accountState }).where(eq(vendors.id, vendorId));
}

export async function activateVendorOnFirstApproval(
  db: DrizzleClient,
  vendorId: string,
): Promise<void> {
  await db
    .update(vendors)
    .set({ accountState: 'ACTIVE' })
    .where(and(eq(vendors.id, vendorId), eq(vendors.accountState, 'PENDING_FIRST_APPROVAL')));
}

export async function approveVendorAnyway(db: DrizzleClient, vendorId: string): Promise<void> {
  await db
    .update(vendors)
    .set({
      accountState: 'PENDING_PROCESSOR',
      flagReason: null,
      rejectReason: null,
      llmDecision: 'APPROVE',
    })
    .where(eq(vendors.id, vendorId));
}

export async function setVendorHeroApprovalStatus(
  db: DrizzleClient,
  vendorId: string,
  status: 'APPROVED' | 'REJECTED',
): Promise<{ id: string } | null> {
  const [row] = await db
    .update(vendors)
    .set({ heroImageApprovalStatus: status })
    .where(eq(vendors.id, vendorId))
    .returning({ id: vendors.id });
  return row ?? null;
}

export async function incrementVendorRemovedReviewsCount(
  db: DrizzleClient,
  vendorId: string,
): Promise<void> {
  await db
    .update(vendors)
    .set({ removedReviewsCount: sql`removed_reviews_count + 1` })
    .where(eq(vendors.id, vendorId));
}

/** Write a new upload to the pending slot. Clears reject reason, sets PENDING. */
export async function setVendorHeroPending(
  db: DrizzleClient,
  vendorId: string,
  pendingImageUrl: string,
  pendingR2Key: string,
) {
  const [row] = await db
    .update(vendors)
    .set({
      heroPendingImageUrl: pendingImageUrl,
      heroPendingImageR2Key: pendingR2Key,
      heroImageApprovalStatus: 'PENDING',
      heroImageRejectReasonCode: null,
    })
    .where(eq(vendors.id, vendorId))
    .returning();
  return row ?? null;
}

/** Approve: promote pending → live. Clears pending slot. Returns null if pending slot empty. */
export async function approveVendorHeroPending(db: DrizzleClient, vendorId: string) {
  const [vendor] = await db
    .select({
      heroPendingImageUrl: vendors.heroPendingImageUrl,
      heroPendingImageR2Key: vendors.heroPendingImageR2Key,
    })
    .from(vendors)
    .where(eq(vendors.id, vendorId))
    .limit(1);
  if (!vendor?.heroPendingImageUrl) return null;
  const [row] = await db
    .update(vendors)
    .set({
      heroImageUrl: vendor.heroPendingImageUrl,
      heroImageR2Key: vendor.heroPendingImageR2Key,
      heroImageApprovalStatus: 'APPROVED',
      heroPendingImageUrl: null,
      heroPendingImageR2Key: null,
      heroImageRejectReasonCode: null,
    })
    .where(eq(vendors.id, vendorId))
    .returning();
  if (row) await invalidateCatalog(db, { scope: 'vendor', vendorSlug: vendorId });
  return row ?? null;
}

/** Reject: clear pending slot, set reason code. Live image (heroImageUrl) untouched. */
export async function rejectVendorHeroPending(
  db: DrizzleClient,
  vendorId: string,
  rejectReasonCode: string,
) {
  const [row] = await db
    .update(vendors)
    .set({
      heroImageApprovalStatus: 'REJECTED',
      heroPendingImageUrl: null,
      heroPendingImageR2Key: null,
      heroImageRejectReasonCode: rejectReasonCode,
    })
    .where(eq(vendors.id, vendorId))
    .returning();
  return row ?? null;
}

// ── Logo approval helpers (mirrors hero pattern) ──────────────────────────────

/**
 * Move a new logo upload into the pending slot for moderation.
 * Clears any prior rejection reason and stamps resolved_at on outstanding
 * image.rejected notifications for this vendor's logo.
 */
export async function setVendorLogoPending(
  db: TxDrizzleClient,
  vendorId: string,
  pendingLogoUrl: string,
  pendingLogoR2Key: string,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx
      .update(vendors)
      .set({
        pendingLogoUrl,
        pendingLogoR2Key,
        logoApprovalStatus: 'PENDING',
        logoRejectReasonCode: null,
      })
      .where(eq(vendors.id, vendorId));
    await resolveImageNotifications(tx, {
      entityType: 'vendor',
      entityId: vendorId,
      purpose: 'vendor_logo',
    });
  });
}

/**
 * Promote pending logo to live (logoUrl). Clears pending slot.
 * No-ops if there is no pending logo.
 */
export async function approveVendorLogoPending(
  db: TxDrizzleClient,
  vendorId: string,
): Promise<void> {
  let approved = false;
  await db.transaction(async (tx) => {
    const [v] = await tx
      .select({ pendingUrl: vendors.pendingLogoUrl })
      .from(vendors)
      .where(eq(vendors.id, vendorId))
      .limit(1);
    if (!v?.pendingUrl) return;
    await tx
      .update(vendors)
      .set({
        logoUrl: v.pendingUrl,
        pendingLogoUrl: null,
        pendingLogoR2Key: null,
        logoApprovalStatus: 'APPROVED',
        logoRejectReasonCode: null,
      })
      .where(eq(vendors.id, vendorId));
    await resolveImageNotifications(tx, {
      entityType: 'vendor',
      entityId: vendorId,
      purpose: 'vendor_logo',
    });
    approved = true;
  });
  if (approved) await invalidateCatalog(db, { scope: 'vendor', vendorSlug: vendorId });
}

/** Shape of one row in `unresolvedNotifications` — mirrors `notifications` table. */
export type DashboardNotification = typeof notifications.$inferSelect;

/** Result of `getDashboardBundle`. `vendor` is null when the user owns no vendor. */
export interface VendorDashboardBundle {
  vendor: { id: string } | null;
  draftsCount: number;
  unresolvedNotifications: DashboardNotification[];
}

/**
 * Fetch the data needed to render `/vendor/dashboard` in a single Neon-HTTP
 * round-trip. Replaces three sequential queries (findByOwnerId → countDrafts →
 * listUnresolved) with one CTE that pivots on the vendor id once and aggregates
 * the related counts/rows.
 *
 * Performance: was 3 sequential HTTP round-trips (~1.5-1.9s) → 1 round-trip
 * (~400-600ms), recovering ~1s on dashboard TTFB.
 *
 * Returns `vendor: null` (and zero/empty siblings) when the user does not own
 * a vendor — caller is expected to redirect to /vendor/register in that case.
 */
export async function getDashboardBundle(
  db: DrizzleClient,
  ownerUserId: string,
): Promise<VendorDashboardBundle> {
  const result = await db.execute<{
    vendor_id: string | null;
    drafts_count: number | string;
    unresolved_notifications: Array<Record<string, unknown>> | null;
  }>(sql`
    WITH v AS (
      SELECT ${vendors.id} AS id
      FROM ${vendors}
      WHERE ${vendors.ownerUserId} = ${ownerUserId}
      LIMIT 1
    )
    SELECT
      (SELECT id FROM v) AS vendor_id,
      COALESCE(
        (SELECT COUNT(*)::int FROM ${dealDrafts} WHERE ${dealDrafts.vendorId} = (SELECT id FROM v)),
        0
      ) AS drafts_count,
      COALESCE(
        (
          SELECT jsonb_agg(n_row ORDER BY (n_row->>'createdAt') DESC)
          FROM (
            SELECT jsonb_build_object(
              'id',            ${notifications.id},
              'recipientType', ${notifications.recipientType},
              'recipientId',   ${notifications.recipientId},
              'eventType',     ${notifications.eventType},
              'severity',      ${notifications.severity},
              'payload',       ${notifications.payload},
              'readAt',        ${notifications.readAt},
              'resolvedAt',    ${notifications.resolvedAt},
              'createdAt',     ${notifications.createdAt}
            ) AS n_row
            FROM ${notifications}
            WHERE ${notifications.recipientType} = 'vendor'
              AND ${notifications.recipientId} = (SELECT id FROM v)
              AND ${notifications.resolvedAt} IS NULL
          ) sub
        ),
        '[]'::jsonb
      ) AS unresolved_notifications
  `);

  // neon-http returns `{ rows: [...] }` from db.execute; narrow defensively.
  const row = firstExecuteRow<{
    vendor_id: string | null;
    drafts_count: number | string;
    unresolved_notifications: Array<Record<string, unknown>> | null;
  }>(result);

  if (!row || !row.vendor_id) {
    return { vendor: null, draftsCount: 0, unresolvedNotifications: [] };
  }

  const raw = row.unresolved_notifications ?? [];
  // jsonb_build_object returns ISO strings for timestamps — revive into Date
  // to match the shape returned by Drizzle .select() on the same table.
  const unresolvedNotifications: DashboardNotification[] = raw.map((n) => ({
    id: n.id as string,
    recipientType: n.recipientType as string,
    recipientId: n.recipientId as string,
    eventType: n.eventType as string,
    severity: n.severity as DashboardNotification['severity'],
    payload: n.payload as Record<string, unknown>,
    readAt: n.readAt ? new Date(n.readAt as string) : null,
    resolvedAt: n.resolvedAt ? new Date(n.resolvedAt as string) : null,
    createdAt: new Date(n.createdAt as string),
  }));

  return {
    vendor: { id: row.vendor_id },
    draftsCount: typeof row.drafts_count === 'string' ? Number(row.drafts_count) : row.drafts_count,
    unresolvedNotifications,
  };
}

// ── Stripe Connect helpers ────────────────────────────────────────────────────

export type StripeOnboardingState = (typeof vendors.$inferSelect)['stripeOnboardingState'];

/**
 * Persist the Stripe Connect Express account ID after account creation.
 * Sets stripeOnboardingState to 'account_created'.
 */
export async function setVendorStripeAccount(
  db: DrizzleClient,
  vendorId: string,
  stripeAccountId: string,
): Promise<void> {
  await db
    .update(vendors)
    .set({ stripeAccountId, stripeOnboardingState: 'account_created' })
    .where(eq(vendors.id, vendorId));
}

/**
 * Update Stripe account capabilities and onboarding state after an
 * account.updated webhook. Pass only the fields that changed.
 */
export async function setVendorStripeStatus(
  db: DrizzleClient,
  vendorId: string,
  patch: {
    stripeChargesEnabled?: boolean;
    stripePayoutsEnabled?: boolean;
    stripeOnboardingState?: StripeOnboardingState;
    stripeDetailsSubmitted?: boolean;
    stripeRequirementsCurrentlyDue?: string[] | null;
  },
): Promise<void> {
  await db.update(vendors).set(patch).where(eq(vendors.id, vendorId));
}

export async function activateVendorOnChargesEnabled(
  db: DrizzleClient,
  vendorId: string,
): Promise<boolean> {
  const rows = await db
    .update(vendors)
    .set({ accountState: 'ACTIVE' })
    .where(and(eq(vendors.id, vendorId), eq(vendors.accountState, 'PENDING_PROCESSOR')))
    .returning({ id: vendors.id });
  return rows.length > 0;
}

/**
 * Find a vendor by their Stripe Connect account ID.
 * Returns null when not found.
 */
export async function getVendorByStripeAccountId(
  db: DrizzleClient,
  stripeAccountId: string,
): Promise<typeof vendors.$inferSelect | null> {
  const [row] = await db
    .select()
    .from(vendors)
    .where(eq(vendors.stripeAccountId, stripeAccountId))
    .limit(1);
  return row ?? null;
}