/**
 * DealDetailDataLoader - server-side data fetcher for the Deal Detail page.
 * loadDeal(db, dealId) returns the deal with full vendor, images, business profile
 * data (logo, rating, address, today's hours) and related deals.
 */

import { israelWeekdayIndex } from '@/lib/datetime';
import { eq, and, desc, inArray, sql, asc } from 'drizzle-orm';
import { alias } from 'drizzle-orm/pg-core';
import type { DrizzleClient } from '@/server/db/client';
import {
  deals,
  vendors,
  dealImages,
  dealVariantAxes,
  type businessHours,
  dealCategories,
  categoryTranslations,
  reviews,
  users,
  vendorTranslations,
  groupDeals,
  groupTiers,
} from '@/server/db/schema';
import type { GroupDealState } from '@/lib/enums/group-deal-state';
import { VENDOR_DEFAULT_LOCALE } from '@/server/db/queries/vendor-constants';
import { getDealTagsWithNames } from '@/server/db/queries/tags';
import { getHoursForVendor } from '@/server/db/queries/business-hours';
import { getByVendorId as getAddressForVendor } from '@/server/db/queries/vendorAddresses';
import { PUBLIC_VENDOR_STATES } from '@/server/catalog/_shared/predicates.js';
import type { DealType } from '@/lib/deal-types';

export type WeekdayKey =
  | 'sunday'
  | 'monday'
  | 'tuesday'
  | 'wednesday'
  | 'thursday'
  | 'friday'
  | 'saturday';

export interface DayHours {
  weekday: WeekdayKey;
  closed: boolean;
  open: string | null;
  close: string | null;
}

export interface ReviewSummary {
  id: string;
  reviewerName: string | null;
  rating: number | null;
  body: string;
  vendorReply: string | null;
  createdAt: string;
}

export interface GalleryImage {
  id: string;
  url: string;
  isPrimary: boolean;
}

export interface DealDetailImages {
  dealMain: GalleryImage[];
  bySkuId: Record<string, GalleryImage[]>;
}

export interface GroupTierLite {
  minParticipants: number;
  discountPercent: number;
  pricePerUnit: number;
}

export interface GroupGroupProp {
  currentReservationCount: number;
  minGroupSize: number;
  maxGroupSize: number;
  groupState: GroupDealState;
  tiers: GroupTierLite[];
}

export interface DealDetailData {
  id: string;
  /** The cheapest active SKU id for non-variant deals (null for variant deals or if no active SKU). */
  defaultSkuId: string | null;
  title: string;
  description: string;
  specialInstructions: string | null;
  dealType: DealType;
  isVoucher: boolean;
  originalPrice: number;
  discountedPrice: number;
  quantityTotal: number;
  quantitySold: number;
  group: GroupGroupProp | null;
  pickupAddress: string;
  pickupStart: string | null;
  pickupEnd: string | null;
  windowEnd: string | null;
  dealState: string;
  vendor: {
    id: string;
    slug: string;
    displayName: string;
    businessName: string;
    logoUrl: string | null;
    heroImageUrl: string | null;
    heroFocalX: number;
    heroFocalY: number;
    heroImageApprovalStatus: 'PENDING' | 'APPROVED' | 'REJECTED';
    tier: string;
    reviewsScore: number;
    reviewsCount: number;
    address: string | null;
    city: string | null;
    todayHours: DayHours | null;
  };
  images: DealDetailImages;
  visualAxisOrder: number | null;
  categoryId: string | null;
  categoryNameHe: string | null;
  categoryNameEn: string | null;
  tags: Array<{ id: string; slug: string; nameHe: string; nameEn: string }>;
  reviews: ReviewSummary[];
}

const WEEKDAYS: WeekdayKey[] = [
  'sunday',
  'monday',
  'tuesday',
  'wednesday',
  'thursday',
  'friday',
  'saturday',
];

function pickTodayHours(row: typeof businessHours.$inferSelect | undefined): DayHours | null {
  if (!row) return null;
  const weekday = WEEKDAYS[israelWeekdayIndex()]!;
  const open = row[`${weekday}Open` as const];
  const close = row[`${weekday}Close` as const];
  const closed = row[`${weekday}Closed` as const];
  return {
    weekday,
    closed: Boolean(closed),
    open: open ?? null,
    close: close ?? null,
  };
}

/**
 * Load a deal by id with full vendor, images, business profile data, and related deals.
 * Returns null if not found.
 */
export async function loadDeal(db: DrizzleClient, dealId: string): Promise<DealDetailData | null> {
  const ctHe = alias(categoryTranslations, 'ct_he');
  const ctEn = alias(categoryTranslations, 'ct_en');
  const ctHeFb = alias(categoryTranslations, 'ct_he_fb');

  // Fire deal-ID-only sub-queries concurrently with the main deal+vendor join.
  // This eliminates the gating round-trip for images, tags, and visual axis.
  const [rows, imageRows, tags, visualAxisRow] = await Promise.all([
    db
      .select({
        deal: {
          id: deals.id,
          vendorId: deals.vendorId,
          dealType: deals.dealType,
          title: deals.title,
          description: deals.description,
          categoryId: deals.categoryId,
          isVoucher: deals.isVoucher,
          dealState: deals.dealState,
          windowStart: deals.windowStart,
          windowEnd: deals.windowEnd,
          pickupStart: deals.pickupStart,
          pickupEnd: deals.pickupEnd,
          pickupAddress: deals.pickupAddress,
          specialInstructions: deals.specialInstructions,
          commissionRate: deals.commissionRate,
          isPersonalDeal: deals.isPersonalDeal,
          personalDealForUserId: deals.personalDealForUserId,
          approvedAt: deals.approvedAt,
          contentHash: deals.contentHash,
          maxPerUser: deals.maxPerUser,
          createdAt: deals.createdAt,
          soldOutAt: deals.soldOutAt,
          minPrice: deals.minPrice,
          maxPrice: deals.maxPrice,
          maxDiscountPercent: deals.maxDiscountPercent,
          stockRemaining: deals.stockRemaining,
          // "From" SKU = cheapest active SKU. Variant deals have no 'default' SKU,
          // so resolve price from the active SKU grid; stock sums across all active SKUs.
          defaultSkuId: sql<
            string | null
          >`(SELECT id FROM deal_skus WHERE deal_id = ${deals.id} AND is_active = true ORDER BY discounted_price ASC LIMIT 1)`,
          originalPrice: sql<string>`(SELECT original_price FROM deal_skus WHERE deal_id = ${deals.id} AND is_active = true ORDER BY discounted_price ASC LIMIT 1)`,
          discountedPrice: sql<string>`(SELECT discounted_price FROM deal_skus WHERE deal_id = ${deals.id} AND is_active = true ORDER BY discounted_price ASC LIMIT 1)`,
          // Cast to ::int — PostgreSQL SUM(integer) returns bigint which Neon's serverless
          // driver returns as a JS string. Without the cast, numeric comparisons like
          // `quantitySold >= quantityTotal` do string comparison ('6' >= '50' = true)
          // and incorrectly report sold-out after the 6th cumulative purchase.
          quantityTotal: sql<number>`(SELECT COALESCE(SUM(quantity_total), 0) FROM deal_skus WHERE deal_id = ${deals.id} AND is_active = true)::int`,
          quantitySold: sql<number>`(SELECT COALESCE(SUM(quantity_sold), 0) FROM deal_skus WHERE deal_id = ${deals.id} AND is_active = true)::int`,
        },
        vendor: vendors,
        categoryNameHe: sql<string | null>`COALESCE(${ctHe.name}, ${ctHeFb.name})`,
        categoryNameEn: sql<string | null>`COALESCE(${ctEn.name}, ${ctHeFb.name})`,
      })
      .from(deals)
      .innerJoin(vendors, eq(deals.vendorId, vendors.id))
      .leftJoin(dealCategories, eq(deals.categoryId, dealCategories.id))
      .leftJoin(ctHe, and(eq(ctHe.categoryId, dealCategories.id), eq(ctHe.locale, 'he')))
      .leftJoin(ctEn, and(eq(ctEn.categoryId, dealCategories.id), eq(ctEn.locale, 'en')))
      .leftJoin(ctHeFb, and(eq(ctHeFb.categoryId, dealCategories.id), eq(ctHeFb.locale, 'he')))
      .where(and(eq(deals.id, dealId), inArray(vendors.accountState, [...PUBLIC_VENDOR_STATES])))
      .limit(1),
    // Images, tags, visualAxis only need dealId — fire in parallel with main join.
    db
      .select({
        id: dealImages.id,
        url: dealImages.url,
        skuId: dealImages.skuId,
        isPrimary: dealImages.isPrimary,
        sortOrder: dealImages.sortOrder,
      })
      .from(dealImages)
      .where(and(eq(dealImages.dealId, dealId), eq(dealImages.approvalStatus, 'APPROVED')))
      .orderBy(asc(dealImages.sortOrder)),
    getDealTagsWithNames(db, dealId),
    db
      .select({ axisOrder: dealVariantAxes.axisOrder })
      .from(dealVariantAxes)
      .where(and(eq(dealVariantAxes.dealId, dealId), eq(dealVariantAxes.isVisualAxis, true)))
      .limit(1),
  ]);

  if (!rows[0]) return null;
  const { deal, vendor, categoryNameHe, categoryNameEn } = rows[0];

  // Vendor-dependent queries: hours, address, reviews (need vendor.id from first query).
  const [hoursRow, address, reviewRows] = await Promise.all([
    getHoursForVendor(db, vendor.id),
    getAddressForVendor(db, vendor.id),
    db
      .select({
        id: reviews.id,
        rating: reviews.rating,
        body: reviews.body,
        vendorReply: reviews.vendorReply,
        createdAt: reviews.createdAt,
        reviewerName: sql<
          string | null
        >`(SELECT vt2.display_name FROM ${vendorTranslations} vt2 INNER JOIN vendors v2 ON v2.id = vt2.vendor_id AND v2.owner_user_id = ${sql.raw('"users"."id"')} WHERE vt2.locale = ${VENDOR_DEFAULT_LOCALE} LIMIT 1)`,
      })
      .from(reviews)
      .innerJoin(users, eq(reviews.userId, users.id))
      .where(
        and(
          eq(reviews.vendorId, vendor.id),
          eq(reviews.reviewType, 'STANDARD'),
          eq(reviews.isVisible, true),
        ),
      )
      .orderBy(desc(reviews.createdAt))
      .limit(10),
  ]);

  // Group deal images: null skuId → dealMain, non-null → bySkuId bucket.
  const dealMain: GalleryImage[] = [];
  const bySkuId: Record<string, GalleryImage[]> = {};
  for (const row of imageRows) {
    const galleryImage: GalleryImage = {
      id: row.id,
      url: row.url,
      isPrimary: row.isPrimary,
    };
    if (row.skuId === null) {
      dealMain.push(galleryImage);
    } else {
      (bySkuId[row.skuId] ??= []).push(galleryImage);
    }
  }
  const visualAxisOrder = visualAxisRow[0]?.axisOrder ?? null;

  let group: GroupGroupProp | null = null;
  if (deal.dealType === 'GROUP') {
    const [gd] = await db
      .select({
        id: groupDeals.id,
        currentReservationCount: groupDeals.currentReservationCount,
        minGroupSize: groupDeals.minGroupSize,
        maxGroupSize: groupDeals.maxGroupSize,
        groupState: groupDeals.groupState,
        tieredPricingEnabled: groupDeals.tieredPricingEnabled,
      })
      .from(groupDeals)
      .where(eq(groupDeals.dealId, deal.id))
      .limit(1);
    if (gd) {
      const tierRows = gd.tieredPricingEnabled
        ? await db
            .select({
              minParticipants: groupTiers.minParticipants,
              discountPercent: groupTiers.discountPercent,
              pricePerUnit: groupTiers.pricePerUnit,
            })
            .from(groupTiers)
            .where(eq(groupTiers.groupDealId, gd.id))
            .orderBy(groupTiers.minParticipants)
        : [];
      group = {
        currentReservationCount: gd.currentReservationCount,
        minGroupSize: gd.minGroupSize,
        maxGroupSize: gd.maxGroupSize,
        groupState: gd.groupState as GroupDealState,
        tiers: tierRows.map((t) => ({
          minParticipants: t.minParticipants,
          discountPercent: t.discountPercent,
          pricePerUnit: Number(t.pricePerUnit),
        })),
      };
    }
  }

  return {
    id: deal.id,
    defaultSkuId: deal.defaultSkuId ?? null,
    title: deal.title,
    description: deal.description,
    specialInstructions: deal.specialInstructions ?? null,
    dealType: deal.dealType,
    isVoucher: deal.isVoucher,
    originalPrice: parseFloat(deal.originalPrice ?? deal.minPrice ?? '0'),
    discountedPrice: parseFloat(deal.discountedPrice ?? deal.minPrice ?? '0'),
    quantityTotal: deal.quantityTotal ?? 0,
    quantitySold: deal.quantitySold ?? 0,
    group,
    pickupAddress: deal.pickupAddress,
    pickupStart: deal.pickupStart ?? null,
    pickupEnd: deal.pickupEnd ?? null,
    windowEnd: deal.windowEnd?.toISOString() ?? null,
    dealState: deal.dealState,
    vendor: {
      id: vendor.id,
      slug: vendor.id,
      displayName: vendor.displayName,
      businessName: vendor.businessName,
      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,
      address: address?.fullAddress ?? null,
      city: address?.city ?? null,
      todayHours: pickTodayHours(hoursRow),
    },
    images: { dealMain, bySkuId },
    visualAxisOrder,
    categoryId: deal.categoryId ?? null,
    categoryNameHe: categoryNameHe ?? null,
    categoryNameEn: categoryNameEn ?? null,
    tags,
    reviews: reviewRows.map((r) => ({
      id: r.id,
      reviewerName: r.reviewerName ?? null,
      rating: r.rating,
      body: r.body,
      vendorReply: r.vendorReply ?? null,
      createdAt: r.createdAt.toISOString(),
    })),
  };
}