/**
 * Admin vendor resource — read-only queries.
 *
 * - listVendors: filtered + paginated vendor list with deal count and revenue.
 * - getVendorDetail: full vendor profile (hours, addresses, recent deals/reviews).
 *
 * No raw SQL interpolation. PII never logged.
 */

import { eq, and, gte, lte, count, desc, isNotNull, ilike, sql, inArray } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  vendors,
  businessHours,
  vendorAddresses,
  deals,
  reviews,
  clubMemberships,
} from '@/server/db/schema.js';
import type {
  ListVendorsFilter,
  VendorSummary,
  VendorDetail,
  VendorBusinessHours,
} from './types.js';

// ─── listVendors ──────────────────────────────────────────────────────────────

export async function listVendors(
  db: DrizzleClient,
  filter: ListVendorsFilter,
): Promise<{ data: VendorSummary[]; total: number }> {
  const conditions = [];

  if (filter.state) {
    type VendorState = NonNullable<typeof vendors.accountState._.data>;
    if (filter.state === 'FLAGGED') {
      // FLAGGED is a virtual filter: PENDING_FIRST_APPROVAL vendors with a non-null flagReason
      conditions.push(eq(vendors.accountState, 'PENDING_FIRST_APPROVAL'));
      conditions.push(isNotNull(vendors.flagReason));
    } else if (filter.state.includes(',')) {
      const states = filter.state.split(',') as VendorState[];
      conditions.push(inArray(vendors.accountState, states));
    } else {
      conditions.push(eq(vendors.accountState, filter.state as VendorState));
    }
  }
  if (filter.tier) {
    conditions.push(eq(vendors.tier, filter.tier));
  }
  if (filter.search) {
    conditions.push(ilike(vendors.businessName, `%${filter.search}%`));
  }
  if (filter.from) {
    conditions.push(gte(vendors.createdAt, new Date(filter.from)));
  }
  if (filter.to) {
    conditions.push(lte(vendors.createdAt, new Date(filter.to)));
  }

  const activeDealsSubquery = sql<number>`(
    SELECT COUNT(*)::int FROM deals
    WHERE deals.vendor_id = ${vendors.id} AND deals.deal_state = 'ACTIVE'
  )`;

  if (filter.hideNoDeals) {
    conditions.push(
      sql`(SELECT COUNT(*) FROM deals WHERE deals.vendor_id = ${vendors.id} AND deals.deal_state = 'ACTIVE') > 0`,
    );
  }

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  const [rows, [countRow]] = await Promise.all([
    db
      .select({
        id: vendors.id,
        businessName: vendors.businessName,
        displayName: vendors.displayName,
        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,
        activeDeals: activeDealsSubquery,
        createdAt: vendors.createdAt,
      })
      .from(vendors)
      .where(whereClause)
      .limit(filter.limit)
      .offset(filter.offset),
    db.select({ count: count() }).from(vendors).where(whereClause),
  ]);

  return { data: rows, total: countRow?.count ?? 0 };
}

// ─── getVendorDetail ──────────────────────────────────────────────────────────

export async function getVendorDetail(
  db: DrizzleClient,
  vendorId: string,
): Promise<VendorDetail | null> {
  // Main vendor row
  const [vendor] = await db
    .select({
      id: vendors.id,
      businessName: vendors.businessName,
      displayName: vendors.displayName,
      description: vendors.description,
      businessTypeNames: sql<string[]>`COALESCE(
        (SELECT array_agg(bt.name_he ORDER BY bt.sort_order)
           FROM vendor_business_types vbt
           JOIN business_types bt ON bt.id = vbt.business_type_id
          WHERE vbt.vendor_id = ${vendors.id}),
        ARRAY[]::text[]
      )`,
      logoUrl: vendors.logoUrl,
      heroImageUrl: vendors.heroImageUrl,
      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,
      unresolvedVoucherComplaints: vendors.unresolvedVoucherComplaints,
      stripeAccountId: vendors.stripeAccountId,
      stripeChargesEnabled: vendors.stripeChargesEnabled,
      stripePayoutsEnabled: vendors.stripePayoutsEnabled,
      stripeOnboardingState: vendors.stripeOnboardingState,
      stripeDetailsSubmitted: vendors.stripeDetailsSubmitted,
      stripeRequirementsCurrentlyDue: vendors.stripeRequirementsCurrentlyDue,
      createdAt: vendors.createdAt,
    })
    .from(vendors)
    .where(eq(vendors.id, vendorId))
    .limit(1);

  if (!vendor) return null;

  // Run all supporting queries in parallel
  const [hoursRows, addressRows, dealRows, reviewRows, clubCountRow, dealCountRows] =
    await Promise.all([
      db.select().from(businessHours).where(eq(businessHours.vendorId, vendorId)).limit(1),
      db
        .select({
          id: vendorAddresses.id,
          label: vendorAddresses.label,
          fullAddress: vendorAddresses.fullAddress,
          lat: vendorAddresses.lat,
          lng: vendorAddresses.lng,
        })
        .from(vendorAddresses)
        .where(eq(vendorAddresses.vendorId, vendorId)),
      db
        .select({
          id: deals.id,
          title: deals.title,
          dealType: deals.dealType,
          dealState: deals.dealState,
          createdAt: deals.createdAt,
        })
        .from(deals)
        .where(eq(deals.vendorId, vendorId))
        .orderBy(desc(deals.createdAt))
        .limit(10),
      db
        .select({
          id: reviews.id,
          rating: reviews.rating,
          body: reviews.body,
          reviewType: reviews.reviewType,
          isVisible: reviews.isVisible,
          createdAt: reviews.createdAt,
        })
        .from(reviews)
        .where(eq(reviews.vendorId, vendorId))
        .orderBy(desc(reviews.createdAt))
        .limit(10),
      db
        .select({ count: count() })
        .from(clubMemberships)
        .where(and(eq(clubMemberships.vendorId, vendorId), eq(clubMemberships.isActive, true))),
      // Deal counts per state
      db
        .select({ dealState: deals.dealState, cnt: count() })
        .from(deals)
        .where(eq(deals.vendorId, vendorId))
        .groupBy(deals.dealState),
    ]);

  // Build deal counts
  let activeCount = 0;
  let pendingCount = 0;
  let totalDealCount = 0;
  for (const row of dealCountRows) {
    totalDealCount += row.cnt;
    if (row.dealState === 'ACTIVE') activeCount = row.cnt;
    if (row.dealState === 'PENDING_APPROVAL') pendingCount = row.cnt;
  }

  const hoursRow = hoursRows[0] ?? null;
  const hours: VendorBusinessHours | null = hoursRow
    ? {
        mondayOpen: hoursRow.mondayOpen,
        mondayClose: hoursRow.mondayClose,
        mondayClosed: hoursRow.mondayClosed,
        tuesdayOpen: hoursRow.tuesdayOpen,
        tuesdayClose: hoursRow.tuesdayClose,
        tuesdayClosed: hoursRow.tuesdayClosed,
        wednesdayOpen: hoursRow.wednesdayOpen,
        wednesdayClose: hoursRow.wednesdayClose,
        wednesdayClosed: hoursRow.wednesdayClosed,
        thursdayOpen: hoursRow.thursdayOpen,
        thursdayClose: hoursRow.thursdayClose,
        thursdayClosed: hoursRow.thursdayClosed,
        fridayOpen: hoursRow.fridayOpen,
        fridayClose: hoursRow.fridayClose,
        fridayClosed: hoursRow.fridayClosed,
        saturdayOpen: hoursRow.saturdayOpen,
        saturdayClose: hoursRow.saturdayClose,
        saturdayClosed: hoursRow.saturdayClosed,
        sundayOpen: hoursRow.sundayOpen,
        sundayClose: hoursRow.sundayClose,
        sundayClosed: hoursRow.sundayClosed,
        specialNotes: hoursRow.specialNotes,
      }
    : null;

  return {
    ...vendor,
    hours,
    addresses: addressRows,
    dealCounts: { active: activeCount, pending: pendingCount, total: totalDealCount },
    clubMemberCount: clubCountRow[0]?.count ?? 0,
    recentDeals: dealRows,
    recentReviews: reviewRows,
  };
}