/**
 * BusinessPageDataLoader - server-side data fetcher for the Business Page (FDS §4.6).
 *
 * loadBusinessPage(db, { slugOrId, userId? }) fetches:
 *  - Vendor info + business hours
 *  - Vendor gallery images
 *  - Active deals from this vendor
 *  - Standard reviews + technical reviews
 *  - User gallery images
 *  - Club membership context for the current user
 */

import { eq, and, desc, gt, isNull, or, inArray, sql } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client';
import {
  vendors,
  vendorGalleryImages,
  userGalleryImages,
  deals,
  dealImages,
  dealTranslations,
  reviews,
  clubMemberships,
  favoriteVendors,
} from '@/server/db/schema';
import { getHoursForVendor } from '@/server/db/queries/business-hours';
import { getByVendorId as getAddressForVendor } from '@/server/db/queries/vendorAddresses';
import type { DealType } from '@/lib/deal-types';
import { notSoldOutDrizzle } from '@/server/db/queries/sold-out-filter';

export interface BusinessHoursData {
  vendorId: string;
  mondayOpen: string | null;
  mondayClose: string | null;
  mondayClosed: boolean;
  tuesdayOpen: string | null;
  tuesdayClose: string | null;
  tuesdayClosed: boolean;
  wednesdayOpen: string | null;
  wednesdayClose: string | null;
  wednesdayClosed: boolean;
  thursdayOpen: string | null;
  thursdayClose: string | null;
  thursdayClosed: boolean;
  fridayOpen: string | null;
  fridayClose: string | null;
  fridayClosed: boolean;
  saturdayOpen: string | null;
  saturdayClose: string | null;
  saturdayClosed: boolean;
  sundayOpen: string | null;
  sundayClose: string | null;
  sundayClosed: boolean;
  specialNotes: string | null;
}

export interface ActiveDealSummary {
  id: string;
  title: string;
  dealType: DealType;
  originalPrice: number;
  discountedPrice: number;
  discountPercent: number;
  quantityTotal: number;
  quantitySold: number;
  windowEnd: string | null;
  imageUrl: string | null;
  heSlug?: string | null;
}

export interface ReviewSummary {
  id: string;
  reviewerName: string;
  rating: number | null;
  body: string;
  date: string;
  vendorReply: string | null;
  reviewType: 'STANDARD' | 'TECHNICAL';
}

export interface GalleryImageSummary {
  id: string;
  src: string;
  alt: string;
}

export interface BusinessPageData {
  vendor: {
    id: string;
    slug: string;
    businessName: string;
    displayName: string;
    description: string;
    logoUrl: string | null;
    heroImageUrl: string | null;
    heroFocalX: number;
    heroFocalY: number;
    heroImageApprovalStatus: 'PENDING' | 'APPROVED' | 'REJECTED';
    tier: string;
    reviewsScore: number;
    reviewsCount: number;
    lat?: number;
    lng?: number;
    address?: string;
  };
  hours: BusinessHoursData | null;
  vendorGallery: GalleryImageSummary[];
  userGallery: GalleryImageSummary[];
  activeDeals: ActiveDealSummary[];
  reviews: ReviewSummary[];
  technicalReviews: ReviewSummary[];
  isMember: boolean;
  joinedViaDeal: boolean;
  isFavorited: boolean;
}

const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

export async function loadBusinessPage(
  db: DrizzleClient,
  { slugOrId, userId }: { slugOrId: string; userId?: string },
): Promise<BusinessPageData | null> {
  if (!UUID_RE.test(slugOrId)) return null;

  // 1. Load vendor — only ACTIVE or VETERAN vendors are visible on public surfaces.
  // Select only the columns needed for BusinessPageData — avoids transferring PII
  // ciphertext (phone, email) and Stripe/admin fields over the wire.
  const vendorRows = await db
    .select({
      id: vendors.id,
      businessName: vendors.businessName,
      displayName: vendors.displayName,
      description: vendors.description,
      logoUrl: vendors.logoUrl,
      heroImageUrl: vendors.heroImageUrl,
      heroFocalX: vendors.heroFocalX,
      heroFocalY: vendors.heroFocalY,
      heroImageApprovalStatus: vendors.heroImageApprovalStatus,
      tier: vendors.tier,
      reviewsScore: vendors.reviewsScore,
      reviewsCount: vendors.reviewsCount,
      accountState: vendors.accountState,
    })
    .from(vendors)
    .where(and(eq(vendors.id, slugOrId), inArray(vendors.accountState, ['ACTIVE', 'VETERAN'])))
    .limit(1);

  if (!vendorRows[0]) return null;
  const vendor = vendorRows[0];

  // 2. Parallel fetches
  const [
    hours,
    vendorGalleryRows,
    userGalleryRows,
    activeDealsRows,
    standardReviewsRows,
    technicalReviewsRows,
    address,
    membershipRows,
    favoriteRows,
  ] = await Promise.all([
    // hours
    getHoursForVendor(db, vendor.id),
    // vendor gallery — project only rendered fields
    db
      .select({
        id: vendorGalleryImages.id,
        url: vendorGalleryImages.url,
        caption: vendorGalleryImages.caption,
        sortOrder: vendorGalleryImages.sortOrder,
      })
      .from(vendorGalleryImages)
      .where(eq(vendorGalleryImages.vendorId, vendor.id))
      .orderBy(vendorGalleryImages.sortOrder)
      .limit(20),
    // user gallery — project only rendered fields
    db
      .select({
        id: userGalleryImages.id,
        url: userGalleryImages.url,
        caption: userGalleryImages.caption,
        createdAt: userGalleryImages.createdAt,
      })
      .from(userGalleryImages)
      .where(eq(userGalleryImages.vendorId, vendor.id))
      .orderBy(desc(userGalleryImages.createdAt))
      .limit(20),
    // active deals with primary image — use cache cols + default-SKU subqueries
    db
      .select({
        id: deals.id,
        title: deals.title,
        dealType: deals.dealType,
        minPrice: deals.minPrice,
        maxDiscountPercent: deals.maxDiscountPercent,
        stockRemaining: deals.stockRemaining,
        windowEnd: deals.windowEnd,
        imageUrl: dealImages.url,
        originalPrice: sql<string>`(SELECT original_price FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
        discountPercent: sql<number>`(SELECT discount_percent FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
        quantityTotal: sql<number>`(SELECT quantity_total FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
        quantitySold: sql<number>`(SELECT quantity_sold FROM deal_skus WHERE deal_id = ${deals.id} AND option_ids_hash = 'default' LIMIT 1)`,
        heSlug: dealTranslations.slug,
      })
      .from(deals)
      .leftJoin(dealImages, and(eq(dealImages.dealId, deals.id), eq(dealImages.isPrimary, true)))
      .leftJoin(
        dealTranslations,
        and(eq(dealTranslations.dealId, deals.id), eq(dealTranslations.locale, 'he')),
      )
      .where(
        and(
          eq(deals.vendorId, vendor.id),
          eq(deals.dealState, 'ACTIVE'),
          or(isNull(deals.windowEnd), gt(deals.windowEnd, new Date())),
          notSoldOutDrizzle(),
        ),
      )
      .orderBy(desc(deals.createdAt))
      .limit(12),
    // standard reviews
    db
      .select()
      .from(reviews)
      .where(
        and(
          eq(reviews.vendorId, vendor.id),
          eq(reviews.reviewType, 'STANDARD'),
          eq(reviews.isVisible, true),
        ),
      )
      .orderBy(desc(reviews.createdAt))
      .limit(20),
    // technical reviews
    db
      .select()
      .from(reviews)
      .where(
        and(
          eq(reviews.vendorId, vendor.id),
          eq(reviews.reviewType, 'TECHNICAL'),
          eq(reviews.isVisible, true),
        ),
      )
      .orderBy(desc(reviews.createdAt))
      .limit(20),
    // vendor address
    getAddressForVendor(db, vendor.id),
    // membership context
    userId
      ? db
          .select()
          .from(clubMemberships)
          .where(
            and(
              eq(clubMemberships.userId, userId),
              eq(clubMemberships.vendorId, vendor.id),
              eq(clubMemberships.isActive, true),
            ),
          )
          .limit(1)
      : Promise.resolve([]),
    // favorite vendor check
    userId
      ? db
          .select({ id: favoriteVendors.id })
          .from(favoriteVendors)
          .where(and(eq(favoriteVendors.userId, userId), eq(favoriteVendors.vendorId, vendor.id)))
          .limit(1)
      : Promise.resolve([]),
  ]);

  const membership = membershipRows[0];
  const isFavorited = favoriteRows.length > 0;

  return {
    vendor: {
      id: vendor.id,
      slug: vendor.id,
      businessName: vendor.businessName,
      displayName: vendor.displayName,
      description: vendor.description,
      logoUrl: vendor.logoUrl ?? null,
      heroImageUrl: vendor.heroImageUrl ?? null,
      heroFocalX: vendor.heroFocalX ?? 0.5,
      heroFocalY: vendor.heroFocalY ?? 0.5,
      heroImageApprovalStatus: (vendor.heroImageApprovalStatus ?? 'PENDING') as
        | 'PENDING'
        | 'APPROVED'
        | 'REJECTED',
      tier: vendor.tier ?? 'NEW',
      reviewsScore: parseFloat(vendor.reviewsScore),
      reviewsCount: vendor.reviewsCount,
      lat: address ? parseFloat(address.lat) : undefined,
      lng: address ? parseFloat(address.lng) : undefined,
      address: address?.fullAddress,
    },
    hours: hours
      ? {
          vendorId: hours.vendorId,
          mondayOpen: hours.mondayOpen ?? null,
          mondayClose: hours.mondayClose ?? null,
          mondayClosed: hours.mondayClosed,
          tuesdayOpen: hours.tuesdayOpen ?? null,
          tuesdayClose: hours.tuesdayClose ?? null,
          tuesdayClosed: hours.tuesdayClosed,
          wednesdayOpen: hours.wednesdayOpen ?? null,
          wednesdayClose: hours.wednesdayClose ?? null,
          wednesdayClosed: hours.wednesdayClosed,
          thursdayOpen: hours.thursdayOpen ?? null,
          thursdayClose: hours.thursdayClose ?? null,
          thursdayClosed: hours.thursdayClosed,
          fridayOpen: hours.fridayOpen ?? null,
          fridayClose: hours.fridayClose ?? null,
          fridayClosed: hours.fridayClosed,
          saturdayOpen: hours.saturdayOpen ?? null,
          saturdayClose: hours.saturdayClose ?? null,
          saturdayClosed: hours.saturdayClosed,
          sundayOpen: hours.sundayOpen ?? null,
          sundayClose: hours.sundayClose ?? null,
          sundayClosed: hours.sundayClosed,
          specialNotes: hours.specialNotes ?? null,
        }
      : null,
    vendorGallery: vendorGalleryRows.map((img) => ({
      id: img.id,
      src: img.url,
      alt: img.caption ?? vendor.displayName,
    })),
    userGallery: userGalleryRows.map((img) => ({
      id: img.id,
      src: img.url,
      alt: img.caption ?? vendor.displayName,
    })),
    activeDeals: activeDealsRows.map((row) => ({
      id: row.id,
      title: row.title,
      dealType: row.dealType,
      originalPrice: parseFloat(row.originalPrice ?? row.minPrice ?? '0'),
      discountedPrice: parseFloat(row.minPrice ?? '0'),
      discountPercent: row.discountPercent ?? row.maxDiscountPercent ?? 0,
      quantityTotal: row.quantityTotal ?? 0,
      quantitySold: row.quantitySold ?? 0,
      windowEnd: row.windowEnd?.toISOString() ?? null,
      imageUrl: row.imageUrl ?? null,
      heSlug: row.heSlug ?? undefined,
    })),
    reviews: standardReviewsRows.map((r) => ({
      id: r.id,
      reviewerName: 'לקוח',
      rating: r.rating,
      body: r.body,
      date: r.createdAt.toISOString(),
      vendorReply: r.vendorReply ?? null,
      reviewType: 'STANDARD' as const,
    })),
    technicalReviews: technicalReviewsRows.map((r) => ({
      id: r.id,
      reviewerName: 'לקוח',
      rating: null,
      body: r.body,
      date: r.createdAt.toISOString(),
      vendorReply: null,
      reviewType: 'TECHNICAL' as const,
    })),
    isMember: !!membership,
    joinedViaDeal: !!membership,
    isFavorited,
  };
}