/**
 * Deal queries - typed async functions for the deals table.
 *
 * Entity-alarm law (§7): every create/update with a deadline arms the DO alarm;
 * every cancel/delete disarms it. Helpers from @/server/do-client are used.
 */

import { eq, and, desc, gt, isNull, or, sql, inArray, lte } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import type { DealType } from '@/lib/deal-types';
import { deals, vendors, wishlistItems, dealImages, dealSkus, dealVariantAxes } from '../schema.js';
import type { ServiceBundle } from '@/server/services/types.js';
import { invalidateCatalog } from '@/server/cache/invalidate.js';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import { notSoldOutDrizzle } from './sold-out-filter.js';
import { captureCaught } from '@/server/observability/capture.server.js';

const axesCountSq = sql<number>`(
  SELECT COUNT(*)::int FROM ${dealVariantAxes}
  WHERE ${dealVariantAxes.dealId} = ${deals.id}
  AND ${dealVariantAxes.isActive} = true
)`;

export { PUBLIC_VENDOR_STATES };

/** Fields that are locked after the first sale (FDS §3.6).
 * Note: originalPrice / discountedPrice / discountPercent / quantityTotal now live in deal_skus.
 * These deal-level fields remain locked because changing them post-sale violates consumer trust.
 */
const LOCKED_FIELDS = [
  'pickupAddress',
  'pickupStart',
  'pickupEnd',
  'title',
  'description',
] as const;

type LockedField = (typeof LOCKED_FIELDS)[number];

export async function findById(db: DrizzleClient, id: string) {
  const [row] = await db.select().from(deals).where(eq(deals.id, id)).limit(1);
  return row ?? null;
}

/** Public catalog visibility: ACTIVE deal, public vendor, non-expired window. */
export async function findByIdPublic(db: DrizzleClient, id: string) {
  const [row] = await db
    .select({ deal: deals })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .where(
      and(
        eq(deals.id, id),
        eq(deals.dealState, 'ACTIVE'),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    )
    .limit(1);
  return row?.deal ?? null;
}

/**
 * Returns R2 keys for a deal's images, ordered by (isPrimary DESC, sortOrder ASC).
 * Primary image first, remainder in author-defined order. Empty array if none.
 */
export async function getDealImageR2Keys(db: DrizzleClient, dealId: string): Promise<string[]> {
  const rows = await db
    .select({
      url: dealImages.url,
      isPrimary: dealImages.isPrimary,
      sortOrder: dealImages.sortOrder,
    })
    .from(dealImages)
    .where(eq(dealImages.dealId, dealId))
    .orderBy(desc(dealImages.isPrimary), dealImages.sortOrder);
  return rows.map((r) => r.url);
}

/**
 * Returns true if the vendor has at least one deal of any state.
 * Used to gate "no deals yet" onboarding prompts.
 */
export async function vendorHasAnyDeal(db: DrizzleClient, vendorId: string): Promise<boolean> {
  const [row] = await db
    .select({ id: deals.id })
    .from(deals)
    .where(eq(deals.vendorId, vendorId))
    .limit(1);
  return row !== undefined;
}

export async function listActive(db: DrizzleClient, dealType?: DealType) {
  const conditions = [
    eq(deals.dealState, 'ACTIVE'),
    inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
    or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
  ];
  if (dealType) {
    conditions.push(eq(deals.dealType, dealType));
  }
  const rows = await db
    .select({ deal: deals })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .where(and(...conditions))
    .orderBy(desc(deals.createdAt));
  return rows.map((r) => r.deal);
}

/**
 * Returns a personalised feed for a given user.
 * v1 implementation: active deals ordered by recency.
 * Phase 2 will incorporate geo-scoring and preference matching.
 */
export async function findForYou(db: DrizzleClient, _userId?: string) {
  return db
    .select()
    .from(deals)
    .where(
      and(
        eq(deals.dealState, 'ACTIVE'),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    )
    .orderBy(desc(deals.createdAt))
    .limit(20);
}

export async function findByVendor(db: DrizzleClient, vendorId: string) {
  return db.select().from(deals).where(eq(deals.vendorId, vendorId)).orderBy(desc(deals.createdAt));
}

/**
 * Active vendor deals (dealState = ACTIVE).
 */
export async function listVendorActiveDeals(db: DrizzleClient, vendorId: string) {
  return db
    .select()
    .from(deals)
    .where(and(eq(deals.vendorId, vendorId), eq(deals.dealState, 'ACTIVE')))
    .orderBy(desc(deals.createdAt));
}

/**
 * Pending vendor deals (UNDER_REVIEW | PENDING_APPROVAL | REJECTED).
 *
 * UNDER_REVIEW: AI moderation queue — non-veteran deals enter this state after submit.
 * PENDING_APPROVAL: awaiting human admin review.
 * REJECTED: returned to vendor for fix-and-resubmit.
 */
export async function listVendorPendingDeals(db: DrizzleClient, vendorId: string) {
  return db
    .select()
    .from(deals)
    .where(
      and(
        eq(deals.vendorId, vendorId),
        inArray(deals.dealState, ['UNDER_REVIEW', 'PENDING_APPROVAL', 'REJECTED']),
      ),
    )
    .orderBy(desc(deals.createdAt));
}

/**
 * Closed vendor deals (EXPIRED | SOLD_OUT | PAUSED) within last 90 days.
 */
export async function listVendorClosedDeals(db: DrizzleClient, vendorId: string) {
  const cutoff = new Date(Date.now() - 90 * 24 * 60 * 60 * 1000);
  return db
    .select()
    .from(deals)
    .where(
      and(
        eq(deals.vendorId, vendorId),
        inArray(deals.dealState, ['EXPIRED', 'SOLD_OUT', 'PAUSED']),
        gt(deals.createdAt, cutoff),
      ),
    )
    .orderBy(desc(deals.createdAt));
}

/**
 * Archived vendor deals (dealState = ARCHIVED).
 * Hidden from default tabs; shown only when vendor explicitly requests them.
 */
export async function listVendorArchivedDeals(db: DrizzleClient, vendorId: string) {
  return db
    .select()
    .from(deals)
    .where(and(eq(deals.vendorId, vendorId), eq(deals.dealState, 'ARCHIVED')))
    .orderBy(desc(deals.createdAt));
}

/**
 * History vendor deals (EXPIRED | PAUSED) older than 90 days.
 */
export async function listVendorHistoryDeals(db: DrizzleClient, vendorId: string) {
  const cutoff = new Date(Date.now() - 90 * 24 * 60 * 60 * 1000);
  return db
    .select()
    .from(deals)
    .where(
      and(
        eq(deals.vendorId, vendorId),
        inArray(deals.dealState, ['EXPIRED', 'PAUSED']),
        lte(deals.createdAt, cutoff),
      ),
    )
    .orderBy(desc(deals.createdAt));
}

/** Sold-out vendor deals (SOLD_OUT) across all time. */
export async function listVendorSoldOutDeals(db: DrizzleClient, vendorId: string) {
  return db
    .select()
    .from(deals)
    .where(and(eq(deals.vendorId, vendorId), eq(deals.dealState, 'SOLD_OUT')))
    .orderBy(desc(deals.createdAt));
}

/**
 * Returns deals of the same type from the same vendor, excluding the current deal.
 */
export async function findRelated(
  db: DrizzleClient,
  dealId: string,
  vendorId: string,
  dealType: DealType,
) {
  const rows = await db
    .select({ deal: deals })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .where(
      and(
        eq(deals.vendorId, vendorId),
        eq(deals.dealType, dealType),
        eq(deals.dealState, 'ACTIVE'),
        sql`${deals.id} <> ${dealId}`,
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    )
    .limit(6);
  return rows.map((r) => r.deal);
}

/**
 * Throws if any locked field is being changed after the deal has made at least one sale.
 * Call this before any deal update mutation.
 */
export async function checkLockedFields(
  db: DrizzleClient,
  dealId: string,
  patch: Partial<Record<LockedField, unknown>>,
): Promise<void> {
  // Check deal exists first
  const deal = await findById(db, dealId);
  if (!deal) throw new Error(`Deal ${dealId} not found`);

  // Has any SKU made a sale? (quantity_sold lives in deal_skus post-migration)
  const [hasSale] = await db
    .select({ v: sql<number>`1` })
    .from(dealSkus)
    .where(and(eq(dealSkus.dealId, dealId), sql`${dealSkus.quantitySold} > 0`))
    .limit(1);
  if (!hasSale) return; // No sales yet — all fields editable.

  const attempted = LOCKED_FIELDS.filter((f) => f in patch && patch[f] !== undefined);
  if (attempted.length > 0) {
    throw new Error(
      `Cannot change locked field(s) [${attempted.join(', ')}] after first sale. ` +
        `Contact Multideal admin.`,
    );
  }
}

export async function markSoldOut(
  db: DrizzleClient,
  services: Pick<ServiceBundle, 'doClient'>,
  id: string,
) {
  const now = new Date();
  // Gold shimmer expires 5 hours after sold-out event.
  const goldExpiry = new Date(now.getTime() + 5 * 60 * 60 * 1000);
  const [row] = await db
    .update(deals)
    .set({
      dealState: 'SOLD_OUT',
      soldOutAt: now,
      soldOutGoldExpiresAt: goldExpiry,
    })
    .where(eq(deals.id, id))
    .returning();
  if (row) {
    // Arm gold window alarm to clear shimmer when window expires
    try {
      await services.doClient.armGoldWindowAlarm(id, goldExpiry);
    } catch (err) {
      captureCaught(err, { scope: 'armGoldWindowAlarm.soldOut', extra: { dealId: id } });
    }
    await invalidateCatalog(db, { scope: 'deal', dealId: id });
  }
  return row ?? null;
}

export async function markExpired(
  db: DrizzleClient,
  services: Pick<ServiceBundle, 'doClient'>,
  id: string,
) {
  const [row] = await db
    .update(deals)
    .set({ dealState: 'EXPIRED' })
    .where(eq(deals.id, id))
    .returning();
  if (row) {
    // Deal is expired — disarm any pending alarm
    try {
      await services.doClient.disarmDealAlarm(id);
      await services.doClient.disarmGoldWindowAlarm(id);
    } catch (err) {
      captureCaught(err, { scope: 'disarmAlarms.expired', extra: { dealId: id } });
    }
    await invalidateCatalog(db, { scope: 'deal', dealId: id });
  }
  return row ?? null;
}

export type CreateDealInput = {
  vendorId: string;
  dealType: DealType;
  title: string;
  description?: string;
  categoryId?: string;
  windowStart?: Date;
  windowEnd?: Date;
  pickupStart?: string;
  pickupEnd?: string;
  pickupAddress?: string;
  specialInstructions?: string;
  isVoucher?: boolean;
  commissionRate?: string;
  isPersonalDeal?: boolean;
  personalDealForUserId?: string;
  /** Override initial deal state (default: DRAFT). Pass ACTIVE for personal/veteran deals. */
  dealState?: 'DRAFT' | 'ACTIVE' | 'UNDER_REVIEW';
  approvedAt?: Date;
  approvedBy?: 'HUMAN' | 'AI_AGENT';
  contentHash?: string;
};

export async function createDeal(
  db: DrizzleClient,
  services: Pick<ServiceBundle, 'doClient'>,
  input: CreateDealInput,
) {
  const [row] = await db
    .insert(deals)
    .values({
      vendorId: input.vendorId,
      dealType: input.dealType,
      title: input.title,
      description: input.description ?? '',
      categoryId: input.categoryId,
      windowStart: input.windowStart,
      windowEnd: input.windowEnd,
      pickupStart: input.pickupStart,
      pickupEnd: input.pickupEnd,
      pickupAddress: input.pickupAddress ?? '',
      specialInstructions: input.specialInstructions,
      isVoucher: input.isVoucher ?? false,
      commissionRate: input.commissionRate ?? '0.100',
      isPersonalDeal: input.isPersonalDeal ?? false,
      personalDealForUserId: input.personalDealForUserId,
      ...(input.contentHash ? { contentHash: input.contentHash } : {}),
      ...(input.dealState ? { dealState: input.dealState } : {}),
      ...(input.approvedAt ? { approvedAt: input.approvedAt } : {}),
      ...(input.approvedBy ? { approvedBy: input.approvedBy } : {}),
    })
    .returning();
  const deal = row!;
  // Arm expiry alarm only when the deal starts ACTIVE with a window end.
  // Drafts/under-review deals arm when approved (see approveDeal).
  if (deal.windowEnd && deal.dealState === 'ACTIVE') {
    try {
      await services.doClient.armDealAlarm(deal.id, deal.windowEnd);
    } catch (err) {
      captureCaught(err, { scope: 'armDealAlarm.create', extra: { dealId: deal.id } });
    }
  }
  return deal;
}

/**
 * Approve a deal — transition to ACTIVE and arm expiry alarm if windowEnd is set.
 * Called by admin approval workflow.
 */
export async function approveDeal(
  db: DrizzleClient,
  services: Pick<ServiceBundle, 'doClient'>,
  id: string,
  approvedBy: 'HUMAN' | 'AI_AGENT',
) {
  const [row] = await db
    .update(deals)
    .set({ dealState: 'ACTIVE', approvedAt: new Date(), approvedBy })
    .where(and(eq(deals.id, id), inArray(deals.dealState, ['UNDER_REVIEW', 'PENDING_APPROVAL'])))
    .returning();
  if (row?.windowEnd) {
    try {
      await services.doClient.armDealAlarm(id, row.windowEnd);
    } catch (err) {
      captureCaught(err, { scope: 'armDealAlarm.approve', extra: { dealId: id } });
    }
  }
  if (row) await invalidateCatalog(db, { scope: 'deal', dealId: id });
  return row ?? null;
}

/**
 * Cancel / reject a deal — disarm alarm.
 */
export async function cancelDeal(
  db: DrizzleClient,
  services: Pick<ServiceBundle, 'doClient'>,
  id: string,
  reason?: string,
) {
  const [row] = await db
    .update(deals)
    .set({ dealState: 'REJECTED', rejectionReason: reason })
    .where(eq(deals.id, id))
    .returning();
  if (row) {
    try {
      await services.doClient.disarmDealAlarm(id);
      await services.doClient.disarmGoldWindowAlarm(id);
    } catch (err) {
      captureCaught(err, { scope: 'disarmAlarms.cancel', extra: { dealId: id } });
    }
  }
  return row ?? null;
}

export async function setDealRejectionDetail(
  db: DrizzleClient,
  dealId: string,
  rejectionDetail: string,
): Promise<void> {
  await db.update(deals).set({ rejectionDetail }).where(eq(deals.id, dealId));
}

// ─── Customer list queries ────────────────────────────────────────────────────

/**
 * Returns deals on a user's wishlist, joined from wishlistItems → deals.
 * Returns empty array for guests (undefined userId).
 */
export async function listWishlistByUser(
  db: DrizzleClient,
  userId: string | undefined,
  limit: number,
) {
  if (!userId) return [];
  const rows = await db
    .select({ deal: deals })
    .from(wishlistItems)
    .innerJoin(deals, eq(wishlistItems.dealId, deals.id))
    .where(
      and(
        eq(wishlistItems.userId, userId),
        eq(deals.dealState, 'ACTIVE'),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
      ),
    )
    .orderBy(desc(wishlistItems.createdAt))
    .limit(limit);
  return rows.map((r) => r.deal);
}

/**
 * Returns active deals in a given category (by dealCategory UUID).
 * The category-chips module resolves slug → UUID before calling this.
 */
export async function listByCategory(db: DrizzleClient, categoryId: string, limit: number) {
  const rows = await db
    .select({ deal: deals, axesCount: axesCountSq })
    .from(deals)
    .innerJoin(vendors, eq(deals.vendorId, vendors.id))
    .where(
      and(
        eq(deals.categoryId, categoryId),
        eq(deals.dealState, 'ACTIVE'),
        inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES]),
        or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
        notSoldOutDrizzle(),
      ),
    )
    .orderBy(desc(deals.createdAt))
    .limit(limit);
  return rows.map((r) => ({ ...r.deal, axesCount: r.axesCount ?? 0 }));
}

/**
 * Returns deals by list of IDs (bounded to 60 for safety).
 * Returns empty array when ids is empty.
 */
export async function listDealsByIds(db: DrizzleClient, ids: string[]) {
  if (ids.length === 0) return [];
  const rows = await db
    .select({ deal: deals, axesCount: axesCountSq })
    .from(deals)
    .where(and(inArray(deals.id, ids), eq(deals.dealState, 'ACTIVE'), notSoldOutDrizzle()))
    .limit(60);
  return rows.map((r) => ({ ...r.deal, axesCount: r.axesCount ?? 0 }));
}

export interface DealCustomFilters {
  dealType?: DealType;
  categoryId?: string;
  /** Only deals with quantityRemaining / quantityTotal < 0.2. */
  lowStock?: boolean;
  /** Only deals expiring within 24 hours. */
  nearExpiry?: boolean;
}

/**
 * Returns active deals matching arbitrary filter combinations.
 * All filters are optional — omitting all returns the standard active feed.
 */
export async function listByCustomFilters(
  db: DrizzleClient,
  filters: DealCustomFilters,
  sort: 'recent' | 'trending',
  limit: number,
) {
  const now = new Date();
  const in24h = new Date(now.getTime() + 24 * 60 * 60 * 1000);

  const conditions = [
    eq(deals.dealState, 'ACTIVE'),
    or(isNull(deals.windowEnd), gt(deals.windowEnd, now)),
    notSoldOutDrizzle(),
  ];

  if (filters.dealType) conditions.push(eq(deals.dealType, filters.dealType));
  if (filters.categoryId) conditions.push(eq(deals.categoryId, filters.categoryId));
  if (filters.lowStock) {
    // Low stock: stock_remaining cache col < 20% of initial total — use absolute threshold < 5
    conditions.push(
      sql`${deals.stockRemaining} IS NOT NULL`,
      sql`${deals.stockRemaining} > 0`,
      sql`${deals.stockRemaining} < 5`,
    );
  }
  if (filters.nearExpiry) {
    conditions.push(and(gt(deals.windowEnd, now), lte(deals.windowEnd, in24h))!);
  }

  const orderCol = sort === 'trending' ? desc(deals.stockRemaining) : desc(deals.createdAt);

  const rows = await db
    .select({ deal: deals, axesCount: axesCountSq })
    .from(deals)
    .where(and(...(conditions as [(typeof conditions)[0], ...typeof conditions])))
    .orderBy(orderCol)
    .limit(limit);
  return rows.map((r) => ({ ...r.deal, axesCount: r.axesCount ?? 0 }));
}

/**
 * Relink draft images to a published deal.
 *
 * When a draft is submitted and a deal row is created, any images that were
 * uploaded against the draft (draftId set, dealId pointing at a placeholder or
 * matching the draft's temp uuid) need their dealId updated to the real deal id
 * and their draftId cleared so cascade-delete on draft removal does not orphan them.
 *
 * Called inside submitDraft transaction before deleteDraft.
 */
export async function relinkDraftImagesToDeal(
  db: DrizzleClient,
  draftId: string,
  dealId: string,
): Promise<void> {
  // Move all images that belong to this draft to the real deal and clear the draft FK
  // so cascade-delete on draft removal does not remove the images.
  await db.update(dealImages).set({ dealId, draftId: null }).where(eq(dealImages.draftId, draftId));
}

/**
 * Thin wrapper used by vendor-deal-draft workflow to insert a deal inside a
 * transaction. Delegates to createDeal which handles alarm arming.
 */
export { createDeal as insertDeal };