/**
 * Coalesced page-data bundle — collapses 3 Neon subrequests into 1.
 *
 * getPageDataBundle fires a single SELECT that returns:
 *   - wishlist         : JSON array of saved deal IDs
 *   - favorite_vendors : JSON array of favorited vendor IDs
 *   - cart             : JSON array of cart rows (same projection as getCart)
 *   - email_decrypted   : decrypted email text (only when needsEv + piiKey)
 *   - email_verified_at : timestamptz string (only when needsEv)
 */

import { executeRows } from '../execute-rows.js';
import { sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { type GetCartResult, type CartRawRow, processCartRows } from './cart.js';
import { getQtyTiersForSkus } from './sku-qty-tiers.js';

// ---------------------------------------------------------------------------
// Return type
// ---------------------------------------------------------------------------

export type PageDataBundle = {
  wishlist: string[];
  favoriteVendors: string[];
  cart: GetCartResult;
  emailDecrypted: string | null;
  emailVerifiedAt: Date | null;
};

// ---------------------------------------------------------------------------
// Raw row returned by db.execute (neon-http poolQueryViaFetch mode)
// ---------------------------------------------------------------------------

type BundleRow = {
  wishlist: string[];
  favorite_vendors: string[];
  cart: RawCartJsonRow[];
  email_decrypted: string | null;
  email_verified_at: string | null;
};

/** Shape of each JSON object inside cart json_agg (snake_case from row_to_json) */
type RawCartJsonRow = {
  cart_item_id: string;
  deal_id: string;
  deal_sku_id: string | null;
  qty: number;
  added_at: string;
  deal_title: string;
  vendor_id: string;
  vendor_name: string;
  discounted_price: string;
  original_price: string;
  commission_rate: string;
  max_per_user: number | null;
  stock_remaining: number | null;
  deal_state: string;
  window_end: string | null;
  image_url: string | null;
};

// ---------------------------------------------------------------------------
// getPageDataBundle
// ---------------------------------------------------------------------------

/**
 * Single-round-trip replacement for:
 *   getWishlistedDealIds + getFavoritedVendorIds + getCart + getUserWithDecryptedEmail
 *
 * Executes ONE parameterised SELECT with scalar subqueries.
 * Returns the same data shapes the individual helpers returned.
 */
export async function getPageDataBundle(
  db: DrizzleClient,
  userId: string,
  opts: { needsEv: boolean; piiKey?: string },
): Promise<PageDataBundle> {
  const { needsEv, piiKey } = opts;

  // Build conditional SQL fragments rather than binding booleans as parameters.
  // Neon-HTTP can't infer `$1` as boolean inside `CASE WHEN $1 AND $2 IS NOT NULL`
  // and throws "could not determine data type of parameter $N" — the bundle then
  // fails silently in the caller's try/catch. Inlining keeps the row shape stable
  // without round-tripping unused parameters.
  const emailDecryptedCol =
    needsEv && piiKey
      ? sql`(
          SELECT
            CASE WHEN email IS NOT NULL
              THEN pgp_sym_decrypt(
                CASE WHEN substring(email, 1, 2) = '\\x'
                  THEN email::bytea
                  ELSE decode(email, 'base64')
                END,
                ${piiKey}
              )::text
              ELSE NULL
            END
          FROM users
          WHERE id = ${userId}::uuid
        )`
      : sql`NULL::text`;

  const emailVerifiedAtCol = needsEv
    ? sql`(
        SELECT email_verified_at
        FROM users
        WHERE id = ${userId}::uuid
      )`
    : sql`NULL::timestamptz`;

  const result = await db.execute<BundleRow>(sql`
    SELECT
      -- wishlist: array of deal IDs saved by this user
      (
        SELECT COALESCE(json_agg(deal_id), '[]'::json)
        FROM wishlist_items
        WHERE user_id = ${userId}::uuid
      ) AS wishlist,

      -- favorite_vendors: array of vendor IDs favorited by this user (IDs only, no PII)
      (
        SELECT COALESCE(json_agg(vendor_id), '[]'::json)
        FROM favorite_vendors
        WHERE user_id = ${userId}::uuid
      ) AS favorite_vendors,

      -- cart: rows matching getCart's SELECT projection, as JSON
      (
        SELECT COALESCE(json_agg(row_to_json(r)), '[]'::json)
        FROM (
          SELECT
            cl.id               AS cart_item_id,
            cl.variant_id       AS deal_sku_id,
            dsk.deal_id,
            cl.qty,
            c.updated_at        AS added_at,
            d.title             AS deal_title,
            d.vendor_id,
            v.display_name      AS vendor_name,
            dsk.discounted_price,
            dsk.original_price,
            d.commission_rate,
            d.max_per_user,
            d.stock_remaining,
            d.deal_state,
            d.window_end,
            di.url              AS image_url
          FROM cart c
          INNER JOIN cart_line cl ON cl.cart_id = c.id
          INNER JOIN deal_skus dsk ON dsk.id = cl.variant_id
          INNER JOIN deals d       ON d.id = dsk.deal_id
          INNER JOIN vendors v     ON v.id = d.vendor_id
          LEFT  JOIN deal_images di
                 ON di.deal_id = d.id AND di.is_primary = TRUE
          WHERE c.user_id = ${userId}
        ) r
      ) AS cart,

      ${emailDecryptedCol} AS email_decrypted,
      ${emailVerifiedAtCol} AS email_verified_at
  `);

  // neon-http with poolQueryViaFetch returns { rows: [...] }
  const row = executeRows<BundleRow>(result)[0];

  // Normalize cart JSON rows: map snake_case keys + parse ISO date strings to Date
  const cartJsonRows = (row?.cart as RawCartJsonRow[]) ?? [];
  const skuIds = cartJsonRows.map((r) => r.deal_sku_id).filter((id): id is string => id !== null);
  const tierMap = await getQtyTiersForSkus(db, skuIds);
  const rawCartRows: CartRawRow[] = cartJsonRows.map((r) => ({
    cartItemId: r.cart_item_id,
    dealSkuId: r.deal_sku_id,
    dealId: r.deal_id,
    qty: r.qty,
    addedAt: new Date(r.added_at),
    dealTitle: r.deal_title,
    vendorId: r.vendor_id,
    vendorName: r.vendor_name,
    discountedPrice: r.discounted_price,
    originalPrice: r.original_price,
    commissionRate: r.commission_rate,
    maxPerUser: r.max_per_user,
    stockRemaining: r.stock_remaining,
    dealState: r.deal_state,
    windowEnd: r.window_end ? new Date(r.window_end) : null,
    imageUrl: r.image_url,
    qtyTiers: r.deal_sku_id ? (tierMap.get(r.deal_sku_id) ?? []) : [],
  }));

  return {
    wishlist: (row?.wishlist as string[]) ?? [],
    favoriteVendors: (row?.favorite_vendors as string[]) ?? [],
    cart: processCartRows(rawCartRows),
    emailDecrypted: row?.email_decrypted ?? null,
    emailVerifiedAt: row?.email_verified_at ? new Date(row.email_verified_at) : null,
  };
}